人事管理系统数据库设计说明书Oracle附带源码

1、人事管理系统数据库系统设计说明 (Oracle部分)计算机学院-王琪31306030092013-11-13文档信息：文档名称蓝山公司人事管理系统功能说明书（Oracle 部分）描述该文档描述人事管理系统的详细功能定义，并对模块划分、业务流程进行了定义。所有设计人员、开发人员、测试人员以及其他团队成员都应该以该文档作为产品的功能定义，并衍生出其他文档负责人状态最终版项目组成：测试主管开发主管项目经理文档变更历史：时间修改人章节描述所有章节创建文档初稿所有章节文档路径：相关文档：文档路径测试案例测试案例.doc详细设计详细设计.doc审核结果：审核人意见签名档全体团队通过测试团队通过目录1概述4

2、1.1目的41.2内容41.3与其他项目的关联42表42.1表设计概述42.2员工表（T_Employee）53视图63.1查看员工基本信息(Web)63.2查看员工考勤情况(Web)73.3查看员工信息和所属部门名称(Win)74存储过程84.1插入一条提交的请假申请(Web)84.2查询员工考勤信息(Win)95用户自定义函数95.1按部门名称得到本部门所有的员工信息96触发器106.1在否决员工请假申请时将请假时间累计返回到请假前的状态107数据库安全性107.1概述107.2数据库验证方式108数据库管理和设置118.1概述118.2数据库属性设置118.3数据库备份恢复策略118.4

3、数据库运行日常维护111 概述1.1 目的本文为教学案例项目Oracle功能规范说明书。本说明书将：l 描述数据库设计的目的l 说明数据库设计中的主要组成部分l 说明数据库设计中涵盖的教学知识要点1.2 内容本文档主要内容包括对数据库设计结构的总体描述，对数据库中各种对象的描述（包括对象的名称、对象的属性、对象和其他对象直接的关系）。本文档中包含对以下数据库内容的描述：l 数据表l 视图l 存储过程l 用户自定义函数l 触发器l 约束在数据库主要对象之外，本文还将描述数据库安全性设置、数据库属性设置和数据库备份策略，为数据库管理员维护数据库安全稳定地运行提供参考。1.3 与其他项目的关联教学案

4、例项目的数据库设计与教学项目（Web部分和Windows部分）功能密切相关。教学案例项目的数据库将按照教学项目程序部分的功能需求而设计，数据库设计将配合教学案例的程序部分，以实现一个功能完备的企业环境内的应用。2 表2.1 表设计概述根据教学案例功能，数据库将以员工信息为中心存储相关数据，配合 Oracle 数据库系统中提供的数据管理，实现员工考勤、请假、加班管理及系统设置等业务功能。数据库设计将以存储员工信息的员工表为基础，连接多张相关表以实现对以下关系的支持：l 员工与请假申请l 员工与加班申请l 员工与考勤记录l 员工与部门l 员工与部门经理l 员工与业绩评定记录l 员工与薪资此外数据库

5、中还将记录教学案例应用中需要的全局配置信息和事件日志记录。数据库系统主要的实体关系如2-0所示。图 202.2 员工表（t_emploee）概述员工表用于记录员工基本信息，并作为基础表与其他表连接。表定义员工表定义如表 21所示。表 21主键员工表的主键是empid字段，类型为number。外键员工表的外键有部门id，类型为number，用于与部门表中的部门id字段关联。部门id字段不可以为空。员工表的外键有员工类型id，类型为number，用于与员工类型表中的员工类型id字段关联。员工表的外键有领导id，类型为number，用于与员工表中的员工id字段关联。索引主键字段员工id具有自动创建的

6、聚集索引。2.3 部门表（t_department）员工表定义如表2-2所示。表 22主键部门表的主键为部门id。外键部门表的外键为部门父id，类型为number，引用部门表的部门id。索引主键字段员工id具有自动创建的聚集索引。2.4 员工类型表（empType）员工类型表定义如表2-3所示。表 23主键员工类型表的主键为员工类型id。2.5 请假表（t_leave）员工类型表定义如表2-4所示。表 24主键请假表的主键为请假id。外键请假表的外键有员工id，类型为number，用于与员工表中的员工id字段关联。请假表的外键有领导id，类型为number，用于与员工表中的员工id字段关联。索

7、引主键字段请假id具有自动创建的聚集索引。2.6 业绩表（result）业绩表定义如表2-5所示。表 2-5主键业绩表的主键为业绩表id。外键请假表的外键有员工id，类型为number，用于与员工表中的员工id字段关联。索引主键字段业绩表id具有自动创建的聚集索引。2.7 加班表（overTimeWork）业绩表定义如表2-6所示。表 2-6主键加班表的主键为加班表id。外键加班表的外键有员工id，类型为number，用于与员工表中的员工id字段关联。加班表的外键有审核人员id，类型为number，用于与员工表中的员工id字段关联。索引主键字段加班表id具有自动创建的聚集索引。2.8 考情表（

8、attendance）业绩表定义如表2-7所示。表 2-7主键考情表的主键为考情表id。外键加班表的外键有员工id，类型为number，用于与员工表中的员工id字段关联。索引主键字段考情表id具有自动创建的聚集索引。字段员工id有聚集索引。2.9 薪资表（salary）业绩表定义如表2-8所示。表 2-8主键薪资表的主键为薪资表id。外键薪资表的外键有员工id，类型为number，用于与员工表中的员工id字段关联。索引主键字段薪资表id具有自动创建的聚集索引。3 视图3.1 查看员工基本信息(Web)概述l 此视图通过左外连接员工和部门表得到了员工的详细信息，其中包括员工的基本信息、员工的部门

9、信息和员工经理信息。l Web用户具有查看的权限。基表l 员工表l 部门表视图包含字段l 员工编号l 员工姓名l 员工电子邮件l 员工电话l 员工登录名l 员工报到日期l 员工所属部门编号l 员工自我介绍l 员工照片l 员工剩余假期l 所属部门名称表：员工表，部门表drop view wq_emploeeInfo; create view wq_emploeeInfo(empid,name,mail,phone,comeTime,depid,selfComent,photo,dname,leftLeave) asselect e.empid , ,e.mail,e.phone,eT

10、ime,d.depid,e.selfComent, e.photo,,(e.yearLeaveTime - e.useLeaveTime)from t_wq_emploee e, t_wq_department dwhere e.depid = d.depid;概述l 此视图通过员工编号内连接员工考勤表和员工表，得到员工的姓名、员工所属部门编号和考勤情况。通过这个视图可以按部门编号查到整个部门员工的缺勤情况。l Web用户具有查看的权限。基表l 员工考勤表l 员工表视图包含字段l 考勤编号l 员工编号l 部门编号l 员工姓名l 考勤类型l 到达时间l 日期drop view wq_

11、emp_attendance;create view wq_emp_attendance(aid,empid,depid,name,type,workTime,atime)as select a.id,e.empid,e.depid,,a.type,a.workTime,sysdate from t_wq_emploee e,t_wq_attendance awhere e.empid=a.empid;3.2 查看员工信息和所属部门名称(Win)概述l 此视图通过内连接表员工表和部门表，得到员工的详细信息和员工所属部门的名称。l Windows用户具有查看的权限。基表l 员工表l

12、部门表视图包含字段l 员工编号l 员工姓名l 员工登录名l 员工电子邮件l 部门名称l 员工所属部门编号l 员工电话drop view wq_emp_dep_info;create view wq_emp_dep_info(empid,emp_name,username,mail,dep_name,depid,photo)as select e.empid,,e.username,e.mail,,d.depid,e.photofrom t_wq_emploee e,t_wq_department dwhere e.depid=d.depid;4 存储过程4.1 插入一条

13、提交的请假申请(Web)概述l 此存储过程向员工请假表插入一条已提交的请假申请。l Web用户具有执行权限。参数字段名称字段代码类型员工编号 p_empid整型提交时间 p_applyTime日期型开始时间 p_leavetime日期型结束时间 p_backtime日期型审核者编号 p_checkEmpid整型小时数 p_realtime整型请假原因字符串(100)返回值字段类型描述本操作影响的记录数整型系统自带的一个参数，返回本次操作影响的记录数create or replace procedure wq_addNewleave( p_empid t_wq_leave.empid%TYPE,

14、 p_applyTime t_wq_leave.applyTime%TYPE, p_leavetime t_wq_leave.leavetime%TYPE, p_backtime t_wq_leave.backtime%TYPE, p_checkEmpid t_wq_leave.checkEmpid%TYPE, p_realtime t_wq_leave.realtime%TYPE, p_reason t_wq_leave.reason%TYPE)isbegin insert into t_wq_leave(empid,applyTime,leavetime,backtime,checkEmp

15、id,realtime,reason) values(p_empid,p_applyTime,p_leavetime,p_backtime,p_checkEmpid,p_realtime,p_reason);end wq_addNewleave;4.2 查询员工考勤信息(Win)概述l 此存储过程根据指定的时间段查询员工考勤信息。l Windows用户具有执行权限。参数字段名称字段代码类型开始时间p_workTime日期型结束时间p_afterWorkTime日期型返回值字段类型描述 p_record p_record数据集一个包含按指定查询条件查询得到的员工考勤信息的数据集create or

16、 replace procedure wq_select_attendance( p_workTime t_wq_attendance.workTime%TYPE, p_afterWorkTime t_wq_attendance.afterWorkTime%TYPE, p_record out t_wq_attendance%ROWTYPE)isbeginselect * into p_record from t_wq_attendance a where a.workTime> p_workTime and a.afterWorkTime< p_afterWorkTime;end

17、 wq_select_attendance;5 用户自定义函数5.1 按部门名称得到本部门所有的员工信息概述此用户自定义函数根据部门名称从视图 viwEmployeeList 中得到本部门所有员工的信息。参数字段名称字段代码类型部门名称 p_name字符串(10)返回值字段类型描述员工信息表p_record一个包含指定部门的所有员工信息的数据表create or replace function wq_getEMP_By_depname( p_name t_wq_%TYPE, p_record out t_wq_emploee%ROWTYPE) return t_

18、wq_emploee%ROWTYPEisbegin select * into p_record from t_wq_emploee e where e.depid=(select depid from t_wq_department d where =p_name);end wq_getEMP_By_depid;6 触发器6.1 在否决员工请假申请时将请假时间累计返回到请假前的状态概述员工请假申请被否决时，之前其年假的总时间已经被减少，否决完成后应该相应增加其年假总时间。触发器影响范围员工信息表。/*触发器 员工请假申请被否决时，之前其年假的总时间已经被减少，否决完成后应该相应增

19、加其年假总时间*/create or replace trigger wq_back_before_leaveafter update on t_wq_leave FOR EACH ROWbeginif(:new.status<1)then update t_wq_emploee e set e.useLeaveTime=e.useLeaveTime-:new.realtime where e.empid=:new.empid;end if;end;7 数据库安全性7.1 概述基于项目特点，教学案例项目的安全控制主要由程序代码控制。Oracle 端不特殊地设定安全设置和检查。项目的用户身

20、份验证、用户授权管理都由应用程序代码实现。7.2 数据库验证方式应用程序端连接数据库时使用的数据库验证方式为Oracle验证。数据库连接串将加密存储在系统配置文件中。8 数据库管理和设置8.1 概述本节内容与数据库功能无关，将从教学案例系统的数据库运行角度出发，对本数据库的属性设置和备份恢复策略提出指导性说明。8.2 数据库属性设置8.3 数据库备份恢复策略数据库的大小和数据修改的频率决定了采用何种数据库备份恢复策略。如果数据库较小或修改的频度非常低，则可以只实施完全数据库备份，不过此时要定期清理数据库的事务日志，防止数据库的事务日志被填满。其他情况则需要使用差异备份。由于本案例教学涉及的数据

21、库规模较小，建议每周做一次完全备份，中间间隔两天做一次差异备份。8.4 数据库运行日常维护Oracle 的一个优势就是对日常维护的要求比较低，但为了追求更高的可用性和安全性，建议每周做备份，同时注意将备份放在不同的备份媒体中，如多个硬盘。/*=*/* DBMS name: ORACLE Version 10g */* Created on: 2013/11/13 14:21:36 */*=*/alter table t_wq_attendance drop constraint FK_T_WQ_ATT_REFERENCE_T_WQ_EMP;alter table t_wq_department

22、 drop constraint FK_T_WQ_DEP_PK_PID213_T_WQ_DEP;alter table t_wq_emploee drop constraint FK_T_WQ_EMP_PK_DEPID3_T_WQ_DEP;alter table t_wq_emploee drop constraint FK_T_WQ_EMP_PK_PID213_T_WQ_EMP;alter table t_wq_emploee drop constraint FK_T_WQ_EMP_REFERENCE_T_WQ_EMP;alter table t_wq_leave drop constrai

23、nt FK_T_WQ_LEA_REFERENCE_T_WQ_DEP;alter table t_wq_leave drop constraint FK_t_wq_leave_1;alter table t_wq_leave drop constraint FK_t_wq_leave_2;alter table t_wq_overTimeWork drop constraint FK_T_OVERTI_4;alter table t_wq_overTimeWork drop constraint FK_T_OVERTI_3;alter table t_wq_result drop constra

24、int FK_T_WQ_RES_REFERENCE_T_WQ_EMP;alter table t_wq_salary drop constraint FK_T_WQ_SAL_REFERENCE_T_WQ_EMP;drop table t_wq_attendance cascade constraints;drop table t_wq_department cascade constraints;drop table t_wq_emploee cascade constraints;drop table t_wq_empoleeType cascade constraints;drop tab

25、le t_wq_leave cascade constraints;drop table t_wq_overTimeWork cascade constraints;drop table t_wq_result cascade constraints;drop table t_wq_salary cascade constraints;/*=*/* Table: t_wq_attendance */*=*/create table t_wq_attendance ( id number(10) not null, empid number(10), workTime date, afterWo

26、rkTime date, type varchar2(20), atime date, constraint PK_T_WQ_ATTENDANCE primary key (id);/*=*/* Table: t_wq_department */*=*/create table t_wq_department ( depid number(10) not null, p_did number(10), name varchar2(50), "desc" varchar2(255), constraint PK_T_WQ_DEPARTMENT primary key (dep

27、id);comment on table t_wq_department is'部门表'/*=*/* Table: t_wq_emploee */*=*/create table t_wq_emploee ( empid number(10) not null, p_empid number(10), depid number(10), typeid number(5), name varchar2(50), mail varchar2(50), sex number(1), birthday date, username varchar2(50), password varc

28、har2(50), photo blob, useLeaveTime number(3), yearLeaveTime number(3), phone varchar2(20), comeTime date, selfComent clob, constraint PK_T_WQ_EMPLOEE primary key (empid);comment on table t_wq_emploee is'员工信息'/*=*/* Table: t_wq_empoleeType */*=*/create table t_wq_empoleeType ( typeid number(5

29、) not null, type number(5), typename varchar2(20), constraint PK_T_WQ_EMPOLEETYPE primary key (typeid);/*=*/* Table: t_wq_leave */*=*/create table t_wq_leave ( id number(20) not null, empid number(10), checkEmpid number(10), depid number(10), leavetime date, backtime date, status number(1), reason c

30、lob, realtime number(3), applyTime date, constraint PK_T_WQ_LEAVE primary key (id);/*=*/* Table: t_wq_overTimeWork */*=*/create table t_wq_overTimeWork ( id number(10) not null, empid number(10), checkPersonId number(10), applyTime DATE, overWorkTime DATE, afterOverWorkTime date, status number(1), v

31、alueTime number(5), convertType number(1), workTimeReason BLOB, constraint PK_T_WQ_OVERTIMEWORK primary key (id);/*=*/* Table: t_wq_result */*=*/create table t_wq_result ( id number(10) not null, empid number(10), jobSummary CLOB, preCompletion CLOB, selfScore number(3), nextStage clob, resultScore

32、number(3), lastResultScore number(3), commitTime date, startTime date, overTime date, constraint PK_T_WQ_RESULT primary key (id);/*=*/* Table: t_wq_salary */*=*/create table t_wq_salary ( id number(5) not null, empid number(10), basicSlary number(5), overWorkSlary number(5), lateSlary number(5), 缺勤扣

33、薪 number(5), startTime date, overTime date, sumtime date, constraint PK_T_WQ_SALARY primary key (id);alter table t_wq_attendance add constraint FK_T_WQ_ATT_REFERENCE_T_WQ_EMP foreign key (empid) references t_wq_emploee (empid);alter table t_wq_department add constraint FK_T_WQ_DEP_PK_PID213_T_WQ_DEP

34、 foreign key (p_did) references t_wq_department (depid);alter table t_wq_emploee add constraint FK_T_WQ_EMP_PK_DEPID3_T_WQ_DEP foreign key (depid) references t_wq_department (depid);alter table t_wq_emploee add constraint FK_T_WQ_EMP_PK_PID213_T_WQ_EMP foreign key (p_empid) references t_wq_emploee (

35、empid);alter table t_wq_emploee add constraint FK_T_WQ_EMP_REFERENCE_T_WQ_EMP foreign key (typeid) references t_wq_empoleeType (typeid);alter table t_wq_leave add constraint FK_T_WQ_LEA_REFERENCE_T_WQ_DEP foreign key (depid) references t_wq_department (depid);alter table t_wq_leave add constraint FK

36、_t_wq_leave_1 foreign key (empid) references t_wq_emploee (empid);alter table t_wq_leave add constraint FK_t_wq_leave_2 foreign key (checkEmpid) references t_wq_emploee (empid);alter table t_wq_overTimeWork add constraint FK_T_OVERTI_4 foreign key (checkPersonId) references t_wq_emploee (empid);alte

37、r table t_wq_overTimeWork add constraint FK_T_OVERTI_3 foreign key (empid) references t_wq_emploee (empid);alter table t_wq_result add constraint FK_T_WQ_RES_REFERENCE_T_WQ_EMP foreign key (empid) references t_wq_emploee (empid);alter table t_wq_salary add constraint FK_T_WQ_SAL_REFERENCE_T_WQ_EMP f

38、oreign key (empid) references t_wq_emploee (empid);/*员工信息视图*/ /*员工编号*/*员工姓名*/*员工电子邮件*/*员工电话*/*员工登录名*/*员工报到日期*/*员工所属部门编号*/*员工自我介绍*/*员工照片*/*员工剩余假期*/*所属部门名称*/drop view wq_emploeeInfo; create view wq_emploeeInfo(empid,name,mail,phone,comeTime,depid,selfComent,photo,dname,leftLeave) asselect e.empid ,e.n

39、ame ,e.mail,e.phone,eTime,d.depid,e.selfComent, e.photo,,(e.yearLeaveTime - e.useLeaveTime)from t_wq_emploee e, t_wq_department dwhere e.depid = d.depid;/* 通过这个视图可以按部门编号查到整个部门员工的缺勤情况考勤编号 员工编号 部门编号 员工姓名 考勤类型 到达时间 日期 表：t_emploee，t_wq_attendance，t_department*/drop view wq_emp_attendance;create vi

40、ew wq_emp_attendance(aid,empid,depid,name,type,workTime,atime)as select a.id,e.empid,e.depid,,a.type,a.workTime,sysdate from t_wq_emploee e,t_wq_attendance awhere e.empid=a.empid;/*员工的详细信息和员工所属部门的名称*/*员工编号*/*员工姓名*/*员工登录名*/*员工电子邮件*/*部门名称*/*员工所属部门编号*/*员工电话*/*表：t_wq_emploee,t_wq_department*/drop

41、view wq_emp_dep_info;create view wq_emp_dep_info(empid,emp_name,username,mail,dep_name,depid,photo)as select e.empid,,e.username,e.mail,,d.depid,e.photofrom t_wq_emploee e,t_wq_department dwhere e.depid=d.depid;/*创建存储过程*/*插入一条提交的请假申请*/*员工编号*/*提交时间*/*开始时间*/*结束时间*/*审核者编号*/*小时数*/*请假原因*/*返回:本操作影响的记录数*/create or replace procedure wq_addNewleave( p_empid t_wq_leave.empid%TYPE, p_applyTime t_wq_leave.applyTime%TYPE, p_leavetime t_wq_