Transitive Dependency in DBMS

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning

Overview

Consider a relation R(ABC)R(ABC), where AA, BB and CC are the attributes of the relation RR. A Transitive dependency exists when you have the following functional dependency pattern, ABA → B and BCB → C; therefore, ACA → 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)R(ABC), where AA, BB and CC are the attributes of the relation RR. A Transitive dependency exists when you have the following functional dependency pattern, ABA → B and BCB → C; therefore, ACA → C.
  • In other words, a dependent is indirectly dependent on determinant in Transitive functional dependency.

Let's explore transitive dependency with an example,

Authors Table

Author_IDAuthorBookAuthor_Nationality
A1AndrewThe Cursed VillaUnited States
A1AndrewChildren's MindUnited States
A2RomanInfinity TaleCanada
  • In the Authors Table above:
    • BookAuthorBook → Author: Here, the Book attribute determines the Author attribute. If you know the book's name, you can learn the author's name.
    • AuthorAuthor_NationalityAuthor → Author\_Nationality: If we know the Author name then we can determine Author_Nationality as well.
    • BookAuthor_NationalityBook → 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
    • ABA → B and BCB → C; therefore, ACA → C.
    • ABookA → Book, BAuthorB → Author and CAuthor_NationalityC → 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_IDAuthorBookAuthor_Nationality
A1AndrewThe Cursed VillaUnited States
A1AndrewChildren's MindUnited States
A2RomanInfinity TaleCanada
  • 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,

Author's Table

Author_IDAuthorAuthor_Nationality
A1AndrewUnited States
A2AndresUnited States
A3RomanCanada
  • The above Author's table is not in 3NF because it has the Transitive dependency. Let's see how
    • Author_IDAuthorAuthor\_ID → Author
    • AuthorAuthor_NationalityAuthor → Author\_Nationality
  • Therefore the following functional dependency also exist,
    • Authir_IDAuthor_NationalityAuthir\_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_IDAuthor
A1Andrew
A2Andres
A3Roman

Author Nationality Table

AuthorAuthor_Nationality
AndrewUnited States
AndresUnited States
RomanCanada
  • 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.

Read More: