数据库系统实验指导书新版

实验一 创建数据库和表

一、实验目的及要求：

1．了解SQL SERVER 数据库的逻辑结构和物理结构

2．了解表的结构特点

3．了解SQL SERVER 的基本数据结构

二、实验任务：

1．学会在企业管理器中创建数据库和表

2．学会在T-SQL 语句创建数据库和表

三、操作要点：

1．启动企业管理器，并创建数据库和表

2．启动SQL 查询分析器

3．掌握创建数据库和表分别是CREATE DATABASE 和CREATE TABLE

四、注意事项：

1．在Microsoft SQL Server 2000的查询分析器(Query Analyzer)中使用单条SQL 语

句，其末尾不需要分号“；”作为命令结尾标记。

2．通常，SQL Server 2000对大多数末尾带有分号的SQL 命令都能顺利执行，但

对少数的SQL 命令，末尾若带分号，则SQL Server 2000会给出错误信息提示。

五、实验学时：2学时

六、实验重点及难点：

1．列级完整性约束条件

2．空值的概念

3．CREATE DA TABASE 和CREATE TABLE 的用法

七、实验步骤：

(一) 企业管理器创建数据库和表

1．启动企业管理器；

2．创建数据库Company ，并创建一个主数据文件Company_Data1，一个二级数据

文件Company_Data2，一个日志文件Company_Log ，以及一个自定义文件组

Company_Group ，该组中有数据文件Company_Data3，设置各文件属性。

(二) T-SQL 语句创建数据库和表

1．启动SQL 查询分析器；

2．创建数据库University ，并创建一个主数据文件University_Data 和一个日志文

件University_Log ；

3. 建立表5.3所示的学生表Students ，各属性为Sno-学号、Sname-姓名、Ssex-性别、Sage-

年龄、Sdept-所在系。要求Sno 和Sname 不能为空值，且取值唯一，Sno 为键码。

表5.3 关系Students

表5.4 关系Courses 表5.5 关系Reports

4.建立表

5.4所示的课程表Courses，各属性为Cno-课程号, Cname-课程名, Pre_Cno-先修课程号, Credits-学分，要求Cno和Cname不能为空值，Cno取值唯一，且为键码。

5.建立表5.5所示的成绩表Reports。各属性名为Sno-学号，Cno-课程号和Grade-考试成绩，要求Sno和Cno不能为空，且取值唯一，Sno+ Cno为键码。

实验二 SQL 基本表的插入、删除与修改

一、实验目的及要求：

熟练掌握基本表的插入、删除与修改，为后继学习作准备。

二、实验任务：

1．了解并掌握SQL 查询分析器及企业管理器的使用;

2．掌握基本表的插入、删除与修改。

三、操作要点：

1.启动SQL 查询分析器

2.掌握进行插入、删除与修改分别是INSERT 、DROP 和UPDATE

四、注意事项：

1.注意每次对表的修改，命令执行完后要查看表的变动刷新表。

2.特别注意在执行插入、删除与修改等更新操作时，要保证数据的完整性。

3.SQL Server 2000 增加了删除属性的命令。

五、实验学时：2学时

六、实验重点及难点

1．T-SQL 语句对基本表的定义进行删除与修改，并插入基本表数据。

2．T-SQL 语句对数据库操作的灵活控制功能。

七、实验步骤：

1．启动SQL 查询分析器；

2．选择SQL SERVER 后，按确认；

3．选择University 数据库；

4．验证如下例题：

4.1 修改基本表

①向基本表Students 中增加“入学时间”属性列，其属性名为Sentrancedate ，数据类型

为DATETIME 型。

②将Sage(年龄)的数据类型改为SMALLINT 型。

③删除Sname(姓名)必须取唯一值的约束。

说明：⑴ 为了保证后面例子能够顺利运行，请大家一定将属性列Sentrancedate 从

Students 表中删除。

⑵ 为了调试SQL 语句方便，这里没有在表Reports 中增加参照完整性约束，甚至没有

表5.3 关系Students

表5.4 关系Courses

定义主键。

4.2 删除Students表。

说明：此表删除后，请立即将其建立起来，以便后面的例子使用。

4.3 向表中添加元组

①将一个学生元组(S01，王建平，男，21，计算机)添加到基本表Students中。

②请同学们用这个命令，按表5.3提供的数据，将其余5个学生的元组也添加到基本表Students中。

③向Courses表插入元组(‘C01’,’英语’,’’,4)。

④请同学们用这个命令，按表5.4提供的数据，将其余6门课程的信息插入Courses表中。

⑤将学习成绩的元组(‘S01’, ’C01’)添加到基本表Reports中。

实验三数据库的查询

一、实验目的及要求

1.熟练掌握SELECT查询语句的使用

2.掌握无条件、有条件查询及查询结果排序与分组

二、实验任务

1．掌握查询语句的一般格式。

2．掌握无条件、有条件查询及查询结果排序与分组

三、操作要点

1.进行查询时先进入查询分析器

2.明白了解无条件、有条件查询及查询结果排序与分组

四、注意事项

1.SELECT语句的基本语法格式

2.SELECT语句的执行方法

3.SELECT语句和ORDER BY子句的作用和用法

4.SELECT语句和GROUP BY子句的作用和用法

五、实验学时：4学时

六、实验重点及难点

1.SELECT语句的基本语法

2.掌握数据汇总的方法

七、实验步骤

1、查询全体学生的详细记录。

2、查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。

3、查询选修了课程的学生学号。

4、查询数学系全体学生的学号(Sno)和姓名(Sname)。

5、查询所有年龄在19~22岁(包括19岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。

6、查询年龄不在19-22岁之间的学生姓名(Sname)及年龄(Sage)。

7、查询自动化系和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。

8、查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。

9、查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。

10、查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。

11、假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。

12、查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。

13、求学生的总人数

14、求选修了课程的学生人数

15、求课程和选修了该课程的学生人数

16、求选修超过2门课的学生学号

17、查询计算机科学系的学生及年龄不大于19岁的学生

18、查询计算机科学系的学生与年龄不大于19岁的学生的交集

19、查询计算机科学系的学生与年龄不大于19岁的学生的差集

20、查询选修课程‘C01’的学生集合与选修课程‘C02’的学生集合的交集

21、查询选修课程‘C01’的学生集合与选修课程‘C02’的学生集合的差集

实验四连接、嵌套和集合查询

一、实验目的及要求：

1．熟练掌握连接、嵌套和集合查询的使用。

2．学习各种连接、嵌套和集合查询方法。

二、实验任务

1．掌握连接、嵌套和集合查询语句的一般格式。

2．掌握连接、嵌套和集合查询的各种使用方法。

三、操作要点

1．创建表时候,在查询分析器中输入程序,编译时查看附录的出错信息再进行修改。

2．函数除了系统函数以外也可以自定义函数。

3．为了避免程序丢失,把程序保存在到规定的目录中去。

四、注意事项

1．注意T-SQL各种运算符、控制语句的功能及使用方法。

2．注意各种系统函数的调用方法。

五、实验学时：4学时

六、实验重点及难点

1．明白两个表之间的联系是通过公共属性实现的。

2．连接、嵌套和集合查询的各种使用方法。

3．多重嵌套的使用。

七、实验步骤

1、求学生号以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。

2、求选修了课程”c01”且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

3、求选修了课程的学生的学生姓名、课程号和成绩。

4、求年龄大于’李伟’的所有学生的姓名、系和年龄。

5、求选修了课程’C02’或’C03’的学生的学号、课程号、课程名和成绩。

6、求与‘李伟’年龄相同的学生的姓名和系。

7、求选修了课程名为’数据结构’的学生的学号和姓名。

8、求比数学系中某一学生年龄大的学生的姓名和系。

9、求比数学系中全体学生年龄大的学生的姓名和系。

10、求选修了课程C04的学生的姓名和系。

11、求与‘黄河’同系且同龄的学生的姓名和系。

12、求与‘黄河’同系，且年龄大于‘黄河’的学生的信息。

13、求数学系中年龄相同的学生的姓名和年龄。

14、检索至少选修所有授课程中一门课程的男学生姓名。

15、检索选修某课程的学生人数多于2人的课程名称。

16、查询被一个以上的学生选修的课程号。

17、求未选修课程’C04’的学生的姓名。

18、求选修了全部课程的学生的姓名。

19、求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。

20、查询所有未选课程的学生姓名和所在系。

实验五数据库系统设计

一、实验目的及要求

1、通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。

2、通过实验使学生加深对数据安全性的理解，并掌握SQL Server中有关用户，角色及操作权限的管理方法。

3、通过实验使学生了解SQL Server的数据备份和恢复机制，掌握SQL Server中数据库备份和恢复的方法。

4、根据数据库系统设计的基本步骤，完成某一具体数据库系统的需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施和数据库运行维护等功能。

二、实验任务

1、使用SQL设计触发器，通过SQL Server企业管理器定义它们。

2、在SQL Server企业管理器中，设置SQL Server的安全认证模式。通过SQL Server

企业管理器，实现对SQL Server的用户和角色管理。分别通过SQL Server企业管理器和SQL的数据控制功能，设置和管理数据操作权限。

3、用企业管理器创建一个备份设备。利用向导为学生选课数据库生成一个完全备份。

在企业管理器中恢复学生选课数据库

4、完成某一具体系统的需求分析、概念结构设计、逻辑结构设计、物理结构设计、数

据库实施和数据库运行维护等功能，并提交数据库。

三、实验学时：8学时

四、实验内容及步骤

（一）创建和使用触发器

（1）实验的内容

使用SQL设计触发器，通过SQL Server企业管理器定义它们。

具体完成下面例题：利用触发器来保证学生选课库中选课表的参照完整性，以维护其外码与参照表中的主码一致。

CREATE TRIGGER SC_inserted ON 选课

FOR INSERT

AS IF（SELECT COUNT（*）

FROM 学生，inserted，课程

WHERE 学生.学号= inserted.学号AND inserted.课程号= 课程.课程号）=0 ROLLBACK TRANSACTION

（2）实验步骤

1、在企业管理器中创建触发器

1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。

图触发器属性对话框

如果要新建触发器，在文本[T]的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。

3）如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本[T]的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。

4）如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本[T]的文本框中的内容，看是否选得正确，最后单击“删除”按钮。

2、验证触发器

1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库）。此时，可在选课表中输入一条记录（学号：’96001’，课程号：’C8’，成绩：88）。

显然，这条记录是不符合参照完整性的。通过企业管理器输入时，会出现错误提示，违反了数据库的约束（如果约束C5和C6同时存在的话）。

2）如果约束C5和C6存在，则删除之

3）打开SQL查询分析器，输入语句“INSERT INTO 选课V ALUES（‘96001’，‘C8’，88）”，查看执行结果。

4）删除触发器（本例为SC_inserted），重复步骤3。

（二）数据安全性

1．在SQL Server企业管理器中为所属的SQL 服务器设置为SQL Server和Windows NT混合安全认证模式。其步骤如下：

1）在企业管理器窗口中展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，则出现SQL Server属性对话框，如图1所示。

图1 SQL Server属性对话框

2）在SQL Server属性对话框中选择“安全性”选项卡。

3）身份验证选择“SQL Server和Windows”单选项。

2．在SQL Server企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。

1）在企业管理器窗口中展开服务器组，展开服务器，用鼠标单击“安全性”文件夹右侧的‘+’，用鼠标右击“登录”，在弹出的菜单中选择“新建登录”项，

则出现新建登录对话框，如图2所示。

图2 新建登录对话框

2）在新建登录对话框中有常规，服务器角色和数据库访问三个选项卡。

在常规选项卡中，输入用户名（本例为王平），选择SQL Server安全验证，输入用户口令。

在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用缺省值即可。

在数据库访问选项卡中，需要指定此登录可以访问的数据库（本例选中学生选课数据库）和“学生选课”的数据库角色（在本例中采用缺省值即可）。

单击“确定”按钮，既完成了创建登录用户的工作。

3．关闭SQL Server企业管理器，打开SQL查询分析器。选择SQL Server安全验证，用户名为王平，输入用户口令，连接到SQL Server。在“查询”窗口中可以输入SQL

语句。如“SELECT * FROM 学生”。运行后，得到消息“拒绝了对对象‘学生’（数

据库‘学生选课’，所有者‘dbo’）的SELECT权限。”。可见用户王平没有对学生

表的SELECT权限。

4．要想将学生选课数据库的操作权限赋予数据库用户王平，有两种方法。

方法一：通过企业管理器

1）在企业管理器窗口中展开服务器组，展开服务器，用鼠标单击“数据库”文件夹右侧的‘+’，用鼠标单击学生选课数据库文件夹右侧的‘+’，用鼠标右击“用

户”。在屏幕右侧的“用户”窗口中选择“王平”项，用鼠标右击，在弹出的

菜单中选择“属性”项，则出现数据库用户属性对话框，如图3所示。

图3 数据库用户属性对话框

2）单击图3中的权限按钮，则出现数据库用户权限对话框，如图4所示。对话框的下面是有关数据库用户和角色所对应的权限表。这些权限均以复选框的形式

表示。复选框有三种状态：“√”为授权；“×”为废除权；空为撤权。在表中

可以对用户或角色的各种对象操作权（SELECT，INSERT，UPDATE，DELETE，

EXEC和DRI）进行授予或撤消。

图4 数据库用户权限对话框

3）在图4中找到学生表，授予SELECT权限，即让学生表与SELECT列交叉的复选框为“√”即可。

方法二：通过SQL的数据控制功能

对用户王平授权，必须是数据库对象拥有者以上用户授予。我们可以以系统管理员或sa用户登录进入查询分析器。在查询分析器中输入授权语句“GRANT SELECT ON 学生TO 王平；”，然后执行即可。

（三）数据库备份和恢复实验步骤

1、在企业管理器中创建一个备份设备

1）在企业管理器中扩展服务器，在管理文件夹中找到备份文件夹。用鼠标右键单击该文件夹，在弹出的菜单中选择“新建备份设备”项，则出现“备份设备属性”对话框，如图所示。在名称文本框中输入设备名（本例为学生选课设备1）。单击“确定”按钮，备份设备就创建成功了。

图“备份设备属性”对话框

2）在备份文件夹的窗口中出现了新创建的设备。选中该设备，用鼠标右键单击，在弹出的菜单中选择“属性”项，则出现“备份设备属性”对话框。

2、利用向导为学生选课数据库生成一个完全备份。

1）在企业管理器中扩展服务器，在数据库文件夹中选中要备份的数据库文件夹（学生选课数据库）。选择菜单[工具]|[向导]，则出现向导选择对话框。在向导选择对话框中，单击管理左边的“+”号，使之展开。选择“备份向导”项，单击“确定”按键。

2）进入创建备份向导后，首先出现的是欢迎使用创建数据库备份向导对话框，其中简单介绍了该向导的功能。单击“下一步”后，就会出现“选择要备份数据库”对话框。选择好要备份数据库后，单击“下一步”，就会出现“键入备份的名称和描述”对话框。键入备份名称为“学生选课备份”，单击“下一步”，就会出现“选择备份类型”对话框。

这里我们选择“数据库备份”，单击“下一步”，就会出现“备份验证和调度”对话框。

这里我们选择默认状态，单击“下一步”，就会出现“正在完成备份数据库向导”对话框。单击“完成”按钮，完成备份数据库向导，生成数据库备份。

3、在企业管理器中恢复学生选课数据库。

1）打开学生选课数据库，删除其中一个表（如选课表），即当前的学生选课数据库中没有选课表。

2）恢复学生选课数据库。步骤如下：在企业管理器中扩展服务器，在数据库文件夹中选中要备份的数据库文件夹（学生选课数据库）。用鼠标右键单击，在弹出的菜单中选择“所有任务”项，在随之出现的级联菜单中选择“还原数据库”项，则出现还原数据库对话框。在此例中按“确定”按钮即可完成恢复工作。

3）再打开学生选课数据库，看一看当前的学生选课数据库中有没有选课表

4）修改学生选课数据库备份计划，要求每月1号进行数据库备份，重复步骤2、3。

5）用其他方法生成备份。

（四）分组完成一具体数据库系统的需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施和数据库运行维护等，并在SQL Server环境中实现数据库及相关功能。具体要求如下：

1）描述系统的需求，可选择数据流图和数据字典；

2）产生系统的E－R图（至少有4个以上的实体）；

3）根据E－R图产生至少满足3NF数据基本表，并说明理由；

4）数据库实施（保证数据的完整性）；

5）根据需要至少生成2个角色，4个用户，并对用户进行授权；

6）数据库运行和维护，包括数据库备份和还原。

每位学生需要提交备份的数据库和相关设计文档。