Database Normalization in SQL – 1NF to BCNF Full Guide (With Examples & Practice Questions)

Normalization was one of those topics that felt super theoretical when I first read about it. I thought, "Why bother? My queries work fine."

Then I saw a real messy table in an interview question — duplicate data everywhere, update anomalies, deletion problems — and suddenly normalization clicked.

Today, we're going from zero to mastery on database normalization: 1NF, 2NF, 3NF, and BCNF.

I'll explain each form with simple, real-world examples using our familiar e-commerce database (users, products, orders). No heavy theory — just practical understanding you can use in interviews and real projects.

All examples and practice questions are live on sqlpractice.in

Let's start!

Why Normalize? (The Problems It Solves)

Imagine a single table with everything:

Problems:

• Insertion anomaly: Can't add a customer without an order
• Update anomaly: Change Rahul's email → update multiple rows
• Deletion anomaly: Delete order → lose customer info

Normalization splits data into logical tables to eliminate these anomalies.

First Normal Form (1NF)

Rule:

• Each column must have atomic (single) values
• No repeating groups or arrays
• Each record is unique (usually via Primary Key)

Practice Question 1 (Easy) Convert a denormalized table with multiple phone numbers in one column to 1NF. Practice here →

Second Normal Form (2NF)

Rule:

• Must be in 1NF
• No partial dependency — non-prime attributes must depend on the entire Primary Key

Example (not 2NF): Composite PK: (order_id, product_id)

Problem: customer_name depends only on order_id, not full key.

Fix to 2NF: Separate into:

• orders (order_id, customer_name)
• order_items (order_id, product_id, price)

Practice Question 2 (Medium) Identify partial dependency and normalize to 2NF. Practice here →

Third Normal Form (3NF)

Rule:

• Must be in 2NF
• No transitive dependency — non-prime attributes must depend only on candidate keys, not on other non-prime attributes

Problem: city_pincode depends on customer_city, not directly on key.

Fix to 3NF: Create separate cities table:

• customers (customer_id, name, city_id)
• cities (city_id, city_name, pincode)

Practice Question 3–4 (Medium) Normalize a table with department name and manager repeated in employee table. Practice here →

4NF (Fourth Normal Form)

What problem does it solve?

Multi-Valued Dependency (MVD)

Simple Definition: A table is in 4NF if it is in BCNF and has no multi-valued dependencies.

Real-life Example:

Imagine a table Student_Skills_Hobbies:

Here, Skills and Hobbies are independent of each other. Rahul’s skills don’t depend on his hobbies.

This causes redundancy and update anomalies.

Solution (4NF): Split into two separate tables:

• Student_Skills (Student, Skill)
• Student_Hobbies (Student, Hobby)

When to apply 4NF?

• When one entity has two or more independent multi-valued attributes.
• Common in cases like: Employee → Skills, Employee → Projects, Employee → Languages, etc.

Practice Question: Design a 4NF structure for a student who can have multiple skills and multiple certifications independently.

5NF (Fifth Normal Form) / Project-Join Normal Form

This is the rarest and most advanced.

What it solves?

Join Dependency — When a table can be split into multiple smaller tables and rejoined without losing or creating extra data.

Simple Example:

Suppose we have a table Supplier_Product_Region:

This table has a join dependency. It can be broken into 3 smaller tables without losing information:

• Supplier_Product
• Product_Region
• Supplier_Region

5NF Rule: A table is in 5NF if it is in 4NF and every join dependency is implied by its candidate keys.

Real Talk: Most developers never need 5NF in their career. It is mostly theoretical and used in extremely complex enterprise systems.

Boyce-Codd Normal Form (BCNF)

Rule: Stronger than 3NF

• For every dependency A → B, A must be a superkey

When 3NF fails BCNF: Multiple candidate keys with overlapping attributes.

Example: Professor teaches Subject in Classroom

Candidate keys: (Professor, Subject) and (Subject, Classroom)

Dependency: Subject → Classroom (same subject always same room)

Subject is not superkey → violates BCNF.

Fix: Split into two tables.

Practice Question 5 (Hard) Normalize the classic "teaching" example to BCNF. Practice here →

Common Interview Questions

1. "Explain normalization with real example."
2. "Difference between 3NF and BCNF?"
3. "Is it always necessary to normalize to BCNF?"
4. "What are insertion/update/deletion anomalies?"

Full Practice Pack All normalization questions (easy to hard): Practice normalization pack →