Question

Kick and Swing Inc. is a wholesaler of sporting goods equipment for retailers in a local metropolitan area. The company buys sporting goods equipment direct from manufacturers and then resells them to individual retail stores in the regional area. The raw data in Figure 14-20 illustrate some of the information required for the company’s purchase order system. As you can see, this information is characteristic of accounting purchase order systems but is not well organized. In fact, because of the repeating groups in the right-most columns, it cannot even be stored in a database.

Purchase Order Number	Date	Customer Number	Customer Name	Customer Phone Number	Item Number	Item Description	Unit Cost	Unit	Quantity Ordered
12345	01/03/2011	123-8209	Charles Dresser, Inc.	(752)433-8733	X32655	Baseballs	$33.69	dozen	20
					X34598	Footballs	53.45	dozen	10
					Z34523	Bball Hoops	34.95	each	20
12346	01/03/2011	123-6733	Patrice Schmidt’s	(673)784-4451	X98673	Softballs	35.89	dozen	10
					X34598	Footballs	53.45	dozen	5
			Sports		X67453	Soccer balls	45.36	dozen	10

FIGURE 14-20 Some purchasing data for Kick and Swing.

Requirements

Store this data in a spreadsheet to make it easy to manipulate. Then perform each of the following tasks in turn:

1. Reorganize the data in first normal form. Why is your data in first normal form?
2. Reorganize the data from part 1 into second normal form. Why is your data in second normal form?
3. Reorganize the data from part 2 into third normal form. Why is your data in third normal form?

Answer 1

a.1NF( all attributes should be atomic)
Purchase Order Number	Date	Customer Number	Customer Name	Customer Phone Number	Item Number	Item Description	Unit Cost	Unit	Quantity Ordered
12345	1/3/2011	123-8209	Charles Dresser, Inc.	(752)433-8733	X32655	Baseballs	$33.69	dozen	20
12345	1/3/2011	123-8209	Charles Dresser, Inc.	(752)433-8733	X34598	Footballs	$53.45	dozen	10
12345	1/3/2011	123-8209	Charles Dresser, Inc.	(752)433-8733	X34523	Bball Hoops	$34.95	each	20
12346	1/3/2011	123-6733	Patrice Schmidt's Sports	(673)784-4451	X98673	Softballs	$35.89	dozen	10
12346	1/3/2011	123-6733	Patrice Schmidt's Sports	(673)784-4451	X34589	Footballs	$53.45	dozen	5
12346	1/3/2011	123-6733	Patrice Schmidt's Sports	(673)784-4451	X67453	Soccer balls	$45.36	dozen	10
b. 2NF( All the non key attributes should be fully functionally dependent on the primary key)
Functional Dependencies :
Purchase Order Number -> Date,Customer Number
Purchase Order Number	Date	Customer Number
12345	1/3/2011	123-8209
12346	1/3/2011	123-6733
Purchase Order Number,Item Number -> Quantity Ordered
Purchase Order Number	Item Number	Quantity Ordered
12345	X32655	20
12345	X34598	10
12345	X34523	20
12346	X98673	10
12346	X34598	5
12346	X67453	10
c. 3NF( Remove transitive dependencies)
Customer Number -> Customer Name,Phone Number
Customer Number	Customer Name	Phone Number
123-8209	Charles Dresser, Inc.	(752)433-8733
123-6733	Patrice Schmidt's Sports	(673)784-4451
Item Number -> Item Description,Unit Cost,Unit
Item Number	Item Description	Unit/Cost	Unit
X32655	Baseballs	$33.69	dozen
X34598	Footballs	53.45	dozen
X34523	Bball Hoops	34.95	each
X98673	Softballs	35.89	dozen
X67453	Soccer Balls	45.36	dozen
Tables in 3NF
Purchase Order(Purchase Order Number, Date,Customer Number)
Purchase Item(Purchase Order Number,Item Number ,Quantity Ordered )
Customer(Customer Number, Customer Name,Phone Number )
Item(Item Number, Item Description,Unit Cost,Unit)
underlined are primary keys and italicized are foreign keys.