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

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

2、DepartmentTitle職位變更（職工編號，前職稱，現(xiàn)職稱，變更日期）TnoTitlelTitle2Dates考勤（職工編號，加班次數(shù)，缺勤次數(shù)，考勤日期）TnoOvertimeAbsenceDates基本工資表（職稱，基本工資）TitleBase工資表（職工編號，基本工資，加班工資，缺勤扣錢，實發(fā)工資 ）TnoBaseOverpayDockedWages文案大全全局E-R圖實用文檔三、物理設(shè)計1、建立數(shù)據(jù)庫，學(xué)校的工資管理系統(tǒng)CREATE DATABASE SchoolSalary文案大全2、建立職工信息表CREATE TABLE Teacher (/*職工號*/*職工名*/*性別*/

3、*年齡*/*所屬部門*/Tno CHAR(20) PRIMARY KEY,Tname CHAR(20) UNIQUE,Tsex CHAR(10) NOT NULL CHECK(Tsex 用(；'女'),Tage INT NOT NULL,Depart CHAR(20),Title CHAR(20)3、建立職稱變更記錄表/*職工號*/*之前的職稱*/*現(xiàn)職稱*/*職位變更的月份*/CREATE TABLE Change ( TnoCHAR(20),TitlelCHAR(20),Title2CHAR(20),DatesINT,FOREIGN KEY (Tno) REFERENCES

4、 Teacher(Tno)4、建立考勤表CREATE TABLE Attendance (TnoCHAR(20),/* 職工號*/OvertimeINT,/* 加班次數(shù)*/AbsenceINT,/* 缺勤次數(shù)*/DatesINT,/* 月份 */FOREIGN KEY (Tno) REFERENCES Teacher(Tno)ON DELETE CASCADEON UPDATE CASCADE)5、建立基本工資表/* 工資參照表*/* 職位 */* 對應(yīng)金額*/CREATE TABLE Refer (Title CHAR(20) PRIMARY KEY, Salary FLOAT, )6、建立

5、工資表/* 職工號*/* 基本工資*/* 加班費*/* 缺勤扣除工資*/* 實際應(yīng)得工資*/CREATE TABLE Pay (TnoCHAR(20),BaseFLOAT,Overpay FLOAT,Docked FLOAT,Wages FLOAT,FOREIGN KEY(Tno) REFERENCES Teacher(Tno) )三、數(shù)據(jù)庫完整性設(shè)計1、各表名及其對應(yīng)主鍵職工(Teacher)職工編號(Tno)職稱變更記錄(Change)職工編號(Tno)考勤(Attendance)職工編號(Tno)基本工資表(Refer)職稱(Title)工資表(Pay)職工編號(Tno)2、參照完整性設(shè)

6、計2.1、 職稱變更記錄(Change)勺職工編號(Tno股為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)2.2、 考勤(Attendance)的職工編號(Tno成為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)ON DELETE CASCADEON UPDATE CASCADE實現(xiàn)在更新和刪除時級聯(lián)操作2.3、 工資表(Pay的職工編號(Tno股為外鍵FOREIGN KEY (Tno) REFERENCES Teacher(Tno)3、CHECK勺束設(shè)計職工（Teache沖對“性另進行CHECK勺束:CHECK

7、（Tsex 慮','女'）要求性別必須為“男”或“女”4、觸發(fā)器設(shè)計4.1、 在職工表中建立職稱變更觸發(fā)器，當某職工的職稱發(fā)生變化時，在職稱變更記錄表中自動插入一個記錄，記錄變更前后的職稱名以及變更日期CREATE TRIGGER Title_changeON TeacherFOR UPDATEASBEGINDECLARE Tno CHAR(20)SELECT Tno=inserted.Tno FROM insertedSELECT * FROM Teacher WHERE Tno=Teacher.TnoDECLARE Title1 CHAR(20)SELECT Tit

8、le1=deleted.Title FROM deletedDECLARE Title2 CHAR(20)SELECT Title2=Teacher.Title FROM Teacher WHERE Tno=Teacher.TnoIF Title1!=Title2BEGININSERT INTO Change(Tno,Title1,Title2) VALUES(Tno,Title1,Title2)ENDENDGO實用文檔4.2、 在職工表中建立基本工資觸發(fā)器，當插入一個新的職工記錄時，根據(jù)其職稱并參照基本工資表，在工資表中自動更新其基本工資，且默認加班工資和缺勤扣錢均為0CREATE TRIG

9、GER Basic_payON TeacherFOR insertASBEGINDECLARE Base FLOAT,tno CHAR(20)SELECT tno=inserted.Tno FROM insertedSELECT Base=Refer.Salary FROM Refer,inserted WHERE Refer.Title=inserted.TitleINSERT INTO Pay(Tno,Base,Overpay,Docked,Wages) VALUES(tno,Base,0,0,Base) ENDGO4.3、 在考勤表中建立考勤工資觸發(fā)器，當給一個職工插入考勤信息后， 自動

10、在工資表中更新其工資信息，算法里設(shè)計加班一次加200 塊，缺勤一次扣100 塊CREATE TRIGGER A_payON AttendanceFOR INSERTASBEGINDECLARE tno CHAR(20)DECLARE a FLOATDECLARE 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=t

11、noSELECT c=Overtime FROM Attendance WHERE Attendance.Tno=tnoSELECT d=Absence FROM Attendance WHERE Attendance.Tno=tnoSELECT e=Wages 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+1

12、00*d) WHERE Pay.Tno=tno END4.4、 在職稱變更記錄表中建立基本工資變更觸發(fā)器，當某職工職稱變更且記錄在職稱變更記錄表插入記錄后，在工資表中自動更新其所有工資信息CREATE TRIGGER Change_payON ChangeFOR UPDATE,INSERTASBEGINDECLARE A CHAR(20)DECLARE B CHAR(20)DECLARE C FLOATDECLARE D FLOATSELECT A=inserted.Title2 FROM insertedSELECT B=inserted.Tno FROM insertedSELECT C=

13、Pay.Overpay FROM Pay,inserted WHERE Pay.Tno=BSELECT D=Pay.Docked FROM Pay,inserted WHERE Pay.Tno=BIF A=t通教師'UPDATE Pay SET Pay.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=60

14、00+C-D WHERE B=Pay.Tno IF A=$ 長UPDATE Pay SET Pay.Base=7000,Pay.Wages=7000+C-D WHERE B=Pay.Tno ENDGO文案大全實用文檔五、數(shù)據(jù)庫視圖設(shè)計查看各職工應(yīng)得和實發(fā)的工資：CREATE VIEW Pay_viewASSELECT Tno,Base+Overpay Gets,Docked,WagesFROM Pay六、存儲過程設(shè)計CREATE PROCEDURE ALL_payASSELECT SUM(Base)、基本工資，SUM(Overpay)總加班工資，SUM(Docked)總扣工 資,SUM(Wag

15、es)總實發(fā)工資FROM Pay七、實驗結(jié)果(1)基本工資表SaULSchoaKilary - dbo.Refer SODTitleSalary高筑教師5000普通領(lǐng)師4000校長7000主任6W0*NULLNULL各職工的基本工資按各自的職稱參照此表(2)職工表，以下為添加記錄后的結(jié)果S0LA.$chaaK>lary - dba.TMch«r SOLAr$chocJSakat7 - dboPay SOlASchocl&ahry - dboXhange 充金TnoTrwneTsetTageDepartTrHe001李橢女is法學(xué)系答謾軟師MN王嵯再30融學(xué)系嘉及軟肺0Q

16、3暴窿男45糊理系主任娥版瞳女30部調(diào)系普通勃師張整男售祖省理校長AUENUU4AWNULLNUU添加后，BasijpayM發(fā)器觸發(fā)，在工資表中自動插入所有人的基本工資信息:EOULSchoolSalary dbo.Pay SOLASchcolSaLary - dbo.Ieacher SOLASchooSAkry - dTnoBaseOwpayDockedWws001000004000M2500000sew004«00Q0400000S7DQ000W»0036D0C00MOONUU.f>muNULLNLUAUi(3)現(xiàn)將李楠、王峰的職稱都提一級，吳鵬降一級，職稱更新后

17、，Title_change觸發(fā)器觸發(fā)，首先職稱變更記錄表中插入相應(yīng)記錄,SOULSchooKalary - dbo.ChangeSOLA,£chool&alary - dbo.Tecber 7TnoTitieiTnieiDdtesMt普通教師M2高級教師003主任高級教師1iff1高正軟1市1*NUUNULLAU£ML然后工資表自動更新，以上三人的基本工資改變:5OLA-Schopl5alary - dbo.Pay SOLA.ScnooiSi abo.Teacner 金三原胃胃蔻TnoBjscOverpayDeckedWagesOCL500000MOO0026D0C

18、00MOO0044000Q0400000570000QTOOT003500C00soooNUUAWNUUNULL(4)在考勤表中添加記錄SOLA.S£hool5alary dbo.Attendance- dbo.TeatbeTnoOverbrrieAbsenceDatesW121100230L(KUQ01004471005021nullnullNULLNULL隨后A_pay觸發(fā)器觸發(fā)，在工資表中按照加班一次加 200、缺勤一次扣100自動更新職工的工資信息：5OLA-Schoo(Salary - dbo.Pay SOLA,久hoHS由-dtxj.TS計cr 三/百月苣震要TwOverpayCodedWages0015000心10053000026000f10a066000C4-KK08D0200*0000570000200630000350C0005000*nullnJLLNULLNULLfJU(5)視圖結(jié)果SOLA. School Salary -dbo.Pa/ viewSOLA- Sc hcolSaljry - dbv-aySOLTroOtsIndeedW的”,001540010053000