Normalization

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

Normalization is the process of organizing a relational schema’s columns and tables to minimize redundancy and the update anomalies redundancy causes — the same fact stored in more than one row, that can drift out of sync when only one copy is updated. Each normal form is a stricter set of rules than the one before it; this page works through First, Second, and Third Normal Form, plus Boyce-Codd Normal Form, applying each in turn to one running example schema, then closes with the trade-off against deliberately denormalizing a schema back down.

Functional Dependencies

Every normal form beyond 1NF is defined in terms of functional dependencies. Column (or set of columns) X functionally determines column Y — written X → Y — if, for any two rows that agree on X, they must also agree on Y. A primary key functionally determines every other column in its table by definition: two rows can never share a primary key value, so the condition holds trivially. The interesting dependencies are the ones not implied by the primary key — those are exactly what the normal forms below are looking for.

First Normal Form (1NF)

A relation is in 1NF when every column holds a single, atomic value — no repeating groups (a list of values crammed into one column or a numbered run of similar columns like course_1, course_2, course_3) and no nested tables inside a cell.

Table 1. Violates 1NF — a repeating group of courses in one column
student_id	student_name	courses
101	Ada Lovelace	MATH201, CS310
102	Alan Turing	CS310

The courses column packs a variable number of values into one field — there’s no way to write a plain WHERE course = 'CS310' predicate against it, and adding a course requires rewriting the whole string. Splitting the repeating group into its own row per (student, course) pair fixes this:

Table 2. 1NF — one row per (student, course) pair
student_id	student_name	course_id
101	Ada Lovelace	MATH201
101	Ada Lovelace	CS310
102	Alan Turing	CS310

This is now 1NF, but student_name is now repeated once per course that student is enrolled in — exactly the kind of redundancy the next normal form addresses.

Second Normal Form (2NF)

A relation is in 2NF when it is in 1NF and every non-key column depends on the entire primary key, not just part of it. 2NF only has something to say when the primary key is composite (more than one column) — a table with a single-column primary key is automatically in 2NF if it’s in 1NF, since there’s no "part of the key" for a column to partially depend on.

Extend the 1NF table above with a course_title and a grade, using the composite primary key (student_id, course_id):

Table 3. Violates 2NF — `student_name` and `course_title` are partial dependencies
student_id	course_id	student_name	course_title	grade
101	MATH201	Ada Lovelace	Calculus I	A
101	CS310	Ada Lovelace	Databases	A-
102	CS310	Alan Turing	Databases	B+

student_name depends only on student_id (student_id → student_name), and course_title depends only on course_id (course_id → course_title) — neither depends on the full (student_id, course_id) key. This is a partial dependency, and it’s why "Ada Lovelace" and "Databases" are each written more than once: renaming Ada or retitling the Databases course means updating every row that happens to mention them, and a typo in one of those rows silently desynchronizes it from the rest.

The fix is to split off each partially-dependent column group into its own table, keyed on just the part of the composite key it actually depends on:

2NF — students and courses split out, enrollments keeps only what depends on the whole key

CREATE TABLE academic.students (
    student_id    INTEGER NOT NULL,
    student_name  CHARACTER VARYING(120) NOT NULL,
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);

CREATE TABLE academic.courses (
    course_id     CHARACTER VARYING(10) NOT NULL,
    course_title  CHARACTER VARYING(120) NOT NULL,
    CONSTRAINT pk_courses PRIMARY KEY (course_id)
);

CREATE TABLE academic.enrollments (
    student_id  INTEGER NOT NULL,
    course_id   CHARACTER VARYING(10) NOT NULL,
    grade       CHARACTER VARYING(2),
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id)
        REFERENCES academic.students (student_id),
    CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id)
        REFERENCES academic.courses (course_id)
);

grade stays in enrollments because it genuinely depends on the combination of student and course — a different student in the same course, or the same student in a different course, can have a different grade.

Third Normal Form (3NF)

A relation is in 3NF when it is in 2NF and no non-key column depends on another non-key column (a transitive dependency) — every non-key column must depend on the primary key directly, not indirectly through some other non-key column.

Suppose courses above is extended with the instructor teaching it and that instructor’s office number:

Table 4. Violates 3NF — `instructor_office` transitively depends on `course_id` through `instructor`
course_id	course_title	instructor	instructor_office
MATH201	Calculus I	Dr. Hopper	E-204
CS310	Databases	Dr. Codd	E-118
CS410	Distributed Systems	Dr. Codd	E-118

instructor_office doesn’t really depend on course_id — it depends on which instructor teaches the course (instructor → instructor_office), and course_id only determines it transitively, via instructor (course_id → instructor → instructor_office). The redundancy shows up exactly where instructor repeats: Dr. Codd’s office is written once per course he teaches, and moving his office means updating every one of those rows.

The fix, as with 2NF, is to split the transitively-dependent column into its own table, keyed on the column it actually depends on:

CREATE TABLE academic.instructors (
    instructor_name    CHARACTER VARYING(120) NOT NULL,
    instructor_office  CHARACTER VARYING(10) NOT NULL,
    CONSTRAINT pk_instructors PRIMARY KEY (instructor_name)
);

CREATE TABLE academic.courses (
    course_id         CHARACTER VARYING(10) NOT NULL,
    course_title      CHARACTER VARYING(120) NOT NULL,
    instructor_name   CHARACTER VARYING(120) NOT NULL,
    CONSTRAINT pk_courses PRIMARY KEY (course_id),
    CONSTRAINT fk_courses_instructor FOREIGN KEY (instructor_name)
        REFERENCES academic.instructors (instructor_name)
);

courses.instructor_name is now a foreign key referencing the fact (the office), rather than repeating it — instructor_office exists in exactly one row per instructor, however many courses that instructor teaches.

Boyce-Codd Normal Form (BCNF)

BCNF tightens 3NF’s rule: for every functional dependency X → Y in the relation, X must be a candidate key (a minimal set of columns that uniquely identifies a row — the primary key is one candidate key, but a table can have others). 3NF allows a narrow exception BCNF does not: a non-key determinant is tolerated in 3NF as long as everything it determines is itself part of some candidate key. That gap only matters when a table has overlapping composite candidate keys, which is uncommon enough that most 3NF schemas are already in BCNF — but when it does occur, it reintroduces the same kind of redundancy the earlier normal forms removed.

Suppose each instructor teaches exactly one course (a constraint this particular example schema adds), tracked in a single table with students, the course each is enrolled in, and that course’s instructor:

Table 5. Violates BCNF — `instructor` is a determinant but not a candidate key
student_id	course_id	instructor
101	CS310	Dr. Codd
102	CS310	Dr. Codd
103	MATH201	Dr. Hopper

This table is in 3NF: the primary key is (student_id, course_id), and instructor depends on the whole key (different students in the same course share the same instructor, but instructor isn’t determined by student_id alone). However, because each instructor teaches only one course, instructor → course_id also holds — and instructor alone is not a candidate key (it doesn’t determine student_id). This determinant that isn’t a candidate key is exactly what BCNF forbids, and the redundancy is visible: Dr. Codd and CS310 are paired in every row for every student in that course, and the pairing could — via an update anomaly — become inconsistent (one row edited to say Dr. Codd teaches CS410 while every other row still says CS310).

The fix decomposes along the offending dependency, same as before — one table keyed on the determinant (instructor → course_id), and one table recording only what still depends on the full original key:

CREATE TABLE academic.instructor_courses (
    instructor_name  CHARACTER VARYING(120) NOT NULL,
    course_id        CHARACTER VARYING(10) NOT NULL,
    CONSTRAINT pk_instructor_courses PRIMARY KEY (instructor_name)
);

CREATE TABLE academic.student_instructors (
    student_id       INTEGER NOT NULL,
    instructor_name  CHARACTER VARYING(120) NOT NULL,
    CONSTRAINT pk_student_instructors PRIMARY KEY (student_id, instructor_name)
);

Progressive Decomposition, End to End

flowchart TB UNF["Un-normalized
students + repeating courses column"] ONE["1NF
enrollments(student_id, student_name, course_id, course_title, grade)"] TWO["2NF split
students(student_id, student_name)
courses(course_id, course_title)
enrollments(student_id, course_id, grade)"] THREE["3NF split
instructors(instructor_name, instructor_office)
courses(course_id, course_title, instructor_name)"] BCNF_STEP["BCNF split (single-instructor-per-course case)
instructor_courses(instructor_name, course_id)
student_instructors(student_id, instructor_name)"] UNF -->|"remove repeating group"| ONE ONE -->|"remove partial dependencies
on part of the composite key"| TWO TWO -->|"remove transitive dependencies
between non-key columns"| THREE THREE -->|"remove non-key determinants
(overlapping candidate keys)"| BCNF_STEP

Normalization vs. Denormalization

Each step above traded storage/write simplicity for read simplicity: a fully normalized schema stores every fact exactly once (cheap, safe updates) but requires more `JOIN`s (see Querying Data (SELECT)) to reassemble a complete picture, which costs query performance, especially at scale. Denormalization — deliberately reintroducing redundant columns or collapsing tables back together — is the opposite trade: fewer joins and faster reads, at the cost of reintroducing the update-anomaly risk normalization exists to eliminate.

A common middle ground is to keep the normalized schema as the system of record for writes, and maintain a deliberately denormalized copy (a reporting table, a materialized view, or a cache) for read-heavy paths where join cost matters more than write-time consistency risk. Whether to normalize fully, partially, or denormalize a given table is a judgment call based on that table’s actual read/write pattern — there is no normal form that is unconditionally "correct" to target.