What does Third Normal Form (3NF) require, and what is a transitive dependency?

## Terms first A **functional dependency** X to Y means any two rows agreeing on X must agree on Y. A **candidate key** is a minimal attribute set determining all others; a **superkey** is any set containing a candidate key. A **prime attribute** belongs to some candidate key; all others are **non-prime**. ## The informal rule A relation is in Third Normal Form when it is in Second Normal Form and no non-prime attribute depends transitively on a candidate key. Transitive dependency means there is a chain - key to A, and - A to B, where A is not a superkey and B is non-prime and not part of A. The key determines B, but only *through* A. The memorable phrasing is: every non-key attribute must depend on the key, the whole key, and nothing but the key. 2NF supplies "the whole key"; 3NF supplies "nothing but the key". ## The formal rule For every non-trivial functional dependency X to A in the relation, at least one must hold: 1. X is a superkey, or 2. A is a prime attribute. Clause 1 alone would give you Boyce-Codd Normal Form. Clause 2 is the concession that makes 3NF strictly weaker than BCNF and, importantly, always achievable while preserving all dependencies. ## The canonical example `EMPLOYEE(emp_id, emp_name, dept_id, dept_name, dept_location)`, key `emp_id`. Dependencies: `emp_id` to everything; `dept_id` to `dept_name`, `dept_location`. `dept_id` is not a superkey (many employees share a department) and `dept_name` is non-prime, so both clauses fail: 3NF is violated. Note the relation *is* in 2NF, because the key is a single attribute and no partial dependency is possible. This is exactly why 3NF matters even in schemas full of surrogate ids. The damage: - **Redundancy.** `dept_name` and `dept_location` are stored once per employee, not once per department. - **Update anomaly.** Relocating a department requires updating every employee row. Half-applied, the table holds two locations for one `dept_id`, and no constraint detects it. - **Insertion anomaly.** A new department with no employees yet cannot be recorded, because `emp_id` is the key and cannot be null. - **Deletion anomaly.** Removing the last employee of a department deletes the only record of its name and location. **Decomposition:** `DEPARTMENT(dept_id, dept_name, dept_location)` and `EMPLOYEE(emp_id, emp_name, dept_id)` with a foreign key. Each fact now has exactly one home row; the join back is lossless because the shared column is the key of `DEPARTMENT`. ## Distinguishing 2NF from 3NF Both remove redundancy caused by a misplaced fact, but they differ in the determinant: - **Partial (2NF):** determinant is a *proper subset of a candidate key*. Requires a composite key. `order_id` to `order_date` inside `ORDER_LINE(order_id, product_id, ...)`. - **Transitive (3NF):** determinant is a *non-key attribute*. Can happen with any key arity, including a single surrogate id. A quick diagnostic: for each functional dependency you can state, look at the left-hand side. Part of a key means 2NF problem. Neither a key nor part of one means 3NF problem. ## Chained example `ORDER(order_id, customer_id, customer_email, customer_city, order_total)`. `customer_id` to `customer_email` and `customer_city` are transitive dependencies on `order_id`. Extract `CUSTOMER(customer_id, customer_email, customer_city)`. If `customer_city` in turn determines a `region`, that is another transitive dependency inside `CUSTOMER`, so you extract again. Normalization is iterative: after each decomposition, re-derive the dependencies of the resulting relations and re-check. ## What 3NF does not do - It does not remove derived columns. `line_total = qty * price` has no functional-dependency violation in the 3NF sense; the argument against it is computability, not normalization. - It does not eliminate all anomalies. A relation can be in 3NF and still violate BCNF when a prime attribute is determined by a non-superkey, which is precisely what clause 2 above tolerates. - It does not decide whether to denormalize for read performance. That is a separate cost decision, and it is easier to make deliberately from a 3NF starting point than to reverse-engineer from a wide table. ## Why 3NF is the practical target For transactional schemas, 3NF is the default stopping point because it removes the redundancy that causes update anomalies, it can always be reached by a decomposition that is both lossless and dependency-preserving, and the remaining schema is stable under change: adding a new fact about a department means adding a column to one table, not backfilling millions of rows.