Создание таблицы enrollments для связи студентов и курсов

Еще немного позанудствуем. Давайте так же детально пройдемся по шагам создания структуры MANY-TO-MANY. Готовы?

Связь MANY-TO-MANY между студентами и курсами не может быть представлена напрямую в одной таблице. Один студент может быть зачислен на несколько курсов, а один курс может быть посещаем сразу несколькими студентами.

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

Как выглядит таблица enrollments?

Вы уже знаете, что таблица enrollments будет центральным узлом связи между таблицами students и courses. Вот её структура:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Уникальный ID записи
    student_id INT REFERENCES students(student_id), -- Внешний ключ на таблицу students
    course_id INT REFERENCES courses(course_id),    -- Внешний ключ на таблицу courses
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Когда студент был зачислен на курс
);

Разберём каждую строку:

• enrollment_id: Это уникальный идентификатор каждой записи. Ведь каждый зачисленный на курс студент должен быть уникально идентифицирован.
• student_id: Указывает, какой студент зачислен. Это внешний ключ, который ссылается на таблицу students (столбец student_id).
• course_id: Указывает, на какой курс студент зачислен. Этот столбец связан с таблицей courses (столбец course_id).
• enrollment_date: Полезное дополнение, которое показывает дату зачисления. Мы используем DEFAULT CURRENT_DATE, чтобы автоматически указывать текущую дату при создании записи.

Создание таблиц students и courses

Прежде чем двигаться дальше, убедимся, что у нас уже есть таблицы students и courses:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- Уникальный идентификатор студента
    name TEXT NOT NULL,                 -- Имя студента
    email TEXT NOT NULL UNIQUE          -- Email студента, чтобы не было дубликатов
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- Уникальный идентификатор курса
    title TEXT NOT NULL,                -- Название курса
    description TEXT,                   -- Описание курса
    start_date DATE                     -- Дата начала курса
);

Заметьте, что здесь мы добавили полезные детали, например, уникальные e-mail для студентов и описание курса в таблице courses.

Связываем всё вместе

Теперь, когда наши таблицы готовы, создадим таблицу enrollments:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Уникальный ID зачисления
    student_id INT NOT NULL REFERENCES students(student_id), -- Внешний ключ
    course_id INT NOT NULL REFERENCES courses(course_id),    -- Внешний ключ
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Дата зачисления
);

Вставка данных в таблицы

Итак, таблицы готовы, но они скучают без данных. Добавим некоторых студентов, курсы и их зачисления:

Вставка студентов:

INSERT INTO students (name, email)
VALUES
    ('Alex Lin', 'alex.lin@example.com'),
    ('Maria Chi', 'maria.chi@example.com'),
    ('Otto Song', 'otto.song@example.com');

Вставка курсов:

INSERT INTO courses (title, description, start_date)
VALUES
    ('Основы программирования', 'Курс для начинающих программистов.', '2023-11-01'),
    ('Базы данных', 'Изучаем SQL и реляционные базы данных.', '2023-11-15'),
    ('Веб-разработка', 'Создание сайтов и веб-приложений.', '2023-12-01');

Вставка записей о зачислениях:

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin на "Основы программирования"
    (1, 2), -- Alex Lin на "Базы данных"
    (2, 2), -- Maria Chi на "Базы данных"
    (3, 3); -- Otto Song на "Веб-разработка"

Здесь student_id и course_id совпадают с идентификаторами в соответствующих таблицах.

Проверяем связи с помощью запросов

Получение всех зачислений:

SELECT e.enrollment_id, s.name AS student_name, c.title AS course_title, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

Результат:

Задача для самостоятельной практики

Попробуйте добавить ещё несколько студентов и курсов, а затем зачислить их в таблицу enrollments. Например, добавьте курс "Машинное обучение" и зачислите туда 1-2 студентов. Используйте приведённый выше запрос для проверки результата.

Возможные типичные ошибки

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

1. Отсутствие записи в родительской таблице: Если вы попытаетесь добавить запись в enrollments со student_id или course_id, которых нет в таблицах students или courses, получите ошибку. Внешний ключ строго следит за этим.

2. Нарушение целостности данных при удалении: Если вы удалите студента или курс, которые уже используются в таблице enrollments, без настроенного ON DELETE CASCADE, это приведёт к ошибке.

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

Теперь у вас есть рабочая модель для представления связи MANY-TO-MANY между студентами и курсами в PostgreSQL. Эта структура очень часто используется в реальных приложениях, таких как системы управления обучением, CRM и многие другие. Вперёд к следующей лекции!

2

Задача

SQL SELF, 20 уровень, 1 лекция

Недоступна

Создание таблицы `enrollments`

Создание таблицы `enrollments`

Открыть

2

Задача

SQL SELF, 20 уровень, 1 лекция

Недоступна

Вывод объединённых данных о зачислениях

Вывод объединённых данных о зачислениях

Открыть