Lecture on Normalization in DBMS

Introduction

• Normalization is a crucial concept in Database Management Systems (DBMS).
• It aims to reduce redundancy in a table.
• Redundancy often occurs in two forms:
  • Row-level duplicacy
  • Column-level duplicacy

Row-Level Duplicacy

• Example: Student Table with columns SID, S. Name, and Age.
  • Values: 1, 2, 1 (first and third row same)
• Solution: Use a primary key to ensure uniqueness.
  • Primary key properties:
    • Unique
    • Not null
• SID set as a primary key prevents row-level duplicacy.

Column-Level Duplicacy

• Example table includes: Student ID, Student Name, Course ID, Course Name, Faculty ID, Faculty Name, Salary.
• No two rows are exactly the same due to primary key usage.
• Column-level duplicacy can lead to anomalies:
  1. Insertion Anomaly
  2. Deletion Anomaly
  3. Updation Anomaly

Anomalies Explained

Insertion Anomaly

• Problem occurs when trying to insert new data without a complete set of information.
• Example: Adding new course information without student data leaves SID empty, violating primary key constraint.

Deletion Anomaly

• Occurs when deleting data inadvertently leads to loss of additional vital information.
• Example: Deleting a student's data also removes course and faculty information linked to that student.

Updation Anomaly

• Occurs when updating duplicated data requires multiple changes:
• Example: Changing salary for Faculty F1 from 30,000 to 40,000 requires updating every instance where F1 appears, even though F1 is one faculty.

Solution: Normalization Rules

• Normalization involves dividing a table into multiple related tables.
• Example Solution:
  • Create separate tables for each entity:
    • SID, S. Name as one table.
    • CID, C. Name as another table.
    • FID, F. Name, Salary as a third table.
• Each new table uses a primary key to ensure integrity and reduce redundancy.

Conclusion

• Normalization helps manage anomalies and redundancy in DBMS.
• Further discussion on specific normalization rules will be addressed later.

Thank you.