Question

A picking list is a list of the parts needed to assemble a product, in this case a front bicycle wheel.

Hint: Use Item Number as your key for UNF.    

ITEM NUMBER:   W150

ITEM DESCRIPTION:   FRONT WHEEL ASSEMBLE A

PART #          DESCRIPTION                     QTY   

M300              CHAMPION RIM                     1

U600              SPECIALIZED HUB               1

S101               CHAMPION SPOKES         36

T003               SPEC TOURING TIRE           1

E102               FUJI TUBE                            1

Normalize this user view. Make sure to show your work for each view – so 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

Below is the normalization process.

Unnormalized table:Below table is unnormalized

ITEMNUMBER	ITEMDESCRIPTION	PART#	DESCRIPTION	QTY
W150	FRONT WHEEL ASSEMBLE A	M300	CHAMPION RIM	1
U600	SPECIALIZED HUB	1
S101	CHAMPION SPOKES	36
T003	SPEC TOURING TIRE	1
E102	FUJI TUBE	1

First Normal Form(1NF) :

• Given table is unnormalized table.Because single item has the multiple parts
• hence above table needs to normalize into 1NF
• Below is the table in the first normal form

ITEMNUMBER	ITEMDESCRIPTION	PART#	DESCRIPTION	QTY
W150	Fron Wheel Assemble A	M300	CHAMPION RIM	1
W150	Fron Wheel Assemble A	U600	SPECIALIZED HUB	1
W150	Fron Wheel Assemble A	S101	CHAMPION SPOKES	36
W150	Fron Wheel Assemble A	T003	SPEC TOURING TIRE	1
W150	Fron Wheel Assemble A	E102	FUJI TUBE	1

Second Normal Form (2NF) :

• Above table need to normalize into 2NF to remove partial dependancy.
• In second normal form new tables are formed like
  • Item :This table store item details like ItemNumber and ItemDescription
  • Part :This table stores part details like part# , Description and QTY

1.Table Name :ITEM

Schema :ITEM (ItemNumber,ItemDescription)

FD :ItemNumber==>ItemDescription

Below is the table data

ITEMNUMBER	ITEMDESCRIPTION
W150	Fron Wheel Assemble A

2.Table Name :PART

Schema :PART (part#,Description,Qty)

FD :part#==>Description,Qty

Below is the table data

PART#	DESCRIPTION	QTY
M300	CHAMPION RIM	1
U600	SPECIALIZED HUB	1
S101	CHAMPION SPOKES	36
T003	SPEC TOURING TIRE	1
E102	FUJI TUBE	1

Third Normal Form (3NF) :

• Above table needs to normalize into 3NF to remove transitive dependancy.
• Below are tables in 3NF

1.Table Name :ITEM

Schema :ITEM (ItemNumber,ItemDescription)

FD :ItemNumber==>ItemDescription

Below is the table data

ITEMNUMBER	ITEMDESCRIPTION
W150	Fron Wheel Assemble A

2.Table Name :PART

Schema :PART (part#,Description,Qty)

FD :part#==>Description,Qty

Below is the table data

PART#	DESCRIPTION	QTY
M300	CHAMPION RIM	1
U600	SPECIALIZED HUB	1
S101	CHAMPION SPOKES	36
T003	SPEC TOURING TIRE	1
E102	FUJI TUBE	1

3.Table Name :ITEMPART

Schema :ITEMPART (itemNumber,part#)

Below is the table data

ITEMNUMBER	PART#
W150	M300
W150	U600
W150	S101
W150	T003
W150	E102