Методическое руководство для преподавателя

Дисциплина: ОП.08 Основы проектирования баз данных
Тема рабочей программы (§2.2 с.7): Тема 2. Взаимосвязи в моделях и реляционный подход к построению моделей — Практическое занятие №4: проектирование реляционной БД
Номер занятия: Занятие 12 (Семестр 2)
Вид занятия: Практическое занятие (2 академических часа: основной регламент 80 минут, сокращенный 60 минут)

1. Дидактическая концепция и целеполагание

Данное занятие является кульминационным в цикле нормализации и моделирования реляционных баз данных Раздела 2. Опираясь на ранее изученные концепции сущностей, связей (занятия 02–04) и нормализации до 1NF/2NF (занятие 10), практическое занятие №4 ориентировано на сквозное инженерное проектирование реляционной БД: от анализа функциональных зависимостей в предметной области до генерации DDL-скрипта с декларативными ограничениями и наполнения базы эталонным набором тестовых кортежей.

Ключевой образовательный фокус: Закрепить строгое понимание третьей нормальной формы (3NF) как инструмента устранения транзитивных функциональных зависимостей вида X → Y → Z, освоить критерии нормальной формы Бойса-Кодда (BCNF) и сформировать устойчивый навык реализации ограничений целостности (PK, FK, UNIQUE, NOT NULL, CHECK) с топологически выверенным порядком выполнения скриптов.

Планируемые результаты освоения темы

Знания: формальное математическое определение 3NF и BCNF; различие между транзитивной и прямой зависимостью; условия сохранения зависимостей при декомпозиции без потерь (lossless-join decomposition); правила ссылочной целостности и синтаксис стандарта ANSI SQL DDL.
Умения: выявлять функциональные зависимости и детерминанты в предметных отношениях; декомпозировать таблицы с устранением транзитивных связей; писать корректные DDL-скрипты с декларативным контролем типов и диапазонов значений; формировать представительные тестовые данные (валидные, граничные и контрпримеры).
Формируемые компетенции: ОК 01 (выбирать способы решения задач профессиональной деятельности), ОК 02 (применять современные цифровые средства), ПК 1.1–ПК 1.5 (проектирование структур данных, разработка модулей информационных систем, контроль целостности данных).

2. Детальный поминутный хронометраж

Занятие рассчитано на 2 академических часа в двух регламентах: основном (80 минут) и сокращенном (60 минут).

Этап занятия	80 мин	60 мин	Деятельность преподавателя и дидактические акценты
1. Оргмомент и мотивация	5 мин	3 мин	Проверка явки. Демонстрация проблемы: транзитивная зависимость и аномалия обновления (Update Anomaly) на примере изменения оклада по должности при хранении в единой таблице сотрудников. Постановка инженерной задачи пары.
2. Теоретический экспресс-инструктаж	15 мин	10 мин	Разбор формулировки 3NF: $X \to Y \to Z$. Почему $Z$ зависит от $X$ опосредованно. Критерий Бойса-Кодда (BCNF) и его строгость относительно 3NF. Сквозной алгоритм проектирования: логическая схема → DDL (CREATE TABLE) → целостность → тестовый набор (INSERT INTO).
3. Демонстрация эталонного кейса	10 мин	7 мин	Демонстрация декомпозиции отношения на проекторе: исходная плоская таблица с транзитивной зависимостью, вынос детерминанта в независимую сущность-справочник, объявление внешнего ключа с политикой `ON DELETE RESTRICT`, вставка граничного кортежа.
4. Самостоятельная практическая работа	35 мин	28 мин	Студенты работают в интерактивном рабочем листе `zanyatie.html` по 4 вариантам (Отдел кадров, Аптечный склад, Учебный центр, Станция техобслуживания). Преподаватель осуществляет индивидуальный тьюторинг, контролируя отсутствие транзитивных зависимостей и корректность FK.
5. Экспертная верификация и разбор ошибок	10 мин	8 мин	Фронтальный анализ скриптов студентов. Разбор распространенных ошибок: нарушение топологического порядка создания таблиц, «слепой CASCADE», отсутствие предикатов CHECK на физические величины. Демонстрация юмористического слайда (xkcd Bobby Tables).
6. Рефлексия, подведение итогов, выдача ДЗ	5 мин	4 мин	Фиксация результатов в оценочной ведомости по критериальной рубрике (4-балльная шкала). Инструктаж по сохранению работы в PDF/распечатке. Разъяснение регламента резервного домашнего задания (`domashnee_zadanie.html`) для отсутствующих.

3. Теоретико-методический базис

3.1. Третья нормальная форма (3NF): математическая суть и устранение транзитивности

Отношение $R$ находится в третьей нормальной форме (3NF) тогда и только тогда, когда оно находится во 2NF (все неключевые атрибуты функционально полно зависят от первичного ключа), и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от первичного ключа.

Формальное определение (критерий Заньоло / Кодда): Отношение $R$ находится в 3NF, если для каждой нетривиальной функциональной зависимости $X \to A$ выполняется хотя бы одно из двух условий:

$X$ является суперключом отношения $R$ (то есть $X$ однозначно идентифицирует каждый кортеж);
$A$ является первичным атрибутом (то есть входит в состав хотя бы одного потенциального ключа отношения $R$).

Суть транзитивной зависимости: Пусть $X$ — первичный ключ отношения. Если существуют атрибуты $Y$ и $Z$, такие что:

$X \to Y$ (первичный ключ определяет $Y$),
$Y \not\to X$ ($Y$ не является потенциальным ключом и не определяет $X$ взаимно однозначно),
$Y \to Z$ ($Y$ функционально определяет $Z$),
$Z$ не входит в первичный ключ,

то имеет место зависимость $X \to Z$ через $Y$, называемая транзитивной. Присутствие атрибута $Z$ в одной таблице с $X$ порождает критические аномалии:

Аномалия избыточности: если 100 сотрудников занимают одну и ту же должность «Главный инженер», то базовый оклад этой должности будет дублироваться ровно 100 раз.
Аномалия обновления (Update Anomaly): при изменении базового оклада должности необходимо обновить 100 строк. Если сбой произойдет на 50-й строке, база окажется в рассогласованном состоянии.
Аномалия вставки (Insertion Anomaly): невозможно ввести в базу новую должность со штатным окладом, пока на нее не принят хотя бы один конкретный сотрудник (поскольку первичный ключ сотрудника не может быть NULL).
Аномалия удаления (Deletion Anomaly): при увольнении единственного сотрудника с данной должности безвозвратно теряется информация о существовании самой должности и утвержденном для нее окладе.

Алгоритм декомпозиции в 3NF:

Выявить функциональную зависимость $Y \to Z$, где $Y$ — не суперключ.
Создать новое отношение $R_2$ с атрибутами $(Y, Z)$, назначив $Y$ первичным ключом.
В исходном отношении $R_1$ исключить зависимый атрибут $Z$, оставив $Y$ в качестве внешнего ключа (Foreign Key), ссылающегося на $R_2(Y)$.
Проверить условие соединения без потерь: $R_1 \cap R_2 = Y$, причем $Y$ является ключом для $R_2$. По теореме Хита, декомпозиция гарантированно не порождает ложных кортежей (spurious tuples).

3.2. Нормальная форма Бойса-Кодда (BCNF): строгость и границы применимости

Нормальная форма Бойса-Кодда представляет собой усиленный вариант 3NF, устраняющий потенциальные аномалии в отношениях со сложной ключевой структурой.

Определение BCNF: Отношение $R$ находится в форме Бойса-Кодда (BCNF), если для каждой нетривиальной функциональной зависимости $X \to A$ детерминант $X$ является суперключом отношения $R$.

В чем разница между 3NF и BCNF? В определении 3NF допускается послабление: если $X$ не суперключ, отношение все равно остается в 3NF, если зависимый атрибут $A$ является первичным (входит в какой-либо потенциальный ключ). В BCNF это послабление полностью исключено. Таким образом, любое отношение в BCNF автоматически находится в 3NF, но обратное не всегда верно.

Когда отношение находится в 3NF, но НЕ в BCNF?
Это происходит исключительно тогда, когда отношение имеет два или более составных перекрывающихся (overlapping) потенциальных ключа.
Классический академический пример: Отношение Консультации(Студент, Преподаватель, Предмет).
Бизнес-правила:

Для каждого предмета студент прикреплен ровно к одному преподавателю: (Студент, Предмет) → Преподаватель.
Каждый преподаватель ведет ровно один предмет: Преподаватель → Предмет.

Потенциальные ключи отношения: {Студент, Предмет} и {Студент, Преподаватель}. Все атрибуты являются первичными! Следовательно, зависимость Преподаватель → Предмет удовлетворяет критерию 3NF (Предмет входит в составной ключ), но нарушает BCNF, так как Преподаватель сам по себе не является суперключом (он не определяет студента).
Компромисс BCNF: При декомпозиции отношения на (Преподаватель, Предмет) и (Студент, Преподаватель) достигается BCNF, но теряется функциональная зависимость (Студент, Предмет) → Преподаватель — для ее поддержания потребуется межтабличный триггер или ограничение на уровне приложения.

3.3. Сквозное проектирование: от ТЗ к физической схеме и DDL

Инженерный процесс создания базы данных состоит из 4 обязательных стадий:

Шаг 1 Концептуальный и логический анализ требований

Выделение независимых сущностей-справочников (родительские таблицы) и операционных сущностей (дочерние таблицы фактов/событий). Составление матрицы функциональных зависимостей и приведение схемы к 3NF.

Шаг 2 Топологическая сортировка и генерация DDL (CREATE TABLE)

СУБД требует строгого порядка исполнения: таблицы, на которые ссылаются внешние ключи (родители), обязаны создаваться раньше таблиц, содержащих эти внешние ключи. При удалении схемы порядок строго обратный: первыми удаляются дочерние таблицы (или используется каскадное удаление метаданных DROP TABLE ... CASCADE).

Шаг 3 Проектирование ограничений целостности

Реализация трех уровней целостности реляционной модели: целостность сущностей (PRIMARY KEY, UNIQUE), ссылочная целостность (FOREIGN KEY ... REFERENCES ... ON DELETE RESTRICT / CASCADE / SET NULL) и семантическая/доменная целостность (NOT NULL, CHECK, DEFAULT).

Шаг 4 Формирование контрольного набора тестовых кортежей (INSERT INTO)

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

4. Методика организации работы студентов по 4 вариантам

В практической части студенты распределяются по 4 отраслевым вариантам комплексного проектирования (не менее 3–4 таблиц в 3NF):

Вариант	Предметная область	Проектируемые сущности (3NF)	Дидактический фокус нормализации
Вариант 1	Отдел кадров предприятия	`departments` (Подразделения) `positions` (Должности) `employees` (Сотрудники) `appointment_orders` (Приказы о назначении)	Устранение транзитивной зависимости базового оклада от подразделения через вынос в справочник должностей: `Сотрудник → Должность → Базовый_оклад`. Спецификация DDL + 5 тестовых записей.
Вариант 2	Аптечный склад	`manufacturers` (Производители) `medicines` (Медикаменты) `batches` (Партии) `sales_invoices` (Расходные накладные)	Устранение транзитивной зависимости страны производителя через отдельную таблицу производителей: `Медикамент → Производитель → Страна`. Контроль целостности сроков годности (`expiry_date > manufacture_date`).
Вариант 3	Учебный центр доп. образования	`courses` (Учебные курсы) `study_groups` (Учебные группы) `students` (Слушатели) `payments` (Платежи)	Устранение транзитивной зависимости стоимости и длительности от группы через сущность курсов. Каскадный контроль статуса оплаты (CHECK на сумму взноса $\le$ стоимость курса).
Вариант 4	Станция техобслуживания (СТО)	`clients` (Клиенты) `vehicles` (Автомобили) `repair_orders` (Заказ-наряды) `work_items` (Выполненные работы)	Устранение транзитивной зависимости контактных данных владельца через связку Автомобиль → Клиент. Проверка связности кортежей и ограничений пробега/стоимости нормо-часа.

5. Типовые ошибки студентов и способы их предупреждения

Ошибка 1. Оставление транзитивной зависимости («Лень декомпозиции»)
Проявление: Студент оставляет поле salary или department_name прямо в таблице employees, заявляя: «Так проще писать SELECT, не нужен JOIN».
Методический прием: Попросить студента смоделировать ситуацию: в штате 500 программистов, оклад повысили на 15%. Студент должен увидеть, что один UPDATE строки должности против обновления 500 записей — это разница между надежной транзакцией и риском рассинхронизации данных.

Ошибка 2. «Слепой CASCADE» на справочниках верхнего уровня
Проявление: Указание ON DELETE CASCADE во внешнем ключе от employees к departments или от sales_invoices к medicines.
Методический прием: Спросить: «Что произойдет при расформировании отдела бухгалтерии?». При каскаде из базы сотрутся все сотрудники и начисления зарплаты за 10 лет. Преподаватель требует использовать строго ON DELETE RESTRICT (или NO ACTION), сохраняя финансово-юридическую историю.

Ошибка 3. Нарушение топологии при создании и заполнении таблиц
Проявление: Студент пытается выполнить CREATE TABLE repair_orders до создания vehicles, либо пытается вставить строку в repair_orders с несуществующим vehicle_id.
Методический прием: Преподаватель демонстрирует в консоли ошибку ядра СУБД: relation "vehicles" does not exist или insert or update on table violates foreign key constraint. Объясняется понятие ориентированного графа зависимостей схемы.

Ошибка 4. Формальные или бессмысленные ограничения CHECK
Проявление: Отсутствие проверки на положительность цен, допустимость дат (например, дата рождения в будущем) или статусов.
Методический прием: Ввести в поле студента тестовую запись с ценой -50000 рублей или датой окончания договора раньше даты начала. Напомнить, что бизнес-логика должна защищаться ядром СУБД, а не только валидацией на фронтенде.

6. Критериальное оценивание и педагогические приемы

Оценивание индивидуальной практической работы проводится строго по 4-балльной закрытой шкале (отраженной в .rubrika.md):

Баллы	Критерий оценивания	Показатели выполнения
2 балла	Нормализация схемы до 3NF / BCNF	Полное выявление и устранение всех транзитивных зависимостей. Корректное выделение справочников и операционных таблиц (3–4 таблицы). Обоснование отсутствия аномалий модификации.
1 балл	DDL-спецификация и ключи	Синтаксически безошибочные операторы `CREATE TABLE` в правильном топологическом порядке. Явное объявление первичных (PK) и внешних ключей (FK) с профессионально обоснованной политикой ссылочной целостности (RESTRICT/CASCADE). Адекватные ограничения `NOT NULL`, `UNIQUE`, `CHECK`.
1 балл	Тестовые данные и самопроверка	Пакет из не менее чем 5 согласованных операторов `INSERT INTO`, подтверждающих ссылочную связность. Наличие граничных проверок и демонстрация перехвата некорректных значений. Исчерпывающие аргументированные ответы на блок вопросов самоконтроля.

Эффективные педагогические приемы занятия

Прием «Ловушка аномалий»: перед началом проектирования предложить студентам внести новую должность в денормализованную таблицу без сотрудника. Невозможность заполнить PK без генерации фиктивного человека («Иван Иванович Пустышка») наглядно доказывает необходимость 3NF.
Прием «Парная верификация топологии»: студенты обмениваются DDL-скриптами и «вслепую» проверяют порядок создания таблиц и корректность типов внешних ключей. Если тип PK в родителе INT, а FK в дочерней таблице объявлен как VARCHAR — скрипт отклоняется.
Прием «Граничный стресс-тест»: преподаватель вводит в DDL студента граничный кортеж (например, дата увольнения раньше даты приема или отрицательная дозировка лекарства). Студент обязан показать, какое именно ограничение CHECK или TRIGGER блокирует эту запись.