人力资源数据库设计计划书

1、.：.；人事管理系统数据库设计数据库构造图员工档案简表员工编号调动时间调前部门编号调后部门编号调前职务编号调后职务编号调动缘由经办人调动类型备注职务调动表FkPK调动编号员工编号PK部门员工姓名性别出生日期民族婚姻情况政治容颜籍贯身份证号地址学历专业毕业院校联络电子邮件部门编号职务号入职时间银行卡号FkFk部门号学院时间教育程度备注教育阅历表Fk员工编号部门称号部门部门指点部门职能职位表职位号pk部门编号工资月份根本工资奖金工资交通补贴失业保险养老保险个人所得税应发工资工资管理表Pk员工编号职位称号部门号培训员工表公司时间职位备注任务阅历表Pk员工编号PK培训员工编号员工编号培训编号培训员工姓

2、名FkFk培训管理表培训编号PK主题开场日期终了日期培训内容培训员工编号备注阐明：由于页面大小的限制，该中文的权限构造图可以在英文构造图中看到。图一中文构造图Train 培训管理表Tra_id int培训编号 pkTitle varchar() 主题Sta_date varchar() 开场日期End_date varchar() 终了日期Cont varchar() 培训内容Person varchar() 培训人姓名 Memo varchar() 备注Department 部门表Dep_id int 部门号 pkDname varchar() 部门称号 varchar() 部门Head v

3、archar() 部门指点Memo varchar() 部门职能Emp_train培训员工表Emt_id int培训员工编号 pkTra_id int培训编号 fkEmp_id int员工编号 fkEmpname varchar() 培训员工姓名 Employee 员工表Emp_id int员工编号 pkEmp_name varchar() 员工姓名Gender varchar() 性别Birth varchar() 出生日期Folk varchar() 民族cid bigint身份证号political varchar() 政治容颜marry varchar() 婚姻情况address va

4、rchar() 地址spec varchar() 专业school varchar() 毕业院校klevel varchar() 学历tel varchar() 联络email varchar() 电子邮件depname varchar() 部门pos varchar() 职务号workdate varchar() 入职时间cnum varchar() 银行卡号nativeplace varchar() 籍贯Poschange 职务调动表Pch_id int调动编号 pkEmp_id int员工编号 fkPch_date varchar() 调动时间Dep_pre int 调前部门编号 fk

5、Dep_sth int调后部门编号 fkPos_pre int调前职务编号 fkPos_sth int调后职务编号 fkReason varchar() 调动缘由Person int经办人号 fkStyle varchar() 调动类型Memo varchar() 备注Sarlary 工资表Sarl_id int工资编号 pkDep_id int 部门号 fkEmp_id int员工编号 fkBasewage money根本工资Poswage money 岗位工资Bonus money奖金工资Trafsub money交通补贴Endowins money养老保险Idleins money失业保

6、险pertax money个人所得税Spay money应发工资Rpay money 实法工资Sdate varchar() 工资月份Syear varchar() 工资年份Endulive教育阅历表Emp_id int员工编号 fkCollege varchar() 学院time varchar() 时间klevel varchar() 教育程度memo varchar() 备注worklive任务阅历表Emp_id int 员工编号Company varchar()公司time varchar() 时间Position varchar() 职位Memo varchar() 备注Positi

7、on 职位表Pos_id pk 职位号Pname 职位称号Dep_id fk 部门号图二中英文对照构造图阐明：在中英文对照表中，由于版面缘由，users用户表，module模块表，modulefunc模块功能分配表和func功能表未画入。所以请以中英文构造图为主。附录一：module模块表，modulefunc模块功能分配表，func功能表表图module_id int PKmodule_name varchar()module_pwd varchar()module模块表module_id int FKfunc_id intmodulefunc模块功能分配表func_id int ident

8、ity PKfunc_namefunc功能表Emp_id int not null ,username varchar() ,pwd varchar() ,module_id intusers用户表接员工表中的员工编号图三中英文构造图补差二，数据库，表的创建及相关触发器编写代码创建数据库create database PMSon(name = PMS_dat,filename = g:PMS_dat.mdf,size = ,maxsize= ,filegrowth=%)log on(name = PMS_log,filename = g:PMS_dat.ldf,size = ,maxsize=

9、,filegrowth=%)go创建员工表create table Employee(Emp_id int identity ,Emp_name varchar() ,Gender varchar() ,Birth varchar() ,Folk varchar() ,cid bigint ,political varchar() ,marry varchar() ,address varchar() ,spec varchar() ,school varchar() ,klevel varchar() ,tel varchar() ,email varchar(),depname varch

10、ar() ,pos varchar() ,workdate varchar(),cnum varchar(),nativeplace varchar() )go 创建培训员工表create table Emp_train(Emt_id int identity ,Emp_id int ,Tra_id int ,Empname varchar() )go-假设输入的id号在Employee表里不存在那么不能插入，到达外键约束目的,以次类推!创建外键约束触发器培训员工表-创建培训员工表create trigger trg_fket on Emp_trainfor insert,updateasif

11、 (select Emp_id from inserted) not in (select Emp_id from Employee)or (select Tra_id from inserted) not in (select Tra_id from Train ) begin rollback tran -回滚 raiserror(输入的员工号或培训号不存在,) -报错 endgo创建培训管理表create table Train(Tra_id int identity,Title varchar() ,Sta_date varchar() ,End_date varchar() ,Con

12、t varchar() ,person varchar() ,Memo varchar() )go创建部门表create table Department(Dep_id int identity ,Dname varchar() , varchar() ,Head varchar(),Memo varchar()goselect * from department创建职位表create table Position(Pos_id int identity ,Pname varchar() ,Dep_id int ,)go创建外键约束触发器职位表create trigger trg_fkps o

13、n Positionfor insert,updateasif (select Dep_id from inserted) not in (select Dep_id from Department) begin rollback tran raiserror(输入的部门号不存在,) endgo创建职务调动表create table Poschange(Pch_id int identity ,Emp_id int not null,Pch_date varchar() ,Dep_pre int not null,Dep_sth int not null,Pos_pre int not nul

14、l,Pos_sth int not null,Reason varchar() ,Person int ,Style varchar() not null,Memo varchar() )go创建外键约束触发器职务调动表create trigger trg_fkpc on Poschangefor insert,updateasif (select Emp_id from inserted) not in (select Emp_id from Employee)or (select Dep_pre from inserted) not in (select Dep_id from depar

15、tment) or (select Dep_sth from inserted) not in (select Dep_id from department) or (select Pos_pre from inserted) not in (select Pos_id from Position )or (select Pos_sth from inserted) not in (select Pos_id from Position ) begin rollback tran -回滚 raiserror(输入的员工号或培训号不存在,) -报错 endgo 创建工资表create table

16、 Sarlary(Emp_id int ,Dep_id int ,Basewage money ,Poswage money ,Bonus money ,Trafsub money ,Endowins as basewage*. ,Idleins money ,pertax money,Spay as basewage+bonus+trafsub ,Rpay as basewage+bonus+trafsub-basewage*.,Sdate varchar(), syear varchar()go创建外键约束触发器工资表create trigger trg_sarla on Sarlaryf

17、or insert,updateasif (select Emp_id from inserted) not in (select Emp_id from Employee) or(select Dep_id from inserted) not in (select Dep_id from Department) begin rollback tran raiserror(输入的人员号或部门号不存在,) endgo创建教育阅历表create table Endulive(Emp_id int ,College varchar() ,en_time varchar() ,klevel varc

18、har() ,memo varchar() )go创建外键约束触发器教育阅历表create trigger trg_endu on Endulivefor insert,updateasif (select Emp_id from inserted) not in (select Emp_id from Employee) begin rollback tran raiserror(输入的人员号不存在,) endgo创建任务阅历表create table worklive(Emp_id int ,Company varchar() ,work_Time varchar() ,Position

19、varchar(),Memo varchar() )go创建外键约束触发器任务阅历表create trigger trg_workl on worklivefor insert,updateasif (select Emp_id from inserted) not in (select Emp_id from Employee) begin rollback tran raiserror(输入的人员号不存在,) endgo创建用户表create table users(Emp_id int not null ,username varchar() ,pwd varchar() ,group_

20、id int )go-创建用户表创建外键约束触发器用户表create trigger trg_users on usersfor insert,updateasif (select Emp_id from inserted) not in (select Emp_id from Employee) begin rollback tran -回滚 raiserror(输入的员工号不存在,) -报错 endgo创建模块表create table module(module_id int identity ,module_name varchar(),module_pwd varchar() )go

21、-创建模块功能分配表create table modulefunc(module_id int not null ,func_id int)go创建功能表create table func(func_id int identity not null ,func_name varchar()go阐明：不同的模块中包含不同的功能，不同层次的员工属于不同的模块。比如，普通员工属于一种模块，该模块中只需阅读功能。指点类型的员工属于另一种模块，该模块中就有另一部分的功能。以此类推。测试数据输入代码列表培训管理表数据输入insert into Train (Title ,Sta_date,End_date

22、,Cont,Person,Memo) values (OFFIEC软件,-,-,各种OFFIEC软件的运用,黄老邪,优秀)insert into Train (Title,Sta_date,End_date,Cont,Person,Memo) values (OFFIEC软件,-,-,各种OFFIEC软件的运用,杨过,良)员工表数据输入insert into Employee (Emp_name,Gender,Birth,Folk,cid,political,marry,address,spec,school,klevel,tel,email,depname,pos,workdate,cnum

23、,nativeplace) values (小龙女,女,-,汉,共产党,否,古墓大街号,会计,古墓大学,本科,无,市场部,员工,-,中原)insert into Employee (Emp_name,Gender,Birth,Folk,cid,political,marry,address,spec,school,klevel,tel,email,depname,pos,workdate,cnum,nativeplace) values (杨过,男,-,汉,共产党,否,古墓大街号,通讯,古墓大学,本科,无,行政部,主管,-,中原)insert into Employee (Emp_name,G

24、ender,Birth,Folk,cid,political,marry,address,spec,school,klevel,tel,email,depname,pos,workdate,cnum,nativeplace) values (洪七公,男,-,汉,明主党,是,丐帮广场号,经济,丐帮学院,研讨生,无,开发部,经理,-,中原)insert into Employee (Emp_name,Gender,Birth,Folk,cid,political,marry,address,spec,school,klevel,tel,email,depname,pos,workdate,cnum

25、,nativeplace) values (黄老邪,男,-,汉,国民党,是,桃花岛号,计算机,桃花岛学府,研讨生,无,开发部,副经理,-,海外)insert into Employee (Emp_name,Gender,Birth,Folk,cid,political,marry,address,spec,school,klevel,tel,email,depname,pos,workdate,cnum,nativeplace) values (黄蓉,女,-,汉,共产党,否,桃花岛号,人事,桃花岛学府,本科,无,人事部,主管,-,海外)insert into Employee (Emp_nam

26、e,Gender,Birth,Folk,cid,political,marry,address,spec,school,klevel,tel,email,depname,pos,workdate,cnum,nativeplace) values (欧阳锋,男,-,回,国民党,是,西域白鸵山小区号,经济,西域白鸵山大学,研讨生,无,市场部,经理,-,西域)部门表数据输入insert into Department (Dname,Head,Memo) values (行政部,杨过,管理公司)insert into Department (Dname,Head,Memo) values (市场部,欧

27、阳锋,市场管理)insert into Department (Dname,Head,Memo) values (开发部,洪七公,软件开发)insert into Department (Dname,Head,Memo) values (人事部,黄蓉,人员调配)培训员工表数据输入insert into Emp_train (Tra_id,Emp_id,Empname)values (,小龙女)insert into Emp_train (Tra_id,Emp_id,Empname)values (,杨过)insert into Emp_train (Tra_id,Emp_id,Empname)

28、values (,洪七公)insert into Emp_train (Tra_id,Emp_id,Empname)values (,黄老邪)insert into Emp_train (Tra_id,Emp_id,Empname)values (,黄蓉)insert into Emp_train (Tra_id,Emp_id,Empname)values (,欧阳锋)职位表数据输入insert into position values(主管,)insert into position values(经理,)insert into position values(经理,)insert into

29、 position values(主管,)职务调动表数据输入insert into Poschange (Emp_id,Pch_date,Dep_pre,Dep_sth,Pos_pre,Pos_sth,Reason,Person,Style,Memo) values (,-,加强公司管理,正常,专业技术才干强)insert into Poschange (Emp_id,Pch_date,Dep_pre,Dep_sth,Pos_pre,Pos_sth,Reason,Person,Style,Memo) values (,-,加强公司管理,正常,专业技术才干强)工资表数据输入insert into

30、 Sarlary (Dep_id,Emp_id,Basewage,Poswage,Bonus,Trafsub,Idleins,pertax,Sdate,Syear) values (,)insert into Sarlary (Dep_id,Emp_id,Basewage,Poswage,Bonus,Trafsub,Idleins,pertax,Sdate,Syear) values (,)insert into Sarlary (Dep_id,Emp_id,Basewage,Poswage,Bonus,Trafsub,Idleins,pertax,Sdate,Syear) values (,

31、)insert into Sarlary (Dep_id,Emp_id,Basewage,Poswage,Bonus,Trafsub,Idleins,pertax,Sdate,Syear) values (,)insert into Sarlary (Dep_id,Emp_id,Basewage,Poswage,Bonus,Trafsub,Idleins,pertax,Sdate,Syear) values (,)insert into Sarlary (Dep_id,Emp_id,Basewage,Poswage,Bonus,Trafsub,Idleins,pertax,Sdate,Syea

32、r) values (,)教育阅历表数据输入insert into Endulive (College,en_time,klevel,memo) values (古墓大学,-到-,本科,往届)insert into Endulive (College,en_time,klevel,memo) values (古墓大学,-到-,本科,应届)insert into Endulive (College,en_time,klevel,memo) values (丐帮学院,-到-,研讨生,专业才干强)insert into Endulive (College,en_time,klevel,memo) v

33、alues (桃花岛学府,-到-,研讨生,专业才干强)insert into Endulive (College,en_time,klevel,memo) values (桃花岛学府,-到-,本科,应届)insert into Endulive (College,en_time,klevel,memo) values (西域白鸵山大学,-到-,研讨生,专业才干强)任务阅历表数据输入insert into worklive (Company,work_Time,Position,Memo) values (本公司,一个月,员工,无)insert into worklive (Company,wo

34、rk_Time,Position,Memo) values (本公司,三个月,主管,良)insert into worklive (Company,work_Time,Position,Memo) values (本公司,十一年,经理,中心经理)insert into worklive (Company,work_Time,Position,Memo) values (本公司,六年,经理,中心经理)insert into worklive (Company,work_Time,Position,Memo) values (本公司,七个月,主管,中心主管)insert into worklive

35、 (Company,work_Time,Position,Memo) values (本公司,十一年七个月,经理,中心经理)存储过程代码列表样例工资表视图与过程-输入员工姓名和月、年份得到该员工的该年月工资情况。-创建工资表视图create view samsarlaryas select 员工编号=a.emp_id ,姓名=b.emp_name , 部门=c.dname ,根本工资=a.basewage,岗位工资=a.poswage,资金工资=a.Bonus,交通补贴=a.Trafsub ,养老保险=a.Endowins,失业保险=a.Idleins,个人所得锐=a.pertax,应发工资=

36、a.Spay,实发工资=a.Rpay,工资月份=a.Sdate, 年份=a.Syearfrom Sarlary a , Employee b , Department cwhere a.emp_id=b.emp_id and a.dep_id=c.dep_id select * from samsarlary-创建过程create procedure samsarlaryx varchar()= % ,y varchar()=% , z varchar()=%as beginselect 员工编号,姓名,部门,根本工资,岗位工资,资金工资,交通补贴,养老保险,失业保险,个人所得锐,应发工资,实

37、发工资,工资月份,年份from samsarlarywhere 姓名 like x and 工资月份 like y and 年份 like zend-创建工资表过程create procedure samsarlaryx varchar()= %as beginselect 员工编号,姓名,部门,根本工资,岗位工资,资金工资,交通补贴,养老保险,失业保险,个人所得锐,应发工资,实发工资,工资月份from samsarlarywhere 姓名 like xend-测试执行样例工资表视图与过程exec samsarlary x=xx,z=-插入语句insert into Employee (emp

38、_name)values (xx)select * from Employeeinsert into Department(dname)values (yy)select * from Departmentinsert into sarlary (emp_id,dep_id,basewage,poswage,bonus,trafsub,idleins,pertax,sdate,syear)values (,)select * from sarlary()各部门工资年报表视图与过程-输入部门名和年份得到该部门全年工资表select * from Sarlaryselect * from Depa

39、rtment-创建过程create procedure yearbiao x varchar(),y varchar()as select distinct 部门= (select dname from Department where dname = x),一月=(select sum(Rpay) from Sarlary a , Department b where a.dep_id=b.dep_id and b.dname = x and a.sdate= and a.syear=y ),二月=(select sum(Rpay) from Sarlary a , Department b

40、 where a.dep_id=b.dep_id and b.dname = x and a.sdate= and a.syear=y ),三月=(select sum(Rpay) from Sarlary a , Department b where a.dep_id=b.dep_id and b.dname = x and a.sdate= and a.syear=y ),四月=(select sum(Rpay) from Sarlary a , Department b where a.dep_id=b.dep_id and b.dname = x and a.sdate= and a.syear=y ),五月=(select sum(Rpay) from Sarlary a , Department b where a.dep_id=b.dep_id and b.dname = x and a.sdate= and a.syear=y ),六月=(select sum(Rpay) from Sarlary a , Department b where a.dep_id=b.