数据库原理实验指导.doc

数据库原理实验指导计算机学院2015/09修订履历版数改版日期改版者内容第一版2015.9李宁、汤小春根据数据库原理教学大纲，整体重新修订实验内容 目录目录31. 实验概要51.1. 实验内容一览51.2. 实验评分说明61.3. 实验要求61.4. 实验环境和配置62. 实验一:数据库和表的创建与管理72.1. 实验目的72.2. 实验内容73. 实验二:基本表的数据操作103.1. 实验目的103.2. 实验内容104. 实验三:数据完整性与安全性控制114.1. 实验目的114.2. 实验内容115. 实验四:视图与索引135.1. 实验目的135.2. 实验内容136. 实验五:触发器、存储过程和函数146.1. 实验目的146.2. 实验内容147. 实验六:ODBC/JDBC数据库编程167.1. 实验目的167.2. 实验内容168. 实验七:事务与并发控制178.1. 实验目的178.2. 实验内容179. 实验八：数据库综合实验199.1. 实验目的199.2. 实验要求199.3. 实验内容205.3.1. 题目一：零件交易管理系统205.3.2. 题目二：图书管理系统205.3.3. 题目三：民航订票管理系统205.3.4. 题目四：学生学籍管理系统215.3.5. 题目五：车站售票管理系统215.3.6. 题目六：企业人事管理系统215.3.7. 题目七：电话交费管理系统215.3.8. 题目八：医药销售管理系统2210. 附录：实验报告格式231. 实验概要1.1. 实验内容一览1. 必做实验序号实验内容具体内容实验类型实验学时1数据库和表的创建与管理数据库备份还原；数据库、基本表定义、修改、删除验证22基本表的数据操作基本表数据查询、增删改设计与验证23数据完整性与安全性控制数据的完整性约束、安全性控制，其中规则与默认对象为选做实验内容设计与验证24视图与索引视图、索引的创建与应用设计与验证25触发器、存储过程和函数触发器、存储过程以及用户自定义函数的编程编程26ODBC/JDBC数据库编程ODBC数据库调试与编程JDBC为选做实验内容编程27事务与并发控制事物提交、回滚；数据不一致现象；隔离级别与锁；事务日志验证28数据库综合作业设计并实现一个数据库应用系统综合216说明：第8次实验，即数据库大作业，实验课内2小时主要是用于数据库的设计等、其余大量的编程等工作均需要自己安排课余充足的时间完成。1.2. 实验评分说明每次实验满分100分，最后取所有实验的加权平均分作为实验的总成绩。其中大作业占比40%，其余7次实验的平均分占比为60%。每次实验中各个环节的评分标准如下：项目具体要求百分比考勤正常出勤或请假 (无故缺席者0分)10%预习报告（无固定格式，纸版或电子版均可）10%实验实验过程结果的正确性()60%报告实验相关文档的正确性，整洁性20%总计100% 上述每一项按照百分制给出分值，最后按照比率计算每次实验的最终成绩。1.3. 实验要求1. 实验报告要求每次实验需提交电子版的实验报告(最后一次大作业需提交设计文档，源程序等相关资料)。每次实验结束时，将写好的实验报告，提交给各班辅导老师。如果确有困难没有完成的情况下，课后自己完成之后提交到辅导老师的邮箱里。由辅导老师根据课堂上机实验检查状况和实验报告的内容给出每次实验的成绩。每次实验需要提交实验源码和实验报告，实验报告的内容包括：实验内容、实验设计、实验步骤、运行结果（可以是程序的输出，也可以是运行画面的抓屏，抓屏图片要尽可能的小，否则文件太大）。每份实验报告是一个WORD文档，具体模板见本指南的附录部分。实验报告命名规则：DBx(实验次数)_XXXXXX（学号）_姓名 例如：20052978的学生的第一次实验报告文件名： DB1_20052978_李宁 注意：请每个人保存好自己的实验报告的电子版，直到该门课考试成绩公布之后。2. 实验上机要求l 上机之前做好预习，需要写一个简单的预习报告，电子版或纸版均可，格式不限。l 严格遵守实验室的各项规定。1.4. 实验环境和配置SQL Server 2008 R2(Microsoft SQL Server 2008 Express With Advance Service)2. 实验一:数据库和表的创建与管理2.1. 实验目的1. 掌握SQL Server中数据库的逻辑结构和物理结构，基本数据类型等。2. 熟练掌握使用图形用户界面以及SQL语句创建数据库和表。3. 掌握使用不同方法备份和还原数据库。4. 掌握数据库和基本表的修改和删除方法。2.2. 实验内容1. 利用图形用户界面创建，备份，删除和还原数据库和数据表 （50分，每小题5分）l 数据库和表的要求（第四版教材第二章习题5要求的数据库）数据库名：SPJ，其中包含四张表：S表, P表, J表, SPJ表 图2.1 S表(供货商表) 图2.2 P表(零件表) 图2.4 SPJ表(供应情况表) 图2.3 J表(工程项目表)l 完成以下具体操作：(1) 创建SPJ数据库，初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名和物理文件名均采用默认值。(2) 在SPJ数据库中创建如图2.1-图2.4的四张表(只输入一部分数据示意即可)。(3) 备份数据库SPJ（第一种方法)：备份成一个扩展名为bak的文件。（提示：最好先删除系统默认的备份文件名，然后添加自己指定的备份文件名）(4) 备份数据库SPJ（第二种方法)：将SPJ数据库定义时使用的文件(扩展名为mdf,ldf的数据文件、日志文件等)复制到其他文件夹进行备份。(5) 删除已经创建的工程项目表(J表)。(6) 删除SPJ数据库。（可以在系统默认的数据存储文件夹下查看此时SPJ数据库对应的mdf,ldf文件是否存在）(7) 利用备份过的bak备份文件还原刚才删除的SPJ数据库。（还原数据库）(8) 利用备份过的mdf,ldf的备份文件还原刚才删除的SPJ数据库。（附加）(9) 将SPJ数据库的文件大小修改为100MB。(10) 修改S表，增加一个联系电话的字段sPhoneNo，数据类型为字符串类型。2. 利用SQL语言创建和删除数据库和数据表（50分，每小题5分）l 数据库和表的要求数据库名：Student， 其中包含三个表：S：学生基本信息表；C：课程基本信息表；SC：学生选课信息表。各表的结构以及数据如下所示： 图2.5 S表(学生表) 图2.7 SC表（选课表） 图2.6 C表（课程表）l 完成以下具体操作：(1) 用SQL语句创建如图2.5-图2.7要求的数据库Student， 初始大小为20MB，最大为100MB，数据库自动增长，增长方式是按10M兆字节增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名和物理文件名，日志文件名请自定义。(2) 用SQL语句创建上述的三张表，各个字段的数据类型请自己确定，每张表只要有主键约束即可，不要其他约束。不用输入数据。(3) 用SQL中的backup 语句将数据库Student备份到一个bak文件。(提示：backup database student to disk)(4) 用SQL语句删除创建的表。(5) 将SPJ数据库定义时使用的文件(扩展名为mdf,ldf的数据文件、日志文件等)复制到其他文件夹进行备份（复制）后，用SQL语句删除创建的数据库。(6) 用SQL中的sp_attache_db语句完成对数据库Student的附加。（通过该方式完成数据库的恢复时，使用的备份文件是第5）小题中备份好的数据文件、日志文件等 ，具体用法：exec sp_attache_db )(7) 先删除SPJ库，再用SQL中的restore 语句还原刚才备份的数据库Student。 （提示：restore database student from disk）(8) 用SQL语言修改数据库Student，给Student数据库添加一个文件组 TestFG1，然后将两个 5 MB 的文件添加到该文件组。(提示：alter database)(9) 修改S表，增加一个表示联系电话的字段sPhoneNo，数据类型为字符串类型。(10) 利用SQL Server Management Studio的生成脚本功能(选中Student库-右键菜单“任务”- “生成脚本”)，自动生成S表的创建脚本，分析自动生成的脚本和自己写的SQL语句的异同点。3. 实验二:基本表的数据操作3.1. 实验目的1. 掌握使用图形用户界面对基本表进行数据插入、修改和删除。2. 掌握使用SQL语句对基本表进行数据插入、修改和删除。3. 掌握使用SQL语句进行各种查询。3.2. 实验内容1 在图形用户界面中对表中的数据进行更新。（6分，每小题2分）(1) 按照实验一图2.1-图2.4中所示数据，输入SPJ数据库所含四张表中的数据。(2) 修改S表的任意一条数据(3) 删除S表的任意一条数据。2 针对SPJ数据库，用SQL语句完成下面的数据更新。（12分，每小题3分）(1) 第四版教材P127页 5题的8-11小题。3 针对SPJ数据库，用SQL语句完成下面的数据查询。(52分，每小题4分)(1) P127页 第4题。（5道小题）(2) P127页 第5题的1-7小题。（7道小题）(3) 查询这样的工程：供给该工程的零件P1的平均供应量大于供给工程J1的任何一种零件的最大供应量。4 针对Student数据库用SQL语句完成下面的数据查询。(12分，每小题4分)(1) 查询不选修“C语言”课程的学生学号。(2) 查询每个学生已经获得的学分的总分（成绩及格表示获得该门课的学分），并按照所获学分由高到低的顺序输出学号，姓名，所获学分。(3) 查询这样的学生姓名：该学生选修了全部课程并且其中一门课在90分以上。5 针对Student数据库用至少三种不同的SQL语句进行查询：查询选修了课程名为“数据库原理”的学生学号和姓名，然后自己设计实验，用数据比较分析三种查询的效率。（18分）4. 实验三:数据完整性与安全性控制4.1. 实验目的1 掌握使用不同的方法定义和管理数据库的完整性。2 掌握SQL Server中的登录名，角色以及用户的创建以及使用方法。3 掌握使用SQL语句对数据库和表进行安全控制。4 掌握规则的创建、使用和删除。（选做实验）5 掌握默认对象的创建、使用和删除。（选做实验）4.2. 实验内容1 利用图形用户界面对实验一中所创建的Student库的S表中，增加以下的约束和索引。（18分，每小题3分） (1) 非空约束：为出生日期添加非空约束。(2) 主键约束：将学号(sno)设置为主键，主键名为pk_sno。(3) 唯一约束：为姓名(sname)添加唯一约束(唯一键)，约束名为uk_sname。(4) 缺省约束：为性别(ssex)添加默认值，其值为“男”。(5) CHECK约束：为SC表的成绩(grade)添加CHECK约束，约束名为ck_grade，其检查条件为：成绩应该在0-100之间。(6) 外键约束：为SC表添加外键约束，将sno,cno设置为外键，其引用表为分别是S表和C表，外键名称分别为fk_sno，fk_cno。2 在图形用户界面中删除上小题中已经创建的各种约束，用SQL语言分别重新创建第1小题中的(2)-(6)小题。（15分，每小题3分，提示：alter table add constraint）3 利用图形用户界面根据上述SC表中的外键定义画出由S,C,SC表构成的数据库关系图。(5分，提示：选中student - 数据关系图）4 用SQL语言删除S表中所创建的缺省约束和外键约束。(6分，每小题3分）5 在图形用户界面中创建新登录名以及用户。（16分）(1) 根据第四版教材P148页第 8题，创建数据库company和其中的基本表，并创建该题中所需要用到的各个用户以及相关的关的登录名。（10分）(2) 用图形用户界面完成以下的权限赋予：（每小题3分）a) 用户王明对两个表均有Select和Insert的权力。b) 用户李勇对职工表具有Select权力，对工资字段具有更新权力。6 用SQL语句授权和收回权限并对权限加以验证。（40分,每题4分）(1) 第四版教材P148页第8题b),c),d),f),g)。(2) 第四版教材P148页第9题的b),c),d),f),g)。（提示：(c)和(g)小题可创建合适的视图，针对视图进行授权，(c)小题用CURRENT_USER）l 选做实验1 利用SQL语言创建，验证和删除规则。(1) 创建一个ssex_rule规则(create rule)，将其绑定(sp_bindrule)到S表的ssex性别字段上（请验证该规则生效，保证了输入的性别只能是“男”或者“女”）。(2) 删除ssex_rule规则（注意：规则已绑定到ssex表的性别字段上，需要先解除原有的绑定sp_unbindrule, drop rule）。2 利用SQL语言创建，验证和删除默认值。(1) 创建一个ssex_def默认对象(create default)，将其绑定(sp_bindefault)到S表的ssex性别字段上，使其默认值为“男”（请验证该默认值生效）。(2) 删除ssex_def（注意：默认对象已绑定到S表的ssex性别字段上,需要先解除原有的绑定sp_unbindefault, drop default）。5. 实验四:视图与索引5.1. 实验目的1 掌握使用图形用户界面和SQL语言创建、操作和删除视图。2 掌握使用图形用户界面和SQL语言创建和删除索引。3 理解索引的作用。5.2. 实验内容1 在Student数据库中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是1996年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5分)2 用两种不同的SQL语句创建第四版教材128页第11题中要求的视图（视图名：V_SPJ）（10分，每种方法5分)。3 用SQL语句完成第四版教材128页第11题中的视图查询（10分，每小题5分)。4 用SQL语句完成视图的数据更新。(15分，每题5分)(1) 给视图V_SPJ中增加一条数据。提示：- SPJ表中JNO允许为空时，数据可以插入基本表，此时JNO为NULL，由于JNO为NULL，所以视图中没有该条数据。- SPJ表中JNO不能为空时，可以使用instead of触发器实现。(2) 修改视图V_SPJ中的任意一条数据的供应数量。(3) 删除视图V_SPJ中的任意一条数据（注意所创建视图可以视图消解时，才能正常删除，否则会删除失败；也可以考虑用instead of触发器实现）。5 用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的唯一索引,索引名称IX_CNo。(5分)6 使用SQL语句对Student数据库完成以下的索引操作。（15分，每题5分）(1) 在C表的CName属性上创建一个非唯一性的聚簇索引，索引名IX_CName。(2) 在SC表上创建一个名为IX_Cnosno的非聚簇复合索引，该索引是针对sno,cno属性集建立的升序索引。(3) 删除C表的索引IX_CName。7 自己设计一个实验验证索引对数据库查询效率的提升作用。（40分） (提示：需要数据量比较大的情况下才容易进行对比)6. 实验五:触发器、存储过程和函数6.1. 实验目的1. 掌握系统存储过程的使用用法。2. 掌握用户自定义的存储过程的创建和执行方法。3. 掌握触发器的创建和执行方法。4. 掌握用户自定义函数的创建和执行方法。6.2. 实验内容1. 使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。（5分）2. 针对SPJ数据库，创建并执行如下的存储过程：（共计35分）(1) 创建一个带参数的存储过程jsearch。该存储过程的作用是：当任意输入一个工程代号时，将返回供应该工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。执行jsearch存储过程，查询“J1”对应的信息。（10分）(2) 使用S表，为其创建一个加密的存储过程jmsearch。该存储过程的作用是：当执行该存储过程时，将返回北京供应商的所有信息。（10分）(3) 使用系统存储过程sp_helptext查看jsearch, jmsearch的文本信息。（5分）(4) 执行jmsearch存储过程，查看北京供应商的情况。（5分）(5) 删除jmsearch存储过程。（5分）3. 针对Student数据库，创建和执行如下的触发器：（共计40分）(1) 删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。该触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。(5分)(2) 为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是禁止删除S表中的记录。触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作。(5分)(3) 为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创建成功之后，删除S表中的记录删除S表中的记录，验证触发器是否正常工作(SC表中的数据被正常删除)。(5分)(4) 为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容。触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。(5分)(5) 禁用update_s触发器。禁用之后，更新S表中的“sdept”字段的内容，验证触发器是否还继续正常工作。(5分)(6) 删除update_s触发器。(5分)(7) 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当SC表中有记录插入、删除或者更新时，自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，需要计算该成绩。(10分)4. 创建一个works数据库，其中包含员工表empoyee(eID, eName, salary)，假设该表中有1000条员工数据，完成下列要求（总计20分，每题10分）。(1) 为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。例如2015年插入的第一条数据是20050001，所有1000条员工ID分别是20150001-20151000。调用该函数实现自动插入1000条数据。（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）(2) 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：工资增长规则如下：l 工资在3000元以下，每月涨300元；l 工资在3000-4000元之间，每月涨200元；l 工资大于或者等于4000元，每月涨50元；7. 实验六:ODBC/JDBC数据库编程7.1. 实验目的1. 熟练掌握ODBC数据源的配置方法。2. 掌握ODBC编程技术。3. 掌握JDBC编程技术。（选做实验）7.2. 实验内容1. ODBC配置以及程序调试：（50分）(1) 配置一个ODBC数据源，要求数据源名称：student，其中包含s(学生信息)表。(2) 阅读并运行实验给出的例子程序，理解ODBC编程，要求简单写出自己对这段程序的理解或者流程图，并且请给出程序运行结果示例图。2. 参考给出的ODBC编程示例，使用ODBC技术，编写一个简单的程序，包含对数据库SPJ的连接，查询，插入，修改和删除。（50分）3. 用JDBC实现对数据库student的连接，查询，插入，修改和删除。(选做实验)8. 实验七:事务与并发控制8.1. 实验目的1. 掌握事务机制，学会创建事务。2. 理解事务并发操作所可能导致的数据不一致性问题，用实验展现四种数据不一致性问题：丢失修改、读脏数据、不可重复读以及幻读现象。3. 理解锁机制，学会采用锁与事务隔离级别解决数据不一致的问题。4. 了解数据库的事务日志。8.2. 实验内容 假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号cardid即为学生的学号： icbc_card(studcardid,icbcid,balance) /校园卡ID，工行卡ID，银行卡余额 campus_card(studcardid,balance) /校园卡ID，校园卡余额针对以上数据库按照要求完成下列实验：1. 编写一个事务处理(begin tran)实现如下的操作：某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。（15分）2. 针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可重复读和幻读(删除和插入)。（40分，每种数据不一致10分）3. 利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读(或者幻读)的数据不一致问题。（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）4. 构造一个出现死锁的情形。（10分）5. 利用dbcc log命令查看student数据库的事务日志。（5分） 附数据创建的代码： use studentcreate table campus_card( studcardid Char(8) , balance Decimal(10,2)create table icbc_card( studcardid Char(8), icbcid Char(10), balance Decimal(10,2) ) 示例数据如下：insert into campus_card values(20150031, 30)insert into campus_card values(20150032, 50)insert into campus_card values(20150033, 70)insert into icbc_card values(20150031, 2015003101, 1000)insert into icbc_card values(20150032, 2015003201, 1000)insert into icbc_card values(20150033, 2015003301, 1000) 9. 实验八：数据库综合实验9.1. 实验目的通过完成从用户需求分析、概念结构设计、逻辑结构设计等一系列的数据库设计到编程、调试和应用等全过程，进一步理解和掌握教材中的相关内容。9.2. 实验要求1. 实验步骤(1) 先对选定的实验做简单的需求分析。(2) 做出数据流图和数据字典。(3) 在数据流图和字典的基础上做出E-R图(概念结构设计)。(4) 在E-R图基础上进行关系模式设计（至少满足3NF）（逻辑结构设计）。(5) 根据需求分析中的各种数据请求得出各种视图以及各种约束、规则、触发器脚本描述。(6) 根据第(5)的内容在SQL-SERVER中设计中实例的数据库。(7) 用自己熟悉的一门语言进行设计的功能的实现，必须包括对数据的查询、增加、修改和删除。(8) 整理前面几步的文档和程序源码，可执行程序。(9) 实验报告的最后写出本次实验完成中遇到的问题及解决方法。2. 提交成果物：(1) 完整的实验报告（报告中包括上述要求中的1）-5)和9），注意不需要将客户端程序源代码贴入实验报告中，但可以将触发器、存储过程等数据库端的代码加以说明写入实验报告中，另外请将开发环境（包括语言、平台、所使用数据库的名称以及版本号）写入实验报告中。(2) 备份好的数据库备份文件，以及相关的SQL语句。(3) 源代码以及可执行程序。(4) 配置运行说明文档：说明程序该如何配置使之能正常运行。(5) 其他：安装包、帮助文件、演示视频等（可选）。3. 评分标准：需求分析：5分（数据流图与数据字典）概念设计：10分（E-R图）逻辑设计：10分（关系模式，包括函数依赖，码等优化、表设计）物理设计：5分（表中的索引设计等）程序源代码：60分其他：10分（文档完整性，环境配置等）9.3. 实验内容从下列内容中选取一个或者自己选题进行实验，并书写实验报告。以下每个题目列出的功能仅供参考，实验中可以自行对功能进行设计。5.3.1. 题目一：零件交易管理系统零件交易管理系统主要提供顾客和供应商之间完成零件交易的功能，其中包括供应商信息、顾客信息以及零件信息。供应商信息包括供应商号、供应商名、地址、电话、简介；顾客信息包括顾客号、顾客名、地址、电话；零件信息包括零件号、零件名、重量、颜色、简介等。此系统可以让供应商增加、删除和修改所提供的零件产品，还可以让顾客增加、删除和修改所需求的零件。交易员可以利用顾客提出的需求信息和供应商提出的供应信息来提出交易的建议，由供应商和顾客进行确认后即完成交易。5.3.2. 题目二：图书管理系统一个简单的图书