DBMS Normalization Explained: 1NF, 2NF, 3NF with Examples

What Is Normalization in DBMS?

Normalization is the process of designing database tables so that each fact is stored in only one place.

When the same fact is repeated in many rows, the database becomes heavy, inconsistent, and dangerous to update. Normalization removes this repetition by splitting one big table into smaller, well-structured tables.

In simple words:

Normalization = right data in the right table.

The main goals of normalization are:

• Reduce duplicate data (redundancy)

• Avoid update conflicts

• Make insert and delete operations safe

• Keep the design easy to maintain

Why Normalization Matters: The Real Problems

When a table is not normalized, the same information is repeated in many rows. This creates three classic problems, called anomalies.

These are not theory-only problems. Real applications face them whenever table design is careless.

The Messy Table We Will Fix

Let’s use one small table and carry it through every normal form. This is the same method you should use in exam questions.

This table stores everything in one place. It looks convenient, but it breaks the basic rules of good design.

Quick Terms You Need First

Before applying the normal forms, understand these six terms. Every normalization question uses them.

If these terms are clear, normalization becomes a simple checking game instead of memorization.

1NF: Single Value in Every Cell

A table is in First Normal Form (1NF) when:

• Every cell holds exactly one value

• There are no repeating groups

• Every row is unique and has a primary key

Our messy table fails because one cell contains multiple courses, instructors, offices, and grades. To fix it, we split each multi-value row into separate rows.

Now every cell holds one value. The primary key becomes the combination (Student_ID, Course), because a student can take multiple courses.

1NF check: Can any cell answer with a list? If yes, the table is not in 1NF.

2NF: No Partial Dependency

A table is in Second Normal Form (2NF) when it is already in 1NF and has no partial dependency.

Partial dependency means a non-key column depends on only part of the primary key. Our 1NF table has the composite key (Student_ID, Course). Now check every non-key column:

• Student_Name depends only on Student_ID → partial dependency ❌

• Instructor depends only on Course → partial dependency ❌

• Office depends only on Course → partial dependency ❌

• Grade depends on both Student_ID and Course → full dependency ✅

To fix this, we move the partially dependent columns into their own tables.

Table 1: Student

Table 2: Course_Info

Table 3: Enrollment

Now every non-key column depends on the full key of its own table. The design is in 2NF.

2NF check: If part of the key alone can determine a column, move that column to its own table.

The Normalization Path

Remember the order. Each normal form builds on the previous one.

3NF: No Transitive Dependency

A table is in Third Normal Form (3NF) when it is already in 2NF and has no transitive dependency.

Transitive dependency means a non-key column depends on another non-key column, not on the key directly.

Look at our 2NF table Course_Info(Course, Instructor, Office):

• Course → Instructor (fine, Course is the key)

• Instructor → Office (problem: Office depends on Instructor, which is not the key)

So the chain becomes:

Course → Instructor → Office

This chain is the transitive dependency. If Dr. Sharma changes office, we must update every row where Dr. Sharma appears. To fix it, split Course_Info into two tables.

Table 3 (fixed): Course

Table 4 (new): Instructor_Office

Now the design is in 3NF. The final structure has four clean tables:

• Student(Student_ID, Student_Name)

• Enrollment(Student_ID, Course, Grade)

• Course(Course, Instructor)

• Instructor_Office(Instructor, Office)

3NF check: If a non-key column depends on another non-key column, move it out.

SQL: Create the Normalized Design

Here is how the final 3NF design looks in real SQL. Use the tabs to see table creation and a join query that brings the data back together.

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50)
);

CREATE TABLE course (
  course_id VARCHAR(10) PRIMARY KEY,
  instructor VARCHAR(50)
);

CREATE TABLE instructor_office (
  instructor VARCHAR(50) PRIMARY KEY,
  office VARCHAR(20)
);

CREATE TABLE enrollment (
  student_id INT,
  course_id VARCHAR(10),
  grade CHAR(1),
  PRIMARY KEY (student_id, course_id)
);

SELECT s.student_name, e.course_id, e.grade
FROM enrollment e
JOIN student s
  ON s.student_id = e.student_id
WHERE e.course_id = 'DBMS';

Notice the idea: normalization splits the data, and joins bring it back when needed. This is the balance every real database uses.

BCNF: The Stricter Version of 3NF

Boyce-Codd Normal Form (BCNF) says: for every dependency X → Y, X must be a super key.

Most 3NF tables are also in BCNF. But some special tables are in 3NF and still not in BCNF. This is a favorite interview question.

Look at this table:

Rules of this table:

• (Student, Course) → Instructor : a student takes a course from one instructor

• Instructor → Course : each instructor teaches only one course

Here the candidate keys are (Student, Course) and (Student, Instructor). Since every column is part of some key, there is no partial or transitive dependency on non-key columns. So the table is in 3NF.

But Instructor → Course violates BCNF, because Instructor is not a super key.

Fix: split into two tables:

• (Student, Instructor)

• (Instructor, Course)

1NF vs 2NF vs 3NF vs BCNF: Quick Comparison

APNOAI Method: Solve Any Normalization Question

Use this fixed order in exams. It removes panic and keeps your answer structured.

flowchart TD
    A[Read Table and Rules] --> B[List Functional Dependencies]
    B --> C[Find Candidate Keys]
    C --> D{1NF? Single-value cells}
    D -->|No| E[Split multi-value cells]
    D -->|Yes| F{2NF? No partial dependency}
    F -->|No| G[Move partial columns out]
    F -->|Yes| H{3NF? No transitive dependency}
    H -->|No| I[Move transitive columns out]
    H -->|Yes| J[Check BCNF if asked]
    J --> K[Write Final Tables]

Common Exam Mistakes

Normalization vs Denormalization

Normalization is not always the final answer in real systems. Sometimes teams intentionally keep controlled redundancy for speed. This is called denormalization.

• Normalization: best for safe writes, clean design, and transactional systems

• Denormalization: used in read-heavy reporting, dashboards, and caching layers

Interview Questions Students Actually Get

1. What is normalization in one line?

It is the process of organizing tables to reduce redundancy and avoid update, insertion, and deletion anomalies.

2. What is the difference between 2NF and 3NF?

2NF removes partial dependency (part of the key determining a column). 3NF removes transitive dependency (a non-key column determining another non-key column).

3. Can a table be in 3NF but not in BCNF?

Yes. It happens when a non-super-key determines a prime attribute. The (Student, Course, Instructor) example above shows exactly this case.

4. Why do we need joins after normalization?

Because normalization splits one big table into smaller tables. Joins combine them back when a query needs related data together.

5. Is higher normalization always better?

Not always. Very high normalization can increase joins and slow read queries. Real systems balance normalization with performance needs.

6. What should a complete normalization answer include?

Functional dependencies, candidate keys, the highest normal form satisfied, and the final decomposed tables.

Conclusion

Normalization becomes easy when you stop memorizing and start checking. One messy table, one fixed order, and clear dependency checks turn any exam question into a step-by-step solution.

Remember the three questions that solve almost everything: Is every cell single-valued? Does any column depend on part of the key? Does any column depend on a non-key column?

Practice this method on different tables, and normalization will stop being a theory topic and become a scoring skill.