ИТ.03 - 11 - Модели БД и способы их представления. ER-диаграммы

Введение

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

В этой теме разберём:

• что такое модель данных и зачем она нужна;
• уровни моделей (концептуальный, логический, физический);
• элементы ER-модели: сущности, атрибуты, связи и их кратности;
• как построить простую ER-диаграмму в нотации Mermaid и как она помогает при создании связанных таблиц.

Зачем нужны модели данных

Модель данных описывает, какую информацию мы храним и как объекты связаны между собой. Она:

• даёт всем участникам проекта единое понимание предметной области;
• служит основой для создания таблиц и ограничений в СУБД;
• помогает выявить лишние связи и дубли ещё до написания SQL-кода;
• показывает зоны, в которых придётся выполнять JOIN, INSERT в несколько таблиц и т.д.

Уровни моделей

Концептуальный уровень

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

```mermaid
flowchart LR
  %% Сущности
  user["Клиент"]
  order{"Заказ"}
  product["Товар"]

  %% Атрибуты
  name([Атрибут: ФИО])
  price([Атрибут: Цена])
  in_stock([Атрибут: В наличии])
  status([Атрибут: Статус])
  quantity([Атрибут: Количество])

  %% Связи
  user --- order --- product

  user -.- name
  product -.- price
  product -.- in_stock
  order -.- status
  order -.- quantity
```

Сущности — прямоугольники, атрибуты — овалы, связи — линии.

Логический уровень

Логическая модель уже описывает атрибуты, ключи, кратности и ограничения (но ещё не привязана к конкретной СУБД).

```mermaid
erDiagram
  customer["Клиент"] {
    INTEGER id PK
    STRING full_name
    STRING phone
  }
  service["Услуга"] {
    INTEGER id PK
    STRING title
    DECIMAL base_price
  }
  order["Заказ"] {
    INTEGER id PK
    INTEGER customer_id FK
    INTEGER employee_id FK
    DATE created_at
  }
  employee["Сотрудник"] {
    INTEGER id PK
    STRING full_name
    STRING role
  }
  order_service["Заказ-Услуга"] {
    INTEGER id PK
    INTEGER order_id FK
    INTEGER service_id FK
    DECIMAL price
  }

  customer ||--o{ order : ""
  employee ||--o{ order : ""
  order ||--o{ order_service : ""
  service ||--o{ order_service : ""
```

Физический уровень

Когда структура согласована, переносим её в SQL: выбираем конкретные типы данных, добавляем индексацию, каскадные правила, комментарии.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  phone TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  employee_id INTEGER NOT NULL REFERENCES employees(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  role TEXT CHECK (role IN ('admin', 'manager'))
);

На физическом уровне мы учитываем особенности конкретной СУБД (типы данных, индексы и ограничения и т.п.).

ER-диаграмма и нотации

ER-диаграмма (Entity-Relationship Diagram) — графическое описание модели. На ней показывают сущности, атрибуты и связи между ними. Чтобы диаграммы “читались” одинаково разными специалистами, используются нотации — наборы правил, которые описывают, как изображать сущности, атрибуты и кратности. Перечислим самые известные нотации и приведём примеры.

Нотация Питера Чена

Сущности рисуют прямоугольниками, атрибуты — овалами, связи — ромбами. Это самая «классическая» форма ER-диаграмм, удобная для обсуждения предметной области без технических деталей.

Нотация Crow's Foot

Связи изображаются линиями с «вороньими лапками», показывающими кратности (1 — 1, 1 — N, M — N). Сущности — прямоугольники с атрибутами. Это самый популярный вариант в современных инструментах.

Нотации IDEF1X, UML и другие

Используются в корпоративных методологиях или CASE-средствах. Они добавляют собственные символы для первичных/внешних ключей, жизненных циклов сущности, наследования.

Нотация Mermaid

Мы будем использовать нотацию Mermaid (подвид Crow's Foot на языке Mermaid). Диаграммы легко создать на сайте mermaid.live, где можно вводить текстовую запись, а система автоматически построит граф. Преимущества такой записи:

• диаграмма хранится как текст, её легко версионировать;
• не нужно привязываться к конкретной СУБД;
• одну и ту же диаграмму можно встроить в документацию, репозиторий или показать в браузере.

Сущности и атрибуты

• Сущность (Entity) — объект предметной области, о котором нужно хранить данные (например, Студент, Курс, Заказ).
• Атрибут (Attribute) — характеристика сущности (ФИО студента, номер курса, цена заказа).

Пример описания сущности:

```mermaid
erDiagram
  student["Студент"] {
    INTEGER   id PK               "Первичный ключ"
    STRING    first_name          "Имя"
    STRING    last_name           "Фамилия"
    INTEGER   group_id FK         "Группа"
    ENUM      gender              "Пол (м/ж)"
    BOOL      is_active           "Учится / отчислен"
    DATE      date_of_birth       "Дата рождения"
    DATE      date_of_assignment  "Дата зачисления"
    DATE      date_of_graduation  "Дата окончания"
  }
```

Связи и кратности

Связь (Relationship) показывает, как сущности взаимодействуют. Для каждой связи важно задать кратности (cardinality) — сколько экземпляров сущности участвует.

В нотации Mermaid кратности обозначаются символами:

```mermaid
erDiagram
  student["Студент"] {
    INTEGER   id PK         "Первичный ключ"
    STRING    first_name    "Имя"
    STRING    last_name     "Фамилия"
    INTEGER   group_id FK   "Группа"
  }
  group["Группа"] {
    INTEGER   id PK         "Первичный ключ"
    STRING    title         "Название группы"
  }

  group ||--o{ student : "содержит"
```

Определяющие и не определяющие связи

В ER-моделях выделяют два типа связей:

• Определяющая (identifying) — дочерняя сущность не существует без родительской. Обычно её первичный ключ включает ключ родителя. На диаграмме связь рисуют сплошной линией.
• Не определяющая (non-identifying) — сущность существует сама по себе, а внешний ключ добавляется только для связи. На диаграмме это пунктирная линия.

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

В левом блоке «Заявка» имеет собственный ключ, а ссылки на клиента и менеджера нужны лишь для связи. В правом блоке дочерние сущности «Позиция заказа» и «Счёт» живут только в контексте конкретного заказа — их ключи зависят от родительского «Заказа».

ER-диаграмма для простого интернет-магазина

Возьмём ограниченную предметную область: покупатель оформляет заказы, заказ состоит из строк с товарами.

```mermaid
erDiagram
  customer["Покупатель"] {
    INTEGER id PK                "Первичный ключ"
    STRING full_name             "ФИО"
    STRING email                 "Электронная почта"
  }

  order["Заказ"] {
    INTEGER id PK                "Первичный ключ"
    INTEGER customer_id FK       "Покупатель"
    DATE created_at              "Дата создания"
    STRING status                "Статус"
  }

  order_item["Детали заказа"] {
    INTEGER id PK                "Первичный ключ"
    INTEGER order_id FK          "Заказ"
    INTEGER product_id FK        "Товар"
    INTEGER quantity             "Количество"
    DECIMAL unit_price           "Цена"
  }

  product["Товар"] {
    INTEGER id PK                "Первичный ключ"
    STRING title                 "Название"
    DECIMAL price                "Текущая цена"
  }

  customer ||--o{ order : "оформляет"
  order ||--o{ order_item : "содержит"
  product ||--o{ order_item : "используется"
```

• Покупатель связан с Заказом как 1 — N: один человек может оформить много заказов, но каждый заказ принадлежит только ему.
• Заказ и Товар взаимодействуют через сущность Детали заказа. Это типичная реализация связи M — N, превращённая в две связи 1 — N.
• На этапе реализации каждую сущность превратим в таблицу, а поля id станут первичными ключами. В таблице деталей заказа появятся внешние ключи order_id, product_id.

Как перейти от ER-диаграммы к таблицам

1. Таблица = сущность. Для каждого прямоугольника на диаграмме создаём таблицу. Колонки берём из атрибутов.
2. Первичные ключи. Поле, помеченное как PK, становится первичным ключом (PRIMARY KEY). Если ключ не указан, его нужно выбрать (обычно автоинкрементное id).
3. Внешние ключи. Для каждого ребра 1 — N добавляем в таблицу «многие» колонку со ссылкой на «один»: например, orders.customer_id REFERENCES customers(id).
4. Связи M — N. Разрываем на две связи 1 — N через промежуточную таблицу (как order_items). В неё переносим дополнительные атрибуты (количество, цены).
5. Типы данных и ограничения. Для каждого поля выбираем подходящий тип SQLite/MySQL, добавляем NOT NULL, UNIQUE, значения по умолчанию и т.д.
6. Дополнительные параметры. На физическом уровне можно задать действия при удалении/обновлении (ON DELETE/UPDATE) и заранее продумать, какие поля потребуется ускорять каждым типом запросов (к этому вернёмся позже).

Инструменты для построения ERD

Mermaid.live

mermaid.live — текстовый редактор диаграмм. Пишем код на языке Mermaid (как в примерах лекции) и сразу видим визуализацию. Плюсы: диаграммы легко хранить в текстовом виде, есть подсветка синтаксиса, можно экспортировать в PNG/SVG.

drawDB

drawDB — графический конструктор, в котором сущности и связи рисуются мышкой. Работает в браузере и офлайн (есть версии для сборки при помощи Docker), поддерживает выгрузку диаграммы сразу в SQL (SQLite, MySQL и др.), а также импорт/экспорт проектов.

Для упражнений ниже можно выбрать любой инструмент: если нравится писать текстом — используйте Mermaid; если удобнее графически — drawDB.

Практические задания

Задание 1. Концептуальная диаграмма

```mermaid
flowchart LR
  teacher[Преподаватель]
  course[Курс]
  enrollment[Запись на курс]
  student[Студент]

  teacher --- course
  course --- enrollment
  student --- enrollment
```

Задание 2. Логическая диаграмма

```mermaid
erDiagram
  teacher["Преподаватель"] {
    INTEGER id PK             "Первичный ключ"
    STRING full_name          "ФИО"
    STRING email              "Контакт"
  }

  course["Курс"] {
    INTEGER id PK             "Первичный ключ"
    STRING title              "Название"
    INTEGER teacher_id FK     "Ведущий преподаватель"
  }

  student["Студент"] {
    INTEGER id PK             "Первичный ключ"
    STRING full_name          "ФИО"
    STRING group_code         "Группа"
  }

  enrollment["Запись на курс"] {
    INTEGER id PK             "Первичный ключ"
    INTEGER student_id FK     "Студент"
    INTEGER course_id FK      "Курс"
    DATE enrolled_at          "Дата записи"
  }

  teacher ||--o{ course : "ведёт"
  course ||--o{ enrollment : "имеет записи"
  student ||--o{ enrollment : "посещает"
```

Задание 3. Физическая реализация и проверка

┌────┬─────┬──────────┬────────────┬────┬───────────┬───────────┬───────┐
│ id │ seq │  table   │    from    │ to │ on_update │ on_delete │ match │
├────┼─────┼──────────┼────────────┼────┼───────────┼───────────┼───────┤
│ 0  │ 0   │ teachers │ teacher_id │ id │ NO ACTION │ NO ACTION │ NONE  │
└────┴─────┴──────────┴────────────┴────┴───────────┴───────────┴───────┘
┌────┬─────┬──────────┬────────────┬────┬───────────┬───────────┬───────┐
│ id │ seq │  table   │    from    │ to │ on_update │ on_delete │ match │
├────┼─────┼──────────┼────────────┼────┼───────────┼───────────┼───────┤
│ 0  │ 0   │ courses  │ course_id  │ id │ NO ACTION │ NO ACTION │ NONE  │
│ 1  │ 0   │ students │ student_id │ id │ NO ACTION │ NO ACTION │ NONE  │
└────┴─────┴──────────┴────────────┴────┴───────────┴───────────┴───────┘

-- Ваш код можете писать тут

CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT
);

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  teacher_id INTEGER NOT NULL REFERENCES teachers(id)
);

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  group_code TEXT
);

CREATE TABLE enrollments (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  enrolled_at TEXT DEFAULT (date('now'))
);

PRAGMA foreign_key_list('courses');
PRAGMA foreign_key_list('enrollments');