Movatterモバイル変換

J.5.1. Schemabookings

J.5.2. Sample Queries

J.5.3. Bookings

J.5.4. New Booking

Thebookings schema contains all objects of the demo database. It means that when you access database objects, you either have to explicitly specify the schema name (for example:bookings.flights), or modify thesearch_path configuration parameter beforehand (for example:SET search_path = bookings, public;).

However, for thebookings.now function, you always have to specify the schema to distinguish this function from the standardnow function.

To better understand the contents of the demo database, let's take a look at the results of several simple queries.

The results displayed below were received on a small database version (demo_small) of October 13, 2016. If the same queries return different data on your system, check your demo database version (using thebookings.now function). Some minor deviations may be caused by the difference between your local time and Moscow time, or your locale settings.

All flights are operated by several types of aircraft:

SELECT * FROM aircrafts;

 aircraft_code |        model        | range---------------+---------------------+------- 773           | Boeing 777-300      | 11100 763           | Boeing 767-300      |  7900 SU9           | Sukhoi SuperJet-100 |  3000 320           | Airbus A320-200     |  5700 321           | Airbus A321-200     |  5600 319           | Airbus A319-100     |  6700 733           | Boeing 737-300      |  4200 CN1           | Cessna 208 Caravan  |  1200 CR2           | Bombardier CRJ-200  |  2700(9 rows)

For each aircraft type, a separate list of seats is supported. For example, in a small Cessna 208 Caravan, one can select the following seats:

SELECT   a.aircraft_code,         a.model,         s.seat_no,         s.fare_conditionsFROM     aircrafts a         JOIN seats s ON a.aircraft_code = s.aircraft_codeWHERE    a.model = 'Cessna 208 Caravan'ORDER BY s.seat_no;

 aircraft_code |       model        | seat_no | fare_conditions---------------+--------------------+---------+----------------- CN1           | Cessna 208 Caravan | 1A      | Economy CN1           | Cessna 208 Caravan | 1B      | Economy CN1           | Cessna 208 Caravan | 2A      | Economy CN1           | Cessna 208 Caravan | 2B      | Economy CN1           | Cessna 208 Caravan | 3A      | Economy CN1           | Cessna 208 Caravan | 3B      | Economy CN1           | Cessna 208 Caravan | 4A      | Economy CN1           | Cessna 208 Caravan | 4B      | Economy CN1           | Cessna 208 Caravan | 5A      | Economy CN1           | Cessna 208 Caravan | 5B      | Economy CN1           | Cessna 208 Caravan | 6A      | Economy CN1           | Cessna 208 Caravan | 6B      | Economy(12 rows)

Bigger aircraft have more seats of various travel classes:

SELECT   s2.aircraft_code,         string_agg (s2.fare_conditions || '(' || s2.num::text || ')',                     ', ') as fare_conditionsFROM     (          SELECT   s.aircraft_code, s.fare_conditions, count(*) as num          FROM     seats s          GROUP BY s.aircraft_code, s.fare_conditions          ORDER BY s.aircraft_code, s.fare_conditions         ) s2GROUP BY s2.aircraft_codeORDER BY s2.aircraft_code;

 aircraft_code |             fare_conditions---------------+----------------------------------------- 319           | Business(20), Economy(96) 320           | Business(20), Economy(120) 321           | Business(28), Economy(142) 733           | Business(12), Economy(118) 763           | Business(30), Economy(192) 773           | Business(30), Comfort(48), Economy(324) CN1           | Economy(12) CR2           | Economy(50) SU9           | Business(12), Economy(85)(9 rows)

The demo database contains the list of airports of almost all major Russian cities. Most cities have only one airport. The exceptions are:

SELECT   a.airport_code as code,         a.airport_name,         a.city,         a.longitude,         a.latitude,         a.timezoneFROM     airports aWHERE    a.city IN (            SELECT   aa.city            FROM     airports aa            GROUP BY aa.city            HAVING   COUNT(*) > 1         )ORDER BY a.city, a.airport_code;

 code |    airport_name     |   city    | longitude | latitude  |   timezone------+---------------------+-----------+-----------+-----------+--------------- DME  | Домодедово          | Москва    | 37.906111 | 55.408611 | Europe/Moscow SVO  | Шереметьево         | Москва    | 37.414589 | 55.972642 | Europe/Moscow VKO  | Внуково             | Москва    | 37.261486 | 55.591531 | Europe/Moscow ULV  | Баратаевка          | Ульяновск |   48.2267 | 54.268299 | Europe/Samara ULY  | Ульяновск-Восточный | Ульяновск |   48.8027 |    54.401 | Europe/Samara(5 rows)

To learn about your flying options from one point to another, it is convenient to use theroutes materialized view that aggregates information on all flights. For example, here are the destinations where you can get from Volgograd on specific days of the week, with flight duration:

SELECT r.arrival_city as city,       r.arrival_airport as airport_code,       r.arrival_airport_name as airport_name,       r.days_of_week,       r.durationFROM   routes rWHERE  r.departure_city = 'Волгоград';

      city      | airport_code |  airport_name  |  days_of_week   | duration----------------+--------------+----------------+-----------------+---------- Москва         | SVO          | Шереметьево    | {1,2,3,4,5,6,7} | 01:15:00 Челябинск      | CEK          | Челябинск      | {1,2,3,4,5,6,7} | 01:50:00 Ростов-на-Дону | ROV          | Ростов-на-Дону | {1,2,3,4,5,6,7} | 00:30:00 Москва         | VKO          | Внуково        | {1,2,3,4,5,6,7} | 01:10:00 Чебоксары      | CSY          | Чебоксары      | {1,2,3,4,5,6,7} | 02:45:00 Томск          | TOF          | Богашёво       | {3}             | 03:50:00(6 rows)

The database was formed at the moment returned by thebookings.now() function:

SELECT bookings.now() as now;

          now------------------------ 2016-10-13 17:00:00+03

In relation to this moment, all flights are classified as past and future flights:

SELECT   status,         count(*) as count,         min(scheduled_departure) as min_scheduled_departure,         max(scheduled_departure) as max_scheduled_departureFROM     flightsGROUP BY status ORDER BY min_scheduled_departure;

  status   | count | min_scheduled_departure | max_scheduled_departure-----------+-------+-------------------------+------------------------- Arrived   | 16707 | 2016-09-13 00:50:00+03  | 2016-10-13 16:25:00+03 Cancelled |   414 | 2016-09-16 10:35:00+03  | 2016-11-12 19:55:00+03 Departed  |    58 | 2016-10-13 08:55:00+03  | 2016-10-13 16:50:00+03 Delayed   |    41 | 2016-10-13 14:15:00+03  | 2016-10-14 16:25:00+03 On Time   |   518 | 2016-10-13 16:55:00+03  | 2016-10-14 17:00:00+03 Scheduled | 15383 | 2016-10-14 17:05:00+03  | 2016-11-12 19:40:00+03(6 rows)

Let's find the next flight from Yekaterinburg to Moscow. Theflight table is not very convenient for such queries, as it does not include information on the cities of departure and arrival. That is why we will use theflights_v view:

\xSELECT   f.*FROM     flights_v fWHERE    f.departure_city = 'Екатеринбург'AND      f.arrival_city = 'Москва'AND      f.scheduled_departure > bookings.now()ORDER BY f.scheduled_departure LIMIT    1;

-[ RECORD 1 ]-------------+-----------------------flight_id                 | 10927flight_no                 | PG0226scheduled_departure       | 2016-10-14 07:10:00+03scheduled_departure_local | 2016-10-14 09:10:00scheduled_arrival         | 2016-10-14 08:55:00+03scheduled_arrival_local   | 2016-10-14 08:55:00scheduled_duration        | 01:45:00departure_airport         | SVXdeparture_airport_name    | Кольцовоdeparture_city            | Екатеринбургarrival_airport           | SVOarrival_airport_name      | Шереметьевоarrival_city              | Москваstatus                    | On Timeaircraft_code             | 773actual_departure          |actual_departure_local    |actual_arrival            |actual_arrival_local      |actual_duration           |

Note that theflights_v view shows both Moscow time and local time at the airports of departure and arrival.

Each booking can include several tickets, one for each passenger. The ticket, in its turn, can include several flight segments. The complete information about the booking is stored in three tables:bookings,tickets, andticket_flights.

Let's find several most expensive bookings:

SELECT   *FROM     bookingsORDER BY total_amount descLIMIT    10;

 book_ref |       book_date        | total_amount----------+------------------------+-------------- 3B54BB   | 2016-09-02 16:08:00+03 |   1204500.00 3AC131   | 2016-09-28 00:06:00+03 |   1087100.00 65A6EA   | 2016-08-31 05:28:00+03 |   1065600.00 D7E9AA   | 2016-10-06 04:29:00+03 |   1062800.00 EF479E   | 2016-09-30 14:58:00+03 |   1035100.00 521C53   | 2016-09-05 08:25:00+03 |    985500.00 514CA6   | 2016-09-24 04:07:00+03 |    955000.00 D70BD9   | 2016-09-02 11:47:00+03 |    947500.00 EC7EDA   | 2016-08-30 15:13:00+03 |    946800.00 8E4370   | 2016-09-25 01:04:00+03 |    945700.00(10 rows)

Let's take a look at the tickets included into the booking with code521C53:

SELECT ticket_no,       passenger_id,       passenger_nameFROM   ticketsWHERE  book_ref = '521C53';

   ticket_no   | passenger_id |   passenger_name---------------+--------------+-------------------- 0005432661914 | 8234 547529  | IVAN IVANOV 0005432661915 | 2034 201228  | ANTONINA KUZNECOVA(2 rows)

If we would like to know, which flight segments are included into Antonina Kuznecova's ticket, we can use the following query:

SELECT   to_char(f.scheduled_departure, 'DD.MM.YYYY') as when,         f.departure_city || '(' || f.departure_airport || ')' as departure,         f.arrival_city || '(' || f.arrival_airport || ')' as arrival,         tf.fare_conditions as class,         tf.amountFROM     ticket_flights tf         JOIN flights_v f ON tf.flight_id = f.flight_idWHERE    tf.ticket_no = '0005432661915'ORDER BY f.scheduled_departure;

    when    |     departure     |      arrival      |  class   |  amount------------+-------------------+-------------------+----------+----------- 26.09.2016 | Москва(SVO)       | Анадырь(DYR)      | Business | 185300.00 30.09.2016 | Анадырь(DYR)      | Хабаровск(KHV)    | Business |  92200.00 01.10.2016 | Хабаровск(KHV)    | Благовещенск(BQS) | Business |  18000.00 06.10.2016 | Благовещенск(BQS) | Хабаровск(KHV)    | Business |  18000.00 10.10.2016 | Хабаровск(KHV)    | Анадырь(DYR)      | Economy  |  30700.00 15.10.2016 | Анадырь(DYR)      | Москва(SVO)       | Business | 185300.00(6 rows)

As we can see, high booking cost is explained by multiple long-haul flights in business class.

Some of the flight segments in this ticket have earlier dates than thebookings.now() return value: it means that these flights had already happened. The last flight had not happened yet at the time of the database creation. After the check-in, a boarding pass with the allocated seat number is issued. We can check the exact seats occupied by Antonina (note the outer left join with tableboarding_passes):

SELECT   to_char(f.scheduled_departure, 'DD.MM.YYYY') as when,         f.departure_city || '(' || f.departure_airport || ')' as departure,         f.arrival_city || '(' || f.arrival_airport || ')' as arrival,         f.status,         bp.seat_noFROM     ticket_flights tf         JOIN flights_v f ON tf.flight_id = f.flight_id         LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id                                      AND tf.ticket_no = bp.ticket_noWHERE    tf.ticket_no = '0005432661915'ORDER BY f.scheduled_departure;

    when    |     departure     |      arrival      |  status   | seat_no------------+-------------------+-------------------+-----------+--------- 26.09.2016 | Москва(SVO)       | Анадырь(DYR)      | Arrived   | 5C 30.09.2016 | Анадырь(DYR)      | Хабаровск(KHV)    | Arrived   | 1D 01.10.2016 | Хабаровск(KHV)    | Благовещенск(BQS) | Arrived   | 2C 06.10.2016 | Благовещенск(BQS) | Хабаровск(KHV)    | Arrived   | 2D 10.10.2016 | Хабаровск(KHV)    | Анадырь(DYR)      | Arrived   | 20B 15.10.2016 | Анадырь(DYR)      | Москва(SVO)       | Scheduled |(6 rows)

Let's try to send Aleksandr Radishchev from Saint Petersburg to Moscow — the route that made him famous. Naturally, he will travel for free and in business class. We have already found a flight for tomorrow, and a return flight a week later.

BEGIN;INSERT INTO bookings (book_ref, book_date, total_amount)VALUES      ('_QWE12', bookings.now(), 0);INSERT INTO tickets (ticket_no, book_ref, passenger_id, passenger_name)VALUES      ('_000000000001', '_QWE12', '1749 051790', 'ALEKSANDR RADISHCHEV');INSERT INTO ticket_flights (ticket_no, flight_id, fare_conditions, amount)VALUES      ('_000000000001', 9720, 'Business', 0),            ('_000000000001', 6662, 'Business', 0);COMMIT;

To avoid conflicts with the range of values present in the database, identifiers are started with an underscore.

We will check in Aleksandr for tomorrow's flight right away:

INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no)VALUES      ('_000000000001', 9720, 1, '1A');

Now let's check the booking information:

SELECT   b.book_ref,         t.ticket_no,         t.passenger_id,         t.passenger_name,         tf.fare_conditions,         tf.amount,         f.scheduled_departure_local,         f.scheduled_arrival_local,         f.departure_city || '(' || f.departure_airport || ')' as departure,         f.arrival_city || '(' || f.arrival_airport || ')' as arrival,         f.status,         bp.seat_noFROM     bookings b         JOIN tickets t ON b.book_ref = t.book_ref         JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no         JOIN flights_v f ON tf.flight_id = f.flight_id         LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id                                     AND tf.ticket_no = bp.ticket_noWHERE    b.book_ref = '_QWE12'ORDER BY t.ticket_no, f.scheduled_departure;

-[ RECORD 1 ]-------------+---------------------book_ref                  | _QWE12ticket_no                 | _000000000001passenger_id              | 1749 051790passenger_name            | ALEKSANDR RADISHCHEVfare_conditions           | Businessamount                    | 0.00scheduled_departure_local | 2016-10-14 08:45:00scheduled_arrival_local   | 2016-10-14 09:35:00departure                 | Санкт-Петербург(LED)arrival                   | Москва(SVO)status                    | On Timeseat_no                   | 1A-[ RECORD 2 ]-------------+---------------------book_ref                  | _QWE12ticket_no                 | _000000000001passenger_id              | 1749 051790passenger_name            | ALEKSANDR RADISHCHEVfare_conditions           | Businessamount                    | 0.00scheduled_departure_local | 2016-10-21 09:20:00scheduled_arrival_local   | 2016-10-21 10:10:00departure                 | Москва(SVO)arrival                   | Санкт-Петербург(LED)status                    | Scheduledseat_no                   |

We hope that these simple examples helped you get an idea of this demo database.