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

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

2、，年龄，部门，职称)TnoTnameTsexTageDepartmentTitle职位变更(职工编号，前职称，现职称，变更日期)TnoTitle1Title2Dates考勤(职工编号，加班次数，缺勤次数，考勤日期)TnoOvertimeAbsenceDates基本工资表(职称，基本工资)TitleBase工资表(职工编号，基本工资，加班工资，缺勤扣钱，实发工资)TnoBaseOverpayDockedWages二、 全局E-R图参照1职称基本工资基本工资表参照2基本工资得到职工编号工资表职称年龄部门加班工资缺勤扣钱实发工资加班次数缺勤次数考勤日期职工编号接收考勤考勤变更日期现职称前职称职工编号

3、职位变更职位变更记录性别姓名职工编号职工三、物理设计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

4、、建立职称变更记录表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) RE

5、FERENCES 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

6、(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 Te

7、acher(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 TRIGG

8、ER 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!

9、=Title2 BEGIN INSERT 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.S

10、alary FROM Refer,inserted WHERE Refer.Title=inserted.TitleINSERT 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

11、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

12、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=tnoEND4.4、在职称变更记录表中建立基本工资变更触发器，当某职工职称变更且记录在职称变更记录表插入记录后，在工资表中自动更新其所有工资信息CREATE TRIGGER Change_payON ChangeFOR UP

13、DATE,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 Pa

14、y.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.TnoENDGO五、 数据库视

15、图设计查看各职工应得和实发的工资：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、缺勤一次扣1