数据库课程设计题目第9题学校工资管理系统

数据库课程设计报告题目：第9题学校的工资管理系统 l 实现部门、职务、职称等基本信息的管理；

l 实现教职工信息的管理；

l 实现工资项目的管理，工资项目设有启用标志和加扣标志；

l 实现教职工工资项目及其工资的管理；

l 创建触发器当往教职工工资项目表中插入记录或删除记录时，自动修改该职工的应发工资数和实发工资数；

l 创建存储过程统计某个月各种工资项目的发放总和；

l 创建视图查询各个员工的应发、应扣和实发工资；

l 建立数据库相关表之间的参照完整性约束。

一、关系模式设计根据题意，为实现所要求的所有功能,此处共设计5个关系，具体介绍和表的形式如下所示：
①职工(职工编号，姓名，性别，年龄，部门，职称) Tno Tname Tsex Tage Department Title ②职位变更(职工编号，前职称，现职称，变更日期) Tno Title1 Title2 Dates ③考勤(职工编号，加班次数，缺勤次数，考勤日期) Tno Overtime Absence Dates ④基本工资表(职称，基本工资) Title Base ⑤工资表(职工编号，基本工资，加班工资，缺勤扣钱，实发工资) Tno Base Overpay Docked Wages 二、全局E-R图参照1 职称基本工资基本工资表参照2 基本工资得到职工编号工资表职称年龄部门加班工资缺勤扣钱实发工资加班次数缺勤次数考勤日期职工编号接收考勤考勤变更日期现职称前职称职工编号职位变更职位变更记录性别姓名职工编号职工三、物理设计 1、建立数据库，学校的工资管理系统 CREATE DATABASE SchoolSalary 2、建立职工信息表 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 CASCADE ON 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、各表名及其对应主键职工(Teacher) 职工编号(Tno) 职称变更记录(Change) 职工编号(Tno) 考勤(Attendance) 职工编号(Tno) 基本工资表(Refer) 职称(Title) 工资表(Pay) 职工编号(Tno) 2、参照完整性设计 2.1、职称变更记录(Change)的职工编号(Tno)设为外键 FOREIGN KEY (Tno) REFERENCES Teacher(Tno) 2.2、考勤(Attendance)的职工编号(Tno)设为外键 FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ON DELETE CASCADE ON 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_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 @Title1=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、在职工表中建立基本工资触发器，当插入一个新的职工记录时，根据其职称并参照基本工资表，在工资表中自动更新其基本工资，且默认加班工资和缺勤扣钱均为0 CREATE 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、在考勤表中建立考勤工资触发器，当给一个职工插入考勤信息后，自动在工资表中更新其工资信息，算法里设计加班一次加200块，缺勤一次扣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 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+200*@c WHERE Pay.Tno=@tno UPDATE Pay SET Pay.Docked=@b+100*@d WHERE Pay.Tno=@tno UPDATE Pay SET Pay.Wages=@e+@a+200*@c-(@b+100*@d) WHERE Pay.Tno=@tno END 4.4、在职称变更记录表中建立基本工资变更触发器，当某职工职称变更且记录在职称变更记录表插入记录后，在工资表中自动更新其所有工资信息 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 SELECT @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=＇高级教师＇ UPDATE Pay SET Pay.Base=5000,Pay.Wages=5000+@C-@D WHERE @B=Pay.Tno IF @A=＇主任＇ UPDATE Pay SET Pay.Base=6000,Pay.Wages=6000+@C-@D WHERE @B=Pay.Tno IF @A=＇校长＇ UPDATE Pay SET Pay.Base=7000,Pay.Wages=7000+@C-@D WHERE @B=Pay.Tno END GO 五、数据库视图设计查看各职工应得和实发的工资：
CREATE VIEW Pay_view AS SELECT Tno,Base+Overpay Gets,Docked,Wages FROM Pay 六、存储过程设计 CREATE PROCEDURE ALL_pay AS SELECT SUM(Base) 总基本工资,SUM(Overpay) 总加班工资,SUM(Docked) 总扣工资,SUM(Wages) 总实发工资 FROM Pay 七、实验结果 (1)基本工资表各职工的基本工资按各自的职称参照此表 (2)职工表，以下为添加记录后的结果添加后，Basic_pay触发器触发，在工资表中自动插入所有人的基本工资信息：
(3)现将李楠、王峰的职称都提一级，吴鹏降一级，职称更新后，Title_change触发器触发，首先职称变更记录表中插入相应记录，：
然后工资表自动更新，以上三人的基本工资改变：
(4)在考勤表中添加记录随后A_pay触发器触发，在工资表中按照加班一次加200、缺勤一次扣100自动更新职工的工资信息：
(5)视图结果 (6)存储过程结果八、实验心得本次的数据库大题目课程设计，所运用到的知识比较丰富，要考虑到的点也比较多，故此需要一定的思考。在整个设计过程中，不仅回顾了以往的理论知识，更重要的是锻炼了对SQL语言的编写能力。题目所给的要求并不是特别复杂，在设计数据库的参照完整性约束上是比较简单的，主要的在于触发器的设计和功能的实现，这也正是此次实验的精髓所在，让我了解到数据库的实用性和各方面的可行性，希望以后也能在数据库的实践上更进一步。