工資管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)

1、工資管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)目錄1 引言22 數(shù)據(jù)庫設(shè)計(jì)32.1 需求設(shè)計(jì)32.2 業(yè)務(wù)流程圖.42.3 數(shù)據(jù)流圖.42.4 數(shù)據(jù)字典53 概念設(shè)計(jì)8e-r圖84 邏輯設(shè)計(jì)104.1 e-r 圖向關(guān)系模型轉(zhuǎn)換104.2 數(shù)據(jù)模型的優(yōu)化105 物理設(shè)計(jì)105.1 建立觸發(fā)器115.2 建立存儲(chǔ)過程116 數(shù)據(jù)庫實(shí)施階段126.1 建立數(shù)據(jù)庫126.2使用數(shù)據(jù)庫126.3創(chuàng)建四張表136.4刪除表146.5創(chuàng)建四個(gè)存儲(chǔ)過程156.6刪除176.7信息錄入176.8創(chuàng)建查詢視圖186.9查詢186.10刪除觸發(fā)器196.11創(chuàng)建修改數(shù)據(jù)的存儲(chǔ)過程197.參考文獻(xiàn)241 引言本次數(shù)據(jù)庫實(shí)習(xí)不要求界面和外部應(yīng)

2、用程序的設(shè)計(jì)，只設(shè)計(jì)數(shù)據(jù)庫部分。工資管理系統(tǒng)采用sql server 2000作為數(shù)據(jù)庫后臺(tái)。工資管理系統(tǒng)的主要任務(wù)是用計(jì)算機(jī)對(duì)各種工資信息進(jìn)行日常的管理，如查詢、修改、增加、刪除以及存儲(chǔ)等，迅速準(zhǔn)確地完成各種工資信息的統(tǒng)計(jì)計(jì)算和匯總工作，快速打印出工資報(bào)表，針對(duì)系統(tǒng)服務(wù)對(duì)象的具體要求，設(shè)計(jì)了企業(yè)工資管理系統(tǒng)。2 數(shù)據(jù)庫設(shè)計(jì)2.1 需求設(shè)計(jì)2.1.1 總體分析借助現(xiàn)代信息技術(shù)和管理理論，建立企業(yè)管理信息系統(tǒng)是當(dāng)今社會(huì)的重要趨勢(shì)。根據(jù)“用信息化帶動(dòng)工業(yè)化”的指導(dǎo)思想，全面開發(fā)和應(yīng)用計(jì)算機(jī)管理信息系統(tǒng)是一個(gè)不能回避的問題。在企業(yè)管理中，人力資源是企業(yè)最寶貴的資源，也是企業(yè)的“生命線”，因此人事管理

3、是企業(yè)的計(jì)算機(jī)管理信息系統(tǒng)的重要組成部分。而工資管理又是人力資源管理的重中之重。實(shí)行電子化的工資管理，可以讓人力資源管理人員從繁重瑣碎的案頭工作，以便抽出時(shí)間完成更重要的工作。工資管理信息系統(tǒng)的實(shí)現(xiàn)可以減輕比較繁瑣的收購(gòu)。現(xiàn)在大型的erp軟件具備相當(dāng)強(qiáng)大的工資管理模塊。但是由于在一些小型公司實(shí)施erp，會(huì)造成不必要的資源浪費(fèi)，所以設(shè)計(jì)一套適合小型企業(yè)的工資管理系統(tǒng)有特別的現(xiàn)實(shí)意義。2.1.2 功能描述要求具備如下基本功能：基本工種、基本工資的管理；公司部門、部門提成和績(jī)效、員工的管理；加班類型、加班工資的管理；公司員工的考勤信息管理；公司員工月工資管理月工資基本工資提成工資+績(jī)效工資+加班工資

4、-社?？圪M(fèi)-其他扣款；查詢統(tǒng)計(jì)上述信息，生成企業(yè)工資報(bào)表。2.2 業(yè)務(wù)流程圖人事管理系統(tǒng)主要有以下幾項(xiàng)功能:員工檔案的管理:包括員工基本信息,所在部門,工作信息和工作簡(jiǎn)歷的輸入,員工信息的查詢和修改。員工考勤的管理:包括考勤的添加,查詢,修改和刪除。員工工資的管理:包括工資的添加,工資報(bào)表的查詢,修改和刪除。員工評(píng)價(jià)的管理:包括評(píng)價(jià)的輸入,查詢和修改。人事變動(dòng)的詳細(xì)記錄,包括崗位和部門的調(diào)整,便于掌握人員的動(dòng)向,及時(shí)調(diào)整人才的分配。企業(yè)所有部門的查詢與管理。管理員以及其它功能模塊。仔細(xì)分析調(diào)查有關(guān)企業(yè)人事信息需求的基礎(chǔ)上,得到如下的數(shù)據(jù)流程。人力資源管理員處理記錄工種津貼基本信息月工資生成報(bào)表

5、統(tǒng)計(jì)查詢處理職工2.3數(shù)據(jù)流圖e1人力資源部更新員工信息p1入職登記表e2員工分配部門p2s1員工信息數(shù)據(jù)入職登記表新的員工信息離職p4工作證部門信息離職表工作證離職表s2 登記記錄入職登記表工號(hào)2.4 數(shù)據(jù)字典數(shù)據(jù)流圖表達(dá)了數(shù)據(jù)和處理的關(guān)系，數(shù)據(jù)字典則是系統(tǒng)中各類數(shù)據(jù)描述的集合，是進(jìn)行詳細(xì)的數(shù)據(jù)收集和數(shù)據(jù)分析所獲得的主要成果。數(shù)據(jù)字典在數(shù)據(jù)庫設(shè)計(jì)中占有很重要的地位。數(shù)據(jù)字典通常包括數(shù)據(jù)項(xiàng)、數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)流、數(shù)據(jù)存儲(chǔ)和處理過程5個(gè)部分。其中數(shù)據(jù)項(xiàng)是數(shù)據(jù)的最小組成單位，若干個(gè)數(shù)據(jù)項(xiàng)可以組成一個(gè)數(shù)據(jù)結(jié)構(gòu)，數(shù)據(jù)字典通過對(duì)數(shù)據(jù)項(xiàng)和數(shù)據(jù)結(jié)構(gòu)的定義來描述數(shù)據(jù)流、數(shù)據(jù)存儲(chǔ)的邏輯內(nèi)容。2.4.1 數(shù)據(jù)項(xiàng)定義數(shù)

6、據(jù)項(xiàng)是不可在分的數(shù)據(jù)單位。下面定義了工資管理系統(tǒng)需要的數(shù)據(jù)項(xiàng)：表2.1 數(shù)據(jù)項(xiàng)定義數(shù)據(jù)項(xiàng)編號(hào)數(shù)據(jù)項(xiàng)名稱簡(jiǎn)述類型及寬度i1sno員工號(hào)char（10）i2sname員工姓名char(20)i3sex性別char（4）i4sage年齡inti5dno部門編號(hào)char(10)i6dname部門名稱char(10)i7dallowance部門福利inti8jno工作編號(hào)char(10)i19jname工作名稱char(20)2.4.2 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)反映了數(shù)據(jù)之間的組合關(guān)系，。一個(gè)數(shù)據(jù)結(jié)構(gòu)可以由若干個(gè)數(shù)據(jù)項(xiàng)組成，也可以由若干個(gè)數(shù)據(jù)結(jié)構(gòu)組成，或由若干個(gè)數(shù)據(jù)項(xiàng)和數(shù)據(jù)結(jié)構(gòu)混合組成。對(duì)數(shù)據(jù)結(jié)構(gòu)的描述通常包括

7、數(shù)據(jù)結(jié)構(gòu)名、含義等。表2-2 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)定義數(shù)據(jù)結(jié)構(gòu)名數(shù)據(jù)結(jié)構(gòu)含義組成ds-1staffinf在職職工信息sno，sname，sex，sage,dno，dname，jno，jnameds-2job職位信息dno，dname，jno，jnameds-3in入職登記表sno，sname，sex，sage,dno，dname，jno，jnameds-4out離職表sno，sname，sex，sage,dno，dname，jno，jname2.4.3 數(shù)據(jù)流數(shù)據(jù)流是數(shù)據(jù)結(jié)構(gòu)在系統(tǒng)內(nèi)傳輸?shù)穆窂?。?duì)數(shù)據(jù)流的描述通常包括以下內(nèi)容:數(shù)據(jù)流名、說明、數(shù)據(jù)流來源、數(shù)據(jù)流去向、組成等。表2-3 數(shù)據(jù)流數(shù)據(jù)流編

8、號(hào)數(shù)據(jù)流名稱簡(jiǎn)述數(shù)據(jù)流來源數(shù)據(jù)流去向數(shù)據(jù)流組成f1員工信息表記錄員工信息 人力資源部員工sno，sname，sex，sage,dno，dname，jno，jnamef2入職表 作為員工入職憑證人力資源部員工sno，sname，sex，sage,dno，dname，jno，jnamef3工牌作為員工在公司身份的憑證部門員工，人力資源部sno sname dname jnamef4離職表作為員工離職的憑證人力資源部員工sno，sname，sex，sage,dno，dname，jno，jnamef5 職位信息表列出公司職位信息人力資源部員工dno dname jno jname2.1.4.4 數(shù)據(jù)存

9、儲(chǔ)數(shù)據(jù)存儲(chǔ)是數(shù)據(jù)結(jié)構(gòu)停留或保存的地方，也是數(shù)據(jù)流的來源和去向之一。他可以是手工文檔或手工憑單，也可以是計(jì)算機(jī)文檔。表 2-4 數(shù)據(jù)存儲(chǔ)數(shù)據(jù)存儲(chǔ)編號(hào)數(shù)據(jù)存儲(chǔ)名稱簡(jiǎn)述數(shù)據(jù)存儲(chǔ)組成相關(guān)處理s1員工信息記錄員工信息sno，sname，sex，sage,dno，dname，jno，jnamep1，p2，p3s2登記記錄記錄員工職位信息sno，sname，sex，sage,dno，dname，jno，jnamep12. 4.5 處理過程處理過程的具體處理邏輯一般是用判定表或判定樹來描述。數(shù)據(jù)字典中只需要描述處理過程的說明信息，通常包括處理過程編號(hào)、名稱、說明、輸入數(shù)據(jù)流、輸出數(shù)據(jù)流、處理等。表 2-5 處

10、理過程處理過程編號(hào)處理過程名稱簡(jiǎn)述輸入的數(shù)據(jù)流處理輸出地?cái)?shù)據(jù)流p1更新員工信息員工入職后，更新信息入職表對(duì)員工信息數(shù)據(jù)進(jìn)行更新操作新的員工信息p2分配部門員工選擇工作入職表選擇可以勝任的工作工牌、工號(hào)p3離職員工里開始辦理退工手續(xù)離職申請(qǐng)表離職表離職信息3 概念設(shè)計(jì)將需求分析得到的用戶需求抽象為信息結(jié)構(gòu)即概念模型的過程就是概念結(jié)構(gòu)設(shè)計(jì)。它是整個(gè)數(shù)據(jù)庫設(shè)計(jì)的關(guān)鍵。e-r圖根據(jù)需求分析的結(jié)果，得出本系統(tǒng)的實(shí)體-關(guān)系圖（e-r圖）如下所示：?jiǎn)T工部門職工姓名職工性別職工年齡職工電話職工工種職工部門職工編號(hào)部門編號(hào)部門津貼部門電話部門名稱屬于工資考勤表工種是否出勤日期職工編號(hào)基本工資工種名稱部門編號(hào)工種

11、編號(hào)擁有扣除基本工資補(bǔ)貼4 邏輯設(shè)計(jì)概念結(jié)構(gòu)是獨(dú)立于任何一種數(shù)據(jù)模型的信息結(jié)構(gòu)。邏輯結(jié)構(gòu)設(shè)計(jì)的任務(wù)就是把概念結(jié)構(gòu)設(shè)計(jì)階段設(shè)計(jì)好的基本e-r圖轉(zhuǎn)換為與所選用的dbms產(chǎn)品所支持的數(shù)據(jù)模型想符合的邏輯結(jié)構(gòu)。4.1 e-r 圖向關(guān)系模型轉(zhuǎn)換把總e-r圖轉(zhuǎn)換成為關(guān)系模式，關(guān)系的碼用下劃線標(biāo)出員工（員工號(hào) 姓名 性別 年齡 職業(yè)號(hào) 部門號(hào)）部門（員工號(hào) 部門號(hào) 部門福利 部門名）職業(yè)（員工號(hào) 職業(yè)號(hào) 職業(yè)名）工資（員工號(hào) 基本工資 部門福利）4.2 數(shù)據(jù)模型的優(yōu)化數(shù)據(jù)邏輯設(shè)計(jì)的結(jié)果不是唯一的。為了進(jìn)一步提高數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能，還應(yīng)該分局應(yīng)用系統(tǒng)的需要適當(dāng)?shù)男薷模{(diào)整數(shù)據(jù)模型的結(jié)構(gòu)，這就是數(shù)據(jù)模型的優(yōu)化

12、。關(guān)系數(shù)據(jù)庫模型的優(yōu)化通常以規(guī)范化理論為指導(dǎo)，方法為：確定數(shù)據(jù)依賴。對(duì)于各個(gè)關(guān)系模式之間的數(shù)據(jù)依賴進(jìn)行極小化處理，消除冗余的聯(lián)系。按照數(shù)據(jù)依賴的理論對(duì)關(guān)系模式逐一進(jìn)行分析，考察是否存在部分函數(shù)依賴、傳遞函數(shù)依賴、多值依賴等，確定個(gè)關(guān)系模式分別屬于第幾范式。按照需求分析階段得到的處理要求，分析對(duì)于這樣的應(yīng)用環(huán)境這些模式是否合適，確定是否要求對(duì)某些模式進(jìn)行合并或分解。對(duì)關(guān)系模式進(jìn)行必要的分解，提高數(shù)據(jù)操作的效率和存儲(chǔ)空間利用率。常用的兩種分解發(fā)事水平分解法和垂直分解法。規(guī)范化理論為數(shù)據(jù)庫設(shè)計(jì)人員判斷關(guān)系模式優(yōu)劣提供了理論標(biāo)準(zhǔn)，可以來預(yù)測(cè)模式可能出現(xiàn)的問題，使數(shù)據(jù)庫設(shè)計(jì)工作有了嚴(yán)格的理論基礎(chǔ)。5 物

13、理設(shè)計(jì)數(shù)據(jù)庫在物理設(shè)備上的存儲(chǔ)結(jié)構(gòu)與存取方法稱為數(shù)據(jù)庫的物理結(jié)構(gòu)，它依賴于選定的數(shù)據(jù)庫管理系統(tǒng)。為一個(gè)給定的邏輯數(shù)據(jù)模型選取一個(gè)最適合應(yīng)用要求的物理結(jié)構(gòu)的過程，就是數(shù)據(jù)庫的物理設(shè)計(jì)。5.1 建立觸發(fā)器這個(gè)系統(tǒng)為數(shù)據(jù)表的更新建立了兩個(gè)觸發(fā)器，一個(gè)是在有新的員工辦理入職手續(xù)時(shí)，插入新員工信息，觸發(fā)觸發(fā)器，使員工信息得到更新。另外就是在有離職員工辦理離職手續(xù)之后，使觸發(fā)器觸發(fā)，更新員工信息表。表 2-6 定義觸發(fā)器觸發(fā)器名觸發(fā)器的作用view_delete在有離職員工辦理離職手續(xù)之后，使觸發(fā)器觸發(fā)，刪除該員工信息view_insert在有新的員工辦理入職手續(xù)時(shí)，插入新員工信息，觸發(fā)觸發(fā)器，在員工信息

14、表里添加信息5.2 建立存儲(chǔ)過程存儲(chǔ)過程是存儲(chǔ)在服務(wù)器端由一組編譯的transactsql語句構(gòu)成的sql sever數(shù)據(jù)庫應(yīng)用程序，它能夠檢索系統(tǒng)信息、管理數(shù)據(jù)庫或執(zhí)行其他操作。為了方便操作，為本系統(tǒng)建立了如下存儲(chǔ)過程：表2-7 存儲(chǔ)過程存儲(chǔ)過程編號(hào)存儲(chǔ)過程名稱存儲(chǔ)過程作用p1stfupdate更新員工信息表p2deptupdate更新部門信息p3outer更新離職員工信息6 數(shù)據(jù)庫實(shí)施階段6.1 建立數(shù)據(jù)庫create database gongzion( name=gongzi_data, filename=d:gongzi.mdf, size=5, maxsize=50, filegr

15、owth=5)log on( name=gongzi_log, filename=d:gongzi.ldf, size=5, maxsize=40, filegrowth=8)6.2 使用數(shù)據(jù)庫use gongzi6.3 創(chuàng)建四張表create table staff ( snum char(7) unique not null, sname char(8), sex char(2), age char(2), zname char(8), dname char(8) )6.4 刪除表drop table departmentcreate table salary ( snum char(7)

16、 , bsalary float(11), fuli float(7), jiangli float(7) )create table profession ( snum char(7) , znum char(7) , zname char(8) )create table department ( snum char(7) , dnum char(7) , dname char(8) -) 6.5 創(chuàng)建四個(gè)存儲(chǔ)過程create procedure cunru ( snum char(7), sname char(8), sex char(2), age char(2), zname cha

17、r(8), dname char(8) -)asinsert into staff values (snum,sname,sex,age,zname,dname)create procedure cunru1 snum char(7) , bsalary float(11), fuli float(7), jiangli float(7) asinsert into salary values (snum,bsalary,fuli,jiangli)create procedure cunru2 ( snum char(7), znum char(7) , zname char(8) )asin

18、sert into profession values (snum,znum,zname) create procedure cunru3 ( snum char(7), dnum char(7) , dname char(8) )asinsert into department values (snum,dnum,dname)6.6 刪除drop procedure cunru delete from staff -6.7 信息錄入exec cunru snum=001,sname=張三,sex=男,age=45,zname=銷售員,dname=銷售部exec cunru snum=002,

19、sname=王五,sex=男,age=48,zname=設(shè)計(jì)師,dname=管理部exec cunru snum=003,sname=趙六,sex=男,age=42,zname=測(cè)試員,dname=設(shè)計(jì)部 exec cunru1 snum=001,bsalary=5000,fuli=1000,jiangli=500exec cunru1 snum=002,bsalary=5500,fuli=1200,jiangli=400exec cunru1 snum=003,bsalary=4500,fuli=1100,jiangli=405exec cunru2 snum=001,znum=11,zna

20、me=銷售員exec cunru2 snum=002,znum=12,zname=工程師exec cunru2 snum=003,znum=13,zname=設(shè)計(jì)師exec cunru3 snum=001,dnum=101,dname=管理部exec cunru3 snum=002,dnum=102,dname=設(shè)計(jì)部exec cunru3 snum=003,dnum=103,dname=銷售部6.8 創(chuàng)建查詢視圖create view chaxun asselect staff.snum,sname,sex,age,bsalary+fuli+jiangli as 總工資,staff.znam

21、e,znum,staff.dname,dnumfrom staff,salary,profession,departmentwhere staff.snum=salary.snum and staff.snum=profession.snum and staff.snum=department.snum6.9 查詢select *from chaxun6.10 創(chuàng)建觸發(fā)器create trigger update0on stafffor update as declare zname chardeclare znum intdeclare snum intset snum=(select sn

22、um from inserted )update profession set zname=(select zname from inserted),znum=(select distinct znum from profession where zname in(select zname from inserted) where snum=snumcreate trigger update1on stafffor updateas declare dname chardeclare dnum intdeclare snum intset snum=(select snum from inse

23、rted )update department set dname=(select dname from inserted),dnum=(select distinct dnum from department where dname in (select dname from inserted)where snum=snum6.11 刪除觸發(fā)器drop trigger update06.12 創(chuàng)建修改數(shù)據(jù)的存儲(chǔ)過程6.12.1 在表staff上創(chuàng)建存儲(chǔ)過程create procedure xiugai( snum char(11), sname char(8), sex char(2), a

24、ge char(2), zname char(10), dname char(10) )as update staffset sname=sname,sex=sex,age=age,zname=zname,dname=dnamewhere snum=snum-修改exec xiugai snum=001,sname=李四,sex=男,age=40,zname=工程師,dname=開發(fā)部-修改后查詢select *from staffselect *from professionselect *from department6.12.2 在表salary上創(chuàng)建存儲(chǔ)過程create procedu

25、re xiugai1( snum char(11) , bsalary float(11), fuli float(7), jiangli float(7) )asupdate salaryset bsalary=bsalary,fuli=fuli,jiangli=jiangliwhere snum=snum-修改exec xiugai1 snum=001,bsalary=11200,fuli=2000,jiangli=1000-修改后查詢select *from salarydrop trigger tongbu-創(chuàng)建刪除數(shù)據(jù)功能的觸發(fā)器create trigger tongbuon stafffor deleteas delete from salarywhere snum=(select snum from deleted ) delete from professio