員工管理系統(tǒng)數(shù)據(jù)庫(kù)(sql server)
--我一個(gè)晚上的成果,不是很完善
數(shù)據(jù)庫(kù)設(shè)置：工資管理系統(tǒng)
帳戶設(shè)置：三個(gè)用戶 管理員,，部門經(jīng)理(查詢本部門員工的信息),員工（權(quán)限只能查詢本人）
創(chuàng)建數(shù)據(jù)庫(kù)
設(shè)置權(quán)限
----------------------------------------------------------------------------------------------------------------------------------------------------
表
1,，員工表（員工編號(hào)(PK)(FK),，姓名,，出生日期,，身份證號(hào),，加入公司日期，登錄名,，密碼,，電話,電子郵件,，部門編號(hào)(FK),員工級(jí)別，擁有假期時(shí)間,備注）
2, 部門表 (部門名稱,部門編號(hào)(PK)(自動(dòng)增量),部門經(jīng)理編號(hào)(員工編號(hào)),部門描述)
3,，工資表（工資編號(hào)(PK),，員工編號(hào)，基本工資,，應(yīng)發(fā)工資,缺勤早退扣除,，津貼，,，獎(jiǎng)金,，住房公積金，養(yǎng)老費(fèi),，是否為單位住房,水電費(fèi),，工作日期，當(dāng)前日期,薪資發(fā)放當(dāng)前日期）
4,，考勤表（考勤編號(hào)(PK),，員工編號(hào)，到達(dá)時(shí)間,，記錄者編號(hào)(員工編號(hào)),，考勤類型，日期）
5,，請(qǐng)假表（請(qǐng)假編號(hào)(PK), 員工編號(hào),，請(qǐng)假原因，提交時(shí)間,，請(qǐng)假開始時(shí)間,，請(qǐng)假結(jié)束時(shí)間，審核者編號(hào),，拒絕申請(qǐng)的理由,，申請(qǐng)狀態(tài)）
6，假期表（假期編號(hào)(PK),，假期具體日期,，假期名稱，是否為法定假期）
各表PK為自動(dòng)增量,聚集索引
表之間的關(guān)系
員工表(部門編號(hào))--部門表(部門編號(hào))
員工表(員工編號(hào))--工資表(員工編號(hào))
員工表(員工編號(hào))--考勤表(員工編號(hào))
員工表(員工編號(hào))--請(qǐng)假表(員工編號(hào))
表之中的約束
1,員工表(check密碼為6位以上,check登錄名長(zhǎng)度大于1,unique登錄名唯一)
2,工資表(基本工資>600,是否為工資住房="是"or"否",，應(yīng)發(fā)工資=基本工資+津貼+獎(jiǎng)金-缺勤早退扣除-住房公積金-養(yǎng)老費(fèi)-水電費(fèi))
3,考勤表(記錄者編號(hào)<>員工編號(hào) and 考勤類型 =“缺勤”or“遲到”or“早退”)
4,請(qǐng)假表(員工編號(hào)<>審核者編號(hào) and 申請(qǐng)狀態(tài) =“已提交”,、“已取消”、“已批準(zhǔn)”,、“已否決”and 開始時(shí)間<結(jié)束時(shí)間)
------------------------------------------------------------------------------------------------------------------------------------------------------
視圖
1,，員工基本信息 基表:員工表-(左外聯(lián)接)-部門表 得到了員工的詳細(xì)信息,，其中包括員工的基本信息,、員工的部門信息
2, 考勤信息 基表:考勤表--(內(nèi)聯(lián)接)--員工表 得到員工的姓名、員工所屬部門編號(hào),考勤情況
3, 請(qǐng)假信息 基表:請(qǐng)假表--(內(nèi)聯(lián)接)--員工表 得到員工的請(qǐng)假申請(qǐng)信息,申請(qǐng)請(qǐng)假狀態(tài)和請(qǐng)假批準(zhǔn)人姓名
4，工資信息 基表:員工表-(左外聯(lián)接)-部門表-(內(nèi)聯(lián)接)-工資表 得到員工的詳細(xì)工資信息
5, 基本工資 基表:員工表--(內(nèi)聯(lián)接)--工資表 得到員工的基本工資
6, 變動(dòng)工資 基表:員工表--(內(nèi)聯(lián)接)--工資表 得到員工基本工資的變動(dòng)增加信息
7,，假期信息 基表:假期表 得到假期信息
-------------------------------------------------------------------------------------------------------------------------------------------------------
存儲(chǔ)過(guò)程
1,，增加員工 基表:員工表 插入員工表一條記錄
2, 刪除員工 基表:員工表 刪除員工表一條記錄 根據(jù)員工編號(hào)
3，更新員工信息 基表:員工表 更改員工的基本信息 據(jù)員工登錄名,密碼
4,，得到用戶密碼 基表:員工表 input用戶名,員工編號(hào)output用戶密碼
5,，更改密碼 基表:員工表 據(jù) 員工編號(hào),登錄名,原始密碼 用戶表的密碼
6, 刪除部門全部員工 基表:員工表
7, 增加部門 基表:部門表
8，請(qǐng)假申請(qǐng) 基表:請(qǐng)假表 員工插入請(qǐng)假表一條記錄
9, 更新員工請(qǐng)假狀態(tài) 基表:請(qǐng)假表 經(jīng)理或管理員更改請(qǐng)假表中的申請(qǐng)請(qǐng)假狀態(tài),或和拒絕申請(qǐng)的理由
10,刪除請(qǐng)假記錄 基表:請(qǐng)假表 刪除請(qǐng)假表一條記錄 據(jù)員工編號(hào)和提交時(shí)間
11,查看員工基本工資 基表:工資表--員工表 據(jù)登陸名和密碼 查出姓名,基本工資
12,更改員工基本工資 基表:工資表--員工表 更改員工的基本工資
13,查詢員工歷史工資信息 基表:員工表--工資表 input員工編號(hào) output得到員工工資具體信息
14,更改員工津貼 基表:員工表--工資表 input員工編號(hào) output更改員工的津貼
15,查看部門總發(fā)放工資 基表:工資表,員工表,部門表 據(jù)部門編號(hào)
--------------------------------------------------------------------------------------------------------------------------
用戶自定義函數(shù)
1,返回員工工資編號(hào) 通過(guò)員工編號(hào)
2,返回員工請(qǐng)假編號(hào) 通過(guò)員工編號(hào)
3,返回員工所有請(qǐng)假記錄 通過(guò)請(qǐng)假編號(hào)
4,返回部門成員信息 通過(guò)部門名稱
---------------------------------------------------------------------------------------------------------------------------
觸發(fā)器
1,，每次只能刪一個(gè)員工 定義在在員工表上 否則給出出錯(cuò)信息
2, 更改員工擁有假期 定義在員工表上 當(dāng)員工請(qǐng)假申請(qǐng)狀態(tài)是"否決"時(shí),，之前其擁有假期時(shí)間已經(jīng)被減少，否決完成后應(yīng)該相應(yīng)恢復(fù)其擁有假期時(shí)間
3, 員工申請(qǐng)請(qǐng)假狀態(tài)為批準(zhǔn)時(shí) 定義在工資表自動(dòng)將員工表的缺勤扣除項(xiàng)設(shè)為應(yīng)扣的一半
4, 更新應(yīng)發(fā)工資 定義在工資表 為基本工資-缺勤早退扣除+獎(jiǎng)金+津貼-住房公積金-養(yǎng)老費(fèi)-水電費(fèi)
----------------------------------------------------------------------------------------------------------------------------
--以下是代碼 其中約束和之間的關(guān)系沒(méi)建立
create database SalaryManagement
on
(name='SM_data',
filename='E:\SM.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEgrowth=10%)
Log On
(NAME='SM_log',
filename='F:\SM.ndf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEgrowth = 2MB)
GO
------------------------------------------------創(chuàng)建基本表----------------------------------------------------------------
use SalaryManagement
create table dbo.員工表
( 員工編號(hào) int identity(1,1) not null primary key,
姓名 nvarchar(50) NOT NULL,
出生日期 datetime NOT NULL ,
身份證號(hào) nvarchar(18) not null,
加入公司日期 datetime NOT NULL ,
登錄名 nvarchar(20) NOT NULL ,
密碼 binary(20) not NULL,
電話 nvarchar (50) not null,
電子郵件 nvarchar (50) NOT NULL ,
部門編號(hào) int not NULL,
員工級(jí)別 int not NULL,
擁有假期時(shí)間 int not NULL,
備注 text null)
go
create table dbo.部門表
(部門編號(hào) int IDENTITY (1, 1) NOT NULL primary key,
部門名稱 nchar (10) not NULL,
部門經(jīng)理編號(hào) int not null,
部門描述 text null)
go
create table dbo.工資表
(工資編號(hào) int IDENTITY (1, 1) NOT NULL primary key,
員工編號(hào) int not null,
基本工資 int not null,
應(yīng)發(fā)工資 int not null,
缺勤早退扣除 int not null,
津貼 int not null,
住房公積金 int not null,
養(yǎng)老費(fèi) int not null,
是否為單位住房 bit not null,--0表示否,，1表示是
水電費(fèi) int not null,--若是否為單位住房為0,，則水電費(fèi)為0
工作日期 datetime NOT NULL,
當(dāng)前記錄日期 datetime NOT NULL,
薪資發(fā)放日期 datetime NOT NULl)
go
create table dbo.考勤表
(考勤編號(hào) int identity(1,1) not null primary key,
員工編號(hào) int not null,
到達(dá)時(shí)間 datetime not null,
記錄者編號(hào) int not null,
考勤類型 nvarchar(8) not null,--應(yīng)為“缺勤”or“遲到”or“早退”
日期 datetime not null)
go
create table dbo.請(qǐng)假表
(請(qǐng)假編號(hào) int identity(1,1) not null primary key,
員工編號(hào) int not null,
請(qǐng)假原因 text not null,
提交時(shí)間 datetime not null,
請(qǐng)假開始時(shí)間 datetime not null,
請(qǐng)假結(jié)束時(shí)間 datetime not null,
審核者編號(hào) int null,
拒絕申請(qǐng)的理由 ntext null,
申請(qǐng)狀態(tài) nvarchar(6) null )--只能為“已提交”、“已取消”,、“已批準(zhǔn)”,、“已否決”)
go
create table dbo.假期表
(假期編號(hào) int identity(1,1) not null primary key,
假期具體日期 datetime not null,
假期名稱 nvarchar(20) not null,
是否為法定假期 bit not null)
go
---------------------------------------------------------------創(chuàng)建視圖---------------------------------------------------
use SalaryManagement
go
create view dbo.員工基本信息
AS
SELECT y.員工編號(hào),y.姓名, y.電子郵件, y.電話, y.登錄名, y.出生日期,
y.部門編號(hào),b.部門名稱,y.員工級(jí)別,y.擁有假期時(shí)間
FROM dbo.員工表 as y left outer join dbo.部門表 b
on y.部門編號(hào)=b.部門編號(hào)
go
create view dbo.考勤信息
as
select k.考勤編號(hào),y.姓名,k.日期,k.考勤類型
from 考勤表 as k inner join 員工表 as y
on k.員工編號(hào)=k.員工編號(hào)
go
create view dbo.請(qǐng)假信息
as
select q.請(qǐng)假編號(hào),y.姓名,q.提交時(shí)間,q.請(qǐng)假原因,q.申請(qǐng)狀態(tài),q.拒絕申請(qǐng)的理由
from 請(qǐng)假表 as q inner join 員工表 as y
on q.員工編號(hào)=y.員工編號(hào)
go
create view dbo.工資信息
as
select y.員工編號(hào),b.部門名稱,y.姓名,g.基本工資,g.工資編號(hào),g.應(yīng)發(fā)工資,g.缺勤早退扣除,
g.津貼,g.住房公積金,g.養(yǎng)老費(fèi),g.水電費(fèi),g.薪資發(fā)放日期
from 工資表 as g inner join 員工表 as y
on g.員工編號(hào) = y.員工編號(hào) left outer join 部門表 as b
on y.部門編號(hào) = b.部門編號(hào)
go
create view dbo.基本工資
as
select y.姓名,y.員工編號(hào),g.基本工資
from 員工表 as y inner join 工資表 as g
on y.員工編號(hào)=g.員工編號(hào)
go
create view dbo.變動(dòng)工資
as
select y.姓名,y.員工編號(hào),g.工資編號(hào),g.缺勤早退扣除,g.津貼,g.住房公積金,g.養(yǎng)老費(fèi),g.水電費(fèi),g.薪資發(fā)放日期
from 員工表 as y inner join 工資表 as g
on y.員工編號(hào) =g. 員工編號(hào)
go
create view dbo.假期信息
as
select 假期編號(hào),假期具體日期 as 假期日期,假期名稱,是否為法定假期
from 假期表
go
-------------------------------------------------------創(chuàng)建存儲(chǔ)過(guò)程---------------------------------------------------------
use SalaryManagement
--利用系統(tǒng)函數(shù)@@error來(lái)確定每個(gè)insert語(yǔ)句是否發(fā)生錯(cuò)誤,如果發(fā)生,事務(wù)將會(huì)滾.
go
create procedure dbo.增加員工
(
@姓名 nvarchar(50) ,
@出生日期 datetime ,
@身份證號(hào) nvarchar(18) ,
@加入公司日期 datetime ,
@登錄名 nvarchar(20) ,
@密碼 binary(20) ,
@電話 nvarchar (50) ,
@電子郵件 nvarchar (50) ,
@部門編號(hào) int ,
@員工級(jí)別 int ,
@擁有假期時(shí)間 int,
@備注 text = null)
as
begin transaction
insert 員工表(
姓名,
出生日期,
身份證號(hào),
加入公司日期,
登錄名,
密碼,
電話,
電子郵件,
部門編號(hào),
員工級(jí)別,
擁有假期時(shí)間,
備注)
values (
@姓名,
@出生日期,
@身份證號(hào),
@加入公司日期,
@登錄名,
@密碼,
@電話,
@電子郵件,
@部門編號(hào),
@員工級(jí)別,
@擁有假期時(shí)間,
@備注)
if @@error<>0
begin
rollback tran
return
end
go
create procedure dbo.刪除員工
@員工編號(hào) int
as
delete from 員工表
where 員工編號(hào)=@員工編號(hào)
go
CREATE PROCEDURE dbo.更改員工信息
( @員工編號(hào) int,
@姓名 nvarchar(50) = null,
@出生日期 datetime = null,
@身份證號(hào) nvarchar(18) = null,
@加入公司日期 datetime = null,
@電話 nvarchar (50) =null,
@電子郵件 nvarchar (50)=null ,
@部門編號(hào) int =null,
@員工級(jí)別 int=null ,
@擁有假期時(shí)間 int =null,
@備注 text = null)
As
UPDATE 員工表
Set 員工編號(hào)=@員工編號(hào),
姓名=@姓名,
出生日期=@出生日期,
身份證號(hào)=@身份證號(hào),
電話=@電話,
電子郵件=@電子郵件,
員工級(jí)別=@員工級(jí)別,
擁有假期時(shí)間=@擁有假期時(shí)間,
備注=@備注
WHERE 員工編號(hào) = @員工編號(hào)
return @@Rowcount
go
CREATE PROCEDURE dbo.得到員工密碼
(
@員工編號(hào) int,
@密碼 binary(20) output
)
As
SELECT @密碼 = 密碼
FROM 員工表
WHERE 員工編號(hào)=@員工編號(hào)
return @@Rowcount
go
Create Procedure dbo.更改密碼
(@登錄名 as nvarchar(20),
@老密碼 as binary(20),
@新密碼 as binary(20))
AS
Declare @員工編號(hào) as int
select @員工編號(hào)=員工編號(hào)
from 員工表
where 登錄名=@登錄名 and 密碼=@老密碼
Update 員工表
set 密碼=@新密碼
where 員工編號(hào)=@員工編號(hào)
go
create procedure dbo.刪除部門全部員工
@部門編號(hào) int
as
delete from 員工表
where 部門編號(hào)= @部門編號(hào)
go
Create Procedure dbo.增加部門
(@部門名稱 nchar(10),
@部門描述 nvarchar(50),
@部門經(jīng)理編號(hào) int)
AS
insert 部門表(部門名稱,部門描述,部門經(jīng)理編號(hào))
values (@部門名稱,@部門描述,@部門經(jīng)理編號(hào))
go
create procedure dbo.請(qǐng)假申請(qǐng)
(
@員工編號(hào) int ,
@請(qǐng)假原因 text ,
@提交時(shí)間 datetime,
@請(qǐng)假開始時(shí)間 datetime,
@請(qǐng)假結(jié)束時(shí)間 datetime)
as
insert 請(qǐng)假表(員工編號(hào),請(qǐng)假原因 ,提交時(shí)間,請(qǐng)假開始時(shí)間,請(qǐng)假結(jié)束時(shí)間)
values (@員工編號(hào), @請(qǐng)假原因,@提交時(shí)間, @請(qǐng)假開始時(shí)間,@請(qǐng)假結(jié)束時(shí)間)
go
create procedure dbo.更新員工請(qǐng)假狀態(tài)
(@員工編號(hào) int,
@審核者編號(hào) int,
@拒絕申請(qǐng)的理由 ntext = null ,
@申請(qǐng)狀態(tài) nvarchar(6) )
as
update 請(qǐng)假表
set 審核者編號(hào) = @審核者編號(hào),
拒絕申請(qǐng)的理由=@拒絕申請(qǐng)的理由,
申請(qǐng)狀態(tài)=@申請(qǐng)狀態(tài)
where 員工編號(hào)=@員工編號(hào)
go
create procedure dbo.刪除請(qǐng)假記錄
( @請(qǐng)假編號(hào) int,
@員工編號(hào) int,
@提交時(shí)間 datetime)
as
delete from 請(qǐng)假表
where 員工編號(hào)=@員工編號(hào) and @提交時(shí)間=提交時(shí)間
go
create procedure dbo.查看基本工資
(@登錄名 nvarchar,
@密碼 binary)
as
--得到員工編號(hào)
declare @員工編號(hào) as int
select @員工編號(hào)=員工編號(hào)
from 員工表
where 登錄名=@登錄名 and 密碼=@密碼
--查詢員工基本工資
select y.姓名,g.基本工資
from 員工表 as y inner join 工資表 as g
on y.員工編號(hào)=g.員工編號(hào)
where g.員工編號(hào)= @員工編號(hào)
go
create procedure dbo.更改員工基本工資
(@員工編號(hào) int,
@老基本工資 int,
@新基本工資 int)
as
update 工資表
set 基本工資=@新基本工資
where 員工編號(hào)=@員工編號(hào)
go
create procedure dbo.查詢員工歷史工資信息
(@員工編號(hào) int,
@開始時(shí)間 datetime,
@結(jié)束時(shí)間 datetime)
AS
select y.姓名, g.工資編號(hào),g.薪資發(fā)放日期 as 時(shí)間, g.基本工資,g.應(yīng)發(fā)工資,
g.缺勤早退扣除,g.津貼,g.住房公積金,g.養(yǎng)老費(fèi)
from 工資表 as g
join 員工表 as y
on g.員工編號(hào)=y.員工編號(hào)
where y.員工編號(hào)=@員工編號(hào)
having g.薪資發(fā)放日期 >= @開始時(shí)間 and g.薪資發(fā)放日期 <=@結(jié)束時(shí)間
group by g.工資編號(hào)
go
create procedure dbo.更改員工津貼
(@員工編號(hào) int,
@新津貼 int)
as
update 工資表
set 津貼= @新津貼
where 員工編號(hào)=@員工編號(hào)
go
create procedure dbo.查看部門總發(fā)放工資
@部門編號(hào) int
as
select b.部門編號(hào),b.部門名稱,sum(g.應(yīng)發(fā)工資) as 總發(fā)放工資
from 部門表 as b inner join 員工表 as y
on b.部門編號(hào)=y.部門編號(hào)
inner join 工資表 as g
on y.員工編號(hào)=g.員工編號(hào)
go
-----------------------------------------------------用戶自定義函數(shù)----------------------------------------------------------
--1,返回員工工資編號(hào) 通過(guò)員工編號(hào)
CREATE FUNCTION dbo.返回員工工資編號(hào) (@員工編號(hào) int)
RETURNS int
AS
begin
Declare @工資編號(hào) int
SELECT @工資編號(hào)= 工資編號(hào)
From 工資表
where 員工編號(hào)=@員工編號(hào)
return @工資編號(hào)
end
go
--2,返回員工請(qǐng)假編號(hào) 通過(guò)員工編號(hào)
create function dbo.返回員工請(qǐng)假編號(hào)(@員工編號(hào) int)
returns int
as
begin
declare @請(qǐng)假編號(hào) int
select @請(qǐng)假編號(hào)=請(qǐng)假編號(hào)
from 請(qǐng)假表
where 員工編號(hào)=@員工編號(hào)
return @請(qǐng)假編號(hào)
end
go
--3,返回員工所有請(qǐng)假記錄 通過(guò)請(qǐng)假編號(hào)
create function dbo.返回員工所有的請(qǐng)假記錄(@請(qǐng)假編號(hào) int)
returns table
as
return
(
select *
from 請(qǐng)假表
where 請(qǐng)假編號(hào)=@請(qǐng)假編號(hào)
)
go
--4返回部門編號(hào) 通過(guò)部門名稱
Create Function dbo.部門編號(hào)
(@部門名稱 nchar(10))
RETURNS int
AS
BEGIN
Declare @部門編號(hào) int
select @部門編號(hào)=部門編號(hào)
from 部門表
where 部門名稱=@部門名稱
return @部門編號(hào)
END
go
--5,返回部門成員信息 通過(guò)部門名稱
create function dbo.返回部門成員信息(@部門名稱 nchar(10))
returns table
as
return
(
select y.*
from 員工表 as y inner join 部門表 as b
on y.部門編號(hào)=b.部門編號(hào)
where b.部門名稱=@部門名稱
group by b.部門編號(hào)
)
go
------------------------------------------------------------觸發(fā)器----------------------------------------------------------
create trigger dbo.每次只能刪一個(gè)員工 on 員工表
for delete
as
if (select count(*) from deleted)>1
begin
raiserror('一次不能刪除超過(guò)一個(gè)員工!',16,1)
rollback transaction
end
go
--
