Given the blank order form, order form with data, and sample data, create the 3NF relations needed to
store this data in a relational database. You should follow the normalization process listed below.
Your solution should include:
– All non-aggregated data from the order form
– Each relation should have a primary key
– Include the foreign keys necessary to create the appropriate relationships
Your solution should list:
– The relational notation for all relation schemas
R(A,B,C,D,E)
– For each relation, list all functional dependencies using the notation:
FD1 (A,B) -> C,D,E
Order Form
Order Number
Order Date
________
_____________
Customer Name:_________________________________________________________
Street Address: ________________________________________
City:______________________________________________
State:______________________________________________
Zip Code:______________________________________________
Customer Phone Number: ____________________
OrderLine Product #
1
2
3
4
5
6
7
8
9
Description
Quantity
Price
Total
Subtotal
Order Form
Order Number
Order Date
__101___
_____3/17/2017_
Customer Name:____ACME, Inc.___________________________________
Street Address: ___ 123 Front St________________________
City:____________ Ellensburg___________________
State:_______WA___________________________________
Zip Code:_______98926__________________________________
Customer Phone Number: ___ 509-555-1212______
OrderLine Product #
1
1
2
2
3
4
5
6
7
8
9
Description
Widgets
Thingamajigs
Quantity
4
4
Price
20
22
Subtotal
80
88
Total
168
Sample Data
Order
Number
Order Date
Customer
Name
101
3/17/2017 ACME, Inc
102
Street
Address
City
State Zip
Ellensburg WA
98926
3/30/2017 ABC Corp
123 Front St
456 Second
St
Ellensburg WA
98926
103
4/4/2017 XYZ, LLC
5 Third St
Seattle
WA
98101
104
4/5/2017 ACME, Inc
123 Front St
Ellensburg WA
98926
Phone
509-5551212
509-5559999
206-5551111
509-5551212
Product#
Sample Data (columns to the right of the above table)
Description Quantity Price Product# Description Quantity Price Product# Description
Widgets
4
20
2 Thingmajigs
4
22
Thingmajigs
5
22
4 Cups
2
30
3 Dohickey
Cups
14
30
2 Thingmajigs
12
22
1 Widgets
Cups
2
30
5 Straws
100
2
2 Thingmajigs
Sample Data (columns to the right of the above tables)
Quantity Price Product# Description Quantity Price
2
4
4
122
20
22
5 Straws
100
2
1
2
4
4

Purchase answer to see full
attachment