Scribd
Upload
11 views

Example Normalization With Solution

Uploaded by

sp23bscs0065
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
11 views

Example Normalization With Solution

Uploaded by

sp23bscs0065
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

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.

You might also like