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

数据库实验报告班级：计算机1105班学号：20113263姓名：张少杰时间：2014年6月19日实验(一):熟练掌握SQL语言实验目的：熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。实验内容：具体包括如下三部分。熟悉上机环境。客户/服务器结构，数据库服务器在一台NT服务器上，同学们通过客户机(操作系统为Windows2000)上安装的SQLServer客户端程序,使用SQLServer数据库服务器。具体包括：了解SQLServer环境。鼠标点击开始，进入“MicrosoftSQLServer企业管理器”，点击SQLServer组下的数据库服务器（服务器名称为NEUC-201S(WindowsNT)）,可以看到服务器上的圆形标志变为绿色，说明客户端程序已与服务器连接成功。点击服务器（NEUC-201S(WindowsNT)）下的数据库，可以看到服务器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。因为，数据库服务器上建有许多数据库,每个数据库都有一些合法的用户。鼠标点击开始，进入“MicrosoftSQLServer查询分析器”，输入用户名和口令，进入SQL查询分析器。如：你登录的客户机为27号，那么请以用户名user27，口令为user27登录，登录后缺省连到数据库user27上，user27/user27是数据库user27的创建者，因此用户user27/user27具有在数据库user27上创建表等一切权力。在SQL查询分析器环境下，你就可进行SQL命令语句操作了。在数据库useri上创建学生选课有关关系模式，并插入相应的数据，实现有关查询。描述学生、课程情况的E-R图如下，请将其转化为用关系数据模型描述的关系模式学生学生课程选修学号姓名年龄所在系成绩课号课程名学时学分先行课号根据E-R图按如下代码建立三个表：CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)UNIQUE,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20));CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno));CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));在Course表中插入学时字段altertableCourseaddCtimesmallintNULL运行后建表成功。在数据库中定义这些关系模式，并向基本表中插入如下数据：INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100001','张一','男','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100002','张二','女','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100003','张三','男','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100004','张四','女','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100005','张五','男','IS',18);INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('201100006','张六','女','IS',18);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('1','数据库原理',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('2','数据结构',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('3','C语言',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('4','算法',NULL,2);INSERTINTOCourse(Cno,Cname,Cpno,Credit)VALUES('5','高等数学',NULL,2);INSERTINTOSC(Sno,Cno)VALUES('201100001','1');INSERTINTOSC(Sno,Cno)VALUES('201100002','2');INSERTINTOSC(Sno,Cno)VALUES('201100003','3');INSERTINTOSC(Sno,Cno)VALUES('201100004','4');INSERTINTOSC(Sno,Cno)VALUES('201100005','5');插入相应的数据，试着插入重复的元组，结果如何？INSERTINTOSC(Sno,Cno)VALUES('201100005','5');在已建立的关系模式之上（已插入一些数据）建立主键约束，参照约束和用户定义的约束（要求学生年龄不小于14岁，不大于35岁），如果约束不能建立，请分析原因，修改后建立上述约束。altertablestudentaddcheck(Sagebetween14and35)在建立主键约束前如果存在数据不满足将要建立的主键约束条件，则不能建立主键约束。先将相关数据修改为满足主键约束条件再建立主键约束则操作成功。如果约束建立后，请试着插入重复元组，结果如何？试着插入重复元组，无法插入：INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('201100005','张五','男',18,'IS');

实验(二):熟练掌握SQL语言用SQL完成下列查询求选修了课程的学生的学号，要求：(1)不使用distinct语句，写出其SQL语句SelectSnofromSC(2)使用distinct语句，消除重复元组，写出其SQL语句selectdistinctSnofromSC(3)如果该查询写成：selectStudent.SnofromStudent,SCwhereStudent.Sno=SC.Sno请问该查询结果和上面哪个结果是相同的？(3)和（1）的结果相同求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。selectCnoMyCno,CnameMyCname,CtimeMyCtime,CreditMyCredictfromCourse求计算机系和数学系的学生学号、姓名和年龄。selectSno,Sname,SagefromStudentwhereSdept='CS'orStudent='Math'求不是数学系、计算机系的学生的情况，要求select后写*代替列出所有的列名。select*fromStudentwhereStudent.Sdept!='CS'andStudent.Sdept!='Math'求全体学生的信息，要求按年龄升序排列。select*fromStudentorderbySageasc求计算机系年龄在18~20岁之间的学生姓名和年龄。selectSname,SagefromStudentwhereSage>=18andSage<=20andSdept='CS'求姓名是以“XX”开头的计算机系学生。select*fromStudentwhereSnamelike'张李%'andSdept='CS'实验(三):熟练掌握SQL语言求选修了2课程的学生的学号、姓名、成绩。selectStudent.Sno,Student.Sname,SC.GradefromStudent,SCwhereSC.Sno=Student.SnoandSC.Cno=2求学号为95开头的学生的学号和所选修的课程的学分。selectStudent.Sno,Course.CreditfromStudent,SC,CoursewhereStudent.Snolike'2012%'andStudent.Sno=SC.SnoandSC.Cno=Course.Cno3求选修2课程且成绩为80分以上的学生的学号、姓名和成绩。selectStudent.Sno,Student.Sname,SC.GradefromStudent,SCwhereStudent.Sno=SC.SnoandSC.Cno=2andSC.Grade>804求计算机系每个学生的姓名，选修的课程名和成绩。selectStudent.Sname,SC.Cno,SC.GradefromStudent,SCwhereStudent.Sdept='CS'andStudent.Sno=SC.Sno5求每个学生的学号及平均成绩。selectSno,AVG(Grade)fromSCgroupbySno6求男学生每一年龄组中超过1人的年龄组及人数。selectSage,Count(Sno)fromStudentgroupbySagehavingCount(Sno)>17求每一门课程的间接先行课号。selectC1.Cno,C2.CpnofromCourseC1,CourseC2whereC1.Cpno=C2.Cno8.求选修了全部课程的学生的学号。selectSnofromStudentwherenotexists(select*fromCoursewherenotexists(select*fromSCwhereSno=Student.SnoandCno=Course.Cno));

实验(四):数据库的安全性实验目的：了解和使用安全子系统和检查。实验内容：具体包括如下二部分。一熟练掌握SQL(续)修改表Students,在其中加入一个字段性别sexchar(1)CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20),SageSMALLINT,SdeptCHAR(20));CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100003','张一','IS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100002','张二','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100001','张三','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100004','张四','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100005','张五','CS',18);INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES('201100006','张六','IS',18);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100002','2',88);INSERTINTOSC(Sno,Cno，Grade)VALUES('201100002','1',98);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100001','2',77);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100001','1',80);INSERTINTOSC(Sno,Cno,Grade)VALUES('201100005','2',92);altertableStudentaddsexchar(1);创建索引：在Students表的Sno上创建唯一聚簇索引,在SC表的（Sno,Cno）上创建唯一索引.查询Students表的内容,记录的顺序有变化吗?查询SC表的内容,记录的顺序有变化吗?CreateuniqueclusteredindexStudent_snoonStudent(Sno);createuniqueindexSno_cnoonSC(Sno,Cno);创建视图创建计算机系学生视图CS_Students，其中包括学号、年龄和性别。 带withcheckopition子句 不带withcheckopition子句时各插入一计算机系学生记录，通过视图查询插入的记录，结果如何？通过表Students查询插入的记录，查看所在系字段上的值？createviewCS_students1asselectSno,Sage,sexfromStudentwhereSdept='CS'withcheckoptionINSERTINTOCS_students1VALUES('201100005',18,’m’);createviewCS_students2asselectSno,Sage,sexfromStudentwhereSdept='CS'INSERTINTOCS_students2VALUES('201100007',18,’m’);创建一视图，表示学生的平均成绩，其中包括Sno,avgGrade。通过该视图插入一学生姓名和平均成绩，结果如何？通过该视图查找平均成绩在90分以上的学生的学号和平均成绩，结果又如何？createviewStuavggrade(sno,avggrade)asselectSno,avg(Grade)fromSCgroupbySC.snoInsertintoStuavggradevalues('201100003',87)selectsno,avggradefromStuavggradewhereavggrade>=90二了解和使用安全子系统1、将sqlserver的一合法帐户(user62)加入到你的数据库当中来，并给其授权，使之：SP_grantdbaccessuser62;具有创建表的权限grantcreatetabletouser62;具有对Students表查询的权限grantselectonStudenttouser62;具有对Studetns表的年龄字段修改的权限grantupdate(Sage)onStudenttouser622、用user62/user62进入系统，完成权限验证：在Students表上，实现select*fromStudentsselect*fromdb63.user63.Student在course表上，实现select*fromcourseselect*fromdb63.user63.courseupdateStudetnssetSage=Sage+1updatedb63.user63.StudentsetSage=Sage+1select*fromdb63.user63.StudentupdateStudentssetSdept=’CS’updatedb63.user63.StudentsetSdept='CS'select*fromdb63.user63.Student3、你是数据库的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’）CREATETABLEStudent(SnoCHAR(9)notnull,SnameCHAR(20),Sdeptchar(20)default'CS',SageSMALLINTcheck(Sage>=16andSage<=25),Ssexchar(2));insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9921','cccc’,’MA’,23,’f’);insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9922','ddd’,’MA’,27,’m’);insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9923','eeeee’,21,’m’);insertintoStudent(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）createtablecourse(cnochar(10)primarykey,ctimechar(10),cnamechar(20),cscorechar(10));insertintocourse(cno,ctime,cname,cscore)values(’c9’,40,’cname1’,1);insertintocourse(cno,ctime,cname,cscore)values(’c9’,40,’cname2’,1);insertintocourse(cno,ctime,cname,cscore)values(null,25,’canme3’,2);3、修改表Studetns，指定学号为主键，然后 插入一记录（’9908’,’shang’,’CI’,20） 插入一记录（’9908’,’shang’,’CI’,20） 插入一记录（’’,’liang’,’CS’,18） 插入一记录（’’,’an’,’CS’,19）ALTERTABLEStudentADDCONSTRAINTpk_SnoPRIMARYKEY(Sno)insertintoStudent(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20);insertintoStudent(Sno,Sname,Sdept,Sage)values('9908','shang','CI',20);insertintoStudent(Sno,Sname,Sdept,Sage)values(‘’,’liang’,’CS’,18);insertintoStudent(Sno,Sname,Sdept,Sage)values(‘’,’an’,’CS’,19);4、创建sc表，要求表sc中的学号参照表Studetns中的学号，sc中的课号参照course中的课号。参照约束创建完成后，向这三个表插入数据。先在sc表插入一个选课记录，看看有什么情况发生？ 先删除sc中的某个记录，看看有什么情况发生？ 将Studetns表中的学号9906改为9907，看看有什么情况发生？insertintoStudent(Sno,Sname,Sdept,Sage,Ssex)values('9906','ddd’,’MA’,23,’m’);createtablesc(Snochar(9),Cnochar(10),Gradeint,foreignkey(Sno)referencesStudent(Sno),foreignkey(Cno)referencescourse(cno);insertintoSC(Sno,Cno,Grade)values('9922','c9',85);deletefromscwhereSno='9922';updateStudentssetSno=‘9907’whereSno=‘9906’;如果SC引用了9906insertintoSC(Sno,Cno,Grade)values('9906','c9',75);updateStudentssetSno=‘9906’whereSno=‘9907’;

实验(六):事务的并发控制实验目的：通过实验了解实际系统中,三级封锁协议的实现方法和使用方法，体会加锁操作、死锁检测与解除。实验内容：具体分如下五部分。说明：（1）Settransactionisolationlevelreaduncommittedreadcommittedrepeatablereadserializable分别对应隔离级0,1,2,3。（2）要求两人一组。CREATETABLEstudents(snoCHAR(9)primarykey,snameCHAR(20),sdeptchar(20),sageSMALLINT,ssexchar(2));insertintostudents(sno,sname,sdept,sage,ssex)values('95001','张三','CS',23,'m');insertintostudents(sno,sname,sdept,sage,ssex)values('95101','张丽','CS',25,'f');insertintostudents(sno,sname,sdept,sage,ssex)values('95201','张伟','CS',20,'m');createtablecourse(cnochar(10)primarykey,ctimechar(10),cnamechar(20),cscorechar(10));insertintocourse(cno,ctime,cname,cscore)values('c9',40,'math',1);createtablesc(snochar(9),cnochar(10),gradeint,foreignkey(sno)referencesstudents(sno),foreignkey(cno)referencescourse(cno));insertintosc(sno,cno,grade)values('950001','c9',85);隔离级1(缺省)方式下体会两事务的并发执行结果。具体步骤如下(以user1和user2为例):user1首先将user2接纳为自己数据库的用户,并授权user2可以查询学生表studentssp_revokedbaccessuser62;SP_grantdbaccessuser62;grantselectonstudentstouser62;user1输入并执行下列语句begintransactionupdatestudentssetage=20+1wheresno=’95001’user2输入并执行下列语句begintransactionselect*fromstudentswheresno=’95001’观察发生的现象,user2的语句可以执行吗(是否可以读到别人没有提交的数据)?不可以。user1输入并执行下列语句commit观察发生的现象,user2的语句执行了吗?查询成功。执行了。3)加锁和解锁操作的完成者是数据库用户吗?user2输入并执行下列语句commit不是。是数据库系统。隔离级1(缺省)方式下体会两事务的并发执行结果。具体步骤如下(以user1和user2为例):user1首先将user2接纳为自己数据库的用户,并授权user2可以查询学生表studentsSP_grantdbaccessuser62;grantselectonstudentstouser62;user2输入并执行下列语句begintransactionselectagefromstudentswheresno=’95001’user1输入并执行下列语句begintransactionupdatetablestudentssetage=20+1wheresno=’95001’观察发生的现象,user1的语句可以执行吗(是否可以修改别人正在读的数据)?User1的语句可以执行。user2输入并执行下列语句selectagefromstudentswheresno=’95001’观察发生的现象,user2的语句执行了吗?结果如何(是否可重复读)?不可以。user2输入并执行下列语句commituser1输入并执行下列语句commit在隔离级0方式下体会两事务的并发执行结果。具体步骤如下(以user1和use