Transcript Slide 1
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