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

1、工資管理系統(tǒng)設(shè)計數(shù)據(jù)庫文檔說明（設(shè)計者：黃劍鋒）一、創(chuàng)建表以及表之間的關(guān)系1、創(chuàng)建表（1） 員工基本信息表（employeebaseinfo）列名數(shù)據(jù)類型長度允許空主鍵說明employee_idchar9no null是員工編號employee_namenvarchar8no null否姓名sexbit1no null否性別birthsmalldatetime4no null否出生年月workingtimesmalldatetime4no null否參加工作時間department_idchar4no null否部門編號position_idchar4no null否職稱編號politicsc

2、har4null否政治面貌marriagechar4null否婚姻狀況性別：缺省值為1（男）政治面貌：缺省值為群眾，設(shè)計實施規(guī)則政治面貌（politics_rule）只能是群眾團員黨員之一 婚姻狀況：缺省值為未婚，設(shè)計實施規(guī)則婚姻狀況(marriage_rule)為未婚已婚離異中的一種設(shè)計索引：主鍵員工編號（2） 員工工資表（employeepay）列名數(shù)據(jù)類型長度允許空主鍵說明midyearsmalldatetime4no null是年月employee_idchar9no null是員工編號housingsmallmoney4null否住房補貼subsidysmallmoney4null否

3、津貼unionfeesmallmoney4null否工會會費waterelecfeesmallmoney4no null否水電費housingfeesmallmoney4null否住房公積金agedsafetysmallmoney4no null否養(yǎng)老保險prizesmallmoney4null否獎金punishsmallmoney4null否懲罰basepaysmallmoney4no null 否基本工資postpaysmallmoney4no null否崗位工資實施check約束，養(yǎng)老保險：agedsafety>0住房補貼 津貼 工會會費 住房公積金 獎金 懲罰 缺省值均為0設(shè)計索

4、引：主鍵員工編號、年月（3） 部門信息表（departmentinfo）列名數(shù)據(jù)類型長度允許空主鍵說明department_idchar4no null是部門編號department_namenvarchar8no null 否部門名稱department_headernvarchar8no null否部門負責(zé)人 設(shè)計索引：主鍵部門編號（4）職稱表（position）列名數(shù)據(jù)類型長度允許空主鍵說明position_idchar4no null是職稱編號position_namenvarchar16no null 否職稱名basepaysmallmoney4no null 否基本工資postpa

5、ysmallmoney4no null否崗位工資 設(shè)計索引：主鍵職稱編號2、表之間的關(guān)系二、工資管理系統(tǒng)功能描述1、 系統(tǒng)功能敘述本系統(tǒng)所描述的管理系統(tǒng)的主要功能包括:（1） 員工基本信息管理l 員工基本信息的錄入,包括員工編號 姓名 性別 出生年月 參加工作時間 所屬部門編號 職稱編號 政治面貌 婚姻狀況等信息;l 員工信息的增加;l 員工信息的刪除;l 員工信息的查詢;l 員工信息的修改;（2） 員工工資管理l 員工工資信息的錄入,包括工資月份 獎金 懲罰金 津貼 住房補貼 工會會費 水電費 住房公積金 養(yǎng)老保險 基本工資 崗位工資 l 員工工資信息的增加;l 員工工資信息的刪除;l 員工

6、工資信息的查詢;l 員工工資信息的修改;（3） 部門信息管理l 部門信息的錄入,包括部門編號 部門名稱 部門負責(zé)人;l 部門信息的增加;l 部門信息的刪除;l 部門信息的查詢;l 部門信息的修改;2、 系統(tǒng)功能模塊示意圖工資管理系統(tǒng)員工工資管理部門信息管理員工基本信息管理 部門信息的修改增加查詢刪部門信息的錄入員工工資的修改增加查詢刪員工工資信息的錄入員工信息的錄入員工信息的修改增加查詢刪除 3、 系統(tǒng)功能模塊的實現(xiàn) 存儲過程功能塊：1) 企業(yè)人數(shù)匯總或部門人數(shù)匯總代碼：create procedure companyordepartment_num department_id char(4)

7、=null asif department_id is nullbeginprint '請檢查輸入是否正確'print '請重新輸入'returnendselect department_id as '部門編號' ,count(employeebaseinfo.employee_id) as '部門人數(shù)匯總'from employeebaseinfowhere employeebaseinfo.department_id=department_idgroup by department_idselect count(employe

8、ebaseinfo.employee_id) as '企業(yè)人數(shù)匯總'from employeebaseinfogo示例：2）新增員工代碼：create procedure addemployeeemployee_id char(9)=null,employee_name nvarchar(8)=null,sex char(2)=null,birth smalldatetime=null,workingtime smalldatetime=null,department_id char(4)=null,position_id char(4)=null,politics char(

9、4)=null,marriage char(4)=nullasif employee_id is null oremployee_name is null orsex is null orbirth is null orworkingtime is null ordepartment_id is null orposition_id is null orpolitics is null ormarriage is nullbeginprint'請輸入員工信息'print'你必須提供員工的員工編號、姓名、性別、出生年月、參加工作時間、 所屬部門編號、婚姻狀況、'p

10、rint'（政治面貌、職稱編號可以為空）'returnenddeclare bitsex bit if sex='男'set bitsex=1else set bitsex=0begin transactioninsert employeebaseinfo(employee_id ,employee_name ,sex ,birth ,workingtime,department_id,position_id,politics, marriage )values(employee_id ,employee_name,bitsex ,birth ,workingt

11、ime ,department_id ,position_id ,politics,marriage)if error <> 0beginrollback transactionreturnendcommit transactionprint '員工'+employee_name+'的信息已經(jīng)添加到employeebaseinfo表中.'go示例：3）員工刪除代碼：create procedure employeedeleteemployee_id char(9)asbegin trandelete from employeebaseinfowher

12、e employee_id=employee_idcommit tranprint'員工編號為' +employee_id+ '的員工信息已經(jīng)全部刪除。'go示例： 4）員工信息修改代碼：create procedure updateemployeeasbeginupdate employeebaseinfoset politics='黨員'where employee_id='09' end print '該員工的部分信息已經(jīng)修改，其政治面貌已經(jīng)由群眾改為黨員。'go示例：5）某員工信息及全部員工信息查詢代碼：c

13、reate procedure employeeemployee_id char(9)asselectemployeebaseinfo.employee_id,employeebaseinfo.employee_name,employeebaseinfo.sex,employeebaseinfo.birth,employeebaseinfo.workingtime,employeebaseinfo.politics,employeebaseinfo.marriage, departmentinfo.department_name,position.position_namefrom emplo

14、yeebaseinfo inner join departmentinfo on employeebaseinfo.department_id=departmentinfo.department_idinner join position on employeebaseinfo.position_id=position.position_idwhere employeebaseinfo.employee_id=employee_idprint'已經(jīng)查詢到該員工的信息。'selectemployeebaseinfo.employee_id,employeebaseinfo.emp

15、loyee_name,employeebaseinfo.sex,employeebaseinfo.birth,employeebaseinfo.workingtime,employeebaseinfo.politics,employeebaseinfo.marriage, departmentinfo.department_name,position.position_namefrom employeebaseinfo inner join departmentinfo on employeebaseinfo.department_id=departmentinfo.department_id

16、inner join position on employeebaseinfo.position_id=position.position_idprint'該企業(yè)的所有員工的信息已經(jīng)全部顯示。'go示例： 6）新增部門代碼：create procedure adddepartmentdepartment_id char(4)=null,department_name nvarchar(8)=null, department_header nvarchar(8)=null,department_num smallint=nullasifdepartment_id is null

17、ordepartment_name is null ordepartment_header is null ordepartment_num is null beginprint'請輸入該部門的具體信息'print'請重新輸入'returnendbegin traninsert departmentinfo(department_id,department_name,department_header ,department_num)values(department_id,department_name,department_header,department

18、_num )if error <> 0beginrollback tranreturnendcommit tranprint'部門'+department_name+'的信息已經(jīng)成功添加到表departmentinfo中。'go示例：7）部門刪除代碼：create procedure departmentdeletedepartment_name nvarchar(8)asbegin trandelete from departmentinfowhere department_name=department_nameif error <>

19、 0beginrollback tranreturnendcommit tranprint'部門'+department_name +'的信息已經(jīng)全部刪除'go示例： 8）部門信息修改代碼：create procedure updatedepartmentasbeginupdate departmentinfoset department_name='張小東'where department_id='d' end print '該部門的部分信息已經(jīng)修改，其負責(zé)人已經(jīng)由方方改為張小東。'go示例：9）全部部門信息及部

20、門信息查詢（實現(xiàn)查詢指定部門信息）代碼：create procedure departmentdepartment_id char(4)=null asif department_id is nullbeginprint' 請檢查輸入是否正確 'print' 請重新輸入'returnendselect department_id ,department_name,department_header,department_numfrom departmentinfowhere department_id=department_idselect department

21、_id,department_name,department_header,department_numfrom departmentinfogo示例：10）員工工資增加代碼：create procedure addemployeepaymidyear smalldatetime=null,employee_id char(4)=null, position_id char(4)=null,housing smallmoney=null,subsidy smallmoney=null,unionfee smallmoney=null,waterelecfee smallmoney=null,h

22、ousingfee smallmoney=null,agedsafety smallmoney=null,prize smallmoney=null,punish smallmoney=null,set basepay=(select basepay from position) smallmoney,set postpay=(select postpay from position) smallmoneyasifmidyear is null oremployee_id is null orposition_id is null orhousing is null orsubsidy is

23、null orunionfee is null orwaterelecfee is null orhousingfee is null oragedsafety is null orprize is null orpunish is null beginprint'請輸入具體的工資信息'print'請重新輸入'returnendbegin traninsert employeepay (midyear,employee_id,position_id,housing,subsidy,unionfee,waterelecfee, housingfee,agedsaf

24、ety,prize,punish,basepay,postpay)values (midyear,employee_id,position_id,housing,subsidy,unionfee,waterelecfee, housingfee,agedsafety,prize,punish,basepay,postpay)if error <> 0beginrollback tranreturnendcommit tranprint '工資信息已經(jīng)成功添加到表employeepay中。'go示例：11）員工工資刪除代碼：create procedure emplo

25、yeepaydeleteemployee_id char(9)asbegin trandelete from employeepaywhere employee_id=employee_idcommit tranprint'已經(jīng)刪除員工編號為'+employee_id+'的員工的工資信息。'go示例：12）員工工資修改代碼：create procedure updateemployeepayasbeginupdate employeepayset prize=3000,subsidy=5000where employee_id='04' end

26、print '該員工的部分工資信息已經(jīng)修改，其獎金和津貼已經(jīng)提高。'go示例：13）員工工資查詢代碼：create procedure anyoneemployeepayid char(7)asselectemployeepay.employee_id,employee_name,position_name,department_name,midyear,housing,subsidy,unionfee,waterelecfee,housingfee,agedsafety,prize,punish,position.basepay,position.postpayfrom em

27、ployeepay inner join employeebaseinfoon employeebaseinfo.employee_id=employeepay.employee_id inner join positionon employeepay.position_id=position.position_id inner join departmentinfoon employeebaseinfo.department_id=departmentinfo.department_idwhere midyear=convert(smalldatetime,id+'-1')p

28、rint'已經(jīng)查詢到該月份員工工資的信息。'go示例：14)企業(yè)年/月工資匯總（實現(xiàn)的功能是匯總某年總的工資支出及某年的某月工資匯總） 及各部門年/月工資匯總代碼：create procedure companyordepartment_payoutmidyear_id char(7),department_id char(4)asif midyear_id is null ordepartment_id is nullbeginprint'請檢查輸入是否正確。'print'請重新輸入。'returnendif len(midyear_id)=4

29、beginselect sum(employeepay.punish+employeepay.prize+employeepay.subsidy+employeepay.housing+position.basepay+position.postpay-employeepay.agedsafety-employeepay.housingfee-employeepay.waterelecfee-employeepay.unionfee)as'企業(yè)工資總額'from employeebaseinfo inner join employeepayon employeebaseinfo

30、.employee_id=employeepay.employee_id inner join positionon position.position_id=employeepay.position_idwhere convert(char(7),year(employeepay.midyear)=midyear_idselect employeebaseinfo.department_id,sum(employeepay.prize+employeepay.subsidy+employeepay.housing+position.basepay+position.postpay-emplo

31、yeepay.agedsafety-employeepay.housingfee-employeepay.waterelecfee-employeepay.unionfee-employeepay.punish)as '部門工資匯總'from employeebaseinfo inner join employeepayon employeebaseinfo.employee_id=employeepay.employee_id inner join positionon position.position_id=employeepay.position_idwhere con

32、vert(char(7),year(employeepay.midyear)=midyear_id and employeebaseinfo.department_id=department_id group by employeebaseinfo.department_idend elsebeginif len(midyear_id)=6 or len(midyear_id)=7select sum(employeepay.punish+employeepay.prize+employeepay.subsidy+employeepay.housing+position.basepay+pos

33、ition.postpay-employeepay.agedsafety-employeepay.housingfee-employeepay.waterelecfee-employeepay.unionfee)as'企業(yè)工資總額'from employeebaseinfo inner join employeepayon employeebaseinfo.employee_id=employeepay.employee_id inner join positionon position.position_id=employeepay.position_idwhere empl

34、oyeepay.midyear=convert(smalldatetime,midyear_id +'-1')select employeebaseinfo.department_id,sum(employeepay.prize+employeepay.subsidy+employeepay.housing+position.basepay+position.postpay-employeepay.agedsafety-employeepay.housingfee-employeepay.waterelecfee-employeepay.unionfee-employeepay

35、.punish)as '部門工資匯總'from employeebaseinfo inner join employeepayon employeebaseinfo.employee_id=employeepay.employee_id inner join positionon position.position_id=employeepay.position_idwhere employeepay.midyear=convert(smalldatetime,midyear_id +'-1')and employeebaseinfo.department_id

36、=department_idgroup by employeebaseinfo.department_idendgo示例：視圖功能塊：15）瀏覽工資信息表（每個月份所有員工或某個部門員工的工資信息）a、所有員工月份工資：代碼：create view employeemonthpay_viewasselect employeebaseinfo.employee_id,employeebaseinfo.employee_name,employeebaseinfo.department_id, (employeepay.housing+employeepay.subsidy) as '工資補

37、貼', (employeepay.unionfee+employeepay.waterelecfee+employeepay.housingfee+employeepay.agedsafety+ employeepay.punish) as '應(yīng)扣費用',(position.basepay+position.postpay) as '應(yīng)發(fā)工資', (employeepay.housing+employeepay.subsidy+position.basepay+position.postpay- employeepay.unionfee-employeepay.waterelecfee-employeepay.housingfee-employeepay.agedsafety- employeepay.punish) as '實際應(yīng)發(fā)工資 ', position.postpay,employeepay