Механизмы управления реляционными базами данных

1. Реляционные базы данных: основные понятия

Реляционные базы данных — это системы управления базами данных, в которых данные организованы в виде таблиц (отношений). Каждая таблица состоит из строк и столбцов: строки представляют записи (объекты), а столбцы — атрибуты (свойства) этих объектов. Основной принцип реляционной модели: все данные представляются в виде взаимосвязанных таблиц, что делает возможным выполнение сложных запросов для выборки, фильтрации и объединения данных.

Основные понятия:

• Таблица (Relation) — основная структура хранения данных, двумерная таблица; строка называется записью, столбец — полем (атрибутом).
• Первичный ключ (Primary Key) — уникальный идентификатор записи в таблице, обеспечивающий однозначность каждой строки.
• Отношение (Relation) — формальный термин для таблицы в реляционной модели; каждое отношение является конечным набором записей одинаковой структуры.

2. Формальное определение отношения

Пусть дана совокупность типов данных \(T_1, T_2, \ldots, T_n\), называемых также доменами, не обязательно различных. Тогда n-арным отношением R, или отношением R степени n, называют подмножество декартова произведения множеств \(T_1 \times T_2 \times \ldots \times T_n\).

Отношение R состоит из заголовка (схемы) и тела. Более строго:

• Заголовок (схема) H отношения R — конечное множество упорядоченных пар вида (A_i, T_i), где A_i — имя атрибута, а T_i — имя типа (домена), i = 1, …, n. По определению требуется, чтобы все имена атрибутов в заголовке были различными (уникальными).
• Тело B отношения R — множество кортежей t. Кортеж t, соответствующий заголовку H, — множество упорядоченных триплетов вида ⟨A_i, T_i, v_i⟩, по одному для каждого атрибута в H, где v_i — допустимое значение домена T_i. Поскольку имена атрибутов уникальны, указание домена в кортеже обычно излишне, поэтому кортеж нередко определяют как множество пар (A_i, v_i).

Количество кортежей называют кардинальным числом отношения (кардинальностью), или мощностью отношения. Количество атрибутов называют степенью, или «арностью», отношения: отношение с одним атрибутом — унарное, с двумя — бинарное, с n атрибутами — n-арное. С точки зрения теории вполне корректно и отношение с нулевым количеством атрибутов, которое либо не содержит кортежей, либо содержит единственный пустой кортеж.

Основные свойства отношения:

• в отношении нет двух одинаковых элементов (кортежей);
• порядок кортежей в отношении не определён;
• порядок атрибутов в заголовке отношения не определён.

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

Отношение имеет простую графическую интерпретацию — таблицу, столбцы которой соответствуют атрибутам, строки — кортежам, а в ячейках находятся значения атрибутов. Тем не менее в строгой реляционной модели отношение — не таблица, кортеж — не строка, атрибут — не столбец: «дружественные» термины — всего лишь приближение.

По определению К. Дж. Дейта, таблица является прямым и верным представлением отношения, если она удовлетворяет пяти условиям:

1. нет упорядочивания строк сверху вниз (порядок строк не несёт информации);
2. нет упорядочивания столбцов слева направо;
3. нет повторяющихся строк;
4. каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена — и больше ничего;
5. все столбцы являются «обычными»: в таблице нет скрытых компонентов, доступных только через специальные операторы, нет скрытых идентификаторов строк и временных меток — строки идентифицируются только значениями потенциальных ключей.

2.1. Пример

Пусть заданы следующие типы (домены): T₁ = {Иванов, Петров, Сидоров}, T₂ = {Информатика, Геодезия}, T₃ = {3, 5, 4}.

Декартово произведение \(T_1 \times T_2 \times T_3\) состоит из 18 кортежей, где каждый кортеж содержит три значения: фамилию, учебную дисциплину и оценку. Фрагмент произведения:

Тело отношения R моделирует реальную ситуацию и содержит пять кортежей, соответствующих результатам сессии (при условии, что Петров экзамен по информатике не сдавал):

3. Реляционная алгебра

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

N-арную реляционную операцию f можно представить функцией, возвращающей отношение и имеющей n отношений в качестве аргументов:

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

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

Ниже приведён список из восьми операций, изначально предложенных создателем реляционной модели Эдгаром Коддом. Все они, кроме деления, широко востребованы и сегодня; список не является исчерпывающим — на практике используется гораздо больше реляционных операций.

3.1. Теоретико-множественные операторы

ПРОИЗВЕДЕНИЕ (×) — построить декартово произведение двух отношений. Пусть R — таблица степени k₁, S — таблица степени k₂. Тогда R × S — множество всех (k₁ + k₂)-кортежей, первые k₁ элементов которых образуют кортеж из R, а последние k₂ — кортеж из S.

ОБЪЕДИНЕНИЕ, UNION (∪) — построить теоретико-множественное объединение двух таблиц. Даны таблицы R и S (обе должны иметь одинаковую степень); объединение R ∪ S — множество кортежей, принадлежащих R, или S, или обоим.

ПЕРЕСЕЧЕНИЕ, INTERSECT (∩) — построить теоретико-множественное пересечение таблиц: множество кортежей, принадлежащих и R, и S. Отношения также должны иметь одинаковую степень.

ВЫЧИТАНИЕ, DIFFERENCE (−) — построить множество различий двух таблиц одинаковой степени: R − S — это множество кортежей R, не принадлежащих S.

3.2. Специальные реляционные операторы

ВЫБОРКА, SELECT (σ) — извлечь кортежи отношения, которые удовлетворяют заданным условиям. Пусть R — таблица с атрибутом A. Тогда

где t — кортеж R, а t(A) — значение атрибута A кортежа t. В простейшем случае условие имеет вид \(\sigma_C(R)\), где C — сравнение с одним из операторов (=, ≠, <, > и т. д.). Такие выборки называются θ-выборками (тэта-выборками).

Синтаксис операции выборки: \(\sigma_C(R)\), что эквивалентно SELECT * FROM R WHERE C.

Пример. Дано отношение A с информацией о сотрудниках:

Результат выборки \(\sigma_{\text{Зарплата} < 3000}(A)\):

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

ПРОЕКЦИЯ (π) — извлечь заданные атрибуты (колонки) из отношения. Пусть R — отношение с атрибутом X. Тогда

где t(X) — значение атрибута X кортежа t. При проекции задаётся проецируемое отношение и набор его атрибутов, который станет заголовком результирующего отношения.

Пример. Отношение о поставщиках:

Проекция на атрибут «Город поставщика» содержит три кортежа — дубликат «Москва» удалён:

СОЕДИНЕНИЕ, JOIN (⋈) — соединить две таблицы по их общим атрибутам. Пусть R — таблица с атрибутами A, B, C, а S — таблица с атрибутами C, D, E; общий атрибут — C. Тогда

Что здесь происходит? Сначала вычисляется декартово произведение R × S. Затем выбираются кортежи, у которых значения общего атрибута совпадают (σ_R.C=S.C). Полученная таблица содержит атрибут C дважды — повторяющаяся колонка удаляется проекцией.

Пример. Отношения «Служащий» и «Отдел», условие соединения — «Служащий.[Код отдела] = Отдел.[Код отдела]»:

Результат соединения:

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

Разновидности операции соединения:

• Общая операция соединения — самый широкий случай.
• θ-соединение — отношение с кортежами из декартова произведения R и S, удовлетворяющими предикату p вида R.a_i θ S.b_i, где θ — один из операторов сравнения (<, ≤, >, ≥, =, ≠).
• Эквисоединение — частный случай θ-соединения, когда θ — проверка на равенство (=).
• Естественное соединение — частный случай эквисоединения: отношения соединяются по всем общим атрибутам, один экземпляр каждого общего атрибута исключается из результата.

Наиболее важный частный случай — естественное соединение. Пусть даны отношения A(A₁, …, A_n, X₁, …, X_p) и B(X₁, …, X_p, B₁, …, B_m) с одинаковыми атрибутами X₁, …, X_p (одинаковые имена, одинаковые домены). Тогда естественным соединением A и B называется отношение с заголовком (A₁, …, A_n, X₁, …, X_p, B₁, …, B_m) и телом, содержащим кортежи (a₁, …, a_n, x₁, …, x_p, b₁, …, b_m) такие, что (a…, x…) ∈ A и (x…, b…) ∈ B. Для него используется специальный синтаксис — A JOIN B.

ДЕЛЕНИЕ, DIVISION (÷) — применяется, когда нужно найти множество значений, связанных со всеми значениями другого множества. Пусть R — отношение с атрибутами A и B, а S — унарное отношение с атрибутом B. Тогда R ÷ S — множество значений A, для которых в R существуют все соответствующие значения из S.

В общем виде: пусть отношения R и S имеют атрибуты X₁, …, X_m и Y₁, …, Y_n соответственно, причём имена не пересекаются, а отношение-посредник T имеет заголовок из объединения заголовков R и S. Рассматривая {X₁, …, X_m} и {Y₁, …, Y_n} как составные атрибуты X и Y, операцию деления R на S по T записывают как R DIVIDEBY S PER T. Результат — отношение с заголовком {X} и телом из таких кортежей {X x}, присутствующих в R, что кортеж {X x, Y y} присутствует в T для всех кортежей {Y y} из S. Иными словами, результат состоит из тех значений X, для которых соответствующие значения Y в T включают все значения Y из S.

3.3. Выразимость одних операций через другие

Некоторые реляционные операции выражаются через другие — базис алгебры избыточен:

• пересечение через вычитание: A ∩ B = A − (A − B);
• соединение через произведение и выборку: (A TIMES B) WHERE X = Y;
• деление через вычитание, произведение и проекцию: A ÷ B = A[X] MINUS ((A[X] TIMES B) MINUS A)[X].

4. Тренажёр реляционной алгебры

Все восемь операций Кодда — на маленьких отношениях из этой лекции. Выберите операцию: тренажёр покажет операнды, вычислит результат и приведёт SQL-эквивалент. У выборки и проекции есть параметры — условие и набор атрибутов; попробуйте разные комбинации и проследите, как меняется результат. Кортежи, попавшие в результат, подсвечиваются в исходных таблицах.

5. Методы моделирования БД

Моделирование данных обычно начинается с концептуального представления данных, а затем их повторного представления в контексте выбранных технологий. Аналитики и заинтересованные стороны создают несколько типов моделей на этапе проектирования. В СУБД используются три основных типа моделей данных: концептуальные, логические и физические. Каждый тип служит своим целям и представляет свой уровень абстракции.

5.1. Концептуальное моделирование

Концептуальное моделирование — первый шаг процесса, ориентированный на высокоуровневое абстрактное представление требований организации к данным. Оно включает идентификацию ключевых объектов, их атрибутов и связей между ними — без подробностей о типах данных или хранении. Основная цель — чётко понять требования бизнеса и сформировать основу для следующих этапов.

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

Основные компоненты:

• Сущности — ключевые объекты или концепции предметной области: клиенты, продукты, заказы, сотрудники.
• Атрибуты — свойства объектов: имя клиента, цена продукта, дата заказа, идентификатор сотрудника.
• Отношения — связи между объектами: клиент размещает несколько заказов, продукт принадлежит категории, сотрудник работает в отделе.

Создание концептуальной модели включает этапы:

1. Определить сущности — перечислить ключевые объекты домена, которые требуют хранения и поиска.
2. Определить атрибуты — зафиксировать основные свойства каждой сущности, не углубляясь в типы данных и ограничения.
3. Установить отношения — проанализировать связи и убедиться, что они имеют смысл с точки зрения бизнеса.
4. Обзор и уточнение — проверить модель на несоответствия, избыточность и недостающую информацию, при необходимости обновить.

Пример — концептуальная модель авиаперелётов:

Основная сущность — бронирование (bookings). В одно бронирование можно включить несколько пассажиров, каждому выписывается отдельный билет (tickets). Билет имеет уникальный номер и содержит информацию о пассажире; как таковой пассажир отдельной сущностью не является — имя и номер документа могут меняться со временем, так что однозначно найти все билеты одного человека невозможно; для простоты считаем всех пассажиров уникальными.

Билет включает один или несколько перелётов (ticket_flights): когда нет прямого рейса и полёт идёт с пересадками либо когда билет взят «туда и обратно». Жёсткого ограничения в схеме нет, но предполагается, что все билеты одного бронирования имеют одинаковый набор перелётов.

Каждый рейс (flights) следует из одного аэропорта (airports) в другой. Рейсы с одним номером имеют одинаковые пункты вылета и назначения, но отличаются датой отправления.

При регистрации пассажиру выдаётся посадочный талон (boarding_passes) с указанием места. Пассажир может зарегистрироваться только на рейс из своего билета; комбинация рейса и места уникальна — чтобы не выдать два талона на одно место.

Количество мест (seats) и их распределение по классам обслуживания зависит от модели самолёта (aircrafts), выполняющего рейс. Предполагается, что каждая модель имеет одну компоновку салона. Схема не контролирует соответствие мест в талонах реальным местам самолёта — такая проверка делается табличными триггерами или в приложении.

5.2. Логическое моделирование

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

Важнейшие компоненты:

• Сущности, атрибуты и связи — сохраняют значение из концептуальной модели.
• Типы данных — каждому атрибуту назначается тип: целые числа, строки, даты.
• Ограничения — правила, которым должны соответствовать данные: уникальность, ссылочная целостность, ограничения домена.

Шаги создания: уточнение сущностей, атрибутов и связей → определение типов данных и ограничений → нормализация (устранение избыточности, проверка соответствия 1НФ, 2НФ, 3НФ — подробно в лекции 3).

Пример — логическая модель перелётов:

Таблицы:

1. Aircrafts — код самолёта, модель, максимальная дальность полёта (км);
2. Airports — код аэропорта, название, город, координаты (широта, долгота), временная зона;
3. Boarding_passes — номер билета, идентификатор рейса, номер посадочного талона, номер места;
4. Bookings — номер бронирования, дата, полная сумма;
5. Flights — идентификатор рейса, номер рейса, время вылета и прилёта по расписанию, аэропорты отправления и прибытия, статус, код самолёта, фактические времена вылета и прилёта;
6. Seats — код самолёта, номер места, класс обслуживания;
7. Ticket_flights — номер билета, идентификатор рейса, класс обслуживания, стоимость перелёта;
8. Tickets — номер билета, номер бронирования, идентификатор, имя и контактные данные пассажира.

Представления:

1. bookings.flights_v — рейс со временами по расписанию и местными, планируемой и фактической продолжительностью полёта, кодами, названиями и городами аэропортов отправления и прибытия, статусом и кодом самолёта;
2. routes — материализованное представление: номер рейса, аэропорты отправления и прибытия с городами, код самолёта, продолжительность полёта, дни недели выполнения рейса.

5.3. Физическое моделирование

Физическое моделирование — последний этап, на котором логическая модель преобразуется в реальную реализацию в конкретной СУБД. Это самое детальное представление: вся информация для создания и управления объектами базы — таблицами, индексами, представлениями и ограничениями.

Ключевые компоненты:

• Таблицы — фактические структуры хранения сущностей; строка таблицы — экземпляр сущности;
• Столбцы — атрибуты логической модели с типами данных и свойствами, специфичными для базы;
• Индексы — дополнительные структуры для ускорения поиска данных;
• Внешние ключи и ограничения — связи между таблицами с поддержанием ссылочной целостности на уровне базы.

Этапы: выбор СУБД (PostgreSQL, MySQL, SQL Server — выбор определяет доступные типы и ограничения) → сопоставление логических сущностей таблицам → определение индексов и ограничений → создание объектов базы инструментом моделирования или SQL-скриптами. Физическая модель — важный справочный документ и для разработчиков, и для аналитиков, и для системных администраторов.

5.4. Методики логического проектирования

Для логического проектирования реляционных хранилищ данных применяются методики:

• Моделирование «сущность-связь» (ER modeling) — абстрактная модель предметной области из сущностей (entities), взаимосвязей (relationships) и атрибутов (attributes).
• Многомерное моделирование (Dimensional modeling) — модель из показателей или метрик (measures), фактов (facts) и измерений (dimensions).
• Моделирование временных данных (Temporal data modeling) — модель временных рядов: временные метки (timestamps), временной ряд (time series), даты, диапазоны дат, классы.
• «Свод данных» (Data Vault) — модель на математических принципах нормализации: сущности-концентраторы (Hub Entities), связывающие сущности (Link Entities), сущности-сателлиты (Satellite Entities).

5.5. Основные понятия модели «сущность-связь»

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

В системе сущность представлена экземплярами: экземпляры сущности «Аэропорт» — аэропорты «Домодедово», «Пулково», «Воронеж». У сущностей есть атрибуты — характеристики, которые их описывают: код, адрес, номер телефона. Атрибуты есть у каждого экземпляра, но значения у них разные: у «Домодедово» и «Воронежа» одинаковый атрибут «Адрес» с разными значениями.

Собрав сущности, аналитик выясняет, как они связаны, и составляет ER-модель (entity–relationship, модель «сущность-связь»). Типы связей:

• «Один-к-одному» — экземпляр сущности связан только с одним экземпляром другой: пассажир рейса и его место в самолёте.
• «Один-ко-многим» — экземпляр связан со множеством экземпляров другой сущности: у пассажира несколько единиц багажа, но каждая единица принадлежит одному пассажиру.
• «Многие-ко-многим» — множества экземпляров связаны с обеих сторон: аэропорт обслуживает несколько авиакомпаний, каждая авиакомпания обслуживается в нескольких аэропортах.

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

Для построения ER-диаграмм используют разные нотации. Три самые известные:

1. Нотация IDEF1X — фундаментальная, но на практике давно не используется: есть более удобные варианты.
2. Нотация Чена — классическая, из простых символов: прямоугольников, овалов и линий. Её часто используют для концептуальных моделей, которые презентуют заказчику: человеку, далёкому от аналитики данных, проще разобраться в понятных диаграммах.
3. Нотация Мартина («воронья лапка», Crow's Foot) — компактнее нотации Чена, поэтому используется для моделей логического уровня, где нужно описать все атрибуты сущностей.

Элементы диаграммы в нотации Чена соединяются линиями; над линией между сущностями обозначают тип связи: 1:1 — «один-к-одному», 1:N — «один-ко-многим», M:N — «многие-ко-многим».

В нотации Мартина сущность также вписывается в прямоугольник, но атрибуты перечисляются прямо под сущностью, а связи рисуются разными соединительными линиями:

Три типа связи в нотации Мартина изображаются разными комбинациями окончаний линий; например, связь «многие-ко-многим» выглядит так:

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

• Дайте формальное определение отношения степени n. Что такое заголовок и тело?
  n-арное отношение R — подмножество декартова произведения доменов T₁ × T₂ × … × Tₙ. Заголовок H — конечное множество пар (Aᵢ, Tᵢ) с уникальными именами атрибутов; тело B — множество кортежей, каждый из которых сопоставляет каждому атрибуту допустимое значение его домена. Мощность — число кортежей, степень (арность) — число атрибутов.
• Что означает замкнутость реляционной алгебры и что она даёт на практике?
  Результат каждой операции сам является отношением. Поэтому операции можно вкладывать друг в друга, строя выражения любой сложности, — именно так СУБД строит планы сложных запросов из простых операций; промежуточные (неименованные) отношения вычисляются на лету.
• Перечислите восемь операций Кодда и разделите их на две группы.
  Теоретико-множественные: произведение (×), объединение (∪), пересечение (∩), вычитание (−). Специальные реляционные: выборка (σ), проекция (π), соединение (⋈), деление (÷). Для ∪, ∩ и − отношения должны иметь одинаковую степень.
• Выразите соединение через другие операции алгебры.
  R ⋈ S = π (σ_{R.C=S.C} (R × S)): декартово произведение, затем выборка по равенству общих атрибутов, затем проекция, убирающая повторяющуюся колонку. Аналогично: пересечение A ∩ B = A − (A − B); деление выражается через вычитание, произведение и проекцию.
• Чем θ-соединение отличается от эквисоединения и естественного соединения?
  θ-соединение допускает любой оператор сравнения в предикате (<, ≤, >, ≥, =, ≠); эквисоединение — частный случай с равенством; естественное соединение — частный случай эквисоединения по всем общим атрибутам, причём дубликаты общих атрибутов исключаются из результата.
• Какие запросы реализуются операцией деления? Приведите пример.
  Запросы со словом «все»: R(Студент, Дисциплина) ÷ S(Дисциплина) даёт студентов, сдавших все дисциплины из S. Результат — значения атрибута X из R, для которых в R есть пары со всеми значениями Y из S.
• Чем концептуальная модель отличается от логической и физической?
  Концептуальная — высокоуровневая: сущности, атрибуты, связи, без типов данных; язык общения с заказчиком. Логическая добавляет типы данных, ограничения и нормализацию, но не зависит от СУБД. Физическая — реализация в конкретной СУБД: таблицы, столбцы, индексы, внешние ключи.
• Назовите три нотации ER-диаграмм. Когда предпочесть нотацию Чена, а когда — Мартина?
  IDEF1X (историческая, почти не используется), нотация Чена и нотация Мартина («воронья лапка»). Чена — для концептуальных моделей и презентаций заказчику: простые понятные символы. Мартина — для логического уровня: компактна и вмещает все атрибуты сущностей.

Источники

1. Codd, E. F. A Relational Model of Data for Large Shared Data Banks // Communications of the ACM. — 1970. — Vol. 13, № 6. — P. 377–387.
2. Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт. — 8-е изд. — М. : Вильямс, 2005. — 1328 с.
3. Гарсиа-Молина, Г. Системы баз данных. Полный курс / Г. Гарсиа-Молина, Дж. Ульман, Дж. Уидом. — М. : Вильямс, 2003. — 1088 с.
4. Демобаза данных «Авиаперевозки» — Postgres Professional : [сайт]. — URL: https://postgrespro.ru/education/demodb (дата обращения: 18.08.2026).