ErdDocs

Примеры / Школьная система

Школьная система

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

Ниже ErdDocs рисует эту схему по её SQL, со всеми таблицами и связями. На вкладке «Словарь данных» – справочник для печати, целиком и бесплатно на этой странице. SQL ещё ниже.

Ваша схема

Наведите на таблицу, чтобы подсветить её связи, кликните, чтобы закрепить.

Зачем нужна каждая таблица

  • students / teachersРазные таблицы. Общее у учеников и учителей только имя, а данные и жизненный цикл разные.
  • coursesПредмет в учебном плане. CS-101 записан один раз, сколько бы раз его ни вели.
  • sectionsУчебная группа: предмет, четверть и учитель. Ученик записывается именно в группу.
  • enrollmentsПромежуточная таблица между учеником и группой. Кроме ссылок, в ней хранится оценка.
  • attendanceПривязана к записи в группу составным внешним ключом. Нельзя отметить ученика на уроке группы, в которую он не записан.

Проектные решения, которые стоит взять

  • Разделение предмета и группы – основа этой схемы. Если записывать учеников прямо на предметы, всё ломается, как только предмет ведут дважды. Две четверти и два учителя, а колонка оценки одна, и уже непонятно, к чему она относится.
  • Посещаемость ссылается на запись в группу составным внешним ключом (student_id, section_id). Поэтому база сама отклонит отметку для ученика, которого нет в группе.
  • Оценка допускает NULL вместо значения по умолчанию. Пустая оценка значит, что четверть ещё идёт, а не что ученик получил двойку.

SQL

Диалект PostgreSQL. Этот SQL уже вставлен в инструмент выше, его можно перенести в свою базу или в миграцию и свободно копировать.

CREATE TABLE students (
    id          bigserial    PRIMARY KEY,
    student_no  varchar(20)  NOT NULL UNIQUE,
    full_name   varchar(120) NOT NULL,
    enrolled_on date         NOT NULL
);

CREATE TABLE teachers (
    id         bigserial    PRIMARY KEY,
    full_name  varchar(120) NOT NULL,
    department varchar(80)  NOT NULL
);

CREATE TABLE courses (
    id      bigserial    PRIMARY KEY,
    code    varchar(12)  NOT NULL UNIQUE,
    title   varchar(160) NOT NULL,
    credits int          NOT NULL
);
COMMENT ON COLUMN courses.code IS 'Код предмета, например CS-101';

CREATE TABLE sections (
    id          bigserial   PRIMARY KEY,
    course_id   bigint      NOT NULL REFERENCES courses(id),
    teacher_id  bigint      NOT NULL REFERENCES teachers(id),
    term        varchar(20) NOT NULL,
    room        varchar(20)
);
COMMENT ON TABLE sections IS 'Предмет, который учитель ведёт в четверти; ученики записываются в группы, а не на предметы';

CREATE TABLE enrollments (
    student_id  bigint     NOT NULL REFERENCES students(id),
    section_id  bigint     NOT NULL REFERENCES sections(id),
    grade       varchar(2),
    PRIMARY KEY (student_id, section_id)
);
COMMENT ON COLUMN enrollments.grade IS 'NULL до конца четверти';

CREATE TABLE attendance (
    student_id  bigint  NOT NULL,
    section_id  bigint  NOT NULL,
    class_date  date    NOT NULL,
    present     boolean NOT NULL,
    PRIMARY KEY (student_id, section_id, class_date),
    FOREIGN KEY (student_id, section_id) REFERENCES enrollments(student_id, section_id)
);

Частые вопросы

Куда положить родителей?

В таблицу guardians и промежуточную student_guardians. Это связь «многие ко многим»: у братьев и сестёр общие родители, а у ученика их может быть несколько.

Почему не хранить teacher_id в courses?

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

Другие примеры