What is Normalization?
Normalization is the process of organizing data in a database to reduce redundancy (repeated data) and improve data integrity (accuracy and consistency).
It divides a large, complex table into smaller, related tables and links them using foreign keys.
Why Normalization is Needed
To avoid data duplication
To ensure data consistency
To make data updates easier
To save storage space
To improve query performance
Types of Normal Forms
First Normal Form (1NF)
Each column contains atomic (single) values.
No repeating groups or arrays.
Each record should be unique (use a primary key).
Second Normal Form (2NF)
Table must be in 1NF.
No partial dependency: non-key columns must depend on the entire primary key, not part of it.
Third Normal Form (3NF)
Table must be in 2NF.
No transitive dependency: non-key attributes shouldn’t depend on other non-key attributes.
BCNF (Boyce-Codd Normal Form)
A stronger version of 3NF; every determinant must be a candidate key.
4NF (Fourth Normal Form)
Removes multi-valued dependencies.
5NF (Fifth Normal Form)
Deals with join dependencies — ensures data reconstruction without redundancy.
We shall use the following data as the starting point:
1 . Identify anomalies (insertion, update, deletion) in this table.
🔸 Insertion Anomaly
We can’t add a new course until at least one student registers for it, because all course data is mixed with student data.
e.g., Can’t add a new course C104 – ML – Dr. Sharma without a student.
🔸 Update Anomaly
If an instructor’s phone number changes, it must be updated in multiple rows.
e.g., Dr. Kumar’s phone number appears twice.
🔸 Deletion Anomaly
If all students drop DBMS, deleting those rows also deletes Dr. Kumar and the course DBMS information.
2 . Convert the table to 1NF and write the SQL CREATE TABLE statement for it.
✅ The table already satisfies 1NF because:
All values are atomic.
Each row is unique (based on StudentID + CourseID).
We’ll explicitly define a composite primary key (StudentID, CourseID).
3 . Convert the table to 2NF and write SQL CREATE TABLE statements for the resulting tables, including primary keys.
In the current table:
StudentName depends only on StudentID
CourseName, Instructor, and InstructorPhone depend only on CourseID
So we separate Student and Course information.
4 . Convert the table to 3NF and write SQL CREATE TABLE statements, including foreign keys.
Here, in Courses, InstructorPhone depends on Instructor, not on CourseID.
So we separate instructor details into a new table.
5 . Insert the sample data into the normalized tables using INSERT INTO statements.
6 . Write a query to list all students along with their courses and instructor names using JOINs.
Top comments (0)