Конспект лекции: Основные этапы проектирования баз данных

Дисциплина «ОП.08 Основы проектирования баз данных» — Тема 3. Занятие 13

1. Понятие жизненного цикла базы данных (DBLC)

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

В теории программной инженерии совокупность взаимосвязанных стадий от первоначального зарождения идеи до вывода системы из эксплуатации называется жизненным циклом базы данных (Database Life Cycle, DBLC).

Классический каскадный процесс проектирования баз данных опирается на трехуровневую архитектуру ANSI/SPARC (внешний, концептуальный и внутренний уровни), которая гарантирует логическую и физическую независимость данных.

Фундаментальное правило проектирования: Ошибка, допущенная на этапе сбора требований или концептуального моделирования, при обнаружении на стадии физической эксплуатации обходится в 50–100 раз дороже, чем исправление на начальных этапах.

2. Наглядная блок-схема стадий DBLC

Жизненный цикл базы данных включает 5 основных последовательных этапов с обратными связями (итерациями верификации):

Системный анализ и сбор требований к БД

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

↓ [Критерий перехода: Утвержденное ТЗ]

Концептуальное (инфологическое) проектирование

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

↓ [Критерий перехода: Верифицированная ER-модель]

Логическое (даталогическое) проектирование

Отображение инфологической модели в реляционную структуру (таблицы, PK, FK). Нормализация отношений (1НФ → 2НФ → 3НФ). В OLAP — осознанная денормализация.

↓ [Критерий перехода: Схема в 3НФ без аномалий]

Физическое проектирование и реализация

Выбор конкретной СУБД (PostgreSQL, ClickHouse), генерация DDL, подбор типов данных, построение индексов (B-Tree, GiST), партиционирование, настройка серверов.

↓ [Критерий перехода: Прохождение нагрузочных тестов]

Эксплуатация, администрирование и сопровождение

Промышленная работа базы данных, регулярное резервное копирование (бэкапы), мониторинг производительности (slow logs), масштабирование и репликация.

3. Детальная характеристика этапов проектирования

3.1. Этап системного анализа и сбора требований

На этом этапе проектировщик взаимодействует с заказчиками, экспертами предметной области и будущими пользователями. Главная задача — понять, какую реальность должна отражать база данных.

Методы сбора: интервьюирование, анкетирование, наблюдение за рабочими процессами, изучение бумажного документооборота предприятия.
Формализация ограничений: выявление бизнес-правил (например: «один клиент не может арендовать более одного автомобиля одновременно» или «рецепт на антибиотик действителен не более 30 дней»).

3.2. Этап концептуального проектирования

Создается инфологическая модель предметной области. Наиболее распространенным инструментом является модель «Сущность-Связь» (Entity-Relationship Model, ER-модель), предложенная Питером Ченом в 1976 году.

Сущность (Entity): обособленный объект реального мира (Студент, Автомобиль, Товар).
Атрибут (Attribute): свойство сущности (ФИО, VIN-номер, Цена).
Связь (Relationship): содержательное отношение между сущностями (Студент «учится в» Группе; Врач «выписывает» Рецепт).

3.3. Этап логического проектирования

Концептуальная модель транслируется в конкретную модель данных. В подавляющем большинстве промышленных систем это реляционная модель.

Каждая сущность трансформируется в отношение (реляционную таблицу).
Связи 1:N реализуются добавлением внешнего ключа (Foreign Key) в дочернюю таблицу.
Связи M:N (многие-ко-многим) расщепляются созданием вспомогательной таблицы связи с составным первичным ключом.
Проводится математическая нормализация отношений до 3-й нормальной формы для исключения избыточности и аномалий модификации.

3.4. Этап физического проектирования

Логическая схема привязывается к архитектурным особенностям выбранной СУБД (PostgreSQL, MariaDB, Oracle Database) и оборудованию.

Определение точных системных типов данных (INT, NUMERIC, TIMESTAMP WITH TIME ZONE).
Проектирование структур быстрого поиска: выбор типов индексов (B-Tree для диапазонов, Hash для точных совпадений, GiST/SP-GiST для геокоординат).
Горизонтальное партиционирование (Partitioning): разбиение гигантских таблиц на секции по диапазонам дат или хэш-ключам.
Обеспечение безопасности: настройка шифрования дисков (TDE), ролевой модели прав (GRANT/REVOKE).

3.5. Этап эксплуатации и сопровождения

База данных вводится в промышленный контур. Основные инженерные задачи:

Реализация стратегии резервного копирования: RPO (допустимая потеря данных во времени) и RTO (время восстановления сервиса).
Настройка репликации (Master-Replica) для обеспечения высокой доступности (High Availability).
Анализ профиля медленных запросов через pg_stat_statements и оптимизация планов выполнения (EXPLAIN ANALYZE).

4. Сводная таблица входов, выходов и ключевых документов этапов

Инженерный процесс проектирования регламентируется четким набором входящей и исходящей документации:

Этап DBLC	Входные данные и документы	Выходные документы и артефакты	Критерий перехода (Gate)
1. Системный анализ	Первичная документация предприятия (накладные, акты, рецепты), интервью, регламенты, 152-ФЗ.	Техническое задание (ТЗ) на БД, словарь данных, спецификация SLA и объемов.	Утверждение ТЗ заказчиком
2. Концептуальное проектирование	Утвержденное ТЗ, перечень бизнес-правил предметной области.	Инфологическая ER-диаграмма (нотации Chen / Crow's Foot), реестр сущностей и кардинальностей.	Согласование ERD экспертами домена
3. Логическое проектирование	Концептуальная ER-диаграмма, правила трансляции отношений.	Реляционная схема в 3НФ, спецификация PK и FK, ограничения CHECK, логическая схема «Звезда» (OLAP).	Подтверждение 3НФ (отсутствие аномалий)
4. Физическое проектирование	Логическая реляционная схема, профиль нагрузочного тестирования.	DDL-скрипты целевой СУБД, структуры индексов (B-Tree/GiST), конфигурация партиций, профиль RAID/дисков.	Успешный нагрузочный бенчмарк
5. Эксплуатация и сопровождение	Развернутая в проде БД, эксплуатационный трафик транзакций.	Регламент резервного копирования (RPO/RTO), конфигурация репликации, отчеты slow-запросов.	Непрерывный эксплуатационный цикл

5. Вопросы для глубокой самопроверки

В чем заключается принципиальная разница между концептуальным и логическим уровнями проектирования БД? Почему на концептуальном уровне нельзя указывать типы данных СУБД?
Каким образом связь «многие-ко-многим» (M:N), выделенная на концептуальном этапе, трансформируется в схему отношений на логическом этапе проектирования?
Почему в аналитических хранилищах данных (DWH / OLAP) на логическом этапе сознательно допускается денормализация структуры таблиц?
Какие ключевые задачи решает физическое проектирование и почему правильный подбор индексов и партиционирования критичен для систем с высокой нагрузкой (на примере телеметрии каршеринга)?
Что означают показатели RPO (Recovery Point Objective) и RTO (Recovery Time Objective) на этапе эксплуатации и администрирования базы данных?