Online College Courses for Credit

+
Third Normal Form

Third Normal Form

Rating:
(0)
Author: Sophia Tutorial
Description:

Recognize correctly normalized tables in third normal form based on a set of tables in second normal form.

(more)
See More
Tutorial

what's covered
This tutorial explores converting a database design from the second normal form to the third normal form.

With the third normal form, this is generally the final stage of most normalization processes. It requires that the database design first fully fulfills the requirements of the second normal form but also has a criterion to ensure that there is no transitive functional dependency. This means that every attribute that is not the primary key in a table must depend on the primary key and only the primary key. Say that we have column A determines column B and column B determines column C. We would have transitive functional dependency as column A would determine column C and column C should be removed and placed in a separate table. We need to check this for all of our tables with each column. For our database design that was in 2NF:

File:11535-5240-1.png

In our case, none of the non-primary key fields depend on something else other than the primary key. So, in essence, after 2NF, our current database design is actually in 3NF. This approach is also quite common to see. However, let us extend the User table a bit further to include some additional details about the user such as the email, phone, address, city, state, and zip code. We will also make the assumption that all of the users will be in the country USA. By doing so, our User table would look like the following:

  • User (UserId, UserFirstname, UserLastName, Email, Phone, Address, City, State, Zip)
One important piece to note by looking at this is that the City and State are determined by the Zip in the US which falls under the pattern that we discussed before as the Zip is dependent on the UserID but the City and State are dependent on the Zip. To truly convert this to 3NF, we would need to split up the tables like the following:

  • User (UserId, UserFirstname, UserLastName, Email, Phone, Address, Zip)
  • ZipCode (Zip, City, State)
The Zip in the User table would link to the Zip in the ZipCode table. This way, any time that the Zip code was entered in, the city and state would automatically be set. If we had to add an address in any other table later on, then we can use the same ZipCode table as a lookup table. The resulting ERD for 3NF would look like the following:

File:11536-5240-2.png

Note though that the zip code itself is not commonly split up on its own even with it being a 3NF rule as there are instances where a zip code in the US can reflect different cities. If we plan to have this setup for any country, this may not apply either in which case the following ERD would make the most sense:

File:11537-5240-3.png

Depending on the requirements, this will be our final normalized database design in third normal form.


summary
The third normal form will ensure that the database fulfills all second normal form rules and handle transitive functional dependency.