目录
1 课题描述
2 需求分析
3 概念模型设计
3.1 分析关系模式
3.2 E-R图
3.3系统表关系
4 逻辑结构设计
5 物理结构设计
5.1 表结构
5.2 视图
5.3 创建触发器
5.4 创建存储过程
5.5 建立索引
6 数据库建立
7 数据库维护
7.1 数据备份
7.2 数据库安全性
1 课题描述
设计内容:
针对中小企业的发展和公司员工的不断增多,人力资源管理的成本也在日益增加的问题。通过对中小企业人力资源管理系统的管理方式进行调查研究,设计人力资源管理系统的数据库和实现数据操作。
1.系统主要功能
(1) 企业员工管理功能。企业员工实现注册、登录、岗位信息查看以及个人中心等功能。
(2) 系统管理员功能。管理员实现用户管理、企业信息维护、系统管理、企业管理及公司财务管理等功能。
(3) 各类信息的统计打印功能,并将统计的结果导出成Excel 文档:
(4) 系统用户的权限设置功能。系统可分为管理员、企业员工两种角色。
2.具体要求:
(1) 根据选题,搜集资料,进行系统调查。
(2) 利用面向对象分析方法进行系统的功能需求,性能需求,完成系统分析。
(3) 设计数据库概念结构:绘制所选课题的E-R图;
(4) 设计数据库逻辑结构:将E-R图转换数据库的逻辑结构,并进行优化;
(5) 设计数据库物理结构:选定实施环境,确定系统数据库的存储结构和存取方法等;
(6) 数据实施和维护:选用SQL SERVER建立数据库结构,加载数据,实现各种数据查询,设计库中视图、存储过程等对象,并能对数据库做简单的维护操作,完成建立数据库的备份作业:
(7) 根据题目的需求,写出完成各个功能操作
2 需求分析
人力资源管理系统涉及企业、部门、岗位、员工、财务着五个实体集。企业从企业编号、企业名称、地址、联系方式、管理员编号几个方面刻画。部门从部门编号、部门名称、负责人编号三个方面刻画。岗位从岗位编号、岗位名称两方面刻画。员工从员工编号、名字、性别、电话、住址、入职时间、岗位、部门几个方面刻画。财务从员工、工资、发放日期三方面刻画。应用语义为一个公司一个企业可以有多个部门,每个部门可以有多个岗位,但一个岗位只能属于一个部门,每个岗位可以有多名员工,但一名员工只能属于一个岗位,每名员工有单独的工资。
该系统有如下功能:
- 企业信息录入
- 部门信息录入
- 岗位信息录入
- 员工信息录入
- 财务信息录入
- 企业信息修改
- 部门信息修改
- 岗位信息修改
- 员工信息修改
- 财务信息修改
- 员工信息删除
- 员工信息添加
- 员工信息查询
3 概念模型设计
3.1 分析关系模式
首先根据需求分析和设计内容,分析出对应的关系模式,再根据关系模式构建E-R图,
进行概念结构设计。
企业与部门:一个企业有多个部门,部门属于一个企业。
部门与岗位:一个部门有多个岗位,一个岗位只属于一个部门。
岗位与员工:一个岗位可以有多名员工,一个员工只属于一个岗位。
员工与财务:一个员工对应一个财务信息。
3.2 E-R图
图3.1 员工实体属性图
图3.2 岗位实体属性图
图3.3 部门实体属性图
图3.4 财务实体属性图
图3.5 企业实体属性图
图3.6 整体E-R图
3.3系统表关系
图 3.7 数据库关系图
4 逻辑结构设计
将E-R图转化为关系模式:
企业(企业编号、企业名称、地址、联系方式、管理员编号)
主码:企业编号
外码:管理员编号(员工编号)
部门(部门编号、部门名称、负责人编号)
主码:部门编号
外码:负责人编号(员工编号)
岗位(岗位编号、岗位名称)
主码:岗位编号
外码:无外码
员工(员工编号、名字、性别、电话、住址、入职时间、岗位编号、部门编号)
主码:员工编号
外码:岗位编号、部门编号
财务(员工编号、工资、发放日期)
主码:员工编号
外码:员工编号
5 物理结构设计
5.1 表结构
SELECT
CASE
WHEN
col.colorder = 1 THEN
obj.name ELSE ”
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL( ep.[value], ” ) AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL( COLUMNPROPERTY( col.id, col.name, ‘Scale’ ), 0 ) AS 小数位数 ,
CASE
WHEN COLUMNPROPERTY( col.id, col.name, ‘IsIdentity’ ) = 1 THEN
‘√’ ELSE ”
END AS 标识 ,
CASE
WHEN EXISTS (
SELECT
1
FROM
dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = ‘PK’
WHERE
sc.id = col.id
AND sc.colid = col.colid
) THEN
‘√’ ELSE ”
END AS 主键 ,
CASE
WHEN col.isnullable = 1 THEN
‘√’ ELSE ”
END AS 允许空 ,
ISNULL( comm.text, ” ) AS 默认值
FROM
dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
INNER JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = ‘U’
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = ‘MS_Description’
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = ‘MS_Description’
WHERE obj.name IN ( ‘企业’) — 表名 ,’部门’,’部岗位’,’员工’,’财务’
图5. 1企业表
图5. 2部门表
图5. 3岗位表
图5. 4员工表
图5. 5财务表
5.2 视图
–员工工资视图
create view vie_员工工资
as
select 员工.员工编号,名字,工资
from 员工,财务
where 员工.员工编号 = 财务.员工编号
图5.6员工工资视图
–员工详细信息
create view vie_员工信息
as
select 员工编号,名字,性别,电话,住址,入职时间,部门名称,岗位名称
from 员工,部门,岗位
where 员工.部门编号 = 部门.部门编号 and 员工.岗位编号 = 岗位.岗位编号 and 岗位.部门编号 = 部门.部门编号
图5.7员工信息视图
5.3 创建触发器
create trigger trg_员工
ON 员工
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM 财务
WHERE 员工编号 IN (SELECT 员工编号 FROM deleted);
DELETE FROM 员工
WHERE 员工编号 IN (SELECT 员工编号 FROM deleted);END;
5.4 创建存储过程
–创建存储过程,插入新员工数据
create procedure pro_员工
@员工编号 int ,
@名字 char(20),
@性别 char(4),
@电话 char(20),
@住址 char(20),
@入职时间 date,
@部门编号 int,
@岗位编号 int
AS
BEGIN
INSERT INTO 员工 (员工编号,名字,性别,电话,住址,入职时间,部门编号,岗位编号)
VALUES (@员工编号,@名字,@性别,@电话,@住址,@入职时间,@部门编号,@岗位编号)
END;
–创建存储过程,更新员工数据
create procedure pro_员工1
@员工编号 int ,
@名字 char(20),
@性别 char(4),
@电话 char(20),
@住址 char(20),
@入职时间 date,
@部门编号 int,
@岗位编号 int
AS
BEGIN
update 员工
set 名字 = @名字,性别 = @性别,电话 = @电话,住址 = @住址,入职时间 = @入职时间 ,部门编号 = @部门编号,岗位编号 = @岗位编号
where 员工编号 = @员工编号
END;
5.5 建立索引
–在员工表的部门编号字段上建立非聚集索引,以支持对直接上级的查询:
CREATE NONCLUSTERED INDEX idx_员工_部门编号 ON 员工 (部门编号);
–在财务表的员工编号字段上建立非聚集索引,以支持对员工工资的查询:
CREATE NONCLUSTERED INDEX idx_财务_员工编号 ON 财务 (员工编号);
–在员工表的岗位编号字段上建立非聚集索引,以支持对直接上级的查询:
CREATE NONCLUSTERED INDEX idx_员工_岗位编号 ON 员工 (岗位编号);
6 数据库建立
Sql代码:
–创建人力资源管理系统数据库
createdatabase人力资源管理系统;
–创建企业表
createtable企业(
企业编号 intnotnull,
企业名称 char(30)null,
地址 char(30)null,
联系方式 char(20)null,
管理员编号 intnull,
constraintPK_企业 primarykey (企业编号)
)
go
–创建员工表
createtable员工(
员工编号 intnotnull,
名字 char(20)null,
性别 char(4)null,
电话 char(20)null,
住址 char(20)null,
入职时间 datenull,
部门编号 intnull,
岗位编号 intnull,
constraintPK_员工 primarykey (员工编号)
)
go
–创建岗位表
createtable岗位(
岗位编号 intnotnull,
部门编号 intnull,
岗位名称 char(20)null,
constraintPK_岗位 primarykey (岗位编号)
)
go
–创建财务表
createtable财务(
员工编号 intnotnull,
工资 moneynull,
发放日期 datenull,
constraintPK_财务 primarykey (员工编号)
)
go
–创建部门表
createtable部门(
部门编号 intnotnull,
部门名称 char(20)null,
负责人编号 intnotnull,
constraintPK_部门 primarykey (部门编号)
)
go
–添加外键
altertable企业
addconstraintFK_企业_REFERENCE_员工 foreignkey (管理员编号)
references员工(员工编号)
go
altertable员工
addconstraintFK_员工_REFERENCE_部门 foreignkey (部门编号)
references部门(部门编号)
go
altertable员工
addconstraintFK_员工_REFERENCE_岗位 foreignkey (岗位编号)
references岗位(岗位编号)
go
altertable岗位
addconstraintFK_岗位_REFERENCE_部门 foreignkey (部门编号)
references部门(部门编号)
go
altertable财务
addconstraintFK_财务_REFERENCE_员工 foreignkey (员工编号)
references员工(员工编号)
go
–插入数据
insertinto企业 values(10001,‘中国人力企业公司’,‘陕西省—汉中市—汉台区’,‘029—83392849’,20001);
insertinto员工 values(20001,‘sa’,‘男’,‘13324539392’,‘天地一号’,‘2023-01-01’,01,10),
(20002,‘张一’,‘男’,‘13324539381’,‘天地二号’,‘2023-02-01’,02,11),
(20003,‘张二’,‘女’,‘13324539382’,‘天地三号’,‘2023-02-01’,02,12),
(20004,‘张三’,‘男’,‘13324539383’,‘天地四号’,‘2023-02-01’,03,13),
(20005,‘张四’,‘女’,‘13324539384’,‘天地五号’,‘2023-02-01’,03,14),
(20006,‘张五’,‘男’,‘13324539385’,‘天地六号’,‘2023-02-01’,02,11);
insertinto部门 values(01,‘财务部’,20001),
(02,‘技术部’,20002),
(03,‘策划部’,20004);
insertinto岗位 values(10,01,‘会计’),
(11,02,‘算法工程师’),
(12,02,‘程序员’),
(13,03,‘策划总监’),
(14,03,‘设计师’);
insertinto财务 values(20001,10000,‘2023-01-01’),
(20002,8000,‘2023-02-01’),
(20003,7500,‘2023-02-01’),
(20004,8000,‘2023-02-01’),
(20005,7000,‘2023-02-01’),
(20006,7500,‘2023-02-01’);
–查询员工编号为2001的财务信息
select*from员工,财务 where员工.员工编号 =财务.员工编号 and财务.员工编号 =20001
图6.120001的财务信息
–查找财务部的部门负责人的详细信息
select*
from员工
where员工编号 =(select负责人编号 from部门 where部门名称 =‘财务部’)
–查找企业信息
select*from企业
图6.2财务部负责人信息
图6.3企业信息
7 数据库维护
7.1 数据备份
–执行完整数据库备份:
BACKUPDATABASE人力资源管理系统
TODISK=‘C:\backup\完整数据库备份.bak’
WITHFORMAT,NAME=‘完整备份’;
–执行差异备份(在完整备份之后):
BACKUPDATABASE人力资源管理系统
TODISK=‘C:\Backup\差异备份.bak’
WITHDIFFERENTIAL,NAME=‘差异备份’;
–执行日志备份(在完整备份之后):
BACKUPLOG人力资源管理系统
TODISK=‘C:\Backup\日志备份.bak’
WITHNORECOVERY,NAME=‘日志备份’;
7.2 数据库安全性
- 创建用户
–创建sa用户
SP_addlogin ‘sa’,’123456′;
SP_adduser ‘sa’;
grant select on 学生 to LaiPing with grant option;
–创建张一用户
SP_addlogin ‘张一’,’123456′;
SP_adduser ‘张一’;
grant delete on 选修课 to LiLi with grant option;
grant select on 选修课 to LiLi with grant option;
–创建张二用户
SP_addlogin ‘张二’,’123456′;
SP_adduser ‘张二’;
grant insert on 选修课 to LiPign ;
grant select on 选修课 to LiPign ;
- 授权
grant select,insert,update on 表名字 to 用户 ;