I need to had this definition done first and then do the project. I am struggling on the project because I may have had the definitions wrong. I am an attachment to the chart for the definitions and the project.

1.) All relations

2.) All attributes in each relation.

3.) The candidate, primary and foreign keys for each relation:

4.) The type of relationship between each relation (i.e. one to one, one to many, many to many)

5.) Intersection data, if any, between any many to many relationships:

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.


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 1 Parks, Ann 5th-B Crisp Chocolate 3 3 Sour Worms 11 4 Frankin, Al 5th-B Almond Chocolate 5 5 Milk Chocolate 4 4 Crisp Chocolate 5 5 Bell, Carol 6th-A Milk Chocolate 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