Entities on their own are just lists: a list of students, a list of courses, a list of departments. The real value of a database comes from the connections between them: which student is in which course, which employee works in which department, who reports to whom.
In the ER model, these connections are called relationships. In this chapter, we will learn how relationships are described, and the two most important constraints on them:
Cardinality: how many entities can be connected (1:1, 1:N, M:N).
Participation: must every entity be connected, or is it optional?
These two ideas are asked in almost every DBMS exam, and they directly decide how your tables and foreign keys will look later.
Relationship, Relationship Type and Relationship Set
We met these terms briefly in the ER Model chapter. Let's make them precise.
A relationship is an association among two or more entities. A relationship type R among entity types E1, E2, ..., En defines the set of possible associations, and the relationship set is the set of associations that currently exist. Mathematically, the relationship set is a subset of E1 × E2 × ... × En.
Example: WORKS_IN between EMPLOYEE and DEPARTMENT.
Relationship type : WORKS_IN ( EMPLOYEE, DEPARTMENT )
Relationship set : { (Aarav, Engineering),
(Priya, Engineering),
(Sneha, Sales),
... }
One relationship : (Priya, Engineering)The "subset of a Cartesian product" view is useful: E1 × E2 is every possible pairing, and the relationship set picks out the pairs that are actually true.
In Chen notation, a relationship type is a diamond connected by lines to the entity types that take part in it.
Degree of a Relationship
The degree of a relationship type is the number of entity types that participate in it.
Degree | Name | Example |
|---|---|---|
1 | Unary (recursive) | EMPLOYEE supervises EMPLOYEE |
2 | Binary | STUDENT enrolls in COURSE |
3 | Ternary | SUPPLIER supplies PART to PROJECT |
n | n-ary | Rare in practice |
Binary relationships are by far the most common.
Unary (Recursive) Relationships and Role Names
In a recursive relationship, the same entity type appears twice, playing different roles. To avoid confusion, we label each line with a role name.
supervisor
+-------------1-------------+
| |
[ EMPLOYEE ] < SUPERVISES >
| |
+-------------N-------------+
subordinateEach employee (as a subordinate) has at most one supervisor, and a supervisor can have many subordinates. Our sample company database actually stores this relationship through the manager_id column, which points back into the same employees table:
SELECT e.emp_id, e.name AS employee, m.name AS reports_to
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.dept_id IN (20, 30)
ORDER BY e.emp_id;+--------+--------------+--------------+
| emp_id | employee | reports_to |
+--------+--------------+--------------+
| 4 | Sneha Kapoor | Aarav Sharma |
| 5 | Vikram Singh | Sneha Kapoor |
| 6 | Ananya Das | Sneha Kapoor |
| 7 | Karan Patel | Aarav Sharma |
| 8 | Meera Nair | Karan Patel |
+--------+--------------+--------------+Sneha appears in both roles: she is a subordinate of Aarav and a supervisor of Vikram and Ananya. That is exactly why role names are needed.
Other examples of recursive relationships: COURSE is prerequisite of COURSE, PERSON is married to PERSON, USER follows USER.
Ternary Relationships: Why Not Just Three Binary Ones?
Consider "supplier S supplies part P to project J". It is tempting to replace this ternary relationship with three binary ones: SUPPLIER–PART (can supply), PART–PROJECT (uses), SUPPLIER–PROJECT (supplies to). But these are not equivalent.
Suppose the true facts are:
SUPPLIES (supplier, part, project)
(S1, P1, J2)
(S1, P2, J1)
(S2, P1, J1)The three binary relationships would store:
SUPPLIER–PART : (S1,P1) (S1,P2) (S2,P1)
PART–PROJECT : (P1,J1) (P1,J2) (P2,J1)
SUPPLIER–PROJECT : (S1,J1) (S1,J2) (S2,J1)Now ask: "Does S1 supply P1 to J1?" Every binary piece says yes: S1 supplies P1, P1 is used in J1, S1 supplies to J1. But that fact is false. S1 supplies P1 only to J2.
The binary version has lost information. A ternary relationship records which three entities go together, and that cannot always be rebuilt from pairs. So: use a ternary relationship when a fact genuinely involves all three at once. (This same idea appears again much later, in 4NF & 5NF.)
Cardinality Ratio (Mapping Cardinality)
The cardinality ratio of a binary relationship specifies the maximum number of relationship instances that an entity can participate in.
For binary relationships between A and B, there are four possibilities.
One-to-One (1:1)
An entity in A is associated with at most one entity in B, and vice versa.
Example: EMPLOYEE manages DEPARTMENT. Each department has at most one manager, and an employee manages at most one department.
EMPLOYEE DEPARTMENT
Aarav ─────────────► Engineering
Sneha ─────────────► Sales
Karan ─────────────► HR
Priya (Priya manages nothing)
Research (no manager yet)
[ EMPLOYEE ]──1──< MANAGES >──1──[ DEPARTMENT ]One-to-Many (1:N)
An entity in A can be associated with many entities in B, but an entity in B is associated with at most one entity in A.
Example: DEPARTMENT has EMPLOYEEs. One department has many employees; each employee is in at most one department.
DEPARTMENT EMPLOYEE
Engineering ─────┬──► Aarav
├──► Priya
└──► Rohan
Sales ─────┬──► Sneha
└──► Vikram
[ DEPARTMENT ]──1──< HAS >──N──[ EMPLOYEE ]Many-to-One (N:1)
The same as 1:N, just read from the other side. "EMPLOYEE works in DEPARTMENT" is N:1. Whether you call it 1:N or N:1 depends only on which entity you name first.
Many-to-Many (M:N)
An entity in A can be associated with many entities in B, and an entity in B with many in A.
Example: STUDENT enrolls in COURSE.
STUDENT COURSE
Priya ──────┬──────► DBMS
└──────► OS
Rahul ──────┬──────► DBMS
└──────► CN
Kavya ─────────────► OS
[ STUDENT ]──M──< ENROLLS >──N──[ COURSE ]How to Read the 1 and N Labels
In Chen notation, the label is written on the line next to an entity, and it tells you how many of that entity one entity from the other side can be connected to.
[ EMPLOYEE ]──N──< WORKS_IN >──1──[ DEPARTMENT ]
"1" next to DEPARTMENT → one employee works in at most 1 department
"N" next to EMPLOYEE → one department can have N employeesA quick way to find the ratio: ask the question from both sides.
"How many departments can one employee work in?" → one.
"How many employees can one department have?" → many.
So it is N:1 (employee to department).
How Many Relationship Instances Are Possible?
A common numerical question. Let entity set A have m entities and B have n entities. The maximum number of instances in the relationship set is:
Cardinality | Maximum relationship instances | Why |
|---|---|---|
1:1 | min(m, n) | Each entity on each side is used at most once |
1:N (A to B) | n | Each B entity can appear at most once |
M:N | m × n | Every possible pair can appear |
Example: 10 departments and 20 employees. If DEPARTMENT–EMPLOYEE is 1:N, at most 20 pairs can exist (one per employee). If it were M:N, up to 10 × 20 = 200. If it were 1:1, at most 10.
Participation Constraint
Cardinality gives the maximum. Participation is about the minimum: must every entity take part at least once?
Total Participation
Total participation of an entity type E in a relationship R means every entity in E must participate in at least one relationship instance of R.
It is also called existence dependency: the entity cannot exist in the database without being related. It is drawn with a double line.
Example: if the company rule is "every employee must belong to a department", EMPLOYEE has total participation in WORKS_IN.
Partial Participation
Partial participation means some entities of E may not participate in R at all.
It is drawn with a single line.
Example: not every employee manages a department, so EMPLOYEE participates partially in MANAGES.
[ EMPLOYEE ]════N════< WORKS_IN >────1────[ DEPARTMENT ]
total: every employee partial: a department
works in some department may have no employeesParticipation in Real Data
Let's check our sample company database:
SELECT d.dept_id, d.dept_name, COUNT(e.emp_id) AS employees
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;+---------+-------------+-----------+
| dept_id | dept_name | employees |
+---------+-------------+-----------+
| 10 | Engineering | 3 |
| 20 | Sales | 3 |
| 30 | HR | 2 |
| 40 | Marketing | 1 |
| 50 | Research | 0 |
+---------+-------------+-----------+Research has zero employees, so in this database DEPARTMENT participates partially. And employee 10 (Ishita Gupta) has a NULL dept_id, so EMPLOYEE participates partially too: the schema allows an employee with no department. If the business rule says every employee must have a department, the schema should enforce it, for example by making dept_id NOT NULL.
This is an important lesson: participation is a business rule decided at design time. The data you see today may happen to satisfy a rule that the design does not enforce; the design must capture the rule so the DBMS can enforce it.
Cardinality + Participation Together: (min, max) Notation
Instead of separate symbols, many textbooks use a single pair (min, max) on each line:
(min, max) written next to entity type E for relationship R means: each entity of E takes part in at least min and at most max instances of R.
min = 0 → partial participation; min ≥ 1 → total participation.
max = 1 → the entity takes part at most once; max = N → many times.
Example for WORKS_IN, with the rules "each employee works in exactly one department" and "a department has zero or more employees":
[ EMPLOYEE ]──(1,1)──< WORKS_IN >──(0,N)──[ DEPARTMENT ]
EMPLOYEE (1,1): each employee is in at least 1 and at most 1 department
DEPARTMENT (0,N): each department has 0 to many employeesCareful, this is the classic trap. In the 1/N notation, the label next to DEPARTMENT is "1"; in (min, max) notation, the pair next to DEPARTMENT is (0,N). The two notations put the "many" on opposite sides, because:
1/N labels describe how many of this entity the other side connects to.
(min, max) describes how many times this entity itself participates.
Rule | 1/N + line style | (min, max) |
|---|---|---|
Each employee works in exactly one department; a department may have none | EMPLOYEE ══N══ WORKS_IN ──1── DEPARTMENT | EMPLOYEE (1,1) — WORKS_IN — (0,N) DEPARTMENT |
Each department has exactly one manager; most employees manage nothing | EMPLOYEE ──1── MANAGES ══1══ DEPARTMENT | EMPLOYEE (0,1) — MANAGES — (1,1) DEPARTMENT |
A student takes 1 to 8 courses; a course may have no students yet | STUDENT ══M══ ENROLLS ──N── COURSE | STUDENT (1,8) — ENROLLS — (0,N) COURSE |
(min, max) is also more precise: it can say "1 to 8 courses", which 1/N cannot.
Always check which convention a question uses before answering.
Attributes on Relationships and Where They Can Go
Relationships can carry attributes (see Types of Attributes). Where such an attribute "naturally" belongs depends on the cardinality:
Cardinality | Relationship attribute can be moved to... | Example |
|---|---|---|
1:1 | Either entity |
|
1:N | The entity on the N side |
|
M:N | Nowhere; it must stay with the relationship |
|
Why? On the N side of a 1:N relationship, each entity is involved in only one instance, so "the attribute of that instance" is also a single value for the entity. For M:N, a student has many enrollments and a course has many enrollments, so neither side has a single value to store. This is exactly what decides whether a relationship needs its own table, as we will see in Converting ER Diagrams to Tables.
Worked Example: Deciding Cardinality and Participation
Requirement: "A hospital has doctors and patients. Each patient is assigned exactly one primary doctor. A doctor may have many patients, but newly joined doctors may have none. Doctors can also be assigned to many wards, and every ward must have at least one doctor."
Step 1: ASSIGNED_TO (PATIENT, DOCTOR)
One patient → how many primary doctors? Exactly 1.
One doctor → how many patients? Many (possibly 0).
Cardinality: PATIENT : DOCTOR = N:1.
Participation: PATIENT total (every patient has one), DOCTOR partial (new doctors have none).
Step 2: WORKS_IN (DOCTOR, WARD)
One doctor → many wards; one ward → many doctors. M:N.
Every ward needs at least one doctor → WARD total. Doctors in admin roles might have no ward → DOCTOR partial.
[ PATIENT ]══N══< ASSIGNED_TO >──1──[ DOCTOR ]──M──< WORKS_IN >══N══[ WARD ]
(min,max) version:
[ PATIENT ](1,1)──< ASSIGNED_TO >──(0,N)[ DOCTOR ](0,N)──< WORKS_IN >──(1,N)[ WARD ]Common Mistakes
Asking the question from only one side. "A department has many employees" alone does not tell you whether it is 1:N or M:N. Also ask "Can an employee be in many departments?"
Mixing up the 1/N and (min,max) placement. They put "many" on opposite sides.
Confusing cardinality with participation. Cardinality = maximum (1 or many). Participation = minimum (0 or at least 1).
Breaking a ternary relationship into three binaries without checking whether information is lost.
Forgetting role names in recursive relationships, making it unclear who supervises whom.
Deciding cardinality from sample data. Five rows in which every employee happens to have one project do not prove that an employee can never have two. The business rules decide.
Interview Questions
1. What is the degree of a relationship? Give examples.
The number of entity types participating in it. Unary: EMPLOYEE supervises EMPLOYEE. Binary: STUDENT enrolls in COURSE. Ternary: SUPPLIER supplies PART to PROJECT.
2. What is the difference between cardinality ratio and participation?
Cardinality ratio gives the maximum number of relationship instances an entity can take part in (1 or N). Participation gives the minimum: total (at least one, every entity must participate) or partial (zero allowed).
3. How are total and partial participation shown in an ER diagram?
Total participation with a double line between the entity and the relationship; partial participation with a single line. In (min,max) notation, min ≥ 1 is total and min = 0 is partial.
4. What is a recursive relationship? Why are role names needed?
A relationship in which the same entity type participates more than once, like EMPLOYEE supervises EMPLOYEE. Role names (supervisor, subordinate) clarify what part each participation plays.
5. Can a ternary relationship always be replaced by three binary relationships?
No. The binaries only store pairs, and combining pairs can produce facts that were never true (e.g., S1 supplies P1 to J1). A ternary relationship is needed when a fact involves all three entities together.
6. If entity sets A and B have 5 and 8 entities, what is the maximum number of instances in a 1:1, a 1:N (A to B) and an M:N relationship?
1:1 → min(5, 8) = 5. 1:N → 8 (each B entity appears at most once). M:N → 5 × 8 = 40.
7. In a 1:N relationship, where can a relationship attribute be placed?
On the entity on the N side, because each such entity participates in only one instance. For M:N, it must stay on the relationship.
Summary
A relationship set is a subset of the Cartesian product of the participating entity sets.
Degree: unary (recursive, needs role names), binary, ternary, n-ary. A ternary relationship is not always equal to three binaries.
Cardinality ratio (maximum): 1:1, 1:N, N:1, M:N. Ask "how many?" from both sides.
Participation (minimum): total (double line, existence dependency) or partial (single line).
(min, max) notation combines both, but places "many" on the opposite side compared to 1/N labels.
Relationship attributes can move to either side in 1:1, to the N side in 1:N, and must stay on the relationship in M:N.
Some entities cannot even be identified without a relationship to another entity. In the next chapter, Weak Entities, we will see what they are and how total participation and identifying relationships work together.