《数据库系统原理及应用教程》(苗雪兰等，第五版) 实验六：数据库的视图和关系图定义及使用实验

阅读前注意：

1. 本实验报告配套《数据库系统原理及应用教程》(苗雪兰等，第五版) 实验六：数据库的视图和关系图定义及使用实验（书上10.4节以及第八章部分内容），书本中采用Microsoft SQL Server Management Studio 2008，实验报告中采用Microsoft SQL Server Management Studio 2019。

2. 如果你的数据库课程选用的书目是这本书的话，那么大概率这会是你的实验作业。建议先自己独立完成后再作参考，数据库这门课是需要自己动手的。

3. 实验报告中可能增加了一些书中没有的附加内容，读者可以有兴趣自行完成。

4. 实验报告仅供初学者参考。

5. 请谅解实验报告中可能存在的问题或错误，欢迎指出，欢迎交流讨论。

一、实验目的

本实验的目的是使学生掌握SQL Server中的视图创建工具和数据库关系图创建工具的使用方法，加深对视图和关系图的理解；通过实验使学生加深对数据安全性和完整性的理解，熟练掌握SQL SERVER 中有关用户、角色及操作权限的管理方法，以及数据库备份和恢复的方法。

二、实验内容

按教材P300 10.4.6的内容完成基本操作实验，实验内容包括：
1）创建、查看、修改和删除视图；
2）创建、编辑和删除数据库关系图；
3) 按教材P301 10.4.7，的内容完成基本操作实验，熟练掌握SQL SERVER 中有关用户、角色及操作权限的管理方法，以及数据库备份和恢复的方法。

三、实验过程

1. 创建视图实验

1）在SQL Server Management Studio中，选中图书读者数据库下的视图对象，调出创建视图工具，在图书读者库中通过Transact-SQL描述创建读者视图。

下面列出该实验常见的两种错误及解决方法：

错误一：

错误原因：“CREATE VIEW”必须是批处理中仅有的语句。这一段代码中出现了不是用于批处理的语句SELECT * FROM Book。

解决方法：删除语句SELECT * FROM Book，保证语句中仅有批处理语句。（或者选中后运行）

错误二：

错误原因：各视图或函数中的列名必须唯一。在视图或函数’Reader_VIEW’ 中多次指定了列名’b_number’。下图是Book、Loan表的各列：

能看出如果按照上面的代码执行的话，b_number被指定了两次。为了避免该情况，可对SELECT后的代码段进行调整。详见下面的代码段。

运行下面这段代码以创建视图：

CREATE VIEW Reader_VIEW
AS SELECT Book.*, Loan.r_number, Loan.l_date 
FROM Book, Loan, Reader
WHERE Book.b_number=Loan.b_number 
AND Loan.r_number=Reader.r_number

运行结果如下：

2）在SQL Server Management Studio中，选中图书读者数据库下的视图对象，调出创建视图工具，在图书读者库中通过Transact-SQL描述的视图定义，创建借阅_计算机图书视图。

运行代码：

CREATE VIEW Loan_Book_of_计算机
AS SELECT Book.*, Loan.r_number, Loan.l_date
FROM Book, Loan, Reader
WHERE Book.b_number=Loan.b_number AND Book.b_type='计算机'

执行结果：

3. 数据库安全性实验

（1）设置服务器安全认证模式

在SQL Server Management Studio中，为正在使用的SQL服务器设置Windows安全认证模式。

右键点击“对象资源管理器”中的服务器，选择“属性”。

在“属性”卡片中，左侧选择页选择“安全性”，服务器身份验证选择“SQL Server和Windows身份验证模式”，点击确定即可完成设置。

（2）建立用户和角色

1）为正在使用的SQL服务建立新登录名（“login1”），使用SQL Server身份验证。

展开“对象资源管理器”中的服务器，展开“安全性”，右键点击“新建登录名”。

“常规”页中，选择SQL Server身份验证，登录名“login1”，录入密码“login1”，点击确定。

2）学生课程库中，新建数据库用户名“user1”（登录名“login1”），新建数据库角色名“rose1”。

展开服务器的“数据库”，展开学生课程数据库的“安全性”，右键“用户”，点击新建用户。

录入用户名和登陆名。

选择“角色”中的“数据库角色”，新建数据库角色。

录入角色名，点击确定。

3）在图书读者数据库中，新建数据库用户名“user2”（登录名“login1”），新建数据库角色名“rose2”。

按照2）的方式进行操作。

（3）设置权限

1）使新登录“login1”加入到System Administrators服务器角色中，可访问学生课程数据库和图书读者数据库。

重新登陆SQL Server，选择SQL Server身份验证，输入用户名和密码，连接即可。

2）学生课程数据库中，将“user1”加入“rose1”和db-owner，通过选择角色对“rose1”授权。

用户user1的属性中，搜索安全对象，在对象类型中加入数据库角色。

在“浏览”中加入rose1和db_owner:

在下面的框中勾选需要授权的权限即可。

3）在图书读者数据库中，将“user2”加入“rose2”和db-owner，通过对象（选择图书、读者和借阅表）对“rose2”授权。

用户user2的属性中，搜索安全对象，在对象类型中加入数据库角色。

在“浏览”中加入rose2和db_owner:

在下面的框中勾选需要授权的权限即可。

4）使“rose2”和“rose1”都有创建表、创建视图和备份数据库的权限。

在“rose2”和“rose1”的属性中，“安全对象”里先搜索“安全对象”，在特定对象中选择对象类型数据库和表，选定对应的数据库和表，在下方创建表、创建视图和备份数据库的权限对应的方框打钩后确定即可。

选择学生课程数据库“角色”中的rose1，右键点击“属性”。

点击“搜索”，选择“特定对象”，确定。

点击“对象类型”，添加数据库和表。点击“浏览”，选择对象中选定学生课程数据库和其对应的3个表，确定。

在权限对应的地方勾选即可。rose2操作同理：

（4）数据库完整性试验

建立学生选课数据库中选课表的插入数据类型触发器，保证学生选课库中选课表的参照完整性，以维护其外码与参照表中的主码一致。

代码如下：

CREATE TRIGGER SC_insert ON Select_Course
FOR INSERT
    AS IF(SELECT COUNT(*)
	    FROM Student, inserted, Course
		WHERE Student.s_number=inserted.s_number
		AND Course.c_number=inserted.c_number)=0
ROLLBACK TRANSACTION