数据库实验12学时.docx

数据库实验12学时.docx

《数据库实验12学时.docx》由会员分享，可在线阅读，更多相关《数据库实验12学时.docx（22页珍藏版）》请在冰豆网上搜索。

数据库实验12学时

《数据库原理》实验介绍

一、实验教学目标与基本要求

数据库原理实验是本课程重要的实践教学环节。

实验的目的不仅仅是验证理论知识，更重要的是通过实验加强学生的实验手段与实践技能，掌握使用实际数据库系统的使用方法，加深理论知识的理解，培养学生分析问题、解决问题、应用知识的能力和创新精神，真正培养学生的实践动手能力，全面提高学生的综合素质。

数据库原理实验在客户/服务器环境下的客户终端计算机上完成，学生通过客户端远程连接SQLServer数据库服务器。

每人一台微机，前两个实验是一人一组，第三、四个实验为两人一组。

基本要求是：

每位学生能独立完成实验内容，还能进行简单的错误分析和错误排除。

通过实验让学生掌握数据库SQL语句的应用和加深理论理解，增强实际应用技能。

二、实验项目及教学安排

序号

实验项目名称

基本内容

实验

学时

每组

人数

实验要求

实验

类型

1

使用SQL

熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。

4

1

必修

设计性与综合性

2

数据库的安全性与完整性

了解和使用安全子系统；进行完整性定义和检查。

4

1

必修

综合性

3

事务的并发控制

体会加锁操作、死锁检测与解除。

4

2

必修

验证性与综合性

三、实验成绩考核办法

《数据库原理》课程分一个学期讲授，实验教学亦在授课学期进行，要求完成表中所有实验。

实验成绩满分10分。

实验成绩按下面7条进行考核评定。

（1）做好实验内容的预习，写出预习报告；

（2）了解实验环境及正确使用方法；

（3）按实验要求正确使用操作系统Windows2000及SQLServer数据库软件；

（4）能够综合分析问题，并利用数据库原理的理论完成实验设计；

（5）认真记录实验数据并按数据库原理分析实验结果；

（6）实验数据正确，原理分析准确；

（7）实验过程中，具有严谨的学习态度，认真、踏实、一丝不苟

的科学作风。

四、实验教材及参考书

[1]申德荣,杨晓春.数据库原理实验讲义.2004.

[2]AbrahamSilberschatz,HenryF.Korth,S.Sudarshan，杨冬青等译.《数据库系统概念》（第四版）北京：

机械工业出版社.2003.

[3]李建中、王珊主编.《数据库系统原理》.北京：

电子工业出版社.

2004年1月.

[4]萨师煊,王珊主编.《数据库系统概论》（第三版）.北京：

高等教育出版社.2002.

实验

（一）:

熟练掌握SQL语言

实验目的：

熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。

实验内容：

具体包括如下三部分。

一、熟悉上机环境。

客户/服务器结构，数据库服务器在一台NT服务器上，同学们通过客户机（操作系统为Windows98）上安装的SQLServer客户端程序,使用SQLServer数据库服务器。

具体包括：

1.了解SQLServer环境。

鼠标点击开始，进入“MicrosoftSQLServer企业管理器”，点击SQLServer组下的数据库服务器（服务器名称为NEUC-2015（WindowsNT））,可以看到服务器上的圆形标志变为绿色，说明客户端程序已与服务器连接成功。

点击服务器（NEUC-2015（WindowsNT））下的数据库，可以看到服务器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。

因为，数据库服务器上建有许多数据库,每个数据库都有一些合法的用户。

2.鼠标点击开始，进入“MicrosoftSQLServer查询分析器”，输入用户名和口令，进入SQL查询分析器。

如：

你登录的客户机为27号，那么请以用户名user27，口令为user27登录，登录后缺省连到数据库user27上，user27/user27是数据库user27的创建者，因此用户user27/user27具有在数据库user27上创建表等一切权力。

3.在SQL查询分析器环境下，你就可进行SQL命令语句操作了。

二、在数据库useri上创建学生选课有关关系模式，并插入相应的数据，实现有关查询。

1.描述学生、课程情况的E-R图如下，请将其转化为用关系数据模型描述的关系模式

2.

在数据库中定义这些关系模式

（1）学生

CREATETABLEstudent（

Snochar（9）,

Snamechar（20）,

Sagesmallint,

Sdeptchar（20）,

（2）选修

CREATETABLESC

（SnoCHAR（9）,

CnoCHAR（9）,

GradeSMALLINT,

PRIMARYKEY（Sno,Cno）,

FOREIGNKEY（Sno）REFERENCESstudent（Sno）,

FOREIGNKEY（Cno）REFERENCEScourse（Cno）

）;

（3）课程

CREATETABLEcourse（

Cnochar（9）,

Cnamechar（20）,

Cgradeint，

Ctimeint,

Cunochar（20）,

3.插入相应的数据，试着插入重复的元组，结果如何？

（1）学生

INSERTINTOstudent（Sno,Sname,Sage,Sdept）VALUES（'2001','阿大','22','电子信息'）;

INSERTINTOstudent（Sno,Sname,Sage,Sdept）VALUES（'2008','阿二','23','通信'）;

INSERTINTOstudent（Sno,Sname,Sage,Sdept）VALUES（'201','阿三','24','计算机'）;

INSERTINTOstudent（Sno,Sname,Sage,Sdept）VALUES（'202','阿四','25','软件'）;

INSERTINTOstudent（Sno,Sname,Sage,Sdept）VALUES（'22','阿五','26','电科'）;

（2）选课

INSERTINTOSC（Sno,Cno,Grade）VALUES（'2001','21','22'）;

INSERTINTOSC（Sno,Cno,Grade）VALUES（'2008','22','23'）;

INSERTINTOSC（Sno,Cno,Grade）VALUES（'201','01','24'）;

INSERTINTOSC（Sno,Cno,Grade）VALUES（'202','31','25'）;

INSERTINTOSC（Sno,Cno,Grade）VALUES（'22','41','26'）;

（3）课程

INSERTINTOcourse（Cno,Cname,Cgrade,Ctime,Cuno）VALUES（'21','语文','24','40','21'）;

INSERTINTOcourse（Cno,Cname,Cgrade,Ctime,Cuno）VALUES（'22','数学','32','04','21'）;

INSERTINTOcourse（Cno,Cname,Cgrade,Ctime,Cuno）VALUES（'01','英语','25','20','01'）;

INSERTINTOcourse（Cno,Cname,Cgrade,Ctime,Cuno）VALUES（'31','生物','72','30','31'）;

INSERTINTOcourse（Cno,Cname,Cgrade,Ctime,Cuno）VALUES（'41','物理','78','45','41'）;

INSERTINTOcourse（Cno,Cname,Cgrade,Ctime,Cuno）VALUES（'66','化学','79','50','66'）;

4.在已建立的关系模式之上（已插入一些数据）建立主键约束，参照约束和用户定义的约束（要求学生年龄不小于14岁，不大于35岁），如果约束不能建立，请分析原因，修改后建立上述约束。

ALTERTABLEstudentaddcheck（Sage>14andSage<35）

5.如果约束建立后，请试着插入重复元组，结果如何？

三、用SQL完成下列查询

1.求选修了课程的学生的学号，要求：

（1）不用distinct消除重复元组，写出其SQL语句

（2）用distinct消除重复元组，写出其SQL语句

（3）如果该查询写成：

selectStudents.SnofromStudents,SCwhereStudents.Sno=SC.Sno

请问该查询结果和上面哪个结果是相同的？

2.求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名

selectcourse.Cno,Cname,Ctime,Cgradefromcourse,SCwherecourse.Cno=SC.Cno

3.求计算机系和数学系的学生学号、姓名和年龄

SelectSdept,Sno,Sname,SagefromStudentwhereSdept='数学'

SelectSdept,Sno,Sname,SagefromStudentwhereSdept='计算机'

4.求不是数学系、计算机系的学生的情况，要求select后写*代替列出所有的列名

Select*fromStudentwherestudent.SdeptNOTIN（'数学','计算机'）

5.求全体学生的信息，要求按年龄升序排列

Select*fromStudentorderbystudent.SageASC

6.求计算机系年龄在18~20岁之间的学生姓名和年龄

Selectstudent.Sname,SagefromStudentwherestudent.Sdept='计算机'orstudent.Sage>18andSage<20

7.求姓名时以D开头的数学系学生

Select*fromStudentwherestudent.Sdept='数学'andstudent.Snamelike'D%'

8.求选修了C2课程的学生的学号、姓名、成绩

Selectstudent.Sno,Sname,SC.GradefromStudent,course,SCwherestudent.Sno=SC.Snoandcourse.Cno=SC.CnoandSC.Cno='C2'

9.求学号为95开头的学生的学号和所选修的课程的学分

Selectstudent.Sno,SC.GradefromStudent,SCwherestudent.Sno=SC.Snoandstudent.Snolike'95%'

10.求选修C1课程且成绩为80分以上的学生的学号、姓名和成绩

Selectstudent.Sno,Sname,SC.GradefromStudent,course,SCwherestudent.Sno=SC.SnoandSC.Sno=course.CnoandSC.Cno='5'andSC.Grade>'80'

11.求计算机系每个学生的姓名，选修的课程名和成绩

Selectstudent.Sname,course.Cname,SC.GradefromStudent,course,SCwherestudent.Sno=SC.SnoandSC.Sno=course.Cnoandstudent.Sdept='计算机'

12.求每个学生的学号及平均成绩

selectSno,AVG（Grade）fromSCGROUPbySno;

13.求男学生每一年龄组中超过50人的年龄组及人数

selectSage,count（*）NUMfromstudentgroupbySagehavingcount（*）>1;

14.求每一门课程的间接先行课号

selectCno,Cunofromcourse

15.求选修了全部课程的学生的学号

selectSnofromstudentwherenotexists（select*fromcoursewherenotexists（select*fromSCwhereSno=student.SnoANDCno=course.Cno））;

实验

（二）:

数据库的安全性和完整性

实验目的：

了解和使用安全子系统；进行完整性定义和检查。

实验内容：

具体包括如下三部分。

一熟练掌握SQL（续）

1.修改表Students,在其中加入一个字段性别sexchar

（1）

2.创建索引：

在Students表的Sno上创建唯一聚簇索引,在SC表的（Sno,Cno）上创建唯一索引.查询Students表的内容,记录的顺序有变化吗?

查询SC表的内容,记录的顺序有变化吗?

3.创建视图

▪创建计算机系学生视图CS_Students，其中包括学号、年龄和性别。

带withcheckopition子句

不带withcheckopition子句

时各插入一计算机系学生记录，通过视图查询插入的记录，结果如何？

通过表Students查询插入的记录，查看所在系字段上的值？

▪创建一视图，表示学生的平均成绩，其中包括Sno,avgGrade。

通过该视图插入一学生姓名和平均成绩，结果如何？

通过该视图查找平均成绩在90分以上的学生的学号和平均成绩，结果又如何？

二了解和使用安全子系统

1、将sqlserver的一合法帐户（user60）加入到你的数据库当中来，并给其授权，使之：

具有创建表的权限

具有对Students表查询的权限

具有对Studetns表的年龄字段修改的权限

2、用user60/user60进入系统，完成权限验证：

创建表dept（sd,mn,addr）

创建视图s_view（Sno,Sage）

在Students表上，实现select*fromStudents

在course表上，实现select*fromcourse

updateStudetnssetSage=Sage+1

updateStudentssetSdept=’CS’

3、你是数据库的dbo，你来查看dept表，结果如何？

使用setuser命令使自己成为user60后，查看dept表，结果如何？

三完整性定义和检查

1、创建表Studetns，要求学生的年龄在16和25岁之间，并且所在系的缺省取值为‘CS’

在Studetns表上插入一学生记录（‘9921’，‘cccc’,’MA’,23,’f’）

在Studetns表上插入一学生记录（‘9922’，‘ddd’,’MA’,27,’m’）

在Studetns表上插入一学生记录（‘9923’，‘eeeee’,21,’m’）

在Studetns表上插入一学生记录（‘9922’，‘ddd’,’MA’,23,’m’）

2、创建表course，要求课号作为主键

在course表上插入一课程记录（’c9’,40,’cname1’,1）

在course表上插入一课程记录（’c9’,40,’cname2’,1）

在course表上插入一课程记录（null,25,’canme3’,2）

结果如何？

3、修改表Studetns，指定学号为主键，然后

插入一记录（’9908’,’shang’,’CI’,20）

插入一记录（’9908’,’shang’,’CI’,20）

插入一记录（’’,’liang’,’CS’,18）

插入一记录（’’,’an’,’CS’,19）

结果如何？

4、创建sc表，要求表sc中的学号参照表Studetns中的学号，sc中的课号参照course中的课号。

参照约束创建完成后，向这三个表插入数据。

先在sc表插入一个选课记录，看看有什么情况发生？

先删除sc中的某个记录，看看有什么情况发生？

将Studetns表中的学号9906改为9907，看看有什么情况发生？

实验（三）:

事务的并发控制

实验目的：

通过实验了解实际系统中,三级封锁协议的实现方法和使用方法，体会加锁操作、死锁检测与解除。

实验内容：

具体分如下五部分。

说明：

（1）Settransactionisolationlevelreaduncommitted

readcommitted

repeatableread

serializable

分别对应隔离级0,1,2,3。

（2）要求两人一组。

1、隔离级1（缺省）方式下体会两事务的并发执行结果。

具体步骤如下（以user1和user2为例）:

●user1首先将user2接纳为自己数据库的用户,并授权user2可以查询学生表students

●user1输入并执行下列语句

begintransaction

updatetablestudentssetage=20+1wheresno=’95001’

●user2输入并执行下列语句

begintransaction

select*fromstudentswheresno=’95001’

1）观察发生的现象,user2的语句可以执行吗（是否可以读到别人没有提交的数据）?

●user1输入并执行下列语句

commit

2）观察发生的现象,user2的语句执行了吗?

3）加锁和解锁操作的完成者是数据库用户吗?

●user2输入并执行下列语句

commit

2、隔离级1（缺省）方式下体会两事务的并发执行结果。

具体步骤如下（以user1和user2为例）:

●user1首先将user2接纳为自己数据库的用户,并授权user2可以查询学生表students

●user2输入并执行下列语句

begintransaction

selectagefromstudentswheresno=’95001’

●user1输入并执行下列语句

begintransaction

updatetablestudentssetage=20+1wheresno=’95001’

1）观察发生的现象,user1的语句可以执行吗（是否可以修改别人正在读的数据）?

●user2输入并执行下列语句

selectagefromstudentswheresno=’95001’

2）观察发生的现象,user2的语句执行了吗?

结果如何（是否可重复读）?

●user2输入并执行下列语句

commit

●user1输入并执行下列语句

commit

3、在隔离级0方式下体会两事务的并发执行结果。

具体步骤如下（以user1和user2为例）:

●user1首先将user2接纳为自己数据库的用户,并授权user2可以查询学生表students

●user1输入并执行下列语句

begintransaction

updatetablestudentssetage=20+1wheresno=’95001’

●user2输入并执行下列语句

begintransaction

settransactionisolationlevelReadUncommitted

select*fromstudentswheresno=’95001’

1）观察发生的现象,user2的语句可以执行吗（是否可以读到别人没有提交的数据）?

●user1输入并执行下列语句

commit

●user2输入并执行下列语句

commit

4、在隔离级1（缺省）方式下体会两事务的并发执行死锁情况。

具体步骤如下（以user1和user2为例）:

●user1首先将user2接纳为自己数据库的用户,并授权user2可以修改学生表students和学生选课表sc

●user1输入并执行下列语句

begintransaction

updatetablestudentssetage=age+1

●user2输入并执行下列语句

begintransaction

updatetablescsetgrade=grade+5

●user1输入并执行下列语句

updatetablescsetgrade=grade+5

1）观察发生的现象,user1的语句可以执行吗?

●user2输入并执行下列语句

updatetablestudentssetage=age+1

2）观察发生的现象,user2的语句执行了吗?

结果如何?

●在成功执行的窗口输入并执行下列语句

commit

5、在隔离级1（缺省）方式下体会两事务并发执行锁的粒度（锁数据所在页）。

具体步骤如下（以user1和user2为例）:

●user1首先将user2接纳为自己数据库的用户,并授权user2可以查询学生表students（该实验要求表中元组数要足够多,要求指定关键字约束）

●user1输入并执行下列语句

begintransaction

updatetablestudentssetage=age+1wheresno='95001'

●user2输入并执行下列语句

begintransaction

select*fromstudentswheresno='95201'

1）观察发生的现象,user2的语句可以执行吗?

●user2输入并执行下列语句

commit

●user1输入并执行下列语句

commit