Lesson 3	Types of Relationships
Objective	Describe and implement the three relationship types (1:1, 1:N, M:N) with correct cardinality and participation.

Three Relationship Types in ER Modeling

Entity-Relationship (ER) diagrams model how entities relate. In practice, almost every design choice you make-keys, foreign keys, junction tables, follows from the type of relationship and whether participation is optional or mandatory.

The three canonical types

One-to-one (1:1) – One instance of A relates to at most one instance of B, and vice versa.
One-to-many (1:N) – One instance of A relates to zero or many instances of B; each instance of B relates to at most one A.
Many-to-many (M:N) – One instance of A relates to zero or many instances of B; and one instance of B relates to zero or many instances of A.

In relational databases you implement M:N via an associative (junction) table, yielding two 1:N links.

Cardinality & Participation (Constraints)

When documenting a relationship, specify both:

Cardinality ratio (how many): 1:1, 1:N, N:1, M:N for a binary relationship.
Participation (must or may): mandatory (at least one) or optional (zero allowed). UML often shows this as min..max (e.g., 0..1, 0..*, 1..1).

These constraints limit the allowed combinations of entity instances and directly influence key/constraint choices in the physical schema.

Diagrams (kept responsive via CSS)

The figures below retain the original, responsive images controlled by the page’s media queries.

one-to-one — **One-to-one (1:1):** Typical example: *Person* ↔ *Passport*. Each person has at most one passport; each passport belongs to at most one person. Often implemented by a foreign key that is also *UNIQUE* (or a shared primary key).

one to many — **One-to-many (1:N):** Example: *Department* → *Employees*. One department has many employees; each employee belongs to one department. Implemented with a foreign key in *Employees* referencing *Department*.

many-to-one — **Many-to-one (N:1):** The inverse view of 1:N. Example: *Employees* → *Department* (many employees map to one department). Same implementation details as 1:N—foreign key on the “many” side.

many-to-many — **Many-to-many (M:N):** Example: *Students* ↔ *Courses*. Implement via an associative table (e.g., `Enrollment(StudentID, CourseID, ...)`) with foreign keys to both sides and a composite or surrogate key.

Relational Implementation Patterns

1:N – Put a foreign key on the N-side referencing the 1-side. Consider indexing that FK for join performance. Use NOT NULL if participation is mandatory; allow NULL if optional.
1:1 – Use a foreign key with a UNIQUE constraint (or share the primary key across both tables). Choose which table holds the FK based on ownership/lifecycle and optionality.
M:N – Create an associative table with two FKs (A_id, B_id). Make (A_id, B_id) the primary key or add a surrogate key and enforce uniqueness on (A_id, B_id). Store relationship attributes (e.g., grade, assigned_on) here.

Notation Cheat Sheet

Concept	Chen	Crow’s Foot	UML
One	“1”	Single bar	`1` or `0..1`
Many	“N” or “M”	Crow’s foot	`` or `0..`
Optional	Note/annotation	Open circle (O)	Lower bound 0 (e.g., `0..1`)

FAQ: 1..* (UML) and 1:N (ER) both mean one-to-many. UML makes optionality explicit via the lower bound (e.g., 0..* vs 1..*).

Everyday Examples

1:1	A vehicle has at most one title; a title applies to at most one vehicle.
1:N	A customer places many orders; each order belongs to one customer.
M:N	A student enrolls in many classes; a class has many students.

In real systems, 1:N and M:N dominate; true 1:1 is rarer and usually modeled for lifecycle, security, or sparsity reasons.