Chapter 12 · Normalization and Practical Schema Design
First, Second, and Third Normal Forms
Normal forms are successive design tests. Each removes a specific class of dependency problem while preserving the facts and relationships the database must represent.
Learning outcomes
Apply first normal form to remove repeating groups and multi-valued columns.
Apply second normal form to eliminate partial dependencies on composite keys.
Apply third normal form to eliminate non-key determinants and transitive dependencies.
Check whether a decomposition is lossless and whether important dependencies remain enforceable.
Implement a normalized SQLite schema with keys, foreign keys, and domain constraints.
Normal forms as successive tests
Each stage assumes the previous stage and targets a more specific dependency problem.
| Form | Core test | Typical defect removed |
|---|---|---|
| 1NF | Every row-column intersection contains one value from the column domain. | Lists, repeating columns, and nested groups. |
| 2NF | Every non-prime attribute depends on the whole of every candidate key. | Facts depending on only part of a composite key. |
| 3NF | For every nontrivial X → A, X is a superkey or A is prime. | Transitive dependency through a non-key determinant. |
First normal form: choose a row grain
student_id | student_name | course_1 | course_2 | course_3101 | Ava Chen | SQL-101 | DB-201 | NULLstudent_id | student_name | course_codes101 | Ava Chen | SQL-101,DB-201Both designs hide multiple course facts inside one student row. In 1NF, each enrollment is represented by its own row and identified by the enrollment key.
CREATE TABLE enrollment_1nf ( student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, term_code TEXT NOT NULL, grade TEXT, PRIMARY KEY (student_id, course_id, term_code)) STRICT, WITHOUT ROWID;Reusable wide-table laboratory
This intentionally redundant relation stores student, course, instructor, offering, and enrollment facts together. It is useful for detecting dependencies and anomalies before decomposition.
DROP TABLE IF EXISTS enrollment_sheet;CREATE TABLE enrollment_sheet ( student_id INTEGER NOT NULL, student_name TEXT NOT NULL, student_email TEXT NOT NULL, course_id INTEGER NOT NULL, course_code TEXT NOT NULL, course_title TEXT NOT NULL, instructor_id INTEGER NOT NULL, instructor_name TEXT NOT NULL, term_code TEXT NOT NULL, grade TEXT, PRIMARY KEY (student_id, course_id, term_code)) STRICT;INSERT INTO enrollment_sheet VALUES(101, 'Ava Chen', 'ava@example.edu', 501, 'SQL-101', 'SQL Foundations', 31, 'Nadia Rahimi', '2026-S1', 'A'),(102, 'Liam Ortiz','liam@example.edu', 501, 'SQL-101', 'SQL Foundations', 31, 'Nadia Rahimi', '2026-S1', 'B'),(101, 'Ava Chen', 'ava@example.edu', 502, 'DB-201', 'Database Design', 32, 'Omar Haddad', '2026-S1', NULL),(103, 'Mina Park', 'mina@example.edu', 502, 'DB-201', 'Database Design', 32, 'Omar Haddad', '2026-S1', 'A-'),(103, 'Mina Park', 'mina@example.edu', 503, 'DE-220', 'Data Engineering', 31, 'Nadia Rahimi', '2026-S2', NULL);Second normal form: remove partial dependencies
The wide table key is (student_id, course_id, term_code). Student facts depend only on student_id; course facts depend only on course_id. Move each fact to the relation whose key fully determines it.
student(student_id, student_name, student_email)course(course_id, course_code, course_title)enrollment(student_id, course_id, term_code, instructor_id, instructor_name, grade)A relation whose only candidate key has one attribute cannot have a dependency on a proper subset of that key. It is therefore automatically in 2NF, though it may still violate 3NF.
Third normal form: remove transitive dependencies
In the 2NF enrollment relation, the offering identity determines an instructor, and instructor_id determines instructor_name. Store the instructor fact once and reference it.
student(student_id, student_name, student_email)instructor(instructor_id, instructor_name)course(course_id, course_code, course_title)course_offering(offering_id, course_id, instructor_id, term_code)enrollment(offering_id, student_id, grade)Reusable normalized target schema
This design separates facts by grain: one row per student, instructor, course, offering, and enrollment.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course_offering;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS student;CREATE TABLE student ( student_id INTEGER PRIMARY KEY, student_name TEXT NOT NULL, student_email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, instructor_name TEXT NOT NULL) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, course_code TEXT NOT NULL UNIQUE, course_title TEXT NOT NULL) STRICT;CREATE TABLE course_offering ( offering_id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL REFERENCES course(course_id), instructor_id INTEGER NOT NULL REFERENCES instructor(instructor_id), term_code TEXT NOT NULL, room_code TEXT, UNIQUE (course_id, term_code)) STRICT;CREATE TABLE enrollment ( offering_id INTEGER NOT NULL REFERENCES course_offering(offering_id) ON DELETE CASCADE, student_id INTEGER NOT NULL REFERENCES student(student_id) ON DELETE CASCADE, grade TEXT CHECK (grade IS NULL OR grade IN ('A','A-','B+','B','B-','C+','C','D','F')), PRIMARY KEY (offering_id, student_id)) STRICT, WITHOUT ROWID;Populate the normalized design
INSERT INTO student VALUES(101, 'Ava Chen', 'ava@example.edu'),(102, 'Liam Ortiz', 'liam@example.edu'),(103, 'Mina Park', 'mina@example.edu');INSERT INTO instructor VALUES(31, 'Nadia Rahimi'),(32, 'Omar Haddad');INSERT INTO course VALUES(501, 'SQL-101', 'SQL Foundations'),(502, 'DB-201', 'Database Design'),(503, 'DE-220', 'Data Engineering');INSERT INTO course_offering(offering_id, course_id, instructor_id, term_code, room_code)VALUES(9001, 501, 31, '2026-S1', 'ONLINE'),(9002, 502, 32, '2026-S1', 'B-204'),(9003, 503, 31, '2026-S2', 'ONLINE');INSERT INTO enrollment VALUES(9001, 101, 'A'),(9001, 102, 'B'),(9002, 101, NULL),(9002, 103, 'A-'),(9003, 103, NULL);Reconstruct the original view
SELECT s.student_id, s.student_name, s.student_email, c.course_id, c.course_code, c.course_title, i.instructor_id, i.instructor_name, o.term_code, e.gradeFROM enrollment AS eJOIN student AS s ON s.student_id = e.student_idJOIN course_offering AS o ON o.offering_id = e.offering_idJOIN course AS c ON c.course_id = o.course_idJOIN instructor AS i ON i.instructor_id = o.instructor_idORDER BY s.student_id, c.course_id;A decomposition is useful only if valid original facts can be reconstructed without inventing spurious rows. Keys and foreign keys establish the join paths that make this reconstruction lossless.
Lossless join and dependency preservation
No information is invented or lost
Joining the decomposed relations on their shared keys reconstructs exactly the legal original relation.
Rules remain local
A dependency is preserved when it can be checked within one resulting relation without performing a join.
Shared attributes identify a side
For a binary decomposition, the common attributes should determine all attributes of at least one component.
Constraints implement theory
PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, and CHECK encode the selected dependencies and domains.
Normalization checklist
| Question | Action |
|---|---|
| What does one row represent? | Write the grain in one sentence before naming columns. |
| Can a column contain several values? | Create a child relation or relationship table. |
| Does a fact depend on only part of a composite key? | Move it to the relation identified by that determinant. |
| Does a non-key attribute determine another non-key attribute? | Create a relation for that determinant and reference it. |
| Can the original facts be reconstructed? | Prove the join path with keys and a verification query. |
Checkpoint
Classify the decomposition
- Why is a comma-separated course list not 1NF?
- Which dependency violates 2NF in the wide enrollment relation?
- Why does instructor_id → instructor_name motivate a 3NF table?
- What makes a decomposition lossless?
- Why is dependency preservation operationally useful?
Review the answers
A list stores several values in one attribute. student_id → student_name is partial relative to the composite key. Instructor names belong to the instructor determinant. A lossless decomposition reconstructs every legal original row without spurious rows. Preserved dependencies can be enforced locally with ordinary constraints.
Summary and references
- 1NF establishes atomic values at an explicit row grain.
- 2NF removes partial dependencies from composite-key relations.
- 3NF removes improper non-key determinants and transitive redundancy.
- Good decompositions are lossless and preferably dependency-preserving.
- Keys and constraints turn normalization decisions into enforceable design.