Requirements for Third Normal Form
For a table to be in the third normal form,
- It should be in the Second Normal form.
- And it should not have 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
The new Exam table
Advantage of removing Transitive Dependency
The advantage of removing transitive dependency is,
- Amount of data duplication is reduced.
- Data integrity achieved.