数据库课程设计题目第9题.docx
《数据库课程设计题目第9题.docx》由会员分享，可在线阅读，更多相关《数据库课程设计题目第9题.docx（11页珍藏版）》请在冰豆网上搜索。
数据库课程设计题目第9题
数据库课程设计报告
题目：
第9题
学校的工资管理系统
●实现部门、职务、职称等基本信息的管理；
●实现教职工信息的管理；
●实现工资项目的管理，工资项目设有启用标志和加扣标志；
●实现教职工工资项目及其工资的管理；
●创建触发器当往教职工工资项目表中插入记录或删除记录时，自动修改该职工的应发工资数和实发工资数；
●创建存储过程统计某个月各种工资项目的发放总和；
●创建视图查询各个员工的应发、应扣和实发工资；
●建立数据库相关表之间的参照完整性约束。
一、关系模式设计
根据题意，为实现所要求的所有功能,此处共设计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、建立数据库，学校的工资管理系统
CREATEDATABASESchoolSalary
2、建立职工信息表
CREATETABLETeacher（
TnoCHAR（20）PRIMARYKEY,/*职工号*/
TnameCHAR（20）UNIQUE,/*职工名*/
TsexCHAR（10）NOTNULLCHECK（Tsexin（'男','女'））,/*性别*/
TageINTNOTNULL,/*年龄*/
DepartCHAR（20）,/*所属部门*/
TitleCHAR（20））
3、建立职称变更记录表
CREATETABLEChange（
TnoCHAR（20）,/*职工号*/
Title1CHAR（20）,/*之前的职称*/
Title2CHAR（20）,/*现职称*/
DatesINT,/*职位变更的月份*/
FOREIGNKEY（Tno）REFERENCESTeacher（Tno）
）
4、建立考勤表
CREATETABLEAttendance（
TnoCHAR（20）,/*职工号*/
OvertimeINT,/*加班次数*/
AbsenceINT,/*缺勤次数*/
DatesINT,/*月份*/
FOREIGNKEY（Tno）REFERENCESTeacher（Tno）
ONDELETECASCADE
ONUPDATECASCADE
）
5、建立基本工资表
CREATETABLERefer（/*工资参照表*/
TitleCHAR（20）PRIMARYKEY,/*职位*/
SalaryFLOAT,/*对应金额*/
）
6、建立工资表
CREATETABLEPay（
TnoCHAR（20）,/*职工号*/
BaseFLOAT,/*基本工资*/
OverpayFLOAT,/*加班费*/
DockedFLOAT,/*缺勤扣除工资*/
WagesFLOAT,/*实际应得工资*/
FOREIGNKEY（Tno）REFERENCESTeacher（Tno）
）
三、数据库完整性设计
1、各表名及其对应主键
职工（Teacher）
职工编号（Tno）
职称变更记录（Change）
职工编号（Tno）
考勤（Attendance）
职工编号（Tno）
基本工资表（Refer）
职称（Title）
工资表（Pay）
职工编号（Tno）
2、参照完整性设计
2.1、职称变更记录（Change）的职工编号（Tno）设为外键
FOREIGNKEY（Tno）REFERENCESTeacher（Tno）
2.2、考勤（Attendance）的职工编号（Tno）设为外键
FOREIGNKEY（Tno）REFERENCESTeacher（Tno）
ONDELETECASCADE
ONUPDATECASCADE
实现在更新和删除时级联操作
2.3、工资表（Pay）的职工编号（Tno）设为外键
FOREIGNKEY（Tno）REFERENCESTeacher（Tno）
3、CHECK约束设计
职工（Teacher）中对“性别”进行CHECK约束：
CHECK（Tsexin（'男','女'））
要求性别必须为“男”或“女”
4、触发器设计
4.1、在职工表中建立职称变更触发器，当某职工的职称发生变化时，在职称变更记录表中自动插入一个记录，记录变更前后的职称名以及变更日期
CREATETRIGGERTitle_change
ONTeacher
FORUPDATE
AS
BEGIN
DECLARE@TnoCHAR（20）
SELECT@Tno=inserted.TnoFROMinserted
SELECT*FROMTeacherWHERE@Tno=Teacher.Tno
DECLARE@Title1CHAR（20）
SELECT@Title1=deleted.TitleFROMdeleted
DECLARE@Title2CHAR（20）
SELECT@Title2=Teacher.TitleFROMTeacherWHERE@Tno=Teacher.Tno
IF@Title1!
=@Title2
BEGIN
INSERTINTOChange（Tno,Title1,Title2）VALUES（@Tno,@Title1,@Title2）
END
END
GO
4.2、在职工表中建立基本工资触发器，当插入一个新的职工记录时，根据其职称并参照基本工资表，在工资表中自动更新其基本工资，且默认加班工资和缺勤扣钱均为0
CREATETRIGGERBasic_pay
ONTeacher
FORinsert
AS
BEGIN
DECLARE@BaseFLOAT,@tnoCHAR（20）
SELECT@tno=inserted.TnoFROMinserted
SELECT@Base=Refer.SalaryFROMRefer,insertedWHERERefer.Title=inserted.Title
INSERTINTOPay（Tno,Base,Overpay,Docked,Wages）VALUES（@tno,@Base,0,0,@Base）
END
GO
4.3、在考勤表中建立考勤工资触发器，当给一个职工插入考勤信息后，自动在工资表中更新其工资信息，算法里设计加班一次加200块，缺勤一次扣100块
CREATETRIGGERA_pay
ONAttendance
FORINSERT
AS
BEGIN
DECLARE@tnoCHAR（20）
DECLARE@aFLOAT
DECLARE@bFLOAT
DECLARE@cINT
DECLARE@dINT
DECLARE@eFLOAT
SELECT@tno=inserted.TnoFROMinserted
SELECT@a=OverpayFROMPayWHEREPay.Tno=@tno
SELECT@b=DockedFROMPayWHEREPay.Tno=@tno
SELECT@c=OvertimeFROMAttendanceWHEREAttendance.Tno=@tno
SELECT@d=AbsenceFROMAttendanceWHEREAttendance.Tno=@tno
SELECT@e=WagesFROMPayWHEREPay.Tno=@tno
UPDATEPaySETPay.Overpay=@a+200*@cWHEREPay.Tno=@tno
UPDATEPaySETPay.Docked=@b+100*@dWHEREPay.Tno=@tno
UPDATEPaySETPay.Wages=@e+@a+200*@c-（@b+100*@d）WHEREPay.Tno=@tno
END
4.4、在职称变更记录表中建立基本工资变更触发器，当某职工职称变更且记录在职称变更记录表插入记录后，在工资表中自动更新其所有工资信息
CREATETRIGGERChange_pay
ONChange
FORUPDATE,INSERT
AS
BEGIN
DECLARE@ACHAR（20）
DECLARE@BCHAR（20）
DECLARE@CFLOAT
DECLARE@DFLOAT
SELECT@A=inserted.Title2FROMinserted
SELECT@B=inserted.TnoFROMinserted
SELECT@C=Pay.OverpayFROMPay,insertedWHEREPay.Tno=@B
SELECT@D=Pay.DockedFROMPay,insertedWHEREPay.Tno=@B
IF@A='普通教师'
UPDATEPaySETPay.Base=4000,Pay.Wages=4000+@C-@DWHERE@B=Pay.Tno
IF@A='高级教师'
UPDATEPaySETPay.Base=5000,Pay.Wages=5000+@C-@DWHERE@B=Pay.Tno
IF@A='主任'
UPDATEPaySETPay.Base=6000,Pay.Wages=6000+@C-@DWHERE@B=Pay.Tno
IF@A='校长'
UPDATEPaySETPay.Base=7000,Pay.Wages=7000+@C-@DWHERE@B=Pay.Tno
END
GO
五、数据库视图设计
查看各职工应得和实发的工资：
CREATEVIEWPay_view
AS
SELECTTno,Base+OverpayGets,Docked,Wages
FROMPay
六、存储过程设计
CREATEPROCEDUREALL_pay
AS
SELECTSUM（Base）总基本工资,SUM（Overpay）总加班工资,SUM（Docked）总扣工资,SUM（Wages）总实发工资
FROMPay
七、实验结果
（1）基本工资表
各职工的基本工资按各自的职称参照此表
（2）职工表，以下为添加记录后的结果
添加后，Basic_pay触发器触发，在工资表中自动插入所有人的基本工资信息：
（3）现将李楠、王峰的职称都提一级，吴鹏降一级，职称更新后，Title_change触发器触发，首先职称变更记录表中插入相应记录，：
然后工资表自动更新，以上三人的基本工资改变：
（4）在考勤表中添加记录
随后A_pay触发器触发，在工资表中按照加班一次加200、缺勤一次扣100自动更新职工的工资信息：
（5）视图结果
（6）存储过程结果
八、实验心得
本次的数据库大题目课程设计，所运用到的知识比较丰富，要考虑到的点也比较多，故此需要一定的思考。
在整个设计过程中，不仅回顾了以往的理论知识，更重要的是锻炼了对SQL语言的编写能力。
题目所给的要求并不是特别复杂，在设计数据库的参照完整性约束上是比较简单的，主要的在于触发器的设计和功能的实现，这也正是此次实验的精髓所在，让我了解到数据库的实用性和各方面的可行性，希望以后也能在数据库的实践上更进一步。
升级会员 