Managing & Using MySQL, 2nd Edition

Third Normal Form

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.

Figure 7-11. Record Label address information in our CD database

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. ...