1NF (First Normal Form) - Remove Repeating Groups
✅ Ensure atomicity (each column contains atomic values).
❌ No repeating or multi-valued attributes.
Example - Unnormalized Table (INF)
Student_ID Name Courses Phone Numbers
101 Alice Math, Science 9876543210, 8765432109
102 Bob Science 9988776655
Issue:
Courses and Phone Numbers contain multiple values, violating
atomicity.
Convert to 1NF
Student_ID Name Course Phone Number
101 Alice Math 9876543210
101 Alice Science 8765432109
102 Bob Science 9988776655
2NF (Second Normal Form) - Remove Partial Dependencies
✅ No partial dependency (Non-prime attributes should depend on the whole
primary key).
❌ Still might have transitive dependencies.
Example - 1NF Table
Student_ID Name Course Instructor Phone Number
101 Alice Math Prof. X 9876543210
101 Alice Science Prof. Y 8765432109
102 Bob Science Prof. Y 9988776655
Issue:
Name and Phone Number depend only on Student_ID (not the full
primary key (Student_ID, Course)).
Instructor depends on Course, not on (Student_ID, Course).
Convert to 2NF
✅ Split into separate tables.
Table 1: Student Table
Student_ID Name Phone Number
101 Alice 9876543210
102 Bob 9988776655
Table 2: Course Table
Course Instructor
Math Prof. X
Science Prof. Y
Table 3: Student-Course Table
Student_ID Course
101 Math
101 Science
102 Science
Understanding 3rd Normal Form (3NF)
Objective: To eliminate transitive dependencies, ensuring that non-key
attributes depend only on the primary key.
Step 1: Understanding the Issue in 2NF
We have the following Student Table in 2NF:
Student_ID Name Phone Number City Zip Code
101 Alice 9876543210 NYC 10001
102 Bob 9988776655 LA 90001
What is the Problem?
Primary Key: Student_ID
Direct Dependencies:
o Name and Phone Number depend only on Student_ID (✅
Correct).
o Zip Code also depends only on Student_ID (✅ Correct).
o But City depends on Zip Code, not directly on Student_ID (❌
Issue).
This creates a transitive dependency, meaning:
Student_ID → Zip Code ✅ (Correct)
Zip Code → City ✅ (Correct)
But Student_ID → City is indirect! ❌ (Problem)
This violates 3NF, where all non-key attributes must depend only on the
primary key.
Step 2: Breaking the Transitive Dependency
To fix this issue, we separate the city information into a new table based on
Zip Code.
New Structure (3NF Compliant)
✅ Table 1: Student Table (Now only contains direct dependencies on
Student_ID)
Student_ID Name Phone Number Zip Code
101 Alice 9876543210 10001
102 Bob 9988776655 90001
✅ Table 2: Zip Code Table (Separate table for City based on Zip Code)
Zip Code City
10001 NYC
90001 LA
Step 3: Why is This 3NF Compliant?
1. All non-key attributes depend only on the primary key in each table:
o In the Student Table, Name, Phone Number, and Zip Code depend
only on Student_ID.
o In the Zip Code Table, City depends only on Zip Code.
2. No transitive dependencies exist:
o Before: Student_ID → Zip Code → City (❌)
o After: Student_ID → Zip Code (✅) and Zip Code → City (✅)
o Since City is removed from the Student Table, no indirect
dependency remains.
Step 4: How This Improves the Database?
1. Avoids Redundancy
o If City remained in the Student Table, it would repeat multiple
times for every student from the same zip code.
o Now, we store City once per Zip Code.
2. Data Integrity
o If City names change (e.g., NYC becomes New York City), we
update it only once in the Zip Code Table, ensuring consistency.
3. Improved Query Performance
o Searching for students from a particular city becomes efficient
using JOIN operations.
Step 5: How Would We Retrieve Data Now?
If we want to get the full student details including the City, we JOIN both
tables:
SELECT s.Student_ID, s.Name, s.Phone_Number, z.City, s.Zip_Code
FROM Student s
JOIN Zip_Code z ON s.Zip_Code = z.Zip_Code;
Result:
Student_ID Name Phone Number City Zip Code
101 Alice 9876543210 NYC 10001
Student_ID Name Phone Number City Zip Code
102 Bob 9988776655 LA 90001
Conclusion
Before (2NF): We had transitive dependency (Student_ID → Zip Code
→ City), violating 3NF.
After (3NF): We normalized the table by separating the Zip Code and
City into another table, ensuring all attributes depend only on their
respective primary keys.
Boyce-Codd Normal Form (BCNF) - Removing Remaining Functional
Dependencies
Issue in 3NF (Functional Dependencies Remain)
Instructor Course Department
Prof. X Math Science
Prof. Y Science Science
Prof. Z History Arts
What is the Problem?
Functional Dependencies in the Table:
1. Course → Department (Each Course belongs to one Department)
2. Instructor → Course (Each Instructor teaches one Course)
Here, Course is not a candidate key, but it determines Department,
violating BCNF.
Solution: Convert to BCNF by Decomposing the Table
We create three separate tables.
✅ Table 1: Instructor Table (Removes redundant dependency)
Instructor Department
Prof. X Science
Prof. Y Science
Prof. Z Arts
✅ Table 2: Course Table (Stores the relationship between Courses and
Departments)
Course Department
Math Science
Science Science
History Arts
✅ Table 3: Instructor-Course Table (Shows which Instructor teaches which
Course)
Instructor Course
Prof. X Math
Prof. Y Science
Prof. Z History
Why is this BCNF?
Every determinant is now a candidate key.
No partial dependencies or functional dependencies exist that do not
involve a super key.
2️⃣ Fourth Normal Form (4NF) - Removing Multi-Valued Dependencies
(MVDs)
Issue in BCNF Table: Multi-Valued Dependencies Exist
Student_ID Course Hobby
101 Math Painting
Student_ID Course Hobby
101 Science Painting
101 Math Music
101 Science Music
What is the Problem?
Two Independent Relationships exist:
o A student can enroll in multiple courses.
o A student can have multiple hobbies.
Courses and hobbies are unrelated to each other, leading to multi-
valued dependencies (MVDs).
Solution: Convert to 4NF by Separating MVDs
✅ Table 1: Student-Course Table (Only stores Student-Course relation)
Student_ID Course
101 Math
101 Science
✅ Table 2: Student-Hobby Table (Only stores Student-Hobby relation)
Student_ID Hobby
101 Painting
101 Music
Why is this 4NF?
We eliminated multi-valued dependencies.
Now, each table represents only one independent relationship.
3️⃣ Fifth Normal Form (5NF) - Removing Join Dependencies
Issue in 4NF Table: Join Dependencies Exist
Project_ID Employee_ID Skill
P1 E101 Java
P1 E102 Python
P2 E101 Java
P2 E103 C++
What is the Problem?
The table is in 4NF, but we have a join dependency:
o A Project has Employees.
o An Employee has Skills.
o But Project and Skills are not directly related.
Solution: Convert to 5NF by Removing Join Dependency
We decompose the table into two separate tables.
✅ Table 1: Project-Employee Table (Only stores Project-Employee relation)
Project_ID Employee_ID
P1 E101
P1 E102
P2 E101
P2 E103
✅ Table 2: Employee-Skill Table (Only stores Employee-Skill relation)
Employee_ID Skill
E101 Java
E102 Python
E103 C++
Why is this 5NF?
No loss of data after decomposition.
No join dependencies remain.
Each table represents only one relationship.
