Домашнее задание - Практическое занятие №3: сущности и связи

1. Назначение и контекст задания

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

2. Формулировка практического задания

Рассмотрите фрагмент ER-модели корпоративного учебного центра:

Требуется выполнить:

1. Провести декомпозицию связи M:N: Опишите, почему реляционные базы данных не позволяют связать таблицы «Слушатели» и «Курсы» напрямую через одиночный внешний ключ. Создайте промежуточную (ассоциативную) таблицу.
2. Спроектировать структуры таблиц: Для всех трех реляционных таблиц (двух базовых сущностей и одной связующей) укажите:
   • Имена полей на латинице в стиле snake_case;
   • Адекватные типы данных SQL (INT, VARCHAR(n), NUMERIC(p,s), TIMESTAMP/DATE, SMALLINT);
   • Первичные ключи (обосновав составной PK для связующей таблицы);
   • Внешние ключи с указанием родительских таблиц;
   • Ограничения целостности (NOT NULL, UNIQUE, CHECK).
3. Сформулировать правила ссылочной целостности: Назначьте политики ON DELETE и ON UPDATE (выбор между RESTRICT и CASCADE) для обоих внешних ключей связующей таблицы и аргументируйте бизнес-риски потери данных.
4. Подготовить DDL-скрипт (SQL): Составьте корректный DDL-скрипт создания схемы данных (команды CREATE TABLE).

3. Методический эталон и образец оформления

Ниже приведен эталонный образец аналогичного преобразования связи M:N «Врачи — Пациенты» через связующую сущность «Медицинский прием»:

-- 1. Таблица базовой сильной сущности «Врачи»
CREATE TABLE vrachi (
    id_vracha       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tab_nomer       VARCHAR(20) NOT NULL UNIQUE,
    familiya        VARCHAR(60) NOT NULL,
    imya            VARCHAR(60) NOT NULL,
    specializaciya  VARCHAR(100) NOT NULL
);

-- 2. Таблица базовой сильной сущности «Пациенты»
CREATE TABLE pacienty (
    id_pacienta     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    snils           VARCHAR(14) NOT NULL UNIQUE,
    familiya        VARCHAR(60) NOT NULL,
    imya            VARCHAR(60) NOT NULL,
    data_rozhdeniya DATE NOT NULL
);

-- 3. Связующая таблица «Приемы» (декомпозиция M:N)
CREATE TABLE priemy_vracha (
    id_priema       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_vracha       INT NOT NULL REFERENCES vrachi(id_vracha)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
    id_pacienta     INT NOT NULL REFERENCES pacienty(id_pacienta)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
    data_vremya     TIMESTAMP NOT NULL,
    kabinet         SMALLINT NOT NULL CHECK (kabinet > 0),
    diagnoz_mkb     VARCHAR(10),
    stoimost        NUMERIC(8,2) NOT NULL CHECK (stoimost >= 0),
    CONSTRAINT uq_vrach_vremya UNIQUE (id_vracha, data_vremya)
);

4. Критерии оценивания (Шкала 4 балла)