8.5. Mapping from ORM to ER

Fully attributed IDEFIX schemas are essentially relational schemas displayed in a different notation. For this reason, Microsoft’s ORM Source Model solution treats IDEFIX as simply a different relational style. If you are using that tool, you can generate IDEFIX schemas from ORM schemas by automatically generating the relational schema, setting the drawing style to IDEFIX, and then noting any extra ORM rules as textual annotations.

If instead you need to manually generate IDEF1X schemas from ORM, follow the relational mapping (Rmap) procedure discussed in Chapter 11 to obtain a relational schema and then redraw it in IDEF1X.

Mapping from ORM to a true ER depends on the ER dialect (e.g., does it support Alary associations, multivalued attributes, any form of objectification, or derived attributes/associations?). Industrial ER dialects typically support none of these options. The ERmap procedure in Table 8.2 provides basic guidelines for mapping ORM to Barker ER or IE.

We now illustrate the steps in this procedure with examples using the Barker ER notation. Step 1.1 replaces objectified associations by coreferenced object types. For example, if the fact type Person wrote Paper is objectified as Writing, replace this by Writing is by Person and Writing is of Paper, as shown in Figure 8.43.

Step 1.2 binarizes n-ary predicates by co-referencing. For example, the ternary Room at HourSlot is booked for Activity is replaced by forming a coreferenced entity type Room-Booking, as shown in Figure 8.44.

Step 1.3 binarizes any sets of exclusive unaries. For example, the Employee is male and Employee is female fact types are replaced by the single binary Employee is of Gender, with relevant constraint transforms, as shown in Figure 8.45.

In step 2, we decide which object types to model as entity types and which n: and 1:1 ORM associations to remodel as attributes. Typically, entity types that play functional fact roles are retained as entity types. In rare cases, value types that are independent or play explicit mandatory, functional fact roles map to entity types. Functional binary (n:1 and 1:1) associations from an entity type A to a value type B, or to an entity type B about which you never want to record details, usually map to an attribute of A. If you have specified role names, these can usually be used as attribute names, with the object type name becoming the attribute’s domain name.

The mapping in Figure 8.46 illustrates several of these step 2 considerations, as well as step 5 (map ORM constraints to ER graphic constraints, textual constraints, or notes).

The “{M, F}” annotation is a textual note to capture the value constraint on gender codes. The “{Ul}” annotation is a textual note to indicate a uniqueness constraint on the ssn attribute. Neither of these textual annotations is part of the Barker ER notation.

In step 3 we map unaries to Boolean attributes or to subtypes. The example in Figure 8.47 assumes a closed world interpretation for the unary. If this is not the case, the is-smoker attribute is made optional and an UnknownSmoking subtype is added. Open/closed world aspects are discussed in more detail in Chapter 10.

In step 4, the remaining fact types are mapped to associations. Any m:n associations should remain that way, unless the target notation doesn’t support them (e.g., IDEF1X). In that case, you can replace the association by an intersection entity type as discussed earlier.

In the example shown in Figure 8.48, the n: fact type is retained as an association because it relates two entity types that will remain as such in the mapping. Even if the m:n association did not apply, we would normally retain Country as an entity type, since now or later we are likely to record details for it (e.g., country name).

If an m:n association involves a value type, and the ER dialect does not support multivalued attributes, map the value type to an entity type. Figure 8.49 provides an example. Otherwise, where possible, transform the m:n association into multiple n: associations (e.g. Employee has PhoneNrl; Employee has PhoneNr2, etc.). Conceptual schema transformations like this are discussed in depth in Chapter 14.

In step 5, the simplest constraints in ORM usually map in an obvious way to multiplicity (mandatory and/or cardinality) constraints, as illustrated earlier. The more complex ORM constraints have no counterpart in ER, so you need to record these separately in textual form. Table 8.3 summarizes the main correspondences.

In step 6, subtypes are mapped to subtypes, adding relevant constraints if needed. Subtype definitions are handled with discriminators and/or textual constraints. If needed, introduce an “Other” subtype to complete a partition. If needed, use multiple diagrams (e.g., when multiple categorization schemes apply to the same supertype). Figure 8.50 shows a simple example. The braced value constraint and footnoted constraints are not part of the Barker ER notation.

In step 7, we map derived and semiderived fact types. Industrial ER typically has no graphical support for derivation, so the derivation rules are captured as textual notes. Figure 8.51 provides a simple example. With these few hints, and the examples discussed, you should now have enough background to do the mapping manually. The mapping procedure is fairly boring and is best automated with a CASE tool.

For mapping to IDEFIX, the relational mapping procedure discussed in Chapter 11 may be used essentially as is, treating the relationships as foreign key references and making the relevant notational variations.

Exercise 8.5