NORMALIZATION

Normalization is a process used in Database Management Systems (DBMS) to organize data in

a database efficiently by reducing redundancy (duplicate data) and avoiding data anomalies
(errors during insert, update, or delete operations).

✅ Definition of Normalization
Normalization is the process of structuring database tables according to certain rules (called
Normal Forms) so that:
 Data is stored only once
 Dependency is maintained properly
 Database becomes easy to maintain and update

🎯 Objectives of Normalization
 Remove duplicate data
 Maintain data consistency(ACCURACY, UNIFORMITY)
 Reduce storage space
 Avoid insertion, deletion, and update anomalies
 Improve database efficiency

📌 Example (Before Normalization)

Consider a Student Course Table:
Student_ID Student_Name Course Faculty Faculty_Phone
101 Ravi C Kumar 9876543210
101 Ravi Java Kumar 9876543210
102 Sita C Ramesh 9123456780
Problems:
 Student details repeated
 Faculty phone repeated
 Updating phone requires multiple changes
This is Unnormalized Data.

🔹 Normal Forms

1️⃣ First Normal Form (1️NF)

Rule:
 Each column must contain atomic (single) values
 No repeating groups or multiple values in one column
Example (1️NF Table)
Student_ID Student_Name Course Faculty Faculty_Phone
101 Ravi C Kumar 9876543210
Student_ID Student_Name Course Faculty Faculty_Phone
101 Ravi Java Kumar 9876543210
102 Sita C Ramesh 9123456780
✔ Now each cell has a single value.

2️⃣ Second Normal Form (2️NF)

Rule:
 Must be in 1️NF
 Remove partial dependency
 Non-key attributes must depend on the whole primary key
Problem:
Student_Name depends only on Student_ID, not Course.
Solution → Split Tables
Student Table
Student_ID Student_Name
101 Ravi
102 Sita
Course Table
Student_ID Course Faculty Faculty_Phone
101 C Kumar 9876543210
101 Java Kumar 9876543210
102 C Ramesh 9123456780
✔ Partial dependency removed.

3️⃣ Third Normal Form (3️NF)

Rule:
 Must be in 2️NF
 Remove transitive dependency
 Non-key attributes should not depend on other non-key attributes
Problem:
Faculty_Phone depends on Faculty, not Student_ID.
Solution → Create Separate Faculty Table
Student Table
Student_ID Student_Name
101 Ravi
102 Sita
Enrollment Table
Student_ID Course Faculty
101 C Kumar
Student_ID Course Faculty
101 Java Kumar
102 C Ramesh
Faculty Table
Faculty Faculty_Phone
Kumar 9876543210
Ramesh 9123456780
✔ No redundancy
✔ Proper dependencies maintained

🎓 Advantages of Normalization
 Eliminates data duplication
 Improves data integrity
 Easier database maintenance
 Efficient storage
 Prevents anomalies

⚠️ Disadvantages
 More tables created
 Complex queries (joins required)

⭐ Simple Real-Life Example

Think of a college:
 Student details stored once
 Course details stored separately
 Faculty details stored separately
Instead of writing all information repeatedly.

If you want, I can also give:

✅ Diagram-based university answer
✅ Short 5-mark answer
✅ Handwritten-style notes
✅ Normalization problems with solutions
Just tell me 👍.
BCNF AND 4TH NORMAL FORM WITH EXAMPLE
Here is a clear university-style explanation of BCNF and Fourth Normal Form (4NF) with
examples.

✅ Boyce–Codd Normal Form (BCNF)

📘 Definition
BCNF (Boyce–Codd Normal Form) is an advanced version of 3️rd Normal Form (3️NF).
A relation is in BCNF if:
👉 For every functional dependency, the determinant must be a candidate key.
In simple words:
The column that determines another column must be a key attribute.

📌 Condition for BCNF

A table is in BCNF if:
 It is already in 3️NF
 Every determinant is a candidate key

❌ Example (Not in BCNF)

Consider a Student–Subject–Teacher table:
Student Subject Teacher
Ravi DBMS Kumar
Sita DBMS Kumar
Ravi Java Ramesh
Functional Dependencies:
1. (Student, Subject) → Teacher
2. Subject → Teacher
Here:
 Subject determines Teacher
 But Subject is not a candidate key
👉 BCNF violation occurs.

✅ Convert into BCNF

Split the table:
Subject Teacher Table
Subject Teacher
DBMS Kumar
Java Ramesh
Student Subject Table
Student Subject
Ravi DBMS
Sita DBMS
Ravi Java
✔ Now determinants are candidate keys.
✔ BCNF satisfied.

🎯 Advantages of BCNF
  Removes redundancy caused by overlapping candidate keys
 Eliminates update anomalies
 Improves consistency

✅ Fourth Normal Form (4NF)

📘 Definition
A table is in Fourth Normal Form (4NF) if:
👉 It is in BCNF and has no multivalued dependency.

📌 Multivalued Dependency (MVD)

Occurs when:
 One attribute determines multiple independent values.
Notation:
A →→ B
means A determines multiple B values independently.

❌ Example (Not in 4NF)

Suppose:
A student can have:
 Multiple Skills
 Multiple Hobbies
These are independent.
Student Table
Student Skill Hobby
Ravi Java Cricket
Ravi Java Music
Ravi Python Cricket
Ravi Python Music
Problem:
 Skills and hobbies are unrelated.
 Data unnecessarily repeated.
This creates multivalued dependency:
Student →→ Skill
Student →→ Hobby

✅ Convert into 4NF

Split into two tables:
Student Skills
Student Skill
Ravi Java
Ravi Python
Student Hobbies
Student Hobby
Ravi Cricket
Ravi Music
✔ Multivalued dependency removed
✔ 4NF achieved

🔍 Difference Between BCNF and 4NF

Feature BCNF 4NF
Based on Functional Dependency Multivalued Dependency
Requirement Determinant must be candidate key No multivalued dependency
Higher than 3NF BCNF
Removes Dependency anomalies Independent multi-values redundancy

⭐ Simple Memory Trick

 BCNF → Key problem
 4NF → Multiple independent values problem

If you want, I can also give:

✅ Diagram explanation for exams
✅ Comparison of 1NF–2NF–3NF–BCNF–4NF in one table
✅ 10-mark university answer format
✅ Practice problems with solutions
Just tell me 👍.
Differences
Here are the clear differences between BCNF and Fourth Normal Form (4NF) in an easy exam-
ready table format.

✅ Difference Between BCNF and Fourth Normal Form (4NF)

Basis BCNF (Boyce–Codd Normal Form) Fourth Normal Form (4NF)
A relation is in BCNF if every A relation is in 4NF if it is in BCNF and
Definition
determinant is a candidate key. has no multivalued dependency.
Based On Functional Dependency (FD) Multivalued Dependency (MVD)
Remove redundancy caused by Remove redundancy caused by
Main Objective
improper functional dependencies. independent multi-valued attributes.
Basis BCNF (Boyce–Codd Normal Form) Fourth Normal Form (4NF)
Dependency Deals with functional dependency Deals with multivalued dependency
Type problems. problems.
Determinant must be a candidate No non-trivial multivalued
Requirement
key. dependency allowed.
Higher Normal
Higher than 3NF Higher than BCNF
Form
Multiple independent values
Problem Solved Key-based anomalies
repetition
Table Split tables based on functional Split tables based on multivalued
Decomposition dependency. dependency.
Subject determines Teacher but is Student has multiple skills and
Example Issue
not a key. hobbies independently.
Complexity Level Moderate More advanced normalization level

⭐ Simple Understanding
👉 BCNF → Checks whether keys determine data correctly.
👉 4NF → Checks whether multiple independent values are stored separately.

🧠 Easy Memory Trick (For Exams)

 BCNF = Candidate Key Rule
 4NF = No Multi-Values Rule

If you want, I can also provide:

✅ Short 5-mark answer
✅ One-page revision notes
✅ All Normal Forms comparison (1NF → 5NF)
✅ Diagram explanation for university exams.
All normal forms comparison
Here is a complete comparison of all Normal Forms (1️NF to 5NF) in an easy exam-ready table.

✅ Comparison of All Normal Forms (1️NF – 5NF)

Normal Type of Example Problem
Full Form Main Rule Removes
Form Dependency Solved
First Each column contains
Repeating Multiple values in
1️NF Normal atomic (single) values; None
attributes one column
Form no repeating groups
Normal Type of Example Problem
Full Form Main Rule Removes
Form Dependency Solved
Must be in 1NF and all
Second Attribute depends
non-key attributes Partial Functional
2️NF Normal on part of
depend on full dependency Dependency
Form composite key
primary key
Third
Must be in 2NF and no Transitive Functional Non-key depends
3️NF Normal
transitive dependency dependency Dependency on another non-key
Form
Boyce–
Every determinant Non-key
Codd Advanced FD Functional
BCNF must be a candidate determining
Normal anomalies Dependency
key another attribute
Form
Fourth Must be in BCNF and
Multi-value Multivalued Independent
4NF Normal no multivalued
redundancy Dependency multiple attributes
Form dependency
Fifth
Must be in 4NF and no Join Join Incorrect table
5NF Normal
join dependency redundancy Dependency decomposition
Form

📊 Simple Understanding of Each Normal Form

🔹 1️NF – Atomic Values
❌ Ravi, Sita in one column
✅ Separate rows for each value

🔹 2️NF – Full Key Dependency

Every attribute must depend on entire primary key.
Example:
 (StudentID, Course) → Marks
 StudentName depends only on StudentID ❌

🔹 3️NF – No Transitive Dependency

Non-key should not depend on another non-key.
Example:
 StudentID → DeptID
 DeptID → DeptName
❌ DeptName indirectly depends on StudentID

🔹 BCNF – Stronger 3️NF

Only candidate keys can determine other attributes.
🔹 4NF – No Multi-Valued Dependency
If a student has:
 Many skills
 Many hobbies
Store separately.

🔹 5NF – Proper Table Joining

Data should not create redundancy when tables are joined.

⭐ Easy Memory Order

👉A→F→T→K→M→J
Step Meaning
A Atomic values (1NF)
F Full dependency (2NF)
T Transitive removed (3NF)
K Key rule (BCNF)
M Multi-values removed (4NF)
J Join dependency removed (5NF)

🎓 One-Line Summary (Best for Exams)

 1️NF: No repeating groups
 2️NF: No partial dependency
 3️NF: No transitive dependency
 BCNF: Determinant must be candidate key
 4NF: No multivalued dependency
 5NF: No join dependency

 Visual Summary Table

NF Focus Removes
1NF Structure Repeating groups
2NF Key dependency Partial dependency
3NF Attribute dependency Transitive dependency
BCNF Candidate keys FD anomalies
4NF Independent data Multivalued dependency
Join dependency
5NF Table joins
UN-NORMALIZED FORM (UNF)
│┌──────────────────────┐
│ 1NF – Atomic Values │
│ • No repeating data │
│ • Single value/cell │
└──────────────────────┘
▼
┌────────────────────────────┐
│ 2NF – Full Dependency │
│ • In 1NF │
│ • No partial dependency │
│ • Depends on full key │
└────────────────────────────┘
▼
┌────────────────────────────┐
│ 3NF – No Transitive Dep. │
│ • In 2NF │
│ • Non-key → Non-key ❌ │
└────────────────────────────┘
▼
┌────────────────────────────┐
│ BCNF – Candidate Key Rule │
│ • Every determinant = Key │
└────────────────────────────┘
▼
┌────────────────────────────┐
│ 4NF – No Multi Values │
│ • Remove MVD │
│ • Independent data split │
└────────────────────────────┘
▼
┌────────────────────────────┐
│ 5NF – No Join Dependency │
│ • Proper table joining │
└────────────────────────────┘