Slide 1

Report
Initial table storing contact
information
1NF
2NF
3NF
What members know DB2?
• 1NF
What is wrong with this table?
• 2NF
What is wrong with the original
table?
• 3NF
Normalization:1NF
Normalization:2NF
Normalization:2NF
Normalization:3NF
Unnormalized
SSN --> Name, Age, Address, PetID, PetName, PetAge, Type, License#, Vehicle, Color, VehPrice, Year
SSN --> Name, Age, Address
PetID --> PetName, PetAge, Type
License# --> Vehicle, Color, VehPrice, Year
Vehicle --> VehPrice
EMPLOYEES
SSN Name Age Address PetID
D2
111
joe
43
72 R
L1
123
joe
22
57 R
bp1
C1
P1
222 steve 32
12 C
L4
234
jim
35
18 C
C2
F1
333
fred
21
12 Q
L2
F2
S1
343
bob
17
15 H
S2
D1
444
ann
21
32 F
D4
555
777
788
789
987
ann
sally
sally
tasha
elena
21
25
24
27
51
32 F
54 Z
54 Z
54 Z
12 Q
SSN : PetID :: 1 : M
SSN : License# :: M : M
PetName PetAge Type License#
buddy
1
dog
snipper
2
lizard LN 03
bl1
fluffy
1
cat
pete
2
parot LN 01
lenny
1
lizard LN 09
sassy
1
cat
herman
1
frog LN 04
vinny
2
lizard LN 06
feddy
3
frog
sneaky
2
snake
sulky
2
snake LN 14
fido
3
dog
arfy
3
dog
LN 05
C3
cotton
4
cat LN 15
D3
mutz
5
dog LN 07
D5
mutz2
4
dog LN 18
LN 08
L3
lizzy
3
lizard LN 06
Vehicle Color VehPrice (K) Year
van
grn
25
1991
viper
celica
red
yel
70
29
1999
1987
jeep
wagon
blu
red
28
10
1995
1975
truck
blu
28
1982
SUV
SUV
jeep
camry
mustang
wagon
yel
red
blu
wht
red
red
35
35
28
23
28
5
1997
1996
1995
1998
1991
1975
1NF
SSN, PetID, License# --> Name, Age, Address, PetName, PetAge, Type, Vehicle, Color, VehPrice, Year
SSN --> Name, Age, Address
PetID --> PetName, PetAge, Type
License# --> Vehicle, Color, VehPrice, Year
Vehicle --> VehPrice
SSN : PetID :: 1 : M
SSN : License# :: M : M
EMPLOYEES
SSN Name Age Address PetID PetName PetAge Type License# Vehicle Color VehPrice (K)
111
joe 43
72 R
D2
buddy
1
dog LN 03
van
grn
25
111
joe 43
72 R
L1
snipper
2
lizard LN 03
van
grn
25
123
joe 22
57 R
bp1
bl1
222 steve 32
12 C
C1
fluffy
1
cat LN 01
viper
red
70
222 steve 32
12 C
P1
pete
2
parot LN 09
celica
yel
29
222 steve 32
12 C
L4
lenny
1
lizard LN 09
celica
yel
29
234
jim 35
18 C
C2
sassy
1
cat bl2
333
fred 21
12 Q
F1
herman
1
frog LN 04
jeep
blu
28
333
fred 53
12 Q
L2
vinny
2
lizard LN 06
wagon red
10
343
bob 17
15 H
F2
freddy
3
frog LN 14
truck
blu
28
343
bob 17
15 H
S1
sneaky
2
snake LN 14
truck
blu
28
343
bob 17
15 H
S2
sulky
2
snake LN 14
truck
blu
28
444
ann 21
32 F
D1
fido
3
dog bl3
444
ann 21
32 F
D4
arfy
3
dog bl4
555
ann 21
32 F
C3
cotton
4
cat LN 05
SUV
yel
35
555
ann 21
32 F
C3
cotton
5
cat LN 15
SUV
red
35
777 sally 25
54 Z
D3
mutz
5
dog LN 07
jeep
blu
28
788 sally 24
54 Z
D5
mutz2
4
dog LN 18
camry wht
23
789 tasha 27
54 Z
bp2
LN 08
mustang red
28
987 elena 51
12 Q
L3
lizzy
3
lizard LN 06
wagon red
5
Year
1991
1991
1999
1987
1987
1995
1975
1982
1982
1982
1997
1996
1995
1998
1991
1975
Redundancy Unleashed
SSN, PetID, License# --> Name, Age, Address, PetName, PetAge, Type, Vehicle, Color, VehPrice, Year
SSN --> Name, Age, Address
PetID --> PetName, PetAge, Type
License# --> Vehicle, Color, VehPrice, Year
Vehicle --> VehPrice
EMPLOYEES
SSN Name Age Address PetID
111
joe
43
72 R
D2
111
joe
43
72 R
L1
123
joe
22
57 R
bp1
222 steve 32
12 C
C1
222 steve 32
12 C
P1
222 steve 32
12 C
L4
234
jim
35
18 C
C2
333 fred 21
12 Q
F1
333 fred 53
12 Q
L2
343 bob 17
15 H
F2
343 bob 17
15 H
S1
343 bob 17
15 H
S2
444
ann 21
32 F
D1
444
ann 21
32 F
D4
555
ann 21
32 F
C3
555
ann 21
32 F
C3
777 sally 25
54 Z
D3
788 sally 24
54 Z
D5
789 tasha 27
54 Z
bp2
987 elena 51
12 Q
L3
SSN : PetID :: 1 : M
LEGEND: redundant
inconsistent
SSN : License# :: M : M
PetName PetAge Type License#
buddy
1
dog LN 03
snipper
2
lizard LN 03
bl1
fluffy
1
cat LN 01
pete
2
parot LN 09
lenny
1
lizard LN 09
sassy
1
cat bl2
herman
1
frog LN 04
vinny
2
lizard LN 06
freddy
3
frog LN 14
sneaky
2
snake LN 14
sulky
2
snake LN 14
fido
3
dog bl3
arfy
3
dog bl4
cotton
4
cat LN 05
cotton
5
cat LN 15
mutz
5
dog LN 07
mutz2
4
dog LN 18
LN 08
lizzy
3
lizard LN 06
redundant for 2 reasons
Vehicle Color VehPrice (K) Year
van
grn
25
1991
van
grn
25
1991
viper
celica
celica
red
yel
yel
70
29
29
1999
1987
1987
jeep
wagon
truck
truck
truck
blu
red
blu
blu
blu
28
10
28
28
28
1995
1975
1982
1982
1982
SUV
SUV
jeep
camry
mustang
wagon
red
red
grn
wht
red
blu
35
30
28
23
28
10
1997
1996
1995
1998
1991
1975
2NF Raw - Part1
SSN --> Name, Age, Address
PetID --> PetName, PetAge, Type, SSN
License# --> Vehicle, Color, VehPrice, Year
Vehicle --> VehPrice
PEOPLE
PETS
SSN
111
Name Age Address
joe
43
72 R
123
222
joe
steve
22
32
57 R
12 C
234
333
jim
fred
35
21
18 C
12 Q
343
bob
17
15 H
444
ann
21
32 F
555
ann
21
777
788
789
987
sally
sally
tasha
elena
25
24
27
51
PetID
D2
L1
PetName PetAge Type
buddy
1
dog
snipper
2
lizard
SSN
111
111
32 F
C1
P1
L4
C2
F1
L2
F2
S1
S2
D1
D4
C3
fluffy
pete
lenny
sassy
herman
vinny
freddy
sneaky
sulky
fido
arfy
cotton
1
2
1
1
1
2
3
2
2
3
3
4
cat
parot
lizard
cat
frog
lizard
frog
snake
snake
dog
dog
cat
222
222
222
234
333
333
343
343
343
444
444
555
54 Z
54 Z
54 Z
12 Q
D3
D5
bp2
L3
mutz
mutz2
5
4
dog
dog
777
788
lizzy
3
lizard
987
2NF Raw - Part2
JT
VEHICLES
SSN
License#
111
LN 03
222
LN 01
222
LN 09
333
LN 04
333
LN 06
343
LN 14
555
LN 05
555
LN 15
777
LN 07
788
LN 18
789
LN08
987
LN06
License# Vehicle Color VehPrice (K) Year
LN 03
van
grn
25
1991
LN 01
LN 09
viper
celica
red
yel
70
29
1999
1987
LN 04
LN 06
LN 14
jeep
wagon
truck
blu
red
blu
28
10
28
1995
1975
1982
LN 05
LN 15
LN 07
LN 18
LN 08
SUV
SUV
jeep
camry
mustang
yel
red
blu
wht
red
35
35
28
23
28
1997
1996
1995
1998
1991
2NF Clean - Part1
SSN --> Name, Age, Address
PetID --> PetName, PetAge, Type, SSN
License# --> Vehicle, Color, VehPrice, Year
Vehicle --> VehPrice
PEOPLE
SSN Name Age Address
111
joe
43
72 R
123
joe
22
57 R
222 steve 32
12 C
234
jim
35
18 C
333
fred
21
12 Q
343
bob
17
15 H
444
ann
21
32 F
555
ann
21
32 F
777
sally 25
54 Z
788
sally 24
54 Z
789 tasha 27
54 Z
987 elena 51
12 Q
PETS
PetID PetName PetAge Type
C1
fluffy
1
cat
C2
sassy
1
cat
C3
cotton
4
cat
D1
fido
3
dog
D2
buddy
1
dog
D3
mutz
5
dog
D4
arfy
3
dog
D5
mutz2
4
dog
F1
herman
1
frog
F2
freddy
3
frog
L1
snipper
2
lizard
L2
vinny
2
lizard
L3
lizzy
3
lizard
L4
lenny
1
lizard
P1
pete
2
parot
S1
sneaky
2
snake
S2
sulky
2
snake
SSN
222
234
555
444
111
777
444
788
333
343
111
333
987
222
222
343
343
2NF Clean – Part2
JT
VEHICLES
SSN
License#
111
LN 03
222
LN 01
222
LN 09
333
LN 04
333
LN 06
343
LN 14
555
LN 05
555
LN 15
777
LN 07
788
LN 18
789
LN08
987
LN06
License#
LN 01
LN 03
LN 04
LN 05
LN 06
LN 07
LN 08
LN 09
LN 14
LN 15
LN 18
Vehicle Color VehPrice (K)
viper
red
70
van
grn
25
jeep
blu
28
SUV
yel
35
wagon red
10
jeep
blu
28
mustang red
28
celica
yel
29
truck
blu
28
SUV
red
35
camry
wht
23
Year
1999
1991
1995
1997
1975
1995
1991
1987
1982
1996
1998
3NF Clean – Part2
SSN --> Name, Age, Address
PetID --> PetName, PetAge, Type, SSN
License# --> Vehicle, Color, Year
Vehicle --> VehPrice
PEOPLE
PETS
SSN
111
123
222
234
333
343
444
555
777
788
789
987
PetID PetName PetAge Type
C1
fluffy
1
cat
C2
sassy
1
cat
C3
cotton
4
cat
D1
fido
3
dog
D2
buddy
1
dog
D3
mutz
5
dog
D4
arfy
3
dog
D5
mutz2
4
dog
F1
herman
1
frog
F2
freddy
3
frog
L1
snipper
2
lizard
L2
vinny
2
lizard
L3
lizzy
3
lizard
L4
lenny
1
lizard
P1
pete
2
parot
S1
sneaky
2
snake
S2
sulky
2
snake
Name Age Address
joe
43
72 R
joe
22
57 R
steve 32
12 C
jim
35
18 C
fred
21
12 Q
bob
17
15 H
ann
21
32 F
ann
21
32 F
sally 25
54 Z
sally 24
54 Z
tasha 27
54 Z
elena 51
12 Q
SSN
222
234
555
444
111
777
444
788
333
343
111
333
987
222
222
343
343
3NF Clean – Part2
JT
VEHICLES
SSN
License#
111
LN 03
222
LN 01
222
LN 09
333
LN 04
333
LN 06
343
LN 14
555
LN 05
555
LN 15
777
LN 07
788
LN 18
789
LN08
987
LN06
License# Vehicle
LN 01
viper
LN 03
van
LN 04
jeep
LN 05
SUV
LN 06
wagon
LN 07
jeep
LN 08
mustang
LN 09
celica
LN 14
truck
LN 15
SUV
LN 18
camry
VEH
Color
red
grn
blu
yel
red
blu
red
yel
blu
red
wht
Year
1999
1991
1995
1997
1975
1995
1991
1987
1982
1996
1998
Vehicle VehPrice (K)
camry
23
celica
29
jeep
28
mustang
28
SUV
35
truck
28
van
25
viper
70
wagon
10

similar documents