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