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
...
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]
 – University of the People.png)
