1、工資管理系統設計規劃目錄一、項目名稱：工資管理系統設計2二、實訓目的：2三、應掌握的知識點2四、應訓練的能力點2五、數據庫設計內容3 (一)需求分析.3 （二）數據庫概念設計.3 （三）數據庫邏輯設計5 （四）數據庫與數據表設計 物理結構設計5 1.數據庫與數據表設計.5 2.數據完整性設計.6六、數據庫操作實施與結果數據庫對象的設計7 (一)建數據庫工資管理庫.7 （二）建表員工信息表、部門信息表、工資信息表.7 （三）視圖.9（四）存儲過程.9（五）觸發器 10（六）索引11（七）運行和維護12 1. 關系圖的建立.12 2. 簡單查詢.123. 復雜查詢.154.數據庫的用戶與權限管理1

2、75.數據庫的備份.206.數據的導入與導出.21七、總結22八、評語.23一、項目名稱：工資管理系統設計二、實訓目的：l 了解數據庫系統的理論l 掌握數據庫設計的基本方法，熟悉數據庫設計的步驟；l 通過設計數據庫系統應用課題，進一步熟悉數據庫管理系統的操作技術，提高動手能力，提高分析問題和解決問題的能力l 學習基本數據庫編程方法l 快速跟蹤數據庫系統軟件的新技術及市場應用動態。三、應掌握的知識點l 數據庫基本原理l 關系數據庫的設計方法、設計思路l 初步掌握一種關系數據庫語言四、應訓練的能力點l SQL Server 2000的操作與使用l 數據庫的概念設計與邏輯設計l 數據庫的建立與管理、

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

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

5、M M 1實體屬性列表如下：實體屬性員工工號姓名性別年齡籍貫身份證號婚姻狀況學歷部門名稱工種工齡職務等級部門部門名稱部門負責人部門人數工資工號工資年月崗位工資崗位補貼出勤天數加班工時加班天數加班類別加班工資病假扣款事假扣款其他扣款應發工資實發工資（三）數據庫邏輯設計將數據庫的概念模型轉換為關系模型：實體轉換成的關系模式有：員工（工號，姓名，性別，年齡，籍貫，身份證號，婚姻狀況，學歷，部門名稱，工種，工齡，職務等級）部門（部門編號，部門名稱，部門負責人，部門人數）獲得（工號，姓名）工資（工號，工資年月，崗位工資，崗位補貼，出勤天數，加班工時，加班工資，加班天數，加班類別，病假扣除，事假扣除，其它

6、扣除，應發工資，實發工資）因為，員工與工資是1：N的關系，所以各關系模式為：員工（工號，姓名，性別，年齡，籍貫，身份證號，婚姻狀況，學歷，部門名稱，工種，工齡，職務等級）部門（部門名稱，部門負責人，部門人數）工資（工號，工資年月，崗位工資，崗位補貼，出勤天數，加班工時，加班工資，加班天數，加班類別，病假扣除，事假扣除，其它扣除，應發工資，實發工資）帶下劃線的屬性為各關系模式的外碼，字體為粗體的屬性為各關系模式的主碼。關系規范式 ：工資管理系統1NF 每個屬性均不可再分（四）數據庫與數據表設計 物理結構設計在SQL數據庫中需要建立3個數據表：員工信息數據表、部門信息數據表和工資數據表。1.數據庫

7、與數據表設計員工信息數據表定義 其中，“工號”是表的主鍵，惟一標識?！安块T名稱”是外鍵。“工號”將記錄員工的編號，在員工信息表中增加這一列時，雖然增加了數據冗余，但可以在查詢每個員工信息時，提高系統的性能。因為員工信息表的記錄數相對固定，相比之下，增加這個冗余的列對于整個系統來說是有利的。通過它，系統將引用到工資信息。此表取名為“員工” 部門信息數據表定義 其中，部門名稱是該表的主鍵，惟一標識一個部門。此表取名為“部門”工資數據表定義其中，“工號”作為該表的主鍵，唯一標識，保證客房信息表數據的參照完整性。此表表取名為“工資”根據上面三個數據表的設計，可執行下面的步驟創建數據表 1)打開企業管理

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

9、EY(工號) REFERENCES 工資(工號)FK_員工_部門 FOREIGN KEY(部門名稱) REFERENCES 部門(名稱)通過此鍵建立3個表的聯系4）惟一約束a) 每個表的主鍵需定義為惟一性：對于工資表，因為工號是主鍵，聯系著員工表，所以建立唯一性 alter table 工資add constraint UQ_工號 unique(工號)b) 對于員工表，員工的工號、身份證號、部門名稱都是用于識別員工身份，所以建立唯一約束 alter table 員工 add constraint UQ_工號 unique(工號)5) 強制約束對INSERT和UPDATE操作強制約束，表明以后對

10、員工表中的工號、部門名稱的添加和更新操作都會分別檢查在工資表、部門表中是否存在與此相應的內容。6）規則1) 為了檢查員工表中輸入的身份證號碼是有效位數，可以創建一個規則綁定到該列；在數據操作時進行檢查。規則的定義語句為： (LEN (身份證號)=15)OR(LEN(身份證號)=18)六、數據庫操作實施與結果數據庫對象的設計（一）建數據庫工資管理庫IF EXISTS(SELECT * FROM sysdatabases WHERE name='工資管理庫') drop database 工資管理庫GOCREATE DATABASE 工資管理庫 ON PRIMARY(NAME=工資

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

12、VARCHAR(50) NOT NULL primary key,姓名 VARCHAR(50) NOT NULL,性別 CHAR(2) DEFAULT'男'CHECK(性別 IN('男','女'),年齡 CHAR(10) NOT NULL,籍貫 VARCHAR(50) NOT NULL,身份證號 VARCHAR(50) UNIQUE NOT NULL,婚姻情況 VARCHAR(50),學歷 VARCHAR(50),部門名稱 VARCHAR(50) NOT NULL,工種 VARCHAR(50),工齡 VARCHAR(50) NOT NULL,職務

13、等級 VARCHAR(50)GOl IF EXISTS(SELECT * FROM sysobjects WHERE name='部門') DROP TABLE 部門 GOcreate table 部門(部門名稱 VARCHAR(50) primary key NOT NULL,部門負責人 VARCHAR(50) NOT NULL, 部門人數 VARCHAR(50)GOl IF EXISTS(SELECT * FROM sysobjects WHERE name='工資') DROP TABLE 工資 GOCREATE TABLE 工資(工號 varchar(5

14、0) not null primary key,工資年月 varchar(50) not null ,崗位工資 decimal not null,崗位補貼 decimal,出勤天數 decimal not null,加班工時 decimal, 加班工資 decimal, 加班天數 decimal,加班類別 varchar(50),病假扣款 decimal,事假扣款 decimal, 其他扣款 decimal, 應發工資 decimal not null, 實發工資 decimal not null)GO（三）視圖(1)查詢員工工資情況建立一個視圖，反映員工姓名及工資情況。CREATE VIEW

15、 員工_工資 AS SELECT 員工.姓名, 工資.* FROM 員工,工資 WHERE 員工.工號=工資.工號（四）存儲過程(1)指定員工工資情況的查詢定義一個名為stsffCost的存儲過程，該存儲過程以以工資年月(staffgongzi)、出勤天數(staffchuqin)、加班工資(staffjiaban)、應發工資(staffying)、實發工資(staffshi)為輸入參數返回指定員工工資信息的存儲過程。stsffCost代碼如下：CREATE PROCEDURE staffCoststaffgongzi decimal,staffchuqin decimal,staffjiab

16、an decimal,staffying decimal,staffshi decimalASSELECT *FROM 工資WHERE 工資年月=staffgongzi AND 出勤天數=staffchuqin AND 加班工資=staffjiaban AND應發工資=staffying AND實發工資=staffshiGO(2）指定范圍的員工的信息查詢。以員工工號、部門編號為輸入參數返回指定員工基本信息的存儲過程，要求輸入部分員工信息查詢員工全部信息。定義一個名為stsffInfo的存儲過程，該存儲過程以員工工號(staffNum)、部門編號(bmNum)作為輸入參數，存儲過程返回查詢到的結

17、果集。satffInfo的定義代碼如下：CREATE PROCEDURE staffInfostaffNum VARCHAR(50)='%',bmNum VARCHAR(50)='%'ASSELECT *FROM 員工,部門WHERE 員工.部門名稱=部門.部門名稱 AND (員工.工號 LIKE staffNum VARCHAR(50)+'%') AND (部門編號 LIKE bmNum VARCHAR(50)+'%')GO （3）統計指定年份各部門每個月份的工資總額。以年份為輸入參數，以總工資金額為輸出參數，產生該年度12個月

18、份的每部門工資統計的存儲過程。定義該存儲過程名為bmMonhSum，輸入參數為ThisYear，實現代碼如下：CREATE PROCEDURE bmMonthSumThisYear CHAR(10)ASSELECT DATEPART(MM,工資年月) AS 員工工資月份,SUM(實發工資) AS 部門人數FROM 員工,工資,部門WHERE 員工.工號=工資.工號 AND 員工.部門名稱=部門.部門名稱 AND DATEPART(YYYY,工資年月)=ThisYearGROUP BY DATEPART(MM,工資年月)GO（五）觸發器（1）關鍵操作，在工資表里添加一條新的記錄，實發工資會自動更

19、新新的數據。定義這個觸發器名稱為insert_工資。代碼如下：create trigger insert_工資 on 工資 for insertasdeclare i char(20)declare bb intset i=casewhen bb=0001 then '實發工資'when bb=0002 then '實發工資'endprint i (2)員工新調入進行員工新調入操作是在員工信息表中添加一條新的記錄，可以為員工信息表設計一個Insert觸發器，當員工信息表執行Insert操作后自動更改出部門信息表相應記錄的數據。定義這個觸發器名稱為staff_in

20、sert。其代碼如下：CREATE TRIGGER staff_insert ON 員工FOR INSERTAS IF (SELECT COUNT(*) FROM 部門,inserted,工資 WHERE 部門.部門人數=inserted.部門人數 AND 工資.工號=inserted.工號)=0ROLLBACK TRANSACTIONGO（3）員工調出進行員工調出操作是在員工信息表中刪除該員工的記錄，可以為員工信息表設計一個Delete觸發器，當員工信息表執行Delete操作后，工資表、部門表中的記錄也將全部刪除。定義這個觸發器名稱為staff_delete。其實現代碼如下：CREATE T

21、RIGGER staff_delete ON 員工FOR DELETEAS SELECT COUNT(*) FROM 部門,工資,deleted WHERE 部門.部門人數=deleted.部門人數 AND 工資.工號=deleted.工號GO(4)員工內部調動為員工表設計一個名為staff_update的UPDATE觸發器，當系統對員工表進行UPDATE操作后，將會自動觸發它。該觸發器將相應記錄的部門人數、部門名稱、工資更新。下面是client_update觸發器的定義代碼；CREATE TRIGGER staff_update ON 員工FOR UPDATEAS SELECT COUNT(

22、*) FROM updated,部門 WHERE 部門.部門名稱=updated.部門名稱GO（六）索引為提高檢索性能，為表創建符合索引，其索引項為工號、姓名。CREATE INDEX 工號_姓名_indON 員工(工號,姓名)GO（七）運行和維護1. 關系圖的建立 使用SQL Server 2000的企業管理器創建工資管理系統關系圖工資管理系統的關系圖 2簡單查詢1) 員工基本信息情況 代碼如下：select * from 員工;2）員工考勤情況；代碼如下： select 出勤天數,工號from 工資; 3）員工每個工種基本工資的設定。代碼如下：select 工種,崗位工資,職務等級from

23、 工資,員工where 工資.工號=員工.工號;4)加班津貼管理，根據加班時間和類型給予不同的加班津貼；代碼如下：select 加班工時,加班天數,加班類別,加班工資from 工資WHERE 加班工資>05）按照不同工種的基本工資情況、員工的考勤情況產生員工的每月的月工資；代碼如下：select 工種,職務等級,崗位工資,出勤天數,實發工資from 員工,工資WHERE 員工.工號=工資.工號;6）員工津貼信息情況，反映員工的加班時間，加班類別、加班天數、津貼情況等；代碼如下：select 員工.工號,員工.姓名,加班工時,加班天數,加班類別,加班工資from 工資,員工WHERE 員工

24、.工號=工資.工號;3復雜查詢1）員工年終獎金的生成，員工的年終獎金計算公式（員工本年度的工資總和津貼的總和）/12；代碼如下：Select 姓名,avg(應發工資+崗位補貼+加班工資)from 工資,員工where 工資.工號=員工.工號group by 姓名2）企業內財務部的工資情況查詢：代碼如下：select 員工.工號,姓名,職務等級,實發工資 from 工資,員工 where 工資.工號=員工.工號 AND 部門名稱='財務部'3）企業工資報表。能夠查詢單個員工的工資情況代碼如下：select 工號,工資年月,崗位工資,病假扣款,事假扣款,應發工資,實發工資from 工資,員工WHERE 工資.工號=員工.工號 AND 姓名 LIKE 閔%;4)每個部門的工資情況,按月的工資統計代碼如下：Select 部門名稱,avg(實發工資)from 工資,員工 where 工資.工號=員工.工號group by 部門名稱5）員工月工資情況：年，月，職工號，姓名，部門名，基本工資，病假扣款，事假