Transitive dependency in DBMS


What is Transitive Dependency

When an indirect relationship causes functional dependency it is called Transitive Dependency.

If  P -> Q and Q -> R is true, then P-> R is a transitive dependency.

To achieve 3NF, eliminate the Transitive Dependency.

Example

<MovieListing>

Movie_ID
Listing_ID
Listing_Type
DVD_Price ($)
M08
L09
Crime
180
M03
L05
Drama
250
M05
L09
Crime
180


The above table is not in 3NF because it has a transitive functional dependency −

Movie_ID -> Listing_ID
Listing_ID -> Listing_Type


Therefore, the following has transitive functional dependency.

Movie_ID -> Listing_Type


The above states the relation <MovieListing> violates the 3rd Normal Form (3NF).

To remove the violation, you need to split the tables and remove the transitive functional dependency.

<Movie>

Movie_ID

Listing_ID
DVD_Price ($)
M08L09180
M03L05250
M05L09180


<Listing>

Listing_ID

Listing_Type
L09Crime
L05Drama
L09Crime


Now the above relation is in Third Normal Form (3NF) of Normalization.

Updated on: 15-Jun-2020

21K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements