Развернутый сценарий проведения учебного занятия (Конспект)

Здравствуйте, уважаемые коллеги! Садитесь. Прошу старосту предоставить сведения об отсутствующих.

На предыдущих лекциях мы изучили реляционную алгебру, реляционную модель данных и ограничения целостности. Мы также умеем рисовать концептуальные ER-диаграммы: выделять прямоугольники сущностей, овалы атрибутов и ромбы связей. Но когда программист садится писать код реальной информационной системы или администратор БД разворачивает сервер PostgreSQL, там нет концептуальных ромбов и стрелочек. Там есть таблицы, первичные ключи и внешние ограничения целостности FOREIGN KEY.

Перед нами встает главный инженерный вопрос: как взять абстрактную ER-модель и безошибочно транслировать ее в схему реляционных таблиц? И в частности: что делать, когда между сущностями возникает связь «многие-ко-многим»? Например, студенты и учебные дисциплины, актеры и фильмы, заказы и блюда в ресторане. Кто готов объяснить, почему мы не можем просто взять и связать две таблицы напрямую?

Именно так! Если бы мы попытались хранить идентификаторы через запятую строкой VARCHAR, мы бы лишились возможности использовать внешние ключи, уничтожили бы индексы B-Tree и парализовали бы сервер полными сканированиями таблиц при каждом поиске. Сегодня наша цель — освоить строгую математическую и инженерную методологию трансляции сильных и слабых сущностей, разрешения связей 1:1, 1:M и декомпозиции связей M:N через промежуточные ассоциативные таблицы с составными ключами. За занятие предусмотрена оценка по критериальной 4-балльной шкале.

Внимание на интерактивный экран, разбираем презентацию. Трансляция ER-диаграммы в реляционные таблицы подчиняется пяти фундаментальным правилам.

Правило 1: Сильные сущности. Сильная сущность независима от других объектов реального мира (Клиент, Товар, Режиссер). Каждая сильная сущность превращается в отдельную базовую таблицу. Имя таблицы задается во множественном числе на латинице (snake_case). Атрибуты становятся столбцами с точными типами данных. Обязательно назначается первичный ключ — в промышленной разработке чаще всего суррогатный INT или BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, а естественные идентификаторы защищаются ограничением UNIQUE NOT NULL.

Правило 2: Слабые сущности. Слабая сущность не имеет смысла без родителя (например, позиции в чеке или абонементы клиента). В таблицу слабой сущности обязательно мигрирует первичный ключ владельца как FOREIGN KEY NOT NULL, а при удалении родителя устанавливается жесткое каскадное удаление ON DELETE CASCADE.

Правило 3: Связь 1:M (Один-ко-многим). Это главный строительный блок реляционных баз данных. Запомните золотое правило реляционного моделирования: внешний ключ ВСЕГДА мигрирует в таблицу на стороне «Многие»! У одного режиссера много фильмов — значит, столбец id_rezhissera добавляется в таблицу фильмов, а не наоборот.

Правило 4: Декомпозиция связей M:N. Реляционная теория Эдгара Кодда физически запрещает связь многие-ко-многим между двумя таблицами. Чтобы разрешить отношение M:N, мы обязаны разбить его на две связи 1:M через введение третьей — промежуточной (ассоциативной) таблицы (Junction / Bridge table).

Что содержит эта связующая таблица? Во-первых, два внешних ключа, указывающих на первичные ключи исходных сущностей. Во-вторых, составной первичный ключ из этих двух колонок. И в-третьих — критически важная деталь — собственные атрибуты связи!

Отличный вопрос! Представьте кастинг актеров в онлайн-кинотеатре. Где должен храниться персонаж «Гамлет»? Если мы запишем Гамлета в анкету актера Иннокентия Смоктуновского, получится, что Смоктуновский во всех фильмах своей жизни играет исключительно Гамлета. Если мы запишем Гамлета в описание фильма, мы не свяжем его с конкретным исполнителем. Персонаж, размер гонорара, статус главной роли — это свойства, возникающие исключительно в момент пересечения актера и фильма! Поэтому они обязаны находиться в связующей таблице film_akter. Точно так же цена на момент покупки находится в связующей таблице заказа, а не в общем меню.

Разберем эталон на доске: медицинская информационная система поликлиники. Сущности: «Врачи», «Пациенты», связь M:N «Врачебные приемы».
Показываю DDL-структуру:

CREATE TABLE vrachi (
    id_vracha INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tab_nomer VARCHAR(20) NOT NULL UNIQUE,
    familiya VARCHAR(60) NOT NULL,
    specializaciya VARCHAR(100) NOT NULL
);

CREATE TABLE pacienty (
    id_pacienta INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    snils VARCHAR(14) NOT NULL UNIQUE,
    familiya VARCHAR(60) NOT NULL
);

CREATE TABLE priemy_vracha (
    id_priema BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_vracha INT NOT NULL REFERENCES vrachi(id_vracha) ON DELETE RESTRICT,
    id_pacienta INT NOT NULL REFERENCES pacienty(id_pacienta) ON DELETE RESTRICT,
    data_vremya TIMESTAMP NOT NULL,
    kabinet SMALLINT NOT NULL CHECK (kabinet > 0),
    stoimost NUMERIC(8,2) NOT NULL CHECK (stoimost >= 0),
    CONSTRAINT uq_vrach_priem UNIQUE (id_vracha, data_vremya)
);

Обратите внимание на ссылочную целостность: для врачей и пациентов установлена опция ON DELETE RESTRICT. Если врач увольняется или пациент меняет поликлинику, мы не имеем права удалить их физически из базы, если за ними числятся завершенные приемы. Это уничтожит медицинскую и финансовую отчетность.

Коллеги, открываем локальный файл zanyatie.html в браузере. Заполните поля ФИО, учебной группы и выберите свой практический вариант согласно номеру по списку журнала:
- Вариант 1: Система онлайн-кинотеатра (Фильм, Актер, Режиссер; M:N Актеры-Фильмы, 1:M Режиссер-Фильмы).
- Вариант 2: Служба доставки еды (Клиент, Заказ, Блюдо; M:N Заказ-Блюда, 1:M Клиент-Заказы).
- Вариант 3: Фитнес-клуб (Клиент, Тренер, Абонемент, Тренировка; M:N Клиенты-Тренеры через журнал тренировок).
- Вариант 4: Ритейл-маркетплейс (Покупатель, Товар, Корзина_покупок, Скидочный_купон; 1:1, 1:M, M:N).

Требуется спроектировать структуру реляционных таблиц, декомпозировать связи M:N, сформировать ключи и обосновать каскадные политики. Все данные сохраняются в памяти браузера по кнопке «Сохранить решение». Приступайте к выполнению!

Остановите работу, поднимем головы на экран. В ходе мониторинга я зафиксировал три типичные ловушки, в которые попали некоторые из вас:

Ловушка 1: Во втором варианте «Доставка еды» студент поместил стоимость блюда только в справочник blyuda, а из таблицы poziciya_zakaza ее убрал. Представьте: клиент заказал пиццу за 500 рублей в мае. В августе ресторан поднял цены до 700 рублей. Если цена не зафиксирована в строке заказа на момент покупки, СУБД пересчитает сумму майского чека, и бухгалтерский баланс развалится!

Ловушка 2: В четвертом варианте связь 1:1 между покупателем и корзиной некоторые попытались реализовать через две независимые таблицы со случайными ID. Правильное решение: первичный ключ корзины одновременно объявляется внешним ключом к покупателю: id_pokupatelya INT PRIMARY KEY REFERENCES pokupateli(id). Это на уровне физической архитектуры делает невозможным создание двух корзин у одного покупателя.

Взгляните на 15-й слайд презентации — это легендарный комикс xkcd про «Little Bobby Tables». Когда проектировщик базы данных не накладывает строгие ограничения типов данных и целостности, одна запись способна стереть таблицы школы.

Подведем итоги занятия. Сегодня мы освоили полный производственный цикл трансляции концептуальных моделей в реляционные таблицы. Вы научились безошибочно декомпозировать связи многие-ко-многим через связующие таблицы, сохранять контекстные атрибуты связи и защищать базу от записей-сирот каскадными политиками RESTRICT и CASCADE.

Нажмите в рабочем листе кнопку «🖨️ Печать / Сохранить в PDF» и отправьте готовый файл в учебную систему. Оценки выставляются в электронный журнал по 4-балльной матрице.

Для тех, кто пропустил занятие по уважительной причине или кому будет назначена индивидуальная отработка, в материалах размещено domashnee_zadanie.html. Обратите внимание: его выполняют строго по назначению преподавателя. Срок сдачи — до начала следующей пары. Всем спасибо за продуктивную работу, занятие окончено!