/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     6/10/2020 10:01:12 PM                        */
/*==============================================================*/


drop table if exists interview_manage;

drop table if exists jobposted_manage;

drop table if exists postsetting;

drop table if exists resume_manage;


/*==============================================================*/
/* Table: interview_manage                                      */
/*==============================================================*/
create table interview_manage
(
   resumename           varchar(20),
   interviewer1         longtext,
   interviewdate1       date,
   interviewevaluate1   varchar(0),
   interviewer2         varchar(0),
   interviewdate2       date,
   interviewevaluate2   varchar(0),
   interviewer3         varchar(0),
   interviewdate3       date,
   interviewevaluate3   varchar(0),
   decisiondate         date,
   decision             varchar(10),
   primary key (resumename)
);

/*==============================================================*/
/* Table: jobposted_manage                                      */
/*==============================================================*/
create table jobposted_manage
(
   department           varchar(20),
   recruitmentype      longblob not null,
   peonumber            longblob not null,
   postname             varchar(20),
   tab_postno           varchar(20),
   postno               varchar(20),
   postclass            longblob not null,
   recordpeople         longblob not null,
   recorddate           longblob not null,
   deadline             longblob not null,
   postdescribe         longtext,
   recruitmentrequest   longtext,
   primary key (department, postname)
);

/*==============================================================*/
/* Table: postsetting                                           */
/*==============================================================*/
create table postsetting
(
   postno               varchar(20),
   zhiwiemingcheng      varchar(100),
   zhiweifenlei         varchar(100),
   suoshubumen          varchar(100),
   zhiweimiaoshu        longtext,
   beizhu               longtext,
   shifouqiyong         bool,
   primary key (postno)
);

/*==============================================================*/
/* Table: resume_manage                                         */
/*==============================================================*/
create table resume_manage
(
   resumename           varchar(20),
   department           varchar(20),
   postname             varchar(20),
   sex                  longblob not null,
   registerdate         longblob not null,
   resumestate          longblob not null,
   postclass            longblob not null,
   email                longblob not null,
   phone                int,
   address              longblob not null,
   domicileplace        longblob not null,
   politicsstatus       longblob not null,
   Idcard               int,
   school               longblob not null,
   education            longblob not null,
   professional         longblob not null,
   workyear             longblob not null,
   poststatus           longblob not null,
   freshgraduate        longblob not null,
   salary               longblob not null,
   recruitmenttype      longblob not null,
   introduction         longblob not null,
   remark               longtext,
   recommandinterview   longblob not null,
   recommander          longblob not null,
   recommanddate        longblob not null,
   recommandadvice      longtext,
   primary key (resumename)
);

alter table interview_manage add constraint FK_Reference_1 foreign key (resumename)
      references resume_manage (resumename);

alter table jobposted_manage add constraint FK_Reference_3 foreign key (tab_postno)
      references postsetting (postno);

alter table resume_manage add constraint FK_Reference_2 foreign key (department, postname)
      references jobposted_manage (department, postname);