Applied Class 2: Entity Relationship Diagrams

exercises
tutorial
databases
er-model
weak-entities
eer-model

Practice for 2026-03-02-weak-entities-the-eer-model-and-design-choices. See er-diagram-notation and relationship-constraints for reference.

Section A — ERD components

Given a COMPANY ER diagram (EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT, plus the relationships MANAGES/WORKS_FOR/CONTROLS/ WORKS_ON/SUPERVISION/DEPENDENTSOF), find an example of each ERD component.

Component Example
Strong entity EMPLOYEE, DEPARTMENT, PROJECT
Weak entity DEPENDENT
(Candidate) key EMPLOYEE.Ssn; DEPARTMENT.Name/Number; PROJECT.Name/Number; DEPENDENT.{Ssn, Name}
Partial key DEPENDENT.Name
Composite attribute EMPLOYEE.Name (→ FirstName, MiddleInitial, LastName)
Derived attribute DEPARTMENT.Number_of_employees
Multivalued attribute DEPARTMENT.Locations
1-1 relationship MANAGES
1-N relationship CONTROLS, WORKS_FOR, SUPERVISION
M-N relationship WORKS_ON
Identifying relationship DEPENDENTSOF 

flowchart TD
    EMPLOYEE[EMPLOYEE] ---|"1 supervisor"| SUPERVISION{SUPERVISION}
    SUPERVISION ---|"N supervisee"| EMPLOYEE
    EMPLOYEE ===|"N"| WORKS_FOR{WORKS_FOR}
    WORKS_FOR ---|"1"| DEPARTMENT[DEPARTMENT]
    EMPLOYEE ---|"1"| MANAGES{MANAGES}
    MANAGES ===|"1"| DEPARTMENT
    DEPARTMENT ---|"1"| CONTROLS{CONTROLS}
    CONTROLS ---|"N"| PROJECT[PROJECT]
    EMPLOYEE ---|"M"| WORKS_ON{WORKS_ON}
    WORKS_ON ---|"N"| PROJECT
    EMPLOYEE ---|"1"| DEPENDENTSOF{DEPENDENTSOF}
    DEPENDENTSOF ===|"N"| DEPENDENT[[DEPENDENT]]

    Ssn(["Ssn (key)"]) --- EMPLOYEE
    NameE(["Name"]) --- EMPLOYEE
    Salary(["Salary"]) --- EMPLOYEE

    DeptKey(["Number/Name (key)"]) --- DEPARTMENT
    NumEmp(["Number_of_employees (derived)"]) --- DEPARTMENT
    Locations(["Locations (multi)"]) --- DEPARTMENT

    ProjKey(["Number/Name (key)"]) --- PROJECT

    DepName(["Name (partial key)"]) --- DEPENDENT

Section B — ERD modelling assumptions

Uses a health/vaccination EER diagram: HEALTHWORKER —1:N OVERSEEN→ HEALTH (subclass of PARTICIPANT, alongside an overlapping AGE subclass); VACCINE (ID, composite Key = Brand+Name) has a recursive WITH relationship (roles First/Second, both partial) and —1:N HAS→ BATCH (weak entity, partial key Number).

Evaluate each statement as Correct or Incorrect.

B1 — Each vaccine must have a second dose or a preceding dose.

B2 — A participant can be overseen by several healthcare workers.

B3 — Every participant in the HEALTH subclass is overseen by a health worker during vaccination.

B4 — A vaccine can only be uniquely identified by the combination of its Brand and Name.

B5 — A participant can be in both the HEALTH subclass and the AGE subclass.

B6 — A BATCH is uniquely identified by only its number.

  • B1 Incorrect — the recursive WITH relationship lets a vaccine be recorded as a first or second dose, but participation from both sides is partial: a vaccine doesn’t have to be part of a two-shot program.
  • B2 Incorrect — OVERSEEN is 1:N from HEALTHWORKER to HEALTH: one healthcare worker can oversee many participants, but each participant has at most one overseeing healthcare worker.
  • B3 Correct — OVERSEEN has total participation from the HEALTH side.
  • B4 Incorrect — VACCINE has two candidate keys: the composite Key (Brand+Name), or its ID.
  • B5 Correct — HEALTH and AGE are overlapping subclasses of PARTICIPANT — an instance can be a member of both at once.
  • B6 Incorrect — BATCH is a weak entity; its real key is Batch Number combined with its owner VACCINE’s key (either ID or Key).

flowchart TD
    PARTICIPANT[PARTICIPANT] --- o1(("o"))
    o1 --- HEALTH[[HEALTH]]
    o1 --- AGE[[AGE]]
    HEALTHWORKER[HEALTHWORKER] ---|"1"| OVERSEEN{OVERSEEN}
    OVERSEEN ===|"N"| HEALTH

    VACCINE[VACCINE] ---|"First"| WITH{WITH}
    WITH ---|"Second"| VACCINE
    VACCINE ---|"1"| HAS0{HAS}
    HAS0 ===|"N"| BATCH[[BATCH]]

    IDv(["ID (key)"]) --- VACCINE
    VKey(["Key (key)"]) --- VACCINE
    Brand(["Brand"]) --- VKey
    VName(["Name"]) --- VKey
    Number(["Number (partial key)"]) --- BATCH

Section C — Conceptual modelling

Question 1 — Cinema

Movies (unique Name, Description, RunningTime); attendants (unique StaffID, Name, Phone); customers (unique ID, Name, Email); viewings record a Timestamp (unique per customer+movie) and any Snacks purchased; exactly one attendant oversees each viewing.

MOVIE —1:N HAS→ VIEWING (Timestamp, multivalued Snack) —1:N OVERSEES←ATTENDANT; CUSTOMER —1:N HAS→ VIEWING. VIEWING’s key is the composite of its owning CUSTOMER+MOVIE+Timestamp (it’s a weak entity in the fuller reading of the UoD, or an M:N relationship with a Timestamp key attribute, depending on the design choice made).

flowchart TD
    MOVIE[MOVIE] ---|"1"| HAS1{HAS}
    HAS1 ---|"N"| VIEWING[[VIEWING]]
    CUSTOMER1[CUSTOMER] ---|"1"| HAS2{HAS}
    HAS2 ---|"N"| VIEWING
    VIEWING ---|"N"| OVERSEES{OVERSEES}
    OVERSEES ---|"1"| ATTENDANT[ATTENDANT]

    NameM(["Name (key)"]) --- MOVIE
    Description(["Description"]) --- MOVIE
    RunningTime(["RunningTime"]) --- MOVIE
    Timestamp(["Timestamp (partial key)"]) --- VIEWING
    Snack(["Snack (multi)"]) --- VIEWING
    StaffID(["StaffID (key)"]) --- ATTENDANT
    IDc(["ID (key)"]) --- CUSTOMER1

Question 2 — Medical clinic

Patients (PatientID) and doctors (LicenceNo); each doctor is either a GP or a specialist (never both), specialists also record their SpecialisationArea; doctors work in clinics (RegistrationNo), one clinic can have several doctors; some clinics are specialist clinics, each run by exactly one specialist doctor; patients make appointments (Fee) to consult doctors.

PATIENT (PatientID) —M:N CONSULTS (Fee)→ DOCTOR (LicenceNo), which disjointly (d) specialises into GP/SPECIALIST (SpecialisationArea). DOCTOR —M:N WORKSIN→ CLINIC (RegistrationNo), which has a subclass SPECIALIST CLINIC —1:N RUNS←SPECIALIST.

Assumptions: every patient in the DB has made an appointment; every clinic has at least one doctor.

flowchart TD
    PATIENT[PATIENT] ---|"M"| CONSULTS{CONSULTS}
    CONSULTS ---|"N"| DOCTOR[DOCTOR]
    DOCTOR --- d(("d"))
    d --- GP[[GP]]
    d --- SPECIALIST[[SPECIALIST]]
    DOCTOR ---|"M"| WORKSIN{WORKSIN}
    WORKSIN ---|"N"| CLINIC[CLINIC]
    CLINIC --- SPECIALISTCLINIC[["SPECIALIST CLINIC"]]
    SPECIALIST ---|"1"| RUNS{RUNS}
    RUNS ---|"N"| SPECIALISTCLINIC

    PatientID(["PatientID (key)"]) --- PATIENT
    LicenceNo(["LicenceNo (key)"]) --- DOCTOR
    Fee(["Fee"]) --- CONSULTS
    SpecArea(["SpecialisationArea"]) --- SPECIALIST
    RegNo(["RegistrationNo (key)"]) --- CLINIC

Section D — Additional resources (not covered in the applied class)

Question 1 — Bubble tea shop

BUBBLETEA (unique Name, Description, Ingredients, Price); customers sign up with a BubbleID, Name, Phone, Email; each order (BUYS) is timestamped with a PrepTime; customers can friend other customers.

BUBBLETEA —N:M BUYS (PrepTime, key Timestamp)→ CUSTOMER (BubbleID); CUSTOMER has a recursive M:N FRIENDS relationship with itself (roles Requestor/Requestee).

flowchart LR
    BUBBLETEA[BUBBLETEA] ---|"N"| BUYS{BUYS}
    BUYS ---|"M"| CUSTOMER2[CUSTOMER]
    CUSTOMER2 ---|"Requestor"| FRIENDS{FRIENDS}
    FRIENDS ---|"Requestee"| CUSTOMER2

    NameB(["Name (key)"]) --- BUBBLETEA
    Description2(["Description"]) --- BUBBLETEA
    Ingredients(["Ingredients"]) --- BUBBLETEA
    Price(["Price"]) --- BUBBLETEA
    Timestamp2(["Timestamp (partial key)"]) --- BUYS
    PrepTime(["PrepTime"]) --- BUYS
    BubbleID(["BubbleID (key)"]) --- CUSTOMER2

Question 2 — Bank

Bank (composite key Code+Name, Address) has branches (Number unique per-bank, Address); a branch manages accounts and loans; every account has Type, Balance, unique Number, controlled by ≥1 customers; every loan has unique Number, Type, Amount, connected to ≥1 customers; every loan/account belongs to exactly one branch; customers have Name, Address, Phone, unique ID.

BANK (key Key = Code+Name) —1:N OPERATES→ BRANCH (weak entity, partial key Number) —1:N OFFERS→ LOAN (Number, Type, Amount); BRANCH —1:N OFFERS→ ACCOUNT (Number, Balance, Type). LOAN —M:N HAS→ CUSTOMER (ID, Name, Address, Phone); ACCOUNT —M:N HAS→ CUSTOMER, both with total participation on the LOAN/ACCOUNT side (“must be associated with… at least one customer”).

flowchart TD
    BANK[BANK] ---|"1"| OPERATES{OPERATES}
    OPERATES ===|"N"| BRANCH[[BRANCH]]
    BRANCH ---|"1"| OFFERS1{OFFERS}
    OFFERS1 ---|"N"| LOAN[LOAN]
    BRANCH ---|"1"| OFFERS2{OFFERS}
    OFFERS2 ---|"N"| ACCOUNT[ACCOUNT]
    LOAN ===|"M"| HAS3{HAS}
    HAS3 ---|"N"| CUSTOMER3[CUSTOMER]
    ACCOUNT ===|"M"| HAS4{HAS}
    HAS4 ---|"N"| CUSTOMER3

    BankKey(["Key (key)"]) --- BANK
    Code(["Code"]) --- BankKey
    BName(["Name"]) --- BankKey
    BranchNum(["Number (partial key)"]) --- BRANCH
    LoanNum(["Number (key)"]) --- LOAN
    AcctNum(["Number (key)"]) --- ACCOUNT
    IDc2(["ID (key)"]) --- CUSTOMER3