Erlan Akbaraliev

Posted on Dec 1 • Edited on Dec 2

БД3 Практ (Create)

#database #postgres #tutorial

✈️ Задание 1. Проектирование схемы базы данных (PostgreSQL, pgAdmin4)

Международный аэропорт Хартсфилд-Джексон (Hartsfield-Jackson International Airport), более известный под кодом ATL, является самым загруженным аэропортом в мире с 1998 года. Он расположен в Атланте, штат Джорджия, США, и обслужил 93.7 миллиона пассажиров в 2022 году. Возможно, вы были одним из них!

Представьте, что вас только что наняли помочь ATL заново спроектировать их систему баз данных.

📌 Требования

Чтобы понять требования ATL к базе данных, вы провели разговор с заместителем генерального менеджера по IT-операциям.

👤 Пассажиры

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

🛄 Регистрация (Check-Ins)

Когда пассажиры прибывают в ATL, они часто регистрируются на рейс — подтверждают, что прибыли и готовы к посадке. Нам нужно вести аккуратный журнал таких действий. А именно хранить:

точные дата и время регистрации
рейс, на который происходит регистрация (нельзя потерять информацию, куда они направляются!)

🛫 Авиакомпании

ATL — крупный хаб для множества авиакомпаний: Delta, British Airways, Air France, Korean Air, Turkish Airlines и многих других. Нам нужно хранить:

название авиакомпании
конкорс (секция аэропорта), из которого она работает У нас есть 7 конкорсов: A, B, C, D, E, F и T

🛩 Рейсы

Мы обслуживаем около 1000 рейсов в день. Чтобы пассажиры всегда были в курсе, нам нужно хранить критически важные данные о каждом рейсе:

номер рейса (например, “900”) (иногда мы повторно используем старые номера)
авиакомпания, выполняющая рейс (предполагаем: один рейс = одна авиакомпания)
код аэропорта отправления (например, “ATL” или “BOS”)
код аэропорта прибытия
ожидаемые дата и время вылета (точность до минуты)
ожидаемые дата и время прибытия (также до минуты)

📋 Пример данных

Ваша база данных должна уметь хранить следующие примеры:

✔ Пассажир

Amelia Earhart, возраст 39

✔ Авиакомпания

Delta, работает в конкорсе A

✔ Рейс

Delta Flight 300, ожидаемый вылет из ATL
3 августа 2023 года в 18:46, прибытие в BOS
3 августа 2023 года в 21:09

✔ Регистрация

Регистрация Amelia Earhart
на Delta Flight 300
3 августа 2023 года в 15:03

Решение

CREATE TABLE пассажиры(
    id INTEGER,
    имя TEXT NOT NULL,
    фамилия TEXT,
    возраст INTEGER,
    PRIMARY KEY(id)
);

CREATE TABLE регистрации(
    id INTEGER,
    дата_время TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    рейс_id INTEGER,
    PRIMARY KEY(id),
    FOREIGN KEY(рейс_id) REFERENCES рейсы(id)
);

CREATE TABLE авиакомпании(
    id INTEGER,
    название TEXT NOT NULL UNIQUE,
    конкорс TEXT NOT NULL CHECK(конкорс IN ('A','B','C','D','E','F','T')),
    PRIMARY KEY (id)
);

CREATE TABLE рейсы (
    id INTEGER,
    номер_рейса INTEGER NOT NULL,
    авиакомпания_id INTEGER,
    аэропорт_отправления TEXT NOT NULL UNIQUE,
    аэропорт_прибытия TEXT NOT NULL UNIQUE,
    вылет TIMESTAMPTZ NOT NULL,
    прибытие TIMESTAMPTZ NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(авиакомпания_id) REFERENCES авиакомпании(id)
);

DEV Community