Тема 4. Проектирование структур баз данных — Средства проектирования структур БД: объекты

1. Паспорт учебного занятия и дидактические цели

Цель занятия: Сформировать у обучающихся комплексное системное представление об объектном составе промышленной реляционной СУБД, назначении и жизненном цикле каждого класса объектов (таблицы, схемы, представления VIEW и MATERIALIZED VIEW, последовательности SEQUENCE, составные и перечислимые типы ENUM/COMPOSITE, индексы, роли и привилегии), а также развить практические навыки работы с CASE-инструментами визуального проектирования и генерации DDL-скриптов прямого (Forward) и обратного (Reverse) проектирования.

Задачи занятия:

• Образовательные: Разобрать многоуровневую иерархию пространства имен СУБД (Кластер → База данных → Схема / Пространство имен → Объекты хранения и логики). Освоить синтаксис и архитектурную разницу между обычными динамическими представлениями (CREATE VIEW) и материализованными витринами (CREATE MATERIALIZED VIEW, стратегия обновления REFRESH MATERIALIZED VIEW [CONCURRENTLY]). Изучить атомарные генераторы суррогатных ключей CREATE SEQUENCE, создание доменных типов (CREATE TYPE ... AS ENUM / AS (...)) и разграничение прав (ROLE, GRANT/REVOKE). Освоить принципы визуального моделирования и сопоставления графических нотаций с реальным DDL-кодом в CASE-системах (pgModeler, DBeaver, dbForge Studio, ERwin Data Modeler).
• Развивающие: Развивать абстрактно-логическое и архитектурное инженерное мышление при выборе между избыточностью предвычисленных данных (Materialized View) и транзакционной свежестью (Dynamic View); формировать навыки декомпозиции монолитных схем public на изолированные предметные контексты (схемы/namespaces).
• Воспитательные: Формировать культуру ответственного проектирования безопасности данных (Security through Views, принцип наименьших привилегий Least Privilege), профессиональную дисциплину в именовании объектов и документировании структур СУБД.

2. Детальный хронометраж и тайминг занятия

Методический регламент разработан для двух режимов проведения: стандартного академического (80 минут) и интенсивного/сокращенного (60 минут).

3. Фундаментальный разбор объектов реляционной СУБД

3.1. Иерархия каталога и пространства имен (SCHEMAS / NAMESPACES)

Студенты часто воспринимают СУБД как одну плоскую папку с таблицами. Необходимо ввести строгую пятиуровневую модель:

1. СУБД Кластер (Database Cluster): единый экземпляр СУБД (процессы PostgreSQL/Oracle, общая память Shared Buffers, WAL-журнал, один сетевой порт).
2. База данных (Database): логически изолированное хранилище данных внутри кластера. Кросс-базовые SQL-запросы в стандартном SQL не поддерживаются без специальных расширений (dblink/postgres_fdw).
3. Схема / Пространство имен (Schema / Namespace): логический контейнер внутри базы данных. Предотвращает коллизии имен (например, hr.employees и finance.employees), разграничивает зоны ответственности микросервисов или модулей ERP.
4. Объекты схемы: таблицы, представления, последовательности, типы, функции.
5. Объекты уровня кластера: пользователи, роли (ROLES), табличные пространства (TABLESPACES).

-- Создание изолированных схем
CREATE SCHEMA billing;
CREATE SCHEMA imaging;
CREATE SCHEMA logistics;
CREATE SCHEMA academic;

-- Управление путем поиска схем в сессии
SET search_path TO billing, public;
SHOW search_path;

3.2. Последовательности (SEQUENCE): атомарные генераторы суррогатных ключей

SEQUENCE — это специальный служебный объект реляционной СУБД, спроектированный для высокопроизводительной непрерывной генерации уникальных числовых идентификаторов вне контекста пользовательских транзакций (без блокировок строк таблицы).

CREATE SEQUENCE billing.receipt_seq
    START WITH 100000
    INCREMENT BY 1
    MINVALUE 100000
    MAXVALUE 999999999
    CACHE 20;

-- Использование в DDL таблицы
CREATE TABLE billing.receipts (
    receipt_id BIGINT PRIMARY KEY DEFAULT nextval('billing.receipt_seq'),
    account_number VARCHAR(32) NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

3.3. Пользовательские типы данных: ENUM и COMPOSITE TYPES

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

• Перечислимый тип (ENUM): упорядоченный статичный набор строковых меток. Хранится внутри СУБД компактно (в PostgreSQL — 4 байта), гарантирует строгую типизацию на уровне схемы.
• Составной тип (COMPOSITE): структура из нескольких именованных полей, которая может использоваться как тип столбца в таблицах или аргумент хранимых процедур.

-- Перечислимый тип для модальностей лучевой диагностики
CREATE TYPE imaging.modality_type AS ENUM ('CT', 'MRI', 'XRAY', 'ULTRASOUND', 'PET');

-- Составной тип адреса
CREATE TYPE logistics.geo_point AS (
    latitude NUMERIC(9, 6),
    longitude NUMERIC(9, 6)
);

3.4. Архитектурное сравнение: обычные VIEW против MATERIALIZED VIEW

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

-- 1. Обычное представление (динамическое сокрытие персональных данных)
CREATE VIEW imaging.v_patient_studies_public AS
SELECT 
    study_id,
    study_uid,
    modality,
    study_date,
    regexp_replace(patient_name, '(?<=\S{2})\S+', '***', 'g') AS masked_patient_name,
    doctor_conclusion
FROM imaging.patient_studies;

-- 2. Материализованное представление (тяжелая витрина агрегатов)
CREATE MATERIALIZED VIEW billing.mv_monthly_debt_summary AS
SELECT 
    a.district_id,
    date_trunc('month', n.accrual_date) AS accrual_month,
    count(DISTINCT a.subscriber_id) AS total_subscribers,
    sum(n.accrual_amount) AS total_accrued,
    sum(n.paid_amount) AS total_paid,
    sum(n.accrual_amount - n.paid_amount) AS total_debt
FROM billing.subscribers a
JOIN billing.accruals n ON a.subscriber_id = n.subscriber_id
GROUP BY a.district_id, date_trunc('month', n.accrual_date);

-- Построение уникального индекса для обеспечения CONCURRENT обновления
CREATE UNIQUE INDEX idx_mv_monthly_debt_pk 
ON billing.mv_monthly_debt_summary (district_id, accrual_month);

-- Регламентное фоновое обновление без эксклюзивной блокировки чтения
REFRESH MATERIALIZED VIEW CONCURRENTLY billing.mv_monthly_debt_summary;

3.5. Роли, пользователи и дискреционное разграничение прав (DCL)

Объекты СУБД защищаются через модель ролей. В PostgreSQL роль (ROLE) объединяет концепции пользователя и группы.

• CREATE ROLE analyst_read WITH LOGIN PASSWORD '***';
• GRANT USAGE ON SCHEMA billing TO analyst_read;
• GRANT SELECT ON billing.mv_monthly_debt_summary TO analyst_read;
• REVOKE ALL ON billing.subscribers FROM analyst_read; — запрет прямого доступа к чувствительным таблицам с предоставлением доступа только через проверенное представление!

4. CASE-инструменты визуального проектирования и DDL-инженерия

Промышленное проектирование редко ведется ручным написанием гигантских скриптов. Архитектор использует CASE-инструменты (Computer-Aided Software Engineering):

Двунаправленная DDL-инженерия:

• Прямое проектирование (Forward Engineering): графическая модель сущностей, представлений и последовательностей на диаграмме → автоматическая компиляция в исполняемый файл schema.sql (команды CREATE SCHEMA, CREATE SEQUENCE, CREATE TABLE, CREATE VIEW).
• Обратное проектирование (Reverse Engineering): подключение к существующей промышленной СУБД через драйвер JDBC/интроспекция системного каталога → автоматическое построение наглядной визуальной ERD-диаграммы со всеми объектами, внешними ключами и типами.

5. Типовые ошибки обучающихся и методы их упреждения

6. Педагогические приемы и интерактивные механики

• Метод проблемного диалога: начать занятие с кейса падения сервера аналитики: «100 менеджеров одновременно обновляют страницу отчета с 8 JOIN и 3 миллионами строк. Сервер лег. Как спасти систему, не меняя код фронтенда? Ответ: MATERIALIZED VIEW».
• Архитектурный батл (Design Review): разделение группы на микрокоманды при разборе 4 прикладных вариантов из рабочего листа: обоснование разделения схем, выбор стратегии актуализации витрин (ночной batch-job по расписанию cron против оперативного триггерного обновления).
• Инженерная рефлексия: сопоставление абстрактных диаграмм в CASE-пакетах с фактическим системным каталогом СУБД (pg_class, pg_views, pg_sequences).