Database System Development Exam

Database System Development ExamAdam War aforesaid(prenominal)Course FDSc ComputingModule Title Database SystemsAssignment Title Database Systems AssessmentIntroductionThe trade union movement for this assignment is to develop a selective informationbase for a local anesthetic garden centre.To understand the system better and what way it should operate it was a good idea to look back other examples of databases online. From these reviewed examples, was able to gather a rough understanding of what the database should withh nonagenarian.AnalysisThe following tables were chosen for this databaseCustomers- Customers table is used to hold either the vital randomness about a customer, such as full name, address, email and feedback/reviews.Delivery- Delivery table is used to withhold only the necessary information inevitable such as shipping attend, order information, arrival date and delivery company.Plants- Plants table is used to set up all the information about plants such as pop ular 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 natural price.The following tables (including attributes) were required as a minimumCustomers table generous Name encompassEmailFeedbackDeliveries tableOrder informationShipping dateArrival dateDelivery companyPlant tableGardenPlant caseLatin namePopular nameColour of leafingColour of flowersFlowering periodHeightSpread emblem of plantFull description and moreOrders tableOrder dateDelivery informationPlants quantitiesPlant pricesThe database testament let staff/admins assume a range of rights over the system and volition be able to do things such as, turn off 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, view as orders, check available plants and choose plants acc ording 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 pass 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 tiny features in which they are interested in virtually.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 customers order and shipping details and allocate them in the order table.Customers will be able track their orders and identify the estimate go off/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 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 wou ld 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 show the t declare, 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.3NFValues 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 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-garden2) gain elude IF not EXISTS Customers (Customer_ID int(11) non NULL AUTO_INCREMENT,Full Name varchar(25) non NULL, cut across varchar(255) NOT NULL,Email va rchar(50) NOT NULL,Feedback varchar(255) DEFAULT NULL, first KEY (Customer_ID)) ENGINE=InnoDB DEFAULT CHAR mess=latin1 AUTO_INCREMENT=13 data for table Customers3) get in INTO Customers (Customer_ID, Full Name, Address, Email, Feedback/Reviews) determine(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, twee 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, uncreated 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) set(1, 2011-04-16 150000, Order_ID 1, 2011-04-22 120000, Royal mail. 1st class),(2, 2012-10-21 140000, Order Number 2, 2012-11-07 140000, Ups),(3, 2012-01-11 110000, Order Number 2, 2012-01-22 130000, Royal mail. 2nd 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 information text NOT NULL,Plant quantities varchar(255) NOT NULL, impairment 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,Ginger 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) Table 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,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 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 cheer, 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 solarise 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 purity 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, juiceless 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 varied 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 covert 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 blossom forth 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, lightsome 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 smiling 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 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, 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 end-to-end 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, Mediter ranean, 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 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 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, YEOVILSO N, 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 GRANT 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