Schema Design — ER Diagrams & Normalization (1NF → BCNF)

Normalization is the discipline of structuring tables so that every fact lives in exactly one place. Each normal form — 1NF through BCNF — is just a precise way of saying "this column is in the wrong table," and getting it right kills the update, insertion, and deletion anomalies that silently corrupt data.

Intermediate schema-design normalization functional-dependency 1nf→bcnf anomalies Created 2026-05-31 · Updated 2026-05-31

Key Components

ER diagram: A design-time sketch of the domain before CREATE TABLE: entities (nouns → tables), attributes (adjectives → columns), and relationships (verbs → foreign keys or junction tables).
Cardinality: How many of each side a relationship connects — 1:1, 1:N, or M:N. It dictates where the foreign key goes: on the many side for 1:N, in a junction table for M:N.
Functional dependency (X → Y): "If you know X, you know exactly one Y." The engine of normalization: examine every dependency and check whether its determinant (left side) is a proper key.
The three anomalies: Update, insertion, and deletion bugs that all stem from one cause — facts about different things crammed into one table. Each normal form exists to eliminate one.
Normal forms (1NF → BCNF): A ladder of rules, each assuming the previous: atomic cells (1NF), no partial dependency (2NF), no transitive dependency (3NF), every determinant a superkey (BCNF).

Concrete Example

See the disease before the cure. Here is a single table that stores everything about course enrollments — student, course, and instructor — all crammed together:

enrollments_bad
student_id | student_name | course_id | course_title | instructor
1          | Abhishek     | CS101     | Databases    | Dr. Codd
1          | Abhishek     | CS102     | Networks     | Dr. Cerf
2          | Maria        | CS101     | Databases    | Dr. Codd

This shape has three structural bugs, and each normal form exists to kill exactly one of them:

Update anomaly — "Databases" is stored twice. Rename the course and you must update N rows; miss one and the data contradicts itself. The same fact lives in more than one place.

Insertion anomaly — you can't add a new course (CS103) until a student enrolls, because there's no row to put it in without a student_id. Forced to invent fake data to record a real fact.

Deletion anomaly — if Maria (the only CS101 student) drops it, deleting her row also erases that CS101 exists and who teaches it. Deleting one fact destroys an unrelated one.

The cure is to split facts about different things — students, courses, enrollments — into their own tables, each keyed by what those facts actually depend on. A weak entity like an order line item, which has no identity of its own, borrows its parent's key:

CREATE TABLE order_items (
    order_id    BIGINT REFERENCES orders(id),
    line_no     INT,
    product_id  BIGINT REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_no)   -- identity borrowed from the parent
);

The composite primary key (order_id, line_no) says a line item is only meaningful inside its order — exactly what "weak entity" means.

William Kent's mnemonic covers 1NF→3NF: every non-key attribute must depend on
the key (1NF) · the whole key (2NF) · nothing but the key (3NF).

Visual Model

Think of normalization as walking one messy table through a series of checkpoints. At each checkpoint you ask a single question — "does every column depend on the key, the whole key, and nothing but the key?" — and whenever a column fails, you carve it off into a new table keyed by whatever it actually depends on. Step through the transformation below: watch the redundant columns peel away one normal form at a time.

Step 1 of 5

1NF — atomic values. A cell like phone = "555-1234, 555-9999" can't be indexed, JOINed, or constrained. Fix: one row per number in a separate phones table.

Deeper — Edge Cases & Gotchas

3NF is the sweet spot. Production schemas target 3NF, occasionally BCNF. 4NF and 5NF are mostly academic — you will rarely justify them in a real design.

BCNF vs 3NF — the loophole. 3NF permits a dependency X → Y when Y is a prime attribute (part of some candidate key) even if X is not a superkey. BCNF closes this: every determinant must be a superkey, no exceptions. It only bites when a table has multiple overlapping composite candidate keys — rare in practice, common in interviews.

Anti-pattern: caching a derived value into a normalized table without owning its synchronization — "I'll just store dept_name next to dept_id so I avoid a JOIN."

employees
employee_id | name  | dept_id | dept_name
1           | Asha  | 7       | Sales
2           | Ben   | 7       | Sales      ← same fact, second copy

This re-introduces the transitive dependency employee_id → dept_id → dept_name and with it the update anomaly: rename department 7 and you must touch every employee row in it. That can be a legitimate denormalization choice — but only when reads genuinely demand it and you've accepted the burden of keeping the copy in sync (e.g. via a trigger). Doing it by accident is just a 3NF violation.

"Atomic" is judgment, not law. Is address one column or five? It depends on whether you ever query by city. Postgres arrays and JSONB deliberately relax 1NF — that's a denormalization choice, not a mistake. Normalization serves your access patterns, not the reverse.

Interview trap — name the violation, not just the fix. "That's a transitive dependency, breaks 3NF." "Partial dependency on a composite key, breaks 2NF." Knowing how to split the table isn't enough; you have to name the disease.

Test Yourself

A books table holds (book_id, author_id, author_name) with one author per book. Which normal form is violated?

Why does 2NF only matter when a table has a composite primary key?

What's the one-sentence difference between 3NF and BCNF — and when does it actually bite?

Pick the update anomaly and explain how splitting the table eliminates it.