基于Sql Server的人事管理数据完整性设计

杨丽玲



基于Sql Server的人事管理数据完整性设计

杨丽玲

(漳州职业技术学院 计算机工程系, 福建 漳州 363000)

Microsoft SQL SERVER 数据库管理平台提供了数据库引擎、分析服务、集成服务和报表服务等功能。主要利用它的核心服务--数据引擎建立人事管理数据库，并利用约束、触发器和存储过程等强制业务规则和数据完整性，为企事业的人事管理提供了良好的后台数据库。

SQL SERVER；人事管理数据库；数据完整性；存储过程

引 言

互联网的发展带动了信息化的普及，它不仅节省了大量的时间、精力、财力，还大大提高了管理的效率，增强企业的生产力。人事管理系统是针对企事业单位开发设计的在线管理系统，用于实现员工的信息管理、薪酬计算、考勤登记等。人事管理系统不受地域的限制，可以随时随地办空，增加了工作的灵活性，同时也为人员的管理提供了大大的方便。

1 SQL SERVER

SQL SERVER是一个大型的关系型数据库管理系统，提供了一个企业级大数据资产和BI解决方案，帮助员工做出更明智的决策。它整合了结构化和非结构化数据，适用于本地和云共存的混合环境，从而让客户充分利用Microsoft的全球数据。同时它是整合了数据库、商业智能、报表服务、分析服务等多种技术的数据平台，在各行各业都发挥着极其重要的作用。企业的人事信息管理在企业中占有重要的地位，本文探讨利用SQL SERVER平台构建的一个人事信息管理数据库，进行数据完整性约束[1][2]。

2 需求分析

人事管理系统中分为2个子系统：客户端子系统，用于实现信息查询功能，普通员工可以通过 Web 浏览自己的基本资料、考勤信息、薪资信息和请假记录等，也可以通过 Web 提出请假和加班申请，如果所属部门的经理审批通过，人事部就可以登记在案；服务器端子系统，用于实现信息管理功能，而SQL系统主要是用于数据库的管理，包括了数据库中对象的设计等工作。

3 数据库设计

为了有效地管理员工信息、考勤、薪酬等工作，人事管理数据库共有9张表，分别为：

1） 员工表tblEmployee：用于记录员工基本信息。

2） 职位表tblTitle：用于记录公司员工各种职位，本公司职位有普通员工、部门经理、人事助理、人事经理、总经理。

3） 部门表tblDepartment：用于记录企业内部的部门信息。

4） 工资表tblSalary：用于记录员工每月的工资信息。

5） 缺勤表tblAttendance：用于记录员工的缺勤信息，以天为单位。

6） 请假表tblLeave：用于登记员工的请假记录，员工请假以小时为单位，请假类型（Type）为“折算成年假”或“折算成津贴”。

7） 加班表tblOvertime：用于记录员工的加班记录，与表tblLeave相似，加班以小时为单位，请假类型（Type）为“折算成年假”或“折算成津贴”。

8） 申请状态表tbapproval：用于记录员工的申请状态。申请状态（Status）为“已提交”、“已批准”、“已拒绝”和“已取消”之一。

9） 折算表tblOvertimeLeave：用于记录加班或请假等的折算类型，类型为“折算成年假”或“折算成津贴”。

其 E-R图如下图1。

图1 人事管理数据库E-R图

4 完整性控制

4.1 约束条件的设计

数据库完整性是指存放在数据库中，数据要满足业务规则。它保证了数据库中数据的正确性[3][4]。为了保证人事管理数据库的完整性，做了如下定义：

1）请假表与加班表中，审核者编号不能与请假或加工员工编号相同，即用检查约束add constraint CK_tblLeave2 check (雇员编号<>审批人编号)来实现。同时结束时间（EndTime）必须大于开始时间（StartTime）,即check ([结束时间]-[开始时间]>=0)

2）缺勤表中考勤记录者编号和员工编号不能相同，即check (雇员编号<>记录员编号)

3）建立各张表的主键，并建立与其它表的外键约束。如员工表中的部门编号参照部门表中的部门编号，即 FOREIGN KEY([部门编号]) REFERENCES [dbo].[tblDepartment] ([部门编号]) ON UPDATE CASCADE，同时实现当部门表中的部门编号发生变化时，级连更新员工表中的部门编号。

4.2 利用存储过程进行数据完整性设计

存储过程是一套已经预先编译好的Transact_SQL代码。在存储过程中允许声明变量、有条件执行及包含程序流、逻辑和对数据库的查询，利用它可以实现相对复杂的业务规则，同时它既有执行速度快、能增加系统的安全性等优点[5][6]。

1) 加班表中的申请加班的员工，当申请成功（即申请状态号为02）时，根据其加班时间折算类型更新员工表的剩余假期（当折算类型为02时）或工资表（当折算类型为01时）。

create proc [dbo].[p_tblOvertime]

as

--根据加班表修改申请已成功的雇员剩余假期或加班工资

declare @雇员编号varchar(15),@类型varchar(2),@申请状态号nvarchar(2),

@时长 int

declare c_overtime cursor

for

select 雇员编号,类型,申请状态号,时长

from dbo.tblOvertime

open c_overtime

fetch next from c_overtime into @雇员编号,@类型,@申请状态号,@时长

while @@fetch_status=0

begin

if @类型='02' and @申请状态号='02'

update dbo.tblEmployee set 剩余假期=剩余假期+CEILING(@时长/8.0)

where 雇员编号=@雇员编号

else if @类型='01' and @申请状态号='02'

update dbo.tblSalary set 加班工资=加班工资+@时长*30

where 雇员编号=@雇员编号

fetch next from c_overtime into @雇员编号,@类型,@申请状态号,@时长

end

close c_overtime

deallocate c_overtime

2） 请假表中申请请假的员工，当申请成功（即申请状态号为02）时，根据其请假时间折算类型更新员工表的剩余假期（当折算类型为02时）或工资表（当折算类型为01时），当剩余假期不够扣除时，自动转为扣除薪资。

ALTER proc [dbo].[p_tblLeave]

as

--根据请假表修改申请已成功的雇员剩余假期或加班工资

declare @雇员编号varchar(15),@类型varchar(2),@申请状态号nvarchar(2),

@时长 int ,@剩余假期int,@加班工资int

declare c_tblLeave cursor

for select 雇员编号,类型,申请状态号,时长

from dbo.tblLeave

open c_tblLeave

fetch next from c_tblLeave into @雇员编号,@类型,@申请状态号,@时长

while @@fetch_status=0

begin

if (@类型='02' and @申请状态号='02')

begin

select @剩余假期=剩余假期from dbo.tblEmployee where 雇员编号=@雇员编号

if (@剩余假期-ceiling(@时长/16.0))>=0

update dbo.tblEmployee set 剩余假期=剩余假期-ceiling(@时长/16.0)

where 雇员编号=@雇员编号

else

print '剩余假期不足'

goto label1

end

else if (@类型='01' and @申请状态号='02' )

label1: begin

select @加班工资=加班工资from dbo.tblSalary where 雇员编号=@雇员编号

if (@加班工资-@时长*15)>=0

update dbo.tblSalary set 加班工资=加班工资-@时长*15

where 雇员编号=@雇员编号

else

update dbo.tblSalary set 旷班工资=旷班工资+@时长*15

where 雇员编号=@雇员编号

end

fetch next from c_tblLeave into @雇员编号,@类型,@申请状态号,@时长

end

close c_tblLeave

deallocate c_tblLeave

4.3 利用触发器进行数据完整性设计

触发器是一种特殊的存储过程，也是提前编译好的SQL语句，它与表紧密相连，当用户修改表的时候，它将会自动执行。它是SQL强制业务规则和数据完整性的另一种方法。

在人事管理数据库的部门表中，当一个部门存在员工时，不可删除该部门。即只有当一个部门中没有任何员工时，才可以删除该部门。针对这个问题，我们使用了触发器来实现，如下：

create trigger t1 on dbo.tblDepartment instead of delete

as

declare @i int

select @i=count(*) from dbo.tblEmployee where 部门编号in (select 部门编号from deleted)

if @i<> 0

begin

print '该部门仍有员工，不允许删除'

rollback

end

else

begin

delete dbo.tblDepartment where 部门编号in (select 部门编号from deleted)

print '该部门已删除'

end

4 结 语

利用SQL数据库管理平台建立人事管理的后台数据库，为了保证存储数据的正确性，利用约束和触发器防止错误的数据存储于数据库中。同时，为了强制业务规则，如人事管理中假期或工资的变化，使用了存储过程对象，经过实验测试，均实现了业务要求，取得了良好的效果。

[1] 徐人凤，曾建华. SQL Server 2005数据库及应用[M].北京：高等教育出版社，2007.

[2] 飞狼，李春萌，杨涵.SQL Server 2005数据库管理与应用指南[M].北京：人民邮电出版社，2007.

[3] 周丽娟，王洁，徐敏.数据库应用系统设计与实践[M].北京：中国铁道出版社，2010.

[4] 丁宝康.数据库原理(第三版)[M].北京：经济科学出版社，2000.

[5] 萨师煊，王姗.数据库系统概论(第二版)[M].北京：高等教育出版社，1991.

[6] 范玉红.基于SQL Server 数据库的人事管理系统的研究与开发[J].信息安全与技术，2012（7）:56-57.

Personnel Management Database Integrity Design Based On Sql Server

YANG Li-ling

(Department of Computing Engineering，Zhangzhou College of Vocational Technology，Fujian Zhangzhou 363000，China)

Microsoft SQL SERVER Database Manage System provides functions about data engine、analysis services、integration services、reporting services.Using data engine function, it set up personnel management database and those of constraint、trigger、procedure help maintain data integrity and rule. All of these provide good database for Enterprises and institutions.

sql server; personnel management database; data integrity; procedure

TP311.135.9

A

1673-1417（2014）04-0008-05

10.13908/j.cnki.issn1673-1417.2014.04.0002

2014－09－10

杨丽玲（1977－），女，福建漳州人，讲师，硕士，研究方向：数据库、数据挖掘。

(责任编辑：季平)