An entity is said to be in the third normal form (3NF) if it is already in 2NF and no non-identifying attributes are dependent on any other non-identifying attributes. A non-identifying attribute is any attribute that is not a part of the identifier for the entity. Attributes that are dependent on other non-identifying attributes are normalized by moving both the dependent attribute and the attribute on which it is dependent into a new entity.
If we wanted to track Record
Label
address information, we would have a problem
putting it in 3NF. The Record
Label
entity with address data would have
State
Name
and
State
Abbreviation
attributes.
Though we really do not need this information to track CD data, we
will add it to our data model for the sake of our example. Figure 7-11 shows address data in the
Record
Label
entity.
The values of State
Name
and
State
Abbreviation
would
conform to 1NF because they have only one value per record in the
Record Label
entity. The problem here is that
State Name
and State
Abbreviation
are dependent on each other. In other
words, if we change the State
Abbreviation
for a particular
Record
Label
—from
MN
to CA
—we also have to
change the State
Name
—from Minnesota
to
California
. We would normalize this by creating a
State
entity with State
Name
and State
Abbreviation
attributes. Figure 7-12 shows how to relate this new entity to the
Record Label
entity.
Now our data model is in 3NF, and we can say that it is normalized. There are other normal forms that have some value from a database design standpoint, but these are beyond the scope of this book. For most design purposes, 3NF is sufficient to guarantee a proper design.