Transitive Dependency in DBMS

Let's consider a relation R(A B C). Here A, B, and C are known as the attribute of the relation R. When the following condition arises; then the transitive dependency occurs in the DBMS. The conditions are A → B, B → C. Therefore, the condition becomes A → C. In other words, we can say that when dependencies are made by two functional dependencies, then the functional dependencies become transitive dependencies.

What is Transitive Dependency?

Consider a relation R(A B C). Here A, B, and C are known as the attribute of the relation R. When the following condition arises; then the transitive dependency occurs in the DBMS. The conditions are A → B, B → C. Therefore, the condition becomes A → C. in transitive functional dependencies, a dependent is directly dependent on the determinant.

Let's understand the transitive dependencies with the help of the following example.

In the above author table, we get that.

• Book → Author: Here, the author attribute is determined by the book attribute. If someone knows the book's name, they can also learn the author's name.
• Author → Author_Nationality: If someone knows the author's name, they can also learn the author's nationality.
• Book → Author_Nationality : If someone knows the book's name, they can also learn the author's nationality.

If we look closely at the functional dependencies discussed above, we find the following pattern.

• A → B and B → C B→C; therefore, A → CA → C.
• A → Book, B → Author B → Author and C → Author_Nationality C→Author_Nationality

Suppose we apply Third Normal Form (3NF) in the above author table. In that case, the transitive dependency of the author table must be removed, and the process of removing the transitive dependencies of the database is known as the Normalization process.

How to Avoid Transitive Dependencies?

Let's consider the above author table again and see what type of issue arises during the table creation.

Authors table:

The data anomalies (like updation, insertion, and deletion anomalies) and inconsistency can be contributed by the author table. When there is too much redundancy in the database, then data anomalies are caused in the data. Data anomalies are also caused when there is a problem with updating, deleting, and inserting new data. For example, in the author table-

• We cannot add a new author until we cannot add a book to the table.
• We cannot delete the author until we completely delete the book from the database.
• If we want to delete the book "The God of Small Things," the author's id, author, and nationality also got deleted.

The above issues occur in any relationship that contains transitive dependencies.

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,

Authors Table:

The above Author's table is not in 3NF because it has a Transitive dependency. Let's see how

• Author → Author_Nationality Author → Author_Nationality
• Author_ID → Author

Therefore the following functional dependency also exists,

• Authir_ID → Author_Nationality is 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 containing {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.