Chương 8-Bài 3. Bài tập thiết kế Cơ sở dữ liệu Quản lý Giảng viên và Sinh viên

1. Mô tả bài toán

Một trường Đại học cần xây dựng hệ thống cơ sở dữ liệu để quản lý thông tin về Giảng viên, Sinh viên, Khoa và Kết quả học tập. Các yêu cầu quản lý cụ thể như sau:

• Khoa: Mỗi khoa cần quản lý Mã khoa, Tên khoa và Điện thoại liên hệ.

• Giảng viên: Thuộc về một Khoa nhất định. Cần lưu trữ Mã GV, Họ tên, Lương cơ bản và Khoa mà họ công tác.

• Sinh viên: Thuộc về một Khoa (lớp quản lý). Cần lưu Mã SV, Họ tên, Năm sinh, Quê quán và Khoa quản lý.

• Môn học: Cần lưu Mã môn học, Tên môn học và Số tín chỉ.

• Kết quả: Lưu trữ điểm thi của sinh viên đối với từng môn học (Một sinh viên học nhiều môn, một môn có nhiều sinh viên học).

2. Thiết kế lược đồ (Schema)

Dựa trên mô tả, chúng ta xác định các bảng (Table) và mối quan hệ (Relationship) như sau:

1. TBL_KHOA (Makhoa, Tenkhoa, Dienthoai)

2. TBL_GIANGVIEN (Magv, Hoten, Luong, Makhoa)

3. TBL_SINHVIEN (Masv, Hoten, Namsinh, Quequan, Makhoa)

4. TBL_MONHOC (Mamh, Tenmh, Sotinchi)

5. TBL_KETQUA (Masv, Mamh, Diemthi)

3. Script tạo bảng và dữ liệu mẫu (SQL)

Bạn hãy chạy đoạn mã SQL dưới đây để khởi tạo môi trường thực hành:

-- Tạo Database
CREATE DATABASE DB_TruongHoc;
USE DB_TruongHoc;

-- 1. Tạo bảng Khoa
CREATE TABLE TBL_KHOA (
    Makhoa CHAR(10) PRIMARY KEY,
    Tenkhoa VARCHAR(50) NOT NULL,
    Dienthoai VARCHAR(20)
);

-- 2. Tạo bảng Giảng Viên
CREATE TABLE TBL_GIANGVIEN (
    Magv INT PRIMARY KEY,
    Hoten VARCHAR(50) NOT NULL,
    Luong DECIMAL(10,2),
    Makhoa CHAR(10),
    FOREIGN KEY (Makhoa) REFERENCES TBL_KHOA(Makhoa)
);

-- 3. Tạo bảng Sinh Viên
CREATE TABLE TBL_SINHVIEN (
    Masv INT PRIMARY KEY,
    Hoten VARCHAR(50) NOT NULL,
    Namsinh INT,
    Quequan VARCHAR(50),
    Makhoa CHAR(10),
    FOREIGN KEY (Makhoa) REFERENCES TBL_KHOA(Makhoa)
);

-- 4. Tạo bảng Môn Học
CREATE TABLE TBL_MONHOC (
    Mamh CHAR(10) PRIMARY KEY,
    Tenmh VARCHAR(50) NOT NULL,
    Sotinchi INT
);

-- 5. Tạo bảng Kết Quả
CREATE TABLE TBL_KETQUA (
    Masv INT,
    Mamh CHAR(10),
    Diemthi DECIMAL(4,1),
    PRIMARY KEY (Masv, Mamh),
    FOREIGN KEY (Masv) REFERENCES TBL_SINHVIEN(Masv),
    FOREIGN KEY (Mamh) REFERENCES TBL_MONHOC(Mamh)
);

-- --- THÊM DỮ LIỆU MẪU ---

-- Thêm Khoa
INSERT INTO TBL_KHOA VALUES 
('CNTT', 'Cong nghe thong tin', '0243888888'),
('TOAN', 'Toan tin', '0243999999'),
('SINH', 'Cong nghe Sinh hoc', '0243777777');

-- Thêm Giảng viên
INSERT INTO TBL_GIANGVIEN VALUES 
(1, 'Tran Van A', 15000000, 'CNTT'),
(2, 'Nguyen Thi B', 12000000, 'CNTT'),
(3, 'Le Van C', 13000000, 'TOAN'),
(4, 'Pham Thi D', 11000000, 'SINH');

-- Thêm Sinh viên
INSERT INTO TBL_SINHVIEN VALUES 
(101, 'Nguyen Van Hung', 2002, 'Ha Noi', 'CNTT'),
(102, 'Tran Thi Hoa', 2003, 'Nam Dinh', 'CNTT'),
(103, 'Le Van Nam', 2002, 'Nghe An', 'TOAN'),
(104, 'Pham Minh Tuan', 2003, 'Ha Noi', 'SINH');

-- Thêm Môn học
INSERT INTO TBL_MONHOC VALUES 
('CSDL', 'Co so du lieu', 3),
('CTDL', 'Cau truc du lieu', 4),
('TRR', 'Toan roi rac', 3);

-- Thêm Kết quả
INSERT INTO TBL_KETQUA VALUES 
(101, 'CSDL', 8.5),
(101, 'CTDL', 7.0),
(102, 'CSDL', 9.0),
(102, 'TRR', 6.5),
(103, 'TRR', 8.0),
(104, 'CSDL', 5.5);

4. Các câu hỏi thực hành (Query Exercise)

Hãy viết câu lệnh SQL để thực hiện các yêu cầu sau:

Phần A: Truy vấn cơ bản

1. Hiển thị danh sách gồm: Mã sinh viên, Họ tên, Mã khoa của tất cả sinh viên.

2. Hiển thị danh sách gồm: Mã sinh viên, Họ tên, Mã khoa của các sinh viên có quê quán ở "Ha Noi".

3. Hiển thị danh sách các môn học có số tín chỉ lớn hơn 3.

Phần B: Truy vấn liên kết (JOIN)

4. Hiển thị danh sách sinh viên gồm: Mã SV, Họ tên, Mã khoa, Tên khoa.

5. Hiển thị kết quả học tập gồm: Mã SV, Họ tên SV, Tên môn học, Điểm thi.

Phần C: Truy vấn tính toán & Nhóm (Aggregate & Group By)

6. Đếm số lượng sinh viên của từng Khoa.

7. Tính điểm trung bình của sinh viên có Mã SV là 101.

8. Hiển thị danh sách các môn học chưa có sinh viên nào dự thi.

Phần D: Truy vấn nâng cao

9. Tìm sinh viên có điểm thi môn "Co so du lieu" cao nhất.

10. Hiển thị danh sách giảng viên có lương cao hơn mức lương trung bình của toàn bộ giảng viên trong trường.

Gợi ý giải (Solutions Hint)

Lưu ý: Hãy tự thực hiện trước khi xem gợi ý bên dưới.

Câu 4:

SELECT sv.Masv, sv.Hoten, k.Tenkhoa 
FROM TBL_SINHVIEN sv 
JOIN TBL_KHOA k ON sv.Makhoa = k.Makhoa;

Câu 6:

SELECT k.Tenkhoa, COUNT(sv.Masv) as SoLuongSV
FROM TBL_KHOA k
LEFT JOIN TBL_SINHVIEN sv ON k.Makhoa = sv.Makhoa
GROUP BY k.Tenkhoa;

Câu 9:

SELECT sv.Hoten, kq.Diemthi
FROM TBL_KETQUA kq
JOIN TBL_SINHVIEN sv ON kq.Masv = sv.Masv
JOIN TBL_MONHOC mh ON kq.Mamh = mh.Mamh
WHERE mh.Tenmh = 'Co so du lieu'
ORDER BY kq.Diemthi DESC
LIMIT 1;