數據庫課程設計報告
題目：第9題
學校的工資管理系統
l
實現部門、職務、職稱等基本信息的管理；
l
實現教職工信息的管理；
l
實現工資項目的管理，工資項目設有啟用標志和加扣標志；
l
實現教職工工資項目及其工資的管理；
l
創建觸發器當往教職工工資項目表中插入記錄或刪除記錄時，自動修改該職工的應發工資數和實發工資數；
l
創建存儲過程統計某個月各種工資項目的發放總和；
l
創建視圖查詢各個員工的應發、應扣和實發工資；
l
建立數據庫相關表之間的參照完整性約束。
一、關系模式設計
根據題意，為實現所要求的所有功能,此處共設計5個關系，具體介紹和表的形式如下所示：
①職工(職工編號，姓名，性別，年齡，部門，職稱)
Tno
Tname
Tsex
Tage
Department
Title
②職位變更(職工編號，前職稱，現職稱，變更日期)
Tno
Title1
Title2
Dates
③考勤(職工編號，加班次數，缺勤次數，考勤日期)
Tno
Overtime
Absence
Dates
④基本工資表(職稱，基本工資)
Title
Base
⑤工資表(職工編號，基本工資，加班工資，缺勤扣錢，實發工資)
Tno
Base
Overpay
Docked
Wages
二、全局E-R圖參照1
職稱
基本工資
基本工資表
參照2
基本工資
得到
職工編號
工資表
職稱
年齡
部門
加班工資
缺勤扣錢
實發工資
加班次數
缺勤次數
考勤日期
職工編號
接收考勤
考勤
變更日期
現職稱
前職稱
職工編號
職位變更
職位變更記錄
性別
姓名
職工編號
職工
三、物理設計
1、建立數據庫，學校的工資管理系統
CREATE
DATABASE
SchoolSalary2、建立職工信息表
CREATE
TABLE
Teacher
（Tno
CHAR(20)
PRIMARY
KEY,/*職工號*/
Tname
CHAR(20)
UNIQUE,/*職工名*/
Tsex
CHAR(10)
NOT
NULL
CHECK(Tsex
in(＇男＇,＇女＇)),/*性別*/
Tage
INT
NOT
NULL,/*年齡*/
Depart
CHAR(20),/*所屬部門*/
Title
CHAR(20))
3、建立職稱變更記錄表
CREATE
TABLE
Change
（Tno
CHAR(20),/*職工號*/
Title1
CHAR(20),/*之前的職稱*/
Title2
CHAR(20),/*現職稱*/
Dates
INT,/*職位變更的月份*/
FOREIGN
KEY
(Tno)
REFERENCES
Teacher(Tno))
4、建立考勤表
CREATE
TABLE
Attendance
（Tno
CHAR(20),/*職工號*/
Overtime
INT,/*加班次數*/
Absence
INT,/*缺勤次數*/
Dates
INT,/*月份*/
FOREIGN
KEY
(Tno)
REFERENCES
Teacher(Tno)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE)
5、建立基本工資表
CREATE
TABLE
Refer
（/*工資參照表*/
Title
CHAR(20)
PRIMARY
KEY,/*職位*/
Salary
FLOAT,/*對應金額*/)
6、建立工資表
CREATE
TABLE
Pay
（Tno
CHAR(20),/*職工號*/
Base
FLOAT,/*基本工資*/
Overpay
FLOAT,/*加班費*/
Docked
FLOAT,/*缺勤扣除工資*/
Wages
FLOAT,/*實際應得工資*/
FOREIGN
KEY(Tno)
REFERENCES
Teacher(Tno))
三、數據庫完整性設計
1、各表名及其對應主鍵
職工(Teacher)
職工編號(Tno)
職稱變更記錄(Change)
職工編號(Tno)
考勤(Attendance)
職工編號(Tno)
基本工資表(Refer)
職稱(Title)
工資表(Pay)
職工編號(Tno)
2、參照完整性設計
2.1、職稱變更記錄(Change)的職工編號(Tno)設為外鍵
FOREIGN
KEY
(Tno)
REFERENCES
Teacher(Tno)
2.2、考勤(Attendance)的職工編號(Tno)設為外鍵
FOREIGN
KEY
(Tno)
REFERENCES
Teacher(Tno)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE
實現在更新和刪除時級聯操作
2.3、工資表(Pay)的職工編號(Tno)設為外鍵
FOREIGN
KEY
(Tno)
REFERENCES
Teacher(Tno)
3、CHECK約束設計
職工(Teacher)中對“性別”進行CHECK約束：
CHECK(Tsex
in(＇男＇,＇女＇))
要求性別必須為“男”或“女”
4、觸發器設計
4.1、在職工表中建立職稱變更觸發器，當某職工的職稱發生變化時，在職稱變更記錄表中自動插入一個記錄，記錄變更前后的職稱名以及變更日期
CREATE
TRIGGER
Title_change
ON
Teacher
FOR
UPDATE
AS
BEGIN
DECLARE
@Tno
CHAR(20)
SELECT
@Tno=inserted.Tno
FROM
inserted
SELECT
*
FROM
Teacher
WHERE
@Tno=Teacher.Tno
DECLARE
@Title1
CHAR(20)
SELECT
@Title1=deleted.Title
FROM
deleted
DECLARE
@Title2
CHAR(20)
SELECT
@Title2=Teacher.Title
FROM
Teacher
WHERE
@Tno=Teacher.Tno
IF
@Title1!=@Title2
BEGIN
INSERT
INTO
Change(Tno,Title1,Title2)
VALUES(@Tno,@Title1,@Title2)
END
END
GO
4.2、在職工表中建立基本工資觸發器，當插入一個新的職工記錄時，根據其職稱并參照基本工資表，在工資表中自動更新其基本工資，且默認加班工資和缺勤扣錢均為0
CREATE
TRIGGER
Basic_pay
ON
Teacher
FOR
insert
AS
BEGIN
DECLARE
@Base
FLOAT,@tno
CHAR(20)
SELECT
@tno=inserted.Tno
FROM
inserted
SELECT
@Base=Refer.Salary
FROM
Refer,inserted
WHERE
Refer.Title=inserted.Title
INSERT
INTO
Pay(Tno,Base,Overpay,Docked,Wages)
VALUES(@tno,@Base,0,0,@Base)
END
GO
4.3、在考勤表中建立考勤工資觸發器，當給一個職工插入考勤信息后，自動在工資表中更新其工資信息，算法里設計加班一次加200塊，缺勤一次扣100塊
CREATE
TRIGGER
A_pay
ON
Attendance
FOR
INSERT
AS
BEGIN
DECLARE
@tno
CHAR(20)
DECLARE
@a
FLOAT
DECLARE
@b
FLOAT
DECLARE
@c
INT
DECLARE
@d
INT
DECLARE
@e
FLOAT
SELECT
@tno=inserted.Tno
FROM
inserted
SELECT
@a=Overpay
FROM
Pay
WHERE
Pay.Tno=@tno
SELECT
@b=Docked
FROM
Pay
WHERE
Pay.Tno=@tno
SELECT
@c=Overtime
FROM
Attendance
WHERE
Attendance.Tno=@tno
SELECT
@d=Absence
FROM
Attendance
WHERE
Attendance.Tno=@tno
SELECT
@e=Wages
FROM
Pay
WHERE
Pay.Tno=@tno
UPDATE
Pay
SET
Pay.Overpay=@a+200*@c
WHERE
Pay.Tno=@tno
UPDATE
Pay
SET
Pay.Docked=@b+100*@d
WHERE
Pay.Tno=@tno
UPDATE
Pay
SET
Pay.Wages=@e+@a+200*@c-(@b+100*@d)
WHERE
Pay.Tno=@tno
END
4.4、在職稱變更記錄表中建立基本工資變更觸發器，當某職工職稱變更且記錄在職稱變更記錄表插入記錄后，在工資表中自動更新其所有工資信息
CREATE
TRIGGER
Change_pay
ON
Change
FOR
UPDATE,INSERT
AS
BEGIN
DECLARE
@A
CHAR(20)
DECLARE
@B
CHAR(20)
DECLARE
@C
FLOAT
DECLARE
@D
FLOAT
SELECT
@A=inserted.Title2
FROM
inserted
SELECT
@B=inserted.Tno
FROM
inserted
SELECT
@C=Pay.Overpay
FROM
Pay,inserted
WHERE
Pay.Tno=@B
SELECT
@D=Pay.Docked
FROM
Pay,inserted
WHERE
Pay.Tno=@B
IF
@A=＇普通教師＇
UPDATE
Pay
SET
Pay.Base=4000,Pay.Wages=4000+@C-@D
WHERE
@B=Pay.Tno
IF
@A=＇高級教師＇
UPDATE
Pay
SET
Pay.Base=5000,Pay.Wages=5000+@C-@D
WHERE
@B=Pay.Tno
IF
@A=＇主任＇
UPDATE
Pay
SET
Pay.Base=6000,Pay.Wages=6000+@C-@D
WHERE
@B=Pay.Tno
IF
@A=＇校長＇
UPDATE
Pay
SET
Pay.Base=7000,Pay.Wages=7000+@C-@D
WHERE
@B=Pay.Tno
END
GO
五、數據庫視圖設計
查看各職工應得和實發的工資：
CREATE
VIEW
Pay_view
AS
SELECT
Tno,Base+Overpay
Gets,Docked,Wages
FROM
Pay
六、存儲過程設計
CREATE
PROCEDURE
ALL_pay
AS
SELECT
SUM(Base)
總基本工資,SUM(Overpay)
總加班工資,SUM(Docked)
總扣工資,SUM(Wages)
總實發工資
FROM
Pay
七、實驗結果
(1)基本工資表
各職工的基本工資按各自的職稱參照此表
(2)職工表，以下為添加記錄后的結果
添加后，Basic_pay觸發器觸發，在工資表中自動插入所有人的基本工資信息：
(3)現將李楠、王峰的職稱都提一級，吳鵬降一級，職稱更新后，Title_change觸發器觸發，首先職稱變更記錄表中插入相應記錄，：
然后工資表自動更新，以上三人的基本工資改變：
(4)在考勤表中添加記錄
隨后A_pay觸發器觸發，在工資表中按照加班一次加200、缺勤一次扣100自動更新職工的工資信息：
(5)視圖結果
(6)存儲過程結果
八、實驗心得
本次的數據庫大題目課程設計，所運用到的知識比較豐富，要考慮到的點也比較多，故此需要一定的思考。在整個設計過程中，不僅回顧了以往的理論知識，更重要的是鍛煉了對SQL語言的編寫能力。題目所給的要求并不是特別復雜，在設計數據庫的參照完整性約束上是比較簡單的，主要的在于觸發器的設計和功能的實現，這也正是此次實驗的精髓所在，讓我了解到數據庫的實用性和各方面的可行性，希望以后也能在數據庫的實踐上更進一步。
點擊咨詢