Все интересующие нас объекты находятся в схеме данных bookings (схема – логический раздел базы данных, в котором создаются таблицы и другие объекты). При подключении к базе данных эта схема обычно используется по умолчанию.
Описание предметной области:
Основной сущностью является бронирование (таблица Bookings). В одно бронирование можно включить несколько пассажиров, каждому из которых выписывается отдельный билет (таблица Tickets). Как таковой пассажир не является отдельной сущностью: для простоты можно считать, что все пассажиры уникальны.
Каждый билет включает один или несколько перелётов (Ticket_flights). Несколько перелётов могут включаться в билет в нескольких случаях:
- Нет прямого рейса, соединяющего пункты отправления и назначения (полет с пересадками);
- Взят билет «туда и обратно».
В схеме данных нет жёсткого ограничения, но предполагается, что все билеты в одном бронировании имеют одинаковый набор перелётов. Каждый рейс (Flights) следует из одного аэропорта (Airports) в другой. Рейсы с одним номером имеют одинаковые пункты вылета и назначения, но будут отличаться датой отправления.
При регистрации на рейс пассажиру выдаётся посадочный талон (Boarding_passes), в котором указано место в самолёте. Пассажир может зарегистрироваться только на тот рейс, который есть у него в билете. Комбинация рейса и места в самолёте должна быть уникальной, чтобы не допустить выдачу двух посадочных талонов на одно место.
Количество мест (Seats) в самолёте и их распределение по классам обслуживания зависит от конкретной модели самолёта (Aircrafts), выполняющего рейс. Предполагается, что каждая модель самолёта имеет только одну компоновку салона.
Описание таблиц:
≡ Booking (бронирование)
Намереваясь воспользоваться услугами авиакомпании, пассажир заранее (book_date, максимум за месяц до рейса) бронирует необходимые билеты. Для этого поля выбран тип данных timestamptz – временная отметка с часовым поясом, т. к. билеты могут приобретаться в городах, находящихся в различных часовых поясах. Бронирование идентифицируется своим номером book_ref (шестизначная уникальная комбинация букв и цифр). В качестве типа данных для него выбран тип character.
В каждое бронирование можно включить несколько пассажиров, для каждого из которых создаётся отдельный билет. Поле total_amount хранит стоимость включённых в бронирование перелётов всех пассажиров (тип данных numeric).
≡ Tickets (билеты)
Билет имеет уникальный номер ticket_no, состоящий из 13 цифр (тип character, поскольку могут присутствовать лидирующие нули). Билет содержит номер документа, который удостоверяет личность пассажира passenger_id (тип varchar), а также его фамилию и имя passenger_name (отчество не используется) и контактную информацию contact_data (здесь используется структурный тип jsonb, поскольку информация может быть индивидуальной для каждого пассажира). Внешним ключом в таблице будет атрибут «Номер бронирования» (book_ref), поскольку в рамках каждой процедуры бронирования может быть оформлено более одного билета.
Заметим, что ни идентификатор пассажира, ни имя не являются постоянными (можно поменять паспорт, можно сменить фамилию). Поэтому однозначно найти все билеты одного и того же пассажира невозможно.
≡ Ticket_flights (билеты на рейс)
Таблица с информацией о всех перелётах, соединяет билет с рейсом и идентифицируется двумя их номерами. Перелёты вписываются в электронные билеты, при этом в каждый электронный билет может быть вписано более одного перелёта. Поэтому первичным ключом будет комбинация двух атрибутов: «Номер билета» (ticket_no) и «Идентификатор рейса» (flight_id). Для каждого перелёта указываются его стоимость amount (тип данных numeric) и класс обслуживания fare_conditions (тип данных varchar).
≡ Flight (рейс)
Естественный ключ таблицы рейсов состоит из двух полей – номера рейса flight_no и даты отправления scheduled_departure. Чтобы сделать внешние ключи на эту таблицу компактнее, в качестве первичного используется суррогатный ключ flight_id.
Рейс всегда соединяет две точки – аэропорты вылета departure_airport и прибытия arrival_airport. Такое понятие, как «рейс с пересадками» отсутствует: если из одного аэропорта до другого нет прямого рейса, в билет просто включаются несколько необходимых рейсов.
У каждого рейса есть запланированные дата и время вылета scheduled_departure и прибытия scheduled_arrival. Реальные время вылета actual_departure и прибытия actual_arrival могут отличаться: обычно не сильно, но иногда и на несколько часов, если рейс задержан. Обратите внимание, что для атрибутов, имеющих смысл даты/времени, выбран тип данных timestamptz, т.е. временная отметка с указанием часового пояса.
Статус рейса status может принимать одно из следующих значений:
- Scheduled– Рейс доступен для бронирования. Это происходит за месяц до плановой даты вылета; до этого запись о рейсе не существует в базе данных.
- On Time– Рейс доступен для регистрации (за сутки до плановой даты вылета) и не задержан.
- Delayed– Рейс доступен для регистрации (за сутки до плановой даты вылета), но задержан.
- Departed– Самолет уже вылетел и находится в воздухе.
- Arrived– Самолет прибыл в пункт назначения.
- Cancelled– Рейс отменен.
≡ Airports (аэропорты)
Каждый аэропорт идентифицируется трехбуквенным кодом airport_code и имеет название airport_name. Название города city указывается как атрибут аэропорта; отдельной сущности для него не предусмотрено. Название можно использовать для того, чтобы определить аэропорты одного города. Также указываются координаты coordinates (объектный тип: долгота и широта) и часовой пояс timezone.
≡ Boarding_passes (посадочные талоны)
При регистрации на рейс, которая возможна за сутки до плановой даты отправления, пассажиру выдаётся посадочный талон. Он идентифицируется так же, как и перелёт – номером билета и номером рейса. Посадочным талонам присваиваются последовательные номера boarding_no (тип данных integer) в порядке регистрации пассажиров на рейс (этот номер будет уникальным только в пределах данного рейса).
Эта таблица имеет связь типа 1:1 с таблицей Tickets_flights. Это объясняется тем, что пассажир, купивший билет на конкретный рейс, при регистрации получает только один посадочный талон. Конечно, если пассажир не явился на регистрацию, он не получает талона. Поэтому число строк в таблице Boarding_passes может в общем случае оказаться меньше числа строк в таблице Tickets_flights.
Известно, что номер конкретного места в самолёте пассажир получает при регистрации билета, а не при его бронировании, поэтому атрибут «Номер места» (seat_no) находится в таблице Boarding_passes, а не в таблице Tickets_flights.
≡ Aircrafts (самолёты)
Каждая модель воздушного судна идентифицируется своим трехзначным кодом aircraft_code. Указывается также название модели model и максимальная дальность полета в километрах range.
≡ Seats (места)
Места определяют схему салона каждой модели. Предполагается, что каждая модель имеет только одну компоновку салона. Каждое место определяется своим номером seat_no и имеет закреплённый за ним класс обслуживания fare_conditions – Economy, Comfort или Business.
Дополнительная информация:
Используемая схема данных на самом деле плохо нормализована (и авторы сами это признают). Так, в таблице рейсов аэропорты отправления и прибытия зависят только от номера рейса, но не зависят от даты и времени вылета, т. е. в этой таблице имеется зависимость от неполного ключа, и поэтому она не находится во второй нормальной форме.
Функция bookings.now()
Демонстрационная база содержит временной «срез» данных – так, как будто в некоторый момент была сделана резервная копия реальной системы. Например, если некоторый рейс имеет статус Departed, это означает, что в момент резервного копирования самолет находился в воздухе. Позиция «среза» сохранена в функции bookings.now(). Ей можно пользоваться в запросах там, где в обычной жизни использовалась бы функция now(). Для функции bookings.now указывать схему bookings принципиально необходимо, чтобы отличать её от стандартной функции now.
