Problems on Normal Forms with Solutions
Problem 1:
Relation:
Student (RollNo, Name, Course, Instructor, InstructorPhone)
Functional Dependencies:
1. RollNo → Name, Course, Instructor
2. Instructor → InstructorPhone
Question: In which normal form is this relation?
Solution:
1NF Check:
o All attributes are atomic (no multi-valued or repeating groups). ✔
o So relation is in 1NF.
2NF Check:
o Candidate Key = RollNo (since RollNo uniquely identifies each student).
o Non-prime attributes (Name, Course, Instructor, InstructorPhone).
o RollNo → Name, Course, Instructor (full dependency, no partial). ✔
o But Instructor → InstructorPhone is a transitive dependency (RollNo →
Instructor and Instructor → InstructorPhone). ❌
o So it violates 2NF.
Final Answer: Relation is in 1NF only.
Problem 2:
Relation:
Order (OrderID, ProductID, ProductName, Quantity, CustomerID, CustomerName)
Functional Dependencies:
1. OrderID → CustomerID, CustomerName
2. ProductID → ProductName
3. (OrderID, ProductID) → Quantity
Question: In which normal form is this relation?
Solution:
1NF:
o All attributes atomic. ✔
o In 1NF.
Candidate Key: (OrderID, ProductID)
2NF:
o Partial dependencies exist:
ProductID → ProductName (ProductName depends only on part of key
ProductID). ❌
OrderID → CustomerID, CustomerName (depends only on OrderID). ❌
o Violates 2NF.
Final Answer: Relation is in 1NF, not in 2NF.
Problem 3:
Relation:
Employee (EmpID, DeptID, DeptName, ManagerID)
Functional Dependencies:
1. EmpID → DeptID
2. DeptID → DeptName, ManagerID
Question: In which normal form is this relation?
Solution:
1NF:
o All attributes atomic. ✔
Candidate Key: EmpID
2NF:
o No partial dependency (only single attribute key). ✔
3NF:
o Transitive dependency exists:
EmpID → DeptID and DeptID → DeptName, ManagerID
So EmpID → DeptName, ManagerID via DeptID. ❌
o Violates 3NF.
Final Answer: Relation is in 2NF, not 3NF.
Problem 4:
Relation:
Book (BookID, Title, Author, Publisher, PublisherAddress)
Functional Dependencies:
1. BookID → Title, Author, Publisher
2. Publisher → PublisherAddress
Question: In which normal form is this relation?
Solution:
1NF: ✔
Candidate Key: BookID
2NF: ✔ (since key is single attribute, no partial dependency).
3NF:
o Transitive dependency exists: BookID → Publisher → PublisherAddress. ❌
BCNF Check:
o Publisher → PublisherAddress is a violation, since Publisher is not a super key. ❌
Final Answer: Relation is in 2NF, not in 3NF/BCNF.
Problem 5 (Tricky):
Relation:
Supplier (SupplierID, PartID, SupplierName, PartName, Price)
Functional Dependencies:
1. SupplierID → SupplierName
2. PartID → PartName
3. (SupplierID, PartID) → Price
Question: In which normal form is this relation?
Solution:
1NF: ✔
Candidate Key: (SupplierID, PartID)
2NF:
o Partial dependencies exist:
SupplierID → SupplierName
PartID → PartName
o These depend only on part of composite key. ❌
Final Answer: Relation is in 1NF, not 2NF.
Problem 6:
Relation:
Hospital (PatientID, PatientName, DoctorID, DoctorName, Department)
Functional Dependencies:
1. PatientID → PatientName, DoctorID
2. DoctorID → DoctorName, Department
Solution:
1NF: ✔ (atomic attributes)
Candidate Key: PatientID
2NF: ✔ (single attribute key, no partial dependency)
3NF:
o PatientID → DoctorID and DoctorID → DoctorName, Department
o So, PatientID → DoctorName, Department (transitive). ❌
BCNF: DoctorID → DoctorName, Department is a violation since DoctorID is not a
superkey. ❌
Final Answer: 2NF only.
Problem 7:
Relation:
Project (ProjID, EmpID, EmpName, ProjName, Role)
Functional Dependencies:
1. ProjID → ProjName
2. EmpID → EmpName
3. (ProjID, EmpID) → Role
Solution:
1NF: ✔
Candidate Key: (ProjID, EmpID)
2NF: ❌ (since ProjID → ProjName and EmpID → EmpName are partial dependencies)
Final Answer: 1NF only.
Problem 8:
Relation:
Course (CourseID, CourseName, Credits)
Functional Dependencies:
1. CourseID → CourseName, Credits
Solution:
1NF: ✔
Candidate Key: CourseID
2NF: ✔ (single key, no partial dependency)
3NF: ✔ (no transitive dependency)
BCNF: ✔ (all determinants are superkeys)
Final Answer: BCNF.
Problem 9:
Relation:
Bank (AccountNo, CustomerID, CustomerName, Balance)
Functional Dependencies:
1. AccountNo → Balance, CustomerID
2. CustomerID → CustomerName
Solution:
1NF: ✔
Candidate Key: AccountNo
2NF: ✔ (single attribute key)
3NF: ❌ (transitive: AccountNo → CustomerID → CustomerName)
BCNF: ❌ (CustomerID is not a superkey)
Final Answer: 2NF only.
Problem 10:
Relation:
Library (BookID, MemberID, IssueDate, ReturnDate)
Functional Dependencies:
1. (BookID, MemberID) → IssueDate, ReturnDate
Solution:
1NF: ✔
Candidate Key: (BookID, MemberID)
2NF: ✔ (all attributes fully depend on composite key)
3NF: ✔ (no transitive dependency)
BCNF: ✔ (all determinants are candidate keys)
Final Answer: BCNF.
