N.4. Schema Objects
- N.4.1. List of Relations
- N.4.2. View
bookings.aircrafts
- N.4.3. Table
bookings.aircrafts_data
- N.4.4. View
bookings.airports
- N.4.5. Table
bookings.airports_data
- N.4.6. Table
bookings.boarding_passes
- N.4.7. Table
bookings.bookings
- N.4.8. Table
bookings.flights
- N.4.9. Table
bookings.seats
- N.4.10. Table
bookings.ticket_flights
- N.4.11. Table
bookings.tickets
- N.4.12. View
bookings.flights_v
- N.4.13. View
bookings.routes
- N.4.14. Function
bookings.now
- N.4.15. Function
bookings.lang
- N.4.2. View
N.4.1. List of Relations
Name | Type | Small | Medium | Big | Description -----------------+---------------+--------+--------+--------+------------------------- aircrafts | view | | | | Aircraft aircrafts_data | table | 16 kB | 16 kB | 16 kB | Aircraft (translations) airports | view | | | | Airports airports_data | table | 56 kB | 56 kB | 56 kB | Airports (translations) boarding_passes | table | 31 MB | 102 MB | 427 MB | Boarding passes bookings | table | 13 MB | 30 MB | 105 MB | Bookings flights | table | 3 MB | 6 MB | 19 MB | Flights flights_v | view | | | | Flights routes | view | | | | Routes seats | table | 88 kB | 88 kB | 88 kB | Seats ticket_flights | table | 64 MB | 145 MB | 516 MB | Flight segments tickets | table | 47 MB | 107 MB | 381 MB | Tickets
N.4.2. View bookings.aircrafts
Each aircraft model is identified by its three-digit code (aircraft_code
). The view also includes the name of the aircraft model (model
) and the maximal flying distance, in kilometers (range
).
The value of the model
field is selected according to the chosen language. See Section N.4.15 for details.
Column | Type | Modifiers | Description ---------------+---------+--------------+----------------------------------- aircraft_code | char(3) | not null | Aircraft code, IATA model | text | not null | Aircraft model range | integer | not null | Maximal flying distance, km View definition: SELECT ml.aircraft_code, ml.model ->> lang() AS model, ml.range FROM aircrafts_data ml;
N.4.3. Table bookings.aircrafts_data
This is the base table for the aircrafts
view. The model
field of this table contains translations of aircraft models to different languages, in the JSONB format. In most cases, this table is not supposed to be used directly.
Column | Type | Modifiers | Description ---------------+---------+--------------+----------------------------------- aircraft_code | char(3) | not null | Aircraft code, IATA model | jsonb | not null | Aircraft model range | integer | not null | Maximal flying distance, km Indexes: PRIMARY KEY, btree (aircraft_code) Check constraints: CHECK (range > 0) Referenced by: TABLE "flights" FOREIGN KEY (aircraft_code) REFERENCES aircrafts_data(aircraft_code) TABLE "seats" FOREIGN KEY (aircraft_code) REFERENCES aircrafts_data(aircraft_code) ON DELETE CASCADE
N.4.4. View bookings.airports
An airport is identified by a three-letter code (airport_code
) and has a name (airport_name
).
There is no separate entity for the city, but there is a city name (city
) to identify the airports of the same city. The view also includes coordinates (coordinates
) and the time zone (timezone
).
The values of the airport_name
and city
fields are selected according to the chosen language. See Section N.4.15 for details.
Column | Type | Modifiers | Description --------------+---------+--------------+-------------------------------------------- airport_code | char(3) | not null | Airport code airport_name | text | not null | Airport name city | text | not null | City coordinates | point | not null | Airport coordinates (longitude and latitude) timezone | text | not null | Airport time zone View definition: SELECT ml.airport_code, ml.airport_name ->> lang() AS airport_name, ml.city ->> lang() AS city, ml.coordinates, ml.timezone FROM airports_data ml;
N.4.5. Table bookings.airports_data
This is the base table for the airports
view. This table contains translations of airport_name
and city
values to different languages, in the JSONB format. In most cases, this table is not supposed to be used directly.
Column | Type | Modifiers | Description --------------+---------+--------------+-------------------------------------------- airport_code | char(3) | not null | Airport code airport_name | jsonb | not null | Airport name city | jsonb | not null | City coordinates | point | not null | Airport coordinates (longitude and latitude) timezone | text | not null | Airport time zone Indexes: PRIMARY KEY, btree (airport_code) Referenced by: TABLE "flights" FOREIGN KEY (arrival_airport) REFERENCES airports_data(airport_code) TABLE "flights" FOREIGN KEY (departure_airport) REFERENCES airports_data(airport_code)
N.4.6. Table bookings.boarding_passes
At the time of check-in, which opens twenty-four hours before the scheduled departure, the passenger is issued a boarding pass. Like the flight segment, the boarding pass is identified by the ticket number and the flight number.
Boarding passes are assigned sequential numbers (boarding_no
), in the order of check-ins for the flight (this number is unique only within the context of a particular flight). The boarding pass specifies the seat number (seat_no
).
Column | Type | Modifiers | Description -------------+------------+--------------+-------------------------- ticket_no | char(13) | not null | Ticket number flight_id | integer | not null | Flight ID boarding_no | integer | not null | Boarding pass number seat_no | varchar(4) | not null | 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)
N.4.7. Table bookings.bookings
Passengers book tickets for themselves, and, possibly, for several other passengers, in advance (book_date
, not earlier than one month before the flight). The booking is identified by its number (book_ref
, a six-position combination of letters and digits).
The total_amount
field stores the total cost of all tickets included into the booking, for all passengers.
Column | Type | Modifiers | Description --------------+---------------+--------------+--------------------------- book_ref | char(6) | not null | Booking number book_date | timestamptz | not null | Booking date total_amount | numeric(10,2) | not null | Total booking cost Indexes: PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
N.4.8. Table bookings.flights
The natural key of the bookings.flights
table consists of two fields — flight_no
and scheduled_departure
. To make foreign keys for this table more compact, a surrogate key is used as the primary key (flight_id
).
A flight always connects two points — the airport of departure (departure_airport
) and arrival (arrival_airport
). There is no such entity as a “connecting flight”: if there are no non-stop flights from one airport to another, the ticket simply includes several required flight segments.
Each flight has a scheduled date and time of departure (scheduled_departure
) and arrival (scheduled_arrival
). The actual departure time (actual_departure
) and arrival time (actual_arrival
) can differ: the difference is usually not very big, but sometimes can be up to several hours if the flight is delayed.
Flight status (status
) can take one of the following values:
-
Scheduled
The flight is available for booking. It happens one month before the planned departure date; before that time, there is no entry for this flight in the database.
-
On Time
The flight is open for check-in (in twenty-four hours before the scheduled departure) and is not delayed.
-
Delayed
The flight is open for check-in (in twenty-four hours before the scheduled departure) but is delayed.
-
Departed
The aircraft has already departed and is airborne.
-
Arrived
The aircraft has reached the point of destination.
-
Cancelled
The flight is canceled.
Column | Type | Modifiers | Description ---------------------+-------------+--------------+----------------------------- flight_id | serial | not null | Flight ID flight_no | char(6) | not null | Flight number scheduled_departure | timestamptz | not null | Scheduled departure time scheduled_arrival | timestamptz | not null | Scheduled arrival time departure_airport | char(3) | not null | Airport of departure arrival_airport | char(3) | not null | Airport of arrival status | varchar(20) | not null | Flight status aircraft_code | char(3) | not null | Aircraft code, IATA actual_departure | timestamptz | | Actual departure time actual_arrival | timestamptz | | Actual arrival time Indexes: PRIMARY KEY, btree (flight_id) UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure) Check constraints: CHECK (scheduled_arrival > scheduled_departure) CHECK ((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL AND actual_arrival IS NOT NULL) AND (actual_arrival > actual_departure))) CHECK (status IN ('On Time', 'Delayed', 'Departed', 'Arrived', 'Scheduled', 'Cancelled')) Foreign-key constraints: FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) FOREIGN KEY (arrival_airport) REFERENCES airports(airport_code) FOREIGN KEY (departure_airport) REFERENCES airports(airport_code) Referenced by: TABLE "ticket_flights" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
N.4.9. Table bookings.seats
Seats define the cabin configuration of each aircraft model. Each seat is defined by its number (seat_no
) and has an assigned travel class (fare_conditions
): Economy
, Comfort
or Business
.
Column | Type | Modifiers | Description -----------------+-------------+--------------+-------------------- aircraft_code | char(3) | not null | Aircraft code, IATA seat_no | varchar(4) | not null | Seat number fare_conditions | varchar(10) | not null | Travel class Indexes: PRIMARY KEY, btree (aircraft_code, seat_no) Check constraints: CHECK (fare_conditions IN ('Economy', 'Comfort', 'Business')) Foreign-key constraints: FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE
N.4.10. Table bookings.ticket_flights
A flight segment connects a ticket with a flight and is identified by their numbers.
Each flight has its cost (amount
) and travel class (fare_conditions
).
Column | Type | Modifiers | Description -----------------+---------------+--------------+--------------------- ticket_no | char(13) | not null | Ticket number flight_id | integer | not null | Flight ID fare_conditions | varchar(10) | not null | Travel class amount | numeric(10,2) | not null | Travel cost Indexes: PRIMARY KEY, btree (ticket_no, flight_id) Check constraints: CHECK (amount >= 0) CHECK (fare_conditions IN ('Economy', 'Comfort', 'Business')) Foreign-key constraints: FOREIGN KEY (flight_id) REFERENCES flights(flight_id) FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no) Referenced by: TABLE "boarding_passes" FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)
N.4.11. Table bookings.tickets
A ticket has a unique number (ticket_no
) that consists of 13 digits.
The ticket includes a passenger ID (passenger_id
) — the identity document number, — their first and last names (passenger_name
), and contact information (contact_data
).
Neither the passenger ID, nor the name is permanent (for example, one can change the last name or passport), so it is impossible to uniquely identify all tickets of a particular passenger.
Column | Type | Modifiers | Description ----------------+-------------+--------------+----------------------------- ticket_no | char(13) | not null | Ticket number book_ref | char(6) | not null | Booking number passenger_id | varchar(20) | not null | Passenger ID passenger_name | text | not null | Passenger name contact_data | jsonb | | Passenger contact information Indexes: PRIMARY KEY, btree (ticket_no) Foreign-key constraints: FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
N.4.12. View bookings.flights_v
There is a flights_v
view over the flights
table that provides additional information:
Details about the airport of departure —
departure_airport
,departure_airport_name
,departure_city
Details about the airport of arrival —
arrival_airport
,arrival_airport_name
,arrival_city
Local departure time —
scheduled_departure_local
,actual_departure_local
Local arrival time —
scheduled_arrival_local
,actual_arrival_local
Flight duration —
scheduled_duration
,actual_duration
.
Column | Type | Description ---------------------------+-------------+-------------------------------------- flight_id | integer | Flight ID flight_no | char(6) | Flight number scheduled_departure | timestamptz | Scheduled departure time scheduled_departure_local | timestamp | Scheduled departure time, | | local time at the point of departure scheduled_arrival | timestamptz | Scheduled arrival time scheduled_arrival_local | timestamp | Scheduled arrival time, | | local time at the point of destination scheduled_duration | interval | Scheduled flight duration departure_airport | char(3) | Departure airport code departure_airport_name | text | Departure airport name departure_city | text | City of departure arrival_airport | char(3) | Arrival airport code arrival_airport_name | text | Arrival airport name arrival_city | text | City of arrival status | varchar(20) | Flight status aircraft_code | char(3) | Aircraft code, IATA actual_departure | timestamptz | Actual departure time actual_departure_local | timestamp | Actual departure time, | | local time at the point of departure actual_arrival | timestamptz | Actual arrival time actual_arrival_local | timestamp | Actual arrival time, | | local time at the point of destination actual_duration | interval | Actual flight duration
N.4.13. View bookings.routes
The bookings.flights
table contains some redundancies, which you can use to single out route information (flight number, airports of departure and destination) that does not depend on the exact flight dates.
Such information is shown in the routes
view.
Column | Type | Description ------------------------+-----------+------------------------------------- flight_no | char(6) | Flight number departure_airport | char(3) | Departure airport code departure_airport_name | text | Departure airport name departure_city | text | City of departure arrival_airport | char(3) | Arrival airport code arrival_airport_name | text | Arrival airport name arrival_city | text | City of arrival aircraft_code | char(3) | Aircraft code, IATA duration | interval | Flight duration days_of_week | integer[] | Days of the week on which flights are performed
N.4.14. Function bookings.now
The demo database contains “snapshots” of data — similar to a backup copy of a real system captured at some point in time. For example, if a flight has the Departed
status, it means that the aircraft had already departed and was airborne at the time of the backup copy.
The “snapshot” time is saved in the bookings.now()
function. You can use this function in demo queries for cases where you would use the now()
function in a real database.
In addition, the return value of this function determines the version of the demo database. The latest version available is of August 15, 2017.
N.4.15. Function bookings.lang
Some fields in the demo database are available in English and Russian. Translations to other languages are not provided, but are easy to add. The bookings.lang
returns the value of the bookings.lang
parameter, that is, the language in which these fields will be displayed.
This function is used in the aircrafts
and airports
views and is not intended to be used directly in queries.