What is 3NF (Third Normal Form)?
SQLLevel: IntermediateDatabase Design
What is 3NF (Third Normal Form)?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
3NF requires 2NF plus: no transitive dependencies — every non-key column must depend on the primary key directly, not through another non-key column.
Example: if a table has employee_id, department_id, department_name — department_name depends on department_id, not on employee_id. This is a transitive dependency. Fix: move department_name to a separate departments table.
-- Violates 3NF: department_name depends on department_id, not employee_id
CREATE TABLE employees_bad (
employee_id INT PRIMARY KEY,
department_id INT,
department_name VARCHAR(100) -- transitive dependency
);
-- 3NF compliant
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
department_id INT REFERENCES departments(department_id)
);
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT