· 5 years ago · Jun 17, 2020, 12:36 PM
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ố.
471IF OBJECT_ID('fn_FINDUSERCODE') IS NOT NULL DROP FUNCTION fn_FINDUSERCODE
472GO
473CREATE FUNCTION fn_FINDUSERCODE
474(
475 @ten NVARCHAR(32)='%',
476 @gioiTinh NVARCHAR(5)='%',
477 @dienThoai NVARCHAR(15)='%',
478 @diaChi NVARCHAR(55)='%',
479 @quan int = NULL,
480 @email NVARCHAR(30) = '%'
481)
482RETURNS TABLE
483AS
484RETURN
485(
486 SELECT maND
487FROM NGUOIDUNG
488WHERE tenNguoiDung LIKE @ten
489 AND gioiTinh LIKE @gioiTinh
490 AND dienThoai LIKE @dienThoai
491 AND diaChi LIKE @diaChi
492 AND email LIKE @email
493 AND 1 = ( CASE
494 WHEN @quan IS NULL THEN 1
495 WHEN @quan = quan THEN 1
496 ELSE 0
497 END)
498 )
499GO
500
501
502SELECT *
503FROM fn_FINDUSERCODE(default,default,default,default,default,default)
504
505-- 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
506-- NHATRO). Hàm này trả về tổng số LIKE và DISLIKE của nhà trọ này.
507IF OBJECT_ID('fn_COUNTLIKE') IS NOT NULL DROP FUNCTION fn_COUNTLIKE
508GO
509CREATE FUNCTION fn_COUNTLIKE
510(
511 @maNT int= null
512)
513RETURNS @res
514TABLE
515(
516 LIKES int,
517 DISLIKES int
518)
519AS
520BEGIN
521 INSERT INTO @res
522 VALUES(0, 0);
523
524 IF @maNT IS NULL
525 BEGIN
526 UPDATE @res
527 SET LIKES = (
528 SELECT COUNT(chatLuong) FROM DANHGIA WHERE chatLuong = 1
529 )
530 UPDATE @res
531 SET DISLIKES = (
532 SELECT COUNT(chatLuong) FROM DANHGIA WHERE chatLuong = 0
533 )
534 END
535 ELSE
536 BEGIN
537 UPDATE @res
538 SET LIKES = (
539 SELECT COUNT(chatLuong)
540 FROM DANHGIA INNER JOIN HOPDONG ON HOPDONG.maHD = DANHGIA.maHopDong
541 WHERE @maNT = maNhaTro AND chatLuong = 1
542 )
543
544 UPDATE @res
545 SET DISLIKES = (
546 SELECT COUNT(chatLuong)
547 FROM DANHGIA INNER JOIN HOPDONG ON HOPDONG.maHD = DANHGIA.maHopDong
548 WHERE @maNT = maNhaTro AND chatLuong = 0
549 )
550 END
551 RETURN
552END
553GO
554
555SELECT *
556FROM fn_COUNTLIKE(DEFAULT)
557-- 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
558-- các thông tin sau:
559-- - Diện tích
560-- - Giá
561-- - Mô tả
562-- - Ngày đăng tin
563-- - Tên người liên hệ
564-- - Địa chỉ
565-- - Điện thoại
566-- - Email
567
568
569
570-- 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
571-- bảng NHATRO). SP này trả về tập kết quả gồm các thông tin sau:
572-- - Mã nhà trọ
573-- - Tên người đánh giá
574-- - Trạng thái LIKE hay DISLIKE
575-- - Nội dung đánh giá
576IF OBJECT_ID('sp_EVALUATEINFO') IS NOT NULL DROP PROC sp_EVALUATEINFO
577GO
578CREATE PROCEDURE sp_EVALUATEINFO
579 @maNhaTro INT = NULL
580AS
581BEGIN
582 IF @maNhaTro IS NULL
583SELECT HOPDONG.maNhaTro, NGUOIDUNG.tenNguoiDung AS N'Người Đánh Giá',
584 IIF(chatLuong = 0,'DISLIKE','LIKE') AS N'TRẠNG THÁI', DANHGIA.noiDung
585 FROM HOPDONG INNER JOIN DANHGIA ON HOPDONG.maHD = DANHGIA.maHopDong
586 INNER JOIN NGUOIDUNG ON HOPDONG.maNguoiDung = NGUOIDUNG.maND
587 ORDER BY maNhaTro
588ELSE
589SELECT HOPDONG.maNhaTro, NGUOIDUNG.tenNguoiDung AS N'Người Đánh Giá',
590 IIF(chatLuong = 0,'DISLIKE','LIKE') AS N'TRẠNG THÁI', DANHGIA.noiDung
591 FROM HOPDONG INNER JOIN DANHGIA ON HOPDONG.maHD = DANHGIA.maHopDong
592 INNER JOIN NGUOIDUNG ON HOPDONG.maNguoiDung = NGUOIDUNG.maND
593 WHERE HOPDONG.maNhaTro = @maNhaTro
594END
595GO
596
597EXEC sp_EVALUATEINFO
598
599EXEC sp_EVALUATEINFO 6
600
601
602
603
604-- 3. Xóa thông tin
605-- 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
606-- 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
607-- DISLIKE tương ứng với nhà trọ này lớn hơn giá trị tham số được truyền vào.
608-- 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
609-- xóa thực hiện không thành công.
610IF OBJECT_ID('sp_REMOVEWITHDISLIKE') IS NOT NULL DROP PROC sp_REMOVEWITHDISLIKE
611GO
612CREATE PROCEDURE sp_REMOVEWITHDISLIKE
613 @maxDisLike INT = 10
614AS
615BEGIN
616 BEGIN TRY
617 BEGIN TRANSACTION
618 DECLARE @needdelete TABLE
619 (ma int)
620 INSERT INTO @needdelete
621 SELECT maNhaTro
622 FROM DANHGIA RIGHT OUTER JOIN HOPDONG ON DANHGIA.maHopDong = HOPDONG.maHD
623 WHERE chatLuong = 0
624 GROUP BY maNhaTro
625 HAVING COUNT(chatLuong) >@maxDisLike
626 DELETE FROM DANHGIA
627 WHERE maHopDong IN (
628 SELECT maHD
629 FROM HOPDONG INNER JOIN @needdelete ndl on HOPDONG.maNhaTro = ndl.ma
630 WHERE maNhaTro = ndl.ma
631 )
632 DELETE FROM HOPDONG
633 WHERE maNhaTro IN (SELECT ma
634 FROM @needdelete)
635
636 DELETE FROM NHATRO
637 WHERE maNT IN (SELECT ma
638 FROM @needdelete)
639 PRINT N'Xóa Thành Công'
640 COMMIT TRANSACTION
641 END TRY
642 BEGIN CATCH
643 ROLLBACK TRAN
644 PRINT N'Có Lỗi Xảy ra. Xóa Thất Bại'
645 END CATCH
646END
647GO
648
649EXEC sp_REMOVEWITHDISLIKE
650EXEC sp_REMOVEWITHDISLIKE 4
651
652
653
654-- 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
655-- 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
656-- qua các tham số.
657-- 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.
658-- 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
659-- xóa thực hiện không thành công.
660IF OBJECT_ID('sp_REMOVEWITHDATE') IS NOT NULL DROP PROC sp_REMOVEWITHDATE
661GO
662CREATE PROCEDURE sp_REMOVEWITHDATE
663 @minDate DATETIME,
664 @maxDate DATETIME
665AS
666BEGIN
667 BEGIN TRY
668 BEGIN TRANSACTION
669 DECLARE @needdelete TABLE
670 (ma int)
671 INSERT INTO @needdelete
672 SELECT maNT
673 FROM NHATRO
674 WHERE ngayDang BETWEEN @minDate AND @maxDate
675
676
677 DELETE FROM DANHGIA
678 WHERE maHopDong IN (
679 SELECT maHD
680 FROM HOPDONG INNER JOIN @needdelete ndl on HOPDONG.maNhaTro = ndl.ma
681 WHERE maNhaTro = ndl.ma
682 )
683 DELETE FROM HOPDONG
684 WHERE maNhaTro IN (SELECT ma
685 FROM @needdelete)
686
687 DELETE FROM NHATRO
688 WHERE maNT IN (SELECT ma
689 FROM @needdelete)
690 PRINT N'Xóa Thành Công'
691 COMMIT TRANSACTION
692 END TRY
693 BEGIN CATCH
694 ROLLBACK TRAN
695 PRINT N'Có Lỗi Xảy ra. Xóa Thất Bại'
696 END CATCH
697END
698GO
699
700
701EXEC sp_REMOVEWITHDATE '03/03/2019' , '03/06/2019'
702
703
704
705
706-- Y4. Yêu cầu quản trị CSDL
707-- - Tạo hai người dùng CSDL.
708-- 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
709-- này chỉ được phép thao tác trên CSDL quản lý nhà trọ cho thuê và có toàn
710-- quyền thao tác trên CSDL đó
711-- 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
712-- 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ừ
713-- các yêu cầu trên
714-- - 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
715-- CSDL.