Normalization from 1NF to 3NF: test the facts you separate
Work through enrollment data, functional dependencies, update anomalies, and the difference between live facts and historical snapshots.
Normalization is not an instruction to create as many tables as possible. It is a way to prevent one fact from being stored in several places. Repetition causes three familiar failures: an update changes only some copies, a new fact cannot be inserted without an unrelated one, or deleting the last related row accidentally deletes useful information.
First normal form means each value is atomic and repeating groups are rows, not a growing set of columns such as product_1, product_2, and product_3. Second normal form requires non-prime attributes to depend on each complete candidate key rather than on a proper subset. A single-column surrogate primary key does not remove a dependency on part of another composite candidate key. In an enrollment keyed by student and course, the student name belongs to students and the course instructor belongs to courses.
Third normal form removes dependencies between non-key attributes. If employee determines department code and department code determines department location, location belongs to departments rather than being copied into every employee row. Start from a normalized model; introduce denormalized values only when a measured query need and a clear consistency strategy justify them.
A change request exposes the dependency
Suppose student S01 takes database design and operating systems. A flat enrollment table repeats the student's name on both rows. Updating only one row gives S01 two names. Splitting the table is justified by the rule student_id → name, rather than by how many columns happen to be present.
Keep students keyed by student id and enrollments keyed by student id plus course code. This example assumes one instructor per course. If instructors change by term, introduce a course offering identified by course and term; otherwise the simplified dependency is false.
CREATE TABLE students (
student_id VARCHAR(9) PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE courses (course_code VARCHAR(10) PRIMARY KEY);
CREATE TABLE enrollments (
student_id VARCHAR(9) NOT NULL,
course_code VARCHAR(10) NOT NULL,
grade VARCHAR(2),
PRIMARY KEY (student_id, course_code),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_code) REFERENCES courses(course_code)
);Verify updates, inserts, and deletes
Insert S01 and two courses, then create two enrollments. Change S01's name in students. A join should now return the new name for both enrollments. Insert S02 without an enrollment: the student must still be representable. Delete S01's last enrollment: the student must remain. Finally, try an enrollment for a missing student; the foreign key should reject it.
SELECT e.student_id, s.name, e.course_code
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
WHERE e.student_id = 'S01';A historical snapshot is a different fact
An order item's agreed price is not the product's current list price. Copying that price at checkout preserves a separate historical fact; it does not create two competing sources of the current catalog price. In contrast, duplicating the student's current name across enrollments does create competing copies. Write down which point in time each value describes before calling duplication an error.
Normalization also does not prove that every query is fast. Measure a representative query and inspect its plan before adding a cache or summary table. Document how the derived value is rebuilt and what happens when a write fails.
Reference and next step
Examples use simplified business rules for teaching. Check the database documentation for the exact enforcement behavior of your chosen engine.
Open the ecommerce sample in YourERD · Explore the sample models