Бронирование

Начнем с бронирования и выберем какое-нибудь одно: 

=> SELECT * FROM bookings b WHERE b.book_ref = '0824C5';

 book_ref |       book_date        | total_amount 
----------+------------------------+--------------
 0824C5   | 2017-07-25 23:36:00+03 |    112400.00
(1 row)

Мы видим дату бронирования и общую сумму.

Если сравнивать дату с текущей, то бронирование сделано довольно давно: 

=> SELECT now();

              now              
-------------------------------
 2019-02-13 13:02:19.861574+03
(1 row)

Но для демобазы "текущим" моментом является другая дата: 

=> SELECT bookings.now();

          now           
------------------------
 2017-08-15 18:00:00+03
(1 row)

Так что "на самом деле" билеты забронированы 20 дней назад: 

=> SELECT bookings.now() - b.book_date
FROM bookings b WHERE b.book_ref = '0824C5';

     ?column?     
------------------
 20 days 18:24:00
(1 row)

Билеты

Посмотрим, какие билеты включены в это бронирование. 

=> SELECT t.*
FROM bookings b, tickets t
WHERE b.book_ref = '0824C5' AND t.book_ref = b.book_ref;

   ticket_no   | book_ref | passenger_id |  passenger_name   |       contact_data        
---------------+----------+--------------+-------------------+---------------------------
 0005435126781 | 0824C5   | 7247 393204  | ALEKSANDR MATVEEV | {"phone": "+70095062310"}
 0005435126782 | 0824C5   | 1745 826066  | NINA KRASNOVA     | {"phone": "+70876976071"}
(2 rows)

Летят два человека; на каждого оформляется собственный билет с информацией о пассажире.

Перелеты

По какому маршруту летят пассажиры? Добавим в запрос перелеты. 

=> SELECT tf.*
FROM tickets t, ticket_flights tf
WHERE t.ticket_no = '0005435126781' AND tf.ticket_no = t.ticket_no;

   ticket_no   | flight_id | fare_conditions |  amount  
---------------+-----------+-----------------+----------
 0005435126781 |     22566 | Economy         | 11700.00
 0005435126781 |     71439 | Economy         |  3200.00
 0005435126781 |     74643 | Economy         |  8800.00
 0005435126781 |     94335 | Economy         | 11700.00
 0005435126781 |     95726 | Economy         |  3200.00
 0005435126781 |    206625 | Business        | 26400.00
(6 rows)

Здесь мы смотрим только на один билет - все маршруты в одном бронировании всегда совпадают.

Видим, что в билете 6 перелетов; из них один бизнес-классом, другие - экономом.

Рейсы

Теперь разберемся, какие рейсы скрываются за этими перелетами. 

=> SELECT f.flight_id, f.scheduled_departure, f.departure_airport dep,
f.arrival_airport arr, f.status, f.aircraft_code aircraft
FROM tickets t, ticket_flights tf, flights f
WHERE t.ticket_no = '0005435126781'
AND tf.ticket_no = t.ticket_no AND f.flight_id = tf.flight_id
ORDER BY f.scheduled_departure;

 flight_id |  scheduled_departure   | dep | arr |  status   | aircraft 
-----------+------------------------+-----+-----+-----------+----------
     22566 | 2017-08-12 11:00:00+03 | VKO | PEE | Arrived   | 773
     95726 | 2017-08-12 15:30:00+03 | PEE | SVX | Arrived   | SU9
     74643 | 2017-08-13 11:30:00+03 | SVX | SGC | Arrived   | SU9
    206625 | 2017-08-15 14:45:00+03 | SGC | SVX | Departed  | SU9
     71439 | 2017-08-16 08:50:00+03 | SVX | PEE | On Time   | SU9
     94335 | 2017-08-16 18:55:00+03 | PEE | VKO | Scheduled | 773
(6 rows)

Видим три рейса "туда" и три "обратно". "Туда" все рейсы уже совершены (Arrived), а в настоящее время пассажир летит "обратно" (Departed). Следующий рейс будет по расписанию (On Time), а на последний еще не открыта регистрация (Scheduled).

Посмотрим внимательнее на все столбцы одного из рейсов. 

=> SELECT * FROM flights f WHERE f.flight_id = 22566 \gx

-[ RECORD 1 ]-------+-----------------------
flight_id           | 22566
flight_no           | PG0412
scheduled_departure | 2017-08-12 11:00:00+03
scheduled_arrival   | 2017-08-12 12:25:00+03
departure_airport   | VKO
arrival_airport     | PEE
status              | Arrived
aircraft_code       | 773
actual_departure    | 2017-08-12 11:01:00+03
actual_arrival      | 2017-08-12 12:25:00+03

Реальное время может отличаться от времени по расписанию (обычно не сильно).

Номер flight_no одинаков для всех рейсов, следующих по одному маршруту по расписанию: 

=> SELECT f.flight_id, f.flight_no, f.scheduled_departure
FROM flights f WHERE f.flight_no = 'PG0412'
ORDER BY f.scheduled_departure
LIMIT 10;

 flight_id | flight_no |  scheduled_departure   
-----------+-----------+------------------------
     22784 | PG0412    | 2016-08-15 11:00:00+03
     22746 | PG0412    | 2016-08-16 11:00:00+03
     22721 | PG0412    | 2016-08-17 11:00:00+03
     22691 | PG0412    | 2016-08-18 11:00:00+03
     22749 | PG0412    | 2016-08-19 11:00:00+03
     22508 | PG0412    | 2016-08-20 11:00:00+03
     22493 | PG0412    | 2016-08-21 11:00:00+03
     22496 | PG0412    | 2016-08-22 11:00:00+03
     22483 | PG0412    | 2016-08-23 11:00:00+03
     22501 | PG0412    | 2016-08-24 11:00:00+03
(10 rows)

Аэропорты

В качестве ключа для аэропортов используется общепринятый трехбуквенный код. Расшифруем: 

=> SELECT f.scheduled_departure,
dep.airport_code || ' ' || dep.city || ' (' || dep.airport_name || ')' departure,
arr.airport_code || ' ' || arr.city || ' (' || arr.airport_name || ')' arrival
FROM tickets t, ticket_flights tf, flights f, airports dep, airports arr
WHERE t.ticket_no = '0005435126781'
AND tf.ticket_no = t.ticket_no AND f.flight_id = tf.flight_id
AND dep.airport_code = f.departure_airport AND arr.airport_code = f.arrival_airport
ORDER BY f.scheduled_departure;

  scheduled_departure   |          departure          |           arrival           
------------------------+-----------------------------+-----------------------------
 2017-08-12 11:00:00+03 | VKO Москва (Внуково)        | PEE Пермь (Пермь)
 2017-08-12 15:30:00+03 | PEE Пермь (Пермь)           | SVX Екатеринбург (Кольцово)
 2017-08-13 11:30:00+03 | SVX Екатеринбург (Кольцово) | SGC Сургут (Сургут)
 2017-08-15 14:45:00+03 | SGC Сургут (Сургут)         | SVX Екатеринбург (Кольцово)
 2017-08-16 08:50:00+03 | SVX Екатеринбург (Кольцово) | PEE Пермь (Пермь)
 2017-08-16 18:55:00+03 | PEE Пермь (Пермь)           | VKO Москва (Внуково)
(6 rows)

Посмотрим полную информацию об одном аэропорте: 

=> SELECT * FROM airports WHERE airport_code = 'VKO' \gx

-[ RECORD 1 ]+------------------------------
airport_code | VKO
airport_name | Внуково
city         | Москва
coordinates  | (37.2615013123,55.5914993286)
timezone     | Europe/Moscow

Помимо названия и города, хранятся координаты аэропорта и часовой пояс.

Снова рейсы (представление)

Для упрощения запросов можно использовать представление, которое расшифровывает информацию о рейсах: 

=> SELECT * FROM flights_v f WHERE f.flight_id = 22566 \gx

-[ RECORD 1 ]-------------+-----------------------
flight_id                 | 22566
flight_no                 | PG0412
scheduled_departure       | 2017-08-12 11:00:00+03
scheduled_departure_local | 2017-08-12 11:00:00
scheduled_arrival         | 2017-08-12 12:25:00+03
scheduled_arrival_local   | 2017-08-12 14:25:00
scheduled_duration        | 01:25:00
departure_airport         | VKO
departure_airport_name    | Внуково
departure_city            | Москва
arrival_airport           | PEE
arrival_airport_name      | Пермь
arrival_city              | Пермь
status                    | Arrived
aircraft_code             | 773
actual_departure          | 2017-08-12 11:01:00+03
actual_departure_local    | 2017-08-12 11:01:00
actual_arrival            | 2017-08-12 12:25:00+03
actual_arrival_local      | 2017-08-12 14:25:00
actual_duration           | 01:24:00

Здесь видим и локальное время в часовых поясах городов отправления и прибытия, и длительность полета.

Маршруты (представление)

Поскольку в демобазе маршруты не меняются со временем, из таблицы рейсов можно выделить информацию, которая не зависит от конкретной даты вылета. Такая информация собрана в представлении: 

=> SELECT * FROM routes r WHERE r.flight_no = 'PG0412' \gx

-[ RECORD 1 ]----------+----------------
flight_no              | PG0412
departure_airport      | VKO
departure_airport_name | Внуково
departure_city         | Москва
arrival_airport        | PEE
arrival_airport_name   | Пермь
arrival_city           | Пермь
aircraft_code          | 773
duration               | 01:25:00
days_of_week           | {1,2,3,4,5,6,7}

Видно, что рейсы выполняются ежедневно (массив days_of_week).

Самолеты

Модели самолетов, обслуживающих рейсы, также используют стандартные трехсимвольные коды в качестве первичных ключей. 

=> SELECT a.*
FROM flights f, aircrafts a
WHERE f.flight_id = 22566 AND a.aircraft_code = f.aircraft_code;

 aircraft_code |     model     | range 
---------------+---------------+-------
 773           | Боинг 777-300 | 11100
(1 row)

Места

В демобазе все самолеты одной модели имеют одинаковую конфигурацию салона. Посмотрим на первый ряд: 

=> SELECT s.*
FROM flights f, aircrafts a, seats s
WHERE f.flight_id = 22566 
AND a.aircraft_code = f.aircraft_code AND s.aircraft_code = a.aircraft_code
AND s.seat_no ~ '^1.$';

 aircraft_code | seat_no | fare_conditions 
---------------+---------+-----------------
 773           | 1A      | Business
 773           | 1C      | Business
 773           | 1D      | Business
 773           | 1G      | Business
 773           | 1H      | Business
 773           | 1K      | Business
(6 rows)

Это бизнес-класс.

А вот общее число мест различных классов обслуживания: 

=> SELECT s.fare_conditions, count(*)
FROM seats s WHERE s.aircraft_code = '733'
GROUP BY s.fare_conditions;

 fare_conditions | count 
-----------------+-------
 Business        |    12
 Economy         |   118
(2 rows)

Посадочные талоны

На каких местах сидел наш пассажир? Для этого надо заглянуть в посадочный талон, который выдается при регистрации на рейс: 

=> SELECT f.status, bp.*
FROM tickets t
  JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no 
  JOIN flights f ON f.flight_id = tf.flight_id
  LEFT JOIN boarding_passes bp
    ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE t.ticket_no = '0005435126781'
ORDER BY f.scheduled_departure;

  status   |   ticket_no   | flight_id | boarding_no | seat_no 
-----------+---------------+-----------+-------------+---------
 Arrived   | 0005435126781 |     22566 |           4 | 22A
 Arrived   | 0005435126781 |     95726 |          64 | 19D
 Arrived   | 0005435126781 |     74643 |          42 | 8D
 Departed  | 0005435126781 |    206625 |          11 | 3F
 On Time   |               |           |             | 
 Scheduled |               |           |             | 
(6 rows)

На два оставшихся рейса пассажир еще не зарегистрировался.

Многоязычность

В демобазе заложена возможность перевода названий аэропортов, городов и самолетов на другие языки. Как мы видели, по умолчанию все названия выводятся по-русски: 

=> SELECT * FROM airports a WHERE a.airport_code = 'VKO' \gx

-[ RECORD 1 ]+------------------------------
airport_code | VKO
airport_name | Внуково
city         | Москва
coordinates  | (37.2615013123,55.5914993286)
timezone     | Europe/Moscow

Чтобы сменить язык, достаточно установить конфигурационный параметр: 

=> SET bookings.lang = 'en';

SET

=> SELECT * FROM airports a WHERE a.airport_code = 'VKO' \gx

-[ RECORD 1 ]+------------------------------
airport_code | VKO
airport_name | Vnukovo International Airport
city         | Moscow
coordinates  | (37.2615013123,55.5914993286)
timezone     | Europe/Moscow

Реализация использует представление над базовой таблицей, которая содержит переводы в формате JSON: 

=> SELECT * FROM airports_data ml WHERE ml.airport_code = 'VKO' \gx

-[ RECORD 1 ]+---------------------------------------------------------
airport_code | VKO
airport_name | {"en": "Vnukovo International Airport", "ru": "Внуково"}
city         | {"en": "Moscow", "ru": "Москва"}
coordinates  | (37.2615013123,55.5914993286)
timezone     | Europe/Moscow

Конец демонстрации.