RelationalDBDesign RelationalDBDesign



Third Normal Form   «Prev  Next»
Lesson 4 Transitive dependencies in more Detail
Objective Explain How to eliminate Transitive Dependencies

How to eliminate Transitive Dependencies

Putting set theory firmly to the side, there is a way to eliminate transitive dependencies. Make sure every relation refers to a single entity.
Here again, is the relation we have been examining in the last few lessons:
Item (ItemNo, Title, Price, CategoryID, CategoryName, CategoryManager)

It refers to two business objects: Items and Categories. The transitive dependency in the relation, which is in 2NF, causes the relation to have the same problems as a relation that is only in 1NF.
  1. You must know one item in a category to enter information about that category.
  2. Deleting a record referring to the only item in a category deletes all information about that category.
    Separating the relation into two relations:
    Item (ItemNo, Title, Price, CategoryID) and
    Category (CategoryID, CategoryName, CategoryManager) 
    

    removes the transitive dependency. The relation is now in 3NF.

Remember that the relations must be linked by a common field and in this case it is CategoryID.
The next lesson summarizes the process for achieving third normal form and asks you to determine whether relations are in 3NF and to translate relations from 2NF into 3NF.

First, Second, and Third Normal Forms

Before moving to the next lesson, click the link below to read about the requirements of
  1. first,
  2. second,
  3. and third normal forms
First, Second, and Third Normal Forms