数据库实验报告三--电子工程学院

1、数据库原理与应用实验成绩：数据库原理与应用实验报告实验名称: 完整性及视图、索引 实验编号： 实验三 学号： 姓名： 实验指导教师： 袁宝库 课程主讲教师: 袁宝库 报告提交日期: 2014 年 12 月 10 日 北京邮电大学28一、 实验目的掌握SQL Server2008视图、索引的使用，理解什么是数据库的完整性。二、 实验要求1、每完成一个任务，截取全屏幕快照13张作为中间步骤和结果的贴图，粘贴在最后的实验报告中。2、除了使用我们提供的数据外还要自己向表中添加些新数据，以保证每个查询结果不为空集，或计数结果不为0。3、思考题可以选做，作为优秀加分的依据。三、 实验任务1、创建一个视图，

2、该视图为每门课程的平均成绩，视图包括的列有课程号及平均成绩，并用利用该视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。2、创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号、课程名及平均成绩，并用利用该视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。3、为院系代码表(dept_code)创建基于“院系代码”列的非聚集索引yxdm_index，其填充因子值为 60。4、为教室信息表(classroom_info) 创建基于room_id列的惟一索引并插入一条room_id列与表中已有的值重复的数据，观察系统的反馈。5、重新修改表stud_info、less

3、on_info及stud_grade，修改的内容为：为三张表增加主码约束，stud_info的主码为stud_id，lesson_info的主码为course_id，stud_grade的主码为stud_id、course_id。为表stud_grade增加外码，其中stud_id参照stud_info的stud_id，course_id参照lesson_info的course_id。为表stud_grade增加检查性约束，约束grade列大于等于0且小于等于100。6、使用SQL语句向上题的3张表中插入新的数据，要求分别违反刚刚增加的主码约束、外码约束、检查性约束及原有的非空约束并观察系统的

4、反馈。【思考题】1、两名同学一组，各自设计一些简单的事务（对数据的增删改等操作），要求所有的事务以begin transaction开始，这些事务有的以rollback结束、有的以commit结束，有的没有明确的结束语句。2、用自己的电脑登录同伴的服务器。在对象资源管理器中执行前边设计的事务。每执行一次事务，合作的同伴在其电脑上通过查询观察这些事务的执行情况。3、执行完没有明确的结束语句的事务后，在同伴观察结果的期间，选择关闭对象资源管理器，看看有什么情况发生。4、根据实验结果你对微软的数据库管理系统的相关策略有什么样的认识？注：以上内容是可以在一台电脑上完成的，就是打开2个对象资源管理器，分

5、别登录到服务器上，一个执行事务，一个观察结果。建议大家在2台电脑上完成。5、表teach_schedule的teacher_id 列与表teacher_info的teacher_id列应满足参照完整性规则的。现在在不创建外码的前提下用触发器实现如下功能：当表teach_schedule发生更新或插入表数据时，检查teacher_id列新的值是否在表teacher_info的teacher_id列中已存在，如果已存在则允许修改，如果不存在则拒绝修改。6、测试上题的触发器是否正确。四、 实验过程和结果1. 创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号及平均成绩，并用利用该视图查询

6、所有课程的平均成绩，要求给出课程号、课程名及平均成绩。use sunting1gocreate view s_gas select course_id,AVG(grade) averageGrade from stud_grade group by course_iduse sunting1goselect a.course_id,course_name,averageGradefrom lesson_info join a on a.course_id=lesson_info.course_id2. 创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号、课程名及平均成绩，并用利用该

7、视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。use sunting1gocreate view a1as select stud_grade.course_id,course_name,AVG(grade) averageGrade from stud_grade join lesson_info on stud_grade.course_id=lesson_info.course_id group by stud_grade.course_id,lesson_info.course_nameuse sunting1goselect a1.course_id,course_n

8、ame,averageGradefrom a13. 为院系代码表(dept_code)创建基于“院系代码”列的非聚集索引yxdm_index，其填充因子值为 60。use sunting1gocreate nonclustered index yxdm_indxon dept_code(deptcode)with fillfactor=60go4. 为教室信息表(classroom_info) 创建基于room_id列的惟一索引并插入一条room_id列与表中已有的值重复的数据，观察系统的反馈。use sunting1gocreate unique index room_indexon cla

9、ssroom_info (room_id)use sunting1goinsert into classroom_info(room_id) values ('120703')5. 重新修改表stud_info、lesson_info及stud_grade，修改的内容为：为三张表增加主码约束，stud_info的主码为stud_id，lesson_info的主码为course_id，stud_grade的主码为stud_id、course_id。use sunting1goalter table stud_info add constraint pk_stuprimary ke

10、y (stud_id)use sunting1goalter table lesson_info add constraint pk_lesprimary key (course_id)use sunting1goalter table stud_grade add constraint pk_sgrprimary key (stud_id,course_id)在为stud_grade表添加主码约束是，由于course_id之前设定的值可以为null，故执行时出现问题。将course_id更改之后，再重新做一次。截图如下：为表stud_grade增加外码，其中stud_id参照stud_inf

11、o的stud_id，course_id参照lesson_info的course_id。use sunting1 go alter table stud_gradeadd constraint fk_stud_idforeign key (stud_id) references stud_info (stud_id)alter table stud_gradeadd constraint fk_course_idforeign key (course_id) references lesson_info (course_id)为表stud_grade增加检查性约束，约束grade列大于等于0且小

12、于等于100。use sunting1 go alter table stud_gradeadd constraint chk_gradecheck (grade>=0 and grade<=100)6. 使用SQL语句向上题的3张表中插入新的数据，要求分别违反刚刚增加的主码约束、外码约束、检查性约束及原有的非空约束并观察系统的反馈。 use sunting1 goinsert into stud_info ("STUD_ID","NAME","BIRTHDAY","GENDER", "ADD

13、RESS","TELCODE","ZIPCODE","MARK")values ('0401010811','张源','12-05-1986','男','北京市海淀区','#39;,'100080',560)use sunting1 go INSERT INTO stud_grade VALUES('0401050127','张军','0401010106&

14、#39;,84)use sunting1goinsert into stud_grade VALUES('2012210865','小米','0401010103',152);use sunting1goinsert into stud_grade VALUES('2012210865','0401010103',56);思考题1. 两名同学一组，各自设计一些简单的事务（对数据的增删改等操作），要求所有的事务以begin transaction开始，这些事务有的以rollback结束、有的以commit结束，有的没

15、有明确的结束语句。2. 用自己的电脑登录同伴的服务器。在对象资源管理器中执行前边设计的事务。每执行一次事务，合作的同伴在其电脑上通过查询观察这些事务的执行情况。use sunting1gobegin transactioninsert into teacher_infovalues ('010111','王丽','女',29,'讲师','#39;,1600,'0401010112');rollback在另一个事务管理器中查询use sunting1go select teacher

16、_id from teacher_infowhere name='王丽'查询没有结果use sunting1gobegin transactioninsert into teacher_infovalues ('010111','王丽','女',29,'讲师','#39;,1600,'0401010112');commit在另一个对象资源管理器中查询的结果use sunting1go select teacher_id from teacher_infowhere

17、name='王丽'查询结果立即出现use sunting1gobegin transactiondelete from teacher_infowhere teacher_id='010101'在另一个对象资源管理器中查询结果如下use sunting1goselect teacher_id from teacher_infowhere name='刘娜'查询结果一直没有出现，一直显示正在执行查询3. 执行完没有明确的结束语句的事务后，在同伴观察结果的期间，选择关闭对象资源管理器，看看有什么情况发生。关闭的时候的截图另一个对象资源管理器会立即显示

18、查询已成功执行以上内容均在同一台电脑上执行完成4. 根据实验结果你对微软的数据库管理系统的相关策略有什么样的认识？根据实验结果可以得知，事务具有隔离性、持久性、一致性和原子性，而不同的语句执行的结果也不同，commit是正常结束，rollback是异常结束，就是事务中的全部操作被撤销，而没有结束语句的时候，事务就会一直执行下去而没有结果。注：以上内容是可以在一台电脑上完成的，就是打开2个对象资源管理器，分别登录到服务器上，一个执行事务，一个观察结果。建议大家在2台电脑上完成。5. 表teach_schedule的teacher_id 列与表teacher_info的teacher_id列应满足

19、参照完整性规则的。现在在不创建外码的前提下用触发器实现如下功能：当表teach_schedule发生更新或插入表数据时，检查teacher_id列新的值是否在表teacher_info的teacher_id列中已存在，如果已存在则允许修改，如果不存在则拒绝修改。use sunting1 go create trigger tri_teacher on teach_scheduleafter insertasif not exists (select * from teacher_info where teacher_id=(select teacher_id from inserted)rollback6测试上题的触发器是否正确。当插入的tercher_id在teach