Data Management - 1
NORMALIZATION
Sammani Maddumahewage
Lecturer / Consultant
BSc (Hons) in Information Technology
Normalization
The process of organizing data to minimize data redundancy.
Data Redundancy
Why is Normalization Important?
Data Redundancy – Same data stored in multiple places.
Update Anomalies – Updating in one place but forgetting another causes inconsistency.
Insert Anomalies – Cannot insert data unless other unrelated data is provided.
Delete Anomalies – Deleting useful data unintentionally while removing irrelevant data.
Anomaly?
An unexpected or inconsistent problem that happens when data is not properly organized
Insert Anomaly
Ex: Cannot add a new record, because Teacher_Name and Mobile No. are not filled.
null null
Insert Anomaly
The inability to add data to the database due to absence of other data.
Update Anomaly
Ex: [Link] resigned from the
school and want to update the
table
Missed
A data inconsistency that results from
data redundancy & a partial update
Delete Anomaly
Ex: If Nimal's record is removed from the school database, the associated teacher details may
also be unintentionally deleted from the table.
Delete Anomaly
Occurs when you delete a record that may contain attributes that shouldn’t be deleted.
First Normal Form (1NF)
Every column contains atomic (single) values.
Each record is unique.
After 1NF
Second Normal Form (2NF)
Must be in 1NF.
No partial dependency (all non-key attributes depend on the full PK).
Partial Dependency ?
◦ Only in tables where the Primary Key is made of two or more columns (called a composite key).
StudentID CourseID StudentName Marks
1 C01 Alice 80
2 C02 Bob 90
Primary Key: (StudentID, CourseID) — together they uniquely identify a row.
Problem: StudentName depends only on StudentID, not on CourseID.
How to Fix this?
Student(StudentID, StudentName)
Enrollment(StudentID, CourseID, Marks)
• Now there is no partial dependency—each table depends on its full key.
Student
StudentID StudentName
1 Alice
2 Bob
Enrollment
StudentID CourseID Marks
1 C01 80
2 C02 90
Third Normal Form (3NF)
Must be in 2NF.
No transitive dependency (non-key attribute depending on another non-key).
Transitive Dependency?
◦ A non-prime attribute (non-primary key column) depends on another non-prime attribute rather than
directly depending on the primary key.
Enrollment
StudentID CourseID Marks CourseName
1 C01 80 Database
2 C02 90 Web
Enrollment Course
StudentID CourseID Marks CourseName CourseID CourseName
1 C01 80 Database C01 Database
2 C02 90 Web C02 Web
Finally…
Student Course
StudentID StudentName CourseID CourseName
1 Alice C01 Database
2 Bob C02 Web
Enrollment
StudentID CourseID Marks
1 C01 80
2 C02 90
Boyce-Codd Normal Form
(BCNF)
Must be in 3NF.
For every functional dependency X → Y, X must be a candidate key.
StudentID CourseID Teacher_Name 1. Teacher_Name StudentID, CourseID
1 C01 Mr. Perera 2. CourseID Teacher_Name
2 C02 Mr. Kamal
Prime-attribute Non Prime-attribute
Prime Attribute depends on Non-Prime Attribute, it violates the BCNF
StudentID CourseID Teacher_Name Course Teacher
1 C01 Mr. Perera CourseID 1 M Teacher_Name
2 C02 Mr. Kamal
Teacher
TeacherID Teacher_Name CourseID
1 Alice C01 BCNF
2 Bob C02
StudentID StudentID TeacherID
1 1 1
2 2 2
Thank You !