Transitive Dependency

Definition

A transitive dependency exists when X -> Y and Y -> Z hold, and Z is not directly dependent on X except through Y (Y is not a candidate key, so this isn't just a normal key-to-key chain). In that situation, Z is said to be transitively dependent on X: X determines Z only indirectly, by way of Y.

In the Running Example

Consider the chain, restricted to the Course-side attributes:

CourseID -> InstructorID -> InstructorOffice

• CourseID -> InstructorID holds (C1 is taught by I1).
• InstructorID -> InstructorOffice holds (I1 sits in Room 204).
• Therefore CourseID -> InstructorOffice also holds, but only transitively — there is no direct, independent business rule "a course determines an office"; it's true purely because the course determines its instructor, and the instructor determines the office. InstructorID is not itself a candidate key of the whole relation (it doesn't determine the students or enrollment dates), so this qualifies as a genuine transitive dependency, not just a chain of keys.

The same applies to CourseID -> InstructorID -> InstructorName.

Why This Still Causes Anomalies (Even After 2NF)

Suppose the table had already been fixed for partial dependency (21.7) and instructor data was already moved into a Course(CourseID, CourseName, InstructorID, InstructorName, InstructorOffice) relation (2NF). Even in this new table, InstructorOffice is still only there because of InstructorID, which is only there because of CourseID — it's fully dependent on the whole key CourseID (a single-attribute key here, so 2NF is trivially satisfied), yet the transitive chain still causes:

• Update anomaly: changing Dr. Rao's office still means updating every course row he teaches (if he teaches C1 and, hypothetically, another course too).
• Insertion anomaly: still can't add Dr. Fernandez without a course.
• Redundancy: his office is still repeated once per course he teaches.

This is precisely why 3NF exists as a further refinement beyond 2NF — 2NF only removes partial dependency on the key; it says nothing about chains of dependency among the non-key attributes themselves.

The Fix (Preview of 21.14)

Split InstructorName and InstructorOffice out into their own Instructor(InstructorID, InstructorName, InstructorOffice) relation, leaving Course(CourseID, CourseName, InstructorID) with just a foreign key reference. Now InstructorOffice depends directly (non-transitively) on the key of the table it lives in.

Edge Cases

• A transitive dependency requires the middle attribute (Y, here InstructorID) to be a non-key (or at least, not a candidate key of the relation being examined) — if Y itself were a candidate key, X -> Y -> Z would just be a normal, harmless chain through an alternate key (this exact nuance is what separates 3NF from the stricter BCNF, see 21.15).
• Transitive dependency is about non-key -> non-key chains ultimately rooted in the key; it says nothing about the key-to-immediate-attribute FDs themselves.

Key Takeaways / Interview Angle

• Q: Define transitive dependency in one line. X determines Z only indirectly, via an intermediate non-key attribute Y (X -> Y -> Z), rather than directly.
• Q: Give the exact transitive dependency from the running example. CourseID -> InstructorID -> InstructorOffice, so InstructorOffice is transitively dependent on CourseID.
• Q: Which normal form removes it? 3NF (21.14) — defined as 2NF plus no transitive dependency on the key.