Home > Articles > Data

  • Print
  • + Share This
From the author of

Entity-Relationship Notations

The Entity-Relationship (ER) approach [5] is the most popular approach to modeling databases. There are many ER dialects, none of which is dominant, so the term ER actually refers to a family of notations. The purpose of ER models is to develop an understanding of requirements so that they can be expressed with a database in a complete and efficient manner. Commercial tools are readily available for constructing ER models and generating a corresponding database structure.

ER models are succinct and cause you to think deeply before getting encumbered with implementation details. ER models are straightforward to understand, yet powerful enough to model real problems. One of the most popular dialects is the Unified Modeling Language (UML).

Unified Modeling Language Dialect

Figure 1 summarizes the UML notation for structure [3] [7].

Figure 1 Summary of the UML notation for structure.

Figure 2 shows a sample UML model. The model concerns frequent flyer account data for airlines, and is admittedly incomplete. The model would support business processes such as posting customer credits and generating monthly statements.

Figure 2 An ER model using the UML notation. ER models provide a powerful means for thinking about the essence of a database.

The model reflects requirements. Airlines offer frequent flyer accounts. A customer can have multiple frequent flyer accounts. Each frequent flyer account is for one airline and one customer. Multiple activities can be posted to a frequent flyer account, such as airline flight activities and other activities (credit card purchases, car rentals, and hotel stays). The activities are eventually reported to the customer in a monthly statement. There are many such statements for a frequent flyer account.

Airlines can belong to many partnerships; each partnership can have multiple airlines. For example, the Star Alliance has member airlines United Airlines, Lufthansa, SAS, and others. United participates in additional partnerships, such as one with TransWorld Express. The model records the dates an airline starts and ends a partner membership.

Each box in Figure 2 with a bold font name at the top denotes an entity type. There are ten entity types, including FrequentFlyerAccount, Customer, and Activity. An entity is a concept, abstraction, or thing that can be individually identified and is relevant to an application. An entity type is a description of a group of entities with similar properties, meaning, and relationships to other entities. Models intrinsically involve entity types because models describe the possible occurrences. A model is ultimately implemented as database structures, which are then populated to hold the actual entities.

The diagram has 20 attributes, which are shown in the lower portion of the boxes. Attributes for the entity type Customer, for example, are customerName and addressString. An attribute describes values that can be stored in the eventual database. Thus, the database can store a name and an address for each customer.

The diagram has nine relationship types, each of which is indicated by a series of one or more lines between entity types. (The lines connected to the triangle are another construct to be discussed shortly.) Relationship types can be given names (written next to one of the lines) but they are not required for the UML. The line between FrequentFlyerAccount and Customer is one of the relationship types. A relationship is a physical or conceptual connection between entities. A relationship type is a description of a group of relationships with similar properties and meaning. Relationship types are the "glue" that connects the entity types in a model. The precise choice of entity types and relationship types to include depends on the application and the modeler's judgment.

Attributes memberStartDate and memberEndDate describe the relationship type between Airline and AirlinePartnership. These attributes clearly belong to the relationship type; not to the related entity types. They are connected to the relationship type with a dotted line. The airlines in a partnership may start and end their membership on different dates. All other attributes in Figure 2 describe entity types.

Multiplicity can be indicated at each end of a relationship type—the "*," "1," and "0..1" annotations in the diagram. Multiplicity is the number of occurrences of an entity type that may connect to a single occurrence of a related entity type. In the diagram, the relationship type between Customer and FrequentFlyerAccount has one-to-many multiplicity. That is, each frequent flyer account pertains to one customer, but a customer may have many (zero or more) frequent flyer accounts. The "*" is UML notation for "many." The "0..1" illustrates at-most-one multiplicity. The "0..1" to the right of the MonthlyStatement box means that an activity appears in at-most-one monthly statement, but may not be in a monthly statement if it has not been reported yet. In the literature, multiplicity is sometimes called cardinality or connectivity.

Generalization, shown with a triangle, is the final concept in the diagram. Generalization organizes entity types by their similarities and differences. The triangle under the Activity box means that an activity can be a flight activity or some other activity. The attributes and relationships for the Activity entity type pertain to all activities. In contrast, only a FlightActivity may have a description, service class, and a relationship to a flight. Only an OtherActivity has a relationship to a company and an otherActivityType attribute to indicate whether the occurrence is a credit card purchase, car rental, or hotel stay. The apex of the triangle connects to the entity type with the general information. Lines connect the base of the triangle to the other entity types with specific information.

  • + Share This
  • 🔖 Save To Your Account