Интерактивный рабочий лист: Практическое занятие №10: управление проектом, этап 2

ОП.08 Основы проектирования баз данных • Тема 4 (§2.2 с.7–8) • Занятие №28

ФИО обучающегося:

Учебная группа:

Выберите практический сценарий (Вариант):

Вариант 1. Складской комплекс: Версионирование V2 и регрессионный тест

Контекст предметной области: В эксплуатируемой складской базе данных действует базовая таблица товаров Товары (product_id, title, category, price_rub, stock_qty). В рамках релиза v2 требуется добавить поддержку штрихкодирования: новую колонку Штрихкод_EAN13 (13 символов) с ограничением уникальности UNIQUE.
Критическое требование: В таблице уже содержится более 10 000 товаров без штрихкодов. Добавление колонки не должно нарушить работу старых складских терминалов, выполняющих поиск по названию и артикулу, а также создание товаров без немедленного присвоения штрихкода.

Задание 1.1. Разработка скрипта прямой миграции (V2__add_barcode_ean13.sql)

Разработайте безопасный DDL-скрипт добавления колонки barcode_ean13 и уникального индекса/ограничения, сохраняющий возможность вставки товаров со значением NULL или дефолтным плейсхолдером:

DDL скрипта V2__add_barcode_ean13.sql:

Задание 1.2. Разработка скрипта отката (U2__drop_barcode_ean13.sql)

Напишите зеркальный Undo-скрипт, возвращающий таблицу товаров к состоянию версии 1.0 (с удалением индекса и колонки):

DDL скрипта U2__drop_barcode_ean13.sql:

Задание 1.3. Регистрация миграции в flyway_schema_history

Составьте строку INSERT для фиксации применения миграции V2 в служебной таблице версий (installed_rank = 2, version = '2', description = 'add barcode ean13', checksum = 87419203, success = true):

SQL фиксации в flyway_schema_history:

Задание 1.4. Протокол регрессионного тестирования старых форм поиска и вставки

Опишите 2 тестовых сценария: 1) выполнение старого поискового SELECT-запроса терминала сбора данных (TSD); 2) выполнение операции INSERT нового товара без указания штрихкода. Укажите ожидаемые результаты:

Протокол регрессионного теста:

Контрольные вопросы самопроверки:

1) Почему уникальное ограничение (UNIQUE) в реляционных базах данных допускает множество значений NULL?
2) Что произойдет с операцией наката, если применить команду ADD COLUMN barcode VARCHAR(13) NOT NULL UNIQUE; без DEFAULT на таблице с 10 000 записей?

Вариант 2. Гостиничный сервис: Версионирование V3 и проверка стабильности регистрации

Контекст предметной области: В гостиничной системе действует таблица Бронирования (booking_id, guest_id, room_id, check_in_date, check_out_date, total_sum, status). Для интеграции с агрегаторами (Островок, Яндекс.Путешествия, собственный сайт) требуется релиз v3: создание отдельного справочника Источники_бронирования (source_id, source_name, commission_pct) и добавление внешнего ключа source_id в таблицу Бронирования.
Критическое требование: Модуль стойки регистрации (Ресепшн) оформляет заселение гостей офлайн и не передает source_id. Миграция обязана предусмотреть источник по умолчанию («Прямой визит / Стойка»).

Задание 2.1. Разработка скрипта прямой миграции (V3__add_booking_sources.sql)

Создайте таблицу источников, наполните ее первичными данными (включая дефолтный источник с ID=1) и добавьте колонку внешнего ключа в Бронирования со значением по умолчанию 1:

DDL скрипта V3__add_booking_sources.sql:

Задание 2.2. Разработка скрипта отката (U3__drop_booking_sources.sql)

Составьте скрипт отката по правилу LIFO (сначала снятие внешнего ключа с таблицы Бронирования, затем удаление таблицы источников):

DDL скрипта U3__drop_booking_sources.sql:

Задание 2.3. Регистрация миграции в flyway_schema_history

Сформируйте SQL-запрос фиксации наката версии V3 (installed_rank = 3, version = '3', description = 'add booking sources', checksum = 45910284, success = true):

SQL фиксации в flyway_schema_history:

Задание 2.4. Регрессионный тест модуля регистрации

Опишите проверку выполнения старого запроса создания бронирования на стойке отеля без передачи поля source_id. Докажите, что запись сохраняется с дефолтным источником:

Протокол регрессионного теста:

Контрольные вопросы самопроверки:

1) Почему нельзя удалить таблицу booking_sources до удаления внешнего ключа в таблице bookings?
2) Какую угрозу для целостности данных несет операция отката Undo-скриптом, если гостиница проработала на версии 3 уже двое суток?

Вариант 3. Банковский портал: Миграция V4 (Expand/Contract) и Undo-скрипт

Контекст предметной области: В учетной системе банка таблица клиентов содержала паспортные данные в единой строке: Клиенты (client_id, full_name, phone, passport_raw, is_active). В рамках перехода на стандарт ФЗ-152 и усиления защиты ПДн в релизе v4 требуется декомпозировать сущность: вынести паспорта в защищенную таблицу Паспортные_данные (passport_id, client_id, doc_series, doc_number, issue_date, issued_by).
Критическое требование: Скрипт миграции должен не просто создать структуру, но и аккуратно перенести существующие данные (Backfill / Data Migration), а Undo-скрипт должен корректно восстановить строку в таблице клиентов без потерь.

Задание 3.1. Разработка скрипта прямой миграции (V4__extract_client_passports.sql)

Разработайте миграцию фазы Expand: создание таблицы client_passports с внешним ключом к clients и перенос данных из passport_raw:

DDL и DML скрипта V4__extract_client_passports.sql:

Задание 3.2. Разработка скрипта безопасного отката (U4__rollback_client_passports.sql)

Составьте скрипт отката, который перед удалением таблицы client_passports синхронизирует новые данные обратно в clients.passport_raw:

Скрипт отката U4__rollback_client_passports.sql:

Задание 3.3. Регистрация миграции в flyway_schema_history

Сформируйте SQL-запрос фиксации наката версии V4 (installed_rank = 4, version = '4', description = 'extract client passports', checksum = 99120412, success = true):

SQL фиксации в flyway_schema_history:

Задание 3.4. Протокол регрессионного теста

Опишите проверку: 1) запроса банковского операциониста старой версии; 2) сверку контрольного количества строк в clients и client_passports:

Протокол регрессионного теста:

Контрольные вопросы самопроверки:

1) Почему на этапе Expand категорически запрещено удалять колонку clients.passport_raw командой ALTER TABLE clients DROP COLUMN passport_raw;?
2) Через какой период времени и при каких условиях разрешается выполнить фазу Contract (удаление устаревшей колонки)?

Вариант 4. Образовательный портал: Миграция V5 (Индексирование) и регрессионный тест производительности

Контекст предметной области: В системе электронного деканата таблица сдачи контрольных точек насчитывает более 2 000 000 строк: Результаты_аттестации (result_id, student_id, discipline_id, submission_date, score, status). Формирование сводной сессионной ведомости по группе за выбранный период времени выполняется недопустимо долго (более 14 секунд), вызывая таймаут клиентского веб-приложения.
Критическое требование: В рамках релиза v5 требуется создать составной B-Tree индекс по полям (submission_date, student_id), зафиксировать миграцию, составить Undo-скрипт и провести сравнительное регрессионное тестирование времени отклика отчета.

Задание 4.1. Разработка скрипта прямой миграции (V5__add_exam_results_idx.sql)

Разработайте DDL-скрипт создания составного индекса (с учетом возможности фонового создания CONCURRENTLY в PostgreSQL):

DDL скрипта V5__add_exam_results_idx.sql:

Задание 4.2. Разработка скрипта отката (U5__drop_exam_results_idx.sql)

Составьте скрипт безопасного удаления индекса в случае его избыточности или негативного влияния на скорость вставки:

DDL скрипта U5__drop_exam_results_idx.sql:

Задание 4.3. Регистрация миграции в flyway_schema_history

Сформируйте SQL-запрос фиксации наката версии V5 (installed_rank = 5, version = '5', description = 'add exam results idx', checksum = 31289410, success = true):

SQL фиксации в flyway_schema_history:

Задание 4.4. Протокол регрессионного теста времени построения сводной ведомости

Опишите результаты команды EXPLAIN ANALYZE для сессионного отчета до создания индекса (Seq Scan, 14 200 мс) и после создания индекса (Index Scan / Bitmap Index Scan, 18 мс):

Протокол теста производительности:

Контрольные вопросы самопроверки:

1) Почему порядок следования колонок в составном индексе (submission_date, student_id) имеет решающее значение?
2) Почему создание индекса на таблице в 2 000 000 строк без опции CONCURRENTLY может привести к аварийной остановке работы деканата?

✓ Все изменения успешно сохранены в LocalStorage