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

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

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

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

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

6、用戶名+密碼+用戶類型）PS：用戶名為主碼；4.數(shù)據(jù)庫(kù)的物理設(shè)計(jì)（1）具體表的結(jié)構(gòu)設(shè)計(jì)部門：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1部門編號(hào)intNot nullprimary key2部門名稱varchar(40)Not null3部門職能描述Varchar(400)null4部門負(fù)責(zé)人編號(hào)Char(3)Not null員工：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1員工編號(hào)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身份證號(hào)varchar(20)null11辦公電話varchar(30)null12手機(jī)號(hào)碼varchar(30)null13部門編號(hào)intnull14職務(wù)varchar(20)null15員工狀態(tài)（1-在職，2-兼職，3，試用，4-離職，5-退休）tinyintnull員工調(diào)動(dòng)：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1調(diào)動(dòng)編號(hào)Char(2)Not null primary key2員工編號(hào)Char(4)Not null3調(diào)動(dòng)前職務(wù)Varchar(20)Null4調(diào)動(dòng)后職務(wù)Varchar(20)

8、null5調(diào)動(dòng)日期DatetimeNull6批復(fù)部門編號(hào)intNot null獎(jiǎng)懲：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1獎(jiǎng)懲編號(hào)Char(2)Not null primary key2員工編號(hào)Char(4)Not null3獎(jiǎng)勵(lì)事由Varchar(200)Null4獎(jiǎng)勵(lì)金額smallintNull5處罰事由Varchar(200)Null6處罰金額smallintnull考勤：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1考勤編號(hào)Char(3)Not null primary key2考勤月份DatetimeNot null3員工編號(hào)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遲到時(shí)間（分）TinyintNull11早退時(shí)間(分)TinyintNull12一類加班天數(shù)（法定節(jié)假日加班）IntNull13二類加班天數(shù)（周六或周日加班）IntNull14三類加班天數(shù)（日常加班）IntNull工資：編號(hào)字段名稱數(shù)據(jù)結(jié)構(gòu)約束條件1工資編號(hào)Char(3)Not nullprimary key2員工編號(hào)Char(4)Not null3基本工資moneyNull4考勤月份Dateti

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

11、歷：編號(hào)說(shuō)明數(shù)據(jù)結(jié)構(gòu)約束條件1編號(hào)tinyintNot nullprimary key2員工編號(hào)intNot null3開(kāi)始日期Char(10)null4終止日期Char(10)null5學(xué)校/單位名稱Varchar(50)null6職務(wù)Varchar(20)null5.T-SQL命令方式創(chuàng)建數(shù)據(jù)庫(kù)文件、表、索引、聯(lián)系和主鍵、外鍵等約束（1）創(chuàng)建數(shù)據(jù)庫(kù)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 部門(部門編號(hào)int primary key,部門名稱varchar(40) not null,部門職能描述varchar(400),

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

14、ble 考勤(考勤編號(hào) int primary key,考勤月份datetime not null,員工編號(hào)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),遲到時(shí)間Tinyint,早退時(shí)間Tinyint,法定假日加班Decimal(4,1),周末加班Decimal(4,1),日常加班Decimal(4,1),)create table 員工調(diào)動(dòng)(調(diào)動(dòng)編號(hào)char(2) primary key,員工編號(hào)ch

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

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

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

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

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

20、號(hào),姓名,職位,辦公電話,手機(jī)號(hào)碼,部門編號(hào))AS SELECT 員工編號(hào),姓名,職務(wù),辦公電話,手機(jī)號(hào)碼,部門編號(hào)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)計(jì)運(yùn)算的建立視圖vie

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

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

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

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

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

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

27、門負(fù)責(zé)人編號(hào)=NULL WHERE 部門負(fù)責(zé)人編號(hào)=BHEnddelete from 員工where 員工編號(hào)='2001'13. 設(shè)置服務(wù)器身份驗(yàn)證模式。用命令創(chuàng)建若干不同驗(yàn)證模式的登錄賬戶。（1）在控制面板了面創(chuàng)建用戶（2）創(chuàng)建Windows身份驗(yàn)證和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ù)庫(kù)中成為用戶。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