Сценарий занятия: Прямая речь преподавателя и учебные интеракции (80 минут)
Этап 1. Организационный момент и мотивация к учебной деятельности
Тайминг: 7 минут (80 мин) / 5 минут (60 мин)
Преподаватель входит в аудиторию, приветствует обучающихся, отмечает присутствующих в электронном журнале и включает мультимедийный проектор с заставкой презентации (Слайд 1: только название темы «Практическое занятие №2: нормализация реляционной БД»).
Преподаватель: «Здравствуйте, коллеги! Садитесь. Сегодня у нас второе практическое занятие по реляционному проектированию, посвященное одной из самых фундаментальных тем в карьере любого разработчика и администратора баз данных — нормализации реляционных отношений.
Представьте типичную ситуацию: молодая IT-компания разрабатывает систему учета для оптовой базы. Начинающий программист, чтобы "не усложнять", создает одну гигантскую плоскую таблицу в Excel или в СУБД: в каждой строке записывает номер накладной, дату, реквизиты поставщика, артикул товара, его название, цену и количество. На первый взгляд все работает. Но проходит месяц, и начинается настоящий производственный хаос.
Коллеги, вопрос к аудитории: что произойдет в такой системе, если наш ключевой поставщик сменит контактный телефон или юридический адрес? А что произойдет, если мы захотим занести в каталог новый товар, по которому еще не оформлено ни одной накладной? Прошу ваши гипотезы!»
Обучающийся (Иванов): «Если поставщик сменил телефон, нам придется вручную переписывать его в тысячах строк старых накладных. Если программа где-то зависнет или пропустит строчку, в базе будут разные телефоны одного и того же поставщика!»
Обучающийся (Смирнова): «А с новым товаром вообще тупик: если номер накладной входит в первичный ключ, поле не может быть пустым (NULL). Мы просто не сможем внести карточку товара, пока кто-то его не купит!»
Преподаватель: «Блестяще! Вы только что абсолютно точно сформулировали две классические аномалии реляционных баз данных: аномалию модификации и аномалию вставки. А если бухгалтер удалит ошибочную накладную, содержащую единственный редкий товар, то вместе с накладной из базы навсегда сотрется и сам товар — это аномалия удаления.
Чтобы защитить базу данных от этого кошмара, создатель реляционной модели Эдгар Кодд разработал строгий математический аппарат нормализации. Сегодня наша цель — научиться приводить любое денормализованное отношение ко Второй нормальной форме (2НФ) через выявление функциональных зависимостей и декомпозицию без потерь.
Работать будем в интерактивных электронных рабочих листах по 4 индивидуальным вариантам. Итоговая работа оценивается по строгой 4-балльной шкале: 2 балла за выявление функциональных зависимостей, 1 балл за декомпозицию таблиц без потерь и 1 балл за обоснование отсутствия аномалий и самопроверку.»
Этап 2. Теоретический инструктаж и разбор эталонного кейса
Тайминг: 23 минуты (80 мин) / 15 минут (60 мин)
Преподаватель переключает презентацию на Слайды 2–11, открывает маркерную доску и начинает подробный математический разбор функциональных зависимостей.
Преподаватель: «Перейдем к математическому базису. Запишите в конспекты ключевое понятие: Функциональная зависимость (Functional Dependency, FD).
Мы говорим, что атрибут Y функционально зависит от атрибута X, и пишем X → Y, если во всей таблице двум одинаковым значениям X всегда и неизбежно соответствует строго одинаковое значение Y. Атрибут X называется детерминантом. Например, ИНН → Название_компании. Зная ИНН, мы однозначно знаем название фирмы.
Теперь самое главное для 2НФ: различие между полной и частичной функциональной зависимостью!
Внимание на доску! Пусть у нас есть составной первичный ключ из двух полей: {Номер_накладной, Артикул_товара}.
Зависимость {Номер_накладной, Артикул_товара} → Количество является полной: количество имеет смысл только для конкретного товара в конкретной накладной. Ни накладная отдельно, ни артикул отдельно не определяют, сколько штук лежит в этой строке!
А теперь посмотрим на наименование товара: {Номер_накладной, Артикул_товара} → Название_товара. Но ведь название товара зависит исключительно от его артикула: Артикул_товара → Название_товара! Зависимость названия от всего составного ключа является частичной (неполной). Точно так же дата накладной зависит только от левой части ключа: Номер_накладной → Дата_поставки.
Именно частичные зависимости порождают чудовищное дублирование!
Сформулируем критерий Второй нормальной формы:
Отношение находится во 2НФ тогда и только тогда, когда оно находится в 1НФ (все значения атомарны, строки уникальны), и каждый неключевой атрибут функционально полно зависит от первичного ключа отношения. То есть частичные зависимости полностью устранены!
Запомните важнейшее следствие: если отношение находится в 1НФ и его первичный ключ является простым (состоит всего из одного поля), то отношение автоматически находится во 2НФ! Частичной зависимости просто неоткуда взяться, ведь у ключа нет "половинок"!»
Преподаватель чертит на доске эталонную схему декомпозиции сквозного кейса интернет-магазина.
Преподаватель: «Как же устранить частичные зависимости? Правило декомпозиции Кодда предельно логично:
1. Все атрибуты, зависящие только от номера накладной, мы выносим в отдельную таблицу Накладные (Invoices) с первичным ключом Номер_накладной.
2. Все атрибуты, зависящие только от артикула товара, мы выносим в таблицу Товары (Products) с первичным ключом Артикул_товара.
3. А составной ключ {Номер_накладной, Артикул_товара} и атрибуты с полной зависимостью (Количество) остаются в связующей таблице Позиции_накладной (Invoice_Items). При этом оба поля составного ключа становятся внешними ключами (Foreign Keys), ссылающимися на родительские таблицы!
Это называется декомпозицией без потерь (Lossless-Join Decomposition). По теореме Хита, если мы выполним операцию естественного соединения (Накладные ⋈ Позиции) ⋈ Товары, мы в точности до байта восстановим исходную ведомость без потерь и без единой ложной строки!»
Этап 3. Индивидуальная практическая работа студентов
Тайминг: 35 минут (80 мин) / 28 минут (60 мин)
Студенты рассаживаются за персональные компьютеры и открывают файл zanyatie.html. Преподаватель распределяет 4 варианта:
Вариант 1 — Оптовые поставки товаров;
Вариант 2 — Регистрация пациентов поликлиники;
Вариант 3 — Успеваемость студентов в сессию;
Вариант 4 — Путевые листы автоколонны.
Преподаватель: «Коллеги, открываем интерактивный лист zanyatie.html. Введите свои реальные фамилию, имя и учебную группу в шапке документа.
Ваша задача состоит из 4 обязательных шагов:
Шаг 1: Определить составной первичный ключ исходной таблицы и обосновать, почему ни одно отдельное поле не может быть ключом.
Шаг 2: Выписать все функциональные зависимости предметной области и четко пометить, какие из них полные, а какие частичные.
Шаг 3: Выполнить декомпозицию исходного универсального отношения на 3 связанные таблицы 2НФ, указав для каждой первичные ключи (PK) и внешние ключи (FK).
Шаг 4: Доказать устранение всех трех видов аномалий (вставки, удаления, модификации) и заполнить чек-лист самопроверки.
Все ваши ответы сохраняются в браузере автоматически. Приступайте, я буду подходить и консультировать индивидуально.»
Преподаватель осуществляет индивидуальный контроль, обходя аудиторию. Консультирует студентов по сложным моментам.
Обучающийся (Вариант 2, поликлиника): «У меня в журнале регистратуры есть ФИО_врача и Специальность_врача. От чего они зависят? От номера талона или от кабинета?»
Преподаватель: «Отличный вопрос! Посмотрим на предметную логику варианта 2. Врач прикреплен к конкретному талону приема. Но зависит ли специальность врача от номера талона пациента? Нет, специальность определяет сам врач (или кабинет, где он ведет прием по штатному расписанию). В талоне фиксируется факт приема конкретного пациента. Поэтому при декомпозиции мы разделяем: данные пациента (СНИЛС, ФИО, дата рождения), данные приема/врача (Номер_талона, Кабинет, Врач, Специальность) и связующую таблицу диагнозов посещения!»
Этап 4. Экспертный разбор типичных ошибок
Тайминг: 10 минут (80 мин) / 8 минут (60 мин)
Преподаватель привлекает внимание всей группы к доске для разбора выявленных в ходе практики ошибок.
Преподаватель: «Внимание на доску, коллеги! Давайте разберем три критические ошибки, которые я заметил в нескольких работах.
Ошибка №1: "Потерянный внешний ключ". Студент создал таблицу "Накладные" и таблицу "Товары", а в третьей таблице оставил только столбцы Количество и Цена, совершенно забыв про Номер_накладной и Артикул! Если вы не оставите внешние ключи, связующая таблица превратится в бессмысленный набор чисел. Вы физически не сможете соединить таблицы через JOIN! Помните: декомпозиция без потерь требует сохранения ключевых атрибутов.
Ошибка №2: "Стрелка не в ту сторону". Некоторые записали: Количество → Артикул_товара. Коллеги, детерминант — это то, ЧТО определяет. Если на складе 10 разных позиций хранятся в количестве по 5 штук, разве по числу 5 мы сможем угадать артикул? Конечно нет! Детерминантом является артикул и накладная, а не количество.
Ошибка №3: Попытка забежать в 3НФ. В варианте 3 некоторые попытались вынести группу в отдельную таблицу от студента. Это похвально, но это предмет следующего занятия (устранение транзитивных зависимостей в 3НФ). Во 2НФ наш фокус — исключительно ликвидация зависимостей от частей составного ключа!
А теперь обратите внимание на экран — Слайд 15 нашей презентации. Перед вами самый знаменитый комикс в истории баз данных: "Little Bobby Tables" от Рэндалла Манро (xkcd #327). Мама назвала сына Robert'); DROP TABLE Students;--, и когда школа попыталась вставить его имя в плоскую невалидированную таблицу, скрипт удалил всю таблицу студентов за год! Этот мем напоминает нам: пренебрежение реляционной структурой, типами данных и целостностью ключей ведет к катастрофическим последствиям для бизнеса.»
Этап 5. Подведение итогов занятия, рефлексия и домашнее задание
Тайминг: 5 минут (80 мин) / 4 минуты (60 мин)
Преподаватель: «Подведем итоги нашего занятия. Сегодня мы совершили качественный инженерный переход: от неструктурированных плоских документов к математически выверенным реляционным моделям во Второй нормальной форме.
Каждый из вас доказал на практике:
1. Как устранять частичные зависимости от составного ключа.
2. Как строить декомпозицию без потерь соединения (Lossless-Join).
3. Как новая структура навсегда защищает систему от аномалий вставки, удаления и модификации.
Проверьте, чтобы в ваших интерактивных листах была нажата кнопка "Сохранить данные". Ваши работы оцениваются по 4-балльной матрице и идут в ведомость.
Домашнее задание: В папке материалов размещен файл domashnee_zadanie.html. Это резервное дистанционное задание. Выполняйте его только в том случае, если вам назначена индивидуальная отработка или если вы пропустили сегодняшнее очное занятие. Срок сдачи — до следующей практической пары.
Всем спасибо за отличную продуктивную работу! Занятие окончено, до свидания!»