2023版《数据库原理实验》指导书

2024版《数据库原理实验》指导书《数据库原理试验》指导书

计算机与通信工程学院

2024年7月

名目

前言(1)

试验一数据库查询(2)

试验二数据库定义(5)

试验三数据更新(7)

试验四视图和索引(8)

试验五数据平安性(9)

试验六T-SQL编程(12)

试验七存储过程编程(14)

试验八触发器编程(15)

试验九事务与死锁(16)

试验十JDBC编程(18)

课程设计(19)

前言

（一）本课程的教学目的和要求：

SQL语言是关系数据库的标准语言，是本课程的一个重点。通过上机试验，可以使同学加深对课堂讲授内容的理解，循序渐进地把握SQL语言的使用；同时，使同学了解和熟识SQLSERVER2000开发的环境，逐步把握编辑、调试、运行程序的方法，初步积累编程阅历。

（二）试验步骤：

1．预备好上机所需的程序。手编程序应书写整齐，并经人工检查无误后才能上机，以提高上机效率。对程序中自己有疑问的地方，应作出记号，以便在上机时赐予留意。

2．上机输入和调试自己所编的程序。一人一组，独立上机，上机过程中消失的问题，除了是系统的问题以外，一般应自己独立处理。

3．上机结束后，整理出试验报告，试验报告应包括以下内容：

1)题目；

2)程序清单（加解释）；

3)运行结果；

4)对运行状况所作的分析

5)本次调试程序取得的阅历。

（三）教学重点与难点：

1.数据库及表的建立与使用

2.简洁查询

3.连接查询

4.嵌套查询

5.数据库更新

6.视图的定义和查询

7.平安性掌握

8.事务的建立和使用

9.存储过程的使用

10.触发器的使用

11.数据转换

试验一数据库查询

课程名称：数据库原理试验试验类型：验证型

试验名称数据库查询学时4学时

试验目的：

使同学把握SQLServerQueryAnalyzer的使用方法，加深对SQL和T-SQL语言的查询语句的理解。娴熟把握表的基本查询，连接查询和嵌套查询，以及把握数据排序和数据分组的操作方法。

试验原理：

SELECT]；

试验方法：

将查询需求用T-SQL语言表示；在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句；设置QueryAnalyzer的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；假如结果不正确，要进行修改，直到正确为止。

试验内容：

1.分别用带DISTINCT和不带DISTINCT关键字的SELELCT在student中进行查询.

2.将teacher表中各老师的姓名、教工号及工资按95％发放的信息，并将工资按95％发放

后的列名改为‘预发工资’

3.查询course表中全部学分大于2并且序号成果不及格的同学的信息.

4.查询学分在4～8之间的同学信息.（用between..and和复合条件分别实现）

5.从student_course表中查询出同学为“2”，“4”的课程号、同学号以及学分，并按学分

降序排列（用in实现）

6.从teacher表中分别检索出姓王的老师的资料，或者姓名的第2个字是远或辉的老师的

资料

7.查询每个同学及其选修课状况

8.以student表为主体列出每个同学的基本状况及其选课状况，假如同学没有选课，只输

出其基本状况

9.查询选修dep04_s001号课程且成果在80分以上的同学信息。（分别用连接，in和exists

实现）

10.查询全部上计算机基础课程的同学的学号、选修课程号以及分数（分别用连接，in和exists

实现）

11.查询选修了课程名为“数据库基础”的同学学号和姓名（分别用连接，in和exists实现）

12.查询全部计算机系同学的学号、选修课程号以及分数（分别用连接，in和exists实现）13．查询每个dep_04系同学的总成果、平均成果，仅显示平均成果及格的同学的记录。14．查询“数据库开发技术”的平均成果

15．按职称查询老师的平均工资，并按总工资降序排列

附录1：

教务管理数据库jwgl结构

student表结构

Course表

SC表

Class_course表

试验二数据库定义

课程名称：数据库原理试验类型：验证型

试验名称数据库定义学时2学时

试验目的：

（1）使同学把握E_R图的设计方法，并能将E_R图转换成关系模式；

（2）熟识SQLDDL和DML语言，能够完成基本的表结构的定义、修改；

（3）把握数据库完整性约束定义

试验原理：

1.将E_R图转换为关系的方法：

?一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的

码

?一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并

?一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并

?一个m:n联系转换为一个关系模式

?三个或三个以上实体间的一个多元联系可以转换为一个关系模式

2.定义库：

CreateDATABASEADDALTERCOLUMNDROPCOLUMN

试验方法：

将查询需求用T-SQL语言表示；在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句；设置QueryAnalyzer的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；假如结果不正确，要进行修改，直到正确为止。

试验内容：

假设某公司的业务规章如下：

（1）公司下设几个部门，如技术部、财务部、市场部等。

（2）每个部门担当多个工程项目，每个工程项目属于一个部门。

（3）每个部门有多名职工，每一名职工只能属于一个部门。

（4）一个部门可能参加多个工程项目，且每个工程项目有多名职工参加施工，一个职工可以参加多个项目。依据职工在工程项目中完成的状况发放酬金。

（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称、办公电话和办公地点等属性；职工有职工号、姓名、性别属性，诞生日期；

问题：

1．依据上述规章设计E-R模型;

2．将E-R模型转换成关系数据模型；

3．利用SQL语句定义上述关系，要求：

（1）为每个关系定义主码和外码；

（2）职工姓名、部门名称不能取空值，并且部门名称是唯一的；

（3）职工的性别只能取“男”和“女”，默认值为“男”；

（4）部门号具有类似“B01”的形式，即每个部门号的首字符为“B”，其余两个字符为数字。

(5)酬金不低于2000元。

4．修改部门号的约束，改为“B001”的形式，每个部门号的首字符为“B”，其余三个字符为数字。

5．在原有关系的基础上，增加一个新列，用于记录每名职工参与项目的起始时间。

留意：请仔细选择属性的数据类型和精度。

试验三数据更新

课程名称：数据库原理试验类型：验证型

试验名称数据更新学时2学时

一．试验目的和要求：

把握用SQL语句实现数据的插入、修改和删除。

二．试验内容：

1、将一个新同学记录（student_id：g9940303、student_name：张红、sex：女、birth：

1985-02-08、class_id：g99402,department_id:Dep_03,home_addr:沈阳皇浦区34号）插入到student表中

2、对每一个班，求同学的平均年龄，并把结果存入数据库。（提示：先建立新表“age_avg”，

其中一列存放班级，另一列存放相应的同学平均年龄，然后将所需数据存入age_avg）

3、将学号为g9940201的同学的姓名改为张丽，年龄改为18

4、student表中的全部系别(department_id)均改为“dep_03”

5、将班级号(class_id)为g99402的全体同学的成果加30分

6、从同学名单中删除全部课程号(course_id)为dep04_b001的记录

试验报告：

1.试验各项内容实现的过程。

2.试验结果分析。

试验四视图和索引

课程名称：数据库原理试验类型：验证型

试验名称视图和索引学时2学时

试验目的：

(1)把握视图的定义、查询和更新；

(2)把握索引的定义；

(3)把握利用索引优化查询效率；

试验原理：

1．视图

?定义视图

CREATEVIEW]…）；

?删除索引

DROPINDEX

试验方法：

将查询需求用T-SQL语言表示；在SQLServerQueryAnalyzer的输入区中输入T-SQL查询语句；设置QueryAnalyzer的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；假如结果不正确，要进行修改，直到正确为止。

试验内容：

在试验1中用到的教务管理数据库jwgl中完成如下问题：

(1)创建一个名为stud_query_viewl的水平视图，从数据库JWGL的student表中查询

出性别为“男”的全部同学的资料;

(2)创建一个名为stulquery_view2的投影视图，从数据库JWGL的student表中查询出

性别为“男”的全部同学的学号，姓名，性别，班级，家庭住址，入学时间，诞生年月资料;

(3)创建一个名为“stud_query__view3”的视图，它是由表course、book及class_course

创建的一个显示“g99402”班所开课程的课程名、所用教材的教材名、出版社及的视图;

(4)创建一个从视图studquery_viewl中查询出班级名为g99402的全部同学的视图；

(5)从视图stud_query_view2中查询出班级为“g99402”、姓名为“张华”的资料;

(6)向视图stud_query_View2中插入一行数据。学号：g9940209，姓名：赵青，班级：

g99402，性别：男，家庭住址：南京中山北路10号，年龄17岁,，系编号：dep_03;

(7)修改视图stud_query_view2中的数据。将stud_query_view2中姓名为“张力”的家

庭地址改为“扬州市南京路8号”；

(8)从视图中stud_query_view2将姓名为“张力”的同学删除；

(9)为表teacher创建一个索引名为teacher_index的惟一性非聚簇索引，索引关键字为

teacher_id，升序；

(10)为表student_course上创建一个名为student_course_index的非聚簇复合索引，索引

关键字为student_id,course_id，升序

试验报告：

1.试验各项内容实现的过程。

2.试验结果分析。

试验五数据平安性

课程名称：数据库原理试验类型：验证型

试验名称数据平安性学时2学时

试验目的：

本试验的目的是通过试验使同学加深对数据平安性的理解，并把握SQLServer中有关用户、角色及操作权限的管理方法。

试验原理：

?添加登录用户

sp_addlogin

?为数据库添加用户

sp_grantdbaccess

?授权

GRANT…

TO…

;

?回收权限

REVOKE…

FROM…

;

?触发器

触发器是一种特别类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT或DELETE。触发器可以查询其它表，而且可以包含简单的SQL语句。它们主要用于强制简单的业务规章或要求。触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。

试验内容：

(1)在SQLServerEnterpriserManager中，设置SQLServer的平安认证模式，

实现对SQLServer的用户和角色管理，设置和管理数据操作权限。

(2)在QueryAnalyzer中使用T-SQL设计管理用户权限。

详细内容包括：

１．基本操作试验

(1)在SQLServerEnterpriserManager中为所属的SQL服务器设置WindowsNT平安

认证模式，然后使用两种不同的身份验证方式登录QueryAnalyzer。

(2)在SQLServerQueryAnalyzer中建立登录用户u1,u2,u3,u4，并使其成为jwgl

数据库的用户。

(3)把对student表的查询权限授予用户u1，并允许其将权限转授给其它用户。验证

u1是否获得相应权限。

(4)用户u1将查询student表的权限授予用户u2。验证u2是否获得相应权限。

(5)将对course表的查询权限授予全部用户。

(6)建立用户u5，并验证u4和u5是否获得查询course表的权限。

(7)回收u4查询course表的权限，并验证。

试验报告：

1.试验各项内容实现的过程。

2.试验结果分析。

试验六T-SQL编程

课程名称：数据库原理试验试验类型：综合型

试验名称T_SQL编程学时4

试验目的和要求：

1.把握变量的定义及赋值、数据显示及IF、WHILE、CASE规律掌握语句。

2.把握简洁子查询、IN子查询及EXISTS子查询的用法，并能应用T-SQL进行综合查询。

试验内容：创建学员成果数据库stu。

1、学员信息表如下：

试编写SQL语句查找李文才的左右同桌。

2、学员成果表如下：

统计并显示本班笔试平均分，假如平均分在70以上，显示“成果优秀“，并显示前三名学员的考试信息；假如在70以下，显示“本班成果较差“，并显示后三名学员的考试信息。

3、本次考试成果较差，假定要提分，确保每人笔试都通过。提分规章很简洁，先每人都加2分，看是否都通过，假如没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到全部人都通过为止。

4、采纳美国的ABCDE五级打分制来显示笔试成果。

A级:90分以上

B级：80－89分

C级:70－79分

D级：60－69分

E级：60分以下

5、统计本次考试的缺考状况

6、比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。加分后重新统计通过状况

7、统计通过率

试验七存储过程编程

课程名称：数据库原理试验试验类型：综合型

试验名称存储过程编程学时4

试验目的

把握存储过程的创建及调用。

试验内容

有学员成果数据库stu，其中学员信息表stuinfo和同学成果表stumarks如下：

1、请创建存储过程，查看本次考试平均分以及未通过考试的学员名单

2、修改上题：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。（提示：使用带输入参数的存储过程）

3、修改上题：返回未通过考试的学员人数。（提示：使用带输出参数的存储过程）

试验八触发器编程

课程名称：数据库原理试验试验类型：综合型

试验名称触发器编程学时4

试验目的

理解触发器的工作原理，把握如何使用inserted表和deleted表及如何创建：INSERT、UPDATE、DELETE触发器。

试验内容

建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。

1、当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。

2、当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。

3、跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。

试验报告

1、试验各项内容实现的过程。

2、试验结果分析。

试验九事务与死锁

课程名称：数据库原理试验试验类型：验证型

学时4

试验目的

1．了解事务的ACID属性，把握事务和批处理的工作原理。

2．了解死锁的类型和其兼容性。

3．了解死锁发生的状况。

4．了解可锁定的资源。

5．了解死锁的一般处理过程。

试验内容：

1.银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。

(1)试用事务解决银行转帐问题。

参考代码如下：

BEGINTRANSACTION

/*--定义变量，用于累计事务执行过程中的错误--*/

DECLARE@errorSumINT

SET@errorSum=0--初始化为0，即无错误

/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/

UPDATEbankSETcurrentMoney=currentMoney-1000

WHEREcustomerName='张三'

SET@errorSum=@errorSum+@@error

UPDATEbankSETcurrentMoney=currentMoney+1000

WHEREcustomerName='李四'

SET@errorSum=@errorSum+@@error--累计是否有错误

IF@errorSum0--假如有错误

BEGIN

print'交易失败，回滚事务'

ROLLBACKTRANSACTION

END

ELSE

BEGIN

print'交易胜利，提交事务，写入硬盘，永久的保存'

MITTRANSACTION

END

GO

print'查看转帐事务后的余额'

SELECT*FROMbank

GO

(2)试演示：分别转帐1000和800时的转帐状况

2．试验内容

1）锁与并行查询执行相关的资源。

2）如何查看进程信息。

3）如何查看锁/对象信息。

2）如何处理死锁。

试验步骤

1）构造死锁，让锁现象发生。

先构造一个可能造成锁表的代码如下：

BEGINTRANSACTION

INSERTpublishers(pub_id，pub_name，city，state)

VALUES(“1111”，“JoeandMary’sBooks”，“NotrthernPlains”，“IA”)

IF@@ERROR=0

BEGIN

PRINT‘Publisherinsertfailedrollingbacktransaction’

ROLLBACKTRANSACTION

END

下面的代码对同一个表执行简洁的查询操作，详细代码如下：

select*fromtitles;

2）处理死锁的一般过程或步骤。

Sp_who:供应关于当前MicrosoftSQLServer用户和进程信息。如：列出全部当前进程。

在SQL查询分析器的命令窗口中输入

sp_who

sp_lock:报告有关锁的信息

在SQL查询分析器的命令窗口中输入

sp_lock@spid1=从sp_who中查出的进程标识号

在企业管理器中打开“管理”文件夹下的当前活动，查看进程信息。

试验报告

1、试验各项内容实现的过程。

2、试验结果分析。

试验十JDBC编程

课程名称：数据库原理试验试验类型：综合型

学时4

试验目的：

熟识使用JDBC来进行数据库应用程序的设计，熟识通过JDBC接口编程访问数据库并对数据库进行操作。

试验要求：

配置环境变量。使用Java语言编程实现对数据库的访问，全部的SQL操作均在自己建立的新库里进行，数据库建议选用同学课程数据库，读者可以选择进行创