基于Sql Server的人事管理數(shù)據(jù)完整性設(shè)計(jì)

楊麗玲

?

基于Sql Server的人事管理數(shù)據(jù)完整性設(shè)計(jì)

楊麗玲

(漳州職業(yè)技術(shù)學(xué)院 計(jì)算機(jī)工程系, 福建 漳州 363000)

Microsoft SQL SERVER 數(shù)據(jù)庫(kù)管理平臺(tái)提供了數(shù)據(jù)庫(kù)引擎、分析服務(wù)、集成服務(wù)和報(bào)表服務(wù)等功能。主要利用它的核心服務(wù)--數(shù)據(jù)引擎建立人事管理數(shù)據(jù)庫(kù)，并利用約束、觸發(fā)器和存儲(chǔ)過(guò)程等強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，為企事業(yè)的人事管理提供了良好的后臺(tái)數(shù)據(jù)庫(kù)。

SQL SERVER；人事管理數(shù)據(jù)庫(kù)；數(shù)據(jù)完整性；存儲(chǔ)過(guò)程

引 言

互聯(lián)網(wǎng)的發(fā)展帶動(dòng)了信息化的普及，它不僅節(jié)省了大量的時(shí)間、精力、財(cái)力，還大大提高了管理的效率，增強(qiáng)企業(yè)的生產(chǎn)力。人事管理系統(tǒng)是針對(duì)企事業(yè)單位開發(fā)設(shè)計(jì)的在線管理系統(tǒng)，用于實(shí)現(xiàn)員工的信息管理、薪酬計(jì)算、考勤登記等。人事管理系統(tǒng)不受地域的限制，可以隨時(shí)隨地辦空，增加了工作的靈活性，同時(shí)也為人員的管理提供了大大的方便。

1 SQL SERVER

SQL SERVER是一個(gè)大型的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，提供了一個(gè)企業(yè)級(jí)大數(shù)據(jù)資產(chǎn)和BI解決方案，幫助員工做出更明智的決策。它整合了結(jié)構(gòu)化和非結(jié)構(gòu)化數(shù)據(jù)，適用于本地和云共存的混合環(huán)境，從而讓客戶充分利用Microsoft的全球數(shù)據(jù)。同時(shí)它是整合了數(shù)據(jù)庫(kù)、商業(yè)智能、報(bào)表服務(wù)、分析服務(wù)等多種技術(shù)的數(shù)據(jù)平臺(tái)，在各行各業(yè)都發(fā)揮著極其重要的作用。企業(yè)的人事信息管理在企業(yè)中占有重要的地位，本文探討利用SQL SERVER平臺(tái)構(gòu)建的一個(gè)人事信息管理數(shù)據(jù)庫(kù)，進(jìn)行數(shù)據(jù)完整性約束[1][2]。

2 需求分析

人事管理系統(tǒng)中分為2個(gè)子系統(tǒng)：客戶端子系統(tǒng)，用于實(shí)現(xiàn)信息查詢功能，普通員工可以通過(guò) Web 瀏覽自己的基本資料、考勤信息、薪資信息和請(qǐng)假記錄等，也可以通過(guò) Web 提出請(qǐng)假和加班申請(qǐng)，如果所屬部門的經(jīng)理審批通過(guò)，人事部就可以登記在案；服務(wù)器端子系統(tǒng)，用于實(shí)現(xiàn)信息管理功能，而SQL系統(tǒng)主要是用于數(shù)據(jù)庫(kù)的管理，包括了數(shù)據(jù)庫(kù)中對(duì)象的設(shè)計(jì)等工作。

3 數(shù)據(jù)庫(kù)設(shè)計(jì)

為了有效地管理員工信息、考勤、薪酬等工作，人事管理數(shù)據(jù)庫(kù)共有9張表，分別為：

1） 員工表tblEmployee：用于記錄員工基本信息。

2） 職位表tblTitle：用于記錄公司員工各種職位，本公司職位有普通員工、部門經(jīng)理、人事助理、人事經(jīng)理、總經(jīng)理。

3） 部門表tblDepartment：用于記錄企業(yè)內(nèi)部的部門信息。

4） 工資表tblSalary：用于記錄員工每月的工資信息。

5） 缺勤表tblAttendance：用于記錄員工的缺勤信息，以天為單位。

6） 請(qǐng)假表tblLeave：用于登記員工的請(qǐng)假記錄，員工請(qǐng)假以小時(shí)為單位，請(qǐng)假類型（Type）為“折算成年假”或“折算成津貼”。

7） 加班表tblOvertime：用于記錄員工的加班記錄，與表tblLeave相似，加班以小時(shí)為單位，請(qǐng)假類型（Type）為“折算成年假”或“折算成津貼”。

8） 申請(qǐng)狀態(tài)表tbapproval：用于記錄員工的申請(qǐng)狀態(tài)。申請(qǐng)狀態(tài)（Status）為“已提交”、“已批準(zhǔn)”、“已拒絕”和“已取消”之一。

9） 折算表tblOvertimeLeave：用于記錄加班或請(qǐng)假等的折算類型，類型為“折算成年假”或“折算成津貼”。

其 E-R圖如下圖1。

圖1 人事管理數(shù)據(jù)庫(kù)E-R圖

4 完整性控制

4.1 約束條件的設(shè)計(jì)

數(shù)據(jù)庫(kù)完整性是指存放在數(shù)據(jù)庫(kù)中，數(shù)據(jù)要滿足業(yè)務(wù)規(guī)則。它保證了數(shù)據(jù)庫(kù)中數(shù)據(jù)的正確性[3][4]。為了保證人事管理數(shù)據(jù)庫(kù)的完整性，做了如下定義：

1）請(qǐng)假表與加班表中，審核者編號(hào)不能與請(qǐng)假或加工員工編號(hào)相同，即用檢查約束add constraint CK_tblLeave2 check (雇員編號(hào)<>審批人編號(hào))來(lái)實(shí)現(xiàn)。同時(shí)結(jié)束時(shí)間（EndTime）必須大于開始時(shí)間（StartTime）,即check ([結(jié)束時(shí)間]-[開始時(shí)間]>=0)

2）缺勤表中考勤記錄者編號(hào)和員工編號(hào)不能相同，即check (雇員編號(hào)<>記錄員編號(hào))

3）建立各張表的主鍵，并建立與其它表的外鍵約束。如員工表中的部門編號(hào)參照部門表中的部門編號(hào)，即 FOREIGN KEY([部門編號(hào)]) REFERENCES [dbo].[tblDepartment] ([部門編號(hào)]) ON UPDATE CASCADE，同時(shí)實(shí)現(xiàn)當(dāng)部門表中的部門編號(hào)發(fā)生變化時(shí)，級(jí)連更新員工表中的部門編號(hào)。

4.2 利用存儲(chǔ)過(guò)程進(jìn)行數(shù)據(jù)完整性設(shè)計(jì)

存儲(chǔ)過(guò)程是一套已經(jīng)預(yù)先編譯好的Transact_SQL代碼。在存儲(chǔ)過(guò)程中允許聲明變量、有條件執(zhí)行及包含程序流、邏輯和對(duì)數(shù)據(jù)庫(kù)的查詢，利用它可以實(shí)現(xiàn)相對(duì)復(fù)雜的業(yè)務(wù)規(guī)則，同時(shí)它既有執(zhí)行速度快、能增加系統(tǒng)的安全性等優(yōu)點(diǎn)[5][6]。

1) 加班表中的申請(qǐng)加班的員工，當(dāng)申請(qǐng)成功（即申請(qǐng)狀態(tài)號(hào)為02）時(shí)，根據(jù)其加班時(shí)間折算類型更新員工表的剩余假期（當(dāng)折算類型為02時(shí)）或工資表（當(dāng)折算類型為01時(shí)）。

create proc [dbo].[p_tblOvertime]

as

--根據(jù)加班表修改申請(qǐng)已成功的雇員剩余假期或加班工資

declare @雇員編號(hào)varchar(15),@類型varchar(2),@申請(qǐng)狀態(tài)號(hào)nvarchar(2),

@時(shí)長(zhǎng) int

declare c_overtime cursor

for

select 雇員編號(hào),類型,申請(qǐng)狀態(tài)號(hào),時(shí)長(zhǎng)

from dbo.tblOvertime

open c_overtime

fetch next from c_overtime into @雇員編號(hào),@類型,@申請(qǐng)狀態(tài)號(hào),@時(shí)長(zhǎng)

while @@fetch_status=0

begin

if @類型='02' and @申請(qǐng)狀態(tài)號(hào)='02'

update dbo.tblEmployee set 剩余假期=剩余假期+CEILING(@時(shí)長(zhǎng)/8.0)

where 雇員編號(hào)=@雇員編號(hào)

else if @類型='01' and @申請(qǐng)狀態(tài)號(hào)='02'

update dbo.tblSalary set 加班工資=加班工資+@時(shí)長(zhǎng)*30

where 雇員編號(hào)=@雇員編號(hào)

fetch next from c_overtime into @雇員編號(hào),@類型,@申請(qǐng)狀態(tài)號(hào),@時(shí)長(zhǎng)

end

close c_overtime

deallocate c_overtime

2） 請(qǐng)假表中申請(qǐng)請(qǐng)假的員工，當(dāng)申請(qǐng)成功（即申請(qǐng)狀態(tài)號(hào)為02）時(shí)，根據(jù)其請(qǐng)假時(shí)間折算類型更新員工表的剩余假期（當(dāng)折算類型為02時(shí)）或工資表（當(dāng)折算類型為01時(shí)），當(dāng)剩余假期不夠扣除時(shí)，自動(dòng)轉(zhuǎn)為扣除薪資。

ALTER proc [dbo].[p_tblLeave]

as

--根據(jù)請(qǐng)假表修改申請(qǐng)已成功的雇員剩余假期或加班工資

declare @雇員編號(hào)varchar(15),@類型varchar(2),@申請(qǐng)狀態(tài)號(hào)nvarchar(2),

@時(shí)長(zhǎng) int ,@剩余假期int,@加班工資int

declare c_tblLeave cursor

for select 雇員編號(hào),類型,申請(qǐng)狀態(tài)號(hào),時(shí)長(zhǎng)

from dbo.tblLeave

open c_tblLeave

fetch next from c_tblLeave into @雇員編號(hào),@類型,@申請(qǐng)狀態(tài)號(hào),@時(shí)長(zhǎng)

while @@fetch_status=0

begin

if (@類型='02' and @申請(qǐng)狀態(tài)號(hào)='02')

begin

select @剩余假期=剩余假期from dbo.tblEmployee where 雇員編號(hào)=@雇員編號(hào)

if (@剩余假期-ceiling(@時(shí)長(zhǎng)/16.0))>=0

update dbo.tblEmployee set 剩余假期=剩余假期-ceiling(@時(shí)長(zhǎng)/16.0)

where 雇員編號(hào)=@雇員編號(hào)

else

print '剩余假期不足'

goto label1

end

else if (@類型='01' and @申請(qǐng)狀態(tài)號(hào)='02' )

label1: begin

select @加班工資=加班工資from dbo.tblSalary where 雇員編號(hào)=@雇員編號(hào)

if (@加班工資-@時(shí)長(zhǎng)*15)>=0

update dbo.tblSalary set 加班工資=加班工資-@時(shí)長(zhǎng)*15

where 雇員編號(hào)=@雇員編號(hào)

else

update dbo.tblSalary set 曠班工資=曠班工資+@時(shí)長(zhǎng)*15

where 雇員編號(hào)=@雇員編號(hào)

end

fetch next from c_tblLeave into @雇員編號(hào),@類型,@申請(qǐng)狀態(tài)號(hào),@時(shí)長(zhǎng)

end

close c_tblLeave

deallocate c_tblLeave

4.3 利用觸發(fā)器進(jìn)行數(shù)據(jù)完整性設(shè)計(jì)

觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，也是提前編譯好的SQL語(yǔ)句，它與表緊密相連，當(dāng)用戶修改表的時(shí)候，它將會(huì)自動(dòng)執(zhí)行。它是SQL強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性的另一種方法。

在人事管理數(shù)據(jù)庫(kù)的部門表中，當(dāng)一個(gè)部門存在員工時(shí)，不可刪除該部門。即只有當(dāng)一個(gè)部門中沒有任何員工時(shí)，才可以刪除該部門。針對(duì)這個(gè)問題，我們使用了觸發(fā)器來(lái)實(shí)現(xiàn)，如下：

create trigger t1 on dbo.tblDepartment instead of delete

as

declare @i int

select @i=count(*) from dbo.tblEmployee where 部門編號(hào)in (select 部門編號(hào)from deleted)

if @i<> 0

begin

print '該部門仍有員工，不允許刪除'

rollback

end

else

begin

delete dbo.tblDepartment where 部門編號(hào)in (select 部門編號(hào)from deleted)

print '該部門已刪除'

end

4 結(jié) 語(yǔ)

利用SQL數(shù)據(jù)庫(kù)管理平臺(tái)建立人事管理的后臺(tái)數(shù)據(jù)庫(kù)，為了保證存儲(chǔ)數(shù)據(jù)的正確性，利用約束和觸發(fā)器防止錯(cuò)誤的數(shù)據(jù)存儲(chǔ)于數(shù)據(jù)庫(kù)中。同時(shí)，為了強(qiáng)制業(yè)務(wù)規(guī)則，如人事管理中假期或工資的變化，使用了存儲(chǔ)過(guò)程對(duì)象，經(jīng)過(guò)實(shí)驗(yàn)測(cè)試，均實(shí)現(xiàn)了業(yè)務(wù)要求，取得了良好的效果。

[1] 徐人鳳，曾建華. SQL Server 2005數(shù)據(jù)庫(kù)及應(yīng)用[M].北京：高等教育出版社，2007.

[2] 飛狼，李春萌，楊涵.SQL Server 2005數(shù)據(jù)庫(kù)管理與應(yīng)用指南[M].北京：人民郵電出版社，2007.

[3] 周麗娟，王潔，徐敏.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)設(shè)計(jì)與實(shí)踐[M].北京：中國(guó)鐵道出版社，2010.

[4] 丁寶康.數(shù)據(jù)庫(kù)原理(第三版)[M].北京：經(jīng)濟(jì)科學(xué)出版社，2000.

[5] 薩師煊，王姍.數(shù)據(jù)庫(kù)系統(tǒng)概論(第二版)[M].北京：高等教育出版社，1991.

[6] 范玉紅.基于SQL Server 數(shù)據(jù)庫(kù)的人事管理系統(tǒng)的研究與開發(fā)[J].信息安全與技術(shù)，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，F(xiàn)ujian 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－），女，福建漳州人，講師，碩士，研究方向：數(shù)據(jù)庫(kù)、數(shù)據(jù)挖掘。

(責(zé)任編輯：季平)