Third Normal Form

In this lesson, we will discuss the concept behind 3NF using an example.

We'll cover the following

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:

SCORE table

Std_Id Subject_Id Marks_obtained Exam_Id
1 CS-100 50 1
2 CS-100 70 1
3 CS-100 85 1
1 Math-101 30 2
1 PHY-100 10 3
2 CHEM-100 20 3
3 PHY-120 40 2

EXAM table

Exam_Id Exam_Type Total_Marks
1 Final 100
2 Mid-term 50
3 Practical 30

In the next lesson, we will learn about our final normal form which is the Boyce-Codd normal form.

Second Normal Form
Boyce-Codd Normal Form
Mark as Completed
Report an Issue