Question 1: First Normal Form (1NF)
Problem:
Consider the following table of student information:
StudentI StudentNam Courses ContactNumbers
D e
1 Alice Math, Science 123-4567, 234-5678
2 Boss History 345-6789
3 Charlie Math, History 456-7890, 567-8901
Requirements:
Convert the table to 1NF.
Solution:
To achieve 1NF, remove any repeating groups by ensuring that each attribute contains only
atomic (indivisible) values.
StudentI StudentName Course ContactNumber
D
1 Alice Math 123-4567
1 Alice Science 234-5678
2 Boss History 345-6789
3 Charlie Math 456-7890
3 Charlie History 567-8901
Now, each field contains only a single value, so the table is in 1NF.
Question 2: Second Normal Form (2NF)
Problem:
Consider the following table for a book store:
OrderI BookID BookTitle Author OrderQuantity
D
101 B1 Database Mgmt Codd 2
102 B2 C++ Basics Stroustrup 1
103 B1 Database Mgmt Codd 3
Requirements:
Convert the table to 2NF by eliminating partial dependencies. Assume that (OrderID,
BookID) is the composite primary key.
Solution:
To be in 2NF, a table must be in 1NF, and all non-key attributes must depend on the entire
primary key.
Partial Dependency: BookTitle and Author depend only on BookID, not on
OrderID.
Separate the table into two tables:
1. Orders Table:
OrderID BookID OrderQuantity
101 B1 2
102 B2 1
103 B1 3
2. Books Table:
BookI BookTitle Author
D
B1 Database Mgmt Codd
B2 C++ Basics Stroustrup
Now, each non-key attribute is fully dependent on the entire primary key in both tables,
achieving 2NF.
Question 3: Third Normal Form (3NF)
Problem:
Consider the following table for employee information:
EmpID EmpName DeptID DeptName ManagerID ManagerName
1 John D1 HR 101 Alice
2 Mary D2 IT 102 Bob
3 Sarah D1 HR 101 Alice
Requirements:
Convert this table to 3NF by eliminating transitive dependencies.
Solution:
To achieve 3NF, the table must be in 2NF, and there should be no transitive dependency
(non-key attributes depending on other non-key attributes).
Transitive Dependency: DeptName and ManagerName depend on DeptID and
ManagerID, respectively, rather than directly on EmpID.
Split the table into three tables:
1. Employee Table:
EmpID EmpName DeptID ManagerID
1 John D1 101
2 Mary D2 102
3 Sarah D1 101
2. Department Table:
DeptI DeptName
D
D1 HR
D2 IT
3. Manager Table:
ManagerID ManagerName
101 Alice
102 Bob
Now, each non-key attribute is fully dependent on the primary key, achieving 3NF.
Question 4: Boyce-Codd Normal Form (BCNF)
Problem:
Consider the following table for student-course enrollments:
StudentI CourseID Instructor
D
S1 C1 Prof. A
S2 C2 Prof. B
S1 C2 Prof. B
S2 C1 Prof. A
Requirements:
Convert this table to BCNF if any anomalies exist. Assume that each course is taught by a
single instructor, but students can enroll in multiple courses.
Solution:
To be in BCNF, the table must be in 3NF, and all non-trivial functional dependencies should
have a superkey on the left side.
Functional Dependency: CourseID → Instructor (Each course has a single
instructor, so CourseID determines Instructor.)
Here, StudentID and CourseID together form a composite key, but CourseID alone
can determine Instructor, violating BCNF.
Separate the table into two tables:
1. Enrollment Table:
StudentI CourseID
D
S1 C1
S2 C2
S1 C2
S2 C1
2. Course Table:
CourseID Instructor
C1 Prof. A
C2 Prof. B
Now, all non-trivial functional dependencies in each table have a superkey on the left side, so
the tables are in BCNF.
