Jasmine Grover Content Strategy Manager
Content Strategy Manager
Transitive Dependency is formed when two Functional Dependencies are linked to each other in a way that forms a new Dependency. This indirectly formed new Functional Dependency is known as the Transitive Dependency.
- In the proceeding sections, we are going to explore more about Transitive Dependency for GATE Exam, its effects, and the solutions to resolving the problems occurring due to Transitive Dependency.
- In the Database Management System, the functionality of the system is greatly affected by certain factors. The presence and occurrence of Transitive Dependency Dependency is one of them.
| Table of Content |
Key Terms: Functional Dependency, Transitive Dependency, Determinant, Determiner, Dependent, Database Management System, Attribute, Non-attribute
Functional Dependency in DBMS- Brief Idea
[Click Here for Sample Questions]
When two attributes are related or connected to each other in such a way that the knowledge of one attribute can help us know the other attribute. This kind of relationship in the Database Management System of Computers is known as Functional Dependency. It usually occurs between a primary key and a non-attribute within a table.
A Functional Dependency is represented as:
Y → Z.
The left side of the arrow is known as the determinant and the right side is known as the dependent. Explore an example to gain a clearer understanding:
A table is created with a column each for Student ID and Student Name.
| Student ID | Student Name | Student's Percentage |
|---|---|---|
- It can be said that by knowing only the Student ID, one can easily know the Student Name.
- The student ID can be uniquely identified, and it can also help know about the Student Name.
- These two attributes of Student ID and Student Name are known as Transitive Dependency.
- This functional Dependency can be written as-
Student ID → Student Name
What is Transitive Dependency?
[Click Here for Sample Questions]
When there is an indirect relationship between two Functional Dependencies that results in the formation of a new Functional Dependency, it is known as Transitive Dependency. This also states that a dependent is indirectly dependent upon a determinant.
- So, if there is a relation R (X, Y, Z). The X, Y, and Z are the different attributes of the relation R. Now if X → Y and Y → Z, then it is possible that X → Z.
- The X → Z is the Transitive Dependency.
- A Transitive Dependency is also a Functional Dependency.
- All Transitive Dependencies are Functional Dependencies, but all Functional Dependencies are not Transitive Dependencies.
Understand the picture of Transitive Dependency with the help of an example:-
A table is created for a relation R, which is Famous Pop Singers. The table has been divided into 3 columns each corresponding to one attribute - Singer ID, Singer Name, Album Produced.
| Singer ID | Singer Name | Album Produced |
|---|---|---|
| S1 | Taylor Swift | Midnights |
| S2 | Ariana Grande | Positions |
| S3 | Selena Gomez | Rare |
From the above table, these are following Dependencies observed:
- Singer ID → Singer Name: It is a Functional Dependency as the knowledge of the Singer ID would conclude the knowing of Singer's Name. It is a Functional Dependency.
- Singer Name → Album Produced: The Singer's name is the determinant for the dependent Album Produced. If one knows the name of the singer he/she will know the name of the album as well. It is a Functional Dependency
- So, Singer ID→ Album Produced: Similarly, a new Dependency comes into play, which is a product of the previously mentioned two Dependencies.
- It simply implies that if one knows the Singer ID, then they'll eventually know the Album Produced by the Singer from the table.
- This kind of Functional Dependency is known as Transitive Dependency as it arises from two previously formed Functional Dependencies.
Also Read:
| Related Articles | ||
|---|---|---|
| Pipeline Hazards | Primary Memory | Relational Operators in C |
| Ring Topology | Recursive Function in C | Process in Operating System |
| Secondary Memory | PnP Full Form | Tree Topology |
The need to remove Transitive Dependency
[Click Here for Sample Questions]
There are two consequences of Transitive Dependency in Database Management Systems:
- Data Redundancy: The occurrence of Transitive Dependencies in the database of the computer system leads to the phenomenon of Data Redundancy. Data redundancy happens when the same piece of information happens to be present at more than one place in the Database. Data redundancy often produces misleading or unreliable information.
- Data Anomalies: The term Data Anomalies refer to the difficulty in the process of updation, insertion, and deletion of data for the given set of instructions. This is the other outcome of Transitive Dependency. Considering the above-mentioned example, due to the prevalence of Transitive Dependency.
- One cannot update new information for any single attribute (Example- Singer Name).
- Moreover, we cannot add additional information in a cell/ row.
- While deleting one data (e.g.- Singer's name- Taylor Swift) would cause the deletion of the entire set of data in that row.
How to Remove Transitive Dependency?
[Click Here for Sample Questions]
The above-mentioned problems arise for relationships that have one or more Transitive Dependencies. Such sets of instructions cannot be processed for Third Normal Form (3NF). To apply 3NF the information must be first modified to remove all the Transitive Dependencies.
This process of removal of Transitive Dependencies is known as Normalisation. Taking the sample example of the Table mentioned before to work to normalize it to eradicate any Transitive Dependency; split the table in such a way that Singer ID and Album Produced won't be a Transitive Dependency anymore.
Splitting of the table can be done in the following manner:-
Table (a)
| Singer ID | Singer Name |
|---|---|
| S1 | Taylor Swift |
| S2 | Ariana Grande |
| S3 | Selena Gomez |
Table (b)
| Singer Name | Album Produced |
|---|---|
| Taylor Swift | Midnights |
| Ariana Grande | Positions |
| Selena Gomez | Rare |
- Here the Determinant Singer ID is longer connected or concerned with the Dependent Album Produced. The segregation has been done in such a way.
- As it can be seen that the Singer ID is no longer a Transitive Determinant of the Album Produced.
- Thus, in the newly formed split Tables, there is no Transitive Dependency. Now, the normalization is completed and the Third Normal Form (3NF) can be easily applied to both tables.
Things to Remember
- A Functional Dependency in DBMS is when two attributes are related or connected to each other in such a way that the knowledge of one attribute can help us know the other attribute.
- Transitive Dependency is the indirect correlation existing between two Functional Dependencies.
- A Transitive Dependency is actually a Functional Dependency.
- All Transitive Dependencies are Functional Dependencies but all Functional Dependencies are not Transitive Dependencies.
- Transitive Dependency hinders the process of 3NF.
- Transitive Dependency causes the occurrence of Data Redundancy and Data Anomalies in the Database.
- Removing Transitive Dependencies from a set of information is known as Normalization.
- Usually, Normalization is done to separate the Determinant and the Determiner of a Transitive Dependency from each other.
Sample Questions
Ques. A functional dependency is a relationship between or among (1 Mark)
(A) Entities
(B) Rows
(C) Attributes
(D) Tables
Ans. Option C
Explanation: When two or more attributes are related to each other, it is known as Functional Dependency.
Ques. Identify the functional dependency types that are not present in the following relationships. (1 Mark)
Empno -> EName, Salary, Deptno, DName
DeptNo -> DName
EmpNo -> DName
(A) Full functional dependency
(B) Partial functional dependency
(C) Transitive functional dependency
(D) Both B and C
Ans. Option B
Explanation:
- Empno -> EName, Salary, Deptno, DName, and EmpNo -> DName are Functional Dependencies.
- DeptNo -> DName is Transitive Dependencies.
- Thus, there is no Partial functional dependency on the given problem.
Ques. Identify the functional dependency types that are not present in the following relationships. (1 Mark)
StaffNo, BranchNo -> StaffName, BranchName, Position, DOB
StaffNo -> StaffName, Position, DOB
BranchNo -> BranchName
A. Full functional dependency
B. Partial functional dependency
C. Transitive functional dependency
D. Both B and C
Ans. Option C
Explanation: All the Dependencies are Functional Dependencies. Hence, there is no Transitive Dependency
Ques. Due to _______, the database design restricts the storage of certain data. (1 Mark)
(A) Deletion anomalies
(B) Insertion anomalies
(C) Update anomalies
(D) Selection anomalies
Ans. Option B
Explanation: The database prevents the storage of data because of Insertion Anomalies.
Ques. A relation is in 2NF if: (1 Mark)
(A) The candidate key fully determines all the values of non-key attributes.
(B) If any non-key attribute relies on only a portion of the candidate key, it must be transferred to a different relation where the partial key becomes the complete key.
(C) It is a requirement that it should already be in the 1NF.
(D) All of the above.
Ans. Option D
Explanation: All the options are correct.
Ques. When there is a chain of dependencies where one attribute determines the second attribute, and the second attribute determines the third attribute, the relation cannot be: (1 Mark)
(A) Well-structured
(B) 1NF
(C) 2NF
(D) 3NF
Ans. Option D
Explanation: If one attribute is a determinant of the second, which in turn is a determinant of the third, then it is a case of Transitive Dependency, and the Third Normal Form (3NF) cannot be applied to a set of instructions having Transitive Dependency.
Ques. See the relation Sale: (Date, Customer, Product, SalesRep, Vendor, VendorCity) (1 Mark)
The composite candidate key is {Date, Customer, Product}, and the given functional dependencies are as follows:
Vendor -> VendorCity,
Product -> Vendor
In what normal form is the sale relation at its highest level?
(A) 0NF
(B) 1NF
(C) 2NF
(D) 3NF
Ans. Option B
Explanation: As {Date, Customer, Product} is the composite candidate key and Vendor -> VendorCity & Product -> Vendor are Transitive Dependencies, the highest Normal form would be 3NF.
Ques. Which of the following statement(s) is/are FALSE about OLAP? (1 Mark)
(A) OLAP involves long-running slow transactions that read a lot of data
(B) OLAP frequently entails insertions, updates, and deletions
(C) For optimal processing, OLAP necessitates data to be in a De-Normalized form
(D) Both B and C
Ans. Option B
Explanation: OLAP does not involve any frequent insert, update and delete operations. Hence, option B is wrong.
Ques. 4NF is designed to cope with the following: (1 Mark)
(A) Transitive dependency
(B) Join dependency
(C) Multi-valued dependency
(D) None of these
Ans. Option C
Explanation: Multi-valued Dependency can be processed by 4NF.
Ques. Differentiate between Functional Dependency and Transitive Dependency in the Context of Database Management Systems (DBMS). Provide examples to illustrate each type of dependency. (3 Marks)
Ans. Functional Dependency:
- Functional Dependency in a database table signifies a direct association between two attributes, where the value of one attribute acts as a determinant for another.
- It is represented as X → Y, where X is the determinant and Y is the dependent attribute.
- Example: Consider a table with attributes "Employee ID" and "Employee Name." Here, Employee ID → Employee Name, as knowing the Employee ID uniquely identifies the Employee Name.
Transitive Dependency:
- Transitive Dependency is an indirect relationship between two Functional Dependencies that results in the formation of a new Dependency.
- It occurs when X → Y and Y → Z, leading to the inference that X → Z.
- Example: In a table with attributes "Employee ID," "Employee Name," and "Department," we have the following Functional Dependencies: Employee ID → Employee Name and Employee Name → Department. This results in a Transitive Dependency: Employee ID → Department.
Ques. Explain the significance of removing Transitive Dependency in the process of database normalization. (3 Marks)
Ans. Removing Transitive Dependency is a crucial step in the process of database normalization to achieve higher data integrity and reduce data redundancy. By eliminating Transitive Dependency:
- Data Redundancy is reduced: Transitive Dependency often leads to duplicated information, causing data redundancy. Normalization helps in breaking down tables into smaller, non-redundant forms, leading to more efficient data storage and retrieval.
- Data Anomalies are minimized: Transitive Dependency can cause data anomalies during data manipulation (insertion, deletion, or updating). By resolving Transitive Dependency, we ensure that data operations are smooth and do not lead to inconsistencies or errors.
- Third Normal Form (3NF) is achieved: Removing Transitive Dependency is a step towards achieving 3NF, which is a higher level of database normalization. 3NF ensures that each non-key attribute is fully dependent on the primary key, and there are no partial or transitive dependencies.
For Latest Updates on Upcoming Board Exams, Click Here: https://t.me/class_10_12_board_updates
Check-Out:
Comments