Name                 |     Type      |       Description
----------------------------+---------------+-------------------------
 airplanes                  | view          | Airplane
 airplanes_data             | table         | Airplane (translations)
 airports                   | view          | Airports
 airports_data              | table         | Airports (translations)
 boarding_passes            | table         | Boarding passes
 bookings                   | table         | Bookings
 flights                    | table         | Flights
 flights_flight_id_seq      | sequence      | Flights
 routes                     | table         | Routes
 seats                      | table         | Seats
 segments                   | table         | Flights
 ticket                     | table         | Tickets
 timetable                  | view          | Timetable
    

When connecting to the base, the search_path configuration parameter is set to bookings,"$user",public, thus specifying the schema name is not required, unless you work with the bookings.now and bookings.version functions.

Each airplane model is identified by its three-digit code (airplane_code). The view also includes the name of the airplane model (model), the maximal flying distance, in kilometers (range), and the cruise speed, in kilometers per hour (speed).

The value of the model field is selected according to the chosen language. See Section M.4.16 for details.

    Column     |  Type   | Modifiers    |             Description
---------------+---------+--------------+-----------------------------------
 airplane_code | char(3) | not null     | Airplane code, IATA
 model         | text    | not null     | Airplane model
 range         | integer | not null     | Maximal flying distance, km
 speed         | integer | not null     | Cruise speed, km/h
View definition:
SELECT airplane_code,
    model ->> lang() AS model,
    range,
    speed
   FROM airplanes_data ml;
    

This is the base table for the airplanes view. The model field of this table contains translations of airplane models to different languages, in the JSONB format. In most cases, this table is not supposed to be used directly.

    Column     |  Type   | Modifiers    |             Description
---------------+---------+--------------+-----------------------------------
 airplane_code | char(3) | not null     | Airplane code, IATA
 model         | jsonb   | not null     | Airplane model
 range         | integer | not null     | Maximal flying distance, km
 speed         | integer | not null     | Cruise speed, km/h
Indexes:
    PRIMARY KEY, btree (airplane_code)
Check constraints:
    CHECK (range > 0)
    CHECK (speed > 0)
Referenced by:
   TABLE "routes" FOREIGN KEY (airplane_code)
        REFERENCES airplanes_data(airplane_code)
    TABLE "seats" FOREIGN KEY (airplane_code)
        REFERENCES airplanes_data(airplane_code) ON DELETE CASCADE
    

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 or country, but there is a city name (city) and country name (country) to identify the airports of the same city or country. The view also includes coordinates (coordinates) and the time zone (timezone).

The values of the airport_name, city, and country fields are selected according to the chosen language. See Section M.4.16 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
 country      | text    | not null     | Country
 coordinates  | point   | not null     | Airport coordinates (longitude and latitude)
 timezone     | text    | not null     | Airport time zone
View definition:
SELECT airport_code,
    airport_name ->> lang() AS airport_name,
    city ->> lang() AS city,
    country ->> lang() AS country,
    coordinates,
    timezone
   FROM airports_data ml;
    

This is the base table for the airports view. This table contains translations of airport_name, city, and country 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
 country      | jsonb   | not null     | Country
 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 "routes" FOREIGN KEY (arrival_airport)
        REFERENCES airports_data(airport_code)
    TABLE "routes" FOREIGN KEY (departure_airport)
        REFERENCES airports_data(airport_code)
    

At the time of the first flight check-in, which opens twenty-four hours before the scheduled departure, the passenger is issued the boarding passes for all flights in the ticket. The boarding pass specifies the seat number (seat_no). 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) and have the boarding time recorded on them (boarding_time).

   Column          |    Type    | Modifiers    |         Description
-------------------+------------+--------------+--------------------------
 ticket_no         | text       | not null     | Ticket number
 flight_id         | integer    | not null     | Flight ID
 seat_no           | text       | not null     | Seat number
 boarding_no       | integer    | not null     | Boarding pass number
 boarding_time     | timestamptz| not null     | Boarding time
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 segments(ticket_no, flight_id)
    

The tickets start selling 60 days prior to the flight. Passengers book tickets for themselves, and, possibly, for several other passengers, in advance (book_date). 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)
    

The natural key of the bookings.flights table consists of two fields — route_no and scheduled_departure. To make foreign keys for this table more compact, a surrogate key is used as the primary key (flight_id).

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:

        Column       |     Type    | Modifiers    |         Description
---------------------+-------------+--------------+-----------------------------
 flight_id           | integer     | not null     | Flight ID
 route_no            | text        | not null     | Route number
 status              | text        | not null     | Flight status
 scheduled_departure | timestamptz | not null     | Scheduled departure time
 scheduled_arrival   | timestamptz | not null     | Scheduled arrival time
 actual_departure    | timestamptz |              | Actual departure time
 actual_arrival      | timestamptz |              | Actual arrival time
Indexes:
   PRIMARY KEY, btree (flight_id)
    UNIQUE CONSTRAINT, btree (route_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 ( 'Scheduled', 'On Time', 'Delayed', 'Boarding',
                      'Departed', 'Arrived', 'Cancelled'))

Referenced by:
       TABLE "segments" FOREIGN KEY (flight_id)
        REFERENCES flights(flight_id)
    

A route always connects two points — the airport of departure (departure_airport) and arrival (arrival_airport). There is no such entity as an “indirect route”: if there are no non-stop flights from one airport to another, the ticket simply includes several required flight segments.

The route has a validity window (validity) of one month. The same route may operate multiple times between the same airports, with the same route_no but different airplanes and departure dates.

The flights following this route have a specific airplane model (airplane_code), the same departure time (scheduled_time, local time of the departure airport), and different days of the week (the days_of_week array, with 1 being Monday and 7 being Sunday).

        Column       |   Type      |                   |   Description
 --------------------+-------------+-------------------+-----------------------------
 route_no            | text        | not null          | Route number
 validity            | tstzrange   | not null          | Validity interval
 departure_airport   | char(3)     | not null          | Departure airport code
 arrival_airport     | char(3)     | not null          | Arrival airport code
 airplane_code       | char(3)     | not null          | Airplane code, IATA
 days_of_week        | integer[]   | not null          | Days of the week on which flights are performed
 scheduled_time      | time        | not null          | Local scheduled departure time
 duration            | interval    | not null          | Flight duration

Indexes:
    btree (departure_airport, lower(validity))
    EXCLUDE USING gist (route_no WITH =, validity WITH &&)
Foreign-key constraints:
    FOREIGN KEY (airplane_code) REFERENCES airplanes_data(airplane_code)
    FOREIGN KEY (arrival_airport) REFERENCES airports_data(airport_code)
    FOREIGN KEY (departure_airport) REFERENCES airports_data(airport_code)
    

Seats define the cabin configuration of each airplane 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
-----------------+-------------+--------------+--------------------
 airplane_code   | char(3)     | not null     | Airplane code, IATA
 seat_no         | text        | not null     | Seat number
 fare_conditions | text        | not null     | Travel class
Indexes:
    PRIMARY KEY, btree (airplane_code, seat_no)
Check constraints:
    CHECK (fare_conditions IN ('Economy', 'Comfort', 'Business'))
Foreign-key constraints:
    FOREIGN KEY (airplane_code)
        REFERENCES airplanes(airplane_code) ON DELETE CASCADE
    

A flight segment connects a ticket with a flight and is identified by their numbers.

Each segment has its cost (price) and travel class (fare_conditions).

     Column      |     Type      | Modifiers    |    Description
-----------------+---------------+--------------+---------------------
 ticket_no       | text          | not null     | Ticket number
 flight_id       | integer       | not null     | Flight ID
 fare_conditions | text          | not null     | Travel class
 price           | numeric(10,2) | not null     | Travel cost
Indexes:
    PRIMARY KEY, btree (ticket_no, flight_id)
    btree (flight_id)
Check constraints:
    CHECK (price >= 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 segments(ticket_no, flight_id)
    

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 based on the country code and a digital ID, and their first and last names (passenger_name) in this specific order.

It is guaranteed that a single passenger_id always corresponds to one passenger_name. A passenger cannot be included in a booking several times and cannot take overlapping flights, although the latter is not checked by the database schema.

The outbound field has the true value for the outbound flights and false for the return flights. The end points of outbound and return flights are the same, although the routes may differ.

     Column     |     Type    | Modifiers    |          Description
----------------+-------------+--------------+-----------------------------
 ticket_no      | text        | not null     | Ticket number
 book_ref       | char(6)     | not null     | Booking number
 passenger_id   | text        | not null     | Passenger ID
 passenger_name | text        | not null     | Passenger name
 outbound       | boolean     | not null     | Is the flight outbound
Indexes:
    PRIMARY KEY, btree (ticket_no)
    UNIQUE CONSTRAINT, btree (book_ref, passenger_id, outbound)
Foreign-key constraints:
    FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Referenced by:
    TABLE "segments" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
     

There is a timetable view over the flights and routes tables. Note that using this view, although simplifies many queries, may decrease performance due to excessive joins with airports tables.

          Column           |    Type     |              Description
---------------------------+-------------+--------------------------------------
 flight_id                 | integer     | Flight ID
 route_no                  | text        | Route number
 departure_airport         | char(3)     | Departure airport code
 arrival_airport           | char(3)     | Arrival airport code
 status                    | text        | Flight status
 airplane_code             | char(3)     | Airplane code, IATA
 scheduled_departure       | timestamptz | Scheduled departure time
 scheduled_departure_local | timestamp   | Scheduled departure time,
                           |             | local time at the point of departure
 actual_departure          | timestamptz | Actual departure time
 actual_departure_local    | timestamp   | Actual 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
 actual_arrival            | timestamptz | Actual arrival time
 actual_arrival_local      | timestamp   | Actual arrival time,
                           |             | local time at the point of destination
    View definition:
    SELECT f.flight_id,
    f.route_no,
    r.departure_airport,
    r.arrival_airport,
    f.status,
    r.airplane_code,
    f.scheduled_departure,
    (f.scheduled_departure AT TIME ZONE dep.timezone) AS scheduled_departure_local,
    f.actual_departure,
    (f.actual_departure AT TIME ZONE dep.timezone) AS actual_departure_local,
    f.scheduled_arrival,
    (f.scheduled_arrival AT TIME ZONE arr.timezone) AS scheduled_arrival_local,
    f.actual_arrival,
    (f.actual_arrival AT TIME ZONE arr.timezone) AS actual_arrival_local
   FROM flights f
     JOIN routes r ON r.flight_no = f.flight_no AND r.validity @> f.scheduled_departure
     JOIN airports_data dep ON dep.airport_code = r.departure_airport
     JOIN airports_data arr ON arr.airport_code = r.arrival_airport;
    

The demo database contains a snapshot of data — similar to a backup of a real system captured at some point in time. For example, if a flight has the Departed status, it means that the airplane 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 live database.

The bookings.version function returns the demo DB version. The version consists of the name of the virtual airline and the date of the first flight for the specified time interval. The latest version is PostgresPro 2025-09-01.

Some fields in the demo database are available in English and Russian. 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 airplanes and airports views and is not intended to be used directly in queries.