大数据库课程设计题目第9题

1、实用文档数据库课程设计报告题目：第9题学校的工资管理系统实现部门、职务、职称等基本信息的管理；实现教职工信息的管理；实现工资项目的管理，工资项目设有启用标志和加扣标志；实现教职工工资项目及其工资的管理；创建触发器当往教职工工资项目表中插入记录或删除记录时，自动修改该职工的应发工资数和实发工资数；创建存储过程统计某个月各种工资项目的发放总和；创建视图查询各个员工的应发、应扣和实发工资；建立数据库相关表之间的参照完整性约束。一、关系模式设计根据题意，为实现所要求的所有功能，此处共设计5个关系，具体介绍和表的形式如下所示：职工（职工编号，姓名，性别，年龄，部门，职称）TnoTnameTsexTage

2、DepartmentTitle职位变更（职工编号，前职称，现职称，变更日期）TnoTitlelTitle2Dates考勤（职工编号，加班次数，缺勤次数，考勤日期）TnoOvertimeAbsenceDates基本工资表（职称，基本工资）TitleBase工资表（职工编号，基本工资，加班工资，缺勤扣钱，实发工资 ）TnoBaseOverpayDockedWages文案大全全局E-R图实用文档三、物理设计1、建立数据库，学校的工资管理系统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、建立职称变更记录表/*职工号*/*之前的职称*/*现职称*/*职位变更的月份*/CREATE TABLE Change ( TnoCHAR(20),TitlelCHAR(20),Title2CHAR(20),DatesINT,FOREIGN KEY (Tno) REFERENCES

4、 Teacher(Tno)4、建立考勤表CREATE TABLE Attendance (TnoCHAR(20),/* 职工号*/OvertimeINT,/* 加班次数*/AbsenceINT,/* 缺勤次数*/DatesINT,/* 月份 */FOREIGN KEY (Tno) REFERENCES Teacher(Tno)ON DELETE CASCADEON UPDATE CASCADE)5、建立基本工资表/* 工资参照表*/* 职位 */* 对应金额*/CREATE TABLE Refer (Title CHAR(20) PRIMARY KEY, Salary FLOAT, )6、建立

5、工资表/* 职工号*/* 基本工资*/* 加班费*/* 缺勤扣除工资*/* 实际应得工资*/CREATE TABLE Pay (TnoCHAR(20),BaseFLOAT,Overpay FLOAT,Docked FLOAT,Wages FLOAT,FOREIGN KEY(Tno) REFERENCES Teacher(Tno) )三、数据库完整性设计1、各表名及其对应主键职工(Teacher)职工编号(Tno)职称变更记录(Change)职工编号(Tno)考勤(Attendance)职工编号(Tno)基本工资表(Refer)职称(Title)工资表(Pay)职工编号(Tno)2、参照完整性设

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实现在更新和删除时级联操作2.3、 工资表(Pay的职工编号(Tno股为外键FOREIGN KEY (Tno) REFERENCES Teacher(Tno)3、CHECK勺束设计职工（Teache冲对“性另进行CHECK勺束:CHECK

7、（Tsex 虑','女'）要求性别必须为“男”或“女”4、触发器设计4.1、 在职工表中建立职称变更触发器，当某职工的职称发生变化时，在职称变更记录表中自动插入一个记录，记录变更前后的职称名以及变更日期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、 在职工表中建立基本工资触发器，当插入一个新的职工记录时，根据其职称并参照基本工资表，在工资表中自动更新其基本工资，且默认加班工资和缺勤扣钱均为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、 在考勤表中建立考勤工资触发器，当给一个职工插入考勤信息后， 自动

10、在工资表中更新其工资信息，算法里设计加班一次加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、 在职称变更记录表中建立基本工资变更触发器，当某职工职称变更且记录在职称变更记录表插入记录后，在工资表中自动更新其所有工资信息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文案大全实用文档五、数据库视图设计查看各职工应得和实发的工资：CREATE VIEW Pay_viewASSELECT Tno,Base+Overpay Gets,Docked,WagesFROM Pay六、存储过程设计CREATE PROCEDURE ALL_payASSELECT SUM(Base)、基本工资，SUM(Overpay)总加班工资，SUM(Docked)总扣工 资,SUM(Wag

15、es)总实发工资FROM Pay七、实验结果(1)基本工资表SaULSchoaKilary - dbo.Refer SODTitleSalary高筑教师5000普通领师4000校长7000主任6W0*NULLNULL各职工的基本工资按各自的职称参照此表(2)职工表，以下为添加记录后的结果S0LA.$chaaK>lary - dba.TMch«r SOLAr$chocJSakat7 - dboPay SOlASchocl&ahry - dboXhange 充金TnoTrwneTsetTageDepartTrHe001李椭女is法学系答谩软师MN王嵯再30融学系嘉及软肺0Q

16、3暴窿男45糊理系主任娥版瞳女30部调系普通勃师张整男售祖省理校长AUENUU4AWNULLNUU添加后，BasijpayM发器触发，在工资表中自动插入所有人的基本工资信息:EOULSchoolSalary dbo.Pay SOLASchcolSaLary - dbo.Ieacher SOLASchooSAkry - dTnoBaseOwpayDockedWws001000004000M2500000sew004«00Q0400000S7DQ000W»0036D0C00MOONUU.f>muNULLNLUAUi(3)现将李楠、王峰的职称都提一级，吴鹏降一级，职称更新后

17、，Title_change触发器触发，首先职称变更记录表中插入相应记录,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触发器触发，在工资表中按照加班一次加 200、缺勤一次扣100自动更新职工的工资信息：5OLA-Schoo(Salary - dbo.Pay SOLA,久hoHS由-dtxj.TS计cr 三/百月苣震要TwOverpayCodedWages0015000心10053000026000f10a066000C4-KK08D0200*0000570000200630000350C0005000*nullnJLLNULLNULLfJU(5)视图结果SOLA. School Salary -dbo.Pa/ viewSOLA- Sc hcolSaljry - dbv-aySOLTroOtsIndeedW的”,001540010053000