Рабочий лист: Средства проектирования структур БД — ограничения

Дисциплина «ОП.08 Основы проектирования баз данных» — Занятие №22 (СИЭУиП)

ФИО студента

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

Выберите назначенный практический вариант:

Вариант 1. Система бронирования конференц-залов бизнес-центра

Техническое задание: Спроектируйте таблицу conference_room_bookings для автоматизированного учета бронирования залов. Бизнес-требования исключают человеческий фактор и наложения встреч:

Каждая бронь привязана к конкретному номеру зала room_id (INT NOT NULL).
Временной диапазон задается интервальным типом booking_slot (TSRANGE NOT NULL).
Обязательно ограничение CHECK на валидность границ интервала: начало встречи должно быть строго меньше окончания встречи (lower(booking_slot) < upper(booking_slot)), а интервал не должен быть пустым.
Ограничение EXCLUDE USING gist обязано гарантировать, что для одного и того же зала room_id (оператор =) временные интервалы booking_slot ни в коем случае НЕ пересекаются (оператор перекрытия &&).
Укажите подготовительную системную команду активации расширения PostgreSQL, необходимого для поддержки оператора равенства в индексе GiST.

1. Напишите полный синтаксически корректный DDL-скрипт создания таблицы с ограничениями CHECK и EXCLUDE USING gist:

2. Объясните, почему традиционная проверка SELECT COUNT(*) перед INSERT в коде бэкенда уязвима к Race Condition при одновременных запросах, а EXCLUDE гарантирует 100% защиту:

3. Напишите тестовый сценарий (два запроса INSERT), где первая вставка проходит успешно, а вторая завершается аппаратным исключением exclusion_violation:

Вариант 2. Регистрация пользователей банковского портала

Техническое задание: Спроектируйте таблицу профилей клиентов интернет-банкинга bank_clients со строгими ограничениями формата персональных данных:

Поле snils (CHAR(14) NOT NULL): валидация формата страхового номера СНИЛС по регулярному выражению POSIX ^\d{3}-\d{3}-\d{3} \d{2}$ (три группы по 3 цифры через дефис, затем пробел и 2 контрольные цифры).
Поля паспорта РФ: passport_series (CHAR(4) NOT NULL) — строго 4 цифры (^\d{4}$), passport_number (CHAR(6) NOT NULL) — строго 6 цифр (^\d{6}$).
Поле birth_date (DATE NOT NULL): ограничение совершеннолетия (клиент должен быть старше 18 лет на момент регистрации: birth_date <= CURRENT_DATE - INTERVAL '18 years').
Поле phone_number (VARCHAR(12) NOT NULL): международный мобильный формат РФ (^\+7\d{10}$).

1. Напишите DDL-скрипт создания таблицы с именованными ограничениями CHECK (или CREATE DOMAIN) и оператором regex (~):

2. Что произойдет, если в регулярном выражении опустить символы якорей ^ и $? Приведите конкретный пример строки, которая ошибочно пройдет валидацию:

3. Поясните, как трехзначная логика SQL (TRUE, FALSE, UNKNOWN) обрабатывает предикаты CHECK, если одно из полей окажется NULL (при отсутствии NOT NULL):

Вариант 3. Отели и гостиничные комплексы — циклические связи

Техническое задание: Смоделируйте структуру гостиничного комплекса с циклической зависимостью 1:1 / 1:N:

Таблица hotels: поля hotel_id (INT PRIMARY KEY), hotel_name (VARCHAR(100) NOT NULL), presidential_suite_id (INT NOT NULL). Отель обязан иметь главный президентский номер-люкс.
Таблица hotel_rooms: поля room_id (INT PRIMARY KEY), hotel_id (INT NOT NULL REFERENCES hotels(hotel_id)), room_number (VARCHAR(20) NOT NULL). Номер принадлежит отелю.
Возникает парадокс «курицы и яйца»: ни одну таблицу нельзя заполнить первой, так как оба внешних ключа объявлены как NOT NULL.
Требуется настроить внешний ключ hotels.presidential_suite_id REFERENCES hotel_rooms(room_id) как DEFERRABLE INITIALLY DEFERRED для отложенной проверки в конце транзакции.

1. Напишите полный DDL-скрипт создания таблиц с циклическим отложенным внешним ключом DEFERRABLE INITIALLY DEFERRED:

2. Напишите транзакцию (BEGIN ... COMMIT), выполняющую атомарную бесконфликтную вставку нового отеля и его главного президентского номера:

3. В чем отличие режимов INITIALLY DEFERRED и INITIALLY IMMEDIATE? В какой момент времени СУБД генерирует ошибку в каждом из режимов?

Вариант 4. Электронный маркетплейс: ценообразование и артикулы

Техническое задание: Спроектируйте каталог товаров интернет-магазина marketplace_goods со сложной бизнес-логикой ограничений:

Поле sku (VARCHAR(20) NOT NULL): артикул товара строго формата 3 заглавные буквы, дефис, 6 цифр (например, 'ELC-109283'). Валидация через POSIX regex.
Поле base_price (NUMERIC(10,2) NOT NULL): базовая стоимость строго больше 0.
Поле discount_percent (NUMERIC(5,2)): процент скидки должен находиться в диапазоне BETWEEN 0 AND 90 (не может быть отрицательным и не может превышать 90%).
Поле sale_price (NUMERIC(10,2)): цена по акции. Если указана, то должна быть строго меньше base_price (sale_price < base_price).
Логическая непротиворечивость: если задана sale_price, то discount_percent не должен быть NULL, и наоборот (связанная пара скидочных реквизитов).

1. Напишите DDL-скрипт таблицы с полным набором именованных ограничений CHECK и валидацией артикула SKU:

2. Приведите пример взаимно противоречивых предикатов CHECK (логический тупик), при которых ни одна строка не сможет быть добавлена в таблицу:

3. Составьте 3 проверочных тестовых запроса INSERT: корректная запись, отклонение по неверному формату SKU и отклонение по завышенной скидочной цене:

✓ Данные успешно сохранены в LocalStorage