WGU c170 SQL COMMANDS ALREADY PASSED

Document Content and Description Below

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 Notes 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 Third 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 Phone 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 h [Show More]

Last updated: 1 year ago

Preview 1 out of 13 pages