Puppy Number
+ Puppy Name
+ Kennel Code
+ Kennel Name
+ Kennel Location
+ { Trick ID
+ Trick Name
+ Trick Where Learned
+ Skill Level
}1...n
When the file was first set up, it was thought that no puppy could possibly learn more than 10 tricks before graduation, but one puppy, Snoopy, learned more than 40 tricks before his adoption by Charles Brown. This forced a restructuring of the entire file, but it also left most of the file filled with meaningless spaces since the average puppy learned only three of the forty possible tricks. In addition, more and more people are asking for puppies which know specific tricks. Right now the entire file of 25,693 puppies needs to be searched to find a match.
Make a separate table for each set of related attribute, and give each table a primary key. How?
Split the table into two tables:
Puppy Table = Puppy Number
+ Puppy Name
+ Kennel Code
+ Kennel Name
+ Kennel Location
Trick Table = Puppy Number + Trick ID
+ Trick Name
+ Trick Where Learned
+ Skill Level
It would help to have all the trick data to be in a separate table. This would decrease the table size, since every puppy who knows trick 43 would have its name, Roll Over, too. If the last puppy to know trick 43 were run over by a car, his record will be removed and we will lose all knowledge of trick 43.
If an attribute depends on only part of a multi-valued key, remove it into a separate table. How?
Split the Trick Table into two: Tricks and Puppy Tricks.
Tricks = Trick ID
+ Trick Name
Puppy Tricks = Puppy Number + Trick ID
+ Trick Where Learned
+ Skill Level
Puppy Table = Puppy Number
+ Puppy Name
+ Kennel Code
+ Kennel Name
+ Kennel Location
Now suppose no puppies from the K9-Kennel were currently stored in the database. There then remains no record of K9-Kennel's existence!
If attributes do not contribute to a description of the key, remove them to a separate table. How?
Split Puppy Table into two tables: Puppies and Kennels
Tricks = Trick ID
+ Trick Name
Puppy Tricks = Puppy Number + Trick ID
+ Trick Where Learned
+ Skill Level
Puppies = Puppy Number
+ Puppy Name
+ Kennel Code
Kennels = Kennel Code
+ Kennel Name
+ Kennel Location
Suppose we want to add a new attribute to the Puppy-Trick table, "Costume." This way we can find puppies who can "sit-up- and-beg" while wearing a Ted Turner mask. The two need not relate, for example, a puppy may be able to walk upright while wearing a wet suit, but may not be able to do both at the same time. The new Puppy Tricks table will look like this:
Puppy Tricks = Puppy Number + Trick ID + Costume
+ Trick Where Learned
+ Skill Level
No table may contain two or more 1:n or n:m relationships that are not directly related. Here Puppy Number specifies
a well defined set of Trick IDs and a well-defined sets of costumes. Thus there is multi-dependency. How do we
prevent anomalies in this case?
Split Puppy Tricks in two, Puppy Tricks and Puppy Costumes.
Puppy Tricks = Puppy Number + Trick ID
+ Trick Where Learned
+ Skill Level
Puppy Costumes = Puppy Number + Costume