Case Study 3: Dirt Road Driving

exercises
tutorial
case-study
databases
relational-model
er-mapping

Group case study applying ER-to-relational mapping from 2026-03-16-er-to-relational-mapping to a revised Dirt Road Driving EER diagram (the EER diagram itself is week2-tutorial-case-study-1-dirt-road-driving’s, refined per that case study’s open-ended Section B). See relational-mapping-notation for reference.

Section A — Full relational mapping

Elaine’s team maps the revised EER diagram (User, Staff disjointly specialising into Driver/Admin, Vehicles disjointly specialising into 4WD/2WD, weak entities EmergencyContact and TripStop, the ternary-ish Trip, and the UserRatesDriver/ UserRatesVehicle relationships) to a full relational schema.

User [id, dob, firstName, middleName, lastName]

Staff [id, dob, firstName, middleName, lastName]

Vehicles [vin, make, model]

Driver [id, licence]
Driver.id references Staff.id

Admin [id, deskNumber]
Admin.id references Staff.id

4WD [vin, rideHeight, wheelType]
4WD.vin references Vehicles.vin

2WD [vin, frontWheelDrive]
2WD.vin references Vehicles.vin

EmergencyContact [userID, name, email, phone]
EmergencyContact.userID references User.id

StaffPhone [id, phone]
StaffPhone.id references Staff.id

Trip [userID, driverID, vin, bookingTime, startTime, endTime]
Trip.userID references User.id
Trip.driverID references Driver.id
Trip.vin references Vehicles.vin

TripStop [userID, driverID, vin, bookingTime, location]
TripStop.{userID, driverID, vin, bookingTime} references Trip.{userID, driverID, vin, bookingTime}

UserRatesDriver [userID, driverID, rating]
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id

UserRatesVehicle [userID, vin, rating]
UserRatesVehicle.userID references User.id
UserRatesVehicle.vin references Vehicles.vin

Key points:

  • Driver/Admin and 4WD/2WD are standard disjoint-subclass mappings — each subclass’s primary key is its superclass’s key, plus a foreign key back to the superclass.
  • EmergencyContact is a weak entity owned by User — its real key is (userID, name) (owner’s key + its own partial key name), not just name alone (a name is only guaranteed unique per user).
  • StaffPhone maps Staff’s multivalued Phone attribute — its primary key is (id, phone) (the multivalued attribute joins the owner’s key to form the key, per the multivalued-attribute mapping rule), which makes sense of why phone isn’t shown as a single extra column on Staff itself.
  • Trip is identified by the combination of userID, driverID, vin, and bookingTime — a trip is only unique per that combination (the same user/driver/vehicle triple could recur across different bookings).
  • TripStop is a weak entity too, owned by Trip — it needs Trip’s entire composite key as a foreign key, plus its own partial key location.
  • UserRatesDriver/UserRatesVehicle are both M:N relationships (each gets its own new relation, combining both sides’ foreign keys as its primary key, plus the relationship’s own rating attribute).

Section B — Spot the mistakes

A junior admin partially mapped the same diagram:

User [id, dob, firstName, middleName, lastName]

Staff [id, dob, firstName, middleName, lastName]

Vehicles [vin, make, model]

EmergencyContact [name, userID, email, phone]
EmergencyContact.userID references User.id

Trip [userID, driverID, vin, bookingTime, startTime, endTime]
Trip.userID references User.id
Trip.driverID references Staff.id
Trip.vin references Vehicles.vin

UserRatesDriver [userID, driverID, rating]
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id

Driver [id, licence]
Driver.id references Staff.id

4WD [vin, make, model, rideHeight, wheelType]
4WD.vin references Vehicles.vin

StaffPhone [id, phone]
StaffPhone.id references Staff.id

Find 5 mistakes (ignore “missing tables” — some relations from Section A are simply left out here, and that’s not one of the 5).

  1. EmergencyContact’s primary key is missing userID. As a weak entity, its key must be (userID, name), not name alone — otherwise two different users couldn’t each have an emergency contact with the same name (e.g. two users both listing a contact named “Mum”).
  2. Trip’s primary key is missing bookingTime. Without it, a user couldn’t take two separate trips with the same driver and vehicle — bookingTime is exactly what distinguishes repeat trips between the same pair.
  3. Trip.driverID incorrectly references Staff.id instead of Driver.id. This would let an Admin-only staff ID (someone who isn’t a Driver at all) be entered as a trip’s driver, corrupting the data and undermining any audit that assumes driverID always names an actual driver.
  4. 4WD redundantly repeats make/model. These are already stored on Vehicles (the superclass) — duplicating them wastes space and risks the two copies going out of sync.
  5. StaffPhone’s primary key is missing phone. As the mapping of a multivalued attribute, the key must be (id, phone) — without phone in the key, each staff member could only ever have one phone number on file, defeating the purpose of the multivalued attribute allowing several.