Download Database Normalization and E-R Diagram for Donut Orders and more Exams Database Programming in PDF only on Docsity!
Donut ID (PK) Donut Order ID (PK) Customer ID Order Date First Name Last Name Street Address Apt # City State Zip Code Home Phone Mobile Phone Other Phone Quantity Name Description Unit Price Notes Section 1 First Normal Form I gathered all the data from the sales invoice and created an unnormalized dataset. I determined the primary key that provided me with a unique value for all my records in this table. I used composite primary keys because both keys would be able to uniquely identify all the data in the table. Finally, I removed all the repeating groups and made the address atomic by creating separate fields for each one.
Second Normal Form There is a multivalued key, so I looked for partial dependencies on part of this key. I had a partial dependency on Donut Order ID. I appropriated everything that was dependent on the Donut Order ID and generated a second table with them. I did the same for Donut ID and separated the rest into the Quantity table to provide a link between the two. Donut Order Quantity Donut Donut Order (PK) Customer ID Last Name First Name Street Address Apt # City State Zip Code Home Phone Mobile Phone Other Phone Notes Order Date Donut Order (PK)(FK) Donut ID (PK)(FK) Quantity Donut ID (PK) Name Description Unit Price
Section 2 Entity-Relationship (E-R) Diagram The entities I chose are from the tables created in part A-1(c). The “Customer” and “Donut” tables are entities that each have a single primary key and related attributes that describe them. The “Donut Order” and “Quantity” tables are associative entities that represents the shared relationship between “Customer” and “Donut”. The “Quantity” table has a multi-attribute primary key, “donutOrder_id” and “donut_id”, which also acts as foreign keys. The relationship between the “Customer” table to “DonutOrder” is 1:M meaning that one customer can place multiple orders and multiple orders can be tied to one customer. The “Quantity” table provides a link between the “DonutOrder” table and the “Donut” table. The relationship between the “DonutOrder” table and “Quantity” is also a 1:M relationship. The same goes for the “Donut” table and “Quantity”. This mean that a donut order can many different quantity of donut types.
Section 3 Creating Tables Code
Index Code
Populate All Code
