A transitive dependency refers to some non-prime attribute other than the candidate key that depends on another non-prime attribute that is dependent entirely on the candidate key.
In this article, we will take a look at the Transitive Dependency in DBMS according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.
Table of Contents
What is Transitive Dependency in DBMS?
Whenever some indirect relationship happens to cause functional dependency (FC), it is known as Transitive Dependency. Thus, if A -> B and B -> C are true, then A -> C happens to be a transitive dependency.
Thus, to achieve 3NF, one must eliminate the Transitive Dependency.
Note:
The given functional dependency can only be transitive when it is formed indirectly by two FDs. For example,
P -> R happens to be a transitive dependency when the following functional dependencies hold true:
- P -> Q
- Q does not -> P
- Q -> R
The transitive dependency can occur easily only in the case of some given relation of three or more attributes. Such a type of dependency helps us in normalizing the database in their 3rd Normal Form (3NF).
Example
<Show_Telecast>
| Show_ID | Telecast_ID | Telecast_Type | CD_Cost ($) |
| F08 | S09 | Thriller | 50 |
| F03 | S05 | Romantic | 30 |
| F05 | S09 | Comedy | 20 |
The table above is not in its 3NF because it includes a transitive functional dependency.
Show_ID -> Telecast_ID
Telecast_ID -> Telecast_Type
Thus, the following has a transitive type of functional dependency.
Show_ID -> Telecast_Type
The statement given above states the relation <Show_Telecast> violates the 3NF (3rd Normal Form). If we want to remove this violation, then we have to split the tables for the removal of the transitive functional dependency.
<Show>
| Show_ID | Telecast_ID | CD_Cost ($) |
| F08 | S09 | 50 |
| F03 | S05 | 30 |
| F05 | S09 | 20 |
<Telecast>
| Telecast_ID | Telecast_Type |
| S09 | Thriller |
| S05 | Romantic |
| S09 | Comedy |
Now the above relation is in the Third Normal Form (3NF) of Normalization.
Practice Questions on Transitive Dependency in DBMS
1. Which of these anomalies is a result of transitive dependency in a database management system?
A. Deletion
B. Insertion
C. Modification
D. All of the mentioned
Answer: D. All of the mentioned
2. The 4th Natural Form is designed so as to cope up with:
A. Transitive dependency
B. Join dependency
C. Multi-valued dependency
D. None
Answer: A. Transitive dependency
3. For some of the relations, the changing of data can deliver some undesirable consequences known as:
A. Modification anomalies
B. Referential Integrity constraints
C. Transitive dependencies
D. Normal forms
Answer: C. Transitive dependencies
FAQs
What is transitive dependency in DBMS? Give an example.
The given functional dependency can only be transitive when it is formed indirectly by two FDs. For example,
P -> R happens to be a transitive dependency when the following functional dependencies hold true:
- P -> Q
- Q does not -> P
- Q -> R
What is a transitive and partial dependency?
Transitive dependency occurs when some non-key attribute determines some other attribute. On the other hand, partial dependency occurs when one primary key determines some other attribute/attributes.
What is a non-prime attribute?
A non-prime attribute refers to an attribute that does not happen to be a part of the prime key. Thus, if we follow the second normal form, every non-prime attribute must be fully functionally dependent on the prime key attribute.
What is a full dependency?
A functional dependency (full) refers to a state of database normalization that equates to the standard of normalization of the Second Normal Form. It means that it must meet the requirements of 1NF and all non-key attributes are fully functionally dependent on the primary key.
Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments