SQLtest: interactive SQL practice

Bookings Database (PostgreSQL)

The subject area of this database is airline flights through various airports.

ER diagram of the Bookings database

The Bookings Database contains 8 tables:

• aircrafts_data - table of aircraft.
• airports_data - table of airports.
• boarding_passes - table of boarding passes.
• bookings - table of bookings.
• flights - table of flights.
• seats - table of aircraft seats.
• ticket_flights - table of ticket to flights relations.
• tickets - table of tickets.

Learn PostgreSQL: Use, manage and build secure and scalable databases with PostgreSQL by Luca Ferrari & Enrico Pirozzi

This new edition will help you learn PostgreSQL from scratch with the latest version, providing a complete focused view on aspects like configuration, high performance, partitioning, backup, server-side programming and replication.

Table aircrafts_data

Table columns:

• aircraft_code - Unique code for each aircraft.
• model - Aircraft model name in English and Russian in JSON format.
• range - Aircraft fly range in kilometers.

Indexes:

• PRIMARY KEY, btree (aircraft_code)

Table airports_data

Table columns:

• airport_code - Unique code for each airport.
• airport_name - Airport name in English and Russian in JSON format.
• city - Airport city in English and Russian in JSON format.
• coordinates - Airport coordinates as POINT(longitude, latitude).
• timezone - Airport timezone name.

Indexes:

• PRIMARY KEY, btree (airport_code)

Table boarding_passes

Table columns:

• ticket_no - Ticket number.
• flight_id - Flight identificator.
• boarding_no - Boarding pass number.
• seat_no - Seat number.

Indexes:

• PRIMARY KEY, btree (ticket_no, flight_id)
• UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
• UNIQUE CONSTRAINT, btree (flight_id, seat_no)

Foreign-key constraints:

• FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

Table bookings

Table columns:

• book_ref - Booking number.
• book_date - Booking date.
• total_amount - Total booking cost.

Indexes:

• PRIMARY KEY, btree (book_ref)

Table flights

Table columns:

• flight_id - Flight ID.
• flight_no - Flight number.
• scheduled_departure - Scheduled departure time.
• scheduled_arrival - Scheduled arrival time.
• departure_airport - Airport of departure.
• arrival_airport - Airport of arrival.
• status - Flight status.
• aircraft_code - Aircraft code, IATA.
• actual_departure - Actual departure time.
• actual_arrival - Actual arrival time.

Indexes:

• PRIMARY KEY, btree (flight_id)
• UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure)

Table seats

Table columns:

• aircraft_code - Aircraft code, IATA.
• seat_no - Seat number.
• fare_conditions - Travel class.

Indexes:

• PRIMARY KEY, btree (aircraft_code, seat_no)

Foreign-key constraints:

• FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE

Table ticket_flights

Table columns:

• ticket_no - Ticket number.
• flight_id - Flight ID.
• fare_conditions - Travel class.
• amount - Travel cost.

Indexes:

• PRIMARY KEY, btree (ticket_no, flight_id)

Foreign-key constraints:

• FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
• FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

Table tickets

Table columns:

• ticket_no - Ticket number.
• book_ref - Booking number.
• passenger_id - Passenger ID.
• passenger_name - Passenger name.
• contact_data - Passenger contact information.

Indexes:

• PRIMARY KEY, btree (ticket_no)

Foreign-key constraints:

• FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)