Transitive Dependency in DBMS

Overview

Consider a relation $R(ABC)$ , where $A$ , $B$ and $C$ are the attributes of the relation $R$ . A Transitive dependency exists when you have the following functional dependency pattern, $A → B$ and $B → C$ ; therefore, $A → C$ . In other words, a functional dependency is said to be transitive if it is indirectly formed by two functional dependencies.

What is Transitive Dependency?

Consider a relation $R(ABC)$ , where $A$ , $B$ and $C$ are the attributes of the relation $R$ . A Transitive dependency exists when you have the following functional dependency pattern, $A → B$ and $B → C$ ; therefore, $A → C$ .
In other words, a dependent is indirectly dependent on determinant in Transitive functional dependency.

Let's explore transitive dependency with an example,

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

Authors Table

Author_ID	Author	Book	Author_Nationality
A1	Andrew	The Cursed Villa	United States
A1	Andrew	Children's Mind	United States
A2	Roman	Infinity Tale	Canada

In the Authors Table above:
- $Book → Author$ : Here, the Book attribute determines the Author attribute. If you know the book's name, you can learn the author's name.
- $Author → Author\_Nationality$ : If we know the Author name then we can determine Author_Nationality as well.
- $Book → Author\_Nationality$ : If we know the book name, we can determine the AuthorsNationality via the Author column.
If you take a closer look into the functional dependencies discussed above, they are indeed forming a pattern
- $A → B$ and $B → C$ ; therefore, $A → C$ .
- $A → Book$ , $B → Author$ and $C → Author\_Nationality$
To ensure Third Normal Form(3NF) in Authors table, his transitive dependency must be removed and this process of removing transitive dependency is called Normalization.

Avoiding Transitive Dependencies

What is the value of avoiding transitive dependencies to help ensure 3NF? Let's consider our Author table again and see the issues it creates:

Authors Table

Author_ID	Author	Book	Author_Nationality
A1	Andrew	The Cursed Villa	United States
A1	Andrew	Children's Mind	United States
A2	Roman	Infinity Tale	Canada

The design of Author table can contribute to data anomalies (like insertion, updation and deletion anomalies) and inconsistencies. Data anomalies are caused when there is too much redundancy in the database's information, making it very difficult to update, delete or insert new data. For example, in Author table:
- You cannot add a new author to the database unless you also add a book. What if the author is yet unpublished or you don't know the name of a book they authored?
- You can't delete any book without completely deleting the author.
- If you want to delete book Infinity Tale, the Author and Author_Nationality also get deleted.

The issues discussed above are not limited to just this Author table, these issues can arise in any relation containing Transitive Dependency.

Third Normal Form by Removing Transitive Dependency

Let's consider the Author table with three attributes(Author_ID, Author, Author_Nationality) and try to find and eliminate the Transitive dependency from this table,

Turn Learning into Career Growth

1200+Hiring Partners

89%Placement Rate

11,000+Placements

147%Avg Salary Increment

2.5XCareer Growth

₹23 LPAAvg Post-Scaler Salary

1200+Hiring Partners

89%Placement Rate

11,000+Placements

147%Avg Salary Increment

2.5XCareer Growth

₹23 LPAAvg Post-Scaler Salary

Author's Table

Author_ID	Author	Author_Nationality
A1	Andrew	United States
A2	Andres	United States
A3	Roman	Canada

The above Author's table is not in 3NF because it has the Transitive dependency. Let's see how
- $Author\_ID → Author$
- $Author → Author\_Nationality$
Therefore the following functional dependency also exist,
- $Authir\_ID → Author\_Nationality$ , which is indeed forming a pattern similar to what we discussed above.
Now to eliminate the Transitive dependency all we need to do is to split the Author's table in such a manner that Author_ID will no longer functionally depend on Author_Nationality.
Let's create two tables, one containing only { Author_ID, Author} and another table containing { Author, Author_Nationality}. The new tables will look like this,

Author Table

Author_ID	Author
A1	Andrew
A2	Andres
A3	Roman

Author Nationality Table

Author	Author_Nationality
Andrew	United States
Andres	United States
Roman	Canada

Now the new Author table and Author Nationality table contains no Transitive dependency and the relation is now in 3NF.

Conclusion

A Transitive dependency in a database is an indirect relationship between values in the same table that causes a functional dependency.
By nature, a transitive dependency requires three or more attributes.
To achieve the normalization standard of Third Normal Form (3NF), any transitive dependency must be eliminated.
Transitive dependency creates deletion, updation and insertion anomalies in the database and are considered as a bad database design.