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:
-
It should be in the second normal form.
-
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:
Get hands-on with 1200+ tech skills courses.