ErdDocs

ER 圖範例 / 學校管理系統

學校管理系統

學校的資料庫透過選課（enrollments）把學生和科目連起來。選課是同時存放成績的中介資料表。教師在每個學期負責某科目的開課班級。出缺勤則掛在選課上，每位學生每個上課日一列。

下方由 ErdDocs 依 SQL 畫出這份結構，包含所有資料表與關聯。「資料字典」分頁有可列印的版本，在本頁免費完整顯示。SQL 在頁面更下方。

你的結構

把滑鼠移到資料表上會標示它的關聯，點一下即可固定。

每個資料表的用途

  • students / teachers分開的資料表。只是名字相同，生命週期與資料都不一樣。
  • courses課程目錄上的科目。CS-101 不管開了幾次都只有一筆。
  • sections開課班級，也就是科目、學期、教師的組合，是學生實際參加的單位。
  • enrollments學生與開課班級的中介資料表，帶的值是成績。
  • attendance用複合外鍵掛在選課上，所以不可能在沒選的班級被記為出席。

值得借鏡的設計決策

  • 把科目和開課班級分開，是這份結構的基礎。如果讓學生直接選「科目」，同一科目開第二次就會出問題。會出現兩個學期、兩位教師，成績欄位的意義也會互相衝突。
  • 出缺勤以複合外鍵 (student_id, section_id) 參照選課。這樣資料庫本身就會拒絕沒有選課的學生的出缺勤紀錄。
  • 成績不設預設值，而是允許 NULL。沒有成績本身就是資訊（學期進行中），不是零分。

SQL

PostgreSQL 方言。可以貼進上方工具（已經放好了）、你自己的資料庫或 migration，也可以自由複製。

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？

因為同一科目在不同學期由不同的教師授課。教師屬於開課班級，而不是課程目錄上的科目。

其他範例