Презентация к лекции №3

1. Лекция №3. Логическое проектирование и нормализация данных

2. Оглавление

§4.1 Логическое проектирование БД
§4.2 Функциональные зависимости
§4.3 Нормальные формы
§4.3.1 Первая нормальная форма (1NF)
§4.3.2 Вторая нормальная форма (2NF)
§4.3.3 Третья нормальная форма (3NF)
§4.3.4 Нормальная форма Бойса-Кодда (BCNF)
§4.4 Необходимость в денормализации
§4.5 Переход от ER-диаграммы к реляционной структуре

3. §4.1 Логическое проектирование БД

Логическое проектирование — это процесс создания схемы БД на
основе конкретной модели данных (например, реляционной).
Цель: преобразовать концептуальную модель (ER-диаграмму) в
логическую структуру, не зависящую от конкретной СУБД.
Результат: набор таблиц, ключей, ограничений и индексов, готовых
к реализации.

4. §4.1 Задачи логического проектирования

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

5. §4.1 Этапы логического проектирования

Процесс включает следующие шаги:
1. Анализ требований: Определение, какие данные нужно хранить
и как они будут использоваться.
2. Создание концептуальной модели: Построение ER-диаграммы.
3. Определение схемы: Разработка таблиц, ключей, ограничений и
индексов на основе ER-диаграммы.
4. Тестирование структуры: Проверка, что модель удовлетворяет
бизнес-процессам и требованиям пользователей.

6. §4.2 Функциональные зависимости

Функциональная зависимость — это семантическое понятие, когда
значение одного атрибута (или набора атрибутов) однозначно
определяет значение другого атрибута.
Обозначается: A > B (читается: "A функционально определяет B").
Пример: В таблице Студентов, Номер_Зачётки > ФИО, Группа, Курс.
Зная номер зачётки, мы всегда можем определить остальные
данные студента.

7. §4.2 Типы функциональных зависимостей

Выделяют несколько ключевых типов:
• Полная: Неключевой атрибут зависит от всех атрибутов составного
ключа.
• Частичная: Неключевой атрибут зависит от части составного ключа.
• Транзитивная: Атрибут зависит от другого неключевого атрибута (A > B
> C).
• Многозначная: Один атрибут может иметь несколько независимых
значений для одного значения другого атрибута.
Нормализация направлена на устранение частичных и транзитивных
зависимостей.

8. §4.3 Нормальные формы

Нормальная форма — это набор правил, которым должна
соответствовать таблица в реляционной БД.
Процесс нормализации — это последовательное приведение таблиц к
более строгим формам для устранения избыточности и аномалий.
Основные нормальные формы:
1. Первая нормальная форма (1NF)
2. Вторая нормальная форма (2NF)
3. Третья нормальная форма (3NF)
4. Нормальная форма Бойса-Кодда (BCNF)

9. §4.3 Зачем нормализовать? Аномалии!

Без нормализации возникают аномалии — ситуации, приводящие к
противоречиям и ошибкам:
•Аномалия обновления: Изменение одного факта требует изменения
многих строк.
• Аномалия удаления: Удаление одной записи приводит к потере другой
важной информации.
• Аномалия вставки: Невозможно добавить данные, не добавив другие,
несвязанные данные.
Нормализация устраняет эти аномалии, обеспечивая целостность и
согласованность данных.

10. §4.3.1 Первая нормальная форма (1NF)

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

11. §4.3.1 Пример: Приведение к 1NF

12. §4.3.1 Пример: Приведение к 1NF

13. §4.3.2 Вторая нормальная форма (2NF)

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

14. §4.3.2 Пример: Нарушение 2NF. Ситуация 1

15. §4.3.2 Пример: Нарушение 2NF. Ситуация 1

16. §4.3.2 Пример: Нарушение 2NF. Ситуация 2

17. §4.3.2 Пример: Нарушение 2NF. Ситуация 2

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

Таблица находится в 3NF, если:
1. Она находится в 2NF.
2. Все неключевые атрибуты зависят только от первичного ключа, а
не от других неключевых атрибутов.
Другими словами, в таблице не должно быть транзитивных
зависимостей.
Формула: Если A > B и B > C, то A > C — это плохо, если B не
является ключом.

19. §4.3.3 Пример: Нарушение 3NF

20. §4.3.3 Пример: Приведение к 3NF (Декомпозиция)

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

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

22. §4.3.4 Пример: Нарушение BCNF

23. §4.3.4 Пример: Приведение к BCNF (Декомпозиция)

24. §4.4 Необходимость в денормализации

Денормализация — это намеренное введение избыточности в
структуру БД для повышения производительности.
Это обратный процесс по отношению к нормализации.
Денормализованная схема нарушает правила нормальных форм
(обычно 3NF или BCNF).
Это компромисс: мы жертвуем чистотой структуры ради скорости
чтения данных.

25. §4.4 Основные причины денормализации

1. Высокая стоимость JOIN-ов: При частых запросах, требующих
соединения многих таблиц, производительность падает. Дублирование
данных устраняет необходимость в JOIN.
2. OLAP и Data Warehousing: В аналитических системах важна скорость
чтения, а не частота обновлений. Используются звёздные и снежные
схемы.
3. Ограничения СУБД/Фреймворков: Некоторые NoSQL-базы (MongoDB)
не поддерживают JOIN, что делает денормализацию обязательной.

26. §4.4 Пример: Денормализация для производительности

Нормализованная структура:
- Таблица «Заказы»: ID, ID_Клиента, ID_Товара, Количество
- Таблица «Клиенты»: ID, Имя
- Таблица «Товары»: ID, Название, Цена
Денормализованная структура:
- Таблица «Заказы»: ID, ID_Клиента, Имя_Клиента, ID_Товара,
Название_Товара, Цена, Количество
Запрос списка заказов теперь не требует JOIN, но при изменении имени
клиента нужно обновить все его заказы.

27. §4.5 Переход от ER-диаграммы к реляционной структуре

ER-диаграмма — это концептуальная модель. Для реализации в
СУБД её нужно преобразовать в набор таблиц.
Основное правило: Каждой сущности соответствует одна таблица.
Атрибуты сущности становятся столбцами таблицы.
Правила преобразования связей зависят от их кардинальности (1:1,
1:M, M:N) и обязательности.

28. §4.5 Правило 1: Связь 1:1 (обе сущности обязательны)

Если связь «один к одному» и обе сущности обязательны, то
достаточно одной таблицы.
Пример: Сущности «Человек» и «Паспорт».
Решение: Создать одну таблицу «Человек», включающую атрибуты
обоих сущностей: ФИО, Дата_Рождения, Серия_Паспорта,
Дата_Выдачи.
Первичный ключ: может быть ID_Человека или Серия_Паспорта.

29. §4.5 Правило 2: Связь 1:1 (одна сущность обязательна, другая — нет)

Создаются две таблицы.
Первичный ключ обязательной сущности становится внешним ключом в
таблице необязательной сущности.
Пример: Сущности «Сотрудник» (обязательна) и
«Корпоративная_Карта» (необязательна).
- Таблица «Сотрудники»: ID, ФИО, Должность.
- Таблица «Карты»: ID_Карты, Номер_Карты, ID_Сотрудника (FK), Статус.
Не у всех сотрудников есть карта, но каждая карта принадлежит одному
сотруднику.

30. §4.5 Правило 3: Связь 1:1 (обе сущности необязательны)

Создаются три таблицы: по одной для каждой сущности и одна
ассоциативная таблица для связи.
Ассоциативная таблица содержит внешние ключи на обе сущности.
Пример: Сущности «Автомобиль» и «Гараж» (машина может быть без
гаража, гараж может быть пустым).
- Таблица «Автомобили»: ID, Модель.
- Таблица «Гаражи»: ID, Адрес.
- Таблица «АвтомобильВГараже»: ID_Автомобиля (FK), ID_Гаража (FK).
Эта таблица хранит только факты связей.

31. §4.5 Правило 4: Связь 1:M (сущность на стороне M обязательна)

Создаются две таблицы.
Первичный ключ сущности на стороне «1» становится внешним ключом
в таблице сущности на стороне «M».
Пример: Сущности «Отдел» (1) и «Сотрудник» (M, обязательный).
- Таблица «Отделы»: ID, Название.
- Таблица «Сотрудники»: ID, ФИО, Должность, ID_Отдела (FK, NOT NULL).
Каждый сотрудник обязан быть прикреплен к отделу.

32. §4.5 Правило 5: Связь 1:M (сущность на стороне M необязательна)

Создаются три таблицы: по одной для каждой сущности и одна
ассоциативная таблица для связи.
Ассоциативная таблица содержит внешние ключи на обе сущности.
Пример: Сущности «Клиент» (1) и «Заказ» (M, но клиент может не иметь
заказов сейчас).
Хотя обычно для 1:M создают две таблицы, это правило применяется,
если логика требует явного разделения связи. Чаще для 1:M (даже с
необязательностью) используют правило 4, делая FK NULLable.
В лекции указано создание 3 таблиц, но на практике чаще используется
2 таблицы с NULLable FK.

33. §4.5 Правило 6: Связь M:N

Создаются три таблицы: по одной для каждой сущности и одна ассоциативная
(связующая) таблица.
Ассоциативная таблица содержит внешние ключи на обе сущности. Часто её
первичный ключ — это комбинация этих двух FK.
Пример: Сущности «Студент» и «Предмет».
- Таблица «Студенты»: ID, ФИО.
- Таблица «Предметы»: ID, Название.
- Таблица «Зачисления»: ID_Студента (FK), ID_Предмета (FK), Оценка,
Дата_Зачисления.
Это классический и обязательный подход для связей многие-ко-многим.