Data Management Applications C170, project with complete solution

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 us ...

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. 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 NotesSecond 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 PriceThird Normal Form Lastly, I removed transitive dependencies by creating the Customer table. There are multiple types of attributes that are associated with the Customer ID. Customer ID will be the primary key for this table making it a foreign key in the Donut Order table. Donut Order Quantity Donut Customer Donut Order ID (PK) Customer ID (FK) Notes Order Date Donut Order (PK)(FK) Donut ID (PK)(FK) Quantity Donut ID (PK) Name Description Unit Price Customer ID (PK) First Name Last Name Street Address Apt # City State Zip Code Home Phone Mobile Phone Other PhoneSection 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 CodeView CodeIndex Code