Database System Development Exam

Database System Development ExamAdam War aforementioned(prenominal)Course FDSc calculationModule Title Database SystemsAssignment Title Database Systems AssessmentIntroductionThe projection for this assignment is to develop a database for a local anaesthetic garden centre.To consider the placement better and what way it should operate it was a good report to review other examples of databases online. From these reviewed examples, was able to gather a rough spirit of what the database should withh honest-to-goodness.AnalysisThe following gameboards were chosen for this databaseCustomers- Customers table is used to hold in in on the whole(prenominal) the vital instruction about a customer, such(prenominal) as upright name, address, email and feedback/reviews. words- saving table is used to withhold all the necessary information indispensablenessed such as shipping date, aim information, arrival date and delivery company. arranges- Plants table is used to store all the information about adjusts such as habitual name, type, features, description etceteraOrders- Orders table is used to hold all the information needed such as magnitude date, delivery information, lay quantities and total price.The following tables (including attributes) were indispensable as a minimumCustomers table undecomposed recognise compensateEmailFeedbackDeliveries tableOrder informationShipping date reaching dateDelivery companyPlant tableGardenPlant caseLatin namePopular nameColour of leafColour of efflorescencesskin rash periodHeightSpread sign of whole kit and caboodle in force(p) description and to a greater extentOrders tableOrder dateDelivery informationPlants quantitiesPlant pricesThe 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 rambles and check delivery information.Customers will too be able to do a ran ge of things such as, register, scram orders, check available plants and choose plants according to its type, attributes or position.Basic commands wish 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 feces mechanically assume that it will be constantly in development two in expanding and editing form which ar the simplest of objectives for this type of database.A escort option is implemented to rose-cheekeduce and in due course continue data loss. Simple password protection will also forethought 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 generate 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/ drug users to shape order s 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 around.When they in the long run 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 customers 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 ModelNormalisation1NF1NF is the most rudimentary 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 holds for 1NF.The other rule is that all tables need a primary pigment, or else that would also go against the rules.2NFOne 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 envision 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) therefrom completing the need for relations in 2NF.3NFValues that have the probable to be changed, in a 3NF database, need to be within a table, as a single order puritythorn alter the melody levels by creating an order. If not removed, the normalisation rules cannot be met.Within a 3NF system there shouldnt 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 database1)Database 199209-ga rden2)CREATE display panel IF non EXISTS Customers (Customer_ID int(11) not useless AUTO_INCREMENT,Full Name varchar(25) non NULL, care varchar(255) non NULL,Email varchar(50) NOT NULL,Feedback varchar(255) DEFAULT NULL, particular get a line (Customer_ID)) locomotive=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 data for table Customers3) interpose INTO Customers (Customer_ID, Full Name, name and address, Email, Feedback/Reviews) set(1, Jo Millan, 7 Windsor Road, CHESEFIELD, FH1 9LA, emailprotected, NULL),(2, Harry Hugo, 4 Colywn quest 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 disconcert IF NOT EXISTS Delivery (Delivery_ID int(11) NOT NULL AUTO_INCREMENT,Shipping determine datetime NOT NULL,Delivery Information varchar(255) NOT NULL,Arrival Date datetime NOT NULL,Delivery Company varchar(255) NOT NULL, native KEY (Delivery_ID)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 data for table DeliveryINSERT INTO Delivery (Delivery_ID, Shipping Date, Delivery Information, Arrival Date, Delivery Company) determine(1, 2011-04-16 150000, Order_ID 1, 2011-04-22 120000, Royal mail. 1st class),(2, 2012-10-21 140000, Order morsel 2, 2012-11-07 140000, Ups),(3, 2012-01-11 110000, Order Number 2, 2012-01-22 130000, Royal mail. second class),(4, 2013-11-09 130000, Order Number 3, 2013-11-30 120000, Ups))CREATE TABLE IF NOT EXISTS Orders (Order_ID int(11) NOT NULL AUTO_INCREMENT,Order date datetime NOT NULL,Order inf ormation 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 OrdersINSERT INTO Orders (Order_ID, Order date, Order information, Plant quantities, Price) VALUES(1, 2013-01-10 000000, genus Rosa Falstaff, Balkan cranesbill., 1, 21.98),(2, 2012-12-28 130000, Abyssinian gladiolus, spice up lily,Striped bloody cranesbill, 1, 31.89),(3, 2013-01-07 000000, Skyscraper lily, 2, 7.98),(4, 2013-01-07 180000, Striped bloody cranesbill, 10, 79.90) display board structure for table PlantsCREATE 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, dulcet/Unscented varchar(255) NOT NULL,Groundcover varchar(255) NOT NULL,Sun Level varchar(255) NOT NULL, daub Type varchar(255) NO T NULL,Orientation varchar(255) NOT NULL,Special conditions varchar(255) NOT NULL, positioning 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 PlantsINSERT 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 stovepipe crimson/purple roses bred to date. The large, cupped, double flowers have a powerful old rose fragrance. The rich, somber crimson flowers eventually turn to a beautiful rich purple colour., 13.99),(2, Narcissus , Canaliculatus, Bulbs, White petals with a yellow cu p, Low maintenance, Spring, Scented, Yes, Full sun or fond(p) shade, Well-drained soil, South, No special conditions, Next to an entrance, windowboxes, search 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, imperial pink flowers are held on slender stems above strong aromatic, kindling 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 qu ickly 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 privateness 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, shopping centre 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 slam dance flowers that will open in the evening and fill the air with their incredible, decided 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, pass 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 noble-minded for training over a rejoicing 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, genus Lilium Bonbini, Bulbs, Cream, Non poisonous, July, Scented, No, Full sun with light cloud shade at its base, Fertile, reliably moist soil, South, No special conditions, warmness 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 shining 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, flag 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, await 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 u pward-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 cater (Staff_ID int(11) NOT NULL AUTO_INCREMENT,Full Name varchar(255) NOT NULL,Address varchar(255) NOT NULL,Phone anatomy varchar(255) NOT NULL,DOB date NOT NULL,PRIMARY KEY (Staff_ID)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 data for table StaffINSERT 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 S treet, 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=1UPDATE GARDEN-CENTER.Customers SET Address = 7 Windsor Road, CHESEFIELD, FH1 9LA WHERE Customers.Customer_ID = 15)CREATE user Stafflocalhost IDENTIFIED BY GardenCenter let INSERT, DELETE, GRANT OPTION ON PLANTSTO USER STAFF6)CREATE USER Staff2localhost IDENTIFIED BY GardenCenter GRANT INSERT ON Customers TO USER STAFF27) REVOKE INSERT ON PLANTSFROM user STAFF