Фундаментальный теоретический конспект

Дисциплина: ОП.08 Основы проектирования баз данных
Тема рабочей программы (§2.2 с.7): Тема 2. Взаимосвязи в моделях и реляционный подход к построению моделей — Практическое занятие №4: проектирование реляционной БД
Учебный модуль: Третья нормальная форма (3NF), форма Бойса-Кодда (BCNF), DDL-спецификация и валидация тестовых данных

1. От концептуальной модели к физической реляционной схеме

Проектирование реляционной базы данных — это строгий инженерный процесс трансформации неформальных требований заказчика в математически корректную и оптимизированную физическую модель хранения данных. Этот процесс включает четыре последовательных уровня:

Концептуальный уровень (ER-диаграмма): выделение ключевых сущностей предметной области (Entity) и смысловых связей между ними (Relationships 1:1, 1:M, M:N).
Логический уровень (Реляционные отношения): отображение сущностей в таблицы, определение атрибутов, первичных и внешних ключей, устранение избыточности путем нормализации.
Физический уровень (DDL-спецификация): выбор конкретной СУБД (PostgreSQL, MySQL, SQLite), назначение машинных типов данных (INT, VARCHAR, NUMERIC, TIMESTAMP), декларация системных ограничений целостности (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK).
Эксплуатационный уровень (Верификация данными): наполнение схемы контрольным набором тестовых кортежей (DML INSERT INTO) и проведение негативного тестирования ограничений.

Главный постулат реляционной надежности: Целостность и непротиворечивость данных должны гарантироваться на уровне самого ядра СУБД через схему отношений и декларативные ограничения, а не зависеть от надежности внешних клиентских программ или бэкенд-сервисов.

2. Третья нормальная форма (3NF): выявление и устранение транзитивности

2.1. Математический аппарат: детерминанты и транзитивность

Фундаментом реляционной нормализации является теория функциональных зависимостей (ФЗ). Запись X → Y означает, что значение набора атрибутов X однозначно определяет значение набора атрибутов Y в рамках любого допустимого состояния отношения. Набор X называется детерминантом.

Вспомним критерии нормальных форм:

1NF (Первая нормальная форма): все атрибуты отношения атомарны (неделимы), нет повторяющихся групп и списков внутри одного поля.
2NF (Вторая нормальная форма): отношение находится в 1NF, и каждый неключевой атрибут функционально полно зависит от первичного ключа (отсутствуют частичные зависимости от части составного ключа).
3NF (Третья нормальная форма): отношение находится во 2NF, и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.

Формальное определение 3NF (критерий Заньоло / Кодда):
Отношение $R$ находится в 3NF тогда и только тогда, когда оно находится во 2NF, и для любой нетривиальной функциональной зависимости $X \to A$ выполняется хотя бы одно из условий:

$X$ является суперключом отношения $R$ (то есть включает в себя потенциальный ключ);
$A$ является первичным атрибутом (то есть входит в состав хотя бы одного потенциального ключа отношения $R$).

2.2. Механизм возникновения аномалий при транзитивной зависимости

Пусть в таблице сотрудников задано отношение со схемой: Сотрудники(Таб_номер, ФИО, Должность, Базовый_оклад).

Здесь Таб_номер — первичный ключ. В организации действует штатное расписание: базовый оклад жестко зафиксирован за должностью. Имеем функциональные зависимости:

Таб_номер → Должность (первичный ключ определяет должность сотрудника);
Должность → Базовый_оклад (должность определяет установленный оклад);
При этом Должность не является потенциальным ключом (несколько людей могут занимать одну должность), а Базовый_оклад не входит в ключ.

Следовательно, зависимость Таб_номер → Базовый_оклад является транзитивной ($X \to Y \to Z$). Это приводит к четырем классическим аномалиям:

Вид аномалии	Как проявляется в ненормализованной таблице	Инженерное последствие
Аномалия избыточности	Оклад должности «Инженер» (75 000 руб.) повторяется в каждой из 300 строк инженеров.	Неоправданный расход дисковой памяти, раздувание B-Tree индексов, лишний ввод данных оператором.
Аномалия обновления (Update Anomaly)	При индексации оклада инженеров до 85 000 руб. требуется обновить все 300 строк.	При аппаратном сбое или прерывании запроса часть сотрудников получит новый оклад, а часть останется со старым — база перейдет в рассогласованное состояние.
Аномалия вставки (Insertion Anomaly)	Руководство ввело новую должность «Архитектор БД» со ставкой 150 000 руб., но вакансия еще открыта.	Невозможно внести должность в базу, так как поле `Таб_номер` (Primary Key) не может принимать значение `NULL` (нарушение целостности сущности).
Аномалия удаления (Deletion Anomaly)	Уволился единственный сотрудник в должности «Специалист по информационной безопасности».	При удалении строки сотрудника из базы навсегда стирается сама должность и утвержденный для нее размер оклада.

2.3. Пошаговый алгоритм декомпозиции отношения в 3NF

Шаг 1. Построение графа функциональных зависимостей

Выпишите все атрибуты отношения и выявите все смысловые зависимости бизнес-логики. Определите первичный ключ $X$.

Шаг 2. Обнаружение неключевых детерминантов

Найдите любую зависимость $Y \to Z$, где левая часть $Y$ не содержит первичного ключа, а правая часть $Z$ не входит в состав ключей. Это признак транзитивной зависимости $X \to Y \to Z$.

Шаг 3. Декомпозиция отношения (вынос в отдельную таблицу)

Разделите исходное отношение $R(X, A_1, \dots, Y, Z)$ на два независимых отношения:

Новая родительская таблица (справочник): $R_{ref}(Y, Z)$, где $Y$ становится первичным ключом.
Скорректированная дочерняя таблица: $R_{main}(X, A_1, \dots, Y)$, где зависимый атрибут $Z$ удаляется, а детерминант $Y$ остается в качестве внешнего ключа (FOREIGN KEY (Y) REFERENCES R_ref(Y)).

Шаг 4. Проверка декомпозиции без потерь (Lossless Join)

Согласно теореме Хита (Heath's Theorem), разбиение отношения $R$ на $R_1$ и $R_2$ является обратимым без потерь тогда и только тогда, когда их пересечение $R_1 \cap R_2$ функционально определяет либо $R_1$, либо $R_2$. Поскольку $R_1 \cap R_2 = Y$, а $Y$ является первичным ключом для $R_{ref}$, естественное соединение гарантированно восстанавливает исходные данные без появления ложных строк.

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

3.1. Определение и отличие от 3NF

Нормальная форма Бойса-Кодда (BCNF, Boyce-Codd Normal Form) была предложена Рэймондом Бойсом и Эдгаром Коддом в 1974 году как строгое усиление 3NF для отношений с множественными составными перекрывающимися ключами.

Строгое определение BCNF:
Отношение $R$ находится в форме Бойса-Кодда тогда и только тогда, когда для любой нетривиальной функциональной зависимости $X \to A$ детерминант $X$ является суперключом отношения $R$.

Сравнение с 3NF: В определении 3NF допускалось второе условие: «или $A$ — первичный атрибут». В BCNF это условие аннулировано. Любая ФЗ обязана исходить строго из суперключа. Отсюда следует: BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF (любое отношение в BCNF всегда находится в 3NF, но отношение в 3NF может нарушать BCNF).

3.2. Практический пример: 3NF выполняется, но BCNF нарушена

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

Отношение: Академический_план(Студент, Предмет, Преподаватель).
Правило 1: По каждому предмету каждого конкретного студента обучает ровно один преподаватель: (Студент, Предмет) → Преподаватель.
Правило 2: Каждый преподаватель ведет ровно один закрепленный за ним предмет: Преподаватель → Предмет.
Правило 3: Один и тот же предмет могут вести несколько разных преподавателей (для разных групп/студентов).

Анализ ключей и нормальных форм:

1. Потенциальные ключи отношения: {Студент, Предмет} и {Студент, Преподаватель}. Оба ключа являются составными и перекрываются по атрибуту Студент.

2. Проверка 3NF: Все атрибуты (Студент, Предмет, Преподаватель) входят в состав хотя бы одного потенциального ключа (являются первичными атрибутами). Зависимость Преподаватель → Предмет удовлетворяет условию 3NF, так как Предмет — первичный атрибут. Отношение находится в 3NF!

3. Проверка BCNF: В зависимости Преподаватель → Предмет детерминантом является Преподаватель. Является ли он суперключом? Нет! Зная только преподавателя, мы не можем однозначно определить студента. BCNF нарушена!

4. Аномалии из-за нарушения BCNF: Мы не можем зафиксировать в базе факт того, что новый преподаватель Иванов ведет предмет «Базы данных», пока к нему не записан хотя бы один студент (Insertion Anomaly).

Декомпозиция в BCNF: Отношение разбивается на две таблицы: Преподавание(Преподаватель, Предмет) (где Преподаватель — PK) и Назначения(Студент, Преподаватель) (составной PK). В обеих таблицах BCNF достигнута!

Инженерная дилемма сохранения зависимостей: При переходе к BCNF исходная функциональная зависимость (Студент, Предмет) → Преподаватель оказывается межтабличной. Если студент запишется на курс к двум разным преподавателям по одному предмету, обычный FOREIGN KEY этого не предотвратит — потребуется триггер проверки. В практическом проектировании иногда осознанно останавливаются на 3NF, чтобы сохранить проверку всех ФЗ на уровне простых ограничений таблиц.

4. Правила физического проектирования DDL CREATE TABLE

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

При выполнении SQL DDL-скрипта критически важен порядок операторов. Таблицы образуют ориентированный ациклический граф (DAG) зависимостей:

Порядок выполнения DDL:

Шаг 1: Создание независимых таблиц-справочников (не содержащих внешних ключей). Например, departments, manufacturers, courses, clients.
Шаг 2: Создание зависимых таблиц 1-го уровня (ссылающихся только на независимые справочники). Например, positions, medicines, study_groups, vehicles.
Шаг 3: Создание операционных таблиц фактов и связующих сущностей (ссылающихся на таблицы шагов 1 и 2). Например, employees, batches, students, repair_orders.
Шаг 4: Создание подчиненных транзакционных таблиц спецификаций. Например, appointment_orders, sales_invoices, payments, work_items.

4.2. Стандарты типов данных и декларативные ограничения

Каждое поле реляционной таблицы должно быть жестко типизировано с учетом занимаемой памяти и точности вычислений:

INT / BIGINT: идентификаторы (ID), счетчики, внешние ключи.
VARCHAR(N): строковые поля с жестким ограничением максимальной длины (не использовать неограниченный TEXT для коротких полей).
NUMERIC(precision, scale): денежные суммы, цены, коэффициенты, физические дозировки. Категорически запрещено использовать типы с плавающей точкой (FLOAT / REAL) для финансовых величин из-за ошибок округления стандарта IEEE 754.
DATE / TIMESTAMP: календарные даты и точные временные метки событий.
BOOLEAN: логические флаги активности/архивности.

4.3. Реляционные ограничения целостности (Constraints)

Ограничение	Назначение в архитектуре БД	Пример ANSI SQL синтаксиса
`PRIMARY KEY`	Обеспечивает целостность сущности, однозначную адресацию строки, не допускает NULL и дубликатов.	`employee_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY`
`FOREIGN KEY ... REFERENCES`	Обеспечивает ссылочную целостность, связывает дочернюю строку с существующей родительской.	`position_id INT NOT NULL REFERENCES positions(position_id) ON DELETE RESTRICT`
`UNIQUE`	Гарантирует неповторяемость естественных бизнес-ключей (ИНН, VIN, email, СНИЛС).	`CONSTRAINT uq_client_phone UNIQUE (phone_number)`
`NOT NULL`	Запрещает отсутствие значения в обязательных информационных полях.	`full_name VARCHAR(150) NOT NULL`
`CHECK`	Ограничивает домен значений предикатом бизнес-логики (неотрицательность, интервалы, перечисления).	`CHECK (base_salary >= 19242.00)` `CHECK (expiry_date > manufacture_date)`
`DEFAULT`	Задает системное значение поля при его отсутствии в операторе INSERT.	`status VARCHAR(20) NOT NULL DEFAULT 'active'`

4.4. Политики ссылочной целостности: RESTRICT против CASCADE

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

ON DELETE RESTRICT (или NO ACTION): Запрещает удаление родительской строки, если на нее ссылается хотя бы одна дочерняя строка. СУБД возвращает ошибку foreign key violation и откатывает транзакцию. Это базовый выбор для всех нормативно-справочных таблиц (должности, отделы, клиенты, категории).
ON DELETE CASCADE: Автоматически удаляет все связанные дочерние строки вслед за родителем. Допустимо исключительно для зависимых таблиц-спецификаций вида «мастер — детали» (например, при удалении черновика заказ-наряда удаляются строки выполненных в нем работ).
ON DELETE SET NULL: Обнуляет поле внешнего ключа в дочерних строках. Требует, чтобы FK допускал значение NULL (не применимо для первичных компонентов связей M:N).

5. Методология формирования контрольного набора тестовых данных

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

5.1. Топологическая последовательность вставки

Строки вставляются строго в том порядке, в котором создавались таблицы: сначала родительские справочники, затем зависимые сущности. Нарушение порядка приведет к мгновенной ошибке ссылочной целостности:

-- 1. Сначала вставляем подразделения и должности
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'Бухгалтерия');
INSERT INTO positions (pos_id, title, base_salary) VALUES (101, 'Главный бухгалтер', 95000.00);

-- 2. Только после этого вставляем сотрудника, ссылающегося на них
INSERT INTO employees (emp_id, full_name, dept_id, pos_id) 
VALUES (1001, 'Смирнова Анна Викторовна', 1, 101);

5.2. Состав тестового пакета

Штатные валидные кортежи (не менее 5 строк): связанные между собой реальные данные предметной области, демонстрирующие различные варианты связей 1:M.
Граничные значения (Boundary Testing): кортежи, проверяющие крайние допустимые точки предикатов CHECK (например, минимальный установленный оклад, нулевой остаток на складе, статус выполнения заказа).

Негативные контрпримеры (Negative Testing): закомментированные демонстрационные запросы с ожидаемой ошибкой ядра СУБД. Например:

-- ДЕМОНСТРАЦИЯ ЗАЩИТЫ: Попытка вставки отрицательного оклада (должна вызвать ошибку CHECK):
-- INSERT INTO positions (pos_id, title, base_salary) VALUES (102, 'Стажер', -500.00);
-- Ожидаемый результат СУБД: ERROR: new row for relation "positions" violates check constraint "chk_salary_positive"

6. Вопросы для самопроверки и аналитического контроля

1. В чем заключается принципиальная разница между частичной и транзитивной функциональной зависимостью?

Ответ: Частичная зависимость возможна только при составном первичном ключе, когда неключевой атрибут зависит лишь от части ключа (устраняется во 2NF). Транзитивная зависимость возникает, когда неключевой атрибут зависит от другого неключевого атрибута, который сам зависит от ключа: $X \to Y \to Z$ (устраняется в 3NF).

2. Почему критерий 3NF допускает наличие зависимости $X \to A$, если $A$ является первичным атрибутом?

Ответ: Если $A$ входит в состав потенциального ключа, его присутствие в отношении необходимо для идентификации кортежей другими ключами. Удаление такого атрибута привело бы к невозможности проверки исходного ключа без соединений.

3. При каких условиях отношение, находящееся в 3NF, НЕ находится в нормальной форме Бойса-Кодда (BCNF)?

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

4. Почему нельзя использовать тип данных FLOAT или DOUBLE для хранения финансовых сумм и окладов в реляционной БД?

Ответ: Типы FLOAT/DOUBLE используют двоичное представление с плавающей точкой стандарта IEEE 754, что приводит к погрешностям округления при сложении цен (например, $0.1 + 0.2 = 0.30000000000000004$). Финансовые данные обязаны храниться в типах точной фиксированной арифметики — NUMERIC(p, s) или DECIMAL(p, s).

5. В каком случае применение правила ON DELETE CASCADE категорически недопустимо?

Ответ: На связях со справочниками и субъектами учета (клиенты, товары, сотрудники, кафедры). Удаление карточки клиента каскадно уничтожит всю историю его платежей и договоров, что приведет к юридическим и финансовым потерям. В таких случаях обязательно используется ON DELETE RESTRICT.

6. Что гарантирует теорема Хита при декомпозиции отношения?

Ответ: Теорема Хита гарантирует обратимость декомпозиции без потерь (Lossless Join): если отношение $R(A, B, C)$ удовлетворяет ФЗ $A \to B$, то разбиение на $R_1(A, B)$ и $R_2(A, C)$ позволяет восстановить исходное отношение операцией естественного соединения ($R_1 \bowtie R_2 = R$) без появления паразитных (ложных) кортежей.

7. Зачем накладывать ограничение UNIQUE на естественный ключ (например, VIN автомобиля), если в таблице уже есть первичный ключ id SERIAL?

Ответ: Суррогатный первичный ключ гарантирует уникальность машинной строки, но ничего не знает о физическом мире. Без ограничения UNIQUE(vin) оператор сможет 10 раз ввести один и тот же автомобиль с разными id, создав дубликаты и разрушив учет на СТО.

8. Почему порядок операторов DROP TABLE должен быть строго противоположен порядку операторов CREATE TABLE?

Ответ: Потому что СУБД блокирует удаление таблицы-родителя, пока на нее ссылаются внешние ключи существующих дочерних таблиц. Чтобы избежать ошибки ссылочной целостности, первыми удаляют дочерние таблицы, а последними — независимые справочники.