employee
| emp_id | name | dept_id | dept_name | dept_city |
|---|---|---|---|---|
| 1 | Mo Ali | D1 | Billing | Toronto |
| 2 | Kim Ode | D1 | Billing | Toronto |
| 3 | Lea Roy | D2 | Search | Ottawa |
The department facts depend on the key only through dept_id:
text
emp_id → dept_id → dept_name, dept_cityThis is a : the key determines X, X determines A, and X is not a superkey.
A table is in (3NF) when, for every nontrivial FD X → A:
- X is a superkey, or
- A is a prime attribute (part of some candidate key).
A shorter form for exams: every non-key attribute depends on "the key, the whole key, and nothing but the key". 2NF covers "the whole key"; 3NF adds "nothing but the key".
The fix
Move the attributes of the transitive dependency to a table keyed by its middle attribute:
employeeemp_id, name, dept_id
dept_id references
departmentdept_id, dept_name, dept_city
Now a department that moves to a new city is one UPDATE of one row.