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:
- Multi-valued Dependency:
- A multi-valued dependency occurs when one attribute in a table uniquely determines another attribute, independent of the other attributes.
- 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
| EmployeeID | Skill | SkillLevel |
|---|---|---|
| 1 | Java | Intermediate |
| 1 | SQL | Advanced |
| 2 | Java | Beginner |
| 2 | SQL | Intermediate |
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
| EmployeeID | Skill |
|---|---|
| 1 | Java |
| 1 | SQL |
| 2 | Java |
| 2 | SQL |
Table: SkillLevels
| EmployeeID | Skill | SkillLevel |
|---|---|---|
| 1 | Java | Intermediate |
| 1 | SQL | Advanced |
| 2 | Java | Beginner |
| 2 | SQL | Intermediate |
Explanation:
- Identifying Violation:
- The original table violates 4NF because “EmployeeID” determines both “Skill” and “SkillLevel” independently.
- 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.
- 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.