Case Study 1: Dirt Road Driving

exercises
tutorial
case-study
databases
er-model
eer-model

Group case study applying the ER/EER model in 2026-02-23-conceptual-database-design-and-the-er-model and 2026-03-02-weak-entities-the-eer-model-and-design-choices to a real-world brief. See er-diagram-notation and relationship-constraints for reference.

Section A — EER diagram

Dirt Road Driving is a rural rideshare company, gathered from correspondence with the company’s Director of Innovation:

  • Users: unique id, full Name, DOB. A user can register any number of emergency contacts (Name unique per user, Email, Phone).
  • Staff: unique staffID, full Name (first/middle/last), DOB, Phone(s). Split into Administration (DeskNumber) and Driver (Licence) — a staff member can be both at once (overlapping).
  • Vehicles: VIN, Make, Model. Disjointly splits into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive).
  • Trip: a user, a vehicle, and a driver (never permanently assigned to one vehicle — recorded per trip), plus a BookingTime and start/end timestamps (used to derive Fare); a trip can record multiple stop Locations. After a trip, the user separately rates the driver and the vehicle (integer out of 10, stored as RATES relationships) — a repeat rating updates the existing one rather than creating a new one.

USER (key ID, Name, DOB) —1:N (total on the weak-entity side) HAS→ EMERGENCYCONTACTS (weak entity, partial key Name, Email, Phone). STAFF (key ID, Name, DOB, multivalued Phone) overlappingly (o) specialises into DRIVER (Licence) and ADMIN (DeskNumber). VEHICLES (key VIN, Make, Model) disjointly (d) specialises into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive).

USER —M:N TRIP←DRIVER, and VEHICLES —M:N TRIP (a trip is identified by USER+DRIVER+VEHICLES+BookingTime, with StartTime, EndTime, multivalued StopLocation, and derived Fare). Separately, USER —M:N RATES (Number, out of 10)→ DRIVER, and USER —M:N RATES→ VEHICLES.

flowchart TD
    USER[USER] ---|"1"| HAS{HAS}
    HAS ===|"N"| EMERGENCYCONTACTS[[EMERGENCYCONTACTS]]

    STAFF[STAFF] --- o1(("o"))
    o1 --- DRIVER[[DRIVER]]
    o1 --- ADMIN[[ADMIN]]

    VEHICLES[VEHICLES] --- d1(("d"))
    d1 --- FourWD[["4WD"]]
    d1 --- TwoWD[["2WD"]]

    USER ---|"N"| TRIP{TRIP}
    DRIVER ---|"N"| TRIP
    VEHICLES ---|"N"| TRIP

    USER ---|"M"| RATESD{RATES}
    RATESD ---|"N"| DRIVER
    USER ---|"M"| RATESV{RATES}
    RATESV ---|"N"| VEHICLES

    IDu(["ID (key)"]) --- USER
    NameEC(["Name (partial key)"]) --- EMERGENCYCONTACTS
    IDs(["ID (key)"]) --- STAFF
    Licence(["Licence"]) --- DRIVER
    DeskNumber(["DeskNumber"]) --- ADMIN
    VIN(["VIN (key)"]) --- VEHICLES
    BookingTime(["BookingTime (partial key)"]) --- TRIP
    Fare(["Fare (derived)"]) --- TRIP

Section B — Critical thinking

Propose a change to the EER diagram that would help the company make a new data-informed decision not currently possible.

This is open-ended — no single correct answer. Consider what a rural rideshare operator might want to analyse that today’s schema can’t answer (e.g. tracking incident reports per trip, recording each vehicle’s service/ maintenance history, or logging cancelled vs. completed trips separately) and how you’d extend the entities/relationships above to capture it.