数据库系统管理方案计划与开发技术实验指导书.doc

|数据库系统管理与开发技术课程实验指导书王贱珍 主编青岛大学国际商学院管理科学与工程系二六年二月二十日目 录前 言1实验1 SQL Server 2000安装2实验2 SQL Server 2000服务器管理4实验3 数据库管理与规化6实验4 数据完整性10实验5 数据查询与更新14实验6 Transact-SQL 语言16实验7 存储过程和用户自定义函数19实验8 触发器20实验9 游标24实验10 SQL Server安全性管理25实验11 备份和还原数据库28实验12 综合实验31|前 言一、实验的地位、作用 数据库系统管理与开发技术实验作为课程实践性环节之一，是教学过程中必不可少的重要内容。通过上机实验，使学生加深理解、验证巩固课堂教学内容，让学生在消化课堂中的内容基础上提高课堂和书本中无法学到的调试和测试程序的能力，培养学生理论与实践相结合的能力。 作为数据库系统管理与开发技术课程的先修课、理论课，数据库原理与本课程有部分重叠，本课程并未将这部分重叠的内容完全忽略，而是进行了巩固提高。例如，在管理数据库表部分，用企业管理器和SQL语言管理数据库表的基本方法，同学们应当在数据库原理课程中已掌握，而数据库系统管理与开发技术实验在巩固前期所学知识的基础上，侧重于重点掌握Transact-SQL提供的为保证数据库完整性而提供的表的各种约束（constraint）机制的用法。只有牢固掌握了一些基本的Transact-SQL语言，才能掌握数据库系统开发技术中重要的存储过程、触发器、游标等技术，才能较好的进行数据库的管理工作。二、实验开设对象 本实验开设对象为数据库系统管理与开发技术课程的学习者。三、课程简介 在信息管理与信息系统专业和电子商务专业的培养计划中，数据库系统管理与开发技术是考察课程。数据库系统管理与开发技术课程以SQL Server 2000为背景介绍大型数据库系统的管理和应用系统的开发技术知识，巩固学生关于数据库理论方面的基础知识，并加强他们在数据库系统管理和开发、设计、应用方面的实践能力，为他们将来从事信息管理或相关专业的实际工作打下良好的基础。 四、考核与报告 实验后，学生将实验结果等内容写出实验报告，应符合实验教学的要求，并得到指导教师认可。 指导教师对每份实验报告进行审阅、评分。 该实验课程内容是对理论教学内容的应用与验证，实验课的成绩记入课程平时成绩，占总成绩的20。实验1 SQL Server 2000安装【实验目的】了解Windows操作系统与SQL Server之间的关系；了解SQL Server 2000各安装选项的作用；会正确安装SQL Server 2000。【实验内容和要求】在VMWare WorkStation软件中练习安装SQL Server 2000。步骤如下：1 打开VMWare WorkStation软件，启动虚拟机。2 进入虚拟机操作系统后，打开资源管理器，找到SQL Server 2000安装盘，双击安装程序，进入SQL Server 2000安装界面。选择安装SQL Server 2000企业版。3 根据安装向导的提示，安装SQL Server 2000服务器和客户端。实验2 SQL Server 2000服务器管理【实验目的】掌握可用什么工具管理SQL Server 2000服务器。【实验内容和要求】1 用SQL Server服务管理器启动、暂停或停止SQL Server服务、SQL Server Agent服务。2 用操作系统中的服务管理程序启动、暂停或停止SQL Server服务、SQL Server Agent服务。3 用命令行方式启动或停止SQL Server服务（sqlservr 命令），写出命令。4 用Net命令启动或停止服务SQL Server服务，写出命令。5 在企业管理器中建立服务器组glx，然后在其中注册几个远程服务器。看看你是否可以在自己的机器上管理和使用远程计算机上的SQL Server服务，从而理解注册服务器的含义。6 练习在企业管理器中获取数据库对象的SQL 脚本的操作：如何获取pubs数据库中创建sales表的脚本？将脚本保存为“getscript.sql”。记录操作步骤。提示：操作完毕后，在查询分析器中打开保存的脚本文件，看看脚本内容。实验3 数据库管理与规化【实验目的】掌握用企业管理器来管理和操作数据库及其相应的SQL语句。【实验内容和要求】1 用企业管理器和SQL语句2种方法建立如下内容的数据库：注意：要先在资源管理器中建立存放数据文件和事物日志文件的目录，否则，会报错。2 如何用企业管理器删除数据库myDB？记录操作步骤。SQL语句如何写？3 练习如何用企业管理器移动数据库。先创建数据库move，该数据库的数据文件move_data.mdf和日志文件move_log.ldf都存放在目录D:下。在企业管理器中把拆分move数据库。把目录D:下的数据文件move_data.mdf和日志文件move_log.ldf剪切、粘贴到E:目录下。然后在企业管理器中用E:目录下的数据文件move_data.mdf和日志文件move_log.ldf附加数据库。记录操作步骤。提示：4 用存储过程完成第3题。分别用企业管理器和SQL语句两种方式完成下面两题：5 为表products 创建一个以p_name为索引键、填充因子为10的簇索引index_product，索引中间级中每个页（节点）上保持开放的空间，索引存放在主文件组。6 products 创建一个p_id, p_name, sumvalue三个字段构成的复合索引，填充因子为50，索引中间级中每个页（节点）上保持开放的空间，索引存放在主文件组。【思考题】1 看看在企业管理器中，有哪几个系统数据库、哪几个实例数据库？各数据库的作用分别是什么？2 一台有数据库Student的SQL Server服务器，因意外，SQL Server服务系统遭到破坏，但数据库文件是完好的。现还有另外一台SQL Server服务器B，但其上没有Student数据库。如何尽快让服务器B上提供数据库Student的数据服务？实验4 数据完整性【实验目的】SQL Server 提供了一些工具来帮助用户实现数据完整性,其中最主要的是约束（constraint）、规则、缺省值、约束和触发器。本次实验目的是掌握约束、规则和缺省值的用法。【实验内容和要求】1 用企业管理器在上次实验中创建的数据库myDB中添加四个表：学生表S、课程表C、教师表T和选课表SC。S(S#, Sname, Sbirth, Ssex, Sdept, Sage)， 各字段含义分别为学号、姓名、生日、性别、院系、年龄;C(C#, Cname, T#)，各字段含义分别为课号、课名、教授该课程的教师编号;SC(S#, C#, grade) ，各字段含义分别为课号、学号、成绩;T(T#, Tname,Tposition) ，各字段含义分别为教师编号、教师名、职称。其中：1) S#, C#为字符型，长度都为5；grade为整型；Sbirth为日期时间型； 2) 看看各表的主码是什么，为各表设置主码； 3) Sage是一个计算字段，等于当前时间与生日（Sbirth）之间相差的年数；4) C表的T#是外键，参照T表；5) C表的Cname不可为空，且值是唯一的，不可重复；6) SC表的S#字段是参照S表的外键，C#字段是参照C表的外键；7) C表的Grade 小于等于100；8) T表的T#是IDENTITY属性的字段，初始值为10，增量为1；9) Tposition字段是字符型，长度为6，缺省值为“助教”；10) 未说明的字段自己根据情况定义其类型。写出创建上面4个表的SQL语句。2 创建如下图所示两表：p和c，二表的主键如图所示，且c表有对更新和删除操作级连的外键a（参照p表的主键a）。3 用下面的SQL语句在p和c两表中插入数据并显示两表的内容：insert into p values(a1,b1)insert into p values(a2,b2)insert into p values(a3,b3)insert into c values(c1,a1)insert into c values(c2,a2)insert into c values(c3,a1)insert into c values(c4,a3)insert into c values(c5,a4)select * from pselect * from c然后，用下面的SQL语句修改p表中的数据，并显示两表的内容：update p set a =a0 where b= b1select * from pselect * from c然后，用下面的SQL语句删除p表中的一行数据，并显示两表的内容：delete from p where b=b1select * from pselect * from c为什么p表中的一行数据，c表的数据也被删除了？通过数据库文件EXP06_Data.MDF和EXP06_Log.LDF附加数据库Exp06，在该数据库中做下面实验。4 创建雇佣日期规则hire_date_rule，hire_date在1980-01-01 以后、当前日期之前。5 创建工作级别规则job_level_rule，job_level 只能在1,2,3,4,5之间。6 创建字符规则my_character_rule，字符串必须以a 到f 的字母开头以0到9的数字结尾。7 绑定规则hire_date_rule 到用户自定义数据类型hire_date 上。8 绑定规则hire_date_rule 到用户自定义数据类型hire_date 上带futureonly选项，写出相应SQL语句并执行之。9 绑定规则my_character_rule 到orders 表的字段order_id。10 创建生日缺省值birthday_defa为1978-1-1。11 创建姓名缺省值name_defa为user。12 把姓名缺省值name_defa绑定到Employees表的emp_name字段。13 解除规则hire_date_rule 与用户自定义数据类型hire_date 的绑定，带futureonly选项。14 解除姓名缺省值name_defa与Employees表的emp_name字段的绑定。【思考题】1 T表的T#字段的数据类型可以为字符型吗？为什么？2 SQL Server怎样保证数据库的实体完整性、参照完整性和用户自定义完整性？3 如果用下面的语句创建p、c两表，然后如实验题3那样删除p表的一行数据，会出现什么情况？create table p (a char(2) primary key, b char(2) )create table c ( c char(2) primary key, a char(2), foreign key(a) references p(a) ON DELETE NO ACTION ON UPDATE NO ACTION )4 绑定规则hire_date_rule 到用户自定义数据类型hire_date 上带futureonly选项，写出相应SQL语句并执行之。现在，Employees表的hiredate和birthday两字段的数据类型分别是datetime和用户自定义数据类型hire_date。我们在企业管理器里把hiredate字段的数据类型改为用户自定义数据类型hire_date。然后，插入两条数据：insert employees values (e1, 张三,1990-1-1,1970-1-1 )insert employees values (e1, 李四,1970-1-1,1990-1-1 )为什么一条SQL语句执行成功，另一条执行失败？实验5 数据查询与更新【实验目的】巩固在数据库原理课程中学过的Ansi-SQL查询语句的基本用法，并进一步掌握Transact-SQL扩展的查询语句用法及复杂查询语句的用法。关于数据更新的三种类型的SQL语句Insert、Delete 、Update的基本用法，同学们在数据库原理课程中应当已经掌握。本实验主要掌握Transact-SQL对ansi-SQL的一些重要的扩展语法的使用（如select .into、insertinto），另外，还要求掌握事务的使用。【实验内容和要求】先把pangu数据库附加到SQL Server服务器。pangu中有如下表：然后用查询分析器在pangu中做下列各题：1查找公司中所有姓张且全名为两个字的员工的姓名、所在部门编号；2查询在编号为P1、P2、F3、S1 的部门中工作的员工的姓名和部门名；3查询firms表中公司介绍字段以”j%” 或“j_”开头的纪录；（提示：LIKE "模式串" ESCAPE "逃逸字符" ）4查询工作级别为2 的员工姓名,查询结果按工资排序；5查询工资最高的4名员工的姓名和工资；6查询公司编号、公司所订购的产品编号和应支付的金额。查询结果按公司编号和产品编号分组；7查询有多个员工的工资不低于1500 的部门编号；8查询各部门中的最高工资数额；9查询在公司工作的工龄相同的员工；10查询平均工资低于公司平均工资的部门编号、名称及其平均工资；11查询单笔订货量超过10 单位的产品名称；12查询order_id为3的订货纪录，将其产品ID、产品名称、订货公司的名称保存到新建的表order3中。13删除department_info中所有数据，然后往department_info表插入各个部门的员工总数（e_num）、工资总额（d_wage）；（提示： insertinto ）14做第13题，不同之处在于：把第13题的操作作为一个事务，若往department_info表插入各个部门的员工总数、工资总额的操作失败则回滚整个事务。(练习事务的使用)实验6 Transact-SQL 语言【实验目的】Transact-SQL 是ANSI SQL 的加强版语言，它提供了标准的SQL命令另外还对SQL 命令做了许多扩充提供类似Basic、Pascal、C 等第三代语言的基本功能，如变量说明、程序流程控制语言功能、函数等。本实验的目的是掌握Transact-SQL 语言中变量声明、流程控制命令使用、注释符运算符与通配符使用、其它命令使用、常用函数使用。【实验类型】设计型实验【实验内容和要求】打开查询分析器，步骤为：【开始】按钮>【程序】>【Microsoft SQL Server】>【查询分析器】。在查询分析器中写出完成下列功能的SQL语句。4 transact-SQL 语言变量声明与使用：(1) 声明一个长度为10 个字符的局部变量str1，将其值赋为SQL;(2) 声明一个长度为10 个字符的局部变量str2，将其值赋为 Server;(3) 声明一个长度为20 个字符的局部变量str，将str1和str2相连接后的值赋给str ;(4) 显示局部变量str、str1、str2的值。【实验说明】用declare声明变量；用set或select为变量赋值；两个字符串的连接运算用“”运算符；用select或print显示变量的值。5 注释符运算符与通配符使用将当前数据库切换为pubs数据库，查询employee表中所有姓张的雇员纪录，并做注释：“本语句查询employee表中所有姓张的雇员纪录”。【实验说明】用use命令切换数据库；6 控制命令使用调整员工工资，工作级别为1 的上调8% ，工作级别为2 的上调7%，工作级别为3 的上调6% ，其它上调5%。【实验说明】使用case命令，case命令能根据不同的条件返回不同的值。7 常用函数使用(1) 统计函数: 在pangu数据库中查询：1） 求各部门的平均工资。2） 计算企业的部门数目。3） 求工资最高的员工姓名。4） 求各部门的员工工资总额。【实验说明】使用统计函数avg( )、count( )、max( )、sum( )。注意，统计函数是对组进行统计，如果查询语句中没有用到分组子句（group by），则整个查询结果被看作一个分组。(2) 算术函数1） 用CEILING（） 和FLOOR（） 函数返回大于或等于2.6的最小整数值和小于或等于2.6最大整数值。2） 给出一个随机数。（提示： rand（）3） 给出值值。(3) 字符串函数1） 把字符串Abc、 ABC全部转换为小写。（提示：lower( )）2） 把字符串Abc、 abc全部转换为大写。（提示：upper ( )）3） select str(123), str(123456,5), str( -124.456,8,3),str(124.456,5), str(124.456,8,2)会得到什么结果？4） 把字符串 abc头部的空格去掉。 5） 把字符串abc 尾部的空格去掉。6） 获得字符串SQL Server,最左边3个字符的字符串。（提示：LEFT( ) ）7） 获得字符串SQL Server,最右边5个字符的字符串。（提示：Right( ) ）8） 获得字符串SQL Server第3个字符开始、长度为4的字符串（提示：SUBSTRING( ) ）9） 把字符串上海倒序。（提示：REVERSE( ) ）(4) 日期函数1） 查询在本单位工作了8 年以上的员工的姓名和所在的部门。 【实验说明】 日期函数是很重要的一类函数，因为在查询、数据库编程中经常要用到这类函数，同学们应该重点掌握。该题必须先求得各员工在本单位的工作时间。employee表中有各员工的雇佣时间（hire_date），当前时间可用函数getdate( )得到，求两日期之差的函数是DATEDIFF( )，这样就可求得员工在本单位的工作时间。【思考题】1 下面SQL语句含义是什么？注意cast( )函数和convert( )函数的使用。1） select cast(hire_date as char) as hire_date, convert(varchar(10), e_wage) as wagefrom employee where e_wage=(select min(e_wage) from employee)2） select getdate(), convert(char(10),getdate(),1)3） select convert(char(18),getdate(),100), convert(char(10),getdate(),112)2 下面SQL语句含义是什么？select dateadd(day,21,12/12/1999), dateadd(month,3, 12/12/1999)select dateadd(year,1,1/1/1999), dateadd(week,1, 1999/12/12)实验7 存储过程和用户自定义函数【实验目的】掌握存储过程的创建和执行方法。掌握用户自定义函数创建和调用方法。【实验内容和要求】附加数据库Exp10。用Transact-SQL语言完成下面功能：1 创建存储过程dept_num， 该存储过程有两个参数：dept_id（部门编号，作为输入参数）, e_num(作为输出参数，获得部门编号为dept_id的部门的雇员总数)。2 执行存储过程dept_num（dept_id为S1），并显示S1的雇员总数。3 在pubs数据库中创建用户自定义函数（内嵌表值函数）SalesByStore，该函数有一参数storeid varchar(30)，返回stor_id为storeid的书店所销售的各种书籍的书名和相应的销售数量的信息。4 调用函数SalesByStore，查看书店号为7131的书店所销售的各种书籍的书名和相应的销售数量的信息。实验8 触发器【实验目的】理解触发器的工作原理，掌握若何创建触发器。【实验内容和要求】附加数据库Exp10。表结构表数据1 学生表S中创建触发器stu_upd, 使得当S表中更改学生学号Sno时： 确保一次最多只更改一行，否则报错，回滚整个事务； 若一次只更改了一行，则将选课表SC中被更改学号的学生在选课表中的所有记录也作相同更改，例如：学号为95001的学生学号被改为94010，则选课表SC中学号95001的选课纪录的学号也都要被改为94010。（创建好触发器后，把学生表中学号95001改为94010，看看选课表有什么变化。）2 在学生表S中创建触发器stu_del, 使得当S表中删除学生数据行时： 确保一次最多只删除一行，否则报错，回滚整个事务； 若一次只删除了一行，则将成绩表中被删除的学生在选课表中的所有记录也删除（创建好触发器后，把学生表中学号95002的记录删除，看看选课表有什么变化。）3表结构如上图。表数据如下图。创建触发器，使得：员工换工作（即员工的job_id改变）,若该员工的job_lvl<他的新工作要求的min_lvl,则把他的job_lvl 置为 min_lvl；若该员工的job_lvl>他的新工作要求的max_lvl,则把他的job_lvl置为max_lvl.【思考题】 首先创建三个表salemay、salejune 和salejuly， 这三个表分别用来保存五、六、七月的销售量信息。create table salemay(sale_id char(6) not null,sale_name varchar(20),sale_qua smallint)表salejune 和表salejuly 与salemay 具有相同的数据列。 再创建分割视图saleview:create view saleviewasselect * from salemayunion allselect * from salejuneunion allselect * from salejuly这时做操作insert into saleview values(jul001,先科VCD,200)会怎样？为什么？ 然后在视图saleview 上创建INSTEAD OF 触发器saleviewtr:create trigger saleviewtr on saleviewinstead of insertasbegindeclare sale_id char(4)select sale_id=sale_idfrom insertedif substring(sale_id,1,3) =maybegin insert into salemay select sale_id, sale_name, sale_qua from insertedendif substring(sale_id,1,3) =junbegin insert into salejune select sale_id, sale_name, sale_qua from insertedendif substring(sale_id,1,3) =julbegin insert into salejuly select sale_id, sale_name, sale_qua from insertedendend这时做操作insert into saleview values(jul001,先科VCD,200)会怎样？为什么？实验9 游标【实验目的】通过本实验掌握游标基本用法。【实验内容和要求】有学生选课表SC(S#, C#, grade, ord) ，各字段含义分别为学号、课号、成绩、名次，类型分别是char(4)、char(4)、smallint、smallint。现在，前三个字段都有数据（没有为null的）。请创建存储过程give_ord，该存储过程有参数C#，该存储过程的功能是将SC表中课程号为C#的记录的ord字段填入学生在该课程的名次。成绩相同的学生的名次也相同。例如，A、B、C、D四人成绩分别是95、93、93、80，则四人的名次分别是1、2、2、4。创建后，执行该存储过程，为选了课号为DB的课的学生排名次，看看排的结果对不对。【思考题】有学生选课表SC(S#, C#, grade, ord) ，各字段含义分别为学号、课号、成绩、名次，类型分别是char(4)、char(4)、smallint、smallint。现在，前三个字段都有数据（没有为null的）。请创建存储过程give_ord，该存储过程将ord字段填入值（即，为每门课的学生排名次），对同一门课，成绩相同的学生的名次也相同。实验10 SQL Server安全性管理【实验目的】在SQL Server 的安全模型中包括以下几部分：SQL Server 登录、数据库用户、权限、角色。通过本实验掌握SQL Server 的安全基本用法。【实验内容和要求】1用SQL Server企业管理器创建SQL Server登录帐户LOGIN1，密码333。【服务器】【安全性】【登录】上右击【新建登录】【SQL Server登录属性新建登录】对话框在【常规】标签中输入：在【常规】标签中单击【确定】按钮。2为test数据库创建新用户LOGIN1（该用户以登录帐户LOGIN1登录数据库），对数据表table1有select、insert、update权限。操作：【服务器】【数据库】【test】数据库【用户】上右击【新建数据库用户】【数据库用户属性新建用户】对话框在【常规】标签中如下输入，并点确定：【test】数据库【用户】在LOGIN1上右击【所有任务】【管理权限】对话框在【数据库用户属性test】对话框的【权限】标签中赋权限，并确定：3以LOGIN1登录SQL Server，打开表table1，做插入、修改数据操作，SQL Server有何反应？做delete操作，SQL Server有何反应？4为test数据库创建加数据库标准角色role1，给该角色赋予某些权限；将该角色赋给test数据库的LOGIN1用户。5附加题：在windows系统中新建windows用户win1，再将该用户添加为SQL Server登录用户，该用户具有test数据库的角色role1的权限。实验11 备份和还原数据库【实验目的】掌握数据库备份及还原的方法。【实验内容和要求】用企业管理器和Transact SQL分别完成下面任务，并记录操作步骤。1 完全备份数据库pangu，备份文件名为pangu_bak。提示：2 把pangu数据库删除；3 然后用pangu_bak还原数据库pangu。提示：实验12 综合实验【实验目的】通过综合实验全面完整地掌握大型数据库管理与开发技术。【实验内容和要求】用企业管理器创建学生管理数据库；在数据库中创建数据表：学生表S、课程表C、教师表T和选课表SC：S(S#, Sname, Sbirth, Ssex, Sclass, Sage)， 各字段含义分别为学号、姓名、生日、性别、班级、年龄;C(C#, Cname, T#)，各字段含义分别为课号、课名、教授该课程的教师编号，一门课一个老师教;SC(S#, C#, grade，ord) ，各字段含义分别为学号、课号、成绩、学生在该课程的排名;T(T#, Tname,Tposition) ，各字段含义分别为教师编号、教师名、职称。1) S#、C#、T#为字符型，长度都为5；grade类型为tinyint；ord类型为smallint；Sbirth为日期时间型； 2) 看看各表的主码是什么，为各表设置主码； 3) Sage是一个计算字段，等于当前时间与生日（Sbirth）之间相差的年数；4) C表的T#是外键，参照T表；5) C表的Cname不可为空，且值是唯一的，不可重复；6) SC表的S#字段是参照S表的外键，C#字段是参照C表的外键；7) C表的Grade 小于等于100；8) T表的T#是IDENTITY属性的字段，初始值为10，增量为1；9) Tposition字段是字符型，长度为6，缺省值为“助教”；10) 未说明的字段自己根据情况定义其类型。1. 写出创建各表的SQL语句。2. 写出招入新生的SQL语句；3. 写出增加新课程的SQL语句；4. 写出增加新老师的SQL语句；5. 写出学生增加选课的SQL语句；6. 写出修改学生信息的SQL语句；7. 若学号被修改，则选课表中该生所选课程的学号也要做同样的修改。（触发器。）8. 写出删除学生纪录的SQL语句，要求一次只能删一条记录，且被删学生在选课表中的纪录也要被删除。（触发器）9. 写出修改学生成绩的存储过程upd_grade，它有四个参数：T#(修改成绩的教师编号)、S#、C#、Grade。该存储过程把SC表中学号为S#、课号为S#的记录成绩改为Grade，但只有教C#这门课的教师才可以修改学C#这门课的学生的成绩，否则，报错，修改失败。10. 增加一个数据库用户Teacher，该用户有修改SC表Grade字段的权限。11. 学生选课表SC(S#, C#, grade, ord) ，前三个字段都有数据（没有为null的）。请创建存储过程give_ord，该存储过程将ord字段填入值（即，为每门课的学生排名次），对同一门课，成绩相同的学生的名次也相同。（游标）12. 在SC表上创建触发器，使得：当grade字段被改动时，执行存储过程give_ord。13. 设计一个数据库备份的方案，用企业管理器实现该方案。要求：若发生数据库瘫痪，丢失的数据不超过两小时。用文字描述该方案。