1、精選優質文檔-傾情為你奉上數據庫課程設計報告題目：第9題學校的工資管理系統l 實現部門、職務、職稱等基本信息的管理；l 實現教職工信息的管理；l 實現工資項目的管理，工資項目設有啟用標志和加扣標志；l 實現教職工工資項目及其工資的管理；l 創建觸發器當往教職工工資項目表中插入記錄或刪除記錄時，自動修改該職工的應發工資數和實發工資數；l 創建存儲過程統計某個月各種工資項目的發放總和；l 創建視圖查詢各個員工的應發、應扣和實發工資；l 建立數據庫相關表之間的參照完整性約束。一、 關系模式設計根據題意，為實現所要求的所有功能,此處共設計5個關系，具體介紹和表的形式如下所示：職工(職工編號，姓名，性別

2、，年齡，部門，職稱)TnoTnameTsexTageDepartmentTitle職位變更(職工編號，前職稱，現職稱，變更日期)TnoTitle1Title2Dates考勤(職工編號，加班次數，缺勤次數，考勤日期)TnoOvertimeAbsenceDates基本工資表(職稱，基本工資)TitleBase工資表(職工編號，基本工資，加班工資，缺勤扣錢，實發工資)TnoBaseOverpayDockedWages二、 全局E-R圖參照1職稱基本工資基本工資表參照2基本工資得到職工編號工資表職稱年齡部門加班工資缺勤扣錢實發工資加班次數缺勤次數考勤日期職工編號接收考勤考勤變更日期現職稱前職稱職工編號

3、職位變更職位變更記錄性別姓名職工編號職工三、物理設計1、建立數據庫，學校的工資管理系統CREATE DATABASE SchoolSalary2、建立職工信息表CREATE TABLE Teacher (Tno CHAR(20) PRIMARY KEY, /*職工號*/Tname CHAR(20) UNIQUE, /*職工名*/Tsex CHAR(10) NOT NULL CHECK(Tsex in('男','女'), /*性別*/Tage INT NOT NULL, /*年齡*/Depart CHAR(20), /*所屬部門*/Title CHAR(20) 3

4、、建立職稱變更記錄表CREATE TABLE Change (Tno CHAR(20), /*職工號*/Title1 CHAR(20), /*之前的職稱*/Title2 CHAR(20), /*現職稱*/Dates INT, /*職位變更的月份*/FOREIGN KEY (Tno) REFERENCES Teacher(Tno) 4、建立考勤表CREATE TABLE Attendance ( Tno CHAR(20), /*職工號*/Overtime INT, /*加班次數*/Absence INT, /*缺勤次數*/Dates INT, /*月份*/FOREIGN KEY (Tno) RE

5、FERENCES Teacher(Tno) ON DELETE CASCADE ON UPDATE CASCADE)5、建立基本工資表CREATE TABLE Refer ( /*工資參照表*/Title CHAR(20) PRIMARY KEY, /*職位*/Salary FLOAT, /*對應金額*/)6、建立工資表CREATE TABLE Pay (Tno CHAR(20), /*職工號*/Base FLOAT, /*基本工資*/Overpay FLOAT, /*加班費*/Docked FLOAT, /*缺勤扣除工資*/Wages FLOAT, /*實際應得工資*/FOREIGN KEY

6、(Tno) REFERENCES Teacher(Tno)三、 數據庫完整性設計1、各表名及其對應主鍵職工(Teacher)職工編號(Tno)職稱變更記錄(Change)職工編號(Tno)考勤(Attendance)職工編號(Tno)基本工資表(Refer)職稱(Title)工資表(Pay)職工編號(Tno)2、參照完整性設計2.1、職稱變更記錄(Change)的職工編號(Tno)設為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)2.2、考勤(Attendance)的職工編號(Tno)設為外鍵FOREIGN KEY (Tno) REFERENCES Te

7、acher(Tno) ON DELETE CASCADEON UPDATE CASCADE實現在更新和刪除時級聯操作2.3、工資表(Pay)的職工編號(Tno)設為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)3、CHECK約束設計職工(Teacher)中對“性別”進行CHECK約束：CHECK(Tsex in('男','女')要求性別必須為“男”或“女”4、觸發器設計4.1、在職工表中建立職稱變更觸發器，當某職工的職稱發生變化時，在職稱變更記錄表中自動插入一個記錄，記錄變更前后的職稱名以及變更日期CREATE TRIGG

8、ER Title_changeON TeacherFOR UPDATEASBEGINDECLARE Tno CHAR(20)SELECT Tno=inserted.Tno FROM insertedSELECT * FROM Teacher WHERE Tno=Teacher.TnoDECLARE Title1 CHAR(20)SELECT Title1=deleted.Title FROM deletedDECLARE Title2 CHAR(20)SELECT Title2=Teacher.Title FROM Teacher WHERE Tno=Teacher.TnoIF Title1!

9、=Title2 BEGIN INSERT INTO Change(Tno,Title1,Title2) VALUES(Tno,Title1,Title2) ENDENDGO4.2、在職工表中建立基本工資觸發器，當插入一個新的職工記錄時，根據其職稱并參照基本工資表，在工資表中自動更新其基本工資，且默認加班工資和缺勤扣錢均為0CREATE TRIGGER Basic_payON TeacherFOR insertASBEGINDECLARE Base FLOAT,tno CHAR(20)SELECT tno=inserted.Tno FROM insertedSELECT Base=Refer.S

10、alary FROM Refer,inserted WHERE Refer.Title=inserted.TitleINSERT INTO Pay(Tno,Base,Overpay,Docked,Wages) VALUES(tno,Base,0,0,Base)ENDGO4.3、在考勤表中建立考勤工資觸發器，當給一個職工插入考勤信息后，自動在工資表中更新其工資信息，算法里設計加班一次加200塊，缺勤一次扣100塊CREATE TRIGGER A_payON AttendanceFOR INSERTASBEGINDECLARE tno CHAR(20)DECLARE a FLOATDECLARE

11、b FLOATDECLARE c INTDECLARE d INTDECLARE e FLOATSELECT tno=inserted.Tno FROM insertedSELECT a=Overpay FROM Pay WHERE Pay.Tno=tnoSELECT b=Docked FROM Pay WHERE Pay.Tno=tnoSELECT c=Overtime FROM Attendance WHERE Attendance.Tno=tnoSELECT d=Absence FROM Attendance WHERE Attendance.Tno=tnoSELECT e=Wages

12、FROM Pay WHERE Pay.Tno=tnoUPDATE Pay SET Pay.Overpay=a+200*c WHERE Pay.Tno=tnoUPDATE Pay SET Pay.Docked=b+100*d WHERE Pay.Tno=tnoUPDATE Pay SET Pay.Wages=e+a+200*c-(b+100*d) WHERE Pay.Tno=tnoEND4.4、在職稱變更記錄表中建立基本工資變更觸發器，當某職工職稱變更且記錄在職稱變更記錄表插入記錄后，在工資表中自動更新其所有工資信息CREATE TRIGGER Change_payON ChangeFOR UP

13、DATE,INSERTASBEGINDECLARE A CHAR(20)DECLARE B CHAR(20)DECLARE C FLOATDECLARE D FLOATSELECT A=inserted.Title2 FROM insertedSELECT B=inserted.Tno FROM insertedSELECT C=Pay.Overpay FROM Pay,inserted WHERE Pay.Tno=BSELECT D=Pay.Docked FROM Pay,inserted WHERE Pay.Tno=BIF A='普通教師'UPDATE Pay SET Pa

14、y.Base=4000,Pay.Wages=4000+C-D WHERE B=Pay.TnoIF A='高級教師'UPDATE Pay SET Pay.Base=5000,Pay.Wages=5000+C-D WHERE B=Pay.TnoIF A='主任'UPDATE Pay SET Pay.Base=6000,Pay.Wages=6000+C-D WHERE B=Pay.TnoIF A='校長'UPDATE Pay SET Pay.Base=7000,Pay.Wages=7000+C-D WHERE B=Pay.TnoENDGO五、 數據庫視

15、圖設計查看各職工應得和實發的工資：CREATE VIEW Pay_viewASSELECT Tno,Base+Overpay Gets,Docked,WagesFROM Pay六、存儲過程設計CREATE PROCEDURE ALL_payASSELECT SUM(Base) 總基本工資,SUM(Overpay) 總加班工資,SUM(Docked) 總扣工資,SUM(Wages) 總實發工資FROM Pay七、實驗結果(1)基本工資表各職工的基本工資按各自的職稱參照此表(2)職工表，以下為添加記錄后的結果添加后，Basic_pay觸發器觸發，在工資表中自動插入所有人的基本工資信息：(3)現將李楠、王峰的職稱都提一級，吳鵬降一級，職稱更新后，Title_change觸發器觸發，首先職稱變更記錄表中插入相應記錄，：然后工資表自動更新，以上三人的基本工資改變：(4)在考勤表中添加記錄隨后A_pay觸發器觸發，在工資表中按照加班一次加200、缺勤一次扣1