Normalizing Database (3NF)

Normalizing a database to the Third Normal Form (3NF) involves eliminating transitive dependencies and ensuring that non-key attributes are only dependent on the primary key. To achieve 3NF, the database should already be in 2NF. Let’s continue with the example of the Student Course Registration Database and normalize it to 3NF:

Recap: Current Database State (2NF)

We have the following tables:

Students Table:

StudentID	StudentName
1	John
2	Jane
3	Alex

Courses Table:

CourseID	CourseName
101	Math
102	Physics
103	Chemistry

Instructors Table:

CourseID	Instructor
101	Prof. Smith
102	Prof. Johnson
103	Prof. Davis

StudentRegistrations Table:

StudentID	CourseID
1	101
1	102
2	101
3	103

Step 1: Identify Transitive Dependencies

A transitive dependency exists when a non-key attribute depends on another non-key attribute, rather than directly on the primary key. In our case, there is a transitive dependency between CourseName and Instructor through the CourseID.

Step 2: Create Separate Tables

We will create a new table for course information, which will separate the CourseName and Instructor attributes from the Courses table.

CourseDetails Table:

CourseID	CourseName	Instructor
101	Math	Prof. Smith
102	Physics	Prof. Johnson
103	Chemistry	Prof. Davis

Step 3: Update Original Table

We can now remove the CourseName and Instructor columns from the Courses table.

Courses Table (3NF):

CourseID
101
102
103

Step 4: Update Other Tables

Update the StudentRegistrations table to reference the CourseDetails table.

StudentRegistrations Table (3NF):

StudentID	CourseID
1	101
1	102
2	101
3	103

Conclusion: The database is now in the Third Normal Form (3NF). We have removed the transitive dependency between CourseName and Instructor by creating a separate CourseDetails table. This ensures that non-key attributes are only dependent on the primary key.

It’s important to note that normalization is a process that can continue beyond 3NF, depending on the specific requirements and complexities of your data. Further normalization to Boyce-Codd Normal Form (BCNF) or even higher normal forms might be necessary in certain cases to eliminate additional types of dependencies and anomalies.