Third Normal Form (3NF): eliminating transitive dependencies

Third Normal Form

A table is in Third Normal Form (3NF) when it is in 2NF and no non-key column depends on another non-key column. In other words: every non-key column must depend on the key, the whole key, and nothing but the key.

Detecting the Violation

-- Violates 3NF
CREATE TABLE employees_bad (
  emp_id      INT PRIMARY KEY,
  name        VARCHAR(100),
  dept_id     INT,
  dept_name   VARCHAR(100),  -- depends on dept_id, not emp_id
  dept_budget DECIMAL(12,2)  -- depends on dept_id, not emp_id
);

dept_name and dept_budget depend on dept_id, which depends on emp_id. That chain is a transitive dependency. If the department name changes, every employee row in that department needs updating.

The Fix

CREATE TABLE departments (
  dept_id     INT PRIMARY KEY AUTO_INCREMENT,
  dept_name   VARCHAR(100) NOT NULL,
  dept_budget DECIMAL(12,2)
);

CREATE TABLE employees (
  emp_id  INT PRIMARY KEY AUTO_INCREMENT,
  name    VARCHAR(100) NOT NULL,
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Department data lives in one place. An employee's department is represented by a single foreign key. No duplication, no update anomalies.