Thực hành quản lý thông tin lab 6

HỌ VÀ TÊN: Nguyễn Thanh Hiếu

LỚP: IE103.M21

MSSV: 20521328

Dựa vào CSDL đã thiết kế ở BTTH số 2, hãy thực hiện các yêu cầu sau:
Câu 1: Liệt kê các thực thể có mặt trong CSDL trên.
- Các thực thể có trong CSDL là: SINHVIEN, DETAI, HOCVI, HOCHAM, GIAOVIEN,
CHUYENNGANH, HOIDONG.
Câu 2: Mô tả lược đồ quan hệ cho CSDL trên. Vẽ sơ đồ Diagram cho các quan hệ trên
(sử dụng chức năng tạo Diagram trong SQL Server)
Mô tả lược đồ quan hệ:
- SINHVIEN(#MSSV, TENSV, SODT, LOP, DIACHI)
- DETAI(#MSDT, TENDT)
- HOCVI(#MSHV, TENHV)
- HOCHAM(#MSHH, TENHH)
- SV_DETAI(#MSSV, #MSDT)
- GIAOVIEN(#MSGV, TENGV, DIACHI, SODT, #MSHH, NAMHH)
- CHUYENNGANH(#MSCN, TENCN)
- GV_HV_CN(#MSGV, #MSHV, #MSCN, NAM)
- GV_HDDT(#MSGV, #MSDT, DIEM)
- GV_PBDT(#MSGV, #MSDT, DIEM)
- GV_UVDT(#MSGV, #MSDT, DIEM)
- HOIDONG(#MSHD, PHONG, TGBD, NGAYHD, TINHTRANG, #MSGV)
- HOIDONG_GV(#MSHD, #MSGV)
- HOIDONG_DT(#MSHD, #MSDT, QUYETDINH)

SINHVIEN
MSSV
TENSV
SODT
LOP

SV_DETAI
MSSV
MSDT

GV_PBDT
MSGV
MSDT

DETAI
MSDT
TENDT

GV_UVDT
MSGV
MSDT
DIEM

DIEM

HOIDONG_DT

GV_HDDT

MSHD


MSGV

MSDT

MSDT

QUYETDINH

DIEM

HOIDONG
MSHD
PHONG

HOIDONG_GV
MSHD

TGBD

MSGV
NGAYHD
TINHTRANG

GV_HV_CN
MSGV

CHUYENNGANH

MSHV


MSCN

MSGV

MSCN

TENCN

TENGV

NAM

GIAOVIEN

DIACHI
SODT
MSHH

HOCVI
MSHV
TENHV

HOCHAM
MSHH
TENHH

Câu 3: Hiện thực các ràng buộc như sau. Yêu cầu phát biểu ràng buộc, và vẽ bảng tầm
ảnh hưởng, sau đó hiện thực trong SQL Server.
3.1. Một sinh viên chỉ được tham gia một đề tài.

Bảng tầm ảnh hưởng
SINHVIEN
DETAI
SV_DT

Thêm
+(MSSV, MSDT)

Sửa
+(MSSV, MSDT)

Xóa
- (*)
- (*)
-


create trigger TG_THEMSV_thamgia1detai
on SV_DETAI FOR INSERT
AS
BEGIN
declare @sldt int, @msdt char(60),

@mssv char(8)

select @mssv = MSSV from inserted
select @sldt = COUNT(MSDT) from SV_DETAI
where MSSV=@mssv
if(@sldt>1)
begin

print N'Loi, 1 sinh vien chi tham gia 1 de tai'
rollback transaction
end
end

create trigger TG_SUASV_thamgia1detai
on SV_DETAI FOR UPDATE
AS
BEGIN
declare @sldt int, @msdt char(60),

@mssv char(8)

select @mssv = MSSV from inserted
select @sldt = COUNT(MSDT) from SV_DETAI
where MSSV=@mssv
if(@sldt>1)
begin
print N'Loi, 1 sinh vien chi tham gia 1 de tai'
rollback transaction
end
end

3.2. Một đề tài khơng có quá 3 sinh viên tham gia.
Bảng tầm ảnh hưởng
SINHVIEN
DETAI
SV_DT

Thêm

+

Sửa
+

create trigger TGTHEM_DTkhongqua3SV on SV_DETAI for INSERT
as
begin
if (select count(t1.mssv) from SV_DETAI t1, INSERTED t2
where t1.msdt = t2.msdt) >3
begin
print 'Khong the qua 3 tren 1 de tai'
rollback transaction
end
end
create trigger TGSUA_DTkhongqua3SV on SV_DETAI for INSERT, UPDATE
as

Xóa
-(*)
-(*)
-


begin
if (select count(t1.mssv) from SV_DETAI t1, INSERTED t2
where t1.msdt = t2.msdt) >3
begin
print 'Khong the qua 3 tren 1 de tai'
rollback transaction

end
end

3.3. Điểm của đề tài trong thang điểm từ 0 đến 10.
Thêm
Sửa
GV_HDDT
+
+(DIEM)
GV_PBDT
+
+(DIEM)
GV_UVDT
+
+(DIEM)
alter table GV_HDDT ADD CONSTRAINT CHECK_1
CHECK(0 <= DIEM AND DIEM <=10);
alter table GV_PBDT ADD CONSTRAINT CHECK_2
CHECK(0 <= DIEM AND DIEM <=10);
alter table GV_UVDT ADD CONSTRAINT CHECK_3
CHECK(0 <= DIEM AND DIEM <=10);
3.4. GV là chủ tịch hội đồng phải có học vị tiến sĩ.

Xóa
-

Bảng tầm ảnh hưởng
HOIDONG
HOCVI
GV_HV_CN

GIAOVIEN

Thêm
+
+
-

Sửa
+
+(MSHV)
-

Xóa
-(*)
-(*)
+
-(*)

Câu 4: Viết các truy vấn xử lý thông tin như sau sử dụng Function, hoặc stored
procedured:
4.1. Tìm điểm trung bình của một đề tài.
create function Tinh_DTB_cuaDT (@MSDT char(10))
returns float
as
begin
declare @DTB float
select @DTB = ((SUM(hd.DIEM) + SUM(uv.DIEM)+ SUM(pb.DIEM)) / (count(hd.DIEM) +
count(uv.DIEM)+ count(pb.DIEM)))
from DETAI DT, GV_HDDT hd, GV_PBDT pb,GV_UVDT uv
where DT.MSDT=hd.MSDT and DT.MSDT=pb.MSDT and dt.MSDT=uv.MSDT and dt.MSDT=@MSDT

return @DTB
end

4.2. In ra danh sách điểm trung bình của mỗi đề tài trong danh sách đề tài.


CREATE TABLE DETAI_DIEM (
MSDT VARCHAR(6) PRIMARY KEY,
DIEM FLOAT
)
DECLARE p CURSOR FOR SELECT MSDT, dbo.Tinh_DTB_cuaDT(MSDT) AS DIEM_TB FROM DETAI
OPEN p
DECLARE @MADT CHAR(6), @DIEM FLOAT
FETCH NEXT FROM p INTO @MADT, @DIEM
WHILE (@@FETCH_STATUS=0)
BEGIN
UPDATE DETAI_DIEM
SET MSDT= @MADT, DIEM=ROUND(@DIEM,2)
FETCH NEXT FROM p INTO @MADT, @DIEM
END
CLOSE P
DEALLOCATE p

4.3. In ra danh sách giảng viên có phản biện đề tài.
4.4. In ra danh sách các đề tài trong một hội đồng.
Câu 5: Phân quyền, xác thực.
5.1. Tạo ra 3 user: GIANGVIEN, GIAOVU và SINHVIEN, đặt mật khẩu tuỳ ý.
create login gvien with password = '12345'
create login gvu with password = '12345'
create login svien with password = '12345'

create user GIAOVIEN for login gvien
create user GIAOVU for login gvu
create user SINHVIEN for login svien

5.2. Phân quyền cho các user trên database như sau:
GIAOVU có quyền xem và chỉnh sửa trên tất cả các bảng.
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT

SELECT,
SELECT,
SELECT,
SELECT,
SELECT,
SELECT,
SELECT,
SELECT,
SELECT,

SELECT,
SELECT,
SELECT,
SELECT,
SELECT,

UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE

ON
ON
ON
ON
ON
ON
ON
ON
ON

ON
ON
ON
ON
ON

CHUYENNGANH TO GIAOVU
DETAI TO GIAOVU
GIAOVIEN TO GIAOVU
GV_HDDT TO GIAOVU
GV_HV_CN TO GIAOVU
GV_PBDT TO GIAOVU
GV_UVDT TO GIAOVU
HOCHAM TO GIAOVU
HOCVI TO GIAOVU
HOIDONG TO GIAOVU
HOIDONG_DT TO GIAOVU
HOIDONG_GV TO GIAOVU
SINHVIEN TO GIAOVU
SV_DETAI TO GIAOVU

GIANGVIEN có quyền xem trên các bảng liên quan đến thông tin giáo
viên, thông tin học sinh, các đề tài mà GV hướng dẫn, phản biện hay làm uỷ viên,


và xem thơng tin hội đồng; GIANGVIEN có quyền cập nhật thông tin của giảng
viên.
GRANT
GRANT
GRANT

GRANT
GRANT
GRANT
GRANT
GRANT
GRANT

SELECT, UPDATE ON GIAOVIEN TO GIAOVIEN
SELECT ON DETAI TO GIAOVIEN
SELECT ON GV_HV_CN TO GIAOVIEN
SELECT ON GV_HDDT TO GIAOVIEN
SELECT ON GV_PBDT TO GIAOVIEN
SELECT ON GV_UVDT TO GIAOVIEN
SELECT ON HOIDONG TO GIAOVIEN
SELECT ON SINHVIEN TO GIAOVIEN
SELECT ON SV_DETAI TO GIAOVIEN

SINHVIEN có quyền xem thơng tin của sinh viên, của hội đồng và các đề
tài hiện hữu trên hệ thống.
GRANT SELECT ON SINHVIEN TO SINHVIEN
GRANT SELECT ON HOIDONG TO SINHVIEN
GRANT SELECT ON DETAI TO SINHVIEN

Tất cả người dùng đều khơng có quyền xố thơng tin.
DENY
DENY
DENY
DENY
DENY
DENY


DELETE
DELETE
DELETE
DELETE
DELETE
DELETE

ON
ON
ON
ON
ON
ON

DETAI TO GIAOVIEN, SINHVIEN, GIAOVU
CHUYENNGANH TO GIAOVIEN, SINHVIEN, GIAOVU
GIAOVIEN TO GIAOVIEN, SINHVIEN, GIAOVU
GV_HDDT TO GIAOVIEN, SINHVIEN, GIAOVU
GV_PBDT TO GIAOVIEN, SINHVIEN, GIAOVU
GV_UVDT TO GIAOVIEN, SINHVIEN, GIAOVU

DENY
DENY
DENY
DENY
DENY
DENY
DENY
DENY


DELETE
DELETE
DELETE
DELETE
DELETE
DELETE
DELETE
DELETE

ON
ON
ON
ON
ON
ON
ON
ON

GV_HV_CN TO GIAOVIEN, SINHVIEN, GIAOVU
HOCHAM TO GIAOVIEN, SINHVIEN, GIAOVU
HOCVI TO GIAOVIEN, SINHVIEN, GIAOVU
HOIDONG TO GIAOVIEN, SINHVIEN, GIAOVU
HOIDONG_DT TO GIAOVIEN, SINHVIEN, GIAOVU
HOIDONG_GV TO GIAOVIEN, SINHVIEN, GIAOVU
SINHVIEN TO GIAOVIEN, SINHVIEN, GIAOVU
SV_DETAI TO GIAOVIEN, SINHVIEN, GIAOVU

Câu 6: Thiết kế report cho đề tài như sau:
a) Thông tin GV phản biện.



b) Thông tin hội đồng chấm đề tài


c) Thông tin điểm đề tài (lưu ý: điểm làm tròn 2 chữ số)