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

数据库课程设计报告题目：第9题学校的工资管理系统●实现部门、职务、职称等基本信息的管理；●实现教职工信息的管理；●实现工资项目的管理，工资项目设有启用标志和加扣标志；●实现教职工工资项目及其工资的管理；●创建触发器当往教职工工资项目表中插入记录或删除记录时，自动修改该职工的应发工资数和实发工资数；●创建存储过程统计某个月各种工资项目的发放总和；●创建视图查询各个员工的应发、应扣和实发工资；●建立数据库相关表之间的参照完整性约束。

一、关系模式设计根据题意，为实现所要求的所有功能,此处共设计5个关系，具体介绍和表的形式如下所示：①职工(职工编号，姓名，性别，年龄，部门，职称)②职位变更(职工编号，前职称，现职称，变更日期)③考勤(职工编号，加班次数，缺勤次数，考勤日期)④基本工资表(职称，基本工资)⑤工资表(职工编号，基本工资，加班工资，缺勤扣钱，实发工资)二、 全局E-R 图职工姓名年龄职位变更记录现职称考勤加班次数 工资表缺勤扣钱基本工资表基本工资职称三、物理设计1、建立数据库，学校的工资管理系统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、建立职称变更记录表CREATE TABLE Change (Tno CHAR(20), /*职工号*/Title1 CHAR(20), /*之前的职称*/Title2 CHAR(20), /*现职称*/Dates INT, /*职位变更的月份*/ FOREIGN KEY (Tno) REFERENCES Teacher(Tno))4、建立考勤表CREATE TABLE Attendance (Tno CHAR(20), /*职工号*/Overtime INT, /*加班次数*/Absence INT, /*缺勤次数*/Dates INT, /*月份*/FOREIGN KEY (Tno) REFERENCES Teacher(Tno)ON DELETE CASCADEON 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(Tno) REFERENCES Teacher(Tno))三、数据库完整性设计1、各表名及其对应主键2、参照完整性设计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约束设计职工(Teacher)中对“性别”进行CHECK约束：CHECK(Tsex in('男','女'))要求性别必须为“男”或“女”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 @Title1=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)ENDENDGO4.2、在职工表中建立基本工资触发器，当插入一个新的职工记录时，根据其职称并参照基本工资表，在工资表中自动更新其基本工资，且默认加班工资和缺勤扣钱均为0CREATE TRIGGER 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.Title INSERT INTO Pay(Tno,Base,Overpay,Docked,Wages) VALUES(@tno,@Base,0,0,@Base) ENDGO4.3、在考勤表中建立考勤工资触发器，当给一个职工插入考勤信息后，自动在工资表中更新其工资信息，算法里设计加班一次加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=@tnoSELECT @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+100*@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=Pay.Overpay FROM Pay,inserted WHERE Pay.Tno=@BSELECT @D=Pay.Docked FROM Pay,inserted WHERE Pay.Tno=@BIF @A='普通教师'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=6000+@C-@D WHERE @B=Pay.TnoIF @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(Wages) 总实发工资FROM Pay七、实验结果(1)基本工资表各职工的基本工资按各自的职称参照此表(2)职工表，以下为添加记录后的结果添加后，Basic_pay触发器触发，在工资表中自动插入所有人的基本工资信息：(3)现将李楠、王峰的职称都提一级，吴鹏降一级，职称更新后，Title_change触发器触发，首先职称变更记录表中插入相应记录，：然后工资表自动更新，以上三人的基本工资改变：(4)在考勤表中添加记录随后A_pay触发器触发，在工资表中按照加班一次加200、缺勤一次扣100自动更新职工的工资信息：(5)视图结果(6)存储过程结果八、实验心得本次的数据库大题目课程设计，所运用到的知识比较丰富，要考虑到的点也比较多，故此需要一定的思考。