DATABASE C170_Performance_Assessment 2020 - Western Governors University | DATABASE C 170_Performance_Assessment 2020

DATABASE C170_Performance_Assessment 2020 - Western Governors University SALES ORDER: Date, Donut Order ID, Customer ID, Customer, First Name, Last Name, Street Address, Apt #, City, St, Zip Code, Home Phone, Mobile Ph ...

DATABASE C170_Performance_Assessment 2020 - Western Governors University SALES ORDER: Date, Donut Order ID, Customer ID, Customer, First Name, Last Name, Street Address, Apt #, City, St, Zip Code, Home Phone, Mobile Phone, Other Phone, Qty, Donut ID, Name, Description, Unit Price, Special Handling Notes There is only one customer for every sales order, so the customer data on the sales order is not a repeating group. Repeating customer data for a given customer is repeated, but that is addressed in third normal form. Since there is only one customer per invoice, this isn’t addressed in first normal form. To move to first normal form, I created a relation that uses a composite key then enter new sales order info for each customer in a new row. For the sales order, the Customer ID and the Donut Order ID attributes become the composite primary key. 1NF SALES ORDER: Customer ID (PK), Date, First Name, Last Name, Street Address, Apt #, City, State, Zip, Home Phone, Mobile Phone, Other Phone, Special Handling Notes, Donut Order ID (PK), Qty, Donut ID, Name, Description, Unit Price Looking at the Sales Order relation, the second normal form violations are that Description, Name, and Unit Price depend on the Donut Order ID instead of Customer ID and Donut Order ID. Date, Customer, First Name, Last Name, Street Address, Apt #, City, State, Home Phone, Mobile Phone, Other Phone, and Special Handling Notes all depend on Customer ID instead of Customer ID and Donut Order ID. I placed the attributes that are partially dependent into separate relations where they depend on the entire key and not part of the key. 2NF SALES ORDER: Customer ID (PK), Date, First Name, Last Name, Street Address, Apt #, City, State, Zip, Home Phone, Mobile Phone, Other Phone, Special Handling Notes SALES ORDER LINE Customer ID (PK)(FK), Donut Order ID (PK)(FK), Qty ITEM: DONUT: Donut Order ID (PK), Donut ID, Name, Description, Unit Price To translate this into 3rd normal form, I had to move all of the transitively dependent attributes to a relation where they depend only on the primary key. Customer is dependent on Customer ID as well as all of the customer attributes. I moved the transitively dependent attributes to a relation where they depend only on the primary key but left the attribute on which they depend in the original relation as a foreign key. 3NF SALES ORDER: Customer ID (PK), Date, Special Handling Notes, Donut Order ID (FK) SALES ORDER LINE Customer ID (PK)(FK), Donut Order ID (PK)(FK), Qty ITEM: DONUT: Donut Order ID (PK), Donut ID, Name, Description, Unit Price CUSTOMER: Customer ID (PK), First Name, Last Name, Street Address, Apt #, City, State, Zip, Home Phone, Mobile Phone, Other Phone1NF SALES ORDER Customer ID (PK) Donut Order ID (PK) Date First Name Last Name Street Address Apt City State Zip Home Phone Mobile Phone Other Phone Special Handling Notes Qty Donut ID Name Description Unit Price2NF - - - - - - - - - - - - - - - - - - - - - - - - - - - - - NSERT INTO DONUT (DONUT_ORDER_ID,DONUT_ID,DONUT_NAME,DONUT_DESCRIPTION,UNIT_PRI CE) VALUES(1,3,'Cinammon','Cinammon Donut','1.75'); INSERT INTO SALES_ORDER (CUSTOMER_ID,DATE,SPECIAL_HANDLING_NOTES,DONUT_ORDER_ID) VALUES(1,'2018­02­18','Plates and Napkins please',1); INSERT INTO SALES_ORDER_LINE_ITEM (CUSTOMER_ID,DONUT_ORDER_ID,QTY) VALUES(1,1,10); SELECT * FROM CUSTOMER_INFO; SELECT * FROM DONUT; SELECT * FROM SALES_ORDER; SELECT * FROM SALES_ORDER_LINE_ITEM;SELECT * FROM DONUT, CUSTOMER, SALES_ORDER, SALES_ORDER_LINE_ITEM WHERE CUSTOMER.CUSTOMER_ID = SALES_ORDER.CUSTOMER_ID AND SALES_ORDER.DONUT_ORDER_ID = SALES_ORDER_LINE_ITEM.DONUT_ORDER_ID AND SALES_ORDER_LINE_ITEM.DONUT_ORDER_ID = DONUT.DONUT_ORDER_ID;