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.
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.
For a table to be in the third normal form,
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
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.
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.
Again the solution is very simple. Take out the columns
total_marks from Score table and put them in an Exam table and use the
exam_id wherever required.
The advantage of removing transitive dependency is,