Fifth normal form

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

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 (<Jack Schneider, Robusto, Breadboxes>, <Jack Schneider, Robusto, Vacuum Cleaners>). With the new setup we need to add only a single entry (<Jack Schneider, Robusto>) in Brands By Traveling Salesman.

• Attribute-value system