数据库实验报告总结

数据库实验报告

刘皓冰

实验(一): 熟练掌握SQL 语言

实验目的：熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，

进行相应的查询操作。

实验内容：具体包括如下三部分。

一、熟悉上机环境。

客户/服务器结构，数据库服务器在一台NT 服务器上，同学们通过客户机(操作系统为Windows 2000)上安装的SQL Server 客户端程序, 使用SQL Server 数据库服务器。具体包括：

1. 了解SQL Server 环境。鼠标点击开始，进入“Microsoft SQL Server →企业

管理器”，点击SQL Server 组下的数据库服务器（服务器名称为NEUC-201S(Windows NT)）, 可以看到服务器上的圆形标志变为绿色，说明客户端程序已与服务器连接成功。点击服务器（NEUC-201S(Windows NT)）下的数据库，可以看到服务器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。因为，数据库服务器上建有许多数据库, 每个数据库都有一些合法的用户。

2. 鼠标点击开始，进入“Microsoft SQL Server →查询分析器”，输入用户名和

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

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

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

关查询。

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

学生 课程 选修 学号 姓名 年龄 所在系

成绩 课号

课程名 学时

学分 先行课号

drop table Students;

drop table Courses;

drop table SC;

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),

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) values ('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 Students 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 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')

insert 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 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)），所以不可以再插入重复的元组

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 Courses)，

现添加用户定义的约束（要求学生年龄不小于14岁，不大于35岁）:alter table Students add check (Sage>=14 and Sage<=35)

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

试着插入重复元组，无法插入：

因为约束已经建立，所以现象和3相同。但是如果没有主键约束，重复元组是可以插入的

实验(二): 熟练掌握SQL语言

用SQL完成下列查询

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

(1)不使用distinct语句，消除重复元组，写出其SQL语句

select Sno from SC group by SNo

(2) 使用distinct语句，消除重复元组，写出其SQL语句

select distinct Sno from SC

(3) 如果该查询写成：

select Student.Sno from Student, SC where Student.Sno=SC.Sno 请问该查询结果和上面哪个结果是相同的？

这样可能会有重复元组

2.求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。select https://www.360docs.net/doc/512064128.html,o 课程号,https://www.360docs.net/doc/512064128.html,ame 课程名,Courses.Ctime 学时, https://www.360docs.net/doc/512064128.html,redit 学分from Courses

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

select Sno,Sname,Sage from Students where Sdept='计算机学院' or Sdept='表演学院'

或者

select Students.Sno, Students.Sname, Students.Sage from Students

where Students.Sdept in ('计算机学院','表演学院')

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

列名。

select * from Students where Sdept!='计算机学院' and Sdept!='表演学院'

或者

select * from Students where Students.Sdept not in ('计算机学院','表演学院')

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

select * from Students order by Sage asc

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

select Sname, Sage from Students where Sdept ='计算机学院' and Sage>18 and Sage<24

或者

select Sname, Sage from Students where Sdept ='计算机学院' and Sage between 18 and 24

7.求姓名是以老开头的计算机系学生。

select * from Students where Sname like '刘%' and Students.Sdept='计算机学院'

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

select Students.Sno,Sname,Grade from Students,SC,Courses

where Students.Sno=SC.Sno and https://www.360docs.net/doc/512064128.html,o=https://www.360docs.net/doc/512064128.html,o and Cname='数据库'

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

select Students.Sno,https://www.360docs.net/doc/512064128.html,redit from Students,SC,Courses

where Students.Sno=SC.Sno and https://www.360docs.net/doc/512064128.html,o=https://www.360docs.net/doc/512064128.html,o and Students.Sno like '2013%'

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

select Students.Sno,Sname,Grade from Students,SC,Courses

where Students.Sno=SC.Sno and https://www.360docs.net/doc/512064128.html,o=https://www.360docs.net/doc/512064128.html,o and Cname='数据库' and Grade>80

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

select Sname,Cname,Grade from Students,SC,Courses

where Students.Sno=SC.Sno and https://www.360docs.net/doc/512064128.html,o=https://www.360docs.net/doc/512064128.html,o and Sdept='计算机学院'

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

select Students.Sname,avg(Grade) 平均成绩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 https://www.360docs.net/doc/512064128.html,o 课程号,courses2.Cpno 间接先行课号from Courses courses1,Courses courses2

where courses1.Cpno=https://www.360docs.net/doc/512064128.html,o

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

select Students.Sno 学号

from Students

where not exists (select *

from Courses

where not exists (select *

from SC

where Sno=Students.Sno and

Cno=https://www.360docs.net/doc/512064128.html,o))

实验(三):数据库的安全性和完整性

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

实验内容：具体包括如下三部分。

一熟练掌握SQL(续)

1.修改表Students,在其中加入一个字段性别sex char(1)

alter table Students add Ssex nchar(8)

2.创建索引：在Students表的Sno上创建唯一聚簇索引, 在SC表的（Sno, Cno）

上创建唯一索引. 查询Students表的内容, 记录的顺序有变化吗? 查询SC表的内容, 记录的顺序有变化吗?

不可以建因为Sno是主键

3.创建视图

?创建计算机系学生视图CS_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 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分以上的学生的学号和平均成绩，结果又如何？

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_grade>90

二了解和使用安全子系统

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

具有创建表的权限

具有对Students表查询的权限

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

SP_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 * from https://www.360docs.net/doc/512064128.html,er51.Student

grant select on Courses to user51#为了让user51能够在我的Courses表上查找select * from https://www.360docs.net/doc/512064128.html,er51.Course

update https://www.360docs.net/doc/512064128.html,er51.Student set Sage=Sage+1

select * from https://www.360docs.net/doc/512064128.html,er51.Student

grant update(Sdept) on Students to user51

update https://www.360docs.net/doc/512064128.html,er51.Student set Sdept='CS'

select * from https://www.360docs.net/doc/512064128.html,er51.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 char(10) ,

Sdept char(10) default'CS',

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、创建表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 values('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)

insert 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表插入一个选课记录，看看有什么情况发生？

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 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(缺省)方式下体会两事务的并发执行结果。具体步骤如下(以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的语句可以执行吗(是否可以读到别人没有提交的数

据)?

不可以执行

2)user1输入并执行下列语句

commit

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

可以执行

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

●user2输入并执行下列语句

commit

不是，是数据库系统

2、隔离级1(缺省)方式下体会两事务的并发执行结果。具体步骤如下(以user1和

user2为例):

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

表students

●user2输入并执行下列语句

begin transaction

select age from students where sno=’95001’ 2读