Fourth Normal Form (4NF) is a level of database normalization that addresses multi-valued dependencies. It ensures that there are no non-trivial multi-valued dependencies between candidate keys and non-key attributes. To achieve 4NF, a database should already be in BCNF. Let’s explore how to use 4NF normalization with an example:
Example: Product Supplier Database
Consider a database that tracks products and their suppliers. Each product can be supplied by multiple suppliers, and each supplier can supply multiple products. We’ll start with an unnormalized table and then proceed to normalize it to 4NF.
Unnormalized Table (BCNF):
In this table,
(ProductID, SupplierID) is the composite primary key.
Step 1: Identify Multi-Valued Dependencies
A multi-valued dependency exists when an attribute is dependent on only part of a multi-valued key, and not on the entire key. In our example,
ProductName is dependent on only
SupplierName is dependent on only
Step 2: Create Separate Tables
We will create two separate tables: one for products and another for suppliers.
Step 3: Update Original Table
SupplierName columns from the original table.
ProductSupplier Table (4NF):
Conclusion: The database is now in Fourth Normal Form (4NF). We have eliminated the multi-valued dependencies by creating separate
Suppliers tables and referencing them in the
ProductSupplier table. This ensures that non-key attributes are only dependent on the entire key.
It’s worth noting that 4NF is not always necessary for every database. Achieving higher normal forms can sometimes result in increased complexity, and the decision to normalize to 4NF should be based on the specific requirements and use cases of the database.