🧠 DBMS Normalization - Complete Guide

✅ What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them.

It helps to:

• Eliminate duplicate data
• Ensure data dependencies make sense
• Make the database more efficient and easier to maintain

🧐 Why Normalization?

To avoid:

• Update Anomaly: If we update a value in one place and forget to update in other places.
  Example: A customer's phone number is stored in 3 different places. If the number changes and only 2 are updated, the data becomes inconsistent.
• Insert Anomaly: We can't insert data unless some other data exists.
  Example: We can't add a new course unless we already have a student enrolled in it, because both course and student are stored in the same table.
• Delete Anomaly: Deleting data removes useful info unintentionally.
  Example: If we delete the last student enrolled in a course, the course data also gets deleted, even though the course still exists.

🔶 Types of Normal Forms

🥇 1NF – First Normal Form

Rule:

• Each cell should have a single value (no multiple values).
• Each row must be unique.

+------------+-----------------------+
| Student_ID | Subjects              |
+------------+-----------------------+
| 101        | Math, English, Hindi  |
+------------+-----------------------+
    

+------------+----------+
| Student_ID | Subject  |
+------------+----------+
| 101        | Math     |
| 101        | English  |
| 101        | Hindi    |
+------------+----------+
    

🥈 2NF – Second Normal Form

✅ Rule to be in 2NF:

• The table must already be in 1NF
• There should be no partial dependency

🤔 What is Partial Dependency?

If a table has a composite primary key (made from two or more columns), and a non-key column depends only on part of that key—not the whole— then it's a partial dependency.

🎓 Example Table: Student_Course

Primary Key: (Roll_No, Course_ID)
Partial Dependencies: Student_Name depends only on Roll_No, and Course_Name depends only on Course_ID.
❌ This violates 2NF.

🛠 Converting to 2NF:

We split the table into three:

📘 Table 1: Student

📗 Table 2: Course

📙 Table 3: Student_Course

✅ Now the design is in 2NF. All non-key columns fully depend on the full primary key.

🥉 3NF – Third Normal Form (Simple Explanation)

✅ Rules of 3NF:

• The table must already be in 2NF
• There should be no transitive dependency

🤔 What is Transitive Dependency?

Every column in the table should depend directly on the primary key.
If a column depends on another column (not on the primary key), that’s called a transitive dependency.

🎓 Example (Bad Table – Not in 3NF)

Problem: Dept_Name depends on Dept_ID, and Dept_ID depends on Roll.

So Dept_Name is not directly dependent on the primary key Roll.
This is called a transitive dependency ❌

If a table is not in 3NF:

• It may store the same data many times (data repetition).
• If department names change, we need to update many rows — this can lead to errors or inconsistent data.
• It increases data redundancy and wastes storage.

✅ Good Design – After Applying 3NF

📘 Student Table

📗 Department Table

✅ Now every column in the Student table depends only on the primary key Roll.
🎉 This design follows Third Normal Form (3NF)!

🏆 BCNF – Boyce-Codd Normal Form

✅ Rules of BCNF:

• The table must be in 3NF
• For every dependency A → B, A should be a super key

🤔 What is a Super Key?

A super key is a column (or set of columns) that can uniquely identify each row in a table.

📌 Bad Table (Not in BCNF)

Issue: Teacher → Dept, but Teacher is not a super key.
This breaks the rule of BCNF.

❌ What happens if not in BCNF?

• Same department info is repeated for the same teacher (data redundancy)
• If the department changes, you must update it in many places (update problem)
• You may end up with mismatched or wrong data (inconsistency)

✅ Fix – Break into 2 Tables

📘 Teacher Table

📗 Subject Table

✅ Now every dependency follows the rule: the left side is a super key. This design is in BCNF.

⭐ 4NF – Fourth Normal Form (Optional)

Rule:

• Must be in BCNF
• No multi-valued dependencies

💎 5NF – Fifth Normal Form (Optional)

• Deals with join dependency
• Rare in practical use

📋 Normalization Forms – Quick Comparison

🔁 Denormalization

Sometimes, we combine tables back together to:

• Improve performance (less joins)
• Make querying faster

📌 Quick Interview Questions Recap

1. What is Normalization? Why is it needed?
2. Explain 1NF, 2NF, 3NF with example tables.
3. What is the difference between 3NF and BCNF?
4. What are the anomalies in DBMS?
5. When do you denormalize a database?

This HTML note is ready for easy revision and can be saved for offline use!