Cardinality and Participation Constraints

Concept

Two constraints describe how entities may participate in a relationship, and both are needed for a complete design.

• Cardinality ratio — how many. One to one, one to many, many to one, many to many.
• Participation — must they. Total participation means every entity of that type must take part; partial means it may.

The four cardinality ratios

ONE TO ONE (1:1)
   each on the left relates to at most one on the right
   and the reverse
     EMPLOYEE ---- MANAGES ---- DEPARTMENT
     one employee heads one department, and one
     department has one head

ONE TO MANY (1:N)
   one on the left relates to many on the right
     DEPARTMENT ---- HAS ---- STUDENT
     one department has many students,
     each student belongs to one department

MANY TO ONE (N:1)
   the same relationship read from the other side

MANY TO MANY (M:N)
   many on each side
     STUDENT ---- ENROLS ---- COURSE
     a student takes many courses,
     a course has many students

Memorise the conversion rule: the foreign key always goes on the many side, and a many to many relationship always becomes its own table. Those two sentences answer a large share of ER to relational questions.

Participation constraints

   DEPARTMENT ====== HAS ------ STUDENT
              double        single
              line          line

  Read it as: every STUDENT must belong to a department
  (total on the student side), but a department may exist
  with no students yet (partial on the department side).

  Note that the double line is drawn on the side whose
  entities must ALL participate.

Min max notation

An alternative notation writes a pair on each line, giving the minimum and maximum number of relationship instances one entity may take part in. It expresses cardinality and participation together, which is why many textbooks prefer it.

   STUDENT  (1,8) ----- ENROLS ----- (0,60)  COURSE

   a student takes at least 1 and at most 8 courses
   a course has at least 0 and at most 60 students

   minimum 0  -> partial participation
   minimum 1  -> total participation
   maximum 1  -> the "one" side
   maximum N  -> the "many" side

Example

   Reading a full constraint set for a college

   DEPARTMENT ==== HAS ---- STUDENT           1:N, student total
     every student belongs to exactly one department
     a department may have no students

   STUDENT ---- ENROLS ---- COURSE            M:N, both partial
     a student may take no courses yet
     a course may have no students yet

   EMPLOYEE ---- HEADS ==== DEPARTMENT        1:1, department total
     every department must have a head
     not every employee heads one

   Converting:
     students   ( roll_no, name, dept_code NOT NULL )
     enrolments ( roll_no, course_code, marks )
     departments( dept_code, dept_name, head_emp_id NOT NULL )

Notice how every constraint became something concrete: a foreign key, a junction table, or a NOT NULL. That is what these symbols are for.

Common mistakes

• Drawing the double line on the wrong side. It goes on the side whose entities must all take part.
• Confusing cardinality with participation. Cardinality is how many; participation is whether it is compulsory. A 1:N relationship can be total or partial on either side.
• Putting the foreign key on the one side. It would need to hold many values, which no column can.
• Trying to represent M:N with a foreign key. It always needs a junction table.
• Ignoring participation when converting. Total participation is the reason a column is NOT NULL; miss it and invalid rows become possible.

Exam and interview questions

1. Define the four cardinality ratios with an example of each.
2. Differentiate total and partial participation, with notation.
3. Where does the foreign key go for a 1:N relationship, and why?
4. Explain min max notation and how it expresses both constraints at once.
5. How does total participation appear in the final table definition?

Practice

1. State cardinality and participation for: patient and admission, book and author, employee and department, customer and order.
2. Draw a 1:1 relationship with total participation on one side and say where the foreign key belongs.
3. Write the min max pairs for: every employee works in exactly one department, a department has at least three employees.

Conclusion

Cardinality answers how many, participation answers whether it is compulsory, and min max notation states both at once. Both must be read correctly, because between them they decide every foreign key, junction table and not null in the finished schema.