search

KHAcademyProject2020 / Spring-Project-Trip2Reap

국내 여행 사이트
Apache License 2.0
0 stars 0 forks source link

[SQL] SQL계정 생성/MEMBER 테이블 #2

Closed loveAlakazam closed 3 years ago

loveAlakazam commented 4 years ago 
CREATE USER TRIP2REAP identified by TRIP2REAP;
GRANT RESOURCE, CONNECT TO TRIP2REAP;

-- 뷰작성 권한 부여
GRANT CREATE VIEW TO TRIP2REAP;

-- SYNONYM 작성권한 부여 
GRANT CREATE SYNONYM TO TRIP2REAP;
CREATE TABLE MEMBER(
   MEMBER_ID VARCHAR2(30) PRIMARY KEY,
   MEMBER_PWD VARCHAR2(100) NOT NULL,
   MEMBER_NAMES VARCHAR2(30) NOT NULL,
   NICKNAME VARCHAR2(30) NOT NULL,
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30) NOT NULL,
   MEMBER_EMAIL VARCHAR2(50) NOT NULL,
   M_STATUS VARCHAR2(10) NOT NULL,
      CHECK(GENDER IN('남자','여자')),
      CHECK(M_STATUS IN('Y','N'))
);

ALTER TABLE MEMBER MODIFY (M_STATUS DEFAULT 'Y');
INSERT INTO MEMBER VALUES('admin', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '관리자', '관리자', '여자', '01012345678','admin@naver.com',default);
INSERT INTO MEMBER VALUES('soo', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김수진', '수진k', '여자', '01011112222','sjrlaa@gmail.com',default);
INSERT INTO MEMBER VALUES('zoo156', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '이수진', 'elisujinth', '여자', '01033334444','aa01039809776@gmail.com',default);
INSERT INTO MEMBER VALUES('ko1850', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '이규호', '규호짱', '남자', '01055556666','ko1804@naver.com',default);
INSERT INTO MEMBER VALUES('TRIP', '$2a$10$a3KAdt7hoVH02YhsqIWszuXUumRpihW3kHnEe1z3hRMbcrs7.bAjK', '최은강', 'loveAlakazam', '여자', '01077778888','dmsrkd1216@gmail.com',default);
INSERT INTO MEMBER VALUES('joy', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김기쁨', 'joy', '여자', '01099990000','lcocvce@naver.com',default);

commit;
--인증번호 테이블
CREATE TABLE CERTIFICATION(
EMAIL VARCHAR2(50) NOT NULL,
RANDOMKEY VARCHAR2(50) NOT NULL,
PRIMARY KEY(EMAIL, RANDOMKEY));


--카테고리 테이블
CREATE TABLE CATEGORY(
    CA_CODE NUMBER(5) NOT NULL,
    CA_NAME VARCHAR2(30) NOT NULL,
    PRIMARY KEY (CA_CODE),
    CHECK(CA_CODE BETWEEN 1 AND 5) --카테고리번호는 1~5로만 제한한다.
);

--카테고리
INSERT INTO CATEGORY VALUES(1, '여행지');
INSERT INTO CATEGORY VALUES(2, '여행 코스');
INSERT INTO CATEGORY VALUES(3, '여행후기 사진');
INSERT INTO CATEGORY VALUES(4, '여행후기 목록');
INSERT INTO CATEGORY VALUES(5, '호텔');

--게시판 테이블
CREATE TABLE BOARD(
    BO_NO NUMBER(10) NOT NULL,
    CA_CODE NUMBER(5) NOT NULL,
    BO_TITLE VARCHAR2(300) NOT NULL,
    BO_CONTENT LONG NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    BO_COUNT NUMBER(10) DEFAULT 0 NOT NULL,
    BO_TAG VARCHAR2(300),
    BO_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    PRIMARY KEY (BO_NO),
    FOREIGN KEY (CA_CODE) REFERENCES CATEGORY(CA_CODE),
    FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),
    CHECK (CA_CODE BETWEEN 1 AND 5), --테이블 카테고리번호는 1,2,3,4,5 로 제한한다.
    CHECK (BO_DELETE_YN IN ('Y', 'N'))
);
--첨부 파일테이블
CREATE TABLE IMG_FILE(
    FILE_NO NUMBER(10) NOT NULL,
    BO_NO NUMBER(10) NOT NULL,
    ORIGIN_NAME VARCHAR2(300) NOT NULL,
    CHANGE_NAME VARCHAR2(300) NOT NULL,
    FILE_LEVEL NUMBER(5) NOT NULL,
    FILE_PATH VARCHAR2(1000) NOT NULL,
    FILE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    -- 제약조건
    PRIMARY KEY(FILE_NO),
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO),
    CHECK(FILE_LEVEL IN (1,2) ), --파일레벨 1(썸네일) / 2(기본 이미지)
    CHECK(FILE_DELETE_YN IN ('Y', 'N'))
);
--좋아요 테이블
CREATE TABLE LIKES(
    BO_NO NUMBER(10) NOT NULL,
    LIKE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    PRIMARY KEY(BO_NO),
    FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),
    CHECK(LIKE_YN IN ('Y', 'N'))
);
--댓글테이블
CREATE TABLE REPLY(
    RE_NO NUMBER(10) NOT NULL,
    BO_NO NUMBER(10) NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    RE_DATE DATE DEFAULT SYSDATE NOT NULL,
    RE_CONTENT VARCHAR2(1500) NOT NULL,
    RE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    PRIMARY KEY(RE_NO), --파일번호
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO), --게시판 번호
    FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID), --작성자 아이디
    CHECK(RE_DELETE_YN IN ('Y', 'N')) --파일 삭제여부 (Y/N)
);
loveAlakazam commented 4 years ago 
--여행지 테이블 생성
CREATE TABLE TRAVEL(
    TR_NO NUMBER(10),
    BO_NO NUMBER(10) NOT NULL,
    TR_ADDR VARCHAR2(300) NOT NULL,
    TR_THEME VARCHAR2(50) NOT NULL,

--제약조건    
    PRIMARY KEY(TR_NO),
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO)
);
--컬럼에 주석
COMMENT ON COLUMN "TRIP2REAP"."TRAVEL"."TR_NO" IS '여행지번호';
COMMENT ON COLUMN "TRIP2REAP"."TRAVEL"."BO_NO" IS '참고게시글번호';
COMMENT ON COLUMN "TRIP2REAP"."TRAVEL"."TR_ADDR" IS '여행지주소';
COMMENT ON COLUMN "TRIP2REAP"."TRAVEL"."TR_THEME" IS '여행지테마';
--테스트용 DB (BOARD테이블) - 규호오빠가 쓰신 테스트DB먼저 커밋하시고 넣어주세요.
insert into BOARD values(8,2,'여행지1','테스트용1','admin',0,'#가족과함께#인천','N',SYSDATE);
insert into BOARD values(9,2,'여행지2','테스트용2','admin',0,'#뚜벅이추천#여행','N',SYSDATE);
insert into BOARD values(10,2,'여행지3','테스트용3','admin',0,'#친구와함께#여행','N',SYSDATE);
insert into BOARD values(11,2,'여행지4','테스트용4','admin',0,'#가족과함께#인천','N',SYSDATE);
insert into BOARD values(12,2,'여행지5','테스트용5','admin',0,'#뚜벅이추천#여행','N',SYSDATE);
--테스트용 DB (BOARD테이블)2 - 은강언니가 쓰신 시퀀스 커밋하고 넣어주세요.
insert into BOARD values(SEQ_BO_NO.NEXTVAL,2,'여행지5','테스트용5','admin',0,'#뚜벅이추천#여행','N',SYSDATE);
--테스트용 DB (TRAVEL테이블)
INSERT INTO TRAVEL VALUES(SEQ_TRNO.NEXTVAL, 8, '인천 송도', '관광지');
INSERT INTO TRAVEL VALUES(SEQ_TRNO.NEXTVAL, 9, '서울 송파구', '축제'); 
INSERT INTO TRAVEL VALUES(SEQ_TRNO.NEXTVAL, 10, '경기 용인시', '음식점'); 
INSERT INTO TRAVEL VALUES(SEQ_TRNO.NEXTVAL, 11, '경북 경주시', '명소'); 
INSERT INTO TRAVEL VALUES(SEQ_TRNO.NEXTVAL, 12, '인천 중구', '관광지');
INSERT INTO TRAVEL VALUES(SEQ_TRNO.NEXTVAL, SEQ_BO_NO.CURRVAL, '서울 송파구', '축제');

--여행지목록보기 VIEW 생성 (view까지 생성 후 커밋해주셔야 여행지 게시판 열려요)
GRANT connect, resource, dba to trip2reap;

CREATE OR REPLACE VIEW TLIST
AS
SELECT BOARD.BO_NO, BOARD.CA_CODE, BO_TITLE, BO_CONTENT, BOARD.MEMBER_ID, BO_COUNT, BO_TAG, BO_DELETE_YN, REGDATE, TR_NO, TR_ADDR, TR_THEME
    FROM BOARD
        JOIN CATEGORY C ON (C.CA_CODE = BOARD.CA_CODE)
        JOIN MEMBER M ON (M.MEMBER_ID = BOARD.MEMBER_ID)
        JOIN TRAVEL T ON (T.BO_NO = BOARD.BO_NO)
    WHERE BO_DELETE_YN = 'N'
    ORDER BY BOARD.BO_NO DESC;
loveAlakazam commented 4 years ago 
--여행코스
CREATE TABLE COURSE(
    BO_NO NUMBER(10) NOT NULL,
    CO_THEME VARCHAR2(50) NOT NULL,
    CO_DAY_TRIP VARCHAR2(20) NOT NULL,
    CO_DISTANCE NUMBER(10) NOT NULL,
    CO_DELETE_YN VARCHAR2(10) NOT NULL,

    PRIMARY KEY(BO_NO),
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO),
    CHECK(CO_DELETE_YN IN ('Y', 'N'))
);


--코스별 여행지 목록
CREATE TABLE COURSE_DETAIL(
    BO_NO NUMBER(10) NOT NULL,
    CO_TR_NO NUMBER(10) NOT NULL,
    CO_TR_LEVEL NUMBER(3) NOT NULL,

    PRIMARY KEY(BO_NO),
    FOREIGN KEY(BO_NO) REFERENCES COURSE(BO_NO),
    FOREIGN KEY(CO_TR_NO) REFERENCES TRAVEL(BO_NO)
);


@elisujinth : 수진리

수진씨 질문이있습니다! 테이블 COURSE_DETAIL의 컬럼 여행지번호 CO_TR_NO는 TRAVEL테이블의 BO_NO컬럼을 참조하나요? 아래는 수진씨가 작성한 구글 스프래드 시트(테이블 정의서)입니다.

Attribute Name Column Name Data Type Null Option Default Is PK Is FK Parent Table
여행 코스 번호 BO_NO NUMBER(10) NOT NULL   YES YES COURSE
여행지 번호 CO_TR_NO NUMBER(10) NOT NULL   NO YES TRAVEL
여행지 순서 CO_TR_LEVEL NUMBER(3) NOT NULL   NO NO  
loveAlakazam commented 4 years ago 
CREATE USER TRIP2REAP identified by TRIP2REAP;
GRANT RESOURCE, CONNECT TO TRIP2REAP;

DROP TABLE MEMBER CASCADE CONSTRAINTS;

CREATE TABLE MEMBER(
   MEMBER_ID VARCHAR2(30) PRIMARY KEY,
   MEMBER_PWD VARCHAR2(100) NOT NULL,
   MEMBER_NAMES VARCHAR2(30) NOT NULL,
   NICKNAME VARCHAR2(30) NOT NULL,
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30) NOT NULL,
   MEMBER_EMAIL VARCHAR2(50) NOT NULL,
   M_STATUS VARCHAR2(10) NOT NULL,
      CHECK(GENDER IN('남자','여자')),
      CHECK(M_STATUS IN('Y','N'))
);

ALTER TABLE MEMBER MODIFY (M_STATUS DEFAULT 'Y');

INSERT INTO MEMBER VALUES('admin', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '관리자', '관리자', '여자', '01012345678','admin@naver.com',default);
INSERT INTO MEMBER VALUES('soo', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김수진', '수진k', '여자', '01011112222','sjrlaa@gmail.com',default);
INSERT INTO MEMBER VALUES('zoo156', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '이수진', 'elisujinth', '여자', '01033334444','aa01039809776@gmail.com',default);
INSERT INTO MEMBER VALUES('ko1850', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '이규호', '규호짱', '남자', '01055556666','ko1804@naver.com',default);
INSERT INTO MEMBER VALUES('TRIP', '$2a$10$a3KAdt7hoVH02YhsqIWszuXUumRpihW3kHnEe1z3hRMbcrs7.bAjK', '최은강', 'loveAlakazam', '여자', '01077778888','dmsrkd1216@gmail.com',default);
INSERT INTO MEMBER VALUES('joy', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김기쁨', 'joy', '여자', '01099990000','lcocvce@naver.com',default);

commit;
------------------------------------------------------------------------------------------------------------------------------

--최은강
DROP TABLE CATEGORY CASCADE CONSTRAINTS;
DROP TABLE BOARD CASCADE CONSTRAINTS;

DROP TABLE HOTEL CASCADE CONSTRAINTS;
DROP TABLE ROOM CASCADE CONSTRAINTS;
DROP TABLE HOTEL_RESERVATION CASCADE CONSTRAINTS;
DROP TABLE HOTEL_REVIEW CASCADE CONSTRAINTS;

DROP TABLE LIKES CASCADE CONSTRAINTS;
DROP TABLE IMG_FILE CASCADE CONSTRAINTS;
DROP TABLE REPLY CASCADE CONSTRAINTS;

------------------------------------------------------------------------------------------------------------------------------

--카테고리 테이블
CREATE TABLE CATEGORY(
    CA_CODE NUMBER(5) NOT NULL,
    CA_NAME VARCHAR2(30) NOT NULL,
    PRIMARY KEY (CA_CODE),
    CHECK(CA_CODE BETWEEN 1 AND 5) --카테고리번호는 1~5로만 제한한다.
);

--카테고리
INSERT INTO CATEGORY VALUES(1, '여행지');
INSERT INTO CATEGORY VALUES(2, '여행 코스');
INSERT INTO CATEGORY VALUES(3, '여행후기 사진');
INSERT INTO CATEGORY VALUES(4, '여행후기 목록');
INSERT INTO CATEGORY VALUES(5, '호텔');

------------------------------------------------------------------------------------------------------------------------------

--첨부 파일테이블
CREATE TABLE IMG_FILE(
    FILE_NO NUMBER(10) NOT NULL,
    BO_NO NUMBER(10) NOT NULL,
    ORIGIN_NAME VARCHAR2(300) NOT NULL,
    CHANGE_NAME VARCHAR2(300) NOT NULL,
    FILE_LEVEL NUMBER(5) NOT NULL,
    FILE_PATH VARCHAR2(1000) NOT NULL,
    FILE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    -- 제약조건
    PRIMARY KEY(FILE_NO),
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO),
    CHECK(FILE_LEVEL IN (1,2) ), --파일레벨 1(썸네일) / 2(기본 이미지)
    CHECK(FILE_DELETE_YN IN ('Y', 'N'))
);

------------------------------------------------------------------------------------------------------------------------------

--댓글테이블
CREATE TABLE REPLY(
    RE_NO NUMBER(10) NOT NULL,
    BO_NO NUMBER(10) NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    RE_DATE DATE DEFAULT SYSDATE NOT NULL,
    RE_CONTENT VARCHAR2(1500) NOT NULL,
    RE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    PRIMARY KEY(RE_NO), --파일번호
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO), --게시판 번호
    FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID), --작성자 아이디
    CHECK(RE_DELETE_YN IN ('Y', 'N')) --파일 삭제여부 (Y/N)
);

------------------------------------------------------------------------------------------------------------------------------

--게시판 테이블
CREATE TABLE BOARD(
    BO_NO NUMBER(10) NOT NULL,
    CA_CODE NUMBER(5) NOT NULL,
    BO_TITLE VARCHAR2(300) NOT NULL,
    BO_CONTENT LONG NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    BO_COUNT NUMBER(10) DEFAULT 0 NOT NULL,
    BO_TAG VARCHAR2(300),
    BO_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    PRIMARY KEY (BO_NO),
    FOREIGN KEY (CA_CODE) REFERENCES CATEGORY(CA_CODE),
    FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),
    CHECK (CA_CODE BETWEEN 1 AND 5), --테이블 카테고리번호는 1,2,3,4,5 로 제한한다.
    CHECK (BO_DELETE_YN IN ('Y', 'N'))
);

------------------------------------------------------------------------------------------------------------------------------

--좋아요 테이블
CREATE TABLE LIKES(
    BO_NO NUMBER(10) NOT NULL,
    LIKE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    PRIMARY KEY(BO_NO),
    FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),
    CHECK(LIKE_YN IN ('Y', 'N'))
);

------------------------------------------------------------------------------------------------------------------------------

--호텔(HOTEL)테이블
CREATE TABLE HOTEL(
    BO_NO NUMBER,                                   --호텔번호
    HOTEL_ADDRESS VARCHAR2(1000) NOT NULL,          --호텔주소
    HOTEL_SITE VARCHAR2(1000),                      --호텔사이트
    HOTEL_TEL VARCHAR2(30) NOT NULL,                --전화번호
    HOTEL_REVIEW_SCORE NUMBER(5,1) DEFAULT 0 NOT NULL,  --호텔 후기 평점

    HOTEL_LATITUDE NUMBER NOT NULL,     --호텔위도
    HOTEL_LONGTITUDE NUMBER NOT NULL,   --호텔경도

    HOTEL_OPEN_TIME NUMBER(5) NOT NULL,                 --호텔오픈시각
    HOTEL_CLOSE_TIME NUMBER(5) NOT NULL,                --호텔종료시각
    HOTEL_OPTION LONG,                                  --호텔옵션
    CHECK_IN NUMBER(5) DEFAULT 10 NOT NULL,             --체크인
    CHECK_OUT NUMBER(5) DEFAULT 15 NOT NULL,            --체크아웃

    CONSTRAINT HT_PK PRIMARY KEY (BO_NO),                --기본키(BO_NO)
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO)
);

------------------------------------------------------------------------------------------------------------------------------

--호텔방(ROOM)테이블
CREATE TABLE ROOM(
    ROOM_NO NUMBER(10),                     --방번호
    BO_NO NUMBER(10) NOT NULL,              --호텔번호
    ROOM_TYPE VARCHAR2(200) NOT NULL,       --방종류
    ROOM_NAME VARCHAR2(1000) NOT NULL,      --방이름
    PRICE_PER_DAY NUMBER(10) NOT NULL,          --1인당 이용가격
    CONSTRAINT RO_PK PRIMARY KEY(ROOM_NO),  --기본키(ROOM_NO)
    CONSTRAINT RO_FK FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) --외래키(BO_NO)
);

------------------------------------------------------------------------------------------------------------------------------

--호텔예약(HOTEL_RESERVATION) 테이블
CREATE TABLE HOTEL_RESERVATION(
    RESERVE_NO NUMBER(10),              --예약번호
    MEMBER_ID VARCHAR2(30) NOT NULL,    --예약자ID
    BO_NO NUMBER(10) NOT NULL,          --호텔번호
    ROOM_NO NUMBER(10) NOT NULL,        --방번호
    RESERVE_PRICE NUMBER(10) NOT NULL,  --예약가격
    RESERVE_PERSON_CNT NUMBER(10) NOT NULL,         --예약인원수
    CHECK_IN_DATE DATE DEFAULT SYSDATE NOT NULL,                    --체크인 날짜
    CHECK_OUT_DATE DATE DEFAULT SYSDATE NOT NULL,                   --체크아웃 날짜
    RESERVE_CHECK VARCHAR2(5) DEFAULT 'N' NOT NULL, --예약확인
    REFUND_CHECK VARCHAR2(5)  DEFAULT 'N' NOT NULL, --환불확인

    CONSTRAINT RS_PK PRIMARY KEY(RESERVE_NO),                                   --기본키(RESERVE_NO)
    CONSTRAINT RS_FK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),   --외래키(MEMBER_ID)
    CONSTRAINT RS_FK_BN FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO),             --외래키(BO_NO)
    CONSTRAINT RS_FK_RN FOREIGN KEY(ROOM_NO) REFERENCES ROOM(ROOM_NO),          --외래키(ROOM_NO)
    CONSTRAINT RS_CK_RSC CHECK(RESERVE_CHECK IN ('Y', 'N')),                    --제약조건(RESERVE_CHECK)
    CONSTRAINT RS_CK_RFC CHECK(REFUND_CHECK IN('Y','N'))                        --제약조건(REFUND_CHECK)
);
------------------------------------------------------------------------------------------------------------------------------

--호텔리뷰 댓글
CREATE TABLE HOTEL_REVIEW(
    RE_NO NUMBER(10),   --댓글번호
    REVIEW_SCORE NUMBER(5) NOT NULL,
    CONSTRAINT HR_PK_RVN PRIMARY KEY(RE_NO),                            --기본키(RE_NO)
    CONSTRAINT HR_FK_RVN FOREIGN KEY(RE_NO) REFERENCES REPLY(RE_NO),    --외래키(RE_NO)
    CONSTRAINT HR_CK_RVS CHECK(REVIEW_SCORE BETWEEN 0 AND 5)            --제약조건(REVIEW_SCORE 0~5점만 부여가능)
);

------------------------------------------------------------------------------------------------------------------------------
--여행지
CREATE TABLE TRAVEL(
    BO_NO NUMBER(10) NOT NULL,
    TR_ADDR VARCHAR2(300) NOT NULL,
    TR_THEME VARCHAR2(50) NOT NULL,
    TR_LATITUDE NUMBER NOT NULL,
    TR_LONGTITUDE NUMBER NOT NULL,
    FILE_NO NUMBER(10) NOT NULL,

    PRIMARY KEY(BO_NO),
    FOREIGN KEY(FILE_NO) REFERENCES IMG_FILE(FILE_NO)
);
------------------------------------------------------------------------------------------------------------------------------

--여행코스
CREATE TABLE COURSE(
    BO_NO NUMBER(10) NOT NULL,
    CO_THEME VARCHAR2(50) NOT NULL,
    CO_DAY_TRIP VARCHAR2(20) NOT NULL,
    CO_DISTANCE NUMBER(10) NOT NULL,
    CO_DELETE_YN VARCHAR2(10) NOT NULL,

    PRIMARY KEY(BO_NO),
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO),
    CHECK(CO_DELETE_YN IN ('Y', 'N'))
);

--코스별 여행지 목록
CREATE TABLE COURSE_DETAIL(
    BO_NO NUMBER(10) NOT NULL,
    CO_TR_NO NUMBER(10) NOT NULL,
    CO_TR_LEVEL NUMBER(3) NOT NULL,

    PRIMARY KEY(BO_NO),
    FOREIGN KEY(BO_NO) REFERENCES COURSE(BO_NO),
    FOREIGN KEY(CO_TR_NO) REFERENCES TRAVEL(CO_TR_NO)
);

------------------------------------------------------------------------------------------------------------------------------
COMMIT;
loveAlakazam commented 4 years ago 
--호텔(HOTEL)테이블
CREATE TABLE HOTEL(
    BO_NO NUMBER(10) NOT NULL,                                   --호텔번호
    HOTEL_ADDRESS VARCHAR2(1000) NOT NULL,          --호텔주소
    HOTEL_SITE VARCHAR2(1000),                      --호텔사이트
    HOTEL_TEL VARCHAR2(30) NOT NULL,                --전화번호
    HOTEL_REVIEW_SCORE NUMBER(5,1) DEFAULT 0 NOT NULL,  --호텔 후기 평점

    HOTEL_OPEN_TIME NUMBER(5) NOT NULL,                 --호텔오픈시각
    HOTEL_CLOSE_TIME NUMBER(5) NOT NULL,                --호텔종료시각
    HOTEL_OPTION LONG,                                  --호텔옵션
    CHECK_IN NUMBER(5) DEFAULT 10 NOT NULL,             --체크인
    CHECK_OUT NUMBER(5) DEFAULT 15 NOT NULL,            --체크아웃

    CONSTRAINT HT_PK PRIMARY KEY (BO_NO),                --기본키(BO_NO)
    FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO)  --외래키(BO_NO)
);



--호텔방(ROOM)테이블
CREATE TABLE ROOM(
    ROOM_NO NUMBER(10),                     --방번호
    BO_NO NUMBER(10) NOT NULL,              --호텔번호
    ROOM_TYPE VARCHAR2(200) NOT NULL,       --방종류
    ROOM_NAME VARCHAR2(1000) NOT NULL,      --방이름
    PRICE_PER_DAY NUMBER(10) NOT NULL,          --1인당 이용가격
    CONSTRAINT RO_PK PRIMARY KEY(ROOM_NO),  --기본키(ROOM_NO)
    CONSTRAINT RO_FK FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) --외래키(BO_NO)
);



--호텔예약(HOTEL_RESERVATION) 테이블
CREATE TABLE HOTEL_RESERVATION(
    RESERVE_NO NUMBER(10),              --예약번호
    MEMBER_ID VARCHAR2(30) NOT NULL,    --예약자ID
    BO_NO NUMBER(10) NOT NULL,          --호텔번호
    ROOM_NO NUMBER(10) NOT NULL,        --방번호
    RESERVE_PRICE NUMBER(10) NOT NULL,  --예약가격
    RESERVE_PERSON_CNT NUMBER(10) NOT NULL,         --예약인원수
    CHECK_IN_DATE DATE DEFAULT SYSDATE NOT NULL,                    --체크인 날짜
    CHECK_OUT_DATE DATE DEFAULT SYSDATE NOT NULL,                   --체크아웃 날짜
    RESERVE_CHECK VARCHAR2(5) DEFAULT 'N' NOT NULL, --예약확인
    REFUND_CHECK VARCHAR2(5)  DEFAULT 'N' NOT NULL, --환불확인

    CONSTRAINT RS_PK PRIMARY KEY(RESERVE_NO),                                   --기본키(RESERVE_NO)
    CONSTRAINT RS_FK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),   --외래키(MEMBER_ID)
    CONSTRAINT RS_FK_BN FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO),             --외래키(BO_NO)
    CONSTRAINT RS_FK_RN FOREIGN KEY(ROOM_NO) REFERENCES ROOM(ROOM_NO),          --외래키(ROOM_NO)
    CONSTRAINT RS_CK_RSC CHECK(RESERVE_CHECK IN ('Y', 'N')),                    --제약조건(RESERVE_CHECK)
    CONSTRAINT RS_CK_RFC CHECK(REFUND_CHECK IN('Y','N'))                        --제약조건(REFUND_CHECK)
);



--호텔리뷰 댓글
CREATE TABLE HOTEL_REVIEW(
    RE_NO NUMBER(10),   --댓글번호
    REVIEW_SCORE NUMBER(5) NOT NULL,
    CONSTRAINT HR_PK_RVN PRIMARY KEY(RE_NO),                            --기본키(RE_NO)
    CONSTRAINT HR_FK_RVN FOREIGN KEY(RE_NO) REFERENCES REPLY(RE_NO),    --외래키(RE_NO)
    CONSTRAINT HR_CK_RVS CHECK(REVIEW_SCORE BETWEEN 0 AND 5)            --제약조건(REVIEW_SCORE 0~5점만 부여가능)
);