Published: Tuesday, December 26, 2023, Updated: Thursday, February 22, 2024

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

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:

InstructorIDCourseIDInstructorNameCourseNameDepartment
1101John DoeMath 101Mathematics
1102John DoePhysics 201Physics
2101Jane SmithMath 101Mathematics
2103Jane SmithChemistry 301Chemistry
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

InstructorIDInstructorName
1John Doe
2Jane Smith
Table: Courses

InstructorIDCourseIDCourseNameDepartment
1101Math 101Mathematics
1102Physics 201Physics
2101Math 101Mathematics
2103Chemistry 301Chemistry
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:

InstructorIDCourseIDCourseNameDepartment
1101Math 101Mathematics
1102Physics 201Physics
2101Math 101Mathematics
2103Chemistry 301Chemistry
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

DepartmentIDDepartment
1Math
2Physics
3Chemistry
Now, the "Courses" table looks like this:

InstructorIDCourseIDCourseNameDepartmentID
1101Math 1011
1102Physics 2012
2101Math 1011
2103Chemistry 3013
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.

Elliyas Ahmed
Elliyas Ahmed
Elliyas Ahmed, founder of COMPROMATH, is a multifaceted professional excelling in blogging, freelance web development, and SEO services. With a passion for digital empowerment, Elliyas delivers top-tier solutions to clients worldwide, inspiring success in the ever-evolving digital landscape.