Database System Development Exam

Database System Development Exam Adam Warsame Course: FDSc Computing Module Title: Database Systems Assignment Title: Database Systems Assessment Introduction The task for this assignment is to develop a database for a local garden centre. To understand the system better and what way it should operate; it was a good idea to review other examples of databases online. From these reviewed examples, was able to gather a rough understanding of what the database should withhold. Analysis The following tables were chosen for this database: Customers:- Customers table is used to hold all the vital information about a customer, such as: full name, address, email and feedback/reviews. Delivery:- Delivery table is used to withhold all the necessary information needed such as shipping date, order information, arrival date and delivery company. Plants:- Plants table is used to store all the information about plants such as popular name, type, features, description etc. Orders:- Orders table is used to hold all the information needed such as order date, delivery information, plant quantities and total price. The following tables (including attributes) were required as a minimum: Customers table; Full Name Address Email Feedback Deliveries table; Order information Shipping date Arrival date Delivery company Plant table; Garden Plant case Latin name Popular name Colour of foliage Colour of flowers Flowering period Height Spread Type of plant Full description and more Orders table; Order date Delivery information Plants quantities Plant prices The database will let staff/admins have a range of rights over the system and will be able to do things such as, edit plant list, create plant list, delete plant list, oversee customer details, monitor orders and check delivery information. Customers will also be able to do a range of things such as, register, make orders, check available plants and choose plants according to its type, attributes or position. Basic commands like edit, delete or save will allow the customer and staff members to quickly accustom to the database and discover things while doing so. As this is an SQL database, we can automatically assume that it will be constantly in development; both in expanding and editing form; which are the simplest of objectives for this type of database. A backup option is implemented to reduce and in due course; stop data loss. Simple password protection will also safeguard customers and staff from identity theft. The requirements are to create a database for a local garden centre called â€Å"Lincoln Garden Centre†. The system should provide the selling facilities, oversee and keep track of customer, plants, orders and staff. Below there is a data flow diagram which displays how the system will allow the customers/users to make orders and what type of processes are included until they have their order. Once the user has registered in the database, they will have the opportunity to scroll through the list of plants and/or select the exact features in which they are interested in most. When they finally decide about their order, they will be able to place an order which in turn will then be seen by a staff member who can store the customer’s order and shipping details and allocate them in the order table. Customers will be able track their orders and identify the estimate dispatch/delivery dates. When the orders are released, the staff can update the plants list if necessary. The whole system is built for this and will easily be able to trace orders, most popular plants, inventory and all related operations. Entity-Relational Model Normalisation 1NF 1NF is the most basic of all the normalisation processes but 1NF can only work if all tables have different entity types. Meaning, no relationships between 2 or more can be developed; otherwise it would break the rules for 1NF. The other rule is that all tables need a primary key, or else that would also go against the rules. 2NF One of the rules within 2NF process is that, only 1 row can have the same details stored. For example, when searching for a postcode, it would also show the town, county etc. To get around this then postcode will need to be created in its own table (postcode being the primary key also a foreign key in other tables) thus completing the need for relations in 2NF. 3NF Values that have the potential to be changed, in a 3NF database, need to be within a table, as a single order may alter the stock levels by creating an order. If not removed, the normalisation rules cannot be met. Within a 3NF system there shouldn’t be any rows/columns that have the exact same information; this is because it should go into a new table. SQL statements for implementation and manipulating database 1) Database: `199209-garden` 2) CREATE TABLE IF NOT EXISTS `Customers` ( `Customer_ID` int(11) NOT NULL AUTO_INCREMENT, `Full Name` varchar(25) NOT NULL, `Address` varchar(255) NOT NULL, `Email` varchar(50) NOT NULL, `Feedback ` varchar(255) DEFAULT NULL, PRIMARY KEY (`Customer_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ; data for table `Customers` 3) INSERT INTO `Customers` (`Customer_ID`, `Full Name`, `Address`, `Email`, `Feedback/Reviews`) VALUES (1, Jo Millan, 7 Windsor Road, CHESEFIELD, FH1 9LA, [emailprotected], NULL), (2, Harry Hugo, 4 Colywn Bay Road, WANEKE, BA91 8FS , [emailprotected], NULL), (3, Alice Payton, 2 Moor Way, PRESTATYN, NF44 1GB, [emailprotected], NULL), (4, Paul Hudson, 19 Johnno Rd, POSSING, PV33 0FLL, [emailprotected], NULL), (5, Tessa Godknock, 79 Butler Lane, HAHISON, DC10 5LF, [emailprotected], NULL), (6, Abdi Armed, 41 Wool Rd, GONZAGAE, BF35 64S, [emailprotected], Nice website!), (7, Masteran Katrina, 3 Bopping Rd, BEALUMONT, FP3 8TE, [emailprotected], NULL), (8, Ryan Starr, 99 Trevor Lane, LOUIS, ZX25 4GL, [emailprotected], NULL), (9, Ben Augustine, 61 Llaneli Street, BALLYMENA, OA38 8FB, [emailprotected], NULL); ); CREATE TABLE IF NOT EXISTS `Delivery` ( `Delivery_ID` int(11) NOT NULL AUTO_INCREMENT, `Shipping Date` datetime NOT NULL, `Delivery Information` varchar(255) NOT NULL, `Arrival Date` datetime NOT NULL, `Delivery Company` varchar(255) NOT NULL, PRIMARY KEY (`Delivery_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; data for table `Delivery` INSERT INTO `Delivery` (`Delivery_ID`, `Shipping Date`, `Delivery Information`, `Arrival Date`, `Delivery Company`) VALUES (1, 2011-04-16 15:00:00, Order_ID 1, 2011-04-22 12:00:00, Royal mail. 1st class), (2, 2012-10-21 14:00:00, Order Number 2, 2012-11-07 14:00:00, Ups), (3, 2012-01-11 11:00:00, Order Number 2, 2012-01-22 13:00:00, Royal mail. 2nd class), (4, 2013-11-09 13:00:00, Order Number 3, 2013-11-30 12:00:00, Ups); ); CREATE TABLE IF NOT EXISTS `Orders` ( `Order_ID` int(11) NOT NULL AUTO_INCREMENT, `Order date` datetime NOT NULL, `Order information` text NOT NULL, `Plant quantities` varchar(255) NOT NULL, `Price` varchar(255) NOT NULL, PRIMARY KEY (`Order_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; data for table `Orders` INSERT INTO `Orders` (`Order_ID`, `Order date`, `Order information`, `Plant quantities`, `Price`) VALUES (1, 2013-01-10 00:00:00, Rosa Falstaff, Balkan cranesbill., 1, 21.98), (2, 2012-12-28 13:00:00, Abyssinian gladiolus,Ginger lily,Striped bloody cranesbill, 1, 31.89), (3, 2013-01-07 00:00:00, Skyscraper lily, 2, 7.98), (4, 2013-01-07 18:00:00, Striped bloody cranesbill, 10, 79.90); Table structure for table `Plants` CREATE TABLE IF NOT EXISTS `Plants` ( `Plant_ID` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) NOT NULL, `Latin Name` varchar(255) NOT NULL, `Type` varchar(255) NOT NULL, `Colour` varchar(255) NOT NULL, `Features` varchar(255) NOT NULL, `Flowering Season` varchar(255) NOT NULL, `Scented/Unscented` varchar(255) NOT NULL, `Groundcover` varchar(255) NOT NULL, `Sun Level` varchar(255) NOT NULL, `Soil Type` varchar(255) NOT NULL, `Orientation` varchar(255) NOT NULL, `Special conditions` varchar(255) NOT NULL, `Position` varchar(255) NOT NULL, `Description` varchar(5000) NOT NULL, `Price` varchar(255) NOT NULL, PRIMARY KEY (`Plant_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=Plants Table AUTO_INCREMENT=11 ; data for table `Plants` INSERT INTO `Plants` (`Plant_ID`, `Name`, `Latin Name`, `Type`, `Colour`, `Features`, `Flowering Season`, `Scented/Unscented`, `Groundcover`, `Sun Level`, `Soil Type`, `Orientation`, `Special conditions`, `Position`, `Description`, `Price`) VALUES (1, Rosa Falstaff, Ausverse, Rose, Purple, Excellent cut-flowers, Summer, Scented, No, Full sun, Fertile, humus-rich, moist, well-drained soil, North, No special conditions, Front of border, One of the best crimson/purple roses bred to date. The large, cupped, double flowers have a powerful old rose fragrance. The rich, dark crimson flowers eventually turn to a lovely rich purple colour., 13.99), (2, Narcissus , Canaliculatus, Bulbs, White petals with a yellow cup, Low maintenance, Spring, Scented, Yes, Full sun or partial shade, Well-drained soil, South, No special conditions, Next to an entrance, windowboxes, front of border, in a pot, Plant this at the front of your border, in a pot or in your windowboxes where you can enjoy the small, scented flowers up close. This variety produces several flowers on each stem so they are really colourful. The flowers have white petals with a small yellow cup and they look best in bold clumps., 3.49), (3, Balkan cranesbill, Geranium macrorrhizum, Perennials, Pink, Non poisonous, May to September, Aromatic foliage, Yes, Full sun or partial shade, Fertile, well-drained soil, West, Dry shade, Front of border, Clusters of small, saucer shaped, purplish pink flowers are held on slender stems above strong aromatic, light green leaves from May to September. This is one of the most versatile and useful geraniums, as it will thrive even in dry shade. The deeply cut, semi-evergreen foliage also spreads quickly to form low hummocks that suppress weeds for most of the year. It looks lovely with strongly shaped perennials, such as bergenia, or polemonium, or planted en masse under trees, and is also valuable for hiding the foliage of spring bulbs as they die down. The leaves often turn red in the autumn too., 7.99), (4, Sulphurea, Oenothera stricta, Evening primrose, Creamy yellow, Non poisonous, May to September, Evening, No, Sun, Acid, South, Coastal, Middle of border, Reliably flowering for many months, this showy evening primrose may be short-lived, but it usually self-seeds very freely. It produces large, pale yellow, almost cream flowers that will open in the evening and fill the air with their incredible, heady scent. It is very tolerant of poor soils and is generally very easy to grow., 1.59), (5, Clematis, Clematis cirrhosa var. purpurascens, Climbers, Cream, Winter colour, All year round, Scented, No, Sun, Light sandy, West, No special conditions, Walls and fences, Scented, bell-like, cream winter flowers heavily speckled inside with reddish-brown freckles and glossy, dark-green leaves. This evergreen clematis is ideal for training over a sunny pergola or arch. This is the best way to appreciate the distinctive freckle-like markings, which are less visible when the plant is grown against a wall., 14.69), (6, Skyscraper lily, Lilium Bonbini, Bulbs, Cream, Non poisonous, July, Scented, No, Full sun with light dappled shade at its base, Fertile, reliably moist soil, South, No special conditions, Middle of border, full sun with light dappled shade at its base, 3.99), (7, Ginger lily, Hedychium densiflorum, Bulbs, Orange-red, Architectural foliage, July and August, Scented, No, Full sun or partial shade, Rich, moist soil, South, No special conditions, Middle of border, Forming a slowly spreading clump of lustrous foliage, this compact ginger lily is one of the hardier forms. The slender spikes of fragrant, orange-red flowers appear early in the season and tend to open in one impressive flush., 5.99), (8, Abyssinian gladiolus, Gladiolus murielae, Bulbs, White, Non poisonous, Augsut to October, Scented, No, Full sun, Moist, well-drained soil, South, No special conditions, Middle of border, Delightlful spikes of nodding, funnel-shaped, highly fragrant white flowers, with a prominent burgundy blotch at the base of each petal, seem to dance on the breeze at the ends of their slender stems from late summer. It is a delightfully elegant plant that is ideal for adding movement to a sunny border. Plant it in clumps throughout the beds, or pot them up and keep them on the patio next to a seating area or often-used pathway, where you will be sure to make the most of the heady scent. , 4.99), (9, Lily, Lilium Dimension, Bulbs, Deepest red, Non poisonous, July to August, Scented, No, Partial shade, Moist, acidic soil, South, No special conditions, Middle of border, Branching, near-black stems carry clusters of upward-facing, dark claret-coloured flowers from midsummer. An asiatic hybrid, the tepals that make up the trumpet-shaped flowers, become more re-curved as the flower ages., 5.99), (10, Striped bloody cranesbill, Geranium sanguineum var. striatum, Mediterranean, Light purple, Non poisonous, June to August, Unscented, No, Full sun or partial shade, Fertile, well-drained soil, West, Dry shade, In a rock garden, fertile, well-drained soil, 7.99); ); CREATE TABLE IF NOT EXISTS `Staff` ( `Staff_ID` int(11) NOT NULL AUTO_INCREMENT, `Full Name` varchar(255) NOT NULL, `Address` varchar(255) NOT NULL, `Phone number` varchar(255) NOT NULL, `DOB` date NOT NULL, PRIMARY KEY (`Staff_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; data for table `Staff` INSERT INTO `Staff` (`Staff_ID`, `Full Name`, `Address`, `Phone number`, `DOB`) VALUES (1, Dean Beale, 3 Crennop Road, CRENSHAW, C52 4ND, 077 8192 8493, 1973-11-19), (2, Isabell Edwards, 29 Beddie Road, PORTERS, CA1 0AS, 079 1028 9487, 1965-01-01), (3, Coutinho, 8 Hohlong Street, TREVENDALEIO, KL41 1LE, 070 5878 0006, 1966-02-17), (4, Mirak Klose, 81 Trollige Road, MAGGIE, DG7 2AH, 075 4832 1123, 1988-10-08), (5, ‘Toni Kroos’, 4 Menaos Lane, PORT TRAK, PA4 1FD, 078 5466 6665, 1956-05-31), (6, El Ahmadi’, 49 Kosovo Street, YEOVILSON, NG1 2HG, 077 5477 7908, 1985-09-13), (7, Gary Hooper, 53 Johnsonia Road, PITIFALL, PF15 3VB, 079 2122 5767, 1990-11-02); 4) DELETE FROM ‘GARDEN-CENTER’,’CUSTOMERS’ WHERE ‘Customer’,’Customer_ID’=1 UPDATE `GARDEN-CENTER`.`Customers` SET `Address` = 7 Windsor Road, CHESEFIELD, FH1 9LA WHERE `Customers`.`Customer_ID` = 1; 5) CREATE USER Staff@localhost IDENTIFIED BY GardenCenter; GRANT INSERT, DELETE, GRANT OPTION ON PLANTS TO USER STAFF 6) CREATE USER Staff2@localhost IDENTIFIED BY GardenCenter; GRANT INSERT ON Customers TO USER STAFF2 7) REVOKE INSERT ON PLANTS FROM user STAFF