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

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