工資管理系統(tǒng)設(shè)計(jì)規(guī)劃

1、.工資管理系統(tǒng)設(shè)計(jì)規(guī)劃目錄一、項(xiàng)目名稱：工資管理系統(tǒng)設(shè)計(jì)2二、實(shí)訓(xùn)目的：2三、應(yīng)掌握的知識(shí)點(diǎn)2四、應(yīng)訓(xùn)練的能力點(diǎn)2五、數(shù)據(jù)庫(kù)設(shè)計(jì)內(nèi)容3 (一)需求分析.3 （二）數(shù)據(jù)庫(kù)概念設(shè)計(jì).3 （三）數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)5 （四）數(shù)據(jù)庫(kù)與數(shù)據(jù)表設(shè)計(jì) 物理結(jié)構(gòu)設(shè)計(jì)5 1.數(shù)據(jù)庫(kù)與數(shù)據(jù)表設(shè)計(jì).5 2.數(shù)據(jù)完整性設(shè)計(jì).6六、數(shù)據(jù)庫(kù)操作實(shí)施與結(jié)果數(shù)據(jù)庫(kù)對(duì)象的設(shè)計(jì)7 (一)建數(shù)據(jù)庫(kù)工資管理庫(kù).7 （二）建表員工信息表、部門信息表、工資信息表.7（三）視圖.9（四）存儲(chǔ)過(guò)程.9（五）觸發(fā)器 10（六）索引11（七）運(yùn)行和維護(hù)12 1.關(guān)系圖的建立.12 2. 簡(jiǎn)單查詢.123. 復(fù)雜查詢.154.數(shù)據(jù)庫(kù)的用戶與權(quán)限管理17

2、5.數(shù)據(jù)庫(kù)的備份.206.數(shù)據(jù)的導(dǎo)入與導(dǎo)出.21七、總結(jié)22八、評(píng)語(yǔ).23. v.一、項(xiàng)目名稱：工資管理系統(tǒng)設(shè)計(jì)二、實(shí)訓(xùn)目的：l 了解數(shù)據(jù)庫(kù)系統(tǒng)的理論l 掌握數(shù)據(jù)庫(kù)設(shè)計(jì)的基本方法，熟悉數(shù)據(jù)庫(kù)設(shè)計(jì)的步驟；l 通過(guò)設(shè)計(jì)數(shù)據(jù)庫(kù)系統(tǒng)應(yīng)用課題，進(jìn)一步熟悉數(shù)據(jù)庫(kù)管理系統(tǒng)的操作技術(shù)，提高動(dòng)手能力，提高分析問(wèn)題和解決問(wèn)題的能力l 學(xué)習(xí)基本數(shù)據(jù)庫(kù)編程方法l 快速跟蹤數(shù)據(jù)庫(kù)系統(tǒng)軟件的新技術(shù)及市場(chǎng)應(yīng)用動(dòng)態(tài)。三、應(yīng)掌握的知識(shí)點(diǎn)l 數(shù)據(jù)庫(kù)基本原理l 關(guān)系數(shù)據(jù)庫(kù)的設(shè)計(jì)方法、設(shè)計(jì)思路l 初步掌握一種關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言四、應(yīng)訓(xùn)練的能力點(diǎn)l SQL Server 2000的操作與使用l 數(shù)據(jù)庫(kù)的概念設(shè)計(jì)與邏輯設(shè)計(jì)l 數(shù)據(jù)庫(kù)的建立與

3、管理、數(shù)據(jù)表的建立與操作l SQL查詢語(yǔ)言的使用與編程l 設(shè)計(jì)和開(kāi)發(fā)一個(gè)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)部分五、數(shù)據(jù)庫(kù)設(shè)計(jì)內(nèi)容(一)需求分析本系統(tǒng)要XX現(xiàn)以下主要功能：1.數(shù)據(jù)錄入功能主要任務(wù)是對(duì)各種工資信息進(jìn)行日常的管理，如工資信息的輸入、查詢、修改、增加、刪除，迅速準(zhǔn)確地完成各種工資信息的統(tǒng)計(jì)計(jì)算和匯總，快速打印出報(bào)表。2.數(shù)據(jù)查詢功能系統(tǒng)需要提供以下查詢功能：1) 員工考勤情況2) 員工工種情況，反映員工的工種、等級(jí)，崗位工資等信息3) 員工津貼信息情況，反映員工的加班時(shí)間，加班類別、加班天數(shù)、崗位補(bǔ)貼情況等4) 員工基本信息情況5) 員工月工資情況：年，月，職工號(hào)，XX，部門名，基本工資，病

4、假扣款，事假扣款，3.應(yīng)發(fā)工資，實(shí)發(fā)工資3.數(shù)據(jù)統(tǒng)計(jì)功能1) 統(tǒng)計(jì)員工年終獎(jiǎng)金2) 統(tǒng)計(jì)每部門月工資情況（二）數(shù)據(jù)庫(kù)概念設(shè)計(jì)分析前面的系統(tǒng)功能要求，需要一個(gè)表來(lái)存儲(chǔ)和管理員工信息，使系統(tǒng)能夠接受員工原始的各項(xiàng)數(shù)據(jù)，以實(shí)現(xiàn)數(shù)據(jù)錄入、查詢或統(tǒng)計(jì)員工信息等功能。員工是本系統(tǒng)的第一個(gè)實(shí)體。為了能實(shí)現(xiàn)查詢和統(tǒng)計(jì)部門內(nèi)員工情況等功能，必須有一個(gè)表來(lái)存儲(chǔ)和管理所有部門的信息。部門是本系統(tǒng)的第二個(gè)實(shí)體。工資是以員工工作情況來(lái)制定的。需要建立一個(gè)表來(lái)記錄各種工資、津貼、扣款的信息。它主要為各種查詢和統(tǒng)計(jì)功能提供工資數(shù)據(jù)。工資是第3個(gè)實(shí)體。據(jù)此可以繪出工資管理系統(tǒng)數(shù)據(jù)庫(kù)的E-R圖如下：部門 1工作N 獲得工資員工

5、 M M 1實(shí)體屬性列表如下：實(shí)體屬性員工工號(hào)XX性別年齡籍貫XX號(hào)婚姻狀況學(xué)歷部門名稱工種工齡職務(wù)等級(jí)部門部門名稱部門負(fù)責(zé)人部門人數(shù)工資工號(hào)工資年月崗位工資崗位補(bǔ)貼出勤天數(shù)加班工時(shí)加班天數(shù)加班類別加班工資病假扣款事假扣款其他扣款應(yīng)發(fā)工資實(shí)發(fā)工資（三）數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)將數(shù)據(jù)庫(kù)的概念模型轉(zhuǎn)換為關(guān)系模型：實(shí)體轉(zhuǎn)換成的關(guān)系模式有：?jiǎn)T工（工號(hào)，XX，性別，年齡，籍貫，XX號(hào)，婚姻狀況，學(xué)歷，部門名稱，工種，工齡，職務(wù)等級(jí)）部門（部門編號(hào)，部門名稱，部門負(fù)責(zé)人，部門人數(shù)）獲得（工號(hào)，XX）工資（工號(hào)，工資年月，崗位工資，崗位補(bǔ)貼，出勤天數(shù)，加班工時(shí)，加班工資，加班天數(shù)，加班類別，病假扣除，事假扣除，其它扣

6、除，應(yīng)發(fā)工資，實(shí)發(fā)工資）因?yàn)?，員工與工資是1：N的關(guān)系，所以各關(guān)系模式為：?jiǎn)T工（工號(hào)，XX，性別，年齡，籍貫，XX號(hào)，婚姻狀況，學(xué)歷，部門名稱，工種，工齡，職務(wù)等級(jí)）部門（部門名稱，部門負(fù)責(zé)人，部門人數(shù)）工資（工號(hào)，工資年月，崗位工資，崗位補(bǔ)貼，出勤天數(shù)，加班工時(shí)，加班工資，加班天數(shù)，加班類別，病假扣除，事假扣除，其它扣除，應(yīng)發(fā)工資，實(shí)發(fā)工資）帶下劃線的屬性為各關(guān)系模式的外碼，字體為粗體的屬性為各關(guān)系模式的主碼。關(guān)系規(guī)X式 ：工資管理系統(tǒng)1NF 每個(gè)屬性均不可再分（四）數(shù)據(jù)庫(kù)與數(shù)據(jù)表設(shè)計(jì) 物理結(jié)構(gòu)設(shè)計(jì)在SQL數(shù)據(jù)庫(kù)中需要建立3個(gè)數(shù)據(jù)表：?jiǎn)T工信息數(shù)據(jù)表、部門信息數(shù)據(jù)表和工資數(shù)據(jù)表。1.數(shù)據(jù)庫(kù)與數(shù)

7、據(jù)表設(shè)計(jì)員工信息數(shù)據(jù)表定義其中，“工號(hào)”是表的主鍵，惟一標(biāo)識(shí)。“部門名稱”是外鍵?！肮ぬ?hào)”將記錄員工的編號(hào)，在員工信息表中增加這一列時(shí)，雖然增加了數(shù)據(jù)冗余，但可以在查詢每個(gè)員工信息時(shí)，提高系統(tǒng)的性能。因?yàn)閱T工信息表的記錄數(shù)相對(duì)固定，相比之下，增加這個(gè)冗余的列對(duì)于整個(gè)系統(tǒng)來(lái)說(shuō)是有利的。通過(guò)它，系統(tǒng)將引用到工資信息。此表取名為“員工”部門信息數(shù)據(jù)表定義其中，部門名稱是該表的主鍵，惟一標(biāo)識(shí)一個(gè)部門。此表取名為“部門”工資數(shù)據(jù)表定義其中，“工號(hào)”作為該表的主鍵，唯一標(biāo)識(shí)，保證客房信息表數(shù)據(jù)的參照完整性。此表表取名為“工資”根據(jù)上面三個(gè)數(shù)據(jù)表的設(shè)計(jì)，可執(zhí)行下面的步驟創(chuàng)建數(shù)據(jù)表 1)打開(kāi)企業(yè)管理器，在本地

8、服務(wù)器上創(chuàng)建新的數(shù)據(jù)庫(kù)“工資管理庫(kù)”2)創(chuàng)建表 員工、部門、工資2.數(shù)據(jù)完整性設(shè)計(jì)1）給每個(gè)表實(shí)施主鍵約束。主鍵約束、非空值約束員工：工號(hào)為主鍵，所以此項(xiàng)非空部門：部門名稱為主鍵，所以此項(xiàng)非空工資：工號(hào)為主鍵，所以此項(xiàng)非空2）實(shí)施CHECK約束l 員工表中建立一個(gè)檢查約束，即員工性別不是男就是女CHECK(性別 IN('男','女')l 工資表中建立一個(gè)對(duì)于出勤天數(shù)的檢查約束，即出勤天數(shù)在0-20天之間 check (出勤天數(shù) >=0 and 出勤天數(shù)<=20)3）外鍵約束員工表中，工號(hào)、部門名稱為外鍵。FK_員工_工資 FOREIGN KEY(工號(hào)

9、) REFERENCES 工資(工號(hào))FK_員工_部門 FOREIGN KEY(部門名稱) REFERENCES 部門(名稱)通過(guò)此鍵建立3個(gè)表的聯(lián)系4）惟一約束a) 每個(gè)表的主鍵需定義為惟一性：對(duì)于工資表，因?yàn)楣ぬ?hào)是主鍵，聯(lián)系著員工表，所以建立唯一性 alter table 工資add constraint UQ_工號(hào) unique(工號(hào))b) 對(duì)于員工表，員工的工號(hào)、XX號(hào)、部門名稱都是用于識(shí)別員工身份，所以建立唯一約束 alter table 員工 add constraint UQ_工號(hào) unique(工號(hào))5) 強(qiáng)制約束對(duì)INSERT和UPDATE操作強(qiáng)制約束，表明以后對(duì)員工表中的工

10、號(hào)、部門名稱的添加和更新操作都會(huì)分別檢查在工資表、部門表中是否存在與此相應(yīng)的內(nèi)容。6）規(guī)則1) 為了檢查員工表中輸入的XX是有效位數(shù)，可以創(chuàng)建一個(gè)規(guī)則綁定到該列；在數(shù)據(jù)操作時(shí)進(jìn)行檢查。規(guī)則的定義語(yǔ)句為： (LEN (XX號(hào))=15)OR(LEN(XX號(hào))=18)六、數(shù)據(jù)庫(kù)操作實(shí)施與結(jié)果數(shù)據(jù)庫(kù)對(duì)象的設(shè)計(jì)（一）建數(shù)據(jù)庫(kù)工資管理庫(kù)IF EXISTS(SELECT * FROM sysdatabases WHERE name='工資管理庫(kù)') drop database 工資管理庫(kù)GOCREATE DATABASE 工資管理庫(kù) ON PRIMARY(NAME=工資管理庫(kù),FILENAM

11、E='D:工資管理庫(kù)_data.mdf',SIZE=4MB,MAXSIZE=6MB,FILEGROWTH=2MB)LOG ON (NAME ='工資管理庫(kù)_log', FILENAME ='D:工資管理庫(kù).ldf', SIZE=1MB,MAXSIZE=25MB,FILEGROWTH=10%)（二）建表員工信息表、部門信息表、工資信息表l IF EXISTS(SELECT * FROM sysobjects WHERE name='員工') DROP TABLE 員工 GOcreate table 員工(工號(hào) VARCHAR(50)

12、 NOT NULL primary key,XX VARCHAR(50) NOT NULL,性別 CHAR(2) DEFAULT'男'CHECK(性別 IN('男','女'),年齡 CHAR(10) NOT NULL,籍貫 VARCHAR(50) NOT NULL,XX號(hào) VARCHAR(50) UNIQUE NOT NULL,婚姻情況 VARCHAR(50),學(xué)歷 VARCHAR(50),部門名稱 VARCHAR(50) NOT NULL,工種 VARCHAR(50),工齡 VARCHAR(50) NOT NULL,職務(wù)等級(jí) VARCHAR(5

13、0)GOl IF EXISTS(SELECT * FROM sysobjects WHERE name='部門') DROP TABLE 部門 GOcreate table 部門(部門名稱 VARCHAR(50) primary key NOT NULL,部門負(fù)責(zé)人 VARCHAR(50) NOT NULL, 部門人數(shù) VARCHAR(50)GOl IF EXISTS(SELECT * FROM sysobjects WHERE name='工資') DROP TABLE 工資 GOCREATE TABLE 工資(工號(hào) varchar(50) not null

14、primary key,工資年月 varchar(50) not null ,崗位工資 decimal not null,崗位補(bǔ)貼 decimal,出勤天數(shù) decimal not null,加班工時(shí) decimal, 加班工資 decimal, 加班天數(shù) decimal,加班類別 varchar(50),病假扣款 decimal,事假扣款 decimal, 其他扣款 decimal, 應(yīng)發(fā)工資 decimal not null, 實(shí)發(fā)工資 decimal not null)GO（三）視圖(1)查詢員工工資情況建立一個(gè)視圖，反映員工XX及工資情況。CREATE VIEW 員工_工資 AS SE

15、LECT 員工.XX, 工資.* FROM 員工,工資 WHERE 員工.工號(hào)=工資.工號(hào)（四）存儲(chǔ)過(guò)程(1)指定員工工資情況的查詢定義一個(gè)名為stsffCost的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程以以工資年月(staffgongzi)、出勤天數(shù)(staffchuqin)、加班工資(staffjiaban)、應(yīng)發(fā)工資(staffying)、實(shí)發(fā)工資(staffshi)為輸入?yún)?shù)返回指定員工工資信息的存儲(chǔ)過(guò)程。stsffCost代碼如下：CREATE PROCEDURE staffCoststaffgongzi decimal,staffchuqin decimal,staffjiaban decimal,s

16、taffying decimal,staffshi decimalASSELECT *FROM 工資WHERE 工資年月=staffgongzi AND 出勤天數(shù)=staffchuqin AND 加班工資=staffjiaban AND應(yīng)發(fā)工資=staffying AND實(shí)發(fā)工資=staffshiGO(2）指定X圍的員工的信息查詢。以員工工號(hào)、部門編號(hào)為輸入?yún)?shù)返回指定員工基本信息的存儲(chǔ)過(guò)程，要求輸入部分員工信息查詢員工全部信息。定義一個(gè)名為stsffInfo的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程以員工工號(hào)(staffNum)、部門編號(hào)(bmNum)作為輸入?yún)?shù)，存儲(chǔ)過(guò)程返回查詢到的結(jié)果集。satffInfo

17、的定義代碼如下：CREATE PROCEDURE staffInfostaffNum VARCHAR(50)='%',bmNum VARCHAR(50)='%'ASSELECT *FROM 員工,部門WHERE 員工.部門名稱=部門.部門名稱 AND (員工.工號(hào) LIKE staffNum VARCHAR(50)+'%') AND (部門編號(hào) LIKE bmNum VARCHAR(50)+'%')GO（3）統(tǒng)計(jì)指定年份各部門每個(gè)月份的工資總額。以年份為輸入?yún)?shù)，以總工資金額為輸出參數(shù)，產(chǎn)生該年度12個(gè)月份的每部門工資統(tǒng)計(jì)的存儲(chǔ)過(guò)

18、程。定義該存儲(chǔ)過(guò)程名為bmMonhSum，輸入?yún)?shù)為ThisYear，實(shí)現(xiàn)代碼如下：CREATE PROCEDURE bmMonthSumThisYear CHAR(10)ASSELECT DATEPART(MM,工資年月) AS 員工工資月份,SUM(實(shí)發(fā)工資) AS 部門人數(shù)FROM 員工,工資,部門WHERE 員工.工號(hào)=工資.工號(hào) AND 員工.部門名稱=部門.部門名稱 AND DATEPART(YYYY,工資年月)=ThisYearGROUP BY DATEPART(MM,工資年月)GO（五）觸發(fā)器（1）關(guān)鍵操作，在工資表里添加一條新的記錄，實(shí)發(fā)工資會(huì)自動(dòng)更新新的數(shù)據(jù)。定義這個(gè)觸發(fā)器

19、名稱為insert_工資。代碼如下：create trigger insert_工資 on 工資 for insertasdeclare i char(20)declare bb intset i=casewhen bb=0001 then '實(shí)發(fā)工資'when bb=0002 then '實(shí)發(fā)工資'endprint i (2)員工新調(diào)入進(jìn)行員工新調(diào)入操作是在員工信息表中添加一條新的記錄，可以為員工信息表設(shè)計(jì)一個(gè)Insert觸發(fā)器，當(dāng)員工信息表執(zhí)行Insert操作后自動(dòng)更改出部門信息表相應(yīng)記錄的數(shù)據(jù)。定義這個(gè)觸發(fā)器名稱為staff_insert。其代碼如下：CR

20、EATE TRIGGER staff_insert ON 員工FOR INSERTAS IF (SELECT COUNT(*) FROM 部門,inserted,工資 WHERE 部門.部門人數(shù)=inserted.部門人數(shù) AND 工資.工號(hào)=inserted.工號(hào))=0ROLLBACK TRANSACTIONGO（3）員工調(diào)出進(jìn)行員工調(diào)出操作是在員工信息表中刪除該員工的記錄，可以為員工信息表設(shè)計(jì)一個(gè)Delete觸發(fā)器，當(dāng)員工信息表執(zhí)行Delete操作后，工資表、部門表中的記錄也將全部刪除。定義這個(gè)觸發(fā)器名稱為staff_delete。其實(shí)現(xiàn)代碼如下：CREATE TRIGGER staff_

21、delete ON 員工FOR DELETEAS SELECT COUNT(*) FROM 部門,工資,deleted WHERE 部門.部門人數(shù)=deleted.部門人數(shù) AND 工資.工號(hào)=deleted.工號(hào)GO(4)員工內(nèi)部調(diào)動(dòng)為員工表設(shè)計(jì)一個(gè)名為staff_update的UPDATE觸發(fā)器，當(dāng)系統(tǒng)對(duì)員工表進(jìn)行UPDATE操作后，將會(huì)自動(dòng)觸發(fā)它。該觸發(fā)器將相應(yīng)記錄的部門人數(shù)、部門名稱、工資更新。下面是client_update觸發(fā)器的定義代碼；CREATE TRIGGER staff_update ON 員工FOR UPDATEAS SELECT COUNT(*) FROM updat

22、ed,部門 WHERE 部門.部門名稱=updated.部門名稱GO（六）索引為提高檢索性能，為表創(chuàng)建符合索引，其索引項(xiàng)為工號(hào)、XX。CREATE INDEX 工號(hào)_XX_indON 員工(工號(hào),XX)GO（七）運(yùn)行和維護(hù)1.關(guān)系圖的建立使用SQL Server 2000的企業(yè)管理器創(chuàng)建工資管理系統(tǒng)關(guān)系圖工資管理系統(tǒng)的關(guān)系圖2簡(jiǎn)單查詢1) 員工基本信息情況 代碼如下：select * from 員工;2）員工考勤情況；代碼如下： select 出勤天數(shù),工號(hào)from 工資;3）員工每個(gè)工種基本工資的設(shè)定。代碼如下：select 工種,崗位工資,職務(wù)等級(jí)from 工資,員工where 工資.工號(hào)

23、=員工.工號(hào);4)加班津貼管理，根據(jù)加班時(shí)間和類型給予不同的加班津貼；代碼如下：select 加班工時(shí),加班天數(shù),加班類別,加班工資from 工資WHERE 加班工資>05）按照不同工種的基本工資情況、員工的考勤情況產(chǎn)生員工的每月的月工資；代碼如下：select 工種,職務(wù)等級(jí),崗位工資,出勤天數(shù),實(shí)發(fā)工資from 員工,工資WHERE 員工.工號(hào)=工資.工號(hào);6）員工津貼信息情況，反映員工的加班時(shí)間，加班類別、加班天數(shù)、津貼情況等；代碼如下：select 員工.工號(hào),員工.XX,加班工時(shí),加班天數(shù),加班類別,加班工資from 工資,員工WHERE 員工.工號(hào)=工資.工號(hào);3復(fù)雜查詢1）

24、員工年終獎(jiǎng)金的生成，員工的年終獎(jiǎng)金計(jì)算公式（員工本年度的工資總和津貼的總和）/12；代碼如下：Select XX,avg(應(yīng)發(fā)工資+崗位補(bǔ)貼+加班工資)from 工資,員工where 工資.工號(hào)=員工.工號(hào)group by XX2）企業(yè)內(nèi)財(cái)務(wù)部的工資情況查詢：代碼如下：select 員工.工號(hào),XX,職務(wù)等級(jí),實(shí)發(fā)工資 from 工資,員工 where 工資.工號(hào)=員工.工號(hào) AND 部門名稱='財(cái)務(wù)部'3）企業(yè)工資報(bào)表。能夠查詢單個(gè)員工的工資情況代碼如下：select 工號(hào),工資年月,崗位工資,病假扣款,事假扣款,應(yīng)發(fā)工資,實(shí)發(fā)工資from 工資,員工WHERE 工資.工號(hào)=員工.工號(hào) AND XX LIKE 閔%;4)每個(gè)部門的工資情況,按月的工資統(tǒng)計(jì)代碼如下：Select 部門名稱,avg(實(shí)發(fā)工資)from 工資,員工 where 工資.工號(hào)=員工.工號(hào)group by 部門名稱5）員工月工資情況：年，月，職工號(hào)，XX，部門名，基本工資，病假扣款，事假