database_of_hrms.sql

人力资源管理系统

create tablespace hrms datafile 'd:\ORACLE\ORADATA\tianlan\USERS02.DBF' size 100m;

create user hrms identified by hrms
default tablespace hrms
temporary tablespace temp;

grant connect to hrms;
grant resource to hrms;
grant unlimited tablespace to hrms;

connect hrms/hrms

create table ypinfo(
  id varchar2(50),
  y_name varchar2(50),
  y_sex varchar2(50),
  y_jg varchar2(50),
  y_school varchar2(50),
  y_xueli varchar2(50),
  y_job varchar2(50),
  self_intro varchar2(100),
  y_email varchar2(50),
  y_phone varchar2(50),
  tab_state varchar2(50));

create table employee(
  emp_id number(50),
  emp_name varchar2(50) ,
  psw varchar2(50),
  id_card varchar2(50),  
  sex varchar2(50),
  dept_name varchar2(50),
  job varchar2(50),
  hire_date varchar2(50),
  birthday varchar2(50));

create table change_job(
  id varchar2(50),
  emp_name varchar2(50),
  old_dept varchar2(50),
  old_job varchar2(50),
  new_dept varchar2(50),
  new_job varchar2(50),
  reason varchar2(50),
  tab_state varchar2(50));

create table emp_train(
  id varchar2(50),
  emp_name varchar2(50),
  dept_name varchar2(50),
  attitude varchar2(500));

create table train_info(
  id varchar2(50),
  people varchar2(50),
  start_date varchar2(50),
  end_date varchar2(50),
  descript varchar2(50));

create table train_sum(
  id varchar2(50),
  emp_name varchar2(50),
  dept_name varchar2(50),
  summary varchar2(500));
 
create table kq_info(
  id varchar2(50),
  emp_name varchar2(50),
  times number(50),
  kq_degree number(50),
  month varchar2(50));

create table kq_leave(
  id varchar2(50),
  emp_name varchar2(50),
  start_date varchar2(50),
  end_date varchar2(50),
  reason varchar2(50),
  tab_state varchar2(50));

create table evaluate(
  id varchar2(50),
  emp_name varchar2(50),
  first_mark number(50),
  sec_mark number(50),
  thi_mark number(50),
  for_mark number(50),
  fif_mark number(50),
  month varchar2(50));

create table jxd(
  id varchar2(50),
  emp_name varchar2(50),
  mark number(50),
  month varchar2(50));

create table jfd(
  id varchar2(50),
  emp_name varchar2(50),
  jf_name varchar2(50),
  jf_money number(50),
  month varchar2(50));

create table salary_info(
  id varchar2(50),
  emp_name varchar2(50),
  month varchar2(50),
  basic_sal number(50),
  tax_rate number(50),
  jintie number(50),
  bonus number(50),
  sum_sal number(50));


alter table ypinfo add CONSTRAINT ypinfo_primary_key primary key (id);
alter table employee add CONSTRAINT employee_primary_key primary key (emp_name);
alter table change_job add CONSTRAINT change_job_primary_key primary key (id);
alter table emp_train add CONSTRAINT emp_train_primary_key primary key (id);
alter table train_sum add CONSTRAINT train_sum_primary_key primary key (id);
alter table train_info add CONSTRAINT train_info_primary_key primary key (id);
alter table kq_leave add CONSTRAINT kq_leave_primary_key primary key (id);
alter table kq_info add CONSTRAINT kq_info_primary_key primary key (id);
alter table evaluate add CONSTRAINT evaluate_primary_key primary key (id);
alter table jxd add CONSTRAINT jxd_primary_key primary key (id);
alter table jfd add CONSTRAINT jfd_primary_key primary key (id);
alter table salary_info add CONSTRAINT salary_info_primary_key primary key (id);


alter table change_job add CONSTRAINT change_job_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table emp_train add CONSTRAINT emp_train_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table kq_leave add CONSTRAINT kq_leave_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table train_sum add CONSTRAINT train_sum_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table kq_info add CONSTRAINT kq_info_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table evaluate add CONSTRAINT evaluate_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table jxd add CONSTRAINT jxd_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table jfd add CONSTRAINT jfd_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table salary_info add CONSTRAINT salary_info_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);


alter table ypinfo add CONSTRAINT check_ypinfo check (tab_state='等待' or tab_state='批准' or tab_state='拒绝');
alter table kq_leave add CONSTRAINT check_kq_leave check (tab_state='等待' or tab_state='批准' or tab_state='拒绝');
alter table change_job add CONSTRAINT check_change_job check (tab_state='等待' or tab_state='批准' or tab_state='拒绝');