DROP TABLE RESUME_STACK;
DROP TABLE SCHOOL;
DROP TABLE USER_LIKE;
DROP TABLE USER_BOARD;
DROP TABLE POSTING_STACK;
DROP TABLE USER_BOOKMARK;
DROP TABLE APPLY;
DROP TABLE POSTING;
DROP TABLE COMPANY;
DROP TABLE RESUME;
DROP TABLE USERS;
COMMIT;
-- 회사 정보 ===============================================================
CREATE TABLE "COMPANY" (
"COM_ID" number(10,0),
"COM_NAME" varchar2(255) NOT NULL,
"COM_EMAIL" varchar2(255) NOT NULL,
"COM_TYPE" varchar2(255) NOT NULL,
"COM_CEO" varchar2(255) NOT NULL,
"COM_PASSWD" varchar2(255) NOT NULL,
"COM_ADDRESS" varchar2(255) NOT NULL,
"COM_PHONE" varchar2(255) NOT NULL,
-- "COM_INDATE" date DEFAULT SYSDATE NOT NULL ,
"COM_SALARY" varchar2(10),
"COM_BIRTH" date,
"COM_LOGO" varchar2(255),
-- "ROLE" varchar2(20) DEFAULT 'USER_COMPANY',
"EMPLOYEE" varchar2(20),
"SALES" varchar2(20),
"BGLOGO" varchar2(20),
"STOCK" varchar2(255),
"PAGE" varchar2(255),
PRIMARY KEY ("COM_ID")
);
-- 개인 회원 ================================================================
CREATE TABLE "USERS" (
"USER_NAME" varchar2(255) NOT NULL,
"USER_PASSWD" varchar2(255) NOT NULL,
"USER_EMAIL" varchar2(255) NOT NULL,
"USER_ADDRESS" varchar2(255) NOT NULL,
"USER_PHONE" varchar2(255) NOT NULL,
"USER_ID" number(10,0),
"USER_SOCIAL_NUM" varchar2(255) NOT NULL,
-- "INDATE" date DEFAULT SYSDATE NOT NULL ,
"USER_GENDER" varchar2(255) NOT NULL,
--"ROLE" varchar2(20) NOT NULL,
PRIMARY KEY ("USER_ID")
);
-- 이력서 정보 =============================================================
CREATE TABLE "RESUME" (
"USER_ID" number(10,0) NOT NULL,
"RE_ID" number(10,0) ,
"RE_PROFILE" varchar2(255),
"RE_TITLE" varchar2(255) NOT NULL,
"RE_INTTI" varchar2(255) NOT NULL,
"RE_INTCON" varchar2(255) NOT NULL,
"LICENSE" varchar2(255),
"INDATE" date DEFAULT SYSDATE NOT NULL ,
"SCHOOL_ID" number(10,0),
PRIMARY KEY ("RE_ID")
);
-- 채용 공고 ==============================================================
CREATE TABLE "POSTING" (
"COM_ID" number(10,0) ,
"PO_ID" number(10,0) ,
"PO_GRADE" varchar2(255) DEFAULT '고졸' NOT NULL ,
"HOPE_DEPARTMENT" varchar2(255),
"PO_START_DATE" date DEFAULT SYSDATE NOT NULL ,
"PO_END_DATE" date,
"PO_PROFILE" varchar2(255),
"PO_TITLE" varchar2(255),
"PO_CONTENT" varchar2(255),
"CAREER" varchar2(255) DEFAULT '신입' NOT NULL ,
PRIMARY KEY ("PO_ID")
);
-- 게시판 =================================================================
CREATE TABLE "USER_BOARD" (
"USER_ID" number(10,0) NOT NULL,
"TITLE" varchar2(255) NOT NULL,
"CONTENT" varchar2(255) NOT NULL,
"HIT" number(10,0) NOT NULL,
"INDATE" date DEFAULT SYSDATE NOT NULL ,
"BOARD_ID" number(10,0) ,
"BOARD_BOOLEAN" number(10,0) DEFAULT '1' NOT NULL ,
"BOARD_LIKE" number(10,0),
PRIMARY KEY ("BOARD_ID")
);
-- 개인 북마크 =============================================================
CREATE TABLE "USER_BOOKMARK" (
"USER_ID" number(10,0) NOT NULL,
"PO_ID" number(10,0) NOT NULL,
"UB_BOOLEAN" number(10,0) NOT NULL,
"UB_ID" number(10,0) DEFAULT '1' NOT NULL ,
PRIMARY KEY ("UB_ID")
);
-- 이력서용 기술 스택 =======================================================
CREATE TABLE "RESUME_STACK" (
"RE_ID" number(10,0) NOT NULL,
"STACK_NAME" varchar2(255) NOT NULL,
"RE_STACK_ID" number(10,0) ,
PRIMARY KEY ("RE_STACK_ID")
);
-- 공고용 기술 스택 =========================================================
CREATE TABLE "POSTING_STACK" (
"PO_ID" number(10,0) NOT NULL,
"STACK" varchar2(255) NOT NULL,
"PO_ST_ID" number(10,0) ,
PRIMARY KEY ("PO_ST_ID")
);
-- 개인 지원 ==============================================================
CREATE TABLE "APPLY" (
"PO_ID" number(10,0) NOT NULL,
"RE_ID" number(10,0) NOT NULL,
"RESULT" varchar2(255) DEFAULT '진행중' NOT NULL ,
"AP_ID" number(10,0) ,
PRIMARY KEY ("AP_ID")
);
-- 유저 좋아요 기능 =========================================================
CREATE TABLE "USER_LIKE" (
"LIKE_ID" number(10,0) PRIMARY KEY,
"USER_ID" number(10,0),
"BOARD_ID" number(10,0),
"LIKE_BOOLEAN" number(10,0),
"BOARD_COMMENT" varchar2(255) DEFAULT '댓글' NOT NULL,
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES
users(user_id),
CONSTRAINT fk_board_id FOREIGN KEY (board_id) REFERENCES
user_board(board_id)
);
-- 학력 사항 ==============================================================
CREATE TABLE "SCHOOL" (
"SCHOOL_ID" NUMBER PRIMARY KEY,
"RE_ID" NUMBER(10,0),
"SCHOOL_NAME" varchar2(255),
"SCHOOL_TYPE" varchar2(255),
CONSTRAINT fk_resume_school_id FOREIGN KEY (re_id) REFERENCES resume(re_id)
);
ALTER TABLE "RESUME" ADD CONSTRAINT "RESUME_fk0" FOREIGN KEY ("USER_ID") REFERENCES "USERS"("USER_ID");
ALTER TABLE "POSTING" ADD CONSTRAINT "POSTING_fk0" FOREIGN KEY ("COM_ID") REFERENCES "COMPANY"("COM_ID");
ALTER TABLE "USER_BOARD" ADD CONSTRAINT "USER_BOARD_fk0" FOREIGN KEY ("USER_ID") REFERENCES "USERS"("USER_ID");
ALTER TABLE "USER_BOOKMARK" ADD CONSTRAINT "USER_BOOKMARK_fk0" FOREIGN KEY ("USER_ID") REFERENCES "USERS"("USER_ID");
ALTER TABLE "USER_BOOKMARK" ADD CONSTRAINT "USER_BOOKMARK_fk1" FOREIGN KEY ("PO_ID") REFERENCES "POSTING"("PO_ID");
ALTER TABLE "RESUME_STACK" ADD CONSTRAINT "RESUME_STACK_fk0" FOREIGN KEY ("RE_ID") REFERENCES "RESUME"("RE_ID");
ALTER TABLE "APPLY" ADD CONSTRAINT "APPLY_fk0" FOREIGN KEY ("PO_ID") REFERENCES "POSTING"("PO_ID");
ALTER TABLE "APPLY" ADD CONSTRAINT "APPLY_fk1" FOREIGN KEY ("RE_ID") REFERENCES "RESUME"("RE_ID");
ALTER TABLE "POSTING_STACK" ADD CONSTRAINT "POSTING_STACK_fk0" FOREIGN KEY ("PO_ID") REFERENCES "POSTING"("PO_ID");