数据库人事管理系统课程设计报告

课程设计任务书设计题目：人事管理系统设计摘要在现代化的社会中，无论是大型企业还是中小型企业，都必不可少地会涉及到人事管理的问题。

一个完善的人事管理系统对企业的发展有着至关重要的作用。

人事管理系统分为6个模块,基本信息管理模块、退休员工信息管理模块、部门信息管理模块、学历信息管理模块、家庭信息管理模块和职称信息管理模。

本系统采用SQL Server 2008作为数据库。

在实现数据库部分功能时要求写出存储过程、触发器和视图。

关键词：人事管理SQL Server 2008 触发器存储过程视图事务处理游标目录摘要............................................................... I I 1 需求分析. (1)1.1 系统目标设计 (1)1.2 系统功能分析 (1)1.3 功能模块分析 (2)1.4 系统全局数据流图 (2)1.5 数据字典 (3)2 概念结构设计 (6)2.1 构成系统的实体型 (6)2.2 人事管理系统E-R图 (7)3 逻辑结构设计 (8)3.1逻辑结构简介 (8)3.2 E-R图向关系模型的转换 (8)4物理模型设计 (9)4.1定义数据库 (9)4.2 创建存储过程 (9)4.3 创建触发器 (14)4.4 创建视图 (17)4.5 创建游标 (19)4.6 创建事物处理 (21)总结 (22)致谢 (23)参考文献 (24)1 需求分析1.1 系统目标设计使企业可以实现规范化的管理。

推动企业劳动人事管理走向科学化、现代化，适应现代企业制度的要求。

使管理员可以很简单地完成日常的管理工作，例如新员工的加入、老员工的退休等。

支持企业进行劳动人事管理及其相关方面的科学决策，如企业领导根据现有职工的数目决定招聘的人数等。

1.2 系统功能分析根据现代企业的情况，本系统要求应能完成以下主要功能：设计友好的登录界面，完成用户身份检验。

可以提供对各种窗口的外观进行设计，可以提供用户进行外观选择。

企业新员工各种信息的输入、修改、删除等，包括员工基本信息、学历信息、婚姻状况、职称等。

数据库信息维护，即员工各种数据信息的备份。

对于转出、辞职、退休员工信息的删除。

按照一定的条件，查询、统计符合条件的员工信息；至少应该包括每个员工详细信息的查询、按婚姻状况查询、按学历查询、按工作岗位查询等，至少应该包括按学历、婚姻状况、岗位、参加工作时间等统计各自的员工信息；对查询、统计的结果打印输出。

按照部门将现有员工进行分类，查看各个部门现有员工的平均年龄，根据各部门工作量的大小，较好地进行人事调度。

另外可以帮助领导作出招聘新员工人数的决定。

可供其他人使用帮助。

1.3 功能模块分析根据系统功能的基本要求，可对整个系统划分为几个模块图1.1 功能模块分析1.4 系统全局数据流图系统的全局数据流图，也称第一层数据流图，是从整体上描述系统的数据流，反映系统数据的整体流向，给设计者、开发者和用户一个总体描述。

图1.2 数据流图1.5 数据字典表1.1 员工基本信息表(worker_info)表1.2离退休员工(tui_info)表1.3部门基本信息表（Department_info）表1.4 学历信息(degree_info)表1.5 家庭信息(home_info)表1.6职称信息(Pro_info)2 概念结构设计2.1 构成系统的实体型由人事管理系统的数据流图和数据字典，抽取出系统的6个主要实体，包括：员工、部门、学历、家庭、职称评定信息、账号。

员工实体型属性：Worker_no, Worker_name, Sex, Birthday, Age, ID, Department_name, Post, Phone, Work_date, Worker_ type退休员工实体型属性：Worker_no, Worker_name, Sex, Birthday, Age, ID, Department_name, Post, Phone, Work_date, Worker_ type，tui_date部门实体型属性：Department_no，Department_name，manager_no，Workeload，Real_number，Need_number学历实体型属性：Degree_no，Worker_no，Degree，Major，School，Graduate_date 家庭实体型属性：Home_no，Worker_no，Marriage_info，Home_number，Home_phone，Home_address职称评定信息实体型属性：Pro_no, Worker_no, Assess_date, Assess_unit, Assess_title, Remark2.2 人事管理系统E-R图图2.1人事管理系统E-R图3 逻辑结构设计3.1逻辑结构简介逻辑结构设计就是把概念结构设计阶段设计好的基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构。

设计逻辑结构一般分为3步进行：(1) 将概念结构转换为一般的关系、网状、层次模型；(2) 将转换来的关系、网状、层次模型向特定DBMS支持下的数据模型转换；(3) 对数据模型进行优化。

3.2 E-R图向关系模型的转换将E-R图转换为关系模型实际上就是要将实体型、实体的属性和实体型间的联系转换为关系模式，这种转换一般遵循如下原则：一个实体型转换为一个关系模式。

实体的属性就是关系的属性，实体的码就是关系的码。

对于实体间的联系则有以下不同的情况：（1）一个1：1联系可以转换为一个独立的关系模式，也可以与任意一断对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的试题的码以及联系本身的属性均转换为关系的属性，每个实体的码均为该关系的候选码。

如果与某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。

（2）一个1：n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。

（3）一个m：n联系转换为一个关系模式。

与该联系相连的饿个实体的码以及联系本身的属性均转换为关系的属性，个实体的码组成关系的码或关系码的一部分。

（4）3个或3个以上实体间的一个多元联系可以转换为一个关系模式。

与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分。

（5）具有相同码的关系模式可合并。

4物理模型设计数据库物理设计是将逻辑设计影射到存储介质上，利用可用的硬件和软件功能尽可能快地对数据进行物理访问和维护。

4.1定义数据库SQL Server 2008数据库文件分为3中类型：主数据文件、次数据文件和日志文件。

通过SQL语言进行数据库创建，创建数据库的语句如下：Create database 人事管理系统;4.2 创建存储过程4.2．1在员工基本信息表,家庭信息表，学历信息表，职称评定表，创建一个插入员工信息的存储过程：create procedure insertworker@Worker_no varchar(15) ,--工号@Worker_name varchar(8) ,--姓名@Sex varchar(2) ,--性别@Birthday date ,--出生年月@ID varchar(18) ,--身份证号@Department_name varchar(15) ,--部门@post varchar(8) ,--职位@phone varchar(11) ,--电话@work_date date ,--工作时间@Worker_type varchar(8) , --员工类型@Degree_no varchar(15) , --学位号@Degree varchar(4) ,--学位@Major varchar(25) ,--专业@School varchar(25) ,--毕业学校@Gradute_date date , --毕业时间@Home_no varchar(15) ,--家庭编号@Marriage_info varchar(4) ,--婚姻状况@Home_number smallint ,--家庭人口@Home_phone varchar(11) ,--家庭电话@Home_address varchar(50) ,--家庭地址@Pro_no varchar(15) ,--职称编号@Assess_date date ,--评定日期@Assess_unit varchar(20) ,--评定单位@Assess_title varchar(8) ,--评定职称@Remark varchar(200) --备注as--插入员工信息insert intoworker_info(Worker_no,Worker_name,Sex,Birthday,age,ID,Department_name,post, phone,work_date,Worker_type)values(@Worker_no,@Worker_name,@Sex,@Birthday,FLOOR(datediff(DY,@Birthday,getdate ())/365.25),@ID,@Department_name,@post,@phone,@work_date,@Worker_type)--插入职称信息insert intoPro_info(Pro_no,Worker_no,Assess_date,Assess_unit,Assess_title,Remark)values (@Pro_no,@Worker_no,@Assess_date,@Assess_unit,@Assess_title,@Remark) --插入家庭信息insert intohome_info(Home_no,worker_no,Marriage_info,Home_number,Home_phone,Home_addre ss)values(@Home_no,@worker_no,@Marriage_info,@Home_number,@Home_phone,@Home_address) --插入学历信息insert intodegree_info(Degree_no,Worker_no,Degree,Major,School,Gradute_date)values (@Degree_no,@Worker_no,@Degree,@Major,@School,@Gradute_date)4.2．2在部门基本信息表中创建插入部门信息的存储过程：create procedure add_department@Department_no varchar(15) ,@Department_name varchar(15) ,@Manager_no varchar(15) ,@Workeload float(2) ,@Real_number smallint ,@Need_number smallintasinsert intodepartment_info(Department_no,Department_name,Manager_no,Workeload,Real_num ber,Need_number)values(@Department_no,@Department_name,@Manager_no,@Workeload,@Real_number,@Need_ number)4.2．3在部门基本信息表中创建更新部门信息的存储过程：create procedure update_department@Department_name varchar(15) ,@Manager_no varchar(15) ,@Workeload float(2) ,@Need_number smallintasupdate department_infoset Manager_no=@Manager_no,Workeload=@Workeload,Need_number=@Need_numberwhere Department_name=@Department_name;4.2．4创建当一个员工辞职时删除该信息，当一个员工退休时，将基本信息复制到退休表中，其他信息删除的存储过程CREATE PROCEDURE worker_type@worker_no varchar(15),@worker_type varchar(4)ASBEGINSET NOCOUNT ON;IF @worker_type='辞职'BEGINdelete from worker_info where worker_no=@worker_noENDIF @worker_type='退休'BEGININSERT INTOtui_info(Worker_no,Worker_name,Sex,Birthday,Age,ID,Department_name,Post,Pho ne,Work_date,Tui_date)SELECTWorker_no,Worker_name,Sex,Birthday,FLOOR(datediff(DY,Birthday,getdate())/36 5.25),ID,Department_name,Post,Phone,Work_date,GETDATE()FROM worker_info WHERE Worker_no=@Worker_no;DELETE FROM worker_info WHERE Worker_no=@Worker_no;ENDEND4.2．5创建更新家庭信息表、学历信息、职称信息的存储过程create procedure update_home@Worker_no varchar(15) ,--工号@Marriage_info varchar(4) ,--婚姻状况@Home_number smallint ,--家庭人口@Home_phone varchar(11) ,--家庭电话@Home_address varchar(50) --家庭地址asupdate home_infoset Marriage_info=@Marriage_info,Home_number=@Home_number,Home_phone=@Home_phone,Home_address=@Home_addresswhere Worker_no=@Worker_no;--更新学历信息create procedure update_degree@Worker_no varchar(15) ,--工号@Degree varchar(4) ,--学位@Major varchar(25) ,--专业@School varchar(25) ,--毕业学校@Gradute_date date --毕业时间asupdate degree_infoset Degree=@Degree,Major=@Major,School=@School,Gradute_date=@Gradute_datewhere Worker_no=@Worker_no;--更新职称信息create procedure update_pro@Worker_no varchar(15) ,--工号@Assess_date date ,--评定日期@Assess_unit varchar(20) ,--评定单位@Assess_title varchar(8) --评定职称asupdate pro_infoset Assess_date=@Assess_date,Assess_unit=@Assess_unit,Assess_title=@Assess_titlewhere Worker_no=@Worker_no;4.2．6创建某部门平均年龄的存储过程create procedure avg_age@Department_name varchar(15)asselect AVG(Age)from worker_info where Department_name=@Department_name 4.2．7按学历查询--学历查询create procedure search_degree@name varchar(4)asbegindeclare search_degree cursor for select worker_no from degree_infowhere worker_no in(select Worker_no from degree_info where Degree=@name) open search_degreedeclare @no1 varchar(15)fetch next from search_degree into @no1while @@FETCH_STATUS = 0beginselectworker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info ,degree_info.Major,degree_info.degree,worker_info.Work_datefrom worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no1 and home_info.Worker_no=@no1and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1fetch next from search_degree into @no1endclose search_degreedeallocate search_degreeend4.2．7按部门查询create procedure search_department@name varchar(15)asbegindeclare search_department cursor for select worker_no from worker_info where Department_name=@nameopen search_departmentdeclare @no1 varchar(15)fetch next from search_department into @no1while @@FETCH_STATUS = 0beginselectworker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info ,degree_info.Major,worker_info.Work_datefrom worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no1 and home_info.Worker_no=@no1and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1fetch next from search_department into @no1endclose search_departmentdeallocate search_departmentend4.2．9按婚姻状况查询create procedure search_marriage@name varchar(15)asbegindeclare search_marriage cursor for select worker_no from home_infowhere worker_no in(select Worker_no from home_info where marriage_info=@name) open search_marriagedeclare @no varchar(15)fetch next from search_marriage into @nowhile @@FETCH_STATUS = 0beginselect * from worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no and home_info.Worker_no=@noand pro_info.Worker_no=@no and degree_info.Worker_no=@nofetch next from search_marriage into @noendclose search_marriagedeallocate search_marriageend4.3 创建触发器4.3．1定义一个触发器，当在数据库中插入一条员工信息后，触发相应的部门人数加1create trigger insert_workeron worker_infoafter insertas beginupdate department_infosetdepartment_info.Real_number = department_info.Real_number + 1fromdepartment_info as department_info,inserted as worker_infowhere department_info.Department_name=worker_info.Department_nameend;4.3．2定义一个触发器，当在数据库中删除一条员工信息后，触发相应的部门人数减1 create trigger delete_workeron worker_infoafter deleteas beginupdate department_infosetdepartment_info.Real_number = department_info.Real_number - 1fromdepartment_info as department_info,deleted as worker_infowhere department_info.Department_name=worker_info.Department_nameend;delete from worker_info where worker_no='j1';4.3．3定义一个触发器，当在数据库中员工所属的部门更改后，触发转出的部门人数减1，转入的部门人数加1create trigger update_workeron worker_infoafter updateas IF(UPDATE(Department_name))begin--转出的部门人数减update department_infosetdepartment_info.Real_number = department_info.Real_number - 1fromdepartment_info as department_info,deleted as worker_infowhere department_info.Department_name=worker_info.Department_name--转入的部门人数加update department_infosetdepartment_info.Real_number = department_info.Real_number + 1fromdepartment_info as department_info,inserted as worker_infowhere department_info.Department_name=worker_info.Department_nameend;4.3．4定义一个触发器，当在数据库中删除一个员工的基本信息时连带删除该员工的职称,学历,家庭信息create TRIGGER del_workerON worker_infofor DELETEASBEGINdelete home_infoFromhome_info as home_info , deleted as worker_infoWhere home_info.worker_no= worker_info.worker_noENDBEGINdelete degree_infoFrom degree_info as degree_info , deleted as worker_infoWhere degree_info.worker_no=worker_info.worker_noENDBEGINdelete pro_infoFrom pro_info as pro_info , deleted as worker_infoWhere pro_info.worker_no=worker_info.worker_noEND4.3．5创建一个触发器，检查部门员工是否已满，满人则不进行操作create Trigger check_departmenton department_infofor updateasIF(UPDATE(Real_number))begindeclare@size smallint,@MaxSize smallint,@Department_name varchar(15)select @department_name =department_name from insertedselect @MaxSize =Need_number ,@size = Real_number from department_info where department_name = @department_namebeginif( @size > @MaxSize )beginprint '该部门员工已满'rollback Transactionreturn ;endendend4.3．6创建一个触发器，插入或更新时检查部门是否存在，不存在则不进行操作create trigger check_dapartment_nameon worker_infofor update,insertasbegindeclare@department_name varchar(15)select @department_name = department_name from insertedif( not exists(select * from department_info where department_name =@department_name ) )beginprint '该部门不存在'rollback Transactionreturn;endend4.4 创建视图4.4．1建立一个查看所有员工的所有信息的视图create view all_worker(员工编号,姓名,性别,出生日期,年龄,身份证号,部门编号,部门,经理编号,职位,电话,工作时间,员工类型,学历号,学位,专业,毕业学校,毕业时间,职称号,评定时间,评定单位,评定职称,备注,家庭编号,婚姻状况,家庭人口,家庭电话,家庭地址)asselectworker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,worker_info.B irthday,worker_info.Age,worker_info.ID,department_info.Department_no,worker_info.Department_name,department_info.M anager_no,worker_info.Post,worker_info.Phone,worker_info.Work_date,worker_i nfo.Worker_type,degree_info.Degree_no,degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute _date,pro_info.Pro_no,pro_info.Assess_date,pro_info.Assess_unit,pro_info.Assess_title,pro_info.Remark,home_info.Home_no,home_info.Marriage_info,home_info.Home_number,home_info.Home_phone,home_info.Home_address from worker_info,degree_info,pro_info,home_info,department_info where worker_info.Worker_no=degree_info.Worker_no andworker_info.Worker_no=pro_info.Worker_noand worker_info.Worker_no=home_info.Worker_no anddepartment_info.Department_name=worker_info.Department_namewith check option4.4．2建立一个用于查看员工常用信息的视图create view searchasselect worker_info.Worker_no '员工编号',worker_info.Worker_name '员工姓名',worker_info.Sex '性别',worker_info.Age '年龄',worker_info.Department_name '部门',worker_info.Post '职位',degree_info.Degree'学位',home_info.Marriage_info '婚姻状况',worker_info.Work_date '工作时间'from worker_info,degree_info,home_info,department_info whereworker_info.Worker_no=degree_info.Worker_noand worker_info.Worker_no=home_info.Worker_nowith check option4.4．3查询员工学历视图create view show_degree(员工编号,姓名,性别,学历号,学位,专业,毕业学校,毕业时间)asselect worker_info.Worker_no,worker_info.Worker_name,worker_info.Sex, degree_info.Degree_no,degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute_date fromworker_info,dergee_infowhere worker_info.Worker_no=degree_info.Worker_nowith check option4.4．4查询部门工作量create view show_load(员工编号,员工姓名,部门,工作量 )asselectworker_info.worker_no,worker_info.worker_name,worker_info.Department_name,d epartment_info.Workeloadfrom department_info,worker_info wheredepartment_info.department_name=worker_info.department_nameselect * from show_loadwith check option4.4．5查询员工职称create view show_degree(员工编号,姓名,性别,职称号,评定时间,评定单位,评定职称,备注)asselectworker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,pro_info.Pro_ no,pro_info.Assess_date,pro_info.Assess_unit,pro_info.Assess_title,pro_info.Remark from worker_info,pro_info,home_info where worker_info.Worker_no=pro_info.Worker_no4.4．6查询员工学历create view show_degree(员工编号,姓名,性别,学历号,学位,专业,毕业学校,毕业时间)asselectworker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,degree_info.D egree_no,degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute _datefrom worker_info,degree_info whereworker_info.Worker_no=degree_info.Worker_nowith check option4.5 创建游标4.5．1创建查询未婚员工的游标declare search_marriage1 cursor for select worker_no from home_infowhere worker_no in(select Worker_no from home_info where marriage_info='未婚')open search_marriagedeclare @no varchar(15)fetch next from search_marriage into @nowhile @@FETCH_STATUS = 0beginselect * from worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no and home_info.Worker_no=@noand pro_info.Worker_no=@no and degree_info.Worker_no=@nofetch next from search_marriage into @noendclose search_marriage4.5．2创建查询已婚员工的游标declare search_marriage2 cursor for select worker_no from home_infowhere worker_no in(select Worker_no from home_info where marriage_info='已婚')open search_marriage2declare @no varchar(15)fetch next from search_marriage2 into @nowhile @@FETCH_STATUS = 0beginselect * from worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no and home_info.Worker_no=@noand pro_info.Worker_no=@no and degree_info.Worker_no=@nofetch next from search_marriage2 into @noendclose search_marriage24.5．3创建查询某部门员工的游标declare search_department cursor for select worker_no from worker_info where Department_name=@nameopen search_departmentdeclare @no1 varchar(15)fetch next from search_department into @no1while @@FETCH_STATUS = 0beginselectworker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info ,degree_info.Major,worker_info.Work_datefrom worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no1 and home_info.Worker_no=@no1and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1fetch next from search_department into @no1endclose search_departmentdeallocate search_department4.5．4创建查询有某学历员工的游标declare search_degree cursor for select worker_no from degree_infowhere worker_no in(select Worker_no from degree_info where Degree=@name) open search_degreedeclare @no1 varchar(15)fetch next from search_degree into @no1while @@FETCH_STATUS = 0beginselectworker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info ,degree_info.Major,degree_info.degree,worker_info.Work_datefrom worker_info,degree_info,pro_info,home_info whereworker_info.Worker_no=@no1 and home_info.Worker_no=@no1and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1fetch next from search_degree into @no1endclose search_degreedeallocate search_degree4.6 创建事物处理4.6．1查询部门人数begin transactionselect department_no,department_name,Manager_no,Real_number,Need_number from department_infocommit transaction;总结通过本次为期一周的数据库原理课程设计，让我对数据库系统的设计流程有了更深层次的了解，学会将理论与实际相结合解决问题，也加强了从整体思考问题的能力。