본문 바로가기
시나리오/ERD

9차 ERD

공부하는 민우 2022. 9. 17.

1차. 회원테이블과 게시판 테이블 설계 (09-17 AM 02:54)

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

SELECT * FROM novice_user;

DROP TABLE novice_board;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL DEFAULT 0,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL DEFAULT 'N',
	board_readcount NUMBER NOT NULL DEFAULT 0,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;


2차. 조회수 중복 증가 방지 테이블 설계 (09-17 PM 01:31)

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

SELECT * FROM novice_user;

DROP TABLE novice_board;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

 

3차. 댓글 테이블 설계 (09-17 : PM 11:49)

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

SELECT * FROM novice_user;

DROP TABLE novice_board;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

 

4차. 첨부파일 테이블 설계 (09-21 AM 02:08)

 

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

SELECT * FROM novice_user;

DROP TABLE novice_board;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

DROP TABLE novice_file;
CREATE TABLE novice_file(
	file_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	org_file_name VARCHAR2(260) NOT NULL,
	stored_file_name VARCHAR2(36) NOT NULL,
	file_size NUMBER,
	upload_write_date DATE,
	CONSTRAINT file_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_file_seq;
CREATE SEQUENCE novice_file_seq;

 

5차. 첨부파일 테이블 설계 수정 (09-21 AM 07:13)

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

SELECT * FROM novice_user;

DROP TABLE novice_board;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

DROP TABLE novice_file;
CREATE TABLE novice_file(
	file_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	org_file_name VARCHAR2(260) NOT NULL,
	stored_file_name VARCHAR2(100) NOT NULL,
	file_size NUMBER,
	upload_write_date DATE,
	file_del VARCHAR2(1) NOT NULL,
	CONSTRAINT file_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_file_seq;
CREATE SEQUENCE novice_file_seq;


6차. 게시글 좋아요 테이블 설계 (09-23 AM 04:24)

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

DROP TABLE novice_file;
CREATE TABLE novice_file(
	file_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	org_file_name VARCHAR2(260) NOT NULL,
	stored_file_name VARCHAR2(100) NOT NULL,
	file_size NUMBER,
	upload_write_date DATE,
	file_del VARCHAR2(1) NOT NULL,
	CONSTRAINT file_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_file_seq;
CREATE SEQUENCE novice_file_seq;

DROP TABLE novice_board_like;
CREATE TABLE novice_board_like(
	like_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	like_write_date DATE,
	CONSTRAINT like_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_like_seq;
CREATE SEQUENCE novice_board_like_seq;

 

7차. 댓글 좋아요 테이블 설계 (09-23 AM 06:48)

\

 

DDL 

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

DROP TABLE novice_file;
CREATE TABLE novice_file(
	file_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	org_file_name VARCHAR2(260) NOT NULL,
	stored_file_name VARCHAR2(100) NOT NULL,
	file_size NUMBER,
	upload_write_date DATE,
	file_del VARCHAR2(1) NOT NULL,
	CONSTRAINT file_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_file_seq;
CREATE SEQUENCE novice_file_seq;

DROP TABLE novice_board_like;
CREATE TABLE novice_board_like(
	like_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	like_write_date DATE,
	CONSTRAINT like_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_like_seq;
CREATE SEQUENCE novice_board_like_seq;

DROP TABLE novice_comment_like;
CREATE TABLE novice_comment_like(
	comment_like_no NUMBER PRIMARY KEY,
	comment_no NUMBER,
	user_no NUMBER,
	comment_like_date DATE,
	CONSTRAINT comment_like_comment_no FOREIGN KEY(comment_no) REFERENCES novice_comment(comment_no),
	CONSTRAINT comment_like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_like_seq;
CREATE SEQUENCE novice_comment_like_seq;

 

8차. 이메일 인증 테이블 설계 (09-24 PM 05:00)

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_eamil VARCHAR2(200) NOT NULL UNIQUE,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

DROP TABLE novice_file;
CREATE TABLE novice_file(
	file_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	org_file_name VARCHAR2(260) NOT NULL,
	stored_file_name VARCHAR2(100) NOT NULL,
	file_size NUMBER,
	upload_write_date DATE,
	file_del VARCHAR2(1) NOT NULL,
	CONSTRAINT file_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_file_seq;
CREATE SEQUENCE novice_file_seq;

DROP TABLE novice_board_like;
CREATE TABLE novice_board_like(
	like_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	like_write_date DATE,
	CONSTRAINT like_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_like_seq;
CREATE SEQUENCE novice_board_like_seq;

DROP TABLE novice_comment_like;
CREATE TABLE novice_comment_like(
	comment_like_no NUMBER PRIMARY KEY,
	comment_no NUMBER,
	user_no NUMBER,
	comment_like_date DATE,
	CONSTRAINT comment_like_comment_no FOREIGN KEY(comment_no) REFERENCES novice_comment(comment_no),
	CONSTRAINT comment_like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_like_seq;
CREATE SEQUENCE novice_comment_like_seq;

DROP TABLE novice_mail_auth;
CREATE TABLE novice_mail_auth(
	mailauth_no NUMBER PRIMARY KEY,
	user_no NUMBER,
	mailauth_key VARCHAR2(200) NOT NULL,
	mailauth_complete VARCHAR2(1) NOT NULL,
	mailauth_date DATE,
	CONSTRAINT mail_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_mail_auth_seq;
CREATE SEQUENCE novice_mail_auth_seq;

 

9차 비밀번호 찾기 질문 및 답변 테이블 설계 (09-25 AM 01:45)

 

DDL

DROP TABLE novice_user;
CREATE TABLE novice_user(
	user_no NUMBER PRIMARY KEY,
	question_no NUMBER,
	user_id VARCHAR2(15) NOT NULL UNIQUE, 
	user_pw VARCHAR2(200) NOT NULL,
	user_nickname VARCHAR2(30) NOT NULL UNIQUE,
	user_gender VARCHAR2(1) NOT NULL,
	user_phone VARCHAR2(15) NOT NULL UNIQUE,
	user_email VARCHAR2(200) NOT NULL UNIQUE,
	user_findAnswer VARCHAR2(50) NOT NULL,
	user_birth DATE NOT NULL,
	user_join_date DATE NOT NULL,
	user_status VARCHAR2(30) NOT NULL,
	CONSTRAINT user_question_no FOREIGN KEY(question_no) REFERENCES novice_find_question
);

DROP SEQUENCE novice_user_seq;
CREATE SEQUENCE novice_user_seq;

DROP TABLE novice_board;
CREATE TABLE novice_board(
	board_no NUMBER PRIMARY KEY,
	user_no NUMBER NOT NULL,
	board_title VARCHAR2(2000) NOT NULL,
	board_content VARCHAR2(4000) NOT NULL,
	board_secret VARCHAR2(1) NOT NULL,
	board_readcount NUMBER NOT NULL,
	board_write_date DATE,
	CONSTRAINT board_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_seq;
CREATE SEQUENCE novice_board_seq;

DROP TABLE novice_read_page;
CREATE TABLE novice_read_page(
	read_page_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	client_ip VARCHAR2(50),
	read_write_date DATE,
	CONSTRAINT page_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_read_page_seq;
CREATE SEQUENCE novice_read_page_seq;

DROP TABLE novice_comment;
CREATE TABLE novice_comment(
	comment_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	comment_content VARCHAR2(4000) NOT NULL,
	comment_write_date DATE,
	CONSTRAINT comment_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT coment_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_seq;
CREATE SEQUENCE novice_comment_seq;

DROP TABLE novice_file;
CREATE TABLE novice_file(
	file_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	org_file_name VARCHAR2(260) NOT NULL,
	stored_file_name VARCHAR2(100) NOT NULL,
	file_size NUMBER,
	upload_write_date DATE,
	file_del VARCHAR2(1) NOT NULL,
	CONSTRAINT file_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no)
);

DROP SEQUENCE novice_file_seq;
CREATE SEQUENCE novice_file_seq;

DROP TABLE novice_board_like;
CREATE TABLE novice_board_like(
	like_no NUMBER PRIMARY KEY,
	board_no NUMBER,
	user_no NUMBER,
	like_write_date DATE,
	CONSTRAINT like_board_no FOREIGN KEY(board_no) REFERENCES novice_board(board_no),
	CONSTRAINT like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_board_like_seq;
CREATE SEQUENCE novice_board_like_seq;

DROP TABLE novice_comment_like;
CREATE TABLE novice_comment_like(
	comment_like_no NUMBER PRIMARY KEY,
	comment_no NUMBER,
	user_no NUMBER,
	comment_like_date DATE,
	CONSTRAINT comment_like_comment_no FOREIGN KEY(comment_no) REFERENCES novice_comment(comment_no),
	CONSTRAINT comment_like_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_comment_like_seq;
CREATE SEQUENCE novice_comment_like_seq;

DROP TABLE novice_mail_auth;
CREATE TABLE novice_mail_auth(
	mailauth_no NUMBER PRIMARY KEY,
	user_no NUMBER,
	mailauth_key VARCHAR2(200) NOT NULL,
	mailauth_complete VARCHAR2(1) NOT NULL,
	mailauth_date DATE,
	CONSTRAINT mail_user_no FOREIGN KEY(user_no) REFERENCES novice_user(user_no)
);

DROP SEQUENCE novice_mail_auth_seq;
CREATE SEQUENCE novice_mail_auth_seq;

DROP TABLE novice_find_question;
CREATE TABLE novice_find_question(
	question_no NUMBER PRIMARY KEY,
	question_content VARCHAR2(200) NOT NULL
);



DROP SEQUENCE novice_find_question_seq;
CREATE SEQUENCE novice_find_question_seq;

INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '졸업한 초등학교의 이름은 무엇 입니까?');
INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '태어난 고향은 어디 입니까?');
INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '가장 친한 사촌의 이름은 무엇 입니까?');
INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '어렷을적의 별명은 무엇 입니까?');
INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '부모님의 고향은 어디 입니까?');
INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '가장 좋아하는 색깔은 무엇 입니까?');
INSERT INTO NOVICE_FIND_QUESTION VALUES(novice_find_question_seq.nextval, '가장 좋아하는 음식은 무엇 입니까?');
저작자표시 비영리 변경금지

티스토리툴바