Relationships, Cardinality & Participation

By the end of this lesson you will be able to draw relationships in an ER diagram, state their degree, mark cardinality (1:1, 1:N, M:N) and participation (total or partial), and read min-max constraints. These are the questions that decide how many tables you need and where the foreign keys go.

What is a relationship?

A relationship is an association between entities. "Priya enrols in DBMS" links one student with one course. A relationship set (or relationship type) is the collection of all such links of one kind, such as every ENROLS_IN link in the college. In Chen notation a relationship is a diamond; in this course it is written <ENROLS_IN>.

Relationships can have their own attributes. The marks Priya scored belong neither to Priya alone nor to DBMS alone. They belong to the pair, so marks is an attribute of ENROLS_IN.

+---------+                         +--------+
| STUDENT |-------< ENROLS_IN >-----| COURSE |
+---------+            |            +--------+
                    (marks)

Degree of a relationship

The degree is the number of entity types that take part.

Most relationships are binary. In a recursive relationship, the same entity type appears twice in different roles. In the practice database, an employee plays the role "manager" for some employees and "reportee" for another. You can follow that relationship with a self join:

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.department = 'Sales'
ORDER BY e.employee_id;

employee | manager
---------+--------
Simran   | Rajesh
Rahul    | Simran
Nisha    | Simran
(3 rows)

You should see that Simran is an employee in one row and a manager in two others. One entity type, two roles.

Cardinality ratio

The cardinality ratio says how many entities on one side can be linked to one entity on the other side. For binary relationships there are four cases.

1. One-to-one (1:1): each department has one head, and a professor heads at most one department.
2. One-to-many (1:N): one department has many students, but each student belongs to one department.
3. Many-to-one (N:1): the same as 1:N read from the other side: many students, one department.
4. Many-to-many (M:N): a student takes many courses, and a course has many students.

[DEPARTMENT] 1 ---< HEADED_BY >--- 1 [PROFESSOR]       1:1

[DEPARTMENT] 1 ---< HAS >--------- N [STUDENT]         1:N

[STUDENT]    M ---< ENROLS_IN >--- N [COURSE]          M:N

An everyday check helps. Ask the question twice, once from each side: "One student can take how many courses?" Many. "One course can have how many students?" Many. So it is M:N. For Aadhaar: "One person has how many Aadhaar numbers?" One. "One Aadhaar number belongs to how many people?" One. So it is 1:1.

Participation

Participation says whether every entity must take part in the relationship.

• Total participation (mandatory): every entity takes part. Every student must belong to a department. Drawn as a double line.
• Partial participation (optional): some entities may not take part. Not every professor heads a department. Drawn as a single line.

Total participation is also called existence dependency: a student cannot exist in the system without a department. In this course a double line is written ===:

[STUDENT] N ===< BELONGS_TO >--- 1 [DEPARTMENT]
   total participation     partial participation
(every student has a dept)  (a new dept may have no students yet)

In tables, total participation on the "many" side usually becomes a NOT NULL foreign key. Step 1: create the tables. Step 2: try a student with no department. Step 3: look at the results.

CREATE TABLE departments (
  dept_id INTEGER PRIMARY KEY,
  dept_name VARCHAR(30) NOT NULL
);
CREATE TABLE students (
  roll_no INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  dept_id INTEGER NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
);

INSERT INTO departments VALUES (10, 'CSE'), (20, 'ECE'), (30, 'Civil');
INSERT INTO students VALUES (2401, 'Farhan', 10), (2402, 'Isha', 10), (2403, 'Kabir', 20);

SELECT d.dept_name, COUNT(s.roll_no) AS students
FROM departments d
LEFT JOIN students s ON s.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

dept_name | students
----------+---------
CSE       | 2
ECE       | 1
Civil     | 0
(3 rows)

You should see that Civil has 0 students. That is allowed, because department participation is partial. A student without a department is not allowed, because the NOT NULL column enforces total participation. In MySQL, the attempt fails like this:

INSERT INTO students VALUES (2404, 'Meera', NULL);

ERROR 1048 (23000): Column 'dept_id' cannot be null

Min-max notation

Cardinality and participation can be combined into one pair (min, max) written next to each entity. It says how many times each entity takes part in the relationship.

For BELONGS_TO, a student is (1, 1): exactly one department. A department is (0, N): zero or more students. A minimum of 1 means total participation; a minimum of 0 means partial.

Be careful when reading other books: min-max pairs are written next to the entity they describe, which is the opposite side from where 1 and N appear in the ratio notation. Always check the legend.

Advanced: ternary relationships

Suppose a teacher teaches a course in a particular semester. You might try three binary relationships instead of one ternary one. They do not say the same thing.

               [SEMESTER]
                    |
[TEACHER] ---< TEACHES >--- [COURSE]

The ternary TEACHES stores facts like "Meera taught DBMS in Sem 5 of 2025". Three separate binary links (teacher-course, course-semester, teacher-semester) would only say that Meera taught DBMS at some time, that DBMS ran in Sem 5, and that Meera taught in Sem 5. You could not tell whether Meera taught DBMS in that semester. Use a ternary relationship when a fact needs all three entities at once.

Common mistakes

• Asking the cardinality question from one side only. Always ask from both sides before writing 1:N or M:N.
• Mixing up participation and cardinality. Cardinality is about the maximum (one or many). Participation is about the minimum (zero or at least one).
• Putting relationship attributes on an entity. marks belongs on ENROLS_IN, not on STUDENT or COURSE.
• Forgetting roles in recursive relationships. Label both lines (manager, reportee) or the diagram is ambiguous.

Key points

• A relationship links entities; a relationship set holds all links of one kind.
• Degree is the number of entity types involved: unary, binary or ternary.
• Cardinality ratios are 1:1, 1:N, N:1 and M:N; ask the question from both sides.
• Total participation (double line) means every entity must take part; partial means it may not.
• Min-max pairs combine both ideas: the minimum shows participation, the maximum shows cardinality.
• Relationships can have their own attributes, such as marks on ENROLS_IN.

[PATIENT] N ===< TREATED_BY >--- 1 [DOCTOR]
patient (1,1), doctor (0,N)

[DOCTOR]  N ===< WORKS_IN >=== 1 [WARD]
doctor (1,1), ward (1,N)

CREATE TABLE doctors (
  doctor_id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL
);
CREATE TABLE patients (
  patient_id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  doctor_id INTEGER NOT NULL,
  FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id)
);