Multiplicity is the number of objects that can participate in a role of a relationship. That is, multiplicity constrains the range of the number of objects that relate to other objects through a particular role. In Chen's original approach [Chen 1976], you put variable letters or constant integers on the roles to indicate their multiplicity. The "plays a role in" Chen relationship, for example, in Figure 6-9, shows an "M" on one side and an "N" on the other. This is a many-to-many relationship: a person may play a role in many (M) criminal organizations, and a criminal organization may have several people (N) playing a role in the organization. You could replace these with a number to indicate a specific number of items; the usual constant is 1, meaning the role is a one rather than a many role.
The crow's-foot notation uses its eponymous line adornments to show to-many roles. Figure 6-9
shows the crow's feet on both sides of the relationship, meaning that it's a many-to-many relationship. There are variations on the symbols that let you show that a role may have no value for a given object (multiplicity 0) or must have exactly one (multiplicity 1).
IDEF1X shows multiplicity with specific adornments on the relationship roles, at least in its incarnation in the ERwin modeling tool [Logic Works 1996], now a product of Platinum Technology. A "P"
indicates a to-one-or-more constraint, a "Z" indicates a to-zero-or-one constraint, and an integer indicates an exact-number constraint. However, although there is a notation for many-to-many relationships, IDEF1X models usually create an associative entity for the relationship, then put one-to- many or one-to-one relationships from the entities to the associative entity. Figure 6-10 illustrates both
formats for the "plays a role in" relationship. In the first, the black dots at both ends of the relationship indicate its many-to-many character. In the second, the relationship becomes an associative entity with two relationships, one to Person and one to CriminalOrganization.
The relationship between Person and Identification is a one-to-many relationship named "is identified by," shown in Figure 6-11. A person is identified by some number of identification documents, while an identification document identifies a single person. In this case, the Person role has multiplicity 1, and the Identification role has multiplicity "many," making it a one-to-many relationship.
You can also have one-to-one relationships, many of which are really subtype relationships, others of which represent objectified candidate keys. Figure 6-8 shows the subtype relationship in IDEF1X notation. A birth certificate is a subtype of an identification document, as is a passport or a driver's license or an identity card. All of these entities are separate and linked by a relationship. In Chen's notation, which does not provide generalization or subtyping notation, this is a simple, one-to-one relationship. One passport corresponds to one identification document. IDEF1X and crow's-foot notations show the generalization relationship directly without indicating the one-to-one nature of it, but the methodology implies that from the way generalization and subtyping works.
Figure 6-9: The Multiplicity of the "plays a role in" Relationship in Two Notations
Figure 6-10: The Multiplicity of the "plays a role in" Relationship in IDEF1X
Other one-to-one relationships relate two objects that are both unique in their own collections. That is, one entity has a unique attribute that refers to another entity's unique key (a set of attributes that uniquely identifies an object, see the following section on "Keys and Relationships" for details). You can identify both entities if you know the value of the attribute. This situation is relatively rare. An
example: every person has a unique set of fingerprints and a unique DNA profile. The relationship between the Person entity and the FingerprintRecord entity is thus one-to-one, as is the DNAProfile relationship to Person. These relationships usually show up as simple connections with 1 for both role multiplicities.
Note
If you are using ER modeling and encounter a one-to-one relationship, analyze it carefully to see whether the data model should actually express this as a subtype. If not, consider whether one entity can be usefully collapsed into an attribute or attributes of the other entity. You could, for example, have a FingerprintProfile as an attribute of the Person rather than as a one-to-one relationship if the profile was just a single value (a picture, say). On the other hand, you may want to break up a single table into two or more tables related to each other one-to-one as a physical design technique to minimize retrieval time, separating data that you use all the time and data you use relatively infrequently. This is not really a logical design issue, however.
Figure 6-11: The Multiplicity of the "is identified by" Relationship in Various Notations
Multiplicity has many names. The term "multiplicity" is the best one I've found; it comes from the UML and originated in OMT [Rumbaugh et al. 1992]. Various design methods name this with other terms:
Cardinality [Teorey 1999; Logic Works 1996]
Cardinality Ratio [Fleming and von Halle 1989]
Connectivity [Teorey, Yang, and Fry 1986]
Designation [Date 1986, pp. 437—438]
Frequency Occurrence [Halpin 1995]
Functionality [Ullman 1988]
Note
In the ER world, you usually use the term "cardinality," which comes by analogy from set theory in mathematics, where it refers to the size of a set. Given that this is really a constraint on the range of such sizes, not the size itself, I prefer to use the term "multiplicity," which has no unfortunate conflicts in meaning. It's also the term that the UML (and its precursor, OMT) uses to describe this design specification.
Relationships and their multiplicities connect the different entities in the schema design. The constraints they create imply certain things about the structure of the entities: primary and foreign keys.