Objective: Normalize table to third normal form (3NF) Lab: For this assignment,
ID: 3695992 • Letter: O
Question
Objective: Normalize table to third normal form (3NF)
Lab: For this assignment, you will normalize the below Orders table to 3NF. Make sure to identify all the entities and their attributes, especially if you make any new items (such as entity or attributes). The following is a list of possible attributes for ORDERS; you may have more:
ORDERS
OrderNo
CustomerNo
CustomerName
CustomerAddress
CustomerCity
CustomerState
CustomerZip
OrderDate
ItemsOrdered
Total
Submit dependency diagrams for all entities you create. The following table is a sample of data, before ORDERS was normalized. Please start with the below table and then write out each dependency diagram as you move from 1NF to 2NF to 3NF. Show your progress
The following table is a sample, before ORDERS was normalized:
ORDER NO
ORDER DATE
CUSTOMER NO
CUSTOMER NAME
CUSTOMER ADDRESS
ITEMS ORDERED
100
1/1/99
54545
ACME Co
1234 1st St.
1A4536, Flange, 7lbs, $75;4-OR2400, Injector, .5lbs, $108;4-OR2403, Injector, .5lbs, $116;1-4I5436, Head, 63lbs, $750
101
1/1/99
12000
Sneed Corp.
555 Main Ave.
1-3X9567, Pump, 5lbs, $62.50
102
1/1/99
66651
ZZZ & Co.
4242 SW 2nd
7-8G9200; Fan, 3lbs, $84;1-8G5437, Fan, 3lbs, $15;1-3H6250, Control, 5lbs, $32
103
1/2/99
54545
ACME Co
1234 1st St.
40-8G9200, Fan, 3lbs, $480;1-2P5523, Housing, 1lb, $165;1-3X9567, Pump, 5lbs, $42
ORDER NO
ORDER DATE
CUSTOMER NO
CUSTOMER NAME
CUSTOMER ADDRESS
ITEMS ORDERED
100
1/1/99
54545
ACME Co
1234 1st St.
1A4536, Flange, 7lbs, $75;4-OR2400, Injector, .5lbs, $108;4-OR2403, Injector, .5lbs, $116;1-4I5436, Head, 63lbs, $750
101
1/1/99
12000
Sneed Corp.
555 Main Ave.
1-3X9567, Pump, 5lbs, $62.50
102
1/1/99
66651
ZZZ & Co.
4242 SW 2nd
7-8G9200; Fan, 3lbs, $84;1-8G5437, Fan, 3lbs, $15;1-3H6250, Control, 5lbs, $32
103
1/2/99
54545
ACME Co
1234 1st St.
40-8G9200, Fan, 3lbs, $480;1-2P5523, Housing, 1lb, $165;1-3X9567, Pump, 5lbs, $42
Explanation / Answer
Underlined Column is Primary key
Dotted Underlined Column is Foreign key
1st Normal Form Dependency Diagram
2nd Normal Form
Customer Number
----------------------------
Address ID
-------------------
3rd Normal Form
Since State and city are on key attributesw which are dependent on other zip which is also non-key attribute
Order Number Customer Number Customer Name Customer Address Customer City Customer State Customer Zip Code Order Date Items OrderedRelated Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.