SQLtest: задачи по SQL

База данных Bookings (PostgreSQL)

Предметной областью этой базы данных являются рейсы авиакомпаний через различные аэропорты.

ER диаграмма базы данных Bookings

База данных Bookings содержит 8 таблиц:

• aircrafts_data - таблица самолетов.
• airports_data - таблица аэропортов.
• boarding_passes - таблица посадочных талонов.
• bookings - таблица бронирований.
• flights - таблица рейсов.
• seats - таблица мест в самолетах.
• ticket_flights - таблица связей билетов с рейсами.
• tickets - таблица билетов.

Таблица aircrafts_data

Колонки:

• aircraft_code – Уникальный код для каждого самолета.
• model — Название модели самолета на английском и русском языках в формате JSON.
• range – Дальность полета самолета в километрах.

Индексы:

• PRIMARY KEY, btree (aircraft_code)

Таблица airports_data

Колонки:

• airport_code – Уникальный код для каждого аэропорта.
• airport_name — Название аэропорта на английском и русском языках в формате JSON.
• city — Город аэропорта на английском и русском языках в формате JSON.
• coordinates – Координаты аэропорта в виде POINT(долгота, широта).
• timezone – Название часового пояса аэропорта.

Индексы:

• PRIMARY KEY, btree (airport_code)

Таблица boarding_passes

Колонки:

• ticket_no - Номер билета.
• flight_id - Идентификатор рейса.
• boarding_no - Номер посадочного талона.
• seat_no - Номер места.

Индексы:

• PRIMARY KEY, btree (ticket_no, flight_id)
• UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
• UNIQUE CONSTRAINT, btree (flight_id, seat_no)

Ограничения внешнего ключа:

• FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

Таблица bookings

Колонки:

• book_ref – Номер бронирования.
• book_date – Дата бронирования.
• total_amount – Общая стоимость бронирования.

Индексы:

• PRIMARY KEY, btree (book_ref)

Таблица flights

Колонки:

• flight_id – Идентификатор рейса.
• flight_no – Номер рейса.
• scheduled_departure – Запланированное время отправления.
• scheduled_arrival – Запланированное время прибытия.
• departure_airport – Аэропорт вылета.
• arrival_airport – Аэропорт прибытия.
• status – Статус рейса.
• aircraft_code – Код самолета, IATA.
• actual_departure – Фактическое время отправления.
• actual_arrival – Фактическое время прибытия.

Индексы:

• PRIMARY KEY, btree (flight_id)
• UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure)

Таблица seats

Колонки:

• aircraft_code – Код самолета, IATA.
• seat_no – Номер места.
• fare_conditions – Класс путешествия.

Индексы:

• PRIMARY KEY, btree (aircraft_code, seat_no)

Ограничения внешнего ключа:

• FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE

Таблица ticket_flights

Колонки:

• ticket_no – Номер билета.
• flight_id – Идентификатор рейса.
• fare_conditions – Класс путешествия.
• сумма – Стоимость поездки.

Индексы:

• PRIMARY KEY, btree (ticket_no, flight_id)

Ограничения внешнего ключа:

• FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
• FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

Таблица tickets

Колонки:

• ticket_no – Номер билета.
• book_ref – Номер бронирования.
• passenger_id – Идентификатор пассажира.
• passenger_name – Имя пассажира.
• contact_data – Контактная информация пассажира.

Индексы:

• PRIMARY KEY, btree (ticket_no)

Ограничения внешнего ключа:

• FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)