E-R Diagram Cardinality and Participation
- How to show cardinality/participation for several methodologies.
- Examples of notation and diagrams.
In my previous posts, I discussed some E-R diagram basics and some common mistakes to avoid. In this post, I’ll cover cardinality and participation. Cardinality is a count of the number of times one entity can (or must) be associated with each occurrence of another entity. Participation refers to whether an entity must participate in a relationship with another entity to exist.
Cardinality and Participation Constraints in E-R Diagrams
In general, cardinality tells you “How many”. Cardinality can be:
- One to one (1:1): every time one entity occurs, there is exactly one occurrence of another entity.
- One to many (1:m): every time one entity occurs, there are multiple occurrences of another entity.
- Many to many (m:m): for each occurrence of an entity, there can be one or many occurrences of another—an vice versa.
In some notations, a cardinality constraint corresponds to maximum cardinality. In other notations, cardinality may be combined with participation (a “minimum”).
Participation can be total or partial (optional):
- Total participation is where an entity must participate in a relationship to exist. For example, an employee must work for at least one department to exist as an “employee”.
- Partial (optional) participation is where the entity can exist without participating in a relationship with another entity . For example, the entity “course” may exist within an organization, even though it has no current students.
How to Show Cardinality and Participationon an E-R Diagram
While there are many ways to create E-R diagrams, a straightforward way is to create a rough draft of your ERD first, then tackle cardinality. While most methodologies (e.g., Chen, I.E, min/max) use the same shapes for entities (rectangles) and relationships (diamonds), when it comes to cardinality each has its own specific notation. This is where you must make a choice about what methodology to use, as you must stay consistent when building cardinality constraints.
One of the most important choices is between Look-Here and Look-Across methods. Look-Here and Look-Across refers to where the cardinality and participation constraints are specified in ER diagrams.
In the above example using the min/max method, the upper bound cardinality constraint 1 states that each employee may have the relationship “work for” at most 1 time, meaning they can only work for one department. The lower bound cardinality constraint 1 states that each employee must have at least 1 appearance in the “works for” relationship. On the other hand, there are no restrictions on how many employees a department may have; the upper bound N indicated no limit .
With the “Look-Across” constraint, you must look to the other side of the relationship to garner meaning:
Different Methodologies and Ways to Show Cardinality
The following examples all show how different methodologies show cardinality and participation:
Crowsfeet is one of the most popular methods for creating E-R diagrams. With crowsfeet notation, cardinality is represented by decorations on the ends of lines.
A cardinality of one is represented by a straight line perpendicular to the relationship line.
A cardinality of many is usually represented by the three-pronged ‘crow-foot’ symbol, but may also be represented by a two-pronged symbol :
Other basic symbols:
───┼< one or many
──o─< zero or many
───┼┼ exactly one.
The IE method is very similar to crowsfeet but does not show attributes related to a relationship: the relationship is depicted as a named line between two entities. Cardinality and participation constraints are combined into min/max (bar and crowfoot) notation. Cardinality is shown as follows :
- One and only one: Two bars at end of line or single bar
- Zero or one: Hollow dot and one bar
- One or more: One bar and crowfoot
- Zero, one or more: Hollow dot and crowfoot
- More than one: Crowfoot.
Your choice of which methodology to use is probably going to be determined by your company’s preference (or perhaps your instructor’s). Unfortunately, differences between notations make the diagrams challenging to transfer from one format to another .
Images: By Author