What anomalies arise due to transitive dependency? Discuss briefly.
Transitive Dependency
The transitive dependency is a type of functional dependency between two or more non-key attributes. Transitive dependency exists if a non-key attribute depends on any other non-key attribute. It means that a relation is not in 3NF if the value of a non-key attribute can be obtained by knowing the value of another non-key attribute.
Anomalies Due to Transitive Dependency
The following anomalies arise due to transitive dependency:
- Insertion anomaly
- Deletion Anomaly
- Modification anomaly
Suppose we have the following relation:
| CUSTNO | NAME | SALESMAN | REGION |
| 10 | Ahsan | Ahmad | South |
| 20 | Babar | Bashir | West |
| 30 | Chauhan | Ahmad | South |
| 40 | Daood | Khalid | East |
| 50 | Ehtasham | Bashir | West |
| 60 | Farooq | Munir | North |
Table 4.6: The SALES Relation
The above relation contains the following functional dependencies:
CUSTNO à NAME, SALESMAN
SALESMAN à REGION
The above relation is in 2NF because the primary key consists of single attribute. A transitive dependency exists in the relation. The REGION is functionally dependent on SALESMAN and SALESMAN is functionally dependent on CUSTNO. It means that REGION IS TRANSITIVELY DEPENDENT ON CUSTNO.
The transitive dependency create the following anomalies:
- 1. Insertion Anomaly
The insertion anomaly occurs when a new record is inserted in the relation. Suppose a new salesman “Abid” joins the company and is assigned to North region. The record of new salesman cannot be entered until a customer is also assigned to him because the primary key of the relation is CUSTNO.
- 2. Deletion Anomaly
The deletion anomaly occurs when a record is deleted from the relation. Suppose the record of CUSTNO 40 is to be deleted. It will also delete the information of the salesman “Khalid” who is assigned to East.
3. Modification Anomaly
The modification anomaly occurs when the record is updated in the relation. Suppose the salesman “Ahmad” is reassigned to East region from South. It requires to update all records in which “Ahmad” appears. It may result in inconsistent data in the relation.