Database Normalization

#Introduction

Normalization is one of the most important database-design concepts for enterprise applications — and one of the most frequently asked SQL and system-design interview topics for Java backend developers.

This chapter covers why normalization is needed, data redundancy, update/insert/delete anomalies, First (1NF), Second (2NF), Third (3NF) and Boyce-Codd (BCNF) Normal Forms, real-world schema design and enterprise best practices.

#Why Do We Need Normalization?

Imagine storing employee information like this:

The same manager details are repeated, more storage is needed, the table is hard to maintain, and inconsistent data becomes likely. Normalization solves these issues.

#Problems Without Normalization

#What Is Normalization?

Normalization is the process of organizing data to eliminate redundancy, improve consistency, maintain integrity, simplify updates and reduce anomalies — by splitting tables so that each fact is stored in exactly one place.

The forms build on each other:

#First Normal Form (1NF)

Rule: every column contains atomic (single) values — no lists or repeating groups.

Not in 1NF — one column holds several values:

Converted to 1NF — each cell holds one value:

Another example — bad:

Good:

In a real schema this becomes a separate customer_phone (customer_id, phone_number) table, keyed on both columns. Adding columns phone1, phone2, phone3 is also a repeating group and doesn't satisfy the spirit of 1NF.

#Second Normal Form (2NF)

Rules:

• The table is already in 1NF.
• Every non-key column depends on the entire composite key, not just part of it (no partial dependency).

2NF mainly matters when the table has a composite primary key. A table whose key is a single column is automatically in 2NF once it's in 1NF.

Example: an Enrollment Table

Primary key: (student ID, course ID). The problem:

• Student name depends only on student ID.
• Course name depends only on course ID.

Partial dependencies exist, so student and course names repeat for every enrollment.

Converted to 2NF

student

course

enrollment

Every non-key column now depends on the entire key of its table.

#Third Normal Form (3NF)

Rules:

• The table is already in 2NF.
• There is no transitive dependency — a non-key column must not depend on another non-key column.

Example

Manager depends on department, not directly on the employee — employee_id → department → manager is a transitive dependency.

Converted to 3NF

employee

department

No transitive dependency remains. (In practice the department would have a surrogate department_id that employee references as a foreign key.)

#Boyce-Codd Normal Form (BCNF)

BCNF is a stricter version of 3NF.

Rule: for every non-trivial functional dependency X → Y, X must be a candidate key (more precisely, a super key) — every determinant must be a key.

Example: a Teaching Table

Business rules: one teacher always teaches in one classroom; a classroom can host many subjects. So teacher → classroom — but teacher is not a candidate key (the key is teacher + subject). This violates BCNF.

Converted to BCNF

teacher

teaching

#Normalization Summary

#Real-World Enterprise Example

Before normalization — one orders table: order ID, customer, customer city, product, price. Customer city and product price repeat on every row, and it's hard to maintain.

After normalization:

Much cleaner.

#Normalization vs Denormalization

When NOT to Normalize?

Sometimes performance matters more — reporting databases, data warehouses, analytics dashboards and read-heavy applications. There, controlled denormalization (summary tables, duplicated columns, star schemas) may improve performance, at the cost of keeping the copies in sync.

#Mini Project

Normalize this table up to 3NF:

#Practice Problems

Easy

1. Identify repeating groups.
2. Convert a table to 1NF.
3. Identify partial dependencies.
4. Convert a table to 2NF.
5. Identify transitive dependencies.

Medium

1. Normalize an employee–department table.
2. Normalize a student–course table.
3. Design a library-management schema.
4. Design a hospital-management schema.
5. Normalize an e-commerce order table.

Interview level

1. Explain 1NF, 2NF and 3NF with examples.
2. Explain BCNF with a real-world scenario.
3. Compare normalization and denormalization.
4. Explain insert, update and delete anomalies.
5. Design a fully normalized database for an online shopping application.

#Key Takeaways

• 1NF ensures atomic values.
• 2NF removes partial dependencies.
• 3NF removes transitive dependencies.
• BCNF is a stricter form of 3NF for specific dependency scenarios.
• Normalization improves consistency, reduces redundancy and prevents anomalies.
• Most enterprise OLTP databases target 3NF, while reporting systems may intentionally denormalize.