Bộ đề môn Cơ sở dữ liệu - Tổng hợp đề 3

Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 1 ĐỀ 3 Cho lược đồ cơ sở dữ liệu Quản lý đề tài tốt nghiệp như sau: KHOA(MaKhoa, TenKhoa, TruongKhoa, TongsoSV) 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, trưởng khoa và tổng số sinh viên thuộc khoa. SINHVIEN(MaSV, HoTenSV, NgaySinh, Gioitinh, DiaChi, MaKhoa) 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, 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 (MaKhoa) . GIANGVIEN(MaGV, HoTenGV, NgaySinh, DiaChi, MaKhoa) 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 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. DETAI(MaDeTai, TenDeTai, LinhVuc, Nam, MaGV) 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 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 ngữ,), năm thực hiện đề tài (Nam), giảng viên hướng dẫn đề tài (MaGV). HUONGDAN(MaDeTai, MaSV, Diemso) 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 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. Yêu cầu: 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 thuộc về khoa đó” (1 điểm). Thực hiện các câu truy vấn sau bằng ngôn ngữ đại số quan hệ: 2. Với các sinh viên thuộc khoa ‘CNTT’ (mã khoa = ‘CNTT’) 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 giáo viên hướng dẫn (HoTenSV, TenDeTai, HoTenGV) (1 điểm). Thực hiện các câu truy vấn sau bằng ngôn ngữ SQL: 3. Với các sinh viên thuộc khoa ‘Công nghệ thông tin’ (tên khoa = ‘Công nghệ 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 giáo viên hướng dẫn (HoTenSV, TenDeTai, HoTenGV), sắp thứ tự theo tên đề tài (1.5 điểm). 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 điểm). 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 năm (0.75 điểm). Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 2 ĐÁP ÁN Đề 3 Câu 1: (1 điểm)  Nội dung: Tổng số sinh viên của khoa bằng số lượng sinh viên thuộc về khoa đó. k  KHOA, k.TongSoSV = Count(sv  SINHVIEN  sv.MaKhoa = k.MaKhoa) (sv.MaSV) Cuối k hoặc cách khác: k.TongSoSV = Count(sv  SINHVIEN  sv.MaKhoa = k.MaKhoa) (*)  Bối cảnh: KHOA, SINHVIEN  Bảng tầm ảnh hưởng: Thêm Xóa Sửa KHOA -(*) - + (TongSoSV) SINHVIEN + + + (MaKhoa) -(*) Ở thời điểm thêm một bộ vào KHOA, giá trị bộ đó tại thuộc tính KHOA là 0. Câu 2: (1 điểm) ((SINHVIEN:MaKhoa=‘CNTT’) HUONGDAN DETAI GIANGVIEN) [HoTenSV, TenDeTai, HoTenGV] Câu 3: (1.5 điểm) Select HoTenSV, TenDeTai, HoTenGV From SinhVien sv, Khoa k, HuongDan hd, DeTai dt, GiangVien gv Where sv.MaKhoa = k.MaKhoa AND k.TenKhoa = ‘Công nghệ thông tin’ AND sv.MaSV = hd.MaSV AND hd.MaDeTai = dt.MaDeTai AND dt.MaGV = gv.MaGV ORDER BY TenDeTai Câu 4: (0.75 điểm) Select HoTenGV, count(MaDeTai) as SoLuongDT From DeTai dt, GiangVien gv Where dt.MaDeTai = gv.MaDeTai GROUP BY HoTenGV Câu 5: (0.75 điểm) Select HoTenGV, Nam, count(MaDeTai) as SoLuongDT From DeTai dt, GiangVien gv Where dt.MaDeTai = gv.MaDeTai GROUP BY HoTenGV, Nam MaSV MaDeTai MaGV Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 3 BÀI TẬP TỔNG HỢP Cho lược đồ cơ sở dữ liệu Quản lý điểm thi như sau: LOP(MaLop, TenLop, LopTruong, SiSo) SINHVIEN(MaSV, HoTenSV, NgaySinh, GioiTinh, DiaChi, Lop) MONHOC(MaMH, TenMH, SoTinChi, SoTiet) KETQUA(MaSV, MaMH, LanThi, Diem, KetQua) Yêu cầu: 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 bằng số lượng sinh viên trực thuộc lớp đó”. Thực hiện các câu truy vấn sau bằng ngôn ngữ đại số quan hệ: 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ố 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 mà sinh viên tham gia với kết quả ‘Đậu’. Thực hiện các câu truy vấn sau bằng ngôn ngữ SQL, DSQH: 3. Liệt kê mã sinh viên, họ tên sinh viên và kết quả thi (MaSV, HoTenSV, Diem, KetQua) lần 1 môn ‘Cơ sở dữ liệu’ (Tên môn học = ’Cơ sở dữ liệu’). Kết quả sắp xếp theo điểm giảm dần. 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ó 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ó số tín chỉ nhiều nhất hoặc Cho biết sinh viên có tuổi lớn nhất,. ) 5. Cho biết mã sinh viên, họ tên và số lượng môn học mà sinh viên đó đã tham gia thi. (dùng GROUP BY) 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 học trên 5? (đk trên nhóm HAVING) 7. Cho biết sinh viên học nhiều môn nhất? (đk HAVING>= all) 8. (DSQH+SQL, phép giao): cho biết sv học cả 2 môn ‘Co So Du Lieu’ và ‘Toan Roi Rac’ 9. (DSQH+SQL, phép trừ): cho biết những sv không học môn ‘Toan Roi Rac’ Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 4 ĐÁP ÁN ĐỀ TỔNG HỢP Câu 1: (1 điểm)  Nội dung: 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 đó. l  LOP, sv  SINHVIEN sao cho sv.Lop = l.MaLop l.Siso = Count (sv.MaSV) Cuối sv Cuối l Hoặc viết khác: l  LOP, l.Siso = Count(sv  SINHVIEN  sv.Lop = l.Malop) (sv.MaSV) Cuối l hoặc cách khác: l.SiSo = Count(sv  SINHVIEN  sv.Lop = l.Malop) (*)  Bối cảnh: LOP, SINHVIEN  Bảng tầm ảnh hưởng: Thêm Xóa Sửa LOP + - + (SiSo) SINHVIEN + + + (Lop) Câu 2: (0.75 điểm) ((LOP:TenLop=‘K1-01’) SINHVIEN (KETQUA:Ketqua=’Đậu’) aaa MONHOC) [MaSV, HoTenSV, TenMH, LanThi, Diem] Câu 3: (0.75 điểm) (kết giữa các bảng, chọn, chiếu đơn giản) SELECT sv.MaSV, HoTenSV, Diem, KetQua FROM SINHVIEN sv, MONHOC m, KETQUA k WHERE sv.MaSV = k.MaSV AND k.MaMH = m.MaMH AND m.TenMH = ‘Cơ sở dữ liệu’ and k.LanThi=1 ORDER BY Diem DESC; 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) SELECT TenLop, SiSo FROM LOP WHERE SiSo = ( SELECT Max (SiSo) FROM Lop) MaLop=Lop MaSV MaMH Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 5 Câu 5: (0.5 điểm) (dùng GROUP BY) SELECT sv.MaSV, HoTenSV, count(distinct k.MaMH) as SoMonDaHoc FROM SINHVIEN sv, KETQUA k WHERE sv.MaSV = k.MaSV GROUP BY sv.MaSV, HoTenSV MỞ RỘNG 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 học trên 5? (đk trên nhóm HAVING) SELECT sv.MaSV, HoTenSV, count(distinct k.MaMH) as SoMonDaHoc FROM SINHVIEN sv, KETQUA k WHERE sv.MaSV = k.MaSV GROUP BY sv.MaSV, HoTenSV HAVING count(distinct k.MaMH) >=5 Câu 7: Cho biết sinh viên học nhiều môn nhất? (đk HAVING>= all) SELECT sv.MaSV, HoTenSV, count(distinct k.MaMH) as SoMonDaHoc FROM SINHVIEN sv, KETQUA k WHERE sv.MaSV = k.MaSV GROUP BY sv.MaSV, HoTenSV HAVING count(distinct k.MaMH) >= All (SELECT count(distinct k.MaMH) FROM SINHVIEN sv, KETQUA k WHERE sv.MaSV = k.MaSV GROUP BY sv.MaSV, HoTenSV ) 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 Rac’ ((MONHOC: TenMH= ‘Co So Du Lieu’) KETQUA)[Masv] a aa(((MONHOC: TenMH= ‘Toan Roi Rac’) KETQUA)[Masv] Select masv From MONHOC, KETQUA Where tenmh=‘Co So Du Lieu’ and monhoc.mamh=ketqua.mamh and Masv IN (Select masv From MONHOC, KETQUA Where tenmh=‘Toan Roi Rac’ and monhoc.mamh=ketqua.mamh and ) Câu 9: (DSQH+SQL, phép trừ): cho biết những sv không học môn ‘Toan Roi Rac’ SINHVIEN[MaSV]-((MONHOC:TenMH=‘Toanroirac’) KETQUA)[Masv] Select masv, hoten From SINHVIEN Where Masv NOT IN (Select masv from monhoc, ketqua Where tenmh=‘Toan Roi Rac’ and monhoc.mamh=ketqua.mamh and )  MaMH MaMH MaMH Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 6 Đề Mở Rộng: Đề 1: Cho lược đồ cơ sở dữ liệu Quản lí đề tài tốt nghiệp như sau: KHOA(MaKhoa, TenKhoa, TruongKhoa, TongsoSV) 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, trưởng khoa và tổng số sinh viên thuộc khoa. SINHVIEN(MaSV, HoTenSV, NgaySinh, Gioitinh, DiaChi, MaKhoa) 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, 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 (MaKhoa). GIANGVIEN(MaGV,HoTenGV, NgaySinh, DiaChi, MaKhoa) 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 khác, tên giảng viên, ngày sinh, giới tính (Nam/Nữ), địa chỉ, khoa trực thuộc. DETAI(MaDeTai, LinhVuc, Nam, MaKhoa) Tân từ: mỗi đề tài tốt nhghiệp có một mã số (MaDeTai) phân biệt với các đề tài 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 ngữ,...), năm thực hiện đề tài (Nam), giảng viên hướng dẫn đề tài ( MaGV). HUONGDAN(MaDeTai,MaSV,Diemso) 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 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 Yêu cầu: 1. Hãy phát biểu một cách chặt chẽ (nội dung, bối cảnh, tầm ảnh hưởng) RBTV "Tổng số sinh viên của khoa bằng số lượng sinh viên của khoa đó". Nội dung: Mọi sv Thuộc Bảng sinh viên(tồn tại U Thuộc vào bảng Khoa(t.makhoa= U.makhoa)). Bối cảnh: Bảng sinhVien, Bảng khoa. Tầm ảnh Hưởng RBTV ........ Thêm......... Xóa........... Sửa SinhVien......-................+.............- Khoa............+............... +.............- Thực hiên các câu truy vấn sau bằng ngôn ngữ đại số quan hệ: 2. Liệt kê danh sách các sinh viên có điểm số đè tài tốt nghiệp trên 8. Thông tin liệt kê bao gồm (MaSV, HoTenSV, TenDeTai, DiemSo). Thực hiện các câu truy vấn sau bằng ngôn ngữ SQL: 3. Với các sinh viên thuộc khoa "Công nghệ thông tin" (tên khoa là "Công nghệ 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 giáo viên hướng dẫn (HoTenSV, TenDeTai, HoTenGV), sắp thứ tự theo HoTenSV. select s.masv,s.hoatensv,d.linhvuc,h.diemso from SINHVIEN s,DETAI d,HUONGDAN h where h.diemso >=8 orderby h.diemso desc 4. Cho biết tên giảng viên không hướng dẫn đề tài nào trong năm 2006. 5. Cho biết tên sinh viên có điểm số tốt nghiệp cao nhất trong năm 2006. Bộ Đề Thi Tổng Hợp - Môn Cơ Sở Dữ Liệu amittkduong@gmail.com – k2cn4.n-stars.org - 4rum K2CN4 7 Đề 1: Cho lược đồ CSDL quản lí bán hàng KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH) Tân từ: mỗi kháh hàng sẽ có một mã số để phân biệt với nhau, cần lưu trữ họ tên, địa chỉ, số điện thoại, ngày sinh SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) Tân từ: Mỗi sản phẩm có một số, một tên gọi, Đơn vị tính, nước sản xuất và một giá bán. HOADON (SOHD, MASP, SL) Tân từ: Mỗi lần mua hàng sẽ ghi nhận số hóa đơn, ngày mua, trị giá của hóa đơn và khách hàng nào mua. CTHD (SOHD, MASP, SL) Tân từ: Diễn giải chi tiết trong hóa đơn gồm có những sản phẩm gì với số lượng là bao nhiêu. Phần 1: Phát biểu các câu truy vấn sau bằng đại số quan hệ( câu a, b) và SQL (câu b,c): a. In ra danh sách các sản phẩm ( mã sản phẩm, tên sản phẩm) được bán ra trong ngày 10/10/2005. b. In ra danh sách các sản phẩm ( mã sản phẩm, tên sản phẩm) có giá từ 10.000 đến 50.000 do Trung Quốc sản xuất. c. Tìm khách hàng (MAKH, HOTEN) mua hóa đơn có trị giá ca nhất Phần 2: Mô tả chặt chẽ các ràng buộc sau: a. Trị giá của một hóa đơn phải bằng tổng tiền của các chi tiết( sl*gia) thuộc hóa đơn đó. Đề 2: Cho lược đồ cơ sở dữ liệu Quản lí đề tài tốt nghiệp như sau: KHOA(MaKH, TenKH) Tân từ: Mỗi khoa có các thông tin: Mã khoa, Tên khoa. MON (MaMH, TenMH, SoTiet) Tân từ: Danh mục các môn học gồm có: Mã môn học, Tên môn học, Số tiết. SINHVIEN (MaSV, HoSV, TenSV, Phai, Ngaysinh, NoiSinh,MaKH, HocBong) Tân từ: Mỗi sinh viên cần lưu trữ các thông tin: Mã sinh viên. Họ tên sinh viên, Giới tính, Ngày sinh, Nơi sinh, Học bổng và Mã khoa mà sinh viên học. KETQUA (MaSV, MaKH, Diem) Tân từ: Lưu trữ thông tin điểm thi các môn học của sinh viên. Câu 1: Phát biểu các câu truy vấn sau bằng đại số liên hệ: a. Cho biết các môn học có số tiết lớn hơn 40 và nhỏ hơn 60, gồm các thông tin: Mã môn học, Tên môn học, Số tiết. b. In ra danh sách các sinh viên (Mã sinh viên) chưa thi môn cơ sở dữ liệu. Câu 2: Phát biểu các truy vấn sau bằng ngôn ngữ SQL: a. Liệt kê danh sách các nam sinh viên khoa Công nghệ thông tin sinh sau năm 1989, gồm các thông tin: Mã sinh viên, Họ sinh viên, Tên sinh viên, Phái, Ngày sinh, Tên khoa. b. Thống kê tổng số môn thi của từng sinh viên, gồm các thông tin: Họ sinh viên, Tên sinh viên, Tên khoa, Tổng số môn thi. Câu 3: Mô tả chặt chẽ các ràng buộc sau: Mỗi khoa có tối đa 150 sinh viên theo học