Этап 1. Организационный момент и мотивация
80 минут: 5 минут | 60 минут: 3 минуты
Действие преподавателя:
Приветствует студентов, отмечает явку в журнале, проверяет запуск оборудования. Включает проектор с заставкой темы (Слайд 1 презентации).
Прямая речь преподавателя:
«Здравствуйте, коллеги! Садитесь. Сегодня у нас критически важное занятие. Представьте себе крупную компанию, где внедрена корпоративная система бронирования переговорных комнат. В 10:00 в системе бронирует зал генеральный директор, а ровно через 50 миллисекунд в ту же переговорную оформляет бронь руководитель отдела маркетинга.
Программисты бэкенда написали привычный код: SELECT COUNT(*) FROM bookings WHERE room_id = 1 AND .... Запрос вернул ноль, оба веб-клиента увидели, что комната свободна, и оба приложения отправили команду INSERT! В результате на одно и то же время назначены две важные встречи, произошел громкий скандал. Почему это произошло? Потому что разработчики наивно доверили контроль целостности коду веб-приложения вместо того, чтобы реализовать декларативное ограничение на уровне СУБД.
Сегодня мы с вами научимся проектировать ограничения целостности промышленного уровня: от сложных предикатов CHECK с регулярными выражениями до ограничений исключения EXCLUDE на обобщенных деревьях GiST и отложенных внешних ключей DEFERRABLE. Открываем рабочие материалы занятия 22!»
Реакция обучающихся:
Включаются в обсуждение, осознают проблему состояния гонки (Race Condition) в веб-системах, открывают конспекты и материалы урока.
Этап 2. Актуализация опорных знаний
80 минут: 8 минут | 60 минут: 5 минут
Действие преподавателя:
Организует фронтальный опрос по ранее изученным базовым ограничениям целостности реляционной модели.
Прямая речь преподавателя:
«Прежде чем перейти к сложным конструкциям, давайте освежим фундамент. В реляционной теории существует два базовых правила целостности. Кто назовет их?
Верно: целостность сущностей (Entity Integrity), которая обеспечивается первичным ключом PRIMARY KEY, и ссылочная целостность (Referential Integrity), поддерживаемая внешними ключами FOREIGN KEY.
А теперь вопрос на понимание: чем принципиально отличается ограничение UNIQUE от PRIMARY KEY? Пожалуйста, ответьте с места.»
Ответ студента:
«Первичный ключ в таблице может быть только один, и его поля строго не могут содержать NULL (NOT NULL). Ограничений UNIQUE может быть сколько угодно, и в стандартном SQL они допускают наличие NULL, причем несколько строк с NULL не считаются дубликатами.»
Прямая речь преподавателя:
«Отлично! Абсолютно верное замечание. А теперь проблемный вопрос: представьте, что мы повесили ограничение UNIQUE (room_id, start_time). Защитит ли это нас, если одна встреча забронирована с 10:00 до 12:00, а вторая — с 11:00 до 13:00? Нет! Моменты старта разные (10:00 и 11:00), поэтому UNIQUE пропустит обе записи, хотя интервалы перекрываются. Сегодня мы ликвидируем эту уязвимость.»
Этап 3. Сообщение новых теоретических знаний
80 минут: 35 минут | 60 минут: 27 минут
Действие преподавателя:
Переключает слайды презентации (слайды 2–14). Подробно излагает теоретический материал, чертит на доске схемы перекрытия временных интервалов и графы циклических связей.
Прямая речь преподавателя (Блок 1: Домены и CHECK с regex):
«Первый рубеж обороны — это ограничения доменов. С помощью команды CREATE DOMAIN мы можем создать именованный тип данных с встроенной проверкой. Например, домен для СНИЛС или адреса электронной почты.
Для проверки строковых форматов реляционные СУБД поддерживают POSIX регулярные выражения через оператор тильды ~. Внимание на экран (Слайд 5)!
Оператор ~ проверяет совпадение с учетом регистра, а ~* — без учета регистра. Отрицания обозначаются как !~ и !~*.
Главная ошибка начинающих инженеров: забыть символы привязки! Если вы напишете: CHECK (passport ~ '\d{4}'), то в таблицу успешно запишется строка "Мой паспорт 12345678", потому что СУБД ищет вхождение подстроки. Чтобы гарантировать строго четыре цифры и ничего больше, обязательно пишем: CHECK (passport ~ '^\d{4}$')! Знак крышки ^ фиксирует начало строки, а доллар $ — ее окончание.
Помните также про трехзначную логику SQL. Предикат CHECK отвергает кортеж только тогда, когда результат строго равен FALSE. Если результат UNKNOWN (например, поле равно NULL), СУБД разрешит вставку! Если поле обязательно к заполнению — обязательно комбинируйте CHECK с ограничением NOT NULL.»
Прямая речь преподавателя (Блок 2: Интервалы и EXCLUDE USING gist):
«Теперь переходим к решению проблемы овербукинга (Слайды 7–10). В PostgreSQL существуют специализированные типы диапазонов: tsrange для временных интервалов и daterange для дат.
Интервалы записываются с указанием границ: квадратная скобка включает границу, круглая исключает. Например, '[10:00, 12:00)'::tsrange. Оператор пересечения интервалов обозначается двумя амперсандами &&. Если два интервала перекрываются хотя бы на миллисекунду, операция slot1 && slot2 возвращает TRUE.
Чтобы ядро СУБД автоматически блокировало любые пересечения, мы используем ограничение исключения EXCLUDE USING gist. Обычный индекс B-Tree умеет искать только по равенству и порядку больше-меньше, а дерево GiST (Generalized Search Tree) индексирует многомерные геометрические фигуры и временные диапазоны!
Для совмещения скалярного идентификатора (например, номера зала room_id) и интервала подключается расширение btree_gist. Запишите в конспекты эталонную конструкцию:
CONSTRAINT exc_no_overlap EXCLUDE USING gist (room_id WITH =, slot WITH &&).
Это означает: "В базе данных не может существовать двух строк, у которых совпадает room_id И одновременно перекрываются интервалы slot!" Попытка вставки вызовет ошибку exclusion_violation на уровне движка базы данных.»
Прямая речь преподавателя (Блок 3: Отложенные внешние ключи DEFERRABLE):
«Следующая сложная архитектурная дилемма — циклические связи (Слайды 11–13). Представьте: сущность Отель ссылается на Номер-люкс, а сущность Номер ссылается на Отель. Оба внешних ключа объявлены как NOT NULL.
Какую запись вставлять первой? Если вставляем отель — ошибка: номер еще не существует! Если вставляем номер — ошибка: отель еще не существует! Возникает парадокс "курицы и яйца".
Как выйти из тупика, не делая поля nullable? Ответ: механизм отложенных ограничений DEFERRABLE INITIALLY DEFERRED. Мы объявляем внешний ключ отложенным. В этом случае СУБД позволяет временно нарушить целостность внутри транзакции, но в момент выполнения команды COMMIT строго проверяет наличие связанного внешнего ключа. Если к моменту фиксации номер был успешно вставлен — транзакция фиксируется без единого сбоя!»
Этап 5. Первичное закрепление и экспресс-практикум
80 минут: 14 минут | 60 минут: 11 минут
Действие преподавателя:
Инструктирует обучающихся по переходу в интерактивный рабочий лист
zanyatie.html. Распределяет по 4 вариантам практических кейсов, организует индивидуальное консультирование.
Прямая речь преподавателя:
«Переходим к практической части в рабочем листе zanyatie.html. У каждого ряда свой вариант:
1 вариант — проектирование системы конференц-залов с интервалами tsrange и EXCLUDE;
2 вариант — банковский портал с регулярными выражениями для СНИЛС, паспорта и даты совершеннолетия;
3 вариант — гостиничный комплекс с циклической ссылкой DEFERRABLE INITIALLY DEFERRED;
4 вариант — интернет-магазин с составными предикатами скидок и форматом артикула SKU.
Заполните свои реальные ФИО и группу, напишите DDL-код ограничений и обоснуйте отсутствие скрытых противоречий. Не забывайте нажимать кнопку "Сохранить", данные автоматически сохраняются в браузере. Приступайте к выполнению!»
Деятельность обучающихся:
Работают с интерактивным листом, составляют SQL-код ограничений, формулируют регулярные выражения и проверяют граничные условия.
Этап 6. Итог занятия, рефлексия и домашнее задание
80 минут: 6 минут | 60 минут: 4 минуты
Действие преподавателя:
Разбирает типичные ошибки, выводит на экран слайд 15 с веб-комиксом xkcd, озвучивает оценки по критериальной рубрике и разъясняет регламент резервного домашнего задания.
Прямая речь преподавателя:
«Подводим итоги. Я прошелся по аудитории и заметил две характерные неточности: некоторые забыли подключить расширение btree_gist при создании EXCLUDE, а в регулярных выражениях пропустили символ конца строки $. Обратите на это внимание!
В завершение взгляните на Слайд 15 нашей презентации. Культовый веб-комикс про "Little Bobby Tables". Он наглядно демонстрирует, что происходит, когда проектировщик халатно относится к ограничениям и очистке данных. Если в схеме заложены жесткие домены, регулярные выражения и строгие внешние ключи, база данных становится неуязвимой для любых аномалий и сбоев!
Оценки за урок выставляются по 4-балльной рубрике. Если кому-то потребуется дополнительная отработка или закрепление — в файле domashnee_zadanie.html размещено резервное домашнее задание. Выполняйте его только в том случае, если я назначу его индивидуально. Всем спасибо за продуктивную работу, занятие окончено!»