A transitive dependency occurs when a nonkey column depends on another nonkey column, which in turn depends on the table’s key. For example, employee_id determines department_id, and department_id determines department_name. The department name therefore depends on employee_id through department_id. section 6.7
Transitive dependencies create repeated data and can cause insertion, update, and deletion anomalies. A normalized table keeps columns directly dependent on its key and on nothing else. section 6.2 section 6.7
To remove a transitive dependency:
- Identify the dependency chain: key → nonkey attribute → another nonkey attribute. section 6.7
- Move the indirectly dependent attributes into a separate table whose primary key determines them. section 6.7
- Keep the determining key in the original table as a foreign key. section 6.7
- Check whether multiple relationships form an unnecessary cycle across tables. section 6.8
Employee and department data
Instead of repeating department_name in every employee row, create department(department_id, department_name) and employee(employee_id, department_id, ...). The department name is now dependent on department_id, while employee-specific columns depend directly on employee_id. section 6.7
A three-table cycle
A purchase table can contain payment_id and email while payment_method also links payment_id to user through email. Removing the direct email link from purchase can break the transitive dependency, though query speed and cost may affect the final design. section 6.8
Use the dependency diagram to trace which employee attributes depend directly on employee_id and which depend indirectly through department_id. section 6.7
