Fourth Normal Form (4NF) is a database normalization technique that builds upon the concepts of the first three normal forms (1NF, 2NF, and 3NF) to further eliminate redundancy and data dependencies in a relational database. The primary objective of 4NF is to address certain types of multi-valued dependencies that can occur when dealing with complex relationships between attributes.
To understand 4NF, let’s briefly review the previous normal forms:
- First Normal Form (1NF): Ensures atomicity by eliminating repeating groups and ensuring that each attribute in a relation contains only atomic (indivisible) values.
- Second Normal Form (2NF): Builds upon 1NF by removing partial dependencies. It requires that each non-key attribute is fully functionally dependent on the entire primary key.
- Third Normal Form (3NF): Extends 2NF by eliminating transitive dependencies. It states that no non-key attribute should be dependent on another non-key attribute.
Now, let’s delve into 4NF. It deals specifically with multi-valued dependencies, which occur when a relation has attributes that depend on multi-valued keys (sets of attributes). A multi-valued dependency arises when there are multiple independent relationships between two sets of attributes within a table.
To achieve 4NF, we decompose a relation that contains multi-valued dependencies into two or more separate relations. Each new relation will have its own primary key, including a foreign key to maintain the relationship with the original table.
By decomposing the relation, we can eliminate redundancy and anomalies that can arise from multi-valued dependencies. This process ensures that each relation contains only the attributes it needs to represent the dependencies it holds.
However, it’s important to note that achieving 4NF may not always be necessary or beneficial for every database design. It primarily becomes relevant when dealing with complex relationships and specific cases where multi-valued dependencies are present.
Overall, 4NF aims to enhance data integrity, minimize redundancy, and improve the efficiency of database operations by eliminating multi-valued dependencies through proper decomposition.
Example of Fourth normal form (4NF):
To illustrate Fourth Normal Form (4NF), let’s consider a hypothetical example involving a database for tracking projects and their team members. We have the following initial relation:
Project_Team (Project_ID, Project_Name, Team_Member)
The attributes are as follows:
- Project_ID (primary key): Unique identifier for each project.
- Project_Name: Name of the project.
- Team_Member: Name of a team member associated with the project. Multiple team members can be associated with a project.
Now, suppose we have the following set of data:
| Project_ID | Project_Name | Team_Member |
|---|---|---|
| 1 | Project A | John |
| 1 | Project A | Mary |
| 2 | Project B | John |
| 2 | Project B | Sarah |
| 2 | Project B | Michael |
| 3 | Project C | Mary |
| 3 | Project C | Sarah |
In this scenario, we can see that the relation exhibits a multi-valued dependency because there are multiple team members associated with each project. The primary key (Project_ID) determines the project, while the attribute Team_Member is dependent on the combination of both Project_ID and Project_Name.
To bring this relation to 4NF, we decompose it into two separate relations:
Projects (Project_ID, Project_Name)
| Project_ID | Project_Name |
|---|---|
| 1 | Project A |
| 2 | Project B |
| 3 | Project C |
Team_Members (Project_ID, Team_Member)
| Project_ID | Team_Member |
|---|---|
| 1 | John |
| 1 | Mary |
| 2 | John |
| 2 | Sarah |
| 2 | Michael |
| 3 | Mary |
| 3 | Sarah |
By decomposing the relation into these two tables, we have eliminated the multi-valued dependency, and each relation now contains only the attributes it needs to represent the respective dependencies. The Projects table represents the projects, while the Team_Members table associates team members with their respective projects using the foreign key Project_ID.
This decomposition ensures that the data is organized efficiently and avoids redundancy or anomalies that could arise from the multi-valued dependency. It conforms to the principles of 4NF by addressing the specific issue related to multi-valued dependencies.