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

《数据库原理与应用》

实验成绩：

数据库原理与应用
实验报告

实验名称: 完整性及视图、索引
实验编号： 实验三
学号：
姓名：
实验指导教师： 袁宝库
课程主讲教师: 袁宝库
报告提交日期: 2014 年 12 月 10 日

北京邮电大学
28

一、 实验目的
掌握SQL Server2008视图、索引的使用，理解什么是数据库的完整性。
二、 实验要求
1、每完成一个任务，截取全屏幕快照1~3张作为中间步骤和结果的贴图，粘贴在最后的实验报告中。
2、除了使用我们提供的数据外还要自己向表中添加些新数据，以保证每个查询结果不为空集，或计数结果不为0。
3、思考题可以选做，作为优秀加分的依据。
三、 实验任务
1、创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号及平均成绩，并用利用该视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。
2、创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号、课程名及平均成绩，并用利用该视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。
3、为院系代码表(dept_code)创建基于“院系代码”列的非聚集索引yxdm_index，其填充因子值为 60。
4、为教室信息表(classroom_info) 创建基于room_id列的惟一索引并插入一条room_id列与表中已有的值重复的数据，观察系统的反馈。
5、重新修改表stud_info、lesson_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张表中插入新的数据，要求分别违反刚刚增加的主码约束、外码约束、检查性约束及原有的非空约束并观察系统的反馈。
【思考题】
1、两名同学一组，各自设计一些简单的事务（对数据的增删改等操作），要求所有的事务以begin transaction开始，这些事务有的以rollback结束、有的以commit结束，有的没有明确的结束语句。
2、用自己的电脑登录同伴的服务器。在对象资源管理器中执行前边设计的事务。每执行一次事务，合作的同伴在其电脑上通过查询观察这些事务的执行情况。
3、执行完没有明确的结束语句的事务后，在同伴观察结果的期间，选择关闭对象资源管理器，看看有什么情况发生。
4、根据实验结果你对微软的数据库管理系统的相关策略有什么样的认识？
注：以上内容是可以在一台电脑上完成的，就是打开2个对象资源管理器，分别登录到服务器上，一个执行事务，一个观察结果。建议大家在2台电脑上完成。
5、表teach_schedule的teacher_id 列与表teacher_info的teacher_id列应满足参照完整性规则的。现在在不创建外码的前提下用触发器实现如下功能：当表teach_schedule发生更新或插入表数据时，检查teacher_id列新的值是否在表teacher_info的teacher_id列中已存在，如果已存在则允许修改，如果不存在则拒绝修改。
6、测试上题的触发器是否正确。
四、 实验过程和结果
1. 创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号及平均成绩，并用利用该视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。
use sunting1
go
create view s_g
as
select course_id,AVG(grade) averageGrade from stud_grade
group by course_id

use sunting1
go
select a.course_id,course_name,averageGrade
from lesson_info join a on a.course_id=lesson_info.course_id

2. 创建一个视图，该视图为每门课程的平均成绩，视图包括的列有课程号、课程名及平均成绩，并用利用该视图查询所有课程的平均成绩，要求给出课程号、课程名及平均成绩。
use sunting1
go
create view a1
as
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_name

use sunting1
go
select a1.course_id,course_name,averageGrade
from a1

3. 为院系代码表(dept_code)创建基于“院系代码”列的非聚集索引yxdm_index，其填充因子值为 60。
use sunting1
go
create nonclustered index yxdm_indx
on dept_code(deptcode)
with fillfactor=60
go

4. 为教室信息表(classroom_info) 创建基于room_id列的惟一索引并插入一条room_id列与表中已有的值重复的数据，观察系统的反馈。
use sunting1
go
create unique index room_index
on classroom_info (room_id)

use sunting1
go
insert 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 sunting1
go
alter table stud_info
add constraint pk_stu
primary key (stud_id)

use sunting1
go
alter table lesson_info
add constraint pk_les
primary key (course_id)

use sunting1
go
alter table stud_grade
add constraint pk_sgr
primary key (stud_id,course_id)

在为stud_grade表添加主码约束是，由于course_id之前设定的值可以为null，故执行时出现问题。将course_id更改之后，再重新做一次。截图如下：

②为表stud_grade增加外码，其中stud_id参照stud_info的stud_id，course_id参照lesson_info的course_id。
use sunting1
go
alter table stud_grade
add constraint fk_stud_id
foreign key (stud_id) references stud_info (stud_id)

alter table stud_grade
add constraint fk_course_id
foreign key (course_id) references lesson_info (course_id)

③为表stud_grade增加检查性约束，约束grade列大于等于0且小于等于100。
use sunting1
go
alter table stud_grade
add constraint chk_grade
check (grade>=0 and grade<=100)

6. 使用SQL语句向上题的3张表中插入新的数据，要求分别违反刚刚增加的主码约束、外码约束、检查性约束及原有的非空约束并观察系统的反馈。
use sunting1
go
insert into stud_info ("STUD_ID","NAME","BIRTHDAY",
"GENDER", "ADDRESS","TELCODE","ZIPCODE","MARK")
values ('0401010811','张源','12-05-1986','男','北京市海淀区','010-64572345','100080',560)

use sunting1
go
INSERT INTO stud_grade VALUES('0401050127','张军','0401010106',84)

use sunting1
go
insert into stud_grade VALUES('2012210865','小米','0401010103',152);

use sunting1
go
insert into stud_grade VALUES('2012210865','0401010103',56);

思考题
1. 两名同学一组，各自设计一些简单的事务（对数据的增删改等操作），要求所有的事务以begin transaction开始，这些事务有的以rollback结束、有的以commit结束，有的没有明确的结束语句。
2. 用自己的电脑登录同伴的服务器。在对象资源管理器中执行前边设计的事务。每执行一次事务，合作的同伴在其电脑上通过查询观察这些事务的执行情况。
use sunting1
go
begin transaction
insert into teacher_info
values ('010111','王丽','女',29,'讲师','010-34603790',1600,'0401010112');;
rollback

在另一个事务管理器中查询
use sunting1
go
select teacher_id from teacher_info
where name='王丽'

查询没有结果
use sunting1
go
begin transaction
insert into teacher_info
values ('010111','王丽','女',29,'讲师','010-34603790',1600,'0401010112');;
commit

在另一个对象资源管理器中查询的结果
use sunting1
go
select teacher_id from teacher_info
where name='王丽'

查询结果立即出现
use sunting1
go
begin transaction
delete from teacher_info
where teacher_id='010101'

在另一个对象资源管理器中查询结果如下
use sunting1
go
select teacher_id from teacher_info
where name='刘娜'

查询结果一直没有出现，一直显示正在执行查询
3. 执行完没有明确的结束语句的事务后，在同伴观察结果的期间，选择关闭对象资源管理器，看看有什么情况发生。
关闭的时候的截图

另一个对象资源管理器会立即显示查询已成功执行

以上内容均在同一台电脑上执行完成
4. 根据实验结果你对微软的数据库管理系统的相关策略有什么样的认识？
根据实验结果可以得知，事务具有隔离性、持久性、一致性和原子性，而不同的语句执行的结果也不同，commit是正常结束，rollback是异常结束，就是事务中的全部操作被撤销，而没有结束语句的时候，事务就会一直执行下去而没有结果。
注：以上内容是可以在一台电脑上完成的，就是打开2个对象资源管理器，分别登录到服务器上，一个执行事务，一个观察结果。建议大家在2台电脑上完成。
5. 表teach_schedule的teacher_id 列与表teacher_info的teacher_id列应满足参照完整性规则的。现在在不创建外码的前提下用触发器实现如下功能：当表teach_schedule发生更新或插入表数据时，检查teacher_id列新的值是否在表teacher_info的teacher_id列中已存在，如果已存在则允许修改，如果不存在则拒绝修改。
use sunting1
go
create trigger tri_teacher
on teach_schedule
after insert
as
if not exists (select * from teacher_info
where teacher_id=(select teacher_id from inserted))
rollback

6．测试上题的触发器是否正确。
当插入的tercher_id在teacher_info中存在时，如‘010103’，执行结果成功
use sunting1
go
insert into teach_schedule
values ('0401022102','08-30-2004','14','120703','01','010103');

当插入的teacher_id在teacher_info中不存在时，查询结果不成功
use sunting1
go
insert into teach_schedule
values ('0401022102','08-30-2004','14','120703','01','000111');

五、 实验总结
这次的实验内容主要是掌握SQL Server2008视图、索引的使用，理解什么是数据库的完整性，这一部分我觉得自己开始并没有掌握得很好，遇到了一些问题。例如为表添加外码时，出现了一些问题，当时弄了好久都没有发现问题，询问了同学，又请教了老师。最后在老师的提醒下发现是表中的数据违反了约束。原因是stud_grade里面的stud_id有些在stud_info中没有，违反了外码约束的条件。
通过本次实验，我学会了很多东西，很感谢同学和老师的指导，只要认真学习，没有什么问题是不可以解决的。希望自己可以继续努力，将这门课程学好。