实验任务

  • 创建和删除视图
    • 创建系统消息表视图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


立志做一名攻城狮