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.PhongDiadiem=”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.
LMaThoFcount(MaCV)asSoCV(CHITIET_HD)
TenTho(SoCVTho(L)|X|SoCVTho.SoCV==MaxSCV.MaxCVMaxSoCV(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.
LMaThoFsum(TriGiaCV)asTongTriGia(CHITIET_HD) TenTho(TongTGTho(L)|X|
TongTGTho.TongTriGia==MaxTongTG.MaxTGMaxTongTG(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)
Chưa có bình luận nào. Hãy là người đầu tiên!