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.


TRICK TABLE
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

puppy table
puppy number
puppy name
kennel code
kennel name
kennel location

tricks table
tricks ID
tricks name

Puppy-Tricks
puppy number
trick ID
trick where learned
skill level

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.