ErdDocs

Ví dụ / Hệ thống quản lý trường học

Hệ thống quản lý trường học

Cơ sở dữ liệu trường học nối học sinh với môn học qua bảng ghi danh. Đây là bảng trung gian, và nó lưu cả điểm. Giáo viên dạy các lớp học phần của một môn trong một học kỳ. Điểm danh gắn với ghi danh, mỗi học sinh có một dòng cho mỗi buổi học.

Bên dưới, ErdDocs vẽ lược đồ này từ SQL của nó, với mọi bảng và quan hệ. Tab «Từ điển dữ liệu» có tài liệu tham chiếu để in, đầy đủ và miễn phí ngay trên trang này. Đoạn SQL nằm ở cuối trang.

Lược đồ của bạn

Rê chuột lên một bảng để làm nổi các quan hệ. Bấm chuột để giữ phần làm nổi.

Mỗi bảng làm gì

  • students / teachersHai bảng riêng. Học sinh và giáo viên chỉ chung nhau cái tên, còn vòng đời và dữ liệu thì khác.
  • coursesMôn học trong chương trình. CS-101 chỉ có một dòng, dù được mở bao nhiêu lần.
  • sectionsLớp học phần: một môn, một học kỳ, một giáo viên. Đây là thứ học sinh thật sự theo học.
  • enrollmentsBảng trung gian giữa học sinh và lớp học phần. Ngoài hai khóa ngoại, bảng còn lưu điểm.
  • attendanceGắn với ghi danh bằng khóa ngoại kép. Không thể điểm danh học sinh ở lớp mà em đó không ghi danh.

Những quyết định thiết kế đáng học

  • Tách môn học và lớp học phần là nền tảng của lược đồ. Nếu ghi danh học sinh thẳng vào môn học, mọi thứ sẽ vỡ ngay khi một môn được mở hai lần. Khi đó có hai học kỳ và hai giáo viên, nhưng chỉ một cột điểm, và không còn rõ điểm đó thuộc về lớp nào.
  • Điểm danh tham chiếu ghi danh bằng khóa ngoại kép (student_id, section_id). Nhờ vậy, chính cơ sở dữ liệu sẽ từ chối điểm danh cho học sinh chưa ghi danh.
  • Điểm cho phép NULL thay vì có giá trị mặc định. Chưa có điểm nghĩa là học kỳ đang diễn ra, chứ không phải học sinh bị điểm không.

SQL

Phương ngữ PostgreSQL. Đoạn SQL này đã có sẵn trong công cụ phía trên. Bạn có thể thoải mái sao chép và đưa nó vào cơ sở dữ liệu của mình hoặc vào một 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 'Mã môn học, ví dụ 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 'Môn học do một giáo viên mở trong một học kỳ; học sinh ghi danh vào lớp học phần, không phải vào môn học';

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 cho tới khi hết học kỳ';

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)
);

Câu hỏi thường gặp

Phụ huynh để ở đâu?

Trong bảng guardians và bảng trung gian student_guardians. Đây là quan hệ nhiều-nhiều, vì anh chị em có chung phụ huynh, và một học sinh có thể có nhiều người giám hộ.

Sao không để teacher_id trong courses?

Vì cùng một môn ở các học kỳ khác nhau do các giáo viên khác nhau dạy. Giáo viên thuộc về lớp học phần, không thuộc về môn trong chương trình.

Ví dụ khác