What is Normalization in Database?
Learn what database normalization is, its types (1NF, 2NF, 3NF, BCNF), benefits, examples, SQL queries and commonly asked interview questions with answers.
Expected Interview Answer
Normalization is the process of organizing data in a database to reduce redundancy, avoid anomalies, and improve data integrity.
The goal is to split data into multiple related tables using relationships. Instead of one wide table repeating the same information across many rows, normalization decomposes it into focused tables linked by primary and foreign keys, applied progressively through normal forms (1NF, 2NF, 3NF, BCNF).
- Reduces duplicate data
- Improves consistency
- Easier maintenance
- Better storage efficiency
- Prevents insertion, update and deletion anomalies
AI Mentor Explanation
Imagine a scorebook where every ball bowled repeats the batter’s full name, team, and jersey number on each line. One spelling mistake and the same player looks like two different people. Instead, real scorecards keep a separate player list and refer to each player by a short ID on every delivery. Normalization does exactly this: player details live in one table, ball-by-ball events in another, linked by the ID — so a player’s name is stored once and every record stays consistent.
How Normalization Works (Example)
Student
- ID
- Name
- DeptID
Department
- DeptID
- DeptName
Teacher
- TeacherID
- DeptID
- Name
Step-by-Step Explanation
Step 1
Understand duplicate data
Identify columns repeated across many rows of a single wide table.
Step 2
Split tables
Move each independent entity (student, department, teacher) into its own table.
Step 3
Create relationships
Decide how the tables relate: one department has many students and teachers.
Step 4
Primary keys
Give every table a primary key that uniquely identifies each row.
Step 5
Foreign keys
Link tables with foreign keys so references stay valid and enforced.
What Interviewer Expects
- Understanding of data redundancy
- Relational database concepts
- Primary keys and foreign keys
- Normalization forms (1NF, 2NF, 3NF, BCNF)
- A real-world example explained clearly
Common Mistakes
- Saying normalization increases redundancy
- Confusing normalization with indexing
- Forgetting foreign keys when describing table splits
- Unable to explain with a real example
Best Answer (HR Friendly)
“Normalization is the process of organizing database tables to eliminate redundancy and improve consistency. It divides data into multiple related tables using primary and foreign keys, which keeps information accurate and easier to maintain.”
Code Example
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);Follow-up Questions
- Explain 1NF, 2NF, 3NF and BCNF with examples.
- What are anomalies in DBMS?
- What is the difference between normalization and denormalization?
- How do primary keys differ from foreign keys?
- Can too much normalization reduce performance?
MCQ Practice
1. Normalization is mainly used for?
Normalization organizes data into related tables specifically to reduce redundancy and prevent anomalies.
2. Which key links two normalized tables together?
A foreign key in one table references the primary key of another, creating the relationship.
3. Which normal form removes partial dependency on a composite key?
2NF requires that every non-key attribute depend on the whole primary key, removing partial dependencies.
Flash Cards
What is 1NF? — Every column holds atomic values and each record is unique — no repeating groups.
What is 2NF? — 1NF plus no partial dependency: every non-key attribute depends on the whole primary key.
What is 3NF? — 2NF plus no transitive dependency: non-key attributes depend only on the key.
What is BCNF? — A stricter 3NF: every determinant must be a candidate key.