关系数据库原理及应用-人力资源管理系统数据库的设计与实现.docx

《关系数据库原理及应用》课程设计实验报告
一、 实验题目
《人力资源管理系统数据库的设计与实现》：
设计一个人力资源管理系统，实现信息的查询和维护。
二、 实验时间、地点
实验时间：2010年11月22至2010年11月24日
实验地点：软件楼501
三、 实验目的
通过本次课程设计让学生能够综合运用所学的关系数据库原理知识解决并能设计一个实际问题，进一步掌握数据库原理的相关理论和数据库的设计实现过程，进一步提高学生的分析问题和解决问题的能力以及学生的动手能力。
四、 实验要求
1. 对各个系统进行系统的功能需求分析
2. 数据库设计分析阶段，进行校内关系的数据库需求分析，进行概念数据库的设计，画出数据库的E-R图（局部和整体E-R图）
3. 设计出详细的逻辑数据库结构，将各个实体和联系转化为相应的二维表，即关系模式，指定各个关系的主关键字和外部关键字，并对各个关系的约束加以限定。
4. 通过企业管理器或是查询分析器实现各个二维关系（建议最好使用SQL代码实现），要求建立相关的索引。
5. 根据系统功能需求设计相应的查询视图。
6. 要求根据系统功能需求建立存储过程
7. 根据功能需求建立相应的触发器以保证数据的一致性。
8. 通过建立用户权限分配实现数据库的一定安全性，考虑数据库的备份与恢复（此内容选作）
五、 实验思路
1. 系统需求分析
通过对某公司的实地调查，了解到该公司的人力资源管理仍停留在完全的人工管理阶段，没有标准的人事管理存档信息。这种人工管理方式费时、费事、费力，导致工作效率底下。开发出合适的人力资源管理系统，可方便人力资源的管理，提高该公司管理的效率。
I. 需求分析阶段的目标
(1) 了解目前企业人力资源管理现状以及SQL2000的功能和特点。
(2) 通过实地调查和问答-记录的方式了解企业人力资源管理的业务流程，并记录和处理相关数据。
(3) 与指导教师交流个人想法，改正不合理的地方，为下面的概念设计和逻辑设计奠定基础。

II. 需求分析阶段的任务
(1) 处理对象
1. 员工基本信息(employee):包括员工号，姓名，性别，年龄，学历，住址，电话，出生年月，部门编号，职位编号，
2. 员工工资信息(salary,basicsalary,additional):包括员工号，基本工资，补贴，应发工资，以及所交税。
3. 公司部门职务信息(position,department):包括员工号，员工的职位和所属部门。
III. 数据字典
(1) 数据项
系统涉及的数据有项。见下表。
数据项目编号
数据项名
数据项含义
存储结构
别名
DI-1
addid
补贴编号
Char(5)
补贴号
DI-2
addmoney
补贴金额
Float
补贴
DI-3
basicid
基本工资编号
Char(5)
基本工资号
DI-4
basicsal
员工基本工资
Float
基本工资
DI-5
dno
部门编号
Char(5)
部门号
DI-6
dname
部门名称
Char(10)
部门名
DI-7
eno
员工编号
Char(5)
员工号
DI-8
ename
员工姓名
Char(10)
姓名
DI-9
sex
员工性别
Char(2)
性别
DI-10
age
员工年龄
Int
年龄
DI-11
edu
员工学历
Char(10)
学历
DI12
address
员工家庭住址
Varchar(50)
住址
DI-13
tel
员工电话
Char(20)
电话
DI-14
birth
员工出生年月
Datetime
生日
DI-15
pno
部门编号
Char(5)
部门号
DI-16
pname
部门名称
Char(10)
部门名
DI-17
salary
员工应发工资
Float
应发工资
DI-18
realsal
员工实发工资
Float
实发工资
DI-19
tax
员工上缴税率
Float
税率

(2) 数据结构
数据结构见下表：
数据结构编号
数据结构名
数据结构含义
组成
DS-1
employee
员工基本信息
eno,ename,sex,age,edu,
address,tel,birth,dno,pno
DS-2
position
职位信息
pno,pname
DS-3
department
部门信息
dno,dname
DS-4
salary
员工工资信息
eno,salary,realsal,addid,
basicid,tax
DS-5
additional
补贴信息
addid,addmoney
DS-6
basicsalary
基本工资信息
basicid,basicsal

六、 实验过程
1. 概念设计阶段
I. 概念模型设计
(1) 根据不同对象，分别画出其E-R图。
各实体间关系的E-R图，如下图所示：
员工
工资
拥有

各实体间关系的E-R图

员工基本信息的E-R图，如下图所示：
员工
员工号
姓名
性别
年龄
学历
住址
电话
生日
部门号
职位号

员工基本信息的E-R图

工资信息的E-R图，如下图所示：
工资
员工号
基本工资号
应发工资
补贴号
应发工资
税

工资信息的E-R图
职务信息和部门信息的E-R图：
职务信息
职务号
职务名称
部门信息
部门编号
部门名称

职务信息、部门信息的E-R图
补贴信息和基本工资信息的E-R图：
基本工资信息
基本工资号
基本工资
补贴信息
补贴编号
补贴

补贴信息、基本工资信息的E-R图

(2) 各E-R图中每个实体的属性如下：
员工：employee(eno,ename,sex,age,edu,address,tel,birth,dno,pno)。
职位：position(pno,pname)
部门：department(dno,dname)
员工工资：salary(eno,salary,realsal,addid,basicid,tax)
补贴：additional(addid,addmoney)
基本工资：basicsalary(basicid,basicsal)
(3) 合并各E-R图，消除属性冲突，命名冲突，结构冲突等3类冲突，得到初步E-R图，再消除不必要的冗余，得到基本E-R图，如下所示：
员工
补贴
职位
部门
基本工资
工资
属于
拥有
拥有
属于
属于

人力资源管理基本E-R图
II. 新系统流程
新系统流程图，如下图所示：
用户
显示查询结果
发出查询内容
判断查询内容
在员工基本信息中处理
在员工工资信息中处理
存储修改数据

发出修改信息
判断修改内容
在员工基本信息中处理
在员工工资信息中处理

2. 逻辑设计阶段
I. 将E-R图转换为关系模型
员工：employee(eno,ename,sex,age,edu,address,tel,birth,dno,pno)。
职位：position(pno,pname)
部门：department(dno,dname)
员工工资：salary(eno,salary,realsal,addid,basicid,tax)
补贴：additional(addid,addmoney)
基本工资：basicsalary(basicid,basicsal)
II. 数据库模式定义
(1) 员工(employee)基本信息表,用于记录员工的基本信息。
员工基本信息表
属性名
数据类型
取值范围
是否是主属性或外键
完整性
eno
Char(5)
00000~99999
是
NOT NULL
ename
Char(10)

否
NOT NULL
sex
Char(2)
男、女
否
NOT NULL
age
Int
0~100
否
NOT NULL
edu
Char(10)

否
NOT NULL
address
Varchar(50)

否
NULL
tel
Char(20)

否
NULL
birth
Datetime
1900-1-1~9999-12-31
否
NULL
dno
Char(5)
00000~99999
是
NOT NULL
pno
Char(5)
00000~99999
是
NOT NULL

(2) 补贴(additional)信息表，记录不同等级的补贴钱数。
补贴信息表
属性名
数据类型
取值范围
是否是主属性或外键
完整性
addid
Char(5)
00000~99999
是
NOT NULL
addmoney
Float
0.0~99999
否
NOT NULL

(3) 基本工资(basicsalary)信息表，记录不同等级的基本工资钱数。
基本工资信息表
属性名
数据类型
取值范围
是否是主属性或外键
完整性
basicid
Char(5)
00000~99999
是
NOT NULL
basicsal
Float
0.0~99999
否
NOT NULL

(4) 部门(department)信息表，记录部门名称。
部门信息表
属性名
数据类型
取值范围
是否是主属性或外键
完整性
dno
Char(5)
00000~99999
是
NOT NULL
dname
Char(10)

否
NOT NULL

(5) 职位(position)信息表，记录职位名称。
职位信息表
属性名
数据类型
取值范围
是否是主属性或外键
完整性
pno
Char(5)
00000~99999
是
NOT NULL
pname
Char(10)

否
NOT NULL

(6) 员工工资(salary)信息表，记录员工的工资情况。
员工工资信息表
属性名
数据类型
取值范围
是否是主属性或外键
完整性
eno
Char(5)
00000~99999
是
NOT NULL
addid
Char(5)
00000~99999
是
NOT NULL
basicid
Char(5)
00000~99999
是
NOT NULL
salary
Float
0.0~99999
否
NULL
realsal
Float
0.0~99999
否
NULL
tax
Float
0.0~1
否
NOT NULL

III. 系统功能模块

人力资源管理系统
员工基本信息管理
员工工资信息管理

系统功能模块图

3. 数据库实施阶段
I. 建立数据库和数据表
Create database employee_management;
(1) 员工基本信息表的建立
create table employee
(
eno char(20) PRIMARY KEY,
pname char(10) NOT NULL,
sex char(2) NOT NULL,
age int NOT NULL,
edu char(10),
address char(50),
tel char(20),
birth datetime NOT NULL,
pno char(20) NOT NULL,
dno char(20) NOT NULL
)
(2) 工资基本信息表的建立
create table salary(
eno char(20) PRIMARY KEY,
salary float,
realsal float ,
addid char(20) NOT NULL,
basicid char(20) NOT NULL
)
(3) 部门信息表的建立
create table department
(
dno char(20) PRIMARY KEY,
dname char(20) NOT NULL,
)
(4) 职务信息表的建立
create table position
(
pno char(20) PRIMARY KEY,
pname char(20) NOT NULL,
)
(5) 补贴表的建立
create table additional
(
addid char(20) PRIMARY KEY,
addmoney float NOT NULL
)
(6) 基本工资表的建立
create table basicsalary
(
basicid char(20) PRIMARY KEY,
basicsal float NOT NULL
)
II. 建立视图
(1) 员工信息视图：
create view view_employee
as
select eno as 员工号,employee.pname as 姓名,sex as 性别,age as 年龄,edu as 学历,address as 住址,tel as 联系电话,birth as 出生年月,position.pname as 职位,department.dname as 部门
from employee,position,department
where employee.pno=position.pno and employee.dno=department.dno

(2) 工资信息视图：
create view view_salary
as
select employee.pname as 姓名,salary as 应发工资,realsal as 实发工资,addmoney as 补贴,basicsal as 基本工资, tax as 税
from salary,additional,basicsalary,employee
where salary.addid=additional.addid and basicsalary.basicid=salary.basicid and employee.eno=salary.eno

(3) 全部信息视图：
create view view_allinfo
as
select employee.eno as 员工号,employee.pname as 姓名,sex as 性别,age as 年龄,edu as 学历,address as 住址,tel as 联系电话,birth as 出生年月,position.pname as 职位,department.dname as 部门,salary as 应发工资,realsal as 实发工资,addmoney as 补贴,basicsal as 基本工资, tax as 税
from salary,additional,basicsalary,employee,position,department
where employee.pno=position.pno and employee.dno=department.dno and salary.addid=additional.addid and basicsalary.basicid=salary.basicid and employee.eno=salary.eno

III. 数据入库
员工信息采取用Excel导入的方式录入数据。这种录入方式方便快捷，适合大量数据的录入。

IV. 创建各功能的存储过程
(1) 存储过程：插入员工信息
create procedure input_employee
(
@员工号 char(20),
@姓名 char(20),
@性别 char(2),
@年龄 int,
@学历 char(10),
@住址 char(50),
@电话 char(20),
@生日 datetime,
@职位号 char(20),
@部门号 char(20)
)
as
insert into employee values(@员工号,@姓名,@性别,@年龄,@学历,@住址,@电话,@生日,@职位号,@部门号)

(2) 存储过程：插入工资信息
create procedure input_salary
(
@员工号 char(20),
@补贴号 char(20),
@基本工资号 char(20),
@税 float
)
as
insert into salary values(@员工号,0,0,@补贴号,@基本工资号,@税)

(3) 存储过程：更新员工信息
create procedure update_employee
(
@员工号 char(5),
@姓名 char(20),
@性别 char(2),
@年龄 int,
@学历 char(10),
@住址 char(50),
@电话 char(20),
@生日 datetime,
@职位号 char(20),
@部门号 char(20)
)
as
if EXISTS(select * from employee where eno=@员工号)
BEGIN
update employee
set pname=@姓名,sex=@性别,age=@年龄,edu=@学历,address=@住址,tel=@电话,birth=@生日,pno=@职位号,dno=@部门号
where @员工号=employee.eno
END
else
print '不存在的员工'

(4) 存储过程：更新工资信息
create procedure update_salary
(
@员工号 char(20),
@补贴号 char(20),
@基本工资号 char(20),
@税 float
)
as
if EXISTS(select * from employee where eno=@员工号)
BEGIN
update salary
set addid=@补贴号,basicid=@基本工资号,tax=@税
where @员工号=salary.eno
END
else
print '不存在的员工'

(5) 存储过程：指定员工号删除信息
create procedure delete_info
(
@员工号 char(5)
)
as
delete
from employee
where eno=@员工号
delete
from salary
where eno=@员工号

V. 创建触发器
(1) 触发器：计算应发工资和实发工资
create trigger salcal
on salary for insert,update
as
DECLARE @bscsal float
declare @additional float
select @additional=addmoney from additional where addid in (select addid from inseted)
select @bscsal=basicsal from basicsalary where basicid in (select basicid from inserted)
update salary
set realsal=@additional+@bscsal
where addid in (select addid from inserted)
VI. 创建和绑定规则
(1) 创建规则：性别限定
create rule sex_rule
as @sex='男' or @sex='女'

(2) 创建规则：年龄限定
create rule age_rule
as @age<=99 and @age>=0

(3) 创建规则：工资限定
create rule salary_rule
as @salary>0

(4) 绑定规则
sp_bindrule sex_rule,'employee.sex'
sp_bindrule age_rule,'employee.age'
sp_bindrule salary_rule,'additional.addmoney'
sp_bindrule salary_rule,'basicsalary.basicsal'

4. 系统的调试和测试
I. 查看各表的基本数据(借助视图查询)
(1)查看员工信息：

查看员工信息

(2)查看工资信息：

查看工资信息
II. 检测各存储过程的功能

(1)验证存储过程：input_salary(插入基本工资信息)

存储过程：input_salary(插入基本工资信息)

(2)验证存储过程：input_employee(插入员工基本信息)

存储过程：input_employee(插入员工基本信息)
(3)验证存储过程：update_employee (修改员工基本信息)

存储过程：update_employee (修改员工基本信息)

(4)验证存储过程：update_salary(修改基本工资信息)

存储过程：update_salary(修改基本工资信息)
(5)验证存储过程：delete_info (指定员工号删除员工信息)

存储过程：delete_info (指定员工号删除员工信息)

七、 实验总结
通过本次实验，加深的了对关系数据库的原理及应用的认识：
1. 主键是多张二维表之间联系的关键。找对主键，能使表及存储过程，触发器的建立变得更加简单和规范。
2. 在建立表之前，应保证表至少符合第三范式，这样才不会出现数据的冗余。
3. 在删除表的同时，也相应删除了先前绑定的触发器。
4. 做好前期准备工作，能让建表等工作更容易。
5. 在需要做赋值等运算时，声明几个变量会使代码简单许多。
八、 心得体会
通过本次实验，加深了我对关系数据库原理的理解，锻炼了我的动手能力。在实验的3天时间里，我做出了一个简易的人力资源管理系统，这也让我额外了解了一些人力资源管理方面的知识，扩展了我的知识面，使我在学习完数据库的理论知识后付诸实践。理论和实际相结合，对数据库的使用更加熟练。