AIS CHAPTERS
Chapter 3
Data Modeling
3-23.
Swan’s Supplies (Normalizing Data)
The raw data is as follows:
Purchase
Order
Number
12345
12346
Customer
Customer Customer
Phone
Item
Item
Date
Number
Name
Number
Number Description
Charles
01/03/12 123-8209 Dresser, (752) 433-8733 X32655 Baseballs
Inc.
X34598 Footballs
Basketball
Z34523
Hoops
Patrice
01/03/12 123-6733 Schmidt's (673) 784-4451 X98673 Softballs
Sports
X34598 Footballs
SoccerX67453
balls
Unit
Cost
Quantity
Unit Ordered
$33.69 dozen
20
$53.45 dozen
10
$34.95 each
20
$35.89 dozen
10
$53.45 dozen
5
$45.36 dozen
10
1. The data can be put in first normal form by repeating the purchase order information as shown below. The data are now in first normal form because they can be stored in computer records.
Purchase
Order
Number
12345
12345
12345
12346
12346
12346
Customer
Date
Number
Customer
Name
01/03/12 123-8209 Charles
Dresser, Inc.
Charles
01/03/12 123-8209
Dresser, Inc.
Charles
01/03/12 123-8209
Dresser, Inc.
123-6733 Patrice
01/03/12
Schmidt's
Sports
Patrice
01/03/12 123-6733 Schmidt's
Sports
01/03/12 123-6733 Patrice
Customer
Phone
Number
Item
Item
Unit
Number Description Cost
Quantity
Unit
Ordered
(752) 433-8733 X32655 Baseballs
$33.69 dozen
20
(752) 433-8733 X34598 Footballs
$53.45 dozen
10
$34.95 each
20
$35.89 dozen
10
(673) 784-4451 X34598 Footballs
$53.45 dozen
5
(673) 784-4451 X67453 Soccer-
$45.36 dozen
10
Basketball
Hoops
(673) 784-4451 X98673 Softballs
(752) 433-8733 Z34523
SM 3.1
Schmidt's
Sports
balls
2. To reorganize the data in part 1 into second normal form, it is necessary to split the file in two—a “customer file” and an “orders file.” The data are in second normal form because the data items in each record depend on the record’s primary key.
Customer File:
(primary key)
Data Modeling
3-23.
Swan’s Supplies (Normalizing Data)
The raw data is as follows:
Purchase
Order
Number
12345
12346
Customer
Customer Customer
Phone
Item
Item
Date
Number
Name
Number
Number Description
Charles
01/03/12 123-8209 Dresser, (752) 433-8733 X32655 Baseballs
Inc.
X34598 Footballs
Basketball
Z34523
Hoops
Patrice
01/03/12 123-6733 Schmidt's (673) 784-4451 X98673 Softballs
Sports
X34598 Footballs
SoccerX67453
balls
Unit
Cost
Quantity
Unit Ordered
$33.69 dozen
20
$53.45 dozen
10
$34.95 each
20
$35.89 dozen
10
$53.45 dozen
5
$45.36 dozen
10
1. The data can be put in first normal form by repeating the purchase order information as shown below. The data are now in first normal form because they can be stored in computer records.
Purchase
Order
Number
12345
12345
12345
12346
12346
12346
Customer
Date
Number
Customer
Name
01/03/12 123-8209 Charles
Dresser, Inc.
Charles
01/03/12 123-8209
Dresser, Inc.
Charles
01/03/12 123-8209
Dresser, Inc.
123-6733 Patrice
01/03/12
Schmidt's
Sports
Patrice
01/03/12 123-6733 Schmidt's
Sports
01/03/12 123-6733 Patrice
Customer
Phone
Number
Item
Item
Unit
Number Description Cost
Quantity
Unit
Ordered
(752) 433-8733 X32655 Baseballs
$33.69 dozen
20
(752) 433-8733 X34598 Footballs
$53.45 dozen
10
$34.95 each
20
$35.89 dozen
10
(673) 784-4451 X34598 Footballs
$53.45 dozen
5
(673) 784-4451 X67453 Soccer-
$45.36 dozen
10
Basketball
Hoops
(673) 784-4451 X98673 Softballs
(752) 433-8733 Z34523
SM 3.1
Schmidt's
Sports
balls
2. To reorganize the data in part 1 into second normal form, it is necessary to split the file in two—a “customer file” and an “orders file.” The data are in second normal form because the data items in each record depend on the record’s primary key.
Customer File:
(primary key)