Bài 18: Đọc và vẽ lược đồ quan hệ

Câu lạc bộ muốn làm một màn hình tra cứu học viên, lớp và điểm. Cô Mai đưa bạn năm bảng, nhưng chưa nói nên nối bảng nào với bảng nào. Nếu chỉ ghép những cột cùng tên, màn hình có thể hiện một điểm dưới nhiều lớp khác nhau. Bạn cần một bản đồ cho biết dữ liệu thuộc về ai và những kết luận nào chưa đủ căn cứ.

Mỗi hộp kể về một loại thông tin

Sơ đồ thực thể và quan hệ (entity relationship diagram, viết tắt ERD) mô tả các bảng và mối liên hệ giữa chúng. Trong sơ đồ của khóa học, mỗi hộp là một bảng. Danh sách bên trong hộp ghi các cột cùng những quy tắc đã khai báo. Một đường nối đi từ cột tham chiếu tới cột được tham chiếu.

Trước khi lần theo đường, hãy đặt tên cho một dòng của từng bảng. Một dòng students là một học viên. Một dòng scores là một lần chấm, nên một người có thể xuất hiện nhiều lần. Sự khác nhau này quyết định bạn đang đếm người hay đếm lần làm bài.

Quan sát sơ đồ từ cấu trúc thật của bộ mẫu:

Khóa chính (primary key, ký hiệu PK) xác định một dòng trong chính bảng đó. Khóa ngoại (foreign key, ký hiệu FK) tham chiếu một dòng của bảng khác. Vì vậy scores.id chỉ lần chấm, còn scores.student_id chỉ người được chấm. Hai cột có thể tình cờ chứa cùng số nhưng vẫn mang hai nghĩa khác nhau.

Đọc hai loại mã cạnh nhau trên dữ liệu thật:

SELECT id, student_id, score
FROM scores
WHERE student_id = 1
ORDER BY id;

Hai dòng có id lần lượt là 1 và 2. Cả hai cùng có student_id = 1, bởi An được chấm hai lần. Nếu dùng mã học viên để định danh lần chấm, bạn sẽ không phân biệt được hai sự kiện. Khi đọc hộp tiếp theo, hãy hỏi lại mỗi mã đang chỉ người, lớp hay sự kiện.

Đọc một quan hệ theo cả hai chiều

Số lượng quan hệ (cardinality) nói một dòng có thể liên quan tới bao nhiêu dòng ở bảng kia. Quan hệ một–nhiều nghĩa là một dòng có thể được nhiều dòng khác tham chiếu. Nhưng chữ “nhiều” chưa nói số lượng tối thiểu. Bạn còn cần hỏi có được chưa có dòng nào hay không.

Với students và scores, mỗi lần chấm phải thuộc đúng một học viên có thật. Cột student_id có khóa ngoại và NOT NULL, nên lần chấm không được thiếu người. Chiều ngược lại khác hẳn: học viên có thể chưa được chấm lần nào. Khóa ngoại không bắt mỗi người phải có dữ liệu ở bảng con.

Hãy đếm lần chấm của cả người đã làm bài lẫn người chưa làm:

SELECT s.id, s.name, COUNT(t.id) AS attempts
FROM students AS s
LEFT JOIN scores AS t ON t.student_id = s.id
WHERE s.id IN (1, 6)
GROUP BY s.id, s.name
ORDER BY s.id;

An có attempts = 2, còn Lan có attempts = 0. LEFT JOIN giữ Lan, và COUNT(t.id) bỏ phần NULL do không ghép được. Hai dòng minh họa hai khả năng được phép: nhiều lần và chưa có lần nào. Chúng không đặt giới hạn rằng mọi học viên chỉ được có tối đa hai lần chấm.

Bạn có thể ghi quan hệ bằng lời trước khi vẽ ký hiệu:

Đọc hai chiều giúp tránh một lỗi dễ mắc: nhìn phía “một” rồi ép mỗi học viên chỉ được làm bài một lần. Từ “một” ở đây nói mỗi lần chấm chỉ thuộc một người. Nó không nói người đó chỉ được xuất hiện một lần trong toàn bộ bảng điểm.

Quan hệ nhiều–nhiều cần bảng ghi từng cặp

Một học viên có thể học nhiều lớp, và một lớp có thể có nhiều học viên. Đây là quan hệ nhiều–nhiều (many-to-many relationship). Bảng enrollments lưu từng cặp học viên–lớp. Nó thường được gọi là bảng liên kết (junction table).

Cặp (student_id, class_id) là khóa chính của bảng liên kết. Một mã học viên được xuất hiện ở các lớp khác nhau. Một mã lớp cũng được xuất hiện với các học viên khác nhau. Chỉ việc lặp toàn bộ cùng một cặp mới vi phạm khóa.

Sơ đồ nhỏ sau giúp chọn đường lấy tên người và tên lớp:

Theo đường đó, tạo danh sách các đăng ký đang học:

SELECT s.name, c.name AS class_name, e.status
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN classes AS c ON c.id = e.class_id
WHERE e.status = 'active'
ORDER BY s.id, c.id;

An có hai dòng vì có hai đăng ký đang học. Dung không xuất hiện vì đăng ký của bạn có status = 'paused'. Đây là danh sách đăng ký đang học, nên bỏ Dung phù hợp yêu cầu. Nếu câu hỏi chuyển sang mọi học viên, bạn phải xét lại bảng nguồn và cách giữ người chưa có đăng ký.

Đường đi qua học viên chưa xác định điểm thuộc lớp nào

Thầy Nam muốn đặt mỗi điểm vào đúng lớp đã tổ chức kiểm tra. Sơ đồ hiện tại chưa có thông tin đó. scores chỉ lưu người được chấm, điểm và ngày. Nó không lưu mã lớp hoặc mã bài kiểm tra.

Một cách ghép dễ gây hiểu sai là nối điểm với mọi đăng ký của người nhận điểm. Câu lệnh vẫn chạy, nhưng kết quả chỉ liệt kê mọi cặp điểm–đăng ký có chung người. Nó chưa chứng minh lớp nào đã chấm bài đó.

Xem cách ghép không đủ căn cứ dưới đây:

SELECT t.id AS attempt_id, t.score, e.class_id
FROM scores AS t
JOIN enrollments AS e ON e.student_id = t.student_id
WHERE t.student_id = 1
ORDER BY t.id, e.class_id;

Điểm 80 của lần chấm 1 xuất hiện với cả lớp 1 và lớp 3. Điểm 90 cũng vậy. An có hai điểm và hai đăng ký, nên phép nối tạo bốn cặp. Thêm DISTINCT không giải quyết được vì các cặp đang khác nhau thật.

Khi thiết kế phần mở rộng, hãy hỏi người phụ trách điểm cần gắn với lớp hay với bài kiểm tra. Nếu một bài dùng ở nhiều lớp, hai câu hỏi còn khác nhau. Chỉ sau khi thống nhất ý nghĩa, bạn mới thêm đúng mã tham chiếu. Một đường vẽ thêm trên giấy chưa tạo ra dữ liệu còn thiếu.

Sơ đồ mô tả quy tắc, dữ liệu cho biết hiện trạng

Sơ đồ cho biết một lớp được phép có nhiều đăng ký. Nó không cho biết hiện mỗi lớp có bao nhiêu người. Muốn trả lời hiện trạng, bạn cần đọc các dòng bằng truy vấn. Sơ đồ và bảng dữ liệu hỗ trợ nhau, không thay thế nhau.

Đếm mọi đăng ký, kể cả đăng ký tạm dừng, theo từng lớp:

SELECT c.id, c.name, COUNT(e.student_id) AS n
FROM classes AS c
LEFT JOIN enrollments AS e ON e.class_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Lớp Python có n = 3, Scratch và Database cùng có n = 2. Scratch tính cả đăng ký tạm dừng của Dung vì câu lệnh chưa lọc trạng thái. Nếu mai có thêm đăng ký, các số đổi nhưng sơ đồ có thể giữ nguyên. Lớp chưa có người cũng vẫn hợp lệ và sẽ có số đếm bằng 0.

Khi tự phác sơ đồ, bạn có thể làm theo thứ tự này:

Tên cột giống nhau chưa đủ để vẽ đường nối. Giá trị hiện tại chỉ có hai trạng thái cũng chưa đủ kết luận đã có quy tắc cấm trạng thái khác. Chẳng hạn bảng enrollments mẫu có giá trị mặc định nhưng chưa có CHECK giới hạn trạng thái. Sơ đồ chính xác chỉ ghi điều cấu trúc thực sự bảo vệ.

Lỗi thường gặp

Tóm tắt

• Mỗi hộp cần có ý nghĩa dòng rõ ràng trước khi chọn khóa.
• Khóa ngoại bắt buộc ở bảng con không ép bảng cha phải có dòng con.
• Bảng liên kết giữ từng cặp của quan hệ nhiều–nhiều.
• Một đường nối qua cùng học viên chưa chứng minh điểm thuộc lớp nào.
• Dùng sơ đồ để chọn đường, dùng truy vấn để kiểm hiện trạng.

Kiểm tra nhanh

Đọc thêm

• CS50 SQL: thiết kế và sơ đồ quan hệ.
• SQLite: khóa ngoại và tham chiếu bắt buộc.