Third Normal Form (3NF)
- Example:
- 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.
- 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.
- 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.
- 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.
- Some subjects you have Practical exams and for some you don't.
- So we can say that exam_name is dependent on both student_id and subject_id.
- 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.
- Advantage of removing Transitive Dependency
- The advantage of removing transitive dependency is,
- Amount of data duplication is reduced.
- Data integrity achieved.
Tags:
DBMS
){kind=link}