Перейти к основному содержимому

Реляционная модель данных

Play ITЗагрузка интерактивного демо…

Play ITЗагрузка интерактивного демо…

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ
Разработчику Аналитику Архитектору

Математические основы​

Реляционная модель данных (РМД) — логическая модель и прикладная теория для построения БД; опирается на теорию множеств и логику предикатов первого порядка. Эдгар Ф. Кодд сформулировал её в 1969–1970 годах; практическая проверка "насколько СУБД реляционна" — двенадцать правил Кодда.

РМД включает три составляющие:

  1. Структура — данные как набор отношений (relation), то есть подмножеств декартовых произведений доменов.
  2. Целостность — декларативные ограничения на домены, ключи и ссылки между отношениями.
  3. Манипуляция — реляционная алгебра (процедурные операторы) и реляционное исчисление (декларативные предикаты); SQL опирается на оба подхода.

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

В интерфейсе СУБД отношение чаще показывают таблицей, но "таблица" — визуализация; в теории важны заголовок, тело и свойства множества кортежей — см. Теоретические основы.

relationship на ER-диаграмме ("покупатель размещает заказ") в SQL — внешний ключ и JOIN. Обзор терминов: Знакомство с БД. Далее: нормализация, DDL.

Фундаментальные понятия​

  • Домен — множество допустимых значений атрибута с заданным типом и семантикой (например, возраст: целые числа от 0 до 150).
  • Атрибут — именованная характеристика кортежа, связанная с доменом. Имя уникально в пределах отношения.
  • Кортеж — набор значений атрибутов; в таблице представлен строкой.
  • Отношение — множество кортежей одной структуры; в таблице — набор строк с общим заголовком столбцов.

Свойства отношений​

СвойствоСмысл
Нет дубликатов кортежейОтношение — множество, не мультимножество
Нет порядка строкПозиция строки не несёт смысла
Нет порядка столбцовСтолбец идентифицируется именем, не позицией
Атомарность значенийКаждая ячейка — одно значение домена (связь с 1НФ)

Ограничения целостности​

Целостность БД — соответствие данных объявленным правилам; СУБД отклоняет нарушающие операции. Это неправдоподобные значения (отрицательный вес при CHECK (вес > 0)), а не гарантия истинности фактов в реальном мире.

  • Сущностная целостность — первичный ключ не содержит NULL и однозначно идентифицирует кортеж (потенциальный ключ).
  • Ссылочная целостность — внешний ключ ссылается на существующий ключ родительского отношения или равен NULL (если разрешено); политики ON DELETE / ON UPDATE — в ER-модели.
  • Доменная целостность — значение соответствует типу и диапазону столбца.
  • Пользовательские правила — CHECK, триггеры, бизнес-логика на уровне БД.

Подробнее о синтаксисе: Ограничения целостности в SQL; классификация уровней — Теоретические основы.

Отношение на практике — таблица, ключи, связи​

В интерфейсе СУБД отношение показывают таблицей: столбцы — атрибуты с типом, строки — кортежи (записи). Схема задаёт имена и ограничения; данные живут в строках.

ЭлементВ теорииВ SQL / интерфейсе
ОтношениеМножество кортежей одной структурыТаблица
АтрибутИменованная характеристика кортежаСтолбец
КортежОдна запись фактовСтрока
Первичный ключПотенциальный ключ, выбранный для идентификацииPRIMARY KEY
Внешний ключСсылка на ключ родительского отношенияFOREIGN KEY … REFERENCES

SQL и базовые операции с данными​

SQL (Structured Query Language) — стандартный язык доступа к реляционным СУБД. На уровне данных чаще всего выделяют четыре группы операций (иногда их называют CRUD):

ОперацияТипичные команды SQLСмысл
CreateINSERTДобавить новые строки
ReadSELECTПрочитать строки по условию
UpdateUPDATEИзменить существующие значения
DeleteDELETEУдалить строки

Структуру (таблицы, ключи, индексы) описывают DDL (CREATE TABLE, ALTER TABLE) — см. DDL. Первые запросы на живой СУБД — Первые шаги с SQL.

Первичный ключ​

Первичный ключ (PRIMARY KEY) однозначно идентифицирует строку в пределах таблицы: значения уникальны и не допускают NULL. СУБД обычно создаёт для него индекс, ускоряя поиск и соединения.

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title   VARCHAR(100) NOT NULL,
    author  VARCHAR(100)
);

Ключ может быть составным — из нескольких столбцов, если ни один атрибут отдельно не идентифицирует кортеж (например, позиция заказа: (order_id, product_code)).

Естественный и суррогатный ключ​

Естественный ключ — атрибут из предметной области, который уже уникален в реальности — ISBN книги, артикул товара, номер паспорта (если политика данных это допускает).

CREATE TABLE products (
    product_code VARCHAR(20) PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(10, 2)
);

Суррогатный ключ — искусственный идентификатор (SERIAL, IDENTITY, UUID), не несущий смысла для бизнеса, но стабильный при смене "человеческих" реквизитов. На практике часто комбинируют: суррогатный id для связей внутри БД и уникальный естественный код для интеграций и отчётов.

Внешний ключ и ссылочная целостность​

Внешний ключ (FOREIGN KEY) хранит ссылку на первичный (или уникальный) ключ родительской таблицы. СУБД не даст оставить "висячую" ссылку: заказ без существующего клиента, строка заказа без товара.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Политики при удалении и обновении родителя (CASCADE, RESTRICT, SET NULL) — в Entity Relationship.

Типы связей между отношениями​

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

КардинальностьПримерТипичная реализация
Один к одному (1:1)Пользователь ↔ профильFK с UNIQUE в одной из таблиц или общий ключ
Один ко многим (1:N)Клиент → заказыFK customer_id в таблице orders
Многие ко многим (M:N)Товар ↔ категорияСвязующая таблица (junction): пары ключей обеих сторон

Пример M:N через связующую таблицу:

CREATE TABLE category_product (
    category_id INT NOT NULL REFERENCES categories(category_id),
    product_id  INT NOT NULL REFERENCES products(product_id),
    PRIMARY KEY (category_id, product_id)
);

Кардинальность и именование FK — ER-модель; нормализация схемы — Нормализация данных.

JOIN — собрать распределённые данные в одну выборку​

Факты разнесены по таблицам; JOIN соединяет строки по условию равенства ключей (чаще всего FK = PK родителя). Так получают отчёт "заказ + имя клиента + название товара" одним SELECT.

Тип JOINЧто попадает в результат
INNERТолько строки с совпадением в обеих таблицах
LEFTВсе строки левой таблицы; справа NULL, если пары нет
RIGHTВсе строки правой таблицы; слева NULL, если пары нет
FULLОбъединение LEFT и RIGHT (зависит от диалекта SQL)

Подробно — четыре основных JOIN на одном примере, раздел Алиасы, JOIN и объединение таблиц с тренажёром.

Индекс и представление (VIEW)​

Индекс — вспомогательная структура по значениям столбца (или их комбинации). Ускоряет WHERE и JOIN, но удорожает вставку и обновление. Первичный ключ индексируется автоматически; вторичные индексы задают явно (CREATE INDEX).

Представление (VIEW) — сохранённый SELECT, к которому обращаются как к таблице. Скрывает сложность запроса (несколько JOIN, фильтры) и ограничивает видимые столбцы для ролей. Материализованные представления кэшируют результат — глава о VIEW.

Пример проектирования — библиотека​

Исходные требования — книги (ISBN, название, год), авторы (многие ко многим с книгами), выдачи читателям.

Сущности и ключи​

Учебная схема; DDL в стиле PostgreSQL (SERIAL, TEXT). Другие СУБД — справочник диалектов.

Код ITЗагрузка примера кода…

Декомпозиция связи "многие-ко-многим" через таблицу book_authors — стандартный приём реляционного проектирования.

Контрольные вопросы​

  1. Почему отношение не допускает дублирующих кортежей, а таблица в Excel — допускает?
  2. Как отсутствие внутреннего порядка атрибутов влияет на формулировку SQL-запросов?
  3. Почему внешний ключ может быть NULL, а первичный — нет?
  4. Как атомарность значений связана с первой нормальной формой?

См. также​

Содержание