Mastering Database Management

0 of 78 lessons complete (0%)

Data Modeling

Third Normal Form

You don’t have access to this lesson

Please register or sign in to access the course content.

Definition of Third Normal Form (3NF):

Third Normal Form (3NF) is a property of a relation in a relational database. A table is said to be in 3NF if it is in 2NF and there are no transitive dependencies. In other words, 3NF eliminates dependencies where non-prime attributes depend on other non-prime attributes.

Example:

Consider a table that violates 3NF:

Table: EmployeeSkills

EmployeeIDSkillSkillLevel
1JavaIntermediate
1SQLAdvanced
2JavaBeginner
2SQLIntermediate

In this table, the primary key is “EmployeeID,” and the non-prime attribute “SkillLevel” depends on the non-prime attribute “Skill,” creating a transitive dependency.

Identifying Transitive Dependency:

  • “SkillLevel” depends on “Skill,” which, in turn, depends on the primary key “EmployeeID.”

Converting to Third Normal Form:

To achieve 3NF, we need to eliminate the transitive dependency:

Table: Skills

SkillSkillLevel
JavaIntermediate
SQLAdvanced
JavaBeginner
SQLIntermediate

Table: EmployeeSkills

EmployeeIDSkill
1Java
1SQL
2Java
2SQL

Explanation:

  1. Identifying Transitive Dependency:
    • In the original table, “SkillLevel” depends on “Skill,” and “Skill” depends on “EmployeeID.”
    • This creates a transitive dependency, violating 3NF.
  2. Eliminating Transitive Dependency:
    • The “Skills” table contains unique skills and their respective skill levels.
    • The “EmployeeSkills” table now contains only the “EmployeeID” and “Skill” columns, removing the transitive dependency.
  3. Achieving Third Normal Form:
    • The resulting tables are now in 3NF.
    • All non-prime attributes are directly dependent on the primary key, and there are no transitive dependencies.

By decomposing the original table into two tables based on functional dependencies and eliminating the transitive dependency, we ensure that the tables are now in Third Normal Form (3NF). This normalization process helps in avoiding data anomalies and maintaining data integrity.