1、版数据库原理实验指导书数据库原理实验指导书计算机与通信工程学院2010年7月目 录前言 1实验一 数据库查询 2实验二 数据更新 5实验三 视图和索引 6实验四 数据库保护 8实验五 T-SQL编程 10实验六 存储过程编程 11实验七 触发器编程 12实验八 JDBC编程* 13实验九 事务与死锁* 14实验名称学时上课时间1实验1 数据库查询462实验2 数据更新实验 273实验3 索引和视图24实验4数据库保护485实验5 T-SQL编程4106实验6存储过程4117实验7 触发器498实验8 JDBC编程4129实验9事务处理413前言（一）本课程的教学目的和要求：SQL语言是关系数据
2、库的标准语言，是本课程的一个重点。通过上机实验，可以使学生加深对课堂讲授内容的理解，循序渐进地掌握SQL语言的使用；同时，使学生了解和熟悉SQL SERVER 2000开发的环境，逐步掌握编辑、调试、运行程序的方法，初步积累编程经验。（二）实验步骤：1准备好上机所需的程序。手编程序应书写整齐，并经人工检查无误后才能上机，以提高上机效率。对程序中自己有疑问的地方，应作出记号，以便在上机时给予注意。2上机输入和调试自己所编的程序。一人一组，独立上机，上机过程中出现的问题，除了是系统的问题以外，一般应自己独立处理。3上机结束后，整理出实验报告，实验报告应包括以下内容：1) 题目；2) 程序清单（加注
3、释）；3) 运行结果；4) 对运行情况所作的分析5) 本次调试程序取得的经验。（三）教学重点与难点：1. 数据库及表的建立与使用2. 简单查询3. 连接查询4. 嵌套查询5. 数据库更新6. 视图的定义和查询7. 安全性控制8. 事务的建立和使用9. 存储过程的使用10. 触发器的使用11. 数据转换实验一 数据库查询课程名称：数据库原理实验实验类型：验证型实验名称数据库查询学时4学时实验目的：使学生掌握SQL Server Query Analyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。实验
4、原理：SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING order by ASC|DESC；实验方法：将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验内容：1. 分别用带DISTINCT和不带DISTINCT关键字的SELELCT在student中进行查
5、询.2. 查询teacher表中各教师的姓名、教工号及工资按95发放的信息，并将工资按95发放后的列名改为预发工资3. 查询选修了2学分以上的课程，并且笔试成绩不及格的学生的信息，只需给出学号、课程号和笔试成绩。4. 查询学分在48之间的课程信息.（用between.and和复合条件分别实现）5. 从sc表中查询出学生“g9940202”，“g9940204”选择的课程号、学生号以及笔试成绩，并按笔试成绩降序排列（用in实现）6. 从teacher表中分别检索出姓王的教师的资料，或者姓名的第2个字是远或辉的教师的资料7. 查询每个学生及其选修课情况8. 以student表为主体列出每个学生的基
6、本情况及其选课情况，如果学生没有选课，只输出其基本情况（提示：使用外连接）9. 查询选修dep04_s001号课程且笔试成绩在80分以上的学生信息。（分别用连接，in和exists实现）10. 查询所有上“计算机基础”课程的学生的学号、选修课程号以及分数（分别用连接，in和exists实现）11. 查询所有“计算机科学与技术”专业学生的学号、选修课程号以及分数（分别用连接，in和exists实现）12查询每个dep_04系学生的总成绩、平均成绩， 仅显示平均成绩及格的学生的记录。（提示使用ISNULL函数，用法见附录2）13查询“数据库开发技术”的平均成绩14按职称查询教师的平均工资，并按总工
7、资降序排列15将笔试成绩转换成A、B、C、D和E五分制显示。（提示使用CASE函数）附录1：教务管理数据库jwgl结构student表结构列名称数据类型长度允许空值说明Student_idChar8否学生学号Student_nameVarchar8否学生姓名SexBit1否性别AgeInt4否年龄Class_id Char6否班级号Department_idChar6否系编号Addressvarchar50否家庭住址Course表字段名称数据类型长度允许空说明Course_idChar10否课程号Course_nameVarchar20否课程名称Book_idChar15否书标识creditT
8、inyint否学分Total_periodTinyint否总学时Week_periodTinyint否周学时SC表字段名称数据类型长度允许空说明Course_idChar10否课程号Student_idVarchar8否学生号WrittenScoreTinyint否笔试成绩LabScoreTinyint否实验成绩Teacher表字段名称数据类型长度允许空说明Teacher_idChar9否教师编号Teacher_namenvarchar8否教师姓名Department_idChar6否所在系号ProfessionNvarchar16职称SexBit否性别phoneNvarchar15是电话ad
9、ressNchar40是住址SalaryNumeric7.2是工资BirthSmalldatetime否出生日期PostalcodeNumeric6,0是邮编Book表字段名称数据类型长度允许空说明Book_idChar13否书号Book_nameVarchar30否书名Publish_companyVarchar50否出版社Authornvarchar8是作者PriceNumeric5.2是价格Class表字段名称数据类型长度允许空说明Class_idChar6否班级号MonitorVarchar8是班主任姓名Class_course表字段名称数据类型长度允许空说明Class_idChar6
10、否班级号Course_idchar10否课程号Department表字段名称数据类型长度允许空说明Department_idChar6否系编号Department_namenvarchar20否系名称附录2：ISNULL函数 作用：使用指定的替换值替换 NULL语法：ISNULL ( check_expression , replacement_value ) 参数：1. check_expression 将被检查是否为 NULL 的表达式。 check_expression 可以为任何类型。2. replacement_value 当 check_expression 为 NULL 时要返回
11、的表达式。replacement_value 必须是可以隐式转换为 check_expresssion 类型的类型。 返回类型：返回与 check_expression 相同的类型。 如果文字 NULL 作为 check_expression 提供，则返回 replacement_value 的数据类型。 如果文字 NULL 作为 check_expression 提供并且未提供 replacement_value，则返回 int。实验二 数据更新课程名称：数据库原理实验类型：验证型实验名称数据更新学时2学时一实验目的和要求：掌握用SQL语句实现数据的插入、修改和删除。二实验内容：1将一个新学
12、生记录（student_id：g9940305、student_name：张红、sex：女、birth：1985-02-08、class_id：g99403,department_id:Dep_01,home_addr:沈阳皇浦区34号）插入到student表中2对每一个班，求学生的平均年龄，并把结果存入数据库。（提示：先建立新表“age_avg”，其中一列存放班级，另一列存放相应的学生平均年龄，然后将所需数据存入age_avg）3将学号为g9940201的学生的姓名改为张宏，年龄改为184student表中的所有系别(department_id)均改为“dep_03”5将班级号(class_
13、id)为g99402的全体学生的笔试成绩加10分6删除所有选修了课程号(course_id)为dep04_b001的学生三实验报告：1. 实验各项内容实现的过程。2. 实验结果分析。实验三 视图和索引课程名称：数据库原理实验类型：验证型实验名称视图和索引学时2学时实验目的：(1) 掌握视图的定义、查询和更新；(2) 掌握索引的定义；(3) 掌握利用索引优化查询效率；实验原理：1视图 定义视图CREATE VIEW ( ，) AS WITH CHECK OPTION； 删除视图DROP VIEW 2索引（注意：索引不能修改） 定义索引CREATE UNIQUECLUSTER INDEX ON （
14、，）； 删除索引DROP INDEX 实验方法：将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验内容：在实验1中用到的教务管理数据库jwgl中完成如下问题：(1) 创建一个名为stud_query_viewl的视图，从数据库JWGL的student表中查询出性别为“男”的所有学生的资料;(2) 创建一个名
15、为stud_query_view2的视图，从数据库JWGL的student表中查询出性别为“男”的所有学生的学号，姓名，性别，班级，家庭住址，年龄;(3) 创建一个名为“stud_query_view3”的视图，它是由表course、book及class_course创建的一个显示“g99402”班所开课程的课程名、所用教材的教材名、出版社及作者的视图;(4) 创建一个从视图stud_query_viewl中查询出班级名为g99402的所有学生的信息；(5) 从视图stud_query_view2中查询出班级为“g99402”、姓名为“张华”的信息;(6) 向视图stud_query_View
16、2中插入一行数据。学号：g9940209，姓名：赵青，班级：g99402，性别：男，家庭住址：南京中山北路10号，年龄17岁;(7) 修改视图stud_query_view2中的数据。将stud_query_view2中姓名为“林红”的家庭地址改为“扬州市南京路8号”；(8) 从视图中stud_query_view2将姓名为“林红”的同学删除；(9) 为表teacher创建一个索引名为teacher_index的惟一性非聚簇索引，索引关键字为teacher_id，升序；(10) 为表class_course上创建一个名为class_course_index的非聚簇复合索引，索引关键字为clas
17、s_id,course_id，升序(11) 假设第（6）小题在插入赵青信息时，希望将赵青所在系“dep_03”也同时插入到数据表中，该如何处理。本题目留作思考题，可自行查找资料完成。实验报告：1. 实验各项内容实现的过程。2. 实验结果分析。实验四 数据库保护课程名称：数据库原理实验类型：设计型实验名称数据库保护学时4学时实验目的：（1） 使学生掌握E_R图的设计方法，并能将E_R图转换成关系模式；（2） 熟悉SQL DDL和DML语言，能够完成基本的表结构的定义、修改；（3） 掌握数据库完整性约束定义（4） 掌握数据库安全授权实验原理：1. 将E_R图转换为关系的方法： 一个实体转换为一个关
18、系模式，实体的属性就是关系的属性，实体的码就是关系的码 一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并 一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并 一个m:n联系转换为一个关系模式 三个或三个以上实体间的一个多元联系可以转换为一个关系模式2. 定义库：Create DATABASE 3. 表 定义表CREATE TABLE （列级完整性约束，列级完整性约束，）； 添加列ALTER TABLE ADD 列级完整性约束 修改列ALTER TABLE ALTER COLUMN 列级完整性约束 删除列ALTER TABLE DROP COLU
19、MN 4安全授权 创建登录名CREATE LOGIN WITH PASSWORD= 为当前数据库创建用户CREATE USER FOR LOGIN 授权GRANT, ON TO ,WITH GRANT OPTION; 回收权限REVOKE, ON FROM ,CASCADE;实验方法：将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要
20、进行修改，直到正确为止。实验内容：假设某公司的业务规则如下：（1）公司下设几个部门，如技术部、财务部、市场部等。（2）每个部门承担多个工程项目，每个工程项目属于一个部门。（3）每个部门有多名职工，每一名职工只能属于一个部门。（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工，一个职工可以参与多个项目。根据职工在工程项目中完成的情况发放酬金。（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称、办公电话和办公地点等属性；职工有职工号、姓名、性别属性，出生日期；问题：1根据上述规则设计E-R模型;2将E-R模型转换成关系数据模型；3利用SQL语句定义上述关系，要求： （
21、1）为每个关系定义主码和外码； （2）职工姓名、部门名称不能取空值，并且部门名称是唯一的； （3）职工的性别只能取“男”和“女”，默认值为“男”； （4）部门号具有类似“B01”的形式，即每个部门号的首字符为“B”，其余两个字符为数字。 (5)酬金不低于2000元。4修改部门号的约束，改为“B001”的形式，每个部门号的首字符为“B”，其余三个字符为数字。5在原有关系的基础上，增加一个新列，用于记录每名职工参加项目的起始时间。6数据库安全性实验(1) 在SQL Server Query Analyzer中建立登录用户u1,u2,u3,u4，并使其成为jwgl数据库的用户。(2) 把对stude
22、nt表的查询权限授予用户u1，并允许其将权限转授给其它用户。验证u1是否获得相应权限。(3) 用户u1将查询student表的权限授予用户u2。验证u2是否获得相应权限。(4) 将对course表的查询权限授予所有用户。(5) 建立用户u5，并验证u4和u5是否获得查询course表的权限。(6) 回收u4查询course表的权限，并验证。实验五 T-SQL编程课程名称：数据库原理实验实验类型：综合型实验名称T_SQL编程学时4实验目的和要求：1. 掌握用户自定义类型的使用2. 掌握变量的分类及其使用3. 掌握各种运算符的使用4. 掌握各种控制语句的使用5. 掌握系统函数及用户自定义函数的使用
23、。实验内容：1定义一个函数check_id()，实现如下功能。对于1个给定的department_id之值，查询该值在department表中是否存在，若存在返回0，否则返回-1。2. 写一段TSQL脚本程序调用上述函数。当向student表插入1条记录时，首先调用函数check_id()检索该记录的department_id之值在表departments的department_id字段中是否存在对应值，若存在，则将该记录插入student表中，否则打印错误提示信息。3. 创建一个名为age()的用户自定义函数，用于根据给定的学生编号计算学生的年龄，然后在查询语句中调用该函数。4创建一个名为s
24、core()的函数，返回一每个学生的学号、姓名、笔试成绩、实验成绩和总成绩，然后在查询语句中调用该函数查询中成绩不及格的学生。5打印乘法口诀表，显示格式如下：实验六 存储过程编程课程名称：数据库原理实验实验类型：综合型实验名称存储过程编程学时4实验目的掌握存储过程的创建及调用。实验内容1、请创建存储过程，查看“计算机基础”课程本次考试平均分以及未通过考试的学员名单。（笔试成绩满分80分，上机成绩满分20分，双科成绩均达到满分的80%以上为优秀，单科不足满分的60%为不合格，否则为合格）。(提示：用带有游标的存储过程)2、修改上题：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化
25、（不再是满分的60%），这导致考试的评判结果也相应变化。（提示：使用带输入参数的存储过程）3、修改上题：返回未通过考试的学员人数。（提示：使用带输出参数的存储过程）4、编写存储过程，显示男生或女生的名单（性别要求通过参数输入）。格式为： 序号学号姓名(提示：用带有游标的存储过程)实验七 触发器编程课程名称：数据库原理实验实验类型：综合型实验名称触发器编程学时4实验目的理解触发器的工作原理，掌握如何使用inserted表和deleted表及如何创建：INSERT、UPDATE、DELETE触发器。实验内容建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(tr
26、ansInfo)存放每次的交易信息。 1、当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。 2、当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。3、跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。 实验报告1、实验各项内容实现的过程。2、实验结果分析。实验八 JDBC编程*课程名称：数据库原理实验实验类型：综合型学时4实验目的：熟悉使用JDBC来进行数据库应用程序的设计，熟悉通过JDBC接口编程访问数据库并对数据库进行操作。实验要求：配置环境变量。使用Java语言编程实现对数据库的访问，所有的SQL操作均在自己建
27、立的新库里进行，数据库建议选用教务管理数据库，读者可以选择进行创建、插入、查询、删除和更新等操作。要求：（1） 以对学生表的操作为例，程序循环输出如下格式菜单，根据用户的选择，完成对学生表的各类操作：1=List ALL; 2=List(By Student_ID);3=Insert;4=Insert; 0=Exit（2）提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。实验九 事务与死锁*课程名称：数据库原理实验实验类型：验证型学时4实验目的1 了解事务的ACID属性，掌握事务和批处理的工作原理。2 了解死锁的类型和其兼容性。3 了解死锁发生的情况。4 了解可锁定的资源。5 了解死锁的一般处理过程。实验内容：1.银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。(1)试用事务解决银行转帐问题。参考代码如下：
