Cardinality and Participation in Relationships

Welcome to the next lesson in our database design course. In our previous session, you learned to identify the three main types of relationships—one-to-one, one-to-many, and many-to-many—by looking at the maximum number of connections between entities.

This lesson adds a crucial layer of precision to that skill. We will now focus on defining the minimum number of connections, a concept called participation. By combining minimum and maximum cardinalities, we can describe business rules with much greater accuracy. Today's goal is to learn how to determine both the cardinality and the participation of a relationship directly from a business rule description. This will complete our understanding of relationship constraints, preparing us to start drawing complete ERDs in the next lesson.

1. Cardinality vs. Participation: Minimum and Maximums

When we define a relationship, we need to specify the rules for participation in both directions. For each side of the relationship, we define a pair of values: a minimum and a maximum number of associated instances.

Cardinality refers to the maximum number of instances. Is it one, or is it many? This is what you learned to identify in the last lesson (1:1, 1:M, M:N).
Participation refers to the minimum number of instances. Is it zero (optional) or one (mandatory)?

Let's look at an example to make this clear.

Cardinality and Participation Example — This diagram shows the relationship between Staff and Branch entities. The business rules define both the cardinality (1:1) and the participation (mandatory for Branch, optional for Staff).

As you can see from the diagram, we can extract two sets of rules from the business description:

Cardinality (Maximums): A branch is managed by one staff member, and a staff member manages at most one branch. This gives us a 1:1 relationship.
Participation (Minimums): All branches must be managed, meaning a branch must participate in the relationship (mandatory). However, not all staff manage a branch, so a staff member's participation is optional.

We express these constraints as a pair of numbers in (min, max) format.

Reading from Staff to Branch: A staff member manages (0,1) branches. (Minimum 0, Maximum 1)
Reading from Branch to Staff: A branch is managed by (1,1) staff members. (Minimum 1, Maximum 1)

The following reading will formalize these concepts and show how they are derived from business rules.

Integrity Rules and Constraints

This text from "Database Design - 2nd Edition" explains how business rules are used to define cardinality and participation (optional vs. mandatory).

Start at the heading Business Rules. Read this section to understand how business rules are the source for our constraints. Continue to the sub-heading Cardinality and connectivity. Pay attention to the definitions and the diagram showing where the symbols are placed. Finally, read the sections Optional relationships and Mandatory relationships to see clear examples of how minimum participation (0 or 1) is represented.

2. Reading the Symbols: Crow's Foot Notation

To apply these rules, we need a consistent visual language. Crow's Foot notation is an industry standard for ERDs. It uses a combination of symbols to represent the (min, max) constraints at each end of a relationship line.

The video below is an excellent guide to interpreting this notation. It clearly explains what each symbol means and, most importantly, how to combine them to represent the business rules.

Topic 04, Part 08 - Crow's Foot Symbols with Cardinalities

This video by Dr. Daniel Soper breaks down how to read and interpret Crow's Foot symbols for cardinality and participation.

Watch the following segments: First, get an introduction to the three basic symbols: a circle for zero, a perpendicular line for one, and the "crow's foot" for many. Next, watch the crucial explanation on how to identify minimum and maximum cardinality. The key rule is that the symbol closest to the entity is the maximum, and the one further away is the minimum. Finally, see how this works in practice by watching the example of reading a relationship in both directions between CUSTOMER and ORDER.

To summarize the key point from the video: when you see the symbols on a relationship line, you can read the (min, max) pair directly.

Business Rules Example for Relationship Cardinality — This diagram illustrates the process of turning a natural language business rule into the structured grammar and notation used in an ERD.

Based on the image and the video, we can establish a clear process:

Identify the two entities (e.g., SHIPMENT, ITEM).
Read the business rule in one direction (e.g., "A shipment must include one or more items").
Extract the min and max values ("must include" -> min=1, "one or more" -> max=many).
Place the corresponding symbols on the line at the ITEM end.
Repeat the process in the other direction.

3. Common Cardinality Combinations

Now let's see how these minimum and maximum values combine to form the four standard relationship endings.

ERD: Relationship Cardinalities

This video from Dustin Ormond provides a quick and clear overview of the four basic cardinality constraint combinations.

Watch the first section of the video which introduces the four combinations: Optional one (0,1): A circle and a single line. Mandatory one (1,1): Two single lines. Optional many (0,N): A circle and a crow's foot. Mandatory many (1,N): A single line and a crow's foot.

These four combinations are the building blocks for describing any relationship between two entities.

4. Practice: Analyzing a Business Scenario

Let's apply these concepts to a practical problem. The following video segment walks through a business scenario, identifying entities and then determining the cardinality and participation for their relationships. This is a great way to see the thought process in action.

ERD: Relationship Cardinalities

In this segment, the presenter analyzes a scenario about a basketball league.

Watch the learning activity from the beginning of the exercise. Before the presenter explains the Player-Game relationship, pause the video and try to determine the (min, max) cardinalities yourself based on the sentence: "a basketball player plays in many games." Player to Game: What is the minimum and maximum number of games a player can play in? Game to Player: What is the minimum and maximum number of players a game can have? After you've thought about it, resume the video to compare your reasoning. Note how the presenter has to make some logical assumptions where the rules aren't perfectly explicit—this is a common part of database design.

5. Exercise

Now it's your turn to determine relationship constraints from a set of business rules.

Swim Club Database Exercise

Let's return to the "Database Design" textbook page and find the exercises at the bottom.

Find the section titled Exercises. You will see an ERD for a swim club and a list of table definitions. Below that is a numbered list of questions. Your task is to answer Question 2. Read each business rule provided and determine the (min, max) cardinality for both sides of the corresponding relationship. For example, for the first rule "A pool may or may not ever have a class," you need to figure out: The (min, max) for the CLASS side of the POOL-CLASS relationship. The (min, max) for the POOL side of the POOL-CLASS relationship (based on the other rules).

Click to see the solution for Question 2.

Here are the cardinalities based on the provided business rules:

tblPool and tblClasses
- Rule: "A pool may or may not ever have a class."
- Rule: "The class must have a valid pool."
- POOL to CLASS: A pool is associated with (0,N) classes.
- CLASS to POOL: A class is associated with (1,1) pools.
tblLevels and tblClasses
- Rule: "The levels table must always be associated with at least one class."
- Rule: "The class must always be associated with an existing level."
- LEVEL to CLASS: A level is associated with (1,N) classes.
- CLASS to LEVEL: A class is associated with (1,1) levels.
tblStaff and tblClasses
- Rule: "The staff table may not have ever taught a class."
- Rule: "The class may not have an instructor assigned."
- STAFF to CLASS: A staff member teaches (0,N) classes.
- CLASS to STAFF: A class is taught by (0,1) staff members (instructors).
tblStudents and tblEnrollment
- Rule: "All students must be enrolled in at least one class."
- STUDENT to ENROLLMENT: A student has (1,N) enrollments.
- ENROLLMENT to STUDENT: An enrollment belongs to (1,1) student.
tblClasses and tblEnrollment
- Rule: "The class must have students enrolled in it."
- CLASS to ENROLLMENT: A class has (1,N) enrollments.
- ENROLLMENT to CLASS: An enrollment is for (1,1) class.

Conclusion

In this lesson, we added the final layer of detail needed to accurately model relationships. By combining maximum cardinality with minimum participation, we can translate nuanced business rules into precise, unambiguous constraints.

Key Takeaways:

A relationship constraint is defined by a (minimum, maximum) pair of values at each end.
Participation describes the minimum value: 0 for optional and 1 for mandatory.
Cardinality describes the maximum value: 1 for one and N (or many) for more than one.
To determine the constraints, you must read the business rules for the relationship in both directions.
Crow's Foot notation provides a standard visual language to represent these (min, max) constraints on an ERD.

You now understand all the core components of an ERD: entities, attributes, relationships, and their detailed constraints. In our next lesson, we will put all this knowledge to use as we learn the standard symbols and begin drawing our first complete Entity-Relationship Diagrams.