大数据库课程设计题目第9题

数据库课程设计报告

题目：第9题

学校的工资管理系统

●实现部门、职务、职称等基本信息的管理；

●实现教职工信息的管理；

●实现工资项目的管理，工资项目设有启用标志和加扣标志；

●实现教职工工资项目及其工资的管理；

●创建触发器当往教职工工资项目表中插入记录或删除记录时，自动修改

该职工的应发工资数和实发工资数；

●创建存储过程统计某个月各种工资项目的发放总和；

●创建视图查询各个员工的应发、应扣和实发工资；

●建立数据库相关表之间的参照完整性约束。

一、关系模式设计

根据题意，为实现所要求的所有功能,此处共设计5个关系，具体介绍和表的形式如下所示：

①职工(职工编号，姓名，性别，年龄，部门，职称)

②职位变更(职工编号，前职称，现职称，变更日期)

③考勤(职工编号，加班次数，缺勤次数，考勤日期)

④基本工资表(职称，基本工资)

⑤工资表(职工编号，基本工资，加班工资，缺勤扣钱，实发工资)

二、 全局E-R 图

职工

姓名

年龄

职位变更记录

现职称

考勤

加班次数 工资表

缺勤扣钱

基本工资表

基本工资

职称

三、物理设计

1、建立数据库，学校的工资管理系统

CREATE DATABASE SchoolSalary

2、建立职工信息表

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、各表名及其对应主键

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