Differences Between 2NF & 3NF in DBMS Normalization with Example

Both Second Normal Form (2NF) and Third Normal Form (3NF) are stages in the normalization process of relational database design. Each form addresses specific types of data redundancy and dependencies. Let's explore the differences between 2NF and 3NF with an example:

Second Normal Form (2NF):

A table is in 2NF if it is in 1NF (no repeating groups or atomic values), and all non-prime attributes are fully functionally dependent on the entire primary key.

Example Table:

Consider a table that tracks information about courses and instructors:

InstructorID	CourseID	InstructorName	CourseName	Department
1	101	John Doe	Math 101	Mathematics
1	102	John Doe	Physics 201	Physics
2	101	Jane Smith	Math 101	Mathematics
2	103	Jane Smith	Chemistry 301	Chemistry

In this example, the composite key is {InstructorID, CourseID}. The table is in 1NF because all values are atomic.

However, the attribute "InstructorName" is partially dependent on the composite key {InstructorID, CourseID}. "InstructorName" depends only on "InstructorID," not on the entire composite key.

Transformation to 2NF:

To bring the table to 2NF, we split it into two tables:

Table: Instructors

InstructorID	InstructorName
1	John Doe
2	Jane Smith

Table: Courses

InstructorID	CourseID	CourseName	Department
1	101	Math 101	Mathematics
1	102	Physics 201	Physics
2	101	Math 101	Mathematics
2	103	Chemistry 301	Chemistry

Now, "InstructorName" is fully functionally dependent on "InstructorID," and the tables are in 2NF.

Third Normal Form (3NF):

A table is in 3NF if it is in 2NF, and there are no transitive dependencies, meaning that non-prime attributes are not dependent on other non-prime attributes.

Example Table (in 2NF):

Consider the "Courses" table from the 2NF example:

InstructorID	CourseID	CourseName	Department
1	101	Math 101	Mathematics
1	102	Physics 201	Physics
2	101	Math 101	Mathematics
2	103	Chemistry 301	Chemistry

In this table, "Department" is transitively dependent on "InstructorID" through "CourseID."

Transformation to 3NF:

To bring the table to 3NF, we further normalize it by creating a separate table for "Departments":

Table: Departments

DepartmentID	Department
1	Math
2	Physics
3	Chemistry

Now, the "Courses" table looks like this:

InstructorID	CourseID	CourseName	DepartmentID
1	101	Math 101	1
1	102	Physics 201	2
2	101	Math 101	1
2	103	Chemistry 301	3

The "Department" attribute is no longer transitively dependent on the composite key, and the table is in 3NF.

In summary, 2NF eliminates partial dependencies, and 3NF eliminates transitive dependencies, ensuring a more robust and normalized database structure.