8.6. Summary

The Entity Relationship (ER) modeling approach was originated by Peter Chen in the 1970s and allows facts to be expressed via relationships (e.g., Person was born in Country) or attributes (e.g., birthdate). Of the dozens of different ER versions in existence, the most widely used are the Barker notation and Information Engineering (IE). The popular IDEFIX approach is often referred to as a version of ER, but is actually a mixture of ER and relational approaches, with the emphasis on relational.

The Barker notation represents entity types as named, soft rectangles with a list of one or more attributes. A “*” or “•” indicates that an attribute is mandatory, and a “°” indicates that the attribute is optional. All relationships are binary, and are shown as named lines. A solid half-line denotes a mandatory role, and a dotted half-line denotes an optional role. A crow’s foot at the end of the line indicates the cardinality “many”, and its absence indicates “one”. A hash “#” in front of an attribute indicates it is, or is a part of, the primary identifier. A bar “I” across one end of a relationship indicates that the relationship is a component of the primary identifier for the entity type at that end.

An exclusive arc across roles indicates that the roles are mutually exclusive; if the role lines are solid, we have an exclusive-or constraint. Subtype partitions are denoted by Euler diagrams, placing the subtypes inside the supertype.

In the Information Engineering approach, entity types are displayed as named rectangles, with a list of attributes. Relationships are binary and are denoted by named lines. A crow’s foot at the end indicates “many”, a stroke “I” indicates “one”, and a circle “O” indicates optional. Two strokes “II” indicates “exactly one”. Some IE versions depict an exclusive-or constraint as a black dot joining relationship lines. Different subtype notations exist for IE, some using Euler diagrams and some “is a” relationship lines.

IDEFIX models may be viewed at three levels. In a high level ER view, m:n (or “nonspecific”) relationships may be shown directly, but these must be resolved into an intersection entity type with two n: relationships as the model is refined into key-based or fully attributed views. An entity type is identifier dependent if its primary key includes a foreign key and is shown as a named, soft rectangle. Otherwise it is identifier independent and is shown as a named, hard rectangle. Attributes are listed inside the rectangle, with the primary key in the top compartment. Alternate keys are denoted by appending “(AKn)” and foreign keys by appending “(FK)”. An attribute is mandatory unless followed by “(O)”.

Connection relationships are foreign key references from the child to the parent and are shown as a named line with a dot “•” at the child end. For a specific connection relationship, its forward name is always read toward the dot. If an inverse name is added, a slash “/” is appended to the forward reading. For nonspecific relationships, the forward reading is left to right (or top-to-bottom if the line is vertical).

A line end has a cardinality of 1 unless annotated. A dot indicates “0 or more” but can be strengthened by adding “P” (1 or more), “Z” (0 or 1), or “n” (exactly n).

A foreign key reference starting from a primary key attribute is an identifying relationship and is shown as a solid line. A foreign key reference starting from a nonkey attribute is a nonidentifying relationship, and is shown as a dashed line; in this case, a diamond at the parent end indicates that each child is associated with at most one parent.

An entity type may be classified into one or more clusters of mutually exclusive categories. Subtype links are depicted as categorization relationships with a circle at the supertype end. The cluster is incomplete or complete according as to whether the circle has a single underline or double underline, respectively.

ER or IDEF1X models are best developed by mapping them from ORM models, and noting any additional ORM constraints as comments. The main guidelines for mapping from ORM to ER are summarized in Table 8.2.