數(shù)據(jù)庫課程設(shè)計(jì)題目第9題學(xué)校工資管理系統(tǒng)

1、第 PAGE8 頁 共 NUMPAGES8 頁數(shù)據(jù)庫課程設(shè)計(jì)題目第9題學(xué)校工資管理系統(tǒng)數(shù)據(jù)庫課程設(shè)計(jì)報(bào)告 題目：第9題 學(xué)校的工資管理系統(tǒng) l 實(shí)現(xiàn)部門、職務(wù)、職稱等基本信息的管理；l 實(shí)現(xiàn)教職工信息的管理；l 實(shí)現(xiàn)工資項(xiàng)目的管理，工資項(xiàng)目設(shè)有啟用標(biāo)志和加扣標(biāo)志；l 實(shí)現(xiàn)教職工工資項(xiàng)目及其工資的管理；l 創(chuàng)建觸發(fā)器當(dāng)往教職工工資項(xiàng)目表中插入記錄或刪除記錄時(shí)，自動(dòng)修改該職工的應(yīng)發(fā)工資數(shù)和實(shí)發(fā)工資數(shù)；l 創(chuàng)建存儲(chǔ)過程統(tǒng)計(jì)某個(gè)月各種工資項(xiàng)目的發(fā)放總和；l 創(chuàng)建視圖查詢各個(gè)員工的應(yīng)發(fā)、應(yīng)扣和實(shí)發(fā)工資；l 建立數(shù)據(jù)庫相關(guān)表之間的參照完整性約束。一、 關(guān)系模式設(shè)計(jì) 根據(jù)題意，為實(shí)現(xiàn)所要求的所有功能,此處共

2、設(shè)計(jì)5個(gè)關(guān)系，具體介紹和表的形式如下所示：職工(職工編號(hào)，姓名，性別，年齡，部門，職稱) Tno Tname Tse_ Tage Department Title 職位變更(職工編號(hào)，前職稱，現(xiàn)職稱，變更日期) Tno Title1 Title2 Dates 考勤(職工編號(hào)，加班次數(shù)，缺勤次數(shù)，考勤日期) Tno Overtime Absence Dates 基本工資表(職稱，基本工資) Title Base 工資表(職工編號(hào)，基本工資，加班工資，缺勤扣錢，實(shí)發(fā)工資) Tno Base Overpay Docked Wages 二、 全局E-R圖參照1 職稱 基本工資 基本工資表 參照2 基本

3、工資 得到 職工編號(hào) 工資表 職稱 年齡 部門 加班工資 缺勤扣錢 實(shí)發(fā)工資 加班次數(shù) 缺勤次數(shù) 考勤日期 職工編號(hào) 接收考勤 考勤 變更日期 現(xiàn)職稱 前職稱 職工編號(hào) 職位變更 職位變更記錄 性別 姓名 職工編號(hào) 職工 三、物理設(shè)計(jì) 1、建立數(shù)據(jù)庫，學(xué)校的工資管理系統(tǒng) CREATE DATABASE SchoolSalary 2、建立職工信息表 CREATE TABLE Teacher ( Tno CHAR(20) PRIMARY KEY, /_職工號(hào)_/ Tname CHAR(20) UNIQUE, /_職工名_/ Tse_ CHAR(10) NOT NULL CHECK(Tse_ in(

4、男,女), /_性別_/ Tage INT NOT NULL, /_年齡_/ Depart CHAR(20), /_所屬部門_/ Title CHAR(20) 3、建立職稱變更記錄表 CREATE TABLE Change ( Tno CHAR(20), /_職工號(hào)_/ Title1 CHAR(20), /_之前的職稱_/ Title2 CHAR(20), /_現(xiàn)職稱_/ Dates INT, /_職位變更的月份_/ FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ) 4、建立考勤表 CREATE TABLE Attendance ( Tno CHAR(20

5、), /_職工號(hào)_/ Overtime INT, /_加班次數(shù)_/ Absence INT, /_缺勤次數(shù)_/ Dates INT, /_月份_/ FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ON DELETE CASCADE ON UPDATE CASCADE ) 5、建立基本工資表 CREATE TABLE Refer ( /_工資參照表_/ Title CHAR(20) PRIMARY KEY, /_職位_/ Salary FLOAT, /_對(duì)應(yīng)金額_/ ) 6、建立工資表 CREATE TABLE Pay ( Tno CHAR(20), /_職工

6、號(hào)_/ Base FLOAT, /_基本工資_/ Overpay FLOAT, /_加班費(fèi)_/ Docked FLOAT, /_缺勤扣除工資_/ Wages FLOAT, /_實(shí)際應(yīng)得工資_/ FOREIGN KEY(Tno) REFERENCES Teacher(Tno) ) 三、 數(shù)據(jù)庫完整性設(shè)計(jì) 1、各表名及其對(duì)應(yīng)主鍵 職工(Teacher) 職工編號(hào)(Tno) 職稱變更記錄(Change) 職工編號(hào)(Tno) 考勤(Attendance) 職工編號(hào)(Tno) 基本工資表(Refer) 職稱(Title) 工資表(Pay) 職工編號(hào)(Tno) 2、參照完整性設(shè)計(jì) 2.1、職稱變更記錄(C

7、hange)的職工編號(hào)(Tno)設(shè)為外鍵 FOREIGN KEY (Tno) REFERENCES Teacher(Tno) 2.2、考勤(Attendance)的職工編號(hào)(Tno)設(shè)為外鍵 FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ON DELETE CASCADE ON UPDATE CASCADE 實(shí)現(xiàn)在更新和刪除時(shí)級(jí)聯(lián)操作 2.3、工資表(Pay)的職工編號(hào)(Tno)設(shè)為外鍵 FOREIGN KEY (Tno) REFERENCES Teacher(Tno) 3、CHECK約束設(shè)計(jì) 職工(Teacher)中對(duì)“性別”進(jìn)行CHECK約束：CHEC

8、K(Tse_ in(男,女) 要求性別必須為“男”或“女” 4、觸發(fā)器設(shè)計(jì) 4.1、在職工表中建立職稱變更觸發(fā)器，當(dāng)某職工的職稱發(fā)生變化時(shí)，在職稱變更記錄表中自動(dòng)插入一個(gè)記錄，記錄變更前后的職稱名以及變更日期 CREATE TRIGGER Title_change ON Teacher FOR UPDATE AS BEGIN DECLARE Tno CHAR(20) SELECT Tno=inserted.Tno FROM inserted SELECT _FROM Teacher WHERE Tno=Teacher.Tno DECLARE Title1 CHAR(20) SELECT Tit

9、le1=deleted.Title FROM deleted DECLARE Title2 CHAR(20) SELECT Title2=Teacher.Title FROM Teacher WHERE Tno=Teacher.Tno IF Title1!=Title2 BEGIN INSERT INTO Change(Tno,Title1,Title2) VALUES(Tno,Title1,Title2) END END GO 4.2、在職工表中建立基本工資觸發(fā)器，當(dāng)插入一個(gè)新的職工記錄時(shí)，根據(jù)其職稱并參照基本工資表，在工資表中自動(dòng)更新其基本工資，且默認(rèn)加班工資和缺勤扣錢均為0 CREATE

10、 TRIGGER Basic_pay ON Teacher FOR insert AS BEGIN DECLARE Base FLOAT,tno CHAR(20) SELECT tno=inserted.Tno FROM inserted SELECT Base=Refer.Salary FROM Refer,inserted WHERE Refer.Title=inserted.Title INSERT INTO Pay(Tno,Base,Overpay,Docked,Wages) VALUES(tno,Base,0,0,Base) END GO 4.3、在考勤表中建立考勤工資觸發(fā)器，當(dāng)給一

11、個(gè)職工插入考勤信息后，自動(dòng)在工資表中更新其工資信息，算法里設(shè)計(jì)加班一次加20_塊，缺勤一次扣100塊 CREATE TRIGGER A_pay ON Attendance FOR INSERT AS BEGIN DECLARE tno CHAR(20) DECLARE a FLOAT DECLARE b FLOAT DECLARE c INT DECLARE d INT DECLARE e FLOAT SELECT tno=inserted.Tno FROM inserted SELECT a=Overpay FROM Pay WHERE Pay.Tno=tno SELECT b=Docked

12、 FROM Pay WHERE Pay.Tno=tno SELECT c=Overtime FROM Attendance WHERE Attendance.Tno=tno SELECT d=Absence FROM Attendance WHERE Attendance.Tno=tno SELECT e=Wages FROM Pay WHERE Pay.Tno=tno UPDATE Pay SET Pay.Overpay=a+20_c WHERE Pay.Tno=tno UPDATE Pay SET Pay.Docked=b+100_d WHERE Pay.Tno=tno UPDATE Pa

13、y SET Pay.Wages=e+a+20_c-(b+100_d) WHERE Pay.Tno=tno END 4.4、在職稱變更記錄表中建立基本工資變更觸發(fā)器，當(dāng)某職工職稱變更且記錄在職稱變更記錄表插入記錄后，在工資表中自動(dòng)更新其所有工資信息 CREATE TRIGGER Change_pay ON Change FOR UPDATE,INSERT AS BEGIN DECLARE A CHAR(20) DECLARE B CHAR(20) DECLARE C FLOAT DECLARE D FLOAT SELECT A=inserted.Title2 FROM inserted SELE

14、CT B=inserted.Tno FROM inserted SELECT C=Pay.Overpay FROM Pay,inserted WHERE Pay.Tno=B SELECT D=Pay.Docked FROM Pay,inserted WHERE Pay.Tno=B IF A=普通教師 UPDATE Pay SET Pay.Base=4000,Pay.Wages=4000+C-D WHERE B=Pay.Tno IF A=高級(jí)教師 UPDATE Pay SET Pay.Base=5000,Pay.Wages=5000+C-D WHERE B=Pay.Tno IF A=主任 UPD

15、ATE Pay SET Pay.Base=6000,Pay.Wages=6000+C-D WHERE B=Pay.Tno IF A=校長(zhǎng) UPDATE Pay SET Pay.Base=7000,Pay.Wages=7000+C-D WHERE B=Pay.Tno END GO 五、 數(shù)據(jù)庫視圖設(shè)計(jì) 查看各職工應(yīng)得和實(shí)發(fā)的工資：CREATE VIEW Pay_view AS SELECT Tno,Base+Overpay Gets,Docked,Wages FROM Pay 六、存儲(chǔ)過程設(shè)計(jì) CREATE PROCEDURE ALL_pay AS SELECT SUM(Base) 總基本工資,SUM(Overpay) 總加班工資,SUM(Docked) 總扣工資,SUM(Wages) 總實(shí)發(fā)工資 FROM Pay 七、實(shí)驗(yàn)結(jié)果 (1)基本工資表 各職工的基本工資按各自的職稱參照此表 (2)職工表，以下為添加記錄后的結(jié)果 添加后，Basic_pay觸發(fā)器觸發(fā)，在工資表中自動(dòng)插入所有人的基本工資信息：(3)現(xiàn)將李楠、王峰的職稱都提一級(jí)，吳鵬降一級(jí)，職稱更新后，Title_change觸發(fā)器觸發(fā)，首先職稱變更記錄表中插入相應(yīng)記錄，：然后工資表自動(dòng)更新，以上三人的基本工資改變：(4)在考勤表中添加記錄 隨后A_pay觸發(fā)器觸發(fā)，在工資表中按照加