Домашнее задание - Средства проектирования структур БД: ограничения

Выполняйте только если преподаватель назначил это задание. Срок сдачи сообщит преподаватель.

Резервное практическое задание предназначено для дистанционного закрепления теоретического материала занятия №22 «Средства проектирования структур БД: ограничения» (§2.2 с.7–8 учебной программы дисциплины «ОП.08 Основы проектирования баз данных»). При выполнении заданий сформируйте синтаксически корректные DDL-скрипты на диалекте PostgreSQL/ANSI SQL и проведите аналитическую проверку на отсутствие взаимных логических противоречий.

Задание 1. Проектирование доменов и DDL с регулярными выражениями (POSIX regex) 2 балла

Разработайте схему таблицы учетных записей медицинского портала patient_profiles со следующими полями:

  • profile_id (BIGSERIAL PRIMARY KEY) — суррогатный ключ;
  • snils (CHAR(14) NOT NULL) — СНИЛС гражданина РФ, строго в формате ХХХ-ХХХ-ХХХ ХХ (3 цифры, дефис, 3 цифры, дефис, 3 цифры, пробел, 2 цифры);
  • oms_policy (VARCHAR(16) NOT NULL) — единый полис ОМС, состоящий строго из 16 арабских цифр;
  • contact_email (VARCHAR(255) NOT NULL) — адрес электронной почты, соответствующий международному стандарту (наличие имени пользователя, символа @, доменного имени и доменной зоны от 2 букв);
  • birth_date (DATE NOT NULL) — дата рождения пациента;
  • registration_timestamp (TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) — отметка времени регистрации.

Требования: составьте DDL-скрипт с созданием ограничений CHECK (или CREATE DOMAIN). Обязательно используйте операторы сопоставления регулярных выражений ~ / ~* с граничными якорями ^ и $. Добавьте ограничение совершеннолетия или логической допустимости возраста (пациент родился не позже сегодняшнего дня и не ранее 1900 года).

Задание 2. Анализ схемы на отсутствие логических противоречий 1 балл

Младший разработчик спроектировал следующую таблицу для учета скидочных карт магазина бытовой электроники:

CREATE TABLE loyalty_cards (
    card_id INT PRIMARY KEY,
    owner_fio VARCHAR(100) NOT NULL,
    discount_rate NUMERIC(5,2) NOT NULL,
    min_spend NUMERIC(10,2) NOT NULL,
    max_spend NUMERIC(10,2) NOT NULL,
    card_status VARCHAR(20) NOT NULL,
    CONSTRAINT chk_discount_bounds CHECK (discount_rate > 5 AND discount_rate < 3),
    CONSTRAINT chk_spend_range CHECK (min_spend >= 10000 AND max_spend <= 5000),
    CONSTRAINT chk_status_active CHECK (card_status IN ('ACTIVE', 'BLOCKED') AND card_status = 'PENDING')
);

Вопросы для анализа:

  1. Найдите все логические противоречия и несовместные предикаты в данной таблице. Почему в нее невозможно вставить ни одну строку?
  2. Предложите исправленный, непротиворечивый блок ограничений CHECK, отражающий корректные бизнес-правила: скидка от 3% до 25%, минимальный порог расходов строго меньше максимального порога, статус карты — один из трех: 'ACTIVE', 'BLOCKED' или 'PENDING'.

Задание 3. Проектирование непересекающихся интервалов и циклической целостности 1 балл

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

  1. Таблица coworking_desks (рабочие места) и таблица desk_rentals (аренда столов). Реализуйте защиту от повторного бронирования одного и того же стола desk_id на пересекающиеся периоды времени через ограничение EXCLUDE USING gist с интервальным типом TSRANGE.
  2. В таблицу coworking_zones (зоны коворкинга) и coworking_desks добавьте циклическую связь: каждая зона имеет флагманский VIP-стол (поле vip_desk_id в зонах), а каждый стол принадлежит зоне (поле zone_id в столах). Настройте ограничение внешнего ключа как DEFERRABLE INITIALLY DEFERRED и напишите транзакцию атомарной вставки нового коворкинга с VIP-столом.