Database Normalization: 1NF, 2NF and 3NF with Examples

Normalization is the process of organizing tables so that each fact is stored once. It reduces duplicate data and prevents the update problems that come with it. Interviewers usually ask for 1NF, 2NF and 3NF with a simple example, so this article uses one example all the way through.

Why normalize? The three anomalies

Imagine a single wide table:

order_id	customer_name	customer_city	product	unit_price	quantity
1	Asha	Pune	Keyboard	25	1
1	Asha	Pune	Mouse	10	2
2	Ben	Delhi	Keyboard	25	1

Storing the same facts in many rows causes three problems:

Insertion anomaly: you cannot add a new product until someone orders it, because every row needs an order.
Update anomaly: if the keyboard price changes, you must change it in every row. Miss one and the data contradicts itself.
Deletion anomaly: deleting order 2 also deletes the only record that Ben exists.

First normal form (1NF)

A table is in 1NF when:

every column holds a single, atomic value (no lists such as "9990001, 9990002" in one cell),
there are no repeating groups (no columns like phone1, phone2, phone3),
each row can be uniquely identified (there is a key).

The fix for a multi-valued column is to move the values into rows (or a separate table), one value per row.

Second normal form (2NF)

A table is in 2NF when it is in 1NF and has no partial dependency: no non-key column depends on only part of a composite key.

order_items has the composite key (order_id, product_id):

order_id	product_id	product_name	unit_price	quantity
1	7	Keyboard	25	1

product_name and unit_price depend only on product_id, which is just half of the key. Move them to their own table:

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price   DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL REFERENCES products (product_id),
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

If you need the price at the time of purchase, keep a column in on purpose. That is a different fact from today's catalogue price.

Third normal form (3NF)

A table is in 3NF when it is in 2NF and has no transitive dependency: no non-key column depends on another non-key column.

id	name	department_id	department_name
1	Asha	1	Engineering

depends on , which is not the key of this table. The chain is to to . Move the department into its own table and keep only here.

A memorable summary: every non-key column must depend on the key, the whole key, and nothing but the key.

BCNF in one paragraph

Boyce-Codd normal form is a slightly stricter 3NF: for every dependency where determines , must be a key (a candidate key or a superset of one). Most 3NF tables already satisfy it. For a fresher interview, the definition is usually enough.

Denormalization: breaking the rules on purpose

Denormalization adds redundancy deliberately to make reads faster, for example storing an order total on the order row, or flattening tables for reports. It is common in data warehouses and reporting databases (OLAP), where reads are heavy and data changes rarely. The cost is extra storage and the work of keeping the copies consistent. Transactional systems (OLTP) that change data constantly are normally kept in 3NF.

Quick answers for the interview

Question	Short answer
1NF	Atomic values, no repeating groups, a key
2NF	1NF plus no partial dependency on a composite key
3NF	2NF plus no transitive dependency
Why normalize	Less duplication and no insert, update or delete anomalies
Why denormalize	Faster reads and simpler reporting, at the cost of redundancy

Practise

Take a spreadsheet you know (an invoice, a student list) and normalize it to 3NF on paper. Then check yourself with the Indexes, Design and Transactions quiz.