Fourth normal form
From Wikipedia, the free encyclopedia
Fourth normal form (4NF) is a normal form used in database normalization. 4NF ensures that independent multivalued facts are correctly and efficiently represented in a database design. 4NF is the next level of normalization after Boyce-Codd normal form (BCNF).
The definition of 4NF relies on the notion of a multivalued dependency. A table with a multivalued dependency is one where the existence of more than one independent many-to-many relationships in a table causes redundancy; and it is this redundancy which is removed by fourth normal form.
Consider the following example:
| Restaurant | Pizza Variety | Delivery Area |
|---|---|---|
| Vincenzo's Pizza | Thick Crust | Springfield |
| Vincenzo's Pizza | Thick Crust | Shelbyville |
| Vincenzo's Pizza | Thin Crust | Springfield |
| Vincenzo's Pizza | Thin Crust | Shelbyville |
| Elite Pizza | Thin Crust | Capital City |
| Elite Pizza | Stuffed Crust | Capital City |
| A1 Pizza | Thick Crust | Springfield |
| A1 Pizza | Thick Crust | Shelbyville |
| A1 Pizza | Thick Crust | Capital City |
| A1 Pizza | Stuffed Crust | Springfield |
| A1 Pizza | Stuffed Crust | Shelbyville |
| A1 Pizza | Stuffed Crust | Capital City |
Each row indicates that a given restaurant can deliver a given variety of pizza to a given area.
Notice that because the table has a unique key and no non-key attributes, it does not violate any normal form up to BCNF. But because the varieties of pizza a restaurant offers are independent from the areas to which the restaurant delivers, there is redundancy in the table: for example, we are told three times that A1 Pizza offers Stuffed Crust, and if A1 Pizza start producing Cheese Crust pizzas then we will need to add multiple records, one for each of A1 Pizza's delivery areas. In formal terms, this is described as Pizza Variety having a multivalued dependency on Restaurant.
To satisfy 4NF, we must place the facts about varieties offered into a different table from the facts about delivery areas:
| Restaurant | Pizza Variety |
|---|---|
| Vincenzo's Pizza | Thick Crust |
| Vincenzo's Pizza | Thin Crust |
| Elite Pizza | Thin Crust |
| Elite Pizza | Stuffed Crust |
| A1 Pizza | Thick Crust |
| A1 Pizza | Stuffed Crust |
| Restaurant | Delivery Area |
|---|---|
| Vincenzo's Pizza | Springfield |
| Vincenzo's Pizza | Shelbyville |
| Elite Pizza | Capital City |
| A1 Pizza | Springfield |
| A1 Pizza | Shelbyville |
| A1 Pizza | Capital City |
In contrast, if the pizza varieties offered by a restaurant sometimes did vary from one delivery area to another, the original three-column table would satisfy 4NF.
Ronald Fagin demonstrated that it is always possible to achieve 4NF (but not always desirable). Rissanen's theorem is also applicable on multivalued dependencies.
[edit] References
- Rules Of Data Normalization
- Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
- Kent, W. (1983) A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM, vol. 26, pp. 120-125
- Date, C.J., & Darwen, H., & Pascal, F. Database Debunkings
- Advanced Normalization by ITS, University of Texas.
- Free PDF poster available by Marc Rettig
| Topics in Database normalization |
|
First normal form | Second normal form | Third normal form |
