PhamVoNhatKha 23110235 Baitapchuong 3&4 - Cơ sở dữ liệu (HCMUTE - PhamVoNhatKha_23110235_Chương03)

PKPham Vo Nhat Kha
0 lượt tải
9
0
Có lời giải

Nội dung tài liệu

Trang 1

BÀI TẬP CHƯƠNG 3 + CHƯƠNG 4 Bài 1: Cho lược đồ CSDL của một công ty như sau: Ví dụ một thể hiện của CSDL trên Hãy viết biểu thức đại số quan hệ thực hiện những yêu cầu sau: 1. Hãy cho biết tên các dự án mà nhân viên có mã ‘NV01’ tham gia TenDA(MaNV=”NV01”(PHANCONG)) |X| DUAN)

Trang 2

2. Tính tổng thời gian tham gia các dự án của mỗi nhân viên TenNV,Thoigian(MaNVF sum(Thoigian) as Thoigian (PHANCONG) |X| NHANVIEN) 3. Cho biết họ tên các nhân viên chưa tham gia dự án nào MaNV(NHANVIEN)-MANV(PHANCONG))|X|TenNV,MaNV(NHANVIEN) TenNV((MaNV(NHANVIEN)-MANV(PHANCONG)) |X| NHANVIEN) a. Tìm ngày sinh và địa chỉ của nhân viên “Nguyễn Bảo Hùng” NgSinh,DChi,TenNV(TenNV=”Nguyễn Bảo Hùng”(NHANVIEN)) b. Tìm tên và địa chỉ của các nhân viên làm việc cho phòng “Nghiên cứu” TenNV,DChi(TenPB=”Nghiêncứu”(NHANVIEN|X|NHANVIEN.Phong=PHONGBAN.MaPBPHONGBAN)) c. Với mỗi dự án được triển khai ở Gò Vấp, cho biết mã dự án, mã phòng quản lý và họ tên, ngày sinh trưởng phòng của phòng đó MaDA,MaPB,HoNV,Tenlot,TenNV,NgSinh(Diadiem=”Gò Vấp”(PHONGBAN|X|PHONGBAN.MaPB=DUAN.PhongDUAN) |X| PHONGBAN.TrPhong=NHANVIEN.MaNV(NHANVIEN)) MaDA,MaPB,HoNV,Tenlot,TenNV,NgSinh(PHONGBAN|X| PHONGBAN.MaPB=DUAN.PhongDiadiem=”GòVấp”(DUAN) |X| PHONGBAN.TrPhong=NHANVIEN.MaNV(NHANVIEN)) d. Với mỗi nhân viên, cho biết họ tên nhân viên và họ tên của người quản lý nhân viên đó HoNV,TenNV,HoQL,TenQL(HoNV,TenNV,MaNV,MaNQL(NHANVIEN)|X| NHANVIEN.MaNV=QUANLY.MaQL QUANLY(HoQL, TenQL, MaQL) (HoNV,TenNV,MaNV(NHANVIEN))) e. Cho biết mã nhân viên, họ và tên của các nhân viên của phòng “Nghiên cứu” có mức lương từ 30000 đến 50000 MaNV,HoNV,TenNV(MaNV,HoNV,TenNV,Phong,Luong(Luong>=30000andLuong<=50000)|X| NHANVIEN.Phong=PHONGBAN.MaPHG(TenPHG=”nghiên cứu”(PHONGBAN)) f. Cho biết mã nhân viên, họ tên nhân viên và mã dự án, tên dự án của các dự án mà họ tham gia HoNV,TenNV,MaDA,TenDA((PHANCONG |X| NHANVIEN) |X| DUAN) g. Cho biết mã nhân viên, họ tên của những người không có người quản lý HoNV,TenNV(MANQL=null(NHANVIEN)) h. Cho biết họ tên của các trưởng phòng có thân nhân HoNV((PHONGBAN |X|PHONGBAN.TrPhong = THANHNHAN.MaNV THANNHAN ) |X| NHANVIEN) i. Tính tổng lương nhân viên, lương cao nhất, lương thấp nhất và mức lương trung bình Fsum(Luong),max(Luong),min(Luong),avg(Luong)(NhanVien) j. Cho biết tổng số nhân viên và mức lương trung bình của phòng “Nghiên cứu” Fcount(*),avg(Luong)(TenPB=”Nghiêncứu”(PHONGBAN) |X| PHONGBAN.MaPB = NHANVIEN.PHONG (NHANVIEN)) k. Với mỗi phòng, cho biết mã phòng, số lượng nhân viên và mức lương trung bình MaPBFcount(MaNV),avg(Luong)(PHONGBAN) |X| PHONGBAN.MaPB = NHANVIEN.PHONG (NHANVIEN) l. Với mỗi dự án, cho biết mã dự án, tên dự án và tổng số nhân viên tham gia MaDA,TenDA Fcount(MaNV)((PHANCONG |X| (NHANVIEN) |X| MaDA,TenDA(DUAN)) m. Với mỗi dự án có nhiều hơn 2 nhân viên tham gia, cho biết mã dự án, tên dự án và số lượng nhân viên tham gia

Trang 3

SoNV>=2(MaDA,TenDAFcount(MaNV) as SoNV((PHANCONG |X| (NHANVIEN) |X| MaDA,TenDA(DUAN))) n. Với mỗi dự án, cho biết mã số dự án, tên dự án và số lượng nhân viên phòng số 5 tham gia SoNV>=2(MaDA,TenDA F count(MaNV) as SoNV((PHANCONG |X| (NHANVIEN) |X| MaDA,TenDA(DUAN))) Phong=5(MaDA,TenDA,PhongFcount(MaNV)((PHANCONG |X| (NHANVIEN) |X| MaDA,TenDA(DUAN))) o. Với mỗi phòng có nhiều hơn 2 nhân viên, cho biết mã phòng và số lượng nhân viên có lương lớn hơn 25000 MaPB,SoNV(SoNV>2((PhongFcount(MaNV) as SoNV(Luong>2500(NHANVIEN)))|X|PHONGBAN) p. Với mỗi phòng có mức lương trung bình lớn hơn 30000, cho biết mã phòng, tên phòng, số lượng nhân viên của phòng đó MaPB,TenPB,SoNV(Luong>3000(PhongFavg(Luong)asLuongTB, count(MaNV) as SoNV(NHANVIEN))|X|PHONGBAN) q. Với mỗi phòng có mức lương trung bình lớn hơn 30000, cho biết mã phòng, tên phòng, số lượng nhân viên nam của phòng đó MaPB, TenPB, SoNV(Luong>3000(PhongFavg(Luong) as LuongTB, count(MaNV) as SoNV(Phai==’Nam’(NHANVIEN)))|X| PHONGBAN)

Trang 4

Bài 2: Cho lược đồ CSDL của một thư viện như sau: Hãy viết biểu thức đại số quan hệ thực hiện các yêu cầu sau: a. Cho biết Địa chỉ và số điện thoại của Nhà xuất bản “Addison Wesley” ĐịaChỉ, SốĐT(TênNXB==”Addison Wesley”(NXB)) b. Cho biết mã sách và Tựa sách của những cuốn sách được xuất bản bởi nhà xuất bản “Addison Wesley” MãSách, Tựa(TenNXB==”Addison Wesley”(NXB)|X|ĐầuSách) c. Cho biết mã sách và Tựa sách của những cuốn sách có tác giả là “Hemingway” MãSách, Tựa(TênTácGiả==”Hemingway”(TácGiả)|X|ĐầuSách) d. Với mỗi đầu sách, cho biết tựa và số lượng cuốn sách mà thư viện đang sở hữu Tựa (MãSách F count(MãCuốn) as SLCuốn ((Đầu Sách)|X|(CuốnSách))) e. Với mỗi độc giả, hãy cho biết Tên, địa chỉ và số lượng cuốn sách mà người đó đã mượn  Tên,ĐịaChỉ((ĐộcGỉa)|X|(MãĐG F count(MãCuốn) as SLCuốn ((Mượn)|X|(ĐộcGỉa)))) f. Cho biết mã cuốn, tựa sách và vị trí của những cuốn sách được xuất bản bởi nhà xuất bản “Addison Wesley” Tựa, MãCuốn,Vịtrí((TênNXB = “Addison Wesley”(NXB))|X| ĐầuSách |X| CuốnSách) g. Với mỗi đầu sách, hãy cho biết Tên nhà xuất bản và số lượng tác giả  TênNXB,SốLượng((Mã Sách F count(Tên Tác giả) as SốLượng(Q)) |X| NXB) h. Hãy cho biết Tên, địa chỉ, số điện thoại của những độc giả đã mượn từ 5 cuốn sách trở lên Tên,ĐịaChỉ,SốĐT (SLCuốn>=5 (MãĐG F count(MãCuốn) as SLCuốn ((Mượn)|X|(ĐộcGỉa))) i. Cho biết mã NXB, tên NXB và số lượng đầu sách của NXB đó trong CSDL MãNXB, TênNXB, SốLượng((MãNXBFcount(Mã Sách) as SốLượng(Đầu Sách))|X| NXB) j. Cho biết mã NXB, tên NXB và địa chỉ của những NXB có từ 100 đầu sách trở lên

Trang 5

MãNXB,TênNXB,ĐịaChỉ(SốLượngĐầuSách>=100((MãNXBFcount(MãSách)asSốLượng(ĐầuSách))|X| NXB)) k. Cho biết mã NXB, tên NXB, và số lượng tác giả đã hợp tác với NXB đó MãNXB, TênNXB F count(Tên Tác Giả) ( MãNXB, TênNXB, TênTácGiả ( ( TácGiả |x| ĐầuSách ) |X| NXB ) ) l. Tựa và số lượng tác giả của những cuốn sách có tác giả là “Hemingway” mà độc giả “Nguyễn Văn A” đã từng mượn Tựa F count(TênTácGiả)( (Tựa (TênTácGiả=”Hemingway” ^ TênĐG = “Nguyễn Văn A” ( ((TácGiả |x| ĐầuSách ) |X| CuốnSách ) ) |X| Mượn) |X| ĐộcGiả ) ) ) |X| ( ((TácGiả |x| ĐầuSách ) | X| CuốnSách ) ) |X| Mượn) X| ĐộcGiả ) ) ) ) Bài tập 3. Cho CSDL gồm các quan hệ sau : NV( MSNV , TEN , MSCOQUAN , CONGVIEC , LUONG) COQUAN( MSCOQUAN , TENCOQUAN , DIACHI) Biểu diễn bằng ngôn ngữ SQL và đại số quan hệ các yêu cầu sau : a. Tìm tên những nhân viên ở cơ quan có mã số là 50 TEN(MSCOQUAN==50(COQUAN)|X|NV) b. Tìm mã số tất cả các cơ quan từ quan hệ NV MSCOQUAN(NV) c. Tìm tên các nhân viên ở cơ quan có mã số là 15,20,25 TEN(MSCOQUAN==15 or MSCOQUAN==20 or MSCOQUAN==25(NV)) d. Tìm tên những người làm việc ở Đồ Sơn TEN(DIACHI==Đồ Sơn(COQUAN)|X|NV) Bài tập 4: Cho một lược đồ CSDL dùng để quản lý hoạt động sửa chữa và bảo trì xe của một ga ra như sau:  THO(MaTho, TenTho, Nhom, NhomTruong) Tân từ: Mỗi người thợ đều có một mã so (MaTho) để nhận diện, một tên (TenTho) và chỉ thuộc một nhóm. Nhóm trưởng của mỗi nhóm là một trong những người thợ của nhóm đó. MGT(MaTho) = MGT(NhomTruong)  CONGVIEC(MaCV, NoiDungCV) Tân từ:

Trang 6

Dịch vụ sửa xe được chia thành nhiều công việc để dễ dàng tính toán chi phí với khách hàng. Mỗi công việc đều có một mã (MaCV) và thuộc tính “NoiDungCV” mô tả nội dung của công việc.  HOPDONG(SoHD, NgayHD, MaKH, SoXe, TriGiaHD, NgayGiaoDK, NgayNgThu) Tân từ: Mỗi hợp đồng sửa chữa đều có một mã số phân biệt. NgayHD là ngày ký hợp đồng sửa xe với khách hàng là chủ xe (MaKH). SoXe là số đăng bộ của xe đem đến sửa. Một khách hàng có thể ký nhiều hợp đồng sửa chữa nhiều xe khác nhau hoặc sửa chữa nhiều lần cho cùng một xe nhung trong cung một ngày, những công việc sửa chữa cho một xe chỉ ký hợp đồng một lần. TriGiaHD là tổng trị giá của hóa đơn. NgayGiaoDK là ngày dự kiến phải giao xe cho khách. NgayNgThu là ngày nghiệm thu thật sự sau khi đã sửa chữa xong để thanh lý hợp đồng.  KHACHHANG(MaKH, TenKH, DiaChi, DienThoai) Tân từ: Mỗi khách hàng có một MAKH để phân biệt, một tên (TenKH), một địa chỉ (DiaChi) và một số điện thoại để theo dõi công nợ.  CHITIET_HD(SoHD, MaCV, TriGiaCV, MaTho, KhoanTHo) Tân từ: Mỗi hợp đồng sửa xe có thể gồm nhiều công việc, MaCV là mã số của từng công việc, TriGiaCV là chi phí của công việc đã tính toán với khách. Mỗi công việc của hợp đồng được giao cho một người thợ (MaTho) phụ trách. Một người thợ có thể được giao một hoặc nhiều công việc của một hay nhiều hợp đồng khác nhau. KhoanTho là số tiền giao khoán cho người thợ ve công việc sửa chữa tương ứng.  PHIEUTHU(SoPT, NgaylapPT, SoHD, MaKH, HoTen, SoTienThu) Tân từ: Khách hàng (MaKH) có thể thanh toán tiền của một hợp đồng (SoHD) làm nhiều lần trước hoặc sau khi nghiệm thu (trong cùng một ngày hoặc khác ngày). Mỗi lần thanh toán đều có số phiếu thu (SoPT) để phân biệt, NgayLapPT là ngày lập phiếu thu. SoTienThu là số tiền thanh toán của lần thu đó. HoTen là ho tên của người mang tiền đến thanh toán (có thể khác với người đứng ra ký hợp đồng). Yêu cầu:  Xác định các khóa trong các lược đồ quan hệ trên.  THO(MaTho, TenTho, Nhom, NhomTruong) MaTho là khóa chính  CONGVIEC(MaCV, NoiDungCV) MaCV là khóa chính  HOPDONG(SoHD, NgayHD, MaKH, SoXe, TriGiaHD, NgayGiaoDK, NgayNgThu) SoHD là khóa chính, MaKH là khóa ngoại tham chiếu đến KHACHHANG.MaKH

Trang 7

 KHACHHANG(MaKH, TenKH, DiaChi, DienThoai) MaKH là khóa chính  CHITIET_HD(SoHD, MaCV, TriGiaCV, MaTho, KhoanTHo) MaTho là khóa ngoại tham chiếu đến THO.MaTho MaCV là khóa ngoại tham chiếu đến CONGVIEC.MaCV SoHD, MaCV là khóa chính  PHIEUTHU(SoPT, NgaylapPT, SoHD, MaKH, HoTen, SoTienThu) MaKH là khóa ngoại tham chiếu đến KHACHHANG.MaKH SoHD là khóa ngoại tham chiếu đến CHITIET_HD.SoHD SoPT là khóa chính  Phát biểu các RBTV có trong lược đồ CSDL + Tất cả các thuộc tính đều có ràng buộc về miền giá trị. + Các khóa chính có ràng buộc not null và unique. + Ràng buộc về khóa ngoại (tham chiếu): HOPDONG: Có ràng buộc tham chiếu ở thuộc tính MaKH các MaKH trong bảng HOPDONG phải có tồn tại trong bảng KHACHHANG CHITIET_HD: Có ràng buộc tham chiếu ở thuộc tính MaCV và MaTho các giá trị này phải có trong bảng THO và CONGVIEC PHIEUTHU: Có ràng buộc tham chiếu ở thuộc tính SoHD và MaKH các giá trị này phải có tồn tại trong bảng HOPDONG và KHACHHANG  Thực hiện các yêu cầu sau bằng ngôn ngữ SQL: 1. Cho biết danh sách các người thợ hiện không tham gia vào một hợp đồng sửa chữa nào.  MaTho, TenTho( MaTho = null( THO CHITIET_HD) 2. Cho biết danh sách những hợp đồng đã thanh lý nhưng chưa được thanh toán tiền đầy đủ.  SoHD( SoTienThu<TriGiaHD(PHIEUTHU|X|HOPDONG)) 3. Cho biết danh sách những hợp đồng cần phải hoàn tất trước ngày 31/12/2002 NgayGiaoDK<31/12/2022(HOPDONG) 4. Cho biết người thợ nào thực hiện công việc nhiều nhất. LMaThoFcount(MaCV)asSoCV(CHITIET_HD) TenTho(SoCVTho(L)|X|SoCVTho.SoCV==MaxSCV.MaxCVMaxSoCV(Fmax(SoCV) as MaxCV(L)|X|THO) 5. Cho biết người thợ nào có tổng trị giá công việc được giao cao nhất. LMaThoFsum(TriGiaCV)asTongTriGia(CHITIET_HD) TenTho(TongTGTho(L)|X| TongTGTho.TongTriGia==MaxTongTG.MaxTGMaxTongTG(Fmax(TongTriGia) as MaxTG(L)|X|THO) Bài tập 5. Cho một phần CSDL của một trường phổ thông như sau :  GV(MAGV, TENGV, MAMH) Tân từ : Một giáo viên (MAGV) chủ nhiệm một bộ môn duy nhất. Đối với những giáo viên không phải là chủ nhiệm bộ môn thì giá trị của thuộc tính MAMH là null.  MHỌC(MAMH, TENMH, SOTIET) Tân từ : Mỗi môn học có một MAMH duy nhất, một TENMH và một số tiết học của môn học đó.

Trang 8

 BUỔITHI(HKY, NGAY, GIO, PHG, MAMH, TGTHI) Tân từ : Mỗi buổi thi được xác định bởi một HỌC KỲ (HK), một ngày, một giờ và một phòng (PHG). Buổi thi liên quan đến một môn duy nhất và có một thời gian thi (TGTHI) duy nhất.  PC_COI_THI(MAGV, HK, NGAY, GIO, PHG) Tân từ : Một lần phân công coi thi được xác định bởi 1 giáo viên, 1 học kỳ, 1 ngày, 1 giờ và 1 phòng. Ghi chú :  Một giáo viên có thể được phân công gác thi nhiều buổi trong một học kỳ, với điều kiện các buổi thi đó không liên quan đến môn học do giáo viên đó chủ nhiệm.  Nếu số tiết học là 30 thì thời gian thi là 120 phút  Nếu số tiết học là 45 tiết trở lên thì thời gian thi là 150 phút Câu hỏi : 1. Xác định khóa của các lược đồ quan hệ trên. GV(MAGV, TENGV, MAMH) MAGV là khóa chính MHỌC(MAMH, TENMH, SOTIET) MAMH là khóa chính BUỔITHI(HKY, NGAY, GIO, PHG, MAMH, TGTHI) HKY, NGAY, GIO là khóa chính PC_COI_THI(MAGV, HK, NGAY, GIO, PHG) MAGV, NGAY, GIO là khóa chính 2. Phát biểu các RBTV. + Tất cả các thuộc tính đều có ràng buộc về miền giá trị. + Các khóa chính có ràng buộc not null và unique + Ràng buộc về khóa ngoại (tham chiếu): GV: ràng buộc ở thuộc tính MAMH các bộ giá trị phải tồn tại trong bảng MHỌCBUỔITHI: Các bộ giá trị trong thuộc tính MAMH phải tồn tại trong thuộc tính MAMH của bảng MHỌC. PC_COI_THI: Các buổi phân công phải trùng học kì, ngày và giờ với bảng BUỔITHI, MAGV phải tồn tại trong bảng GV 3. Dùng đại số quan hệ và SQL để viết các câu hỏi sau: a. Danh sách các giáo viên dạy các môn học có số tiết từ 45 trở lên MAGV,TENGV(SOTIET >= 45( GV |X| MHỌC )) b. Danh sách giáo viên được phân công gác thi trong học kỳ 1 TENGV ,MAGV( HK = ‘học kỳ 1’(GV|X|PC_COI_THI)) c. Danh sách giáo viên không được phân công gác thi trong học kỳ 1 TENGV ( (MAGV(GV) - ( MAGV ( BUỔI THI |X| PC_COI_THI ) ) ) |X| GV ) d. Cho biết lịch thi môn văn (TENMH = ‘VĂN HỌC’)

Trang 9

NGAY, GIO, PHG( TENMH = ‘VĂN HỌC’ ( MHỌC |X| BUỔITHI )) e. Cho biết các buổi gác thi của các giáo viên chủ nhiệm môn văn (TENMH = ‘VĂN HỌC’). NGAY, GIO, PHG( TENMH = ‘VĂN HỌC’ ( PC_COI_THI |X| BUỔITHI ))

Hiển thị 9 trang đầu. Tải tài liệu để xem đầy đủ.

Bình luận (0)

Đăng nhập để tham gia bình luận

Chưa có bình luận nào. Hãy là người đầu tiên!