CREATE DATABASE ch06;

# -----------------------------------------------------------------------
# user
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS user;

CREATE TABLE user (
username   VARCHAR(20)  NOT NULL,
password   VARCHAR(20)  NOT NULL,
realname   VARCHAR(20)  NOT NULL,
PRIMARY KEY (username) );

# -----------------------------------------------------------------------
# resume
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS resume;

CREATE TABLE resume (
resume_id        CHAR(8)      NOT NULL,
realname         VARCHAR(20)  NOT NULL,
sex              CHAR(1)      NOT NULL,
born_date        CHAR(10)     NOT NULL,
max_education    VARCHAR(20)  NOT NULL,
major            VARCHAR(30)  NOT NULL,
email            VARCHAR(20)  NOT NULL,
contact_phone    VARCHAR(15)  NOT NULL,
mobile           VARCHAR(15)  ,
current_job_type VARCHAR(40)  NOT NULL,
expect_job_type  VARCHAR(40)  NOT NULL,
current_position VARCHAR(40)  ,
expect_position  VARCHAR(40)  ,
current_city     VARCHAR(40)  ,
expect_city      VARCHAR(40)  ,
expect_salary    VARCHAR(12)  ,
resume_content   TEXT         ,
expire_time      CHAR(10)     ,
PRIMARY KEY (resume_id) ,
INDEX (realname, sex, max_education, current_job_type, expect_job_type) );

# -----------------------------------------------------------------------
# interview
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS interview;

CREATE TABLE interview (
resume_id        CHAR(8)  NOT NULL,
interview_date   CHAR(10) NOT NULL,
interview_result TEXT     NOT NULL,
PRIMARY KEY (resume_id, interview_date) );

# -----------------------------------------------------------------------
# setting
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS setting;

CREATE TABLE setting (
username       VARCHAR(20) NOT NULL,
count_per_page INT         NOT NULL,
PRIMARY KEY (username) );