1.
Functional Dependency (FD)
A Functional Dependency describes a relationship where:
One attribute uniquely determines another attribute.
Notation:
A→B
means:
● if we know A, we can uniquely identify B
Example
StudentID StudentName
101 Ravi
102 Anu
Here:
StudentID → StudentName
Reason:
● each StudentID has only one student name
Real-Life Example
EmpID Department
EmpID → Department
One employee ID determines one department.
Why FD Important?
Used in:
● normalization
● identifying keys
● reducing redundancy
Types of Functional Dependency
Full Functional Dependency
Attribute depends on entire primary key.
Example:
(StudentID, CourseID) → Grade
Grade depends on both columns.
Partial Dependency
Depends only on part of composite key.
Example:
StudentID → StudentName
Only part of key determines value.
Causes 2NF violation.
Transitive Dependency
One non-key attribute determines another.
Example:
EmpID → DeptID
DeptID → DeptName
Indirect dependency.
Causes 3NF violation.
2. Multivalued Dependency (MVD)
A Multivalued Dependency occurs when:
One attribute determines multiple independent values.
Notation:
A →→ B
Example
Student Subject Hobby
Ravi Python Cricket
Ravi Python Music
Ravi MySQL Cricket
Ravi MySQL Music
Here:
● one student has many subjects
● one student has many hobbies
Subjects and hobbies are independent.
MVDs
Student →→ Subject
Student →→ Hobby
Problem with MVD
Causes:
● repeated data
● unnecessary combinations
● update anomalies
Solution
Split into tables:
StudentSubject
Student Subject
StudentHobby
Student Hobby
This achieves 4NF.
Difference Between FD and MVD
FD MVD
One value determines one One value determines multiple
value values
A→B A →→ B
Used in 2NF/3NF Used in 4NF
3. Informal Design Guidelines
These are rules followed while designing good databases.
Goal:
● reduce redundancy
● avoid anomalies
● improve consistency
Guideline 1: Avoid Redundancy
Do not store repeated data unnecessarily.
Bad Design
EmpID EmpName DeptName
1 Ravi HR
2 Anu HR
Department name repeats.
Better Design
Employee Table
| EmpID | EmpName | DeptID |
Department Table
| DeptID | DeptName |
Redundancy reduced.
Guideline 2: Avoid Null Values
Too many NULL values indicate poor design.
Bad Example
Student HostelRoom
Ravi NULL
If many students are day scholars:
● many NULLs occur
Better Design
Separate hostel details into another table.
Guideline 3: Avoid Update Anomalies
Anomalies cause inconsistent data.
Types of Anomalies
Insert Anomaly
Cannot insert data without unrelated data.
Example:
● cannot add department until employee exists
Update Anomaly
Need to update same data in many rows.
Example:
● changing HR to Human Resources everywhere
Delete Anomaly
Deleting one row removes useful information.
Example:
● deleting last employee deletes department info too
Why These Guidelines Important?
They help:
● maintain consistency
● improve storage efficiency
● simplify updates
● reduce errors
Normalization in MySQL
Normalization is the process of organizing data in a database to:
● reduce data redundancy
● avoid duplicate data
● improve data consistency
● simplify maintenance
It divides large tables into smaller related tables using relationships.
Why Normalization?
Without normalization:
● Same data gets repeated
● Updating data becomes difficult
● Data inconsistency occurs
Example problem:
StudentID StudentName Course Faculty
1 Ravi Python Anu
1 Ravi MySQL Kiran
Here, student details repeat multiple times.
Types of Normal Forms
1NF (First Normal Form)
Rules
● Each column should contain atomic (single) values
● No multiple values in one field
● Each row should be unique
Not in 1NF
ID Name Subjects
1 Ravi Python, MySQL
In 1NF
ID Name Subject
1 Ravi Python
1 Ravi MySQL
2NF (Second Normal Form)
Rules
● Table must already be in 1NF
● No partial dependency
● Non-key attributes must depend on the whole primary key
Example
Not in 2nf Table
StudentID CourseID StudentName CourseName
Primary Key = (StudentID, CourseID)
Problem:
● StudentName depends only on StudentID
● CourseName depends only on CourseID
In 2nf Split into:
Student Table
StudentID StudentName
Course Table
CourseID CourseName
Enrollment Table
StudentID CourseID
3NF (Third Normal Form)
Rules
● Table must be in 2NF
● No transitive dependency
● Non-key columns should depend only on primary key
Example
Not in 3NF Table
EmpID EmpName DeptID DeptName
Problem:
● DeptName depends on DeptID, not EmpID
In 3NF Split into:
Employee Table
EmpID EmpName DeptID
Department Table
DeptID DeptName
BCNF (Boyce-Codd Normal Form)
BCNF is an advanced version of 3NF used to remove anomalies that 3NF cannot handle.
Definition
A table is in BCNF if:
For every functional dependency, the determinant must be a candidate key.
Important Terms
Functional Dependency
If:
A→B
means:
● A determines B
● Using A, we can identify B uniquely
Example:
StudentID → StudentName
StudentID uniquely determines StudentName.
Candidate Key
A column (or combination of columns) that can uniquely identify each row.
Example:
| StudentID | Email |
Both can uniquely identify students.
So both are candidate keys.
Why BCNF Needed?
Sometimes a table satisfies 3NF but still has redundancy.
BCNF removes that issue.
Example of BCNF
Table
Teacher Subject Room
Anu Python R1
Ravi MySQL R2
Anu Java R1
Assumptions
1. One teacher always uses the same room
Teacher → Room
2. One subject is taught by one teacher
Subject → Teacher
Candidate Key
To uniquely identify rows:
(Teacher, Subject)
or
Subject
since subject determines teacher.
Problem
Dependency:
Teacher → Room
But Teacher is NOT a candidate key.
So table violates BCNF.
Decomposition into BCNF
TeacherRoom Table
Teacher Room
Anu R1
Ravi R2
SubjectTeacher Table
Subject Teacher
Python Anu
MySQL Ravi
Java Anu
Now:
● determinants are candidate keys
● redundancy removed
Difference Between 3NF and BCNF
3NF BCNF
Less strict More strict
Allows some redundancy Removes more redundancy
Determinant may not be candidate Determinant must be candidate
key key
Real-life Example
Not Normalized
Course Faculty Cabin
Python Anu C1
Java Anu C1
MySQL Ravi C2
Dependency:
Faculty → Cabin
Faculty is not candidate key.
So not BCNF.
After BCNF: Faculty Table
Faculty Cabin
Anu C1
Ravi C2
Course Table
Course Faculty
Python Anu
Java Anu
MySQL Ravi
4NF (Fourth Normal Form)
4NF is used to remove:
● multi-valued dependencies
● unnecessary repetition of independent data
A table is in 4NF if:
● it is already in BCNF
● it has no multi-valued dependency
What is Multi-Valued Dependency?
When one column depends on another column independently of other columns.
Notation:
A →→ B
means:
● for one value of A, there can be multiple independent values of B
Example
Suppose:
A student can learn:
● multiple subjects
● multiple hobbies
These are independent.
Table
Student Subject Hobby
Ravi Python Cricket
Ravi Python Music
Ravi MySQL Cricket
Ravi MySQL Music
Problem
Here:
● Ravi has many subjects
● Ravi has many hobbies
Subjects and hobbies are unrelated.
This creates unnecessary duplication.
Multi-Valued Dependencies
Student →→ Subject
Student →→ Hobby
Why Not Good?
If Ravi adds a new hobby:
● many rows must be inserted
If one row deleted:
● data inconsistency may happen
Convert to 4NF
Split table into two tables.
StudentSubject Table
Student Subject
Ravi Python
Ravi MySQL
StudentHobby Table
Student Hobby
Ravi Cricket
Ravi Music
Now:
● no duplication
● easier maintenance
● no multi-valued dependency issue
Simple Definition
4NF removes multi-valued dependencies by separating independent multi-valued
attributes into different tables.
Difference Between BCNF and 4NF
BCNF 4NF
Handles functional dependency Handles multi-valued dependency
Removes redundancy due to Removes redundancy due to multiple independent
keys values
Real-Life Example
Not in 4NF
Employee Skill Language
John Java English
John Java Hindi
John Python English
John Python Hindi
Employee has:
● multiple skills
● multiple languages
Both independent.
4NF Decomposition
EmployeeSkill
Employee Skill
John Java
John Python
EmployeeLanguage
Employee Language
John English
John Hindi
5NF (Fifth Normal Form)
5NF is also called:
Project-Join Normal Form (PJNF)
It removes:
● join dependency problems
● redundancy caused while reconstructing tables using joins
Definition
A table is in 5NF if:
● it is already in 4NF
● it cannot be decomposed further without losing information
● every join dependency is implied by candidate keys
Simple Meaning
If a large table can be broken into smaller tables and later perfectly joined back without:
● losing data
● generating extra data
then it satisfies 5NF.
Example
Suppose a company manages:
● Suppliers
● Parts
● Projects
Table
Supplier Part Project
S1 P1 J1
S1 P2 J1
S2 P1 J1
Meaning:
● Supplier supplies parts for projects
Problem
This table may contain redundancy.
If relationships are independent, storing all combinations causes repeated data.
Decompose into Smaller Tables
SupplierPart
Supplier Part
S1 P1
S1 P2
S2 P1
PartProject
Part Project
P1 J1
P2 J1
SupplierProject
Supplier Project
S1 J1
S2 J1
Join Back
Using joins:
SELECT *
FROM SupplierPart sp
JOIN PartProject pp
ON [Link] = [Link]
JOIN SupplierProject sj
ON [Link] = [Link]
AND [Link] = [Link];
Original table can be reconstructed.
Why 5NF Needed?
To eliminate:
● complex redundancy
● repeated combinations
● incorrect data after joins
Used mainly in:
● large enterprise databases
● data warehouses
● highly relational systems
Difference Between 4NF and 5NF
4NF 5NF
Removes multi-valued dependency Removes join dependency
Focuses on independent Focuses on decomposition and
multi-values joins
Real-Life Example
Not in 5NF
Doctor Patient Hospital
D1 P1 H1
D1 P2 H1
D2 P1 H2
This may create redundancy if:
● doctor-hospital
● doctor-patient
● patient-hospital
relationships are independent.
Decompose
DoctorPatient
Doctor Patient
DoctorHospital
Doctor Hospital
PatientHospital
Patient Hospital