5.13
数据库实验报告三
-- 创建数据库角色
CREATE
ROLE [20224082-role]; GO
-- 将用户添加到角色
ALTER ROLE [20224082-role] ADD MEMBER [20224082-user];
GO
use students
go
grant update on Student(Sname,Ssex,Sage) to [20224074-user]
go
grant select on Student to [20224074-role];
grant select on Course to [20224074-role];
grant select on SC to [20224074-role];
拒绝自己创建的角色拥有sc表中grade的修改权限。
Deny update on SC(grade) to [20224074-role]
4.7.1用管理器创建触发器实现如下安全性和完整性：定义 course 表中 credit 属性为“3”的缺省。
create trigger dbo.st1
on [dbo].[Course]
after insert,update
as
begin
set nocount on;
update Course set Ccredit=3 from Course
join SC on SC.Cno=Course.Cno
where Sno in(select Sno from inserted where Cno=null)
end
go
4.7.2建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级联修改触发器和一个受限插入触发器。
-- 级联删除触发器
CREATE TRIGGER trg_CascadeDelete ON course
AFTER DELETE
AS
BEGIN
DELETE FROM sc WHERE cno IN (SELECT cno FROM DELETED);
END;
GO
-- 级联修改触发器
CREATE TRIGGER trg_CascadeUpdate ON course
AFTER UPDATE
AS
BEGIN
IF UPDATE(cno)
BEGIN
DECLARE @OldCno INT, @NewCno INT;
SELECT @OldCno = cno FROM DELETED;
SELECT @NewCno = cno FROM INSERTED;
UPDATE sc SET cno = @NewCno WHERE cno = @OldCno;
END;
END;
GO
-- 受限插入触发器
CREATE TRIGGER trg_RestrictInsert ON sc
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM course WHERE cno IN (SELECT cno FROM INSERTED))
BEGIN
RAISERROR('Invalid course number', 16, 1);
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
INSERT INTO sc (sno, cno, grade)
SELECT sno, cno, grade FROM INSERTED;
END
END;
GO
对“students 数据库”编写存储过程，完成下面功能：
4.8.1成绩 60 分以下的人数；
4.8.2统计给定 cno 的课程的平均成绩，并返回平均成绩；
4.8.3将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40
为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。
-- 统计成绩60分以下的人数
CREATE PROCEDURE CountLowGrades
AS
BEGIN
SELECT COUNT(*) AS LowGradeCount
FROM sc
WHERE grade < 60;
END;
GO
-- 统计给定cno课程的平均成绩
CREATE PROCEDURE GetAverageGrade
@cno INT
AS
BEGIN
SELECT AVG(grade) AS AverageGrade
FROM sc
WHERE cno = @cno;
END;
GO
-- 将百分制成绩改为等级制
CREATE PROCEDURE ConvertGradesToScale
AS
BEGIN
UPDATE sc
SET grade = CASE
WHEN grade BETWEEN 0 AND 20 THEN 1
WHEN grade BETWEEN 21 AND 40 THEN 2
WHEN grade BETWEEN 41 AND 60 THEN 3
WHEN grade BETWEEN 61 AND 80 THEN 4
WHEN grade BETWEEN 81 AND 100 THEN 5
ELSE grade
END;
END;
GO
