Relational Database Design and Modeling for Software Engineers
Ask Author
Back to course home

0% completed

Vote For New Content
Third Normal Form (3NF)
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

The Third Normal Form (3NF) is a further step in the normalization process that eliminates transitive dependencies in a table. 3NF ensures that non-key attributes are directly dependent on the primary key and not indirectly dependent through another non-key attribute. By achieving 3NF, we reduce redundancy even further, making the database more efficient and consistent.

Note: For a table to be in 3NF, it must first be in 2NF.

Requirements of 3NF

To satisfy the requirements of 3NF:

  1. The table must already be in 2NF.
  2. There should be no transitive dependencies, meaning non-key attributes should not depend on other non-key attributes.

Example: Student_Department Table

Consider the following Student_Department table, which records information about students, their departments, and department locations. Here, Student_ID is the primary key.

Student_IDStudent_NameDepartment_IDDepartment_NameDepartment_Location
101Alice SmithD01ScienceBuilding A
102Bob JohnsonD02ArtsBuilding B
103Carol WhiteD03CommerceBuilding C

In this table:

  • Student_ID is the primary key.
  • Department_ID uniquely determines Department_Name and Department_Location, so we have transitive dependencies:
    • Student_ID → Department_ID
    • Department_ID → Department_Name and Department_ID → Department_Location

Because Department_Name and Department_Location depend on Department_ID (a non-key attribute) instead of directly on Student_ID, this table violates 3NF due to transitive dependencies.

Converting to 3NF

To bring this table into 3NF, we need to remove the transitive dependencies by separating the table into two tables: Student and Department. Here’s how we can structure them:

Step 1: Create the Student Table

The Student table will store information specific to each student, with Student_ID as the primary key.

Student_IDStudent_NameDepartment_ID
101Alice SmithD01
102Bob JohnsonD02
103Carol WhiteD03

Step 2: Create the Department Table

The Department table will store department-related information, with Department_ID as the primary key.

Department_IDDepartment_NameDepartment_Location
D01ScienceBuilding A
D02ArtsBuilding B
D03CommerceBuilding C

Result After Conversion to 3NF

After separating the original table into Student and Department tables:

  • The Student table now contains only the attributes directly related to students.
  • The Department table contains department-related attributes.
  • The transitive dependency has been removed, as Department_Name and Department_Location are now in a separate table directly dependent on Department_ID.

Why 3NF is Important

3NF helps:

  • Reduce Redundancy: By eliminating transitive dependencies, we avoid repeating information (e.g., department location).
  • Improve Data Integrity: Updates become more manageable, as each piece of data is stored in only one place.
  • Simplify Maintenance: Changes in one table (e.g., updating department location) don’t require updates in multiple rows, reducing the chance of errors.

In the next lesson, we’ll discuss Boyce-Codd Normal Form (BCNF), an advanced form of 3NF that resolves additional dependencies, ensuring a higher level of normalization.

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible