Đề thi tổng hợp môn Cơ sở dữ liệu (Đề số 3)

Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu<br /> <br /> ĐỀ 3<br /> Cho lược đồ cơ sở dữ liệu Quản lý đề tài tốt nghiệp như sau:<br /> KHOA(MaKhoa, TenKhoa, TruongKhoa, TongsoSV)<br /> Tân từ: mỗi khoa có một mã số duy nhất để phân biệt với các khoa khác, tên khoa,<br /> trưởng khoa và tổng số sinh viên thuộc khoa.<br /> SINHVIEN(MaSV, HoTenSV, NgaySinh, Gioitinh, DiaChi, MaKhoa)<br /> Tân từ: mỗi sinh viên có một mã số duy nhất để phân biệt với các sinh viên khác,<br /> tên sinh viên, ngày sinh, giới tính (Nam/Nữ), địa chỉ, và khoa sinh viên trực thuộc<br /> (MaKhoa) .<br /> GIANGVIEN(MaGV, HoTenGV, NgaySinh, DiaChi, MaKhoa)<br /> Tân từ: mỗi giảng viên có một mã số duy nhất để phân biệt với các giảng viên<br /> khác, tên giảng viên, ngày sinh, giới tính (Nam/Nữ), địa chỉ, và khoa trực thuộc.<br /> DETAI(MaDeTai, TenDeTai, LinhVuc, Nam, MaGV)<br /> Tân từ: mỗi đề tài tốt nghiệp có một mã số (MaDeTai) phân biệt với các đề tài<br /> khác, tên đề tài, thuộc lĩnh vực nào (ví dụ: máy học, CSDL phân tán, xử lý ngôn<br /> ngữ,…), năm thực hiện đề tài (Nam), giảng viên hướng dẫn đề tài (MaGV).<br /> HUONGDAN(MaDeTai, MaSV, Diemso)<br /> Tân từ: Sinh viên tham gia làm đề tài tốt nghiệp dưới sự hướng dẫn của giảng<br /> viên, có một kết quả đề tài (điểm số). Mỗi đề tài có tổi đa 2 sinh viên tham gia.<br /> Yêu cầu:<br /> 1. Hãy phát biểu RBTV “Tổng số sinh viên của khoa bằng số lượng sinh viên<br /> thuộc về khoa đó” (1 điểm).<br /> Thực hiện các câu truy vấn sau bằng ngôn ngữ đại số quan hệ:<br /> 2. Với các sinh viên thuộc khoa ‘CNTT’ (mã khoa = ‘CNTT’) và có làm đề<br /> tài tốt nghiệp, hãy liệt kê tên sinh viên, tên đề tài, tên giáo viên hướng<br /> dẫn (HoTenSV, TenDeTai, HoTenGV) (1 điểm).<br /> Thực hiện các câu truy vấn sau bằng ngôn ngữ SQL:<br /> 3. Với các sinh viên thuộc khoa ‘Công nghệ thông tin’ (tên khoa = ‘Công nghệ<br /> thông tin’) và có làm đề tài tốt nghiệp, hãy liệt kê tên sinh viên, tên đề tài, tên<br /> giáo viên hướng dẫn (HoTenSV, TenDeTai, HoTenGV), sắp thứ tự theo tên đề<br /> tài (1.5 điểm).<br /> 4. Cho biết tên giảng viên và số lượng đề tài mà giảng viên hướng dẫn (0.75<br /> điểm).<br /> 5. Cho biết tên giảng viên, năm và số lượng đề tài mà họ hướng dẫn theo từng<br /> năm (0.75 điểm).<br /> <br /> amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4<br /> <br /> 1<br /> <br /> Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu<br /> ĐÁP ÁN Đề 3<br /> Câu 1: (1 điểm)<br />  Nội dung:<br /> Tổng số sinh viên của khoa bằng số lượng sinh viên thuộc về khoa đó.<br /> k  KHOA,<br /> k.TongSoSV = Count(sv  SINHVIEN  sv.MaKhoa = k.MaKhoa)<br /> (sv.MaSV)<br /> Cuối k<br /> hoặc cách khác: k.TongSoSV = Count(sv  SINHVIEN  sv.MaKhoa =<br /> k.MaKhoa) (*)<br />  Bối cảnh: KHOA, SINHVIEN<br />  Bảng tầm ảnh hưởng:<br /> <br /> KHOA<br /> SINHVIEN<br /> <br /> Thêm<br /> -(*)<br /> <br /> Xóa<br /> -<br /> <br /> +<br /> <br /> +<br /> <br /> Sửa<br /> +<br /> (TongSoSV)<br /> +<br /> (MaKhoa)<br /> <br /> -(*) Ở thời điểm thêm một bộ vào KHOA, giá trị bộ đó tại thuộc tính<br /> KHOA là 0.<br /> MaSV<br /> MaDeTai<br /> MaGV<br /> Câu 2: (1 điểm)<br /> ((SINHVIEN:MaKhoa=‘CNTT’)<br /> HUONGDAN<br /> DETAI<br /> GIANGVIEN) [HoTenSV, TenDeTai, HoTenGV]<br /> Câu 3: (1.5 điểm)<br /> Select HoTenSV, TenDeTai, HoTenGV<br /> From SinhVien sv, Khoa k, HuongDan hd, DeTai dt, GiangVien gv<br /> Where sv.MaKhoa = k.MaKhoa AND k.TenKhoa = ‘Công nghệ thông tin’<br /> AND sv.MaSV = hd.MaSV AND hd.MaDeTai = dt.MaDeTai<br /> AND dt.MaGV = gv.MaGV<br /> ORDER BY TenDeTai<br /> Câu 4: (0.75 điểm)<br /> Select HoTenGV, count(MaDeTai) as SoLuongDT<br /> From DeTai dt, GiangVien gv<br /> Where dt.MaDeTai = gv.MaDeTai<br /> GROUP BY HoTenGV<br /> Câu 5: (0.75 điểm)<br /> Select HoTenGV, Nam, count(MaDeTai) as SoLuongDT<br /> From DeTai dt, GiangVien gv<br /> Where dt.MaDeTai = gv.MaDeTai<br /> GROUP BY HoTenGV, Nam<br /> <br /> amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4<br /> <br /> 2<br /> <br /> Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu<br /> BÀI TẬP TỔNG HỢP<br /> Cho lược đồ cơ sở dữ liệu Quản lý điểm thi như sau:<br /> LOP(MaLop, TenLop, LopTruong, SiSo)<br /> SINHVIEN(MaSV, HoTenSV, NgaySinh, GioiTinh, DiaChi, Lop)<br /> MONHOC(MaMH, TenMH, SoTinChi, SoTiet)<br /> KETQUA(MaSV, MaMH, LanThi, Diem, KetQua)<br /> Yêu cầu:<br /> 1. Hãy phát biểu một cách chặt chẽ RBTV “Sĩ số thực sự (SiSo) của một lớp<br /> bằng số lượng sinh viên trực thuộc lớp đó”.<br /> Thực hiện các câu truy vấn sau bằng ngôn ngữ đại số quan hệ:<br /> 2. Với các sinh viên thuộc lớp ‘K1-01’ (tên lớp = ‘K1-01’), hãy liệt kê mã số<br /> sinh viên, họ tên sinh viên, tên môn học, lần thi, điểm số của các môn học<br /> mà sinh viên tham gia với kết quả ‘Đậu’.<br /> Thực hiện các câu truy vấn sau bằng ngôn ngữ SQL, DSQH:<br /> 3. Liệt kê mã sinh viên, họ tên sinh viên và kết quả thi (MaSV, HoTenSV,<br /> Diem, KetQua) lần 1 môn ‘Cơ sở dữ liệu’ (Tên môn học = ’Cơ sở dữ liệu’).<br /> Kết quả sắp xếp theo điểm giảm dần.<br /> 4. Cho biết tên lớp và sĩ số của lớp có đông sinh viên nhất. (chọn mẫu tin có<br /> cột kiểu số có giá trị lớn nhất) (tương tự cho câu hỏi: Cho biết môn học có<br /> số tín chỉ nhiều nhất hoặc Cho biết sinh viên có tuổi lớn nhất,…. )<br /> 5. Cho biết mã sinh viên, họ tên và số lượng môn học mà sinh viên đó đã tham<br /> gia thi. (dùng GROUP BY)<br /> 6. Cho biết mã sinh viên, họ tên và số môn học mà sinh viên đó đã tham<br /> gia thi với số môn học trên 5? (đk trên nhóm HAVING)<br /> 7. Cho biết sinh viên học nhiều môn nhất? (đk HAVING>= all…)<br /> 8. (DSQH+SQL, phép giao): cho biết sv học cả 2 môn ‘Co So Du Lieu’ và<br /> ‘Toan Roi Rac’<br /> 9. (DSQH+SQL, phép trừ): cho biết những sv không học môn ‘Toan Roi<br /> Rac’<br /> <br /> amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4<br /> <br /> 3<br /> <br /> Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu<br /> ĐÁP ÁN ĐỀ TỔNG HỢP<br /> Câu 1: (1 điểm)<br />  Nội dung:<br /> Sĩ số thực sự của một lớp bằng số lượng sinh viên trực thuộc lớp đó.<br /> l  LOP,<br /> sv  SINHVIEN sao cho sv.Lop = l.MaLop<br /> l.Siso = Count (sv.MaSV)<br /> Cuối sv<br /> Cuối l<br /> Hoặc viết khác:<br /> l  LOP,<br /> l.Siso = Count(sv  SINHVIEN  sv.Lop = l.Malop) (sv.MaSV)<br /> Cuối l<br /> hoặc cách khác: l.SiSo = Count(sv  SINHVIEN  sv.Lop = l.Malop) (*)<br />  Bối cảnh: LOP, SINHVIEN<br />  Bảng tầm ảnh hưởng:<br /> <br /> LOP<br /> SINHVIEN<br /> <br /> Thêm<br /> +<br /> <br /> Xóa<br /> -<br /> <br /> +<br /> <br /> +<br /> <br /> Sửa<br /> +<br /> (SiSo)<br /> +<br /> (Lop)<br /> <br /> Câu 2: (0.75 điểm)<br /> MaLop=Lop<br /> MaSV<br /> MaMH<br /> ((LOP:TenLop=‘K1-01’)<br /> SINHVIEN<br /> (KETQUA:Ketqua=’Đậu’) aaa<br /> MONHOC) [MaSV, HoTenSV, TenMH, LanThi, Diem]<br /> Câu 3: (0.75 điểm) (kết giữa các bảng, chọn, chiếu đơn giản)<br /> SELECT sv.MaSV, HoTenSV, Diem, KetQua<br /> FROM SINHVIEN sv, MONHOC m, KETQUA k<br /> WHERE sv.MaSV = k.MaSV AND k.MaMH = m.MaMH<br /> AND m.TenMH = ‘Cơ sở dữ liệu’ and k.LanThi=1<br /> ORDER BY Diem DESC;<br /> <br /> Câu 4: (0.5 điểm) (chọn mẫu tin có cột kiểu số có giá trị lớn nhất)<br /> SELECT TenLop, SiSo<br /> FROM LOP<br /> WHERE SiSo = ( SELECT Max (SiSo) FROM Lop)<br /> <br /> amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4<br /> <br /> 4<br /> <br /> Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu<br /> Câu 5: (0.5 điểm) (dùng GROUP BY)<br /> SELECT sv.MaSV, HoTenSV, count(distinct k.MaMH) as SoMonDaHoc<br /> FROM SINHVIEN sv, KETQUA k<br /> WHERE sv.MaSV = k.MaSV<br /> GROUP BY sv.MaSV, HoTenSV<br /> MỞ RỘNG<br /> Câu 6: Cho biết mã sinh viên, họ tên và số môn học mà sinh viên đó đã tham gia thi với số môn<br /> học trên 5? (đk trên nhóm HAVING)<br /> SELECT sv.MaSV, HoTenSV, count(distinct k.MaMH) as SoMonDaHoc<br /> FROM SINHVIEN sv, KETQUA k<br /> WHERE sv.MaSV = k.MaSV<br /> GROUP BY sv.MaSV, HoTenSV<br /> HAVING count(distinct k.MaMH) >=5<br /> Câu 7: Cho biết sinh viên học nhiều môn nhất? (đk HAVING>= all…)<br /> SELECT sv.MaSV, HoTenSV, count(distinct k.MaMH) as SoMonDaHoc<br /> FROM SINHVIEN sv, KETQUA k<br /> WHERE sv.MaSV = k.MaSV<br /> GROUP BY sv.MaSV, HoTenSV<br /> HAVING count(distinct k.MaMH) >= All (SELECT count(distinct k.MaMH)<br /> FROM SINHVIEN sv, KETQUA k<br /> WHERE sv.MaSV = k.MaSV<br /> GROUP BY sv.MaSV, HoTenSV )<br /> Câu 8: (DSQH+SQL, phép giao): cho biết sv học cả 2 môn ‘Co So Du Lieu’ và ‘Toan Roi<br /> Rac’<br /> MaMH<br /> ((MONHOC: TenMH= ‘Co So Du Lieu’)<br /> KETQUA)[Masv] a aa(((MONHOC:<br /> MaMH<br /> TenMH= ‘Toan Roi Rac’)<br /> KETQUA)[Masv]<br /> Select masv From MONHOC, KETQUA<br /> Where tenmh=‘Co So Du Lieu’ and monhoc.mamh=ketqua.mamh and<br /> Masv IN (Select masv From MONHOC, KETQUA<br /> Where tenmh=‘Toan Roi Rac’ and monhoc.mamh=ketqua.mamh and<br /> )<br /> <br /> <br /> <br /> Câu 9: (DSQH+SQL, phép trừ): cho biết những sv không học môn ‘Toan Roi Rac’<br /> MaMH<br /> <br /> SINHVIEN[MaSV]-((MONHOC:TenMH=‘Toanroirac’)<br /> <br /> KETQUA)[Masv]<br /> <br /> Select masv, hoten From SINHVIEN<br /> Where Masv NOT IN (Select masv from monhoc, ketqua<br /> Where tenmh=‘Toan Roi Rac’ and monhoc.mamh=ketqua.mamh and<br /> )<br /> <br /> amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4<br /> <br /> 5<br /> <br />