What is the third normal form (3NF)?

Normal forms

Normalization is the process of reducing redundancy from a set of relations. In database tables, we can use normal forms to do this. The four main normal forms are:

• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
• Boyce–Codd normal form (BCNF)

As the figure below shows, these normal forms build upon one another progressively. In this shot, we will focus on the third normal form.

Third normal form (3NF)

For a table to be in third normal form, it needs to satisfy the following conditions:

• It should be in second normal form
• It should not have any transitive dependencies for non-prime attributes

Transitive dependencies are indirect relationships between values in the same table that cause functional dependencies.

For a table to not have any transitive dependencies, we need to ensure that no non-prime attribute determines another non-prime attribute as only prime attributes or candidate keys can determine non-prime attributes for a table in 3NF.

The following figure shows an example of a transitive dependency:

Now, let’s look at a concrete example of a table that is currently not in third normal form.

In the table above, ID is the prime attribute while the other two are non-prime. As you can see, an issue arises since Country is a non-prime attribute that determines State, another non-prime attribute.

To achieve 3NF, we must remove the above transitive dependency from the table in question. This can be done by splitting the table in two. We now have 3NF.