5.24实验三 数据库完整性、安全性实现

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

一、实验目的:

使学生加深对数据库安全性和完整性的理解，并掌握 SQL Server 中有关用户、角

色及操作权限的管理方法，学会创建和使用规则、缺省和触发器以及存储过程。

二、实验要求：

通过实验对数据进行完整性控制、安全性维护。

三、实验步骤:

1、 开始→程序→Microsoft SQL Server→SQL Server Management Stdio。

2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进入

SQL Server Management Stdio 操作界面。

3、数据库的安全性实验：设置 SQL Server 的安全认证模式，实现对 SQL Server 的用户

和角色管理，设置和管理数据操作权限。

4、数据库的完整性实验：使用 Transact-SQL 设计规则、缺省、约束和触发器，通过 SQL

Server 管理器定义它们。

创建 students 数据库。利用如下 sql 语句在 students 数据库中创建表（具体含义见课

本 P33 页）

CREATE TABLE Student (

Sno char ( 7 ) PRIMARY KEY,

Sname char ( 10 ) NOT NULL,

Ssex char (2),

Sage tinyint ,

Sdept char (20)

)

CREATE TABLE Course (

Cno char(10) NOT NULL,

Cname char(20) NOT NULL,

Ccredit tinyint ,

Semester tinyint, PRIMARY KEY(Cno)

)

CREATE TABLE SC

(

Sno char(7) NOT NULL,

Cno char(10) NOT NULL,

Grade tinyint,

XKLB char(4) ,

PRIMARY KEY ( Sno, Cno )

)

完成如下操作：

（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 表间来维护参照完整性而使用的一个级联删除触发器、一个级

联修改触发器和一个受限插入触发器。

 

1定义credit属性为"3"的默认值：

     

     ALTER TABLE Course

     ADD CONSTRAINT DF_Course_Credit DEFAULT 3 FOR Ccredit;

     

 

 

2. 建立course与sc表之间的触发器来维护参照完整性：

   - 级联删除触发器：

     ```sql

     CREATE TRIGGER trg_CascadeDelete

     ON Course

     FOR DELETE

     AS

     BEGIN

       DELETE FROM SC WHERE Cno IN (SELECT Cno FROM deleted);

     END;

    

   - 级联修改触发器：

   

     CREATE TRIGGER trg_CascadeUpdate

     ON Course

     FOR UPDATE

     AS

     BEGIN

       UPDATE SC SET Cno = inserted.Cno WHERE Cno IN (SELECT Cno FROM deleted);

     END;

    

   - 受限插入触发器：

 

     CREATE TRIGGER trg_RestrictedInsert

     ON SC

     INSTEAD OF INSERT

     AS

     BEGIN

       -- Add your logic here to restrict inserts based on your requirements

     END;

 

（8）对“students 数据库”编写存储过程，完成下面功能：

① 统计成绩 60 分以下的人数；

CREATE PROCEDURE CountLowScores

AS

BEGIN

    SELECT COUNT(*) AS LowScoreCount

    FROM SC

    WHERE Grade <= 60;

END;

 

② 统计给定 cno 的课程的平均成绩，并返回平均成绩；③将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40

为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。

CREATE PROCEDURE CalculateAverageGrade

@CourseNo CHAR(10)

AS

BEGIN

    SELECT AVG(Grade) AS AverageGrade

    FROM SC

    WHERE Cno = @CourseNo;

END;

 

3. 将sc表中grade从百分制改为等级制（5、4、3、2、1）：

CREATE PROCEDURE ConvertToGradeScale

AS

BEGIN

    UPDATE SC

    SET Grade =

        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;

 

 

(9) 完成课本 P172 页的上机练习。

 

1.在"students"数据库中，创建三张表格

**Teacher表格**：

   列名：Tno（长度为7，主键）、Tname（长度为10，非空）、Tsex（长度为2，取值为男或女）、Birthday（小日期时间整型，允许空）、Dept（长度为20，允许空）、Sid（长度为18，取值不重）。

 

**Course表格**：

   - 列名：Con（长度为10，主键）、Cname（长度为20，非空）、Credit（小整型，大于0）、Property（长度为10，默认值为必修）。

 

**Teaching表格**：

   - 列名：Ton（长度为7，主键列，引用教师表的外键）、Con（长度为10，主键列，引用课程表的外键）、Hours（整数，大于0）。

1. 在授课表中添加一个授课类别列，列名为Type,类型为char(4)。

ALTER TABLE Teaching

ADD Type CHAR(4);

1. 将授课表Type列的数据类型改为char（8)

ALTER TABLE Teaching

ALTER COLUMN Type CHAR(8);

1. 删除课程表的Property列

ALTER TABLE Course

DROP COLUMN Property;

四、体会

这些实验让我更加熟悉了数据库管理的基本概念和操作步骤，同时也加深了我对SQL语言和数据库设计的理解。通过实践，我进一步掌握了如何利用SQL语句和存储过程来管理和操作数据库，提高了我的数据库管理技能和实际应用能力。