Question

Customer Order Example

ORDER NO: 61384                                                            ORDER DATE: 9/24/2014

CUSTOMER NO: 1273

CUSTOMER NAME: CONTEMPORARY DESIGNS

CUSTOMER ADDRESS: 123 OAK ST.

CITY STATE ZIP: AUSTIN, TX 28384

PRODUCT                                        QUANTITY    UNIT                          EXTENDED

NO                DESCRIPTION         ORDERED    PRICE                        PRICE

M128              BOOKCASE                4                   200.00                        800.00

B381               CABINET                      2                   150.00                        300.00           

R210              TABLE                          1                   500.00                        500.00

                                                                                    TOTAL                  1600.00

Normalize this user view. Make sure to show your work for each view – you should have 4 answers (e.g. Unnormalized, First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF). It is possible that some tables will be in 3NF without any changes to their 2NF status. You may just note that in your response. Also make sure to follow good DBDL protocol by capitalizing the relation name, putting attributes in parenthesis and underlining primary keys.

.

Answer 1

Given customer order is already in the unnormalized form.Below is the normalization process.

First Normal Form(1NF) :

Given is the table int the first normal from (1NF) .

OrderNo

OrderDate

CustomerNo

CustomerName

CustomerAddress

City

State

Zip

ProductNo

Description

QuantityOrdered

UnitPrice

ExtendedPrice

61384

9/24/2014

1273

Contemporary Designs

123 Oak ST

Austin

TX

28384

M128

Bookcase

4

200.00

800.00

61384

9/24/2014

1273

Contemporary Designs

123 Oak ST

Austin

TX

28384

B381

Cabinet

2

150.00

300.00

61384

9/24/2014

1273

Contemporary Designs

123 Oak ST

Austin

TX

28384

R210

Table

1

500.00

500.00

Second Normal Form (2NF) :

• Given customerOrder table contains partial dependancies.
• This table need to normalize into 2NF to remove partial dependancies.
• Need to create new tables like
  • Customer :This table stores customer details
  • Product :This table stores product details

Below are tables in 2NF.

1.Table Name :Customer

Schema :Customer (CustomerNo,CustomerName,CustomerAddress,City,State,Zip)

FD :CustomerNo==>CustomerName,CustomerAddress,City,State,Zip

Below is the table data.

CustomerNo	CustomerName	CustomerAddress	City	State	Zip
1273	Contemporary Designs	123 Oak ST	Austin	TX	28384

2.Table Name :Product

Schema :Product(ProductNo,Description,QuantityOrdered,UnitPrice,ExtendedPrice)

FD :ProductNo==>Description,QuantityOrdered,UnitPrice,ExtendedPrice

Below is the table data.

ProductNo	Description	QuantityOrdered	UnitPrice	ExtendedPrice
M128	Bookcase	4	200.00	800.00
B381	Cabinet	2	150.00	300.00
R210	Table	1	500.00	500.00

Third Normal Form (3NF) :

• Above table needs to normalize into 3NF to remove transitive dependancies.

Below are tables in 3NF.

1.Table Name :Customer

Schema :Customer (CustomerNo,CustomerName,CustomerAddress,City,State,Zip)

FD :CustomerNo==>CustomerName,CustomerAddress,City,State,Zip

Below is the table data.

CustomerNo	CustomerName	CustomerAddress	City	State	Zip
1273	Contemporary Designs	123 Oak ST	Austin	TX	28384

2.Table Name :Product

Schema :Product(ProductNo,Description,QuantityOrdered,UnitPrice,ExtendedPrice)

FD :ProductNo==>Description,QuantityOrdered,UnitPrice,ExtendedPrice

Below is the table data.

ProductNo	Description	QuantityOrdered	UnitPrice	ExtendedPrice
M128	Bookcase	4	200.00	800.00
B381	Cabinet	2	150.00	300.00
R210	Table	1	500.00	500.00

3.Table Name :Order

Schema :Order(OrderNo,OrderDate,CustomerNo)

FD :OrderNo,CustomerNo==>OrderDate

Below is the table data.

OrderNo	OrderDate	CustomerNo
61384	9/24/2014	1273

4.Table Name :OrderDetails

Schema :OrderDetails(OrderNo,ProductNo)

NOTE :This table is craeted because a single order contains multiple tables.

Below is the table data.

OrderNo	ProductNo
61384	M128
61384	B381
61384	R210