기본 연습용 DB 만들기

SELECT current_database(); -- 현재 접속한 DB 확인하기 -- 위 SQL의 결과가 startdbpg 일때, 다음 SQL을 실행합니다.

CREATE TABLE cm_base_cd_dv ( base_cd_dv VARCHAR(40) NOT NULL ,base_cd_dv_nm VARCHAR(100) NULL ) ; ALTER TABLE cm_base_cd_dv ADD CONSTRAINT cm_base_cd_dv_pk PRIMARY KEY (base_cd_dv); COMMENT ON COLUMN cm_base_cd_dv.base_cd_dv IS '기준코드구분'; COMMENT ON COLUMN cm_base_cd_dv.base_cd_dv_nm IS '기준코드구분명'; COMMENT ON TABLE cm_base_cd_dv IS '기준코드구분'; CREATE TABLE cm_base_cd ( base_cd_dv VARCHAR(40) NOT NULL ,base_cd VARCHAR(40) NOT NULL ,base_cd_nm VARCHAR(100) NULL ,sort_order INTEGER NULL ); ALTER TABLE cm_base_cd ADD CONSTRAINT cm_base_cd_pk PRIMARY KEY (base_cd_dv,base_cd); COMMENT ON TABLE cm_base_cd IS '기준코드'; COMMENT ON COLUMN cm_base_cd.base_cd_dv IS '기준코드구분'; COMMENT ON COLUMN cm_base_cd.base_cd IS '기준코드'; COMMENT ON COLUMN cm_base_cd.base_cd_nm IS '기준코드명'; COMMENT ON COLUMN cm_base_cd.sort_order IS '정렬순서'; CREATE TABLE ms_item_cat ( item_cat VARCHAR(40) NOT NULL ,item_cat_nm VARCHAR(100) NULL ); ALTER TABLE ms_item_cat ADD CONSTRAINT ms_item_cat_pk PRIMARY KEY (item_cat); COMMENT ON TABLE ms_item_cat IS '상품카테고리'; COMMENT ON COLUMN ms_item_cat.item_cat IS '상품카테고리'; COMMENT ON COLUMN ms_item_cat.item_cat_nm IS '상품카테고리명'; CREATE TABLE ms_item ( item_id VARCHAR(40) NOT NULL ,item_nm VARCHAR(100) NULL ,item_cat VARCHAR(40) NULL ,item_size_cd VARCHAR(40) NULL ,hot_cold_cd VARCHAR(40) NULL ,launch_dt DATE NULL ) ; ALTER TABLE ms_item ADD CONSTRAINT ms_item_pk PRIMARY KEY (item_id); COMMENT ON TABLE ms_item IS '상품'; COMMENT ON COLUMN ms_item.item_id IS '상품ID'; COMMENT ON COLUMN ms_item.item_nm IS '상품명'; COMMENT ON COLUMN ms_item.item_cat IS '상품카테고리'; COMMENT ON COLUMN ms_item.item_size_cd IS '상품사이즈코드'; COMMENT ON COLUMN ms_item.hot_cold_cd IS '아이스/HOT구분코드'; COMMENT ON COLUMN ms_item.launch_dt IS '출시일자'; CREATE TABLE ms_item_prc_hist ( item_id VARCHAR(40) NOT NULL ,from_dt DATE NOT NULL ,to_dt DATE NOT NULL ,sale_prc DECIMAL(18,3) NULL ); ALTER TABLE ms_item_prc_hist ADD CONSTRAINT ms_item_prc_hist_pk PRIMARY KEY (item_id,from_dt); COMMENT ON TABLE ms_item_prc_hist IS '상품가격이력'; COMMENT ON COLUMN ms_item_prc_hist.item_id IS '아이템ID'; COMMENT ON COLUMN ms_item_prc_hist.from_dt IS '시작일자'; COMMENT ON COLUMN ms_item_prc_hist.to_dt IS '종료일자'; COMMENT ON COLUMN ms_item_prc_hist.sale_prc IS '판매가격'; CREATE TABLE ms_shop ( shop_id VARCHAR(40) NOT NULL ,shop_nm VARCHAR(100) NULL ,shop_size INTEGER NULL ,shop_oper_tp VARCHAR(40) NULL ,table_qty INTEGER NULL ,chair_qty INTEGER NULL ,open_time VARCHAR(100) NULL ,close_time VARCHAR(100) NULL ,shop_st VARCHAR(40) NULL ,shop_start_ymd VARCHAR(8) NULL ,shop_end_ymd VARCHAR(8) NULL ); ALTER TABLE ms_shop ADD CONSTRAINT ms_shop_pk PRIMARY KEY (shop_id); COMMENT ON TABLE ms_shop IS '매장'; COMMENT ON COLUMN ms_shop.shop_id IS '매장ID'; COMMENT ON COLUMN ms_shop.shop_nm IS '매장명'; COMMENT ON COLUMN ms_shop.shop_size IS '매장면적(미터제곱)'; COMMENT ON COLUMN ms_shop.shop_oper_tp IS '매장운영유형(직영,대리점,플래그십)'; COMMENT ON COLUMN ms_shop.table_qty IS '테이블수'; COMMENT ON COLUMN ms_shop.chair_qty IS '의자수'; COMMENT ON COLUMN ms_shop.open_time IS '오픈시간'; COMMENT ON COLUMN ms_shop.close_time IS '클로즈시간'; COMMENT ON COLUMN ms_shop.shop_st IS '매장상태'; COMMENT ON COLUMN ms_shop.shop_start_ymd IS '매장시작일자'; COMMENT ON COLUMN ms_shop.shop_end_ymd IS '매장종료일자'; CREATE TABLE ms_member ( member_id VARCHAR(40) NOT NULL ,nick_nm VARCHAR(100) NULL ,mobile_no VARCHAR(100) NULL ,email VARCHAR(100) NULL ,join_dtm TIMESTAMP WITHOUT TIME ZONE NULL ,member_gd VARCHAR(40) NULL ,member_st VARCHAR(40) NULL ,leave_dtm TIMESTAMP WITHOUT TIME ZONE NULL ); ALTER TABLE ms_member ADD CONSTRAINT ms_member_pk PRIMARY KEY (member_id); COMMENT ON TABLE ms_member IS '회원'; COMMENT ON COLUMN ms_member.member_id IS '회원ID'; COMMENT ON COLUMN ms_member.nick_nm IS '닉네임'; COMMENT ON COLUMN ms_member.mobile_no IS '핸드폰번호'; COMMENT ON COLUMN ms_member.email IS '이메일'; COMMENT ON COLUMN ms_member.join_dtm IS '가입일시'; COMMENT ON COLUMN ms_member.member_gd IS '회원등급'; COMMENT ON COLUMN ms_member.member_st IS '회원상태'; COMMENT ON COLUMN ms_member.leave_dtm IS '탈퇴일시'; CREATE TABLE tr_ord ( ord_no INTEGER NOT NULL ,ord_dtm TIMESTAMP WITHOUT TIME ZONE NULL ,prepare_cmp_dtm TIMESTAMP WITHOUT TIME ZONE NULL ,pickup_dtm TIMESTAMP WITHOUT TIME ZONE NULL ,member_id VARCHAR(40) NULL ,shop_id VARCHAR(40) NULL ,ord_st VARCHAR(40) NULL ,ord_amt DECIMAL(18,3) NULL ,pay_tp VARCHAR(40) NULL ); ALTER TABLE tr_ord ADD CONSTRAINT tr_ord_pk PRIMARY KEY (ord_no); COMMENT ON TABLE tr_ord IS '주문'; COMMENT ON COLUMN tr_ord.ord_no IS '주문번호'; COMMENT ON COLUMN tr_ord.ord_dtm IS '주문일시'; COMMENT ON COLUMN tr_ord.prepare_cmp_dtm IS '제조완료일시'; COMMENT ON COLUMN tr_ord.pickup_dtm IS '픽업일시'; COMMENT ON COLUMN tr_ord.member_id IS '회원ID'; COMMENT ON COLUMN tr_ord.shop_id IS '매장ID'; COMMENT ON COLUMN tr_ord.ord_st IS '주문상태'; COMMENT ON COLUMN tr_ord.ord_amt IS '주문금액'; COMMENT ON COLUMN tr_ord.pay_tp IS '지불유형'; CREATE TABLE tr_ord_det ( ord_no INTEGER NOT NULL ,ord_det_no INTEGER NOT NULL ,item_id VARCHAR(40) NULL ,ord_qty INTEGER NULL ,sale_prc DECIMAL(18,3) NULL ); ALTER TABLE tr_ord_det ADD CONSTRAINT tr_ord_det_pk PRIMARY KEY (ord_no,ord_det_no); COMMENT ON TABLE tr_ord_det IS '주문상세'; COMMENT ON COLUMN tr_ord_det.ord_no IS '주문번호'; COMMENT ON COLUMN tr_ord_det.ord_det_no IS '주문상세번호'; COMMENT ON COLUMN tr_ord_det.item_id IS '상품ID'; COMMENT ON COLUMN tr_ord_det.ord_qty IS '주문수량'; COMMENT ON COLUMN tr_ord_det.sale_prc IS '판매가격'; CREATE TABLE cm_base_dt ( base_dt DATE NOT NULL ,base_ymd VARCHAR(8) NOT NULL ,base_dt_seq INT NOT NULL ,base_wkd VARCHAR(10) NOT NULL ) ; ALTER TABLE cm_base_dt ADD CONSTRAINT cm_base_dt_pk PRIMARY KEY (base_dt); COMMENT ON TABLE cm_base_dt IS '기준일자'; COMMENT ON COLUMN cm_base_dt.base_dt IS '기준일자'; COMMENT ON COLUMN cm_base_dt.base_ymd IS '기준일자_YMD'; COMMENT ON COLUMN cm_base_dt.base_dt_seq IS '기준일자순번'; COMMENT ON COLUMN cm_base_dt.base_wkd IS '기준요일'; CREATE TABLE ms_event ( event_id VARCHAR(40) NOT NULL ,event_nm VARCHAR(100) NOT NULL ,event_start_dtm TIMESTAMP WITHOUT TIME ZONE NOT NULL ,event_end_dtm TIMESTAMP WITHOUT TIME ZONE NOT NULL ); ALTER TABLE ms_event ADD CONSTRAINT ms_event_pk PRIMARY KEY (event_id); COMMENT ON TABLE ms_event IS '이벤트'; COMMENT ON COLUMN ms_event.event_id IS '이벤트ID'; COMMENT ON COLUMN ms_event.event_nm IS '이벤트명'; COMMENT ON COLUMN ms_event.event_start_dtm IS '이벤트시작일시'; COMMENT ON COLUMN ms_event.event_end_dtm IS '이벤트종료일시'; CREATE TABLE tr_event_entry ( event_id VARCHAR(40) NOT NULL ,member_id VARCHAR(40) NOT NULL ,entry_dtm TIMESTAMP WITHOUT TIME ZONE NOT NULL ,entry_result_cd VARCHAR(40) NOT NULL ) ; ALTER TABLE tr_event_entry ADD CONSTRAINT tr_event_entry_pk PRIMARY KEY (event_id,member_id); COMMENT ON TABLE tr_event_entry IS '이벤트'; COMMENT ON COLUMN tr_event_entry.event_id IS '이벤트ID'; COMMENT ON COLUMN tr_event_entry.member_id IS '회원ID'; COMMENT ON COLUMN tr_event_entry.entry_dtm IS '응모일시'; COMMENT ON COLUMN tr_event_entry.entry_result_cd IS '응모결과코드';

ALTER TABLE tr_event_entry ADD CONSTRAINT tr_event_entry_fk01 FOREIGN KEY (event_id) REFERENCES ms_event(event_id); ALTER TABLE tr_event_entry ADD CONSTRAINT tr_event_entry_fk02 FOREIGN KEY (member_id) REFERENCES ms_member(member_id); ALTER TABLE cm_base_cd ADD FOREIGN KEY (base_cd_dv) REFERENCES cm_base_cd_dv(base_cd_dv); ALTER TABLE ms_item ADD CONSTRAINT ms_item_fk01 FOREIGN KEY (item_cat) REFERENCES ms_item_cat(item_cat); ALTER TABLE ms_item_prc_hist ADD CONSTRAINT ms_item_prc_hist_fk01 FOREIGN KEY (item_id) REFERENCES ms_item(item_id); ALTER TABLE tr_ord ADD CONSTRAINT tr_ord_fk01 FOREIGN KEY (member_id) REFERENCES ms_member(member_id); ALTER TABLE tr_ord ADD CONSTRAINT tr_ord_fk02 FOREIGN KEY (shop_id) REFERENCES ms_shop(shop_id); ALTER TABLE tr_ord_det ADD CONSTRAINT tr_ord_det_fk01 FOREIGN KEY (ord_no) REFERENCES tr_ord(ord_no); ALTER TABLE tr_ord_det ADD CONSTRAINT tr_ord_det_fk02 FOREIGN KEY (item_id) REFERENCES ms_item(item_id);