124 5NF

Informally, Fifth Normal Form, or 5NF, says that a relation should not store a fact that can be reconstructed without loss from smaller projections.

Example which violates 5NF

The following relation records which suppliers may supply which parts to which projects:

Assume the business rule is that a supply assignment is valid exactly when three binary facts all hold: the supplier can supply the part, the supplier is approved for the project, and the project uses the part. Under that rule, the ternary relation is determined by a join dependency rather than by an irreducible ternary fact:

SupplyAssignment = SupplierPart ⨝ SupplierProject ⨝ ProjectPart

Unlike the 2NF, 3NF, and BCNF examples, this is not a functional-dependency problem. The problem is that the whole ternary relation is determined by its binary projections.

The same modelling mistake can be written in Predica:

entity SupplyAssignment
{
    String supplier;
    String part;
    String project;

    key {supplier, part, project};
}

Here one entity type stores a ternary fact even though the intended rule is the conjunction of three binary facts.

Writing the dependency as a constraint

If the model is left in this shape, the join dependency has to be imposed over the containing collection of supply assignments. Since the binary facts are projections of the stored ternary assignments, the constraint says that whenever those projections support a ternary assignment, that assignment must be present:

entity SupplySystem
{
    SupplyAssignment assignment*;

    constraints
    {
        assignment.(supplier, part, project).Σ =
        (
            assignment.(supplier, part)
            ⊓ assignment.(supplier, project)
            ⊓ assignment.(project, part)
        ).Σ;
    }
}

This is awkward because the base representation stores the derived ternary relation and then has to be closed under the join of its binary projections. The equality says that the stored ternary relation is exactly the join of the three binary projections. The constraint is doing work that would disappear if the three binary facts were stored directly.

Fixing the violation

The fix is to store the three binary facts directly. In relational form, this decomposes the original relation into three relations:

In Predica, the same repair is to model the participating domains explicitly and put the attributes on the domains where they belong:

entity Supplier
{
    String name;
    Part part*;
    Project project*;

    key {name};
}

entity Project
{
    String name;
    Part part*;

    key {name};
}

entity Part
{
    String name;

    key {name};
}

entity SupplySystem
{
    Supplier supplier*;
    Project project*;
    Part part*;
}

The RM decomposition uses three binary relations. The Predica schema expresses the same information as unary predicates on domains: Supplier.part, Supplier.project, and Project.part. If the ternary view is needed, it can be derived from those attributes. The base schema does not need a separate SupplyAssignment entity unless the assignment itself has independent facts.

Part and Project are modelled as entity types here because the example treats them as domains in their own right. In particular, Project has its own part* attribute, so it is not merely an uninterpreted name.

SKNF

Superkey Normal Form, or SKNF, is a technical normal form between 4NF and 5NF. A relvar is in SKNF when every component of every irreducible join dependency it satisfies is a superkey. 5NF implies SKNF, and SKNF implies 4NF, but neither converse holds. Conditions equivalent to SKNF have sometimes been presented incorrectly as definitions of 5NF.

For Predica, SKNF can refine the analysis of an entity or relationship type whose extension represents an n-ary fact. Its practical question is still whether the fact is irreducible or should be represented by smaller predicate extensions. The existing 5NF analysis captures that modelling decision, so SKNF is noted here for theoretical completeness rather than treated as a separate Predica design stage.

ETNF and RFNF

Essential Tuple Normal Form, or ETNF, refines the connection between join dependencies and tuple redundancy. A relvar is in ETNF when every tuple in every permitted value of the relvar is essential, meaning that it is neither partly nor wholly redundant. ETNF is strictly weaker than 5NF, but is exactly as effective as 5NF at excluding redundant tuples. Thus 5NF can require a decomposition that is not required merely to eliminate tuple redundancy.

Redundancy Free Normal Form, or RFNF, considers redundancy at the finer level of an attribute value within a tuple. Informally, such a value is redundant when the constraints make it uniquely determined by the rest of the relation. RFNF requires that no permitted relation value contain such a redundant value occurrence.

Predica does not need independently declared relvars in order to apply these ideas. Attribute extensions and relation denotations such as assignment.(supplier, part, project).Σ can still be examined for redundant tuples and values. In the example above, the stored ternary extension is determined by the three smaller predicate extensions, so it should be derived rather than stored as an independent base fact. More generally, the Predica interpretation of ETNF and RFNF is to ask whether each stored predicate fact is essential, or whether it is already forced by the remaining database state and its constraints.