Reification

Now that we have a complete abstract account of the semantics of a entity-relationship schema, it is time to reify it: this process maps our abstract multirelation-theoretic semantics to actual tables of a relational database.

ERW handles the reification process for you; in fact, it has to handle it, as the run-time environment will have to query and update an actual database, and there are several strategies to reify an entity-relationship schema. ERW follows a standard general-purpose algorithm that should work reasonably in all circumstances.

Reification of entity types is fairly obvious: each entity type is represented by an SQL table whose columns correspond to the attributes of the type (there is some other work to do to support ERW extended types, but we have not to concern ourselves with such details in this section), and whose rows represent entities. ERW adds an integer id primary key column to all tables, so to mimic the set-theoretic semantics we just discussed. Additionally, to support user/group based authorisation two additional columns id_owner_usr and id_share_grp might be added.

Even if the primary key is handled by ERW, you still have the possibility of defining set of columns that should not be duplicated using the key attribute of the attr element of ERL. The set of columns thus identified is declared UNIQUE. If you do not declare keys, you get a truly set-theoretical semantics: each entity is a distinguished object on its own, even if it has exactly the same attribute values of another entity (the same happens for relationships).

Reification of relationship types is more sophisticated: the obvious way is using a support table whose rows represent relationships (i.e., the elements of the multirelation) and the images of such elements under the two legs. In our library example, ERtool produces a table looking as follows:

CREATE TABLE loan (
   id int NOT NULL PRIMARY KEY,
   id0_person int NOT NULL,
   id1_book int NOT NULL,
   startdate date NOT NULL DEFAULT CURRENT_DATE,
   enddate date,
   duration char(1) NOT NULL
);

Again, the additional primary key id let us distinguish every relationship (even in multirelations), whereas id0_person and id1_book are the images of a relationship through the two legs. The remaining columns are attributes.

If, however, the cardinality constraints imposed force the multirelation instantiating a relationship type (or its transpose) to be monodrome (or even a function), there is a more efficient method: a column named id_relationship-type_other-entity-type is added to the table reifying an entity type. In this case, we say that the SQL table reifying the entity type absorbed the SQL table reifying the relationship type, or, informally, that the entity type absorbed the relationship type. Thus, for instance, if we modified our example and set upper="1" in the definition of the book leg (i.e., each book is on loan to at most one person), and moreover we eliminated all attributes from the relationship, instead of a support table we would have produced a new column id_loan_person in the table reifying the entity book. That is, book would have absorbed loan.

When ERtool reifies an entity-relationship schema, it first tries to absorb all relationship types without attributes into their sources (if it is possible); then it checks whether it would be possible to absorb the remaining relationship types without attributes into their targets, and complains if this is true, as by design absorption is possible only in the source entity types; finally, it creates a support table for all remaining types. There is some control on the first part of the process, as you can explicitly ask for a relationship type not to be absorbed even if it is possible.

Subtypes are realised by keeping in sync the id fields of the tables reifying the types involved. Thus, for instance, if A is a B then for each value of the id field in the table reifying A there is a corresponding row of B whose id field has the same value. This behaviour mimics exactly set-theoretical inclusion.

The reification process is more complicated, as, for instance, part of the reification of a dynamic enumerative attribute consist in creating an ad hoc table for storing the enumeration. The details of the reification process are given for each ERL element in detail in the Chapter called ERL: an Entity-Relationship Language. In any case, ERtool generates detailed documentation about the reification process.