Интерактивный рабочий лист: Основные этапы проектирования БД

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

Вариант 1: Разработка БД «Сеть аптек с центральным складом»

Технический контекст: Фармацевтическая сеть состоит из центрального логистического склада и 50 розничных аптечных пунктов. Требуется спроектировать базу данных для строгого учета серий лекарств, сроков годности (контроль FEFO), работы с поставщиками и отпуска рецептурных препаратов по бланкам строгой отчетности.

1. Карта 5 этапов жизненного цикла базы данных (DBLC):

Этап 1: Системный анализ и сбор требований

Специфицируйте входные документы (рецептурные бланки, прайс-листы поставщиков, товарные накладные ТТН), нормативные требования Минздрава и итоговое ТЗ:

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

Перечислите ключевые сущности («Аптека», «Препарат», «Партия_товара», «Поставщик», «Рецепт») и обоснуйте независимость модели от СУБД:

Этап 3: Логическое проектирование (реляционная модель)

Опишите таблицы, первичные и внешние ключи, нормализацию до 3НФ и правила целостности связей для партий и рецептов:

Этап 4: Физическое проектирование (привязка к СУБД)

Специфицируйте DDL-скрипт, типы данных для сроков годности и цен, структуру B-Tree индексов для ускорения поиска годных партий и партиционирование:

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

Разработайте план резервного копирования (RPO/RTO) и схему синхронизации/репликации данных между центральным складом и аптеками:

2. Контрольный проблемный вопрос:

Почему при отпуске рецептурного препарата правило ссылочной целостности для внешнего ключа рецепта обязано быть ON DELETE RESTRICT, а каскадное удаление (CASCADE) является грубейшей ошибкой?

Отрасль: Городской транспорт, HighLoad и IoT-телеметрия

Вариант 2: Разработка БД «Каршеринговая компания»

Технический контекст: Каршеринг с парком 5 000 автомобилей в мегаполисе. Бортовые контроллеры передают телеметрию (GPS-координаты, скорость, уровень топлива) каждые 3 секунды. Нефункциональное требование (SLA): время отклика валидации аренды и статуса авто в радиусе поиска строго < 100 мс при 10 000 одновременных сессий.

1. Карта 5 этапов жизненного цикла базы данных (DBLC):

Этап 1: Системный анализ и спецификация SLA

Опишите входящие потоки данных с CAN-шины авто, тарифные сетки и формализуйте нефункциональные требования (SLA < 100 мс):

Этап 2: Концептуальное проектирование гео-сущностей

Выделите сущности («Пользователь», «Автомобиль», «Аренда», «Телеметрический_трек», «Геозона») и связи между ними:

Этап 3: Логическое проектирование

Спроектируйте реляционные таблицы, выделите сущность временных рядов (Time-Series) для трекинга и проведите нормализацию справочников:

Этап 4: Физическое проектирование (индексы и шардинг)

Обоснуйте выбор СУБД с поддержкой геоданных (PostgreSQL + PostGIS/TimescaleDB), использование пространственных GiST-индексов и партиционирование телеметрии по дням:

Этап 5: Эксплуатация, администрирование и High Availability

Специфицируйте регламент очистки/архивации исторических треков старше 90 дней, мониторинг Latency через pg_stat_statements и failover:

2. Контрольный проблемный вопрос:

Почему сохранение всей телеметрии (140 млн строк в сутки) в единую непартиционированную реляционную таблицу приведет к полному нарушению SLA (> 100 мс) уже через две недели работы?

Отрасль: E-Commerce, Big Data и витрины данных DWH

Вариант 3: Разработка БД «Аналитическая система маркетплейса»

Технический контекст: Аналитическое хранилище данных (Data Warehouse) крупного маркетплейса. Входы: кликстрим веб-сайта и мобильного приложения (просмотры, клики, скроллы), события добавления в корзину, транзакции оплат. Задача — построение витрин данных под OLAP-отчеты (LTV, воронка конверсии, ABC-анализ) с использованием осознанной денормализации.

1. Карта 5 этапов жизненного цикла базы данных (DBLC):

Этап 1: Системный анализ аналитических требований

Определите источники кликстрима, логи сессий пользователей, финансовые отчеты и формализуйте перечень ключевых бизнес-метрик:

Этап 2: Концептуальное проектирование (Факты и Измерения)

Выделите таблицы фактов («Покупка», «Просмотр») и таблицы измерений («Пользователь», «Товар», «Категория», «Время»):

Этап 3: Логический этап и осознанная денормализация

Опишите схему «Звезда» (Star Schema), приведите доказательство целесообразности денормализации измерений ради ускорения OLAP-агрегаций:

Этап 4: Физическое проектирование в колоночной СУБД

Обоснуйте выбор колоночной СУБД (ClickHouse) или партиционированного PostgreSQL, ключи сортировки (ORDER BY) и сжатие данных:

Этап 5: Эксплуатация, ETL-пайплайны и мониторинг

Опишите регламент пакетной загрузки данных (batch ETL), сжатие архивных слоев на S3 и мониторинг скорости аналитических запросов:

2. Контрольный проблемный вопрос:

В чем фундаментальное противоречие между требованиями к логической модели в транзакционной системе (OLTP) и аналитическом хранилище (OLAP)? Почему 3НФ противопоказана витринам данных?

Отрасль: Здравоохранение, защита информации по 152-ФЗ

Вариант 4: Разработка БД «Частная медицинская клиника»

Технический контекст: База данных многопрофильной клиники. Обрабатываются специальные категории персональных данных (сведения о здоровье, диагнозы по МКБ-10, анализы). Требуется спроектировать архитектуру в строгом соответствии с требованиями Федерального закона №152-ФЗ и стандартами ИСПДн (шифрование TDE, разделение контуров, неизменяемый аудит доступа).

1. Карта 5 этапов жизненного цикла базы данных (DBLC):

Этап 1: Системный анализ и регуляторные требования 152-ФЗ

Специфицируйте входящие медицинские карты (форма 025/у), бланки согласий на обработку ПДн, модель угроз безопасности и ТЗ:

Этап 2: Концептуальное проектирование с разграничением доменов

Выделите сущности («Пациент», «Врач», «Прием», «Медицинская_запись», «Диагноз») и определите их атрибутивный состав:

Этап 3: Логическое проектирование и обезличивание

Опишите реляционную модель с разделением открытых ПДн (ФИО, паспорт) и медицинских записей через суррогатные идентификаторы (UUID):

Этап 4: Физическое проектирование (TDE и журнал аудита)

Специфицируйте прозрачное шифрование табличных пространств (TDE), неизменяемый партиционированный журнал аудита обращений и индексы:

Этап 5: Эксплуатация, администрирование и безопасность

Разработайте регламент зашифрованного резервного копирования, процедуру периодического аудита журналов безопасности и сроки хранения:

2. Контрольный проблемный вопрос:

Почему с точки зрения безопасности 152-ФЗ и архитектуры БД хранение паспортных данных и диагнозов пациентов в одной плоской реляционной таблице считается критической уязвимостью?

Данные успешно сохранены в памяти браузера!