數(shù)據(jù)庫應(yīng)用實(shí)驗(yàn)報(bào)告視圖存儲(chǔ)過程觸發(fā)器等的建立與維護(hù)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上內(nèi)蒙古工業(yè)大學(xué)信息工程學(xué)院實(shí) 驗(yàn) 報(bào) 告課程名稱： 數(shù)據(jù)庫應(yīng)用 實(shí)驗(yàn)名稱： 視圖存儲(chǔ)過程觸發(fā)器等的建立與維護(hù) 實(shí)驗(yàn)類型： 驗(yàn)證性 綜合性 設(shè)計(jì)性實(shí)驗(yàn)室名稱： 班級(jí)： 學(xué)號(hào)： 姓名： 組別： 同組人： 成績(jī)： 實(shí)驗(yàn)日期： 預(yù)習(xí)報(bào)告成績(jī)： 指導(dǎo)教師審核（簽名）： 年 月 日預(yù)習(xí)報(bào)告一、實(shí)驗(yàn)?zāi)康?學(xué)會(huì)使用企業(yè)管理器建立視圖，應(yīng)用視圖插入、刪除、修改數(shù)據(jù)；2掌握存儲(chǔ)過程的使用方法；3掌握觸發(fā)器的使用方法。二、實(shí)驗(yàn)內(nèi)容 此實(shí)驗(yàn)是綜合視圖、存儲(chǔ)過程、觸發(fā)器等知識(shí)應(yīng)用的一個(gè)綜合性實(shí)驗(yàn)。實(shí)驗(yàn)要求：1利用所創(chuàng)建的數(shù)據(jù)庫和數(shù)據(jù)表，綜合應(yīng)用視圖、存儲(chǔ)過程、觸發(fā)器等知識(shí)完善數(shù)據(jù)庫；2掌握應(yīng)用

2、更新視圖數(shù)據(jù)可以修改基本表數(shù)據(jù)的方法；3熟練掌握添加、修改、刪除記錄的存儲(chǔ)過程的定義及調(diào)用；4掌握通過觸發(fā)器來實(shí)現(xiàn)數(shù)據(jù)的參照完整性。實(shí)驗(yàn)內(nèi)容要求： 利用員工管理數(shù)據(jù)庫YGGL中 3個(gè)表：Employees：?jiǎn)T工自然信息表、Departments：部門信息表、Salary：?jiǎn)T工薪水情況表。（1） 利用YGGL各表建立視圖實(shí)現(xiàn)各種連接查詢。建立視圖view1,查詢所有職工的員工編號(hào)、姓名、部門名和收入，并按部門名順序排列。建立視圖view2，查詢所有職工的員工編號(hào)、姓名和平均工資。建立視圖view3，查詢各部門名和該部門的所有職工平均工資。（2） 編寫對(duì)YGGL各表進(jìn)行插入、修改、刪除操作的存儲(chǔ)過

3、程，然后編寫程序，調(diào)用這些存儲(chǔ)過程。創(chuàng)建一個(gè)為Employees表添加員工記錄的存儲(chǔ)過程addEmployees。創(chuàng)建一個(gè)存儲(chǔ)過程delEmployees刪除Employees表中指定員工編號(hào)的記錄。（3） 對(duì)于YGGL數(shù)據(jù)庫，請(qǐng)用觸發(fā)器實(shí)現(xiàn)兩個(gè)表間的參照完整性。在表Departments上創(chuàng)建一個(gè)觸發(fā)器Departments _update，當(dāng)更改部門編號(hào)時(shí)同步更改Employees表中對(duì)應(yīng)的部門編號(hào)。在表Employees上創(chuàng)建一個(gè)觸發(fā)器Employees _delete，當(dāng)刪除員工記錄時(shí)同步刪除salary表中對(duì)應(yīng)的工資收入記錄。參考實(shí)例步驟： 1.創(chuàng)建視圖（1）班級(jí)表（U_CLASSE

4、S ）：ID含義為"班號(hào)",CLASS含義為"班名",DEPARTMENT含義為所在 系,各字段類型按需要設(shè)置是否允許為空，ID字段被設(shè)置為主鍵。（2）成績(jī)表（U_SCORES ）：STUDENT_ID含義為學(xué)號(hào),COURSE_ID含義為課程號(hào),SCORE為成績(jī),各字段類型按需要設(shè)置是否允許為空，STUDENT_ID 、COURSE_ID字段被設(shè)置為主鍵。（3）課程表（U_COURSES）：COURSE含義為課程名稱， ID含義為課程編號(hào)，CREDIT含義為課程學(xué)分。（4）學(xué)生表(U_STUDENTS)，表結(jié)構(gòu)如下: 序號(hào)字段類型(長(zhǎng)度)主鍵含義1IDc

5、har(10)是學(xué)號(hào)2NAMEvarchar(16)姓名3CLASS_IDtinyint所在班級(jí)編號(hào)1. 用企業(yè)管理器建立一個(gè)基于學(xué)生表、課程表、成績(jī)表的視圖，要求該視圖顯示學(xué)號(hào)、姓名、課程、成績(jī)。 1) 啟動(dòng)企業(yè)管理器、注冊(cè)、連接 2) 展開服務(wù)器、數(shù)據(jù)庫、在視圖上右擊，在快捷菜單中執(zhí)行"新建視圖(V)." 3) 在新視圖窗口內(nèi)的關(guān)系圖窗格內(nèi)右擊鼠標(biāo)，彈出的菜單即為視圖設(shè)計(jì)菜單，執(zhí)行"添加表(B)." 4) 再在添加表對(duì)話框中選擇U_SCORES表，再單擊添加按鈕。 5) 依此操作，分別添加U_STUDENTS、U_COURSES表，單擊關(guān)閉按鈕。 6

6、) 再在關(guān)系窗格內(nèi)，拖動(dòng)U_STUDENTS表的"ID"至U_SCORES的STUDENT_ID,拖動(dòng)U_COURSES表的"ID"至U_SCORES的COURSE_ID,再分別選中U_STUDENTS表的"ID","NAME"列(列前的復(fù)選框),U_COURSES表的"COURSE"列以及U_SCORES表的"SCORE"列,然后單擊"!"按鈕,顯示視圖結(jié)果。7) 單擊保存按鈕,將視圖保存為V_SCORES,單擊確定。 2. 用查詢分析器建立一個(gè)基于學(xué)生

7、表、班級(jí)表的學(xué)生視圖(V_STUDENTS),包括學(xué)號(hào)、姓名、班級(jí)、系,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. 自己寫一個(gè)SQL語句建立一個(gè)基于課程表的視圖(V_COURSES)，要求顯示課程

8、編號(hào)、課程名、學(xué)分。 2創(chuàng)建存儲(chǔ)過程在查詢分析器編輯窗口輸入各存儲(chǔ)過程的代碼并執(zhí)行以下程序。(1) 添加職員記錄的存儲(chǔ)過程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 VA

9、LUES(employeeid，name，birthday，sex，address，zip，phonenumber,emailaddress，departmentlD) END RETURN GO(2) 修改職員記錄的存儲(chǔ)過程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)， em

10、ailaddress 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) 刪除職員記錄的存儲(chǔ)過程EmployeeDelete：

11、USE YGGL GO CREATE PROCEDURE EmployeeDelete(employeeid char(6) AS BEGIN DELETE FROM EmployeesWHERE Employeeid=employeeid ENDRETURNG03調(diào)用存儲(chǔ)過程 USE YGGL EXEC EmployeeAdd，劉朝,1,武漢小洪山5號(hào)，”，”，”，3 GO USE YGGL EXEC Employeeupdate，,劉平， ,1,武漢小洪山5號(hào)，”，”，”，2 GO USE YGGI， EXEC EmployeeDelete GO 分析一下此段程序執(zhí)行時(shí)可能出現(xiàn)哪幾種情況。

12、 【思考與練習(xí)】 編寫如下T-SQL程序：(1) 自定義1個(gè)數(shù)據(jù)類型，用于描述YGGL數(shù)據(jù)庫中的DepartmentlD字段，然后編寫代碼重新定義數(shù)據(jù)庫各表。(2) 編寫對(duì)YGGL各表進(jìn)行插入、修改、刪除操作的存儲(chǔ)過程，然后，編寫l段程序調(diào)用這些存儲(chǔ)過程。(3)對(duì)于YGGL數(shù)據(jù)庫，表Employees的EmployeelD列與表Salary的EmployeelD列應(yīng)滿足參照完整性規(guī)則，請(qǐng)用觸發(fā)器實(shí)現(xiàn)兩個(gè)表問的參照完整性。 4.創(chuàng)建觸發(fā)器對(duì)于YGGL數(shù)據(jù)庫，表Employees的DepartmentID列與表Departments的DepartmentID列對(duì)應(yīng)滿足參照完整性規(guī)則，即：（1） 向

13、Employees表添加1條記錄時(shí)，該記錄的DepartmentID值在Departments表中應(yīng)存在。（2） 修改Departments表DepartmentID 字段值時(shí)，該字段在Employees表中的對(duì)應(yīng)值也應(yīng)修改。（3） 刪除Departments表中1條記錄時(shí)，該記錄DepartmentID字段值在Employees表中對(duì)應(yīng)的記錄也應(yīng)刪除。對(duì)于上述參照完整性規(guī)則，在此通過觸發(fā)器實(shí)現(xiàn)。在查詢分析器編輯窗口輸入各觸發(fā)器的代碼并執(zhí)行：向Employees表插入或修改1條記錄時(shí)，通過觸發(fā)器檢查記錄的DepartmentID值在Departments表是否存在，若不存在，則取消插入或修改操

14、作。USE YGGLGOCREATE TRIGGER EmployeesIns on dbo.EmployeesFOR INSRET,UPDATEASBEGIN IF(SELECT ins.departmentid from inserted ins)NOT IN (SELECT departmentid FROM departments) ROLLBACK *對(duì)當(dāng)前事務(wù)回滾，即恢復(fù)到插入前的狀態(tài) END 修改Departments表departmentID字段值時(shí)，該字段在Employees表中的對(duì)應(yīng)值也進(jìn)行相應(yīng)修改。 USE YGGL GO CREATE TRIGGER Departmen

15、tsUpdate 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條記錄的同時(shí)刪除該記錄departmentlD字段值在Employees表中對(duì)應(yīng)的記錄。 USE YGGL GO CREATE

16、TRIGGER DepartmentsDelete On db.Departments FOR DELETE AS BEGIN DELETE FROM Employees WHERE DepartmentlD=(SELECT DepartmentlD FROM deleted) ENDGO【思考與練習(xí)】上述觸發(fā)器的功能用完整性的方法完成。【思考與練習(xí)】 編寫如下T-SQL程序：（1） 自定義1個(gè)數(shù)據(jù)類型，用于描述YGGL數(shù)據(jù)庫中的DepartmentlD字段，然后編寫代碼重新定義數(shù)據(jù)庫各表。（2） 對(duì)于YGGL數(shù)據(jù)庫，表Employees的EmployeelD列與表Salary的EmployeelD列應(yīng)滿足參照完整性規(guī)則，請(qǐng)用觸發(fā)器實(shí)現(xiàn)兩個(gè)表間的參照完整性。 實(shí)驗(yàn)報(bào)告成績(jī)： 指導(dǎo)教師審核（簽名）： 年 月 日實(shí)驗(yàn)報(bào)告1. 添加職員記錄的存儲(chǔ)過程EmployeeAdd： 2. 修改職員記錄的存儲(chǔ)過程Employ