An Entity Relationship diagram representing the database design showing all necessary entities and their relationships

2. Conversion of E-R diagram into table structures with appropriate fields, primary and foreign keys and field types. This will be turned in as data definition statements using data definition language (DDL). 

3. Data manipulation statements using data manipulation language (DML) to populate tables with data from the case study

4. All tables normalized in third normal form

5. Two examples of SQL statements to retrieve data from related tables

 

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. Student Class Candy1 QtyR1 QtyS1 Candy2 QtyR2 QtyS2 Candy3 QtyR3 QtyS3 Smith, Joe 5th-A Almond Chocolate 5 5 Crisp Chocolate 5 5 Sour Worms 4 2 Blaha, Mike 5th-A Milk Chocolate 6 3 Crisp Chocolate 8 6 Edwards, Linda 5th-A Almond Chocolate 5 4 Crisp Chocolate 5 5 Sour Worms 4 Parks, Ann 5th-B Crisp Chocolate 3 3 Sour Worms 11 4 Frankin, Al Bell, Carol 5th-B Almond Chocolate 6th-A Milk Chocolate 5 5 Milk Chocolate 4 4 Crisp Chocolate 5 5 14 7 Final Project will consist of the following: 1. An Entity Relationship diagram representing the database design showing all necessary entities and their relationships 2. Conversion of E-R diagram into table structures with appropriate fields, primary and foreign keys and field types. This will be turned in as data definition statements using data definition language (DDL) 3. Data manipulation statements using data manipulation language (DML) to populate tables with data from the case study. 4. All tables normalized in third normal form 5. Two examples of SQL statements to retrieve data from related tables