数据库实验六——视图及索引的创建和操作

实验任务

• 创建和删除视图
  • 创建系统消息表视图message，包括请假单编号、请假时间和审批状态；
  • 在消息表视图message的基础上，创建视图prove_message, 包括所有“审批已通过”的请假单编号；
  • 创建学生选课视图v_stu_lesson,字段包括学号、学生姓名、课程号、课程名称、学分、任课教师工号、任课教师姓名；
  • 创建学分为3的课程视图v_lesson,字段包括课程号、课程名称、学分；
  • 删除视图message，然后查询prove_message的信息，结果如何？
• 视图的消解与更新
  • 对视图v_stu_lesson进行查询操作，查询教师号为3的老师所教授课程的学生选课信息，包含视图所有字段；
  • 对学生表、教师表、课程表、学生选课表进行联合查询操作，查询教师号为3的老师所教授课程的学生选课信息，包括学号、学生姓名、课程号、课程名称、任课教师工号、任课教师姓名；（理解视图消解机制）
  • 更新视图v_stu_lesson，更新课程4的学分为3分，分析更新操作可以执行成功或失败的原因；
  • 更新视图v_stu_lesson，使用update语句将课程号1的任课教师工号修改为4， 能否成功修改，如果不能请分析原因。
  • 更新视图v_stu_lesson，使用update语句将任课教师姓名由‘刘波’改为‘刘海波’；能否成功修改，如果不能请分析原因。
  • 更新视图v_lesson，将学分改为4，分析视图更新操作前后lesson表和v_lesson视图的变化，如何防止出现迁移行？
• 创建和删除索引
  • 在请假单表上为lessonNum列创建索引index_lessonNum；
  • 在学生选课表中在“studentNum”和“lessonNum”属性组上创建唯一索引；
  • 删除索引index_lessonNum

实验过程

Step1: 创建系统消息表视图message，包括请假单编号、请假时间和审批状态

CREATE VIEW message AS
SELECT applyNum, applytime, state FROM leave_apply

Step2：在消息表视图message的基础上，创建视图prove_message, 包括所有“审批已通过”的请假单编号

GO
CREATE VIEW prove_message AS
	SELECT applyNum FROM message
	WHERE state = '审批已通过'
GO

Step3: 创建学生选课视图v_stu_lesson,字段包括学号、学生姓名、课程号、课程名称、学分、任课教师工号、任课教师姓名；

将四张表连接起来后选出我们需要的属性

CREATE VIEW v_stu_lesson AS 
SELECT sc.studentNum, studentName, sc.lessonNum, lessonName, credit, te.teacherNum, teacherName
FROM student st, stu_choose_lesson sc, teacher te, lesson le
	WHERE st.studentNum = sc.studentNum AND sc.lessonNum = le.lessonNum AND le.teacherNum = te.teacherNum

Step4：创建学分为3的课程视图v_lesson,字段包括课程号、课程名称、学分

GO
CREATE VIEW v_lesson AS
SELECT lessonNum, lessonName, credit
	FROM lesson
GO

Step5：删除视图message，然后查询prove_message的信息，结果如何？

DROP VIEW message  
SELECT * FROM prove_message 

由于我们定义prove_message的时候，是在message的基础上定义的，删除message之后，prove_message也会失效。

Step6：对视图v_stu_lesson进行查询操作，查询教师号为3的老师所教授课程的学生选课信息，包含视图所有字段

SELECT * FROM v_stu_lesson   
WHERE teacherNum = '3'  

Step7：对学生表、教师表、课程表、学生选课表进行联合查询操作，查询教师号为3的老师所教授课程的学生选课信息，包括学号、学生姓名、课程号、课程名称、任课教师工号、任课教师姓名；（理解视图消解机制）

对视图的查询，可以转化成对基本表的查询

SELECT sc.studentNum, studentName, sc.lessonNum, lessonName, credit, te.teacherNum, teacherName
FROM student st, stu_choose_lesson sc, teacher te, lesson le
	WHERE st.studentNum = sc.studentNum AND sc.lessonNum = le.lessonNum AND le.teacherNum = te.teacherNum AND te.teacherNum = '3'

Step8：更新视图v_stu_lesson，更新课程4的学分为3分，分析更新操作可以执行成功或失败的原因；

UPDATE v_stu_lesson SET credit = 3 WHERE lessonNum = '4'  

此次的更新操作，可以唯一且有意义地对lesson基本表中的credit属性进行修改，所以视图的更新是有效的。

Step9：更新视图v_stu_lesson，使用update语句将课程号1的任课教师工号修改为4， 能否成功修改，如果不能请分析原因。

UPDATE v_stu_lesson SET teacherNum = 4 WHERE lessonNum = '1'  

执行教师工号修改命令之后，在基本表teacher中，之前任教课程号1的教师会被更改，而由于外键的约束，请假单中的教工号会丢失参照信息。

Step10：更新视图v_stu_lesson，使用update语句将任课教师姓名由‘刘波’改为‘刘海波’；能否成功修改，如果不能请分析原因。

UPDATE v_stu_lesson SET teacherName = '刘海波' WHERE teacherName = '刘波'  

因为我们修改刘波为刘海波时，只有基本表teacher中的teacherName属性会被修改，修改唯一且有意义，所以更新成功。

Step11：更新视图v_lesson，将学分改为4，分析视图更新操作前后lesson表和v_lesson视图的变化，如何防止出现迁移行？

UPDATE v_lesson SET credit = 4

更新前v_lesson表

更新前lesson表

更新后v_lesson表

更新后lesosn表

防止行迁移的出现，可以使用WITH CHECK OPTON，强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则。

Step12: 在请假单表上为lessonNum列创建索引index_lessonNum 

CREATE INDEX index_lessonNum ON leave_apply(lessonNum) 

Step13：在学生选课表中在“studentNum”和“lessonNum”属性组上创建唯一索引；

CREATE UNIQUE INDEX index_choose ON stu_choose_lesson (studentNum, lessonNum)  

Step14：删除索引index_lessonNum

DROP INDEX index_lessonNum ON leave_apply  

实验六Over