Flights Booking Schema and Passenger Queries

Problem Design a relational schema for a flights booking system, then write SQL queries answering passenger-related questions — which passengers are on a given flight, and which flights a given passenger has booked.

Schema

• flights(id, flight_no, origin, destination, departure_time, arrival_time)
• passengers(id, name, email)
• bookings(id, flight_id, passenger_id, seat_no, booking_time) — the junction table that makes Passengers ↔ Flights many-to-many
• Airports and Airlines enter if the interviewer pushes on normalisation: origin / destination become FKs into airports(code, city, name)

Output

• DDL for the core entities with keys and relationships stated explicitly
• One row per passenger for the "who is on flight X" query; one row per flight for the "what has passenger Y booked" query

Example

• "Find all passengers on flight X" → passengers JOIN bookings on passenger_id, filtered by flight_id = X
• "Find all flights booked by passenger Y" → the mirror join, filtering on passenger_id = Y

Areas the interviewer probes

• Getting cardinality right (seat_no / booking_time belong on the junction table), indexing the FKs, a unique constraint on (flight_id, seat_no), recurring flight vs dated instance, and modelling cancellation as status not delete.