SQL Practice Platform
Article •

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 →

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 →