《数据库管理系统》实验指导书(本部)

数据库管理系统实验指导书 刘 颖 长沙理工大学经管学院 1 数据库管理系统实验指导书 课程代码：0812000814 英文名称：Database Management System 适用对象：会计学、财务管理专业 学时学分：共 32，实验 10 学时，2.0 分。 一、实验的目的 数据库系统产生于 20 世纪 60 年代末。30 多年来，数据库技术得到迅速发展，已形成 较为完整的理论体系和一大批实用系统，现已成为计算机软件领域的一个重要分支。随着 网络技术的迅猛发展，以及数据库技术与网络技术的紧密结合，数据库技术已经广泛应用 于各种领域，小到工资管理、人事管理，大到企业信息的管理等，数据库技术已成为计算 机系统应用最广的技术之一。同时，数据库技术及其应用也成为国内外高等学校计算机专 业和许多非计算机专业的必修或选修内容。本课程实验教学的目的和任务是使学生通过实 践环节深入理解和掌握课堂教学内容，使学生得到数据库应用的基本训练，提高其解决实 际问题的能力。 二、实验开设对象 本实验开设对象为数据库管理系统课程的学习者，其中主要是会计学、财务管理 专业的学生。 三、基本原理及课程简介 SQL Server 2008 是当今深受欢迎的关系数据库管理系统，是一个杰出的 数据库平台，它建立在成熟而强大的关系数据模型的基础之上，可以很好地支 持客户机/服务器模式，可用于大型联机事务处理、数据仓库以及电子商务等， 能够满足各种类型的企事业单位构建网络数据库的要求，是目前各类学校学习 大型数据库管理系统的首选对象。本课程主要介绍数据库管理系统的基本概念 和基本原理以及 SQL Server 2008 关系数据库管理系统的主要功能及其使用。 具体内容包括 SQL Server 2008 的安装、服务器与客户端配置、Transact-SQL 基础、数据库管理、表和视图管理、存储过程和触发器管理、游标管理、维护 数据库、SQL Server 安全管理和 SQL Server 代理服务。本实验课程内容共设 3 个大的子实验项目。 四、指导教师要求 本实验课程教学指导原则上由管理信息系统课程讲授教师负责，在人数较多时配 1-2 名教师担任实验指导教师，实验室人员配合指导。指导教师应在实验前阐述实验目的、 基本方法、基本技术、实验要求等，指导学生在规定的时间内完成相关课程实验任务。 五、实验设备配置 每人配置 1 台电脑，安装 SQL Server 2008 软件，并能访问 Internet。 2 六、考核与报告 1、实验完成后，学生将实验结果从系统中打印出来，并撰写实验报告，符合实验教学 的要求，并得到指导教师的认可。 2、指导教师对每份实验报告进行审阅、评分。 3、该实验程依附于理论课，实验课的成绩记入课程平时成绩，占总成绩的 40%。 实 验 一 数据库的创建与管理 一、实验题目 数据库的管理 二、实验课时 课内 2 课时 三、实验目的 通过本课程实验，让学生熟练掌握数据库的创建、修改和删除操作。理解数据分离和 附加的概念，并掌握数据的分离、附加和移动操作。 四、实验内容和步骤 1、在 D 盘根目录下简历两个文件夹 sqldata 和 sqllog。打开 SQL Server Management Studio,注意选择所连接的 SQL Server 为你自己的机器，连接使用“Windows 身份验证”。用 CREATE DATABASE 语句按以下要求在本地 SQL Server 下建立数据库。 数据库名称-mydb1 主数据文件逻辑名称-f1 主数据文件物理名称-D：sqldataf1.mdf 初始大小-2MB； 最大尺寸-无限大； 增长速度-5% 次数据文件逻辑名称-f2 次数据文件物理名称-D：sqldataf2.mdf 初始大小-3MB； 最大尺寸-200MB； 增长速度-2MB 事务日志文件逻辑名称-lg1 事务日志文件物理名称-D：sqlloglg1.ldf 初始大小-1MB； 最大尺寸-10MB； 增长速度-1MB 调试成功后，在 SQL Server Management Studio 中找到所建立的数据库，打开其属 性窗口，观察其所建立的数据是否和以上要求一致。确认正确后记录下所使用的 CREATE DATABASE 语句。 2、用 ALTER DATABASE 语句完成以下操作。 （1）向第 2 题创建的 mydb1 数据库的 primary 文件组中添加文件： 次数据文件逻辑名称-f3 次数据文件物理名称-D：sqldataf3.ndf 初始大小-2MB； 最大尺寸-5MB； 增长速度-1MB （2）修改以上生成的数据库文件 f3，使其初始大小为 3MB，最大尺寸为 10MB。 3 （3）将数据库名称 mydb1 修改为 mydb2。 五、实验要求 1、掌握利用图形界面工具创建和管理数据库。 2掌握利用 CREATE DATABASE 命令创建和管理数据库 3要求提交对各个实验步骤的实验结果。 六、实验参考资料 学生在设计方案与实验时，可以参考有关教材、教学案例与 CAI 课件。 实 验 二 表和视图的创建与管理 一、实验题目 表和视图的创建与管理 二、实验课时 课内 4 课时。 三、实验目的 通过本课程实验，让学生掌握表的创建、修改、更新、查询过程；理解表约束的内容； 掌握利用 SELECT 查询命令进行表的查询操作；掌握视图的创建过程。 四、实验内容与步骤 （1）表的定义及约束。 1、建立名称为“职工”的数据库，数据库属性自定。按以下要求完成各步操作，保存或记 录完成各题功能的 Transact-SQL 语句。 2、使用 CREATE TABLE 语句在“职工”数据库中按以下要求创建各表。 （1）“职工基本信息”表：表结构如表 6-8 所示。 表 6-8 “职工基本信息”表 字段名 职工编号 姓名 性别 出生日期 部门编号 类型及说明 Char（5）主 键 Char（10）不允许为空 Char(2) Datetime Char（3 ） （2）“工资”表：表结构如表 6-9 所示。 表 6-9 “工资”表 字段名 职工编号 基本工资 奖金 实发工资 类型及说明 Char（5）主键 Money Money Money (3) “部门信息”表：表结构如表 6-10 所示。 表 6-10 “部门信息”表 字段名 部门编号 部门名称 部门简介 类型及说明 Char（3）主键 Char（20）不允许为空 Varchar（50） 3、使用 ALTER TABLE 语句向“职工基本信息”表中添加一项，列名称为“职称”，类型为 char，长度为 10。 4 4、使用 ALTER TABLE 语句删除第 3 题添加的职称列。 5、为“部门信息”表的部门名称字段添加一个唯一性约束，以限制部门名称的唯一性。 6、限制“职工基本信息”标的“性别”子短板只接受“男”和“女”两个值。 7、限制“工资”表的“基本工资”字段的值为不小于 0 的数。 8、限制“工资”表的“基本工资”和“奖金”字段的默认值为 0。 9、设“职工基本信息”表的“性别”字段的默认值为“男” 10、创建外部键约束，定义职工基本信息表的“默认编号”为外部键，引用“部门信息” 表的“部门编号”；定义“工资”表的“职工编号”为外部键，引用“职工基本信息”表 的“职工编号”。 11、删除第 9 题创建的外部键约束。 （二）表的更新 继续使用第（一）创建的“职工”数据库，完成以下各题功能，保存或记录相应的 FSQL 语句。 1、用 INSERT 语句向“职工基本信息”表中插入如表 6-11 所示的 4 行数据。 表 6-11 “职工基本信息”表中的数据 职工编号 姓名 性别 出生日期 部门编号 10001 王佳 女 1979 年 2 月 1 日 001 20001 张欣 男 1965 年 5 月 10 日 002 20003 李勇 男 1976 年 8 月 1 日 002 10002 刘军 男 1973 年 7 月 1 日 001 2、用 INSERT 语句向“工资”表中插入如 6-12 所示的两行数据（即部门编号为“001”的 职工工资信息）。 表 6-12 部门编号为“001”的职工工资信息 职工编号 基本工资 奖金 10001 2000 2200 10002 2500 2000 3、在“职工”数据表中在创建一张新表，表名为“临时工资信息”，其结构和内容如表 6-13 所示 表 6-13 “临时工资信息”表 职工编号 姓名 基本工资 奖金 20001 张欣 2000 2500 20003 李勇 2200 2000 用带子查询的 INSERT 语句将该新表的有关数据添加到“工资”表中。 4、有 CREATE TABLE 语句在“职工”数据库中创建一个新表“部门平均年龄”，包含“部 门编号”和“平均年龄”两个字段。 5、使用 INSERT 语句将每一个部门编号及对应的职工平均年龄添加到以上“部门平均年 龄”表中。（提示：在子查询语句中可以使用“YEAR（GETDATE（）-YEAR（出生日期）” 求每一个职工的年龄，在对其使用 AVG 函数求平均。 6、用 UPDATE 语句给“工资”表中所有所属部门编号为“001”的职工的奖金增加 10%。 7、用 UPDATE 语句求所有职工的实发工资（即计算“工资”表的实发工资一列的值，等 于基本工资+奖金）。 5 8、删除职工编号为“10001”的职工工资信息。 9、删除“工资”表中所属部门编号为“001”的所有职工的工资信息。 （三）表的查询 以下各题基于数据库 HrSystem，编写 SECLECT 语句，完成以下各题的要求，保存或记录 下实现各题功能的查询语句。 1、查询员工表 Employees 的全部信息。 2、查询员工表 Employees 的姓名、性别及工资。 3、要将所有员工工资上涨 20%明显是所有员工的姓名、性别和上涨后的工资。将上涨 后的工资一列的标题显示为“New Wage”。 4、查询员工表 Employees 中有哪些职务（使用 title 字段，这里需要去掉重复的职务） 。 5、查询部门编号为 1 的员工姓名和职务。 6、查询图书类型职务为“部门经理”且性别为女的员工的姓名。 7、使用 BETWEEN 表示范围，找出工资在 3000-4000 员的所有员工信息。 8、使用 BETWEEN 表示范围，找出工资不在 3000-4000 员的所有员工信息。 9、找出部门为人事部（编号为 1）和办公室（编号为 2）的所有员工信息（条件使用 IN）。 10、从 Employees 表中找出所有身份证以“110”开头的员工的所有信息。 11、查询 Employees 表中所有身份证第 1 个字母在 0-3 之间的员工的所有信息。 12、统计员工的最高工资、最低工资、总工资和平均工资。 13、统计一共有几种职务。 14、统计职务为“部门经理”的员工总人数。 15、统计每一个部门的员工人数，统计结果中包含部门编号和员工人数。（提示：使 用 groupby 实现分类汇总）。 16、统计每一部门（按 Depid 分类）的平均工资（wage），要求统计结果列标题显 示为“部门编号”及“平均工资”。（提示：使用 groupby 实现分类汇总）。 17、统计每一部门（按 Depid 分类）的平均工资（wage），要求显示每一个部门的 明细内容后再显示该部门的平均工资，明细中要求显示部门编号、部门名称、姓名和工资。 （提示：使用 COMPUTEBY）。 18、查询每一个员工的明细信息，查询结果包括姓名、性别、职务和对应的部门名称。 使用 where 条件指定表间的连接。（提示：使用 EMployees 和表 Departments） 19、实现第 18 题功能，改成使用 INNER JOIN 实现表间连接。 20、查询每一个部门所包含的员工信息。查询结果包括姓名、性别、职务和对应的部 门名称。对于没有员工的部门，在查询结果中要同样列出，其对应的员工信息为空 （NULL）。（提示：使用外连接，使用 Employees 和表 Departments） 21、使用子查询实现：查询此部门为“办公室”的员工。 （四）视图 1、创建视图 view1，使该视图中包含 HrSystem 数据库中所用一个的明细结果（视图 中的列名全部使用中文）。 2、显示第 1 题创建的视图 view1 的所有数据。 3、利用第 1 题创建的视图 view1，列出视图中所有姓李的员工的所有信息。 4、使用 ALTER VIEW 修改第 1 题创建的视图 View1，使其只包含所有作者的姓名、职 务和部门三列（殊途中的列名全部使用中文）。 5、删除以上创建的视图 view1. 6 五、实验要求 1、掌握表的创建与管理过程 2、理解表约束的概念，以及掌握视图的创建与管理过程。 3要求提交对各个实验步骤的实验结果。 六、实验参考资料 学生在设计方案与实验时，可以参考有关教材、教学案例与 CAI 课件。 实 验 三 规则与索引、触发器与游标 一、实验题目 规则与索引、触发器与游标 二、实验课时 课内 4 课时 三、实验目的 通过本课程实验，学生理解规则、索引、触发器、游标的概念，并熟练掌握其创建和 使用过程。 四、实验内容和步骤 （一）规则 以下操作均针对数据库 HrSystem。 使用 CREATE RULE 语句创建规则 SexRule，指定变量sex 的取值只能为“男”或“女” 。 完成后，在对象资源管理器中展开数据库 HrSystem“可编程性”“规则” ，确认 可以看到规则 SexRule. 使用存储过程 sp_bindrule 可以将规则 SexRule 绑定到表 Employees 的列 SEX 上。 执行下面的 INSERT 语句，向表 Employees 中插入一条记录。 USE HrSystem GO INSERT INTO Employess(Emp_name , Sex , Title , Wage ,IdCard , Dep_id) Values (“小李” ,“无” ， “职员” ，10000， “110123xxx”，1） GO 确认是否可以成功执行 INSERT 语句，为什么？ 使用存储过程 sp_unbindrule 取消表 Employess 的列 Sex 上绑定的规则。成功后再执 行第 4 步中的 INSERT 语句，确认是否可以成功执行 INSERT 语句，为什么？ 使用 DROP RULE 删除规则 SexRule。完成后，在对象资源管理器中展开数据库 HrSystem“可编程性”“规则” ，确认是否可以看到规则 SexRule。 （二）索引 执行以下语句，利用数据库 HrSystem 的表 Employees 产生一个新表 emp，该新表包 含了表 Employees 中的所有记录。 USE HrSystem SELECT * INTO emp FROM Employees 在新表 emp 上建立一个唯一聚集索引，索引名称为 name_ind,索引字段为 Emp_name。 使用 SQL Server Management Studio 查看索引 name_ind 的属性信息。 使用 DROP INDEX 语句删除第一题创建的索引 name_ind。 （三）存储过程 7 完成以下各题功能，保存或记录下实现各题功能的 Trasact-SQL 语句。 在数据库 HrSystem 中创建存储过程 avg_wage，用于求所有员工的平均工资，并通过 输出参数返回该平均工资。要求在创建存储过程之前要首先判断该存储过程是否已存在， 如果存在，则将其删除。 执行第一题创建的存储过程 avg_wage 打印员工的平均工资。 在数据库 HrSystem 中创建 max_wage,根据指定的部门名称（输入参数）返回该部门的 最高工资（输出参数） ，要求在创建存储过程之前要是判断该存储过程是否已存在，如果存 在，则将其删除。 执行第 3 题创建的存储过程 max_wage,指定部门为“财务部” ，打印该类部门的最高工 资。 删除存储过程 avg_wage 和 max_wage。 （四）触发题 创建一个“学生信息”数据库，包含“学生基本信息”表、 “专业”表和“系”表， 各表包含字段如下。 “学生基本信息”表：学号；姓名；性别；班级；出生日期；专业编号。 “专业”表：专业编号；专业名称；系编号。 “系”表：系编号；系名称；系简介。 各字段类型按其实际含义自行定义，输入一些数据，要求数据要有代表性。 以下操作要求全部在 SQL Server Management Studio 中完成，保存或记录实现各 题功能的 Transcat-SQL 语句， （包括测试相应触发器是否生效的相关语句及测试成果） 。 1、 在“专业”表上创建一个 INSERT 触发器“TRG1” 。当发生插入专业表操作时， 将显示插入记录。 2、在“专业”表上创建一个 DELETE 触发器“TRG2” ，当发生删除操作时，将给出 警告、列出删除的记录并撤销删除。 3、在“专业”表上创建一个 UPDTAE 触发器“TRG3” ，当发生更新“专业名称”字 段的操作时，给出警告并撤销更新。 4、在“学生基本信息”表上创建一个更新触发器“TRG4” ，当发生更新“学号”或 “