Database Normalization Rule 2: Eliminate Redundant Data, if an attribute depends on only part of a multi-valued key, remove it to a separate table.
The trick name (e.g. “roll over”) appears redundantly for every puppy that knows it. Just trick ID whould do.
Puppy Number Trick ID Trick Name Where Learned Skill Level
52 27 “roll over” 16 9
53 16 “Nose Stand” 9 9
54 27 “roll over” 9 5
*Note that trick name depends on only a part (the trick ID) of the multi-valued, i.e. composite key.
In the trick table, the primary key is made up of the puppy number and trick ID. This makes sense for the “where learned” and “skill level” attributes, since they will be different for every puppy-trick combination. But the trick name depends only on the trick ID. The same name will appear redundantly every time its associated ID appears in the trick table.
Second Normal Form
trick where learned
Suppose you want to reclassify a trick, i.e. to give it a different trick ID. The change has to be made for every puppy that knows the trick. If you miss some of the changes, you will have several puppies with the same trick under different IDs, this is an update anomaly.