1、員工管理系統數據庫(sql server2009-12-27 16:49員工管理系統數據庫(sql server數據庫設置:工資管理系統帳戶設置:三個用戶管理員,部門經理(查詢本部門員工的信息,員工(權限只能查詢本人創建數據庫設置權限-表1,員工表(員工編號(PK(FK,姓名,出生日期,身份證號,加入公司日期,登錄名,密碼,電話,電子郵件,部門編號(FK,員工級別,擁有假期時間,備注2, 部門表 (部門名稱,部門編號(PK(自動增量,部門經理編號(員工編號,部門描述3,工資表(工資編號(PK,員工編號,基本工資,應發工資,缺勤早退扣除,津貼,獎金,住房公積金,養老費,是否為單位住房,水電費,工

2、作日期,當前日期,薪資發放當前日期 4,考勤表(考勤編號(PK,員工編號,到達時間,記錄者編號(員工編號,考勤類型,日期 5,請假表(請假編號(PK, 員工編號,請假原因,提交時間,請假開始時間,請假結束時間,審核者編號,拒絕申請的理由,申請狀態6,假期表(假期編號(PK,假期具體日期,假期名稱,是否為法定假期各表PK為自動增量,聚集索引表之間的關系員工表(部門編號-部門表(部門編號員工表(員工編號-工資表(員工編號員工表(員工編號-考勤表(員工編號員工表(員工編號-請假表(員工編號表之中的約束1,員工表(check密碼為6位以上,check登錄名長度大于1,unique登錄名唯一2,工資表(

3、基本工資>600,是否為工資住房="是"or"否",應發工資=基本工資+津貼+獎金-缺勤早退扣除-住房公積金-養老費-水電費3,考勤表(記錄者編號<>員工編號 and 考勤類型 =“缺勤”or“遲到”or“早退”4,請假表(員工編號<>審核者編號 and 申請狀態 =“已提交”、“已取消”、“已批準”、“已否決”and 開始時間<結束時間-視圖1,員工基本信息基表:員工表-(左外聯接-部門表得到了員工的詳細信息,其中包括員工的基本信息、員工的部門信息2, 考勤信息基表:考勤表-(內聯接-員工表得到員工的姓名、員工所屬部

4、門編號,考勤情況3, 請假信息基表:請假表-(內聯接-員工表得到員工的請假申請信息,申請請假狀態和請假批準人姓名4,工資信息基表:員工表-(左外聯接-部門表-(內聯接-工資表得到員工的詳細工資信息5, 基本工資基表:員工表-(內聯接-工資表得到員工的基本工資6, 變動工資基表:員工表-(內聯接-工資表得到員工基本工資的變動增加信息7,假期信息基表:假期表得到假期信息-存儲過程1,增加員工基表:員工表插入員工表一條記錄2, 刪除員工基表:員工表刪除員工表一條記錄根據員工編號3,更新員工信息基表:員工表更改員工的基本信息據員工登錄名,密碼4,得到用戶密碼基表:員工表 input用戶名,員工編號ou

5、tput用戶密碼5,更改密碼基表:員工表據員工編號,登錄名,原始密碼用戶表的密碼6, 刪除部門全部員工基表:員工表7, 增加部門基表:部門表8,請假申請基表:請假表員工插入請假表一條記錄9, 更新員工請假狀態基表:請假表經理或管理員更改請假表中的申請請假狀態,或和拒絕申請的理由10,刪除請假記錄基表:請假表刪除請假表一條記錄據員工編號和提交時間11,查看員工基本工資基表:工資表-員工表據登陸名和密碼查出姓名,基本工資12,更改員工基本工資基表:工資表-員工表更改員工的基本工資13,查詢員工歷史工資信息基表:員工表-工資表 input員工編號 output得到員工工資具體信息14,更改員工津貼基

6、表:員工表-工資表 input員工編號 output更改員工的津貼15,查看部門總發放工資基表:工資表,員工表,部門表據部門編號-用戶自定義函數1,返回員工工資編號通過員工編號2,返回員工請假編號通過員工編號3,返回員工所有請假記錄通過請假編號4,返回部門成員信息通過部門名稱-觸發器1,每次只能刪一個員工定義在在員工表上否則給出出錯信息2, 更改員工擁有假期定義在員工表上當員工請假申請狀態是"否決"時,之前其擁有假期時間已經被減少,否決完成后應該相應恢復其擁有假期時間3, 員工申請請假狀態為批準時定義在工資表自動將員工表的缺勤扣除項設為應扣的一半4, 更新應發工資定義在工資

7、表為基本工資-缺勤早退扣除+獎金+津貼-住房公積金-養老費-水電費-以下是代碼其中約束和之間的關系沒建立create database SalaryManagementon(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 = 2MBGO-創建基本表-use SalaryManagem

8、entcreate 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

9、NULL,擁有假期時間 int not NULL,備注 text nullgocreate table dbo.部門表(部門編號 int IDENTITY (1, 1 NOT NULL primary key,部門名稱 nchar (10 not NULL,部門經理編號 int not null,部門描述 text nullgocreate table dbo.工資表(工資編號 int IDENTITY (1, 1 NOT NULL primary key,員工編號 int not null,基本工資 int not null,應發工資 int not null,缺勤早退扣除 int not

10、null,津貼 int not null,住房公積金 int not null,養老費 int not null,是否為單位住房 bit not null,-0表示否,1表示是水電費 int not null,-若是否為單位住房為0,則水電費為0工作日期 datetime NOT NULL,當前記錄日期 datetime NOT NULL,薪資發放日期 datetime NOT NULlgocreate table dbo.考勤表(考勤編號 int identity(1,1 not null primary key,員工編號 int not null,到達時間 datetime not nul

11、l,記錄者編號 int not null,考勤類型 nvarchar(8 not null,-應為“缺勤”or“遲到”or“早退”日期 datetime not nullgocreate table dbo.請假表(請假編號 int identity(1,1 not null primary key,員工編號 int not null,請假原因 text not null,提交時間 datetime not null,請假開始時間 datetime not null,請假結束時間 datetime not null,審核者編號 int null,拒絕申請的理由 ntext null,申請狀態

12、nvarchar(6 null -只能為“已提交”、“已取消”、“已批準”、“已否決”gocreate table dbo.假期表(假期編號 int identity(1,1 not null primary key,假期具體日期 datetime not null,假期名稱 nvarchar(20 not null,是否為法定假期 bit not nullgo-創建視圖-use SalaryManagementgocreate view dbo.員工基本信息ASSELECT y.員工編號,y.姓名, y.電子郵件, y.電話, y.登錄名, y.出生日期,y.部門編號,b.部門名稱,y.員工

13、級別,y.擁有假期時間FROM dbo.員工表 as y left outer join dbo.部門表 bon y.部門編號=b.部門編號gocreate view dbo.考勤信息asselect k.考勤編號,y.姓名,k.日期,k.考勤類型from 考勤表 as k inner join 員工表 as yon k.員工編號=k.員工編號gocreate view dbo.請假信息asselect q.請假編號,y.姓名,q.提交時間,q.請假原因,q.申請狀態,q.拒絕申請的理由from 請假表 as q inner join 員工表 as yon q.員工編號=y.員工編號gocre

14、ate view dbo.工資信息asselect y.員工編號,b.部門名稱,y.姓名,g.基本工資,g.工資編號,g.應發工資,g.缺勤早退扣除,g.津貼,g.住房公積金,g.養老費,g.水電費,g.薪資發放日期from 工資表 as g inner join 員工表 as yon g.員工編號 = y.員工編號 left outer join 部門表 as bon y.部門編號 = b.部門編號gocreate view dbo.基本工資asselect y.姓名,y.員工編號,g.基本工資from 員工表 as y inner join 工資表 as gon y.員工編號=g.員工編號

15、gocreate view dbo.變動工資asselect y.姓名,y.員工編號,g.工資編號,g.缺勤早退扣除,g.津貼,g.住房公積金,g.養老費,g.水電費,g.薪資發放日期from 員工表 as y inner join 工資表 as gon y.員工編號 =g. 員工編號gocreate view dbo.假期信息asselect 假期編號,假期具體日期 as 假期日期,假期名稱,是否為法定假期from 假期表go-創建存儲過程-use SalaryManagement-利用系統函數error來確定每個insert語句是否發生錯誤,如果發生,事務將會滾.gocreate proc

16、edure dbo.增加員工(姓名 nvarchar(50 ,出生日期 datetime ,身份證號 nvarchar(18 ,加入公司日期 datetime ,登錄名 nvarchar(20 ,密碼 binary(20 ,電話 nvarchar (50 ,電子郵件 nvarchar (50 , 部門編號 int ,員工級別 int ,擁有假期時間 int,備注 text = nullasbegin transactioninsert 員工表(姓名,出生日期,身份證號,加入公司日期,登錄名,密碼,電話,電子郵件,部門編號,員工級別,擁有假期時間,備注values (姓名,出生日期,身份證號,加

17、入公司日期,登錄名,密碼,電話,電子郵件,部門編號,員工級別,擁有假期時間,備注if error<>0beginrollback tranreturnendgocreate procedure dbo.刪除員工員工編號 intasdelete from 員工表where 員工編號=員工編號goCREATE PROCEDURE dbo.更改員工信息( 員工編號 int,姓名 nvarchar(50 = null,出生日期 datetime = null,身份證號 nvarchar(18 = null,加入公司日期 datetime = null,電話 nvarchar (50 =nu

18、ll,電子郵件 nvarchar (50=null , 部門編號 int =null,員工級別 int=null ,擁有假期時間 int =null,備注 text = nullAsUPDATE 員工表Set 員工編號=員工編號,姓名=姓名,出生日期=出生日期,身份證號=身份證號,電話=電話,電子郵件=電子郵件,員工級別=員工級別,擁有假期時間=擁有假期時間,備注=備注WHERE 員工編號 = 員工編號return RowcountgoCREATE PROCEDURE dbo.得到員工密碼(員工編號 int,密碼 binary(20 outputAsSELECT 密碼 = 密碼FROM 員工表

19、WHERE 員工編號=員工編號return RowcountgoCreate Procedure dbo.更改密碼(登錄名 as nvarchar(20,老密碼 as binary(20,新密碼 as binary(20ASDeclare 員工編號 as intselect 員工編號=員工編號from 員工表where 登錄名=登錄名 and 密碼=老密碼Update 員工表set 密碼=新密碼where 員工編號=員工編號gocreate procedure dbo.刪除部門全部員工部門編號 intasdelete from 員工表where 部門編號= 部門編號goCreate Proce

20、dure dbo.增加部門(部門名稱 nchar(10,部門描述 nvarchar(50,部門經理編號 intASinsert 部門表(部門名稱,部門描述,部門經理編號values (部門名稱,部門描述,部門經理編號gocreate procedure dbo.請假申請(員工編號 int ,請假原因 text ,提交時間 datetime,請假開始時間 datetime,請假結束時間 datetimeasinsert 請假表(員工編號,請假原因 ,提交時間,請假開始時間,請假結束時間values (員工編號, 請假原因,提交時間, 請假開始時間,請假結束時間 gocreate procedur

21、e dbo.更新員工請假狀態(員工編號 int,審核者編號 int,拒絕申請的理由 ntext = null ,申請狀態 nvarchar(6 asupdate 請假表set 審核者編號 = 審核者編號,拒絕申請的理由=拒絕申請的理由,申請狀態=申請狀態where 員工編號=員工編號gocreate procedure dbo.刪除請假記錄( 請假編號 int,員工編號 int,提交時間 datetimeasdelete from 請假表where 員工編號=員工編號 and 提交時間=提交時間gocreate procedure dbo.查看基本工資(登錄名 nvarchar,密碼 bina

22、ryas-得到員工編號declare 員工編號 as intselect 員工編號=員工編號from 員工表where 登錄名=登錄名 and 密碼=密碼-查詢員工基本工資select y.姓名,g.基本工資from 員工表 as y inner join 工資表 as gon y.員工編號=g.員工編號where g.員工編號= 員工編號gocreate procedure dbo.更改員工基本工資(員工編號 int,老基本工資 int,新基本工資 intasupdate 工資表set 基本工資=新基本工資where 員工編號=員工編號gocreate procedure dbo.查詢員工歷

23、史工資信息(員工編號 int,開始時間 datetime,結束時間 datetimeASselect y.姓名, g.工資編號,g.薪資發放日期 as 時間, g.基本工資,g.應發工資,g.缺勤早退扣除,g.津貼,g.住房公積金,g.養老費from 工資表 as gjoin 員工表 as yon g.員工編號=y.員工編號where y.員工編號=員工編號having g.薪資發放日期 >= 開始時間 and g.薪資發放日期 <=結束時間group by g.工資編號gocreate procedure dbo.更改員工津貼(員工編號 int,新津貼 intasupdate 工

24、資表set 津貼= 新津貼where 員工編號=員工編號gocreate procedure dbo.查看部門總發放工資部門編號 intasselect b.部門編號,b.部門名稱,sum(g.應發工資 as 總發放工資from 部門表 as b inner join 員工表 as yon b.部門編號=y.部門編號inner join 工資表 as gon y.員工編號=g.員工編號go-用戶自定義函數-1,返回員工工資編號通過員工編號CREATE FUNCTION dbo.返回員工工資編號 (員工編號 intRETURNS intASbegin Declare 工資編號 int SELECT 工資編號= 工資編號 From 工資表 where 員工編號=員工編號 return