1NF, 2NF, 3NF and BCNF

In one line

1NF: atomic values, no repeating groups. 2NF: no partial dependencies on a composite key. 3NF: no transitive dependencies from the key to non-key attributes. BCNF: every determinant is a candidate key. Each step removes a kind of redundancy and the update, insert and delete anomalies it causes.

0/5 · 0%

Think of it like this

A school keeps one giant spreadsheet with a row per student per course, repeating the teacher's phone number on every row. Change the phone and you must fix hundreds of rows (update anomaly); you can't record a new course until someone enrols (insert anomaly); remove the last student and the course disappears (delete anomaly).

Key ideas

01
1NF: each column holds one value of one type, no lists (phones = '98..., 99...') and no repeating columns (phone1, phone2, phone3). Move repeating data to a child table.
02
2NF (only matters with composite keys): every non-key attribute depends on the whole key. Move product_name out of order_item(order_id, product_id, ...) into product.
03
3NF: no non-key attribute depends on another non-key attribute. Move customer_city out of orders into customer. Codd's summary: every non-key attribute depends on "the key, the whole key, and nothing but the key".
04
BCNF: for every dependency X → Y, X is a super key. It differs from 3NF only when there are overlapping candidate keys. Classic case: (student, subject) → teacher and teacher → subject: teacher is a determinant but not a key.
05
Most OLTP schemas aim for 3NF/BCNF by default. Going further (4NF, 5NF) matters for independent multivalued facts (Topic 3.3); going back (denormalizing) is a measured performance decision (Topic 3.4).

Code & diagrams

normalize-orders.sqlsql

-- Before: one wide table with repeated facts
-- orders_flat(order_id, order_date, customer_id, customer_name, customer_city,
--             product_id, product_name, unit_price, qty)

-- After (3NF / BCNF)
CREATE TABLE customer (id bigint PRIMARY KEY, name text NOT NULL, city text);
CREATE TABLE product  (id bigint PRIMARY KEY, name text NOT NULL, list_price numeric(10,2) NOT NULL);
CREATE TABLE orders   (id bigint PRIMARY KEY,
                       customer_id bigint NOT NULL REFERENCES customer(id),
                       order_date date NOT NULL);
CREATE TABLE order_item (
  order_id   bigint NOT NULL REFERENCES orders(id),
  product_id bigint NOT NULL REFERENCES product(id),
  qty        int NOT NULL CHECK (qty > 0),
  unit_price numeric(10,2) NOT NULL,   -- price AT ORDER TIME: a different fact from list_price
  PRIMARY KEY (order_id, product_id)
);

bcnf-example.txttext

teaching(student, subject, teacher)
  student, subject -> teacher     (each student has one teacher per subject)
  teacher -> subject              (each teacher teaches one subject)

Candidate keys: {student, subject}, {student, teacher}
3NF: yes (subject is prime, part of a candidate key)
BCNF: no (teacher is a determinant but not a super key)

Decompose: teacher_subject(teacher PK, subject), student_teacher(student, teacher)
Trade-off: "one teacher per student per subject" is no longer a single-table constraint.

Interview problem

The problem

Normalize a university spreadsheet

registrations(student_id, student_name, dept_id, dept_name, course_id, course_title, credits, semester, grade) where a student belongs to one department. Normalize to 3NF and name each anomaly the original suffers.

When it breaks

Copying current price into reports instead of order lines

What you see

Historical invoices change when a product's price changes, because order lines stored only product_id and joined to the current price.

Fix & prevent

Recognise that price-at-order-time is a different fact and store it on the order line. That's correct normalization, not denormalization.

Explain it without notes

Give an example that is in 3NF but not BCNF.

Practice

Is employee(emp_id, name, dept_id, dept_manager_id) in 3NF? Fix it if not.

Trade-offs

↔
Higher normal forms remove redundancy and anomalies at the cost of more joins; BCNF can sacrifice dependency preservation.

Done when you can

I can normalize a flat table to 3NF/BCNF and name the anomalies removed.

Command Palette