东北大学数据库实验报告2014.doc

《东北大学数据库实验报告2014.doc》由会员分享，可在线阅读，更多相关《东北大学数据库实验报告2014.doc（35页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 数据库实验报告班级：计算机1105班学号：20113263姓名：张少杰 时间：2014年6月19日实验(一): 熟练掌握SQL语言实验目的：熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。实验内容：具体包括如下三部分。一、 熟悉上机环境。客户/服务器结构，数据库服务器在一台NT服务器上，同学们通过客户机(操作系统为Windows 2000)上安装的SQL Server客户端程序, 使用SQL Server数据库服务器。具体包括：1. 了解SQL Server 环境。鼠标点击开始，进入“Microsoft SQL Server企业管理器”，点击SQL Serve

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

3、7上，user27/user27是数据库user27的创建者，因此用户user27/ user27具有在数据库user27上创建表等一切权力。3. 在SQL查询分析器环境下，你就可进行SQL命令语句操作了。二、 在数据库useri上创建学生选课有关关系模式，并插入相应的数据，实现有关查询。1. 描述学生、课程情况的E-R图如下，请将其转化为用关系数据模型描述的关系模式学生课程选修学号姓名年龄所在系成绩课号课程名学时学分先行课号根据E-R图按如下代码建立三个表：CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) UNIQUE,S

4、sex CHAR (2),Sage SMALLINT,Sdept CHAR(20);CREATE TABLE Course(Cno CHAR(4) PRIMARY KEY,Cname CHAR(40),Cpno CHAR(4),Credit SMALLINT,FOREIGN KEY (Cpno) REFERENCES Course(Cno);CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno) REFERENCES Student(Sno),FOREIGN KEY

5、(Cno) REFERENCES Course(Cno);在Course 表中插入学时字段alter table Course add Ctime smallint NULL运行后建表成功。2. 在数据库中定义这些关系模式，并向基本表中插入如下数据：INSERTINTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(201100001,张一,男,IS,18);INSERTINTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(201100002,张二,女,IS,18);INSERTINTO Student(Sno,Snam

6、e,Ssex,Sdept,Sage)VALUES(201100003,张三,男,IS,18);INSERTINTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(201100004,张四,女,IS,18);INSERTINTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(201100005,张五,男,IS,18); INSERTINTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(201100006,张六,女,IS,18);INSERTINTO Course(Cno,Cname,Cp

7、no,Credit)VALUES(1,数据库原理,NULL,2);INSERTINTO Course(Cno,Cname,Cpno,Credit)VALUES(2,数据结构,NULL,2);INSERTINTO Course(Cno,Cname,Cpno,Credit)VALUES(3,C语言,NULL,2);INSERTINTO Course(Cno,Cname,Cpno,Credit)VALUES(4,算法,NULL,2);INSERTINTO Course(Cno,Cname,Cpno,Credit)VALUES(5,高等数学,NULL,2);INSERTINTO SC(Sno,Cno)

8、VALUES(201100001,1);INSERTINTO SC(Sno,Cno)VALUES(201100002,2);INSERTINTO SC(Sno,Cno)VALUES(201100003,3);INSERTINTO SC(Sno,Cno)VALUES(201100004,4);INSERTINTO SC(Sno,Cno)VALUES(201100005,5);3. 插入相应的数据，试着插入重复的元组，结果如何？INSERTINTO SC(Sno,Cno)VALUES(201100005,5);4. 在已建立的关系模式之上（已插入一些数据）建立主键约束，参照约束和用户定义的约束（要

9、求学生年龄不小于14岁，不大于35岁），如果约束不能建立，请分析原因，修改后建立上述约束。alter table student add check (Sage between 14 and 35)在建立主键约束前如果存在数据不满足将要建立的主键约束条件，则不能建立主键约束。先将相关数据修改为满足主键约束条件再建立主键约束则操作成功。5. 如果约束建立后，请试着插入重复元组，结果如何？试着插入重复元组，无法插入：INSERTINTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES(201100005,张五,男,18,IS); 实验(二): 熟练掌握SQL语言用

10、SQL完成下列查询1. 求选修了课程的学生的学号，要求：(1) 不使用distinct语句，写出其SQL语句Select Sno from SC(2) 使用distinct语句，消除重复元组，写出其SQL语句select distinct Sno from SC(3) 如果该查询写成： select Student.Sno from Student, SC where Student.Sno=SC.Sno 请问该查询结果和上面哪个结果是相同的？(3) 和（1）的结果相同2. 求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。select Cno MyCno ,Cname MyCn

11、ame,Ctime MyCtime,Credit MyCredict from Course3. 求计算机系和数学系的学生学号、姓名和年龄。select Sno,Sname,Sage from Student where Sdept=CS or Student =Math4. 求不是数学系、计算机系的学生的情况，要求select后写*代替列出所有的列名。select * from Student where Student.Sdept!=CS and Student.Sdept!=Math5. 求全体学生的信息，要求按年龄升序排列。select * from Student order by

12、Sage asc6. 求计算机系年龄在1820岁之间的学生姓名和年龄。select Sname,Sage from Student where Sage=18 and Sage804求计算机系每个学生的姓名，选修的课程名和成绩。select Student.Sname,SC.Cno,SC.Grade from Student,SC where Student.Sdept=CS and Student.Sno=SC.Sno5求每个学生的学号及平均成绩。select Sno,AVG(Grade) from SC group by Sno6求男学生每一年龄组中超过1人的年龄组及人数。select S

13、age,Count(Sno) from Student group by Sage having Count(Sno)17求每一门课程的间接先行课号。select C1.Cno,C2.Cpno from Course C1,Course C2 where C1.Cpno=C2.Cno 8. 求选修了全部课程的学生的学号。select Sno from Student where not exists (select * from Course where not exists(select * from SC where Sno=Student.Sno and Cno=Course.Cno);

14、实验(四):数据库的安全性实验目的：了解和使用安全子系统和检查。实验内容：具体包括如下二部分。一熟练掌握SQL(续)1. 修改表Students,在其中加入一个字段性别sex char(1)CREATE TABLE Student(Sno CHAR(9) ,Sname CHAR(20) ,Sage SMALLINT,Sdept CHAR(20);CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT);INSERTINTO Student(Sno,Sname,Sdept,Sage)VALUES(201100003,张一,IS,18);INS

15、ERTINTO Student(Sno,Sname, Sdept,Sage)VALUES(201100002,张二,CS,18);INSERTINTO Student(Sno,Sname,Sdept,Sage)VALUES(201100001,张三,CS,18);INSERTINTO Student(Sno,Sname,Sdept,Sage)VALUES(201100004,张四,CS,18);INSERTINTO Student(Sno,Sname,Sdept,Sage)VALUES(201100005,张五,CS,18);INSERTINTO Student(Sno,Sname,Sdept

16、,Sage)VALUES(201100006,张六,IS,18);INSERTINTO SC(Sno,Cno,Grade)VALUES(201100002,2,88);INSERTINTO SC(Sno,Cno，Grade)VALUES(201100002,1,98);INSERTINTO SC(Sno,Cno,Grade)VALUES(201100001,2,77);INSERTINTO SC(Sno,Cno,Grade)VALUES(201100001,1,80);INSERTINTO SC(Sno,Cno,Grade)VALUES(201100005,2,92);alter table

17、Student add sex char(1);2. 创建索引：在Students表的Sno上创建唯一聚簇索引, 在SC表的（Sno, Cno）上创建唯一索引. 查询Students表的内容, 记录的顺序有变化吗? 查询SC表的内容, 记录的顺序有变化吗?Create unique clustered index Student_sno on Student(Sno);create unique index Sno_cno on SC(Sno,Cno);3. 创建视图 创建计算机系学生视图CS_Students，其中包括学号、年龄和性别。带with check opition子句不带with

18、check opition子句时各插入一计算机系学生记录，通过视图查询插入的记录，结果如何？通过表Students查询插入的记录，查看所在系字段上的值？create view CS_students1 as select Sno,Sage,sex from Student where Sdept=CS with check optionINSERT INTO CS_students1VALUES(201100005,18,m);create view CS_students2 as select Sno,Sage,sex from Student where Sdept=CSINSERT IN

19、TO CS_students2VALUES(201100007,18,m);创建一视图，表示学生的平均成绩，其中包括Sno,avgGrade。通过该视图插入一学生姓名和平均成绩，结果如何？通过该视图查找平均成绩在90分以上的学生的学号和平均成绩，结果又如何？create view Stuavggrade(sno,avggrade)as select Sno,avg(Grade)from SC group by SC.snoInsert into Stuavggradevalues(201100003,87)select sno,avggradefrom Stuavggradewhere avg

20、grade=90二了解和使用安全子系统1、将sql server的一合法帐户(user62)加入到你的数据库当中来，并给其授权，使之：SP_grantdbaccess user62;具有创建表的权限grant create table to user62;具有对Students表查询的权限grant select on Student to user62;具有对Studetns表的年龄字段修改的权限grant update(Sage) on Student to user622、用user62/user62进入系统，完成权限验证：在Students表上，实现select * from Stud

21、entsselect * from db63.user63.Student在course表上，实现select * from courseselect * from db63.user63.courseupdate Studetns set Sage=Sage+1update db63.user63.Student set Sage=Sage+1select * from db63.user63.Studentupdate Students set Sdept=CSupdate db63.user63.Student set Sdept=CSselect * from db63.user63.

22、Student3、你是数据库的dbo，你来查看dept表，结果如何？使用set user命令使自己成为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）CREATE TA

23、BLE Student(Sno CHAR(9) not null,Sname CHAR(20) ,Sdept char(20) defaultCS,Sage SMALLINT check(Sage=16 and Sage=25),Ssex char(2);insert into Student(Sno,Sname,Sdept,Sage,Ssex)values(9921,cccc,MA,23,f);insert into Student(Sno,Sname,Sdept,Sage,Ssex)values(9922,ddd,MA,27,m);insert into Student(Sno,Sname

24、,Sdept,Sage,Ssex)values(9923,eeeee,21,m);insert into Student(Sno,Sname,Sdept,Sage,Ssex)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 course(cno char(10) primary key,ctime char(10),cname char

25、(20),cscore char(10);insert into course(cno,ctime,cname,cscore)values(c9,40,cname1,1);insert into course(cno,ctime,cname,cscore)values(c9,40,cname2,1);insert into course(cno,ctime,cname,cscore)values(null,25,canme3,2);3、修改表Studetns，指定学号为主键，然后插入一记录（9908,shang,CI,20）插入一记录（9908,shang,CI,20）插入一记录（,liang

26、,CS,18）插入一记录（,an,CS,19） ALTER TABLE Student ADD CONSTRAINT pk_Sno PRIMARY KEY(Sno)insert into Student(Sno,Sname,Sdept,Sage)values(9908,shang,CI,20);insert into Student(Sno,Sname,Sdept,Sage)values(9908,shang,CI,20);insert into Student(Sno,Sname,Sdept,Sage)values(,liang,CS,18);insert into Student(Sno,

27、Sname,Sdept,Sage)values(,an,CS,19);4、创建sc表，要求表sc中的学号参照表Studetns中的学号，sc中的课号参照course中的课号。参照约束创建完成后，向这三个表插入数据。先在sc表插入一个选课记录，看看有什么情况发生？先删除sc中的某个记录，看看有什么情况发生？将Studetns表中的学号9906改为9907，看看有什么情况发生？insert into Student(Sno,Sname,Sdept,Sage,Ssex)values(9906,ddd,MA,23,m);create table sc(Sno char(9),Cno char(10),

28、Grade int,foreign key(Sno) references Student(Sno),foreign key(Cno) references course(cno);insert into SC(Sno,Cno,Grade)values(9922,c9,85);delete from sc where Sno = 9922;update Students set Sno=9907 where Sno=9906;如果SC引用了9906 insert into SC(Sno,Cno,Grade)values(9906,c9,75);update Students set Sno=9

29、906 where Sno=9907;实验(六):事务的并发控制实验目的：通过实验了解实际系统中,三级封锁协议的实现方法和使用方法，体会加锁操作、死锁检测与解除。实验内容：具体分如下五部分。 说明：（1）Set transaction isolation level read uncommittedread committedrepeatable readserializable分别对应隔离级0,1,2,3。（2）要求两人一组。CREATE TABLE students(sno CHAR(9) primary key,sname CHAR(20) ,sdept char(20),sage SM

30、ALLINT,ssex char(2);insert into students(sno,sname,sdept,sage,ssex)values(95001,张三,CS,23,m);insert into students(sno,sname,sdept,sage,ssex)values(95101,张丽,CS,25,f);insert into students(sno,sname,sdept,sage,ssex)values(95201,张伟,CS,20,m);create table course(cno char(10) primary key,ctime char(10),cnam

31、e char(20),cscore char(10);insert into course(cno,ctime,cname,cscore)values(c9,40,math,1);create table sc(sno char(9),cno char(10),grade int,foreign key(sno) references students(sno),foreign key(cno) references course(cno);insert into sc(sno,cno,grade)values(950001,c9,85);1、 隔离级1(缺省)方式下体会两事务的并发执行结果。

32、具体步骤如下(以user1和user2为例):l user1首先将user2接纳为自己数据库的用户, 并授权user2可以查询学生表studentssp_revokedbaccess user62;SP_grantdbaccess user62;grant select on students to user62;l user1输入并执行下列语句begin transactionupdate students set age=20+1 where sno=95001l user2输入并执行下列语句begin transaction select * from students where sn

33、o=950011) 观察发生的现象, user2的语句可以执行吗(是否可以读到别人没有提交的数据)? 不可以。2) user1输入并执行下列语句commit观察发生的现象, user2的语句执行了吗? 查询成功。执行了。 3) 加锁和解锁操作的完成者是数据库用户吗? l user2输入并执行下列语句commit不是。是数据库系统。2、 隔离级1(缺省)方式下体会两事务的并发执行结果。具体步骤如下(以user1和user2为例):l user1首先将user2接纳为自己数据库的用户, 并授权user2可以查询学生表studentsSP_grantdbaccess user62;grant sel

34、ect on students to user62;l user2输入并执行下列语句begin transaction select age from students where sno=95001l user1输入并执行下列语句begin transactionupdate table students set age=20+1 where sno=950011) 观察发生的现象, user1的语句可以执行吗(是否可以修改别人正在读的数据)?User1的语句可以执行。l user2输入并执行下列语句select age from students where sno=950012) 观察发

35、生的现象, user2的语句执行了吗? 结果如何(是否可重复读)?不可以。 user2输入并执行下列语句commit l user1输入并执行下列语句commit3、 在隔离级0方式下体会两事务的并发执行结果。具体步骤如下(以user1和user2为例):l user1首先将user2接纳为自己数据库的用户, 并授权user2可以查询学生表studentsSP_grantdbaccess user62;grant select on students to user62;l user1输入并执行下列语句begin transactionupdate students set sage=sage

36、+1 where sno=95001user2输入并执行下列语句begin transaction set transaction isolation level Read Uncommittedselect * from students where sno=950011) 观察发生的现象, user2的语句可以执行吗(是否可以读到别人没有提交的数据)? 可以。l user1输入并执行下列语句commitl user2输入并执行下列语句commit 4、 在隔离级1(缺省)方式下体会两事务的并发执行死锁情况。具体步骤如下(以user1和user2为例):l user1首先将user2接纳为自

37、己数据库的用户, 并授权user2可以修改学生表students和学生选课表scgrant update on students to user62;grant update on sc to user62;l user1输入并执行下列语句begin transaction update students set sage=sage+1l user2输入并执行下列语句begin transactionupdate table sc set grade=grade+5l user1输入并执行下列语句update table sc set grade=grade+51) 观察发生的现象, user

38、1的语句可以执行吗? 可以。l user2输入并执行下列语句update table students set age=age+12) 观察发生的现象, user2的语句执行了吗? 结果如何? 不可以。l 在成功执行的窗口输入并执行下列语句commit 5、 在隔离级1(缺省)方式下体会两事务并发执行锁的粒度(锁数据所在页)。具体步骤如下(以user1和user2为例):l user1首先将user2接纳为自己数据库的用户, 并授权user2可以查询学生表students(该实验要求表中元组数要足够多, 要求指定关键字约束)l user1输入并执行下列语句begin transaction update students set sage=sage+1 where sno=95001l user2输入并执行下列语句begin transactionselect * from students where sno=952011) 观察发生的现象, user2的语句可以执行吗?可以执行。l user2输入并执行下列语句commitl user1输入并执行下列语句commit