Third Normal Form

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

EmployeeID	Skill	SkillLevel
1	Java	Intermediate
1	SQL	Advanced
2	Java	Beginner
2	SQL	Intermediate

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

Skill	SkillLevel
Java	Intermediate
SQL	Advanced
Java	Beginner
SQL	Intermediate

Table: EmployeeSkills

EmployeeID	Skill
1	Java
1	SQL
2	Java
2	SQL

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.