Closed loveAlakazam closed 3 years ago
CREATE TABLE MEMBER(
MEMBER_ID VARCHAR2(30) PRIMARY KEY,
MEMBER_PWD VARCHAR2(100),
MEMBER_NAMES VARCHAR2(30),
NICKNAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
MEMBER_EMAIL VARCHAR2(50),
M_CATEGORY VARCHAR2(10) 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');
ALTER TABLE MEMBER ADD UNIQUE(PHONE);
4-1. CATEGORY
--카테고리 테이블
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, '호텔');
BO_NO
) 시퀀스 - SEQ_BO_NO
CREATE SEQUENCE SEQ_BO_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;
FILE_NO
) 시퀀스 - SEQ_FILE_NO
CREATE SEQUENCE SEQ_FILE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;
RE_NO
) 시퀀스 - SEQ_RE_NO
CREATE SEQUENCE SEQ_RE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;
-- BOARD테이블에 여행후기샘플데이터 삽입
-- SEQ_BO_NO 현재값 출력확인
SELECT SEQ_BO_NO.CURRVAL FROM DUAL; -- 6이 출력됩니다.
--여행지 테이블 생성
CREATE TABLE "TRIP2REAP"."TRAVEL"
( "TR_NO" NUMBER(10,0),
"BO_NO" NUMBER(10,0) NOT NULL ENABLE,
"TR_ADDR" VARCHAR2(300 BYTE) NOT NULL ENABLE,
"TR_THEME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"TR_REG" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"TR_TITLE" VARCHAR2(100 BYTE),
"TR_PHONE" VARCHAR2(20 BYTE),
PRIMARY KEY ("TR_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
FOREIGN KEY ("BO_NO")
REFERENCES "TRIP2REAP"."BOARD" ("BO_NO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
References
--여행코스 테이블
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 LOCAL_INFO(
LOCAL_CODE NUMBER(5) NOT NULL, --지역번호
LOCAL_NAME VARCHAR2(50) NOT NULL, --지역이름
PRIMARY KEY(LOCAL_CODE)
);
--지역정보 데이터 삽입
INSERT INTO LOCAL_INFO VALUES(1,'강원도');
INSERT INTO LOCAL_INFO VALUES(2,'경기도');
INSERT INTO LOCAL_INFO VALUES(3,'경상남도');
INSERT INTO LOCAL_INFO VALUES(4,'경상북도');
INSERT INTO LOCAL_INFO VALUES(5,'광주광역시');
INSERT INTO LOCAL_INFO VALUES(6, '대구광역시');
INSERT INTO LOCAL_INFO VALUES(7, '대전광역시');
INSERT INTO LOCAL_INFO VALUES(8, '부산광역시');
INSERT INTO LOCAL_INFO VALUES(9, '서울특별시');
INSERT INTO LOCAL_INFO VALUES(10, '세종특별자치시');
INSERT INTO LOCAL_INFO VALUES(11, '인천광역시');
INSERT INTO LOCAL_INFO VALUES(12, '울산광역시');
INSERT INTO LOCAL_INFO VALUES(13, '전라남도');
INSERT INTO LOCAL_INFO VALUES(14, '전라북도');
INSERT INTO LOCAL_INFO VALUES(15, '제주특별자치도');
INSERT INTO LOCAL_INFO VALUES(16, '충청남도');
INSERT INTO LOCAL_INFO VALUES(17, '충청북도');
--호텔(HOTEL)테이블
CREATE TABLE HOTEL(
BO_NO NUMBER(10) NOT NULL, --호텔번호
HOTEL_ADDRESS VARCHAR2(1000) NOT NULL,--호텔주소
HOTEL_LOCAL_CODE NUMBER(5) NOT NULL,--지역코드
HOTEL_SITE VARCHAR2(1000),--호텔사이트
HOTEL_TEL VARCHAR2(30) NULL,--전화번호
HOTEL_REVIEW_SCORE NUMBER(5,1) DEFAULT 0 NOT NULL, --호텔 후기 평점
HOTEL_RANK NUMBER(5), --호텔등급(1~5, 없음)
HOTEL_OPEN_TIME NUMBER(5) DEFAULT 0 NOT NULL,--호텔오픈시각
HOTEL_CLOSE_TIME NUMBER(5) DEFAULT 23 NOT NULL,--호텔종료시각
HOTEL_OPTION VARCHAR2(2000), --호텔옵션
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)
FOREIGN KEY(HOTEL_LOCAL_CODE) REFERENCES LOCAL_INFO(LOCAL_CODE) --외래키(HOTEL_LOCAL_CODE)
);
--컬럼에 주석
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 '여행지테마';
--[1. HOTEL_REVIEW 제약조건]
--호텔리뷰 제약조건 삭제
ALTER TABLE HOTEL_REVIEW DROP CONSTRAINT HR_FK_RVN; --기존 제약조건 삭제
-- 제약조건 다시 추가.
ALTER TABLE HOTEL_REVIEW ADD CONSTRAINT HR_FK_RVN -- 리뷰삭제되면 호텔리뷰도 같이 삭제
FOREIGN KEY(RE_NO) REFERENCES REPLY(RE_NO) ON DELETE CASCADE;
--[2. LIKES 제약조건]
--회원탈퇴/ 회원정보 수정시에 발생하는 참조테이블도 같이 업데이트 시키기
--@@LIKES 기존 외래키 제약조건 삭제(MEMBER 테이블을 참조하는 FOREIGN KEY)
--@@사람마다 제약조건 이름이 다를수있으니, 제약조건보고 CONSTRAINT_NAME 확인!!
ALTER TABLE LIKES DROP CONSTRAINT SYS_C007142;
--LIKES 외래키 제약조건 추가
ALTER TABLE LIKES ADD CONSTRAINT FK_LK_MID
FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE;
--[3. BOARD 제약조건]
--@@기존 외래키 제약조건 삭제(MEMBER 테이블을 참조하는 외래키)
--@@사람마다 제약조건이름이 다를 수있으니, 제약조건보고 CONSTRAINT_NAME확인!
ALTER TABLE BOARD DROP CONSTRAINT (외래키이름);
--제약조건 추가
ALTER TABLE BOARD ADD CONSTRAINT BOARD_FK_MID
FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE;
--[4. IMG_FILE 제약조건]
--@@기존 외래키 제약조건 삭제(BOARD 테이블 참조하는 외래키)
--@@사람마다 제약조건 이름이 다를 수 있으니, 제약조건보고 CONSTRAINT_NAME 확인!!
ALTER TABLE IMG_FILE DROP CONSTRAINT SYS_C007025;
-- 이미지 제약조건은 빼주세요!
--ALTER TABLE IMG_FILE ADD CONSTRAINT IMG_FK_BNO
--FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE;
--[5. HOTEL_RESERVATION 제약조건]
ALTER TABLE HOTEL_RESERVATION ADD CONSTRAINT RS_FK_MID
FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE;
--[6. HOTEL 제약조건]
--BOARD를 참조하는 외래키
--@@ 사람마다 다를 수 있으니 제약조건 보고 CONSTRAINT_NAME 확인할것!
--@@기존 호텔테이블 제약조건 삭제! (R_TABLE_NAME: BOARD)
ALTER TABLE HOTEL DROP CONSTRAINT SYS_C007069;
ALTER TABLE HOTEL ADD CONSTRAINT HT_FK_BNO
FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE;
--[7. ROOM 제약조건]
ALTER TABLE ROOM DROP CONSTRAINT RO_FK;
ALTER TABLE ROOM ADD CONSTRAINT RO_FK
FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) ON DELETE CASCADE;
--[8. REPLY 제약조건]
--@@사람마다 다를 수 있으니 제약조건 보고 CONSTRAINT_NAME 확인할것!
--@@R_TABLE_NAME이 BOARD인 제약조건삭제!
--@@R_TABLE_NAME이 MEMBER인 제약조건도 같이 삭제!
ALTER TABLE REPLY DROP CONSTRAINT SYS_C007040;
--게시글을 삭제하면 reply도 같이 삭제하도록 제약조건 추가.
ALTER TABLE REPLY ADD CONSTRAINT RP_FK_BNO
FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE;
--작성자가 탈퇴되어 member테이블에 삭제되면, 탈퇴한 작성자가 작성한 덧글 삭제
ALTER TABLE REPLY ADD CONSTRAINT RP_FK_MID
FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE;
COMMIT;
접속계정은 SYSTEM입니다.
1. 계정생성 및 권한 부여 (접속계정: SYSTEM)
# 접속계정은 **TRIP2REAP**입니다. # 2. 기존 테이블 & 시퀀스 삭제 ```SQL -- TABLE 삭제 DROP TABLE MEMBER CASCADE CONSTRAINTS; DROP TABLE KAKAOMEMBER CASCADE CONSTRAINTS; DROP TABLE CATEGORY CASCADE CONSTRAINTS; DROP TABLE HOTEL CASCADE CONSTRAINTS; DROP TABLE HOTEL_RESERVATION CASCADE CONSTRAINTS; DROP TABLE HOTEL_REVIEW CASCADE CONSTRAINTS; DROP TABLE IMG_FILE CASCADE CONSTRAINTS; DROP TABLE REPLY CASCADE CONSTRAINTS; DROP TABLE LOCAL_INFO CASCADE CONSTRAINTS; DROP TABLE ROOM CASCADE CONSTRAINTS; DROP TABLE BOARD CASCADE CONSTRAINTS; DROP TABLE LIKES CASCADE CONSTRAINTS; DROP TABLE CERTIFICATION; DROP TABLE COURSE CASCADE CONSTRAINTS; DROP TABLE COURSE_DETAIL CASCADE CONSTRAINTS; DROP TABLE TRAVEL CASCADE CONSTRAINTS; --시퀀스 삭제 DROP SEQUENCE SEQ_BO_NO; DROP SEQUENCE SEQ_FILE_NO; DROP SEQUENCE SEQ_RE_NO; DROP SEQUENCE SEQ_TRNO; DROP SEQUENCE SEQ_ROOM_NO; DROP SEQUENCE SEQ_RESERVE_NO; ```
## 뷰 삭제 ```sql --뷰 삭제 SQL쿼리문 DROP VIEW HOTEL_DETAIL_IMG_VIEW; DROP VIEW HOTEL_REVIEW_INFO_VIEW; DROP VIEW HOTEL_ROOM_INFO_VIEW; DROP VIEW THUMBNAIL_IMG_VIEW; DROP VIEW HOTEL_VIEW; DROP VIEW TLIST; ```