数据库应用实验报告视图存储过程触发器等的建立与维护(共10页).doc

精选优质文档-倾情为你奉上内蒙古工业大学信息工程学院实 验 报 告课程名称： 数据库应用 实验名称： 视图存储过程触发器等的建立与维护 实验类型： 验证性 综合性 设计性实验室名称： 班级： 学号： 姓名： 组别： 同组人： 成绩： 实验日期： 预习报告成绩： 指导教师审核（签名）： 年 月 日预习报告一、实验目的1学会使用企业管理器建立视图，应用视图插入、删除、修改数据；2掌握存储过程的使用方法；3掌握触发器的使用方法。二、实验内容 此实验是综合视图、存储过程、触发器等知识应用的一个综合性实验。实验要求：1利用所创建的数据库和数据表，综合应用视图、存储过程、触发器等知识完善数据库；2掌握应用更新视图数据可以修改基本表数据的方法；3熟练掌握添加、修改、删除记录的存储过程的定义及调用；4掌握通过触发器来实现数据的参照完整性。实验内容要求： 利用员工管理数据库YGGL中 3个表：Employees：员工自然信息表、Departments：部门信息表、Salary：员工薪水情况表。（1） 利用YGGL各表建立视图实现各种连接查询。建立视图view1,查询所有职工的员工编号、姓名、部门名和收入，并按部门名顺序排列。建立视图view2，查询所有职工的员工编号、姓名和平均工资。建立视图view3，查询各部门名和该部门的所有职工平均工资。（2） 编写对YGGL各表进行插入、修改、删除操作的存储过程，然后编写程序，调用这些存储过程。创建一个为Employees表添加员工记录的存储过程addEmployees。创建一个存储过程delEmployees删除Employees表中指定员工编号的记录。（3） 对于YGGL数据库，请用触发器实现两个表间的参照完整性。在表Departments上创建一个触发器Departments _update，当更改部门编号时同步更改Employees表中对应的部门编号。在表Employees上创建一个触发器Employees _delete，当删除员工记录时同步删除salary表中对应的工资收入记录。参考实例步骤： 1.创建视图（1）班级表（U_CLASSES ）：ID含义为"班号",CLASS含义为"班名",DEPARTMENT含义为所在 系,各字段类型按需要设置是否允许为空，ID字段被设置为主键。（2）成绩表（U_SCORES ）：STUDENT_ID含义为学号,COURSE_ID含义为课程号,SCORE为成绩,各字段类型按需要设置是否允许为空，STUDENT_ID 、COURSE_ID字段被设置为主键。（3）课程表（U_COURSES）：COURSE含义为课程名称， ID含义为课程编号，CREDIT含义为课程学分。（4）学生表(U_STUDENTS)，表结构如下: 序号字段类型(长度)主键含义1IDchar(10)是学号2NAMEvarchar(16)姓名3CLASS_IDtinyint所在班级编号1. 用企业管理器建立一个基于学生表、课程表、成绩表的视图，要求该视图显示学号、姓名、课程、成绩。 1) 启动企业管理器、注册、连接 2) 展开服务器、数据库、在视图上右击，在快捷菜单中执行"新建视图(V)." 3) 在新视图窗口内的关系图窗格内右击鼠标，弹出的菜单即为视图设计菜单，执行"添加表(B)." 4) 再在添加表对话框中选择U_SCORES表，再单击添加按钮。 5) 依此操作，分别添加U_STUDENTS、U_COURSES表，单击关闭按钮。 6) 再在关系窗格内，拖动U_STUDENTS表的"ID"至U_SCORES的STUDENT_ID,拖动U_COURSES表的"ID"至U_SCORES的COURSE_ID,再分别选中U_STUDENTS表的"ID","NAME"列(列前的复选框),U_COURSES表的"COURSE"列以及U_SCORES表的"SCORE"列,然后单击"!"按钮,显示视图结果。7) 单击保存按钮,将视图保存为V_SCORES,单击确定。 2. 用查询分析器建立一个基于学生表、班级表的学生视图(V_STUDENTS),包括学号、姓名、班级、系,SQL语句如下： 1) CREATE VIEW dbo.V_STUDENTSASSELECT dbo.U_STUDENTS.ID,dbo.U_STUDENTS.NAME, dbo.U_CLASSES.CLASS, dbo.U_CLASSES.DEPARTMENTFROM dbo.U_STUDENTS INNER JOINdbo.U_CLASSES ON dbo.U_STUDENTS.CLASS_ID = dbo.U_CLASSES.ID 3. 自己写一个SQL语句建立一个基于课程表的视图(V_COURSES)，要求显示课程编号、课程名、学分。 2创建存储过程在查询分析器编辑窗口输入各存储过程的代码并执行以下程序。(1) 添加职员记录的存储过程EmployeeAdd： USE YGGL GO CREATE PROCEDURE Emplo)reeAdd(employeeid char6)，name char(10)，birthday datetime，sex bit，address char(20)，zip char(6)，phonenumber char(12)，emailaddress char(20)，departmenflD char(3) AS BEGIN INSERT INTO Employees VALUES(employeeid，name，birthday，sex，address，zip，phonenumber,emailaddress，departmentlD) END RETURN GO(2) 修改职员记录的存储过程EmployeeUpdate： USE YGGLGO CREATE PROCEDURE EmployeeUpdate (empid char(6)，employeeid char(6)，name char(10)，birthday datetime， sex bit，address char(20)，zip chat(6),phonenumber char(12)， emailaddress char(20)，departmentlD char(3) AS BEGIN UPDATE Employees SET Employeeid=employeeid， Name=name Birthday=birthday， Sex=sex， Address=address Zip=zip， Phonenumber=-phonenumber, Emailaddree=emailaddress DepartmentD=departmenflD WHERE Employeeid=empid END RETURN GO(3) 删除职员记录的存储过程EmployeeDelete： USE YGGL GO CREATE PROCEDURE EmployeeDelete(employeeid char(6) AS BEGIN DELETE FROM EmployeesWHERE Employeeid=employeeid ENDRETURNG03调用存储过程 USE YGGL EXEC EmployeeAdd，刘朝,1,武汉小洪山5号，”，”，”，3 GO USE YGGL EXEC Employeeupdate，,刘平， ,1,武汉小洪山5号，”，”，”，2 GO USE YGGI， EXEC EmployeeDelete GO 分析一下此段程序执行时可能出现哪几种情况。 【思考与练习】 编写如下T-SQL程序：(1) 自定义1个数据类型，用于描述YGGL数据库中的DepartmentlD字段，然后编写代码重新定义数据库各表。(2) 编写对YGGL各表进行插入、修改、删除操作的存储过程，然后，编写l段程序调用这些存储过程。(3)对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列应满足参照完整性规则，请用触发器实现两个表问的参照完整性。 4.创建触发器对于YGGL数据库，表Employees的DepartmentID列与表Departments的DepartmentID列对应满足参照完整性规则，即：（1） 向Employees表添加1条记录时，该记录的DepartmentID值在Departments表中应存在。（2） 修改Departments表DepartmentID 字段值时，该字段在Employees表中的对应值也应修改。（3） 删除Departments表中1条记录时，该记录DepartmentID字段值在Employees表中对应的记录也应删除。对于上述参照完整性规则，在此通过触发器实现。在查询分析器编辑窗口输入各触发器的代码并执行：向Employees表插入或修改1条记录时，通过触发器检查记录的DepartmentID值在Departments表是否存在，若不存在，则取消插入或修改操作。USE YGGLGOCREATE TRIGGER EmployeesIns on dbo.EmployeesFOR INSRET,UPDATEASBEGIN IF(SELECT ins.departmentid from inserted ins)NOT IN (SELECT departmentid FROM departments) ROLLBACK *对当前事务回滚，即恢复到插入前的状态 END 修改Departments表departmentID字段值时，该字段在Employees表中的对应值也进行相应修改。 USE YGGL GO CREATE TRIGGER DepartmentsUpdate on dboDepartments FoR UPDATE AS BEGIN IF(COLUMNS_UPDATED()&01)>0 UPDATE Employees SET DepartmentlD=(SELECT ins.DepartmentlD from INSERTED ins) WHERE DepaxtmentlD=(SELECT DepartmentlD FROM deleted) END GO 删除Departments表中1条记录的同时删除该记录departmentlD字段值在Employees表中对应的记录。 USE YGGL GO CREATE TRIGGER DepartmentsDelete On db.Departments FOR DELETE AS BEGIN DELETE FROM Employees WHERE DepartmentlD=(SELECT DepartmentlD FROM deleted) ENDGO【思考与练习】上述触发器的功能用完整性的方法完成。【思考与练习】 编写如下T-SQL程序：（1） 自定义1个数据类型，用于描述YGGL数据库中的DepartmentlD字段，然后编写代码重新定义数据库各表。（2） 对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。 实验报告成绩： 指导教师审核（签名）： 年 月 日实验报告1. 添加职员记录的存储过程EmployeeAdd： 2. 修改职员记录的存储过程EmployeeUpdate：3. 删除职员记录的存储过程EmployeeDelete4. 调用存储过程5. 向Employees表插入或修改1条记录时，通过触发器检查记录的DepartmentID值在Departments表是否存在，若不存在，则取消插入或修改操作。6.修改Departments表departmentID字段值时，该字段在Employees表中的对应值也进行相应修改。7. 删除Departments表中1条记录的同时删除该记录departmentlD字段值在Employees表中对应的记录专心-专注-专业