数据库课程设计报告
题目：第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
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
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语言的编写能力。题目所给的要求并不是特别复杂，在设计数据库的参照完整性约束上是比较简单的，主要的在于触发器的设计和功能的实现，这也正是此次实验的精髓所在，让我了解到数据库的实用性和各方面的可行性，希望以后也能在数据库的实践上更进一步。
点击咨询