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