Đề thi giữa kì môn hệ quản trị cơ sở dữ liệu -Trường Đại học Hoa Sen

Mã đề _( A )_
ĐỀ THI GIỮA HỌC KỲ
HỌC KỲ 02, NĂM HỌC 2024 - 2025
TÊN HỌC PHẦN THI : THIẾT KẾ VÀ QUẢN TRỊ CƠ SỞ DỮ LIỆU MSHP : IT209DV01 Thời lượng thi
: 90 phút (không kể thời gian phát đề) NỘI DUNG ĐỀ THI
Cho lược đồ cơ sở dữ liệu Xe(maxe, tenxe) Khachhang(makhach, hoten)
Hopdongthuexe(mahopdong, ngaythue, makhach). Makhach là khóa ngoại tham chiếu đến khachhang(makhach
Chitiethopdong(mahopdong, maxe, ngaynhan, ngaytra, gia thue). Mahopdong là khóa ngoại tham
chiếu đến hopdongthuexe(mahopdong), maxe la khóa ngoại tham chiếu đến xe(maxe) xe Maxe tenxe X01 Toyota Vios X02 Toyota Camry X03 Hyundai i10 2024 X04 Hyundai Venue 2023 X05 Ford Everest khachhang makhach hoten KH01 Bùi Thu Hà KH02 Nguyễn Hải Hà hopdongthuexe mahopdong ngaythue makhach HD0001 1/2/2025 KH01 HD0002 7/3/2025 KH02 Chitiethopdong mahopdong maxe ngaynhan ngaytra giathue HD0001 X01 3/2/2025 6/2/2025 3000000 HD0001 X02 3/2/2025 6/2/2025 3000000 HD0001 X03 4/2/2025 6/2/2025 2000000 HD0002 X04 10/3/2025 31/3/2025 15000000 HD0002 X01 8/3/2025 9/3/2025 1000000
I. PHẦN TỰ LUẬN (10 điểm)
Câu 1: (0.5 điểm) Tạo database
Câu 2: (1.5 điểm) Tạo table Trang 1 / 7
Câu 3: (1.5 điểm) Tạo ràng buộc khóa chính, khóa ngoại Alter table hopdongthuexe Add constraint FK_makhach
Foreign key ( makhach ) references khachhang ( makhach ) Alter table chitiethopdong Add constraint FK_mahopdong
Foreign key ( mahopdong ) references hopdongthuexe ( mahopdong ) Alter table chitiethopdong Add constraint maxe
Foreign key ( maxe ) references xe ( maxe )
Câu 4: (0.5 điểm) Tạo ràng buộc default: Cột ngaythue trên table hopdongthuexe có giá trị mặc
định là ngày hiện hành Alter table hopdongthuexe Add constraint DF_ngaythue,
Default getdate () for ngaythue;
Câu 5:(0.5 điểm) Tạo ràng buộc check: Maxe trên table xe: ký tự đầu là X, 2 ký tự sau là ký tự số Alter table xe Add constraint Chk_maxe Check (
Substring ( maxe,1,1 ) = ‘X’ and
substring ( maxe,2,1 ) between ‘0’ and ‘9’ and
substring ( maxe,3,1 ) between ‘0’ and ‘9’ and )
Câu 6:(0.5 điểm) Nhập dữ liệu Insert into ( maxe, tenxe )
Values ( ‘X01’, ‘Toyota Vios’ )
Câu 7: (5 điểm) Viết truy vấn
a. Khách hàng Nguyễn Hải Hà đã thuê những xe có tên xe là gì Select k.hoten, x.tenxe From khachhang k
join hopdongthuexe h on k.makhach = h.makhach
join Chitiethopdong c = h.mahopdong = c.mahopdong join xe x on c.maxe = x.maxe
Where hoten = n’Nguyen Hai Ha’;
b. Tìm họ tên khách hàng và tên xe họ đã thuê Trang 2 / 7 Select k.hoten, x.tenxe From khachhang k
Join hopdongthuexe h on k.makhach = h.makhach
Join chitiethopdong c on h.mahopdong = c.mahopdong Join xe x on x.maxe = c.maxe
c. Tìm mã xe, tên xe của những xe có tên chứa chữ “Toyota” Select x.maxe, x.tenxe From xe x
Where x.tenxe = ‘%Toyota%’
d. Tìm tổng tiền của từng hợp đồng
Select c.mahopdong, Sum (c.giathue) as tongtien From chitiethopdong c Group by C.chitiethopdong;
e. Tìm tên xe có tổng tiền thuê cao nhất Câu 8: Viết các script:
a) Hủy các hợp đồng thuê xe vào ngày 30/2, 31/2 Delete * From chitiethopdong
Where ngaythue in ( ‘30/2’, ‘31/2’ )
b) Ghép các hợp đồng thuê xe cùng 1 ngày, 1 khách thành 1 hợp đồng nếu tổng giá thuê
của các hợp đồng ghép < 100 triệu
c) Tách hợp đồng thuê xe thành nhiều hợp đồng thuê xe đối với hợp đồng thuê nhiều loại
xe. Mỗi hợp đồng tách chỉ gồm 1 loại xe
Insert into ( mahopdong, ngaythue, makhach ) Values
(‘HD001-1’, ‘1/2/2025’, ‘KH01’),
(‘HD001-2’, ‘1/2/2025’, ‘KH01’),
(‘HD001-3’, ‘1/2/2025’, ‘KH01’); Update chitiethopdong Set mahopdong = ‘HD001-1’
Where mahopdong = ‘HD001’ and maxe = ‘X01’ Update chitiethopdong Set mahopdong = ‘HD001-2’
Where mahopdong = ‘HD001’ and maxe = ‘X01’ Update chitiethopdong Set mahopdong = ‘HD001-3’
Where mahopdong = ‘HD001’ and maxe = ‘X01’ Delete from chitiethopdong Where mahopdong = ‘HD001’ Câu 9: Tạo các proc
a) Proc1: tham số vào là mahopdong. Tính và xuất ra tham số ra: Tổng tiền thuê, số
lượng xe thuê của hợp đồng này Create proc proc1 @mahopdong int, @tongtienthue money output,
@soluongthue varchar ( 50 ) output; As Begin Select @soluongthue = count (*),
@tongtienthue = sum (c.giathue ) From Chitiethopdong c
Where @mahopdong = c.mahopdong End
b) Proc2: tham số vào là tên xe, tháng. Tính và xuất ra tham số ra: tổng số tiền cho thuê của xe có tên này Create proc proc2 @tenxe varchar ( 50 ), @tongtienthue money output As Begin Select
@tongtienthue = sum ( c.giathue ) From
Xe x join chitiethopdong c on x.maxe = c.maxe Where @tenxe = x.tenxe; Declare @tongtienthue money;
Exec proc2 @tenxe = N’Toyota Vios’, @tongtienthue output; Trang 4 / 7
c) Proc3: tham số vào là makhach, soluongxethue, songaythue, ngaynhan. Tạo
hopdongthuexe mới với makhach, ngaythue là ngày hiên tại. Chọn ra các xe có thể cho
thuê đưa vào chi tiết đồng.
d) Proc4: tham số vào là makhach. Tính và xuất ra tổng tiền thuê xe của khách này Create proc proc4 @makhach varchar ( 50 ), @sotienthue money output As Begin Select
@sotienthue = sum ( c.giathue )
From chitiethopdong c join khachhang k on c.makhach = k.makhach Where k.makhach = @makhach End
e) Proc6: tham số vào là nam. Xuất ra danh sách tên xe với tiêu chí: xe này được thuê vào
tất cả các tháng trong năm, mỗi tháng xe xe được cho thuê tối thiểu 10 triệu. Câu 10: Trigger
f) Tổng tiền của 1 hợp đồng không quá 100 triệu. Create trigger kttien On chitiethopdong After insert, update as begin
declare @mahopdong varchar ( 50 ); declare @tongtien money;
select top 1 @mahopdong = mahopdong from inserted;
select @tongtien = sum ( giathue ) from chitiethopdong where mahopdong = @mahopdong; IF @tongtien > 100000000 BEGIN
RAISERROR(N'Tổng tiền thuê của hợp đồng vượt quá 100 triệu.', 16, 1); ROLLBACK; END END;
g) Khách hàng Nguyễn Hải Hà không bao giờ thuê xe Toyota Create trigger ktthuexe On chitiethopdong Trang 5 / 7 After insert, update As Begin If exists ( Select 1 From inserted i
Join hopdongthuexe h on i.mahopdong = h.mahopdong
Join khachhang k on k.makhach = h.makhach Join xe x on x.maxe = i.maxe
Where k.hoten = m’Nguyen Hai Ha’
And x.tenxe like ‘%Toyota%’ ) Begin
RAISERROR(N'Khách hàng Nguyễn Hải Hà không được thuê xe Toyota.', 16, 1); ROLLBACK; END END;r
h) 1 xe không được cho thuê trên 2 hợp đồng vào cùng 1 thời điểm Create trigger ktxe On chitiethopdong After insert, update As Begin If exists ( Select 1 From insert i
Join hopdongthuexe h on i.mahopdong = h.mahopdong
Where i.mahopdong <> c.mahopdong(
(i.ngaynhan BETWEEN c.ngaynhan AND c.ngaytra) OR
(i.ngaytra BETWEEN c.ngaynhan AND c.ngaytra) OR
(c.ngaynhan BETWEEN i.ngaynhan AND i.ngaytra) ) ) BEGIN
RAISERROR(N'1 xe không được cho thuê trên 2 hợp đồng vào cùng 1 thời điểm.', 16, 1); ROLLBACK; END END;
i) Ngày nhận xe < ngày trả xe
j) Xe Toyota không cho thuê với giá dưới 500 1 ngày
k) Xe Huyndai cho thuê tối thiểu 2 ngày trở lên Câu 11: Security
l) Tạo role ChuCuaHang gán quyền xem tất cả các table Create role quanly; Trang 6 / 7 Grant select
m) Tạo role Khach gán quyền xem tất cả các table hopdong, chitiethopdong
n) Tạo role Nhanvien gán quyền xem tất cả các table, gán quyền insert, update, delete trên tất cả các table
o) Tạo user VTTHA, gán user này là member của role ChuShop. Kiểm tra truy cập của user này.
p) Tạo user NguyenAnh, gán user này là member của role nhanvien. Kiểm tra truy cập của user này.
q) Tạo user HoangLan, gán user này là member của role khach. Kiểm tra truy cập của user này --HẾT-- Trang 7 / 7