Практическое занятие №4: проектирование реляционной БД

1. От концепции к реляционной архитектуре

        Проектирование реляционной БД — строгий переход от неструктурированных требований к математически нормализованной структуре таблиц с защитой целостности на уровне ядра СУБД.
      

Концептуальный уровень (ER)

Сущности (Сотрудник, Товар, Студент)
Атрибуты предметной области
Связи 1:1, 1:M, M:N
Бизнес-правила заказчика

Физический уровень (ANSI SQL)

Таблицы (CREATE TABLE)
Машинные типы данных (INT, NUMERIC, DATE)
Ключи: PRIMARY KEY, FOREIGN KEY
Ограничения: UNIQUE, NOT NULL, CHECK

2. Функциональные зависимости и детерминанты

Функциональная зависимость X → Y означает, что значение атрибута X (детерминанта) однозначно определяет значение Y.

Полная зависимость (2NF)

Неключевой атрибут зависит от всего составного ключа целиком, а не от его части.

(Order_ID, Item_ID) → Quantity

Транзитивная зависимость (3NF)

Зависимость через промежуточный неключевой детерминант: X → Y → Z.

Emp_ID → Pos_ID → Salary

Суперключ и Потенциальный ключ

Минимальный суперключ, уникально идентифицирующий каждый кортеж отношения.

PRIMARY KEY (emp_id)

3. Третья нормальная форма (3NF)

        Критерий 3NF: Отношение находится во 2NF, и для любой нетривиальной зависимости X → A выполняется хотя бы одно из двух условий:
        
1) X — суперключ отношения;
        
2) A — первичный атрибут (входит в состав потенциального ключа).

Суть правила: «Каждый неключевой атрибут должен зависеть от ключа, от полного ключа и ни от чего, кроме ключа (so help me Codd)». Все промежуточные зависимости между неключевыми полями должны быть вынесены в отдельные отношения.

4. Почему транзитивность разрушает базу данных?

Если в таблице Employees(id, name, position, salary) оклад зависит от должности:

Аномалия избыточности

Оклад 80 000 руб. дублируется у всех 200 инженеров. Неоправданный расход диска и памяти кэша.

Аномалия обновления (Update)

При изменении оклада нужно изменить 200 строк. Сбой посередине приведет к рассогласованию данных.

Аномалия вставки (Insert)

Нельзя внести новую должность «Архитектор» с окладом 150 000 руб., пока нет принятого сотрудника (PK is NULL).

Аномалия удаления (Delete)

При увольнении единственного инженера по ИБ из базы навсегда стирается сама должность и утвержденный оклад.

5. Алгоритм декомпозиции в 3NF

        Теорема Хита (Lossless Join): Разложение отношения на две проекции является обратимым без потерь, если их общий атрибут функционально определяет одну из них.
      

Шаг 1: Выявить неключевой детерминант Y → Z (например: position_id → base_salary).
Шаг 2: Создать новую таблицу-справочник positions(position_id PK, title, base_salary).
Шаг 3: Удалить зависимый атрибут base_salary из исходной таблицы сотрудников.
Шаг 4: Оставить position_id в таблице сотрудников как внешний ключ (FOREIGN KEY).

6. Нормальная форма Бойса-Кодда (BCNF)

        Определение BCNF: Для каждой нетривиальной функциональной зависимости X → A левая часть X обязана являться суперключом.
      

В BCNF полностью устранено послабление 3NF для первичных атрибутов. Любая зависимость, детерминант которой не является суперключом отношения, запрещена!

Третья нормальная форма (3NF)

Допускает X → A, если A входит в потенциальный ключ. Защищает от транзитивности неключевых полей.

Форма Бойса-Кодда (BCNF)

Исключений нет: детерминант X обязан быть суперключом. Строгое устранение всех аномалий ФЗ.

7. Когда 3NF выполняется, но BCNF нарушена?

Это происходит исключительно при наличии составных перекрывающихся ключей.

        Отношение: Консультации(Студент, Предмет, Преподаватель)
        
• (Студент, Предмет) → Преподаватель (у студента по предмету один преподаватель).
        
• Преподаватель → Предмет (преподаватель ведет ровно один предмет).

Потенциальные ключи: {Студент, Предмет} и {Студент, Преподаватель}.
В зависимости Преподаватель → Предмет атрибут Предмет входит в ключ → в 3NF находится!
Но Преподаватель — НЕ суперключ (не определяет студента) → BCNF нарушена!
Решение: Декомпозиция на (Преподаватель, Предмет) и (Студент, Преподаватель).

8. Топологический порядок создания таблиц

СУБД блокирует создание внешнего ключа, если родительская таблица еще не существует в базе данных.

1. Независимые справочники

Таблицы без внешних ключей:

departments
manufacturers
clients
courses

2. Зависимые сущности 1 ур.

Ссылаются на справочники:

positions
medicines
vehicles
study_groups

3. Операционные таблицы

Факты, транзакции, заказы:

employees
batches
repair_orders
payments

9. Синтаксис DDL и выбор типов данных

CREATE TABLE positions (
    position_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    base_salary NUMERIC(10, 2) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT chk_salary_positive CHECK (base_salary >= 19242.00)
);

NUMERIC(10, 2): строго для цен и окладов (исключает погрешности FLOAT стандарта IEEE 754).
VARCHAR(N): явный лимит длины строки вместо неограниченного TEXT.
DATE / TIMESTAMP: встроенная валидация календарных дат ядром СУБД.

10. Декларативные ограничения целостности

PRIMARY KEY & UNIQUE

PK: уникальность и запрет NULL для идентификации строк.

UNIQUE: защита от повторного ввода естественных ключей (ИНН, VIN, телефон).

CHECK & NOT NULL

CHECK: валидация бизнес-диапазонов (остаток >= 0, дата_сдачи > дата_выдачи).

NOT NULL: запрет неопределенных значений в критически важных полях.

        Любое нарушение ограничения вызывает немедленный откат транзакции (ROLLBACK) на уровне ядра СУБД.
      

11. Ссылочная целостность: RESTRICT vs CASCADE

Опция ON DELETE во внешнем ключе определяет поведение СУБД при удалении родительской записи:

ON DELETE RESTRICT (Безопасно)

Запрещает удаление родителя при наличии ссылок.
Применение: Справочники, клиенты, товары, отделы.
Защищает от случайного уничтожения финансовой истории и архивных документов.

ON DELETE CASCADE (Опасно)

Молча стирает все связанные дочерние записи.
Применение: Строго для спецификаций «мастер-детали» (позиции в чеке при отмене чека).
Категорически запрещено ставить на сущности-субъекты!

12. Верификация схемы тестовыми данными

Пакет INSERT INTO должен доказать работоспособность структуры в 3 сценариях:

1. Happy Path

Не менее 5 связанных корректных строк, покрывающих ключевые сценарии бизнес-процесса.

2. Boundary Values

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

3. Негативные тесты

Заведомо некорректные вставки для проверки срабатывания ограничений ядра СУБД.

13. Топ-4 ошибок начинающих проектировщиков

«Лень декомпозиции»: хранение названия отдела и оклада прямо в таблице сотрудников ради экономии JOIN.
«Слепой CASCADE»: навешивание каскадного удаления на справочники, приводящее к безвозвратной потере учетных записей.
Суррогатный ключ без UNIQUE: создание id SERIAL без наложения UNIQUE на естественный бизнес-ключ (VIN, паспорт).
Нарушение топологии: запуск скрипта создания дочерней таблицы до создания таблицы-родителя (Foreign Key Error).

Тематический интернет-мем: «Exploits of a Mom» (культовый классический веб-комикс про «Little Bobby Tables», xkcd №327).
Автор: Randall Munroe. Первоисточник: xkcd.com/327.
Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Смысл мема: Когда проектировщик пренебрегает строгой валидацией типов данных, ограничениями целостности реляционной схемы и параметризацией SQL-запросов, одна строковая запись в таблице учеников способна стереть всю базу данных учебного заведения (Robert'); DROP TABLE Students;--).