《数据库原理》试验指导书

本文格式为Word版，下载可任意编辑——《数据库原理》试验指导书

数据库原理试验指导书

（16学时）

信息管理与决策研究所综合试验中心

编制

东北大学工商管理学院

目录

试验一数据库的建立与完整性1

试验二TRANSACT-SQL及其程序设计基础（一）6

试验三TRANSACT-SQL及其程序设计基础（二）10

试验四索引、视图存储过程和触发器的使用13

试验五数据库的安全性21

试验六数据库的备份、恢复和维护25

试验七查询规则及查询优化32

试验八数据库应用程序设计简介38

—1—

试验一数据库的建立与完整性

试验一第1部分——运行环境的建立及配置

一、试验目的

本试验主要陈述数据库和数据模型的有关概念及数据库系统的结构。通过本试验，学生应当把握以下内容：数据库和数据模型的基本概念、数据模型的三要素、概念模型的表示方法、数据库系统的模式结构与体系结构、DBMS的功能与组成。具体包括了解和熟悉DBMS，了解连接数据库服务器的身份验证模式，熟悉样例数据库，体会DBMS的三层模式结构等。

二、试验准备

数据模型由三个要素组成：数据结构、数据操作和完整性约束。1、数据结构

数据结构用于描述系统的静态特性，是所研究的对象类型的集合。数据模型按其数据结构分为层次模型、网状模型和关系模型。

2、数据操作

数据操作用于描述系统的动态特性，是指对数据库中各种对象的实例允许执行的操作的集合，包括操作及有关的操作集合。

3、数据的约束条件

数据的约束条件是一组完整性规则的集合。完整性规则是给定的数据及其联系所具有的制约和存储规则，用以限定符合数据库状态以及状态的变化，以保证数据的正确、有效和相容。

数据库系统的三级模式结构

数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三级组成。

1、外模式。外模式也称子模式或用户模式，它是数据库用户（包括应用程序员和最终用户）看见和使用的局部数据的规律结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的规律表示。一个数据库可以有多个外模式。

2、模式。模式也称规律模式，是数据库中全体数据的规律结构和特征的描述，是所有用户的公用数据视图。一个数据库只有一个模式。

3、内模式。内模式也称存储模式，它是数据物理和存储结构的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模式。

DBMS的功能1、数据定义

数据定义包括定义构成数据库结构的外模式、模式和内模式，定义各个外模式与模式之间的映射，定义模式与内模式之间的映射，定义有关的约束条件（例如，为保证数据库中数据具有正确语义而定义的完整性规则，为保证数据库安全而定义的用户口令和存取权限等）。

2、数据操纵

数据操纵包括对数据库数据的查询、添加、修改和删除等基本操作。3、数据库运行管理

对数据库的运行进行管理是DBMS运行时的核心部分，包括对数据库进行并发控制、安全性检查、完整性约束条件的检查和执行、数据库的内部维护（如索引、数据字典的自动维护）等。所有访问数据库的操作都要在这些控制程序的统一管理下进行，以保证数据的安全性、完整性、一致

—1—

性以及多用户对数据库的并发使用。

4、数据组织、存储和管理

数据库中需要存放多种数据，如数据字典、用户数据、存取路径等，DBMS负责分门别类地组织、存储和管理这些数据，确定以何种文件结构和存取方式物理地组织这些数据，如何实现数据之间的联系，以便提高存储空间利用率以及提高随机查找、顺序查找、增、删、改等操作的时间效率。

5、数据库的建立和维护

建立数据库包括数据库初始数据的输入与数据转换等。维护数据库包括数据库的转储与恢复、数据库的重组织与重构造、性能的监视与分析等。

6、数据通信接口

DBMS需要提供与其他软件系统进行通信的功能。例如，提供与其他DBMS或文件系统的接口，从而能够将数据转换为另一个DBMS或文件系统能够接受的格式，或者接收其他DBMS或文件系统的数据。

三、试验内容

1、熟悉网络环境和数据库环境数据库服务器：

操作系统：Windows2000AdvancedServerIP地址：202.201.224.123

数据库管理系统：MicrosoftSQLServer2000服务器名称：HP客户机:

操作系统：Windows2000ProfessionalIP地址：202.201.X.X

MicrosoftSQLServer2000客户端

启动MicrosoftSQLServer2000查询分析器：

1、开始->程序->MicrosoftSQLServer2000->查询分析器出现如下画面2、连接到MicrosoftSQLServer2000

SQLServer：HP（或202.201.224.123）

选择SQLServer身份验证；登录名：student密码：student

—2—

2、在查询分析器的编辑窗口中输入创立数据库的SQL语句：

3、身份验证模式：在安装SQLServer2000过程中，出现[身份验证对话框]，提醒选择身份验证模式。Windows身份验证模式或混合模式（Windows身份验证和SQLServer身份验证）并提醒添加sa用户登陆密码（一般选中空密码）。安装完SQL后，开启企业管理器，逐级展开节点到[安全性]|[登陆]，在右边视图中可以看到登陆的用户名称，右击sa用户，开启属性，弹出属性对话框，可以看到当前用户的身份验证模式。

4、体会DBMS的三层模式结构：（以理解为主）从数据库管理系统角度看，数据库描述由三级抽象模式组成：概念模式（规律模式）、物理模式（内模式）和外模式；从数据库最终用户角度看，数据库系统的结构分为单用户结构、主从式结构、分布式结构和客户/服务器结构。概念模式体会：开启任意所建立[数据库]，选择一个[表]，右击[设计表]，开启[设计表]框。这里描述了存储的数据的属性和实体及实体关系。物理模式：描述存储细节。外模式：视图和来自概念模式的关系组成。

5、安装SQLServer2000后，安装程序将在所创立的数据库实例中创立数据库和日志文件。Master、Model、Msdb和Tempdb都是系统数据库。Pubs和Northwind例如数据库作为学习工具提供。我们提供基于样例数据库Northwind的一些操作。开启[企业管理器]|[数据库][Northwind]。Northwind例如数据库包含了一个名为NorthwindTraders的虚构公司的销售数据，开启[表]，可以看到该数据库包括了Categories、Customers等13张用户表和Alphabeticallistproduct、CategoryScalefor1997等16张视图。可以任意开启用户表查看信息。

6、从[开始]|[程序]|MirosoftSQLServer|联机丛书。学习数据字典。

试验一第2部分——数据的完整性

一、试验目的：通过本试验，学生将

1.把握数据完整性的基础知识

2.学会使用约束来实施数据完整性的保护3.把握使用默认值实现数据完整性的方法4.熟练使用规则实施数据完整性的保护

二、试验准备：1、四种完整性约束

数据完整性一共有四种形式：

实体完整性（ENTITYINTEGRITY）值域完整性（DOMAININTEGRITY）

—3—

四、思考题

学生——课程数据库中包括三个表。①学生表：Student（Sno,Sname,Ssex,Sage,Sdept）；②课程表：Course（Cno,Cname,Ccredit）；③选课表：SC（Sno,Cno,Grade）。请依照要求写出相应的SQL语句。

1.查询计算机系年龄在20岁以下的学生的姓名2.查询姓刘且全名为三个汉字的学生的姓名

3.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。4.查询选修了3门以上课程的学生的学号

5.查询选修2号课程且成绩在90分以上的学生学号和姓名

6.查询与刘晨在同一个系学习的学生的学号、姓名（要求用子查询实现）。7.插入一条选课记录（‘95020’，‘1’）。8.将所有学生的年龄增加1岁。

9.从学生表中删除所有计算机系学生的信息。

—9—

试验三Transact-SQL及其程序设计基础（二）

一、试验目的：

同试验二

二、试验准备

同试验二

三、试验内容SELECT（查询）

1、把握SELECT语句的基本用法

2、查询你的各个表中所有的记录数目。

3、查询你自己的平均成绩（用avg（）函数）

4、查询你班级所有同学的平均成绩，查询输出以别名：学号，姓名，平均成绩；并按平均成绩由高到低排序（用到orderby）

5、查询没有成绩记录的学生，查询输出：学号，姓名、的在班级简称。

6、查询你自己的各课成绩，结果格式如下：

7、查询你班两门及两门以上课程不及格的学生，查询输出：学号，姓名、不及格课程数量。

—10—

8、查询你班男生人数。

9、查询你班女生人数。

10、查询班级Class_IDlike'02%'的班级。

11、请查询输出：班级编号，班级名称，男生，女生。

12、请查询输出：班级编号，班级名称，男生，女生，合计。

13、执行以下查询，解释该查询的含义

四、思考题

学生——课程数据库中包括三个表。①学生表：Student（Sno,Sname,Ssex,Sage,Sdept）；②课程表：Course（Cno,Cname,Ccredit）；③选课表：SC（Sno,Cno,Grade）。请依照要求写出相应的SQL语句。

1.查询计算机系年龄在20岁以下的学生的姓名2.查询姓刘且全名为三个汉字的学生的姓名

3.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。4.查询选修了3门以上课程的学生的学号

5.查询选修2号课程且成绩在90分以上的学生学号和姓名

6.查询与刘晨在同一个系学习的学生的学号、姓名（要求用子查询实现）。7.插入一条选课记录（‘95020’，‘1’）。

—11—

8.将所有学生的年龄增加1岁。

9.从学生表中删除所有计算机系学生的信息。

—12—

试验四索引、视图存储过程和触发器的使用

一、试验目的:

1.2.3.4.

把握视图的概念和使用方法；把握索引的概念和使用方法；把握存储过程的概念和使用方法；把握触发器的概念和使用方法；

二、试验准备:

1.了解视图的概念和作用;2.把握视图相关的命令;3.了解索引的作用与分类；4.把握索引的创立方法；

5.理解数据完整性的概念及分类；6.了解各种数据完整性的实现方法；7.了解存储过程的使用方法；8.把握存储过程的调用方法;9.了解触发器的使用方法；

三、试验内容:一、视图1.创立视图

（1）在STUDENT表中，为信息管理及信息系统022班建立视图V1_STU，在查询分析器中输入以下语句:

（2）创立V3_STU_COU视图，包括学号，课程号，课程名，成绩，要保证对该视图的修改都要符合班为信息管理及信息系统022班这个条件:

—13—

2.查询视图

（1）查找信息管理及信息系统021班女生的学号和出生日期:

—14—

3.更新视图

可更新视图满足以下条件:A）创立视图的SELECT语句中没有聚合函数，且没有TOP、GROUPBY、UNION子句及DISTINCT关键字。

B）创立视图的SELECT语句中不包含从基本表列通过计算所得的列。C）创立视图的SELECT语句的FROM子句中至少要包含一个基本表。

（1）向V1_STU插入一条记录（‘024631267’，’0246312’，’刘明仪’，’男’，’1982-3-2’，’NANCHANGROAD42#’，’830052’，’刘林’）:

注:

1）当视图所依靠的基本表有多个时，不能向该视图插入数据.

2）向可更新的分区视图中插入数据时，系统会依照插入记录的键值所属的范围，将数据插入到其键值所属的基本表中。

—15—

（2）将信管992班中学号为994631209的学生的14110003课程成绩改为90分:

（3）删除V2_STU中女同学的记录:

4.修改视图定义

（1）将V1_STU修改成只包含信息管理及信息系统022班的学号和姓名:

（2）删除V4_AVG视图

—16—

二.索引

1.对STUDENT表的STUDENT_ID列建立索引，在查询分析器编辑窗口中输入以下程序并执行:

三.创立触发器

对于STUDENT数据库，表STUDENT的CLASS_ID列与表CLASS的CLASS_ID满足以下参照完整性规则:

A.向STUENT表插入或修改一记录时，通过触发器检查记录的CLASS_ID值在CLASS表中是否存在，若不存在，则取消插入或修改操作；

B.修改CLASS表的CLASS_ID字段值时，该字段在STUDENT表中的对应值也做相应修改；C.删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在STUDENT表中对应的记录；在查询分析器编辑窗口输入以下触发器的代码并执行。（1）向STUENT表插入或修改一记录时，通过触发器检查记录的CLASS_ID值在CLASS表中是否存在，若不存在，则取消插入或修改操作：

—17—

（2）修改CLASS表的CLASS_ID字段值时，该字段在STUDENT表中的对应值也做相应修改：

（3）删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在CLASS表中对应的记录：

四.创立存储过程

1.添加学生记录的存储过程STUDENTAdd：

2.修改学生记录的存储过程STUDENTUpdate：

—18—

3.删除学生记录的存储过程STUDENTDelete：

4.调用三个存储过程：

四、思考题

根据本试验的内容，课后自己完成下面的练习

1.在STUDENT表中，为计算机科学与技术021建立视图SV1_STU。

2.在TUITION表中，为第五学年缴全额学费（3500）的学生建立视图SV2_TUI。

—19—

3.在STUDENT和CLASS两表中，建立视图SV3_STU_CLA，包含学号，班号，学院号，姓名，

性别。

4.编写对TUITION表的插入、修改、删除操作的存储过程，然后调用这些存储过程。5.查找SV3_STU_CLA视图中学号为024631416所在的班号和学院号。6.查找信管992班平均成绩在80分以上的学生的学号和平均分数:

7.在STUDENT和CLASS两表中，建立视图SV3_STU_CLA，包含学号，班号，学院号，姓名，

性别。

8.向SV1_STU视图插入一条记录。

9.删除SV3_STU_CLA中，班号为'0246312'的记录。10.将SV3_STU_CLA修改成只包含学号，班号，学院号11.删除SV3_STU_CLA视图。

12.为COURSE表中的COURSE_ID建立索引COU_IND。

13.COURSE表与GRADE表中的COURSE_ID列满足参照完整性规则，用触发器实现两表间的参

照完整性。

—20—

试验五数据库的安全性

一、试验目的：

1.了解SQLServer2000中的两种用户登录模式；

2.熟悉SQLServer2000中登录帐户、用户、角色、权限的概念；

3.把握SQLServer2000中创立帐户、数据库用户的方法，会使用角色来分派权限

一、二、试验准备：

SQLServer的用户权限控制是SQLServer一个最重要的管理概念，也是其众多管理任务之一。SQLServer的登录方式有标准SQLServer登录和集成Windows系统登录两种。

SQLServer集成的安全登录模式即将Windows系统的用户和工作组映射为SQLServer的登陆帐户。Windows集成登录模式实际上是让Windows系统代替SQLServer执行对登陆审查的任务。

假使要采用SQLServer管理的安全登录模式实现SQLServer服务器的登录连接，用户必需拥有合法的帐号和正确的密码。由SQLServer系统本身来进行用户的身份验证。

三、试验内容：

本次试验采用SQLServer管理的安全登录模式来连接服务器，也就是要求用户输入帐户和密码并且经系统验证通过后方可登录。

首先开启SQLServer本地客户端的查询分析器，如下图所示，选择或指定要连接的服务名称（这里为HP或202.201.224.123），输入帐户：student，密码为student。

1、创立登录帐户

用户可以通过企业管理器或系统提供的存储过程来进行登录帐户的创立。这里使用系统存储过程来创立，创立帐户的语法可参见SQLServer2000联机帮助中的相关内容。下面的例子创立了一个登陆帐户：

—21—

这个例子创立了一个名为xs，密码是123456，默认数据库为northwind的帐户。在建立用户的登录帐号信息时，用户应选中择默认的数据库，以后每次连接上服务器后，系统都会自动转到默认的数据库上。这里也可以不指定数据库，系统默认为master库。

请同学将上例中的xs帐户改为各自学号，建立各自的登录帐户，默认数据库为student数据库，密码自行设计。下面的各个试验均参循例如将xs修改为各自的帐户。

2、修改和删除帐户

建立完成的帐户信息还可以进行修改。用户可以使用系统存储过程sp_defaultdb，sp_defaultlanguage来修改默认数据库和默认语言。例如：

班级名称包括（信管011、信管013、信管022、信管024、信管026、、计科021、计科022、计科024、计科026），以上的班级名称是已经建好的数据库名，将你的帐户的默认数据库改为所属班级的数据库。

（系统默认是‘SimplifiedChinese’，建议使用默认项。）返回的结果是：默认数据库已更改。

xs的默认语言已改为English。

使用系统存储过程sp_password可修改SQLServer的帐户密码，如：

该例子修改了xs的帐户密码。

要删除SQLServer标准登录帐户可以使用系统存储过程sp_droplogin，例如：

在SQLServer中删除帐户信息时，有好多限制。主要有以下几种状况：（1）系统帐户sa不能被删除；

（2）已经映射到数据库用户上的帐户不能被删除；（3）正在使用或连接的帐户不能被删除；（4）拥有数据库的帐户不能被删除

3、添加数据库用户

以上的操作只是建立了登录SQLServer的帐户，用户登录后还不能对数据库进行访问，将登录帐户添加为数据库用户后，使用登录帐户登录的SQLServer的用户就可以实现对数据库的访问。添加数据库用户可通过存储过程sp_grantdbaccess来实现，参照下面的例子将你的帐户添加为student（该库是本课程的例如库）和班级名称（你所在班级的数据库名）两个数据库的用户。

—22—

4、删除数据库用户

可以使用系统存储过程sp_revokedbaccess来删除数据库用户，更确凿地说，是断开SQLServer的登录帐户与数据库用户之间的对应关系。

返回结果为：

用户已从当前数据库中除去。

5、角色

角色是SQLServer2000引进的用来集中管理数据库或服务器权限的概念。数据库管理员将数据库的权限赋予角色，然后将角色再赋予给数据库用户或登录帐户，从而是数据库用户或登录帐户拥有相应的权限。

SQLServer提供了固定服务器角色和数据库角色，用户可以修改固定数据库角色的权限，也可以自己创立新的数据库角色，在分派权限给新的角色。（1）固定服务器角色

使用系统存储过程sp_helpsrvrole可以浏览固定服务器角色的内容。

存储过程sp_addsrvrolemember可以将某固定服务器角色分派给某给定的登录帐户。

下面的操作将登录帐户xs添加为服务器角色dbcreatord的成员，使其具有创立和修改数据库的权利：

存储过程sp_dropsrvrolemember可以收回分派给某给定的登录帐户的指定固定服务器角色。这个例子收回了分派给登录帐户xs的固定服务器角色dbcreator.

（2）固定数据库角色

使用系统存储过程sp_helpdbfixedrole可以浏览所有的固定数据库角色的相关内容。如：

用存储过程sp_addrolemember可将某个登录帐户增加到某个固定数据库角色中，从而使该帐户拥有指定固定数据库角色所拥有的所有权限。

进行下面的操作给你的帐户授予对student库的只读权限，xs换为你的帐户名。

—23—

下面的操作给你的登录帐户授予对你所在班级数据库的所有者权限，xs换为你的帐户名。

同样使用sp_droprolemember可以从某个固定数据库角色中删除指定的登录帐户，从而收回分派的对student库的只读权限。

完成所有上面操作以后，断开当前连接，使用本试验创立的个人登录帐户登录SQLServer服务器，登录成功后，可以看到当前默认操作数据库为你所在班级的数据库名即你的班级名称。

四、思考题

1.思考身份验证模式与登录帐号的关系及如何用各种帐号进行登录；2.登录帐号、数据库用户及数据库角色之间的关系；

3.数据库用户、数据库角色与数据库对象之间的关系，并说出直接对用户授权与间接对用户授权

（系统权限与对象权限）的方法；

4.固定服务器角色、固定数据库角色、自定义数据库角色与应用程序角色的区别与验证其权限的

方法。

5.假使就将服务器配置成NT验证模式，但是不能使用WindowsNT登录名访问服务器，导致这

种状况最可能的原因是什么？6.验证模式的信息保存在哪里？

7.假使要为所有的登录名提供有限的数据访问，哪种方法最好？8.了解应用程序角色的主要作用。

—24—

试验六数据库的备份、恢复和维护

一、试验目的：

理解备份的基本概念，了解备份设备的概念。把握各种备份数据库的方法，了解如何制定备份计划，如何从备份中恢复设备。

二、试验准备：

数据库的备份与恢复是两个相对应的概念，备份是恢复的基础，恢复是备份的目的。数据库备份是指系统管理员定期或不定期地将数据库部分或全部内容复制到磁带或另一个磁盘上保存起来的过程。备份可分为静态备份和动态备份。数据库恢复是指在数据库遭到破坏时使数据库从有效的备份中恢复正常。

备份期间不允许对数据库进行任何存取、修改活动的备份方式称为静态备份。备份期间允许对数据库进行存取或修改，即各份和用户事务可以并发执行的备份方式称为动态备份。

在SQLServer中可创立维护数据库的备份设备，使用EnterpriseManager或系统存储过程来规划和执行备份操作和恢复操作。

确定备份计划主要考虑以下几个方面：

1）确定备份的频率。确定备份频率要考虑两个因素：一是系统恢复时的工作量，二是系统活动的事务量。对于完整数据库备份，可以是每个月、每一周甚至是每一天进行，而事务日志备份可以是每一周、每一天甚至是每一小时进行。

2）确定备份的内容。确定数据库中的哪些数据需要备份。

3）确定备份的介质。确定是使用磁盘、还是磁带作为备份的介质。4）确定备份的方式。确定备份采用动态备份还是静态备份。

5）估计备份需要的存储空间量。在执行备份前，应当估计备份需要使用的存储空间量。6）确定备份的人员。应当指定专人负责数据库的备份，一般只有以下角色的成员才可以备份数据库：服务器角色Sysadmin、数据库角色db_owner和db_backupoperator。

7）是否使用备份服务器。假使使用备份服务器，那么在出现故障时，系统就能迅速地得到恢复。

8）确定备份存储的期限和存放地点：备份应当存储在安全的地方并保存适当的期限。

执行数据库恢复以前，应注意以下两点：

l）在数据库恢复前，应当删除故障数据库，以便删除对故障数据库的任何引用。

2）在数据库恢复前，必需限制用户对数据库的访问，数据库的恢复是静态的，应使用企业管理器或系统存储过程sp_dbotion设置数据库为单用户。

三、试验内容：1.创立备份设备

先创立文件夹D：＼你的学号。

l）在D：＼stubak位置创立一个名为DiskBak_yourdb的本地磁盘备份文件。USEyourdbGO

EXECsp_addumpdevice‘disk’，’DiskBak_yourdb’，’D:\\stubak\\你的学号.bak’如：

—25—

2）查看系统中有哪些备份设备。Sp_helpdevice

2.进行数据库备份，管理备份设备1）创立数据库yourdb的完全备份。USEyourdb

BACKUPDATABASEyourdbTODiskBak_yourdb如：

—26—

2）查看备份设备中备份集内包含的数据库和日志文件列表。RESTOREFILELISTONLYFROMDiskBak_yourdb

3）查看特定备份设备上所有备份集的备份首部信息。RESTOREHEADERONLYFROMDiskBak_yourdb

—27—

在执行上述语句返回的结果集中：

BackupName：备份集名BackupDescription：备份描述BackupType：备份类型Position：备份集号DeviceType：备份设备名ServerName：服务器名DatabaseName：数据库名BackupSize：备份集大小其中BackupType值的含义如下：

1：完整数据库备份2：事务日志备份4：文件各份5：差异数据库备份6：差异文件备份

4）创立数据库yourdb的差异备份。先修改数据库中Student表的记录，再进行差异备份。BACKUPDATABASEyourdbTODiskBak_yourdbWITHDIFFERENTIAL

5）创立数据库yourdb的事务日志备份。

BACKUPLOGyourdbTODiskBak_yourdb

—28—

2、选择SQLSERVER的驱动

3、建立一个数据源名为ds，服务器：HP或（202.201.224.123）的数据源。

—39—

4、S