数据库实验报告总结.pdf

1、数据库 实验报告 刘皓冰 实 验 (一 ): 熟练掌握 SQL 语言 实验目的： 熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。 实验内容： 具体 包括如下三部分。 一、 熟悉上机环境。 客户 /服务器结构，数据库服务器在一台 NT 服务器上，同学们通过客户机 (操作系统为 Windows 2000)上安装的 SQL Server客户端程序 , 使用 SQL Server数据库服务器。具体包括： 1. 了解 SQL Server 环境。鼠标点击开始，进入“ Microsoft SQL Server企业管理器”，点击 SQL Server 组下的数据库服务器（服

2、务器名称为 NEUC-201S(Windows NT)） , 可以看到服务器上的圆形标志变为绿色，说明客户端程序已与服务器连接成功。点击服务器（ NEUC-201S(Windows NT)）下的数据库，可以看到服务器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。因为，数据库服务器上建有许多数据库 , 每个数据库都有一些合法的用户。 2. 鼠标点击开 始，进入“ Microsoft SQL Server查询分析器”，输入用户名和口令，进入 SQL查询分析器。如：你登录的客户机为 27号，那么请以用户名 user27，口令为 user27 登录，登录后缺省连到数据库 user

3、27 上，user27/user27 是数据库 user27 的创建者，因此用户 user27/ user27 具有在数据库 user27上创建表等一切权力。 3. 在 SQL查询分析器环境下，你就可进行 SQL命令语句操作了。 二、 在数据库 useri上创建学生选课有关关系模式，并插入相应的数据，实现有关查询。 1. 描述学生、课程情况的 E-R 图如下，请将其转化为用关系数据模型描述的关系模式 学生 课程 选修 学号 姓名 年龄 所在系 成绩 课号 课程名 学时 学分 先行课号 drop table Students; drop table Courses; drop table SC;

4、 create table Students( Sno nchar(10), Sname nchar(20), Sage int, Sdept nchar(20), primary key(Sno), ); create table Courses( Cno nchar(4), Cname nchar(50), Ctime int, Ccredit int, Cpno nchar(4) primary key(Cno), foreign key(Cpno) references Courses, ); create table SC( Sno nchar(10), Cno nchar(4),

5、Grade int, primary key(Sno,Cno), foreign key(Sno) references Students, foreign key(Cno) references Courses, ); 2. 在数据库中定义这些关系模式 ，并 向基本表中插入如下数据： insert into Students values (20134017,刘皓冰 ,19,计算机学院 ); insert into Students values (20134028,袁堃皓 ,20,计算机学院 ); insert into Students(Sno,Sname,Sage,Sdept) val

6、ues (20130000,范冰冰 ,22,表演学院 ); insert into Students values (20130011,黄晓明 ,23,表演学院 ); insert into Students values (20130012,王一 ,23,表演学院 ); insert into Students values (20130013,李天一 ,23,表演学院 ); insert into Students values (20130014,李二麻 ,23,表演学院 ); delete from Students where Sno=20130015; insert into St

7、udents values (20130015,李白 ,23,计算机学院 ) insert into Students values (20134018,王妮娜 ,21,计算机学院 ) insert into Students values (20134019,刘十 ,20,计算机学院 ) insert into Courses values(1001,C+语言 ,30,3,null) insert into Courses values(1002,编译原理 ,30,3,1008) insert into Courses values(1003,算法设计 ,40,4,1001) insert

8、into Courses values(1004,离散数学 ,30,3,null) insert into Courses values(1005,线性代数 ,20,2,null) insert into Courses values(1006,Java 语言 ,30,3,1001) insert into Courses values(1007,Python 语言 ,20,2,1009) insert into Courses values(1008,计算机组成原理 ,40,4,null) insert into Courses values(1009,操作系统 ,20,2,1008) in

9、sert into Courses(Cno,Cname,Ctime,Ccredit,Cpno) values(1000,数据库 ,30,3,1009) insert into SC(Sno,Cno,Grade) values(20134017,1000,100) insert into SC values(20134017,1001,97) insert into SC values(20134017,1002,95) insert into SC values(20134028,1000,100) insert into SC values(20134028,1004,95) insert

10、into SC values(20130000,1005,91) insert into SC values(20130011,1001,87) insert into SC values(20130011,1002,90) insert into SC values(20130000,1003,97) insert into SC values(20134019,1007,99) 3. 插入相应的数据，试着插入重复的元组，结果如何？ 由于在定义关系模式时，已经添加主键约束（ primary key(Sno),primary key(Cno),primary key(Sno,Cno)），所以不

11、可以再插入重复的元组 4. 在已建立的关系模式之上（已插入一些数据）建立主键约束，参照约束和用户定义的约束（要求学生年龄不小于 14岁，不大于 35岁），如果约束不能建立，请分析原因，修改后建立上述约束。 由于在定义关系模式 时，已经添加主键约束（ primary key(Sno),primary key(Cno),primary key(Sno,Cno) ） 和 参 照 约 束 (foreign key(Sno) references Students,foreign key(Cno) references Courses, foreign key(Cpno) references Cour

12、ses)， 现添加用户定义的约束（要求学生年龄不小于 14岁，不大于 35岁） :alter table Students add check (Sage=14 and Sage18 and Sage80 11. 求计算机系每个学生的姓名，选修的课程名和成绩 。 select Sname,Cname,Grade from Students,SC,Courses where Students.Sno=SC.Sno and SC.Cno=Courses.Cno and Sdept=计算机学院 12. 求每个学生的学号及平均成绩 。 select Students.Sname,avg(Grade)

13、平均成绩 from Students,SC where Students.Sno=SC.Sno group by Students.Sname 13. 求男学生每一年龄组中超过 1人的年龄组及人 数 。 alter table Students add Ssex nchar(8) update Students set Ssex=男 where Students.Sno=20130013 select Sage 年龄组 ,count(Sno) 人数 from Students group by Sage having count(*)1 14. 求每一门课程的间接先行课号 。 select c

14、ourses1.Cno 课程号 ,courses2.Cpno 间接先行课号 from Courses courses1,Courses courses2 where courses1.Cpno=courses2.Cno 15. 求选修了全部课程的学生的学号 。 select Students.Sno 学号 from Students where not exists (select * from Courses where not exists (select * from SC where Sno=Students.Sno and Cno=Courses.Cno) 实验 (三 ):数据库的安

15、全性和完整性 实验目的： 了解和使用安全子系统；进行完整性定义和检查。 实验内容： 具体 包括如下三部分。 一 熟练掌握 SQL(续 ) 1. 修改表 Students,在其中加入一个字段性别 sex char(1) alter table Students add Ssex nchar(8) 2. 创建索引：在 Students表的 Sno上创建唯一聚簇索引 , 在 SC表的（ Sno, Cno）上创建唯一索引 . 查询 Students表的内容 , 记录的顺序有变化吗 ? 查询 SC表的内容 , 记录的顺序有变化吗 ? 不可以建 因为 Sno是主键 3. 创建视图 创建计算机系学生视图 C

16、S_Students，其中包括学号、年龄和性别。 带 with check opition子句 不带 with check opition子句 时各插入一计算机系 学生记录， 通过视图查询插入的记录，结果如何？通过表 Students查询插入的记录，查看所在系字段上的值？ create view CS_Students as select sno,sage,ssex,sdept from students where sdept=计算机学院 with check option insert into CS_Students values(20114055,23,女 ,计算机学院 ); drop

17、 view CS_Students create view CS_Students as select sno,sage,ssex,sdept from students where sdept=计算机学院 insert into CS_Students values(20114056,23,女 ,艺术学院 ); select * from CS_Students 没有 with check option 时不对 where子句进行检查，可以插入 创建一视图，表示学生的平均成绩，其中包括 Sno,avgGrade。通过该视图插入一学生姓名和平均成绩，结果如何？通过该视图查找平均成绩在 90 分

18、以上的学生的学号和平均成绩，结果又如何？ create view Avg_Stu(sno,avg_grade) as select sno,avg(grade) from SC group by sno insert into Avg_Stu values(20114057,91) select * from Avg_Stu where avg_grade90 二 了解和使用安全子系统 1、将 sql server的一合法帐户 (user60)加入到你的数据库当中来，并给其授权，使之： 具有创建表的权限 具有对 Students表查询的权限 具有对 Studetns表的年龄字段修改的权限 SP

19、_grantdbaccess user51 grant create table to user51 grant select on Students to user51 grant update(Sage) on Students to user51 2、用 user60/user60进入系统，完成权限验证： 在 Students表上，实现 select * from Students 在 course表上，实现 select * from course update Studetns set Sage=Sage+1 update Students set Sdept=CS select *

20、 from db51.user51.Student grant select on Courses to user51#为了让 user51能够在我的 Courses表上查找 select * from db51.user51.Course update db51.user51.Student set Sage=Sage+1 select * from db51.user51.Student grant update(Sdept) on Students to user51 update db51.user51.Student set Sdept=CS select * from db51.u

21、ser51.Student 三 完整性定义和检查 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） create table Students_1( Sno char(4) not null, Sname cha

22、r(10) , Sdept char(10) defaultCS, Sage int check(Sage=16 and Sage=25), Ssex char(1) ) insert into Students_1 values(9921,cccc,MA,23,f) insert into Students_1 values(9922,ddd,MA,27,m) insert into Students_1(Sno,Sname,Sage,Ssex) values(9923,eeeee,21,m) insert into Students_1 values(9922,ddd,MA,23,m) 2

23、、创建表 course，要求课号作为主键 在 course表上插入一课程记录 （ c9,40,cname1,1） 在 course表上插入一课程记录 （ c9,40,cname2,1） 在 course表上插入一课程记录 （ null,25,canme3,2） create table Courses_1( Cno char(4), Ctime int, Cname char(50), Ccredit int, primary key(Cno), ) insert into Courses_1 values(c9,40,cname1,1) insert into Courses_1 value

24、s(c9,40,cname2,1) insert into Courses_1 values(null,25,cname3,2) 3、修改表 Studetns，指定学号为主键，然后 插入一记录 （ 9908,shang,CI,20） 插入一记录 （ 9908,shang,CI,20） 插入一记录 （ ,liang,CS,18） 插入一记录 （ ,an,CS,19） alter table Students_1 add primary key(Sno) insert into Students_1(Sno,Sname,Sdept,Sage) values(9908,shang,CI,20) in

25、sert into Students_1(Sno,Sname,Sdept,Sage) values(9908,shang,CI,20) insert into Students_1(Sno,Sname,Sdept,Sage) values(,liang,CS,18) insert into Students_1(Sno,Sname,Sdept,Sage) values(,an,CS,19) 4、 创建 sc表，要求表 sc中的学号参照表 Studetns中的学号， sc中的课号参照course中的课号。参照约束创建完成后，向这三个表插入数据。 先在 sc表插入一个选课记录，看看有什么情况发生？

26、 create table SC_1( Sno char(4), Cno char(4), Grade int, primary key(Sno,Cno), foreign key(Sno) references Students_1, foreign key(Cno) references Courses_1, ); insert into SC_1 values(9921,c9,100) 先删除 sc中的某个记录，看看有什么情况发生？ delete from SC_1 where Sno=9921 将 Studetns表中的学号 9906改为 9907，看看有什么情况发生？ update

27、Students_1 set Sno=8888 where Sno=9908 实验 (四 ):事务的并发控制 实验目的： 通过实验了解实际系统中三 级封锁协议 的实现方法和使用方法，体会加锁操作、死锁检测与解除。 实验内容： 具体分如下五部分。 说明： （ 1） Set transaction isolation level read uncommitted（读取未提交内容） read committed（读取提交内容） repeatable read（可重读） serializable（可串行化） 分别对应 隔离级 0,1,2,3。 （ 2） 要求两人一组。 1、 隔离级 1(缺省 )方式下

28、体会两事务的并发执行结果 。具体步骤如下 (以 user1和user2为例 ): user1 首先将 user2 接纳为自己数据库的用户 , 并授权 user2 可以查询学生表 students user1输入并执行下列语句 begin transaction update students set age=20+1 where sno=95001 1写 user2输入并执行下列语句 begin transaction select * from students where sno=95001 2读 1) 观察发生的现象 , user2 的语句可以执行吗 (是否可以读到别人没有提交的数据 )

29、? 不 可以 执行 2) user1输入并执行下列语句 commit 观察发生的现象 , user2的语句执行了吗 ? 可以 执行 3) 加锁和解锁操作的完成者是数据库用户吗 ? user2输入并执行下列语句 commit 不是 ， 是数据库系统 2、 隔离级 1(缺省 )方式下体会两事务的并发执行结果。 具体步骤如下 (以 user1和user2为例 ): user1 首先将 user2 接纳为自己数据库的用户 , 并授权 user2 可以查询学生表 students user2输入并执行下列语句 begin transaction select age from students wher

30、e sno=95001 2读 user1输入并执行下列语句 begin transaction update table students set age=20+1 where sno=95001 1写 1) 观察发生的现象 , user1的语句可以执行吗 (是否可以修改别人正在读的数据 )? 可以 执行 user2输入并执行下列语句 select age from students where sno=95001 2再读 2) 观察发生的现象 , user2的语句执行了吗 ? 结果如何 (是否可重复读 )? user2输入并执行下列语句 commit 不可 以执行 user1输入并执行下列语

31、句 commit user2 语句 这时 执行了， 可以读到修改后的结果 3、 在隔离级 0 方式下体会两事务的并发执行结果。 具体步骤如下 (以 user1 和user2为例 ): user1 首先将 user2 接纳为自己数据库的用户 , 并授权 user2 可以查询学生表 students user1输入并执行下列语句 begin transaction update table students set age=20+1 where sno=95001 1写 user2输入并执行下列语句 begin transaction set transaction isolation level

32、 Read Uncommitted select * from students where sno=95001 2读 1) 观察发生的现象 , user2 的语句可以执行吗 (是否可以读到别人没有提交的数据 )? 可以 读到别人没有提交的数据 user1输入并执行下列语句 commit user2输入并执行下列语句 commit 4、 在隔离级 1(缺省 )方式下体会两事务的并发执行死锁情况。 具体步骤如下 (以user1和 user2为例 ): user1 首先将 user2 接纳为自己数据库的用户 , 并授权 user2 可以修改学生表 students和学生选课表 sc user1输入

33、并执行下列语句 begin transaction update table students set age=age+1 1写学生表 user2输入并执行下列语句 begin transaction update table sc set grade=grade+5 2写选修表 user1输入并执行下列语句 update table sc set grade=grade+5 1写选修表 1) 观察发生的现象 , user1的语句可以执行吗 ? 第一次操作可以执行，第二次操作操作不可以 user2输入并执行下列语句 update table students set age=age+1 2写学

34、生表 2) 观察发生的现象 , user2的语句执行了吗 ? 结果如何 ? 没有，一直显示正在执行查询 在成功执行的窗口输入并执行下列语句 commit user1 commit后 user2的语句就可以执行 5、 在隔离级 1(缺省 )方式下体会两事务并发执行锁的粒度 (锁数据所在页 )。具体步骤如下 (以 user1和 user2为例 ): user1 首先将 user2 接纳为自己数据库的用户 , 并授权 user2 可以查询学生表 students(该实验要求表中元组数要足够多 , 要求指定关键字约束 ) user1输入并执行下列语句 begin transaction update table students set age=age+1 where sno=95001 user2输入并执行下列语句 begin transaction select * from students where sno=95201 1) 观察发生的现象 , user2的语句可以执行吗 ? 可以执行 ， 因为 1写的是学号 95001元组而 2查的是 95201元组信息 user2输入并执行下列语句 commit user1输入并执行下列语句 commit