SQL2008_综合实验

1、华北科技学院计算机系综合性实验报告华北科技学院计算机系综合性实验实 验 报 告 课程名称 SQL SERVER数据库设计 实验学期 2011 至 2012 学年 第 1 学期学生所在系部 计算机系 年级 09级 专业班级 信管 学生姓名 白邵杰 学号 200907034207 任课教师 实验成绩 计算机系制 SQL SERVER数据库设计课程综合性实验报告开课实验室： 软件一室 2011 年 12 月 7 日实验题目图书借阅系统数据库设计一、实验目的通过该实验把数据库的理论知识（数据库和数据表的设计理论、数据完整性的实现、存储过程、触发器、数据库安全等）应用到具体的综合实例中，达到数据库知识整

2、合的目的。二、设备与环境硬件：多媒体计算机软件：WindowsXP以上的操作系统、Visual Basic或其它可视化语言及SQL Server 2008版本三、实验内容及要求1.数据库设计要求数据库设计要合理，对数据库设计作必要的说明并抓图。数据库名必须与自己真实姓名有关，所有同学不能同名。图不要太大，看清即可。2数据表设计要求数据表设计要合理，要符合数据库设计的理论范式，对数据表设计作必要的说明并抓图。数据表名必须与自己真实姓名有关，所有同学不能同名。3视图设计要求根据系统需求作必要的视图设计，如在一次查询中涉及到多个表，应该创建视图。不可以只取一个表的几个字段就算创建视图。4索引设计要求

3、根据系统需求作必要的索引设计，本系统需要的聚集索引、非聚集索引、唯一索引、全文索引等。5数据完整性设计根据系统需求作必要的数据完整性设计，本系统需要的实体完整性体现、域完整性体现、参照完整性体现等。6存储过程和触发器设计根据系统需求作必要的存储过程和触发器设计，本系统需要的存储过程和触发器设计。必要的存储过程和触发器设计都要写全说明，图可以是一个表的完整存储过程或触发器。7备份与恢复设计根据系统需求作必要的备份与恢复设计，如需要对那些内容备份，备份策略、由谁来做备份、什么时间做备份等。8数据库安全设计根据系统需求作必要的数据库安全设计，如本系统分几级用户、分别是什么角色成员具有什么操作权限等。

4、四、实验结果及分析1.数据库设计设计思想（根据系统需求作数据库设计的简单说明） 数据库是数据的集合。数据库技术是研究数据库的结构、存储、设计、管理和应用的一门软件学科。而数据库管理系统（Database Management System，DBMS）是位于用户与操作系统之间的一层数据管理软件。 数据库是存储数据和数据库对象的操作系统文件，是数据库系统的主要组件，是数据库管理系统的核心。 数据库对象包括表、函数、视图、存储过程、规则等等。数据库的存储结构分为逻辑存储结构和物理存储结构两种。数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成。实际上，SQL Server的数据库是由诸如表、视

5、图、索引等各种不同的数据库对象所组成。数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。一个数据库只能有一个主数据文件，后缀名为mdf；一个数据库可以没有辅助数据文件，但也可以同时拥有多个辅助数据文件。后缀名为ndf。事务日志文件是存放数据库的更新情况等事务日志信息 ，当数据库损坏时，管理员使用事务日志还原数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf。数据库文件组是文件的逻辑集合。一个文件只能存在于一个文

6、件组中，而一个文件组也只能被一个数据库使用；且日志文件是独立的，它不能作为任何文件组的成员。具体实现（可以是数据库设计抓图）2数据表设计设计思想（根据系统需求作数据表设计的简单说明，如包含几个表，每个表的简单功能等。）表是数据库中最重要、最基本、最核心的对象，是实际存储数据的地方。并简单阐述表的特点：代表实体由行和列组成行和列的顺序是任意的在同一个数据库中，表名是唯一的。另外，需要简单讲解：行也称为记录，列也称为字段或域。对于每一个表，用户最多可以定义1024个列，且在同一个表中，列名必须是唯一的。系统表是不能被删除的。具体实现（可以是数据表设计抓表结构图）要求体现数据库、数据表设计的合理性3

7、视图设计设计思想（根据系统需求作视图设计，本系统需要做那几个视图，如普通用户想查看那个学生借阅了那本书，什么时间借的，什么时间还的等）视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。是查看数据库表中数据的一种方法。特点强调试图只是一种逻辑对象，是一种虚拟表。一般地，视图的内容包括：* 基表的列的子集或行的子集视图可以是基表的其中一部分；* 两个或多个基表的联合视图可以是对多个基表进行联合运算检索的SELECT语句；* 两个或多个基表的连接视图可以是通过对若干个基表的连接生成的；* 基表的统计汇总视图不仅仅是基表的投影，还可以是对基表的各种复杂运算的结果；* 另外一

8、个视图的子集视图可以基于另外一个视图；* 视图和基表的混合视图和基表可以起到同样的作用。优点当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。视图有很多优点，主要表现在： 视点集中 简化操作 定制数据 合并分割数据 安全性创建试图1在创建或使用视图时，应该注意到以下情况： 只能在当前数据库中创建视图，在视图中最多只能引用1024列； 如果视图引用的表被删除，则当使用该视图时将返回一条错误信息，如果创建具有相同的表的结构新表来替代已删除的表视图则可以使用，否则必须重新创建视图； 如果视图中某一列是函数、数学表达式、

9、常量或来自多个表的列名相同，则必须为列定义名字； 不能在视图上创建索引；不能在规则、缺省、触发器的定义中引用视图； 当通过视图查询数据时，SQL SERVER 不仅要检查视图引用的表是否存在，是否有效，而且还要验证对数据的修改是否违反了数据的完整性约束。如果失败将返回错误信息，若正确，则把对视图的查询转换成对引用表的查询。2演示使用企业管理器管理（创建、查看、修改、重命名、删除）视图3使用SQL语句管理视图具体实现（可以是视图设计抓图）4索引的建立设计思想（根据系统需求作索引设计的简单说明，如本系统需要做那几个聚集索引、非聚集索引、唯一索引、全文索引等）索引就是加快检索表中数据的方法。通过书籍

10、目录的类比方式讲解索引的作用：数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整本书籍就能迅速地找到所需要的信息。在数据库中，索引允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。在书籍中，索引就是内容和相应页号的清单。在数据库中，索引就是表中数据和相应存储位置的列表。索引可以大大减少数据库管理系统查找数据的时间。索引优缺点告知学生索引的优缺点，并讲解在哪些列上最好创建索引，哪些列上不必创建索引。为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。* 保证每行数据的惟一性；* 加快数据的检索速度；* 加速表和表之间的连接；* 减少查询中分组和排序的时间； * 使

11、用优化隐藏器，提高系统性能。也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。索引是建立在数据库表中的某些列的上面。那么在

12、创建索引的时候我们应该如何考虑呢？其实很简单，我们只需要在主键上、在经常需要搜索的列上、在外键上、在经常需要排序的列上、在经常用在WHERE子句中的列上创建索引即可。而不必在那些在查询中很少使用或者参考的列上、只有很少数据值的列上、定义为text, image和bit数据类型的列上、修改性能远远大于检索性能的列上创建索引。索引类型简述索引的分类，及它们的区别和创建它们应考虑的因素。根据索引的顺序与数据库表的物理顺序是否相同，可以把索引分为：顺序相同的聚簇索引和顺序不同的非聚簇索引。告知在默认情况下，创建的索引是非聚簇索引，且强调由于聚簇索引改变表的物理顺序,应先创建聚簇索引，后生成非聚簇索引。

13、提醒：聚簇索引创建在很少更新的表上。换句话说，它创建在查找表（一般指主表）上。也就是说，表列上必须创建的聚簇索引必须较少执行行的插入、删除和更新操作。如果需要创建索引来提高查询速度，但同时列值又经常更动（由于插入、更新和删除），那么即使列值是唯一的（如主键值），也必须创建非聚簇索引。在频繁处理的表上创建聚集簇索引将降低事务处理速度，因为每次插入、更新和删除操作都将对表重新组织。索引属性索引都有两个重要属性：复合索引和唯一索引。复合索引就是一个索引创建在两个列或者多个列上。唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。创建索引创建索引有多种方法，这些方法包括直接创建索引的方法和间

14、接创建索引的方法。直接创建索引就是使用 CREATE INDEX语句或者使用创建索引向导；间接创建索引就是在表中定义主键约束或者唯一性键约束时，同时也创建了索引。具体实现（可以是索引设计抓图）5数据完整性设计设计思想（根据系统需求作数据完整性设计的简单说明，如本系统需要的实体完整性、域完整性、参照完整性等）数据完整性数据完整性是指存储在数据库中的数据的一致性和正确性。在这里只需要简单提一下：数据完整性的设计是数据库设计好坏的一项重要指标。约束简单讲解约束的定义及类型。约束是通过限制列中的数据、行中的数据和表之间数据来保证数据完整性的非常有效的方法。约束的类型分为：缺省约束、检查约束、主键约束、

15、惟一性约束、外键约束。创建约束使用CREATE TABLE语句表示在创建表的时候定义约束；使用ALTER TABLE语句表示在已有的表中添加约束。如果在一个表中只有一个约束，则为列级约束，否则为表级约束。查看约束告之学生使用系统存储过程sp_helpconstraint可以得到数据库中某一个表的全部约束信息。例：在查询分析器中执行sp_helpconstraint Word，就可以获得上边创建表Word的全部约束信息。主键约束：也就是在表中定义一个主键值。在创建表的即可定义，但是其属性都为默认值。该约束强制实体完整性，且默认的主键约束是惟一性的聚簇索引。使用主键约束时，必须注意：1、一个表最多

16、只能定义一个主键约束。2、列所输入的值必须是唯一的。如果主键约束是由两个或两个以上的列组成的，那么这些列的组合必须是唯一的。3、列不允许空值。4、约束在指定的列上创建了一个惟一性索引。5、在定义主键约束时添加级联操作选项。具体实现（可以是数据完整性设计设计抓图）6存储过程和触发器设计设计思想（根据系统需求作必要的存储过程和触发器设计的简单说明）存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。分类系统存储过程就是系统创建的存储过程，可以作为命令执行各种操作。系统存储

17、过程存储在master数据库中，其前缀是sp_。系统存储过程可以在任意一个数据库中执行。本地存储过程是指创建在每个用户自己数据库中的存储过程。这种存储过程是用户创建的普通数据库，其名称前面没有前缀sp_。临时存储过程首先是本地存储过程，它只在应用程序运行时存在，程序结束后，被自动删除。如果本地存储过程的名称前面有一个符号“#”，那么把该存储过程称为局部临时存储过程，这种存储过程只能在一个用户会话中使用；如果本地存储过程的名称前面有一个符号“#”，那么把该存储过程称为全局临时存储过程，这种存储过程所有用户会话中使用。远程存储过程是指从远程服务器上调用存储过程，或从连接到另外一个服务器上的客户机上

18、调用存储过程。扩展存储过程：在SQL Server中执行的动态连接库DLL，其前缀是xp_。优点 允许标准组件式编程 实现较快的执行速度 减少网络流量 可作为一种安全机制充分利用执行存储过程强调只有具有存储过程的执行权限，才可以执行存储过程。执行存储过程有两种方法:一种是直接执行存储过程，另外一种是在INSERT语句中执行存储过程。直接执行存储过程就是调用EXECUTE语句来执行存储过程。语法形式如下：EXECUTE procedure_name具体实现（可以是一个表完整的存储过程或触发器）存储过程：触发器设计：触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包含复杂的 SQL语句。

19、它们主要用于强制复杂的业务规则或要求。触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。在使用触发器的时候，需要注意：1、触发器在操作发生之后执行，约束是在操作发生之前进行检查；2、约束优先于触发器，如果操作与约束有冲突，则不执行触发器；3、任意一个表可以有多个触发器；4、表的所有者必须有执行全部在触发器定义中的语句的许可；5、触发器不应该返回结果集。优点触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。同其他约束相比，它主要有以下优点：1）触发器自动执行在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。2）触发器能够对数据

20、库中的相关表实现级联更改触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，在产品销售表的产品编号上自动插入编号值。 3）触发器可以实现比CHECK约束更为复杂的数据完整性约束在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其它表中的列来完成检查工作，而触发器可以引用其它表中的列。例如，在STUDENT数据库中，向学生表中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该系。这只能通过触发器实现，而不能通过CHECK约束完

21、成。4）触发器可以评估数据修改前后的表状态，并根据其差异采取对策。5）一个表中可以存在多个同类触发器（INSERT、UPDATE或DELETE），对于同一个修改语句可以有多个不同的对策以响应。分类SQL Server 2000按触发被激活的时机可以分为两种类型：AFTER 触发器和 INSTEAD OF触发器。触发操作包括：INSERT、UPDATE、DELETE创建触发器当创建一个触发器时，必须指定触发器的名字，在哪一个表上定义触发器，激活触发器的修改语句，如INSERT、 DELETE、 UPDATE。 当然两个或三个不同的修改语句也可以都触发同一个触发器，如INSERT 和UPDATE

22、语句都能激活同一个触发器。当创建触发器时，有关触发器的信息记录在sysobjects系统表和syscomments系统表中。如果创建触发器的时候，使用了与已创建触发器相同的名称，那么原来的触发器将被新的触发器覆盖。另外需要注意的是，不能为系统表创建触发器。创建触发器的时候必须是表的所有者或数据库的所有者或sysadmin角色成员触发器工作原理当向表中插入数据时，INSERT触发器触发执行。当INSERT触发器触发时，新的记录增加到触发器表中和inserted表中。该inserted表是一个逻辑表，保存了所插入记录的拷贝，允许用户参考INSERT语句中数据。触发器可以检查inserted表，来确

23、定该触发器的操作是否应该执行和如何执行。在inserted表中的那些记录，总是触发器表中一行或多行记录的冗余。当触发一个DELETE触发器时，被删除的记录放在一个特殊的deleted表中。deleted表是一个逻辑表，用来保存已经从表中删除的记录。该deleted表允许参考原来的DELETE语句删除的已经记录在日志中的数据。 修改一条记录就等于插入一条新记录和删除一条旧记录。同样，UPDATE语句也可以看成是由删除一条记录的DELETE语句和增加一条记录的INSERT语句组成。当在某一个有UPDATE触发器表的上面修改一条记录时，表中原来的记录移动到deleted表中，修改过的记录插入到了in

24、serted表中。触发器可以检查deleted表和inserted表以及被修改的表，以便确定是否修改了多个行和应该如何执行触发器的操作。触发器允许嵌套，最多可以嵌套32层。但是用户可以使用系统存储过程sp_configure禁止使用触发器嵌套。且触发器不能调用自己。要启用触发器嵌套，执行语句：sp_configure 'nested trigger', 1要禁用触发器嵌套，执行语句：sp_configure 'nested trigger', 07备份与恢复设计设计思想（根据系统需求作必要的备份与恢复设计的简单说明，本系统需要对那些内容备份，备份策略、由谁来做备

25、份、什么时间做备份等）备份就是对SQL Server数据库或事务日志进行备份，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其还原。提醒执行备份操作必须拥有对数据库备份的权限许可，SQL Server只允许系统管理员、数据库所有者和数据库备份执行者备份数据库。SQL Server 2000对所要备份内容的选项设置，提供了四种不同的方式：（1）数据库完全备份（2）数据库差异备份（3）事务日志备份（4）文件和文件组备份默认情况为第一种方法。管理备份设备备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质，可以是硬盘、磁带或管道。备份设备在硬

26、盘中是以文件的方式存储的。在这里我们首先演示通过企业管理器来管理备份设备。接下来我们通过SQL语句来管理备份设备。具体实现可选创建备份设备：8数据库安全设计设计思想（根据系统需求作必要的数据库安全设计的简单说明，如本系统需要分几级用户、分别是什么角色成员具有什么操作权限等）数据库的安全性主体主要有三个级别：服务器级别、数据库级别、架构级别在SQL Server 2008中，一个主体是任何单独的、可以要求访问受保护的资源并可被授予权限来访问它的个体、组或过程。在SQL Server的之前版本中，你可以定义一个Windows中的主体或者你将它基于一个SQL Server登陆而不和Windows主体关联。下面的列表显示了SQL Server 2008主体的层次，不包括固定服务器和数据库角色，以及你可以怎样匹配登陆和数据库用户来保护对象。这个主体的影响范围取决于它的定义的范围，因此一个 Windows级别的主体比一个SQL Server级别的主体范围更广。每一个数据库用户会自动地属于固定的公共(public)角色。Windows级别主体· Windows域登陆· Windows本地登陆· Windows组SQL Server级别主体· SQL Server登陆· 与一个Windows 登陆相匹配的SQL Server登陆&