- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 ($) | |
M08 | L09 | 180 |
M03 | L05 | 250 |
M05 | L09 | 180 |
<Listing>
Listing_ID
Listing_Type | |
L09 | Crime |
L05 | Drama |
L09 | Crime |
Now the above relation is in Third Normal Form (3NF) of Normalization.
Advertisements