Relationships & Cardinality
Lecture 3 · how entities connect, how many can connect, whether they must, and the special case of weak entities.
Relationship sets
- A relationship is an association among entities; a relationship set is a set of such associations (e.g.
advisorbetween instructor and student). - Relationships can carry descriptive attributes — e.g.
dateonadvisor,gradeonenrolls. - Degree = number of entity sets involved: binary (2, most common) or ternary (3).
- Role = the function an entity plays — vital in recursive relationships where one entity set appears twice (course ↔ prerequisite, teacher ↔ mentor).
Mapping cardinality — the four shapes
| Type | Meaning | Example |
|---|---|---|
| 1 : 1 | Each A ↔ at most one B, and vice-versa | Person ↔ Passport |
| 1 : N | One A ↔ many B; each B ↔ at most one A | Department ↔ Employees |
| N : 1 | Many A ↔ one B | Students ↔ Advisor |
| N : N | Many both ways | Students ↔ Courses |
Participation
| Type | Line | Meaning | Example |
|---|---|---|---|
| Total | double line | Every entity must participate | Every student must have an advisor |
| Partial | single line | Some entities may not participate | Some instructors advise nobody |
Complex constraint notation: a line labelled
l..h gives min..max — 0..* = optional, many; 1..1 = exactly one. Min of 1 ⇒ total participation.Weak entity sets
A weak entity can't be identified by its own attributes — it depends on an identifying (owner) entity. Its key uses a discriminator (partial key).
PK(weak) = PK(owner) + discriminator.Example: course-offering / section is weak on course. Its PK is (course_id, sec_id, semester, year) — it borrows course_id from its owner.
- Weak entity → double rectangle.
- Identifying relationship → double diamond.
- Discriminator → dashed underline.
Next: ER Diagram & EER →