《数据库原理实验》指导书.docx

1、数据库原理实验指导书数据库原理实验指导书计算机与通信工程学院2010年7月实验名称学时实验周1实验1 数据库查询492实验2数据库的定义2103实验3 数据更新实验 28实验8 触发器4119实验9 事务处理4124实验4 索引和视图2135实验5 数据安全性实验26实验6 T-SQL编程4147实验7存储过程41510实验10 JDBC编程416前言（一）本课程的教学目的和要求：SQL语言是关系数据库的标准语言，是本课程的一个重点。通过上机实验，可以使学生加深对课堂讲授内容的理解，循序渐进地掌握SQL语言的使用；同时，使学生了解和熟悉SQL SERVER 2000开发的环境，逐步掌握编辑、调

2、试、运行程序的方法，初步积累编程经验。（二）实验步骤：1准备好上机所需的程序。手编程序应书写整齐，并经人工检查无误后才能上机，以提高上机效率。对程序中自己有疑问的地方，应作出记号，以便在上机时给予注意。2上机输入和调试自己所编的程序。一人一组，独立上机，上机过程中出现的问题，除了是系统的问题以外，一般应自己独立处理。3上机结束后，整理出实验报告，实验报告应包括以下内容：1) 题目；2) 程序清单（加注释）；3) 运行结果；4) 对运行情况所作的分析5) 本次调试程序取得的经验。（三）教学重点与难点：1. 数据库及表的建立与使用2. 简单查询3. 连接查询4. 嵌套查询5. 数据库更新6. 视图

3、的定义和查询7. 安全性控制8. 事务的建立和使用9. 存储过程的使用10. 触发器的使用11. 数据转换实验一 数据库查询课程名称：数据库原理实验实验类型：验证型实验名称数据库查询学时4学时实验目的：使学生掌握SQL Server Query Analyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。熟练掌握表的基本查询，连接查询和嵌套查询，以及掌握数据排序和数据分组的操作方法。实验原理：SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING order by ASC|DESC；实验方法：将查询需求用T-SQL语言表示；在SQL

4、Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验内容：1. 查询所有的Germany国家的员工.2. 查询出生地为London的员工3. 查询所有7号顾客签订的订单.4. 查询所有 FirstName为 Robert的顾客签订的订单.5. 查看所有订单6. 查询所有没有按时发货的订单7. 查找哪些顾客的订单在规定时间前没有发货。给出顾客名，电话8

5、. 查找所有已经订购了货物但尚未发货的顾客信息（分别用连接，in和exists实现）9. 查看每个员工签订的订单的总金额，按照金额由小到大排序（分别用连接，in和exists实现）10. 假设运费要顾客自己承担，计算每个顾客为自己的订单付出的金额11. 查询所有与1号和9号都签过订单的顾客12. 查询签订订单金额超过100000美元的员工姓名13. 查询员工FirstName为Robert的所有同事14. 查询已经现已经退休的职工，假设退休年龄为60，给出他的姓名，工作年限，住址，电话。附录1：可能用到的函数获取系统日期：getdate()计算两个日期的时间差：DateDiff(Datepar

6、t,startDate,enddate). datepart是规定了应在日期的哪一部分计算差额的参数,取值有：日期部分缩写yearyy, yyyyquarterqq, qMonthmm, mdayofyeardy, yDaydd, dWeekwk, wwHourhhminutemi, nsecondss, smillisecondms附录2：数据库结构Relation SchemasEmployees()Column NameISNullData TypeMax LengthDescriptionKey1EmployeeIDNo intYes2LastNameNo varchar203Firs

7、tNameNo varchar104TitleYESvarchar305TitleOfCourtesyYESvarchar256BirthDateYESdatetime7HireDateYESdatetime8AddressYESvarchar609CityYESvarchar1510RegionYESvarchar1511PostalCodeYESvarchar1012CountryYESvarchar1513HomePhoneYESvarchar2414ExtensionYESvarchar415PhotoYESimage214748364716NotesYESntext107374182

8、317ReportsToYESintOrders()Column NameISNullData TypeMax LengthDescriptionKey1ProductIDNo intYes2CustomerIDNo nchar53EmployeeIDNo int4OrderDateNo datetime5RequiredDateYESdatetime6ShippedDateYESdatetime8QuantityNo smallint9DiscountNo real折扣掉的百分比10FreightYESmoney运费11ShipNameYESvarchar4012ShipAddressYES

9、varchar6013ShipCityYESvarchar1514ShipRegionYESvarchar1515ShipPostalCodeYESvarchar1016ShipCountryYESvarchar15Customers()Column NameISNullData TypeMax LengthDescriptionKey1CustomerIDNo char10Yes2CompanyNameNo varchar403ContactNameYESvarchar304ContactTitleYESvarchar305AddressYESvarchar606CityYESvarchar

10、157RegionYESvarchar158PostalCodeYESvarchar109CountryYESvarchar1510PhoneYESvarchar2411FaxYESvarchar24Products()Column NameISNullData TypeMax LengthDescriptionKey1ProductIDNo intYes2ProductNameNo varchar405QuantityPerUnitYESvarchar206UnitPriceYESmoney单价7UnitsInStockYESsmallint存储量实验二 数据库定义课程名称：数据库原理实验类

11、型：综合型实验名称数据库定义学时2学时实验目的：（1） 使学生掌握E_R图的设计方法，并能将E_R图转换成关系模式；（2） 熟悉SQL DDL和DML语言，能够完成基本的表结构的定义、修改；（3） 掌握数据库完整性约束定义实验原理：1. 将E_R图转换为关系的方法： 一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码 一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并 一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并 一个m:n联系转换为一个关系模式 三个或三个以上实体间的一个多元联系可以转换为一个关系模式2. 定义库：

12、Create DATABASE 3. 表 定义表CREATE TABLE （列级完整性约束，列级完整性约束，）； 添加列ALTER TABLE ADD 列级完整性约束 修改列ALTER TABLE ALTER COLUMN 列级完整性约束 删除列ALTER TABLE DROP COLUMN 实验方法：将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；

13、如果结果不正确，要进行修改，直到正确为止。实验内容：假设某公司的业务规则如下：（1）公司下设几个部门，如技术部、财务部、市场部等。（2）每个部门承担多个工程项目，每个工程项目属于一个部门。（3）每个部门有多名职工，每一名职工只能属于一个部门。（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工，一个职工可以参与多个项目。根据职工在工程项目中完成的情况发放酬金。（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称、办公电话和办公地点等属性；职工有职工号、姓名、性别属性，出生日期；问题：1根据上述规则设计E-R模型;2将E-R模型转换成关系数据模型；3利用SQL语句定义上

14、述关系，要求： （1）为每个关系定义主码和外码； （2）职工姓名、部门名称不能取空值，并且部门名称是唯一的； （3）职工的性别只能取“男”和“女”，默认值为“男”； （4）部门号具有类似“B01”的形式，即每个部门号的首字符为“B”，其余两个字符为数字。 (5)酬金不低于2000元。4修改部门号的约束，改为“B001”的形式，每个部门号的首字符为“B”，其余三个字符为数字。5在原有关系的基础上，增加一个新列，用于记录每名职工参加项目的起始时间。注意：请认真选择属性的数据类型和精度。实验三 数据更新课程名称：数据库原理实验类型：综合型实验名称数据更新学时2学时一实验目的和要求：掌握用SQL语句实

15、现数据的插入、修改和删除。二实验内容：建立一个商店的数据库store，记录顾客及其购物情况，由下面三个表组成： 商品（商品号，商品名，单价，商品类别，供应商）； 顾客（顾客号，姓名，住址）； 购买（顾客号，商品号，购买数量）； 三试用SQL语言完成下列功能： 1建表，在定义中要求声明： （1）每个表的主外码； （2）顾客的姓名和商品名不能为空值； （3）单价必须大于0；（4）购买数量必须在0到20之间； 2往表中插入数据： 部门（M01，佳洁士，8.00，牙膏，宝洁； M02，高露洁，6.50，牙膏，高露洁； M03，洁诺，5.00，牙膏，联合利华； M04，舒肤佳，3.00，香皂，宝洁； M

16、05，夏士莲，5.00，香皂，联合利华； M06，雕牌，2.50，洗衣粉，纳爱斯 M07，中华，3.50，牙膏，联合利华； M08，汰渍，3.00，洗衣粉，宝洁； M09，碧浪，4.00，洗衣粉，宝洁；） 顾客（C01，Dennis，海淀； C02，John，朝阳； C03，Tom，东城； C04，Jenny，东城； C05，Rick，西城；） 购买 (C01，M01，3；C01，M05，2； C01，M08，2；C02，M02，5； C02，M06，4；C03，M01，1； C03，M05，1；C03，M06，3； C03，M08，1；C04，M03，7； C04，M04，3；C05，M06

17、，2； C05，M07，8；) 注：商品表数据（9）,顾客表数据（5）, 购买表数据（5） 2.完成下列查询： （1）求购买了供应商宝洁产品的所有顾客； （2）求购买的商品包括了顾客Dennis所购买商品的顾客（姓名）； （3）求牙膏卖出数量最多的供应商。 3.将所有的牙膏商品单价增加10%。 4.删除从未被购买的商品记录。实验报告：1. 实验各项内容实现的过程。2. 实验结果分析。实验四 视图和索引课程名称：数据库原理实验类型：综合型实验名称视图和索引学时2学时实验目的：(1) 掌握视图的定义、查询和更新；(2) 掌握索引的定义；(3) 掌握利用索引优化查询效率；实验原理：1视图 定义视图C

18、REATE VIEW ( ，) AS WITH CHECK OPTION； 删除视图DROP VIEW 2索引（注意：索引不能修改） 定义索引CREATE UNIQUECLUSTER INDEX ON （，）； 删除索引DROP INDEX 实验方法：将查询需求用T-SQL语言表示；在SQL Server Query Analyzer的输入区中输入T-SQL查询语句；设置 Query Analyzer的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验内容：

19、1、在实验1中用到的Commerce数据库中完成如下问题：(1) 为表Employees在Title列上添加一个升序索引;(2) 为表orders添加索引v1，索引键OrderDate 升序,ShippedDate 降序;(3) 设计一个视图View1，用于查询每个员工签订的订单总数;(4) 设计视图View2，查询ShipCountry为Canada，且订购货物数量超过30件的订单信息(5) 在View2的基础上设计视图View3，查询ShipCountry为Canada，且订购货物数量超过30件的顾客信息;(6) 删除视图View2，测试View3是否仍有效;2、测试索引实验五 数据安全性

20、课程名称：数据库原理实验类型：综合型实验名称数据安全性学时2学时实验目的：本实验的目的是通过实验使学生加深对数据安全性的理解，并掌握SQL Server中有关用户、角色及操作权限的管理方法。实验原理： 添加登录用户sp_addlogin , 为数据库添加用户sp_grantdbaccess , 授权GRANT, ON TO ,WITH GRANT OPTION; 回收权限REVOKE, ON FROM ,CASCADE; 触发器触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。触发器可以查询

21、其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。实验内容：(1) 在SQL Server Enterpriser Manager中，设置SQL Server的安全认证模式，实现对SQL Server的用户和角色管理，设置和管理数据操作权限。(2) 在Query Analyzer中使用T-SQL设计管理用户权限。具体内容包括：基本操作实验(1) 在SQL Server Enterpriser Manager中为所属的SQL服务器设置Windows NT安全认证模式，然后使用两种不

22、同的身份验证方式登录Query Analyzer。(2) 在SQL Server Query Analyzer中建立登录用户u1,u2,u3,u4，并使其成为commerce数据库的用户。(3) 把对products表的查询权限授予用户u1，并允许其将权限转授给其它用户。验证u1是否获得相应权限。(4) 用户u1将查询products表的权限授予用户u2。验证u2是否获得相应权限。(5) 将对orders表的查询权限授予所有用户。(6) 建立用户u5，并验证u4和u5是否获得查询orders表的权限。(7) 回收u4查询orders表的权限，并验证。实验六 T-SQL编程课程名称：数据库原理实

23、验实验类型：设计型实验名称T_SQL编程学时4实验目的和要求：1.掌握变量的定义及赋值、数据显示及IF、WHILE、CASE逻辑控制语句。2.掌握简单子查询、IN子查询及EXISTS子查询的用法，并能应用T-SQL进行综合查询。实验内容：创建学员成绩数据库stu。1、学员信息表如下：试编写SQL语句查找李文才的左右同桌。 2、学员成绩表如下：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。3、本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都

24、通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。4、采用美国的ABCDE五级打分制来显示笔试成绩。 A级: 90分以上 B级： 8089分 C级: 7079分 D级： 6069分 E级： 60分以下5、统计本次考试的缺考情况 6、比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分 。加分后重新统计通过情况7、统计通过率 实验七 存储过程编程课程名称：数据库原理实验实验类型：设计型实验名称存储过程编程学时4实验目的掌握存储过程的创建及调用。实验内容有学员成绩数据库stu，其中学员信息表stuinfo和学生成绩表stumarks

25、如下： 1、请创建存储过程，查看本次考试平均分以及未通过考试的学员名单2、修改上题：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。（提示：使用带输入参数的存储过程）3、修改上题：返回未通过考试的学员人数。（提示：使用带输出参数的存储过程）4、编写存储过程，显示男生或女生的名单（性别要求通过参数输入）。格式为： 序号学号姓名(提示：用带有游标的存储过程)实验八 触发器编程课程名称：数据库原理实验实验类型：设计型实验名称触发器编程学时4实验目的理解触发器的工作原理，掌握如何使用inserted表和deleted表及如何创建：INSERT、UPDATE、DELETE触发器。实验内容建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。 1、当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。 2、当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。3、跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。 实验报告1、实验各项内容实现的过程。2、实验结果分析。实验九 事务与死锁课程名称：数据库原理实验实验类型：验证型学时4