데이터베이스 생성(앞에서 DOCKER 설치 후 테스트하면서 생성했으면 생략합니다.)
CREATE DATABASE startdbpg;
DBeaver에서 startdbpg 접속 컨넥션 만들기(앞에서 pg_hint_plan 만들면서 생성했으면 생략합니다.)
-
PG는 MySQL처럼 특정 DB에 접속 후 DB를 변경할 수 없습니다.
- Dbeaver에서 새로운 컨넥션을 추가
- Database를 새로 만든 startdbpg로 설정한다.
새로 만든 컨넥션으로 DB에 연결해 현재 데이터베이스를 확인합니다.
SELECT current_database(); -- 현재 접속한 DB 확인하기
-- 위 SQL의 결과가 startdbpg 일때, 다음 SQL을 실행합니다.
-- startdbpg 스키마 추가하기. 데이터베이스 아래 스키마를 추가해, 해당 스키마에 테이블들을 추가한다.
CREATE SCHEMA IF NOT EXISTS startdbpg AUTHORIZATION postgres;
-- postgres(root) 사용자한테, 스키마 serach path 설정하기
ALTER ROLE postgres SET search_path = startdbpg, public;
테이블 생성
-
앞에서 테스트로 만들던 테이블들이 있으면 DROP합니다.
drop table IF EXISTS startdbpg.tr_ord_det; drop table IF EXISTS startdbpg.tr_ord; drop table IF EXISTS startdbpg.cm_base_cd; drop table IF EXISTS startdbpg.cm_base_cd_dv; drop table IF EXISTS startdbpg.ms_item_prc_hist; drop table IF EXISTS startdbpg.ms_item; drop table IF EXISTS startdbpg.ms_item_cat; drop table IF EXISTS startdbpg.cm_base_dt; drop table IF EXISTS startdbpg.tr_event_entry; drop table IF EXISTS startdbpg.ms_event; drop table IF EXISTS startdbpg.ms_shop; drop table IF EXISTS startdbpg.ms_mbr; DROP TABLE IF EXISTS test; -- 앞에서 테스트로 만든 테이블이 존재한다면 DROP 처리 -
startdbpg에서 다음 SQL들을 차례대로 실행해 테이블을 생성합니다.
CREATE TABLE startdbpg.cm_base_cd_dv
(
base_cd_dv VARCHAR(40) NOT NULL
,base_cd_dv_nm VARCHAR(100) NULL
) ;
ALTER TABLE startdbpg.cm_base_cd_dv ADD CONSTRAINT cm_base_cd_dv_pk PRIMARY KEY (base_cd_dv);
COMMENT ON COLUMN startdbpg.cm_base_cd_dv.base_cd_dv IS '기준코드구분';
COMMENT ON COLUMN startdbpg.cm_base_cd_dv.base_cd_dv_nm IS '기준코드구분명';
COMMENT ON TABLE startdbpg. startdbpg.cm_base_cd_dv IS '기준코드구분';
CREATE TABLE startdbpg.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 startdbpg.cm_base_cd ADD CONSTRAINT cm_base_cd_pk PRIMARY KEY (base_cd_dv,base_cd);
COMMENT ON TABLE startdbpg.cm_base_cd IS '기준코드';
COMMENT ON COLUMN startdbpg.cm_base_cd.base_cd_dv IS '기준코드구분';
COMMENT ON COLUMN startdbpg.cm_base_cd.base_cd IS '기준코드';
COMMENT ON COLUMN startdbpg.cm_base_cd.base_cd_nm IS '기준코드명';
COMMENT ON COLUMN startdbpg.cm_base_cd.sort_seq IS '정렬순서';
CREATE TABLE startdbpg.ms_item_cat
(
item_cat VARCHAR(40) NOT NULL
,item_cat_nm VARCHAR(100) NULL
);
ALTER TABLE startdbpg.ms_item_cat ADD CONSTRAINT ms_item_cat_pk PRIMARY KEY (item_cat);
COMMENT ON TABLE startdbpg.ms_item_cat IS '상품카테고리';
COMMENT ON COLUMN startdbpg.ms_item_cat.item_cat IS '상품카테고리';
COMMENT ON COLUMN startdbpg.ms_item_cat.item_cat_nm IS '상품카테고리명';
CREATE TABLE startdbpg.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 startdbpg.ms_item ADD CONSTRAINT ms_item_pk PRIMARY KEY (item_id);
COMMENT ON TABLE startdbpg.ms_item IS '상품';
COMMENT ON COLUMN startdbpg.ms_item.item_id IS '상품ID';
COMMENT ON COLUMN startdbpg.ms_item.item_nm IS '상품명';
COMMENT ON COLUMN startdbpg.ms_item.item_cat IS '상품카테고리';
COMMENT ON COLUMN startdbpg.ms_item.item_size_cd IS '상품사이즈코드';
COMMENT ON COLUMN startdbpg.ms_item.hot_cold_cd IS '아이스/HOT구분코드';
COMMENT ON COLUMN startdbpg.ms_item.lach_dt IS '출시일자';
CREATE TABLE startdbpg.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 startdbpg.ms_item_prc_hist ADD CONSTRAINT ms_item_prc_hist_pk PRIMARY KEY (item_id,prc_start_dt);
COMMENT ON TABLE startdbpg.ms_item_prc_hist IS '상품가격이력';
COMMENT ON COLUMN startdbpg.ms_item_prc_hist.item_id IS '상품ID';
COMMENT ON COLUMN startdbpg.ms_item_prc_hist.prc_start_dt IS '시작일자';
COMMENT ON COLUMN startdbpg.ms_item_prc_hist.prc_end_dt IS '종료일자';
COMMENT ON COLUMN startdbpg.ms_item_prc_hist.sale_prc IS '판매가격';
CREATE TABLE startdbpg.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 startdbpg.ms_shop ADD CONSTRAINT ms_shop_pk PRIMARY KEY (shop_id);
COMMENT ON TABLE startdbpg.ms_shop IS '매장';
COMMENT ON COLUMN startdbpg.ms_shop.shop_id IS '매장ID';
COMMENT ON COLUMN startdbpg.ms_shop.shop_nm IS '매장명';
COMMENT ON COLUMN startdbpg.ms_shop.shop_size IS '매장면적(미터제곱)';
COMMENT ON COLUMN startdbpg.ms_shop.shop_oper_tp IS '매장운영유형(직영,대리점,플래그십)';
COMMENT ON COLUMN startdbpg.ms_shop.table_qty IS '테이블수';
COMMENT ON COLUMN startdbpg.ms_shop.chair_qty IS '의자수';
COMMENT ON COLUMN startdbpg.ms_shop.open_time IS '오픈시간';
COMMENT ON COLUMN startdbpg.ms_shop.close_time IS '클로즈시간';
COMMENT ON COLUMN startdbpg.ms_shop.shop_st IS '매장상태';
COMMENT ON COLUMN startdbpg.ms_shop.shop_start_ymd IS '매장시작일자';
COMMENT ON COLUMN startdbpg.ms_shop.shop_end_ymd IS '매장종료일자';
CREATE TABLE startdbpg.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 startdbpg.ms_mbr ADD CONSTRAINT ms_mbr_pk PRIMARY KEY (mbr_id);
COMMENT ON TABLE startdbpg.ms_mbr IS '회원';
COMMENT ON COLUMN startdbpg.ms_mbr.mbr_id IS '회원ID';
COMMENT ON COLUMN startdbpg.ms_mbr.nick_nm IS '닉네임';
COMMENT ON COLUMN startdbpg.ms_mbr.mobl_no IS '핸드폰번호';
COMMENT ON COLUMN startdbpg.ms_mbr.emal_adr IS '이메일';
COMMENT ON COLUMN startdbpg.ms_mbr.join_dtm IS '가입일시';
COMMENT ON COLUMN startdbpg.ms_mbr.join_tp IS '가입유형';
COMMENT ON COLUMN startdbpg.ms_mbr.mbr_gd IS '회원등급';
COMMENT ON COLUMN startdbpg.ms_mbr.mbr_st IS '회원상태';
COMMENT ON COLUMN startdbpg.ms_mbr.leave_dtm IS '탈퇴일시';
CREATE TABLE startdbpg.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 startdbpg.tr_ord ADD CONSTRAINT tr_ord_pk PRIMARY KEY (ord_no);
COMMENT ON TABLE startdbpg.tr_ord IS '주문';
COMMENT ON COLUMN startdbpg.tr_ord.ord_no IS '주문번호';
COMMENT ON COLUMN startdbpg.tr_ord.ord_dtm IS '주문일시';
COMMENT ON COLUMN startdbpg.tr_ord.prep_cmp_dtm IS '제조완료일시';
COMMENT ON COLUMN startdbpg.tr_ord.pkup_dtm IS '픽업일시';
COMMENT ON COLUMN startdbpg.tr_ord.mbr_id IS '회원ID';
COMMENT ON COLUMN startdbpg.tr_ord.shop_id IS '매장ID';
COMMENT ON COLUMN startdbpg.tr_ord.ord_st IS '주문상태';
COMMENT ON COLUMN startdbpg.tr_ord.ord_amt IS '주문금액';
COMMENT ON COLUMN startdbpg.tr_ord.pay_tp IS '지불유형';
CREATE TABLE startdbpg.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 startdbpg.tr_ord_det ADD CONSTRAINT tr_ord_det_pk PRIMARY KEY (ord_no,ord_det_no);
COMMENT ON TABLE startdbpg.tr_ord_det IS '주문상세';
COMMENT ON COLUMN startdbpg.tr_ord_det.ord_no IS '주문번호';
COMMENT ON COLUMN startdbpg.tr_ord_det.ord_det_no IS '주문상세번호';
COMMENT ON COLUMN startdbpg.tr_ord_det.item_id IS '상품ID';
COMMENT ON COLUMN startdbpg.tr_ord_det.ord_qty IS '주문수량';
COMMENT ON COLUMN startdbpg.tr_ord_det.sale_prc IS '판매가격';
CREATE TABLE startdbpg.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 startdbpg.cm_base_dt ADD CONSTRAINT cm_base_dt_pk PRIMARY KEY (base_dt);
COMMENT ON TABLE startdbpg.cm_base_dt IS '기준일자';
COMMENT ON COLUMN startdbpg.cm_base_dt.base_dt IS '기준일자';
COMMENT ON COLUMN startdbpg.cm_base_dt.base_ymd IS '기준일자_YMD';
COMMENT ON COLUMN startdbpg.cm_base_dt.base_dt_seq IS '기준일자순번';
COMMENT ON COLUMN startdbpg.cm_base_dt.base_wkd IS '기준요일';
CREATE TABLE startdbpg.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 startdbpg.ms_event ADD CONSTRAINT ms_event_pk PRIMARY KEY (event_id);
COMMENT ON TABLE startdbpg.ms_event IS '이벤트';
COMMENT ON COLUMN startdbpg.ms_event.event_id IS '이벤트ID';
COMMENT ON COLUMN startdbpg.ms_event.event_nm IS '이벤트명';
COMMENT ON COLUMN startdbpg.ms_event.event_start_dtm IS '이벤트시작일시';
COMMENT ON COLUMN startdbpg.ms_event.event_end_dtm IS '이벤트종료일시';
CREATE TABLE startdbpg.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 startdbpg.tr_event_entry ADD CONSTRAINT tr_event_entry_pk PRIMARY KEY (event_id,entry_no);
COMMENT ON TABLE startdbpg.startdbpg.tr_event_entry IS '이벤트';
COMMENT ON COLUMN startdbpg.tr_event_entry.event_id IS '이벤트ID';
COMMENT ON COLUMN startdbpg.tr_event_entry.entry_no IS '응모순번';
COMMENT ON COLUMN startdbpg.tr_event_entry.shop_id IS '매장ID';
COMMENT ON COLUMN startdbpg.tr_event_entry.mbr_id IS '회원ID';
COMMENT ON COLUMN startdbpg.tr_event_entry.entry_dtm IS '응모일시';
COMMENT ON COLUMN startdbpg.tr_event_entry.entry_rslt_cd IS '응모결과코드';
COMMENT ON COLUMN startdbpg.tr_event_entry.entry_rslt_dtm IS '응모결과일시';
데이터베이스를 구축하기 위해 데이터가 담긴 CSV 파일을 다운로드 합니다.
- 아래 경로에서 파일을 다운로드한 후 압축을 풉니다. 압축을 풀면 총 12개의 CSV 파일이 존재합니다.
DBeaver의 ‘데이터 가져오기’ 기능을 사용해, CSV 파일을 각각의 테이블에 INSERT합니다.
-
DBeaver의 왼쪽 Database Navigator 창에서, 앞에서 만든 PG(PostgreSQL)의 startdbpg 컨넥션을 클릭해 Schemas의 목록을 새로고침합니다.
- Schemas에서 마우스 우클릭 후 팝업메뉴에서 새로고침 선택
- startdbpg schema 아래에 새로 만든 테이블들이 존재하는지 확인합니다.
-
새로 생성된 테이블들을 모두 선택 후 마우스 우클릭해 데이터 가져오기를 선택합니다.
-
모든 테이블을 선택하기 위해 Ctrl을 누른채 위에서 새로 만든 12개 테이블을 하나씩 선택합니다.
-
테이블을 모두 선택한 후에 마우스 우클릭 > 데이터 가져오기를 선택합니다.
-
-
Import Source: CSV에서 가져오기를 선택 후 다음을 클릭합니다.
-
Input file(s): 왼쪽의 Source 항목을 하나씩 클릭해 오른쪽 Target과 같은 명칭의 CSV 파일을 하나씩 선택합니다. 모두 선택(총 12개)후 다음을 클릭합니다. 서로 다른 Source와 Target을 선택하지 않도록 주의합니다.
-
Tables mapping: Source와 Target이 제대로 매핑되었는지 확인 후 문제 없으면 다음을 클릭합니다. 매핑이 맞지 않으면, 처음부터 다시 작업하는 것을 권장합니다.
-
Data load settings: 진행을 클릭합니다.
-
아래와 같은 화면이 나온 후에 모두 완료되면 창이 저절로 닫힙니다.
-
새롭게 SQL 창을 열어서 아래와 같이 ms_mbr에 데이터가 성공적으로 입력되었는지 조회해봅니다.
데이터 가져오기를 완료한 후에는 다음 SQL을 후처리를 합니다.
- 일부 데이터를 NULL로 업데이트 처리
- FK 생성 및 그에 맞는 인덱스 생성
- 통계 추가
--> DBeaver로 데이터 IMPORT 후 실행
UPDATE startdbpg.ms_shop SET shop_end_ymd = NULL WHERE shop_end_ymd = '';
COMMIT;
ALTER TABLE startdbpg.tr_event_entry ADD CONSTRAINT tr_event_entry_fk01 FOREIGN KEY (event_id) REFERENCES startdbpg.ms_event(event_id);
ALTER TABLE startdbpg.tr_event_entry ADD CONSTRAINT tr_event_entry_fk02 FOREIGN KEY (mbr_id) REFERENCES startdbpg.ms_mbr(mbr_id);
ALTER TABLE startdbpg.tr_event_entry ADD CONSTRAINT tr_event_entry_fk03 FOREIGN KEY (shop_id) REFERENCES startdbpg.ms_shop(shop_id);
ALTER TABLE startdbpg.cm_base_cd ADD CONSTRAINT cm_base_cd_fk01 FOREIGN KEY (base_cd_dv) REFERENCES startdbpg.cm_base_cd_dv(base_cd_dv);
ALTER TABLE startdbpg.ms_item ADD CONSTRAINT ms_item_fk01 FOREIGN KEY (item_cat) REFERENCES startdbpg.ms_item_cat(item_cat);
ALTER TABLE startdbpg.ms_item_prc_hist ADD CONSTRAINT ms_item_prc_hist_fk01 FOREIGN KEY (item_id) REFERENCES startdbpg.ms_item(item_id);
ALTER TABLE startdbpg.tr_ord ADD CONSTRAINT tr_ord_fk01 FOREIGN KEY (mbr_id) REFERENCES startdbpg.ms_mbr(mbr_id);
ALTER TABLE startdbpg.tr_ord ADD CONSTRAINT tr_ord_fk02 FOREIGN KEY (shop_id) REFERENCES startdbpg.ms_shop(shop_id);
ALTER TABLE startdbpg.tr_ord_det ADD CONSTRAINT tr_ord_det_fk01 FOREIGN KEY (ord_no) REFERENCES startdbpg.tr_ord(ord_no);
ALTER TABLE startdbpg.tr_ord_det ADD CONSTRAINT tr_ord_det_fk02 FOREIGN KEY (item_id) REFERENCES startdbpg.ms_item(item_id);
CREATE INDEX tr_event_entry_fk01 ON startdbpg.tr_event_entry (event_id) ;
CREATE INDEX tr_event_entry_fk02 ON startdbpg.tr_event_entry (mbr_id) ;
CREATE INDEX tr_event_entry_fk03 ON startdbpg.tr_event_entry (shop_id) ;
CREATE INDEX cm_base_cd_fk01 ON startdbpg.cm_base_cd (base_cd_dv) ;
CREATE INDEX ms_item_fk01 ON startdbpg.ms_item (item_cat) ;
CREATE INDEX ms_item_prc_hist_fk01 ON startdbpg.ms_item_prc_hist (item_id) ;
CREATE INDEX tr_ord_fk01 ON startdbpg.tr_ord (mbr_id) ;
CREATE INDEX tr_ord_fk02 ON startdbpg.tr_ord (shop_id) ;
CREATE INDEX tr_ord_det_fk01 ON startdbpg.tr_ord_det (ord_no) ;
CREATE INDEX tr_ord_det_fk02 ON startdbpg.tr_ord_det (item_id) ;
CREATE INDEX tr_ord_x01 ON startdbpg.tr_ord (ord_dtm) ;
-- 클러스터 처리
CLUSTER startdbpg.cm_base_cd USING cm_base_cd_pk;
CLUSTER startdbpg.cm_base_cd_dv USING cm_base_cd_dv_pk;
CLUSTER startdbpg.cm_base_dt USING cm_base_dt_pk;
CLUSTER startdbpg.ms_event USING ms_event_pk;
CLUSTER startdbpg.ms_item USING ms_item_pk;
CLUSTER startdbpg.ms_item_cat USING ms_item_cat_pk;
CLUSTER startdbpg.ms_item_prc_hist USING ms_item_prc_hist_pk;
CLUSTER startdbpg.ms_mbr USING ms_mbr_pk;
CLUSTER startdbpg.ms_shop USING ms_shop_pk;
CLUSTER startdbpg.tr_event_entry USING tr_event_entry_pk;
CLUSTER startdbpg.tr_ord USING tr_ord_pk;
CLUSTER startdbpg.tr_ord_det USING tr_ord_det_pk;
-- VACUUM 처리
VACUUM startdbpg.cm_base_cd;
VACUUM startdbpg.cm_base_cd_dv;
VACUUM startdbpg.cm_base_dt;
VACUUM startdbpg.ms_event;
VACUUM startdbpg.ms_item;
VACUUM startdbpg.ms_item_cat;
VACUUM startdbpg.ms_item_prc_hist;
VACUUM startdbpg.ms_mbr;
VACUUM startdbpg.ms_shop;
VACUUM startdbpg.tr_event_entry;
VACUUM startdbpg.tr_ord;
VACUUM startdbpg.tr_ord_det;
-- 통계 처리
ANALYZE startdbpg.cm_base_cd;
ANALYZE startdbpg.cm_base_cd_dv;
ANALYZE startdbpg.cm_base_dt;
ANALYZE startdbpg.ms_event;
ANALYZE startdbpg.ms_item;
ANALYZE startdbpg.ms_item_cat;
ANALYZE startdbpg.ms_item_prc_hist;
ANALYZE startdbpg.ms_mbr;
ANALYZE startdbpg.ms_shop;
ANALYZE startdbpg.tr_event_entry;
ANALYZE startdbpg.tr_ord;
ANALYZE startdbpg.tr_ord_det;