大三下学期--数据库系统原理实验讲义(2009级3班)

楚雄师范学院计算机科学系计算机专业数据库系统概论课程实验教学大纲实验一 数据库管理系统的认识一、实验目的1、通过对某个商用数据库管理系统（本课程使用SQLSERVER2000关系数据库管理系统）的使用，了解DBMS的工作原理和系统构架。2、熟悉对DBMS的操作。3、搭建今后实验的平台。二、实验类型验证性实验三、实验教学要求1性质：选做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求：1）、操作系统：Windows 2000或者Windows XP。2）、数据库管理系统：SQL Server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤（一）实验内容1、安装和启动1）根据安装文件的说明安装数据库管理系统。在安装过程中记录安装的选择，并且对所作的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。2）学会启动和停止数据库服务，思考可以用哪些方式来完成启动和停止。3）初步了解SQL Server2000的安全性，这里主要是用户的登录和服务器预定义角色。2、数据库系统的构架1）了解数据库系统的逻辑组件：它们主要是数据库对象，包括基本表、视图、触发器、存储过程、约束等。今后将学习如何操作这些数据库对象。2）了解数据库的物理组件：思考数据库物理存储的存储单位是什么，数据主要以什么方式存放，如何确定数据的存放位置。3、SQL Server2000的管理和使用：了解SQL Server2000如何通过它提供的工具对数据和数据库服务器进行管理和使用的。1）启动、暂停和停止SQL Server20002）学会运用控制管理器和企业管理器进行操作。3）了解系统其他管理工具：初步了解SQL Server2000的主要工具和它们的功能，为今后的实验做准备。例如，数据迁移工具：导入和导出数据使用的；企业服务器和作业调度管理器：可以用该工具对数据库服务器进行管理，管理和监视服务器性能和活动；查询分析器：给出数据库查询语句的查询计划和执行时间，帮助用户分析查询过程，进行查询优化等等。（二）实验步骤1安装Microsoft的数据库服务软件SQL Server 2000：运行服务器目录SQL2000下的可执行文件autorun.exe，进行SQL Server 2000的安装。安装主界面如图1-1所示。图1-1选择安装SQL Server 2000组件，出现组件安装界面，如图1-2所示。图1-2选择安装数据库服务器，进入SQL Server 2000的安装向导，如图1-3所示。图1-3点击“下一步”，到计算机名称对话框，如图1-4所示。图1-4“本地计算机”是默认选项，本地计算机的名称就显示在上面，点击“下一步”，出现安装选择对话框，如图1-5所示。图1-5选择“创建新的SQL Server实例，或安装客户端工具”，点击“下一步”，出现用户信息对话框，如图1-6所示。图1-6输入姓名和公司名称后，点击“下一步”，出现软件许可协议，如图1-7所示。图1-7点“是”按钮，出现安装定义对话框，如图1-8所示。图1-8选择“服务器和客户端工具”，点击下一步，出现实例名对话框，如图1-9所示。图1-9系统提供了“默认”复选框，点击下一步，出现安装类型选择框，如图1-10所示。图1-10选择典型，点击“下一步”，出现服务帐户对话框，如图1-11所示。图1-11接受系统的缺省值，点击“下一步”，出现身份验证模式窗口，如图1-12所示。图1-12接受系统的缺省值，点击“下一步”，出现开始拷贝文件窗口，如图1-13所示。图1-13点击“下一步”，开始将文件拷贝到选择的目录中，如图1-14所示。图1-14文件拷贝完成后，出现安装结束界面，如图1-15所示。图1-15点击“完成”按钮，完成SQL Server 2000的安装。2熟悉软件的使用，SQL Server 2000主菜单如图1-16所示：图1-163启动数据库服务软件SQL Server 2000：在程序菜单中选择Microsoft SQL Server，如图2-1所示：图2-1再选中服务管理器，如图2-2所示：图2-2点击服务管理器后，出现SQL Server服务管理器，如图2-3所示：图2-3点击“开始/继续”按钮，启动SQL Server 2000数据库服务。启动成功后，在时钟旁边出现一个符号，如图2-4所示。图2-44在SQL Server 2000中建立数据库：在程序菜单中选择Microsoft SQL Server，如图2-5所示：图2-5再选中企业管理器，如图2-6所示：图2-6点击企业管理器后，出现企业管理器的主界面，如图2-7所示：图2-7点击左边树状控制栏的+/号可以打开和关闭SQL Server组中的本地服务项目，在选中数据库服务项目后，单击鼠标右键，出现数据库的菜单，如图2-8所示：图2-8点击新建数据库，出现新建数据库窗口,如图2-9所示：图2-9输入数据库名称：Test，点击数据文件选项卡，如图2-10所示：图2-10在数据文件中，你可以设定数据文件的名称，所在目录以及文件的大小等信息。完成后点击事务日志选项卡，如图2-11所示：图2-11事务日志中，你可以设定日志文件的名称，所在目录以及文件的大小等信息。接受系统的缺省值，点击确定按钮，完成数据库的新建，如图2-12所示。图2-125.在Test数据库上建立表：点击数据库Test前面的号，打开数据库的各种属性，如图2-13所示：图2-13选中表的属性，单击鼠标右键，出现表的菜单，如图2-14所示：图2-14点击新建表按钮，出现新建表窗口，如图2-15所示：图2-15输入列名，数据类型，长度等信息，点击图标，出现选择表名窗口，如图2-16所示：图2-16输入表名，点击确定按钮，将新建的表存盘。向该表中添入数据。方法：进入企业管理器、分别点击“数据库”、“表”，右击要添加数据的表，在系统弹出的快捷菜单上，分别点击“打开表”、返回“所有行”，系统将弹出如图2-17的窗口。图2-17可以把鼠标定位在窗口任一行的任一列上，对此单元格的数据进行修改，也可以右击单元格，在系统弹出的快捷菜单上选择“删除”将某条记录删除掉。如果把光标定位在一个新的空行上，就可以添加新的数据了。但要注意，表中的列经常是带有约束的，比如数据类型，无论修改数据，还是插入新数据，都必须遵循约束的限制，否则，修改或插入数据就可能不成功。六、实验思考题实验二 数据操纵一、实验目的1、熟悉数据库的交互式SQL工具。2、熟悉通过SQL对数据库进行操作。二、实验类型验证性实验三、实验教学要求1性质：必做2时间要求：6课时2对学生的要求：3对教师的要求：4对实验室的要求：pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤（一）实验内容在SQL Server2000中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。同时根据以下要求认真填写实验报告，记录所有的实验用例。1、数据定义1）熟悉基本表的创建、修改及删除。2）熟悉索引的创建和删除。3）熟悉视图的创建和删除。2、数据操作1）完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）。2）完成各类更新操作（插入数据，修改数据，删除数据）。3、视图的操作视图的定义（创建和删除），查询，更新（注意更新的条件）。（二）实验步骤1打开数据库SQL Server 2000的查询分析器，用SQL语言建立一个数据文件名为stu_data、大小50M，日志文件名为stu_log、大小25M的新数据库，该数据库名为stu。并在该项数据库内创建student、course和sc表，表结构如下图所示：student字段名类型长度含义snochar10学号snamechar8姓名ssexchar2性别sageinteger年龄sdeptvarchar20系别Course字段名类型长度含义cnochar5课程编号cnamevarchar20课程名cpnochar5先行课Ccreditint学分sc字段名类型长度含义snochar10学号cnochar5课程编号gradeint5先行课2用SQL语言向各表中插入记录，记录见教材。3 利用查询分析器完成如下查询操作。1) 查询全体学生的详细记录；2) 查询所有选修过课的学生的学号；3) 查询考试成绩不及格的学生的学号；4) 查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别；5) 查所有姓刘的学生的姓名、学号和性别；6) 查 DB_Design 课程的课程号和学分；7) 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列；8) 查询学习1号课程的学生最高分数；9) 查询各个课程号与相应的选课人数；10) 查询每个学生及其选修课程的情况；11) 查询每个学生及其选修的课程名其及成绩；12) 查询每一门课的间接先行课（即先行课的先行课）。13) 求选修了高等数学的学生学号和姓名。14) 求2课程的成绩高于张三的学生学号和成绩。15) 求其他系中年龄小于计算机系年龄最大者的学生。16) 求其他系中比计算机系学生年龄都小的学生。17) 求选修了2号课程的学生姓名。18) 求没有选修2课程的学生姓名。19) 查询选修了全部课程的学生的姓名。20) 求选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。21) 查询计算机科学系的学生及年龄不大于19岁的 学生22) 查询选修了课程1或者选修了课程2的学生。23) 查询计算机科学系的 学生与年龄不大于19岁的学生的交集。24) 查询选修了课程1的学生的集合与选修了课程2的学生的集合。25) 查询计算机科学系的学生与年龄大于19 的学生的差集。4利用查询分析器用UPDATE语句更改表中数据。1) 将学生95001的年龄改为22岁。2) 将所有学生的年龄增加1岁3) 将计算机科学系全体学生的成绩置零。5利用查询分析器用DELETE语句删除表中数据。1) 删除学号为95019的学生记录。2) 删除计算机科学系所有学生的选课记录3) 删除所有的学生选课记录6利用查询分析器执行如下操作。1) 建立信息系学生的视图IS_student2) 建立信息系选修了1号课程的学生的视图IS_S13) 建立信息系选修了1号课程且成绩在90分以上的学生的视图IS_S24) 建立一个反映学生出生年份的视图BT_S5) 将学生的学号及他的平均成绩定义为一个视图S_G.6) 将课程的课号及选修人数定义为一个视图C_XIU.7) 在信息系学生的视图中找出年龄小于20岁的学生8) 查询信息系选修了1号课程的学生9) 在S_G中查询平均成绩在90分以上的学生的学号和平均成绩10) 在C_XIU中查询选修人数在2人以上的课程号11) 将信息系学生视图IS_Student中学号为95002的学生姓名改为刘辰12) 向信息系学生视图IS_S中插入一个新的学生记录,其中学号为95029,姓名为赵新,年龄为2013) 删除信息系学生视图IS_S中学号为95029的记录.14) 删除IS_S1视图15) 删除BT_S视图7向Student 表增加“入学时间”列（列名为Scome,日期型），将年龄的数据类型改为半字长整数。8为SC建立按学号升序和课程号降序建立唯一索引。六、实验思考题实验三 数据库的安全性一、实验目的1、了解DCL通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。2、熟悉通过SQL对数据进行安全性控制。二、实验类型验证性实验三、实验教学要求1性质：必做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤（一）实验内容使用SQL对数据进行安全性控制，包括：授权和权力回收。操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力）。根据要求认真填写实验报告，记录所有的实验用例。（二）实验步骤1用Windows2000的Administrator(系统管理员)用户登录系统，建立一个新用户DCL，用户名和口令都相同。2打开数据库SQL Server 2000的查询分析器，用GRANT语句将ALL PRIVILEGES的系统特权授给用户DCL。3注销用户Administrator，用DCL登录系统，打开数据库SQL Server 2000的查询分析器。4建立U1,U2,U3,U4,U5,U6,U7,U8用户，利用查询分析器对这些用户进行授权。并加以验证.1) 把查询Student表的权限授给用户U1.2) 把对Student表和Course表的全部操作权限授予用户U2和U33) 把对表Sc的查询权限授予所有用户.4) 把查询Student表和修改学生学号的权限授给用户U45) 把对表SC的INSERT权限授予U5用户,并允许将此权限再授予其他用户6) DBA把在Stu中建立表的权限授予用户U85利用查询分析器对这些用户进行收权.并加以验证.1) 把用户U4修改学生学号的权限收回2) 收回所有用户对表的查询权限3) 把拥护U5对SC表的INSERT 权限收回6注销用户DCL，用Administrator登录系统，用REVOKE语句将用户DCL在表Abc中修改A1的权利收回。六、实验思考题实验四 数据库的完整性一、实验目的熟悉通过SQL对数据进行完整性控制。二、实验类型验证性实验三、实验教学要求1性质：必做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤（一）实验内容使用SQL对数据进行完整性控制（3类完整性、CHECK短语、CONSTRAIN子句、触发器）。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。根据要求认真填写实验报告，记录所有的实验用例。（二）实验步骤1建立Student、Course及SC的主码约束。2建立 Student与SC、Course与SC之间的外码约束。3实现学生性别只能是“男”或“女”的Check(检查)约束。4实现学生年龄只能小于等于30大于17的语义约束。5定义一个触发器，当学生所在系别为“计科系”时，自动改写为“计算机科学系”。6创建一个学号（以我校的学号编码规则为准）的规则，并将其绑定到student表中的SNO属性上。7将student表中的SSEX属性的默认值设为“男”。六、实验思考题实验五 数据库的备份和恢复一、实验目的1、理解备份的基本概念，了解备份设备的概念。2、掌握各种备份数据库的方法，了解如何制定备份计划，如何从备份中恢复设备。二、实验类型验证性实验三、实验教学要求1性质：必做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤1. 创建备份设备 先创建文件夹D：你的学号。 l）在D：stubak 位置创建一个名为DiskBak_yourdb的本地磁盘备份文件。 USE yourdb GO EXEC sp_addumpdevice disk，DiskBak_yourdb，D:stubak你的学号.bak 2）查看系统中有哪些备份设备。 Sp_helpdevice2. 进行数据库备份，管理备份设备 1）创建数据库yourdb的完全备份。 USE yourdb BACKUP DATABASE yourdb TO DiskBak_yourdb2）查看备份设备中备份集内包含的数据库和日志文件列表。 RESTORE FILELISTONLY FROM DiskBak_yourdb 3）查看特定备份设备上所有备份集的备份首部信息。 RESTORE HEADERONLY FROM DiskBak_yourdb 在执行上述语句返回的结果集中： BackupName：备份集名 BackupDescription：备份描述 BackupType：备份类型 Position：备份集号 DeviceType：备份设备名 ServerName：服务器名 DatabaseName：数据库名 BackupSize：备份集大小 其中BackupType值的含义如下：1：完整数据库备份 2：事务日志备份 4：文件各份5：差异数据库备份 6：差异文件备份 4）创建数据库yourdb的差异备份。先修改数据库中Student表的记录，再进行差异备份。 BACKUP DATABASE yourdb TO DiskBak_yourdb WITH DIFFERENTIAL 5）创建数据库yourdb的事务日志备份。 BACKUP LOG yourdb TO DiskBak_yourdb3. 数据库恢复 1）从备份设备 DiskBak_yourdb的完整数据库备份中恢复数据库yourdb。 USE master RESTORE DATABASE yourdb FROM DiskBak_yourdb 2）从备份设备DiskBak_yourdb的差异数据库备份（假设为第24个备份集）中恢复数据库yourdb。 RESTORE DATABASE yourdb FROM DiskBak_yourdb WITH NORECOVERY GO RESTORE DATABASE yourdb FROM DiskBak_yourdb WITH FILE24，RECOVERY 3）从备份设备DiskBak_yourdb的事务日志备份（假设为第1个设备集）中恢复数据库yourdb。 RESTORE LOG yourdb FROM DiskBak_yourdb WITH FILE20，NORECOVERY4删除特定备份设备。 Sp_dropdevice DiskBak_yourdb六、实验思考题1. 根据实验步骤，再回顾一下前面介绍的实验原理，确切理解数据库备份的含义。2. 根据实验步骤中备份数据库的各种方法进行数据库备份，并注意不同方法之间的差别。3. 思考哪些数据库文件应该定期备份，为什么？4. 在用T-SQL语句进行数据库恢复的实验中，没有给出太多的实例，主要是要大家举一反三，同学还可按照语法设计实验自己完成。5. 对前面已建立的某个数据库进行备份，然后在它上面创建一个表格，并记住它的名字，随后进行数据库恢复，比较恢复前后这个数据库的不同点。实验六 通过嵌入式SQL访问数据库一、实验目的熟悉通过嵌入式SQL（主语言为C语言）编程访问数据库。二、实验类型设计性实验三、实验教学要求1性质：选做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤熟悉RDBMS的预编译程序。通过嵌入式SQL编程访问数据库的基本步骤对学生课程数据库中的表，完成下面功能（也可以自己给出功能要求）：1、查询某一门课程的信息。要查询的课程由用户在程序运行过程中指定，放在主变量中。2、查询选修某一门课程的选课信息，要查询的课程号由用户在程序运行过程中指定，放在主变量中，然后根据用户的要求修改其中某些记录的成绩字段。要求：提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。3、嵌入式SQL要求：将一段连续的SQL操作写成嵌入到C程序中的程序，调试程序，使得程序能完成一项复杂的数据处理功能，并提交程序。嵌入式SQL由SQL语句和C/C+代码组成。其中SQL语句由预处理器翻译成C或C+的源代码。对预处理后的源代码进行编译、链接生成可执行程序后方可运行。六、实验思考题实验七 通过ODBC方式访问数据库一、实验目的1、学会配置ODBC数据源。2、熟悉使用ODBC来进行数据库应用程序的设计。二、实验类型设计性实验三、实验教学要求1性质：必做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求：SQL Server2000及其相关工具。编程工具自选。四、实验参考资料内容（宋体五号）五、实验内容及步骤1.创建ODBC数据源或直接使用ADO连接到SQL Server的技术。2.在高级语言（VB、Delphi、PB、VC、VF等）中编程对SQL Server中的数据进行访问、操作。3.建议步骤：1)、建立工程项目；2)、界面窗口设计；添加数据库相关的典型控件。3)、添加数据库控件。4)、修改控件属性，把控件和数据库绑定。5)、利用控件直接操作数据库。6)、退出宿主语言后，在DBMS环境下观察数据库的变化。7)、在操纵语言编程环境下为事件增加程序代码，实现对数据库的操纵；8)、保存设计的可执行代码和相应的数据库。4至少完成以下两个界面六、实验思考题实验八 使用PL/SQL编写存储过程访问数据库一、实验目的熟悉使用存储过程来进行数据库应用程序的设计。二、实验类型设计性实验三、实验教学要求1性质：选做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤对学生课程数据库，编写存储过程，完成下面功能： 1）统计离散数学的成绩分布情况，即按照各分数段统计人数； 2）统计任意一门课的平均成绩。 3）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）； 要求：提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。六、实验思考题实验九 通过JDBC方式访问数据库一、实验目的1、熟悉使用JDBC来进行数据库应用程序的设计。2、熟悉通过JDBC接口编程访问数据库并对数据库进行操作。二、实验类型验证性实验三、实验教学要求1性质：选做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤配置环境变量。使用Java语言编程实现对数据库的访问，所有的SQL操作均在自己建立的新库里进行，数据库建议选用学生课程数据库，并选择进行创建、插入、查询、删除和更新等操作。要求：提交应用程序源代码，并标识必要的注释，尽可能清楚明白地说明程序的功能，实现的方法，关键数据结构、变量、函数的定义。六、实验思考题实验十 事务管理一、实验目的理解事务的概念及事务的结构，掌握事务的使用方法。二、实验类型验证性实验三、实验教学要求1性质：选做2时间要求：2课时2对学生的要求：3对教师的要求：4对实验室的要求： pc机、Windows操作系统、sql server2000。四、实验参考资料内容（宋体五号）五、实验内容及步骤实验前先给Student表的birthday字段创建CHECK约束，使输入的生日小于系统日期USE yourdb GO 1. 比较以非事务方式及事务方式执行SQL脚本的异同1) 以事务方式修改Student表中学号为“” 李慧同学的姓名及出生年月。 USE yourdb GO 执行完上述脚本后，查看对表 student中学号为“”的记录的影响。注意在前面的实验中，Student表的birthday字段己经加了CHECK约束。2) 以非事务方式执行同上的语句。 USE yourdb GO 再执行上述脚本，查看表Student中学号为“”的记录。注意比较两种执行方式对执行结果的影响有何不同。2比较以事务方式及非事务方式向表中插人数据Class表CLASS_IDDEPT_IDABBREVIATIONMONITORLEVELSENROLL_DATENO_STUDENT11信管032本科2003Student表：STUDENT_IDCLASS_IDNAMEGENDERBIRTHDAYADDRESSZIP_CODEHOUSEHOLDER陈岩清男200611GradeSTUDENT_IDCOURSE_IDGRADE851) 以事务方式向表中插入记录：A. 无断点方式： USE yourdb GO B. 有断点方式： USE yourdb GO 分别执行以上脚本，分析回滚操作在有无断点时如何影响分列于事务不同部分的3条insert语句。2) 以非事务方式向表中插入记录： USE yourdb GO 再执行上述脚本，查看三条insert语句执行的结果。注意比较事务与非事务两种执行方式对执行结果的影响有何不同。六、实验思考题1根据实验步骤，再回顾一下前面介绍的实验原理，确切