數(shù)據(jù)庫課程設計題目第9題

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

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

3、職位變更職位變更記錄性別姓名職工編號職工三、物理設計1、建立數(shù)據(jù)庫，學校的工資管理系統(tǒng)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), /*現(xiàn)職稱*/Dates INT, /*職位變更的月份*/FOREIGN KEY (Tno) REFERENCES Teacher(Tno) 4、建立考勤表CREATE TABLE Attendance ( Tno CHAR(20), /*職工號*/Overtime INT, /*加班次數(shù)*/Absence INT, /*缺勤次數(shù)*/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)三、 數(shù)據(jù)庫完整性設計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實現(xiàn)在更新和刪除時級聯(lián)操作2.3、工資表(Pay)的職工編號(Tno)設為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)3、CHECK約束設計職工(Teacher)中對“性別”進行CHECK約束：CHECK(Tsex in('男','女')要求性別必須為“男”或“女”4、觸發(fā)器設計4.1、在職工表中建立職稱變更觸發(fā)器，當某職工的職稱發(fā)生變化時，在職稱變更記錄表中自動插入一個記錄，記錄變更前后的職稱名以及變更日期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、在職工表中建立基本工資觸發(fā)器，當插入一個新的職工記錄時，根據(jù)其職稱并參照基本工資表，在工資表中自動更新其基本工資，且默認加班工資和缺勤扣錢均為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、在考勤表中建立考勤工資觸發(fā)器，當給一個職工插入考勤信息后，自動在工資表中更新其工資信息，算法里設計加班一次加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、在職稱變更記錄表中建立基本工資變更觸發(fā)器，當某職工職稱變更且記錄在職稱變更記錄表插入記錄后，在工資表中自動更新其所有工資信息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五、 數(shù)據(jù)庫視

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