SQL Interview Prep · Урок

ER-моделирование и кардинальность связей

Преобразование требований в сущности, связи и таблицы-связки

«ER-моделирование и кардинальность связей» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Почему ER-моделирование встречается на собеседованиях

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

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

Суть навыка — преобразовать существительные и глаголы из описания задачи в таблицы и внешние ключи.

Сущности, атрибуты и связи

Любую ER-модель составляют три строительных блока:

Сущность: объект, о котором Вы храните данные (клиент, заказ, товар). Обычно становится таблицей.
Атрибут: свойство сущности (имя, цена, дата создания). Обычно становится столбцом.
Связь: способ соединения сущностей (клиент размещает заказ). Реализуется с помощью внешних ключей или связующих таблиц.

Подсказка для анализа задачи: существительные становятся сущностями и атрибутами, а глаголы — связями.

Кардинальность: основная концепция

Кардинальность описывает, сколько экземпляров одной сущности связано с другой. Выделяют три основных типа:

Один к одному (1:1): одна строка с этой стороны соответствует не более чем одной строке с другой стороны.
Один ко многим (1:N): одна строка с этой стороны соответствует многим строкам с другой стороны (самый распространённый вариант).
Многие ко многим (M:N): строки с обеих сторон могут соответствовать многим строкам на другой стороне.

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

Реализация связи один ко многим

Связь один ко многим реализуется размещением внешнего ключа на стороне «много». У клиента много заказов, поэтому каждая строка заказа содержит customer_id.

На собеседовании всегда явно обозначайте направление: «От одного клиента к многим заказам, поэтому FK находится в таблице orders».

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

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

Реализация связи многие ко многим

Реляционная база данных не может хранить связь M:N напрямую. Ответ, который ожидают услышать интервьюеры, — связующая таблица (её также называют таблицей-мостом, таблицей связей или ассоциативной таблицей).

Студенты записываются на многие курсы, а на каждом курсе учатся многие студенты. Создайте таблицу enrollments, ключ которой объединяет оба внешних ключа. Так связь M:N преобразуется в две связи 1:N.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(100)
);

CREATE TABLE courses (
  course_id  INT PRIMARY KEY,
  title      VARCHAR(100)
);

CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  enrolled_at DATE,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

Связующая таблица может хранить данные

Распространённый дополнительный вопрос: «Где хранить оценку, которую студент получил за курс?»

Оценка относится к связи, а не только к студенту или курсу. Поэтому её следует поместить в связующую таблицу. Именно это и хотят проверить интервьюеры: атрибуты связи M:N хранятся в таблице-мосте.

Примеры: дата записи на курс, оценка, количество в строке заказа, роль участника в проекте.

ALTER TABLE enrollments
  ADD COLUMN grade CHAR(2);
-- grade describes THIS student in THIS course,
-- so it belongs on the junction table

Реализация связи один к одному

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

Пример: user и user_profile с дополнительными необязательными данными. Если сделать user_id первичным ключом таблицы профилей, это обеспечит не более одного профиля на пользователя.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email   VARCHAR(255)
);

CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,           -- 1:1 enforced here
  bio     TEXT,
  avatar_url VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Необязательность и участие

У кардинальности есть второе измерение, которому интервьюеры уделяют внимание: необязательность (также называемая участием).

Обязательное участие: у каждого заказа должен быть клиент, поэтому customer_id имеет значение NOT NULL.
Необязательное участие: у пользователя может быть профиль, а может и не быть, поэтому связь может отсутствовать.

Обязательное участие выражается ограничением NOT NULL для внешнего ключа. Упоминание возможности значения NULL показывает, что Вы думаете о реальных ограничениях, а не только о структуре модели.

Самоссылочные связи

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

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

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name        VARCHAR(100),
  manager_id  INT NULL,
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
-- manager_id NULL = top of the hierarchy (e.g. CEO)

Небольшой разбор моделирования

Потренируйтесь преобразовывать глаголы в связи. Условие: «Клиенты размещают заказы; каждый заказ содержит много товаров; товары принадлежат поставщикам».

Клиент 1:N Заказ (внешний ключ customer_id в таблице orders).
Заказ M:N Товар -> связующая таблица order_items (с количеством).
Поставщик 1:N Товар (внешний ключ supplier_id в таблице products).

Назовите каждую кардинальность и укажите, где находится ключ. Такое объяснение — залог успешного ответа на собеседовании.

Уточняющие вопросы, которые стоит задать

Интервьюеры высоко оценивают кандидатов, которые задают вопросы до начала проектирования. Хорошие уточняющие вопросы:

«Может ли товар принадлежать более чем одному поставщику?» (определяет выбор между 1:N и M:N).
«В каждом ли заказе обязательно должен быть хотя бы один товар?» (участие).
«Нужна ли нам история или только текущее состояние?» (определяет необходимость дополнительных таблиц).

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

Быстрая проверка

Вы моделируете студентов и курсы: каждый студент может посещать много курсов, а на каждом курсе может учиться много студентов.

Повторение: ER-моделирование и кардинальность

Теперь Вы можете самостоятельно решать открытые задачи по проектированию схем:

Превращайте существительные в сущности и атрибуты, глаголы — в связи.
1:N: внешний ключ находится на стороне «многие».
M:N: связующая таблица содержит оба внешних ключа и любые атрибуты связи.
1:1: общий уникальный ключ в зависимой таблице.
Используйте NOT NULL, чтобы выразить обязательное участие, а для иерархий — внешние ключи, ссылающиеся на ту же таблицу.
Задавайте уточняющие вопросы, прежде чем фиксировать кардинальность.

Часто задаваемые вопросы

Урок «ER-моделирование и кардинальность связей» бесплатный?

Да — полный текст урока «ER-моделирование и кардинальность связей» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «ER-моделирование и кардинальность связей»?

Преобразование требований в сущности, связи и таблицы-связки Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «ER-моделирование и кардинальность связей»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Нормализация до третьей нормальной формы
ER-моделирование и кардинальность связей
Звёздная схема и проектирование хранилищ данных
Полный набор задач для пробного собеседования

← Назад к SQL Interview Prep