SQL Data Normalization: 1NF BCNF 3.5NF for Data Engineers

sql data normalization is the discipline of arranging a relational schema so that every fact is stored exactly once, and it is the single most-tested fundamental in a data-engineering interview because it is the thing that quietly decides whether a database stays correct as it grows. When the same customer address is copied into ten thousand order rows, one botched update leaves nine thousand nine hundred rows lying about where that customer lives — and no amount of downstream tooling can un-corrupt a source that contradicts itself. The normal forms — 1NF, 2NF, 3NF, and BCNF — are a formal recipe for eliminating that duplication step by step, each form removing a specific class of data redundancy and the anomalies redundancy causes, so that a schema which satisfies them cannot store the same fact in two places that can drift apart.

This guide is the walkthrough you wished existed the first time an interviewer said "explain 1NF 2NF 3NF to me," or "give me a table that is in 3NF but not BCNF," or "when would you deliberately denormalize?" It builds the whole ladder from the ground up: the three anomalies (insert, update, delete) that motivate database normalization, the functional dependency notation (X → Y, determinants, candidate keys) that makes each normal form precise instead of hand-wavy, the real-SQL decompositions that take a messy table up through each form, the 4NF/5NF forms above BCNF and why "3.5NF" — plain BCNF — is where almost every operational schema stops, and finally the deliberate denormalization you apply when the workload flips from transactional writes to analytical reads. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, model warehouse schemas on the dimensional-modeling practice library →, and rehearse schema trade-offs on the design practice library →.

---

On this page

• Why normalization matters: anomalies & redundancy
• 1NF & 2NF: atomic values and full-key dependency
• 3NF & BCNF: transitive dependencies and determinants
• Higher forms: 4NF, 5NF & 3.5NF in practice
• Denormalization for analytics: when to break the rules
• Cheat sheet — normalization recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why normalization matters: anomalies & redundancy

One fact, one place — the invariant every normal form is chasing

The one-sentence invariant: sql data normalization is the process of decomposing tables so that every non-trivial functional dependency has a whole candidate key on its left-hand side — which is a precise way of saying "store each fact exactly once, keyed by the thing it actually depends on" — and the reward for doing so is the elimination of the insert, update, and delete anomalies that a redundant schema is helpless against. Redundancy is not a cosmetic problem. The moment a value lives in two rows, those two rows can disagree, and a database that contradicts itself is worse than one that is merely slow: it is wrong, and it is wrong silently, until an auditor or a reconciliation job trips over the contradiction months later.

The three anomalies — the whole reason normal forms exist.

• Update anomaly. A fact stored redundantly must be updated in every copy at once. UPDATE orders SET customer_city = 'Berlin' WHERE customer_id = 42 has to touch every order row for that customer; miss the WHERE or run it in two transactions and the copies drift. The single fact "customer 42 lives in Berlin" now has two answers.
• Insertion anomaly. You cannot record a fact until an unrelated fact also exists. If customer address lives only inside the orders table, you cannot store a customer who has not yet placed an order — there is no row to put the address in. The schema forces you to invent placeholder rows or NULL-pad columns.
• Deletion anomaly. Deleting one fact accidentally destroys another. Delete the last order for a customer and, if the address lived in the order row, you have also deleted the only record that the customer exists. Two independent facts share one row's lifetime, so one cannot outlive the other.

Data redundancy is the root cause — normalization is the cure.

• Redundancy = the same fact in more than one place. Not the same value (two customers can both live in Berlin); the same fact (customer 42's city, copied into every order). Copies that must always agree but are stored separately are the disease.
• Normal forms attack redundancy in a fixed order. 1NF removes repeating groups and non-atomic cells; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies through non-key columns; BCNF closes the last determinant loophole. Each form is a strictly stronger condition than the one below it.
• The payoff is anomaly-freedom, not disk savings. People sometimes justify normalization as "saving space." The real payoff is correctness: a normalized schema cannot represent a contradiction, because the fact only exists in one row to begin with.

OLTP normalize, OLAP denormalize — the framing that makes the whole topic click.

• OLTP (transactional) systems normalize. Order entry, banking, inventory — many small writes, correctness-critical, concurrency-heavy. Normalization keeps writes cheap (touch one row per fact) and keeps the source of truth internally consistent.
• OLAP (analytical) systems denormalize. Dashboards, reports, ML features — few huge reads, join-averse, correctness delegated upstream. Star schemas and one-big-table trade controlled redundancy for scan speed (covered in section 5).
• The exam answer. "Normalize the system of record; denormalize the read models you derive from it." Both are correct engineering; the workload decides which.

What interviewers actually probe.

• Can you name the specific anomaly a redundant table exhibits — not just "it's bad," but "that's an update anomaly"? — required answer.
• Can you write the functional dependency that a normal form violates ({order_id} → customer_city is fine; customer_id → customer_city inside orders is the smell)? — senior signal.
• Do you decompose losslessly — can the original table be reconstructed by joining the pieces? — required answer.
• Do you know when to stop (BCNF for OLTP) and when to reverse (denormalize for OLAP)? — senior signal.

Worked example — one un-normalized table, all three anomalies

Detailed explanation. The fastest way to internalise the anomalies is to build a deliberately bad table and provoke each one. Consider a single flat sales table that jams customer facts, product facts, and order facts into one row. Watch how a single stored fact (the customer's city, the product's price) becomes un-updatable, un-insertable, and un-deletable in isolation.

• The bad design. sales(order_id, customer_id, customer_city, product_id, product_name, unit_price, qty) — one row per line item, with customer and product facts copied in.
• The redundancy. customer_city repeats for every order the customer places; product_name and unit_price repeat for every line item of that product.
• The consequence. Three distinct anomalies, each provable with one SQL statement.

Question. Given the flat sales table, demonstrate the update, insertion, and deletion anomaly, and name the redundancy that causes each.

Input.

order_id	customer_id	customer_city	product_id	product_name	unit_price	qty
1001	42	Berlin	7	Widget	500	2
1002	42	Berlin	9	Gadget	800	1
1003	51	Paris	7	Widget	500	4

Code.

-- The un-normalized table: customer + product facts copied into every line item
CREATE TABLE sales (
    order_id       BIGINT      NOT NULL,
    customer_id    BIGINT      NOT NULL,
    customer_city  TEXT        NOT NULL,   -- copied for every order of this customer
    product_id     BIGINT      NOT NULL,
    product_name   TEXT        NOT NULL,   -- copied for every line item of this product
    unit_price     BIGINT      NOT NULL,   -- copied for every line item of this product
    qty            INT         NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- UPDATE ANOMALY: customer 42 moves to Munich. The fact lives in many rows.
-- Forget one row (or run two separate statements) and the copies disagree.
UPDATE sales SET customer_city = 'Munich' WHERE customer_id = 42 AND order_id = 1001;
-- Row 1002 still says 'Berlin' -> the database now contradicts itself.

-- INSERTION ANOMALY: onboard customer 60 (Rome) who has NOT ordered yet.
-- There is no order row to hang the city on; you cannot store the fact.
INSERT INTO sales (order_id, customer_id, customer_city, product_id,
                   product_name, unit_price, qty)
VALUES (NULL, 60, 'Rome', NULL, NULL, NULL, NULL);  -- fails: NULLs in the PK

-- DELETION ANOMALY: order 1003 is cancelled and its row deleted.
-- That row was the ONLY place recording product 7's name/price in Paris context,
-- and the only trace that customer 51 exists.
DELETE FROM sales WHERE order_id = 1003;
-- Customer 51 and any Paris-only facts vanish along with the order.

Step-by-step explanation.

1. The primary key is (order_id, product_id) — the line item. Every non-line-item fact (customer_city, product_name, unit_price) is copied into each line item, which is the redundancy.
2. The update anomaly shows up the instant a copied fact changes. customer_city for customer 42 exists in every one of their order rows; a partial update leaves the copies inconsistent. The only safe update touches all rows, which is both slow and error-prone.
3. The insertion anomaly shows up when you try to store a fact that has no home. A customer with no orders has no row, so their city cannot be recorded without inventing a fake order or violating the NOT NULL primary-key columns.
4. The deletion anomaly shows up when deleting one fact takes another with it. Removing the last order for customer 51 also removes the only evidence that customer 51 exists. Two unrelated facts share one row's lifespan.
5. Every anomaly traces back to the same root: a fact that depends on customer_id (or product_id) is stored in a table keyed by order_id. The fix — decompose so each fact sits in a table keyed by what it depends on — is exactly what the normal forms formalise.

Output.

Anomaly	Trigger	Root-cause redundancy
Update	Change customer 42's city	customer_city copied per order
Insertion	Add a customer with no orders	customer facts only exist inside order rows
Deletion	Delete the last order of a customer	customer existence tied to an order row

Rule of thumb. If you can name a WHERE clause that must touch more than one row to change a single real-world fact, the table is redundant and at least one anomaly is present. Decompose until every fact changes in exactly one row.

Worked example — the normal-forms ladder overview

Detailed explanation. Interviewers love a candidate who can rattle off the ladder in order, each rung phrased as the one thing it removes. Memorise the ladder as a sequence of removals, not as abstract definitions — "1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies, BCNF removes non-key determinants." Each rung assumes the one below it.

• Cumulative. A table in 3NF is automatically in 2NF and 1NF. You climb the ladder; you never skip a rung.
• Each rung targets one dependency shape. The whole ladder is a march through kinds of functional dependency that cause redundancy.
• BCNF is the practical top. 4NF and 5NF exist (section 4) but are rarely needed; BCNF — informally "3.5NF" — is where operational schemas stop.

Question. Produce the one-line ladder: for each normal form, state the condition and the single redundancy it removes.

Input.

Rung	Informal condition	Dependency shape removed
1NF	atomic cells	repeating groups / arrays in a cell
2NF	full-key dependency	partial dependency on part of a composite key
3NF	no transitive dependency	non-key → non-key
BCNF	determinant = candidate key	non-key (or partial-key) determinant

Code.

The normal-forms ladder (climb in order; each rung assumes the one below)
========================================================================

1NF   Every cell holds a single atomic value; no repeating groups,
      no comma-lists, no arrays standing in for child rows.
        smell: "phone1, phone2, phone3" in one column.

2NF   1NF AND no non-key attribute depends on only PART of a
      composite candidate key.
        smell: (order_id, product_id) key, but product_name depends
               on product_id alone.

3NF   2NF AND no non-key attribute depends on another NON-KEY
      attribute (no transitive dependency key -> nonkey -> nonkey).
        smell: order_id -> zip -> city  (city depends on zip, not the key).

BCNF  For EVERY non-trivial functional dependency X -> Y, X is a
      candidate key. (a.k.a. "3.5NF": 3NF's last loophole closed.)
        smell: a non-key column that determines part of a candidate key.

4NF   BCNF AND no non-trivial multivalued dependency (independent
      multi-valued facts kept in separate tables).  [rarely needed]

5NF   4NF AND no non-trivial join dependency.        [very rarely needed]

Step-by-step explanation.

1. 1NF is about shape: one value per cell. A column holding "SQL, Python" or a phone1/phone2/phone3 triple is a repeating group masquerading as columns; 1NF pushes those into their own rows.
2. 2NF only bites when the key is composite. With key (order_id, product_id), an attribute that depends on product_id alone (like product_name) is a partial dependency; 2NF moves it to a products table keyed by product_id.
3. 3NF targets non-key chains: order_id → zip → city. city depends on zip, and zip is not a key, so city is transitively dependent; 3NF splits zip → city into its own table.
4. BCNF is the strict version of 3NF. Even a table with no transitive dependency can hide a determinant that is not a candidate key; BCNF requires the left side of every FD to be a candidate key. That is why it is nicknamed "3.5NF."
5. 4NF and 5NF sit above BCNF and handle multivalued and join dependencies. They matter for a small number of many-to-many designs and are covered in section 4; most schemas never reach for them.

Output.

Table state	Also satisfies	Still may violate
1NF	—	2NF, 3NF, BCNF
2NF	1NF	3NF, BCNF
3NF	1NF, 2NF	BCNF (rare edge case)
BCNF	1NF, 2NF, 3NF	4NF, 5NF (rarely relevant)

Rule of thumb. State each normal form as the one dependency it removes, in order. If you can say "1NF: atomic; 2NF: whole key; 3NF: no transitive; BCNF: determinant is a key" without pausing, you have the ladder cold.

Worked example — functional-dependency notation primer

Detailed explanation. Every normal form is defined in terms of functional dependencies, so fluency in the notation is what turns a vague answer into a precise one. A functional dependency X → Y reads "X determines Y": any two rows that agree on X must agree on Y. Learn to write the FDs of a table, and the normal-form violations become mechanical to spot.

• Determinant. The left side of an FD (X in X → Y) — the thing that "decides" the value on the right.
• Candidate key. A minimal set of attributes that functionally determines every other attribute (a determinant of the whole row, with nothing removable).
• Prime vs non-prime attribute. An attribute that is part of some candidate key is prime; everything else is non-prime. The normal-form definitions are stated in these terms.

Question. For the enrollment(student_id, course_id, student_name, course_title, grade) table, list the functional dependencies, find the candidate key, and classify each FD.

Input.

Attribute	Depends on
student_name	student_id
course_title	course_id
grade	(student_id, course_id)

Code.

Functional dependencies of enrollment(student_id, course_id,
                                      student_name, course_title, grade)
=======================================================================

FD1:  student_id              -> student_name        (partial: LHS is part of key)
FD2:  course_id               -> course_title        (partial: LHS is part of key)
FD3:  (student_id, course_id) -> grade               (full key -> non-prime)
FD4:  (student_id, course_id) -> student_name, course_title, grade   (whole row)

Candidate key:  {student_id, course_id}
  - determines every other attribute (FD4), and neither half alone does.

Prime attributes:      student_id, course_id      (in the candidate key)
Non-prime attributes:  student_name, course_title, grade

Reading X -> Y:  "any two rows equal on X are equal on Y."
Trivial FD:      X -> Y where Y is a subset of X (always holds; ignore).

Step-by-step explanation.

1. student_id → student_name: two enrollment rows with the same student have the same student name, so student_id determines student_name. This is a partial dependency because student_id is only half of the candidate key.
2. course_id → course_title: same logic on the course side — another partial dependency on half the key.
3. (student_id, course_id) → grade: the grade is specific to this student in this course; it needs the whole key. This is the only "good" (full-key) dependency in the table.
4. The candidate key is {student_id, course_id}: together they determine every column (FD4), and removing either half breaks that (a student has many courses, a course has many students), so the key is minimal.
5. Prime attributes are those in the candidate key (student_id, course_id); the rest are non-prime. FD1 and FD2 — non-prime attributes depending on part of the key — are exactly the 2NF violations section 2 will fix. Writing the FDs first makes the violation obvious before you touch any SQL.

Output.

FD	Left side is	Classification
student_id → student_name	part of key	partial dependency (2NF violation)
course_id → course_title	part of key	partial dependency (2NF violation)
(student_id, course_id) → grade	whole key	full functional dependency (good)

Rule of thumb. Before naming a normal-form violation, write the table's FDs and its candidate key. Every violation is just an FD whose left side is the wrong thing — part of a key (2NF), a non-key attribute (3NF), or a non-candidate-key determinant (BCNF).

Senior interview question on normalization fundamentals

A senior interviewer often opens with: "Here is a flat reporting table one of our analysts built to track subscriptions: subs(user_id, user_email, plan_id, plan_name, plan_price, signup_date). Users have exactly one current plan. Walk me through the anomalies this table can suffer, write out its functional dependencies, and take it to 3NF with real DDL — then tell me which anomalies survive and which are gone."

Solution Using functional-dependency analysis and a lossless 3NF decomposition

-- The analyst's flat table (redundant): plan facts copied into every user row
CREATE TABLE subs (
    user_id      BIGINT      PRIMARY KEY,
    user_email   TEXT        NOT NULL,
    plan_id      BIGINT      NOT NULL,
    plan_name    TEXT        NOT NULL,   -- copied for every user on this plan
    plan_price   BIGINT      NOT NULL,   -- copied for every user on this plan
    signup_date  DATE        NOT NULL
);

-- Functional dependencies:
--   user_id -> user_email, plan_id, signup_date   (key -> attributes)
--   plan_id -> plan_name, plan_price              (TRANSITIVE: nonkey -> nonkey)
-- The second FD is the 3NF violation: plan_name/plan_price depend on plan_id,
-- which is itself a non-key attribute of subs.

-- Lossless 3NF decomposition: split the transitively-dependent facts out.
CREATE TABLE plans (
    plan_id     BIGINT      PRIMARY KEY,
    plan_name   TEXT        NOT NULL,
    plan_price  BIGINT      NOT NULL
);

CREATE TABLE user_subscriptions (
    user_id      BIGINT      PRIMARY KEY,
    user_email   TEXT        NOT NULL,
    plan_id      BIGINT      NOT NULL REFERENCES plans(plan_id),
    signup_date  DATE        NOT NULL
);

-- Reconstruct the original view when a report truly needs the wide shape:
CREATE VIEW subs_wide AS
SELECT us.user_id, us.user_email, us.plan_id,
       p.plan_name, p.plan_price, us.signup_date
FROM   user_subscriptions us
JOIN   plans p ON p.plan_id = us.plan_id;

Step-by-step trace.

Step	Action	Result
1	Write FDs of subs	user_id → …; plan_id → plan_name, plan_price
2	Spot the transitive FD	plan_id is non-key but determines two columns
3	Create plans keyed by plan_id	plan facts now live once
4	Keep plan_id as FK in user_subscriptions	link preserved, no copied plan facts
5	Verify losslessness	join on plan_id rebuilds every original row

Starting from the flat subs table, the FD plan_id → plan_name, plan_price is a transitive dependency (user_id → plan_id → plan_name), which is the 3NF violation. Splitting plans out and leaving plan_id as a foreign key stores each plan's name and price exactly once; the subs_wide view rebuilds the original shape on demand, and because plan_id is a key of plans and a foreign key in user_subscriptions, the join is lossless — no row is invented or lost.

Output:

Anomaly	Before (flat subs)	After (3NF)
Update plan price	touch every user on the plan	one row in plans
Insert a plan nobody uses yet	impossible (no user row)	one plans row
Delete last user on a plan	plan facts vanish	plan survives in plans
Reconstruct wide report	already wide	JOIN / subs_wide view

Why this works — concept by concept:

• Functional dependency analysis — writing plan_id → plan_name, plan_price before touching SQL turns a fuzzy "this looks redundant" into a provable violation: the left side is a non-key attribute, so the FD is transitive and breaks 3NF.
• Transitive dependency removal — 3NF forbids key → non-key → non-key. Moving the tail of that chain (plan_name, plan_price) into a table keyed by its true determinant (plan_id) is the mechanical fix for every 3NF violation.
• Lossless-join decomposition — the split is safe precisely because the shared column plan_id is a candidate key of the new plans table. That guarantees user_subscriptions ⋈ plans reproduces subs exactly, with no spurious rows.
• Foreign key as the seam — plan_id REFERENCES plans(plan_id) both preserves the relationship and lets the database enforce that every subscription points at a real plan, replacing copied data with a validated link.
• Cost — one extra table and one join per wide read (O(1) index lookup on plan_id), in exchange for turning three anomalies into single-row operations. Writes to plan facts drop from O(users-on-plan) to O(1); the join cost is paid only by reads that actually need the wide shape, which is exactly what a view or the OLAP layer handles.

SQL
Topic — sql
SQL schema-design and anomaly problems

Practice →

Design Topic — design Design problems on relational modeling

Practice →

---

2. 1NF & 2NF: atomic values and full-key dependency

1NF makes cells atomic; 2NF makes every non-key column depend on the whole key

The mental model in one line: the first two rungs of sql data normalization fix shape and partial dependence — 1NF forbids a cell from holding a list or a repeating group so that every value is atomic and addressable by SQL, and 2NF (which only bites when the candidate key is composite) forbids a non-key attribute from depending on just part of that key. These are the rungs interviewers use to warm up, because a candidate who confuses "1NF violation" with "2NF violation" reveals they memorised names without understanding dependencies.

What 1NF actually requires.

• Atomic cells. Each cell holds one indivisible value. "555-1234, 555-5678" in a phones column is not atomic — SQL cannot index it, join on it, or aggregate it without string surgery.
• No repeating groups. phone1, phone2, phone3 columns are a repeating group dressed as separate columns. Adding a fourth phone means an ALTER TABLE; the data model should absorb it as rows, not columns.
• A key that identifies each row. 1NF assumes a primary key exists so every row is distinct and addressable. Duplicate, key-less rows are pre-1NF.
• Order-independence. Rows carry no meaning in their physical order; all meaning lives in column values. Relying on insertion order is a 1NF smell.

What 2NF adds on top of 1NF.

• Only relevant with a composite key. If the candidate key is a single column, every non-key attribute depends on the whole key trivially, so a 1NF table with a single-column key is automatically in 2NF. 2NF is a composite-key problem.
• No partial dependencies. A partial dependency is a non-key attribute that depends on only part of a composite candidate key. In (order_id, product_id) → …, product_name depending on product_id alone is the textbook partial dependency.
• The fix is decomposition. Move each partially-dependent attribute to a table keyed by the part of the key it actually depends on. product_name and unit_price go to a products table keyed by product_id.
• Why it matters. A partial dependency is redundancy: product_name repeats for every line item of that product, reintroducing the update/insert/delete anomalies at the line-item grain.

Common interview probes on 1NF and 2NF.

• "Is a column holding a JSON array in 1NF?" — pragmatically debatable, but the exam answer is "no, if you treat the array as a repeating group of addressable values."
• "Can a table be in 1NF but not 2NF?" — yes, whenever the key is composite and a non-key column depends on part of it.
• "Does a single-column primary key guarantee 2NF?" — yes, once the table is 1NF; there is no part of the key to depend on partially.
• "What's the fix for a partial dependency?" — decompose into a table keyed by the determining part of the key, linked by a foreign key.

Worked example — converting a repeating-group table to 1NF

Detailed explanation. A contacts table stores up to three phone numbers per person as phone1, phone2, phone3. This is the classic repeating group: the phone numbers are the same kind of fact, addressed by column position instead of by rows. The 1NF fix pushes them into a child contact_phones table with one row per phone.

• The smell. Positional columns (phone1..phone3), a hard cap (only three fit), and NULLs when a person has fewer than three phones.
• The 1NF fix. One contact_phones row per phone; unlimited phones; no NULL padding.
• The bonus. You can now attach per-phone attributes (type, verified flag) that had nowhere to live in the positional model.

Question. Migrate the repeating-group contacts table to a 1NF design and show how a query for "all of a person's phones" changes.

Input.

contact_id	name	phone1	phone2	phone3
1	Ada	555-1000	555-1001	NULL
2	Grace	555-2000	NULL	NULL

Code.

-- BEFORE: repeating group (violates 1NF) — phones addressed by column position
CREATE TABLE contacts (
    contact_id  BIGINT PRIMARY KEY,
    name        TEXT   NOT NULL,
    phone1      TEXT,
    phone2      TEXT,
    phone3      TEXT      -- a 4th phone needs ALTER TABLE; unused slots are NULL
);

-- AFTER (1NF): the parent holds atomic person facts...
CREATE TABLE contacts_1nf (
    contact_id  BIGINT PRIMARY KEY,
    name        TEXT   NOT NULL
);

-- ...and a child table holds one atomic phone per row (no cap, no NULL padding)
CREATE TABLE contact_phones (
    contact_id  BIGINT NOT NULL REFERENCES contacts_1nf(contact_id),
    phone       TEXT   NOT NULL,
    phone_type  TEXT   NOT NULL DEFAULT 'mobile',   -- now has a home
    PRIMARY KEY (contact_id, phone)
);

-- Migration: unpivot the positional columns into rows
INSERT INTO contact_phones (contact_id, phone)
SELECT contact_id, phone1 FROM contacts WHERE phone1 IS NOT NULL
UNION ALL
SELECT contact_id, phone2 FROM contacts WHERE phone2 IS NOT NULL
UNION ALL
SELECT contact_id, phone3 FROM contacts WHERE phone3 IS NOT NULL;

-- Query "all phones for Ada" — set-based, no positional gymnastics
SELECT c.name, p.phone
FROM   contacts_1nf c
JOIN   contact_phones p ON p.contact_id = c.contact_id
WHERE  c.name = 'Ada';

Step-by-step explanation.

1. The phone1/phone2/phone3 columns are a repeating group: three columns holding the same kind of value, addressed by position. This is the 1NF violation — the values are not stored as independent, addressable rows.
2. The 1NF design splits person facts (name) from phone facts. contacts_1nf holds one row per person; contact_phones holds one row per phone with (contact_id, phone) as the key.
3. The migration UNION ALLs the three positional columns into rows, filtering NULLs so empty slots simply do not become rows. There is no more NULL padding and no three-phone cap.
4. A per-phone attribute like phone_type now has a natural home; in the positional model there was nowhere to record that phone2 is a work number without adding yet more positional columns.
5. The "all phones" query becomes an ordinary join instead of COALESCE-ing across three columns. Aggregations like "count phones per person" become GROUP BY contact_id — trivial in 1NF, painful in the repeating-group form.

Output.

contact_id	name	phone	phone_type
1	Ada	555-1000	mobile
1	Ada	555-1001	mobile
2	Grace	555-2000	mobile

Rule of thumb. Any time you see numbered columns (col1, col2, col3) or a delimited list inside a cell, you are looking at a repeating group. Push it down into a child table with one row per value; the row count grows, but every value becomes atomic, addressable, and unbounded.

Worked example — removing a partial dependency for 2NF

Detailed explanation. Return to the enrollment table from section 1: (student_id, course_id) is the composite key, but student_name depends on student_id alone and course_title on course_id alone. Those are partial dependencies — the 2NF violation. The fix decomposes into three tables so each fact sits under the whole of its key.

• The partial dependencies. student_id → student_name and course_id → course_title, both against a composite key.
• The redundancy. student_name repeats for every course the student takes; course_title repeats for every student in the course.
• The 2NF fix. students(student_id → student_name), courses(course_id → course_title), and enrollment(student_id, course_id → grade) — the last keeps only the full-key-dependent fact.

Question. Decompose the 1NF enrollment table into 2NF and show that grade correctly stays on the composite key while the names move out.

Input.

Attribute	Determined by	2NF status in original
student_name	student_id (part of key)	partial dependency
course_title	course_id (part of key)	partial dependency
grade	(student_id, course_id)	full dependency (keep)

Code.

-- BEFORE (1NF, not 2NF): composite key, but names depend on half the key
CREATE TABLE enrollment_1nf (
    student_id    BIGINT NOT NULL,
    course_id     BIGINT NOT NULL,
    student_name  TEXT   NOT NULL,   -- depends on student_id only (partial)
    course_title  TEXT   NOT NULL,   -- depends on course_id only  (partial)
    grade         TEXT,              -- depends on the whole key    (full)
    PRIMARY KEY (student_id, course_id)
);

-- AFTER (2NF): one table per determinant of a partial dependency
CREATE TABLE students (
    student_id    BIGINT PRIMARY KEY,
    student_name  TEXT   NOT NULL
);

CREATE TABLE courses (
    course_id     BIGINT PRIMARY KEY,
    course_title  TEXT   NOT NULL
);

CREATE TABLE enrollment (              -- only the full-key-dependent fact remains
    student_id    BIGINT NOT NULL REFERENCES students(student_id),
    course_id     BIGINT NOT NULL REFERENCES courses(course_id),
    grade         TEXT,
    PRIMARY KEY (student_id, course_id)
);

-- Migrate the data out of the flat table
INSERT INTO students (student_id, student_name)
SELECT DISTINCT student_id, student_name FROM enrollment_1nf;

INSERT INTO courses (course_id, course_title)
SELECT DISTINCT course_id, course_title FROM enrollment_1nf;

INSERT INTO enrollment (student_id, course_id, grade)
SELECT student_id, course_id, grade FROM enrollment_1nf;

Step-by-step explanation.

1. The composite key (student_id, course_id) is what makes 2NF relevant. Both student_name and course_title depend on only half of it, so each is a partial dependency and a 2NF violation.
2. student_name moves to a students table keyed by student_id, storing the name once per student instead of once per enrollment. SELECT DISTINCT collapses the repeated copies during migration.
3. course_title moves to a courses table keyed by course_id for the same reason — one row per course, not one per enrollment.
4. grade is genuinely full-key-dependent: it is specific to this student in this course, so it stays in enrollment on the composite key. Recognising which attribute stays is as important as knowing which move.
5. Foreign keys wire the pieces back together and let the database enforce that every enrollment references a real student and a real course. The decomposition is lossless because student_id and course_id are candidate keys of their new tables.

Output.

Table	Key	Non-key attribute	Copies of each fact
students	student_id	student_name	one per student
courses	course_id	course_title	one per course
enrollment	(student_id, course_id)	grade	one per enrollment

Rule of thumb. 2NF only applies to composite keys. Find every non-key attribute, ask "does it depend on the whole key or just part of it?", and relocate the part-of-key ones to a table keyed by the part they depend on. Whatever genuinely needs the whole key stays put.

Worked example — "is this table in 2NF, and fix it"

Detailed explanation. A common interview drill hands you a table and asks you to decide whether it is in 2NF, then repair it if not. Consider a project_assignments(emp_id, project_id, hours, emp_department) table where the key is (emp_id, project_id). The trap is emp_department — does it depend on the whole key, or just emp_id? Walk the decision explicitly.

• The candidate key. (emp_id, project_id) — an employee can be on many projects, a project has many employees.
• The suspect. emp_department — an employee's department does not change per project, so it depends on emp_id alone.
• The verdict. Partial dependency ⇒ not in 2NF. hours is fine (it is per employee-per-project).

Question. Determine whether project_assignments is in 2NF; if not, decompose it and justify each attribute's placement.

Input.

emp_id	project_id	hours	emp_department
7	100	20	Data
7	200	15	Data
9	100	30	Platform

Code.

-- Candidate key = (emp_id, project_id).
-- FDs:  (emp_id, project_id) -> hours        (full key   -> keep)
--        emp_id              -> emp_department (PARTIAL   -> 2NF violation)
-- Verdict: NOT in 2NF, because emp_department depends on part of the key.

-- FIX: department is an employee fact, not an assignment fact.
CREATE TABLE employees (
    emp_id          BIGINT PRIMARY KEY,
    emp_department  TEXT   NOT NULL      -- stored once per employee
);

CREATE TABLE project_assignments (
    emp_id      BIGINT NOT NULL REFERENCES employees(emp_id),
    project_id  BIGINT NOT NULL,
    hours       INT    NOT NULL,         -- genuinely (emp_id, project_id)-dependent
    PRIMARY KEY (emp_id, project_id)
);

-- Now an employee's department is one UPDATE, not one-per-assignment:
UPDATE employees SET emp_department = 'ML' WHERE emp_id = 7;   -- single row, no drift

Step-by-step explanation.

1. Establish the candidate key first: (emp_id, project_id). Without the key you cannot classify any dependency, so this is always step one.
2. Test each non-key attribute against the key. hours genuinely varies by employee and project, so it is full-key-dependent and stays.
3. emp_department does not change from one of an employee's projects to another — it depends on emp_id alone. That is a partial dependency on half a composite key, so the table is not in 2NF.
4. The fix relocates emp_department to an employees table keyed by emp_id. Now the department is stored once per employee, and project_assignments holds only the full-key-dependent hours.
5. The correctness win is concrete: changing employee 7's department is now a single-row UPDATE instead of an update to every one of their assignment rows, so the copies can never drift. That is the update anomaly, gone.

Output.

Question	Answer
In 2NF as given?	No — emp_department partially depends on emp_id
Offending FD	emp_id → emp_department
Fix	move department to employees(emp_id)
Attribute that stays	hours (full-key dependency)

Rule of thumb. To decide 2NF fast: find the composite key, then for each non-key attribute ask "could this value ever differ across the rows that share one half of the key?" If it cannot differ, it depends on that half only — a partial dependency — and the table is not in 2NF.

Senior interview question on 1NF and 2NF

A senior interviewer might ask: "An analyst hands you invoice_lines(invoice_id, line_no, product_sku, product_desc, product_category, qty, line_total, customer_tier). The key is (invoice_id, line_no). It's already 1NF. Take it to 2NF, being explicit about every functional dependency, which attributes are partial dependencies, and which genuinely belong on the composite key."

Solution Using explicit FD analysis and a 2NF decomposition keyed by each determinant

-- Given (1NF): key = (invoice_id, line_no)
-- FDs:
--   (invoice_id, line_no) -> product_sku, qty, line_total   (full key)
--   product_sku           -> product_desc, product_category (depends on a NON-KEY
--                                                             col -> transitive, 3NF)
--   invoice_id            -> customer_tier                   (PARTIAL: half the key)
--
-- 2NF target: remove partial dependencies on part of (invoice_id, line_no).
-- (The product_sku -> product_desc chain is a 3NF issue, flagged for section 3.)

-- invoice-level facts depend on invoice_id (half the key) -> own table
CREATE TABLE invoices (
    invoice_id    BIGINT PRIMARY KEY,
    customer_tier TEXT   NOT NULL          -- was a partial dependency
);

-- product facts depend on product_sku -> own table (also fixes the 3NF chain early)
CREATE TABLE products (
    product_sku       TEXT PRIMARY KEY,
    product_desc      TEXT NOT NULL,
    product_category  TEXT NOT NULL
);

-- the line item keeps only what depends on the WHOLE key
CREATE TABLE invoice_lines (
    invoice_id   BIGINT NOT NULL REFERENCES invoices(invoice_id),
    line_no      INT    NOT NULL,
    product_sku  TEXT   NOT NULL REFERENCES products(product_sku),
    qty          INT    NOT NULL,
    line_total   BIGINT NOT NULL,
    PRIMARY KEY (invoice_id, line_no)
);

Step-by-step trace.

Attribute	Depends on	Classification	Destination
customer_tier	invoice_id	partial dependency	invoices
product_desc	product_sku	via non-key (transitive)	products
product_category	product_sku	via non-key (transitive)	products
product_sku	(invoice_id, line_no)	full key	invoice_lines
qty, line_total	(invoice_id, line_no)	full key	invoice_lines

Working attribute by attribute: customer_tier depends on invoice_id (half the key) → partial dependency → move to invoices. product_desc and product_category depend on product_sku, which is itself a non-key column of the line — so they move to products (this simultaneously clears a lurking 3NF violation). product_sku, qty, and line_total genuinely need the whole (invoice_id, line_no) key, so they remain in invoice_lines. The decomposition is lossless because invoice_id keys invoices and product_sku keys products.

Output:

Table	Key	Facts stored once
invoices	invoice_id	customer_tier per invoice
products	product_sku	desc + category per product
invoice_lines	(invoice_id, line_no)	sku, qty, line_total per line

Why this works — concept by concept:

• Explicit FD enumeration — listing every dependency before decomposing turns "which columns move?" into a mechanical read-off: any FD whose left side is part of the composite key is a 2NF violation.
• Partial dependency removal — invoice_id → customer_tier and product_sku → product_desc both have a left side that is not the whole key; relocating each to a table keyed by its true determinant removes the per-line duplication.
• Keeping full-key attributes in place — qty and line_total legitimately depend on the whole (invoice_id, line_no) key, so they stay; recognising the attributes that must not move is what prevents over-decomposition.
• Early 3NF win — moving product_desc/product_category to products also fixes the transitive chain line → product_sku → product_desc, so a careful 2NF pass often lands you in 3NF for free.
• Cost — three tables and up to two joins to reconstruct a full invoice line (O(1) index lookups on invoice_id and product_sku), traded for single-row updates of tier and product facts. Write cost drops from O(lines) to O(1) per changed fact; read cost is a bounded join paid only when the wide row is needed.

SQL
Topic — sql
SQL normalization and composite-key problems

Practice →

Joins Topic — joins Joins problems on reconstructing decomposed tables

Practice →

---

3. 3NF & BCNF: transitive dependencies and determinants

3NF kills transitive dependencies; BCNF demands every determinant be a candidate key

The mental model in one line: 3NF removes the last common redundancy — a non-key attribute that depends on another non-key attribute (key → non-key → non-key) — while BCNF closes 3NF's one remaining loophole by requiring that the left side of every non-trivial functional dependency be a candidate key, which is why BCNF is nicknamed "3.5NF". For the overwhelming majority of tables the two forms coincide; the interview value is being able to produce the rare table that is in 3NF but not BCNF and explain exactly why.

What 3NF requires.

• 2NF plus no transitive dependency. A transitive dependency is key → A → B where A is non-key: B depends on the key only through another non-key attribute.
• Canonical example. order_id → zip → city. city depends on zip, and zip is not a key, so city is transitively dependent on order_id.
• The redundancy it removes. Without the fix, city repeats for every order sharing a zip; changing a city-for-zip mapping means touching every such order.
• The fix. Split zip → city into its own table; keep zip as a foreign key in the order table.

What BCNF adds — the strict version of 3NF.

• Every determinant is a candidate key. For each non-trivial FD X → Y, X must be a candidate key of the table. 3NF permits one exception BCNF does not.
• The 3NF loophole. 3NF technically allows a non-key attribute to determine a prime attribute (part of a candidate key). That is legal in 3NF but violates BCNF.
• When they differ. Only when a table has multiple, overlapping candidate keys — typically two composite keys sharing an attribute. Single-key tables in 3NF are always in BCNF.
• The cost of BCNF. Occasionally a BCNF decomposition is not dependency-preserving — a functional dependency can no longer be enforced by a single table's key. This is the one case where practitioners knowingly stop at 3NF.

How to find candidate keys (the skill BCNF depends on).

• Start from the FDs. Compute the closure of attribute sets: an attribute set X whose closure X⁺ is all attributes is a superkey; a minimal such X is a candidate key.
• Watch for overlap. BCNF violations live where two candidate keys share attributes, or where a non-key attribute determines a prime attribute.
• Prime vs non-prime again. 3NF is stated in terms of prime attributes; BCNF ignores that distinction and simply demands "determinant = candidate key," which is why it is cleaner to state and stricter to satisfy.

Common interview probes on 3NF and BCNF.

• "Give me a table in 3NF but not BCNF." — the money question; the answer needs overlapping candidate keys.
• "Why not always go to BCNF?" — dependency-preservation can be lost; 3NF is sometimes the pragmatic stop.
• "What's a transitive dependency?" — key → non-key → non-key; name it precisely.
• "Is every 3NF table in BCNF?" — no, but the exceptions require multiple overlapping candidate keys, which are uncommon.

Worked example — a 3NF decomposition that removes a transitive dependency

Detailed explanation. An employees table stores emp_id, dept_id, and dept_name. emp_id is the key; dept_id depends on emp_id; and dept_name depends on dept_id. That chain — emp_id → dept_id → dept_name — is a transitive dependency, the 3NF violation. dept_name repeats for every employee in the department.

• The chain. emp_id → dept_id (each employee is in one department) and dept_id → dept_name (each department has one name).
• The redundancy. dept_name is copied into every employee row of the department.
• The fix. Move dept_id → dept_name to a departments table; keep dept_id as a foreign key on employees.

Question. Take the employees table to 3NF and show the effect on renaming a department.

Input.

emp_id	emp_name	dept_id	dept_name
1	Ada	10	Data
2	Grace	10	Data
3	Linus	20	Platform

Code.

-- BEFORE (2NF, not 3NF): transitive dependency emp_id -> dept_id -> dept_name
CREATE TABLE employees_2nf (
    emp_id     BIGINT PRIMARY KEY,
    emp_name   TEXT   NOT NULL,
    dept_id    BIGINT NOT NULL,
    dept_name  TEXT   NOT NULL     -- depends on dept_id (a non-key col): transitive
);

-- AFTER (3NF): break the chain by giving dept_name its own keyed home
CREATE TABLE departments (
    dept_id    BIGINT PRIMARY KEY,
    dept_name  TEXT   NOT NULL     -- stored once per department
);

CREATE TABLE employees (
    emp_id    BIGINT PRIMARY KEY,
    emp_name  TEXT   NOT NULL,
    dept_id   BIGINT NOT NULL REFERENCES departments(dept_id)
);

-- migrate
INSERT INTO departments (dept_id, dept_name)
SELECT DISTINCT dept_id, dept_name FROM employees_2nf;

INSERT INTO employees (emp_id, emp_name, dept_id)
SELECT emp_id, emp_name, dept_id FROM employees_2nf;

-- Renaming a department is now ONE row, not one-per-employee:
UPDATE departments SET dept_name = 'Data Platform' WHERE dept_id = 10;

Step-by-step explanation.

1. The table is already in 2NF (single-column key emp_id, so no partial dependency is possible), which is why this is specifically a 3NF problem, not a 2NF one.
2. The FD chain emp_id → dept_id → dept_name is transitive: dept_name reaches the key only through dept_id, a non-key attribute. That is the 3NF violation.
3. The fix relocates dept_id → dept_name into a departments table keyed by dept_id. Now dept_name is stored once per department, and employees keeps only dept_id as a foreign key.
4. SELECT DISTINCT dept_id, dept_name collapses the duplicated department names during migration; the foreign key then enforces that every employee points at a real department.
5. The anomaly disappears: renaming department 10 is a single UPDATE departments row, so every employee "sees" the new name through the join and no copies can drift. Before the fix, the same rename touched every employee in the department.

Output.

Operation	Before (2NF)	After (3NF)
Rename department 10	update N employee rows	update 1 department row
Add a department with no employees	impossible	one departments row
Store dept_name	once per employee	once per department

Rule of thumb. A transitive dependency shows up as a non-key column that "looks like it belongs to another non-key column." If B is really a fact about A (and A is not the key), pull A → B into its own table keyed by A. That is the entire 3NF move.

Worked example — a BCNF violation and its fix

Detailed explanation. Here is the classic table that is in 3NF but not BCNF. teaching(student_id, subject, teacher) with the business rules: each subject is taught by many teachers, a student takes each subject from exactly one teacher, and — crucially — each teacher teaches exactly one subject. Those rules produce two candidate keys and an FD (teacher → subject) whose left side is not a candidate key.

• The FDs. (student_id, subject) → teacher and teacher → subject.
• The candidate keys. {student_id, subject} and {student_id, teacher} — overlapping (both contain student_id).
• The violation. teacher → subject: teacher is a determinant but not a candidate key, so the table is not in BCNF (though it is in 3NF, because subject is a prime attribute).

Question. Show why teaching is in 3NF but not BCNF, then decompose it into BCNF.

Input.

student_id	subject	teacher
1	Math	Dr. Ada
1	Physics	Dr. Bohr
2	Math	Dr. Ada

Code.

-- BEFORE: 3NF but NOT BCNF
-- FDs: (student_id, subject) -> teacher     [candidate key on the left]
--      teacher               -> subject     [determinant is NOT a candidate key]
-- Candidate keys: {student_id, subject} and {student_id, teacher}  (overlapping)
-- It's 3NF because 'subject' on the RHS of teacher->subject is a PRIME attribute,
-- which 3NF permits. BCNF forbids it: teacher is not a candidate key.
CREATE TABLE teaching (
    student_id  BIGINT NOT NULL,
    subject     TEXT   NOT NULL,
    teacher     TEXT   NOT NULL,
    PRIMARY KEY (student_id, subject)
    -- redundancy: 'Dr. Ada teaches Math' is repeated for every student of Dr. Ada
);

-- AFTER (BCNF): decompose on the offending determinant teacher -> subject
CREATE TABLE teacher_subject (
    teacher  TEXT PRIMARY KEY,      -- teacher determines subject: teacher is the key
    subject  TEXT NOT NULL
);

CREATE TABLE student_teacher (
    student_id  BIGINT NOT NULL,
    teacher     TEXT   NOT NULL REFERENCES teacher_subject(teacher),
    PRIMARY KEY (student_id, teacher)
);

-- Reconstruct the original relation losslessly:
CREATE VIEW teaching_rebuilt AS
SELECT st.student_id, ts.subject, st.teacher
FROM   student_teacher st
JOIN   teacher_subject ts ON ts.teacher = st.teacher;

Step-by-step explanation.

1. Enumerate the FDs from the business rules: (student_id, subject) → teacher (a student takes a subject from one teacher) and teacher → subject (a teacher teaches one subject).
2. Find the candidate keys. {student_id, subject} determines teacher; {student_id, teacher} determines subject (via teacher → subject). Both are minimal, so the table has two overlapping candidate keys.
3. Check 3NF: the only "suspicious" FD is teacher → subject. Its right side, subject, is a prime attribute (part of a candidate key), and 3NF permits a non-key determinant of a prime attribute — so the table is in 3NF.
4. Check BCNF: BCNF ignores the prime/non-prime distinction and simply demands that every determinant be a candidate key. teacher is a determinant but not a candidate key, so the table is not in BCNF. The visible symptom is that "Dr. Ada teaches Math" repeats for every student of Dr. Ada.
5. Decompose on the offending FD: teacher_subject(teacher → subject) stores the teacher-subject fact once, and student_teacher(student_id, teacher) records who studies with whom. The join on teacher rebuilds the original relation losslessly because teacher is the key of teacher_subject.

Output.

Property	Before	After (BCNF)
"Dr. Ada teaches Math" stored	once per student of Ada	once, in teacher_subject
teacher → subject enforced by	nothing (redundant copies)	primary key of teacher_subject
In BCNF?	no	yes

Rule of thumb. A 3NF-but-not-BCNF table always has overlapping candidate keys and a determinant that is not one of them. Decompose on that determinant: give the offending X → Y its own table keyed by X. If that decomposition would lose a dependency you must enforce, that is your cue to consider stopping at 3NF instead.

Worked example — 3NF vs BCNF with a concrete counterexample

Detailed explanation. Interviewers frequently ask you to contrast 3NF and BCNF using one table, so it pays to have a side-by-side ready. Reuse teaching: the same table satisfies 3NF but fails BCNF, and the reason is a single word — 3NF's tolerance of a non-key determinant when the dependent attribute is prime. Lay the two definitions against the same FD and show where they diverge.

• The shared FD. teacher → subject.
• 3NF's verdict. Legal — subject is prime, and 3NF allows non-key-determinant → prime-attribute.
• BCNF's verdict. Illegal — teacher is not a candidate key, full stop.

Question. Present the 3NF vs BCNF contrast on teaching, stating the exact clause of each definition that produces the different verdict.

Input.

Definition	Condition on FD X → Y (non-trivial)	Verdict on teacher → subject
3NF	X is a superkey, OR Y is a prime attribute	passes (subject is prime)
BCNF	X is a superkey (candidate key)	fails (teacher is not a key)

Code.

Same table, same FD, two verdicts
=================================

Table:   teaching(student_id, subject, teacher)
FDs:     (student_id, subject) -> teacher
         teacher               -> subject
Cand.keys: {student_id, subject}, {student_id, teacher}

Test the FD  teacher -> subject  against each definition:

  3NF rule:  for X -> Y, either X is a superkey OR every attribute in
             (Y - X) is prime.
             -> teacher is NOT a superkey, BUT subject IS prime
                (it's in candidate key {student_id, subject}).
             -> 3NF is SATISFIED.

  BCNF rule: for X -> Y, X MUST be a superkey. No prime-attribute escape hatch.
             -> teacher is NOT a superkey.
             -> BCNF is VIOLATED.

Conclusion: teaching is in 3NF but not in BCNF. The single word of
difference is 3NF's "...OR Y is prime" clause, which BCNF drops.

Step-by-step explanation.

1. Both definitions quantify over the same set of non-trivial FDs; the only difference is the condition each imposes on the left side X.
2. 3NF's condition has an escape hatch: X → Y is fine if X is a superkey or every attribute of Y is prime. teacher → subject uses the second clause — subject is prime — so 3NF passes.
3. BCNF removes that escape hatch entirely: X must be a superkey, period. teacher is not, so BCNF fails on exactly the FD that 3NF forgave.
4. This is why the two forms coincide unless the table has overlapping candidate keys — the prime-attribute escape hatch only ever matters when a non-key attribute can determine part of a candidate key, which requires those overlaps.
5. The practical takeaway for the interview: name the escape-hatch clause explicitly. Saying "3NF allows a non-key determinant of a prime attribute; BCNF does not" is the sentence that demonstrates you understand the difference rather than reciting it.

Output.

Aspect	3NF	BCNF
Condition on X in X → Y	superkey or Y prime	superkey only
Verdict on teacher → subject	passes	fails
Requires overlapping candidate keys to differ	—	yes
Nickname	third normal form	"3.5NF"

Rule of thumb. 3NF and BCNF differ by exactly one clause: 3NF's "or the dependent attribute is prime." Memorise that clause. When asked for a 3NF-but-not-BCNF table, reach for one with two overlapping candidate keys and a small FD like teacher → subject.

Senior interview question on 3NF vs BCNF

A senior interviewer might ask: "Design a room-booking table booking(room, time_slot, client) where a room is booked by one client per slot, and each client is always given the same single room. Tell me the functional dependencies and candidate keys, decide whether it is in 3NF and BCNF, and if it violates BCNF, decompose it — then tell me whether your decomposition preserves all the dependencies."

Solution Using candidate-key analysis and a dependency-aware BCNF decomposition

-- Business rules:
--   a (room, time_slot) is booked by exactly one client:  (room, time_slot) -> client
--   each client is always given one fixed room:            client -> room
-- Candidate keys: {room, time_slot} and {client, time_slot}  (overlapping on time_slot)
-- 3NF? yes: the only risky FD is client -> room; 'room' is PRIME, so 3NF allows it.
-- BCNF? no:  client is a determinant but NOT a candidate key.

CREATE TABLE booking (              -- 3NF, not BCNF
    room       TEXT   NOT NULL,
    time_slot  TEXT   NOT NULL,
    client     TEXT   NOT NULL,
    PRIMARY KEY (room, time_slot)   -- redundancy: client->room repeats per slot
);

-- BCNF decomposition on the offending determinant client -> room
CREATE TABLE client_room (
    client  TEXT PRIMARY KEY,       -- client determines room
    room    TEXT NOT NULL
);

CREATE TABLE client_booking (
    client     TEXT NOT NULL REFERENCES client_room(client),
    time_slot  TEXT NOT NULL,
    PRIMARY KEY (client, time_slot)
);

-- Reconstruct the original:
CREATE VIEW booking_rebuilt AS
SELECT cr.room, cb.time_slot, cb.client
FROM   client_booking cb
JOIN   client_room cr ON cr.client = cb.client;

Step-by-step trace.

Step	Finding
FDs	(room, time_slot) → client, client → room
Candidate keys	{room, time_slot}, {client, time_slot} (overlap on time_slot)
3NF check	passes — room in client → room is prime
BCNF check	fails — client is a determinant but not a candidate key
Decompose on	client → room
Dependency-preservation	(room, time_slot) → client is not enforced by any single table

Enumerating the rules gives (room, time_slot) → client and client → room; the two overlapping candidate keys are {room, time_slot} and {client, time_slot}. The table is in 3NF (the client → room FD has a prime right-hand side) but not BCNF (client is not a candidate key). Decomposing into client_room(client → room) and client_booking(client, time_slot) reaches BCNF — but note the catch: the original FD (room, time_slot) → client (no two clients share a room-slot) is now split across two tables and cannot be enforced by a single key, so the BCNF decomposition is not dependency-preserving.

Output:

Property	booking (3NF)	Decomposed (BCNF)
client → room stored	once per booked slot	once in client_room
In BCNF?	no	yes
(room, time_slot) → client enforceable by a key?	yes	no (needs a trigger/constraint)
Redundancy on client → room	present	removed

Why this works — concept by concept:

• Candidate-key analysis — computing both candidate keys ({room, time_slot} and {client, time_slot}) is what reveals the overlap that makes BCNF and 3NF diverge; without it you cannot classify client → room.
• The prime-attribute escape hatch — the table survives 3NF only because room (the RHS of client → room) is prime. Naming this clause is the difference between "it's 3NF" as a guess and as a proof.
• BCNF decomposition on the determinant — giving client → room its own table keyed by client removes the repeated "client X always sits in room Y" fact, which is the redundancy BCNF exists to eliminate.
• Dependency-preservation trade-off — the decomposition loses the ability to enforce (room, time_slot) → client with a single key, so you either add a cross-table constraint/trigger or consciously stop at 3NF. This is the real reason practitioners sometimes prefer 3NF.
• Cost — BCNF removes one class of redundancy (O(1) updates to client → room) but may add an enforcement mechanism (a trigger or a unique index across the join) to protect the lost dependency. The engineering decision is "redundancy removed vs constraint complexity added," and it is workload-dependent.

SQL
Topic — sql
SQL functional-dependency and BCNF problems

Practice →

Design Topic — design Design problems on candidate keys and decomposition

Practice →

---

4. Higher forms: 4NF, 5NF & 3.5NF in practice

4NF splits independent multi-valued facts; 5NF handles join dependencies; BCNF ("3.5NF") is where you stop

The mental model in one line: above BCNF sit 4NF (which forbids independent multivalued facts from sharing a table) and 5NF (which forbids a table that can only be reconstructed by joining three or more projections), but both address dependency shapes that are rare in practice — which is why BCNF, informally "3.5NF," is the stopping point almost every operational schema targets. Knowing the higher forms exist, being able to recognise a multivalued dependency, and being able to explain the diminishing returns is the senior signal; reaching for 5NF in a design review is usually a smell.

What 4NF requires.

• A multivalued dependency (MVD). X ↠ Y means: for a given X, the set of Y values is independent of the other attributes. Two independent many-valued facts about the same key in one table cause a cartesian blow-up.
• The classic smell. An employee has a set of skills and an independent set of languages. Storing both in one (emp_id, skill, language) table forces a row for every skill × language combination — pure redundancy.
• The 4NF fix. Split each independent multi-valued fact into its own table: emp_skills(emp_id, skill) and emp_languages(emp_id, language).
• Precondition. 4NF assumes BCNF first; it only concerns non-trivial MVDs that are not already implied by a candidate key.

What 5NF requires.

• A join dependency. A table is in 5NF if it cannot be losslessly decomposed into smaller projections and reconstructed only by joining three or more of them.
• When it appears. Highly-constrained many-to-many-to-many relationships — e.g. (supplier, part, project) where the presence of pairwise facts implies the triple. Genuinely rare.
• Why it's mostly academic. Most real ternary relationships do not have a non-trivial join dependency, so decomposing them would be lossy. 5NF is correct in a narrow band of cases and over-engineering everywhere else.

Why "3.5NF" (BCNF) is the practical stopping point.

• Diminishing returns. 1NF→BCNF removes the redundancy that causes the everyday insert/update/delete anomalies. 4NF/5NF address MVDs and join dependencies that appear in a small fraction of schemas.
• Cost of over-normalization. Every decomposition adds a join. Past BCNF, the extra joins usually cost more (query complexity, planner work) than the rare redundancy they remove saves.
• The default target. "Normalize to BCNF; go higher only when a concrete MVD or join dependency is demonstrated, not suspected." That sentence is the senior answer to "how far do you normalize?"
• The naming. "3.5NF" is informal shorthand for BCNF — a nod to it being "3NF plus a little more," and to it being the practical ceiling.

Common interview probes on higher forms.

• "What's a multivalued dependency?" — independent many-valued facts about the same key.
• "When would you go past BCNF?" — only when a real MVD (4NF) or join dependency (5NF) is demonstrated.
• "Why do most schemas stop at BCNF?" — diminishing returns; higher forms add joins without removing anomalies most schemas actually suffer.
• "Is 4NF common in OLTP?" — occasionally, wherever a table accidentally merges two independent one-to-many facts.

Worked example — a 4NF multivalued-dependency fix

Detailed explanation. An employee_facts(emp_id, skill, language) table records, for each employee, the skills they have and the languages they speak — two independent multi-valued facts. Storing them together forces one row per skill×language pair, so an employee with 3 skills and 2 languages needs 6 rows to express 5 facts. That cartesian product is the MVD redundancy 4NF removes.

• The MVDs. emp_id ↠ skill and emp_id ↠ language, independent of each other.
• The blow-up. rows = skills × languages, even though skills and languages have nothing to do with each other.
• The 4NF fix. Two tables: emp_skills(emp_id, skill) and emp_languages(emp_id, language).

Question. Show the cartesian blow-up in the combined table and decompose it into 4NF.

Input.

emp_id	skill	language
7	SQL	EN
7	SQL	FR
7	Python	EN
7	Python	FR

Code.

-- BEFORE (BCNF but NOT 4NF): two independent multi-valued facts in one table.
-- The whole row is the key, so it's technically BCNF, yet it's massively redundant:
-- rows = |skills| x |languages|.
CREATE TABLE employee_facts (
    emp_id    BIGINT NOT NULL,
    skill     TEXT   NOT NULL,
    language  TEXT   NOT NULL,
    PRIMARY KEY (emp_id, skill, language)
);
-- emp 7 with {SQL, Python} x {EN, FR} needs 4 rows to state 4 independent facts.
-- Adding one new skill forces one new row PER language -> update anomaly returns.

-- AFTER (4NF): one table per independent multi-valued fact
CREATE TABLE emp_skills (
    emp_id  BIGINT NOT NULL,
    skill   TEXT   NOT NULL,
    PRIMARY KEY (emp_id, skill)
);

CREATE TABLE emp_languages (
    emp_id    BIGINT NOT NULL,
    language  TEXT   NOT NULL,
    PRIMARY KEY (emp_id, language)
);

-- migrate: DISTINCT collapses the cartesian rows back to independent facts
INSERT INTO emp_skills (emp_id, skill)
SELECT DISTINCT emp_id, skill FROM employee_facts;

INSERT INTO emp_languages (emp_id, language)
SELECT DISTINCT emp_id, language FROM employee_facts;

Step-by-step explanation.

1. The combined table is technically in BCNF — the only candidate key is the whole row (emp_id, skill, language), so there is no non-key determinant. BCNF does not catch this redundancy, which is exactly why 4NF exists.
2. The redundancy comes from two independent multivalued dependencies: emp_id ↠ skill and emp_id ↠ language. Because they are independent, the table must store every combination — a cartesian product.
3. The blow-up is quantifiable: 3 skills and 2 languages produce 6 rows to express 5 facts. Adding a fourth skill adds two more rows (one per existing language), which is the update anomaly resurfacing at a new grain.
4. The 4NF fix splits the two independent facts into separate tables. emp_skills holds skills; emp_languages holds languages; neither multiplies the other. SELECT DISTINCT collapses the cartesian rows during migration.
5. After the split, adding a skill is one row in emp_skills, independent of how many languages the employee speaks. The row count drops from skills×languages to skills+languages.

Output.

emp_skills		emp_languages
emp_id	skill	emp_id	language
7	SQL	7	EN
7	Python	7	FR

Rule of thumb. If one table holds two lists that vary independently — skills and languages, phone numbers and email addresses — and you find yourself storing every combination, you have a 4NF violation. Give each independent multi-valued fact its own table so the row count adds instead of multiplies.

Worked example — 5NF and knowing when to stop

Detailed explanation. Consider supply(supplier, part, project): which suppliers provide which parts to which projects. A join dependency exists only if the business rule is "if supplier S supplies part P, and part P is used by project J, and supplier S supplies to project J, then S supplies P to J." That rule is unusual; most real supply relationships do not obey it. 5NF matters only when such a rule genuinely holds — otherwise decomposing is lossy and wrong.

• The 5NF-relevant case. The pairwise facts imply the triple ⇒ the triple table is redundant ⇒ decompose into three binary tables.
• The common case. No such rule ⇒ the triple carries information the pairs cannot ⇒ keep the ternary table; do not decompose.
• The lesson. 5NF is a decision you make only after demonstrating a join dependency, never by default.

Question. Decide whether supply should be decomposed to 5NF, given a business rule that does not imply the triple, and show why decomposing would be lossy.

Input.

supplier	part	project
Acme	Bolt	Bridge
Acme	Nut	Tower
Globex	Bolt	Tower

Code.

-- Ternary relationship: who supplies which part to which project.
CREATE TABLE supply (
    supplier  TEXT NOT NULL,
    part      TEXT NOT NULL,
    project   TEXT NOT NULL,
    PRIMARY KEY (supplier, part, project)
);

-- A tempting "5NF" decomposition into three binary projections:
CREATE VIEW sp AS SELECT DISTINCT supplier, part    FROM supply;
CREATE VIEW pj AS SELECT DISTINCT part,     project FROM supply;
CREATE VIEW sj AS SELECT DISTINCT supplier, project FROM supply;

-- Reconstruct by joining all three:
CREATE VIEW supply_rebuilt AS
SELECT sp.supplier, sp.part, pj.project
FROM   sp
JOIN   pj ON pj.part    = sp.part
JOIN   sj ON sj.supplier = sp.supplier AND sj.project = pj.project;

-- LOSSY unless a join dependency holds. Here it does NOT:
--   sp: (Acme,Bolt),(Acme,Nut),(Globex,Bolt)
--   pj: (Bolt,Bridge),(Nut,Tower),(Bolt,Tower)
--   sj: (Acme,Bridge),(Acme,Tower),(Globex,Tower)
-- The 3-way join yields a SPURIOUS row (Acme, Bolt, Tower) that was never a fact.
-- => decomposition is lossy => 'supply' is ALREADY in 5NF; do NOT decompose.

Step-by-step explanation.

1. The ternary supply table states genuine triples: Acme supplies Bolts to the Bridge project specifically. The triple is the unit of fact.
2. A 5NF decomposition would replace it with three binary projections (supplier-part, part-project, supplier-project) and rebuild by joining all three.
3. That reconstruction is lossless only if a join dependency holds — i.e. only if the pairwise facts always imply the triple. You must test this, not assume it.
4. Here the test fails: joining the three projections produces (Acme, Bolt, Tower), a triple that was never in the original data. The join invents a fact, so the decomposition is lossy.
5. Because decomposition is lossy, the table is already in 5NF and must stay as a ternary relation. This is the crucial lesson: over-normalizing a ternary relationship corrupts the data. Only decompose when the join dependency is demonstrated.

Output.

Rebuilt row	In original?	Verdict
(Acme, Bolt, Bridge)	yes	real
(Acme, Nut, Tower)	yes	real
(Globex, Bolt, Tower)	yes	real
(Acme, Bolt, Tower)	no	spurious — decomposition is lossy

Rule of thumb. Never decompose a ternary relationship to 5NF on reflex. Test the join dependency by projecting and re-joining; if the re-join produces a row that was not in the original, the decomposition is lossy and the ternary table is already in its correct (5NF) form.

Worked example — why most OLTP schemas stop at BCNF

Detailed explanation. The senior framing question is "how far do you normalize, and why not further?" The answer is a cost-benefit argument: each rung up to BCNF removes an anomaly class that everyday CRUD workloads actually hit, while 4NF/5NF remove dependency shapes that most schemas do not contain — so past BCNF you pay in extra joins without buying anomaly-freedom. Lay the argument out as a ledger.

• Benefit curve. Steep from 1NF to BCNF (each rung kills a real anomaly class), then flat (few schemas have MVDs or join dependencies).
• Cost curve. Rises steadily — every decomposition adds a table and a join.
• The crossover. For typical OLTP, benefit ≈ cost right around BCNF; hence "3.5NF" as the default target.

Question. Build the cost-benefit ledger that justifies stopping at BCNF for a typical OLTP schema.

Input.

Rung	Anomaly / redundancy removed	How common in OLTP
1NF–BCNF	insert/update/delete anomalies, transitive & determinant redundancy	~every schema
4NF	independent-MVD cartesian redundancy	occasional
5NF	join-dependency redundancy	rare

Code.

Normalization cost-benefit ledger (typical OLTP schema)
=======================================================

Rung   Removes                                  Present here?  Join cost added
-----  ---------------------------------------  -------------  ---------------
1NF    non-atomic cells, repeating groups       yes            0 (same table)
2NF    partial deps on composite key            yes            +1 join
3NF    transitive deps (nonkey -> nonkey)       yes            +1 join
BCNF   non-candidate-key determinants           sometimes      +1 join (if any)
----   -------- practical stop: "3.5NF" --------------------------------------
4NF    independent multivalued deps             rarely         +1 join, only if MVD
5NF    join deps on ternary relations           almost never   +2 joins, risky

Decision rule:
  normalize to BCNF by default.
  go to 4NF ONLY if a concrete independent MVD is observed.
  go to 5NF ONLY if a join dependency is DEMONSTRATED (re-join is lossless).
  otherwise: stop. extra joins cost more than the absent redundancy saves.

Step-by-step explanation.

1. The ledger pairs each rung with the redundancy it removes and how often that redundancy actually appears in an OLTP schema. This reframes "how far to normalize" as a data question, not a dogma.
2. Rungs 1NF through BCNF remove redundancy that is present in essentially every real schema, so the benefit is near-certain and the cost (a bounded number of joins) is worth paying.
3. 4NF only helps if the schema contains two independent multi-valued facts in one table — an occasional, detectable situation. If no such MVD exists, moving to 4NF changes nothing but adds a table.
4. 5NF only helps under a demonstrated join dependency, which is rare and risky to assume; decomposing without it is lossy (previous example). So 5NF is opt-in on proof, never by default.
5. The crossover — where marginal benefit stops exceeding marginal join cost — lands at BCNF for typical OLTP, which is why "normalize to BCNF, denormalize deliberately for OLAP" is the standard doctrine.

Output.

Target	When to choose it
BCNF ("3.5NF")	default for OLTP systems of record
4NF	a concrete independent MVD is observed
5NF	a join dependency is demonstrated (lossless re-join)
Below 3NF (denormalized)	read-heavy OLAP models (section 5)

Rule of thumb. Normalize to BCNF and stop, unless you can point at a specific multivalued or join dependency that a higher form would remove. "Might have one" is not a reason; a demonstrated dependency is. Past BCNF, the extra joins usually outweigh the redundancy they eliminate.

Senior interview question on higher normal forms

A senior interviewer might ask: "A teammate wants to take our BCNF orders schema all the way to 5NF 'to be safe.' Talk me through 4NF and 5NF, give me a table in our domain that genuinely needs 4NF versus one that would be wrong to push to 5NF, and give me the rule you'd write into our design guide for how far to normalize."

Solution Using an MVD test, a join-dependency test, and a written stopping rule

-- CASE A: a table that genuinely NEEDS 4NF.
-- 'customer_contacts' merges two independent multi-valued facts:
--   customer -> {phone}   and   customer -> {shipping_region}
-- Independent => cartesian blow-up => 4NF violation.
CREATE TABLE customer_contacts (
    customer_id      BIGINT NOT NULL,
    phone            TEXT   NOT NULL,
    shipping_region  TEXT   NOT NULL,
    PRIMARY KEY (customer_id, phone, shipping_region)   -- redundant combinations
);
-- 4NF fix: split the independent facts
CREATE TABLE customer_phones  (customer_id BIGINT, phone TEXT,
                               PRIMARY KEY (customer_id, phone));
CREATE TABLE customer_regions (customer_id BIGINT, shipping_region TEXT,
                               PRIMARY KEY (customer_id, shipping_region));

-- CASE B: a table that would be WRONG to push to 5NF.
-- 'order_assignment(order_id, picker, station)' — the triple is a real fact
-- (this picker handled this order at this station); the pairwise projections
-- do NOT imply the triple, so a 3-way-join reconstruction is lossy.
CREATE TABLE order_assignment (
    order_id  BIGINT NOT NULL,
    picker    TEXT   NOT NULL,
    station   TEXT   NOT NULL,
    PRIMARY KEY (order_id, picker, station)
);
-- Test: project to (order,picker),(picker,station),(order,station) and re-join.
-- If the re-join yields a triple that never happened -> lossy -> already 5NF -> STOP.

Step-by-step trace.

Case	Test applied	Result	Action
A: customer_contacts	independent-MVD check	phone and region vary independently	decompose to 4NF
B: order_assignment	join-dependency check (project + re-join)	re-join invents spurious triples	keep ternary; already 5NF
Guide rule	cost vs join count	benefit flat above BCNF	default stop at BCNF

Case A has two independent multivalued dependencies (customer_id ↠ phone, customer_id ↠ shipping_region), so storing them together forces every phone×region combination — a real 4NF violation, fixed by splitting into customer_phones and customer_regions. Case B looks similar but fails the join-dependency test: projecting order_assignment into three binary tables and re-joining fabricates triples that never occurred, so the decomposition is lossy and the table is already in 5NF. The written guide rule follows: default to BCNF, ascend only on a demonstrated MVD (4NF) or join dependency (5NF).

Output:

Table	Correct normal form	Reason
customer_contacts	decompose to 4NF	two independent MVDs
order_assignment	leave at BCNF/5NF (no decomposition)	join dependency does not hold
Design-guide default	BCNF ("3.5NF")	anomaly-free without over-joining

Why this works — concept by concept:

• Multivalued dependency (MVD) — the customer_id ↠ phone / customer_id ↠ shipping_region pair is independent, so the combined table stores a cartesian product; 4NF splits each independent fact into its own table so counts add instead of multiply.
• Join dependency test — the only rigorous way to decide 5NF is to project and re-join: if the re-join is lossless the table decomposes; if it invents rows (as in Case B) the table is already 5NF and must stay ternary.
• Losslessness as the guardrail — every legitimate decomposition, at every normal form, must reconstruct the original by join with no spurious rows; Case B fails this, which is the proof that pushing it to 5NF would corrupt data.
• A written stopping rule — "BCNF by default; 4NF on a demonstrated MVD; 5NF on a demonstrated join dependency" turns a judgement call into a reviewable policy, which is exactly what a senior is expected to author.
• Cost — Case A's split trades one table for O(1) independent inserts (rows add, not multiply); Case B's non-decomposition avoids a lossy 2-extra-join reconstruction. The net rule keeps schemas anomaly-free at the minimum join cost — BCNF for almost everything.

SQL
Topic — sql
SQL problems on multivalued dependencies and keys

Practice →

Dimensional modeling Topic — dimensional-modeling Dimensional-modeling problems on grain and keys

Practice →

---

5. Denormalization for analytics: when to break the rules

Normalize the source of truth; denormalize the read models you derive from it

The mental model in one line: denormalization is the deliberate reintroduction of data redundancy into a derived read model — a star schema, a one-big-table, or a materialized view — to make analytical reads fast, and it is the correct move precisely when the workload flips from many small correctness-critical writes (OLTP) to few enormous join-averse reads (OLAP). Denormalization is not "giving up on normalization"; it is normalizing the system of record and then building denormalized copies from it, so the redundancy is controlled, refreshed on a schedule, and never the authoritative source.

Why denormalize at all — the OLTP/OLAP split.

• OLTP wants cheap writes and correctness. Normalized schemas touch one row per fact and cannot store contradictions — ideal for order entry, banking, inventory.
• OLAP wants cheap reads over huge scans. A dashboard that joins eight normalized tables per query pays a join tax on every scan of millions of rows. Pre-joining into a wide table removes that tax.
• The two are different databases. The modern stack normalizes the operational store and derives denormalized analytical models (warehouse, lakehouse) via ETL/ELT. Redundancy in the derived model is fine because the source stays authoritative.
• The trade you accept. Faster reads and simpler queries in exchange for storage, refresh cost, and redundancy you must manage rather than eliminate.

The three denormalization patterns.

• Star schema. A central fact table (one row per event, e.g. a sale) surrounded by dimension tables (date, product, customer). Dimensions are deliberately denormalized (a snowflake would re-normalize them) so a query joins the fact to a handful of wide dimensions instead of a deep chain.
• One big table (OBT). Pre-join everything into a single wide table — no joins at all at query time. Extreme denormalization; ideal for columnar engines (BigQuery, Snowflake, Redshift) where scanning wide rows is cheap and joins are the bottleneck.
• Materialized views / summary tables. Precompute an aggregate or a join once, store the result, refresh on a schedule. Controlled redundancy with an explicit freshness contract.

Controlling the redundancy you introduce.

• Single source of truth. The normalized OLTP store is authoritative; denormalized models are derived and rebuildable. Never let a dashboard's copy become the only record of a fact.
• Refresh discipline. Every denormalized artifact has a freshness policy — nightly rebuild, incremental merge, or on-commit CDC. Stale redundancy is a correctness bug; scheduled redundancy is a feature.
• Idempotent rebuilds. A denormalized table you can TRUNCATE and rebuild from the normalized source is safe; one you mutate in place and cannot reproduce is a liability.
• Document the trade. Record why each denormalization exists (which query it accelerates) so a future reviewer does not "fix" it back into a slow normalized shape.

Common interview probes on denormalization.

• "When would you deliberately violate 3NF?" — read-heavy OLAP models where join cost dominates and the source stays normalized.
• "What's a star schema?" — fact + denormalized dimensions; the canonical warehouse denormalization.
• "How do you keep denormalized data correct?" — derive it from a normalized source, refresh on a schedule, keep rebuilds idempotent.
• "Materialized view vs table?" — a materialized view is a managed, refreshable precomputation with a declared freshness policy.

Worked example — denormalizing a 3NF OLTP schema into a star / wide table

Detailed explanation. A normalized OLTP schema splits sales across fact_orders, customers, products, and dates. Analysts run "revenue by product category by month by customer region" — a four-table join over millions of rows, every time. The fix builds a denormalized star (or a fully wide OBT) in the warehouse: pre-join the dimensions onto the fact so the dashboard query scans one table.

• The normalized source. Four tables, correctness-critical, updated transactionally.
• The analytical query. A repeated multi-join aggregation.
• The denormalized target. A wide sales_obt (or star) refreshed from the source; the dashboard scans one table.

Question. Build a denormalized wide table from the normalized schema and show the query simplification and the refresh job.

Input.

Normalized table	Key	Role
fact_orders	order_id	one row per order line (measures)
customers	customer_id	region, tier (dimension)
products	product_id	category, name (dimension)
dates	date_id	month, quarter (dimension)

Code.

-- Normalized OLTP source (3NF) — great for writes, join-heavy for analytics
-- fact_orders(order_id, customer_id, product_id, date_id, qty, amount_cents)
-- customers(customer_id -> region, tier)
-- products(product_id -> category, product_name)
-- dates(date_id -> month, quarter, year)

-- Denormalized wide table (OBT) built for the warehouse: dimensions pre-joined in.
CREATE TABLE sales_obt (
    order_id      BIGINT,
    -- pre-joined customer dimension (controlled redundancy)
    customer_id   BIGINT,
    region        TEXT,
    tier          TEXT,
    -- pre-joined product dimension
    product_id    BIGINT,
    category      TEXT,
    product_name  TEXT,
    -- pre-joined date dimension
    month         DATE,
    quarter       TEXT,
    year          INT,
    -- measures
    qty           INT,
    amount_cents  BIGINT
);

-- Idempotent nightly rebuild from the normalized source (TRUNCATE + reload).
TRUNCATE sales_obt;
INSERT INTO sales_obt
SELECT f.order_id,
       c.customer_id, c.region, c.tier,
       p.product_id,  p.category, p.product_name,
       d.month, d.quarter, d.year,
       f.qty, f.amount_cents
FROM   fact_orders f
JOIN   customers c ON c.customer_id = f.customer_id
JOIN   products  p ON p.product_id  = f.product_id
JOIN   dates     d ON d.date_id     = f.date_id;

-- Analytics query BEFORE: a 4-table join every run.
-- Analytics query AFTER: single-table scan, no joins.
SELECT category, region, month, SUM(amount_cents) AS revenue
FROM   sales_obt
GROUP  BY category, region, month;

Step-by-step explanation.

1. The normalized source stays exactly as it is — it remains the authoritative, anomaly-free system of record. Denormalization happens in a derived table, not by degrading the source.
2. sales_obt pre-joins each dimension's attributes (region, category, month, …) directly onto the fact row. This is deliberate, controlled redundancy: category now repeats for every order line of that product.
3. The rebuild is idempotent — TRUNCATE then reload from the source join. Because the wide table is fully reproducible from the normalized source, stale or corrupt data is fixed by rerunning the job, not by manual patching.
4. The analytics query collapses from a four-table join to a single-table GROUP BY. On columnar warehouses this is dramatically cheaper: the engine scans a few columns of one table instead of hash-joining four.
5. The redundancy is safe because it is derived and refreshed: the nightly job re-materialises sales_obt from the source, so any change in the normalized data propagates on the next run. The freshness contract (nightly) is explicit and documented.

Output.

Aspect	Normalized (OLTP)	Denormalized OBT (OLAP)
Dashboard query	4-table join	single-table scan
Write cost	O(1) per fact	rebuilt nightly
Redundancy	none	controlled, derived
Source of truth	this schema	still the normalized schema

Rule of thumb. Denormalize in a derived table, never by degrading the source. Keep the rebuild idempotent (TRUNCATE + reload, or incremental merge), pre-join the dimensions the dashboards actually use, and write down the freshness contract. The normalized schema stays authoritative; the wide table is a fast, disposable copy.

Worked example — controlled-redundancy patterns with materialized views

Detailed explanation. Sometimes a full OBT is overkill and you only need one expensive aggregate to be fast — "revenue per customer per month," recomputed constantly. A materialized view stores the precomputed result and refreshes on a schedule, giving you controlled redundancy with an explicit freshness policy, without hand-building a rebuild job.

• The pattern. Precompute an aggregate/join once; store it; refresh periodically.
• The control. The view is derived from the normalized source and is REFRESH-able; it never becomes the source of truth.
• The trade. Reads hit a tiny precomputed table; writes to the source do not see the change until the next refresh (the freshness contract).

Question. Create a materialized view for "monthly revenue per customer" and define its refresh policy and staleness contract.

Input.

Concern	Choice
Precomputed metric	monthly revenue per customer
Source	normalized fact_orders + dates
Refresh cadence	nightly (concurrently)
Staleness contract	up to 24h behind source

Code.

-- Controlled redundancy: a materialized view derived from the normalized source.
CREATE MATERIALIZED VIEW mv_customer_month_revenue AS
SELECT f.customer_id,
       d.month,
       SUM(f.amount_cents) AS revenue_cents,
       COUNT(*)            AS order_lines
FROM   fact_orders f
JOIN   dates d ON d.date_id = f.date_id
GROUP  BY f.customer_id, d.month
WITH   NO DATA;   -- populate on first refresh

-- Unique index enables CONCURRENT refresh (no read-lock during rebuild)
CREATE UNIQUE INDEX ux_mv_cust_month
    ON mv_customer_month_revenue (customer_id, month);

-- Nightly refresh job (freshness contract: <= 24h stale)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_month_revenue;

-- Dashboards read the tiny precomputed view instead of scanning the fact table:
SELECT customer_id, revenue_cents
FROM   mv_customer_month_revenue
WHERE  month = DATE '2026-08-01'
ORDER  BY revenue_cents DESC
LIMIT  20;

Step-by-step explanation.

1. The materialized view precomputes the expensive GROUP BY once and stores the result, so dashboards read a small aggregate table instead of re-scanning fact_orders on every load.
2. WITH NO DATA defers population to the first explicit refresh, which keeps the CREATE cheap and lets the refresh job own the (potentially large) initial build.
3. The unique index on (customer_id, month) is what enables REFRESH ... CONCURRENTLY: Postgres can rebuild the view without taking an exclusive lock, so dashboards keep reading the old snapshot until the new one is ready.
4. The nightly REFRESH is the freshness contract made concrete: the view is at most 24 hours behind the source. That staleness is acceptable for monthly revenue and is documented so consumers know not to expect real-time numbers.
5. The redundancy is controlled: the view is derived, rebuildable, and never authoritative. If it is wrong, a refresh fixes it; the normalized fact_orders remains the single source of truth.

Output.

Property	Value
Read cost	tiny aggregate scan (indexed)
Refresh cost	one GROUP BY per night
Staleness	≤ 24h (documented contract)
Source of truth	normalized fact_orders (unchanged)

Rule of thumb. Reach for a materialized view (or summary table) when a specific expensive aggregate is read far more often than the source changes. Give it a unique index for concurrent refresh, pin an explicit staleness contract, and keep it derived — a materialized view you cannot rebuild from the source is just redundancy without the controls.

Worked example — when to deliberately violate 3NF

Detailed explanation. Not every denormalization is a whole warehouse table; sometimes you copy one attribute into a normalized OLTP table on purpose, accepting a transitive dependency to kill a hot join. The discipline is to do it consciously — measure the read pressure, add the redundant column, and add a mechanism (trigger or CDC) that keeps the copy correct. Consider copying product_name onto order_lines so the order-history screen renders without joining products.

• The justification. A hot, read-heavy path (order history) joins products millions of times a day for a value that rarely changes.
• The controlled violation. Store product_name redundantly on order_lines; accept the transitive dependency.
• The safeguard. A trigger (or a documented "historical snapshot" semantic) keeps the copy consistent — or intentionally freezes it as the name-at-time-of-order.

Question. Add a controlled redundant product_name to order_lines, justify the 3NF violation, and define how the copy stays correct.

Input.

Decision	Choice
Redundant column	order_lines.product_name
Motivation	remove a hot join on a read-heavy path
Consistency mechanism	snapshot-at-order-time (intentional freeze)
Source of truth for current name	products.product_name

Code.

-- Deliberate, documented 3NF violation: copy product_name onto the order line.
-- Rationale: the order-history screen is read millions of times/day and only
-- needs the product NAME AS IT WAS at order time (a snapshot, by design).
ALTER TABLE order_lines ADD COLUMN product_name TEXT;

-- Populate at write time from the current product (snapshot semantics):
CREATE OR REPLACE FUNCTION snapshot_product_name() RETURNS TRIGGER AS $$
BEGIN
    SELECT p.product_name INTO NEW.product_name
    FROM   products p
    WHERE  p.product_id = NEW.product_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_snapshot_product_name
BEFORE INSERT ON order_lines
FOR EACH ROW EXECUTE FUNCTION snapshot_product_name();

-- Read path: NO join to products for historical display.
SELECT order_id, product_id, product_name, qty
FROM   order_lines
WHERE  order_id = 1001;

-- 'products' remains the source of truth for the CURRENT name; the order line
-- deliberately preserves the name at purchase time (a feature, not a bug).

Step-by-step explanation.

1. This is a conscious 3NF violation: order_lines.product_name is transitively dependent (order_id → product_id → product_name). We accept it deliberately, for a measured reason.
2. The motivation is a hot read path: the order-history screen renders millions of times a day and would otherwise join products every time for a nearly-static value.
3. The consistency mechanism is snapshot semantics: a BEFORE INSERT trigger copies the current product_name onto the line at order time. This is not a caching bug — it is the correct behaviour, because a receipt should show the name the product had when it was bought, even if the product is renamed later.
4. Because the redundancy is a deliberate historical snapshot, there is no drift problem to solve: the copy is supposed to be frozen. If instead we wanted the always-current name, we would keep it in products and join — the redundancy would not be justified.
5. products stays the source of truth for the current name; order_lines.product_name is the historical name. Documenting this distinction is what turns a "denormalization" from a latent bug into a designed feature.

Output.

Read path	Join needed	Value shown
Order history (denormalized)	none	product name at order time
Product catalogue (normalized)	—	current product name
Rename a product later	—	history unchanged; catalogue updates

Rule of thumb. Deliberately violate 3NF only on a hot path, only with a measured read-vs-join justification, and only with an explicit consistency mechanism — a refresh, a trigger, or an intentional snapshot. Copying a value with no plan to keep it correct is not denormalization; it is a bug waiting for an audit.

Senior interview question on denormalization

A senior interviewer might ask: "Our normalized OLTP schema is clean, but the analytics team's dashboards each join six tables and time out. Walk me through how you'd serve them without corrupting the source — the star schema or OBT you'd build, how you keep the redundancy under control, the refresh strategy, and how you'd answer 'isn't denormalization just bad design?'"

Solution Using a derived star schema, scheduled refresh, and a source-of-truth contract

-- Keep the normalized OLTP schema as the SINGLE SOURCE OF TRUTH (unchanged).
-- Build a DERIVED star schema in the warehouse for analytics.

-- Conformed dimensions (deliberately denormalized: flat, wide, few rows)
CREATE TABLE dim_customer (
    customer_key BIGINT PRIMARY KEY,   -- surrogate key
    customer_id  BIGINT,               -- natural key from OLTP
    region       TEXT,
    tier         TEXT
);
CREATE TABLE dim_product (
    product_key  BIGINT PRIMARY KEY,
    product_id   BIGINT,
    category     TEXT,
    product_name TEXT
);
CREATE TABLE dim_date (
    date_key BIGINT PRIMARY KEY,
    day DATE, month DATE, quarter TEXT, year INT
);

-- Fact table: one row per order line, measures + dimension foreign keys
CREATE TABLE fact_sales (
    sale_id      BIGINT PRIMARY KEY,
    customer_key BIGINT REFERENCES dim_customer(customer_key),
    product_key  BIGINT REFERENCES dim_product(product_key),
    date_key     BIGINT REFERENCES dim_date(date_key),
    qty          INT,
    amount_cents BIGINT
);

-- Scheduled, idempotent ELT refresh from the normalized source (incremental merge)
MERGE INTO fact_sales tgt
USING staging_sales src            -- staged from OLTP via CDC / nightly extract
ON  tgt.sale_id = src.sale_id
WHEN MATCHED     THEN UPDATE SET qty = src.qty, amount_cents = src.amount_cents
WHEN NOT MATCHED THEN INSERT (sale_id, customer_key, product_key, date_key, qty, amount_cents)
                       VALUES (src.sale_id, src.customer_key, src.product_key,
                               src.date_key, src.qty, src.amount_cents);

-- Dashboards join a fact to a few WIDE dimensions (star), not a deep 3NF chain:
SELECT dp.category, dc.region, dd.month, SUM(fs.amount_cents) AS revenue
FROM   fact_sales fs
JOIN   dim_product  dp ON dp.product_key  = fs.product_key
JOIN   dim_customer dc ON dc.customer_key = fs.customer_key
JOIN   dim_date     dd ON dd.date_key     = fs.date_key
GROUP  BY dp.category, dc.region, dd.month;

Step-by-step trace.

Layer	Component	Purpose
Source of truth	normalized OLTP schema	correctness-critical writes, unchanged
Dimensions	dim_customer / dim_product / dim_date	denormalized, wide, few rows
Fact	fact_sales	one row per order line + measures
Refresh	scheduled MERGE from staging	idempotent, incremental, derived
Read path	star join (fact + wide dims)	shallow joins, warehouse-friendly

The normalized OLTP schema stays authoritative; the warehouse gets a derived star. Dimensions are deliberately denormalized (flat and wide) so a query joins the fact to a handful of small dimension tables instead of walking a deep normalized chain. A scheduled, idempotent MERGE from staged OLTP data refreshes the fact table incrementally, so the redundancy is controlled and rebuildable. The dashboard query becomes a shallow star join over pre-shaped dimensions — fast on columnar engines — and the "isn't denormalization bad?" objection is answered by "the source stays normalized; this is a disposable, refreshable read model derived from it."

Output:

Metric	Six-table 3NF join	Derived star schema
Dashboard latency	times out	seconds
Joins per query	6 (deep chain)	3 (fact + wide dims)
Source of truth	OLTP schema	still the OLTP schema
Redundancy control	n/a	scheduled idempotent refresh

Why this works — concept by concept:

• Source-of-truth contract — the normalized OLTP schema stays authoritative and anomaly-free; the star is derived, so denormalization never risks the correctness of the system of record.
• Star schema — a central fact surrounded by deliberately denormalized (flat, wide) dimensions turns a deep 3NF join chain into a shallow fact-to-dimension join, which is what columnar warehouses are optimised for.
• Surrogate keys — integer *_key columns on dimensions decouple the warehouse from OLTP natural keys and make joins and slowly-changing-dimension handling cheap and stable.
• Idempotent scheduled refresh — the MERGE from staging is rebuildable and incremental, so the controlled redundancy is always reproducible from the source and carries an explicit freshness contract.
• Cost — extra storage and a scheduled refresh job, in exchange for O(shallow-join) analytics instead of O(deep-join) timeouts. The redundancy cost is bounded and managed; the read speedup is large and constant — the exact trade OLAP workloads want.

Dimensional modeling
Topic — dimensional-modeling
Dimensional-modeling problems on star schemas and facts

Practice →

ETL
Topic — etl
ETL problems on building denormalized read models

Practice →

---

Cheat sheet — normalization recipes

• The normal-forms ladder in one line each. 1NF = atomic cells, no repeating groups. 2NF = 1NF + no partial dependency on part of a composite key. 3NF = 2NF + no transitive dependency (key → non-key → non-key). BCNF ("3.5NF") = every determinant is a candidate key. 4NF = BCNF + no independent multivalued dependency. 5NF = 4NF + no non-trivial join dependency. Climb in order; each rung assumes the one below.
• Functional-dependency rules (Armstrong's axioms). Reflexivity: if Y ⊆ X then X → Y (trivial). Augmentation: if X → Y then XZ → YZ. Transitivity: if X → Y and Y → Z then X → Z. Derived: union (X → Y, X → Z ⇒ X → YZ), decomposition, pseudotransitivity. Use these to compute the attribute closure X⁺ and find candidate keys.
• Anomaly decoder. Update anomaly = a single fact needs a multi-row UPDATE (redundant copies drift). Insertion anomaly = you cannot store fact A without unrelated fact B (no row to hold it). Deletion anomaly = deleting fact B destroys the only copy of fact A. All three trace to one fact stored in more than one place.
• 1NF → BCNF step recipe. (1) Make cells atomic, push repeating groups to child rows. (2) Write every FD and find the candidate key(s). (3) Remove partial dependencies → 2NF. (4) Remove transitive dependencies → 3NF. (5) For every FD X → Y, if X is not a candidate key, decompose on X → BCNF. Verify each split is lossless (shared column is a key of one side).
• 3NF vs BCNF in one sentence. 3NF allows X → Y when X is a superkey or Y is prime; BCNF drops the "or Y is prime" clause and demands X be a candidate key. They differ only when a table has overlapping candidate keys and a non-key determinant of a prime attribute (e.g. teacher → subject).
• Candidate-key finder. Compute the closure of attribute sets: X is a superkey iff X⁺ = all attributes; a minimal superkey is a candidate key. Prime attribute = in some candidate key; non-prime = in none. Multiple overlapping candidate keys are the flag for a possible 3NF-but-not-BCNF table.
• Lossless-join test. A decomposition of R into R1, R2 is lossless iff R1 ∩ R2 is a candidate key of R1 or of R2. If the shared column is not a key of either side, the re-join invents spurious rows — the split is lossy and wrong.
• Dependency-preservation caveat. A BCNF decomposition can lose the ability to enforce an FD with a single table's key (e.g. (room, time_slot) → client). When that FD matters, either add a cross-table constraint/trigger or consciously stop at 3NF — 3NF is always dependency-preserving.
• When to stop (3.5NF default). Normalize OLTP systems of record to BCNF. Go to 4NF only on a demonstrated independent multivalued dependency; go to 5NF only on a demonstrated join dependency (test by projecting and re-joining — lossless ⇒ decompose, spurious rows ⇒ keep). "Might have one" is never a reason.
• When to denormalize (OLTP vs OLAP). Normalize for writes (OLTP: many small correctness-critical writes). Denormalize for reads (OLAP: few huge join-averse scans). Keep the normalized store authoritative; build denormalized star / OBT / summary models derived from it.
• Star schema recipe. Central fact (one row per event, measures + dimension FKs) surrounded by flat, wide dimension tables with surrogate keys. Deliberately denormalized dimensions turn a deep 3NF chain into a shallow fact-to-dimension join — ideal for columnar warehouses.
• Materialized-view / controlled-redundancy policy. Precompute a hot aggregate/join, store it, add a unique index for concurrent refresh, refresh on a schedule, and pin an explicit staleness contract. Keep every denormalized artifact idempotent and rebuildable from the normalized source — derived redundancy is a feature; unmanaged redundancy is a bug.
• Snapshot vs cached copy. If a copied value should reflect history (product name at order time), a BEFORE INSERT snapshot trigger is correct and needs no refresh. If it should reflect the current value, keep it normalized and join, or refresh the copy on change — do not freeze it.

Frequently asked questions

What is database normalization?

Database normalization is the process of structuring a relational schema so that every fact is stored exactly once, by decomposing tables until each non-trivial functional dependency has a whole candidate key on its left-hand side. It proceeds through a fixed ladder of normal forms — 1NF, 2NF, 3NF, and BCNF — where each form removes a specific class of data redundancy and the insert, update, and delete anomalies that redundancy causes. The payoff is not primarily saving disk space; it is correctness: a normalized schema cannot store a contradiction, because a fact only exists in one row to begin with. sql data normalization is the load-bearing fundamental of transactional database design and the single most-tested topic in a data-engineering interview.

Can you explain 1NF, 2NF, and 3NF simply?

1NF requires atomic cells: one value per cell, no repeating groups (phone1, phone2, phone3), no lists stuffed into a column. 2NF applies only when the key is composite and requires that no non-key attribute depend on just part of that key — e.g. with key (order_id, product_id), product_name depending on product_id alone is a partial dependency to be removed. 3NF requires that no non-key attribute depend on another non-key attribute — the transitive dependency order_id → zip → city is the smell, fixed by splitting zip → city into its own table. The mnemonic that carries the interview: "the key, the whole key, and nothing but the key" — 1NF gets you a key, 2NF makes attributes depend on the whole key, 3NF makes them depend on nothing but the key. 1NF 2NF 3NF are cumulative: a 3NF table is automatically in 2NF and 1NF.

What is the difference between 3NF and BCNF?

The difference is a single clause. 3NF permits a functional dependency X → Y when either X is a superkey or Y is a prime attribute (part of some candidate key); BCNF drops that second escape hatch and demands that X be a candidate key for every non-trivial FD. Practically, the two coincide unless a table has overlapping candidate keys — the classic counterexample is teaching(student_id, subject, teacher) with teacher → subject, which is in 3NF (because subject is prime) but not BCNF (because teacher is not a candidate key). BCNF is nicknamed "3.5NF" because it is "3NF plus a little more," and it is the practical stopping point for operational schemas — with one caveat: a BCNF decomposition is occasionally not dependency-preserving, which is the one situation where engineers deliberately stop at 3NF.

What is a functional dependency?

A functional dependency written X → Y is the statement "X determines Y" — any two rows that agree on X must agree on Y. The left side X is the determinant; a minimal attribute set that determines every column is a candidate key; an attribute in some candidate key is prime. Functional dependencies are the vocabulary in which every normal form is defined: a 2NF violation is an FD whose left side is part of a composite key, a 3NF violation is an FD whose left side is a non-key attribute, and a BCNF violation is an FD whose left side is not a candidate key. Writing out a table's FDs and computing its candidate keys (via attribute closure) is the mechanical first step of every normalization exercise — spot the FD with the wrong left side and you have found the violation.

When should I denormalize?

You should denormalize when the workload flips from transactional writes to analytical reads — that is, for OLAP read models, not for OLTP systems of record. denormalization deliberately reintroduces data redundancy (a star schema, a one-big-table, a materialized view) so that a read scans one wide table instead of joining many normalized ones, which is exactly what columnar warehouses reward. The discipline is to keep the normalized store authoritative and build the denormalized model derived from it, with an idempotent, scheduled refresh and an explicit staleness contract, so the redundancy is controlled and rebuildable rather than a latent update anomaly. Deliberately violating 3NF inside an OLTP table is defensible only on a measured hot path and only with a consistency mechanism (a trigger, a snapshot semantic, or a refresh). "Normalize the source; denormalize the read models you derive from it" is the whole doctrine.

Do analytics warehouses need normalization?

Analytics warehouses are usually deliberately denormalized, but that is a decision derived from normalization, not a rejection of it. The upstream operational system of record is normalized to BCNF so its writes stay correct and anomaly-free; the warehouse then builds star schemas or one-big-table models from that source, trading controlled redundancy for scan speed on join-averse analytical queries. The redundancy is safe precisely because the normalized source remains authoritative and the warehouse tables are refreshable copies — a star schema's dimensions are denormalized on purpose, and a materialized view is redundancy with a refresh contract. So the honest answer is "the warehouse is denormalized, the source it derives from is normalized, and understanding both — plus when each is right — is the senior signal." database normalization and denormalization are two sides of the same design discipline, chosen by workload.

Practice on PipeCode

• Drill the SQL practice library → for the normalization, functional-dependency, decomposition, and anomaly problems senior interviewers love.
• Model warehouse schemas on the dimensional-modeling practice library → for star schemas, fact/dimension grain, surrogate keys, and deliberate denormalization.
• Rehearse schema trade-offs on the design practice library → for candidate-key analysis, 3NF-vs-BCNF decisions, and OLTP-vs-OLAP modeling.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the normal-forms ladder against real graded inputs.

Lock in normalization muscle memory

Docs define the normal forms. PipeCode drills make you *apply* them — naming the anomaly, writing the functional dependency, choosing the lossless decomposition, and knowing when to denormalize for analytics. Pipecode.ai is Leetcode for Data Engineering — schema-first practice tuned for the modeling trade-offs data engineers actually defend in interviews and design reviews.

Practice SQL problems →
Practice design problems →