7 Identity

Example where the RM needs artificial surrogate keys

Predica’s structural identity eliminates the need for surrogate keys in situations where the RM is forced to introduce them.

Consider a box of objects that are uniquely identified by their shape and colors. There are no other identifying attributes. The following Predica schema represents the required information and models the constraints: 


variant Shape
{
    { Real radius } sphere;
    { Real side } cube;
    { Real radius, Real height } cone;
}
enum Color
{
    red, green, blue 
}
entity Object
{
    Shape shape;
    Color color+;
}
entity Box
{
    Object object*;
}

This can be mapped to Tutorial D as follows:

Predica Tutorial D 

variant Shape
{
    { Real radius } sphere;
    { Real side } cube;
    { Real radius, Real height } cone;
}
        

TYPE Sphere POSSREP { radius RATIONAL };
TYPE Cube   POSSREP { side   RATIONAL };
TYPE Cone   POSSREP { radius RATIONAL, height RATIONAL };

TYPE Shape UNION { Sphere, Cube, Cone };
        

enum Color
{
    red, green, blue
}
        

TYPE Color POSSREP { tag CHAR }
CONSTRAINT ColorConstraint
    tag IN { 'red', 'green', 'blue' };
        

entity Object
{
    Shape shape;
    Color color+;
}
        

TYPE ObjectId POSSREP { id INTEGER };

VAR Object :
    RELATION {
        ObjectId : ObjectId,
        Shape    : Shape
    }
KEY { ObjectId };

VAR ObjectColors :
    RELATION {
        ObjectId : ObjectId,
        Color   : Color
    }
KEY { ObjectId, Color };

-- Inclusion dependencies
CONSTRAINT ColorRefersToObject
    ObjectColors.ObjectId ⊆ Object.ObjectId;

CONSTRAINT EveryObjectHasColor
    Object.ObjectId ⊆ ObjectColors.ObjectId;
        

entity Box
{
    Object object*;
}
        

-- No two objects may have the same
-- shape and the same set of colors
CONSTRAINT NoDuplicateObjects
    IS_EMPTY (
        (Object JOIN Object AS O2)
        WHERE
            Object.ObjectId <> O2.ObjectId
        AND Object.Shape = O2.Shape
        AND
            (ObjectColors WHERE ObjectId = Object.ObjectId)
            =
            (ObjectColors WHERE ObjectId = O2.ObjectId)
    );

1NF forbids representing the multivalued color+ attribute as a value, so the RM must split it into a separate relvar. That split destroys the natural identity of an object, forcing the introduction of a surrogate key to maintain identity across relvars.