Transitive Dependency in DBMS

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,

Authors Table

• 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

• 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

• 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 Nationality Table

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

Read More:

• DBMS Denormalization.