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

Дисциплина: ОП.08 Основы проектирования баз данных (СПО 09.02.07 / 09.02.06)
Раздел программы (§2.2 с.7): Тема 2. Взаимосвязи в моделях и реляционный подход к построению моделей
Тема занятия: Практическое занятие №2: нормализация реляционной БД
Вид занятия: Практическое занятие (формирование и совершенствование практических умений и навыков декомпозиции отношений)

1. Дидактические цели и планируемые результаты занятия

Основная дидактическая цель занятия — сформировать у обучающихся устойчивые инженерные навыки нормализации реляционных структур данных: от денормализованных универсальных отношений (суррогатных журналов) через первую нормальную форму (1НФ) ко второй нормальной форме (2НФ) с устранением частичных функциональных зависимостей и сохранением данных без потерь (Lossless-Join Decomposition).

Формируемые знания и умения:

Знания: фундаментальная теория реляционной нормализации Эдгара Кодда; сущность и математическое определение функциональной зависимости (FD: X → Y); понятие потенциального, первичного и составного ключа; критерии первой нормальной формы (1НФ — атомарность значений, отсутствие повторяющихся групп); критерии второй нормальной формы (2НФ — нахождение в 1НФ и отсутствие частичных функциональных зависимостей неключевых атрибутов от части составного первичного ключа); виды аномалий манипулирования данными (вставки, удаления, модификации); теорема Хита и критерий декомпозиции без потерь соединения (Lossless-Join).
Умения: выявлять полный спектр функциональных зависимостей в предметной области; определять составной первичный ключ в денормализованной таблице; строить диаграммы функциональных зависимостей; разделять зависимости на полные и частичные; выполнять строгую проекционную декомпозицию исходного универсального отношения на совокупность нормализованных отношений; доказывать отсутствие аномалий и сохранение зависимостей; сопоставлять схемы связанных таблиц через первичные и внешние ключи (PK → FK).

Профессиональный контекст (СПО): Студенты должны осознать, что нормализация — это не абстрактная математическая гимнастика, а строгий инженерный метод проектирования надежных транзакционных OLTP-баз данных, гарантирующий нулевую избыточность, физическую компактность и целостность бизнес-информации при сбоях и параллельных модификациях.

2. Поминутный тайминг учебного занятия

В зависимости от расписания учебной части СИЭУиП занятие реализуется по одному из двух регламентов:

Сравнительная сетка хронометража:

Этап занятия	Основной регламент (80 мин)	Сокращенный регламент (60 мин)	Ключевые контрольные точки этапа
1. Организационный момент и мотивация	7 минут	5 минут	Проверка явки; постановка проблемы: «Что случится с бухгалтерией, если при удалении одной накладной исчезнут данные о поставщике?»; актуализация темы и критериев оценки (4 балла).
2. Теоретический инструктаж и разбор эталона	23 минуты	15 минут	Презентация (слайды 1–14); теория Кодда, FD (X → Y), 1НФ и 2НФ; демонстрация сквозного кейса «Универсальная таблица заказов интернет-магазина»; вывод функциональных зависимостей и декомпозиция на доске.
3. Индивидуальная практическая работа	35 минут	28 минут	Самостоятельная работа в интерактивном листе `zanyatie.html` по 4 вариантам; составление формул FD, выделение частичных зависимостей, разбиение на 3 связанные таблицы 2НФ, проверка в localStorage.
4. Экспертный разбор типичных ошибок	10 минут	8 минут	Фронтальный анализ ошибок (потеря внешнего ключа при декомпозиции, сохранение транзитивных зависимостей в 2НФ, путаница детерминанта и зависимой части); демонстрация слайда 15 (культовый мем xkcd «Bobby Tables»).
5. Подведение итогов, рефлексия и ДЗ	5 минут	4 минут	Оценка результатов по закрытой критериальной рубрике; самопроверка чек-листа; инструктаж по резервному дистанционному заданию `domashnee_zadanie.html`.

3. Фундаментальная теория нормализации реляционных отношений

3.1. Теория Эдгара Кодда и истоки нормализации

В 1970 году Эдгар Франк Кодд (Edgar F. Codd) в своей фундаментальной статье «A Relational Model of Data for Large Shared Data Banks» сформулировал реляционный подход. Главной угрозой реляционных баз данных Кодд назвал аномалии манипулирования данными, возникающие из-за дублирования фактов в плоских неструктурированных таблицах.

3.2. Классификация аномалий манипулирования данными

Вид аномалии	Физическое проявление	Практический пример из универсальной ведомости
Аномалия модификации (Update Anomaly)	Изменение одного атрибута сущности требует массового обновления множества строк отношения. При сбое или неполном обновлении возникает рассогласованность данных (data inconsistency).	Если поставщик «СеверСнаб» изменил контактный номер или юридический адрес, нам придется перезаписать его в тысячах строк накладных. Пропуск хотя бы одной строки создаст противоречивое состояние базы данных.
Аномалия вставки (Insertion Anomaly)	Невозможность зафиксировать факт о существовании одной независимой сущности без одновременного создания записи о другой (в силу ограничений первичного ключа `NOT NULL`).	Нельзя внести в базу данных информацию о новом товаре на складе, пока по нему не выписана хотя бы одна накладная, так как атрибут `Номер_накладной` входит в первичный ключ и не может быть `NULL`.
Аномалия удаления (Deletion Anomaly)	Удаление кортежа, содержащего информацию об одном событии или объекте, непреднамеренно приводит к безвозвратному уничтожению критически важных данных о совершенно другом объекте.	Если мы удаляем ошибочную накладную, содержащую поставку единственного уникального товара, мы безвозвратно теряем карточку самого товара с его характеристиками и ценой закупки.

3.3. Функциональные зависимости (Functional Dependencies, FD)

Определение: Пусть задано отношение $R$ со схемой атрибутов $U$. Говорят, что атрибут (или подмножество атрибутов) $Y$ функционально зависит от атрибута (или подмножества атрибутов) $X$ (обозначается $X \rightarrow Y$), если в любой допустимый момент времени для любых двух кортежей $t_1, t_2 \in R$ из равенства их значений на атрибутах $X$ следует равенство их значений на атрибутах $Y$:

t1[X] = t2[X]  ⇒  t1[Y] = t2[Y]

Множество $X$ называется детерминантом (determinant), а $Y$ — зависимой частью (dependent).

3.4. Полная и частичная функциональная зависимость

Ключевое различие для 2НФ:

Полная функциональная зависимость (Full FD): Атрибут $Y$ функционально зависит от составного детерминанта $X$, но не зависит ни от какого собственного подмножества $X' \subset X$.
Пример: {Номер_накладной, Артикул_товара} → Количество. Значение количества имеет смысл исключительно на пересечении конкретной накладной и конкретного товара! Ни отдельно номер накладной, ни артикул товара не определяют количество в строке.
Частичная (неполная) зависимость (Partial FD): Атрибут $Y$ функционально зависит от части составного первичного ключа $X$.
Пример: {Номер_накладной, Артикул_товара} → Название_товара. Однако в силу свойств предметной области справедливо Артикул_товара → Название_товара. Зависимость названия товара от всего составного ключа является частичной, что грубо нарушает требования 2НФ!

3.5. Приведение к 1НФ и 2НФ

Первая нормальная форма (1NF): Отношение находится в 1НФ тогда и только тогда, когда все используемые домены содержат только скалярные (атомарные, неделимые) значения, а само отношение не содержит повторяющихся групп или массивов в ячейках. Таблица представляет собой строго двумерную матрицу фиксированной размерности с уникальным первичным ключом.
Вторая нормальная форма (2NF): Отношение находится в 2НФ тогда и только тогда, когда оно уже находится в 1НФ, и каждый неключевой атрибут функционально полно зависит от первичного потенциального ключа. Иными словами, в отношении полностью отсутствуют частичные зависимости неключевых атрибутов от составного первичного ключа!

3.6. Декомпозиция без потерь (Lossless-Join Decomposition)

Разбиение отношения $R(U)$ на проекции $R_1(U_1)$ и $R_2(U_2)$, где $U = U_1 \cup U_2$, называется декомпозицией без потерь, если естественное соединение проекций в точности воспроизводит исходное отношение:

R = R1 ⋈ R2  (то есть π[U1](R) ⋈ π[U2](R) = R)

Теорема Хита (Heath's Theorem): Декомпозиция отношения $R(A, B, C)$ на проекции $R_1(A, B)$ и $R_2(A, C)$ является декомпозицией без потерь тогда и только тогда, когда среди функциональных зависимостей отношения $R$ истинна хотя бы одна из зависимостей: $A \cap B \rightarrow A$ или $A \cap B \rightarrow C$. Для нашего случая: общий атрибут декомпозированных таблиц обязан являться первичным (или потенциальным) ключом хотя бы в одной из полученных проекций!

4. Типовые ошибки студентов и педагогические приемы

Типовая ошибка студента	Причина и сущность заблуждения	Педагогический прием и контрпример
1. Потеря внешнего ключа (FK) при декомпозиции	Студент выделяет сущности в отдельные таблицы, но «забывает» оставить связующий внешний ключ в подчиненной таблице позиций.	Прием «Разрыв цепочки»: Попросите студента восстановить естественным соединением `JOIN`, в какую именно накладную был включен данный товар. Без внешнего ключа это математически невозможно (возникает ложное декартово произведение кортежей).
2. Путаница детерминанта и зависимого атрибута	Студент пишет `Количество → Артикул_товара` вместо `{Номер_накладной, Артикул} → Количество`.	Прием «Проверка на дубликат»: Спросите: «Если на складе несколько разных позиций заказаны в количестве 10 штук, можем ли мы по числу 10 однозначно сказать, какой именно товар лежит на полке? Нет! Значит, детерминантом является артикул, а не количество».
3. Попытка устранить транзитивную зависимость на этапе 2НФ	Студенты часто забегают вперед и пытаются вынести зависимость `Поставщик → Телефон_поставщика`, забывая о первоочередной цели 2НФ — ликвидации частичных зависимостей от составного ключа.	Прием «Лестница Кодда»: Объясните правило лестницы: «Нельзя прыгнуть на третью ступеньку (3НФ), не встав прочно на вторую (2НФ). В 2НФ мы ликвидируем зависимость неключевых полей от половины составного ключа! А зависимости неключевых полей друг от друга — это предмет следующей темы (3НФ)».
4. Неверное определение первичного ключа исходной плоской таблицы	Студент считает, что первичным ключом денормализованной ведомости является только `Номер_накладной` или суррогатный `ID_строки`.	Прием «Семантический дубликат кортежа»: Покажите, что в одной накладной содержится несколько строк товаров. Если ключом объявить только `Номер_накладной`, СУБД отвергнет вставку второй строки из-за нарушения `PRIMARY KEY UNIQUE`. Следовательно, естественный ключ обязан быть составным.

5. Методические указания по дифференцированной оценке

Оценивание результатов практической работы обучающихся осуществляется по строгой закрытой 4-балльной критериальной матрице (максимум 4 академических балла):

Критерий 1 (2 балла): Полнота и математическая строгость выявления функциональных зависимостей (FD), четкое разделение зависимостей на полные и частичные, доказательство частичного характера зависимостей от составного ключа.
Критерий 2 (1 балл): Корректность проекционной декомпозиции универсального отношения без потерь данных (Lossless-Join) с явным указанием первичных (PK) и внешних (FK) ключей в результирующих схемах отношений 2НФ.
Критерий 3 (1 балл): Качественное обоснование ликвидации аномалий манипулирования (вставки, удаления, обновления) и успешное прохождение контрольного чек-листа самопроверки.

Академическая шкала: 4 балла — отлично («5»); 3 балла — хорошо («4»); 2 балла — удовлетворительно («3»); 0–1 балл — неудовлетворительно («2»).