Практическое занятие №3: сущности и связи

Концептуальный уровень (ER-модель)

• Сущности (сильные и слабые)
• Связи (1:1, 1:M, M:N)
• Атрибуты сущностей и связей
• Бизнес-правила предметной области

Логический/реляционный уровень

• Отношения (таблицы реляционной БД)
• Первичные ключи (PRIMARY KEY)
• Внешние ключи (FOREIGN KEY)
• Ассоциативные (связующие) таблицы

Ключевые свойства трансляции

• Обязательная миграция первичного ключа владельца в виде FOREIGN KEY NOT NULL.
• Формирование составного первичного ключа: PRIMARY KEY (id_roditelya, nomer_pozicii).
• Политика жесткого каскадного удаления: ON DELETE CASCADE.

Пример в SQL

CREATE TABLE abonementy (
    id_abonementa INT GENERATED ALWAYS AS IDENTITY,
    id_klienta    INT NOT NULL 
        REFERENCES klienty(id_klienta) 
        ON DELETE CASCADE,
    tip_karty     VARCHAR(50) NOT NULL,
    PRIMARY KEY (id_abonementa)
);

Способ А: Внешний ключ с UNIQUE

Внешний ключ помещается в подчиненную таблицу и объявляется уникальным:

CREATE TABLE pasporta (
    id_pasporta INT PRIMARY KEY,
    seriya_nomer VARCHAR(20) NOT NULL UNIQUE,
    id_grazhdanina INT NOT NULL UNIQUE
        REFERENCES grazhdane(id)
);

Способ Б: Совмещение PK и FK

Первичный ключ дочерней таблицы одновременно является внешним ключом (идеально для Корзины):

CREATE TABLE korziny (
    id_pokupatelya INT PRIMARY KEY 
        REFERENCES pokupateli(id)
        ON DELETE CASCADE,
    obnovleno TIMESTAMP NOT NULL
);

Родительская таблица (сторона 1)

Содержит первичный ключ и не знает о дочерних строках:

CREATE TABLE rezhissery (
    id_rezhissera INT PRIMARY KEY,
    fio VARCHAR(120) NOT NULL
);

Дочерняя таблица (сторона M)

Получает ссылку на родителя:

CREATE TABLE filmy (
    id_filma INT PRIMARY KEY,
    nazvanie VARCHAR(150) NOT NULL,
    id_rezhissera INT NOT NULL 
        REFERENCES rezhissery(id_rezhissera)
        ON DELETE RESTRICT
);

Почему M:N нельзя связать напрямую?

• В реляционной таблице строка пересечения строки и колонки обязана быть атомарной (1NF).
• Нельзя хранить список внешних ключей "1, 5, 23" в ячейке.
• Внешний ключ может ссылаться ровно на одну строку родителя.

Решение: Декомпозиция

Отношение M:N строго декомпозируется на две связи 1:M через введение третьей — промежуточной (ассоциативной) таблицы.

1. Внешние ключи

Две колонки ссылаются на первичные ключи исходных сущностей.

2. Составной PK

Комбинация внешних ключей и роли исключает дублирование записей.

3. Атрибуты связи

Поля, которые не принадлежат ни фильму отдельно, ни актеру отдельно.

Кейс «Доставка еды»

Где хранить цену блюда?

• В таблице blyuda — хранится текущая цена в меню.
• В таблице poziciya_zakaza — хранится цена на момент заказа!
• Если не сохранить цену в строке заказа, при изменении меню изменятся суммы всех закрытых чеков в архиве!

Кейс «Кастинг фильмов»

Где хранить роль персонажа?

• У актера роли нет (он играет сотни ролей за жизнь).
• У фильма одной роли нет (в фильме десятки персонажей).
• Роль привязана строго к связке «Конкретный Актер в Конкретном Фильме».

Подход 1: Чистый составной PK

PRIMARY KEY (id_zakaza, id_blyuda)

Плюсы: Нулевой оверхед по памяти; СУБД физически гарантирует невозможность добавления дубликата одной и той же пары.

Минусы: Сложнее ссылаться из других таблиц (если потребуются внешние ключи на саму строку позиции).

Подход 2: Суррогатный ID + UNIQUE

id_pozicii BIGINT PRIMARY KEY,
UNIQUE (id_zakaza, id_blyuda)

Плюсы: Простой компактный идентификатор для дочерних спецификаций и REST API.

Требование: Ограничение UNIQUE на связку внешних ключей СТРОГО обязательно!

1. Потерянная таблица M:N

Студент пытается записать список кодов в текстовую строку VARCHAR. База теряет атомарность, индексы перестают работать, поиск парализован.

2. Несоответствие типов

Первичный ключ родителя объявлен как BIGINT, а внешний ключ дочерней таблицы — как INT или VARCHAR. СУБД возвращает ошибку DDL.

3. Утечка атрибута связи

Цена чека помещена в общее меню блюд, а роль персонажа — в анкету актера. Нарушается логика предметной области и история операций.