Problem 1

Given Relation
ENROLLMENT(StudentID, CourseID, StudentName, CourseName, Instructor, Marks)
Functional Dependencies
StudentID → StudentName
CourseID → CourseName, Instructor
StudentID, CourseID → Marks

Step 1: Candidate Key

(StudentID, CourseID)+
StudentID → StudentName
CourseID → CourseName, Instructor
StudentID, CourseID → Marks
Therefore
(StudentID, CourseID)+
= {StudentID, CourseID, StudentName, CourseName, Instructor, Marks}

Candidate Key = (StudentID, CourseID)

Prime attributes = StudentID, CourseID

Step 2: First Normal Form (1NF)

All attributes contain atomic values.
Relation is in 1NF

Step 3: Second Normal Form (2NF)

Check partial dependency.
StudentID → StudentName
CourseID → CourseName, Instructor
These depend on part of the composite key.
Violates 2NF
Decomposition into 2NF
R1(StudentID, StudentName)
R2(CourseID, CourseName, Instructor)
R3(StudentID, CourseID, Marks)
Now relations satisfy 2NF

Step 4: Third Normal Form (3NF)

Check transitive dependency.
No transitive dependency exists.
Relations are in 3NF

Final Relations
R1(StudentID, StudentName)
R2(CourseID, CourseName, Instructor)
R3(StudentID, CourseID, Marks)

Problem 2
Given Relation
EMP_PROJECT(EmpID, ProjectID, EmpName, ProjectName, ManagerID, ManagerName,
HoursWorked)
Functional Dependencies
EmpID → EmpName
ProjectID → ProjectName, ManagerID
ManagerID → ManagerName
EmpID, ProjectID → HoursWorked

Step 1: Candidate Key

(EmpID, ProjectID)+
EmpID → EmpName
ProjectID → ProjectName, ManagerID
ManagerID → ManagerName
EmpID, ProjectID → HoursWorked
Therefore
(EmpID, ProjectID)+
= {EmpID, ProjectID, EmpName, ProjectName, ManagerID, ManagerName, HoursWorked}
Candidate Key = (EmpID, ProjectID)

Step 2: First Normal Form (1NF)

All attributes contain atomic values.
Relation is in 1NF

Step 3: Second Normal Form (2NF)

Check partial dependency.
EmpID → EmpName
ProjectID → ProjectName, ManagerID
Violates 2NF

Decomposition into 2NF

R1(EmpID, EmpName)
R2(ProjectID, ProjectName, ManagerID)
R3(EmpID, ProjectID, HoursWorked)

Step 4: Third Normal Form (3NF)

Check for transitive dependency.
ManagerID → ManagerName
Thus
ProjectID → ManagerID → ManagerName
Transitive dependency exists.

Decomposition into 3NF

R4(ManagerID, ManagerName)
Modify previous relation:
R2(ProjectID, ManagerID)
Final 3NF Relations
R1(EmpID, EmpName)
R2(ProjectID, ManagerID)
R3(EmpID, ProjectID, HoursWorked)
R4(ManagerID, ManagerName)

Problem 3
Given Relation
LIBRARY(BookID, MemberID, BookTitle, Author, MemberName, IssueDate, ReturnDate)
Functional Dependencies
BookID → BookTitle, Author
MemberID → MemberName
BookID, MemberID → IssueDate, ReturnDate

Step 1: Candidate Key

(BookID, MemberID)+
BookID → BookTitle, Author
MemberID → MemberName
BookID, MemberID → IssueDate, ReturnDate
Candidate Key = (BookID, MemberID)

Step 2: First Normal Form (1NF)

All attributes contain atomic values.
Relation is in 1NF

Step 3: Second Normal Form (2NF)

BookID → BookTitle, Author
MemberID → MemberName
These depend on part of the composite key.
Violates 2NF
Decomposition into 2NF
R1(BookID, BookTitle, Author)
R2(MemberID, MemberName)
R3(BookID, MemberID, IssueDate, ReturnDate)

Step 4: Third Normal Form (3NF)

No transitive dependency.
Relations are in 3NF

Final Relations
R1(BookID, BookTitle, Author)
R2(MemberID, MemberName)
R3(BookID, MemberID, IssueDate, ReturnDate)

Problem 4
Given Relation
FLIGHT_BOOKING(PassengerID, FlightID, PassengerName, FlightName, PilotID,
PilotName, SeatNo)
Functional Dependencies
PassengerID → PassengerName
FlightID → FlightName, PilotID
PilotID → PilotName
PassengerID, FlightID → SeatNo

Step 1: Candidate Key

(PassengerID, FlightID)+
= {PassengerID, FlightID, PassengerName, FlightName, PilotID, PilotName, SeatNo}
Candidate Key = (PassengerID, FlightID)

Step 2: 1NF
All attributes atomic.
1NF satisfied.
Step 3: 2NF
PassengerID → PassengerName
FlightID → FlightName, PilotID
Partial dependency exists.

Decomposition into 2NF

R1(PassengerID, PassengerName)
R2(FlightID, FlightName, PilotID)
R3(PassengerID, FlightID, SeatNo)

Step 4: 3NF
PilotID → PilotName
Thus
FlightID → PilotID → PilotName
Transitive dependency.

Decomposition into 3NF

R4(PilotID, PilotName)
Modify relation:
R2(FlightID, PilotID)

Final 3NF Relations

R1(PassengerID, PassengerName)
R2(FlightID, PilotID)
R3(PassengerID, FlightID, SeatNo)
R4(PilotID, PilotName)
BCNF :
Problem 1
Relation
R(A, B, C, D, E, F, G)
FDs
A→B
B→C
A,D → E
E→F
F→G

(a) Candidate Key

Compute (AD)+
Start: {A, D}
A → B → add B
B → C → add C
AD → E → add E
E → F → add F
F → G → add G
Therefore
(AD)+ = {A, B, C, D, E, F, G}
Candidate Key = {A, D}
Prime attributes = A, D

(b) Running Normalization

1NF
All attributes atomic.
R in 1NF

2NF
Check partial dependency on part of key.
A→B
B→C
Here A (part of key) determines non-prime attributes.
Violates 2NF
Decompose
R1(A,B,C)
R2(A,D,E,F,G)
3NF
Check R1(A,B,C)
A→B
B→C
Transitive dependency exists.
Decompose
R3(A,B)
R4(B,C)

Check R2(A,D,E,F,G)
AD → E
E→F
F→G
Transitive dependency exists.
Decompose
R5(E,F)
R6(F,G)
R7(A,D,E)
BCNF Check
R3(A,B) → A is key
R4(B,C) → B is key
R5(E,F) → E is key
R6(F,G) → F is key
R7(A,D,E) → AD is key
Final BCNF Relations
R3(A,B)
R4(B,C)
R7(A,D,E)
R5(E,F)
R6(F,G)
Problem 2
Relation
R(A,B,C,D,E,F,G,H)
FDs
A→B
C→D
AC → E
E→F
F→G
B→H

(a) Candidate Key

Compute (AC)+
AC → E
A→B
B→H
C→D
E→F
F→G
Thus
(AC)+ = {A,B,C,D,E,F,G,H}
Candidate Key = {A,C}

(b) Normalization
1NF
Atomic attributes.
1NF satisfied

2NF
Partial dependencies exist.
A→B
B→H
C→D
Since A and C are parts of key
Violates 2NF
Decompose
R1(A,B,H)
R2(C,D)
R3(A,C,E,F,G)

3NF
Check R1(A,B,H)
A→B
B→H
Transitive dependency.
Decompose
R4(A,B)
R5(B,H)

Check R3(A,C,E,F,G)
AC → E
E→F
F→G
Transitive dependency exists.
Decompose
R6(E,F)
R7(F,G)
R8(A,C,E)

BCNF Check
All determinants are keys. BCNF satisfied

Final BCNF Relations

R4(A,B)
R5(B,H)
R2(C,D)
R8(A,C,E)
R6(E,F)
R7(F,G)
Problem 3
Relation
R(P,Q,R,S,T,U,V)
FDs
P→Q
Q→R
PS → T
T→U
U→V

(a) Candidate Key

Compute (PS)+
P→Q
Q→R
PS → T
T→U
U→V
Thus
(PS)+ = {P,Q,R,S,T,U,V}
Candidate Key = {P,S}

(b) Normalization
1NF
Relation in 1NF

2NF
Partial dependency:
P→Q
Q→R
P is part of key.
Violates 2NF

Decompose
R1(P,Q,R)
R2(P,S,T,U,V)

3NF
Check R1
P→Q
Q→R
Transitive dependency.
Decompose
R3(P,Q)
R4(Q,R)

Check R2
PS → T
T→U
U→V
Transitive dependency.
Decompose
R5(T,U)
R6(U,V)
R7(P,S,T)

BCNF
All determinants are keys.
BCNF satisfied

Final BCNF Relations

R3(P,Q)
R4(Q,R)
R7(P,S,T)
R5(T,U)
R6(U,V)
Problem 4
Relation
R(A,B,C,D,E,F,G)
FDs
A→B
B→C
AD → E
E→F
C→G

(a) Candidate Key

Compute (AD)+
A→B
B→C
C→G
AD → E
E→F
Thus
(AD)+ = {A,B,C,D,E,F,G}
Candidate Key = {A,D}

(b) Normalization
1NF
Satisfied

2NF
Partial dependency
A→B
B→C
Violates 2NF

Decompose
R1(A,B,C,G)
R2(A,D,E,F)
3NF
Check R1
A→B
B→C
C→G
Transitive dependency.
Decompose
R3(A,B)
R4(B,C)
R5(C,G)

Check R2
AD → E
E→F
Transitive dependency.
Decompose
R6(E,F)
R7(A,D,E)

BCNF
All determinants keys.
BCNF satisfied

Final BCNF Relations

R3(A,B)
R4(B,C)
R5(C,G)
R7(A,D,E)
R6(E,F)
Problem 5
Relation
R(K,L,M,N,O,P,Q)
FDs
K→L
L→M
KN → O
O→P
P→Q

(a) Candidate Key

Compute (KN)+
K→L→M
KN → O
O→P
P→Q
Thus
(KN)+ = {K,L,M,N,O,P,Q}
Candidate Key = {K,N}

(b) Normalization
1NF
Satisfied

2NF
Partial dependency
K→L
L→M
Violates 2NF

Decompose
R1(K,L,M)
R2(K,N,O,P,Q)
3NF
Check R1
K→L
L→M
Transitive dependency.
Decompose
R3(K,L)
R4(L,M)

Check R2
KN → O
O→P
P→Q
Transitive dependency.
Decompose
R5(O,P)
R6(P,Q)
R7(K,N,O)

BCNF
All determinants keys.
BCNF satisfied

Final BCNF Relations

R3(K,L)
R4(L,M)
R7(K,N,O)
R5(O,P)
R6(P,Q)
Problem 6
Relation
R(A,B,C,D,E,F,G,H)
FDs
A→B
B→C
AD → E
E→F
F→G
G→H

(a) Candidate Key

Compute (AD)+
A→B
B→C
AD → E
E→F
F→G
G→H
Thus
(AD)+ = {A,B,C,D,E,F,G,H}
Candidate Key = {A,D}

(b) Normalization
1NF
Satisfied

2NF
Partial dependency
A→B
B→C
Violates 2NF

Decompose
R1(A,B,C)
R2(A,D,E,F,G,H)

3NF
Check R1
A→B
B→C
Transitive dependency.
Decompose
R3(A,B)
R4(B,C)

Check R2
AD → E
E→F
F→G
G→H
Transitive dependency chain.
Decompose
R5(E,F)
R6(F,G)
R7(G,H)
R8(A,D,E)

BCNF
All determinants keys.
BCNF satisfied

Final BCNF Relations

R3(A,B)
R4(B,C)
R8(A,D,E)
R5(E,F)
R6(F,G)
R7(G,H)
(Normalization Problems without explicit FDs)
Question 1
Consider the scenario of a University Course Registration System. In this relation, we track
which student registers for which course in a particular semester, the marks obtained, and the
department offering the course. A student can register for many courses and a course can have
many students. The marks obtained depend on both which student registered and which course
it was. Each course belongs to a specific department and each department has a Head of
Department (HOD).
Relation
StudentCourseRecord

StudentID CourseID StudentName DeptID Marks DeptName HOD

S01 C101 Ravi D01 85 CSE Kumar

S01 C102 Ravi D01 78 CSE Kumar

S02 C101 Priya D01 91 CSE Kumar

S03 C103 Arun D02 72 ECE Meena

Identify the functional dependencies, keys and subsequently perform a step-by-step

decomposition up to BCNF.
Given:
• A student can register for many courses.
• A course can have many students.
• Marks depend on student and course.
• Each course belongs to one department.
• Each department has one head.
To Find:
1. Identify Functional Dependencies
2. Find Candidate Key
3. Decompose to BCNF
Solution
Relation
R(StudentID, CourseID, StudentName, DeptID, Marks, DeptName, HOD)

Step 1: Functional Dependencies

StudentID → StudentName
CourseID → DeptID
DeptID → DeptName, HOD
(StudentID, CourseID) → Marks

Step 2: Candidate Key

(StudentID, CourseID)

Step 3: First Normal Form (1NF)

The relation already satisfies 1NF because:
• All attributes are atomic
• No repeating groups exist
So relation remains:
R(StudentID, CourseID, StudentName, DeptID, Marks, DeptName, HOD)

Step 4: Second Normal Form (2NF)

2NF removes partial dependency.
Candidate Key = (StudentID, CourseID)
Partial dependency:
StudentID → StudentName
Decompose:
Student(StudentID, StudentName)
Enrollment(StudentID, CourseID, DeptID, Marks, DeptName, HOD)

Step 5: Third Normal Form (3NF)

Remove transitive dependencies
DeptID → DeptName, HOD
Decompose:
Department(DeptID, DeptName, HOD)
Course(CourseID, DeptID)
Enrollment(StudentID, CourseID, Marks)

Step 6: BCNF
Check determinants:
StudentID → StudentName
DeptID → DeptName, HOD
CourseID → DeptID
All determinants are candidate keys in their relations

Final BCNF relations:

Student(StudentID, StudentName)
Course(CourseID, DeptID)
Department(DeptID, DeptName, HOD)
Enrollment(StudentID, CourseID, Marks)
Question 2
Consider the scenario of an Airline Reservation System. In this relation, we track which
passenger booked which flight, their seat number, and the airline operating the flight. A
passenger can book many flights and a flight can have many passengers. The seat number
depends on both which passenger booked and which flight it was. Each flight is operated by a
specific airline and each airline has a headquarters location.A passenger can book many flights.
Relation
PassengerFlightRecord

PassengerID FlightID PassengerName AirlineID SeatNo AirlineName HQ

P01 F101 Raj A01 12A Indigo Delhi

P01 F102 Raj A02 15C AirIndia Mumbai

P02 F101 Anita A01 14B Indigo Delhi

Identify the functional dependencies, keys and subsequently perform a step-by-step

decomposition up to BCNF.
Given:
• A flight has many passengers.
• Seat number depends on passenger and flight.
• Each flight belongs to one airline.
• Each airline has a headquarters.
To Find:
1. Identify Functional Dependencies
2. Find Candidate Key
3. Decompose to BCNF
Solution
Relation
R(PassengerID, FlightID, PassengerName, AirlineID, SeatNo, AirlineName, HQ)

Functional Dependencies
PassengerID → PassengerName
FlightID → AirlineID
AirlineID → AirlineName, HQ
(PassengerID, FlightID) → SeatNo
Candidate Key
(PassengerID, FlightID)

1NF
All attributes atomic → Already 1NF

2NF
Partial dependency
PassengerID → PassengerName
Decompose
Passenger(PassengerID, PassengerName)
Booking(PassengerID, FlightID, AirlineID, SeatNo, AirlineName, HQ)

3NF
AirlineID → AirlineName, HQ
Decompose
Airline(AirlineID, AirlineName, HQ)
Flight(FlightID, AirlineID)
Booking(PassengerID, FlightID, SeatNo)

BCNF
Check determinants
PassengerID → PassengerName
FlightID → AirlineID
AirlineID → AirlineName, HQ

Final BCNF relations:

Passenger(PassengerID, PassengerName)
Flight(FlightID, AirlineID)
Airline(AirlineID, AirlineName, HQ)
Booking(PassengerID, FlightID, SeatNo)
Question 3
Consider the scenario of a Hospital Patient Treatment System. In this relation, we track which
patient consults which doctor, the diagnosis given, and the department of the doctor. A patient
can consult many doctors and a doctor can treat many patients. The diagnosis depends on both
which patient consulted and which doctor treated them. Each doctor belongs to a particular
department and each department has a department head.
PatientDoctorRecord

PatientID DoctorID PatientName DeptID Diagnosis DeptName Head

P01 D01 Arun DP01 Fever General Ramesh

P01 D02 Arun DP02 Eye infection Ophthalmology Devi

P02 D01 Ravi DP01 Cold General Ramesh

Identify the functional dependencies, keys and subsequently perform a step-by-step

decomposition up to BCNF.
Given:
• A patient can consult many doctors.
• A doctor treats many patients.
• Diagnosis depends on patient and doctor.
• Each doctor belongs to one department.
• Each department has one head.
To Find:
1. Identify Functional Dependencies
2. Find Candidate Key
3. Decompose to BCNF
Solution
Relation
R(PatientID, DoctorID, PatientName, DeptID, Diagnosis, DeptName, Head)

Functional Dependencies
PatientID → PatientName
DoctorID → DeptID
DeptID → DeptName, Head
(PatientID, DoctorID) → Diagnosis
Candidate Key
(PatientID, DoctorID)

1NF
Relation already in 1NF

2NF
Partial dependency
PatientID → PatientName
Decompose
Patient(PatientID, PatientName)
Treatment(PatientID, DoctorID, DeptID, Diagnosis, DeptName, Head)

3NF
DeptID → DeptName, Head
DoctorID → DeptID
Decompose
Doctor(DoctorID, DeptID)
Department(DeptID, DeptName, Head)
Treatment(PatientID, DoctorID, Diagnosis)

BCNF
All determinants are keys
PatientID → PatientName
DoctorID → DeptID
DeptID → DeptName, Head
Final BCNF relations:
Patient(PatientID, PatientName)
Doctor(DoctorID, DeptID)
Department(DeptID, DeptName, Head)
Treatment(PatientID, DoctorID, Diagnosis)
Question 4
Consider the scenario of an Online Shopping System. In this relation, we track which customer
purchases which product, the quantity purchased, and the supplier providing the product. A
customer can purchase many products and a product can be purchased by many customers. The
quantity purchased depends on both which customer purchased and which product it was. Each
product is supplied by a particular supplier and each supplier has a supplier name.
CustomerProductRecord

CustomerID ProductID CustomerName SupplierID Quantity SupplierName

C01 P101 John S01 2 Dell

C01 P102 John S02 1 HP

C02 P101 Maya S01 3 Dell

Identify the functional dependencies, keys and subsequently perform a step-by-step

decomposition up to BCNF.
Given:
• A customer can buy many products.
• A product can be bought by many customers.
• Quantity depends on customer and product.
• Each product has one supplier.
To Find:
1. Identify Functional Dependencies
2. Find Candidate Key
3. Decompose to BCNF

Solution
Relation
R(CustomerID, ProductID, CustomerName, SupplierID, Quantity, SupplierName)

Functional Dependencies
CustomerID → CustomerName
ProductID → SupplierID
SupplierID → SupplierName
(CustomerID, ProductID) → Quantity
Candidate Key
(CustomerID, ProductID)

1NF
Already in 1NF

2NF
CustomerID → CustomerName
Decompose
Customer(CustomerID, CustomerName)
OrderDetails(CustomerID, ProductID, SupplierID, Quantity, SupplierName)

3NF
SupplierID → SupplierName
ProductID → SupplierID
Decompose
Supplier(SupplierID, SupplierName)
Product(ProductID, SupplierID)
OrderDetails(CustomerID, ProductID, Quantity)

BCNF
All determinants are keys
CustomerID → CustomerName
ProductID → SupplierID
SupplierID → SupplierName

Final BCNF relations:

Customer(CustomerID, CustomerName)
Product(ProductID, SupplierID)
Supplier(SupplierID, SupplierName)
OrderDetails(CustomerID, ProductID, Quantity)
Question 5
Consider the scenario of a Railway Reservation System. In this relation, we track which
passenger travels in which train, their seat number, and the railway zone controlling the train.
A passenger can travel in many trains and a train can have many passengers. The seat number
depends on both which passenger travelled and which train it was. Each train belongs to a
particular railway zone and each zone has a zone name.
PassengerTrainRecord

PassengerID TrainID PassengerName ZoneID SeatNo ZoneName

P01 T101 Kumar Z01 S1-12 Southern

P01 T102 Kumar Z02 B1-15 Western

P02 T101 Ravi Z01 S1-14 Southern

Identify the functional dependencies, keys and subsequently perform a step-by-step

decomposition up to BCNF.
Given:
• A passenger can travel on many trains.
• A train has many passengers.
• Seat number depends on passenger and train.
• Each train belongs to one railway zone.

To Find:
1. Identify Functional Dependencies
2. Find Candidate Key
3. Decompose to BCNF
Solution
Functional Dependencies
PassengerID → PassengerName
TrainID → ZoneID
ZoneID → ZoneName
(PassengerID, TrainID) → SeatNo

Candidate Key
(PassengerID, TrainID)
1NF
Already satisfies 1NF

2NF
PassengerID → PassengerName
Decompose
Passenger(PassengerID, PassengerName)
Reservation(PassengerID, TrainID, ZoneID, SeatNo, ZoneName)

3NF
ZoneID → ZoneName
TrainID → ZoneID
Decompose
Train(TrainID, ZoneID)
Zone(ZoneID, ZoneName)
Reservation(PassengerID, TrainID, SeatNo)

BCNF
All determinants are keys
PassengerID → PassengerName
TrainID → ZoneID
ZoneID → ZoneName

Final BCNF relations:

Passenger(PassengerID, PassengerName)
Train(TrainID, ZoneID)
Zone(ZoneID, ZoneName)
Reservation(PassengerID, TrainID, SeatNo)
Question 6
GIVEN RELATION: R(A,B,C,D,E,F,G)
Functional Dependencies (FDs)
A→B
B→C
A→D
Multivalued Dependencies (MVDs)
A↠E
A↠F
Join Dependency (JD)
(A, D, G) can be reconstructed from:
(A, D)
(A, G)
(D, G)

STEP 1: 1NF
Assume initially:
E and F are multi-valued attributes.
To convert to 1NF, make all attributes atomic.
R_1 (A,B,C,D,E,F,G)
Now each tuple contains:
One value of E
One value of F
Relation is in 1NF
STEP 2: 2NF
Step 2.1 – Find Candidate Key
Find closure of A:
A→B
B→C
A→D
So,
A⁺ = {A, B, C, D}
Still missing E, F, G
Since no FD determines E, F, G:
Candidate Key = (A, E, F, G)

Step 2.2 – Check Partial Dependency

We see:
A→B
A→D
A is only part of composite key → Partial dependency
So NOT in 2NF.

Step 2.3 – Decompose

Create:
R₂(A, B, D)
R₃(B, C)
R₄(A, E, F, G)
Now no attribute partially depends on key.
In 2NF
STEP 3: 3NF
Check transitive dependency in R₂:
B → C (via R₃ already separated)
Now:
R₂(A, B, D)
R₃(B, C)
R₄(A, E, F, G)
No transitive dependency remains.
In 3NF
STEP 4: BCNF (3.5NF)
Rule: Determinant must be super key.
Check each relation:
R₂(A, B, D)
A→B
A→D
A is key → BCNF

R₃(B, C)
B→C
B is key → BCNF

R₄(A, E, F, G)
No problematic FD.
BCNF
All relations now in BCNF

STEP 5: 4NF
Now check multivalued dependencies in:
R₄(A, E, F, G)
Given:
A↠E
A↠F
A is NOT super key in R₄
So violates 4NF.

Decompose R₄
R₅(A, E)
R₆(A, F)
R₇(A, G)
Now each MVD isolated.
All relations in 4NF

STEP 6: 5NF
Now consider relation:
R₈(A, D, G)
Given Join Dependency:
(A, D, G) can be reconstructed from:
(A, D)
(A, G)
(D, G)
This is a non-trivial join dependency
So not in 5NF.
Decompose
R₉(A, D)
R₁₀(A, G)
R₁₁(D, G)
Now no non-trivial join dependency remains.
In 5NF

FINAL SET OF RELATIONS (After Full Normalization)

R₂(A, B, D)
R₃(B, C)
R₅(A, E)
R₆(A, F)
R₉(A, D)
R₁₀(A, G)
R₁₁(D, G)

SUMMARY:
Step Removed
1NF Repeating groups
2NF Partial dependency
3NF Transitive dependency
BCNF Non-superkey determinants
4NF Multivalued dependency
5NF Join dependency

Question – 7
Given Relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐹)
Functional Dependencies (FDs)
AB → C
C→D
A→E
Multivalued Dependency (MVD)
B↠F

Join Dependency (JD)

(A, B, F) can be reconstructed from:
(A, B)
(B, F)
(A, F)

STEP 1: Convert to 1NF

Assume initially:
F is multivalued.

To satisfy 1NF, make all attributes atomic.

𝑅1(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐹)
Now each row contains only single values.
In 1NF

STEP 2: Convert to 2NF

Step 2.1: Find Candidate Key
Using FDs:
AB → C
C→D
A→E

Compute closure of AB:

AB⁺ = {A, B, C, D, E}
Still missing F.
Since B ↠ F (not FD), F must be included in key.
Therefore,
Candidate Key = (A, B, F)

Step 2.2: Check Partial Dependency

Partial dependencies:
A → E (A is part of composite key)
AB → C (full key dependency )
Since A alone determines E → violation.
So NOT in 2NF.
Step 2.3: Decompose
Create:
R₂(A, E)
R₃(A, B, C, D, F)

Now no partial dependency.

In 2NF
STEP 3: Convert to 3NF
Now check R₃(A, B, C, D, F)
FDs:
AB → C
C→D

Here:
AB → C
C→D

So AB → D (transitive dependency)
Not in 3NF.

Decompose
R₄(C, D)
R₅(A, B, C, F)

Now no transitive dependency.

In 3NF

STEP 4: Convert to BCNF (3.5NF)

Check each relation.
R₂(A, E)
A→E
A is key
BCNF satisfied

R₄(C, D)
C→D
C is key
BCNF satisfied

R₅(A, B, C, F)
AB → C
AB is NOT super key (key is ABF)
So violates BCNF.

Decompose R₅
R₆(A, B, C)
R₇(A, B, F)

Now:
In R₆: AB → C
AB is key
In R₇: No FD violation
All relations in BCNF

STEP 5: Convert to 4NF

Check multivalued dependency:
B↠F
Relation R₇(A, B, F)
B is NOT super key
So violates 4NF.

Decompose R₇
R₈(B, F)
R₉(A, B)

Now MVD removed.

In 4NF

STEP 6: Convert to 5NF

Consider relation R₉(A, B)

Suppose join dependency exists in original:

(A, B, F) reconstructed from:

(A, B)
(B, F)
(A, F)
Check if non-trivial JD exists.

Relation:
R₁₀(A, F)
If dependency is not implied by candidate key → violation.

Decompose to 5NF
R₁₁(A, B)
R₈(B, F)
R₁₀(A, F)
Now no non-trivial join dependency remains.
In 5NF
FINAL DECOMPOSED SET
R₂(A, E)
R₄(C, D)
R₆(A, B, C)
R₈(B, F)
R₁₁(A, B)
R₁₀(A, F)