2024.5.31

所学时间：2.2小时

代码行数：62

博客园数：1篇

所学知识：今天继续完成数据库的数据库完整性、安全性实现实验。

完成如下操作： （1）在 SQL Server 管理器中，创建一个新的 windows 登录名。 （2）在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己学号）。 （3）为所属的 SQL 服务器设置混合认证安全认证模式。 （4）用自己创建的登录名登录服务器。 （5）在 SQL Server 管理器中为自己建立一个 students 数据库用户（自己学号-user）和 角色（自己学号-role）。 （6）用管理器实现如下权限的分配： ① 将 student 表中“sname”、“ssex”、“sage”的修改权限赋予自己创建的数据库用 户。 ② 将 student 表、course 表、sc 表的查询权限授予自己创建的角色。 ③ 拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。 ④ 将自己创建的数据库用户添加为自己创建的角色成员。 （7）用管理器创建触发器实现如下安全性和完整性： ①定义 course 表中 credit 属性为“3”的缺省。 ②建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级 联修改触发器和一个受限插入触发器。 （8）对“students 数据库”编写存储过程，完成下面功能： ① 统计成绩 60 分以下的人数； ② 统计给定 cno 的课程的平均成绩，并返回平均成绩；

首先先创建创建数据库和表格：

-- 创建 students 数据库
CREATE DATABASE students;
GO

-- 使用 students 数据库
USE students;
GO

-- 创建 Student 表
CREATE TABLE Student (
Sno CHAR(7) PRIMARY KEY,
Sname CHAR(10) NOT NULL,
Ssex CHAR(2),
Sage TINYINT,
Sdept CHAR(20)
);

-- 创建 Course 表
CREATE TABLE Course (
Cno CHAR(10) NOT NULL,
Cname CHAR(20) NOT NULL,
Ccredit TINYINT,
Semester TINYINT,
PRIMARY KEY (Cno)
);

-- 创建 SC 表
CREATE TABLE SC (
Sno CHAR(7) NOT NULL,
Cno CHAR(10) NOT NULL,
Grade TINYINT,
XKLB CHAR(4),
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

接下来是权限分配的步骤：

GRANT UPDATE (Sname, Ssex, Sage) ON Student TO [your_username_user];

GRANT SELECT ON Student TO [your_username_role];
GRANT SELECT ON Course TO [your_username_role];
GRANT SELECT ON SC TO [your_username_role];

DENY UPDATE (Grade) ON SC TO [your_username_role];

EXEC sp_addrolemember '[your_username_role]', '[your_username_user]';

接下来就是触发器的创建

ALTER TABLE Course ADD CONSTRAINT DF_Course_Credit DEFAULT 3 FOR Ccredit;

CREATE TRIGGER trg_Cascade_Delete
ON Course
FOR DELETE
AS
BEGIN
DELETE FROM SC WHERE Cno IN (SELECT Cno FROM DELETED);
END;
GO

CREATE TRIGGER trg_Cascade_Update
ON Course
FOR UPDATE
AS
BEGIN
UPDATE SC SET Cno = i.Cno FROM inserted i WHERE SC.Cno = i.Cno;
END;
GO

CREATE TRIGGER trg_Restrict_Insert
ON SC
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE Grade > 100 OR Grade < 0)
BEGIN
RAISERROR ('Grade must be between 0 and 100.', 16, 1);
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
INSERT INTO SC (Sno, Cno, Grade, XKLB) SELECT Sno, Cno, Grade, XKLB FROM inserted;
END
END;

最后是存储过程的编写：

CREATE PROCEDURE sp_CountStudentsBelow60
AS
BEGIN
SELECT COUNT(*) AS CountBelow60
FROM SC
WHERE Grade < 60;
END;
GO

CREATE PROCEDURE sp_GetAverageGradeByCno
@Cno CHAR(10)
AS
BEGIN
SELECT AVG(Grade) AS AverageGrade
FROM SC
WHERE Cno = @Cno;
END;
GO

CREATE PROCEDURE sp_ConvertGradeToRanking
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 0
END;
END;
GO