What Is a Transitive Dependency?

A transitive dependency is an indirect functional dependency: if A → B and B → C, then A → C. In a common normalization example, a key determines a non-key attribute, which determines another non-key attribute. Storing those facts in one table can repeat data and create anomalies.

For example, an employee ID determines the employee's department ID, and the department ID determines its name. The department name therefore depends on the employee ID through the department ID. The important design question is whether department facts belong in every employee row.

Read the Arrow as “Determines”

A functional dependency X → Y means that any two valid rows with the same value of X must have the same value of Y. X is the determinant. It can contain one attribute or several attributes.

• The arrow does not mean “happens next,” “causes,” or “is calculated from.”
• The reverse dependency is not implied. One department can have many employees, so its ID does not determine an employee ID.
• Dependencies come from business rules for all valid data. A few unique values in a sample do not establish a candidate key.

Example: Employees and Departments

Assume EmployeeID is the only candidate key, each employee belongs to exactly one department, and a department can have many employees. Each DepartmentID determines one current DepartmentName; employee and department names need not be unique.

The relevant dependencies are:

EmployeeID → EmployeeName, DepartmentID
DepartmentID → DepartmentName

Therefore:
EmployeeID → DepartmentName

Transitive Dependency Diagram

Read the chain from left to right: an employee identifies a department; that department identifies its name. The repeated Sales cells show the consequence of storing a department fact once per employee. The diagram supplements the business rules rather than proving them.

Why This Table Violates 3NF

A superkey uniquely identifies a row. A candidate key is a minimal superkey. A prime attribute belongs to at least one candidate key; a non-prime attribute belongs to none.

The formal third normal form test is: for every nontrivial dependency X → A, either X is a superkey or A is prime. A dependency is nontrivial when A is not already included in X.

In DepartmentID → DepartmentName, DepartmentID is not a superkey of Employee because multiple employees can share it. DepartmentName is non-prime. The dependency therefore violates 3NF.

The table can still satisfy 2NF: it contains single values and its only candidate key is a single attribute, so there is no partial-key dependency. As Lesson 2 explained, removing partial dependencies does not remove this separate problem.

What Problems Does the Repetition Cause?

• Update anomaly: Renaming department 10 requires changing every employee row for that department. A missed row creates conflicting names.
• Insertion anomaly: The combined design cannot record a department independently before it has employees without inventing an employee row.
• Deletion anomaly: Deleting the last employee in a department removes the only stored department name.

Separate the Facts and Preserve the Relationship

Move DepartmentName to a Department table and keep DepartmentID in Employee:

Employee(EmployeeID PK, EmployeeName, DepartmentID FK)
Department(DepartmentID PK, DepartmentName)

Employee.DepartmentID references Department.DepartmentID

This is schema notation, not executable SQL. Department now stores one row for department 10 and its name, Sales. Both employee rows reference that record. Use a foreign-key constraint to enforce valid references and NOT NULL for the stated rule that every employee must have a department.

Joining on DepartmentID recovers the original information without extra row combinations because DepartmentID uniquely identifies a Department row. The stated dependencies can be enforced within their respective tables. Under these rules, with no additional dependencies, the two relations satisfy 3NF.

The business dependency has not disappeared. An employee still identifies a department and its name through a join. Normalization removes the repeated department fact from Employee; it does not erase the relationship between employees and departments.

Not Every Arrow Chain Is a 3NF Violation

Transitivity is a general inference rule. A chain alone is not enough to reject a design. If its intermediate determinant is a candidate key, it also uniquely identifies the row. For example, if EmployeeID and WorkEmail are both candidate keys, EmployeeID → WorkEmail → EmployeeName does not itself violate 3NF: WorkEmail is a superkey.

Likewise, the formal 3NF definition permits a non-superkey determinant when the dependent attribute is prime. This is why “non-key depends on non-key” is a useful introductory pattern, but not a complete test for relations with multiple candidate keys.

Apply the Same Reasoning to Item and Category

In the previous lesson, ItemNo → CategoryID → CategoryManager had the same structure. ItemNo identified an item, while CategoryID determined a category-level fact. Moving the manager into Category placed that fact with its determinant.

Confirm what the attributes mean before decomposing. A historical price paid on a particular sale, for example, is a transaction fact; it need not equal the product's current catalog price. Similar names do not establish a functional dependency.

A Practical Detection Checklist

1. State the business rules, including whether values represent current facts or history.
2. Identify all candidate keys, not only the chosen primary key.
3. List dependencies and trace indirect paths from keys through other determinants.
4. Apply the 3NF test to each nontrivial dependency; do not classify a table by arrows alone.
5. For a violation, propose separate relations and verify a lossless join, dependency preservation, and the required integrity constraints.

Check Your Understanding

Suppose BookID is the only candidate key of Book, many books share a PublisherID, and PublisherID → PublisherName. Where should the publisher name be stored?

Summary

A transitive dependency provides an indirect path from one determinant to another attribute. In the employee example, that path exposes department facts repeated in employee rows. Use the candidate keys and the formal 3NF rule to decide whether decomposition is needed. Continue with eliminating transitive dependencies for the next step.

Further reading: Database Design for Mere Mortals.