Этап 1. Актуализация опорных знаний и мотивация (5 мин / 5 мин)
Хронометраж: 00:00 – 00:05
Прямая речь преподавателя:
«Здравствуйте, уважаемые студенты! Рад приветствовать вас на занятии по дисциплине "Основы проектирования баз данных".
Сегодня мы изучаем операцию, ради которой, по сути, и существует реляционная модель данных — операцию соединения, или JOIN.
На предыдущих занятиях мы выяснили, что данные в реляционных базах намеренно разбиваются на отдельные таблицы: клиенты живут отдельно, договоры отдельно, счета отдельно, платежи отдельно. Это называется нормализацией и избавляет нас от аномалий удаления и дублирования.
Но представьте, что к вам приходит руководитель финансового департамента и спрашивает: "Кто из наших VIP-клиентов открыл счет на сумму свыше миллиона рублей за последний месяц?". Руководителю не нужны отдельно таблица клиентов и отдельно таблица счетов с непонятными внешними ключами. Ему нужен сводный отчет.
Как из двух, трех или десяти раздельных таблиц собрать целостный результат? Как объединить строки, не запутавшись в миллионах комбинаций? За это отвечает операция реляционного соединения. Давайте разберем её математическую и инженерную суть».
Адаптация для сокращенного регламента (60 мин):
Преподаватель сокращает вводные примеры из бизнеса, сразу формулируя тезис: «Нормализация декомпозирует данные, соединение (JOIN) собирает их обратно по предикату связывания первичного и внешнего ключа».
Этап 2. Сообщение новых теоретических знаний (35 мин / 25 мин)
Хронометраж 80 мин: 00:05 – 00:40 | 60 мин: 00:05 – 00:30
2.1. Концепция: Декартово произведение и Селекция
Прямая речь преподавателя:
«Обратите внимание на слайд 3 презентации. Каково математическое определение соединения? В реляционной алгебре Эдгара Кодда соединение — это не какая-то принципиально новая магия. Это строгая композиция двух уже знакомых нам операций: декартова произведения и селекции!
Формула проста: R ⋈_θ S = σ_θ (R × S).
Что здесь происходит? Сначала мы берем каждый кортеж отношения R и соединяем с каждым кортежем отношения S. Если в таблице клиентов 100 строк, а в счетах 200 — декартово произведение даст 20 000 строк! А затем мы накладываем логический фильтр селекции: отбираем только те комбинации, где ID_клиента в таблице R в точности равен Клиент_ID в таблице S. Все строки, не удовлетворяющие равенству, безжалостно отбрасываются.
Внимание: если вы забудете указать условие связи, вы получите так называемый "картезианский взрыв" (Cartesian Explosion). На небольших базах в колледже это просто длинный список, но на продакшн-сервере таблицы по миллиону строк порождают триллион строк, и сервер баз данных падает с переполнением диска tempdb!».
2.2. Эквисоединение vs Естественное соединение
Прямая речь преподавателя:
«Посмотрите на слайды 4 и 5. В чем разница между эквисоединением и естественным соединением (Natural Join)?
В эквисоединении условием связи является строгое равенство: R.A = S.B. Но в схеме результата остаются ОБА столбца: и R.A, и S.B! То есть идентификатор клиента будет напечатан в результирующей таблице дважды.
В естественном соединении (Natural Join, знак "галстук-бабочка" ⋈) СУБД делает две вещи автоматически:
Во-первых, она сама ищет все столбцы с совпадающими именами и приравнивает их.
Во-вторых, она проводит реляционную проекцию, удаляя дублирующий столбец из схемы! В результате атрибут связи присутствует строго в единственном экземпляре.
Но помните: если у двух таблиц нет ни одного общего названия столбца, Natural Join вырождается в чистое декартово произведение!».
2.3. Внешние соединения и феномен NULL
Прямая речь преподавателя:
«А теперь важнейший практический вопрос: что делать с "одинокими" кортежами?
Представьте: у нас есть студенты Иванов, Петров и Сидоров. Иванов и Сидоров получают стипендию, а Петров — нет.
Если мы выполним обычное естественное соединение (INNER JOIN), Петров исчезнет из отчета! Будто его вообще нет в нашем колледже. Для деканата это катастрофа — потеряли человека.
Чтобы сохранить кортежи, не имеющие пары, используются внешние соединения (Outer Joins):
1. Левое внешнее соединение (LEFT OUTER JOIN): сохраняет всех студентов из левой таблицы R. Для Петрова, у которого нет стипендии, в колонках стипендии СУБД выставит специальный маркер — NULL!
2. Правое внешнее соединение (RIGHT OUTER JOIN): сохраняет все строки правой таблицы S. Если стипендия назначена, но студент еще не зачислен — студент будет NULL.
3. Полное внешнее соединение (FULL OUTER JOIN): сохраняет всех из R и всех из S, подставляя NULL там, где нет пары.
Запомните железное правило реляционной алгебры: маркер NULL означает "неизвестность". Он порождает трехзначную логику: True, False, Unknown.
Поэтому выражение NULL = NULL дает не True, а UNKNOWN! И два кортежа с NULL никогда не свяжутся в эквисоединении!».
2.4. Полусоединение, антисоединение и физические алгоритмы
Прямая речь преподавателя:
«На слайдах 11 и 13 мы видим полусоединение (Semi-Join) и антисоединение (Anti-Join).
Полусоединение (R ⋉ S) оставляет столбцы только таблицы R и возвращает тех студентов, у которых ЕСТЬ хотя бы одна стипендия. Причем студент не продублируется, даже если у него три стипендии!
Антисоединение (R ▷ S) возвращает только тех студентов, у которых НЕТ ни одной стипендии. Это ключевой инструмент аудита баз данных для поиска товаров без продаж, клиентов без счетов или "зависших" записей.
А как СУБД выполняет это физически? В зависимости от объема и индексов оптимизатор выбирает:
- Nested Loop Join — вложенные циклы, идеален для маленьких таблиц и поиска по B-Tree индексу;
- Hash Join — строит хэш-таблицу в оперативной памяти, стандарт де-факто для аналитических запросов;
- Sort Merge Join — параллельное слияние предварительно отсортированных данных».
Адаптация для сокращенного регламента (60 мин):
Преподаватель объединяет разбор Equi-Join и Natural Join в один блок, а объяснение физических алгоритмов дает в форме краткого 3-минутного резюме слайда 13 без вывода оценок сложности.
Этап 3. Закрепление знаний и практический тренинг (25 мин / 18 мин)
Хронометраж 80 мин: 00:40 – 00:65 | 60 мин: 00:30 – 00:48
Прямая речь преподавателя:
«Коллеги, открываем на своих рабочих станциях файл zanyatie.html.
Обязательно введите свою фамилию, имя и номер учебной группы. Лист сохраняет все ваши ответы локально в браузере.
Я распределяю варианты:
- 1 вариант: Клиенты и Счета. Ваша задача — вычислить естественное соединение и левое внешнее соединение, выявив клиентов без открытых счетов.
- 2 вариант: Преподаватели и Кафедры. Строим эквисоединение с сохранением двух ключей и правое внешнее соединение для кафедр без преподавателей.
- 3 вариант: Студенты и Сданные экзамены. Строим полное внешнее соединение (Full Outer Join), аккуратно проставляя NULL для несдававших студентов и невостребованных экзаменов.
- 4 вариант: Склад и Поставки. Применяем операцию антисоединения для поиска неликвидных товаров, по которым не было ни одной поставки.
Внимание к деталям: не теряйте строки, правильно вычисляйте число столбцов и не путайте маркер NULL со значением "0" или пустой строкой! Время пошло, я подхожу для индивидуальных консультаций».
Адаптация для сокращенного регламента (60 мин):
Время самостоятельного выполнения сокращается до 18 минут. Преподаватель через 10 минут выводит на экран подсказку по расчету кардинальности схемы, чтобы студенты быстрее заполнили финальные таблицы.
Этап 4. Итог занятия, рефлексия и домашнее задание (15 мин / 12 мин)
Хронометраж 80 мин: 00:65 – 00:80 | 60 мин: 00:48 – 00:60
Прямая речь преподавателя:
«Итак, время вышло. Давайте взглянем на слайд 15 нашей презентации.
Перед вами культовый мем мировой IT-индустрии от Рэндалла Манро — комикс xkcd "Exploits of a Mom", известный как "Little Bobby Tables".
Мама назвала сына Robert'); DROP TABLE Students;--. Когда школьная база попыталась неаккуратно добавить имя в таблицу, команда удалила всю таблицу студентов!
Какая здесь прямая связь с нашей сегодняшней темой?
Если в базе данных нарушена ссылочная целостность или удалена таблица, с которой связываются внешние ключи, все наши операции JOIN начинают выдавать пустые множества или заполняться бесконечными маркерами NULL! Надежность баз данных держится на двух китах: строгой ссылочной целостности первичных и внешних ключей и математически выверенном проектировании операций соединения.
Оценивание сегодняшней работы проводится строго по критериальной рубрике:
- 2 балла за безошибочное построение результирующей таблицы;
- 1 балл за обоснование размерности схемы и обработку маркеров NULL;
- 1 балл за корректные ответы на блок самопроверки.
Те, кто пропустил пару или хочет закрепить тему, выполняют резервное дистанционное задание в файле domashnee_zadanie.html. Срок сдачи — до следующего теоретического занятия по расписанию. Всем спасибо за продуктивную работу!».
Адаптация для сокращенного регламента (60 мин):
Рефлексия проводится экспрессно (12 минут): разбор одного наиболее показательного кейса с NULL и озвучивание дедлайна сдачи работ.