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

1、课程设计任务书设计题目：人事管理系统设计学生姓名课程名称数据库原理课程设计设 计 内 容 及 要 求起止时间内容：学生根据本课程设计指导书中的题目，进行设计。(1)设计友好的登录界面，完成用户身份检验。可以提供对各种窗日 的外观进行设计，可以提供用户进行外观选择。(2)企业新员工各种信息的输入、修改、删除等，包括员工基本信息、 学历信息、婚姻状况、职称等。(3)数据库信息维护，即员工各种数据信息的备份。(4)对于转出、辞职、退休员工信息的删除。(5)按照一定的条件，查询、统计符合条件的员工信息；至少应该包括每个员工详细信息的查询、按婚姻状况查询、按学历查询、按工作岗位查询等，至少应该包括按学历

2、、婚姻状况、岗位、参加工作时间等统计各 自的员工信息；(6)对查询、统计的结果打印输出。(7)按照部门将现有员工进行分类，查看各个部门现有员工的平均年龄，根据各部门工作量的大小，较好地进行人事调度。另外可以帮助领导做出招聘新员工人数的决定。(8)可供其他人使用帮助。要求：按照数据库系统开发步骤进行数据库概念结构设计、逻辑结构设计、 物理结构设计，使学生掌握数据库应用软件的开发流程，SQL语句的使用和 存储过程的使用。至少5个表结构；5个存储过程；5个触发器；5个视图、2处使用事务处理、3处 使用游标过程报告格式规范进 度要 求19.1 讲课阅读分析任务书制订设计计划19.2 - 3 需求分析概

3、念结构设计19.4 - 5概念结构设计准备文挡20.1-3逻辑结构设计物理结构设计20.4-5写文挡答辩交设计报告书参 考 资 料1 .雷亮等数据库原理课程设计指导书2 .王珊、萨师熠.数据库系统概述（第四版）.北京：高等教育出版社. 2006。3 . C.J.Date著.孟小峰、王珊等译.数据库系统导论（第8版）.北京：机械工 业出版社.2007。4 .陈根才等.数据库课程设计.浙江.浙江大学出版社.2007其 它说明1 .本表应在每次实施前一周由负责教师填写二份， 院系审批后交院系办备案， 一份由 负责教师留用。2 .若填写内容较多可另纸附后。3.一题多名学生共用的，在设计内容、 参数、要

4、求等方面应有所区别。教研室主任：指导教师:摘要在现代化的社会中，无论是大型企业还是中小型企业，都必不可少地会涉及 到人事管理的问题。一个完善的人事管理系统对企业的发展有着至关重要的作 用。人事管理系统分为6个模块，基本信息管理模块、退休员工信息管理模块、部 门信息管理模块、学历信息管理模块、家庭信息管理模块和职称信息管理模。本 系统采用SQL Server 2008作为数据库。在实现数据库部分功能时要求写出存储 过程、触发器和视图。关键词：人事管理SQL Server 2008触发器存储过程视图事务处理游标II目录摘要 II1需求分析 11.1 系统目标设计 11.2 系统功能分析 11.3

5、功能模块分析 21.4 系统全局数据流图 21.5 数据字典 32概念结构设计 62.1 构成系统的实体型 62.2 人事管理系统E-R图 73逻辑结构设计 83.1 逻辑结构简介 83.2 E-R图向关系模型的转换 84物理模型设计 94.1 定义数据库 94.2 创建存储过程 94.3 创建触发器 144.4 创建视图 174.5 创建游标 194.6 创建事物处理 21总结 22致谢 23参考文献 24iii1需求分析1.1 系统目标设计使企业可以实现规范化的管理。推动企业劳动人事管理走向科学化、现代化，适应现代企业制度的要求。使管理员可以很简单地完成日常的管理工作，例如新员工的加入、老

6、员工的退 休等。支持企业进行劳动人事管理及其相关方面的科学决策，如企业领导根据现有职 工的数目决定招聘的人数等。1.2 系统功能分析根据现代企业的情况，本系统要求应能完成以下主要功能：设计友好的登录界面，完成用户身份检验。可以提供对各种窗口的外观进行设 计，可以提供用户进行外观选择。企业新员工各种信息的输入、修改、删除等，包括员工基本信息、学历信息、 蜡姻状况、职称等。数据库信息维护，即员工各种数据信息的备份。对于转出、辞职、退休员工信息的删除。按照一定的条件，查询、统计符合条件的员工信息；至少应该包括每个员工详 细信息的查询、按蜡姻状况查询、按学历查询、按工作岗位查询等，至少应该包括按学 历

7、、焙姻状况、岗位、参加工作时间等统计各自的员工信息；对查询、统计的结果打印输出。按照部门将现有员工进行分类，查看各个部门现有员工的平均年龄，根据各部 门工作量的大小，较好地进行人事调度。另外可以帮助领导作出招聘新员工人数的决定可供其他人使用帮助。1.3 功能模块分析根据系统功能的基本要求，可对整个系统划分为几个模块人事管理系统图1.1功能模块分析1.4 系统全局数据流图系统的全局数据流图，也称第一层数据流图，是从整体上描述系统的数据流，反映 系统数据的整体流向，给设计者、开发者和用户一个总体描述。更新人事病理数据库查询1.5 数据字典表1.1员工基本信息表(worker_info)名称数据类型

8、说明Worker_novarchar(15)r员工编号关键字Worker_namevarchar(8)员工姓名Sexvarchar(2)性别Birthdaydate厂出生日期Agesmallint年龄IDvarchar(18)身份证号Department_namevarchar(15)部门Postvarchar(8)职位Phonevarchar(11)电话Work_datedate工作时间Worker_ typevarchar(8)员工类型表1.2离退休员工(tui_info)名称数据类型说明Worker_novarchar(15)员工编号关键字Worker_namevarchar(8)员工姓

9、名Sexvarchar(2)性别Birthdaydate出生日期Agesmallint年龄IDvarchar(18)身份证号Department_namevarchar(15)部门Postvarchar(8)职位Phonevarchar(11)电话Work_datedate工作时间表1.3部门基本信息表(Department_info )名称数据类型说明Department_noVarchar(15)部门编号关键字Department_namevarchar(15)部门名称manager_novarchar(15)经理编号WorkeloadFloat(2)工作量（%Real_numberInt

10、现人数Need_numberInt所需人数表 1.4 学历信息(degree_info)名称数据类型说明Degree_noVarchar(15)学历编号关键字Worker_novarchar(15)员工编号Degreevarchar(4)学位MajorVarchar(8)专业Schoolvarchar(25)学校Graduate_datedate毕业时间表1.5 家庭信息（home_info）名称数据类型说明Home_noVarchar(15)家庭编号关键字Worker_novarchar(15)员工编号Marriage_infovarchar(4)婚姻状况Home_numbersmallin

11、t家庭人数Home_phonevarchar(8)家庭电话Home_addressvarchar(50)家庭住址表1.6职称信息（Pro_info）名称数据类型说明Pro_noVarchar(15)职称编号关键字Worker_novarchar(15)员工编号Assess_datedatetime评定日期Assess_unitvarchar(20)评定单位Assess_titlevarchar(8)评定职称Remarkvarchar(200)备注222概念结构设计2.1构成系统的实体型由人事管理系统的数据流图和数据字典，抽取出系统的6个主要实体，包括：员工、部门、学历、家庭、职称评定信息、账号

12、。员工 实体型属性： 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。，Department_name, manager_no, Workeload,Real_nu

13、mber, Need_number学历实体型属性：Degree_no, Worker_no, Degree, Major, School, Graduate_date 家庭实体型属性：Home_noWorker_no, Marriage_info , Home_numberHome_phone Home_address职称评 定信息 实体型 属性：Pro_no, Worker_no, Assess_date, Assess_unit, Assess_title, Remark2.2人事管理系统E-R图SckiRcla 距MajorSchoddegree_inf0弋 Degree-WoiuEB1

14、 r»worker_mfodepartmen Jirif uq Dapi«rlrn«fTt_r>9Wcrki|r_nflni4DepartEgE：AmeMAhigyioRj&a numberNeed nuriTiberCOErtl也IDOflpMtTTW<_nMTM-PostPtoraWcrk_iiirtsWorkjef_jype图2.1人事管理系统E-R图tuijnf。牙 Workef_w Warkjar_nffn« Sex B<ihdj>r * IDDMAWKjiWH*PgsPhaneWgrkjhte Ty i ckr

15、te3逻辑结构设计3.1逻辑结构简介逻辑结构设计就是把概念结构设计阶段设计好的基本E-R图转换为与选用DBMS"品所支持的数据模型相符合的逻辑结构。设计逻辑结构一般分为3步进行：(1)将概念结构转换为一般的关系、网状、层次模型；(2)将转换来的关系、网状、层次模型向特定 DBM豉持下的数据模型转换；(3)对数据模型进行优化。3.2 E-R图向关系模型的转换将E-R图转换为关系模型实际上就是要将实体型、实体的属性和实体型间的联系转 换为关系模式，这种转换一般遵循如下原则：一个实体型转换为一个关系模式。实体的 属性就是关系的属性，实体的码就是关系的码。对于实体间的联系则有以下不同的情况：

16、(1) 一个1: 1联系可以转换为一个独立的关系模式，也可以与任意一断对应的关 系模式合并。如果转换为一个独立的关系模式，则与该联系相连的试题的码以及联系本 身的属性均转换为关系的属性，每个实体的码均为该关系的候选码。如果与某一端实体 对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本 身的属性。(2) 一个1: n联系可以转换为一个独立的关系模式，也可以与 n端对应的关系模 式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身 的属性均转换为关系的属性，而关系的码为 n端实体的码。(3) 一个m n联系转换为一个关系模式。与该联系相连的饿个实

17、体的码以及联系 本身的属性均转换为关系的属性，个实体的码组成关系的码或关系码的一部分。(4) 3个或3个以上实体间的一个多元联系可以转换为一个关系模式。 与该多元联 系相连的各实体的码以及联系本身的属性均转换为关系的属性， 各实体的码组成关系的 码或关系码的一部分。(5)具有相同码的关系模式可合并4物理模型设计数据库物理设计是将逻辑设计影射到存储介质上，利用可用的硬件和软件功能尽可 能快地对数据进行物理访问和维护。4.1 定义数据库SQL Server 2008数据库文件分为3中类型：主数据文件、次数据文件和日志文件 通过SQL语言进行数据库创建，创建数据库的语句如下：Create datab

18、ase 人事管理系统；4.2 创建存储过程4.3 . 1在员工基本信息表，家庭信息表，学历信息表，职称评定表，创建一个插入员工信息的存储过程：create procedure insertworkerWorker_no varchar(15),- 工号Worker_name varchar(8),- 姓名Sex varchar(2),-性另1JBirthday date ,-出生年月IDvarchar(18),-身份证号Department_name varchar(15),- 部门postvarchar(8),-职位phone varchar(11),-电话work_date date ,-

19、工作时间Worker_type varchar(8)，-员工类型Degree_no varchar(15) ,-学位号Degreevarchar(4),-学位Majorvarchar(25),-专业Schoolvarchar(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(

20、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

21、,Birthday,getdate ()/365.25),ID,Department_name,post,phone,work_date,Worker_type) -插入职称信息一一一insert into Pro_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 into home_info(Home_no,worker_no,Marriag

22、e_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在部门基本信息表中创建插入部门信息的存储过程：cr

23、eate procedure add_department Department_no varchar(15), Department_name varchar(15), Manager_novarchar(15),Workeload float(2), Real_numbersmallint ,Need_number smallint as insert into department_info(Department_no,Department_name,Manager_no,Workeload,Real_num ber,Need_number) values(Department_no,D

24、epartment_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 smallint as update department_info set Manager_no=Manager_no, Workeload=Workeload, Need_n

25、umber=Need_number where Department_name=Department_name;4.2 . 4创建当一个员工辞职时删除该信息，当一个员工退休时，将基本信息复制到退休表 中，其他信息删除的存储过程CREATE PROCEDURE worker_type worker_no varchar(15), worker_type varchar(4) AS BEGIN SET NOCOUNT ON; IF worker_type='辞职' BEGIN delete from worker_info where worker_no=worker_no END

26、 IF worker_type='退休' BEGIN INSERT 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 WH

27、ERE Worker_no=Worker_no;DELETE FROM worker_info WHERE Worker_no=Worker_no; END END 4.2 . 5创建更新家庭信息表、学历信息、职称信息的存储过程create procedure update_homeWorker_no varchar(15),-工号Marriage_info varchar(4),-蜡姻状况Home_number smallint ,-家庭人口Home_phone varchar(11),-家庭电话Home_address varchar(50)-家庭地址as update home_info

28、 set Marriage_info=Marriage_info, Home_number=Home_number, Home_phone=Home_phone, Home_address=Home_address where Worker_no=Worker_no;-更新学历信息create procedure update_degree Worker_no varchar(15),- 工号 Degreevarchar(4),-学位Majorvarchar(25),-专业Schoolvarchar(25),-毕业学校Gradute_date date -毕业时间asupdate degree

29、_info set Degree=Degree, Major=Major, School=School, Gradute_date=Gradute_date where Worker_no=Worker_no;-更新职称信息create procedure update_proWorker_no varchar(15),- 工号 Assess_date date ,-评定日期Assess_unit varchar(20),-评定单位Assess_title varchar(8)-评定职称asupdate pro_info set Assess_date=Assess_date, Assess_

30、unit=Assess_unit, Assess_title=Assess_title where 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_name4.2 . 7按学历查询-学历查询create procedure search_degreename varchar(4) asbegindeclare search_d

31、egree 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.Marr

32、iage_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

33、 search_degree end4.2 . 7按部门查询create procedure search_departmentname varchar(15) asbegindeclare search_department cursor for select worker_no from worker_infowhere Department_name=nameopen search_departmentdeclare no1 varchar(15)fetch next from search_department into no1while FETCH_STATUS = 0beginse

34、lectworker_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 se

35、arch_department into no1 endclose search_departmentdeallocate search_department end4.2 . 9按蜡姻状况查询create procedure search_marriage name varchar(15)as begindeclare search_marriage cursor for select worker_no from home_infowhere worker_no in(select Worker_no from home_info where marriage_info=name) ope

36、n search_marriage declare no varchar(15)fetch next from search_marriage into nowhile FETCH_STATUS = 0beginselect * from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=no and home_info.Worker_no=no and pro_info.Worker_no=no and degree_info.Worker_no=no fetch next from search_m

37、arriage into no endclose search_marriagedeallocate search_marriage end4.3创建触发器4.3 . 1定义一个触发器，当在数据库中插入一条员工信息后，触发相应的部门人数加1create trigger insert_worker on worker_info after insert as beginupdate department_info setdepartment_info.Real_number = department_info.Real_number + 1 fromdepartment_info as depa

38、rtment_info,inserted as worker_infowhere department_info.Department_name=worker_info.Department_name end;4.3 . 2定义一个触发器，当在数据库中删除一条员工信息后，触发相应的部门人数减1create trigger delete_workeron worker_infoafter deleteas beginupdate department_infosetdepartment_info.Real_number = department_info.Real_number - 1fromd

39、epartment_info as department_info,deleted as worker_infowhere department_info.Department_name=worker_info.Department_name end;delete from worker_info where worker_no='j1'4.3 . 3定义一个触发器，当在数据库中员工所属的部门更改后，触发转出的部门人数减1,转入的部门人数加1create trigger update_workeron worker_infoafter updateas IF(UPDATE(De

40、partment_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_numbe

41、r = department_info.Real_number + 1fromdepartment_info as department_info,inserted as worker_infowhere department_info.Department_name=worker_info.Department_name end;4.3 . 4定义一个触发器，当在数据库中删除一个员工的基本信息时连带删除该员工的职称学历，家庭信息create TRIGGER del_workerON worker_infofor DELETEASBEGINdelete home_infoFromhome_in

42、fo 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 degreeinfo.workerno=workerinfo.workernoENDBEGINdelete pro_infoFrom pro_info as pro_info , deleted as worker_infoWhere pro_in

43、fo.worker_no=worker_info.worker_noEND4.3 . 5创建一个触发器，检查部门员工是否已满，满人则不进行操作create Trigger check_departmenton department_infofor update asIF(UPDATE(Real_number)begindeclaresize smallint,MaxSize smallint,Department_name varchar(15)select department_name =department_name from insertedselect MaxSize =Need_n

44、umber ,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,insertasbegindeclaredepart

45、ment_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(员工编号，姓名，性别，出生日期，年龄，身份证号，部门编号，部

46、门，经理编号,讪位，电话,工作时间，员工类型，学历号，学位，专业,毕业学校，毕业时间，职称号，评定时间，评定单位，评定职称，备注，家庭编号，焙姻 状况,家庭人口，家庭电话，家庭地址)asselectworkerJnfo.Worker_no,worker_info.Worker_name,worker_info.Sex,workerJnfo.B irthday,worker_info.Age,worker_info.ID, department_info.Department_no,worker_info.Department_name,department_info.M anager_no,w

47、orker_info.Post,worker_info.Phone,workerJnfo.Work_date,workerJ nfo.Worker_type,degree_info.Degree_no,degree_info.Degree,degree_info.Major,degreeJnfo.School,degreeJnfo.Gradute _date,pro_info.Pro_no,pro_info.Assess_date,pro_info.Assess_unit, pro_info.Assess_title,pro_info.Remark,homeJnfo.Home_no,homeJ

48、nfo.Marriage_info,home_info.Home_number,home_info.Home_phone,homeJnfo.Home_address from worker_info,degree_info,pro_info,home_info,department_info where workerinfo.Workerno=degreeinfo.Workerno and worker_info.Worker_no=pro_info.Worker_noand worker_info.Worker_no=home_info.Worker_no anddepartment_inf

49、o.Department_name=worker_info.Department_name with check option4.4 . 2建立一个用于查看员工常用信息的视图create view search asselect worker_info.Worker_no ' 员工编号',worker_info.Worker_name '员工姓名',worker_info.Sex '性另 U',workerJnfo.Age'年龄',worker_info.Department_name '部门,，_worker_info.

50、Post ' 职位',degree_info.Degree' 学位',home_info.Marriage_info ' 女昏 姻状况',worker_info.Work_date ' 工作时间 from worker_info,degree_info,home_info,department_info where workerinfo.Workerno=degreeinfo.Workerno and worker_info.Worker_no=home_info.Worker_no with check option 4.4 . 3查询

51、员工学历视图create view show_degree（员工编号，姓名，性别，学历号，学位，专业，毕业学校，毕业时 问）asselect worker_info.Worker_no,workerJnfo.Worker_name,worker_info.Sex, degree_info.Degree_no,degree_info.Degree, degree_info.Major,degree_info.School,degree_info.Gradute_date from worker_info,dergee_infowhere workerinfo.Workerno=degreeinf

52、o.Workerno with 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 * fro

53、m show_loadwith check option4.4 . 5查询员工职称create view show_degree(员工编号，姓名，性别，职称号，评定时间，评定单位，评定职称， 备注)asselectworkerJnfo.Worker_no,worker_info.Worker_name,workerJnfo.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_infowh

54、ere 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.Degree_no,degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute_datefrom worker_info,degree_info whereworkerinfo.Workerno=degreeinfo.Workernowith 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