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

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫(kù)課程設(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ǔ)過(guò)程統(tǒng)計(jì)某個(gè)月各種工資項(xiàng)目的發(fā)放總和；l 創(chuàng)建視圖查詢各個(gè)員工的應(yīng)發(fā)、應(yīng)扣和實(shí)發(fā)工資；l 建立數(shù)據(jù)庫(kù)相關(guān)表之間的參照完整性約束。一、 關(guān)系模式設(shè)計(jì)根據(jù)題意，為實(shí)現(xiàn)所要求的所有功能,此處共設(shè)計(jì)5個(gè)關(guān)系，具體介紹和表的形式如下所示：職工(職工編號(hào)，姓名，性別

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

3、職位變更職位變更記錄性別姓名職工編號(hào)職工三、物理設(shè)計(jì)1、建立數(shù)據(jù)庫(kù)，學(xué)校的工資管理系統(tǒng)CREATE DATABASE SchoolSalary2、建立職工信息表CREATE TABLE Teacher (Tno CHAR(20) PRIMARY KEY, /*職工號(hào)*/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), /*職工號(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), /*職工號(hào)*/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, /*對(duì)應(yīng)金額*/)6、建立工資表CREATE TABLE Pay (Tno CHAR(20), /*職工號(hào)*/Base FLOAT, /*基本工資*/Overpay FLOAT, /*加班費(fèi)*/Docked FLOAT, /*缺勤扣除工資*/Wages FLOAT, /*實(shí)際應(yīng)得工資*/FOREIGN KEY

6、(Tno) REFERENCES Teacher(Tno)三、 數(shù)據(jù)庫(kù)完整性設(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、職稱變更記錄(Change)的職工編號(hào)(Tno)設(shè)為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)2.2、考勤(Attendance)的職工編號(hào)(Tno)設(shè)為外鍵FOREIGN KEY (Tno) REFERENCES Te

7、acher(Tno) ON DELETE CASCADEON 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約束：CHECK(Tsex in('男','女')要求性別必須為“男”或“女”4、觸發(fā)器設(shè)計(jì)4.1、在職工表中建立職稱變更觸發(fā)器，當(dāng)某職工的職稱發(fā)生變化時(shí)，在職稱變更記錄表中自動(dòng)插入一個(gè)記錄，記錄變更前后的職稱名以及變更日期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ā)器，當(dāng)插入一個(gè)新的職工記錄時(shí)，根據(jù)其職稱并參照基本工資表，在工資表中自動(dòng)更新其基本工資，且默認(rèn)加班工資和缺勤扣錢均為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ā)器，當(dāng)給一個(gè)職工插入考勤信息后，自動(dòng)在工資表中更新其工資信息，算法里設(shè)計(jì)加班一次加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ā)器，當(dāng)某職工職稱變更且記錄在職稱變更記錄表插入記錄后，在工資表中自動(dòng)更新其所有工資信息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='高級(jí)教師'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='校長(zhǎng)'UPDATE Pay SET Pay.Base=7000,Pay.Wages=7000+C-D WHERE B=Pay.TnoENDGO五、 數(shù)據(jù)庫(kù)視

15、圖設(shè)計(jì)查看各職工應(yīng)得和實(shí)發(fā)的工資：CREATE VIEW Pay_viewASSELECT Tno,Base+Overpay Gets,Docked,WagesFROM Pay六、存儲(chǔ)過(guò)程設(shè)計(jì)CREATE PROCEDURE ALL_payASSELECT 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ā)，在工資表中按照加班一次加200、缺勤一次扣1