DBMS –Third Normal Form (3NF)

A normal form which is stricter than other normal forms is the Third Normal Form (3NF). Third normal form tries to eliminate most of the redundant data hence reducing redundancy in the tables. It also ensures referential Integrity among tables. Hence it leads to simplifying the approach towards relations.

Constraints to be followed in Third Normal Form

For a relation to be in third normal form (3NF) the following constraints must be followed:

1. The relation must be already be in its Second Normal Form (2NF).
2. All non-key attributes must not depend on other non-key attributes in the relation. In short, all non-key attributes must be independent of each other. This would mean that there should be no transitive dependency in the relation.

If both of the above constraints are followed then the table is said to be in its third normal form.

Now let us take up an example to understand the concept in a better way.

Example

Let us consider a relation HOSTEL (Stu_Id, Stu_Name, Course, Hostel_Block)

Let us suppose in this relation that each course has a designated hostel block.

Here in this table, students enrolling in course BA are allotted block 1 of the hostel, students enrolled in BSc are allotted block number 2 and students of course BCA are allotted block number 3.

The dependency diagram for the above table is as follows:

Now in this diagram if we check the dependencies then we can clearly see that there is a transitive dependency between the attributes Course and Hostel_Block.

How to convert a relation to Third Normal Form?

In order to convert the relation in Third Normal Form, we have to simply remove the transitive dependency between the non key attributes.

This can be achieved by splitting the table into two different tables and linking the tables with the help of foreign key attributes thereafter.

Hence in the above example we can divide the relation HOSTEL into 2 sub tables as follows:

Table 1: Stu_Info

Table 2: Hostel_Block

The splitting of the table into two distinct tables helps in reducing redundancy to a certain extent. This helps in improved performance and better management of data in the relations. Now, the relations are in Third Normal Form.

Anupreet Kalsi