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
    

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 L.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;
    

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
    

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 L.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;
    

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)
    

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)
    

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)
    

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:

        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)
    

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
    

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)
    

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)
     

There is a flights_v view over the flights table that provides additional information:

          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
    

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
    

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.

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.