Bài tập Cơ sở dữ liệu quan hệ - Đại học Công nghệ thông tin

ran Minh Long 27/2/1986 Nam TpHCM K11 
K1106 Le Nhat Minh 24/1/1986 Nam TpHCM K11 
K1107 Nguyen Nhu Nhut 27/1/1986 Nam Ha Noi K11 
K1108 Nguyen Manh Tam 27/2/1986 Nam Kien Giang K11 
K1109 Phan Thi Thanh Tam 27/1/1986 Nu Vinh Long K11 
K1110 Le Hoai Thuong 5/2/1986 Nu Can Tho K11 
K1111 Le Ha Vinh 25/12/1986 Nam Vinh Long K11 
K1201 Nguyen Van B 11/2/1986 Nam TpHCM K12 
K1202 Nguyen Thi Kim Duyen 18/1/1986 Nu TpHCM K12 
K1203 Tran Thi Kim Duyen 17/9/1986 Nu TpHCM K12 
K1204 Truong My Hanh 19/5/1986 Nu Dong Nai K12 
K1205 Nguyen Thanh Nam 17/4/1986 Nam TpHCM K12 
K1206 Nguyen Thi Truc Thanh 4/3/1986 Nu Kien Giang K12 
K1207 Tran Thi Bich Thuy 8/2/1986 Nu Nghe An K12 
K1208 Huynh Thi Kim Trieu 8/4/1986 Nu Tay Ninh K12 
K1209 Pham Thanh Trieu 23/2/1986 Nam TpHCM K12 
K1210 Ngo Thanh Tuan 14/2/1986 Nam TpHCM K12 
K1211 Do Thi Xuan 9/3/1986 Nu Ha Noi K12 
K1212 Le Thi Phi Yen 12/3/1986 Nu TpHCM K12 
K1301 Nguyen Thi Kim Cuc 9/6/1986 Nu Kien Giang K13 
K1302 Truong Thi My Hien 18/3/1986 Nu Nghe An K13 
K1303 Le Duc Hien 21/3/1986 Nam Tay Ninh K13 
K1304 Le Quang Hien 18/4/1986 Nam TpHCM K13 
K1305 Le Thi Huong 27/3/1986 Nu TpHCM K13 
K1306 Nguyen Thai Huu 30/3/1986 Nam Ha Noi K13 
K1307 Tran Minh Man 28/5/1986 Nam TpHCM K13 
K1308 Nguyen Hieu Nghia 8/4/1986 Nam Kien Giang K13 
K1309 Nguyen Trung Nghia 18/1/1987 Nam Nghe An K13 
K1310 Tran Thi Hong Tham 22/4/1986 Nu Tay Ninh K13 
K1311 Tran Minh Thuc 4/4/1986 Nam TpHCM K13 
K1312 Nguyen Thi Kim Yen 7/9/1986 Nu TpHCM K13 
Khoa Hệ Thống Thông Tin - Đại học Công Nghệ Thông Tin 
Cơ Sở Dữ Liệu Quan Hệ
Trang 10
BẢNG THUỘC TÍNH 
Quan hệ Thuộc tính Diễn giải Kiểu dữ liệu
MAKHOA Mã khoa (khóa chính) varchar(4) 
TENKHOA Tên khoa varchar(40) 
NGTLAP Ngày thành lập khoa smalldatetime 
KHOA 
TRGKHOA Trưởng khoa (mã giáo viên) char(4) 
MAMH Mã môn học (khóa chính) varchar(10) 
TENMH Tên môn học varchar(40) 
TCLT Số tín chỉ lý thuyết tinyint 
TCTH Số tín chỉ thực hành tinyint 
MONHOC 
MAKHOA Môn học thuộc khoa nào varchar(4) 
MAMH Mã môn học (thuộc tính khóa) varchar(10) DIEUKIEN 
MAMH_TRUOC Mã môn học phải học trước (thuộc tính khóa) varchar(10) 
MAGV Mã giáo viên (khóa chính) char(4) 
HOTEN Họ tên varchar(40) 
HOCVI, HOCHAM Học vị, học hàm varchar(10) 
GIOITINH Giới tính varchar(3) 
NGSINH, NGVL Ngày sinh, ngày vào làm việc smalldatetime 
HESO Hệ số lương numeric(4,2) 
MUCLUONG Mức lương money 
GIAOVIEN 
MAKHOA Thuộc khoa nào varchar(4) 
MALOP Mã lớp (khóa chính) char(3) 
TENLOP Tên lớp varchar(40) 
TRGLOP Lớp trưởng (mã học viên) char(5) 
SISO Sỉ số lớp tinyint 
LOP 
MAGVCN Mã giáo viên chủ nhiệm char(4) 
MAHV Mã học viên (khóa chính) char(5) 
HO Họ và tên lót varchar(40) 
TEN Tên varchar(10) 
NGSINH Ngày sinh smalldatetime 
GIOITINH Giới tính varchar(3) 
NOISINH Nơi sinh varchar(40) 
HOCVIEN 
MALOP Mã lớp char(3) 
MALOP Mã lớp (thuộc tính khóa) char(3) 
MAMH Mã môn học (thuộc tính khóa) varchar(10) 
MAGV Mã giáo viên char(4) 
HOCKY Học kỳ tinyint 
NAM Năm học smallint 
GIANGDAY 
TUNGAY, DENNGAY Ngày bắt đầu và ngày kết thúc môn học smalldatetime 
MAHV Mã học viên (thuộc tính khóa) char(5) 
MAMH Mã môn học (thuộc tính khóa) varchar(10) 
LANTHI Lần thi (thuộc tính khóa) tinyint 
NGTHI Ngày thi smalldatetime 
DIEM Điểm thi numeric(4,2) 
KETQUATHI 
KQUA Kết quả thi varchar(10) 
Khoa Hệ Thống Thông Tin - Đại học Công Nghệ Thông Tin 
Cơ Sở Dữ Liệu Quan Hệ
Trang 11
I. Ngôn ngữ định nghĩa dữ liệu (Data Definition Language): 
1. Tạo quan hệ và khai báo tất cả các ràng buộc khóa chính, khóa ngoại. Thêm vào 3 thuộc tính 
GHICHU, DIEMTB, XEPLOAI cho quan hệ HOCVIEN. 
2. Mã học viên là một chuỗi 5 ký tự, 3 ký tự đầu là mã lớp, 2 ký tự cuối cùng là số thứ tự học 
viên trong lớp. VD: “K1101” 
3. Thuộc tính GIOITINH chỉ có giá trị là “Nam” hoặc “Nu”. 
4. Điểm số của một lần thi có giá trị từ 0 đến 10 và cần lưu đến 2 số lẽ (VD: 6.22). 
5. Kết quả thi là “Dat” nếu điểm từ 5 đến 10 và “Khong dat” nếu điểm nhỏ hơn 5. 
6. Học viên thi một môn tối đa 3 lần. 
7. Học kỳ chỉ có giá trị từ 1 đến 3. 
8. Học vị của giáo viên chỉ có thể là “CN”, “KS”, “Ths”, ”TS”, ”PTS”. 
9. Lớp trưởng của một lớp phải là học viên của lớp đó. 
10. Trưởng khoa phải là giáo viên thuộc khoa và có học vị “TS” hoặc “PTS”. 
11. Học viên ít nhất là 18 tuổi. 
12. Giảng dạy một môn học ngày bắt đầu (TUNGAY) phải nhỏ hơn ngày kết thúc 
(DENNGAY). 
13. Giáo viên khi vào làm ít nhất là 22 tuổi. 
14. Tất cả các môn học đều có số tín chỉ lý thuyết và tín chỉ thực hành chênh lệch nhau không 
quá 3. 
15. Học viên chỉ được thi một môn học nào đó khi lớp của học viên đã học xong môn học này. 
16. Mỗi học kỳ của một năm học, một lớp chỉ được học tối đa 3 môn. 
17. Sỉ số của một lớp bằng với số lượng học viên thuộc lớp đó. 
18. Trong quan hệ DIEUKIEN giá trị của thuộc tính MAMH và MAMH_TRUOC trong cùng 
một bộ không được giống nhau (“A”,”A”) và cũng không tồn tại hai bộ (“A”,”B”) và 
(“B”,”A”). 
19. Các giáo viên có cùng học vị, học hàm, hệ số lương thì mức lương bằng nhau. 
20. Học viên chỉ được thi lại (lần thi >1) khi điểm của lần thi trước đó dưới 5. 
21. Ngày thi của lần thi sau phải lớn hơn ngày thi của lần thi trước (cùng học viên, cùng môn 
học). 
22. Học viên chỉ được thi những môn mà lớp của học viên đó đã học xong. 
23. Khi phân công giảng dạy một môn học, phải xét đến thứ tự trước sau giữa các môn học (sau 
khi học xong những môn học phải học trước mới được học những môn liền sau). 
24. Giáo viên chỉ được phân công dạy những môn thuộc khoa giáo viên đó phụ trách. 
Khoa Hệ Thống Thông Tin - Đại học Công Nghệ Thông Tin 
Cơ Sở Dữ Liệu Quan Hệ
Trang 12
II. Ngôn ngữ thao tác dữ liệu (Data Manipulation Language): 
1. Tăng hệ số lương thêm 0.2 cho những giáo viên là trưởng khoa. 
2. Cập nhật giá trị điểm trung bình tất cả các môn học (DIEMTB) của mỗi học viên (tất cả các 
môn học đều có hệ số 1 và nếu học viên thi một môn nhiều lần, chỉ lấy điểm của lần thi sau 
cùng). 
3. Cập nhật giá trị cho cột GHICHU là “Cam thi” đối với trường hợp: học viên có một môn bất 
kỳ thi lần thứ 3 dưới 5 điểm. 
4. Cập nhật giá trị cho cột XEPLOAI trong quan hệ HOCVIEN như sau: 
o Nếu DIEMTB ≥ 9 thì XEPLOAI =”XS” 
o Nếu 8 ≤ DIEMTB < 9 thì XEPLOAI = “G” 
o Nếu 6.5 ≤ DIEMTB < 8 thì XEPLOAI = “K” 
o Nếu 5 ≤ DIEMTB < 6.5 thì XEPLOAI = “TB” 
o Nếu DIEMTB < 5 thì XEPLOAI = ”Y” 
III. Ngôn ngữ truy vấn dữ liệu: 
1. In ra danh sách (mã học viên, họ tên, ngày sinh, mã lớp) lớp trưởng của các lớp. 
2. In ra bảng điểm khi thi (mã học viên, họ tên , lần thi, điểm số) môn CTRR của lớp “K12”, 
sắp xếp theo tên, họ học viên. 
3. In ra danh sách những học viên (mã học viên, họ tên) và những môn học mà học viên đó thi 
lần thứ nhất đã đạt. 
4. In ra danh sách học viên (mã học viên, họ tên) của lớp “K11” thi môn CTRR không đạt (ở 
lần thi 1). 
5. * Danh sách học viên (mã học viên, họ tên) của lớp “K” thi môn CTRR không đạt (ở tất cả 
các lần thi). 
6. Tìm tên những môn học mà giáo viên có tên “Tran Tam Thanh” dạy trong học kỳ 1 năm 
2006. 
7. Tìm những môn học (mã môn học, tên môn học) mà giáo viên chủ nhiệm lớp “K11” dạy 
trong học kỳ 1 năm 2006. 
8. Tìm họ tên lớp trưởng của các lớp mà giáo viên có tên “Nguyen To Lan” dạy môn “Co So 
Du Lieu”. 
9. In ra danh sách những môn học (mã môn học, tên môn học) phải học liền trước môn “Co So 
Du Lieu”. 
10. Môn “Cau Truc Roi Rac” là môn bắt buộc phải học liền trước những môn học (mã môn học, 
tên môn học) nào. 
11. Tìm họ tên giáo viên dạy môn CTRR cho cả hai lớp “K11” và “K12” trong cùng học kỳ 1 
năm 2006. 
Khoa Hệ Thống Thông Tin - Đại học Công Nghệ Thông Tin 
Cơ Sở Dữ Liệu Quan Hệ
Trang 13
12. Tìm những học viên (mã học viên, họ tên) thi không đạt môn CSDL ở lần thi thứ 1 nhưng 
chưa thi lại môn này. 
13. Tìm giáo viên (mã giáo viên, họ tên) không được phân công giảng dạy bất kỳ môn học nào. 
14. Tìm giáo viên (mã giáo viên, họ tên) không được phân công giảng dạy bất kỳ môn học nào 
thuộc khoa giáo viên đó phụ trách. 
15. Tìm họ tên các học viên thuộc lớp “K11” thi một môn bất kỳ quá 3 lần vẫn “Khong dat” 
hoặc thi lần thứ 2 môn CTRR được 5 điểm. 
16. Tìm họ tên giáo viên dạy môn CTRR cho ít nhất hai lớp trong cùng một học kỳ của một năm 
học. 
17. Danh sách học viên và điểm thi môn CSDL (chỉ lấy điểm của lần thi sau cùng). 
18. Danh sách học viên và điểm thi môn “Co So Du Lieu” (chỉ lấy điểm cao nhất của các lần 
thi). 
19. Khoa nào (mã khoa, tên khoa) được thành lập sớm nhất. 
20. Có bao nhiêu giáo viên có học hàm là “GS” hoặc “PGS”. 
21. Thống kê có bao nhiêu giáo viên có học vị là “CN”, “KS”, “Ths”, “TS”, “PTS” trong mỗi 
khoa. 
22. Mỗi môn học thống kê số lượng học viên theo kết quả (đạt và không đạt). 
23. Tìm giáo viên (mã giáo viên, họ tên) là giáo viên chủ nhiệm của một lớp, đồng thời dạy cho 
lớp đó ít nhất một môn học. 
24. Tìm họ tên lớp trưởng của lớp có sỉ số cao nhất. 
25. * Tìm họ tên những LOPTRG thi không đạt quá 3 môn (mỗi môn đều thi không đạt ở tất cả 
các lần thi). 
26. Tìm học viên (mã học viên, họ tên) có số môn đạt điểm 9,10 nhiều nhất. 
27. Trong từng lớp, tìm học viên (mã học viên, họ tên) có số môn đạt điểm 9,10 nhiều nhất. 
28. Trong từng học kỳ của từng năm, mỗi giáo viên phân công dạy bao nhiêu môn học, bao 
nhiêu lớp. 
29. Trong từng học kỳ của từng năm, tìm giáo viên (mã giáo viên, họ tên) giảng dạy nhiều nhất. 
30. Tìm môn học (mã môn học, tên môn học) có nhiều học viên thi không đạt (ở lần thi thứ 1) 
nhất. 
31. Tìm học viên (mã học viên, họ tên) thi môn nào cũng đạt (chỉ xét lần thi thứ 1). 
32. * Tìm học viên (mã học viên, họ tên) thi môn nào cũng đạt (chỉ xét lần thi sau cùng). 
33. * Tìm học viên (mã học viên, họ tên) đã thi tất cả các môn đều đạt (chỉ xét lần thi thứ 1). 
34. * Tìm học viên (mã học viên, họ tên) đã thi tất cả các môn đều đạt (chỉ xét lần thi sau cùng). 
35. ** Tìm học viên (mã học viên, họ tên) có điểm thi cao nhất trong từng môn (lấy điểm ở lần 
thi sau cùng).