Third normal form:
In second normal form, all columns in the table rely on the primary key and the table has a singular purpose
There could be relationships between the columns… dependencies could lurk within these columns called transitive dependence.
Transitive dependence means that a value of a column/field within a table relies on a another column in that same table, but this is facilitated through another column between them.
A table is in third normal form when the following conditions are met:
- It is in second normal form.
- All nonprimary fields are dependent on the primary key.
Again, transitive dependence means dependence between columns of the same table. Think of ArtistNationality, Artist, and Artwork. The values for ArtistNationality and Artist depend on the Artwork; once you figure out the Artwork, you know the Artist/ArtistNationality. But ArtistNationality depends on the value from Artist: This is a transitive dependence.
In order to get to third normal form, we need to make sure all columns are only dependent upon the primary key. That means we have to get the country code out of the artist table. In the following example, we’ve added country name to the table to further highlight the issue with normalizing
It’s fine that the country code exists in the artist table, but having the country name breaks the 3rd normal form rule, since we can’t get the country name without the code; we can’t get the code without the artist ID. This is by nature the definition of transitive dependence.
Let’s look at some examples that will use the fictional music database as a subject.
we’ll create another table called countries, move the country code and country name to this table. Country code becomes the primary key in the countries table, but is retained in the artist table as a foreign key.