Bài tập thực hành môn Quản lý Thông tin tuần 6
Câu 1: Liệt kê các thực thể có mặt trong CSDL trên.
SINHVIEN,
DETAI,
GIAOVIEN,
HOCVI,
CHUYENNGANH,
HOCHAM,
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)
SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI)
DETAI (MSDT, TENDT)
SV_DETAI (MSSV, MSDT)
GIAOVIEN (MSGV, TENGV, DIACHI, SODT, MSHH, NAMHH)
HOCVI (MSHV, TENHV)
CHUYENNGANH (MSCN, TENCN)
GV_HV_CN (MSGV, MSHV, MSCN, NAM)
HOCHAM (MSHH, TENHH)
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)


Sơ đồ Diagram:
SV_DETAI

SINHVIEN

MSSV

MSSV

TENSV

MSDT

SODT

GV_PBDT

LOP

MSGV

DIACHI

MSDT

DETAI

GV_HDDT

DIEM

MSDT

MSGV


TENDT

MSDT
DIEM

GV_UVDT

HOIDONG_DT

MSGV

MSHD
MSDT
MSDT
QUYETDINH

DIEM

HOIDONG_GV
MSHD
MSGV

HOIDONG
MSHD

HOCHAM

PHONG

MSHH


TGBD

TENHH

NGAYHD
TINHTRANG
MSGV

GIAOVIEN
MSGV
TENGV
DIACHI
SODT
MSHH

GV_HV_CN

NAMHH

MSGV
MSHV
MSCN
NAM

CHUYENNGANH
HOCVI
MSHV
TENHV


MSCN
TENCN


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:
Thêm
Sửa
SINHVIEN
DETAI
SV_DETAI
+
+(MSSV)
- Trigger cho thao tác thêm dữ liệu trên bảng SV_DETAI:

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

create trigger TRG_SV_DETAI_INSERT
on SV_DETAI
for insert
as
begin
declare @MSSV char(8), @SLDT int
select @MSSV = MSSV from inserted
select @SLDT = count(MSDT)

from SV_DETAI
where MSSV = @MSSV
if(@SLDT > 1)
begin
print 'Mot sinh vien chi duoc tham gia mot de tai!'
rollback transaction
end
end

- Trigger cho thao tác sửa dữ liệu trên bảng SV_DETAI:
create trigger TRG_SV_DETAI_UPDATE
on SV_DETAI
for update
as
begin
declare @MSSV char(8), @SLDT int
select @MSSV = MSSV from inserted
select @SLDT = count(MSDT) from SV_DETAI
where MSSV = @MSSV
if(@SLDT > 1)
begin
print 'Mot sinh vien chi duoc tham gia mot de tai!'
rollback transaction
end
end


3.2. Một đề tài khơng có q 3 sinh viên tham gia.
Bảng tầm ảnh hưởng:
Thêm

Sửa
SINHVIEN
DETAI
SV_DETAI
+
+(MSDT)
- Trigger cho thao tác thêm dữ liệu trên bảng SV_DETAI:
create trigger TRG_SV_DETAI_MAX_BA_INSERT
on SV_DETAI
for insert
as
begin
declare @MSDT char(8), @SLSV int
select @MSDT = MSDT from inserted
select @SLSV = count(MSSV)
from SV_DETAI
where MSDT = @MSDT
if(@SLSV > 3)
begin
print 'Mot de tai khong qua 3 sinh vien!'
rollback transaction
end
end

- Trigger cho thao tác sửa dữ liệu trên bảng SV_DETAI:
create trigger TRG_SV_DETAI_MAX_BA_UPDATE
on SV_DETAI
for update
as
begin

declare @MSDT char(8), @SLSV int
select @MSDT = MSDT from inserted
select @SLSV = count(MSSV)
from SV_DETAI
where MSDT = @MSDT
if(@SLSV > 3)
begin
print 'Mot de tai khong qua 3 sinh vien!'
rollback transaction
end
end

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


3.3. Điểm của đề tài trong thang điểm từ 0 đến 10.
Bảng tầm ảnh hưởng:
Thêm

Sửa

Xóa

GV_UVDT

+


+(DIEM)

-

GV_HDDT

+

+(DIEM)

-

GV_PBDT

+

+(DIEM)

-

- Thêm ràng buộc DIEM trên bảng GV_UVDT:
alter table GV_UVDT add
constraint CHECK_DIEM_GV_UVDT
check (DIEM between 0 and 10)
- Thêm ràng buộc DIEM trên bảng GV_HDDT:
alter table GV_HDDT add
constraint CHECK_DIEM_GV_HDDT
check (DIEM between 0 and 10)

-Thêm ràng buộc DIEM trên bảng GV_PBDT:

alter table GV_PBDT add
constraint CHECK_DIEM_GV_PBDT
check (DIEM between 0 and 10)

3.4. GV là chủ tịch hội đồng phải có học vị tiến sĩ.
Thêm
Sửa
HOIDONG
+
+(MSGV)
GIAOVIEN
HOCVI
GV_HV_CN
+(MSGV, MSHV)
- Trigger cho thao tác thêm dữ liệu trên bảng HOIDONG:

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

create trigger TRG_TS_MSGV_HOIDONG_INSERT
on HOIDONG
for insert
as
begin
declare @MSGV char(8), @MSHV int
select @MSGV = MSGV from inserted
select @MSHV = MSHV

from GV_HV_CN
where MSGV = @MSGV
if (@MSHV != 4)
begin
print 'Chu tich hoi dong phai co hoc vi tien si'
rollback transaction
end
end


- Trigger cho thao tác sửa dữ liệu trên bảng HOIDONG:
create trigger TRG_TS_MSGV_HOIDONG_UPDATE
on HOIDONG
for update
as
begin
declare @MSGV char(8), @MSHV int
select @MSGV = MSGV from inserted
select @MSHV = MSHV
from GV_HV_CN
where MSGV = @MSGV
if (@MSHV != 4)
begin
print 'Chu tich hoi dong phai co hoc vi tien si'
rollback transaction
end
end

- Trigger cho thao tác sửa dữ liệu trên bảng GV_HV_CN:
create trigger MSGV_MSHV_GV_HV_CN_UPDATE

on GV_HV_CN
for UPDATE
as
begin
if ( exists (
select *
from HOIDONG, inserted
where HOIDONG.MSGV = inserted.MSGV)
)
begin
declare @MSGV char(8), @MSHV int
select @MSGV = MSGV from inserted
select @MSHV = MSHV
from GV_HV_CN
where MSGV = @MSGV
if (@MSHV != 4)
begin
print 'Chu tich hoi dong phai co hoc vi tien si'
rollback transaction
end
end
end


- Trigger cho thao tác xóa dữ liệu trên bảng GV_HV_CN:
create trigger MSGV_MSHV_GV_HV_CN_DELETE
on GV_HV_CN
for delete
as
begin

if ( exists (
select *
from HOIDONG, deleted
where HOIDONG.MSGV = deleted.MSGV)
)
begin
declare @MSHV int
select @MSHV = MSHV from deleted
if (@MSHV = 4)
begin
print 'Chu tich hoi dong phai co hoc vi tien si'
rollback transaction
end
end
end

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 procedure TINH_DTB @MSDT char(6), @DIEMTB float OUTPUT
as
begin
select @DIEMTB = (SUM(GV_HDDT.DIEM) + SUM(GV_PBDT.DIEM) +
SUM(GV_UVDT.DIEM)) / (count(GV_HDDT.MSGV) +
count(GV_PBDT.MSGV) + count(GV_UVDT.MSGV))
from DETAI as A inner join GV_HDDT on A.MSDT = GV_HDDT.MSDT
inner join GV_UVDT on A.MSDT = GV_UVDT.MSDT
inner join GV_PBDT on a.MSDT = GV_PBDT.MSDT
where A.MSDT = @MSDT
if (@DIEMTB is null)

set @DIEMTB = 0
end
declare @A float
-- Tạo biến A
exec TINH_DTB '97005', @A OUTPUT -- Truyền MSDT và A vào hàm TINH_DTB
print @A – In ra giá trị biến A


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 function F_TINH_DTB (@MSDT char(6))
returns float
as
begin
declare @DIEMTB float
select @DIEMTB = (SUM(GV_HDDT.DIEM) + SUM(GV_PBDT.DIEM) +
SUM(GV_UVDT.DIEM)) / (count(GV_HDDT.MSGV) +
count(GV_PBDT.MSGV) + count(GV_UVDT.MSGV))
from DETAI as A inner join GV_HDDT on A.MSDT = GV_HDDT.MSDT
inner join GV_UVDT on A.MSDT = GV_UVDT.MSDT
inner join GV_PBDT on a.MSDT = GV_PBDT.MSDT
where A.MSDT = @MSDT
if (@DIEMTB is null)
set @DIEMTB = 0
return @DIEMTB
end
-- In ra danh sách điểm trung bình của mỗi đề tài
select MSDT, TENDT, dbo.F_TINH_DTB(MSDT) DIEM_TB
from DETAI

4.3. In ra danh sách giảng viên có phản biện đề tài.

create procedure DS_GV_PBDT
as
begin
select distinct GIAOVIEN.*
from GIAOVIEN, GV_PBDT
where GIAOVIEN.MSGV = GV_PBDT.MSGV
end
-- In ra danh sách giảng viên có phản biện đề tài
exec DS_GV_PBDT

4.4. In ra danh sách các đề tài trong một hội đồng.
create procedure DS_DETAI_HOIDONG @MSHD int
as
begin
select HD.MSHD, DETAI.*
from DETAI, HOIDONG_DT HD
where DETAI.MSDT = HD.MSDT AND
@MSHD=HD.MSHD
end
-- In ra danh sách các đề tài trong một hội đồng (1)
exec DS_DETAI_HOIDONG 1


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ỳ ý.


Tạo login user




create login GIANGVIEN with password = '12345'
create login GIAOVU with password = '12345'
create login SINHVIEN with password = '12345'
Tạo database user

create user GIANGVIEN for login GIANGVIEN
create user GIAOVU for login GIAOVU
create user SINHVIEN for login SINHVIEN
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




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


SINHVIEN to GIAOVU
DETAI to GIAOVU
SV_DETAI to GIAOVU
GIAOVIEN to GIAOVU
HOCVI to GIAOVU
CHUYENNGANH to GIAOVU
GV_HV_CN to GIAOVU
HOCHAM to GIAOVU
GV_HDDT to GIAOVU
GV_PBDT to GIAOVU
GV_UVDT to GIAOVU
HOIDONG to GIAOVU
HOIDONG_GV to GIAOVU
HOIDONG_DT 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
grant
grant
grant
grant
grant
grant



select,
select,

select,
select,
select,
select,
select,
select,
select,
select,
select,
select,
select,
select,

select
select
select
select
select
select

on
on
on
on
on
on

GIAOVIEN to GIANGVIEN
SINHVIEN to GIANGVIEN
GV_HDDT to GIANGVIEN

GV_PBDT to GIANGVIEN
GV_UVDT to GIANGVIEN
HOIDONG to GIANGVIEN

GIANGVIEN có quyền cập nhật thơng tin của giảng viên.
grant update on GIAOVIEN to GIANGVIEN



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
deny
deny
deny
deny

deny
deny
deny
deny

delete
delete
delete
delete
delete
delete
delete
delete
delete
delete
delete
delete
delete
delete

on
on
on
on
on
on
on
on
on
on

on
on
on
on

SINHVIEN to GIANGVIEN, GIAOVU, SINHVIEN
DETAI to GIANGVIEN, GIAOVU, SINHVIEN
SV_DETAI to GIANGVIEN, GIAOVU, SINHVIEN
GIAOVIEN to GIANGVIEN, GIAOVU, SINHVIEN
HOCVI to GIANGVIEN, GIAOVU, SINHVIEN
CHUYENNGANH to GIANGVIEN, GIAOVU, SINHVIEN
GV_HV_CN to GIANGVIEN, GIAOVU, SINHVIEN
HOCHAM to GIANGVIEN, GIAOVU, SINHVIEN
GV_HDDT to GIANGVIEN, GIAOVU, SINHVIEN
GV_PBDT to GIANGVIEN, GIAOVU, SINHVIEN
GV_UVDT to GIANGVIEN, GIAOVU, SINHVIEN
HOIDONG to GIANGVIEN, GIAOVU, SINHVIEN
HOIDONG_GV to GIANGVIEN, GIAOVU, SINHVIEN
HOIDONG_DT to GIANGVIEN, GIAOVU, SINHVIEN

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ố)