SQL Normalization Interview Questions and Answers

Normalization is the database-design topic interviewers use to test whether you can reason about data structure, not just query it. It appears in almost every backend, data-engineering and analyst interview, usually as "take this messy table and normalize it" — a task that exposes whether you understand functional dependencies or have only memorized the phrase "1NF, 2NF, 3NF".

The examples use an order-management scenario and standard SQL. Normalization is engine-independent — the concepts apply identically across PostgreSQL, MySQL, SQL Server and Oracle.

What is normalization and what problem does it solve?

Normalization organizes data so each fact is stored exactly once, eliminating the insertion, update and deletion anomalies that redundant tables cause. It is applied as a series of normal forms, each removing a specific kind of harmful dependency.

The pitch to lead with: redundancy is the enemy, because the same fact stored in many rows can drift out of sync. Normal forms are a systematic way to remove that redundancy. Say that, then walk the forms.

-- Unnormalized: repeats customer + product data on every order line
-- orders(order_id, customer_name, customer_city, product, product_price, qty)

Q1. What is First Normal Form (1NF)?

1NF requires atomic (indivisible) column values and no repeating groups — each cell holds a single value, and you don't stuff a list into one column or number columns like item1, item2, item3.

-- Violates 1NF: multiple products in one column
-- order(id, products) -> (1, 'Pen, Pencil, Eraser')

-- 1NF: one product per row
CREATE TABLE order_items (
  order_id INT,
  product  VARCHAR(100),
  qty      INT,
  PRIMARY KEY (order_id, product)
);

The two violations to name are multi-valued cells (a comma-separated list) and repeating column groups (phone1, phone2, phone3). Fixing 1NF usually means splitting those into separate rows in a child table.

Interview note: Trap: "is storing JSON in a column a 1NF violation?" Purists say yes; pragmatically, modern databases treat JSON as a supported type. Know the theory, then note the practical exception.

Q2. What is Second Normal Form (2NF)?

2NF requires 1NF plus no partial dependency: no non-key column may depend on only part of a composite primary key. It only matters when the primary key is composite.

-- Composite key (order_id, product). product_price depends on product ALONE -> partial dependency
-- order_items(order_id, product, product_price, qty)

-- 2NF: move product_price to a table keyed by product
CREATE TABLE products (
  product VARCHAR(100) PRIMARY KEY,
  price   DECIMAL(10,2)
);
CREATE TABLE order_items (
  order_id INT, product VARCHAR(100), qty INT,
  PRIMARY KEY (order_id, product)
);

product_price depends on product, which is only half the key — so it's stored redundantly on every order line for that product. Moving it to a products table keyed by product removes the partial dependency. The phrase "depends on part of the key" is the exact wording interviewers want.

Interview note: Follow-up: "can a table with a single-column key violate 2NF?" No — partial dependency needs a composite key. Single-column-key tables in 1NF are automatically in 2NF.

Q3. What is Third Normal Form (3NF)?

3NF requires 2NF plus no transitive dependency: no non-key column may depend on another non-key column. Every non-key attribute must depend on the key, the whole key, and nothing but the key.

-- customer_city depends on customer_id (a non-key attribute) -> transitive dependency
-- orders(order_id, customer_id, customer_name, customer_city)

-- 3NF: move customer attributes to a customers table
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100),
  city        VARCHAR(100)
);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id)
);

customer_name and customer_city depend on customer_id, not directly on order_id — a transitive chain. Splitting customers into their own table means a customer's city is stored once. The mnemonic "the key, the whole key, and nothing but the key" is the crisp way to state 1NF/2NF/3NF together.

Interview note: Trap: "why is redundant customer_city harmful?" Update anomaly: correcting a customer's city means updating every order row, and missing one leaves contradictory data.

Q4. What are insertion, update and deletion anomalies?

They are the three concrete harms of redundancy. Insertion: you can't record one fact without another (can't add a product until someone orders it). Update: a changed value must be fixed in many rows. Deletion: removing one row loses unrelated data.

Ground each in the unnormalized orders table: you can't add a new product without an order (insertion), changing a customer's city touches every order (update), and deleting a customer's only order erases their address (deletion). Normalization removes all three by storing each fact once.

-- Deletion anomaly avoided: deleting an order no longer erases the customer
DELETE FROM orders WHERE order_id = 10;   -- customer row survives in its own table

Being able to name a specific anomaly for a given bad schema — rather than reciting definitions — is what distinguishes a strong answer.

Interview note: Follow-up: "which anomaly does 3NF specifically target?" Update anomalies from transitive dependencies most directly, though normalization reduces all three together.

Q5. What is a functional dependency?

A functional dependency A → B means each value of A determines exactly one value of B. Normalization is really the process of arranging tables so every functional dependency has its determinant as a key.

-- customer_id -> customer_city  (each customer has one city)
-- (order_id, product) -> qty     (each line has one quantity)

Functional dependencies are the formal engine behind the normal forms: 2NF removes dependencies on part of a key, 3NF removes dependencies whose determinant isn't a key at all. Introducing the FD vocabulary shows you understand why the forms are defined the way they are.

Interview note: Trap: "what is a determinant?" The left side of a functional dependency — the attribute(s) that determine another. BCNF is defined in terms of determinants being candidate keys.

Q6. What is BCNF and how is it stricter than 3NF?

Boyce-Codd Normal Form requires that every determinant is a candidate key. It closes a rare 3NF loophole where a non-key attribute determines part of a candidate key.

3NF permits a case where an attribute that is part of a candidate key depends on a non-key attribute; BCNF forbids it by demanding every left-hand side of an FD be a full candidate key. Most well-designed 3NF tables already satisfy BCNF; the difference surfaces mainly with overlapping candidate keys.

-- Classic BCNF issue: overlapping candidate keys where a non-key attribute
-- determines part of a key, e.g. (student, subject) -> teacher, but teacher -> subject

You don't need to derive the textbook example flawlessly; naming that BCNF = "every determinant is a candidate key" and that it handles overlapping-key edge cases is enough to score well.

Interview note: Follow-up: "is BCNF always achievable while preserving dependencies?" Not always — some decompositions to BCNF lose dependency preservation, which is why 3NF is sometimes kept deliberately.

Q7. When and why do you denormalize?

When a hot read path pays too much for joins and the data changes rarely — reporting tables, analytics marts, and caches. You reintroduce controlled redundancy to speed reads, accepting the burden of keeping copies consistent.

-- Denormalized reporting table: precomputed, redundant, fast to read
CREATE TABLE order_report (
  order_id      INT,
  customer_name VARCHAR(100),   -- duplicated from customers on purpose
  city          VARCHAR(100),
  total         DECIMAL(10,2)
);

The senior framing is that normalization and denormalization are a deliberate read/write trade-off, not right versus wrong: normalize the transactional system for integrity, denormalize selectively for query speed, and manage the resulting consistency with triggers, application logic or scheduled refreshes.

Interview note: Trap: "how do you keep a denormalized copy correct?" Refresh it deliberately — via a materialized view, a trigger, or a batch job. Uncontrolled redundancy is exactly what normalization warned against.

Q8. Take an unnormalized table to 3NF — walk through it.

Identify the atomic-value and repeating-group violations (1NF), the composite-key partial dependencies (2NF), then the transitive dependencies on non-key columns (3NF), splitting a table at each step.

Starting from orders(order_id, customer_id, customer_name, customer_city, product, product_price, qty):

-- 3NF result: three clean tables, each fact stored once
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(100));
CREATE TABLE products  (product VARCHAR(100) PRIMARY KEY, price DECIMAL(10,2));
CREATE TABLE order_items (
  order_id    INT,
  customer_id INT REFERENCES customers(customer_id),
  product     VARCHAR(100) REFERENCES products(product),
  qty         INT,
  PRIMARY KEY (order_id, product)
);

Narrating the decomposition step by step — which dependency each split removes — is the exact task most normalization interviews end on. Practising this transformation out loud is the single most valuable prep for this topic.

Interview note: Follow-up: "how far should you normalize in production?" Usually to 3NF/BCNF for the transactional model, then denormalize specific read paths as measured performance requires. 3NF is the practical default.

How to prepare

Take one deliberately messy table and normalize it to 3NF on paper, naming the specific anomaly each step removes — this is precisely the exercise interviewers run, and doing it a few times makes the functional-dependency reasoning automatic. Then reverse the process: build a denormalized reporting table and articulate why the redundancy is worth it, so you can argue both directions of the trade-off.

Pair this with the constraints questions, since primary and foreign keys implement the structure normalization designs, and the indexes set, because normalized schemas rely on joins that indexes make fast. The SQL learning path covers the relational model these normal forms formalize. A mock interview centered on schema design is the best way to rehearse taking a table from unnormalized to 3NF under time pressure.