Normalization asks where each fact belongs so the same fact does not need conflicting updates in several rows. Its rules depend on functional dependencies and candidate keys, not only on how many tables you draw. This reference explains first, second, and third normal form, then the stricter Boyce-Codd normal form. You will inspect an enrollment table, separate course and student facts, and reason about a subtler instructor rule. The SQL examples use explicit keys and generic constraints. Normalization is a logical design method; indexes, historical snapshots, and performance decisions remain separate questions.
Start with dependencies and anomalies
A functional dependency X -> Y means each permitted value of X determines exactly one value of Y under the business rules. student_id determines student_name if that ID identifies one student's current name. The dependency is a rule, not a coincidence in a three-row sample. A candidate key minimally determines every attribute in the relation; a primary key is the selected candidate key used for implementation. When a course title repeats in every enrollment, changing it requires several updates and can leave contradictory titles. Deleting the last enrollment can also erase the only stored course fact. These update, insertion, and deletion anomalies motivate moving facts into relations where their determinants are keys.
First normal form avoids repeating groups
In ordinary relational design, each row has values of the declared domains rather than a list of unrelated repeating fields. A student row with session1, session2, and session3 columns cannot support an arbitrary number of enrollments cleanly. A comma-separated session-ID string is likewise difficult to constrain and query as relationships. Move each enrollment into a separate row with student and session identifiers. Be careful with simplistic 'atomic means no spaces' definitions: a name containing spaces can still be one value under its domain. JSON and array types exist in some engines, but storing relationships inside them trades away ordinary relational constraint and query behavior. Choose them deliberately for appropriate data, not to avoid modeling a many-to-many relationship.
Second normal form addresses partial dependencies
A relation is in 2NF when it is in 1NF and non-key facts do not depend on only part of a candidate key. Suppose enrollments has composite key (student_id, course_id), plus student_name, course_title, and grade. student_name depends on student_id alone; course_title depends on course_id alone. Those facts belong in students and courses. Grade belongs to the whole student-course association under the model. A single-column surrogate primary key does not magically erase dependencies involving other candidate keys. Analyze the actual business determinants rather than declaring every table with an id column normalized. After decomposition, foreign keys connect enrollments to the two parent relations, and joins can reconstruct the required report.
Third normal form addresses transitive dependencies
Suppose employees contains employee_id, department_id, and department_name. employee_id determines department_id, while department_id determines department_name. Storing the department name in every employee repeats a department fact through a transitive dependency. Move departments into their own relation and reference them from employees. A common informal description of 3NF says non-key attributes depend on the key, the whole key, and no other non-key fact. The formal rule also accounts for candidate-key attributes, so use the informal phrase as a starting aid rather than a substitute for analysis. Historical snapshots can be legitimate: an order's agreed delivery address describes a past event, while a customer's current address describes a different fact. They are not automatically the same dependency.
BCNF requires every determinant to be a superkey
Boyce-Codd normal form strengthens the dependency rule: every nontrivial functional dependency has a determinant that is a superkey. Consider teaching(student, course, instructor), with a rule that each instructor teaches exactly one course and a student has one instructor per course. Candidate keys can include (student, course) and (student, instructor). The dependency instructor -> course has a determinant that does not identify a teaching row by itself, so the relation violates BCNF. Separate instructor_course(instructor, course) and student_instructor(student, instructor). Decomposition must be evaluated for lossless joins and dependency preservation; BCNF decomposition can trade away direct enforcement of some original dependencies. Do not split tables mechanically without checking those properties and the business rules.
Use normalized design with practical discipline
Write each dependency and candidate key, then ask which anomalies the proposed decomposition removes. Verify the join reconstructs exactly the intended original facts without spurious combinations. Add keys and foreign keys so the implementation enforces the logical design. Normalization does not select every index, guarantee fast queries, or replace transactions. A reporting system may use denormalized read models, but their refresh and consistency policy must be explicit. Do not denormalize a write model before measuring a concrete performance need. Test changes to shared facts, inserting an entity before it has associations, and deleting the last association. A sound design can answer those operations without losing unrelated facts.
Practice task and worked solution
Analyze order_lines(order_id, product_id, product_name, quantity, customer_id, customer_name). State the candidate key under a one-product-line-per-order policy and list dependencies: product_id determines current product_name, order_id determines customer_id, and customer_id determines current customer_name. The solution separates products, customers, orders, and lines, while preserving quantity on the association. Ask whether product_name is a current name or a historical description before moving it; a deliberate order snapshot has different meaning. Identify partial and transitive dependencies and explain how the join reconstructs the report. Do not claim the surrogate line ID alone solves all normalization issues.
Continue learning
Review normalization, relationships, and constraints.
The PostgreSQL constraint guide documents the keys and references used to implement decomposed relations.
Normalization: 1NF, 2NF, 3NF and BCNF Examples
Each example shows the code, the output it produces and what each part does.
Separate course facts from enrollments
This generic fixture decomposes repeated course titles and student names into parent tables. IDs are explicit and foreign-key enforcement must be enabled.
The report reconstructs enrollment facts without storing the course title twice. A course-title change now updates one course row while both report rows reflect the new title.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id INTEGER PRIMARY KEY,
course_title VARCHAR(100) NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER NOT NULL REFERENCES students(student_id),
course_id INTEGER NOT NULL REFERENCES courses(course_id),
grade INTEGER,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Sam');
INSERT INTO courses VALUES (10, 'SQL Basics');
INSERT INTO enrollments VALUES (1, 10, 90), (2, 10, 80);
SELECT s.student_name, c.course_title, e.grade
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
ORDER BY s.student_id;student_name | course_title | grade
Ada | SQL Basics | 90
Sam | SQL Basics | 80Normalization: 1NF, 2NF, 3NF and BCNF FAQ
Does adding id make a table normalized?
No. An id gives each row an identity, but normalisation is about dependencies between columns: whether every fact is stored once, in the table it describes. A table with an id can still repeat a customer's city on every order row. Analyse all relevant dependencies.
Does 1NF forbid spaces in a name?
No. First normal form is about atomic values: one value per column per row, not a comma-separated list. A full name such as "Ana Silva" is fine if the application treats it as one value; domains define which values are meaningful.
Is all duplication wrong?
No. Historical facts, such as the price on an invoice, and derived read models, such as a reporting summary, can repeat values deliberately. They need their own semantics and refresh rules, but they aren't normalisation mistakes.
Is BCNF always the only choice?
No. Decomposing to BCNF can lose the ability to enforce some dependencies with simple constraints. Evaluate lossless joins and dependency preservation alongside the requirements; third normal form is often the practical target.
What is a functional dependency?
A rule that one set of columns determines another: knowing a product id tells you its name, so product_id → product_name. Normal forms are defined by which dependencies a table contains. Listing them, from the requirements, is the most reliable way to decide how to split tables.
Normalization: 1NF, 2NF, 3NF and BCNF Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
employee_id -> department_id -> department_name repeats department facts. What helps?
Normalization: 1NF, 2NF, 3NF and BCNF Quiz
2 questions to check what you learned.
What causes the 2NF issue in composite-key enrollments?
- A student name depends on student_id alone
- A grade depends on the whole student-course pair
- The table uses an integer surrogate ID
- A student name contains more than one word
What does BCNF require of a nontrivial dependency determinant?
- It must be the selected primary key specifically, excluding other superkeys
- It must be a superkey
- It must determine only one non-key column
- It must appear in every foreign-key declaration