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.

The two specialisations

STAFF (key StaffID, composite Name = FirstName/MiddleName/ LastName, DOB, multivalued Phone) specialises overlappingly (o) into DRIVER (Licence) and ADMINISTRATION (DeskNumber) — overlapping because the brief says a staff member can be both at once. VEHICLES (key VIN, Make, Model) specialises disjointly (d) into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive) — disjoint because a vehicle is one or the other. Both are drawn below in the notation of er-diagram-notation and specialisation-and-generalisation:

Figure 1: The two specialisations in EER notation: entity types are solid purple rectangles with white ALL-CAPS names, attributes are grey ellipses (key underlined, multivalued double-outlined), and a subclass is joined to the d/o circle by a line carrying the ⊂ arc, whose opening faces the superclass. The brief does not say whether every staff member must be a driver or an administrator, nor whether every vehicle must be a 4WD or a 2WD, so the line from each superclass down to its circle is drawn single (partial specialisation); a double line there would assert total specialisation, and the brief does not settle it.

Note the box shapes. A subclass is an ordinary single-line rectangle — what makes it a subclass is the ⊂ arc on the line into the d/o circle, not the shape of its box. The double-line rectangle means weak entity and nothing else, so in this answer the only double box is EMERGENCYCONTACTS below. Drawing DRIVER, ADMINISTRATION, 4WD or 2WD with a double border would assert the opposite of what they are: a subclass inherits its superclass’s key, whereas a weak entity has no key of its own at all.

The relationships

USER (key ID, Name, DOB) owns the weak entity type EMERGENCYCONTACTS (partial key Name, plus Email and Phone) through the identifying relationship HAS — 1 on the USER side, N on the contact side, and a double line on the contact side because a contact cannot exist without the user it belongs to. Name is only a partial key (unique per user, not globally), which is exactly why the mapping in week4-tutorial-case-study-3-dirt-road-driving gives the resulting relation the composite key (userID, name).

TRIP is a single ternary relationship over USER, DRIVER and VEHICLES carrying BookingTime, StartTime, EndTime, multivalued StopLocation and derived Fare — a driver is not permanently assigned to a vehicle, so the vehicle belongs to the trip rather than to the driver. Ratings are two separate RATES relationships, USER–DRIVER and USER–VEHICLES, each M:N with its own Number (out of 10); because the rating is an attribute of the relationship instance, re-rating updates the one existing tuple instead of adding a second one, which is what the brief asks for.

Figure 2: The relationship half of the answer. EMERGENCYCONTACTS is the one weak entity type — double-line rectangle, identifying (double) HAS diamond, double participation line, dash-underlined partial key Name. DRIVER and VEHICLES appear here as plain boxes; their attributes and their place in the class hierarchy are in Figure 1. Two conventions here go beyond what the brief or the notation settles: (1) the dashed underline is defined for a weak entity’s partial key, so using it on the relationship attribute BookingTime is an extension, recording that BookingTime is what distinguishes two trips with the same user, driver and vehicle — the fact the Week 4 mapping’s composite key relies on; (2) the brief does not fix the ternary cardinality ratio, so all three TRIP edges are marked N for many on every side.

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.