Question 6 of 8: Relational algebra — Passengers/Reservations/Flights

Question 6: Relational algebra — Passengers/Reservations/Flights (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. Passengers(Name, Address, Age), Reservations(Name, FlightNum, Seat), Flights(FlightNum, DepartCity, DestinationCity, DepartureTime, ArrivalTime, MinutesLate).

(a) passengers with a reservation on a flight >30 min late — select the late flights, natural-join against Reservations on FlightNum, project the name.

$$\pi_{Name}\big(\sigma_{MinutesLate>30}(Flights) \bowtie Reservations\big)$$

(b) passengers with reservations on ALL flights >60 min late — the classic relational-algebra division: the set of (Name, FlightNum) reservation pairs divided by the set of late-flight numbers keeps exactly the names paired with every one of them.

$$LateFlights = \pi_{FlightNum}(\sigma_{MinutesLate>60}(Flights))$$

$$\pi_{Name,\,FlightNum}(Reservations) \div LateFlights$$

Note the edge case built into division: if no flight was more than 60 minutes late, $LateFlights$ is empty and the quotient returns every passenger who holds any reservation at all (the "for all" condition is vacuously true); an exam answer should state whether that behaviour is intended or guard against it.

(c) pairs of passengers who are the same age — a self-join via two renamed copies of Passengers, matching on Age while excluding a passenger pairing with themself (and, to avoid printing the same unordered pair twice, keeping only one name-ordering direction).

$$\pi_{P1.Name,\,P2.Name}\Big(\sigma_{P1.Age = P2.Age\ \wedge\ P1.Name < P2.Name}\big(\rho_{P1}(Passengers) \times \rho_{P2}(Passengers)\big)\Big)$$