Fifth normal form (5NF), also known as project-join normal form (PJ/NF) is a level of database normalization designed to reduce redundancy in relational databases recording multi-valued facts by isolating semanticallyrelated multiple relationships. A table is said to be in the 5NF if and only if every non-trivial join dependency in it is implied by the candidate keys.
A join dependency *{A, B, … Z} on R is implied by the candidate key(s) of R if and only if each of A, B, …, Z is a superkey for R.[1]
Example[edit]
Consider the following example:
| Traveling Salesman | Brand | Product Type |
|---|---|---|
| Jack Schneider | Acme | Vacuum Cleaner |
| Jack Schneider | Acme | Breadbox |
| Willy Loman | Robusto | Pruning Shears |
| Willy Loman | Robusto | Vacuum Cleaner |
| Willy Loman | Robusto | Breadbox |
| Willy Loman | Robusto | Umbrella Stand |
| Louis Ferguson | Robusto | Vacuum Cleaner |
| Louis Ferguson | Robusto | Telescope |
| Louis Ferguson | Acme | Vacuum Cleaner |
| Louis Ferguson | Acme | Lava Lamp |
| Louis Ferguson | Nimbus | Tie Rack |
The table's predicate is: Products of the type designated by Product Type, made by the brand designated by Brand, are available from the traveling salesman designated by Traveling Salesman.
In the absence of any rules restricting the valid possible combinations of Traveling Salesman, Brand, and Product Type, the three-attribute table above is necessary in order to model the situation correctly.
Suppose, however, that the following rule applies: A Traveling Salesman has certain Brands and certain Product Types in his repertoire. If Brand B1 and Brand B2 are in his repertoire, and Product Type P is in his repertoire, then (assuming Brand B1 and Brand B2 both make Product Type P), the Traveling Salesman must offer products of Product Type P those made by Brand B1 and those made by Brand B2.
In that case, it is possible to split the table into three:
| Traveling Salesman | Product Type |
|---|---|
| Jack Schneider | Vacuum Cleaner |
| Jack Schneider | Breadbox |
| Willy Loman | Pruning Shears |
| Willy Loman | Vacuum Cleaner |
| Willy Loman | Breadbox |
| Willy Loman | Umbrella Stand |
| Louis Ferguson | Telescope |
| Louis Ferguson | Vacuum Cleaner |
| Louis Ferguson | Lava Lamp |
| Louis Ferguson | Tie Rack |
| Traveling Salesman | Brand |
|---|---|
| Jack Schneider | Acme |
| Willy Loman | Robusto |
| Louis Ferguson | Robusto |
| Louis Ferguson | Acme |
| Louis Ferguson | Nimbus |
| Brand | Product Type |
|---|---|
| Acme | Vacuum Cleaner |
| Acme | Breadbox |
| Acme | Lava Lamp |
| Robusto | Pruning Shears |
| Robusto | Vacuum Cleaner |
| Robusto | Breadbox |
| Robusto | Umbrella Stand |
| Robusto | Telescope |
| Nimbus | Tie Rack |
In this case, it's impossible for Louis Ferguson to refuse to offer Vacuum Cleaners made by ACME (assuming ACME makes Vacuum Cleaners) if he sells anything else made by Acme (Lava Lamp) and he also sells Vacuum Cleaners made by any other brand (Robusto).
Note how this setup helps to remove redundancy. Suppose that Jack Schneider starts selling Robusto's products Breadboxes and Vacuum Cleaners. In the previous setup we would have to add two new entries one for each product type (, ). With the new setup we need to add only a single entry ()in Brands By Traveling Salesman.
Usage[edit]
Only in rare situations does a 4NF table not conform to 5NF. These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table. If such a table is not normalized to 5NF, the burden of maintaining the logical consistency of the data within the table must be carried partly by the application responsible for insertions, deletions, and updates to it; and there is a heightened risk that the data within the table will become inconsistent. In contrast, the 5NF design excludes the possibility of such inconsistencies.
See also[edit]
References[edit]
Further reading[edit]
- 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
- Darwen, H.; Date, C. J.; Fagin, R. (2012). "A normal form for preventing redundant tuples in relational databases". Proceedings of the 15th International Conference on Database Theory - ICDT '12. pp. 114–126. doi:10.1145/2274576.2274589.ISBN 9781450307918.
| ||||||||||
No comments:
Post a Comment