Введение в БД, часть 2: связи, JOIN и агрегатные функции

1. О связях между таблицами

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

Например, в базе данных авиакомпании связь между таблицами «Рейсы» и «Аэропорты» может быть установлена с помощью внешнего ключа, который связывает код аэропорта в таблице рейсов с записью об этом аэропорте в таблице аэропортов. Это позволяет получить доступ к информации о конкретных аэропортах из данных о рейсах и обеспечивает целостность данных, предотвращая, например, создание рейса с несуществующим или неправильным кодом аэропорта. Правильно настроенные связи делают базу данных эффективной, удобной для использования и поддержания, а также обеспечивают согласованность и точность данных при их изменении или обновлении.

1.1. Виды ключей

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

1.2. Обеспечение целостности данных

Обеспечение целостности данных — критически важное направление поддержания качества данных на высоком уровне.

Виды целостности данных:

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

1.3. Типы связей

Один к одному (One-to-One). Каждая запись в одной таблице связана с одной и только одной записью в другой таблице. Например, таблица «Сотрудники» может иметь отдельную связанную таблицу «Контактная информация», где каждый сотрудник имеет только одну запись с контактной информацией.

Один ко многим (One-to-Many). Одна запись в одной таблице связана с несколькими записями в другой. Пример: у одного автора может быть несколько книг в таблицах «Авторы» и «Книги» соответственно.

Многие к одному (Many-to-One). Несколько записей в одной таблице связаны с одной записью в другой. Например, несколько студентов связаны с одним курсом в таблицах «Студенты» и «Курсы».

Многие ко многим (Many-to-Many). Множество записей в одной таблице связано с множеством записей в другой. Этот тип связи обычно реализуется через дополнительную таблицу-связку (join table), которая связывает две другие таблицы. Например, студенты могут иметь множество курсов, и каждый курс — множество студентов; для этого создаётся таблица, соединяющая студентов с курсами.

2. Нормализация

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

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

Что такое «несогласованные зависимости»? Пользователю интуитивно понятно искать в таблице «Клиенты» адрес конкретного клиента, но, возможно, не имеет смысла искать там зарплату сотрудника, который обращается к данному клиенту: зарплата связана с сотрудником (зависит от него), поэтому эти сведения следует хранить в таблице Employees. Несогласованные зависимости могут затруднять доступ к данным: путь к данным может отсутствовать или быть неправильным.

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

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

2.1. Первая нормальная форма

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

Не используйте несколько полей одной таблицы для хранения похожих данных. Например, для слежения за товаром, который закупается у двух поставщиков, можно создать запись с полями «код первого поставщика» и «код второго поставщика». А что при добавлении третьего поставщика? Добавление поля потребует изменений в программе и таблице и не обеспечит плавного размещения динамического числа поставщиков. Вместо этого поместите сведения о поставщиках в отдельную таблицу Vendors и свяжите товары с поставщиками кодами.

2.2. Вторая нормальная форма

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

Записи не должны зависеть ни от чего, кроме первичного ключа таблицы (составного, если необходимо). Пример: адрес клиента в системе бухгалтерского учёта нужен не только таблице Customers, но и таблицам Orders, Shipping, Invoices, Accounts Receivable и Collections. Вместо хранения адреса отдельным элементом в каждой из таблиц храните его в одном месте: в таблице Customers или в отдельной таблице Addresses.

2.3. Третья нормальная форма

• Исключите поля, которые не зависят от ключа.

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

3. JOIN

Оператор JOIN в языке SQL используется для объединения строк из двух или более таблиц на основе определённого условия. JOIN позволяет получать данные из нескольких таблиц и объединять их в один результат запроса. Существуют различные типы JOIN:

1. INNER JOIN — возвращает строки, которые имеют совпадающие значения в обеих таблицах по указанному условию:

SELECT * FROM Table1
INNER JOIN Table2 ON Table1.column = Table2.column;

2. LEFT JOIN (LEFT OUTER JOIN) — возвращает все строки из левой таблицы (первой указанной), а также совпадающие строки из правой. Если в правой таблице нет соответствующих строк, будут возвращены NULL-значения:

SELECT * FROM Table1
LEFT JOIN Table2 ON Table1.column = Table2.column;

3. RIGHT JOIN (RIGHT OUTER JOIN) — возвращает все строки из правой таблицы и совпадающие строки из левой; при отсутствии соответствий слева — NULL:

SELECT * FROM Table1
RIGHT JOIN Table2 ON Table1.column = Table2.column;

4. FULL JOIN (FULL OUTER JOIN) — возвращает все строки из обеих таблиц; для строк без соответствия недостающие столбцы заполняются NULL:

SELECT * FROM Table1
FULL JOIN Table2 ON Table1.column = Table2.column;

5. CROSS JOIN — возвращает декартово произведение строк обеих таблиц: каждая строка первой таблицы комбинируется со всеми строками второй:

SELECT * FROM Table1
CROSS JOIN Table2;

3.1. Тренажёр: пять видов JOIN вживую

Те же «цифры и буквы»: Numbers(id, num) и Letters(id, letter). Ключ id = 1 есть в обеих таблицах, id = 2 — только в левой, id = 3 — совпадает, id = 4 — только в правой. Выбирайте тип соединения: совпадающие пары подсвечены цветом, недостающие значения заполняются NULL. Число строк результата меняется — следите за ним.

4. Агрегатные функции

Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT, не учитывают значения NULL. Они часто используются с выражением GROUP BY инструкции SELECT, а в качестве выражений допустимы только в списке выбора инструкции SELECT (вложенный или внешний запрос) и в предложении HAVING.

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

• Avg — среднее значение числовых значений в столбце или наборе;
• Count — количество строк или значений; подсчёт записей таблицы или записей, удовлетворяющих условию;
• First и Last — первое и последнее значение в группе; полезны для упорядоченных данных;
• Min, Max — минимальное и максимальное значения;
• StDev, StDevP — стандартное отклонение для выборки (StDev) и для генеральной совокупности (StDevP): оценка разброса данных относительно среднего;
• Sum — сумма числовых значений;
• Var и VarP — дисперсия для выборки и для генеральной совокупности: насколько значения отклоняются от среднего.

Эти агрегатные функции широко используются для анализа данных, позволяя проводить статистические вычисления в запросах SQL (в Microsoft Access — также при создании объектов Recordset). Потренировать агрегаты можно в SQL-песочнице занятия 2 — пресет «средняя цена по вылету».

5. Наполняемость базы данных

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

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

5.1. Сервисы для генерации «реалистичных» данных

Mockaroo — один из лучших онлайн-инструментов для генерации большого объёма тестовых данных с заданными характеристиками; генерирует более 1000 строк в форматах JSON, CSV, Excel и SQL. Особенности:

• позволяет создавать собственные макеты API;
• предоставляет различные типы данных: страна, штат или область, город, улица, номер телефона и др.;
• позволяет контролировать URL-адреса, ответы и условия ошибок;
• предоставляет библиотеки имитации для любого языка и платформы;
• помогает проводить тестирование на реалистичных данных.

GenerateData — инструмент генерации данных с открытым исходным кодом; генерирует большие объёмы пользовательских данных в различных форматах для тестирования ПО. Особенности: собственные типы данных для генерации случайных значений; плагины стран с названиями городов, регионов и почтовыми индексами.

MOSTLY AI — генератор синтетических тестовых данных на основе искусственного интеллекта. Каждый сгенерированный набор сопровождается отчётом о проверке качества; после загрузки примера данных генератор создаёт статистически и структурно идентичные синтетические версии оригинала — реалистичные и конфиденциальные. Недостаток: для обучения алгоритма необходим набор данных. Особенности:

• создаёт базы данных с сохранением ссылочной целостности;
• полностью соответствует общему регламенту по защите данных (GDPR);
• легко выполнить дискретизацию данных;
• бесплатная генерация до 100 тыс. строк в день;
• подключение к AWS, GCP и Azure;
• поддержка DB2, MySQL, Oracle и PostgreSQL.

6. Практика

Используя сервис Mockaroo, сгенерируйте тестовые данные для базы «Аэропорт» из занятия 2.

После заполнения таблиц выполните следующие запросы.

SELECT Flights.DepartureAirport, DestinationAirports.AirportName
FROM Flights
INNER JOIN DestinationAirports ON Flights.DestinationAirport = DestinationAirports.AirportCode
WHERE Flights.DepartureAirport = DestinationAirports.AirportName;

SELECT COUNT(*) AS UniqueAirportCount
FROM (
  SELECT DISTINCT AirportName
  FROM DestinationAirports
);

SELECT * FROM Flights
WHERE DepartureAirport LIKE 'М%' AND TicketPrice > 5000;

7. Источники данных

1. Natural Earth Data — бесплатные географические данные в форме векторных и растровых наборов, охватывающих культурные, физические и географические аспекты всего мира; доступны для использования, модификации и распространения.
2. USGS Earth Explorer — доступ к снимкам Landsat, MODIS, цифровым картам и другим геопространственным данным Геологической службы США; полезный ресурс для исследований земной поверхности.
3. Esri Open Data Hub — платформа Esri с открытыми данными от источников по всему миру: карты, наборы данных о населении, экологии и многое другое.
4. NASA SEDAC — данные о социально-экономических условиях и природной среде: население, климат, здоровье, экономика — собранные NASA и другими организациями.
5. Sentinel Satellite Data — программа космических миссий Европейского космического агентства (ESA): бесплатные данные спутников для мониторинга окружающей среды, изменений земной поверхности и анализа климата.

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

• Постройте иерархию: суперключ → потенциальный ключ → первичный ключ. Где здесь внешний ключ?
  Суперключ — любой набор атрибутов, уникально идентифицирующий кортеж (всё множество атрибутов — тоже суперключ). Потенциальный ключ — суперключ минимального размера. Первичный ключ — выбранный из потенциальных. Внешний ключ — атрибут(ы), соответствующий потенциальному ключу другого (или того же) отношения; он реализует связь таблиц.
• Назовите три вида целостности данных и что каждый гарантирует.
  Доменная (столбец) — значения из допустимого набора, контроль пустых значений; объектная (таблица) — каждая строка имеет уникальный первичный ключ; ссылочная — внешние ключи указывают только на существующие записи связанных таблиц.
• Как реализуется связь «многие-ко-многим» в реляционной базе?
  Через дополнительную таблицу-связку (join table) с внешними ключами на обе стороны: например, таблица «Студент—Курс» с парами (id студента, id курса). Прямой связью M:N реляционные таблицы не соединяются.
• Сформулируйте правила 1НФ, 2НФ и 3НФ одним предложением каждое.
  1НФ: устранить повторяющиеся группы, атомарные значения, первичный ключ у каждого набора данных. 2НФ: наборы значений, относящиеся к нескольким записям, — в отдельные таблицы, связанные внешним ключом; записи зависят только от полного первичного ключа. 3НФ: исключить поля, не зависящие от ключа.
• Когда допустимо сознательно нарушить третью нормальную форму?
  Когда строгая нормализация порождает множество маленьких таблиц, снижающих производительность СУБД или исчерпывающих ресурсы. До 3НФ целесообразно нормализовать часто изменяемые данные; для остальных допустимы зависимые поля — при условии, что приложение контролирует их согласованное обновление.
• Сравните пять видов JOIN по составу результата.
  INNER — только совпавшие пары; LEFT — все строки левой таблицы + совпадения (справа NULL при отсутствии); RIGHT — зеркально, все строки правой; FULL — все строки обеих таблиц с NULL там, где пары нет; CROSS — декартово произведение: каждая строка левой с каждой строкой правой, условие ON не используется.
• Как агрегатные функции обращаются с NULL и где их можно использовать?
  Все агрегаты, кроме COUNT(*), игнорируют NULL-значения. Использовать их можно в списке выбора SELECT и в предложении HAVING; в WHERE агрегаты недопустимы, так как WHERE фильтрует строки до группировки.
• Какие способы наполнить базу тестовыми данными существуют и чем интересен MOSTLY AI?
  Случайная генерация скриптами, специализированные генераторы (Mockaroo, GenerateData), импорт реальных данных, генерация по шаблонам. MOSTLY AI обучается на примере данных и создаёт статистически идентичную синтетическую копию с сохранением ссылочной целостности и соответствием GDPR — реалистично и конфиденциально.

Источники

1. Описание основ нормализации базы данных — Microsoft Learn : [сайт]. — URL: https://learn.microsoft.com/ru-ru/office/troubleshoot/access/database-normalization-description (дата обращения: 18.08.2026).
2. PostgreSQL 15 Documentation. Joins Between Tables : [сайт]. — URL: https://www.postgresql.org/docs/15/tutorial-join.html (дата обращения: 18.08.2026).
3. Mockaroo — Random Data Generator : [сайт]. — URL: https://www.mockaroo.com/ (дата обращения: 18.08.2026).
4. Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт. — 8-е изд. — М. : Вильямс, 2005. — 1328 с.