数据库—人力资源管理系统

人力资源管理系统数据库设计与实现

所完成的工作：数据库整体结构设计

需求分析

ER图绘制

完成数据库的物理设计

SQL语句编写和上机检验

系统名称：人力资源管理系统

、系统需求分析

1、该“人力资源管理系统”只是对数据库应用技术的一个样本数据库的实例，重在对数

据库一些方法的熟悉与掌握，其中包括从数据库的概念模型到ER图的绘制，再到数据库模式的建立（即为数据神农百草膏第三代库及其基本表的建立），而后数据的录入, 最后完成一些简单的关系运算表达式的表达和相关的Select查询语句的使用。

2、系统结构简述

本系统包括6个实体：总经理、经理、员工、部门、指标、工资

实体之间的关系转换为5个表：职务表、部门表、员工表、考核表、工资表

其中，实体部分有3个表：员工表、部门表、工资表

员工与指标间多对多关系转换为考核表

注：各个基本表中关键字已在关系模式中说明

3、实现的功能

1）通过表的创建过程进行数据的关联和约束

创建多个约束、规则、默认值对数据进行安全性管理（详见物理设计）

创建检查约束（用到case- when语句分情况），检验员工编号的合理性，即通过编号可以体

现他的部门（A为总经理，B为经理，C为员工）

2）插入、修改、删除功能

本系统有5个表：员工表、部门表、职务表、考核表、工资表。其中员工表、部门表、职务表

是基本表，数据直接、完全输入。考核表、工资表与员工表有外键联系，且有计算列，需要创

建存储过程来插入数据。

◎建立2个存储过程，进行考核表、工资表的插入

◎建立一个存储过程，考虑外键联系，删除某员工的全部信息

◎建立触发器，当添加、删除数据时会显示

3 ）查询功能

◎使用select语句实现简单查询和复杂查询

并创建存储过程进行查询

◎创建视图，通过视图来查询

注：总经理与经理的属性ER图与员工相同

员工的职务属性通过员工编号体现，规则见“职务表”

三、关系模式

系统中的实体有：总经理、经理、员工、部门、指标、工资

为简化关系模式，将总经理、经理、员工的信息集中到一个表。关系模式如下：

职务表（职务名称、职务编号、基本工资、基本奖金）

部门表（部门编号、部门名称、经理编号、办公电话）

员工表（员工编号、员工姓名、部白兔牌暗疮膏门编号、职务、出生日期、年龄、入职日期、性别、学历、电话）

考核表（员工编号、本月出勤次、本月业绩、本月考核）

工资表（员工编号、基本工资、基本奖金、计算工资、所得税、本月实发工资）

四、物理设计

说明：职务表为基本表，直接输入数据。其中，职务编号、、分别为总经理、经理、员工的员工编号的首字母（如，经理B01，员工C03）

说明：1、员工表与部门表有外键联系，部门编号是部门表的主键；直接输入数据

2、员工编号是主键，并有检查约束，确认输入的员工编号合理。具体是员工编号的

首字母与职务对应的编号一致，如，总经理编号为AXX，经理

BXX，员工CXX

3、职务与规则“职务_rule”绑定，取值范围（“总经理” “'经理” “员工”）

4、公式：年龄=year（getdate（））-year（出生日期）

5、性别与规则“性别_rule”绑定，取值范围（“男” “'女”）

6、学历与规则“学历_rule”绑定，取值范围（“博士” “ '硕士” “学士”），默认值

学士

说明：1、考核表与员工表有外键联系；用表约束定义联合主键

2、本月考核为计算列，公式本月考核=本月业绩*本月出勤次。创建存储过程

“ insert考核表”实现输入数据，

3、本月出勤次与默认“ default本月出勤次”绑定，默认值为30

说明：1、考核表与员工表有外键联系；用表约束定义联合主键

2、后三列为计算列，公式：

计算工资=基本工资+本月考核*基本奖金/50

所得税=计算工资*0.05

本月实发工资=计算工资-所得税其中，本月考核是考核表中该员工对应的考核创建存储

过程“ insert工资表”实现输入数据

五、系统实现

--创建数据库“人力资源管理系统DB"

create database 人力资源管理系统DB

on (name =人力资源管理系统DB_dat ,

file name = 人力资源管理系统DB_dat.mdf'

size =5120 kb ,

maxsize = kb ,

filegrowth =1024 kb

log on

( name = ' 人力资源管理系统DB_log' ,

filename = 人力资源管理系统DB_log.ldf' size =1024 kb ,

maxsize =kb ,

filegrowth =1024 kb

)

use 人力资源管理系统DB

-- 创建表

create table 职务表

( 职务名称char ( 6) not null primary key 职务编号char ( 2) not null,

基本工资int ,

int ,

基本奖金

)

create table 部门表

( 部门编号char ( 2) not null primary key 部门名称char ( 6) not null,

经理编号char ( 4),

char ( 12)

经理电话

)

create table 员工表

( 员工编号char ( 4) not null primary key 员工姓名char ( 6) not null,

部门编号char ( 2),

职务char ( 6),

性别char (2) not null,

出生日期datetime not null,

年龄int ,

入职日期datetime not null,

学历char ( 4),

电话char ( 12 ),

constraint fk_ 员工表_ 部门表_key

foreign key ( 部门编号) references 部门表( 部门编号), constraint fk_ 员工表_ 职务表_key

foreign key ( 职务) references 职务表( 职务名称)

)

create table 考核表

( 员工编号char ( 4) not null,

本月出勤次 int not null, 本月业绩 int not null, 本月考核 int ,

constraint pk_ 考核表 _key primary key ( 员工编号 , 本月考核 ), constraint fk_ 员工表 _ 考核表 _key foreign key ( 员工编号 ) references 员工表 ( 员工编号 )

)

-- 为员工表建立检查约束，保证输入的员工编号合理 alter table 员工表 with nocheck add constraint ck_ 员工编号 check

（ substring （ 员工编号 , 1, 1）= case

when 职务 =' 总经理 ' then 'A' when 职务 =' 经理 ' then 'B' when 职务 =' 员工 ' then 'C' end

）

go

-- 建立职务规则、学历规则、性别规则，并与员工表绑定 create

rule 职务 _rule

AS @职务 in （ ' 总经理 ' , ' 经理 ' ,' 员工')

go

exec sp_bindrule

职务 _rule ,' 员工表.职务'

go

create

rule 学历

rule

AS @学历 in （ ' 博士 ' ,' 硕士' ,

' 学士 ' , ' 大专 ' )

go

exec sp_bindrule

学历 _rule ,' 员工表.学历'

go

create rule 性别 _rule AS @性别 in( ' 男 ' , ' 女 ' )

go

go -- 建立学历默认值约束，与员工表对应列绑定 create default 学历 _defa AS' 学士 ' go

exec sp_bindefault 学历 _defa , ' 员工表 . 学历 ' go

-- 建立本月缺勤次默认值约束，与考核表对影列绑定 create default 本月出勤次 _defa AS'31' go exec sp_bindefault 本月出勤次 _defa , ' 考核表 . 本月出勤次 go

create table

员工编号 char ( 4) not 基本工资 int not null, 基本奖金 int not null,

计算工资 int 1

所得税 int ,

本月实发工资

int ,

constraint

pk_ 工资表 constraint

fk_ 员工表

foreign key （ 员工编号

key （ 员工编号 , 本月实发工资 ）,

员工表 （ 员工编号 ）

工资表

null,

(

_key primary _

工资表 _key ) references

insert 员工表 values ( 'C09' , ' 陈某 ' , 'D3' , ' 员工 ' , ' 女 ' , '' , '20' , '' , ' 硕士 ' , '' ) -- 查看员工表、职务表、部门表数据 select * from 员工表 select * from 职务表 select * from

部门表

go

-- 创建存储过程，用于输入考核表、工资表的数据

create procedure insert 考核表 @员工编号 char （ 4）, @本月出勤次 int , @本月业绩 int as

insert 考核表 values （ @员工编号 , @本月出勤次 , @本月业绩 , @本月业绩 - 5 * @本月出勤次 ） go create procedure insert 工资表 @员工编号 char （ 4）, @基本工资 int , @基本奖金 int as begin

insert 职务表 values ( ' 总经理 ' , 'A' ,

'50000'

, '5000' )

insert 职务表 values ( ' 经理 ' , 'B' , '10000' , '5000' )

insert 职务表 values (' 员工' , 'C' , '3000' , '1000' )

insert 部门表 values ( 'D1' , ' 销售部 ' , 'B01' , '' )

insert 部门表 values ( 'D2' , ' 采购部 ' , 'B02' , '' )

insert 部门表 values ( 'D3' , '

人事部 ' , 'B03'

, '' )

insert 部门表

values

( 'D' , ' 总经理 ' , 'A01' , '' )

insert 员工表 values ( 'A01' , '***' , >

'D' , ' 总经理 ' , ' 女 ' , I! I!

1

> > '' , ' 硕士 ' , insert

员工表 values ( 'B01' , '' , 'D1' ,' 经理' , ' 女' , '' , I! I!

> > ' 博士 ' , '' ) insert

员工表 values

( 'B02' , '' , 'D2'

,' 经理' , ' 女' , '' ,

'19' , '' , ' 硕士' ,'' insert ' , '' ) insert ' , '' ) insert ' , '' ) insert ' , '' ) insert ' , '' ) insert ' , '' ) insert ' , '' ) insert

员工表

values

( 'B03' , ' 赵某 ' , 'D3' , ' 经理 ' , ' 男 ' , '' , '21' , '' , ' 博士 员工表 values

( 'C01' ,

' 钱某 ' , 'D1' , ' 员工 ' , ' 女 ' ,

'' , '22'

, '' , ' 学士

员工表 values ( 'C02' , ' 孙某 ' , 'D1' , ' 员工 ' , ' 男 ' , '' , '21' , '' , ' 硕士

员工表 values ( 'C03' , ' 李某 ' , 'D1' , ' 员工 ' , ' 男 ' , '' , '24' , '' , ' 学士

员工表 values ( 'C04' , ' 周某 ' , 'D1' , ' 员工 ' , ' 男 ' , '' , '26' , '' , ' 大专

员工表 values ( 'C05' , ' 吴某 ' , 'D2' , ' 员工 ' , ' 女 ' , '' , '19' , '' , ' 学士

员工表 values ( 'C06' , ' 郑某 ' , 'D2' , ' 员工 ' , ' 男 ' , '' , '20' , '' , ' 硕士

员工表 values

( 'C07' , ' 王某 ' , 'D2' , ' 员工 ' , ' 男 ' ,

'' , '25' , '' , ' 博士

' , '5' )

insert 员工表 values ( 'C08' ,

' 冯某 ' , 'D3' , ' 员工 ' , ' 女 ' ,

'' , '29'

, '' , ' 博士

-- 输入员工表、职务表、部门表数据 , '' )

, '' ) )

declare @本月考核int

set @本月考核=（ select 本月考核from 考核表where 员工编号=@员工编号） insert 工资表values （ @员工编号, @基本工资, @基本奖金,

@基本工资+@本月考核* @基本奖金/ 50,

（ @基本工资+@本月考核* @基本奖金/ 50）* 0.05 ,

（ @基本工资+@本月考核* @基本奖金/ 50）* 0.95 ） end go

-- 输入考核表、工资表数据

exec insert 考核表A01 , 31, 95

exec insert 考核表B01 , 31, 95

exec insert 考核表B02 , 30, 80

exec insert 考核表B03 , 31, 55

exec insert 考核表C01, 31, 100

exec insert 考核表C02, 27, 95

exec insert 考核表C03, 31, 95

exec insert 考核表C04, 31, 85

exec insert 考核表C05, 29, 75

exec insert 考核表C06, 31, 95

exec insert 考核表C07, 28, 80

exec insert 考核表C08, 31, 30

exec insert 考核表C09, 29, 70

exec insert 工资表A01 , 50000 , 5000

exec insert 工资表B01 , 10000 , 5000

exec insert 工资表B02 , 10000 , 5000

exec insert 工资表B03 , 10000 , 5000

exec insert 工资表C01, 3000 , 1000

exec insert 工资表C02, 3000 , 1000

exec insert 工资表C03, 3000 , 1000

exec insert 工资表C04, 3000 , 1000

exec insert 工资表C05, 3000 , 1000

exec insert 工资表C06, 3000 , 1000

exec insert 工资表C07, 3000 , 1000

exec insert 工资表C08, 3000 , 1000

exec insert 工资表C09, 3000 , 1000

-- 查看考核表、工资表数据select * from 考核表

select * from 工资表

go

-- 创建存储函数删除某一员工的信息

create procedure 删除员工@员工编号char ( 4)

as

begin

delete 考核表where 员工编号=@员工编号

delete 工资表where 员工编号=@员工编号

delete 员工表where 员工编号=@员工编号

end

go

-- 创建触发器

create trigger tr_delete on 员工表for delete

as

declare @num varchar ( 50)

select @num=str ( @@rowcount ) +' 个学生被删除select @num

return

go

create trigger tr_insert on 员工表for insert

as

declare @num varchar ( 50)

select @num=str ( @@rowcount ) +' 个学生被添加select @num return

go

-- 检验触发器

exec 删除员工A01

go -- 删除数

据，

检验触发器

insert 员工表values ( 'A01' , '' , 'D' , ' 总经理' , ' 女' , '' , '20' , '' ' 硕士' , '' )

go -- 恢复数据

-- 创建索引

create nonclustered index 员工编号_ idx on 员工表（员工编号）

-- 创建存储过程进行简单查询和复杂查询

-- 查询员工业绩，按业绩降序排列

create procedure 查询员工业绩降序排列AS

select 员工表. 员工编号, 员工表. 员工姓名, 考核表. 本月业绩

from 员工表inner join 考核表on 员工表. 员工编号= 考核表. 员工编号order by 考核表. 本月业绩

desc

exec 查询员工业绩降序排列

go -- 查询所有的研究生名单

create procedure 查询所有研究生名单按部门分组AS

select 部门表. 部门名称, 员工表. 员工姓名, 员工表. 学历

from 员工表inner join 部门表on 员工表. 部门编号= 部门表. 部门编号where 员工表.学历=' 博士' or 员工表.学历=' 硕士'

exec 查询所有研究生名单按部门分组

go -- 建立视图进行查询

create view 查询员工所有个人信息AS

select 员工表. 员工编号, 员工表. 员工姓名, 部门表. 部门名称, 员工表. 职务, 员工表. 性别, 员工表. 出生日期, 员工表. 年龄, 员工表. 入职日期, 员工表. 学历, 员工表. 电话, from 部门表inner join 员工表on 员工表. 部门编号= 部门表. 部门编号

create procedure 查询员工所有个人信息_proc AS select * from 查询员工所有个人信息

exec 查询员工所有个人信息_proc go