Теоретический конспект: Сущности и связи в реляционных базах данных

1. Концептуальная и реляционная парадигмы: от диаграмм к таблицам

Проектирование базы данных состоит из последовательных уровней абстракции: концептуального (модель «сущность-связь», ERD), логического (реляционная схема отношений) и физического (конкретные таблицы в СУБД с индексами и типами хранения).

Сложность перехода заключается в том, что в реляционной теории Эдгара Кодда отсутствуют прямые физические конструкции для представления связей. Все связи моделируются исключительно совпадением значений первичных (PRIMARY KEY) и внешних (FOREIGN KEY) ключей.

2. Пошаговый алгоритм трансляции ER-модели в реляционную структуру

Трансляция концептуальной схемы выполняется строго по пятишаговому алгоритму, гарантирующему нормализацию и целостность:

Детализация шагов трансляции:

1. Шаг 1: Сильные сущности. Создаются независимые таблицы каталогов и справочников. Назначается первичный ключ INT или BIGINT. Для естественных кодов накладываются ограничения уникальности UNIQUE NOT NULL.
2. Шаг 2: Связи 1:1. Выбирается ведущая и подчиненная сторона. В подчиненную таблицу добавляется колонка FOREIGN KEY UNIQUE. Если сущности разделяют одинаковый жизненный цикл (Покупатель — Корзина), первичный ключ корзины одновременно объявляется внешним ключом к покупателю.
3. Шаг 3: Связи 1:M. Внешний ключ помещается строго в таблицу на стороне «многие». Например, у одного режиссера много фильмов — внешний ключ id_rezhissera добавляется в таблицу filmy.
4. Шаг 4: Связи M:N. Прямая связь физически запрещена правилом атомарности (1NF). Создается новая связующая таблица. В нее мигрируют первичные ключи обеих связанных сущностей. Туда же переносятся все атрибуты связи.
5. Шаг 5: Ссылочная целостность. Проверяется соответствие типов (например, INT ссылается строго на INT). Для справочников устанавливается ON DELETE RESTRICT, для зависимых строк спецификаций — ON DELETE CASCADE.

3. Архитектура связующих таблиц (Junction Tables)

Рассмотрим детальные промышленные примеры реализации ассоциативных таблиц для различных предметных областей.

Пример 1: Онлайн-кинотеатр (Связь Актеры ↔ Фильмы)

Связь многие-ко-многим: актер снимается во множестве фильмов, в фильме занято множество актеров. Атрибуты связи: роль (персонаж), признак главной роли, индивидуальный гонорар.

-- Таблица сильной сущности «Фильмы»
CREATE TABLE filmy (
    id_filma        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nazvanie        VARCHAR(150) NOT NULL,
    god_vypuska     SMALLINT NOT NULL CHECK (god_vypuska >= 1895),
    id_rezhissera   INT NOT NULL REFERENCES rezhissery(id_rezhissera) 
                        ON UPDATE CASCADE ON DELETE RESTRICT
);

-- Таблица сильной сущности «Актеры»
CREATE TABLE aktery (
    id_aktera       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    familiya        VARCHAR(60) NOT NULL,
    imya            VARCHAR(60) NOT NULL,
    data_rozhdeniya DATE NOT NULL
);

-- Промежуточная связующая таблица «Фильм_Актер»
CREATE TABLE film_akter (
    id_filma        INT NOT NULL REFERENCES filmy(id_filma)
                        ON UPDATE CASCADE ON DELETE CASCADE,
    id_aktera       INT NOT NULL REFERENCES aktery(id_aktera)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
    rol_personazh   VARCHAR(120) NOT NULL,
    is_glavnaya     BOOLEAN NOT NULL DEFAULT FALSE,
    gonorar         NUMERIC(12,2) CHECK (gonorar >= 0),
    PRIMARY KEY (id_filma, id_aktera, rol_personazh)
);

Пример 2: Доставка еды (Связь Заказ ↔ Блюда меню)

Один заказ включает несколько блюд, блюдо входит во множество заказов. Атрибуты связи: количество порций, цена на момент заказа.

CREATE TABLE poziciya_zakaza (
    id_zakaza              BIGINT NOT NULL REFERENCES zakazy(id_zakaza)
                               ON UPDATE CASCADE ON DELETE CASCADE,
    id_blyuda              INT NOT NULL REFERENCES blyuda(id_blyuda)
                               ON UPDATE CASCADE ON DELETE RESTRICT,
    kolichestvo            SMALLINT NOT NULL DEFAULT 1 CHECK (kolichestvo > 0),
    cena_na_moment_zakaza  NUMERIC(8,2) NOT NULL CHECK (cena_na_moment_zakaza > 0),
    PRIMARY KEY (id_zakaza, id_blyuda)
);

4. Обеспечение ссылочной целостности: RESTRICT, CASCADE, SET NULL

Внешний ключ (FOREIGN KEY) гарантирует, что значение в дочерней таблице обязано соответствовать существующему первичному ключу в родительской таблице, либо быть равным NULL.

5. Вопросы для глубокой самопроверки