Third Normal Form

Third normal form (3NF)

For a table to be in the third normal form:

1. It should be in the second normal form.

2. It should not have transitive dependency.

Example

SCORE Table

Std_Id	Subject_Id	Marks_obtained	Exam_Type	Total_Marks
1	CS-100	50	Final	100
2	CS-100	70	Final	100
3	CS-100	85	Final	100
1	Math-101	30	Mid-term	50
1	PHY-100	10	Practical	30
2	CHEM-100	20	Practical	30
3	PHY-120	40	Mid-term	50

From the table, we can see that the primary key for our SCORE table is a composite key, which means it’s made up of two attributes (columns): {Std_Id, subject_Id}.

The column Exam_Type depends on both Std_Id and Subject-Id. For example, a student taking a chemistry course will have a practical lab exam but a student in a mathematics course will not. So we can say that Exam_Type is dependent on the whole composite key, thus there is no partial dependency, so the table is in 2NF.

But what about the column Total_Marks? Does it depend on our SCORE table’s primary key?

Well, the column Total_Marks depends on Exam_Type since the type of exam the total score changes. For example, practicals are worth fewer marks while theory exams are worth more marks.

This results in a transitive dependency because a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.

So, in order to convert this table into 3NF, we take out the attributes Exam_Type and Total_Marks from the SCORE table and put them in their own table called the EXAM table. We will also add another column called Exam_Id in the EXAM table to act as the primary key. This column will also be added to the SCORE as a foreign key, so now we have a link between the two tables.

This is illustrated below: