Computer Science > EXAM > DATABASE C170_Performance_Assessment 2_2020 - Western Governors University | DATABASE C 170_Performa (All)

DATABASE C170_Performance_Assessment 2_2020 - Western Governors University | DATABASE C 170_Performance_Assessment 2_2020

Document Content and Description Below

DATABASE C170_Performance_Assessment 2_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 To move to first normal form all repeating groups were removed. Donut Order ID and Donut ID are Composite Primary Keys. 1NF SALES ORDER: Customer ID, 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 (PK), Name, Description, Unit Price To move to second normal form the table needed to be split into three tables and partial dependencies were moved into separate relations. Sales Order has the Primary Key of Donut Order ID which holds the customer’s info and details of each order. Sales Order Line Item has two foreign keys to create the relationship to Donut and Sales Order tables which also act as the Composite Primary Keys. The Donut table has all of the information of donuts that is needed for a sale. 2NF SALES ORDER: Donut Order ID(PK), Customer ID, 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 ID (PK), Name, Description, Unit Price To move to third normal form all transitive dependencies need to be removed. To do this I added the Customer table since Last Name was was dependent on Donut Order ID. I moved it to the Customer table where it depends on only the Primary Key. 3NF SALES ORDER: Donut Order ID (PK), Customer ID (FK), Date, Special Handling Notes SALES ORDER LINE Donut ID (PK)(FK), Donut Order ID (PK)(FK), Qty ITEM: DONUT: Donut ID (PK), 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 Donut 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 Customer ID Name Description Unit Price2N - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - -- INSERT INTO DONUT (DONUT_ID,NAME,DESCRIPTION,UNIT_PRICE) VALUES(3,'Cinammon','Cinammon Donut','1.75'); INSERT INTO SALES_ORDER (DONUT_ORDER_ID, CUSTOMER_ID,DATE,SPECIAL_HANDLING_NOTES) VALUES(1,1,'20180218','Plates and Napkins please');INSERT INTO SALES_ORDER_LINE_ITEM (DONUT_ID,DONUT_ORDER_ID,QTY) VALUES(3,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 DONUT.DONUT_ID = SALES_ORDER_LINE_ITEM.DONUT_ID; [Show More]

Last updated: 2 years ago

Preview 1 out of 11 pages

Buy Now

Instant download

We Accept: