How to eliminate Transitive Dependencies

To eliminate transitive dependencies in the normalization process and ensure that every relation refers to a single entity, you must progress to "Third Normal Form (3NF)". Here's how you can do it:
Steps to Eliminate Transitive Dependencies:

1. Understand the Concept of Transitive Dependency:
   • A transitive dependency exists when a non-prime attribute (not part of a candidate key) depends on another non-prime attribute, which in turn depends on the primary key.
   • In other words:
     

     A → B and B → C ⇒ A → C
     

   • Here, \( C \) is transitively dependent on \( A \) through \( B \).
2. Identify Transitive Dependencies:
   • Examine your relation (table) to determine if there are attributes indirectly dependent on the primary key through another non-prime attribute.
   • For example:
     

     Student(ID, Name, Major, DeptHead, DeptName)
     

   • • \( ID \) is the primary key.
     • \( DeptName \) depends on \( Major \) (not \( ID \)).
     • \( Major \) depends on \( ID \).
     • This creates a transitive dependency: \( ID → Major → DeptName \).
3. Decompose the Relation:
   • Split the table into smaller relations such that each non-prime attribute depends directly on the primary key.
   • Example decomposition:
     • Relation 1: `Student(ID, Name, Major)`
     • Relation 2: `Department(Major, DeptName, DeptHead)`
4. Ensure Every Relation Refers to a Single Entity:
   • Each table should now describe a single entity or theme.
   • `Student` table refers only to students.
   • `Department` table refers only to departments.
5. Verify Dependency Structure:
   • Check that all dependencies are direct and avoid indirect ones.
   • Ensure no attribute is dependent on a non-prime attribute.

Why Eliminate Transitive Dependencies?

• Avoid Redundancy: Prevent unnecessary duplication of data.
• Minimize Update Anomalies: Ensures updates to one table propagate correctly without leaving inconsistent data.
• Enhance Data Integrity: Each table captures only one type of information, making it easier to maintain relationships.

Final Note:
After removing transitive dependencies, the resulting tables satisfy "Third Normal Form (3NF)". This process reduces complexity and ensures that each relation refers to a single logical entity.

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.

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