数据库系统实验指导书新版.doc

实验一 创建数据库和表一、实验目的及要求：1了解SQL SERVER数据库的逻辑结构和物理结构2了解表的结构特点3了解SQL SERVER的基本数据结构二、实验任务：1学会在企业管理器中创建数据库和表2学会在T-SQL语句创建数据库和表三、操作要点：1启动企业管理器，并创建数据库和表2启动SQL查询分析器3掌握创建数据库和表分别是CREATE DATABASE和CREATE TABLE四、注意事项：1在Microsoft SQL Server 2000的查询分析器(Query Analyzer)中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。2通常，SQL Server 2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQL Server 2000会给出错误信息提示。五、实验学时：2学时六、实验重点及难点：1列级完整性约束条件2空值的概念3CREATE DATABASE和CREATE TABLE 的用法 七、实验步骤：(一) 企业管理器创建数据库和表 1启动企业管理器； 2创建数据库Company，并创建一个主数据文件Company_Data1，一个二级数据文件Company_Data2，一个日志文件Company_Log，以及一个自定义文件组Company_Group，该组中有数据文件Company_Data3，设置各文件属性。 (二) T-SQL语句创建数据库和表1启动SQL查询分析器；2创建数据库University，并创建一个主数据文件University_Data和一个日志文件University_Log；表5.5 关系ReportsSnoCnoGradeS01S01S02S02S02S03S03S04C01C03C01C02C03C01C02C039284909482729075表5.4 关系CoursesCnoCnamePre_CnoCreditsC01C02C03C04C05C06C07英语数据结构数据库DB_设计C+网络原理操作系统C05C02C03C07C054223333表5.3 关系StudentsSnoSnameSsexSageSdeptS01S02S03S04S05S06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学3. 建立表5.3所示的学生表Students，各属性为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。要求Sno和Sname不能为空值，且取值唯一，Sno为键码。 4.建立表5.4所示的课程表Courses，各属性为Cno-课程号, Cname-课程名, Pre_Cno-先修课程号, Credits-学分，要求Cno和Cname不能为空值，Cno取值唯一，且为键码。5.建立表5.5所示的成绩表Reports。各属性名为Sno-学号，Cno-课程号和Grade-考试成绩，要求Sno和Cno不能为空，且取值唯一，Sno+ Cno为键码。实验二 SQL基本表的插入、删除与修改一、实验目的及要求：熟练掌握基本表的插入、删除与修改，为后继学习作准备。二、实验任务：1了解并掌握SQL查询分析器及企业管理器的使用;2掌握基本表的插入、删除与修改。三、操作要点：1.启动SQL查询分析器2.掌握进行插入、删除与修改分别是INSERT、DROP和UPDATE 四、注意事项：1.注意每次对表的修改，命令执行完后要查看表的变动刷新表。2.特别注意在执行插入、删除与修改等更新操作时，要保证数据的完整性。3.SQL Server 2000 增加了删除属性的命令。五、实验学时：2学时六、实验重点及难点1T-SQL语句对基本表的定义进行删除与修改，并插入基本表数据。2T-SQL语句对数据库操作的灵活控制功能。七、实验步骤：1启动SQL查询分析器；2选择SQL SERVER后，按确认；3选择University数据库；4验证如下例题：表5.4 关系CoursesCnoCnamePre_CnoCreditsC01C02C03C04C05C06C07英语数据结构数据库DB_设计C+网络原理操作系统C05C02C03C07C054223333表5.3 关系StudentsSnoSnameSsexSageSdeptS01S02S03S04S05S06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学 4.1 修改基本表向基本表Students中增加“入学时间”属性列，其属性名为Sentrancedate，数据类型为DATETIME型。将Sage(年龄)的数据类型改为SMALLINT型。删除Sname(姓名)必须取唯一值的约束。说明： 为了保证后面例子能够顺利运行，请大家一定将属性列Sentrancedate从Students表中删除。 为了调试SQL语句方便，这里没有在表Reports中增加参照完整性约束，甚至没有定义主键。4.2 删除Students表。说明：此表删除后，请立即将其建立起来，以便后面的例子使用。4.3 向表中添加元组将一个学生元组(S01，王建平，男，21，计算机)添加到基本表Students中。请同学们用这个命令，按表5.3提供的数据，将其余5个学生的元组也添加到基本表Students中。向Courses表插入元组(C01,英语,4)。请同学们用这个命令，按表5.4提供的数据，将其余6门课程的信息插入Courses表中。将学习成绩的元组(S01, C01)添加到基本表Reports中。实验三 数据库的查询一、实验目的及要求1.熟练掌握SELECT查询语句的使用2.掌握无条件、有条件查询及查询结果排序与分组二、实验任务1掌握查询语句的一般格式。2掌握无条件、有条件查询及查询结果排序与分组三、操作要点1.进行查询时先进入查询分析器2.明白了解无条件、有条件查询及查询结果排序与分组四、注意事项1. SELECT语句的基本语法格式2. SELECT语句的执行方法3. SELECT语句和ORDER BY子句的作用和用法4. SELECT语句和GROUP BY子句的作用和用法五、实验学时：4学时六、实验重点及难点1.SELECT语句的基本语法2.掌握数据汇总的方法七、实验步骤1、查询全体学生的详细记录。2、查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。3、查询选修了课程的学生学号。4、查询数学系全体学生的学号(Sno)和姓名 (Sname)。5、查询所有年龄在1922岁(包括19岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。6、查询年龄不在19-22岁之间的学生姓名(Sname)及年龄(Sage)。7、查询自动化系和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。8、查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。9、查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。10、查询以DB_开头，且倒数第2个汉字字符为“设”的课程的详细情况。11、假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。12、查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。13、求学生的总人数14、求选修了课程的学生人数15、求课程和选修了该课程的学生人数16、求选修超过2门课的学生学号17、查询计算机科学系的学生及年龄不大于19岁的学生18、查询计算机科学系的学生与年龄不大于19岁的学生的交集19、查询计算机科学系的学生与年龄不大于19岁的学生的差集20、查询选修课程C01的学生集合与选修课程C02的学生集合的交集21、查询选修课程C01的学生集合与选修课程C02的学生集合的差集实验四 连接、嵌套和集合查询一、实验目的及要求： 1熟练掌握连接、嵌套和集合查询的使用。2学习各种连接、嵌套和集合查询方法。二、实验任务1掌握连接、嵌套和集合查询语句的一般格式。2掌握连接、嵌套和集合查询的各种使用方法。三、操作要点1创建表时候,在查询分析器中输入程序,编译时查看附录的出错信息再进行修改。2函数除了系统函数以外也可以自定义函数。3为了避免程序丢失,把程序保存在到规定的目录中去。四、注意事项1注意T-SQL各种运算符、控制语句的功能及使用方法。2注意各种系统函数的调用方法。五、实验学时：4学时六、实验重点及难点1明白两个表之间的联系是通过公共属性实现的。2连接、嵌套和集合查询的各种使用方法。3多重嵌套的使用。七、实验步骤1、求学生号以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。 2、求选修了课程”c01”且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。 3、求选修了课程的学生的学生姓名、课程号和成绩。 4、求年龄大于 李伟 的所有学生的姓名、系和年龄。 5、求选修了课程C02或C03的学生的学号、课程号、课程名和成绩。 6、求与 李伟 年龄相同的学生的姓名和系。 7、求选修了课程名为 数据结构 的学生的学号和姓名。 8、求比数学系中某一学生年龄大的学生的姓名和系。 9、求比数学系中全体学生年龄大的学生的姓名和系。 10、求选修了课程C04的学生的姓名和系。 11、求与 黄河 同系且同龄的学生的姓名和系。 12、求与 黄河 同系，且年龄大于 黄河 的学生的信息。 13、求数学系中年龄相同的学生的姓名和年龄。14、检索至少选修所有授课程中一门课程的男学生姓名。 15、检索选修某课程的学生人数多于2人的课程名称。 16、查询被一个以上的学生选修的课程号。 17、求未选修课程C04的学生的姓名。 18、求选修了全部课程的学生的姓名。 19、求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。20、查询所有未选课程的学生姓名和所在系。实验五 数据库系统设计一、实验目的及要求 1、通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。2、通过实验使学生加深对数据安全性的理解，并掌握SQL Server中有关用户，角色及操作权限的管理方法。3、通过实验使学生了解SQL Server的数据备份和恢复机制，掌握SQL Server中数据库备份和恢复的方法。4、根据数据库系统设计的基本步骤，完成某一具体数据库系统的需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施和数据库运行维护等功能。二、实验任务1、使用SQL设计触发器，通过SQL Server企业管理器定义它们。2、在SQL Server企业管理器中，设置SQL Server的安全认证模式。通过SQL Server企业管理器，实现对SQL Server的用户和角色管理。分别通过SQL Server企业管理器和SQL的数据控制功能，设置和管理数据操作权限。3、用企业管理器创建一个备份设备。利用向导为学生选课数据库生成一个完全备份。在企业管理器中恢复学生选课数据库4、完成某一具体系统的需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施和数据库运行维护等功能，并提交数据库。三、实验学时：8学时四、实验内容及步骤（一）创建和使用触发器（1）实验的内容使用SQL设计触发器，通过SQL Server企业管理器定义它们。具体完成下面例题：利用触发器来保证学生选课库中选课表的参照完整性，以维护其外码与参照表中的主码一致。CREATE TRIGGER SC_inserted ON 选课FOR INSERTAS IF（SELECT COUNT（*） FROM 学生，inserted，课程WHERE 学生.学号 = inserted.学号 AND inserted.课程号 = 课程.课程号）=0ROLLBACK TRANSACTION（2）实验步骤1、 在企业管理器中创建触发器1） 在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。2） 在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。 图 触发器属性对话框如果要新建触发器，在文本T的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。3） 如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本T的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。4） 如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本T的文本框中的内容，看是否选得正确，最后单击“删除”按钮。2、 验证触发器1） 在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库）。此时，可在选课表中输入一条记录（学号：96001，课程号：C8，成绩：88）。显然，这条记录是不符合参照完整性的。通过企业管理器输入时，会出现错误提示，违反了数据库的约束（如果约束C5和C6同时存在的话）。2） 如果约束C5和C6存在，则删除之3） 打开SQL查询分析器，输入语句“INSERT INTO 选课 VALUES（96001，C8，88）”，查看执行结果。4） 删除触发器（本例为SC_inserted），重复步骤3。（二）数据安全性1 在SQL Server企业管理器中为所属的SQL 服务器设置为SQL Server和Windows NT混合安全认证模式。其步骤如下：1） 在企业管理器窗口中展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，则出现SQL Server属性对话框，如图1所示。 图1 SQL Server属性对话框2） 在SQL Server属性对话框中选择“安全性”选项卡。3） 身份验证选择“SQL Server和Windows”单选项。2 在SQL Server企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。1） 在企业管理器窗口中展开服务器组，展开服务器，用鼠标单击“安全性”文件夹右侧的+，用鼠标右击“登录”，在弹出的菜单中选择“新建登录”项，则出现新建登录对话框，如图2所示。图2 新建登录对话框2） 在新建登录对话框中有常规，服务器角色和数据库访问三个选项卡。在常规选项卡中，输入用户名（本例为王平），选择SQL Server安全验证，输入用户口令。在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用缺省值即可。在数据库访问选项卡中，需要指定此登录可以访问的数据库（本例选中学生选课数据库）和“学生选课”的数据库角色（在本例中采用缺省值即可）。单击“确定”按钮，既完成了创建登录用户的工作。3 关闭SQL Server企业管理器，打开SQL查询分析器。选择SQL Server安全验证，用户名为王平，输入用户口令，连接到SQL Server。在“查询”窗口中可以输入SQL语句。如“SELECT * FROM 学生”。运行后，得到消息“拒绝了对对象学生（数据库学生选课，所有者dbo）的SELECT权限。”。可见用户王平没有对学生表的SELECT权限。4 要想将学生选课数据库的操作权限赋予数据库用户王平，有两种方法。方法一：通过企业管理器1） 在企业管理器窗口中展开服务器组，展开服务器，用鼠标单击“数据库”文件夹右侧的+，用鼠标单击学生选课数据库文件夹右侧的+，用鼠标右击“用户”。在屏幕右侧的“用户”窗口中选择“王平”项，用鼠标右击，在弹出的菜单中选择“属性”项，则出现数据库用户属性对话框，如图3所示。 图3 数据库用户属性对话框2） 单击图3中的权限按钮，则出现数据库用户权限对话框，如图4所示。对话框的下面是有关数据库用户和角色所对应的权限表。这些权限均以复选框的形式表示。复选框有三种状态：“”为授权；“”为废除权；空为撤权。在表中可以对用户或角色的各种对象操作权（SELECT，INSERT，UPDATE，DELETE，EXEC和DRI）进行授予或撤消。 图4 数据库用户权限对话框3） 在图4中找到学生表，授予SELECT权限，即让学生表与SELECT列交叉的复选框为“”即可。方法二：通过SQL的数据控制功能对用户王平授权，必须是数据库对象拥有者以上用户授予。我们可以以系统管理员或sa用户登录进入查询分析器。在查询分析器中输入授权语句“GRANT SELECT ON 学生 TO 王平；”，然后执行即可。（三）数据库备份和恢复实验步骤1、在企业管理器中创建一个备份设备1）在企业管理器中扩展服务器，在管理文件夹中找到备份文件夹。用鼠标右键单击该文件夹，在弹出的菜单中选择“新建备份设备”项，则出现“备份设备属性”对话框，如图所示。在名称文本框中输入设备名（本例为学生选课设备1）。单击“确定”按钮，备份设备就创建成功了。 图 “备份设备属性”对话框2） 在备份文件夹的窗口中出现了新创建的设备。选中该设备，用鼠标右键单击，在弹出的菜单中选择“属性”项，则出现“备份设备属性”对话框。2、 利用向导为学生选课数据库生成一个完全备份。1） 在企业管理器中扩展服务器，在数据库文件夹中选中要备份的数据库文件夹（学生选课数据库）。选择菜单工具|向导，则出现向导选择对话框。在向导选择对话框中，单击管理左边的“+”号，使之展开。选择“备份向导”项，单击“确定”按键。