Login

Thiết kế cơ sở dữ liệu quản lý lớp học bằng SQL: bảng, khóa ngoại và truy vấn JOIN

  • author-image

    Nguyễn Đình Cường

  • blog-tag
  • blog-comment 0 comment
  • created-date 09 Nov, 2026
blog-thumbnail

Biết viết SELECT chưa đủ, bạn còn cần biết chia dữ liệu vào các bảng hợp lý và nối chúng lại. Bài này dẫn bạn thiết kế một cơ sở dữ liệu quản lý lớp học nhỏ gồm sinh viên, môn học và điểm số, từ lúc tạo bảng đến các truy vấn JOIN và thống kê.

Bắt đầu từ bài toán

Ta cần lưu: danh sách sinh viên, danh sách môn học, và điểm của từng sinh viên ở từng môn. Một sinh viên học nhiều môn, một môn có nhiều sinh viên, đó là quan hệ nhiều-nhiều. Cách giải chuẩn là thêm một bảng trung gian grades mỗi dòng ghi điểm của một sinh viên ở một môn. Nếu nhồi tất cả vào một bảng, bạn sẽ lặp lại tên sinh viên và tên môn khắp nơi, dễ sai lệch khi cập nhật. Việc tách ra để mỗi thông tin chỉ nằm ở một chỗ gọi là chuẩn hóa.

Bước 1: Tạo các bảng

Các lệnh dưới đây dùng cú pháp MySQL. Với PostgreSQL, bạn thay AUTO_INCREMENT bằng GENERATED ... AS IDENTITY hoặc SERIAL.

CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE school;

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE,
  birth_year SMALLINT
) ENGINE=InnoDB;

CREATE TABLE subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  credits TINYINT NOT NULL DEFAULT 3
) ENGINE=InnoDB;

CREATE TABLE grades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  subject_id INT NOT NULL,
  score DECIMAL(4,2) NOT NULL,
  CONSTRAINT fk_grades_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_grades_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  CONSTRAINT uq_student_subject UNIQUE (student_id, subject_id),
  CONSTRAINT chk_score CHECK (score >= 0 AND score <= 10)
) ENGINE=InnoDB;

Một vài điểm quan trọng: khóa chính định danh duy nhất từng dòng; khóa ngoại đảm bảo điểm chỉ gắn với sinh viên và môn có thật; ràng buộc UNIQUE (student_id, subject_id) ngăn nhập hai điểm cho cùng cặp; CHECK chặn điểm sai miền giá trị (MySQL từ bản 8.0.16 mới thực thi ràng buộc này). Quy tắc ON DELETE CASCADE tự xóa điểm khi xóa sinh viên, hãy cân nhắc kỹ trước khi dùng cho dữ liệu quan trọng.

Bước 2: Thêm dữ liệu mẫu

INSERT INTO students (full_name, email, birth_year) VALUES
  ('Nguyễn An', 'an@example.com', 2004),
  ('Trần Bình', 'binh@example.com', 2003),
  ('Lê Cường', 'cuong@example.com', 2004);

INSERT INTO subjects (name, credits) VALUES
  ('Lập trình căn bản', 3),
  ('Cơ sở dữ liệu', 3),
  ('Tiếng Anh chuyên ngành', 2);

INSERT INTO grades (student_id, subject_id, score) VALUES
  (1, 1, 8.5), (1, 2, 9.0),
  (2, 1, 7.0), (2, 2, 6.5), (2, 3, 8.0),
  (3, 1, 9.5);

Bước 3: Truy vấn với JOIN

Để xem điểm kèm tên sinh viên và tên môn, nối ba bảng:

SELECT s.full_name, sub.name AS subject, g.score
FROM grades g
JOIN students s ON s.id = g.student_id
JOIN subjects sub ON sub.id = g.subject_id
ORDER BY s.full_name, sub.name;

JOIN (hay INNER JOIN) chỉ giữ các dòng khớp ở cả hai phía. Nếu muốn liệt kê cả sinh viên chưa có điểm nào, dùng LEFT JOIN:

SELECT s.full_name, COUNT(g.id) AS so_mon_co_diem
FROM students s
LEFT JOIN grades g ON g.student_id = s.id
GROUP BY s.id, s.full_name;

Bước 4: Thống kê với GROUP BY

Điểm trung bình theo sinh viên, chỉ lấy người có trung bình từ 8 trở lên:

SELECT s.full_name, ROUND(AVG(g.score), 2) AS diem_tb
FROM students s
JOIN grades g ON g.student_id = s.id
GROUP BY s.id, s.full_name
HAVING AVG(g.score) >= 8
ORDER BY diem_tb DESC;

Hãy ghi nhớ khác biệt: WHERE lọc từng dòng trước khi gom nhóm, còn HAVING lọc các nhóm sau khi đã tính hàm tổng hợp. Và điểm trung bình theo môn học:

SELECT sub.name, COUNT(*) AS so_sv, MAX(g.score) AS cao_nhat, MIN(g.score) AS thap_nhat
FROM subjects sub
JOIN grades g ON g.subject_id = sub.id
GROUP BY sub.id, sub.name;

Bước 5: Chỉ mục để truy vấn nhanh

Khóa ngoại thường được MySQL tự đánh chỉ mục. Với các cột hay dùng trong WHERE hoặc JOIN khác, hãy tạo chỉ mục. Dùng EXPLAIN trước câu truy vấn để xem MySQL thực thi thế nào:

CREATE INDEX idx_students_name ON students(full_name);
EXPLAIN SELECT * FROM students WHERE full_name = 'Nguyễn An';

Những lỗi thiết kế hay gặp

  • Lưu nhiều giá trị trong một ô (như danh sách môn cách nhau dấu phẩy), phá vỡ khả năng truy vấn.
  • Không dùng khóa ngoại nên dữ liệu mồ côi tích tụ.
  • Dùng kiểu dữ liệu quá rộng hoặc sai kiểu, ví dụ lưu ngày dưới dạng chuỗi tự do.
  • Quên bảng mã utf8mb4 nên tiếng Việt lỗi.
  • Dùng SELECT * thường xuyên trong ứng dụng thật.

Bài tập tự luyện

  1. Viết truy vấn liệt kê các môn chưa có sinh viên nào có điểm.
  2. Tìm sinh viên có điểm cao nhất ở mỗi môn (gợi ý: dùng truy vấn con hoặc hàm cửa sổ nếu hệ quản trị hỗ trợ).
  3. Thêm cột xếp loại bằng biểu thức CASE WHEN dựa trên điểm trung bình.
  4. Thêm bảng teachers và khóa ngoại từ subjects sang giảng viên.

Kết luận

Ba bảng, vài khóa ngoại và các truy vấn JOIN, GROUP BY đã đủ mô hình hóa nhiều bài toán thực tế. Hãy tự thử thêm bảng giảng viên, học kỳ và xếp loại học lực để mở rộng.

Nếu muốn học SQL và thiết kế cơ sở dữ liệu có hệ thống, bạn có thể xem các khóa học tại codebem.com.

author_photo
Nguyễn Đình Cường

0 comment