数据库原理实验指导书.doc

数据库原理实验指导书二一一年二月21目录实验一 认识DBMS、安装DBMS、完成DB创建2实验二 数据定义与SQL基本操作7实验三 复杂查询13实验四 视图与索引15实验五 安全性与完整性控制17实验一 认识DBMS、安装DBMS、完成DB创建目的和意义学习安装DBMS、创建和修改用户数据库的方法。实验内容安装SQLServer2000，分别使用企业管理器和Transact-SQL语句，按下列要求创建和修改用户数据库。1. 创建一个数据库，要求如下:(1) 数据库名testDB。(2) 数据库中包含一个数据文件，逻辑文件名为 testDB_data，磁盘文件名为testDB_data.mdf，文件初始容量为5MB，最大容量为15MB，文件容量递增值为1MB。(3) 事务日志文件，逻辑文件名为TestDB_log, 磁盘文件名为TestDB_log.ldf,文件 初始容量为5MB, 最大容量为10MB，文件容量递增值为1MB。2. 对该数据库做如下修改:(1) 添加一个数据文件，逻辑文件名为TestDB2_data，实际文件为TestDB2_data.ndf,文件初始容量为1MB，最大容量为6MB，文件容量递增值为1MB。(2) 将日志文件的最大容量增加为15MB，递增值改为2MB。实现步骤方法一：使用企业管理器创建和修改数据库TestDB1. 设置常规选项卡(1) 在企业管理器的控制面板目录中选中结点数据库，单击鼠标右键，在弹出菜单中选择新建数据库命令。(2) 设置新建数据库的常规选项卡，在名称文本框中键入数据库名称TestDB。如图1-l所示。图A12. 设置数据文件选项卡在“文件名”字段中键入数据文件名“TestDB_data”。设置该文件初始大小为5MB。图1-1选中“文件属性”对话框中的复选框“文件自动增长”，并选中“按兆字节”设置文件容量递增值为1。在“最大文件大小”对话框中选择“将文件增长限制为（MB）”为15。如图1-2所示。图1-23. 设置事务日志选项卡(1) 在文件名字段中键入事务日志文件的逻辑文件名TestDB_log。设置该文 件初始大小为5MBo(2) 选中文件属性对话框中复选框文件自动增长，并选中按兆字节设置文件容量递增值为1。(3) 在最大文件大小对话框中选择将文件增长限制为(MB)为10。如图1-3所示。(4) 单击确定按钮，完成数据库的创建。 图1-3设置事务日志选项卡4. 修改事务日志文件属性(1) 从树状目录窗口中找到刚刚创建的数据库TestDB，单击鼠标右键，从弹出菜单中选择命令属性，打开数据库TestDB的属性窗口。(2) 选择数据文件选项卡。在该选项卡中添加数据文件TestDB2_data,方法是:在文件名字段中键入数据文件逻辑文件名TestDB2_data，设置该文件初始大小为 1MB,然后选中文件属性对话框中复选框文件自动增长，并选中按兆字节设置文件容量递增值为1，最后在最大文件大小对话框中选择将文件增长限制为(MB)6。如图1-4所示。图1-4(3) 选择事务日志选项卡，在该选项卡中将事务日志文件的最大容量改为15，递增量改为2。如图1-5所示。图1-5方法二：使用Transact-SQL语句在查询分析器中创建和修改数据库TestDB1. 创建数据库TestDBCREATE DATABASE TestDBON(NAME = TestDB_data,FILENAME=”D:Program filesMicrosoft sql servermssqldataTestDB_data.mdf”,SIZE=5,MAXSIZE=15,FILEGROWTH=1)LOG ON(NAME = TestDB_log,FILENAME=”D:Program filesMicrosoft sql servermssqldataTestDB_log.ldf”,SIZE=5,MAXSIZE=10,FILEGROWTH=1)2. 修改数据库TestDBALTER DATABASE TestDBADD FILE(NAME = TestDB2_data,FILENAME=“D:Program filesMicrosoft sql servermssqldataTestDB2_data.mdf”,SIZE=1,MAXSIZE=6,FILEGROWTH=1)GOALTER DATABASE TestDBMODIFY FILE(NAME=TestDB_log,MAXSIZE=15)GOALTER DATABASE TestDBMODIFY FILE(NAME=TestDB_log,FILEGROWTH=2)GO习题 建立学生选课的数据库stuDB, 逻辑文件名为 stuDB_data，磁盘文件名为stuDB_data.mdf，文件初始容量为10MB，最大容量为15MB，文件容量递增值为2MB。事务日志文件，逻辑文件名为stuDB_log, 磁盘文件名为stuDB_log.ldf,文件初始容量为5MB, 最大容量为10MB，文件容量递增值为1MB。体会实验二 数据定义与SQL基本操作目的和意义熟悉有关数据表的创建和修改等工作，并了解主键、外键以及约束的创建和应用，熟练掌握使用企业管理器和CREATE TABLE、ALTER TABLE等Transact-SQL语句对数据表的操作方法。熟练掌握查询语句实验内容分别使用企业管理器和Transact-SQL语句按下列要求创建两个数据表: 表1项目数据表，表2员工数据表，并按照步骤完成对表的相关修改和约束设置。项目表（Project）字段名数据类型字段长度注释项目编号int主键名称varchar长度为50负责人int客户int开始日期datetime结束日期datetime员工数据表(Employee)字段名数据类型字段长度注释编号int主键姓名varchar长度为50性别varchar长度为50所属部门varchar长度为50工资money长度为8实现步骤方法一：使用企业管理器创建数据表并添加约束1. 打开企业管理器，在树状目录窗口中找到数据库节点TestDB，并选中下一级节点表。2. 单击鼠标右键，从弹出菜单中选择命令新建表，打开表设计窗口，在窗口中按照上机任务中所列的项目数据表的字段及要求键入列名、数据类型、长度等属性，并将项目编号设置为主键。如图2-l所示。图2-l创建项目数据表3. 单击保存按钮，在弹出的对话框中输入表名称项目数据表，关闭表设计窗口，完成表项目数据表的刨建。4. 重复1-3步，创建另一个数据表员工数据表，表设计窗口如图2-2所示。图2-2创建员工数据表5. 添加外键约束:在项目数据表的负责人字段上添加外键约束，参照字段为员工数据表中的字段编号，约束名为FK_pm。实现方法为:首先在企业管理器中选中项目数据表并单击鼠标右键，在弹出菜单中选择命令设计表打开设计表窗口。然后在该窗口中单击按钮表和索引属性按钮，打开表的属性对话框。最后选择关系选项卡，在改选项卡中做如下的操作。单击新建按钮;从主键表中选择员工数据表，并选择字段编号;从外键表中选择项目数据表，并选择字段负责人;在关系名中输入FK_pm。单击关闭按钮，完成外键约束的设置。6. 添加检查约束:将员工数据表中的字段工资的值限定在1000-10000,约束名CK_Salary。操作方法为:首先在企业管理器中选中员工数据表并单击鼠标右键，在弹出菜单中选择命令设计表打开设计表窗口。然后在该窗口中单击按钮表和索引属性按钮，打开表的属性对话框。最后选择CHECK约束选项卡，在改选项卡中做如下的操作:单击标签中新建按钮;在约束表达式文本框中输入一个条件表达式;(工资=1000 AND 工资=1000 AND 工资2000；3书写查询语句查询来自CCH公司的项目名称(以CCH开始)和负责人姓名。参考答案: SELECT a.名称，b.姓名 FROM 项目数据表 AS a , 员工数据表 AS bWHERE a.负责人=b.编号AND a.名称 LIKE “CCH%”；4将所有REALIDEA公司的项目的结束日期更改为2002年1月8日 。UPDATE 项目数据表SET 结束日期= January 8, 2002WHERE 名称 LIKE REALIDEA%;5录入部的张晓峰决定辞职，请将员工数据库中有关他的记录删除，并将他负责的项目移交给杨亭亭。书写语句对数据表做相应的更改。DELETE员工数据表WHERE 姓名=“张晓峰”UPDATE 项目数据表 SET负责人=8WHERE负责人=3； 习题在student , course , sc中 添加元组1)从student , course , sc表中选择出当年年龄在20岁以上的学生的学号和姓名。2)统计平均年龄价格。3)“张三”同学要退学，请删除他的所有信息。 体会实验三 复杂查询目的和意义掌握使用T-SQL语句编写复杂查询的方法，熟练使用关联查询、子查询、数据分组、排序等复杂的查询语句。实验内容与要求1书写语句查询每个部门的平均工资，结果按照平均工资的多少排序参考答案：SELECT 所属部门，AVG（工资）FROM 员工数据表GROUP BY 所属部门ORDER BY AVG（工资）；2使用外向联接输出所有的员工姓名和负责人的项目名称参考答案：SELECT员工数据表.姓名，项目数据表.名称FROM 员工数据表 LEFT JOIN项目数据表ON员工数据表.编号=项目数据表.负责人；3使用子查询输出所有负责 CCH公司项目(以CCH开始)的员工姓名，以及没有负责REALIDEA公司(以REALIDEA开始)项目的员工姓名 。参考答案：SELECT 姓名FROM 员工数据表WHERE 编号 IN (SELECTDISTINCT 负责人FROM 项目数据表WHERE 名称 LIKECCH%)SELECT 姓名FROM 员工数据表WHERE 编号 NOT IN (SELECT DISTINCT 负责人FROM 项目数据表WHERE 名称 LIKE REALIDEA%);4. 使用子查询显示哪些员工的工资高于本部门的平均工资。参考答案 SELECT 姓名，所属部门 FROM 员工数据表 a WHERE a.工资 = （SELECT AVG（工资）FROM 员工数据表 bWHERE a.所属部门 b.所属部门）；5、分析实验4中的查询执行效率，能否将其改为效率更好的查询语句？实验4中的查询为相关子查询，对外查询中的每条数据，子查询都要执行一次，因此效率很低，应将相关性去掉，变为结果集关联的方式。参考答案：SELECT a.姓名，a.所属部门，a.工资，b.平均工资FROM 员工数据表 a，（SELECT 所属部门， AVG（工资） 平均工资FROM 员工数据表GROUP BY 所属部门） bWHERE a.所属部门 b.所属部门 AND a.工资 = b.平均工资；实验四 视图与索引目的和意义掌握使用T -SQL语句创建视图的方法，包括视图的建立、删除、修改；了解如何应用视图有选择地查看所需数据，并熟悉通过视图更改数据表中数据的方法。掌握创建索引的方法。实验内容与要求一、视图在数据库TestDB中，基于表项目数据表和员工数据表创建视图，要求为:(1) 视图名为员工项目。(2) 包含字段编号、姓名、名称和开始日期。(3) 字段别名分别是员工编号、”员工姓名、项目名称、项目开始日期。实现步骤 (1)打开查询分析器。在查询窗口书写CREATE VIEW语句创建视图，并指定字段别名：USE TestDBGOCREATE VIEW员工项目(员工编号，员工姓名，项目名称，项目开始日期)ASSELECT a编号，a姓名，b名称，b开始日期，FROM员工数据表AS a INNER JOIN项目数据表AS bON a编号=b负责人WHERE a编号=b负责人GO(2)使用INSERT语句通过视图向员工数据表中添加一条记录，要求姓名字段值为马中兴。USETestDBGOINSERTINTO 员工项目(员工姓名)VALUES(马中兴)GO (3)使用UPPDATE语句通过视图将第二步中插入记录的员工姓名改为马中新。USETestDBGOUPDATE员工项目SET 项目负责人= 马中新，WHERE 项目负责人=马中兴GO (4)执行系统存储过程 sp_rename将视图更名为employee_project_VIEW 。USETestDBGOEXEC sp_rename 员工项目, emp1oyee_project_VIEW; 实验内容分别使用企业管理器和Transact-SQL语句在实验二的数据表员工数据表中基于姓名创建索引，要求索引名为IDX_Name，索引类型为非聚集索引。实现步骤方法一，使用企业管理器 (1)打开企业管理器，在树状目录中展开数据表员工数据表所在的数据库节点TestDB，并选择其下一级节点表。 (2)选择表员工数据表，并单击鼠标右键。在弹出菜单中选择命令所有任务管理索引，打开管理索引对话框。 (3)在管理索引对话框中的数据库下拉菜单中选择数据库表。 (4)选择新建按钮，弹出新建索引对话框。 (5)在新建索引对话框的索引名文本框中输入索引名称IDX_Name，然后选择字段姓名并设置索引属性。如图所示。图4-l创建索引(6)设置索引选项以后，单击确定按钮，返