Database Design Principles
Normalization
What is Normalization?
Normalization is the process of organizing data in a database to:
Reduce data redundancy (avoid repeating the same data in multiple places)
Ensure data integrity (maintain accuracy and consistency)
It is done by dividing a large table into smaller, related tables and defining relationships among
them (using keys like primary key and foreign key).
The Main Purpose of Normalization
Goal Explanation
Eliminate Avoid storing the same data more than once. Example: Student details
redundancy should not be repeated in every course enrollment record.
Maintain data When a piece of information changes, it needs to be updated in only
consistency one place.
Make database Proper structure makes querying faster and updating safer.
efficient
Improve data Relationships between tables ensure valid and meaningful data.
integrity
Simplify Easier to add, delete, or modify records without affecting other data.
maintenance
Normal Forms (Levels of Normalization)
There are several “levels” of normalization, called Normal Forms (NF):
Normal Rule Summary Main Focus
Form
1NF Each cell holds only a single value. No repeating Atomic data
groups.
2NF Be in 1NF + every non-key column fully depends on Remove partial
the primary key. dependency
3NF Be in 2NF + no transitive dependency (non-key Remove derived
depends on another non-key). dependencies
BCNF Every determinant is a candidate key. Stronger version of 3NF
Please Note: (Higher forms like 4NF, 5NF exist but used rarely)
1
Example: Unnormalized form → 1NF → 2NF → 3NF, using a simple and realistic Student–Course
Enrollment example.
Student-Course Table (RAW Table - Unnormalized Form)
StudentID StudentName Courses Instructor
101 Arjun SQL, Python Kumar, Ravi
102 Meena Excel Divya
103 Kavi SQL, Tableau Kumar, Priya
Major observations in the above table:
One cell has multiple values (like "SQL, Python").
It’s not organized — difficult to query or update.
Step 1: Convert to First Normal Form (1NF)
Rule of 1NF:
Each column should have atomic (single) values — no repeating groups.
So we split multiple course entries into separate rows
StudentI StudentName Course Instructor
D
101 Arjun SQL Kumar
101 Arjun Python Ravi
102 Meena Excel Divya
103 Kavi SQL Kumar
103 Kavi Tableau Priya
Now each field is atomic.
But still: StudentName and Instructor are repeated → redundancy remains.
Step 2: Convert to Second Normal Form (2NF)
Rule of 2NF:
Must be in 1NF
No partial dependency — every non-key column should depend on the entire primary
key, not just part of it.
Here, the composite key could be:
(StudentID, Course) → uniquely identifies each record.
2
But StudentName depends only on StudentID, not on the course.
So we split tables
Table 1: Student
StudentI StudentName
D
101 Arjun
102 Meena
103 Kavi
Table 2: Enrollment
StudentI Course Instructor
D
101 SQL Kumar
101 Python Ravi
102 Excel Divya
103 SQL Kumar
103 Tableau Priya
Here removed partial dependency (StudentName now isolated).
But still Instructor is repeating and depends on Course, not StudentID.
Step 3: Convert to Third Normal Form (3NF)
Rule of 3NF:
Must be in 2NF
No transitive dependency — non-key column shouldn’t depend on another non-key
column.
Here, Instructor depends on Course, not directly on (StudentID, Course).
So we split the instructor data into a separate table
Table 1: Student
StudentI StudentName
D
101 Arjun
102 Meena
103 Kavi
3
Table 2: Course
Course Instructor
SQL Kumar
Python Ravi
Excel Divya
Tableau Priya
Table 3: Enrollment
StudentI Course
D
101 SQL
101 Python
102 Excel
103 SQL
103 Tableau
After the normalization, we have the following features with the table
Data redundancy removed
Relationships are clean and logical
Database is easy to update and maintain