Introduction
Before understanding Fourth Normal Form (4NF), it is important to understand the concept of multivalued dependencies. Multivalued dependencies occur when a single attribute is associated with multiple independent values of another attribute.
Earlier normal forms such as First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF) primarily focus on functional dependencies. However, these normal forms do not completely eliminate redundancy caused by independent multivalued attributes.
As a result, even a relation that satisfies BCNF can still experience insertion, deletion, and update anomalies when multivalued dependencies exist.
Fourth Normal Form (4NF) addresses these problems by eliminating undesirable multivalued dependencies from a relation.
Table of Contents
Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
What is Fourth Normal Form?
A relation is said to be in Fourth Normal Form (4NF) if:
- The relation is already in Boyce-Codd Normal Form (BCNF).
- It contains no non-trivial multivalued dependencies.
In simpler terms, 4NF ensures that independent multivalued facts about an entity are not unnecessarily stored together in the same table.
Suppose an employee can have several skills and several hobbies, and the skills are completely independent of the hobbies. Storing both sets of information in the same relation can create unnecessary combinations and redundancy.
4NF solves this problem by decomposing the relation into separate tables.
What is a Multivalued Dependency?
A multivalued dependency (MVD) occurs when one attribute determines a set of values for another attribute independently of the remaining attributes.
A multivalued dependency is represented using the symbol →→.
For example:
Employee →→ Equipment
Employee →→ LanguageThis means that an employee can have multiple equipment items and multiple languages, and these two sets of values are independent of each other.
Example 1: Employee Relation
Consider the following EMPLOYEE relation:
| Emp_Name | Equipment | Language |
|---|---|---|
| Anurag | Personal Computer | English |
| Anurag | Personal Computer | French |
| Anurag | Mainframe | English |
| Anurag | Mainframe | French |
| Kapil | Personal Computer | English |
| Kapil | Personal Computer | French |
| Kapil | Personal Computer | Japanese |
In this relation:
- An employee can be associated with multiple pieces of equipment.
- An employee can speak multiple languages.
- Equipment and language are independent of each other.
Therefore, the relation contains the following multivalued dependencies:
Emp_Name →→ Equipment
Emp_Name →→ LanguageKeeping these independent values in a single table produces unnecessary combinations. For example, every equipment item may have to be paired with every language associated with the same employee.
Decomposition into 4NF
To remove the multivalued dependencies, we decompose the original relation into two separate relations.
EMP_EQUIP
| Emp_Name | Equipment |
|---|---|
| Anurag | Personal Computer |
| Anurag | Mainframe |
| Kapil | Personal Computer |
EMP_LANG
| Emp_Name | Language |
|---|---|
| Anurag | English |
| Anurag | French |
| Kapil | English |
| Kapil | French |
| Kapil | Japanese |
Now, equipment and language information are stored independently. This eliminates the unnecessary combinations present in the original relation.
Example 2: Student Relation
Consider a STUDENT relation containing information about students, courses, and hobbies:
| STU_ID | COURSE | HOBBY |
|---|---|---|
| 21 | Computer | Dancing |
| 21 | Computer | Singing |
| 21 | Math | Dancing |
| 21 | Math | Singing |
| 34 | Chemistry | Dancing |
| 74 | Biology | Cricket |
| 59 | Physics | Hockey |
For student 21, there are two courses and two hobbies. Since courses and hobbies are independent, all possible combinations of those values are stored.
This creates unnecessary redundancy. The multivalued dependencies can be represented as:
STU_ID →→ COURSE
STU_ID →→ HOBBYTo achieve 4NF, we decompose the relation into two tables.
STUDENT_COURSE
| STU_ID | COURSE |
|---|---|
| 21 | Computer |
| 21 | Math |
| 34 | Chemistry |
| 74 | Biology |
| 59 | Physics |
STUDENT_HOBBY
| STU_ID | HOBBY |
|---|---|
| 21 | Dancing |
| 21 | Singing |
| 34 | Dancing |
| 74 | Cricket |
| 59 | Hockey |
The decomposition separates courses and hobbies into independent relations. This significantly reduces redundancy and makes the database easier to maintain.
Why is Fourth Normal Form Important?
4NF is important because it prevents independent multivalued facts from being unnecessarily combined in the same relation.
The major benefits include:
- Reduced Data Redundancy: Independent sets of values are stored separately.
- Better Data Consistency: Updating one type of information does not require unnecessary changes elsewhere.
- Fewer Anomalies: Insertion, deletion, and update anomalies caused by multivalued dependencies are reduced.
- Better Database Structure: Related information is organized into logically independent tables.
- Easier Maintenance: Changes to one multivalued attribute can be made without affecting unrelated information.
How to Convert a Relation into 4NF?
The general process for converting a relation into 4NF is:
- Ensure that the relation is already in BCNF.
- Identify any non-trivial multivalued dependencies.
- Determine whether the multivalued attributes are independent.
- Decompose the relation into separate relations based on those dependencies.
- Ensure that the decomposition is lossless.
For example, if a relation has:
Student →→ Course
Student →→ Hobbyit can generally be decomposed into:
STUDENT_COURSE(Student, Course)
STUDENT_HOBBY(Student, Hobby)YT:- DecodeIT
Fourth Normal Form vs BCNF
| BCNF | 4NF |
|---|---|
| Deals primarily with functional dependencies. | Deals with multivalued dependencies in addition to the requirements inherited from BCNF. |
| Every determinant must be a candidate key. | Every non-trivial multivalued dependency must have a superkey as its determinant. |
| Can still allow certain multivalued dependency problems. | Removes non-trivial multivalued dependencies that violate 4NF. |
Frequently Asked Questions About 4NF
1. What is Fourth Normal Form?
Fourth Normal Form is a database normalization level that eliminates undesirable non-trivial multivalued dependencies. A relation must first satisfy BCNF before it can satisfy 4NF.
2. What problem does 4NF solve?
4NF primarily solves redundancy and anomalies caused by independent multivalued attributes. These problems may remain even after a relation has been normalized to BCNF.
3. What is the main condition for 4NF?
A relation is in 4NF if, for every non-trivial multivalued dependency X →→ Y, X is a superkey of the relation.
4. Is every relation in 4NF also in BCNF?
Yes. By definition, 4NF is stronger than BCNF, so a relation in 4NF also satisfies BCNF.
5. What is the difference between 3NF, BCNF, and 4NF?
3NF addresses certain functional dependency violations, BCNF provides a stricter treatment of functional dependencies, and 4NF extends normalization to handle non-trivial multivalued dependencies.
Final Thoughts
Fourth Normal Form (4NF) is an important stage in database normalization. It focuses on eliminating redundancy caused by independent multivalued dependencies that may remain even after achieving BCNF.
By separating independent multivalued attributes into different relations, 4NF produces a cleaner and more maintainable database structure. It also helps prevent unnecessary data repetition and reduces the possibility of insertion, deletion, and update anomalies.
Therefore, when a database contains multiple independent sets of values associated with the same key, applying 4NF can lead to a more efficient and logically organized relational design.
Keywords
Fourth Normal Form (4NF), 4NF in DBMS, Fourth Normal Form in DBMS, 4NF normalization, multivalued dependency, multivalued dependency in DBMS, database normalization, BCNF, 3NF, relational database design, normalization in DBMS, 4NF examples
