Mastering Database Management

0 of 78 lessons complete (0%)

Data Modeling

Fourth Normal Form

You don’t have access to this lesson

Please register or sign in to access the course content.

Definition of Fourth Normal Form (4NF):

The fourth Normal Form (4NF) is a level of database normalization that addresses multi-valued dependencies in a relational database. A table is said to be in 4NF if it is in BCNF and it has no non-trivial multi-valued dependencies. In simpler terms, 4NF ensures that there are no dependencies between non-prime attributes.

Key Concepts:

  1. Multi-valued Dependency:
    • A multi-valued dependency occurs when one attribute in a table uniquely determines another attribute, independent of the other attributes.
  2. Non-trivial Multi-valued Dependency:
    • A non-trivial multi-valued dependency exists when the relationship between attributes is not implied by a candidate key.

Example:

Consider a table that violates 4NF:

Table: EmployeeSkills

EmployeeIDSkillSkillLevel
1JavaIntermediate
1SQLAdvanced
2JavaBeginner
2SQLIntermediate

In this table, “EmployeeID” uniquely determines both “Skill” and “SkillLevel.”

Identifying Violation of 4NF:

  • The table violates 4NF because there is a multi-valued dependency: “EmployeeID” →→ “Skill” and “EmployeeID” →→ “SkillLevel.”

Converting to 4NF:

To achieve 4NF, we need to decompose the table:

Table: EmployeeSkills

EmployeeIDSkill
1Java
1SQL
2Java
2SQL

Table: SkillLevels

EmployeeIDSkillSkillLevel
1JavaIntermediate
1SQLAdvanced
2JavaBeginner
2SQLIntermediate

Explanation:

  1. Identifying Violation:
    • The original table violates 4NF because “EmployeeID” determines both “Skill” and “SkillLevel” independently.
  2. Decomposing the Table:
    • The “EmployeeSkills” table contains unique pairs of “EmployeeID” and “Skill.”
    • The “SkillLevels” table contains information about skill levels, and “EmployeeID” and “Skill” together form a composite key.
  3. Achieving 4NF:
    • The resulting tables are now in 4NF.
    • There are no non-trivial multi-valued dependencies between attributes, addressing the violation.

By decomposing the original table into two tables based on multi-valued dependencies, we ensure that the tables are now in Fourth Normal Form (4NF), reducing the risk of anomalies related to multi-valued dependencies.