Database Normalization (4NF)

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):

ProductID	ProductName	SupplierID	SupplierName
1	Laptop	101	Supplier A
1	Laptop	102	Supplier B
2	Phone	101	Supplier A
3	Tablet	102	Supplier B

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 ProductID, and SupplierName is dependent on only SupplierID.

Step 2: Create Separate Tables

We will create two separate tables: one for products and another for suppliers.

Products Table:

ProductID	ProductName
1	Laptop
2	Phone
3	Tablet

Suppliers Table:

SupplierID	SupplierName
101	Supplier A
102	Supplier B

Step 3: Update Original Table

Remove the ProductName and SupplierName columns from the original table.

ProductSupplier Table (4NF):

ProductID	SupplierID
1	101
1	102
2	101
3	102

Conclusion: The database is now in Fourth Normal Form (4NF). We have eliminated the multi-valued dependencies by creating separate Products and 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.