实验三 数据库完整性、安全性实现（1）

以下是可能的代码示例：

1. 创建数据库students、创建表Student、Course和SC

CREATE DATABASE students
GO

USE students
GO

CREATE TABLE Student (
 Sno char ( 7 ) PRIMARY KEY,
 Sname char ( 10 ) NOT NULL,
 Ssex char (2), 
 Sage tinyint ,
 Sdept char (20)
)
GO

CREATE TABLE Course (
 Cno char(10) NOT NULL,
 Cname char(20) NOT NULL,
 Ccredit tinyint ,
 Semester tinyint,
 PRIMARY KEY(Cno) 
) 
GO

CREATE TABLE SC 
(
 Sno char(7) NOT NULL,
 Cno char(10) NOT NULL,
 Grade tinyint,
 XKLB char(4) , 
 PRIMARY KEY ( Sno, Cno )
)
GO

2. 创建新的Windows登录名，并配置它的权限

USE master
GO

CREATE LOGIN new_login WITH PASSWORD='<enter_new_password_here>', DEFAULT_DATABASE=students
GO

USE students
GO

CREATE USER new_user FOR LOGIN new_login
GO

GRANT SELECT, INSERT, UPDATE ON Student (Sname, Ssex, Sage) TO new_user
GO

EXEC sp_addrolemember '<自己学号-role>', 'new_user'
GO

3. 设置SQL Server为混合认证安全认证模式

在SSMS中选择“服务器属性” -> “安全性” -> “服务器身份验证”，并选择“SQL Server和Windows身份验证模式”

4. 用刚才创建的登录名登录服务器

在SSMS中选择“连接到数据库引擎”，输入刚才创建的登录名和密码

5. 创建一个students数据库用户（自己学号-user）和角色（自己学号-role）

USE students
GO

CREATE USER '<自己学号-user>' FOR LOGIN '<自己学号>'
GO

CREATE ROLE '<自己学号-role>'
GO

6. 用管理器实现权限的分配

USE students
GO

GRANT SELECT ON STUDENT TO '<自己学号-role>'
GO

GRANT SELECT ON COURSE TO '<自己学号-role>'
GO

GRANT SELECT ON SC TO '<自己学号-role>'
GO

DENY UPDATE ON SC(grade) TO '<自己学号-role>'
GO

EXEC sp_addrolemember '<自己学号-role>', '<自己学号-user>'
GO

7. 创建触发器

USE students
GO

-- 定义course表中credit属性为“3”的默认值。
ALTER TABLE COURSE ADD CONSTRAINT DefaultCredit DEFAULT 3 FOR Ccredit
GO

-- 建立course与sc表间来维护参照完整性而使用的一个级联删除触发器、一个级联修改触发器和一个受限插入触发器。

CREATE TRIGGER tr_delete_sc ON COURSE
FOR DELETE
AS
BEGIN
    DELETE FROM SC WHERE Cno IN (SELECT Cno FROM deleted)
END
GO

CREATE TRIGGER tr_update_sc ON COURSE
FOR UPDATE
AS
BEGIN
    UPDATE SC SET Cno = inserted.Cno WHERE Cno = deleted.Cno
END
GO

CREATE TRIGGER tr_insert_sc ON SC
FOR INSERT
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM COURSE WHERE Cno = inserted.Cno)
    BEGIN
        RAISERROR ('Invalid course number', 16, 1)
        ROLLBACK TRANSACTION
    END
END
GO

8. 编写存储过程

USE students
GO

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

CREATE PROCEDURE AvgCourseGrade
    @cno char(10),
    @avg_grade float output
AS
BEGIN 
    SELECT @avg_grade = AVG(Grade) 
    FROM SC 
    WHERE Cno = @cno
END
GO

CREATE PROCEDURE ConvertToGrade
AS
BEGIN 
    UPDATE SC SET XKLB = 
    CASE 
        WHEN grade<=20 THEN '1'
        WHEN grade<=40 THEN '2'
        WHEN grade<=60 THEN '3'
        WHEN grade<=80 THEN '4'
        ELSE '5'
    END
END
GO