Month Year Issue

August 2000 Issue: 15

Journal of Conceptual Modeling
www.inconcept.com/jcm

Entity Relationship Modeling from an ORM perspective: Part 5
By Dr. Terry Halpin

Correction

I'm grateful to Gascon Mascarenas and John Kuzmic for pointing out an error I made in Part 4, concerning the direction in which relationship names should be read in IDEF1X. In covering forward and inverse predicate readings for relationships, I said that a slash "/" is appended to the forward reading, which is read from left to right (or top to bottom if the relationship line is vertical). In actuality, this is true only if the relationship is "non-specific" (a many:many association-allowed only at the ER view level). For specific connection relationships this rule does not apply.

Instead, the forward reading of a specific connection relationship is always towards the child, and hence is directed towards the dot "●". This forward or "parent-perspective" reading must always be supplied. The inverse or "child-perspective" reading, if supplied, is directed towards the parent (away from the dot). If both forward and inverse readings are supplied, a slash "/" is appended to the forward reading. In the light of this rule, the predicate readings in Figures 4(a) and 5(a) from my previous article (Part 4 of this series) should be transposed.

Figure 1 shows some correct ways of displaying the relationship. In each case, the forward reading "Building contains Room" is read right-to-left because the dot is on the left.

Figure 1 The forward reading of a specific relationship is towards the dot

Non-identifying relationships

A non-identifying relationship is shown as a named, dashed line. Child and parent are independent unless made dependent by some other relationship. If the dashed line is unadorned, each child (instance) is associated with exactly one parent (instance), as in Figure 2(a). If a diamond is added at the parent end, each child is associated with at most one parent, as in Figure 2(b). The corresponding ORM diagrams are also shown.

Figure 2 Non-identifying connection relationships

In a non-identifying relationship, the child non-key attributes (those not in the primary key) include a foreign key that references the parent's primary key. If the foreign key is mandatory (each component is non-null) then each child instance is associated with exactly one parent instance. This is referred to as a mandatory relationship, since the child role of the conceptual relationship that (before migration) gave rise to the foreign key reference is mandatory. Figure 3(a) depicts this situation in more detail. Here a and b are simple or composite attributes. If the foreign key is optional (at least one each component is nullable) then each child instance is associated with at most one parent instance. Figure 3(b) depicts this situation. In IDEF1X this is referred to as an optional relationship.

In both the mandatory and optional cases, each instance of the child's foreign key that has no null components must occur as an instance of the parent's primary key. This is the default meaning of a foreign key constraint in SQL. For the mandatory case of course, there can be no null components.

Figure 3 "Mandatory" (a) and "optional" (b) non-identifying connection relationships

An example of a mandatory, non-identifying connection relationship is shown in Figure 4(a). Here each employee is identified by an employee number, must have a name and must work for a department. Each department is identified by its code and must have a unique name. The fact (conceptual relationship) that a given employee works for a given department is stored by instantiating the empNr and deptCode attributes of Employee (e.g. empNr = 101, deptCode = 'HR'). From a logical design viewpoint, the connection relationship is simply a foreign key reference indicating that each department code of an employee must be a department code of a department.

The same example is shown in IE notation in Figure 4(b) and in ORM notation in Figure 4(d). Here the association Employee works for Department is depicted conceptually, rather than by including deptCode as an attribute of Employee. This would also be the case for the Barker notation. Figure 4(c) depicts the situation in a relational notation. Here facts about who works for what department are stored in the Employee table, which includes a deptCode attribute referencing the Department table. If you compare this with the IDEF1X notation in Figure 4(a) it should be clear that they are just two notational variations of the same structure. In key-based and fully-attributed views, the IDEF1X notation may be regarded simply as a logical design notation. From a migration viewpoint, however you may treat it as conceptual.

Figure 4 Same example in (a) IDEF1X, (b) IE, (c) Relational and (d) ORM notation

As a further example of non-identifying relationships, Figure 5(a) models a room scheduling application is modeled in IDEF1X notation. An ORM model for this situation is shown in Figure 5(b). The primary key and alternate key constraints in the IDEF1X model correspond to the uniqueness constraints in the ORM model. Although the IDEF1X schema does capture all of the constraints, it does not allow the natural ternary formulation for the booking fact type. Nor does it facilitate validation by verbalization and population in the way that ORM does.

Figure 5 A room schedule example in (a) IDEF1X and (b) ORM

Resolving non-specific relationships

In an ER view or "entity level diagram" all entity types are depicted by hard rectangles, and non-specific (m:n) relationships are permitted. Here each non-specific relationship line must end in a dot, possibly adorned by a cardinality mark. If unadorned, a cardinality of exactly one is assumed. In moving to a key-based or fully attributed view, view, each line m:n relationship must be resolved into an "intersection entity type" with two n:1 relationships. Figure 6 shows an example, and the equivalent transformation in ORM.

Figure 6 In IDEF1X, m:n relationships must be resolved into n:1 relationships

An intersection entity type is sometimes called an "associative entity type". Although conceptually this corresponds to a co-referenced object type in ORM, in IDEF1X the identifying attributes must be "migrated" to become foreign keys inside the primary key of the intersection entity type, which therefore must be identifier-dependent, appearing as a soft rectangle. This foreign key depiction effectively lowers the representation to the relational level. Although this resolution is often unnatural, it does allow attributes to be added to what was once a relationship. For example, we might wish to record a skill level for Play. In ORM and UML, this objective can also be achieved by simply permitting a relationship to be objectified (as an objectified association or association class), but this conceptual alternative is not allowed in IDEF1X.

If the m:n relationship is a ring association, role names may be prepended to the names of the migrated foreign keys to distinguish them. For example, in Figure 7(a) the role names "subPartNr" and "superPartNr" are prepended to "partNr" to disambiguate the primary key attributes of Containment. Although the IDEF1X standard uses a dot after the role name, this dot notation is not supported in some CASE tools. However the distinction can be easily achieved without the dot by using just a role name (e.g. "subPartNr" can be used instead of "subPartNr.partNr"). In either case, the role name should be shown as a noun phrase that identifies the attribute even without appending the name of the referenced primary key. In ORM, a more natural way to model this situation is to objectify the containment association, as shown in Figure 7(b).

Figure 7 In IDEF1X (a) role names may be used if needed to distinguish attributes

Subtyping

In IDEF1X, an entity type may be classified into one or more clusters of mutually exclusive categories (subtypes). The supertype is called the generic entity (type). Each subtype inherits the primary key of its supertype, and hence is identifier-dependent. The subtype linkage is called a "categorization relationship". This version of subtyping is very restricted compared with ORM subtyping, which allows overlapping types within a "cluster" as well as context dependent reference. In effect, the restricted subtyping approach of IDEF1X treats entity types as tables, and the "categorization relationships" are simply foreign key references. In sharp contrast, ORM subtyping is purely conceptual, and different mapping strategies may be chosen at implementation time (as discussed in a later chapter).

However there are two aspects of the subtyping approach in IDEF1X that express more than simple foreign key references. These are the completeness and discriminator declarations. A subtype link is shown as a line with an underlined circle at the supertype end. A single underline means the cluster is incomplete (i.e. the supertype is more than the union of its subtypes). This means that the population of the supertype may contain instances not present in any of its subtypes. For example, in Figure 8(a), the subtypes TallPerson and ShortPerson are mutually exclusive, but not collectively exhaustive. It is possible for a person to be of medium height without being classified as either a tall or short.

In contrast to IDEF1X, ORM requires that subtypes must be defined in terms of roles played by their supertype(s). Figure 8(b) shows one way of modeling this case in ORM. Here a person's height determines whether he/she is a member of TallPerson or ShortPerson. The subtype definitions and the lack of a value constraint on Height indicate that these subtypes are exclusive but not exhaustive. This can also be depicted explicitly by an implied exclusion constraint as shown. As an alternative to using height, you could attach the optional, exclusive unaries "is tall" and "is short" to Person and supply the obvious definitions.

Figure 8 An incomplete and exclusive subtype cluster in (a) IDEF1X and (b) ORM

In IDEF1X, a double underline at the supertype end means the cluster is complete. This means the supertype is the union of its subtypes. Since members of an IDEF1X cluster are also exclusive, this means we have a partition. In Figure 9(a) for example, Person is partitioned into MalePerson and FemalePerson.

Figure 9 A complete, exclusive subtype cluster in (a) IDEF1X and (b) ORM

A mandatory attribute of a supertype may be used as a discriminator, and written beside the circle, to indicate the basis for the subtyping. In Figure 9(a) for example, sex is used as a discriminator to classify Person into MalePerson and FemalePerson. The corresponding ORM schema is shown in Figure 9(b). The partition constraint shown explicitly here is implied by the subtype definitions and other constraints.

Well that pretty much covers the IDEF1X notation. Although it is a widely used standard, especially in the defense industry, I find it less suitable than the Barker or IE notations as an ER approach to conceptual modeling. To capture any detail or even basic constraints in IDEF1X, you have to resolve the model into what is essentially a relational model. Moreover the IDEF1X set of concepts and its notation are unnecessarily complex and forgettable. For conceptual analysis and validation, it also suffers from the weaknesses of an attribute-based approach. In spite of such drawbacks, IDEF1X can be used effectively by experienced modelers, who often associate connection relationships with the conceptual relationships from which they are migrated. Even for such experienced modelers however, the best way to model in IDEF1X is to first do an ORM model, then map it to IDEF1X, expressing any additional ORM constraints as supplementary text or implementation code. An ORM modeling tool is capable of performing this mapping automatically.

References

Bruce, T. 1992, Designing quality databases: practical information management and IDEF1X, Dorset House, New York.
IEEE 1999, IEEE standard for conceptual modeling language syntax and semantics for IDEF1X97 (IDEFobject), IEEE Std 1320.2-1998, IEEE, New York.
Menzel, C. & Mayer, R.J. 1998, 'The IDEF family of languages', Handbook on Architectures of Information Systems, eds. P. Bernus, K. Mertins & G. Schmidt, Springer-Verlag, Berlin, Germany, pp. 209-41.
NIST 1993, Integration definition for information modeling (IDEF1X), FIPS Publication 184, National Institute of Standards and Technology, www.sdct.itl.nist.gov/~ftp/idef1x.trf.

Dr Terry Halpin, BSc, DipEd, BA, MLitStud, PhD, is a Program Manager in Database Modeling for the Enterprise Frameworks and Tools Unit, Microsoft Corporation, USA., Seattle WA, USA. During a lengthy career as an academic in computer science, he also worked in industry on database modeling technology and as a data modeling consultant. His recent positions include head of database research at Asymetrix Corporation, and research director of InfoModelers Inc., which was acquired by Visio Corporation. For several years, his research has focused on conceptual modeling and conceptual query technology for information systems, using a business rules approach. Dr Halpin has presented papers and tutorials at many international conferences. His doctoral thesis provided the first full formalization of Object-Role Modeling (ORM/NIAM), and his publications include over ninety technical papers, as well as four books, including Information Modeling and Relational Databases (Morgan Kaufmann, 2001).

Contact Information:

Dr Terry Halpin
Program Manager, Database Modeling
Enterprise Framework & Tools Unit, Microsoft Corporation
One Microsoft Way
Redmond WA 98052-6399 (USA)
terryha@microsoft.com
(425) 705 9190
fax: (425) 936 7329
http://www.orm.net