一、数据库分析和设计 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;