Normalize a Table up to Third Normal Form (3NF)

Problem: Normalize a Table up to Third Normal Form (3NF)

Suppose we have the following unnormalized table in a university database:

Student_Course_Record (Unnormalized Form)

StudentID	StudentName	Department	CourseIDs	CourseNames	InstructorNames
101	Ahsan	CSE	C101, C102	DBMS, Algorithms	Rahman, Karim
102	Nabila	CSE	C101	DBMS	Rahman
103	Tanvir	EEE	E201, E202	Circuits, Electronics	Ahmed, Hasan

Problems in This Table

• Multiple courses are stored in a single row (repeating groups).

• Course details are mixed with student details.

• Instructor depends on Course, not directly on Student.

Now, normalize this table step by step up to Third Normal Form (3NF).

---

Step 1: First Normal Form (1NF)

Rule of 1NF:

• Remove repeating groups.

• Ensure each column contains atomic (single) values.

• Each row must be unique.

Transformed Table (1NF)

StudentID	StudentName	Department	CourseID	CourseName	InstructorName
101	Ahsan	CSE	C101	DBMS	Rahman
101	Ahsan	CSE	C102	Algorithms	Karim
102	Nabila	CSE	C101	DBMS	Rahman
103	Tanvir	EEE	E201	Circuits	Ahmed
103	Tanvir	EEE	E202	Electronics	Hasan

Justification:

• Repeating groups (multiple courses in one cell) are removed.

• Each field now contains only one value.

• Table structure is atomic.

However, problems still exist:

• StudentName and Department depend only on StudentID.

• CourseName and InstructorName depend only on CourseID.

• This creates partial dependency.

---

Step 2: Second Normal Form (2NF)

Rule of 2NF:

• Must already be in 1NF.

• Remove partial dependencies.

• Non-key attributes must depend on the entire primary key.

Here, the composite key is:
(StudentID, CourseID)

But:

• StudentName, Department depend only on StudentID.

• CourseName, InstructorName depend only on CourseID.

Transformed Tables (2NF)

1. Students Table

StudentID	StudentName	Department
101	Ahsan	CSE
102	Nabila	CSE
103	Tanvir	EEE

2. Courses Table

CourseID	CourseName	InstructorName
C101	DBMS	Rahman
C102	Algorithms	Karim
E201	Circuits	Ahmed
E202	Electronics	Hasan

3. Enrollments Table

StudentID	CourseID
101	C101
101	C102
102	C101
103	E201
103	E202

Justification:

• Student-related data stored separately.

• Course-related data stored separately.

• Enrollment table connects students and courses.

• Partial dependencies removed.

But still:

• InstructorName may depend on CourseName.

• Department details may depend on another attribute.

• This creates transitive dependency.

---

Step 3: Third Normal Form (3NF)

Rule of 3NF:

• Must already be in 2NF.

• Remove transitive dependencies.

• Non-key attributes should depend only on the primary key.

Assume:

• Each Instructor teaches one Course.

• Each Department has a DepartmentHead.

If Instructor details are separate, we refine further.

Final Tables (3NF)

1. Students

StudentID	StudentName	DepartmentID
101	Ahsan	D01
102	Nabila	D01
103	Tanvir	D02

2. Departments

DepartmentID	DepartmentName
D01	CSE
D02	EEE

3. Courses

CourseID	CourseName
C101	DBMS
C102	Algorithms
E201	Circuits
E202	Electronics

4. Instructors

InstructorID	InstructorName	CourseID
I01	Rahman	C101
I02	Karim	C102
I03	Ahmed	E201
I04	Hasan	E202

5. Enrollments

StudentID	CourseID
101	C101
101	C102
102	C101
103	E201
103	E202

Justification:

• Transitive dependencies removed.

• Student depends only on StudentID.

• Course depends only on CourseID.

• Department stored separately.

• Instructor stored separately.

• Data redundancy minimized.

• Update, insertion, and deletion anomalies eliminated.

---

Final Summary

Normal Form	Problem Removed
1NF	Repeating groups
2NF	Partial dependency
3NF	Transitive dependency

Now the database is fully normalized up to Third Normal Form (3NF), ensuring:

• Minimal redundancy

• Better data integrity

• No update anomalies

• Improved consistency