员工管理系统数据库.docx
《员工管理系统数据库.docx》由会员分享，可在线阅读，更多相关《员工管理系统数据库.docx（21页珍藏版）》请在冰豆网上搜索。
员工管理系统数据库
员工管理系统数据库
员工管理系统数据库（sqlserver）
2019-12-2716:
49
员工管理系统数据库（sqlserver）
数据库设置：
工资管理系统
帐户设置：
三个用户管理员，部门经理（查询本部门员工的信息）,员工（权限只能查询本人）
创建数据库
设置权限
--------------------------------------------------------------------------------
--------------------------------------------------------------------
表
1，员工表（员工编号（PK）（FK），姓名，出生日期，身份证号，加入公司日期，登录名，密码，
电话,电子邮件，部门编号（FK）,员工级别，拥有假期时间,备注）
2,部门表（部门名称,部门编号（PK）（自动增量）,部门经理编号（员工编号）,部门描述）
3，工资表（工资编号（PK），员工编号，基本工资，应发工资,缺勤早退扣除，津贴，，奖金，
住房公积金，养老费，是否为单位住房,水电费，工作日期，当前日期,薪资发放当前日期）
4，考勤表（考勤编号（PK），员工编号，到达时间，记录者编号（员工编号），考勤类型，日期）
5，请假表（请假编号（PK）,员工编号，请假原因，提交时间，请假开始时间，请假结束时间，
审核者编号，拒绝申请的理由，申请状态）
6，假期表（假期编号（PK），假期具体日期，假期名称，是否为法定假期）
各表PK为自动增量,聚集索引
表之间的关系
员工表（部门编号）--部门表（部门编号）
员工表（员工编号）--工资表（员工编号）
员工表（员工编号）--考勤表（员工编号）
员工表（员工编号）--请假表（员工编号）
表之中的约束
1,员工表（check密码为6位以上,check登录名长度大于1,unique登录名唯一）
2,工资表（基本工资>600,是否为工资住房="是"or"否"，应发工资=基本工资+津贴+奖金-缺勤
早退扣除-住房公积金-养老费-水电费）
3,考勤表（记录者编号员工编号and考勤类型=“缺勤”or“迟到”or“早退”）
4,请假表（员工编号审核者编号and申请状态=“已提交”、“已取消”、“已批准”、“已否
决”and开始时间
--------------------------------------------------------------------------------
----------------------------------------------------------------------
视图
1，员工基本信息基表:
员工表-（左外联接）-部门表得到了员工的详细信息，其中包括员
工的基本信息、员工的部门信息
2,考勤信息基表:
考勤表--（内联接）--员工表得到员工的姓名、员工所属部门编号,
考勤情况
3,请假信息基表:
请假表--（内联接）--员工表得到员工的请假申请信息,申请请假
状态和请假批准人姓名
4，工资信息基表:
员工表-（左外联接）-部门表-（内联接）-工资表得到员工的详细工资
信息
5,基本工资基表:
员工表--（内联接）--工资表得到员工的基本工资
6,变动工资基表:
员工表--（内联接）--工资表得到员工基本工资的变动增加信息7，假期信息基表:
假期表得到假期信息
-------------------------------------------------------------------------------------------------------------------------------------------------------存储过程
1，增加员工基表:
员工表插入员工表一条记录
2,删除员工基表:
员工表删除员工表一条记录根据员工编号
3，更新员工信息基表:
员工表更改员工的基本信息据员工登录名,密码
4，得到用户密码基表:
员工表input用户名,员工编号output用户密码
5，更改密码基表:
员工表据员工编号,登录名,原始密码用户表的密码6,删除部门全部员工基表:
员工表
7,增加部门基表:
部门表
8，请假申请基表:
请假表员工插入请假表一条记录
9,更新员工请假状态基表:
请假表经理或管理员更改请假表中的申请请假状态,或和拒绝申请的理由
10,删除请假记录基表:
请假表删除请假表一条记录据员工编号和提交时间11,查看员工基本工资基表:
工资表--员工表据登陆名和密码查出姓名,基本工资12,更改员工基本工资基表:
工资表--员工表更改员工的基本工资
13,查询员工历史工资信息基表:
员工表--工资表input员工编号output得到员工工资具体信息
14,更改员工津贴基表:
员工表--工资表input员工编号output更改员工的津贴15,查看部门总发放工资基表:
工资表,员工表,部门表据部门编号
--------------------------------------------------------------------------------------------------------------------------
用户自定义函数
1,返回员工工资编号通过员工编号
2,返回员工请假编号通过员工编号
3,返回员工所有请假记录通过请假编号
4,返回部门成员信息通过部门名称
---------------------------------------------------------------------------------------------------------------------------
触发器
1，每次只能删一个员工定义在在员工表上否则给出出错信息
2,更改员工拥有假期定义在员工表上当员工请假申请状态是"否决"时，之前其拥有假期时间已经被减少，否决完成后应该相应恢复其拥有假期时间
3,员工申请请假状态为批准时定义在工资表自动将员工表的缺勤扣除项设为应扣的一半4,更新应发工资定义在工资表为基本工资-缺勤早退扣除+奖金+津贴-住房公积金-养老费-水电费
----------------------------------------------------------------------------------------------------------------------------
--以下是代码其中约束和之间的关系没建立
createdatabaseSalaryManagement
on
（name='SM_data',
filename='E:
\SM.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEgrowth=10%）
LogOn
（NAME='SM_log',
filename='F:
\SM.ndf',
SIZE=5MB,
MAXSIZE=10MB,
FILEgrowth=2MB）
GO
------------------------------------------------创建基
----------------------------------------------------------------
useSalaryManagement
createtabledbo.员工表
（员工编号intidentity（1,1）notnullprimarykey,
姓名nvarchar（50）NOTNULL,
出生日期datetimeNOTNULL,
身份证号nvarchar（18）notnull,
加入公司日期datetimeNOTNULL,
登录名nvarchar（20）NOTNULL,
密码binary（20）notNULL,
电话nvarchar（50）notnull,
电子邮件nvarchar（50）NOTNULL,
部门编号intnotNULL,
员工级别intnotNULL,
拥有假期时间intnotNULL,
备注textnull）
go
createtabledbo.部门表
（部门编号intIDENTITY（1,1）NOTNULLprimarykey,
部门名称nchar（10）notNULL,
部门经理编号intnotnull,
部门描述textnull）
go
createtabledbo.工资表
（工资编号intIDENTITY（1,1）NOTNULLprimarykey,
员工编号intnotnull,
基本工资intnotnull,
应发工资intnotnull,
缺勤早退扣除intnotnull,
津贴intnotnull,本表
住房公积金intnotnull,
养老费intnotnull,
是否为单位住房bitnotnull,--0表示否，1表示是
水电费intnotnull,--若是否为单位住房为0，则水电费为0
工作日期datetimeNOTNULL,
当前记录日期datetimeNOTNULL,
薪资发放日期datetimeNOTNULl）
go
createtabledbo.考勤表
（考勤编号intidentity（1,1）notnullprimarykey,
员工编号intnotnull,
到达时间datetimenotnull,
记录者编号intnotnull,
考勤类型nvarchar（8）notnull,--应为“缺勤”or“迟到”or“早退”
日期datetimenotnull）
go
createtabledbo.请假表
（请假编号intidentity（1,1）notnullprimarykey,
员工编号intnotnull,
请假原因textnotnull,
提交时间datetimenotnull,
请假开始时间datetimenotnull,
请假结束时间datetimenotnull,
审核者编号intnull,
拒绝申请的理由ntextnull,
申请状态nvarchar（6）null）--只能为“已提交”、“已取消”、“已批准”、“已否决”）go
createtabledbo.假期表
（假期编号intidentity（1,1）notnullprimarykey,
假期具体日期datetimenotnull,
假期名称nvarchar（20）notnull,
是否为法定假期bitnotnull）
go
---------------------------------------------------------------创建视---------------------------------------------------
useSalaryManagement
go
createviewdbo.员工基本信息
AS
SELECTy.员工编号,y.姓名,y.电子邮件,y.电话,y.登录名,y.出生日期,
y.部门编号,b.部门名称,y.员工级别,y.拥有假期时间
FROMdbo.员工表asyleftouterjoindbo.部门表b
ony.部门编号=b.部门编号
go图
createviewdbo.考勤信息
as
selectk.考勤编号,y.姓名,k.日期,k.考勤类型
from考勤表askinnerjoin员工表asy
onk.员工编号=k.员工编号
go
createviewdbo.请假信息
as
selectq.请假编号,y.姓名,q.提交时间,q.请假原因,q.申请状态,q.拒绝申请的理由from请假表asqinnerjoin员工表asy
onq.员工编号=y.员工编号
go
createviewdbo.工资信息
as
selecty.员工编号,b.部门名称,y.姓名,g.基本工资,g.工资编号,g.应发工资,g.缺勤早退扣除,
g.津贴,g.住房公积金,g.养老费,g.水电费,g.薪资发放日期
from工资表asginnerjoin员工表asy
ong.员工编号=y.员工编号leftouterjoin部门表asb
ony.部门编号=b.部门编号
go
createviewdbo.基本工资
as
selecty.姓名,y.员工编号,g.基本工资
from员工表asyinnerjoin工资表asg
ony.员工编号=g.员工编号
go
createviewdbo.变动工资
as
selecty.姓名,y.员工编号,g.工资编号,g.缺勤早退扣除,g.津贴,g.住房公积金,g.养老费,g.水电费,g.薪资发放日期
from员工表asyinnerjoin工资表asg
ony.员工编号=g.员工编号
go
createviewdbo.假期信息
as
select假期编号,假期具体日期as假期日期,假期名称,是否为法定假期
from假期表
go
-------------------------------------------------------创建存储过程---------------------------------------------------------
useSalaryManagement
--利用系统函数@@error来确定每个insert语句是否发生错误,如果发生,事务将会滚.
createproceduredbo.增加员工
（
@姓名nvarchar（50）,
@出生日期datetime,
@身份证号nvarchar（18）,
@加入公司日期datetime,
@登录名nvarchar（20）,
@密码binary（20）,
@电话nvarchar（50）,
@电子邮件nvarchar（50）,
@部门编号int,
@员工级别int,
@拥有假期时间int,
@备注text=null）
as
begintransaction
insert员工表（
姓名,
出生日期,
身份证号,
加入公司日期,
登录名,
密码,
电话,
电子邮件,
部门编号,
员工级别,
拥有假期时间,
备注）
values（
@姓名,
@出生日期,
@身份证号,
@加入公司日期,
@登录名,
@密码,
@电话,
@电子邮件,
@部门编号,
@员工级别,
@拥有假期时间,
@备注）
if@@error0
begin
rollbacktran
return
end
go
createproceduredbo.删除员工
@员工编号int
as
deletefrom员工表
where员工编号=@员工编号
go
CREATEPROCEDUREdbo.更改员工信息
（@员工编号int,
@姓名nvarchar（50）=null,
@出生日期datetime=null,
@身份证号nvarchar（18）=null,
@加入公司日期datetime=null,
@电话nvarchar（50）=null,
@电子邮件nvarchar（50）=null,
@部门编号int=null,
@员工级别int=null,
@拥有假期时间int=null,
@备注text=null）
As
UPDATE员工表
Set员工编号=@员工编号,
姓名=@姓名,
出生日期=@出生日期,
身份证号=@身份证号,
电话=@电话,
电子邮件=@电子邮件,
员工级别=@员工级别,
拥有假期时间=@拥有假期时间,
备注=@备注
WHERE员工编号=@员工编号
return@@Rowcount
go
CREATEPROCEDUREdbo.得到员工密码
（
@员工编号int,
@密码binary（20）output
）
As
SELECT@密码=密码
FROM员工表
WHERE员工编号=@员工编号
return@@Rowcount
go
CreateProceduredbo.更改密码
（@登录名asnvarchar（20）,
@老密码asbinary（20）,
@新密码asbinary（20））
AS
Declare@员工编号asint
select@员工编号=员工编号
from员工表
where登录名=@登录名and密码=@老密码
Update员工表
set密码=@新密码
where员工编号=@员工编号
go
createproceduredbo.删除部门全部员工
@部门编号int
as
deletefrom员工表
where部门编号=@部门编号
go
CreateProceduredbo.增加部门
（@部门名称nchar（10）,
@部门描述nvarchar（50）,
@部门经理编号int）
AS
insert部门表（部门名称,部门描述,部门经理编号）
values（@部门名称,@部门描述,@部门经理编号）
go
createproceduredbo.请假申请
（
@员工编号int,
@请假原因text,
@提交时间datetime,
@请假开始时间datetime,
@请假结束时间datetime）
as
insert请假表（员工编号,请假原因,提交时间,请假开始时间,请假结束时间）
values（@员工编号,@请假原因,@提交时间,@请假开始时间,@请假结束时间）go
createproceduredbo.更新员工请假状态
（@员工编号int,
@审核者编号int,
@拒绝申请的理由ntext=null,
@申请状态nvarchar（6））
as
update请假表
set审核者编号=@审核者编号,
拒绝申请的理由=@拒绝申请的理由,
申请状态=@申请状态
where员工编号=@员工编号
go
createproceduredbo.删除请假记录
（@请假编号int,
@员工编号int,
@提交时间datetime）
as
deletefrom请假表
where员工编号=@员工编号and@提交时间=提交时间
go
createproceduredbo.查看基本工资
（@登录名nvarchar,
@密码binary）
as
--得到员工编号
declare@员工编号asint
select@员工编号=员工编号
from员工表
where登录名=@登录名and密码=@密码
--查询员工基本工资
selecty.姓名,g.基本工资
from员工表asyinnerjoin工资表asg
ony.员工编号=g.员工编号
whereg.员工编号=@员工编号
go
createproceduredbo.更改员工基本工资
（@员工编号int,
@老基本工资int,
@新基本工资int）
as
update工资表
set基本工资=@新基本工资
where员工编号=@员工编号
go
createproceduredbo.查询员工历史工资信息
（@员工编号int,
@开始时间datetime,
@结束时间datetime）
AS
selecty.姓名,g.工资编号,g.薪资发放日期as时间,g.基本工资,g.应发工资,g.缺勤早退扣除,g.津贴,g.住房公积金,g.养老费
from工资表asg
join员工表asy
ong.员工编号=y.员工编号
wherey.员工编号=@员工编号
havingg.薪资发放日期>=@开始时间andg.薪资发放日期
go
createproceduredbo.更改员工津贴
（@员工编号int,
@新津贴int）
as
update工资表
set津贴=@新津贴
where员工编号=@员工编号
go
createproceduredbo.查看部门总发放工资
@部门编号int
as
selectb.部门编号,b.部门名称,sum（g.应发工资）as总发放工资
from部门表asbinnerjoin员工表asy
onb.部门编号=y.部门编号
innerjoin工资表asg
ony.员工编号=g.员工编号
go
-----------------------------------------------------用户自定义----------------------------------------------------------
--1,返回员工工资编号通过员工编号
CREATEFUNCTIONdbo.返回员工工资编号（@员工编号int）
RETURNSint
AS函数
begin
Declare@工资编号int
SELECT@工资编号=工资编号
From工
升级会员 