WGU C170 Data Management - Applications Performance Assessment.

Sales order 1NF WGU C170 Data Management - Applications Performance Assessment. EXPLANATION: I am going to call this table the "Order Table" I also need to make sure that this table contains a primary Key, in this particular table I am going to make the “OrderID” and “DonutID” the Primary Key (composite key), the reason I chose to use OrderID and DonutID as the primary key is because Order ID or DonutID by themselves do not guarantee Uniqueness but when combined uniqueness is achieved. I am going to separate city, state and zip in order to make the data atomic with no repeating groups. I am also going to get rid of Customer since there is already a Customer ID field and customer will most likely be a table of its own. I left the Donut name and description empty because my table was starting to be too big to fit it in this document. Orde r ID Custome r ID (PK) OrderDat e First Nam e Last Nam e Street addres s Apt # City State Zip Home phone Mobile phone Other phone Donu t ID Qt y Donu t Nam e Descriptio n Unit price Note s 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 1 1 $1.5 None n s 5 1 9 9 0 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 2 5 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 3 12 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 4 3 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 5 4 $1.7 None n s 5 9 9 5 1 2 9/1/17 John Perez 12th ST #4 Austi Texa 1234 6 5 $2.0 None n s 5 9 9 Sales Order 2NF EXPLANATION In Second Form I need to have fulfilled 1NF and ensure that No functional dependencies exist, in order to accomplish that I need to Separate Donut ID and Order ID and create their own tables and since Quantity can cause a many to many relationship To resolve that issue I will create the OrderLineItem table in which OrderID and DonutID will be the PK, however since OrderID and Donut ID are the primary keys for their own respective tables they are also Foreign keys in the OrderLineItem table and therefore reference their own table as FK in orderlineitem table . In the Order Table I have chosen to use Order ID as the Primary Key since the Order table most likely will contain order related information like customer ID Order ID the Date and the notes. Orders Table Order ID (PK) Custo mer ID OrderDate First Name Last Name Street address Apt# City State Zip Home phone Mobile phone Other phone Notes 1 2 9/1/17 John Perez 12th ST #4 Austin Texas 12345 1 None Donut Table Donut ID (PK) Name Description Unit Price 1 Plain Plain Donut $1.50 2 Glazed Glazed Donut $1.75 3 Cinnamon Cinnamon Donut $1.75 4 Chocolate Chocolate $1.75 5 Sprinkle Sprinkle Donut $1.75 6 Gluten-Free Gluten-Free Donut $2.0 Order Line Item Table Order ID (PK) (FK) Donut ID (PK) (FK) Quantity 1 1 1 1 2 5 1 3 12 1 4 3 1 5 4 1 6 5 Sales Order 3NF EXPLANATION: There was no need to have all the customer information as it is redundant so I removed it. I also created two more tables one to house the information that does not need to be in Order table like Donut ID, Quantity, Donut name, Description or unit prize that information needs to go in to separate tables. The quantity of each line item is defined with the combination of the Order ID and the Donut ID, so that information was separated into the Order Line Item table. The Name, Description, and Unit Price of each donut can be defined by a Donut ID, so that was separated into a table to reduce redundancy. A customer will most likely have more than one order if we were to leave the customer ID in the Order table the customer information will be repeated for each of their orders. To avoid this the customer table was created that way the customer information is only added once. In Order Table the PK is OrderID and the FK is CustomerID. In Order Line Item table the OrderID and DonutID are the PK and also FKs. In Donut table the DonutID is the PK and in the customer table the CustomerID is the PK. Order Table Order ID (PK) Custo merID (FK) OrderDate Notes 1011 0001 9/10/17 None Order Line Item Table Order ID (PK) (FK) Donut ID (PK) (FK) Quantity 1 1 1 1 2 5 1 3 12 1 4 3 1 5 4 1 6 5