Question 4 of 8

Question 4 (10+10=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.

Given. A trip-reservation system: a trip reservation is a sequence of flight reservations, each referring to a specific flight and possibly a substitute flight, with an optional seat, a record locator, a reservation date with a purchase deadline, one or more payments, a booking agent (airline or travel-agency employee), and an optional frequent-flyer account.

Find. A complete ER model: entities, attributes, relationships with cardinality/participation, role names where a relationship uses the same entity type more than once, and any weak entity / identifying relationship.

Approach. Model TripReservation as identified by its own natural key (the record locator); model FlightReservation as a weak entity owned by TripReservation (a "sequence" implies a partial key such as a sequence number, since a flight reservation has no identity outside its trip); model the substitute-flight rule with a second, role-named relationship rather than folding it into the primary "refers to" relationship; and model Agent as an EER specialization into two disjoint subclasses, since the question explicitly asks for role names, weak entities and identifying relationships where appropriate — these are exactly the constructs the source's own wording is steering the answer toward.

Entities. Passenger (PassengerID key) — the source does not spell out passenger attributes beyond being the frequent-flyer-account owner, so a minimal key-only entity is assumed (flagged below). TripReservation (RecordLocator key, ReservationDate) — "the airlines use record locators to find a particular trip reservation quickly and unambiguously" is precisely a statement that RecordLocator is the key. FlightReservation (weak; partial key SequenceNumber, SeatNumber) — "a sequence of flight reservations" gives each one an ordinal position within its trip. Flight (FlightNumber key) — attributes beyond the key are not given in the source and are not needed for the relationships asked about. FrequentFlyerAccount (AccountNumber key). Payment (PaymentID key, Amount, Method). Agent (AgentID key), specialized (disjoint, total) into AirlineAgent and TravelAgencyAgent — "an agent, who either works for an airline or a travel agency" is a textbook disjoint total specialization (every agent is in exactly one subclass).

Relationships. Owns: Passenger (1, partial — not every passenger has an account) – FrequentFlyerAccount (1, total — "the owner of the frequent flyer account must be the passenger" makes every account belong to exactly one passenger). Makes: Passenger (1) – TripReservation (N, total on TripReservation — every reservation is made by a passenger; partial on Passenger). Includes (identifying, weak): TripReservation (1) – FlightReservation (N, total — a flight reservation cannot exist outside its trip). RefersTo, role name "primary flight": FlightReservation (N, total — every flight reservation names exactly one flight) – Flight (1). SubstitutedBy, role name "substitute flight" (a SECOND, optional relationship to the same Flight entity, distinguishing "another flight is substituted" from the original booking): FlightReservation (0 or 1, partial) – Flight (1). Pays: TripReservation (1) – Payment (N, total on Payment, "multiple payments may be made for a trip"). Books: Agent (1) – TripReservation (N, total — "a trip is reserved by an agent" is mandatory).