Third Normal Form (3NF)

Further improvement of second normal form (2NF) is third normal form (3NF). We hope you are clear about 1NF and 2NF, if not do go through them:

A table is in third normal form (3NF) if it satisfies following two conditions:

  • The table is in second normal form (2NF)
  • There is no transitive dependency of non-key columns on primary key

What is transitive dependency?

Let’s say in a table, there are three columns A, B, C such that:

  1. There is a functional dependency between two columns, A and B, such that B is functionally dependent on A.
  2. There is a functional dependency between two columns, B and C, such that C is functionally dependent on B.

Then C transitively depends on A. This is called transitive dependency.

For example, we have table Department.

Table: Department

Suppose for each department there can be only one manager, thus non-key columns Department_name and Department_Manager_Id depends on Department_Id (Primary key). But Department_Manager_Name depends upon Department_Manager_Id which further depends upon Department_Id, thus there is transitive dependency in this table. To remove transitive dependency we need to redesign the tables as given below:

Table: Department

Table: DepartmentManager

Now the table is in third normal form.

Download as PDF

Read next:  BCNF, 4NF and 5NF ››

« Back to Course page