Normalization in DBMS Explained - 1NF 2NF 3NF and BCNF

If you have ever worked with a database that had duplicate data scattered across multiple tables, records that refused to update cleanly, or rows that left behind garbage data when deleted — you have experienced what happens when a database is not normalised. Normalization is the process that fixes all of that, and understanding it properly is one of the most important skills any developer or database administrator can have.

In this guide we will walk through every normal form — 1NF, 2NF, 3NF, and BCNF — with clear examples and real tables so you can see exactly what changes at each stage and why it matters.

What Is Normalization in DBMS?

Normalization is the process of organising a relational database to reduce data redundancy and eliminate data anomalies. It works by decomposing large, poorly structured tables into smaller, well-structured ones while preserving all the relationships between the data.

A database that is not normalised suffers from three main problems:

• Insertion anomaly — you cannot add new data without adding unrelated data at the same time. For example, you cannot add a new course to the database until at least one student has enrolled in it.
• Update anomaly — the same piece of information is stored in multiple rows. Updating it in one place but forgetting another leaves the database in an inconsistent state.
• Deletion anomaly — deleting one piece of data accidentally destroys other unrelated data. For example, deleting the last student enrolled in a course also deletes the course record entirely.

Normalization solves all three by making sure each piece of information lives in exactly one place in the database.

Types of Normal Forms

Normalization is done in stages called Normal Forms. Each stage builds on the previous one — a table cannot be in 2NF unless it is already in 1NF, and cannot be in 3NF unless it is already in 2NF.

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)

Most real-world databases aim for 3NF or BCNF. Going beyond that (4NF, 5NF) is rarely necessary in practice and can actually make queries more complex without meaningful benefit.

1. First Normal Form (1NF)

A table is in First Normal Form when every column contains only atomic values — meaning each cell holds a single indivisible value, not a list or a set of values.

Rule: No column should contain multiple values in a single cell. Every row must be unique.

Here is a table that violates 1NF:

The Courses column holds multiple values in a single cell — that violates 1NF. To fix it, each value gets its own row:

Now every cell holds a single atomic value. This table is in 1NF. Notice however that StudentName is still repeated for every course — that redundancy gets fixed in the next stage.

2. Second Normal Form (2NF)

A table is in Second Normal Form when it is already in 1NF and every non-primary-key column is fully dependent on the entire primary key — not just part of it.

This rule only applies to tables with a composite primary key (a primary key made up of more than one column). If your table has a single-column primary key and is already in 1NF, it is automatically in 2NF.

Rule: Remove partial dependencies — columns that depend on only part of a composite primary key should be moved to their own table.

Consider this table with a composite primary key of (StudentID, CourseID):

Here StudentName depends only on StudentID (not CourseID), and CourseName depends only on CourseID (not StudentID). These are partial dependencies — a 2NF violation. Only Grade depends on both StudentID and CourseID together.

Fix it by splitting into three tables:

-- Students table
Students(StudentID 🔑, StudentName)

-- Courses table
Courses(CourseID 🔑, CourseName)

-- Enrollments table (junction table)
Enrollments(StudentID 🔑, CourseID 🔑, Grade)

Now each column depends on the full primary key of its table. No partial dependencies remain. This is 2NF.

3. Third Normal Form (3NF)

A table is in Third Normal Form when it is already in 2NF and there are no transitive dependencies — meaning non-key columns should not depend on other non-key columns.

Rule: Every non-key column must depend directly on the primary key, nothing else.

Here is a table in 2NF that violates 3NF:

The problem here is that City depends on ZipCode, not directly on StudentID. This is a transitive dependency: StudentID → ZipCode → City. If the city name for a zip code ever changes, you have to update every student row with that zip code — that is exactly the update anomaly we were trying to avoid.

Fix by separating ZipCode and City into their own table:

-- Students table (3NF)
Students(StudentID 🔑, StudentName, ZipCode)

-- ZipCodes table
ZipCodes(ZipCode 🔑, City)

Now City depends on ZipCode's primary key directly, and the Students table only holds data that depends on StudentID. No transitive dependencies remain. This is 3NF.

4. Boyce-Codd Normal Form (BCNF)

BCNF is a slightly stronger version of 3NF. A table is in BCNF when it is in 3NF and for every functional dependency (A → B), A must be a superkey — meaning A uniquely identifies every row in the table.

Most tables in 3NF are automatically in BCNF. The difference only shows up in specific edge cases involving multiple overlapping candidate keys.

Rule: For every dependency X → Y, X must be a superkey of the table.

Here is the classic example that shows the 3NF vs BCNF difference:

Assume each teacher teaches only one subject, but a subject can have multiple teachers. The dependency here is Teacher → Subject (knowing the teacher tells you the subject). But Teacher is not a superkey — it does not uniquely identify each row. This violates BCNF.

Fix it by decomposing into two tables:

-- Teacher to Subject mapping
TeacherSubject(Teacher 🔑, Subject)

-- Student enrollment
StudentTeacher(Student, Teacher)

Now every determinant in both tables is a superkey. The table is in BCNF.

Summary — All Normal Forms at a Glance

When Should You Stop Normalising?

In most production databases, 3NF is the practical target. Going to BCNF is worthwhile when you have complex relationships with multiple overlapping candidate keys. Going beyond BCNF to 4NF or 5NF is rarely necessary and can actually hurt query performance by creating too many joins.

There are also real situations where deliberate denormalization makes sense — intentionally storing some redundant data to avoid expensive joins in high-traffic read operations. Reporting databases, data warehouses, and analytics systems often use denormalized schemas (like star schema) for performance reasons.

The goal of normalization is not to hit the highest normal form possible — it is to find the right balance between data integrity and query performance for your specific use case.

Final Thought

Normalization is one of those foundational concepts that pays dividends throughout your entire career as a developer. Understanding it properly means you design databases that are easier to maintain, easier to query, and far less likely to develop silent data corruption over time.

Start with 1NF and work your way up. In most cases you will reach 3NF naturally just by asking the right question at each stage — does this column belong here, or does it belong in its own table?

Have a specific database design you are trying to normalise? Drop us a message on our contact page and we will be happy to help you work through it.