Third Normal Form (3NF)

Advertisement by Google

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
IT_Officer_008_01

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
IT_Officer_008_02

Table: DepartmentManager
IT_Officer_008_03

Now the table is in third normal form.

Download as PDF

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

« Back to Course page

Advertisement

Comments

Subscribe Us

Join us to get latest article in your inbox

Advertisement

Article of the Week

Follow Us On Facebook