Transcript Slide 1

Normalization Exercises
A337
Normalization Example 1
SOFTWARE
PACKID
TAGNUM COMPID INSTDATE
AC01
32808
M579
9/13/95
DB32
32808
M579 12/13/95
37691
B121
6/15/95
DB33
57772
C007
5/27/95
WP08
37691
B121
6/15/95
57772
C007
5/27/95
WP09
59836
B221 10/30/95
77740
M579
5/27/95
SOFTCOST EMPNUM EMPNAME
754.95
611 Dinh, Meli ssa
380.00
611 Dinh, Meli ssa
380.00
124 Alvarez, Ramon
412.77
567 Feinstei n, Betty
227.50
124 Alvarez, Ramon
170.24
567 Feinstei n, Betty
35.00
124 Alvarez, Ramon
35.00
567 Feinstei n, Betty
2
LOCATION
Accounting
Accounting
Sales
Info Systems
Sales
Info Systems
Home
Home
1st Normal Form
SOFTWARE
PACKID TAGNUMCOMPID INSTDATE SOFTCOST EMPNUM EMPNAME
AC01
32808
M579
9/12/1991
754.95
611 Dinh, Melissa
DB32
32808
M579 12/12/1991
380.00
611 Dinh, Melissa
DB32
37691
B121
6/14/1991
380.00
124 Alvarez, Ramon
DB33
57772
C007
5/26/1991
412.77
567 Feinstein, Betty
WP08
37691
B121
6/14/1991
227.50
124 Alvarez, Ramon
WP08
57772
C007
5/26/1991
170.24
567 Feinstein, Betty
WP09
59836
B221 10/29/1991
35.00
124 Alvarez, Ramon
WP09
77740
M579
5/26/1991
35.00
567 Feinstein, Betty
Primary Key
3
LOCATION
Accounting
Accounting
Sales
Info Systems
Sales
Info Systems
Home
Home
2nd Normal Form
Create 3 Tables
PACKID
TAGNUM
PACKID
TAGNUM
Distribute the remaining Attributes
COMPID
INSTDATE
SOFTCOST
EMPNUM
EMPNAME
LOCATION
2nd Normal Form
PACKID
PACKID
TAGNUM
TAGNUM
INSTDATE, SOFTCOST
(none in this example),
could be things like package
name, version, etc.
COMPID,
EMPNUM, EMPNAME,
LOCATION
Because the Package table has only one field
(PACKID), we really don’t need this table
Normalization Example 2
PARKING TICKETS
SSN
LAST_NAME
123-34- 5678
Cur ry
FIRST_NAME
Dor othy
PHONE_NO
(916)358-4448
134-56- 7783
May
(916)563-7865
Fong
LIC_PLATE_ST
CA
CA
LIC_PLATE_NO
123 MCD
253 DAL
TICKET_NO
10151
10152
10121
10231
12051
DATE
10/15/98
10/16/98
11/12/98
10/23/98
12/5/98
CODE
A
B
B
C
A
Don’t be tricked!!! The first column of the data is not always the
primary key of the 1NF table.
8
$
$
$
$
$
FINE
10
20
20
50
10
1st Normal Form
PARKING TICKETS
TICKET_NO SSN
10151 123-34-5678
10152 123-34-5678
10121 123-34-5678
10231 134-56-7783
12051 134-56-7783
Primary Key
LAST_NAME
Cur ry
Cur ry
Cur ry
Fong
Fong
FIRST_NAME
Dor othy
Dor othy
Dor othy
May
May
PHONE_NO
(916)358-4448
(916)358-4448
(916)358-4448
(916)563-7865
(916)563-7865
LIC_PLATE_ST
CA
CA
CA
CA
CA
LIC_PLATE_NO
123 MCD
123 MCD
123 MCD
253 DAL
253 DAL
DATE
10/15/98
10/16/98
11/12/98
10/23/98
12/5/98
CODE
A
B
B
C
A
$
$
$
$
$
If 1NF has only one column as the primary key (no composite key) then
1NF and 2NF are the SAME!!!!!
9
FINE
10
20
20
50
10
2nd Normal Form
PARKING TICKETS
TICKET_NO SSN
10151 123-34-5678
10152 123-34-5678
10121 123-34-5678
10231 134-56-7783
12051 134-56-7783
Primary Key
LAST_NAME
Cur ry
Cur ry
Cur ry
Fong
Fong
FIRST_NAME
Dor othy
Dor othy
Dor othy
May
May
PHONE_NO
(916)358-4448
(916)358-4448
(916)358-4448
(916)563-7865
(916)563-7865
LIC_PLATE_ST
CA
CA
CA
CA
CA
10
LIC_PLATE_NO
123 MCD
123 MCD
123 MCD
253 DAL
253 DAL
DATE
10/15/98
10/16/98
11/12/98
10/23/98
12/5/98
CODE
A
B
B
C
A
$
$
$
$
$
FINE
10
20
20
50
10
3rd Normal Form
REGISTRATION
SSN
123-34-5678
134-56-7783
LAST_NAME
Cur ry
Fong
PARKING TICKETS
TICKET_NO SSN
10151 123-34-5678
10152 123-34-5678
10121 123-34-5678
10231 134-56-7783
12051 134-56-7783
FIRST_NAME PHONE_NO
Dor othy
(916)358-4448
May
(916)563-7865
DATE
10/15/98
10/16/98
11/12/98
10/23/98
12/5/98
CODE
A
B
B
C
A
LIC_PLATE_ST
CA
CA
LIC_PLATE_NO
123 MCD
253 DAL
FINES
CODE FINE
A
$ 10
B
$ 20
C
$ 50
Knowing the nature of the information, we can see that one person can have two
cars and probably LIC_PLATE_NO should be the primary key for the
REGISTRATION table and the foreign key for the PARKING_TICKETS table
and that another table with LIC_PLATE_NO and LIC_PLATE_ST as a primary
11
key and SSN as a foreign key should exist
3rd Normal Form
REGISTRATION
LIC_PLATE_NO LIC_PLATE_ST
123 MCD
CA
253 DAL
CA
FINES
CODE FINE
A
$ 10
B
$ 20
C
$ 50
SSN
123-34-5678
134-56-7783
PARKING TICKETS
TICKET_NO LIC_PLATE_NOLIC_PLATE_ST
DATE
10151
123 MCD
CA
10/15/1998
10152
123 MCD
CA
10/16/1998
10121
123 MCD
CA
11/12/1998
10231
253 DAL
CA
10/23/1998
12051
253 DAL
CA
12/5/1998
INDIVIDUALS
SSN
123-34-5678
134-56-7783
LAST_NAME
Curry
Fong
FIRST_NAME PHONE_NO
Dorothy
(916)358-4448
May
(916)563-7865
12
CODE
A
B
B
C
A
Normalization Example 3
InvoiceID CustomerIDCompanyName
214123
214390
214418
214460
214480
PhoneNumber Contact
InventoryIDQuantity UnitPrice
30139 Cavco Industries Inc.
(602) 555-6141
Golkin, David
30174 Thomas Nelson Inc. (615) 555-9079
Harber, L. H.
30174 Thomas Nelson Inc. (615) 555-9079
Harber, L. H.
30139 Cavco Industries Inc.
(602) 555-6141
Golkin, David
30125 Alamo Group Inc.
(210) 555-1483
M aul, Duane A.
14
1207
10
$8.40
1191
15
$4.50
1101
10
$8.10
1143
1
$6.30
1157
12
$9.30
1171
4
$7.40
1187
3 $22.00
1224
14
$15.20
1226
11
$12.90
1250
1
$11.60
1175
8
$11.70
1114
10
$9.50
1141
9
$7.90
1136
3
$7.00
1143
1
$6.30
1149
20
$6.00
1207
3
$8.40
1104
16
$5.30
1207
20
$8.40
1143
1
$6.30
1136
18
$7.00
1250
4
$11.60
1st Normal Form
InvoiceID InventoryID CustomerID CompanyName
PhoneNumber Contact
Quantity UnitPrice
214123
1207
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
10
$8.40
214123
1191
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
15
$4.50
214123
1101
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
10
$8.10
214123
1143
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
1
$6.30
214390
1157
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
12
$9.30
214390
1171
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
4
$7.40
214390
1187
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
3 $22.00
214390
1224
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
14 $15.20
214390
1226
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
11 $12.90
214418
1250
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
1
$11.60
214418
1175
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
8
$11.70
214418
1114
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
10
$9.50
214460
1141
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
9
$7.90
214460
1136
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
3
$7.00
214460
1143
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
1
$6.30
214460
1149
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
20
$6.00
214460
1207
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
3
$8.40
214460
1104
30139 Cavco Industries Inc. (602) 555-6141Golkin, David
16
$5.30
214480
1207
30125 Alamo Group Inc.
(210) 555-1483Maul, Duane A.
20
$8.40
214480
1143
30125 Alamo Group Inc.
(210) 555-1483Maul, Duane A.
1
$6.30
214480
1136
30125 Alamo Group Inc.
(210) 555-1483Maul, Duane A.
18
$7.00
214480
1250
30125 Alamo Group Inc.
(210) 555-1483Maul, Duane A.
4
$11.60
15
2nd Normal Form
InvoiceID InventoryID Quantity InvoiceID CustomerID CompanyName
PhoneNumber Contact
214123
1207
10
214123
30139 Cavco Industries Inc. (602) 555-6141 Golkin, David
214123
1191
15
214390
30174 Thomas Nelson Inc. (615) 555-9079 Harber, L. H.
214123
1101
10
214418
30174 Thomas Nelson Inc. (615) 555-9079 Harber, L. H.
214123
1143
1
214460
30139 Cavco Industries Inc. (602) 555-6141 Golkin, David
214390
1157
12
214480
30125 Alamo Group Inc.
(210) 555-1483 Maul, Duane A.
214390
1171
4
214390
1187
3 InventoryID UnitPrice
1101
$8.10
214390
1224
14
1104
$5.30
214390
1226
11
1114
$9.50
214418
1250
1
1136
$7.00
214418
1175
8
1141
$7.90
214418
1114
10
1143
$6.30
214460
1141
9
1149
$6.00
214460
1136
3
1157
$9.30
214460
1143
1
1171
$7.40
214460
1149
20
1175 $11.70
214460
1207
3
1187 $22.00
214460
1104
16
1191
$4.50
214480
1207
20
1207
$8.40
214480
1143
1
1224 $15.20
214480
1136
18
1226 $12.90
214480
1250
4
1250 $11.60
16
3rd Normal Form
InvoiceID InventoryID Quantity
214123
1207
10
214123
1191
15
214123
1101
10
214123
1143
1
214390
1157
12
214390
1171
4
214390
1187
3
214390
1224
14
214390
1226
11
214418
1250
1
214418
1175
8
214418
1114
10
214460
1141
9
214460
1136
3
214460
1143
1
214460
1149
20
214460
1207
3
214460
1104
16
214480
1207
20
214480
1143
1
214480
1136
18
214480
1250
4
CustomerID CompanyName
PhoneNumber Contact
30125 Alamo Group Inc.
(210) 555-1483 Maul, Duane A.
30139 Cavco Industries Inc. (602) 555-6141 Golkin, David
30174 Thomas Nelson Inc. (615) 555-9079Harber, L. H.
InventoryID UnitPrice
1101
$8.10
1104
$5.30
1114
$9.50
1136
$7.00
1141
$7.90
1143
$6.30
1149
$6.00
1157
$9.30
1171
$7.40
1175
$11.70
1187 $22.00
1191
$4.50
1207
$8.40
1224 $15.20
1226 $12.90
1250
$11.60
InvoiceID
CustomerID
214123
30139
214390
30174
214418
30174
214460
30139
214480
30125
17