人事管理系统(37页).doc

人事管理系统

1.简要的系统需求分析
人事管理系统是非常通用的管理信息系统，是企事业单位实现办公自动化的前提和基础。一个公司要正常运作必定有一个健全的人事管理系统，来管理员工的信息，包括员工编号、姓名、性别、民族工资及奖惩考勤情况。除此之外，系统还供员工查询个人和公共信息，管理员对员工信息进行更改或删除，管理公司的人事变更和管理，大大提高公司内部员工管理的工作效率。
1.1业务分析
（1）部门管理：对公司的部门进行编号，将每一个部门的部门号、部门名称、部门描述和部门负责人信息统一保存在部门信息档案中。当新增或去除某个部门时，将其信息添加到部门信息档案或或从中删除。当部门信息发生变化时，修改对应和相关的属性值。
（2）员工信息管理：对公司里的所有员工进行统一编号；将每一位员工的信息录入到员工信息档案中。对新聘用的员工，将其信息加入到员工信息档案中；对于解雇的员工，将其信息从信息档案中删除。当员工的信息发生变动时，修改员工信息档案中相应的属性。同时供员工自己查询使用。其下还包含有个人家庭关系和个人呢工作学习的经历，各放在一张表格中。
（3）员工职务调动管理：同样对公司员工的职务进行统一编号；登记员工调动前后的具体职务，以及调动时间。
（4）奖惩管理：根据每个员工的编号登记奖惩原因和奖惩金额及奖惩月份。
（5）考勤管理：根据员工编号，登记员工每个月的全勤，出差，请假时日和加班迟到早退信息。
（6）工资管理：对所有员工的每笔工资进行编号，记录工资记录表。
1.2功能需求分析
（1）一般用户可以查询个人信息和修改自己的登录密码；
（2）管理员可以查询所有员工的信息并可以做添加、修改、删除的操作。

2.概念设计
ER模型设计
部门
部门编号
部门名称
部门职能描述
部门负责人编号

用户管理
用户名
密码
用户类型

员工
员工编号
姓名
籍贯
婚姻状况
文化程度
政治面貌
······
性别
民族
身份证号
生日

考勤
考勤编号
考勤月份
法定休假天数
旷工天数
员工编号
全勤天数
出差天数
病假天数
事假天数
······

员工调动
调动编号
调动日期
员工编号
调动前职务
调动后职务
批复部门编号

奖惩
员工编号
奖惩编号
处罚金额
奖励事由
奖励金额
处罚事由

家庭关系
家庭编号
工作单位
与本人关系
员工编号
年龄
性别
姓名

工作经历
职务
学校/单位名称
终止日期
开始日期
员工编号
编号

工资
工资编号
处罚金额
奖励金额
员工编号
基本工资
考勤月份

管理员
员工
属于
调动
查询
部门
维护
工作经历
家庭关系
附有
附有
员工调动
工资
考勤
查询
查询修改
查询修改
管理员
查询修改
查询修改
查询修改
维护

3.逻辑结构设计
将ER模型转化为关系模型
（1）部门（部门编号+部门名称+部门简介+部门负责人）；PS：部门编号为主码
（2）员工信息（员工编号+姓名+性别+民族+生日+政治面貌+文化程度+婚姻状况+籍贯+身份证号+工作证号+办公电话+手机号码+部门编号+职务+状态+工资+备注）Ps：员工编号为主码，部门编号为外码
（3）职务调动信息（调动编号+员工编号+调动前职务+调动后职务+调动日期+批复部门号）PS：调动编号为主码，员工编号为外码
（4）奖惩信息（奖惩编号+员工编号+奖励事由+奖励金额+处罚事由+处罚金额）PS：奖惩编号为主码+员工编号为外码
（5）考勤信息（考勤月份+员工编号+全勤天数+出差天数+病假天数+事假天数+旷工天数+迟到时间+早退时间+…）PS:考勤月份为主码，员工编号为外码
（6）工资信息（工资编号+员工编号+基本工资+奖励金额+处罚金额+考勤月份）PS：工资编号为主码，员工编号和考勤月份为外码
（7）管理员信息（用户名+密码+用户类型）PS：用户名为主码；

4.数据库的物理设计
（1）具体表的结构设计
部门：
编号
字段名称
数据结构
约束条件
1
部门编号
int
Not nullprimary key
2
部门名称
varchar(40)
Not null
3
部门职能描述
Varchar(400)
null
4
部门负责人编号
Char(3)
Not null
员工：
编号
字段名称
数据结构
约束条件
1
员工编号
Char(4)
Not null primary key
2
姓名
Varchar(50)
Not null
3
性别
Char(2)
null
4
民族
varchar(40)
null
5
生日
datetime
null
6
政治面貌
varchar(40)
null
7
文化程度
varchar(40)
null
8
婚姻状况
varchar(20)
null
9
籍贯
varchar(60)
null
10
身份证号
varchar(20)
null
11
办公电话
varchar(30)
null
12
手机号码
varchar(30)
null
13
部门编号
int
null
14
职务
varchar(20)
null
15
员工状态（1-在职，2-兼职，3，试用，4-离职，5-退休）
tinyint
null
员工调动：
编号
字段名称
数据结构
约束条件
1
调动编号
Char(2)
Not null primary key
2
员工编号
Char(4)
Not null
3
调动前职务
Varchar(20)
Null
4
调动后职务
Varchar(20)
null
5
调动日期
Datetime
Null
6
批复部门编号
int
Not null
奖惩：
编号
字段名称
数据结构
约束条件
1
奖惩编号
Char(2)
Not null primary key
2
员工编号
Char(4)
Not null
3
奖励事由
Varchar(200)
Null
4
奖励金额
smallint
Null
5
处罚事由
Varchar(200)
Null
6
处罚金额
smallint
null
考勤：
编号
字段名称
数据结构
约束条件
1
考勤编号
Char(3)
Not null primary key
2
考勤月份
Datetime
Not null
3
员工编号
int
Not null
4
全勤天数
Decimal(4,1)
Null
5
出差天数
Decimal(4,1)
Null
6
病假天数
Decimal(4,1)
Null
7
事假天数
Decimal(4,1)
Null
8
旷工天数
Decimal(4,1)
Null
9
法定休假天数
Decimal(4,1)
Null
10
迟到时间（分）
Tinyint
Null
11
早退时间(分)
Tinyint
Null
12
一类加班天数（法定节假日加班）
Int
Null
13
二类加班天数（周六或周日加班）
Int
Null
14
三类加班天数（日常加班）
Int
Null
工资：

编号
字段名称
数据结构
约束条件
1
工资编号
Char(3)
Not nullprimary key
2
员工编号
Char(4)
Not null
3
基本工资
money
Null
4
考勤月份
Datetime
Not null
5
奖励金额
money
Null
6
处罚金额
money
null
用户管理：
编号
字段名称
数据结构
约束条件
1
用户名
Varchar(40)
Not nullprimary key
2
密码
Varchar(40)
Not null
3
用户类型（1-系统管理员，2-普通用户

tinyint
Not null
家庭关系：
编号
说明
数据结构
约束条件
1
编号
tinyint
Not nullprimary key
2
员工编号
int
Not null
3
姓名
Varchar(50)
Null
4
性别
Char(2)
Null
5
年龄
tinyint
Null
6
与本人关系
Varchar(20)
Null
7
工作单位
Varchar(40)
null
工作经历：
编号
说明
数据结构
约束条件
1
编号
tinyint
Not nullprimary key
2
员工编号
int
Not null
3
开始日期
Char(10)
null
4
终止日期
Char(10)
null
5
学校/单位名称
Varchar(50)
null
6
职务
Varchar(20)
null
5.T-SQL命令方式创建数据库文件、表、索引、联系和主键、外键等约束
（1）创建数据库
create database HrSys
ALTER DATABASE HrSyS
ADD FILE
(
NAME=HrSys_dat2,
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\HrSys_dat2.ndf'
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)
LOG ON
(
NAME='HrSys_LOG',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\HrSys_log.ldf',
SIZE=3,
MAXSIZE=20,
FILEGROWTH=1
)
（2）创建表

use HrSys
create table 部门
(
部门编号int primary key,
部门名称varchar(40) not null,
部门职能描述varchar(400),
部门负责人编号char(4)not null,
)

create table 员工
(
员工编号char(4) primary key,
姓名varchar(50) not null,
性别char(2),
民族varchar(40),
生日varchar(20),
政治面貌varchar(40),
文化程度varchar(40),
婚姻状况varchar(20),
籍贯varchar(60),
身份证号varchar(20),
办公电话varchar(30),
手机号码varchar(40),
部门编号int not null,
职务varchar(20),
状态tinyint,
工资decimal(9,2)
)

create table 考勤
(
考勤编号 int primary key,
考勤月份datetime not null,
员工编号char(4) not null,
全勤天数 Decimal(4,1),
出差天数 Decimal(4,1),
病假天数 Decimal(4,1),
事假天数 Decimal(4,1),
旷工天数 Decimal(4,1),
法定休假日 Decimal(4,1),
迟到时间 Tinyint,
早退时间 Tinyint,
法定假日加班 Decimal(4,1),
周末加班 Decimal(4,1),
日常加班 Decimal(4,1),
)

create table 员工调动
(
调动编号char(2) primary key,
员工编号char(4) not null,
调动前职务varchar(20),
调动后职务varchar(20),
调动日期char(10),
批复部门编号int
)

create table 工资
(
工资编号char(3) primary key,
员工编号char(4) not null,
基本工资money,
考勤月份datetime not null,
奖励金额money,
处罚金额money,
)
alter table 工资
add 总共资as 基本工资+奖励金额-处罚金额

create table 用户管理
(
用户名varchar(40)PRIMARY KEY,
密码 varchar(40) NOT NULL,
用户类型tinyint NOT NULL
)
insert into Users values('Admin','8888',1)

create table 奖惩
(
奖惩编号 char(10)primary key,
员工编号 char(4) not null,
奖励事由 varchar(200),
奖励金额 smallint,
处罚事由 varchar(200),
处罚金额 smallint,
)
reate table 家庭关系
(
家庭编号 char(2) primary key,
员工编号 char(4) not null,
姓名 varchar(50) not null,
性别 char(2),
年龄 tinyint,
与本人关系 varchar(20),
工作单位 varchar(40)
)

create table 工作经历
(
编号 tinyint primary key,
员工编号 char(4) not null,
开始日期 Char(10),
终止日期 Char(10),
学校/单位名称 Varchar(50),
职务 Varchar(20)
)
（3）创建索引

（4）修改表来创建外键关系和约束

6.利用T-SQL命令增加、删除、修改数据
（1）向表中增加属性列

（2）修改、添加和删除数据

例如：更新工资表的结果为

7.利用T-SQL命令进行数据的检索和统计
（1）投影和选择

（2）多表连接
选择在技术部或企划部的员工姓名和所在部门名称

选择总共资在4000元以上的员工编号，姓名，职务，工资和考勤月份信息

选择有信息存在员工表，家庭关系表，工资表的员工的编号、姓名、籍贯、家庭关系、与本人关系、基本工资信息

（3）分组统计与HAVING
统计每个部门的男女人数
select 部门编号,性别,count(*) as 人数from 员工
group by 部门编号,性别

选择基本工资在2500以上的员工
SELECT 员工编号,avg(基本工资)FROM 工资
group by 员工编号
having avg(基本工资)>2500

（4）子查询
选择没有信息在家庭关系表中的员工信息
SELECT *
FROM 员工
WHERE not EXISTS (SELECT *
FROM 家庭关系
WHERE 家庭关系.员工编号=员工.员工编号)

select 员工编号,姓名,生日,籍贯,身份证号,职务,办公电话
from 员工
where EXISTS(SELECT *
FROM 工作经历
WHERE 员工.员工编号=工作经历.员工编号)

（5）查询结果保存
select 姓名,职务,办公电话,手机号码,调动前职务
into B_INfo
from 员工,员工调动
where 员工.员工编号=员工调动.员工编号
select *from B_INfo

8.利用SQL命令创建数据库视图
（1）基于单表的
建立视图员工常用信息
CREATE VIEW 员工常用信息(员工编号,姓名,职位,办公电话,手机号码,部门编号)
AS
SELECT 员工编号,姓名,职务,办公电话,手机号码,部门编号
FROM dbo.员工
sp_helptext 员工常用信息

（2）包含多表连接的
建立视图员工考勤信息，员工基本信息和2012年考勤情况
CREATE VIEW 员工考勤信息(员工姓名,职位,办公电话,旷工天数,事假天数,病假天数)
AS
SELECT 姓名,职务,办公电话,旷工天数,事假天数,病假天数
FROM dbo.员工, dbo.考勤
WHERE (datename(yy,考勤月份)='2012')

sp_helptext 员工考勤信息

（3）包含统计运算的
建立视图view1，查询所有职工的职工号、姓名、部门名和2011年份工资，并按部门名顺序排列；
create view view1
as
select top 50 员工.员工编号,员工.姓名,部门.部门名称,工资.总工资
from 员工,部门,工资
where year(考勤月份)='2011'
order by 部门名称
sp_helptext view1

建立视图view2,查询所有员工的员工编号、姓名和平均工资；
create view view2
as
select 员工.员工编号,员工.姓名,avg(基本工资)as 平均工资from 员工,工资
where 员工.员工编号=工资.员工编号
GROUP BY 员工.员工编号,员工.姓名
sp_helptext view2

（4）利用SQL对视图进行查询

9.对视图进行插入、删除、修改数据操作
插入：

删除：

修改：

10.编写一个自定义函数，将一个字符串作为自变量，返回颠倒顺序的字符串

（2）带参数的函数，通过员工编号返回员工信息
create function 员工情况(@个人编号varchar(4))
returns table
as
return(select 姓名,职务,办公电话
from 员工
where 员工编号=@个人编号)
select * from 员工情况('4002')

11. 编写利用游标进行数据处理的存储过程。并在存储过程中应用事务的概念。
自己确定过程的具体要求，然后编写出来。
（1）利用游标读取员工表中的每一行数据：
declare cur_员工cursor
global scroll dynamic
for
select 员工编号,姓名,职务,文化程度,籍贯,身份证号,手机号码
from 员工

open cur_员工
fetch next from cur_员工
while @@fetch_status=0
begin
fetch next from cur_员工
end

（2）创建带参数的存储过程，从表员工，家庭关系，工资的连接中返回输入的员工姓名的员工编号、姓名、籍贯、婚姻状况、基本工资、家人姓名、与自己的关系、工作单位信息。
create proc yg_info @姓名varchar(20) as
select 员工.员工编号,员工.姓名,籍贯,婚姻状况,基本工资,家庭关系.姓名,与本人关系,工作单位
from 员工inner join 家庭关系on 员工.员工编号=家庭关系.员工编号
join 工资on 家庭关系.员工编号=工资.员工编号
where 员工.姓名like @姓名

exec yg_info '陈礼貌'

12. 编写一个实现修改表的触发器，实现完整性控制。
(1)更新员工表信息
use HrSys
CREATE TRIGGER workerid ON 员工
FOR UPDATE
AS
IF (update(员工编号))
BEGIN
DECLARE @BHO CHAR(4),@BHN CHAR(4)
SELECT @BHO=员工编号FROM DELETED
SELECT @BHN=员工编号FROM INSERTED
UPDATE 工资
SET 工资.员工编号=@BHN
WHERE 工资.员工编号=@BHO
UPDATE 考勤
SET 考勤.员工编号=@BHN
WHERE 考勤.员工编号=@BHO
UPDATE 奖惩
SET 奖惩.员工编号=@BHN
WHERE 奖惩.员工编号=@BHO
UPDATE 工作经历
SET 工作经历.员工编号=@BHN
wHERE 工作经历.员工编号=@BHO
UPDATE 员工调动
SET 员工调动.员工编号=@BHN
WHERE 员工调动.员工编号=@BHO
UPDATE 家庭关系
set 家庭关系.员工编号=@BHN
WHERE 家庭关系.员工编号=@BHO
END

UPDATE dbo.员工
SET 员工编号='1012'
WHERE 员工编号='1002'

（2）删除员工编号
CREATE TRIGGER YGDEL
ON dbo.员工
FOR DELETE
AS
BEGIN
DECLARE @BH CHAR(4)
SELECT @BH=员工编号FROM DELETED
IF EXISTS (SELECT * FROM 部门WHERE 部门负责人编号=@BH)
UPDATE dbo.部门
SET 部门负责人编号=NULL
WHERE 部门负责人编号=@BH
End
delete from 员工
where 员工编号='2001'

13. 设置服务器身份验证模式。用命令创建若干不同验证模式的登录账户。
（1）在控制面板了面创建用户

（2）创建Windows身份验证和SQL Server的登录账户

14. 将部分登录赋予服务器角色。然后撤消。
exec sp_addsrvrolemember '四叶草','sysadmin'
exec sp_addsrvrolemember 'ZY-20080929TQYO\满天星','dbcreator'
exec sp_helpsrvrolemember 'sysadmin'
exec sp_helpsrvrolemember 'dbcreator'

撤销服务器角色成员：
exec sp_dropsrvrolemember '四叶草','sysadmin'
exec sp_dropsrvrolemember 'ZY-20080929TQYO\满天星','dbcreator'

15. 将部分登录映射到你的数据库中成为用户。
use HrSys
go
create user [四叶草] from login [四叶草]
create user [ZY-20080929TQYO\满天星] from login [ZY-20080929TQYO\满天星]

16. 创建自定义角色、架构。
（1）创建自定义角色
create role [role1]
create role [role2]
create role [role3] authorization [四叶草]

（2）创建自定义架构
create schema sc1

create schema sc2 authorization [四叶草]

create schema sc3
//把sc2的所有权转让给ZY-20080929TQYO\满天星，它就可以直接使用sc2下的表
alter authorization
on schema:: sc2
to [ZY-20080929TQYO\满天星]

17. 通过角色给用户授权。
use HrSys
GRANT update ON 员工TO role1
GRANT SELECT ON 工资TO role1
EXEC sp_addrolemember 'role1','四叶草'
用户四叶草获得对员工表的更新和对工资表的查询权限
GRANT update ON 部门TO role2
GRANT SELECT ON 考勤TO role2
EXEC sp_addrolemember 'role2','ZY-20080929TQYO\满天星'
用户满天星获得对部门表的更新和对考勤表的查询权限，连接数据库服务器后可以看到HrSys数据库中的部门表和考勤表
exec sp_helprolemember 'role1'
exec sp_helprolemember 'role2'

18. 直接给用户授权，验证其获得权限前后的操作差别。
例如：赋予用户user1 对部门表插入和对员工表查询的权限，则在用户user1下可以看到HrSys数据库中的表部门和员工表，可以队员工表进行插入却不可以进行查询；而在员工表上可以查询而不能进行其他操作，授权是局部的权利，被授权者只能执行获得权利的操作，维护数据库的安全性。

19. 删除用户和自定义的角色。
exec sp_droprolemember 'role3','四叶草'
drop role [role3]
drop user [四叶草]
drop role [role1]
exec sp_droprolemember 'role2','ZY-20080929TQYO\满天星'
drop role [role2]
drop user [ZY-20080929TQYO\满天星]
drop user [user1]
删除角色时要先删除角色中的成员