数据库系统概论课程之二-计算机中心-福建工程学院

数据库系统原理及应用实验指导书网络工程0801,网络工程0802班
福建工程学院信息科学与工程学院软件工程教研室胡文瑜
2010-2011学年第1学期
目录
实验一认识DBMS系统 1 实验二交互式SQL（1）（DDL子语言实践） 3 实验三交互式SQL（2）（DDL：表的约束定义和DML：查询） 5 实验四交互式SQL（3）（嵌套查询、数据更新和视图）7实验五数据控制（安全性部分） 9 实验六变量与控制流语言11 实验七数据控制（触发器） 13 实验八存储过程和用户自定义函数 14
实验一认识DBMS系统
1 实验目的
1．通过对某个商用数据库管理系统的使用，了解DBMS的工作原理和系统构架。

2．熟悉对DBMS的操作
3．搭建今后实验的平台
2 实验平台
2.1 操作系统：
Windows 2000或者Windows XP
2.2 数据库管理系统：
推荐使用Microsoft公司的SQL Server，目前实验可用的版本可以是SQL Server 2000。

也可以选择国产数据库管理系统，或国外的Oracle, DB 2等。

3 实验内容及要求
3.1 安装和启动
1．学着安装数据库管理系统。

在安装过程中记录安装的选择，并且对所作的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。

2．学会启动和停止数据库服务，思考可以用哪些方式来完成启动和停止。

3．初步了解MS SQL Server的安全性，这里主要是用户的登录和服务器预定义角色。

可以尝试建立一个新的用户，赋予其数据库管理员的角色，今后的实验可以用该用户来创建数据库应用。

3.2 数据库系统的构架
1．了解数据库系统的逻辑组件：
它们主要是数据库对象，包括基本表、视图、触发器、存储过程、约束等。

今后将学习如何操作这些数据库对象。

2．了解数据库的物理组件：
思考数据库物理存储的存储单位是什么，数据主要以什么方式存放，如何确定数据的存放位置。

3.3 MS SQL Server的管理和使用
了解MS SQL Server如何通过它提供的工具对数据和数据库服务器进行管理和使用的。

3.3.1 启动、暂停和停止SQL Server
学会运用企业管理器进行操作。

3.3.2 了解系统其他管理工具
初步了解SQL Server的主要工具和它们的功能，为今后的实验做准备。

例如，数据迁移工具：导入和导出数据使用的；企业服务器和作业调度管理器：可以用该工具对数据库服务器进行管理，管理和监视服务器性能和活动；查询分析器：给出数据库查询语句的查询计划和执行时间，帮助用户分析查询过程，进行查询优化。

等等。

4 实验报告
实验二交互式SQL（1）
（DDL子语言实践）
1实验目的
1．熟悉数据库的交互式SQL工具。

如：SQL Server2000查询分析器。

2．掌握SQL语言的DDL子语言，在SQL Server2000环境下采用Transact-SQL 实现表的定义、删除与修改，掌握索引的建立与删除方法。

2实验内容和要求
1. 在SQL Server2000中建立一个数据库，进行实验所要求的各种操作，所有的
SQL操作均在此建立的新库里进行。

2. 根据以下实验内容的要求认真填写实验报告，记录所有的实验用例。

(1) 设有如下关系表S：
S(NO, NAME, SEX, AGE, CLASS)，主关键字是NO。

其中NO为学号，char(2)，学号不能为空，值是唯一的；NAME为姓名，char(10)，姓名的取值也唯一；SEX为性别，char(2)；AGE为年龄，int；CLASS 为班号，char(5)。

写出实现下列功能的SQL语句。

1)创建表S；
2)插入一个记录（25，“李明”，“男”，21，“95031”）；
再插入一个记录（10，“王丽”，“女”，20，“95101”）；
3)插入“95031”班学号为30，姓名为“郑和”的学生记录；
4)对表S，按学号升序建唯一索引（索引名为sno）；
对表S，按年龄降序建索引（索引名为sage）；
5)向S表添加“入学时间（comedate）”列，其数据类型为日期型（datetime）；
6)删除S表的sage索引；
7)将年龄的数据类型改为smallint；
8)删除学生姓名必须取唯一值的约束；（注意：SQL Server2000与标准SQL
语言有区别）
9)删除S表；
(2) 按照《数据库系统概论》(第四版)P82中的关系模式及数据，创建STUDENT、COURSE和SC表，并插入相应数据。

每一张表都必须有主键约束，分别试着使用列级完整性约束和表级完整性。

3实验提示：
1)要学会使用SQL Server2000的联机丛书。

2)查看具体表的信息可使用如下SQL命令：exec sp_help ＜表名＞;
3)根据实验内容的要求认真填写实验报告，记录所有的实验用例。

4实验报告
实验三交互式SQL（2）
（DDL：表的约束定义和DML：查询）
1实验目的
1)掌握SQL语言的DDL子语言，在SQL Server2000环境下采用Transact-SQL
实现表的定义中主键、外键、唯一值、检查、非空和默认值等列级或表级完整性约束条件的使用。

2)掌握采用Transact-SQL实现查询操作，包括单表查询和连接查询（如：等值
连接、自然连接、自身连接、外连接和复合条件连接）的方法。

2实验内容和要求
2.1按照《数据库系统概论》(第四版)P82中的关系模式及数据，再次创建
STUDENT、COURSE和SC表。

1)每一张表都必须有主键。

2)需要使用外键的表必须使用外键。

3)根据需要适当采用唯一值、检查、非空和默认值约束。

要求这四种约束在
STUDENT、COURSE和SC表这三张表中至少使用一次。

4)根据主键、外键、唯一值、检查、非空和默认值六种约束的特性，设计适
当的方案对STUDENT、COURSE和SC表中的这六种约束进行检验。

方案自定。

一种约束进行检验一次即可。

2.2按照《数据库系统概论》(第四版)P82及P75中的关系模式及数据，实现以
下查询：
1)找出所有供应商的姓名和所在城市。

2)找出所有零件的名称、颜色、重量。

3)查询供应工程J1零件的供应商号码SNO。

4)查询使用红色零件的工程名称。

5)查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些
工程）。

6)查询先行课的学分为4的课程信息。

7)查询选修3号课程且成绩在85分以上的所有学生。

3实验报告
实验四交互式SQL（3）
（嵌套查询、数据更新和视图）
1．实验目的：
1)掌握SQL语言的核心语句－－SELECT语句的使用，具体为在SQL
Server2000环境下采用Transact-SQL实现嵌套查询。

2)掌握采用Transact-SQL实现数据更新。

3)掌握采用Transact-SQL实现视图的定义、删除、查询与更新。

2．实验内容和要求
按照《数据库系统概论》（第4版）P82及P75中的关系模式及数据，完成以下实验：
2.1嵌套查询
1)查询其他系中比计算机科学系所有学生年龄都小的学生姓名与年龄，请
用二种方法实现。

2)查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，请用
二种方法实现。

3)查询供应工程J1红色零件的供应商号SNO，请用三种方法实现。

4)查询至少用了供应商S1所供应的全部零件的工程号JNO，要求用带
EXISTS谓词的子查询实现。

5)查询使用了全部零件的工程号JNO，要求用带EXISTS谓词的子查询实
现。

2.2数据更新
1)将学号为10的学生姓名改为“王华英”；
2)将所有“95101”班号改为“95091”；
2.3视图
请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。

针对该视图完成下列查询：
●找出三建工程项目使用的各种零件代码及其数量；
●找出供应商S1的供应情况；
3．实验报告
实验五数据控制（安全性部分）
1．实验目的：
1) 熟悉通过SQL对数据进行安全性控制。

2) 完成书本上习题的上机练习。

2．实验内容和要求
使用SQL对数据进行安全性控制，包括：授权和权力回收。

操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。

根据以下要求认真填写实验报告，记录所有的实验用例。

2.1 授权与回收
[1] 建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。

1)建立用户U1、U2、U3、U4、U5、U6、U7，选择其数据库角色全部允许为
public。

2)在DBA与七个Connect用户之间进行授权
3)在授权之后验证用户是否拥有了相应的权限。

[2] 回收权限。

将[1]授予的权限部分收回，检查回收后，该用户是否真正丧失了对数据的相应权限。

1)回收权限
2)在回收权限之后验证用户是否真正丧失了该权限。

2.1 数据库角色
[3] 角色的创建与授权。

[3-1] 首先创建一个角色R1
[3-2] 然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT
[3-3] 将这个角色授予U1,U3,U7。

使他们具有角色R1所包含的全部权限。

[3-4] 对角色R1的权限进行修改，增加对Student表的DELETE权限，并回收对Student表的INSERT权限。

[3-5] 删除角色R1。

3．实验报告
实验（六）变量与控制流语言
1．实验目的：
1)掌握Transact-SQL局部变量的声明、赋值和使用方法以及全局变量的运
用方法，掌握Transact-SQL的局部变量的作用域概念。

2)掌握Transact-SQL的控制流语言的使用方法。

3)掌握与控制流语言语句一起使用的其它Transact-SQL 语句：
CASE、/*...*/（注释）、--（注释）、PRINT。

2．实验内容和要求
1)按照《数据库概论》P59中的student表，在批处理中声明两个局部变量＠ssex
和＠@sdept并对它们赋值，要求它们在SELECT 语句的WHERE 子句中被使用，查询的要求是性别为‘女’或所在系为‘IS’系的那些学生。

2)用@@ERROR 检测INSERT 和DELETE 语句的成功操作与否。

3)在student表中执行UPDATE 语句并用@@ROWCOUNT 来检测是否有发
生更改的行。

4)实践使用以下全局变量：@@CONNECTIONS、@@VERSION
5)在SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行
替换。

本题要求仿照《数据库概论》P59中的sc表，根据学生的成绩范围将成绩（grade 列）显示为文本注释。

（要求：＜60分，文本注释为“不及格”，60－70，“及格”，70－80，“中”，80－90，“良”，90－100，“优”）。

6)自选实例，实践并掌握Transact-SQL以下控制流语言的使用方法：
BEGIN...END、WHILE、BREAK、CONTINUE、IF...ELSE、WAITFOR
7)自选实例，实践并掌握以下Transact-SQL语句：
/*...*/（注释）、--（注释）、PRINT
3．实验报告
实验七数据控制（触发器）
1．实验目的：
掌握SQLServer中触发器的使用方法。

熟悉通过SQL的触发器对数据进行功能更强的完整性控制。

2．实验内容和要求
按照《数据库系统概论》（第4版）P75中的关系模式实现以下操作：
1) 创建一个作用在P表上的触发器P_Ins，确保用户在插入或更新数据时所
提供的WEIGHT值是非负和非零的，否则给出错误提示并回滚此操作。

请测试该触发器。

测试方法自定。

2) 利用触发器为教师表Teacher定义完整性规则“教授的工资不得低于4000
元，如果低于4000元，自动改为4000元”。

Teacher表的数据结构如下：Teacher(tno char(5), tname char(10), job char(8),sal smallmoney)
3) 删除以上建的触发器。

4) 查看P表上的触发器。

3.实验报告
实验八存储过程和用户自定义函数
1．实验目的：
1)掌握SQLServer中存储过程的使用方法。

2)掌握SQLServer中用户自定义函数的使用方法。

2．实验内容和要求
2.1 存储过程
对学生课程数据库，编写存储过程，完成下面功能：
1）统计离散数学的成绩分布情况，即按照各分数段统计人数。

2）统计任意一门课的平均成绩。

3）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

4) 查看存储过程的定义和存储过程的信息。

5) 为存储过程改名。

6）删除存储过程。

2.2 用户自定义函数
1)创建一个返回标量值的用户定义函数RectangleArea：输入矩形的长和宽
就能计算矩形的面积。

自选2种实例调用该函数。

2)创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。

该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最
低分和平均分。

调用这个函数，生成相应的报表并给用户浏览。

要求：提交源程序并标识必要的注释。

保证程序能正确编译和运行，认真填写实验报告。

3．实验报告。