Exercises — ER diagrams — entities, attributes, relationships, cardinality

• Rectangle ::: an entity (a thing we store data about).
• Ellipse ::: an attribute (a property of an entity).
• Diamond ::: a relationship (an association between entities).
• Double ellipse ::: a multivalued attribute (one entity holds many values, e.g. phone_numbers).
• Dashed ellipse ::: a derived attribute (computed, e.g. age from dob).
• Underlined ellipse ::: the key attribute (uniquely identifies each instance).

Why these shapes and not one shape for all? The diagram must let you tell things apart by eye alone: a table-worthy noun (rectangle) is structurally different from a fact about it (ellipse) and from a verb linking two nouns (diamond). Different jobs → different silhouettes.

• Book → entity (a noun you'd make a table for).
• title → attribute (a fact describing only Book).
• borrows → relationship (a verb linking Member and Book).
• Member → entity.
• join_date → attribute (describes only Member).
• isbn → attribute, specifically the key of Book (uniquely identifies a book).

The trick used: table-worthy noun → rectangle; describing noun → ellipse; linking verb → diamond.

The FK goes on the many side: Employee gets a dept_id column. Why: each employee belongs to exactly one department, so a single column value is enough. If we tried to store employees on Department, we'd need a list of employee ids in one cell — which violates 1NF (see Normalization — 1NF, 2NF, 3NF). Result: Employee(emp_id PK, name, dept_id FK → Department).

Create a junction table:

• Student(student_id PK, name, …)
• Course(course_id PK, title, …)
• Enrollment(student_id FK, course_id FK) — the pair (student_id, course_id) is the composite primary key.

Why a third table: neither side can hold a single FK to the other (both are "many"). So we externalise the link: each row of Enrollment is one pair $(a,b)$ = "this student takes this course". See Primary keys and Foreign keys and SQL CREATE TABLE and constraints.

A double line = total participation: every Loan must be attached to some Customer (a loan can't float free). SQL enforcement: make the FK column customer_id in Loan NOT NULL (and a real FOREIGN KEY). NOT NULL forbids a loan with no customer → mandatory participation. (Contrast: a single line = partial participation = the FK may be NULL.)

Use $R_{\max }=|A|\times |B|$ as the unrestricted ceiling, then apply the cardinality restriction.

• M:N: every pair is allowed → $R_{\max }=4\times 6=24$.
• 1:N (one $A$, many $B$): each $b\in B$ has exactly one partner, so each of the 6 B's contributes at most one pair → $R_{\max }=|B|=6$. Not 24, because the "many" side ($B$) cannot be reused across different A's.
• 1:1: each side used at most once → $R_{\max }=\min (|A|,|B|)=\min (4,6)=4$.

Look at the grid figure: the full 4×6 grid = 24 possible dots (M:N). The 1:N case keeps at most one dot per B-column → 6 dots. The 1:1 case keeps at most one dot per row and per column → a diagonal of 4.

Yes — they are independent axes (ratio = how many; participation = whether mandatory).

• 1:N + total on many side: Department–Employee where every employee must belong to a department. Ratio is 1:N; the "must" makes the employee side total. Enforce with dept_id NOT NULL.
• 1:N + partial: Department–Project where a project may be unassigned (dept_id nullable). Ratio still 1:N; participation is partial because a project can exist alone.

Key insight: the diamond's ratio labels ($1$, $N$) and the line style (single vs double) answer different questions and can be combined freely.

• All assigned: each employee → exactly one pair → $10$ pairs.
• 2 unassigned: those 2 contribute 0 pairs; the other 8 contribute one each → $8$ pairs.

Why departments (the "one" side, count 3) never appear in the arithmetic: in 1:N the count of pairs is driven entirely by the many side, because reuse is forbidden there. Number of departments only caps how they're distributed, not the total.

Entities & attributes

• Patient: patient_id (key, underlined), name (simple), allergies (multivalued — double ellipse; one patient, many allergies).
• Doctor: doctor_id (key), name.

Relationship

• Appointment links Patient–Doctor, M:N (each side "many"). It carries its own attribute datetime. Participation is typically partial on both sides (a newly registered patient may have no appointment yet).

Translate to tables (see figure):

• Patient(patient_id PK, name)
• PatientAllergy(patient_id FK, allergy) — multivalued attribute becomes its own table; composite key (patient_id, allergy).
• Doctor(doctor_id PK, name)
• Appointment(patient_id FK, doctor_id FK, datetime) — junction table for the M:N; primary key (patient_id, doctor_id, datetime).

Why this composite key (patient_id, doctor_id, datetime) and not (patient_id, doctor_id) alone? The primary key must uniquely identify one row and match reality's uniqueness rule. Here, the same patient can legitimately see the same doctor more than once (a follow-up next week). If the key were only (patient_id, doctor_id), the database would reject that second visit as a "duplicate". Adding datetime distinguishes the two visits — the combination "this patient, this doctor, at this exact time" is what's truly unique. We exclude name/other attributes from the key because they don't add identifying power (they describe, not identify) and would bloat every foreign-key reference.

Why allergies is its own table: a multivalued attribute cannot live as one comma-packed cell (breaks 1NF and search). Each value gets its own row — see Relational Model — tables, keys, foreign keys.

Put the FK on Passport (the total-participation side):

• Passport(passport_no PK, person_id FK → Person, NOT NULL, UNIQUE)
• Person(person_id PK, name)

Why this side: every passport must have a person → NOT NULL on the FK enforces total participation on Passport. UNIQUE on person_id enforces the "at most one passport per person" half of 1:1. If we'd put the FK on Person, most rows would be NULL (many people have no passport) and we'd fail to force "every passport has an owner". Placing it on the mandatory/optional-aware side keeps NULLs out where they'd be illegal, and the UNIQUE+NOT NULL pair together is the 1:1 constraint: at most one owner, and exactly one owner is required.

What's wrong:

1. product_ids is a multivalued list in one cell → violates 1NF; no clean join, no FK integrity (nothing stops "99999" for a non-existent product).
2. The relationship is M:N (an order has many products, a product appears in many orders) → it needs a junction table, not a column.
3. Repeating 7,7 to encode "two units" abuses the list to smuggle quantity, which is really an attribute of the relationship.

Correct design:

• Order(order_id PK, order_date)
• Product(product_id PK, name, price)
• OrderLine(order_id FK, product_id FK, quantity, PRIMARY KEY(order_id, product_id))

OrderLine is the junction table; quantity lives on the relationship (one row per distinct product in the order, quantity = 2 instead of listing 7 twice).

Why the key is (order_id, product_id) and not (order_id, product_id, quantity)? The primary key must be the minimal set of columns that uniquely identifies a row. In one order, a given product should appear on exactly one line — its ordered count is recorded in quantity, not by repeating the product. So (order_id, product_id) is already unique on its own; adding quantity to the key is redundant and — worse — would allow two rows for the same product in the same order (with different quantities), reintroducing the very duplication we removed. quantity is a descriptive attribute of the pair, so it stays a non-key column. This restores 1NF, FK integrity, and clean JOINs — see Cardinality and JOIN behaviour.

Cardinalities & participation:

• Course–Section: 1:N (one course → many sections; each section → exactly one course). The Section side is total (every section belongs to a course).
• Instructor–Section: 1:N (one instructor → many sections; each section → exactly one instructor). The Section side is total — the phrase "every Section must have an Instructor" forces mandatory participation.

Physical tables (FK on the many side both times — "the Many holds the One's name"):

• Course(course_id PK, title)
• Instructor(instructor_id PK, name)
• Section(section_id PK, course_id FK → Course NOT NULL, instructor_id FK → Instructor NOT NULL)

Both FKs are NOT NULL, which enforces both total participations on the Section side (a section can't exist without a course and without an instructor).

Counting Section–Instructor pairs (1:N, many side = Section):

• Maximum: each section has exactly one instructor → at most one pair per section → $R_{\max }=|\text{Section}|=20$. It is not $8\times 20=160$, because the instructor (the "one" side) is not what bounds the count — the many side does.
• Actual: every section must have an instructor (total participation) → all 20 sections contribute exactly one pair → $20$ pairs. The instructor count (8) doesn't change the total — it only affects how those 20 pairs are distributed across the 8 instructors.

New cardinality: Instructor–Section becomes M:N (a section has many instructors and an instructor has many sections). Previously it was 1:N. Why the old column breaks: Section.instructor_id is a single FK — it can name exactly one instructor per section. To hold "several instructors" it would have to store a list in one cell → violates 1NF, and the trap is to write "3,7" into instructor_id. Corrected design — externalise into a junction table:

• Course(course_id PK, title)
• Instructor(instructor_id PK, name)
• Section(section_id PK, course_id FK → Course NOT NULL) — the instructor_id column is removed.
• Teaches(section_id FK, instructor_id FK, PRIMARY KEY(section_id, instructor_id)) — the M:N junction, one row per (section, instructor) pair.

Why the composite key (section_id, instructor_id): each specific instructor teaches a specific section at most once, so that pair is uniquely identifying and minimal. Total participation preserved: "every section must have at least one instructor" is now a minimum-one rule on the junction. A raw FK+NOT NULL can no longer express it (the FK moved out of Section); this must be enforced at the application layer or with a trigger, because a plain relational schema cannot force "≥1 matching junction row". Recognising that limit is the mastery point.