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)

Stu_Id Stu_Name Course Hostel_Block
1021 Anuj BA Block 1
1058 Mannat BA Block 1
2142 Simran BCA Block 3
3068 Mandeep BSc Block 2

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:

Third Normal form Dependency DIagram

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

Stu_Id Stu_Name Course
1021 Anuj BA
1058 Mannat BA
2142 Simran BCA
3068 Mandeep BSc

Table 2: Hostel_Block

Course Hostel_Block
BA Block 1
BA Block 1
BCA Block 3
BSc Block 2

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.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *