· 5 years ago · Jun 16, 2020, 11:18 AM
1-- Y1. YÊU CẦU THIẾT KẾ VÀ CÀI ĐẶT CSDL
2-- - Dựa trên mô tả bài toán, sinh viên xây dựng thiết kế lược đồ CSDL cho bài toán và tiến
3-- hành cài đặt CSDL trên SQL Server thỏa mãn các yêu cầu sau:
4-- 1. Cơ sở dữ liệu phải được đặt tên với định dạng sau QLNHATRO_<Tên đăng nhập
5-- SV>.
6-- 2. Thiết kế CSDL phải đảm bảo:
7-- - Đầy đủ liên kết giữa các bảng
8-- - Kiểu dữ liệu và độ dài trường dữ liệu phải phù hợp, tốn ít dung lượng bộ nhớ và hỗ trợ
9-- Tiếng Việt với các trường cần thiết.
10-- - Có ràng buộc CHECK dữ liệu cho các trường cần thiết.
11-- - Thiết lập thuộc tính NULL/NOT NULL cho các cột một cách phù hợp
12-- Y2. YÊU CẦU VỀ BỘ DỮ LIỆU MẪU
13-- - Sinh viên tiến hành nhập liệu cho các bảng.
14-- - Yêu cầu về số lượng bản ghi:
15-- o Bảng LOAINHA phải có tối thiểu 3 bản ghi
16-- o Các bảng còn lại phải có tối thiểu 10 bản ghi
17-- Lưu ý: Dữ liệu nhập vào các bảng phải có ý nghĩa thực tế, logic, đúng quy định về kiểu
18-- dữ liệu và đảm bảo có thể sử dụng để chạy thử tất cả các yêu cầu bên dưới đều trả về
19-- kết quả.
20
21
22use master
23go
24
25if exists (select *
26from sysdatabases
27where name='QLNT')
28 drop database QLNT
29go
30
31create database QLNT
32go
33
34use QLNT
35go
36
37create table LOAINHA
38(
39 maLN int not null identity(1,1),
40 tenLoai nvarchar(50) not null,
41
42 constraint PK_LN primary key (maLN)
43)
44
45create table QUAN
46(
47 maQ int not null identity(1,1),
48 tenQuan nvarchar(50) not null,
49
50 constraint PK_Q primary key (maQ)
51)
52
53create table NGUOIDUNG
54(
55 maND int not null identity(1,1),
56 tenNguoiDung nvarchar(32) not null,
57 gioiTinh nvarchar(5) not null,
58 dienThoai varchar(15) not null,
59 diaChi nvarchar(55) not null,
60 quan int not null,
61 email varchar(30) not null,
62
63 constraint PK_ND primary key (maND),
64 constraint FK_ND_Q foreign key (quan) references QUAN (maQ)
65)
66
67create table NHATRO
68(
69 maNT int not null identity(1,1),
70 maLoai int not null,
71 dienTich float not null,
72 gia money not null,
73 diaChi nvarchar(55) not null,
74 quan int not null,
75 moTa nvarchar(500),
76 ngayDang datetime not null,
77 chuNha int not null,
78
79 constraint PK_NT primary key (maNT),
80 constraint FK_NT_LN foreign key (maLoai) references LOAINHA (maLN),
81 constraint FK_NT_ND foreign key (chuNha) references NGUOIDUNG (maND),
82 constraint FK_NT_Q foreign key (quan) references QUAN (maQ)
83)
84
85create table HOPDONG
86(
87 maHD int not null identity (1,1),
88 maNguoiDung int not null,
89 maNhaTro int not null,
90 ngayBatDau datetime not null,
91 ngayKetThuc datetime ,
92
93 constraint PK_HD primary key (maHD),
94 constraint FK_HD_ND foreign key (maNguoiDung) references NGUOIDUNG (maND),
95 constraint FK_HD_NT foreign key (maNhaTro) references NHATRO (maNT)
96)
97
98create table DANHGIA
99(
100 maDG int not null identity(1,1),
101 maHopDong int not null,
102 chatLuong bit not null,
103 noiDung nvarchar(500),
104
105 constraint PK_DG primary key (maDG),
106 constraint FK_DG_HD foreign key (maHopDong) references HOPDONG (maHD),
107)
108
109
110insert into LOAINHA
111 (tenLoai)
112values
113 (N'Nhà riêng'),
114 (N'Chung cư'),
115 (N'Phòng trọ khép kín'),
116 (N'Phòng trọ chung chủ'),
117 (N'Ký túc xá');
118
119insert into QUAN
120 (tenQuan)
121values
122 (N'Quận 2'),
123 (N'Quận 3'),
124 (N'Quận 4'),
125 (N'Quận 5'),
126 (N'Quận 6'),
127 (N'Quận 7'),
128 (N'Quận 8'),
129 (N'Quận 9'),
130 (N'Quận 10'),
131 (N'Quận 11'),
132 (N'Quận 12'),
133 (N'Ba Đình'),
134 (N'Bắc Từ Liêm'),
135 (N'Bình Tân'),
136 (N'Bình Thạnh'),
137 (N'Bình Thủy'),
138 (N'Cái Răng'),
139 (N'Cầu Giấy'),
140 (N'Cẩm Lệ'),
141 (N'Dương Kinh'),
142 (N'Đống Đa'),
143 (N'Đồ Sơn'),
144 (N'Gò Vấp'),
145 (N'Hai Bà Trưng'),
146 (N'Hà Đông'),
147 (N'Hải An'),
148 (N'Hải Châu'),
149 (N'Hoàn Kiếm'),
150 (N'Hoàng Mai'),
151 (N'Hồng Bàng'),
152 (N'Kiến An'),
153 (N'Lê Chân'),
154 (N'Liên Chiểu'),
155 (N'Long Biên'),
156 (N'Nam Từ Liêm'),
157 (N'Ngô Quyền'),
158 (N'Ngũ Hành Sơn'),
159 (N'Ninh Kiều'),
160 (N'Ô Môn'),
161 (N'Phú Nhuận'),
162 (N'Sơn Trà'),
163 (N'Tân Bình'),
164 (N'Tân Phú'),
165 (N'Tây Hồ'),
166 (N'Thanh Khê'),
167 (N'Thanh Xuân'),
168 (N'Thốt Nốt'),
169 (N'Thủ Đức');
170
171
172insert into NGUOIDUNG
173 (tenNguoiDung,gioiTinh,dienThoai,diaChi,quan,email)
174values
175 (N'Nguyễn văn Hải', N'Nam', '0987654321', N'Số 1 Trinh Văn Bô', 1, 'mailnumber1@gmail.com'),
176 (N'Hoàng văn Thêm', N'Nam', '0987654322', N'Số 2 Trinh Văn Bô', 3, 'mailnumber11@gmail.com'),
177 (N'Lê thị Thanh', N'Nữ', '0987654323', N'Số 3 Trinh Văn Bô', 5, 'mailnumber21@gmail.com'),
178 (N'Trần mạnh Hùng', N'Nam', '0987654324', N'Số 4 Trinh Văn Bô', 9, 'mailnumber31@gmail.com'),
179 (N'Đặng xuân Đăng', N'Nam', '0987654325', N'Số 5 Trinh Văn Bô', 7, 'mailnumber41@gmail.com'),
180 (N'Nguyên kim Thư', N'Nữ', '0987654326', N'Số 6 Trinh Văn Bô', 11, 'mailnumber51@gmail.com'),
181 (N'Hoàng Ngọc Linh', N'Nữ', '0987654327', N'Số 7 Trinh Văn Bô', 12, 'mailnumber61@gmail.com'),
182 (N'Lê Minh Thành', N'Nam', '0987654328', N'Số 8 Trinh Văn Bô', 6, 'mailnumber71@gmail.com'),
183 (N'Ngô Kiến Huy', N'Nam', '0987654329', N'Số 9 Trinh Văn Bô', 13, 'mailnumber81@gmail.com'),
184 (N'Hà thị Lan', N'Nữ', '0987654331', N'Số 10 Trinh Văn Bô', 21, 'mailnumber91@gmail.com'),
185 (N'Trần huy Anh', N'Nam', '0987654332', N'Số 11 Trinh Văn Bô', 14, 'mailnumber10@gmail.com'),
186 (N'Đinh ngọc Phượng', N'Nữ', '0987654333', N'Số 12 Trinh Văn Bô', 16, 'mailnumber11@gmail.com'),
187 (N'Nguyễn Thủy Tiên', N'Nữ', '0987654334', N'Số 13 Trinh Văn Bô', 8, 'mailnumber12@gmail.com'),
188 (N'Lã thị Nhi', N'Nữ', '0987654335', N'Số 14 Trinh Văn Bô', 10, 'mailnumber13@gmail.com');
189
190insert into NHATRO
191 (maLoai,dienTich,gia,diaChi,quan,moTa,ngayDang,chuNha)
192values
193 (1, 20.1, 1000000, N'Số 1 đường Trịnh Văn Bô', 3, N'Căn số 1', '03/02/2019', 1),
194 (3, 50, 50000000, N'Số 2 đường Trịnh Văn Bô', 6, N'Căn số 2', '03/05/2019', 13),
195 (2, 16, 500000, N'Số 3 đường Trịnh Văn Bô', 1, N'Căn số 3', '03/01/2019', 10),
196 (5, 32, 5000000, N'Số 4 đường Trịnh Văn Bô', 22, N'Căn số 4', '03/04/2019', 7),
197 (4, 29, 4500000, N'Số 5 đường Trịnh Văn Bô', 10, N'Căn số 5', '03/07/2019', 6),
198 (3, 22, 1200000, N'Số 6 đường Trịnh Văn Bô', 15, N'Căn số 6', '03/09/2019', 8),
199 (1, 24.5, 2000000, N'Số 7 đường Trịnh Văn Bô', 10, N'Căn số 7', '03/10/2019', 4),
200 (2, 15, 800000, N'Số 8 đường Trịnh Văn Bô', 3, N'Căn số 8', '03/12/2019', 3),
201 (4, 35, 4000000, N'Số 9 đường Trịnh Văn Bô', 9, N'Căn số 9', '03/3/2019', 9),
202 (5, 60, 7000000, N'Số 10 đường Trịnh Văn Bô', 20, N'Căn số 10', '03/4/2019', 1),
203 (3, 25, 2000000, N'Số 11 đường Trịnh Văn Bô', 7, N'Căn số 11', '03/8/2019', 2);
204
205insert into HOPDONG
206 (maNguoiDung,maNhaTro,ngayBatDau,ngayKetThuc)
207values
208 (1, 6, '03/05/2020', '04/25/2020'),
209 (3, 1, '03/22/2020', '04/25/2020'),
210 (4, 2, '03/10/2020', '04/15/2020'),
211 (1, 3, '04/6/2020', null),
212 (4, 4, '03/23/2020', '04/05/2020'),
213 (7, 5, '03/15/2020', '04/30/2020'),
214 (5, 10, '03/10/2020', '04/28/2020'),
215 (6, 7, '03/6/2020', '04/6/2020'),
216 (8, 5, '03/7/2020', '04/7/2020'),
217 (4, 8, '03/2/2020', '04/23/2020'),
218 (11, 6, '03/3/2020', '04/1/2020');
219
220insert into DANHGIA
221 (maHopDong,chatLuong,noiDung)
222values
223 (1, 1, N'Tốt'),
224 (2, 0, N'Tệ'),
225 (3, 1, N'Tốt'),
226 (4, 1, N'Tốt'),
227 (1, 0, N'Tệ'),
228 (10, 1, N'Tốt'),
229 (6, 1, N'Tốt'),
230 (7, 1, N'Tốt'),
231 (11, 1, N'Tốt'),
232 (8, 0, N'Tệ'),
233 (9, 1, N'Tốt');
234
235
236
237-- PROC
238-- Y3. CÁC YÊU CẦU VỀ CHỨC NĂNG
239-- 1. Thêm thông tin vào các bảng
240-- - Tạo ba Stored Procedure (SP) với các tham số đầu vào phù hợp.
241-- Yêu cầu đối với các SP: Trong mỗi SP phải kiểm tra giá trị các tham số đầu vào. Với
242-- các cột không chấp nhận thuộc tính NULL, nếu các tham số đầu vào tương ứng với
243-- chúng không được truyền giá trị, thì không thực hiện câu lệnh chèn mà in một thông báo
244-- yêu cầu người dùng nhập liệu đầy đủ.
245-- - Với mỗi SP, viết hai lời gọi. Trong đó, một lời gọi thực hiện chèn thành công dữ liệu,
246-- và một lời gọi trả về thông báo lỗi cho người dùng.
247USE QLNT
248SELECT *
249from NGUOIDUNG
250-- o SP thứ nhất thực hiện chèn dữ liệu vào bảng NGUOIDUNG
251IF OBJECT_ID('sp_ADDTONGUOIDUNG') IS NOT NULL DROP PROCEDURE sp_ADDTONGUOIDUNG
252GO
253CREATE PROCEDURE sp_ADDTONGUOIDUNG
254 @tenNguoiDung nvarchar(32) = null,
255 @gioiTinh nvarchar(5)=null,
256 @dienThoai varchar(15) =null,
257 @diaChi nvarchar(55)=null,
258 @quan int=null,
259 @email varchar(30)=null
260AS
261BEGIN
262 IF(@tenNguoiDung =NULL)
263 PRINT N'KHÔNG ĐƯỢC ĐỂ TRỐNG TÊN NGƯỜI DÙNG'
264 ELSE IF(@gioiTinh =NULL)
265 PRINT N'KHÔNG ĐƯỢC ĐỂ TRỐNG GIỚI TÍNH'
266 ELSE IF(@dienThoai =NULL)
267 PRINT N'KHÔNG ĐƯỢC ĐỂ TRỐNG ĐIỆN THOẠI'
268 ELSE IF(@diaChi =NULL)
269 PRINT N'KHÔNG ĐƯỢC ĐỂ TRỐNG ĐỊA CHỈ'
270 ELSE IF(@quan =NULL)
271 PRINT N'KHÔNG ĐƯỢC ĐỂ TRỐNG QUAN'
272 ELSE IF(@email =NULL)
273 PRINT N'KHÔNG ĐƯỢC ĐỂ TRỐNG EMAIL'
274 ELSE IF NOT EXISTS
275 (
276 SELECT *
277 FROM QUAN
278 WHERE maQ = @quan
279 )
280 PRINT N'LỖI KHÓA NGOẠI. KHÔNG TỒN TẠI QUẬN KHÔNG THÊM ĐƯỢC.'
281 ELSE
282 BEGIN
283 BEGIN TRY
284 INSERT INTO NGUOIDUNG
285 (tenNguoiDung,gioiTinh,dienThoai,diaChi,quan,email)
286 VALUES(@tenNguoiDung, @gioiTinh, @dienThoai, @diaChi, @quan, @email)
287 PRINT N'THÊM VAO THÀNH CÔNG'
288 END TRY
289 BEGIN CATCH
290 DECLARE @mes NVARCHAR(2048)= ERROR_MESSAGE(), @sev INT = ERROR_SEVERITY(), @state INT = ERROR_STATE()
291 RAISERROR(@mes,@sev,@state)
292 END CATCH
293 END
294END
295GO
296-- GỌI
297EXEC sp_ADDTONGUOIDUNG N'Nguyễn văn Hải','Nam','0987654321',N'Số 1 Trinh Văn Bô',1,'mailnumber1@gmail.com'
298
299
300SELECT *
301from NHATRO
302-- o SP thứ hai thực hiện chèn dữ liệu vào bảng NHATRO
303IF OBJECT_ID('sp_ADDTONHATRO') IS NOT NULL DROP PROC sp_ADDTONHATRO
304GO
305CREATE PROCEDURE sp_ADDTONHATRO
306 @maLoai int = null,
307 @dienTich float = null,
308 @gia money = null,
309 @diaChi nvarchar(55) = null,
310 @quan int =null,
311 @ngayDang NVARCHAR(20) = null ,
312 @chuNha int = null,
313 @moTa nvarchar(500) = null
314AS
315BEGIN
316 IF @maLoai IS NULL
317 PRINT N'Không để trống mã loại'
318 ELSE IF @dienTich IS NULL
319 PRINT N'Không để trống diện tích'
320 ELSE IF @gia IS NULL
321 PRINT N'Không để trống giá'
322 ELSE IF @diaChi IS NULL
323 PRINT N'Không để trống địa chỉ'
324 ELSE IF @quan IS NULL
325 PRINT N'Không để trống quận'
326 ELSE IF @ngayDang IS NULL
327 PRINT N'Không để trống ngày đăng'
328 ELSE IF @chuNha IS NULL
329 PRINT N'Không để trống chủ nhà'
330 ELSE IF NOT EXISTS
331 (
332 SELECT *
333 FROM QUAN
334 WHERE maQ = @quan
335 )
336 PRINT N'Không có quận'
337 ELSE IF NOT EXISTS
338 (
339 SELECT *
340 FROM LOAINHA
341 WHERE maLN = @maLoai
342 )
343 PRINT N'Không có loại nhà này'
344 ELSE IF NOT EXISTS
345 (
346 SELECT *
347 FROM NGUOIDUNG
348 WHERE maND = @chuNha
349 )
350 PRINT N'Không có chủ nhà này'
351 ELSE
352 BEGIN
353 BEGIN TRY
354 INSERT INTO NHATRO
355 (maLoai,dienTich,gia,diaChi,quan,moTa,ngayDang,chuNha)
356 VALUES
357 (@maLoai, @dienTich, @gia, @diaChi, @quan, @moTa, @ngayDang, @chuNha)
358 PRINT N'THÊM VAO THÀNH CÔNG'
359 END TRY
360 BEGIN CATCH
361 DECLARE @mes NVARCHAR(2048)= ERROR_MESSAGE(), @sev INT = ERROR_SEVERITY(), @state INT = ERROR_STATE()
362 RAISERROR(@mes,@sev,@state)
363 END CATCH
364 END
365END
366GO
367-- gọi
368EXEC sp_ADDTONHATRO 1, 21, 1100000, N'Số 11 đường Trịnh Văn Bô', 7, '03/02/2020', 10, N'Căn số 22'
369
370SELECT *
371from DANHGIA
372-- o SP thứ ba thực hiện chèn dữ liệu vào bảng DANHGIA
373IF OBJECT_ID('sp_ADDTODANHGIA') IS NOT NULL DROP PROC sp_ADDTODANHGIA
374GO
375CREATE PROCEDURE sp_ADDTODANHGIA
376 @maHopDong int = NULL,
377 @chatLuong bit = NULL,
378 @noiDung NVARCHAR(500) = NULL
379AS
380BEGIN
381 IF @maHopDong IS NULL
382 PRINT N'Phải có mã hợp đồng'
383 ELSE IF @chatLuong IS NULL
384 PRINT N'Phải có chất lượng đánh giá'
385 ELSE IF NOT EXISTS
386 (
387 SELECT *
388 FROM HOPDONG
389 WHERE maHD = @maHopDong
390 )
391 PRINT N'Không tồn tại mã hợp đồng'
392 ELSE
393 BEGIN TRY
394 INSERT INTO DANHGIA
395 (maHopDong,chatLuong,noiDung)
396 VALUES
397 (@maHopDong, @chatLuong, @noiDung)
398 PRINT N'Thêm vào thành công'
399 END TRY
400 BEGIN CATCH
401 DECLARE @mes NVARCHAR(2048)= ERROR_MESSAGE(), @sev INT = ERROR_SEVERITY(), @state INT = ERROR_STATE()
402 RAISERROR(@mes,@sev,@state)
403 END CATCH
404
405END
406GO
407-- gọi
408EXEC sp_ADDTODANHGIA 5,1,N'điều kiện tốt'
409
410
411
412-- 2. Truy vấn thông tin
413-- a. Viết một SP với các tham số đầu vào phù hợp. SP thực hiện tìm kiếm thông tin các
414-- phòng trọ thỏa mãn điều kiện tìm kiếm theo: Quận, phạm vi diện tích, phạm vi ngày đăng
415-- tin, khoảng giá tiền, loại hình nhà trọ.
416-- SP này trả về thông tin các phòng trọ, gồm các cột có định dạng sau:
417-- o Cột thứ nhất: có định dạng ‘Cho thuê phòng trọ tại’ + <Địa chỉ phòng trọ>
418-- + <Tên quận/Huyện>
419-- o Cột thứ hai: Hiển thị diện tích phòng trọ dưới định dạng số theo chuẩn Việt Nam +
420-- m2. Ví dụ 30,5 m2
421-- o Cột thứ ba: Hiển thị thông tin giá phòng dưới định dạng số theo định dạng chuẩn
422-- Việt Nam. Ví dụ 1.700.000
423-- o Cột thứ tư: Hiển thị thông tin mô tả của phòng trọ
424-- o Cột thứ năm: Hiển thị ngày đăng tin dưới định dạng chuẩn Việt Nam.
425-- Ví dụ: 27-02-2012
426-- o Cột thứ sáu: Hiển thị thông tin người liên hệ dưới định dạng sau:
427-- ▪ Nếu giới tính là Nam. Hiển thị: A. + tên người liên hệ. Ví dụ A. Thắng
428-- ▪ Nếu giới tính là Nữ. Hiển thị: C. + tên người liên hệ. Ví dụ C. Lan
429-- o Cột thứ bảy: Số điện thoại liên hệ
430-- o Cột thứ tám: Địa chỉ người liên hệ
431-- - Viết hai lời gọi cho SP này
432IF OBJECT_ID('sp_FIND') IS NOT NULL DROP PROC sp_FIND
433GO
434CREATE PROCEDURE sp_FIND
435 @quan NVARCHAR(50) = '%',
436 @minS FLOAT = 0,
437 @maxS FLOAT = 200,
438 @minTimeAdd DATETIME = '1990-01-01',
439 @maxTimAdd DATETIME = NULL,
440 @minGia MONEY = 0,
441 @maxGia MONEY = 100000000,
442 @LOAINHA NVARCHAR(50) = "%"
443AS
444BEGIN
445 IF @maxTimAdd IS NULL SET @maxTimAdd = GETDATE()
446 SELECT NHATRO.diaChi+ ', '+ quan.tenQuan AS N'Cho thuê phòng trọ tại',
447 CAST(dienTich AS decimal(7,3)) AS N'Diện tích (M2)', gia AS N'GIÁ', moTa AS N'Mô Tả',
448 ngayDang AS N'Ngày Đăng Tin', IIF(gioiTinh = 'Nam','A. ','C. ') + tenNguoiDung AS N'Liên Hệ',
449 dienThoai AS N'Số Điện Thoại', NGUOIDUNG.diaChi + ', '+ quan.tenQuan AS N'Địa Chỉ Liên Hệ'
450 FROM QUAN JOIN NHATRO ON QUAN.maQ = NHATRO.quan
451 JOIN LOAINHA ON NHATRO.maLoai=LOAINHA.maLN
452 JOIN NGUOIDUNG ON NHATRO.chuNha=NGUOIDUNG.maND
453 WHERE (QUAN.tenQuan LIKE @quan) AND
454 (NHATRO.gia BETWEEN @minGia AND @maxGia) AND
455 (NHATRO.dienTich BETWEEN @minS AND @maxS) AND
456 (NHATRO.ngayDang BETWEEN @minTimeAdd AND @maxTimAdd)
457 AND LOAINHA.tenLoai LIKE @LOAINHA
458END
459GO
460
461EXEC sp_FIND
462
463
464
465
466
467
468-- b. Viết một hàm có các tham số đầu vào tương ứng với tất cả các cột của bảng
469-- NGUOIDUNG. Hàm này trả về mã người dùng (giá trị của cột khóa chính của bảng
470-- NGUOIDUNG) thỏa mãn các giá trị được truyền vào tham số.
471
472
473
474
475-- c. Viết một hàm có tham số đầu vào là mã nhà trọ (cột khóa chính của bảng
476-- NHATRO). Hàm này trả về tổng số LIKE và DISLIKE của nhà trọ này.
477
478
479
480-- d. Tạo một View lưu thông tin của TOP 10 nhà trọ có số người dùng LIKE nhiều nhất gồm
481-- các thông tin sau:
482-- - Diện tích
483-- - Giá
484-- - Mô tả
485-- - Ngày đăng tin
486-- - Tên người liên hệ
487-- - Địa chỉ
488-- - Điện thoại
489-- - Email
490
491
492
493-- e. Viết một Stored Procedure nhận tham số đầu vào là mã nhà trọ (cột khóa chính của
494-- bảng NHATRO). SP này trả về tập kết quả gồm các thông tin sau:
495-- - Mã nhà trọ
496-- - Tên người đánh giá
497-- - Trạng thái LIKE hay DISLIKE
498-- - Nội dung đánh giá
499IF OBJECT_ID('sp_EVALUATEINFO') IS NOT NULL DROP PROC sp_EVALUATEINFO
500GO
501CREATE PROCEDURE sp_EVALUATEINFO
502 @maNhaTro INT = NULL
503AS
504BEGIN
505 IF @maNhaTro IS NULL
506SELECT HOPDONG.maNhaTro, NGUOIDUNG.tenNguoiDung AS N'Người Đánh Giá',
507 IIF(chatLuong = 0,'DISLIKE','LIKE') AS N'TRẠNG THÁI', DANHGIA.noiDung
508 FROM HOPDONG INNER JOIN DANHGIA ON HOPDONG.maHD = DANHGIA.maHopDong
509 INNER JOIN NGUOIDUNG ON HOPDONG.maNguoiDung = NGUOIDUNG.maND
510 ORDER BY maNhaTro
511ELSE
512SELECT HOPDONG.maNhaTro, NGUOIDUNG.tenNguoiDung AS N'Người Đánh Giá',
513 IIF(chatLuong = 0,'DISLIKE','LIKE') AS N'TRẠNG THÁI', DANHGIA.noiDung
514 FROM HOPDONG INNER JOIN DANHGIA ON HOPDONG.maHD = DANHGIA.maHopDong
515 INNER JOIN NGUOIDUNG ON HOPDONG.maNguoiDung = NGUOIDUNG.maND
516 WHERE HOPDONG.maNhaTro = @maNhaTro
517END
518GO
519
520EXEC sp_EVALUATEINFO
521
522EXEC sp_EVALUATEINFO 1
523
524
525
526
527-- 3. Xóa thông tin
528-- 1. Viết một SP nhận một tham số đầu vào kiểu int là số lượng DISLIKE. SP này thực hiện
529-- thao tác xóa thông tin của các nhà trọ và thông tin đánh giá của chúng, nếu tổng số lượng
530-- DISLIKE tương ứng với nhà trọ này lớn hơn giá trị tham số được truyền vào.
531-- Yêu cầu: Sử dụng giao dịch trong thân SP, để đảm bảo tính toàn vẹn dữ liệu khi một thao tác
532-- xóa thực hiện không thành công.
533IF OBJECT_ID('sp_REMOVEWITHDISLIKE') IS NOT NULL DROP PROC sp_REMOVEWITHDISLIKE
534GO
535CREATE PROCEDURE sp_REMOVEWITHDISLIKE
536 @maxDisLike INT = 10
537AS
538BEGIN
539 BEGIN TRY
540 BEGIN TRANSACTION
541 DECLARE @needdelete TABLE
542 (ma int)
543 INSERT INTO @needdelete
544 SELECT maNhaTro
545 FROM DANHGIA RIGHT OUTER JOIN HOPDONG ON DANHGIA.maHopDong = HOPDONG.maHD
546 WHERE chatLuong = 0
547 GROUP BY maNhaTro
548 HAVING COUNT(chatLuong) >@maxDisLike
549 DELETE FROM DANHGIA
550 WHERE maHopDong IN (
551 SELECT maHD
552 FROM HOPDONG INNER JOIN @needdelete ndl on HOPDONG.maNhaTro = ndl.ma
553 WHERE maNhaTro = ndl.ma
554 )
555 DELETE FROM HOPDONG
556 WHERE maNhaTro IN (SELECT ma
557 FROM @needdelete)
558
559 DELETE FROM NHATRO
560 WHERE maNT IN (SELECT ma
561 FROM @needdelete)
562 PRINT N'Xóa Thành Công'
563 COMMIT TRANSACTION
564 END TRY
565 BEGIN CATCH
566 ROLLBACK TRAN
567 PRINT N'Có Lỗi Xảy ra. Xóa Thất Bại'
568 END CATCH
569END
570GO
571
572EXEC sp_REMOVEWITHDISLIKE
573EXEC sp_REMOVEWITHDISLIKE 4
574
575
576
577-- 2. Viết một SP nhận hai tham số đầu vào là khoảng thời gian đăng tin. SP này thực hiện
578-- thao tác xóa thông tin những nhà trọ được đăng trong khoảng thời gian được truyền vào
579-- qua các tham số.
580-- Lưu ý: SP cũng phải thực hiện xóa thông tin đánh giá của các nhà trọ này.
581-- Yêu cầu: Sử dụng giao dịch trong thân SP, để đảm bảo tính toàn vẹn dữ liệu khi một thao tác
582-- xóa thực hiện không thành công.
583IF OBJECT_ID('sp_REMOVEWITHDATE') IS NOT NULL DROP PROC sp_REMOVEWITHDATE
584GO
585CREATE PROCEDURE sp_REMOVEWITHDATE
586 @minDate DATETIME,
587 @maxDate DATETIME
588AS
589BEGIN
590 BEGIN TRY
591 BEGIN TRANSACTION
592 DECLARE @needdelete TABLE
593 (ma int)
594 INSERT INTO @needdelete
595 SELECT maNT
596 FROM NHATRO
597 WHERE ngayDang BETWEEN @minDate AND @maxDate
598
599
600 DELETE FROM DANHGIA
601 WHERE maHopDong IN (
602 SELECT maHD
603 FROM HOPDONG INNER JOIN @needdelete ndl on HOPDONG.maNhaTro = ndl.ma
604 WHERE maNhaTro = ndl.ma
605 )
606 DELETE FROM HOPDONG
607 WHERE maNhaTro IN (SELECT ma
608 FROM @needdelete)
609
610 DELETE FROM NHATRO
611 WHERE maNT IN (SELECT ma
612 FROM @needdelete)
613 PRINT N'Xóa Thành Công'
614 COMMIT TRANSACTION
615 END TRY
616 BEGIN CATCH
617 ROLLBACK TRAN
618 PRINT N'Có Lỗi Xảy ra. Xóa Thất Bại'
619 END CATCH
620END
621GO
622
623
624EXEC sp_REMOVEWITHDATE '03/03/2019' , '03/06/2019'
625
626
627
628
629-- Y4. Yêu cầu quản trị CSDL
630-- - Tạo hai người dùng CSDL.
631-- o Một người dùng với vai trò nhà quản trị CSDL. Phân quyền cho người dùng
632-- này chỉ được phép thao tác trên CSDL quản lý nhà trọ cho thuê và có toàn
633-- quyền thao tác trên CSDL đó
634-- o Một người dùng thông thường. Phân cho người dùng này toàn bộ quyền thao
635-- tác trên các bảng của CSDL và quyền thực thi các SP và các hàm được tạo ra từ
636-- các yêu cầu trên
637-- - Kết nối tới Server bằng tài khoản của người dùng thứ nhất. Thực hiện tạo một bản sao
638-- CSDL.