人事管理系統(tǒng)

1、人事管理系統(tǒng)1.簡要的系統(tǒng)需求分析 人事管理系統(tǒng)是非常通用的管理信息系統(tǒng)，是企事業(yè)單位實現(xiàn)辦公自動化的前提和基礎(chǔ)。一個公司要正常運作必定有一個健全的人事管理系統(tǒng)，來管理員工的信息，包括員工編號、姓名、性別、民族工資及獎懲考勤情況。除此之外，系統(tǒng)還供員工查詢個人和公共信息，管理員對員工信息進(jìn)行更改或刪除，管理公司的人事變更和管理，大大提高公司內(nèi)部員工管理的工作效率。1.1業(yè)務(wù)分析（1）部門管理：對公司的部門進(jìn)行編號，將每一個部門的部門號、部門名稱、部門描述和部門負(fù)責(zé)人信息統(tǒng)一保存在部門信息檔案中。當(dāng)新增或去除某個部門時，將其信息添加到部門信息檔案或或從中刪除。當(dāng)部門信息發(fā)生變化時，修改對應(yīng)和相關(guān)

2、的屬性值。（2）員工信息管理：對公司里的所有員工進(jìn)行統(tǒng)一編號；將每一位員工的信息錄入到員工信息檔案中。對新聘用的員工，將其信息加入到員工信息檔案中；對于解雇的員工，將其信息從信息檔案中刪除。當(dāng)員工的信息發(fā)生變動時，修改員工信息檔案中相應(yīng)的屬性。同時供員工自己查詢使用。其下還包含有個人家庭關(guān)系和個人呢工作學(xué)習(xí)的經(jīng)歷，各放在一張表格中。（3）員工職務(wù)調(diào)動管理：同樣對公司員工的職務(wù)進(jìn)行統(tǒng)一編號；登記員工調(diào)動前后的具體職務(wù)，以及調(diào)動時間。（4）獎懲管理：根據(jù)每個員工的編號登記獎懲原因和獎懲金額及獎懲月份。（5）考勤管理：根據(jù)員工編號，登記員工每個月的全勤，出差，請假時日和加班遲到早退信息。（6）工資管

3、理：對所有員工的每筆工資進(jìn)行編號，記錄工資記錄表。1.2功能需求分析（1）一般用戶可以查詢個人信息和修改自己的登錄密碼；（2）管理員可以查詢所有員工的信息并可以做添加、修改、刪除的操作。2.概念設(shè)計ER模型設(shè)計部門部門編號部門名稱部門職能描述部門負(fù)責(zé)人編號用戶管理用戶名密碼用戶類型員工員工編號姓名籍貫婚姻狀況文化程度政治面貌······性別民族身份證號生日考勤考勤編號考勤月份法定休假天數(shù)曠工天數(shù)員工編號全勤天數(shù)出差天數(shù)病假天數(shù)事假天數(shù)······員工調(diào)動調(diào)動編號調(diào)動日期員工編

4、號調(diào)動前職務(wù)調(diào)動后職務(wù)批復(fù)部門編號獎懲員工編號獎懲編號處罰金額獎勵事由獎勵金額處罰事由家庭關(guān)系家庭編號工作單位與本人關(guān)系員工編號年齡性別姓名工作經(jīng)歷職務(wù)學(xué)校/單位名稱終止日期開始日期員工編號編號工資工資編號處罰金額獎勵金額員工編號基本工資考勤月份管理員員工屬于調(diào)動查詢部門維護(hù)工作經(jīng)歷家庭關(guān)系附有附有員工調(diào)動工資考勤查詢查詢修改查詢修改管理員查詢修改查詢修改查詢修改維護(hù)3.邏輯結(jié)構(gòu)設(shè)計將ER模型轉(zhuǎn)化為關(guān)系模型（1）部門（部門編號+部門名稱+部門簡介+部門負(fù)責(zé)人）；PS：部門編號為主碼（2）員工信息（員工編號+姓名+性別+民族+生日+政治面貌+文化程度+婚姻狀況+籍貫+身份證號+工作證號+辦公電話

5、+手機號碼+部門編號+職務(wù)+狀態(tài)+工資+備注）Ps：員工編號為主碼，部門編號為外碼（3）職務(wù)調(diào)動信息（調(diào)動編號+員工編號+調(diào)動前職務(wù)+調(diào)動后職務(wù)+調(diào)動日期+批復(fù)部門號）PS：調(diào)動編號為主碼，員工編號為外碼（4）獎懲信息（獎懲編號+員工編號+獎勵事由+獎勵金額+處罰事由+處罰金額）PS：獎懲編號為主碼+員工編號為外碼（5）考勤信息（考勤月份+員工編號+全勤天數(shù)+出差天數(shù)+病假天數(shù)+事假天數(shù)+曠工天數(shù)+遲到時間+早退時間+）PS:考勤月份為主碼，員工編號為外碼（6）工資信息（工資編號+員工編號+基本工資+獎勵金額+處罰金額+考勤月份）PS：工資編號為主碼，員工編號和考勤月份為外碼（7）管理員信息（

6、用戶名+密碼+用戶類型）PS：用戶名為主碼；4.數(shù)據(jù)庫的物理設(shè)計（1）具體表的結(jié)構(gòu)設(shè)計部門：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1部門編號intNot nullprimary key2部門名稱varchar(40)Not null3部門職能描述Varchar(400)null4部門負(fù)責(zé)人編號Char(3)Not null員工：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1員工編號Char(4)Not null primary key2姓名Varchar(50)Not null3性別Char(2)null4民族varchar(40)null5生日datetimenull6政治面貌varchar(40)null7文化程度

7、varchar(40)null8婚姻狀況varchar(20)null9籍貫varchar(60)null10身份證號varchar(20)null11辦公電話varchar(30)null12手機號碼varchar(30)null13部門編號intnull14職務(wù)varchar(20)null15員工狀態(tài)（1-在職，2-兼職，3，試用，4-離職，5-退休）tinyintnull員工調(diào)動：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1調(diào)動編號Char(2)Not null primary key2員工編號Char(4)Not null3調(diào)動前職務(wù)Varchar(20)Null4調(diào)動后職務(wù)Varchar(20)

8、null5調(diào)動日期DatetimeNull6批復(fù)部門編號intNot null獎懲：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1獎懲編號Char(2)Not null primary key2員工編號Char(4)Not null3獎勵事由Varchar(200)Null4獎勵金額smallintNull5處罰事由Varchar(200)Null6處罰金額smallintnull考勤：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1考勤編號Char(3)Not null primary key2考勤月份DatetimeNot null3員工編號intNot null4全勤天數(shù)Decimal(4,1)Null5出差天數(shù)Deci

9、mal(4,1)Null6病假天數(shù)Decimal(4,1)Null7事假天數(shù)Decimal(4,1)Null8曠工天數(shù)Decimal(4,1)Null9法定休假天數(shù)Decimal(4,1)Null10遲到時間（分）TinyintNull11早退時間(分)TinyintNull12一類加班天數(shù)（法定節(jié)假日加班）IntNull13二類加班天數(shù)（周六或周日加班）IntNull14三類加班天數(shù)（日常加班）IntNull工資：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1工資編號Char(3)Not nullprimary key2員工編號Char(4)Not null3基本工資moneyNull4考勤月份Dateti

10、meNot null5獎勵金額moneyNull6處罰金額moneynull用戶管理：編號字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1用戶名Varchar(40)Not nullprimary key2密碼Varchar(40)Not null3用戶類型（1-系統(tǒng)管理員，2-普通用戶tinyintNot null家庭關(guān)系：編號說明數(shù)據(jù)結(jié)構(gòu)約束條件1編號tinyintNot nullprimary key2員工編號intNot null3姓名Varchar(50)Null4性別Char(2)Null5年齡tinyintNull6與本人關(guān)系Varchar(20)Null7工作單位Varchar(40)null工作經(jīng)

11、歷：編號說明數(shù)據(jù)結(jié)構(gòu)約束條件1編號tinyintNot nullprimary key2員工編號intNot null3開始日期Char(10)null4終止日期Char(10)null5學(xué)校/單位名稱Varchar(50)null6職務(wù)Varchar(20)null5.T-SQL命令方式創(chuàng)建數(shù)據(jù)庫文件、表、索引、聯(lián)系和主鍵、外鍵等約束（1）創(chuàng)建數(shù)據(jù)庫create database HrSysALTER DATABASE HrSySADD FILE(NAME=HrSys_dat2,FILENAME='C:Program FilesMicrosoft SQL ServerMSSQLDat

12、aHrSys_dat2.ndf'SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)LOG ON ( NAME='HrSys_LOG', FILENAME='C:Program FilesMicrosoft SQL ServerMSSQLDataHrSys_log.ldf', SIZE=3, MAXSIZE=20, FILEGROWTH=1 )（2）創(chuàng)建表use HrSyscreate table 部門(部門編號int primary key,部門名稱varchar(40) not null,部門職能描述varchar(400),

13、部門負(fù)責(zé)人編號char(4)not null,)create table 員工(員工編號char(4) primary key,姓名varchar(50) not null,性別char(2),民族varchar(40),生日varchar(20),政治面貌varchar(40),文化程度varchar(40),婚姻狀況varchar(20),籍貫varchar(60),身份證號varchar(20),辦公電話varchar(30),手機號碼varchar(40), 部門編號int not null,職務(wù)varchar(20),狀態(tài)tinyint,工資decimal(9,2)create ta

14、ble 考勤(考勤編號 int primary key,考勤月份datetime not null,員工編號char(4) not null,全勤天數(shù)Decimal(4,1),出差天數(shù)Decimal(4,1),病假天數(shù)Decimal(4,1),事假天數(shù)Decimal(4,1),曠工天數(shù)Decimal(4,1),法定休假日Decimal(4,1),遲到時間Tinyint,早退時間Tinyint,法定假日加班Decimal(4,1),周末加班Decimal(4,1),日常加班Decimal(4,1),)create table 員工調(diào)動(調(diào)動編號char(2) primary key,員工編號ch

15、ar(4) not null,調(diào)動前職務(wù)varchar(20),調(diào)動后職務(wù)varchar(20),調(diào)動日期char(10),批復(fù)部門編號int)create table 工資(工資編號char(3) primary key,員工編號char(4) not null,基本工資money,考勤月份datetime not null,獎勵金額money,處罰金額money,)alter table 工資add 總共資as 基本工資+獎勵金額-處罰金額create table 用戶管理(用戶名varchar(40)PRIMARY KEY,密碼 varchar(40) NOT NULL,用戶類型tiny

16、int NOT NULL)insert into Users values('Admin','8888',1)create table 獎懲(獎懲編號 char(10)primary key,員工編號 char(4) not null,獎勵事由 varchar(200),獎勵金額 smallint,處罰事由 varchar(200),處罰金額 smallint,)reate table 家庭關(guān)系(家庭編號 char(2) primary key, 員工編號 char(4) not null,姓名 varchar(50) not null,性別 char(2),年

17、齡 tinyint,與本人關(guān)系 varchar(20),工作單位 varchar(40)create table 工作經(jīng)歷(編號tinyint primary key,員工編號 char(4) not null,開始日期Char(10),終止日期Char(10),學(xué)校/單位名稱Varchar(50),職務(wù)Varchar(20)（3）創(chuàng)建索引（4）修改表來創(chuàng)建外鍵關(guān)系和約束6.利用T-SQL命令增加、刪除、修改數(shù)據(jù)（1）向表中增加屬性列（2）修改、添加和刪除數(shù)據(jù)例如：更新工資表的結(jié)果為7.利用T-SQL命令進(jìn)行數(shù)據(jù)的檢索和統(tǒng)計（1）投影和選擇（2）多表連接選擇在技術(shù)部或企劃部的員工姓名和所在部門

18、名稱選擇總共資在4000元以上的員工編號，姓名，職務(wù)，工資和考勤月份信息選擇有信息存在員工表，家庭關(guān)系表，工資表的員工的編號、姓名、籍貫、家庭關(guān)系、與本人關(guān)系、基本工資信息（3）分組統(tǒng)計與HAVING統(tǒng)計每個部門的男女人數(shù)select 部門編號,性別,count(*) as 人數(shù)from 員工group by 部門編號,性別選擇基本工資在2500以上的員工SELECT 員工編號,avg(基本工資)FROM 工資group by 員工編號having avg(基本工資)>2500（4）子查詢選擇沒有信息在家庭關(guān)系表中的員工信息SELECT * FROM 員工 WHERE not EXIST

19、S (SELECT * FROM 家庭關(guān)系 WHERE 家庭關(guān)系.員工編號=員工.員工編號)select 員工編號,姓名,生日,籍貫,身份證號,職務(wù),辦公電話from 員工where EXISTS(SELECT * FROM 工作經(jīng)歷 WHERE 員工.員工編號=工作經(jīng)歷.員工編號)（5）查詢結(jié)果保存select 姓名,職務(wù),辦公電話,手機號碼,調(diào)動前職務(wù)into B_INfofrom 員工,員工調(diào)動where 員工.員工編號=員工調(diào)動.員工編號select *from B_INfo8.利用SQL命令創(chuàng)建數(shù)據(jù)庫視圖（1）基于單表的建立視圖員工常用信息CREATE VIEW 員工常用信息(員工編

20、號,姓名,職位,辦公電話,手機號碼,部門編號)AS SELECT 員工編號,姓名,職務(wù),辦公電話,手機號碼,部門編號FROM dbo.員工sp_helptext 員工常用信息（2）包含多表連接的建立視圖員工考勤信息，員工基本信息和2012年考勤情況CREATE VIEW 員工考勤信息(員工姓名,職位,辦公電話,曠工天數(shù),事假天數(shù),病假天數(shù))AS SELECT 姓名,職務(wù),辦公電話,曠工天數(shù),事假天數(shù),病假天數(shù)FROM dbo.員工, dbo.考勤WHERE (datename(yy,考勤月份)='2012')sp_helptext 員工考勤信息（3）包含統(tǒng)計運算的建立視圖vie

21、w1，查詢所有職工的職工號、姓名、部門名和2011年份工資，并按部門名順序排列；create view view1asselect top 50 員工.員工編號,員工.姓名,部門.部門名稱,工資.總工資 from 員工,部門,工資 where year(考勤月份)='2011' order by 部門名稱 sp_helptext view1建立視圖view2,查詢所有員工的員工編號、姓名和平均工資；create view view2 as select 員工.員工編號,員工.姓名,avg(基本工資)as 平均工資from 員工,工資where 員工.員工編號=工資.員工編號GR

22、OUP BY 員工.員工編號,員工.姓名sp_helptext view2（4）利用SQL對視圖進(jìn)行查詢9.對視圖進(jìn)行插入、刪除、修改數(shù)據(jù)操作插入：刪除：修改：10.編寫一個自定義函數(shù)，將一個字符串作為自變量，返回顛倒順序的字符串（2）帶參數(shù)的函數(shù)，通過員工編號返回員工信息create function 員工情況(個人編號varchar(4)returns tableasreturn(select 姓名,職務(wù),辦公電話from 員工where 員工編號=個人編號)select * from 員工情況('4002')11. 編寫利用游標(biāo)進(jìn)行數(shù)據(jù)處理的存儲過程。并在存儲過程中應(yīng)用事

23、務(wù)的概念。自己確定過程的具體要求，然后編寫出來。（1）利用游標(biāo)讀取員工表中的每一行數(shù)據(jù)：declare cur_員工cursorglobal scroll dynamicforselect 員工編號,姓名,職務(wù),文化程度,籍貫,身份證號,手機號碼from 員工open cur_員工fetch next from cur_員工while fetch_status=0beginfetch next from cur_員工end（2）創(chuàng)建帶參數(shù)的存儲過程，從表員工，家庭關(guān)系，工資的連接中返回輸入的員工姓名的員工編號、姓名、籍貫、婚姻狀況、基本工資、家人姓名、與自己的關(guān)系、工作單位信息。create

24、proc yg_info 姓名varchar(20) asselect 員工.員工編號,員工.姓名,籍貫,婚姻狀況,基本工資,家庭關(guān)系.姓名,與本人關(guān)系,工作單位from 員工inner join 家庭關(guān)系on 員工.員工編號=家庭關(guān)系.員工編號join 工資on 家庭關(guān)系.員工編號=工資.員工編號where 員工.姓名like 姓名exec yg_info '陳禮貌'12. 編寫一個實現(xiàn)修改表的觸發(fā)器，實現(xiàn)完整性控制。(1)更新員工表信息use HrSysCREATE TRIGGER workerid ON 員工FOR UPDATEASIF (update(員工編號) BEG

25、INDECLARE BHO CHAR(4),BHN CHAR(4) SELECT BHO=員工編號FROM DELETED SELECT BHN=員工編號FROM INSERTEDUPDATE 工資 SET 工資.員工編號=BHN WHERE 工資.員工編號=BHOUPDATE 考勤 SET 考勤.員工編號=BHN WHERE 考勤.員工編號=BHOUPDATE 獎懲 SET 獎懲.員工編號=BHN WHERE 獎懲.員工編號=BHO UPDATE 工作經(jīng)歷 SET 工作經(jīng)歷.員工編號=BHN wHERE 工作經(jīng)歷.員工編號=BHOUPDATE 員工調(diào)動 SET 員工調(diào)動.員工編號=BHN W

26、HERE 員工調(diào)動.員工編號=BHOUPDATE 家庭關(guān)系 set 家庭關(guān)系.員工編號=BHN WHERE 家庭關(guān)系.員工編號=BHO ENDUPDATE dbo.員工SET 員工編號='1012' WHERE 員工編號='1002'（2）刪除員工編號CREATE TRIGGER YGDELON dbo.員工FOR DELETEAS BEGIN DECLARE BH CHAR(4) SELECT BH=員工編號FROM DELETED IF EXISTS (SELECT * FROM 部門WHERE 部門負(fù)責(zé)人編號=BH) UPDATE dbo.部門 SET 部

27、門負(fù)責(zé)人編號=NULL WHERE 部門負(fù)責(zé)人編號=BHEnddelete from 員工where 員工編號='2001'13. 設(shè)置服務(wù)器身份驗證模式。用命令創(chuàng)建若干不同驗證模式的登錄賬戶。（1）在控制面板了面創(chuàng)建用戶（2）創(chuàng)建Windows身份驗證和SQL Server的登錄賬戶14. 將部分登錄賦予服務(wù)器角色。然后撤消。exec sp_addsrvrolemember '四葉草','sysadmin'exec sp_addsrvrolemember 'ZY-20080929TQYO滿天星','dbcreator&#

28、39;exec sp_helpsrvrolemember 'sysadmin'exec sp_helpsrvrolemember 'dbcreator'撤銷服務(wù)器角色成員：exec sp_dropsrvrolemember '四葉草','sysadmin'exec sp_dropsrvrolemember 'ZY-20080929TQYO滿天星','dbcreator'15. 將部分登錄映射到你的數(shù)據(jù)庫中成為用戶。use HrSysgocreate user 四葉草 from login 四葉草create user ZY-20080929TQYO滿天星 from login ZY-20080929TQYO滿天星16. 創(chuàng)建自定義角色、架構(gòu)。（1）創(chuàng)建自定義角色create role role1create role role2create role role3 authorization 四葉草（2）創(chuàng)建自定義架構(gòu)create schema sc1create sche