Normalization
1NF, 2NF, 3NF, BCNF

Introduction

Database design is like planning a school timetable — it must be clean, consistent, and conflict-free. Normalization helps us organize data in a way that removes redundancy, improves integrity, and makes maintenance easier. In this tutorial, we’ll walk through the key normal forms — 1NF, 2NF, 3NF, and BCNF — using relatable school-based examples.

What is Normalization?

Normalization is the process of restructuring a relational database to reduce data redundancy and improve data integrity. Each “normal form” adds a layer of structure and removes anomalies in insertions, deletions, and updates.

1NF – First Normal Form

Rule:

• All values must be atomic (no repeating groups or arrays)

Unnormalized Table:

CREATE TABLE student_subjects (
  roll_no INT,
  name VARCHAR(50),
  subjects VARCHAR(100) -- e.g., "Maths, Science"
);

Problem:

The subjects column contains multiple values. This violates 1NF.

1NF Version:

CREATE TABLE student_subjects_1nf (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30)
);

Now each row holds only atomic values.

2NF – Second Normal Form

Rule:

• Be in 1NF
• No partial dependency (non-key attributes must depend on full primary key)

Example:

CREATE TABLE result (
  roll_no INT,
  subject VARCHAR(30),
  student_name VARCHAR(50),
  marks INT,
  PRIMARY KEY (roll_no, subject)
);

Problem:

student_name depends only on roll_no, not the whole key (roll_no, subject). That’s a partial dependency.

2NF Solution – Split the table:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  student_name VARCHAR(50)
);

CREATE TABLE marks (
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  PRIMARY KEY (roll_no, subject),
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

Now each non-key attribute depends on the full key — no partial dependency.

3NF – Third Normal Form

Rule:

• Be in 2NF
• No transitive dependency (non-key attributes shouldn't depend on other non-key attributes)

Example:

CREATE TABLE student_details (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(30),
  pincode INT
);

Problem:

pincode depends on city, not directly on the primary key. That’s a transitive dependency.

3NF Solution – Separate the city table:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(30)
);

CREATE TABLE cities (
  city VARCHAR(30) PRIMARY KEY,
  pincode INT
);

Now all non-key columns depend directly on the primary key only.

BCNF – Boyce-Codd Normal Form

Rule:

• Be in 3NF
• Every determinant must be a candidate key

Example:

Let’s say a teacher is assigned uniquely to each subject, but a teacher can teach only one subject.

CREATE TABLE subject_teacher (
  subject VARCHAR(30),
  teacher_name VARCHAR(50),
  room_no INT,
  PRIMARY KEY (subject, room_no)
);

Problem:

subject → teacher_name but subject is not a superkey here. This violates BCNF.

BCNF Solution:

CREATE TABLE subjects (
  subject VARCHAR(30) PRIMARY KEY,
  teacher_name VARCHAR(50)
);

CREATE TABLE subject_rooms (
  subject VARCHAR(30),
  room_no INT,
  PRIMARY KEY (subject, room_no),
  FOREIGN KEY (subject) REFERENCES subjects(subject)
);

Why Normalization Matters

• Prevents redundant data
• Improves consistency and accuracy
• Reduces storage cost
• Makes updates easier and safer

Real-World School Use Cases

• 1NF – Each student-subject row stored individually
• 2NF – Student details separated from subject marks
• 3NF – City info separated to avoid duplication of pin codes
• BCNF – Ensure teachers uniquely assigned to subjects

Summary

Normalization is the foundation of clean, scalable databases. From splitting repeating values in 1NF to eliminating complex dependencies in BCNF, each normal form brings you closer to efficient and error-free data design. Especially in large-scale systems like school databases, normalization ensures your structure remains solid as data grows.

What’s Next?

Up next: Denormalization – when and why you might intentionally break normalization rules for performance.