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. 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.

  1. 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.