Logical Design - 2
Map EER $\rightarrow$ Relations
This mapping extends on ER to relational mapping with a step 8.
This converts each specialisation with $m$ subclasses $S_1,S_2,\ldots,S_m$ of a generalised superclass $C$ into relation schemas. There are several options available depending on the nature of the specialisation.
Step 8a - Multiple Relations for Superclass and Subclasses
This is the standard method that works for any specialisation $S_1,S_2,\ldots,S_m$ of a class $C$ (total, partial, disjoint or overlapping).
You create a relation for each subclass $S_1$ and add the primary key fo $C$ to each. This is equivalent to representing classes via weak entities (is-a relation).
This is like flattening down an EER diagram to an ER diagram.
Step 8b - Multiple Relations for Subclasses and Relations Only
If the following constraints are met we can use this method:
- The classification is total.
- AND
- The classification is disjoint.
If we know this then we can avoid creating a relation for the superclass altogether, but instead add all of the superclass attributes to each subclass relation.
As the two sets are disjoint we can split the two sets into two unrelated tables.
Step 8c - Single Relation with One Type Attribute
If the following constraints are met we can use this method:
- The classification is disjoint.
- AND
- The classification is specified by an attribute.
If these are true then we can avoid creating relations for the subclasses and add all attributes to the superclass relation.
Many null values can be created. This could be okay as we know when to expect them.
Either field, Salary or Payscale, will be filled. If one has a null value then the other one will be filled. This is not an issue as we know why there is a null value in there.
Step 8d - Single Relation with Multiple Type Attributes
If the following constrains are met we can use this method:
- The classification is overlapping.
- OR
- There are many different classifications.
- OR
- The classification is specified by a more complex predicate.
Then only create the superclass relation, with all the attributes of the subclasses, and add “flags” (boolean attributes), one for each subclass.
This method creates a lot of new attributes in the form of the flags. By doing this the flags describe why fields are null.
This will turn the following lattice:
into the following set of linked tables:
