StartUP SQL DATABASE 를 생성합니다.
1.
데이터베이스 생성(앞 과정에서 생성했으면 생략합니다.)
CREATE DATABASE startdbpg;
SQL
복사
2.
DBeaver에서 startdbpg 접속 컨넥션 만들기(앞에서 pg_hint_plan 만들면서 생성했으면 생략합니다.)
a.
PG는 MySQL처럼 특정 DB에 접속 후 DB를 변경할 수 없습니다.
•
Dbeaver에서 새로운 컨넥션을 추가
•
Database를 새로 만든 startdbpg로 설정한다.
3.
새로 만든 컨넥션으로 DB에 연결해 현재 데이터베이스를 확인합니다.
SELECT current_database(); -- 현재 접속한 DB 확인하기
-- 위 SQL의 결과가 startdbpg 일때, 다음 SQL을 실행합니다.
SQL
복사
4.
테이블 생성
a.
startdbpg에서 다음 SQL들을 차례대로 실행해 테이블을 생성합니다.
drop table IF EXISTS tr_ord_det;
drop table IF EXISTS tr_ord;
drop table IF EXISTS cm_base_cd;
drop table IF EXISTS cm_base_cd_dv;
drop table IF EXISTS ms_item_prc_hist;
drop table IF EXISTS ms_item;
drop table IF EXISTS ms_item_cat;
drop table IF EXISTS cm_base_dt;
drop table IF EXISTS tr_event_entry;
drop table IF EXISTS ms_event;
drop table IF EXISTS ms_shop;
drop table IF EXISTS ms_mbr;
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_seq 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_seq 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
,lach_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.lach_dt IS '출시일자';
CREATE TABLE ms_item_prc_hist
(
item_id VARCHAR(40) NOT NULL
,prc_start_dt DATE NOT NULL
,prc_end_dt DATE NULL
,sale_prc DECIMAL(18,3) NULL
);
ALTER TABLE ms_item_prc_hist ADD CONSTRAINT ms_item_prc_hist_pk PRIMARY KEY (item_id,prc_start_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.prc_start_dt IS '시작일자';
COMMENT ON COLUMN ms_item_prc_hist.prc_end_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_mbr
(
mbr_id VARCHAR(40) NOT NULL
,nick_nm VARCHAR(100) NULL
,mobl_no VARCHAR(100) NULL
,emal_adr VARCHAR(100) NULL
,join_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,join_tp VARCHAR(40) NULL
,mbr_gd VARCHAR(40) NULL
,mbr_st VARCHAR(40) NULL
,leave_dtm TIMESTAMP WITHOUT TIME ZONE NULL
);
ALTER TABLE ms_mbr ADD CONSTRAINT ms_mbr_pk PRIMARY KEY (mbr_id);
COMMENT ON TABLE ms_mbr IS '회원';
COMMENT ON COLUMN ms_mbr.mbr_id IS '회원ID';
COMMENT ON COLUMN ms_mbr.nick_nm IS '닉네임';
COMMENT ON COLUMN ms_mbr.mobl_no IS '핸드폰번호';
COMMENT ON COLUMN ms_mbr.emal_adr IS '이메일';
COMMENT ON COLUMN ms_mbr.join_dtm IS '가입일시';
COMMENT ON COLUMN ms_mbr.join_tp IS '가입유형';
COMMENT ON COLUMN ms_mbr.mbr_gd IS '회원등급';
COMMENT ON COLUMN ms_mbr.mbr_st IS '회원상태';
COMMENT ON COLUMN ms_mbr.leave_dtm IS '탈퇴일시';
CREATE TABLE tr_ord
(
ord_no INTEGER NOT NULL
,ord_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,prep_cmp_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,pkup_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,mbr_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.prep_cmp_dtm IS '제조완료일시';
COMMENT ON COLUMN tr_ord.pkup_dtm IS '픽업일시';
COMMENT ON COLUMN tr_ord.mbr_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) NULL
,base_dt_seq INT NULL
,base_wkd VARCHAR(10) 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) NULL
,event_start_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,event_end_dtm TIMESTAMP WITHOUT TIME ZONE 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
,entry_no INTEGER NOT NULL
,shop_id VARCHAR(40) NULL
,mbr_id VARCHAR(40) NULL
,entry_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,entry_rslt_cd VARCHAR(40) NULL
,entry_rslt_dtm TIMESTAMP WITHOUT TIME ZONE NULL
);
ALTER TABLE tr_event_entry ADD CONSTRAINT tr_event_entry_pk PRIMARY KEY (event_id,entry_no);
COMMENT ON TABLE tr_event_entry IS '이벤트';
COMMENT ON COLUMN tr_event_entry.event_id IS '이벤트ID';
COMMENT ON COLUMN tr_event_entry.entry_no IS '응모순번';
COMMENT ON COLUMN tr_event_entry.shop_id IS '매장ID';
COMMENT ON COLUMN tr_event_entry.mbr_id IS '회원ID';
COMMENT ON COLUMN tr_event_entry.entry_dtm IS '응모일시';
COMMENT ON COLUMN tr_event_entry.entry_rslt_cd IS '응모결과코드';
COMMENT ON COLUMN tr_event_entry.entry_rslt_dtm IS '응모결과일시';
SQL
복사
5.
데이터베이스를 구축하기 위해 데이터가 담긴 CSV 파일을 다운로드 합니다.
a.
아래 경로에서 파일을 다운로드한 후 압축을 풉니다. 압축을 풀면 총 12개의 CSV 파일이 존재합니다.
6.
DBeaver의 ‘데이터 가져오기’ 기능을 사용해, CSV 파일을 각각의 테이블에 INSERT합니다.
a.
DBeaver의 왼쪽 Database Navigator 창에서, 앞에서 만든 PG(PostgreSQL)의 startdbpg 컨넥션을 클릭해 public schema의 Tables 목록을 새로고침 합니다.
b.
새로 생성된 테이블들을 모두 선택 후 마우스 우클릭해 데이터 가져오기를 선택합니다.
c.
Import Source: CSV에서 가져오기를 선택 후 다음을 클릭합니다.
d.
Input file(s): 왼쪽의 Source 항목을 하나씩 클릭해 오른쪽 Target과 같은 명칭의 CSV 파일을 하나씩 선택합니다. 모두 선택(총 12개)후 다음을 클릭합니다. 서로 다른 Source와 Target을 선택하지 않도록 주의합니다.
e.
Tables mapping: Source와 Target이 제대로 매핑되었는지 확인 후 문제 없으면 다음을 클릭합니다. 매핑이 맞지 않으면, 처음부터 다시 작업하는 것을 권장합니다.
f.
Data load settings: 진행을 클릭합니다.
g.
아래와 같은 화면이 나온 후에 모두 완료되면 창이 저절로 닫힙니다.
h.
새롭게 SQL 창을 열어서 아래와 같이 ms_mbr에 데이터가 성공적으로 입력되었는지 조회해봅니다.
7.
데이터 가져오기를 완료한 후에는 다음 SQL을 후처리를 합니다.
a.
일부 데이터를 NULL로 업데이트 처리
b.
FK 생성 및 그에 맞는 인덱스 생성
c.
통계 추가
--> DBeaver로 데이터 IMPORT 후 실행
UPDATE ms_shop SET shop_end_ymd = NULL WHERE shop_end_ymd = '';
COMMIT;
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 (mbr_id) REFERENCES ms_mbr(mbr_id);
ALTER TABLE tr_event_entry ADD CONSTRAINT tr_event_entry_fk03 FOREIGN KEY (shop_id) REFERENCES ms_shop(shop_id);
ALTER TABLE cm_base_cd ADD CONSTRAINT cm_base_cd_fk01 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 (mbr_id) REFERENCES ms_mbr(mbr_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);
CREATE INDEX tr_event_entry_fk01 ON tr_event_entry (event_id) ;
CREATE INDEX tr_event_entry_fk02 ON tr_event_entry (mbr_id) ;
CREATE INDEX tr_event_entry_fk03 ON tr_event_entry (shop_id) ;
CREATE INDEX cm_base_cd_fk01 ON cm_base_cd (base_cd_dv) ;
CREATE INDEX ms_item_fk01 ON ms_item (item_cat) ;
CREATE INDEX ms_item_prc_hist_fk01 ON ms_item_prc_hist (item_id) ;
CREATE INDEX tr_ord_fk01 ON tr_ord (mbr_id) ;
CREATE INDEX tr_ord_fk02 ON tr_ord (shop_id) ;
CREATE INDEX tr_ord_det_fk01 ON tr_ord_det (ord_no) ;
CREATE INDEX tr_ord_det_fk02 ON tr_ord_det (item_id) ;
CREATE INDEX tr_ord_x01 ON tr_ord(ord_dtm);
-- 통계 생성
ANALYZE cm_base_cd;
ANALYZE cm_base_cd_dv;
ANALYZE cm_base_dt;
ANALYZE ms_event;
ANALYZE ms_item;
ANALYZE ms_item_cat;
ANALYZE ms_item_prc_hist;
ANALYZE ms_mbr;
ANALYZE ms_shop;
ANALYZE tr_event_entry;
ANALYZE tr_ord;
ANALYZE tr_ord_det;
SQL
복사