人力资源管理系统中数据库的设计和实现

一、数据库分析和设计

1、 数据库分析

人力资源管理系统主要实现对企业员工等8项管理。员工管理实现对部门信息的添加、修改、删除、查询以及统计等。

2、 数据库概念结构设计

通过对人力资源管理系统中数据及数据处理过程的分析，抽象出员工（EMPLOYEES ）等9个实体，画出ER 图，包括实体、实体的属性和关系等。

3、 数据库逻辑结构设计

表结构设计：根据人力资源管理系统ER 图，设计出9个关系表，其中针对EMPLOYEES 表，设计出字段名、数据类型、长度、约束和说明。包括employee_id、first_name、last_name等9个字段，其中字段名employee_id，数据类型NUMBER ，长度6，约束PRIMARY KEY，说明职位编号。

序列的设计：创建3个序列，其中EMPLOYEES_SEQ用于产生员工编号，起始值为100，步长为1，不缓存，不循环。

索引的设计：创建10个索引，其中在EMPLOYEES 表的DEPARTMENT_ID列上创建名为EMP_DEPARTMENT_INDX的平衡树索引，在LAST_NAME和FIRST_NAME列上创建名为EMP_NAME_INDX的复合索引。

视图的设计：创建2个视图，其中EMP_BASE_INFO_VIEW视图用于员工信息，包括员工号、员工名、工资和部门号。

存储过程的设计：创建5个存储过程，其中PROC_SHOW_EMP以部门编号为参数，查询并返回该部门平均工资，以及该部门中比该部门平均工资高的员工信息。

函数的设计：创建3个函数，其中FUNC_EMP_SALARY以员工编号为参数，返回员工的工资。

触发器设计：设计5个触发器，其中TRG_SECURE_EMP保证非工作时间禁止对EMPLOYEES 表进行DML 操作。

二、数据库的创建和客户端的连接

安装Oracle 11g 数据库服务器，包括实例human 和数据库human_resource，选择服务器类、单实例数据库、高级安装、企业版、一般用途/事物处理、具有示例方案的数据库、使用Database Control管理数据库、文件系统、启用自动备份和对所有账户使用相同的口令tiger 或admin123。

安装Oracle 11g客户机，选择运行时。通过网络配置助手ONCA 配置远程数据库的本地网络服务名。

三、数据库的实现

在human_resource数据库中创建一个名为ehr 的用户。以该用户登录数据库并创建各种数据库对象，这些数据库对象都将成为ehr 模式的对象。

首先需要给ehr 用户授权：

CONNECT sys/admin123@human_resource AS SYSDBA

CREATE USER ehr IDENTIFIDE BY ehrDEFAULT TABLESPACE users;

GRANT CONNECT,RESOURCE,CREATE VIEW TO ehr;

创建过程是在sql plus中执行以下sql 语句或pl/sql程序。

1、 表的创建

CREATE TABLE employees(

employee_id NUMBER(6) PRIMARY KEY,

first_nmaeVARCHAR2(20),

last_nameVARCHAR2(25) NOT NULL,

email VARCHAR2(25) NOT NULL UNIQUE,

phone_numberVARCHAR2(20),

hire_date DATE NOT NULL,

job_idVARCHAR2(10)NOT NULL,

salary NUMBER(8,2) CHECK(salary>0),

commission_pct NUMBER(2,2),

manager_id NUMBER(6),

department_id NUMBER(4),

)

TABLESPACE USERS ;

说明：省略了REFERENCES jobs(job_id)和departments(department_id)。

2、 序列的创建

CREATE SEQUENCE employees_seq START WITH 100 INCREMENT BY 1 NOCACHE NOCYCLE

3、CREATE INDEX emp_department_indx ON employees(department_id) TABLESPACE indx; CREATE INDEX emp_name_indx ON employees(last_name,first_name) TABLESPACE indx;

4、视图的创建

CREATE VIEW emp_base_info_view(empno,fname,lname,sal,deptno)

AS

SELECT employee_id,first_name,last_name,salary,department_id

FROM employees;

5、 PL/SQL程序开发-存储过程

CREATE OR REPLACE PROCEDURE proc_show_emp

( p_deptnoemployees.department_id%TYPE

)

AS

v_salemployees.salary%TYPE;

BEGIN

SELECT salary INTO v_sal FROM employees WHERE department_id=p_deptno;

--保证例外正确

SELECT avg(salary) INTO v_sal FROM employees WHERE department_id=p_deptno; DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary is: '||v_sal);

FOR v_emp IN (

SELECT * FROM employees WHERE department_id= p_deptno AND salary>v_sal)

LOOP

DBMS_OUTPUT.PUT_LINE(v_emp.employee_id||' '||v_emp.first_name||' '||v_emp.last_name);

END LOOP;

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.PUT_LINE ('The department doesn''t exists!');

ENDproc_show_emp;

6、PL/SQL程序开发-函数的创建

CREATE OR REPLACE FUNCTION func_emp_salary (

p_empnoemployees.employee_id%TYPE)

RETURNemployees.salary%TYPE

AS

v_salemployees.salary% TYPE;

BEGIN

SELECT salary INTO v_salFROM employees WHEREemployee_id= p_empno;

RETURNv_sal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20000,'There is not such an employee!');

END func_emp_salary;

7、PL/SQL程序开发-触发器的创建

CREATE OR REPLACE TRIGGER trg_secure_emp

BEFORE INSERT OR UPDATE OR DELETE ON employees

BEGIN

IF TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ‘8:00’ AND ’18:00’

OR TO_CHAR(YSDATE,’DY ’, ’NLS_DATE_LANGUAGE=AMERICAN’) IN (‘SAT ’, ’SUN ’)

THEN

RAISE_APPLICATION_ERROR(-20005,’只能在正常的工作时间内进行改变。’);

END IF;

END trg_secure_emp;

四、向表中插入初始数据

利用子查询插入数据的方式，将hr 模式下的表中数据复制到ehr 模式下的相应表中。

1、解锁hr 账户

ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;

2、以system 用户连接数据库

CONN system/admin123@human_resource

3、 插入初始数据

INSERT INTO ehr.employees SELECT * FROM hr.employees;