数据库系统原理及应用

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

2、DBMS的工作原理和系统构架。2 熟悉对DBMS的操作3 搭建今后实验的平台2 实验平台2.1 操作系统： Windows 2000或者Windows XP2.2 数据库管理系统：推荐使用Microsoft公司的SQL Server，目前实验可用的版本可以是SQL Server 2000。也可以选择国产数据库管理系统，或国外的Oracle, DB 2等。3 实验内容及要求3.1 安装和启动 1 学着安装数据库管理系统。在安装过程中记录安装的选择，并且对所作的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。 2 学会启动和停止数据库服务，思考可以用哪些方式来完成启动和停

3、止。 3 初步了解MS SQL Server的安全性，这里主要是用户的登录和服务器预定义角色。可以尝试建立一个新的用户，赋予其数据库管理员的角色，今后的实验可以用该用户来创建数据库应用。3.2 数据库系统的构架 1 了解数据库系统的逻辑组件： 它们主要是数据库对象，包括基本表、视图、触发器、存储过程、约束等。今后将学习如何操作这些数据库对象。 2 了解数据库的物理组件： 思考数据库物理存储的存储单位是什么，数据主要以什么方式存放，如何确定数据的存放位置。3.3 MS SQL Server的管理和使用 了解MS SQL Server如何通过它提供的工具对数据和数据库服务器进行管理和使用的。3.3

4、.1 启动、暂停和停止SQL Server 学会运用企业管理器进行操作。3.3.2 了解系统其他管理工具 初步了解SQL Server的主要工具和它们的功能，为今后的实验做准备。例如，数据迁移工具：导入和导出数据使用的；企业服务器和作业调度管理器：可以用该工具对数据库服务器进行管理，管理和监视服务器性能和活动；查询分析器：给出数据库查询语句的查询计划和执行时间，帮助用户分析查询过程，进行查询优化。等等。4 实验报告数据库系统原理及应用实验报告题目：序号姓名日期实验环境：实验内容与完成情况：出现的问题：解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：实验二 交互式SQL（1）（DDL子

5、语言实践）实验目的熟悉数据库的交互式SQL工具。如：SQL Server2000查询分析器。掌握SQL语言的DDL子语言，在SQL Server2000环境下采用Transact-SQL实现表的定义、删除与修改，掌握索引的建立与删除方法。实验内容和要求1. 在SQL Server2000中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。2. 根据以下实验内容的要求认真填写实验报告，记录所有的实验用例。(1) 设有如下关系表S： S(NO, NAME, SEX, AGE, CLASS)，主关键字是NO。其中NO为学号，char(2)，学号不能为空，值是唯一的；N

6、AME为姓名，char(10)，姓名的取值也唯一；SEX为性别，char(2)；AGE为年龄，int； CLASS为班号，char(5)。写出实现下列功能的SQL语句。创建表S；插入一个记录（25，“李明”，“男”，21，“95031”）；再插入一个记录（10，“王丽”，“女”，20，“95101”）；插入“95031”班学号为30，姓名为“郑和”的学生记录；对表S，按学号升序建唯一索引（索引名为sno）；对表S，按年龄降序建索引（索引名为sage）；向S表添加“入学时间（comedate）”列，其数据类型为日期型（datetime）；删除S表的sage索引；将年龄的数据类型改为smallin

7、t； 删除学生姓名必须取唯一值的约束；（注意：SQL Server2000与标准SQL语言有区别）删除S表； (2) 按照数据库系统概论(第四版)P82中的关系模式及数据，创建STUDENT、COURSE和SC表，并插入相应数据。每一张表都必须有主键约束，分别试着使用列级完整性约束和表级完整性。实验提示：要学会使用SQL Server2000的联机丛书。查看具体表的信息可使用如下SQL命令：exec sp_help 表名;根据实验内容的要求认真填写实验报告，记录所有的实验用例。实验报告数据库系统原理及应用实验报告题目：序号姓名日期实验环境：实验内容与完成情况：出现的问题：解决方案（列出遇到的问

8、题和解决办法，列出没有解决的问题）：实验三 交互式SQL（2）（DDL：表的约束定义和DML：查询）实验目的掌握SQL语言的DDL子语言，在SQL Server2000环境下采用Transact-SQL实现表的定义中主键、外键、唯一值、检查、非空和默认值等列级或表级完整性约束条件的使用。掌握采用Transact-SQL实现查询操作，包括单表查询和连接查询（如：等值连接、自然连接、自身连接、外连接和复合条件连接）的方法。实验内容和要求按照数据库系统概论(第四版)P82中的关系模式及数据，再次创建STUDENT、COURSE和SC表。每一张表都必须有主键。需要使用外键的表必须使用外键。根据需要适当

9、采用唯一值、检查、非空和默认值约束。要求这四种约束在STUDENT、COURSE和SC表这三张表中至少使用一次。根据主键、外键、唯一值、检查、非空和默认值六种约束的特性，设计适当的方案对STUDENT、COURSE和SC表中的这六种约束进行检验。方案自定。一种约束进行检验一次即可。按照数据库系统概论(第四版)P82及P75中的关系模式及数据，实现以下查询：找出所有供应商的姓名和所在城市。找出所有零件的名称、颜色、重量。查询供应工程J1零件的供应商号码SNO。查询使用红色零件的工程名称。查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。查询先行课的学分为4的课程信息。查询选

10、修3号课程且成绩在85分以上的所有学生。实验报告数据库系统原理及应用实验报告题目：序号姓名日期实验环境：实验内容与完成情况：出现的问题：解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：实验四 交互式SQL（3）（嵌套查询、数据更新和视图）实验目的：掌握SQL语言的核心语句SELECT语句的使用，具体为在SQL Server2000环境下采用Transact-SQL实现嵌套查询。掌握采用Transact-SQL实现数据更新。掌握采用Transact-SQL实现视图的定义、删除、查询与更新。实验内容和要求按照数据库系统概论（第4版）P82及P75中的关系模式及数据，完成以下实验：嵌套查询

11、查询其他系中比计算机科学系所有学生年龄都小的学生姓名与年龄，请用二种方法实现。查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，请用二种方法实现。查询供应工程J1红色零件的供应商号SNO，请用三种方法实现。查询至少用了供应商S1所供应的全部零件的工程号JNO，要求用带EXISTS谓词的子查询实现。查询使用了全部零件的工程号JNO，要求用带EXISTS谓词的子查询实现。数据更新将学号为10的学生姓名改为“王华英”；将所有“95101”班号改为“95091”；视图请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询

12、：找出三建工程项目使用的各种零件代码及其数量；找出供应商S1的供应情况；实验报告数据库系统原理及应用实验报告题目：序号姓名日期实验环境：实验内容与完成情况：出现的问题：解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：实验五 数据控制（安全性部分）实验目的： 1)熟悉通过SQL对数据进行安全性控制。2)完成书本上习题的上机练习。实验内容和要求使用SQL对数据进行安全性控制，包括：授权和权力回收。操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。根据以下要求认真填写实验报告，记录所有的实验用例。2.1 授权与回收1 建

13、立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。建立用户U1、U2、U3、U4、U5、U6、U7，选择其数据库角色全部允许为public。在DBA与七个Connect用户之间进行授权在授权之后验证用户是否拥有了相应的权限。2 回收权限。将1授予的权限部分收回，检查回收后，该用户是否真正丧失了对数据的相应权限。回收权限在回收权限之后验证用户是否真正丧失了该权限。2.1 数据库角色3 角色的创建与授权。3-1 首先创建一个角色 R13-2 然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT3-3 将这个角色授予U1,U3,U7。使

14、他们具有角色R1所包含的全部权限。3-4 对角色R1的权限进行修改，增加对Student表的DELETE权限，并回收对Student表的INSERT权限。3-5 删除角色R1。实验报告数据库系统原理及应用实验报告题目：序号姓名日期实验环境：实验内容与完成情况：出现的问题：解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：实验（六）变量与控制流语言实验目的：掌握Transact-SQL局部变量的声明、赋值和使用方法以及全局变量的运用方法，掌握Transact-SQL的局部变量的作用域概念。掌握Transact-SQL的控制流语言的使用方法。掌握与控制流语言语句一起使用的其它 Transa

15、ct-SQL 语句： HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_ca-co_5t9v.htm CASE、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_operator_7tx5.htm /*.*/（注释）、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL

16、%20Server80ToolsBookstsqlref.chm:/ts_operator_76pl.htm -（注释）、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_pa-pz_5q44.htm PRINT。实验内容和要求按照数据库概论P59中的student表，在批处理中声明两个局部变量ssex和sdept并对它们赋值，要求它们在SELECT 语句的 WHERE 子句中被使用，查询的要求是性别为女或所在系为IS系的那些学生。用 ERROR 检测INSE

17、RT 和 DELETE 语句的成功操作与否。在student表中执行 UPDATE 语句并用 ROWCOUNT 来检测是否有发生更改的行。实践使用以下全局变量：CONNECTIONS、VERSION 在 SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。本题要求仿照数据库概论P59中的sc表，根据学生的成绩范围将成绩（grade 列）显示为文本注释。（要求：60分，文本注释为“不及格”，6070，“及格”，7080，“中”，8090，“良”，90100，“优”）。自选实例，实践并掌握Transact-SQL以下控制流语言的使用方法： HYPERLINK mk:MSI

18、TStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_ba-bz_9yas.htm BEGIN.END、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_wa-wz_6oyt.htm WHILE、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.

19、chm:/ts_ba-bz_11gr.htm BREAK、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_ca-co_0dd1.htm CONTINUE、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_ia-iz_7usl.htm IF.ELSE、 HYPERLINK mk:MSITStore:D:Program%20Fi

20、lesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_wa-wz_8ipg.htm WAITFOR自选实例，实践并掌握以下Transact-SQL语句： HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_operator_7tx5.htm /*.*/（注释）、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBoo

21、kstsqlref.chm:/ts_operator_76pl.htm -（注释）、 HYPERLINK mk:MSITStore:D:Program%20FilesMicrosoft%20SQL%20Server80ToolsBookstsqlref.chm:/ts_pa-pz_5q44.htm PRINT实验报告数据库系统原理及应用实验报告题目：序号姓名日期实验环境：实验内容与完成情况：出现的问题：解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：实验七 数据控制（触发器）实验目的：掌握SQLServer中触发器的使用方法。熟悉通过SQL的触发器对数据进行功能更强的完整性控制。实验内容和要求按照数据库系统概论（第4版）P75中的关系模式实现以下操作：1) 创建一个作用在P表上的触发器P_Ins，确保用户在插入或更新数据时所提供的WEIGHT值是非负和非零的，否则给出错误提示并回滚此操作。请测试该触发器。测试方法自定。2) 利用触发器为教师表Teacher定义完整性规则“教授的