工资数据库管理系统

工资管理系统设计数据库文档说明（设计者：黄剑锋）一、创建表以及表之间的关系1、创建表（1） 员工基本信息表（employeebaseinfo）列名数据类型长度允许空主键说明employee_idchar9No null是员工编号employee_namenvarchar8No null否姓名sexbit1No null否性别birthsmalldatetime4No null否出生年月workingtimesmalldatetime4No null否参加工作时间department_idchar4No null否部门编号position_idchar4No null否职称编号politicschar4Null否政治面貌marriagechar4Null否婚姻状况性别：缺省值为1（男）政治面貌：缺省值为群众，设计实施规则政治面貌（politics_rule）只能是群众团员党员之一 婚姻状况：缺省值为未婚，设计实施规则婚姻状况(marriage_rule)为未婚已婚离异中的一种设计索引：主键员工编号（2） 员工工资表（employeepay）列名数据类型长度允许空主键说明midyearsmalldatetime4No null是年月employee_idchar9No null是员工编号housingsmallmoney4Null否住房补贴subsidysmallmoney4Null否津贴unionfeesmallmoney4Null否工会会费waterelecfeesmallmoney4No null否水电费housingfeesmallmoney4Null否住房公积金agedsafetysmallmoney4No null否养老保险prizesmallmoney4Null否奖金punishsmallmoney4Null否惩罚basepaysmallmoney4No null 否基本工资postpaysmallmoney4No null否岗位工资实施CHECK约束，养老保险：agedsafety0住房补贴 津贴 工会会费 住房公积金 奖金 惩罚 缺省值均为0设计索引：主键员工编号、年月（3） 部门信息表（departmentinfo）列名数据类型长度允许空主键说明department_idchar4No null是部门编号department_namenvarchar8No null 否部门名称department_headernvarchar8No null否部门负责人 设计索引：主键部门编号（4）职称表（position）列名数据类型长度允许空主键说明position_idchar4No null是职称编号position_namenvarchar16No null 否职称名basepaysmallmoney4No null 否基本工资postpaysmallmoney4No null否岗位工资 设计索引：主键职称编号2、表之间的关系二、工资管理系统功能描述1、 系统功能叙述本系统所描述的管理系统的主要功能包括:（1） 员工基本信息管理l 员工基本信息的录入,包括员工编号 姓名 性别 出生年月 参加工作时间 所属部门编号 职称编号 政治面貌 婚姻状况等信息;l 员工信息的增加;l 员工信息的删除;l 员工信息的查询;l 员工信息的修改;（2） 员工工资管理l 员工工资信息的录入,包括工资月份 奖金 惩罚金 津贴 住房补贴 工会会费 水电费 住房公积金 养老保险 基本工资 岗位工资 l 员工工资信息的增加;l 员工工资信息的删除;l 员工工资信息的查询;l 员工工资信息的修改;（3） 部门信息管理l 部门信息的录入,包括部门编号 部门名称 部门负责人;l 部门信息的增加;l 部门信息的删除;l 部门信息的查询;l 部门信息的修改;2、 系统功能模块示意图工资管理系统员工工资管理部门信息管理员工基本信息管理 部门信息的修改增加查询删部门信息的录入员工工资的修改增加查询删员工工资信息的录入员工信息的录入员工信息的修改增加查询删除 3、 系统功能模块的实现 存储过程功能块：1) 企业人数汇总或部门人数汇总代码：CREATE PROCEDURE companyordepartment_num department_id char(4)=null ASif department_id is nullbeginprint 请检查输入是否正确print 请重新输入returnendselect department_id as 部门编号 ,count(employeebaseinfo.employee_id) as 部门人数汇总from employeebaseinfowhere employeebaseinfo.department_id=department_idgroup by department_idselect count(employeebaseinfo.employee_id) as 企业人数汇总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(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你必须提供员工的员工编号、姓名、性别、出生年月、参加工作时间、 所属部门编号、婚姻状况、print（政治面貌、职称编号可以为空）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 ,workingtime ,department_id ,position_id ,politics,marriage)if error 0beginrollback transactionreturnendcommit transactionprint 员工+employee_name+的信息已经添加到employeebaseinfo表中.GO示例：3）员工删除代码：CREATE PROCEDURE employeedeleteemployee_id char(9)ASbegin trandelete from employeebaseinfowhere employee_id=employee_idcommit tranprint员工编号为 +employee_id+ 的员工信息已经全部删除。GO示例： 4）员工信息修改代码：CREATE PROCEDURE updateemployeeASbeginupdate employeebaseinfoset politics=党员where employee_id=09 end print 该员工的部分信息已经修改，其政治面貌已经由群众改为党员。GO示例：5）某员工信息及全部员工信息查询代码：CREATE 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 employeebaseinfo inner join departmentinfo on employeebaseinfo.department_id=departmentinfo.department_idinner join position on employeebaseinfo.position_id=position.position_idwhere employeebaseinfo.employee_id=employee_idprint已经查询到该员工的信息。selectemployeebaseinfo.employee_id,employeebaseinfo.employee_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_idinner join position on employeebaseinfo.position_id=position.position_idprint该企业的所有员工的信息已经全部显示。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 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_num )if error 0beginrollback tranreturnendcommit tranprint部门+department_name+的信息已经成功添加到表departmentinfo中。GO示例：7）部门删除代码：CREATE PROCEDURE departmentdeletedepartment_name nvarchar(8)ASbegin trandelete from departmentinfowhere department_name=department_nameif error 0beginrollback tranreturnendcommit tranprint部门+department_name +的信息已经全部删除GO示例： 8）部门信息修改代码：CREATE PROCEDURE updatedepartmentASbeginupdate departmentinfoset department_name=张小东where department_id=D end print 该部门的部分信息已经修改，其负责人已经由方方改为张小东。GO示例：9）全部部门信息及部门信息查询（实现查询指定部门信息）代码：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_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,housingfee 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 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,agedsafety,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 工资信息已经成功添加到表employeepay中。GO示例：11）员工工资删除代码：CREATE PROCEDURE employeepaydeleteemployee_id char(9)ASbegin trandelete from employeepaywhere employee_id=employee_idcommit tranprint已经删除员工编号为+employee_id+的员工的工资信息。GO示例：12）员工工资修改代码：CREATE PROCEDURE updateemployeepayASbeginupdate employeepayset prize=3000,subsidy=5000where employee_id=04 end print 该员工的部分工资信息已经修改，其奖金和津贴已经提高。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 employeepay 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)print已经查询到该月份员工工资的信息。GO示例：14)企业年/月工资汇总（实现的功能是汇总某年总的工资支出及某年的某月工资汇总） 及各部门年/月工资汇总代码：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)=4beginselect sum(employeepay.punish+employeepay.prize+employeepay.subsidy+employeepay.housing+position.basepay+position.postpay-employeepay.agedsafety-employeepay.housingfee-employeepay.waterelecfee-employeepay.unionfee)as企业工资总额from employeebaseinfo inner join employeepayon employeebaseinfo.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-employeepay.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 convert(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+position.postpay-employeepay.agedsafety-employeepay.housingfee-employeepay.waterelecfee-employeepay.unionfee)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)select employeebaseinfo.department_id,sum(employeepay.prize+employeepay.subsidy+employeepay.housing+position.basepay+position.postpay-employeepay.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 employeepay.midyear=convert(smalldatetime,midyear_id +-1)and employeebaseinfo.department_id=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 工资补贴, (employeepay.unionfee+employeepay.waterelecfee+employeepay.housingfee+employeepay.agedsafety+ employeepay.punish) as 应扣费用,(position.basepay+position.postpay) as 应发工资, (employeepay.housing+employeepay.subsidy+position.basepay+position.postpay- employeepay.unionfee-employeepay.waterelecfee-employeepay.housingfee-employeepay.agedsafety- employeepay.punish) as 实际应发工资 , p