pmsys.sql

1.员工添加 2.人事调动 3.在职人员管理 4.部门（分厂）管理

/*==============================================================*/
/* DBMS name:      ORACLE Version 9i2                           */
/* Created on:     2007-10-30 10:06:48                          */
/*==============================================================*/


alter table EMPLOYEES
   drop constraint FK_EMPLOYEE_EMP_DEP_DEPARTME;

alter table EMPLOYEES
   drop constraint FK_EMPLOYEE_EMP_NAT_NATIONS;

alter table EMPLOYEES
   drop constraint FK_EMPLOYEE_EMP_STATE_STATES;

alter table EMPLOYEES
   drop constraint FK_EMPLOYEE_EMP_XULI_XUELIS;

alter table  emp_zhich 
   drop constraint FK_EMP_ZHIC_EMP_ZHICH_EMPLOYEE;

alter table  emp_zhich 
   drop constraint FK_EMP_ZHIC_EMP_ZHICH_ZHICHENG;

alter table  emp_zhiwu 
   drop constraint FK_EMP_ZHIW_EMP_ZHIWU_EMPLOYEE;

alter table  emp_zhiwu 
   drop constraint FK_EMP_ZHIW_EMP_ZHIWU_ZHIWUS;

drop index  emp_dep_FK ;

drop index  emp_nat_FK ;

drop index  emp_state_FK ;

drop index  emp_xuli_FK ;

drop index  emp_zhich_FK ;

drop index  emp_zhich_FK2 ;

drop index  emp_zhiwu_FK ;

drop index  emp_zhiwu_FK2 ;

drop table ADMINS cascade constraints;

drop table EMPLOYEES cascade constraints;

drop table NATIONS cascade constraints;

drop table STATES cascade constraints;

drop table XUELIS cascade constraints;

drop table  departments  cascade constraints;

drop table  emp_zhich  cascade constraints;

drop table  emp_zhiwu  cascade constraints;

drop table  zhichengs  cascade constraints;

drop table  zhiwus  cascade constraints;

/*==============================================================*/
/* Table: ADMINS                                                */
/*==============================================================*/
create table ADMINS  (
    admin_name          VARCHAR2(20)                    not null,
    admin_mima          VARCHAR2(20)                    not null,
    admin_grant         VARCHAR2(20)                    not null
);

comment on table ADMINS is
'用户登录信息表';

comment on column ADMINS. admin_name  is
'用户名';

comment on column ADMINS. admin_mima  is
'用户密码';

comment on column ADMINS. admin_grant  is
'用户权限';

/*==============================================================*/
/* Table: EMPLOYEES                                             */
/*==============================================================*/
create table EMPLOYEES  (
    emp_id              NUMBER                          not null,
    XUE_xue_id          NUMBER                          not null,
    dep_dep_id          NUMBER                          not null,
    states_id           NUMBER                          not null,
    NAT_nation_id       NUMBER                          not null,
    emp_name            VARCHAR2(20)                    not null,
    emp_sex             VARCHAR2(20)                    not null,
    emp_birth           DATE                            not null,
    emp_jianli          VARCHAR2(200)                   not null,
    emp_shenfen         VARCHAR2(20)                    not null,
    emp_baozhang        VARCHAR2(20)                    not null,
    emp_tuizi           NUMBER(10)                      not null,
    emp_tuishi          DATE                            not null,
   constraint PK_EMPLOYEES primary key ( emp_id )
);

comment on table EMPLOYEES is
'员工信息表';

comment on column EMPLOYEES. emp_id  is
'主键';

comment on column EMPLOYEES. XUE_xue_id  is
'学历编号';

comment on column EMPLOYEES. dep_dep_id  is
'部门编号';

comment on column EMPLOYEES. states_id  is
'员工编号';

comment on column EMPLOYEES. NAT_nation_id  is
'民族编号';

comment on column EMPLOYEES. emp_name  is
'员工姓名';

comment on column EMPLOYEES. emp_sex  is
'员工性别';

comment on column EMPLOYEES. emp_birth  is
'员工生日';

comment on column EMPLOYEES. emp_states  is
'员工状态';

comment on column EMPLOYEES. emp_jianli  is
'员工简历';

comment on column EMPLOYEES. emp_shenfen  is
'员工身份证';

comment on column EMPLOYEES. emp_baozhang  is
'员工社会保障号';

comment on column EMPLOYEES. emp_tuizi  is
'员工退休工资';

comment on column EMPLOYEES. emp_tuishi  is
'员工退休时间';

/*==============================================================*/
/* Index:  emp_dep_FK                                           */
/*==============================================================*/
create index  emp_dep_FK  on EMPLOYEES (
    dep_dep_id  ASC
);

/*==============================================================*/
/* Index:  emp_xuli_FK                                          */
/*==============================================================*/
create index  emp_xuli_FK  on EMPLOYEES (
    XUE_xue_id  ASC
);

/*==============================================================*/
/* Index:  emp_nat_FK                                           */
/*==============================================================*/
create index  emp_nat_FK  on EMPLOYEES (
    NAT_nation_id  ASC
);

/*==============================================================*/
/* Index:  emp_state_FK                                         */
/*==============================================================*/
create index  emp_state_FK  on EMPLOYEES (
    states_id  ASC
);

/*==============================================================*/
/* Table: NATIONS                                               */
/*==============================================================*/
create table NATIONS  (
    nation_id           NUMBER                          not null,
    nation_name         VARCHAR2(20)                    not null,
   constraint PK_NATIONS primary key ( nation_id )
);

comment on table NATIONS is
'民族信息表';

comment on column NATIONS. nation_id  is
'民族编号';

comment on column NATIONS. nation_name  is
'民族名称';

/*==============================================================*/
/* Table: STATES                                                */
/*==============================================================*/
create table STATES  (
    states_id           NUMBER                          not null,
    states_name         VARCHAR2(20)                    not null,
   constraint PK_STATES primary key ( states_id )
);

comment on table STATES is
'员工状态信息表';

comment on column STATES. states_id  is
'员工编号';

comment on column STATES. states_name  is
'员工名称';

/*==============================================================*/
/* Table: XUELIS                                                */
/*==============================================================*/
create table XUELIS  (
    xue_id              NUMBER                          not null,
    xue_name            VARCHAR2(20),
   constraint PK_XUELIS primary key ( xue_id )
);

comment on table XUELIS is
'学历信息表';

comment on column XUELIS. xue_id  is
'学历编号';

comment on column XUELIS. xue_name  is
'学历名称';

/*==============================================================*/
/* Table:  departments                                          */
/*==============================================================*/
create table  departments   (
    dep_id              NUMBER                          not null,
    dep_name            VARCHAR2(20)                    not null,
    dep_tel             VARCHAR2(20)                    not null,
   constraint PK_DEPARTMENTS primary key ( dep_id )
);

comment on table  departments  is
'部门信息表';

comment on column  departments . dep_id  is
'部门编号';

comment on column  departments . dep_name  is
'部门名称';

/*==============================================================*/
/* Table:  emp_zhich                                            */
/*==============================================================*/
create table  emp_zhich   (
    emp_id              NUMBER                          not null,
    zhicheng_id         NUMBER                          not null,
   constraint PK_EMP_ZHICH primary key ( emp_id ,  zhicheng_id )
);

comment on table  emp_zhich  is
'员工与职称';

comment on column  emp_zhich . emp_id  is
'主键';

comment on column  emp_zhich . zhicheng_id  is
'职称编号';

/*==============================================================*/
/* Index:  emp_zhich_FK                                         */
/*==============================================================*/
create index  emp_zhich_FK  on  emp_zhich  (
    emp_id  ASC
);

/*==============================================================*/
/* Index:  emp_zhich_FK2                                        */
/*==============================================================*/
create index  emp_zhich_FK2  on  emp_zhich  (
    zhicheng_id  ASC
);

/*==============================================================*/
/* Table:  emp_zhiwu                                            */
/*==============================================================*/
create table  emp_zhiwu   (
    emp_id              NUMBER                          not null,
    zhiwu_id            NUMBER                          not null,
   constraint PK_EMP_ZHIWU primary key ( emp_id ,  zhiwu_id )
);

comment on table  emp_zhiwu  is
'员工与职务';

comment on column  emp_zhiwu . emp_id  is
'主键';

comment on column  emp_zhiwu . zhiwu_id  is
'职务编号';

/*==============================================================*/
/* Index:  emp_zhiwu_FK                                         */
/*==============================================================*/
create index  emp_zhiwu_FK  on  emp_zhiwu  (
    emp_id  ASC
);

/*==============================================================*/
/* Index:  emp_zhiwu_FK2                                        */
/*==============================================================*/
create index  emp_zhiwu_FK2  on  emp_zhiwu  (
    zhiwu_id  ASC
);

/*==============================================================*/
/* Table:  zhichengs                                            */
/*==============================================================*/
create table  zhichengs   (
    zhicheng_id         NUMBER                          not null,
    zhicheng_name       VARCHAR2(20)                    not null,
   constraint PK_ZHICHENGS primary key ( zhicheng_id )
);

comment on table  zhichengs  is
'职称信息表';

comment on column  zhichengs . zhicheng_id  is
'职称编号';

comment on column  zhichengs . zhicheng_name  is
'职称名称';

/*==============================================================*/
/* Table:  zhiwus                                               */
/*==============================================================*/
create table  zhiwus   (
    zhiwu_id            NUMBER                          not null,
    zhiwu_name          VARCHAR2(20)                    not null,
   constraint PK_ZHIWUS primary key ( zhiwu_id )
);

comment on table  zhiwus  is
'职务信息表';

comment on column  zhiwus . zhiwu_id  is
'职务编号';

comment on column  zhiwus . zhiwu_name  is
'职务名称';

alter table EMPLOYEES
   add constraint FK_EMPLOYEE_EMP_DEP_DEPARTME foreign key ( dep_dep_id )
      references  departments  ( dep_id );

alter table EMPLOYEES
   add constraint FK_EMPLOYEE_EMP_NAT_NATIONS foreign key ( NAT_nation_id )
      references NATIONS ( nation_id );

alter table EMPLOYEES
   add constraint FK_EMPLOYEE_EMP_STATE_STATES foreign key ( states_id )
      references STATES ( states_id );

alter table EMPLOYEES
   add constraint FK_EMPLOYEE_EMP_XULI_XUELIS foreign key ( XUE_xue_id )
      references XUELIS ( xue_id );

alter table  emp_zhich 
   add constraint FK_EMP_ZHIC_EMP_ZHICH_EMPLOYEE foreign key ( emp_id )
      references EMPLOYEES ( emp_id );

alter table  emp_zhich 
   add constraint FK_EMP_ZHIC_EMP_ZHICH_ZHICHENG foreign key ( zhicheng_id )
      references  zhichengs  ( zhicheng_id );

alter table  emp_zhiwu 
   add constraint FK_EMP_ZHIW_EMP_ZHIWU_EMPLOYEE foreign key ( emp_id )
      references EMPLOYEES ( emp_id );

alter table  emp_zhiwu 
   add constraint FK_EMP_ZHIW_EMP_ZHIWU_ZHIWUS foreign key ( zhiwu_id )
      references  zhiwus  ( zhiwu_id );