Skip to content
Quantoros

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

Related questions