Question 3 of 8: ER model — bus company

Question 3: ER model — bus company (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Approach. Model each first-class noun as an entity, each stated fact linking two or more entities as a relationship with the stated cardinality, and use weak entities wherever the problem's own wording makes one thing dependent on another for its identity (a scheduled trip only makes sense attached to a line; an actual trip only makes sense as one week's occurrence of a scheduled trip).

Design decisions. BusType (name, num_seats) is promoted to its own entity rather than a Bus attribute, because "all buses of the same type have the same capacity" is exactly the kind of fact a shared entity enforces structurally: every Bus of a given type points at the SAME BusType row, so num_seats cannot accidentally diverge between two buses of the same type. ScheduledTrip is modelled as a weak entity owned by Line through the identifying relationship BelongsTo (total participation on ScheduledTrip, partial key (departure day, departure time)) — this captures "a set of trips is scheduled for each line every week" directly: a scheduled trip has no independent existence or identity outside its line. The trip's allowed bus types are the stated many-to-many CanUse between ScheduledTrip and BusType. Because "the list of locations... exists independently of particular trips," Location is its own entity (not an attribute of ScheduledTrip), connected via the many-to-many relationship MakesStop carrying the descriptive attributes (stop_time, sequence number) that belong to the (trip, location) combination, not to either entity alone. Employee (SIN, name, address, hourly_wage) covers drivers and other personnel uniformly, since the problem gives them identical attributes; no ISA specialization is needed because nothing driver-specific is asked for beyond appearing in the DrivenBy relationship. ActualTrip is a second weak entity, owned by ScheduledTrip through the identifying relationship Realizes (partial key: the actual calendar occurrence_date, since up to 52 actual trips per year all realize the same scheduled trip and are distinguished only by which date they occurred on); it connects via UsesBus (N:1 to Bus, total participation on ActualTrip since every realized trip used exactly one specific bus) and DrivenBy (N:1 to Employee, likewise total participation) to record which specific bus and driver were used for that one occurrence.

Constraints the ER diagram cannot capture. The rule "no trip can last more than 24 hours, so the arrival day is redundant" is a computed/derived-attribute constraint (arrival_day is functionally determined by departure_day, departure_time and the duration) — basic ER notation has no way to express that one attribute's value is always derivable from others; it can only be enforced by omitting the redundant attribute (as done here) or by an application-level check. Likewise, "the bus actually used on an ActualTrip must be one of the BusTypes permitted for its parent ScheduledTrip (via CanUse)" is a constraint that spans three different relationships (UsesBus, OfType, CanUse) and compares attribute values across them — ER diagrams can show each relationship's own cardinality but cannot express a logical implication between separate relationships.