Needs the following;
- An Entity Relationship diagram representing the database design showing all necessary entities and their relationships with all tables normalized in third normal form. This can be scanned, drawn in a drawing application (any Microsoft product, i.e. Paint, Visio, Word, etc.), scanned image of a hand-drawn diagram in pdf, bmp, or jpg format or a digital picture of your hand-drawn diagram in jpg or bmp.
- Data definition (DDL) statements to create your tables
- Data manipulation (DML) statements to populate your tables
- Two examples of SQL statements to retrieve data from related tables
CS 2500 Elementary School Candy Sales Database Design Problem The following table is an attempt to track the sale of candy by elementary school students. The sale is to raise money for a school trip. Each class could have several students attempting to sell candy they have received. Each student is given several units of candy (QtyR), and the amount of each that they have sold is recorded (Qtys). The system is to track the running total of candy received and sold. Individual allocations and sales of candy do not need to be recorded. Note that the sample data within the table shown below is an un-normalized, flat-file system. QtyR3 Qty S3 Student Class Candy1 Smith, Joe 5th-A Almond Chocolate Blaha, Mike 5th-A Milk Chocolate Edwards, Linda 5th-A Almond Chocolate Parks, Ann 5th-B Crisp Chocolate Frankin, AI 5th-B Almond Chocolate Bell, Carol 6th-A Milk Chocolate QtyR1 Qtys1 Candy2 5 Crisp Chocolate 3 Crisp Chocolate 4 Crisp Chocolate 3 Sour Worms 5 Milk Chocolate wooo QtyR2Qtys2Candy3 5 5 Sour Worms 8 6 5 Sour Worms 4 Crisp Chocolate Supplier Supplier Number Name SUPPLIER Supplier Address Sales Rep Product Product Number Name SUPPLIES Product Category Unit Price Cruise Number CRUISE Start Date End Cruise Ship Date Director Number Supplier Product Cruise Number Number Number SUPPLIED Date Quantity Cost Ship Ship Number Name SHIP Ship Launch Gross Builder Date Weight Port Name Country PORT Number Port of Docks Manager Cruise Port Number Name CALLED ON Arrival Departure Date Date Country Passenger Passenger Number Name PASSENGER Home Telephone Address Number Group Contast Passenger Cruise Cabin Number Number Number SAILED Fare Tour Tour Number Name Duration TOUR Port Price Name County Cruise Passenger Number Number TOOK TOUR Tour Number Date CS 2500 Elementary School Candy Sales Database Design Problem The following table is an attempt to track the sale of candy by elementary school students. The sale is to raise money for a school trip. Each class could have several students attempting to sell candy they have received. Each student is given several units of candy (QtyR), and the amount of each that they have sold is recorded (Qtys). The system is to track the running total of candy received and sold. Individual allocations and sales of candy do not need to be recorded. Note that the sample data within the table shown below is an un-normalized, flat-file system. QtyR3 Qty S3 Student Class Candy1 Smith, Joe 5th-A Almond Chocolate Blaha, Mike 5th-A Milk Chocolate Edwards, Linda 5th-A Almond Chocolate Parks, Ann 5th-B Crisp Chocolate Frankin, AI 5th-B Almond Chocolate Bell, Carol 6th-A Milk Chocolate QtyR1 Qtys1 Candy2 5 Crisp Chocolate 3 Crisp Chocolate 4 Crisp Chocolate 3 Sour Worms 5 Milk Chocolate wooo QtyR2Qtys2Candy3 5 5 Sour Worms 8 6 5 Sour Worms 4 Crisp Chocolate Supplier Supplier Number Name SUPPLIER Supplier Address Sales Rep Product Product Number Name SUPPLIES Product Category Unit Price Cruise Number CRUISE Start Date End Cruise Ship Date Director Number Supplier Product Cruise Number Number Number SUPPLIED Date Quantity Cost Ship Ship Number Name SHIP Ship Launch Gross Builder Date Weight Port Name Country PORT Number Port of Docks Manager Cruise Port Number Name CALLED ON Arrival Departure Date Date Country Passenger Passenger Number Name PASSENGER Home Telephone Address Number Group Contast Passenger Cruise Cabin Number Number Number SAILED Fare Tour Tour Number Name Duration TOUR Port Price Name County Cruise Passenger Number Number TOOK TOUR Tour Number Date
