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:
- There is a functional dependency between two columns, A and B, such that B is functionally dependent on A.
- 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.
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:
Now the table is in third normal form.
Download as PDF
Read next: BCNF, 4NF and 5NF ››
« Back to Course page