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

数据库课程设计题目第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, /x职工号x/ Tname CHAR(20) UNIQUE, /x职工名x/ Tsex CHAR(10) NOT NULL CHECK(Tsex in(＇男＇,＇女＇)), /x性别x/ Tage INT NOT NULL, /x年龄x/ Depart CHAR(20), /x所属部门x/ Title CHAR(20)) 3、建立职称变更记录表 CREATE TABLE Change ( Tno CHAR(20), /x职工号x/ Title1 CHAR(20), /x之前的职称x/ Title2 CHAR(20), /x现职称x/ Dates INT, /x职位变更的月份x/ FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ) 4、建立考勤表 CREATE TABLE Attendance ( Tno CHAR(20), /x职工号x/ Overtime INT, /x加班次数x/ Absence INT, /x缺勤次数x/ Dates INT, /x月份x/ FOREIGN KEY (Tno) REFERENCES Teacher(Tno) ON DELETE CASCADE ON UPDATE CASCADE ) 5、建立根本工资表 CREATE TABLE Refer ( /x工资参照表x/ Title CHAR(20) PRIMARY KEY, /x职位x/ Salary FLOAT, /x对应金额x/ ) 6、建立工资表 CREATE TABLE Pay ( Tno CHAR(20), /x职工号x/ Base FLOAT, /x根本工资x/ Overpay FLOAT, /x加班费x/ Docked FLOAT, /x缺勤扣除工资x/ Wages FLOAT, /x实际应得工资x/ 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 x 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+200x@c WHERE Pay.Tno=@tno UPDATE Pay SET Pay.Docked=@b+100x@d WHERE Pay.Tno=@tno UPDATE Pay SET Pay.Wages=@e+@a+200x@c-(@b+100x@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)存储过程结果 八、实验心得 本次的数据库大题目课程设计，所运用到的知识比较丰富，要考虑到的点也比较多，故此需要一定