Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 27

From Business Rules to ERD: Cardinality & Participation

Recommended resources

Hello! In our last lesson, we developed a process for extracting entities, attributes, and high-level relationships from a business scenario. You learned to spot the "nouns" and "verbs" in a description to build the basic skeleton of a database model.

Today, we will focus on adding the critical details to that skeleton. We'll explore how to precisely define the rules governing each relationship by determining its cardinality and participation constraints. By the end of this lesson, you will be able to interpret business rules to establish not just the type of relationship (like 1:M), but also whether it's optional or mandatory. This is a vital skill for creating accurate and robust database designs for your university project.

1. Cardinality and Participation: The Rules of the Relationship

In database design, the term "cardinality" is often used to describe the nature of a relationship. However, to be precise, we need to define two distinct components:

  1. Maximum Cardinality: This specifies the maximum number of instances one entity can be related to in another entity. The options are one or many. This is what defines the relationship as one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N).
  2. Minimum Cardinality (Participation): This specifies the minimum number of instances required in the relationship. The options are zero (optional) or one (mandatory).
    • Optional Participation (0) means an instance of an entity is not required to participate in the relationship. For example, a CUSTOMER might exist in the database without ever having placed an ORDER.
    • Mandatory Participation (1) means an instance of an entity must participate in the relationship. For example, every ORDER must be associated with a CUSTOMER.

The following resource provides clear, formal definitions for these concepts.

Defining Cardinality and Participation

This guide from Drexel University clearly distinguishes between cardinality and participation constraints, which together define the business rules for a relationship.

Read the two paragraphs under the heading "Cardinality and Participation Constraints". Focus on how cardinality defines the maximum number of associations (1:1, 1:N, M:N), while participation defines the minimum (optional vs. mandatory).

2. Representing Constraints with Crow's Foot Notation

Once we determine these constraints, we need a way to draw them on our ERD. The most common method is Crow's Foot notation, which combines symbols to show both minimum and maximum cardinality at each end of a relationship line.

The basic symbols are:

  • A circle O represents zero.
  • A single hash mark | represents one.
  • The "crow's foot" <- represents many.

These are combined to form four main symbols representing the (min, max) pair for one side of a relationship:

Symbol Reads as Meaning
`O ` Zero or One
` `
O< Zero or Many (min=0, max=N) - Optional
` <` One or Many

The following video provides an excellent walkthrough of how these symbols are constructed and read on a diagram.

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

This video from Dr. Daniel Soper explains how to interpret Crow's Foot symbols to understand both minimum and maximum cardinality.

First, understand the three fundamental symbols: the circle (zero), the line (one), and the crow's foot (many). Next, pay close attention to the crucial rule for reading min/max cardinality. The symbol closer to the entity box represents the maximum cardinality, while the symbol further away represents the minimum. Finally, see this in action by watching the demonstration of how to read relationships in both directions using the CUSTOMER and ORDER example. This will solidify your understanding.

Here is a helpful chart that summarizes all the common ERD symbols, including the cardinality notations we've just discussed.

ERD Symbols and Notations
A comprehensive guide to standard ERD notations, covering entities, attributes, relationships, cardinality, and participation constraints.

3. A Method for Analyzing Business Rules

Now for the main task: how do you translate a sentence from a business description into a specific (min, max) constraint? A systematic approach is to ask two simple questions for each side of a relationship.

Consider two entities, A and B. To find the constraints on B's side of the relationship, you ask:

  1. For Minimum Cardinality (Participation): Can an instance of entity A exist without being related to any instance of entity B?
    • If yes, the minimum is zero (optional).
    • If no, the minimum is one (mandatory).
  2. For Maximum Cardinality: What is the maximum number of instances of entity B that can be related to a single instance of entity A?
    • If the maximum is one, the max cardinality is one.
    • If the maximum is more than one, the max cardinality is many.

You then repeat this process for the other side of the relationship (i.e., from B's perspective towards A).

Let's see this method applied to a practical example.

ERD: Relationship Cardinalities

In this segment, Dustin Ormond analyzes a short business scenario and determines the cardinality constraints for the relationships.

Watch the section titled "learning activity". Notice how the presenter works through the business rules sentence by sentence. For the relationship between PLAYER and GAME, he asks questions like: "Does a basketball player have to play any games?" The answer ("no, they could be a benchwarmer") directly leads to an optional participation (minimum of zero) on the GAME side of the relationship for a PLAYER.

This thought process—turning business rules into specific questions about minimums and maximums—is the key skill you need to develop.

4. Your Turn: The Swim Club Database

It's time to put these skills into practice. The following exercise describes a database for a swim club. Your task is to use the provided business rules to determine the correct cardinality and participation constraints for the relationships.

Practice: The Swim Club Database

This webpage section provides an ERD for a swim club and a set of business rules. It's an ideal scenario to practice applying your new knowledge.

Find the section titled "Exercises". First, take a moment to understand the ERD (Figure 9.17) and the entities involved (tblStudents, tblClasses, tblStaff, etc.). Then, focus on question 2, which lists eight business rules.

Now, let's analyze a few of these relationships together. On a piece of paper, try to determine the (min, max) cardinality for both sides of the following relationships based on the given rules.

  1. tblStaff and tblClasses

    • Rule: "The staff table may not have ever taught a class."
    • Rule: "The class may not have an instructor assigned."

  2. tblStudents and tblEnrollment

    • Rule: "All students must be enrolled in at least one class."
    • The problem description states: "...students who are enrolled in swim classes." An enrollment record represents a single student in a single class.

Think it through, then check your answers below.

Click to reveal the analysis

1. tblStaff teaches tblClasses

  • From Staff to Class:

    • Minimum: "The staff table may not have ever taught a class." This means a staff member can exist without teaching any classes. So, the minimum is 0.
    • Maximum: The problem doesn't state a maximum. A reasonable assumption is that an instructor (staff) can teach many classes. So, the maximum is Many (N).
    • Result: A staff member teaches (0,N) classes.

  • From Class to Staff:

    • Minimum: "The class may not have an instructor assigned." This means a class can exist without a staff member. So, the minimum is 0.
    • Maximum: A class is taught by an "instructor" (singular). This implies one instructor per class. So, the maximum is 1.
    • Result: A class is taught by (0,1) staff members.

2. tblStudents has tblEnrollment

  • From Student to Enrollment:

    • Minimum: "All students must be enrolled in at least one class." This means a student must have at least one enrollment record. So, the minimum is 1.
    • Maximum: A student can presumably enroll in many classes over time. So, the maximum is Many (N).
    • Result: A student has (1,N) enrollment records.

  • From Enrollment to Student:

    • Minimum: An enrollment record is meaningless without being tied to a specific student. It must belong to a student. So, the minimum is 1.
    • Maximum: By definition, a single enrollment record is for exactly one student in one class. So, the maximum is 1.
    • Result: An enrollment record belongs to (1,1) students.

Conclusion

In this lesson, you've learned to dissect the rules of a relationship into their most fundamental parts: minimum and maximum cardinality. This level of detail is what separates a rough sketch from a precise blueprint for a database.

Key Takeaways:

  • A relationship constraint has two parts: minimum participation (0 for optional, 1 for mandatory) and maximum cardinality (1 or Many).
  • Crow's Foot notation is a standard for visually representing these (min, max) pairs on an ERD.
  • You can determine these constraints by translating business rules into two key questions: "Can A exist without B?" (for the minimum) and "What's the maximum number of Bs for one A?" (for the maximum).

You now have all the analytical tools needed to deconstruct a business problem. In our next lesson, we will put everything together. You will learn to synthesize and draw a complete ERD for a scenario with multiple entities, incorporating 1:1, 1:M, and M:N relationships, moving from analysis to creation.