工资管理系统设计报告.doc

目录一、项目名称：工资管理系统设计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. 复杂查询.一五4.数据库的用户与权限管理175.数据库的备份.206.数据的导入与导出.21七、总结22八、评语.2322一、项目名称：工资管理系统设计二、实训目的：l 了解数据库系统的理论l 掌握数据库设计的基本方法，熟悉数据库设计的步骤；l 通过设计数据库系统应用课题，进一步熟悉数据库管理系统的操作技术，提高动手能力，提高分析问题和解决问题的能力l 学习基本数据库编程方法l 快速跟踪数据库系统软件的新技术及市场应用动态。三、应掌握的知识点l 数据库基本原理l 关系数据库的设计方法、设计思路l 初步掌握一种关系数据库语言四、应训练的能力点l SQL Server 2000的操作与使用l 数据库的概念设计与逻辑设计l 数据库的建立与管理、数据表的建立与操作l SQL查询语言的使用与编程l 设计和开发一个数据库应用系统的数据库设计部分五、数据库设计内容(一)需求分析本系统要求实现以下主要功能：1.数据录入功能 主要任务是对各种工资信息进行日常的管理，如工资信息的输入、查询、修改、增加、删除，迅速准确地完成各种工资信息的统计计算和汇总，快速打印出报表。2.数据查询功能系统需要提供以下查询功能：1) 员工考勤情况2) 员工工种情况，反映员工的工种、等级，岗位工资等信息3) 员工津贴信息情况，反映员工的加班时间，加班类别、加班天数、岗位补贴情况等4) 员工基本信息情况5) 员工月工资情况：年，月，职工号，姓名，部门名，基本工资，病假扣款，事假扣款，3.应发工资，实发工资3.数据统计功能1) 统计员工年终奖金2) 统计每部门月工资情况（二）数据库概念设计分析前面的系统功能要求，需要一个表来存储和管理员工信息，使系统能够接受员工原始的各项数据，以实现数据录入、查询或统计员工信息等功能。员工是本系统的第一个实体。为了能实现查询和统计部门内员工情况等功能，必须有一个表来存储和管理所有部门的信息。部门是本系统的第二个实体。工资是以员工工作情况来制定的。需要建立一个表来记录各种工资、津贴、扣款的信息。它主要为各种查询和统计功能提供工资数据。工资是第3个实体。据此可以绘出工资管理系统数据库的E-R图如下：部门 1工作 N 获得工资员工 M M 1实体属性列表如下：实体属性员工工号姓名性别年龄籍贯身份证号婚姻状况学历部门名称工种工龄职务等级部门部门名称部门负责人部门人数工资工号工资年月岗位工资岗位补贴出勤天数加班工时加班天数加班类别加班工资病假扣款事假扣款其他扣款应发工资实发工资（三）数据库逻辑设计将数据库的概念模型转换为关系模型：实体转换成的关系模式有：员工（工号，姓名，性别，年龄，籍贯，身份证号，婚姻状况，学历，部门名称，工种，工龄，职务等级）部门（部门编号，部门名称，部门负责人，部门人数）获得（工号，姓名）工资（工号，工资年月，岗位工资，岗位补贴，出勤天数，加班工时，加班工资，加班天数，加班类别，病假扣除，事假扣除，其它扣除，应发工资，实发工资）因为，员工与工资是1：N的关系，所以各关系模式为：员工（工号，姓名，性别，年龄，籍贯，身份证号，婚姻状况，学历，部门名称，工种，工龄，职务等级）部门（部门名称，部门负责人，部门人数）工资（工号，工资年月，岗位工资，岗位补贴，出勤天数，加班工时，加班工资，加班天数，加班类别，病假扣除，事假扣除，其它扣除，应发工资，实发工资）带下划线的属性为各关系模式的外码，字体为粗体的属性为各关系模式的主码。关系规范式 ：工资管理系统1NF 每个属性均不可再分（四）数据库与数据表设计 物理结构设计在SQL数据库中需要建立3个数据表：员工信息数据表、部门信息数据表和工资数据表。1.数据库与数据表设计员工信息数据表定义 其中，“工号”是表的主键，惟一标识。“部门名称”是外键。“工号”将记录员工的编号，在员工信息表中增加这一列时，虽然增加了数据冗余，但可以在查询每个员工信息时，提高系统的性能。因为员工信息表的记录数相对固定，相比之下，增加这个冗余的列对于整个系统来说是有利的。通过它，系统将引用到工资信息。此表取名为“员工” 部门信息数据表定义 其中，部门名称是该表的主键，惟一标识一个部门。此表取名为“部门”工资数据表定义其中，“工号”作为该表的主键，唯一标识，保证客房信息表数据的参照完整性。此表表取名为“工资”根据上面三个数据表的设计，可执行下面的步骤创建数据表 1)打开企业管理器，在本地服务器上创建新的数据库“工资管理库”2)创建表 员工、部门、工资2.数据完整性设计1）给每个表实施主键约束。主键约束、非空值约束员工：工号为主键，所以此项非空部门：部门名称为主键，所以此项非空工资：工号为主键，所以此项非空2）实施CHECK约束l 员工表中建立一个检查约束，即员工性别不是男就是女CHECK(性别 IN('男','女')l 工资表中建立一个对于出勤天数的检查约束，即出勤天数在0-20天之间 check (出勤天数 >=0 and 出勤天数<=20)3）外键约束员工表中，工号、部门名称为外键。FK_员工_工资 FOREIGN KEY(工号) REFERENCES 工资(工号)FK_员工_部门 FOREIGN KEY(部门名称) REFERENCES 部门(名称)通过此键建立3个表的联系4）惟一约束a) 每个表的主键需定义为惟一性：对于工资表，因为工号是主键，联系着员工表，所以建立唯一性 alter table 工资add constraint UQ_工号 unique(工号)b) 对于员工表，员工的工号、身份证号、部门名称都是用于识别员工身份，所以建立唯一约束 alter table 员工 add constraint UQ_工号 unique(工号)5) 强制约束对INSERT和UPDATE操作强制约束，表明以后对员工表中的工号、部门名称的添加和更新操作都会分别检查在工资表、部门表中是否存在与此相应的内容。6）规则1) 为了检查员工表中输入的身份证号码是有效位数，可以创建一个规则绑定到该列；在数据操作时进行检查。规则的定义语句为： (LEN (身份证号)=一五)OR(LEN(身份证号)=一八)六、数据库操作实施与结果数据库对象的设计（一）建数据库工资管理库IF EXISTS(SELECT * FROM sysdatabases WHERE name='工资管理库') drop database 工资管理库GOCREATE DATABASE 工资管理库 ON PRIMARY(NAME=工资管理库,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 员工(工号 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,职务等级 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(50) 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 员工_工资 AS SELECT 员工.姓名, 工资.* FROM 员工,工资 WHERE 员工.工号=工资.工号（四）存储过程(1)指定员工工资情况的查询定义一个名为stsffCost的存储过程，该存储过程以以工资年月(staffgongzi)、出勤天数(staffchuqin)、加班工资(staffjiaban)、应发工资(staffying)、实发工资(staffshi)为输入参数返回指定员工工资信息的存储过程。stsffCost代码如下：CREATE PROCEDURE staffCostxstaffgongzi decimal,xstaffchuqin decimal,xstaffjiaban decimal,xstaffying decimal,xstaffshi decimalASSELECT *FROM 工资WHERE 工资年月=xstaffgongzi AND 出勤天数=xstaffchuqin AND 加班工资=xstaffjiaban AND应发工资=xstaffying AND实发工资=xstaffshiGO(2）指定范围的员工的信息查询。以员工工号、部门编号为输入参数返回指定员工基本信息的存储过程，要求输入部分员工信息查询员工全部信息。定义一个名为stsffInfo的存储过程，该存储过程以员工工号(staffNum)、部门编号(bmNum)作为输入参数，存储过程返回查询到的结果集。satffInfo的定义代码如下：CREATE PROCEDURE staffInfoxstaffNum VARCHAR(50)='%',xbmNum VARCHAR(50)='%'ASSELECT *FROM 员工,部门WHERE 员工.部门名称=部门.部门名称 AND (员工.工号 LIKE xstaffNum VARCHAR(50)+'%') AND (部门编号 LIKE xbmNum VARCHAR(50)+'%')GO （3）统计指定年份各部门每个月份的工资总额。以年份为输入参数，以总工资金额为输出参数，产生该年度12个月份的每部门工资统计的存储过程。定义该存储过程名为bmMonhSum，输入参数为ThisYear，实现代码如下：CREATE PROCEDURE bmMonthSumxThisYear CHAR(10)ASSELECT DATEPART(MM,工资年月) AS 员工工资月份,SUM(实发工资) AS 部门人数FROM 员工,工资,部门WHERE 员工.工号=工资.工号 AND 员工.部门名称=部门.部门名称 AND DATEPART(YYYY,工资年月)=xThisYearGROUP BY DATEPART(MM,工资年月)GO（五）触发器（1）关键操作，在工资表里添加一条新的记录，实发工资会自动更新新的数据。定义这个触发器名称为insert_工资。代码如下：create trigger insert_工资 on 工资 for insertasdeclare xi char(20)declare xbb intset xi=casewhen xbb=0001 then '实发工资'when xbb=0002 then '实发工资'endprint xi (2)员工新调入进行员工新调入操作是在员工信息表中添加一条新的记录，可以为员工信息表设计一个Insert触发器，当员工信息表执行Insert操作后自动更改出部门信息表相应记录的数据。定义这个触发器名称为staff_insert。其代码如下：CREATE TRIGGER staff_insert ON 员工FOR INSERTAS IF (SELECT COUNT(*) FROM 部门,inserted,工资 WHERE 部门.部门人数=inserted.部门人数 AND 工资.工号=inserted.工号)=0ROLLBACK TRANSACTIONGO（3）员工调出进行员工调出操作是在员工信息表中删除该员工的记录，可以为员工信息表设计一个Delete触发器，当员工信息表执行Delete操作后，工资表、部门表中的记录也将全部删除。定义这个触发器名称为staff_delete。其实现代码如下：CREATE TRIGGER 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(*) FROM updated,部门 WHERE 部门.部门名称=updated.部门名称GO（六）索引为提高检索性能，为表创建符合索引，其索引项为工号、姓名。CREATE INDEX 工号_姓名_indON 员工(工号,姓名)GO（七）运行和维护1. 关系图的建立 使用SQL Server 2000的企业管理器创建工资管理系统关系图工资管理系统的关系图 2简单查询1) 员工基本信息情况 代码如下：select * from 员工;2）员工考勤情况；代码如下： select 出勤天数,工号from 工资; 3）员工每个工种基本工资的设定。代码如下：select 工种,岗位工资,职务等级from 工资,员工where 工资.工号=员工.工号;4)加班津贴管理，根据加班时间和类型给予不同的加班津贴；代码如下：select 加班工时,加班天数,加班类别,加班工资from 工资WHERE 加班工资>05）按照不同工种的基本工资情况、员工的考勤情况产生员工的每月的月工资；代码如下：select 工种,职务等级,岗位工资,出勤天数,实发工资from 员工,工资WHERE 员工.工号=工资.工号;6）员工津贴信息情况，反映员工的加班时间，加班类别、加班天数、津贴情况等；代码如下：select 员工.工号,员工.姓名,加班工时,加班天数,加班类别,加班工资from 工资,员工WHERE 员工.工号=工资.工号;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）员工月工资情况：年，月，职工号，姓名，部门名，基本工资，病假扣款，事假扣款，应发工资，实发工资。代码如下：select 工资年月,员工.工号, 姓名,部门名称,岗位工资,病假扣款,事假扣款,应发工资,实发工资from 工资,员工where 工资.工号=员工.工号; 4数据库的用户与权限管理（1）数据库的用户管理工资管理库的用户：Dbo用户属性：数据库登陆文件夹：Sa登陆属性建立新用户:用户名stu,密码1234（登录名login1） CREATE LOGIN login1 WITH PASSWORD = '1234'USE Master;CREATE USER stu FRO login1;GO （2）用户的权限管理a) 系统权限管理ll对象权限管理设置权限将员工表的录入、查询权限授予给了用户人事部部长GRANT INSERT,SELECT ON 员工TO publicWITH GRANT OPTION5数据库的备份对工资管理库进行备份SQL sever 代理启动单击备份右键，进行数据库备份6.数据的导入与导出(1)数据的导出服务 进入Excel查看 (2)数据的导入服务七、总结经过一周的时间，在同学和老师的帮助下，基本完成本次课程设计，基本达到了工资管理系统的要求。这次数据库课程设计给我的最大的印象是：流程是一切的根本；架构是重要的，想像的还要重要；实践比一切空谈和理论更能学到东西。从做这个数据库开始无论遇到什么困难，我都没有一丝的放弃的念头。在设计过程中一定要慎重，仔细。来不得半点马虎。需求分析是整个课程设计的中心，考虑要全面，分析彻底。才会为后来的设计打下良好的基础。否则，前功尽弃，甚至不能完成任务。做实例过程中要注意与说明书中数据项数据类型，数据长度等的一致，命令中心输入任何命令都要谨记一条原则，那就是严格按照格式来，不能像平时在纸上作业。最后，再次感谢老师的耐心指导和同学的帮助，没有你们的无私教导和帮助，就没有今天的工资管理系统的实现，衷心感谢你们！