Dark Mode On/Off

# Third Normal Form (3NF)

Third Normal Form is an upgrade to Second Normal Form. When a table is in the Second Normal Form and has no transitive dependency, then it is in the Third Normal Form.

Before moving forward with Third Normal Form, check these topics out to understand the concept better :

The video below covers the concept of Third Normal Form in details.

In our last tutorial, we learned about the second normal form and even normalized our Score table into the 2nd Normal Form.

So let's use the same example, where we have 3 tables, Student, Subject and Score.

#### Student Table

10Akon07-WYCSEKerala
11Akon08-WYITGujarat
12Bkon09-WYITRajasthan

#### Subject Table

subject_idsubject_nameteacher
1JavaJava Teacher
2C++C++ Teacher
3PhpPhp Teacher

#### Score Table

score_idstudent_idsubject_idmarks
110170
210275
311180

In the Score table, we need to store some more information, which is the exam name and total marks, so let's add 2 more columns to the Score table.

score_idstudent_idsubject_idmarksexam_nametotal_marks

## Requirements for Third Normal Form

For a table to be in the third normal form,

1. It should be in the Second Normal form.
2. And it should not have Transitive Dependency.

### What is Transitive Dependency?

With `exam_name` and `total_marks` added to our Score table, it saves more data now. Primary key for our Score table is a composite key, which means it's made up of two attributes or columns → student_id + subject_id.

Our new column `exam_name` depends on both student and subject. For example, a mechanical engineering student will have Workshop exam but a computer science student won't. And for some subjects you have Prctical exams and for some you don't. So we can say that `exam_name` is dependent on both `student_id` and `subject_id`.

And what about our second new column `total_marks`? Does it depend on our Score table's primary key?

Well, the column `total_marks` depends on `exam_name` as with exam type the total score changes. For example, practicals are of less marks while theory exams are of more marks.

But, `exam_name` is just another column in the score table. It is not a primary key or even a part of the primary key, and `total_marks` depends on it.

This is Transitive Dependency. When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.

### How to remove Transitive Dependency?

Again the solution is very simple. Take out the columns `exam_name` and `total_marks` from Score table and put them in an Exam table and use the `exam_id` wherever required.

#### Score Table: In 3rd Normal Form

score_idstudent_idsubject_idmarksexam_id

#### The new Exam table

exam_idexam_nametotal_marks
1Workshop200
2Mains70
3Practicals30

### Advantage of removing Transitive Dependency

The advantage of removing transitive dependency is,

• Amount of data duplication is reduced.
• Data integrity achieved.