Этап 1. Организационный момент и мотивация
Хронометраж: 80 минут — 5 мин / 60 минут — 3 мин
Действие преподавателя: Приветствует учебную группу, фиксирует отсутствующих в журнале, проверяет готовность интерактивной панели и персональных компьютеров обучающихся.
Прямая речь преподавателя:
«Здравствуйте, коллеги! Садитесь. На прошлом занятии мы с вами научились раскладывать таблицы по полочкам: выявляли функциональные зависимости, избавлялись от частичных и транзитивных связей, доводили схему до третьей нормальной формы и нормальной формы Бойса-Кодда (НФБК). Казалось бы: разделили широкую таблицу на две компактные — и задача решена!
Но представьте реальный случай из практики крупного маркетплейса: проектировщики разделили единую таблицу заказов на две таблицы — данные о клиентах с адресами доставки и строки заказов с товарами. Однако в качестве связующего звена разработчик выбрал не уникальный идентификатор заказа или клиента, а название города! Что произошло при первой же попытке сформировать чек? Все клиенты из Сургута объединились со всеми заказами, сделанными в Сургуте. Покупатель Иван увидел в своем личном кабинете товары, заказанные Алексеем, а система списала деньги за чужие покупки.
Такие паразитные данные в теории баз данных называют
ложными кортежами (Spurious Tuples), а само явление —
декомпозицией с потерями. Сегодня на занятии №16 наша цель — освоить строгие математические и практические методы проверки связей между отношениями. Тема урока:
"Нормализация БД: проверка связей". Запишите тему в свои конспекты.»
Действие студентов: Приветствуют преподавателя, организуют рабочие места, записывают тему занятия в конспекты.
Этап 2. Актуализация опорных знаний
Хронометраж: 80 минут — 8 мин / 60 минут — 5 мин
Действие преподавателя: Организует фронтальный экспресс-опрос группы для актуализации ключевых понятий предыдущих занятий (ФЗ, замыкание, ключи, нормальные формы).
Прямая речь преподавателя:
«Прежде чем перейти к критериям проверки связей, давайте вспомним наш теоретический фундамент. Кто напомнит строгой формулировкой: что такое функциональная зависимость между множествами атрибутов X и Y?»
Ответ обучающегося (Смирнов):
«Функциональная зависимость X → Y означает, что для любых двух кортежей отношения, если совпадают их значения по атрибутам X, то обязательно должны совпадать и значения по атрибутам Y. То есть каждому значению детерминанта X соответствует строго одно значение Y.»
Прямая речь преподавателя:
«Верно. А что отличает суперключ от потенциального первичного ключа?»
Ответ обучающегося (Иванова):
«Суперключ — это любое подмножество атрибутов, функционально определяющее все остальные атрибуты отношения. А потенциальный ключ — это минимальный суперключ, то есть суперключ, из которого нельзя удалить ни одного атрибута без потери свойства однозначной идентификации кортежа.»
Прямая речь преподавателя:
«Отлично. И последний вопрос на размышление: если мы привели каждую полученную таблицу к 3НФ или даже к НФБК, гарантирует ли это, что при их соединении мы гарантированно получим корректные данные без мусора и искажений? Сегодня мы докажем, что одной нормализации таблиц "по отдельности" недостаточно: необходимо проверять корректность связей между ними!»
Этап 3. Сообщение новых теоретических знаний
Хронометраж: 80 минут — 32 мин / 60 минут — 27 мин
Действие преподавателя: Включает интерактивную презентацию prezentaciya.html, последовательно раскрывает теоретические концепции с фиксацией ключевых определений.
Прямая речь преподавателя (Слайды 2–4: Соединение без потерь и ложные кортежи):
«Переходим к первому и главному инварианту декомпозиции — свойству
соединения без потерь данных (Lossless-Join).
Обратите внимание на терминологический парадокс: в повседневной жизни слово "потеря" ассоциируется с тем, что что-то стерлось, удалилось, пропало. Но в реляционной алгебре операция естественного соединения проекций никогда не теряет исходные строки: выполняется строгое математическое включение
R ⊆ R1 ⋈ R2.
Что же тогда "теряется"? Теряется
достоверность информации! Когда мы соединяем таблицы по столбцам, которые не обеспечивают однозначную связь, декартово произведение по одинаковым значениям плодит строки-призраки (Spurious Tuples). Взгляните на слайд 4: у нас было всего 2 студента и 2 куратора на факультете ИТ. Соединили по неключевому полю
Факультет — и получили 4 строки! Система "придумала", что Иванов прикреплен к куратору Сидорову, а Смирнов — к Петрову. Для финансовой СУБД это катастрофа.»
Прямая речь преподавателя (Слайд 5: Теорема Риссанена):
«Чтобы инженеру не гадать и не проверять вручную тысячи строк, в 1977 году финский ученый Йорма Риссанен доказал строгий математический критерий для декомпозиции таблицы на две подсхемы. Запишите формулировку теоремы Риссанена:
Декомпозиция схемы R на {R1, R2} является соединением без потерь относительно множества ФЗ F тогда и только тогда, когда:
(R1 ∩ R2 → R1) ∈ F⁺ ИЛИ (R1 ∩ R2 → R2) ∈ F⁺.
Переведем эту формулу на язык практикующего инженера:
общие столбцы двух таблиц (их пересечение R1 ∩ R2) обязаны быть суперключом хотя бы для одной из этих таблиц! Если общий столбец не является ключом ни в R1, ни в R2 — декомпозиция гарантированно породит паразитные кортежи. Запомните это железное правило!»
Прямая речь преподавателя (Слайды 6–7: Алгоритм табло):
«А что делать, если мы разбили отношение не на две, а на три, четыре или пять таблиц? Теорема Риссанена работает только для парных разбиений. Для произвольного числа таблиц применяется матричный
алгоритм табло (Tableau Algorithm), предложенный Ахо, Бири и Ульманом.
Посмотрите на слайды 6 и 7. Мы рисуем матрицу: строки — это наши таблицы R1, R2, R3, а столбцы — все атрибуты системы A, B, C, D. Если атрибут входит в таблицу, ставим универсальный символ
aⱼ. Если нет — локальный символ
bᵢⱼ.
Затем мы начинаем по очереди применять наши функциональные зависимости. Например, зависимость B → C означает: если у двух строк совпадают значения в столбце B, то мы обязаны уравнять их значения в столбце C. При этом универсальный символ
a всегда имеет приоритет над локальным
b.
Критерий завершения алгоритма: если хотя бы одна строка матрицы полностью заполнилась символами
a₁…aₙ, соединение без потерь доказано математически строго!»
Прямая речь преподавателя (Слайд 8: Сохранение функциональных зависимостей):
«Второй важнейший инвариант —
сохранение функциональных зависимостей (Dependency Preservation).
Декомпозиция сохраняет зависимости, если объединение всех локальных ФЗ в полученных таблицах позволяет логически вывести все исходные бизнес-правила:
(∪ π_{Ri}(F))⁺ = F⁺.
Почему это важно? Если зависимость оказывается разорванной между таблицами, СУБД теряет возможность контролировать ее через стандартный индекс первичного ключа или UNIQUE. Каждая операция INSERT в одну таблицу потребует соединения с другой таблицей, что в разы замедляет работу высоконагруженной базы.
Здесь существует классический компромисс:
третья нормальная форма (3НФ) всегда позволяет достичь одновременно соединения без потерь и сохранения всех ФЗ. А вот нормальная форма Бойса-Кодда (НФБК) гарантирует соединение без потерь, но сохранение всех зависимостей гарантировать может не всегда!»
Прямая речь преподавателя (Слайды 9–12: Многозначные зависимости, 4НФ и 5НФ):
«Идем дальше. Представьте отношение
R(Преподаватель, Дисциплина, Учебник). Преподаватель читает курсы "Базы данных" и "Сети", и рекомендует учебники Дейта и Коннолли. Причем выбор учебника никак не зависит от читаемого курса.
В этой таблице вообще нет функциональных зависимостей! Все три атрибута входят в составной первичный ключ. Отношение формально находится в НФБК! Но чтобы отразить независимость списков, мы обязаны хранить декартово произведение: 2 курса × 2 учебника = 4 строки. Добавили 3-й учебник — вынуждены вставить еще 2 строки!
Это проявление
многозначной зависимости (MVD: X ↠ Y).
Рональд Фейгин доказал: декомпозиция таблицы на две части без потерь возможна тогда и только тогда, когда в ней есть многозначная зависимость.
Отсюда определение
Четвертой нормальной формы (4NF): отношение находится в 4НФ, если оно в НФБК и для любой нетривиальной зависимости X ↠ Y множество X является суперключом!
Решение: разбиваем отношение на две независимые таблицы:
R1(Преподаватель, Дисциплина) и
R2(Преподаватель, Учебник).
А если ограничения циклически связывают три и более сущности, применяется зависимость соединения (Join Dependency) и
Пятая нормальная форма (5НФ / PJNF), являющаяся теоретическим пределом нормализации.»
Прямая речь преподавателя (Слайды 13–14: Осознанная денормализация в OLAP):
«И наконец, важнейший инженерный вопрос: нужно ли нормализовать абсолютно всё и всегда?
В транзакционных системах (OLTP — интернет-магазины, банковские переводы) нормализация до 3НФ/4НФ строго обязательна. Но в аналитических системах (OLAP, хранилища данных DWH, витрины отчетов), где хранятся сотни миллионов строк и выполняются тяжелые аналитические агрегации
GROUP BY, соединение 10–15 нормализованных таблиц просто убьет процессор и диск.
В таких системах применяется
контролируемая денормализация: построение схем "Звезда" (Star Schema), репликация артикулов и наименований товаров прямо в строки чеков, использование материализованных представлений.
Но запомните правило взрослого архитектора:
денормализация допустима только при наличии компенсирующих механизмов — триггеров СУБД, потоков захвата изменений CDC (Debezium/Kafka) или ночных ETL-пакетов аудита данных.»
Этап 4. Демонстрация сквозного инженерного кейса
Хронометраж: 80 минут — 12 мин / 60 минут — 10 мин
Действие преподавателя: Переходит к доске / интерактивной панели. Разбирает сквозной пример проверки схемы R(A, B, C) с ФЗ {A → B} на два конкурирующих варианта декомпозиции.
Прямая речь преподавателя:
«Коллеги, давайте проверим два варианта декомпозиции отношения
R(A, B, C) с функциональной зависимостью
A → B.
Случай 1: Разбиваем на
R1(A, B) и
R2(A, C).
Проверяем по теореме Риссанена:
1) Общие атрибуты:
R1 ∩ R2 = {A}.
2) Считаем замыкание:
A⁺ = {A, B}.
3) Замыкание
{A, B} содержит все атрибуты отношения R1! Значит,
A → R1 выполняется, A — суперключ для R1.
Вывод: декомпозиция гарантированно
без потерь!
Случай 2: Неопытный разработчик предложил разбить на
R1(A, B) и
R3(B, C).
1) Пересечение:
R1 ∩ R3 = {B}.
2) Считаем замыкание: так как у нас есть только A → B, по атрибуту B мы не можем определить ничего, кроме самого B. Значит,
B⁺ = {B}.
3) Содержит ли {B} атрибуты {A, B}? Нет, A не входит. Содержит ли {B} атрибуты {B, C}? Нет, C не входит.
Вывод по Риссанену: ни одно условие не выполнено! Декомпозиция
породит ложные кортежи!
Посмотрите, насколько прост и элегантен математический анализ: нам потребовалось всего 3 строчки вычислений, чтобы спасти базу данных от критической уязвимости!»
Этап 6. Итог занятия, рефлексия, разбор ошибок и ДЗ
Хронометраж: 80 минут — 8 мин / 60 минут — 4 мин
Действие преподавателя: Выводит на экран слайд 15 презентации с культовым веб-комиксом Bobby Tables (xkcd #327), организует подведение итогов и рефлексию.
Прямая речь преподавателя:
«Коллеги, время практикума вышло. Взгляните на экран (Слайд 15 презентации).
Перед вами легендарный комикс Рэндалла Манро про "Маленького Бобби Тейблса" — ученика, чье имя мама записала как
Robert'); DROP TABLE Students;--.
Этот мем напоминает каждому инженеру: база данных беспощадна к ошибкам проектирования. Если вы забыли проверить связи, если допустили декомпозицию с потерями или слепо денормализовали таблицы без механизмов синхронизации — база накажет вас в самый неподходящий момент под пиковой нагрузкой в продакшене.
Давайте разберем две типичные ошибки, которые я заметил во время вашей работы:
1) Некоторые из вас написали, что при декомпозиции с потерями "теряются строки исходной таблицы". Еще раз: строки не теряются, они размножаются и искажаются!
2) В задании по маркетплейсу не забывайте: цена товара в момент покупки должна быть жестко зафиксирована в строке чека, иначе при изменении цен в каталоге исказится вся финансовая отчетность за прошлые годы.
Оценивание ваших работ будет проводиться по 4-балльной закрытой рубрике: 2 балла за строгость доказательства соединения без потерь, 1 балл за анализ зависимостей/MVD и 1 балл за обоснование денормализации и блок самопроверки.
Домашнее задание: В папке материалов расположен файл
domashnee_zadanie.html. Это резервное дистанционное задание. Обратите внимание на плашку: вы выполняете его только в случае пропуска занятия или если я назначу его персонально для повышения балла. Срок сдачи — до следующей пары. Нажмите кнопку сохранения в рабочем листе, сохраните страницу в PDF. Всем спасибо за продуктивную работу, занятие окончено!»