数据库大作业报告

上海电力学院

数据库应用

课程设计

题目: 电力行业工资管理系统设计

学生姓名：杜志豪

学号： 20121798 班级： 2012053

院系：计算机科学与技术学院

专业年级：计算机科学与技术（电力企业信息化）2012级

2014年07 月 08 日

目录

一、需求分析 (3)

1.1系统功能设计 (3)

1.1.1数据表的创建 (3)

1.1.2数据完整性设计 (4)

1.1.3数据库对象设计 (4)

二、概念结构设计 (5)

三、逻辑结构设计 (6)

四、数据库的物理设计 (8)

五、数据库设计实现及运行 (9)

5.1数据库的创建 (9)

5.2 数据表的创建 (12)

5.3 视图的创建 (13)

5.4存储过程的定义实现 (14)

5.5触发器的定义实现 (15)

5.6自行设计各模块中所涉及的操作语句 (17)

5.6.1 (17)

六、系统详细设计及实现 (21)

七、附录：核心源程序代码 (22)

一、需求分析

电力工资管理系统是电力行业常见的计算机信息管理系统。它的主要任务是对各种工资信息进行日常的管理，如工资信息的输入、查询、修改、增加、删除，迅速准确地完成各种工资信息的统计计算和汇总，快速打印出报表。在建立与实现工资管理系统时，应进行功能划分，能够由数据库管理系统完成的功能应尽可能由数据库管理系统守成，这样才能够充分发挥数据库管理系统高效、完全、可靠、便捷的性能，减少编程人员的工作量。

1.1系统功能设计

（1）信息输入功能

1）输入电力行业员工的基本信息。包括：电力员工编号、姓名、性别、出生年月、参加工作时间、所属部门、职务、职称、政治面貌、婚姻状况等基本信息。

2）输入电力行业员工的工资信息。包括：基本工资、岗位工资、住房补贴、津贴、工会会费、水电费、住房公积金、养老保险、医疗保险、失业保险金、奖惩。

3）输入电力行业员工的部门信息。包括：部门编号、部门名称、部门负责人、部门人数。（2）数据修改删除功能

1）修改和删除员工的基本信息。当单位人员的信息发生变化，如职称的改变，工作部门变动，或调离本单位等，系统应能修改员工的信息或将其从员工信息表中删除。

2）修改和删除员工的工资信息。员工升职加薪、工资普调是企业中常见的事情，这就需要系统能方便对员工工资进行个别及批量的修改；如员工调离本单位，就应当从员工信息表中删除这个员工信息，将其工资信息表中相应的数据删除。

3）修改和删除部门信息。当撤消一个部门时，管理员可以从部门信息表中将其删除。而当一个部门的名称发生改变时，系统中所有该项部门的名称应一致的改变。

（3）查询和统计功能

1）能够查询指定员工的基本信息。

2）能够查询指定某个部门或全部部门的基本信息。

3）查询某个员工的工资信息。

4）统计、汇总指定年份企业各部门或某个部门每个月或全年工资总额，汇总各部门的人数或本单位的总人数。

5）工资表月工资记录的生成功能。生成当月所有员工或某个部门的工资记录，同时能进行员工工资的计算，即计算应发金额、应扣金额及实发金额。

1.1.1数据表的创建

（1）电力行业员工基本信息表

电力行业员工基本信息表包括以下字段：员工编号、姓名、性别、出生年月、参加工作时间、所

属部门、学历、职务、职称、政治面貌、婚姻状况。

（2）电力行业员工工资信息表

电力行业员工工资信息表包括以下字段：员工编号、基本工资、岗位工资、住房补贴、津贴、工会会费、水电费、住房公积金，养老保险、奖惩、应发金额、应扣金额及实发金额使用计算列。（3）部门信息表

电力行业部门信息表包括以下字段：部门编号、部门名称、部门负责人、部门人数。

1.1.2数据完整性设计

为了保证数据库系统的正确性、完备性和一致性，就必须进行数据完整性设计。就本设计而言应考虑实施如下数据完整性：

1）给每个表实施主键及外键约束。

2）设定缺省约束。如员工性别。

3）设置非空约束。如员工姓名。

4）实施CHECK约束，如养老保险的金额大于0。

5）实施规则，如政治面貌必须是“党员”、“团员”及“群众”、“民主党派”四个选项之一。

1.1.3数据库对象设计

为充分发挥数据库的效能，保证数据库的安全性，提高数据库管理系统的执行效率，可以考虑使用视图、存储过程及表的触发器来实现某些功能。本设计可考虑如下数据库对象：

（1）指定员工或某个部门的信息查询。可以设计一个存储过程，以员工编号或部门编号为输入参数返回指定员工或部门的基本信息。

（2）统计指定年份整个企业或各部门每个月份的工资总额。设计一个以年份为输入参数，以总工资金额为输出参数的存储过程，返回每个月份企业工资支出的总额。

（3）浏览工资表。设计一个视图，返回当前月份所有员工或某个部门员工的工资信息。

（4）为提高检索性能，为表创建索引。

（5）为新调入/调出/内部调动人员创建INSERT、DELETE、UPDATE触发器，实现部门人数的自动更新。

需包含内容为：

(1)问题描述：设计系统的简单描述

(2)系统功能描述，对自己所负责的功能模块，可画出所设计模块的操作流程；

并分析所需要存储的数据信息。

(3)有何安全性与完整性方面的要求（参照所给出的语义规则）。如是否需要显示定

义原子事务、需定义具有什么功能的存储过程、需定义具有什么功能的触发器、对不同

的用户有何不同的系统使用权限等。

二、概念结构设计

画出系统整体的E-R模型；并对模型中所出现的实体及属性等信息加以说明。

三、逻辑结构设计

在这个数据库中，三个关系模式是：

Worker(workerno,workername,workersex,birth,startworktime,departno,job,position,status,marry) Department(departno,departname,departhead,departamount)

Wage(wageno,basewage,gangweiwage,housebutie,jintie,gonghuihuifei,shuidianfei,housegjj,yangl aobx,reward,yingfawage,yingkouwage,finalwage)

这三个关系模式组成了工资管理系统的数据库模式，这三个关系组成了工资管理系统关系数据库；关系worker的主键是workerno，关系department的主键是departno，关系wage的主键是wageno；关系worker和关系department有一个共同属性departno，在表worker中，departno是外键，关系worker和关系wage有一个共同属性workerno，在表wage中，workerno 是外键。

各个属性类型与长度，是否可取空值如下图：

其中workersex默认为男且取值为｛’男’或’女’｝，status的取值在{‘群众’,’团员’,’党员’,’民主党派’}，marry默认为未婚且取值在{‘未婚’,’已婚’}。

其中yingfawage,yingkouwage,finalwage为计算列。Yingfawage=basewage+gangweiwage+housebutie+jintie+reward

Yingkouwage=gonghuihuifei+shuidianfei+housegjj+yanglaobx

Finalwage=yingfawage-yingkouwage

四、数据库的物理设计

为了提高在表中搜索元组的速度，在实际实现的时候应该基于某些属性建立索引。给出所建立的索引。（包括按哪些表中哪些属性按升序还是降序来创建索引及相应的T-SQL的创建

语句）。

建立索引：

create unique index wage_PK on wage (wageno ASC)

create unique index worker_PK on worker (workerno ASC)

create unique index department_PK on department (departno ASC)

五、数据库设计实现及运行

5.1数据库的创建

T-SQL语句：

USE[master]

GO

/****** Object: Database [20121798_工资管理系统] Script Date: 2014/7/8 1:46:46 ******/ CREATE DATABASE[20121798_工资管理系统]

CONTAINMENT=NONE

ON PRIMARY

(NAME=N'20121798_工资管理系统',FILENAME=N'C:\Program Files\Microsoft SQL

Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\20121798_工资管理系统.mdf',SIZE= 5120KB, MAXSIZE=UNLIMITED,FILEGROWTH= 1024KB)

LOG ON

(NAME=N'20121798_工资管理系统_log',FILENAME=N'C:\Program Files\Microsoft SQL

Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\20121798_工资管理系统_log.ldf',SIZE= 2048KB, MAXSIZE= 2048GB,FILEGROWTH= 10%)

GO

ALTER DATABASE[20121798_工资管理系统]SET COMPATIBILITY_LEVEL= 110

GO

IF (1 =FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC[20121798_工资管理系统].[dbo].[sp_fulltext_database]@action='enable'

end

GO

ALTER DATABASE[20121798_工资管理系统]SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET ANSI_NULLS OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET ANSI_PADDING OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET ANSI_WARNINGS OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET ARITHABORT OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET AUTO_CLOSE OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE[20121798_工资管理系统]SET AUTO_SHRINK OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE[20121798_工资管理系统]SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE[20121798_工资管理系统]SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET DISABLE_BROKER

GO

ALTER DATABASE[20121798_工资管理系统]SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO

ALTER DATABASE[20121798_工资管理系统]SET DATE_CORRELATION_OPTIMIZATION OFF GO

ALTER DATABASE[20121798_工资管理系统]SET TRUSTWORTHY OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE[20121798_工资管理系统]SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET RECOVERY FULL

GO

ALTER DATABASE[20121798_工资管理系统]SET MULTI_USER

GO

ALTER DATABASE[20121798_工资管理系统]SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE[20121798_工资管理系统]SET DB_CHAINING OFF

GO

ALTER DATABASE[20121798_工资管理系统]SET FILESTREAM(NON_TRANSACTED_ACCESS= OFF)

GO

ALTER DATABASE[20121798_工资管理系统]SET TARGET_RECOVERY_TIME= 0 SECONDS

GO

ALTER DATABASE[20121798_工资管理系统]SET READ_WRITE

GO

5.2 数据表的创建

T-SQL语句：

Worker表的创建：

create table worker (

workerno char(6) not null,

departno char(2) null,

workername char(20) not null,

workersex char(4) not null default '男',

birth datetime null,

startworktime datetime null,

job char(20) null,

position char(20) null,

status char(20) not null,

marry char(8) null default '未婚' )

go

alter table worker

add constraint Rule_1 check (workersex='男'or workersex='女') go

alter table worker

add constraint Rule_2 check (marry='已婚'or marry='未婚') go

department表的创建：

create table department (

departno char(2) not null,

departname varchar(20) null,

departhead varchar(20) null,

departamount int null default 0 )

Go

Wage表达的创建：

create table wage (

wageno char(12) not null,

workerno char(6) null,

basewage money null,

gangweiwage money null,

housebutie money null,

jintie money null,

gonghuihuifei money null,

shuidianfei money null,

housegjj money null,

yanglaobx money null,

reward money null,

yingfawage AS (basewage+gangweiwage+housebutie+jintie+reward),

yingkouwage AS (gonghuihuifei+shuidianfei+housegjj+yanglaobx),

finalwage AS (basewage+gangweiwage+housebutie+jintie+reward-gonghuihuifei-shuidianfei-housegjj-yan glaobx)

)

go

alter table wage

add constraint CKC_YANGLAOBX_WAGE check (yanglaobx is null or (yanglaobx >=

0.1))

go

5.3 视图的创建

T-SQL语句：

create view wageget as

select

worker.workerno,worker.workername,wage.wageno

from

wage,worker

where wage.wageno like worker.workerno+'______'

go

5.4存储过程的定义实现

T-SQL语句：

create procedure department_search

@departno char(8)

as

begin

select* from department

where departno=@departno

end

go

create procedure department_wage

@wageno char(12),@departno char(8), @wagedate char(12) as begin

select department.departno,worker.workerno,wage.wageno from department,worker,wage

where wage.wageno like '____'+@wagedate and

@departno=department.departno and

wage.wageno like worker.workerno+'____' and department.departno=worker.departno

end

go

create procedure worker_search

@workerno char(6) as

begin

select * from worker

where workerno=@workerno

end

go

5.5触发器的定义实现

T-SQL语句：

Worker触发器员工编号前2位必须是部门编号：

create trigger tr1_worker

on worker

for insert

as

declare @workerno char(6),@departno char(2)

select @workerno=workerno,@departno=departno

from inserted

if(@workerno not like @departno+'____')

begin

rollback tran

raiserror('员工编号前2位必须是部门编号',16,10)

End

go

worker insert触发器，部门人数自动更新

create trigger tr2_worker

on worker

for insert

as

declare @x int

select @x=count(worker.departno)

from worker,inserted

where worker.departno=inserted.departno

update department

set departamount=@x

from department,inserted

where department.departno=inserted.departno go

worker delete触发器，部门人数自动更新：

CREATE trigger tr3_worker

on worker

for delete

as

declare @x int

select @x=count(worker.departno)

from worker,deleted

where worker.departno=deleted.departno

update department

set departamount=@x

from department,deleted

where department.departno=deleted.departno go

worker update触发器，部门人数自动更新：CREATE trigger tr4_worker

on worker

for update

as

declare @x int,@y int

select @x=count(worker.departno)

from worker,inserted

where worker.departno=inserted.departno

update department

set departamount=@x

from department,inserted

where department.departno=inserted.departno

select @y=count(worker.departno)

from worker,deleted

where worker.departno=deleted.departno

update department

set departamount=@y

from department,deleted

where department.departno=deleted.departno

go

worker delete触发器，删除员工自动删除该员工工资信息：

create trigger tr5_worker

on worker

for delete

as

declare @x char(6)

select @x=deleted.workerno

from deleted

delete from wage

where wageno like @x+'______'

go

5.6自行设计各模块中所涉及的操作语句

5.6.1功能界面描述

每个功能模块描述方法如下：

功能界面：

功能界面简单描述；员工增、删、改、查：

部门的增、删、改、查：

工资信息的增、删、改、查：

查询部门月工资汇总：

select sum(finalwage) from wage where wageno like'01'+'____'+'201407'

查询员工工资信息：

查询员工某月工资详情：

查询所有员工信息：

select * from worker