Consider the following example: 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. The primary key is the composite of all three columns. Also note that the table is in
4NF, since there are no
multivalued dependencies (
2-part join dependencies) in the table: no column (which by itself is not a candidate key or a superkey) is a determinant for the other two columns. In the absence of any rules restricting the valid possible combinations of traveling salespeople, 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 salesperson has certain brands and certain product types in their repertoire. If brand B1 and brand B2 are in their repertoire, and product type P is in their repertoire, then (assuming brand B1 and brand B2 both make product type P), the traveling salesperson must offer product type P from both brands; that is, the salesperson cannot sell only B1's product P or only B2's product P.'' In that case, it is possible to split the table into three: 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 Only in rare situations does a 4NF table not conform to 5NF; for instance, when the decomposed tables are
cyclic. 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. A table T is in fifth normal form (5NF) or projection-join normal form (PJ/NF) if it cannot have a
lossless decomposition into any number of smaller tables. The case where all the smaller tables after the decomposition have the same candidate key as the table T is excluded. ==See also==