SQL STARTER (For PostgreSQL) DATABASE를 생성합니다.
1.
데이터베이스 생성(앞에서 DOCKER 설치 후 테스트하면서 생성했으면 생략합니다.)
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을 실행합니다.
-- startdbpg 스키마 추가하기. 데이터베이스 아래 스키마를 추가해, 해당 스키마에 테이블들을 추가한다.
CREATE SCHEMA IF NOT EXISTS startdbpg AUTHORIZATION postgres;
-- postgres(root) 사용자한테, 스키마 serach path 설정하기
ALTER ROLE postgres SET search_path = startdbpg, public;
SQL
복사
4.
테이블 생성
a.
앞에서 테스트로 만들던 테이블들이 있으면 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 처리
SQL
복사
b.
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 '응모결과일시';
SQL
복사
5.
데이터베이스를 구축하기 위해 데이터가 담긴 CSV 파일을 다운로드 합니다.
a.
아래 경로에서 파일을 다운로드한 후 압축을 풉니다. 압축을 풀면 총 12개의 CSV 파일이 존재합니다.
6.
DBeaver의 ‘데이터 가져오기’ 기능을 사용해, CSV 파일을 각각의 테이블에 INSERT합니다.
a.
DBeaver의 왼쪽 Database Navigator 창에서, 앞에서 만든 PG(PostgreSQL)의 startdbpg 컨넥션을 클릭해 Schemas의 목록을 새로고침합니다.
i.
Schemas에서 마우스 우클릭 후 팝업메뉴에서 새로고침 선택
ii.
startdbpg schema 아래에 새로 만든 테이블들이 존재하는지 확인합니다.
b.
새로 생성된 테이블들을 모두 선택 후 마우스 우클릭해 데이터 가져오기를 선택합니다.
i.
모든 테이블을 선택하기 위해 Ctrl을 누른채 위에서 새로 만든 12개 테이블을 하나씩 선택합니다.
ii.
테이블을 모두 선택한 후에 마우스 우클릭 > 데이터 가져오기를 선택합니다.
c.
Import Source: CSV에서 가져오기를 선택 후 다음을 클릭합니다.
d.
Input file(s): 왼쪽의 Source 항목을 하나씩 클릭해 오른쪽 Target과 같은 명칭의 CSV 파일을 하나씩 선택합니다. 모두 선택(총 12개)후 다음을 클릭합니다. 서로 다른 Source와 Target을 선택하지 않도록 주의합니다.
e.
f.
Tables mapping: Source와 Target이 제대로 매핑되었는지 확인 후 문제 없으면 다음을 클릭합니다. 매핑이 맞지 않으면, 처음부터 다시 작업하는 것을 권장합니다.
g.
Data load settings: 진행을 클릭합니다.
h.
아래와 같은 화면이 나온 후에 모두 완료되면 창이 저절로 닫힙니다.
i.
새롭게 SQL 창을 열어서 아래와 같이 ms_mbr에 데이터가 성공적으로 입력되었는지 조회해봅니다.
7.
데이터 가져오기를 완료한 후에는 다음 SQL을 후처리를 합니다.
a.
일부 데이터를 NULL로 업데이트 처리
b.
FK 생성 및 그에 맞는 인덱스 생성
c.
통계 추가
--> 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) ;
-- 통계 생성
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;
-- 베큠 수행
VACUUM FULL startdbpg.cm_base_cd;
VACUUM FULL startdbpg.cm_base_cd_dv;
VACUUM FULL startdbpg.cm_base_dt;
VACUUM FULL startdbpg.ms_event;
VACUUM FULL startdbpg.ms_item;
VACUUM FULL startdbpg.ms_item_cat;
VACUUM FULL startdbpg.ms_item_prc_hist;
VACUUM FULL startdbpg.ms_mbr;
VACUUM FULL startdbpg.ms_shop;
VACUUM FULL startdbpg.tr_event_entry;
VACUUM FULL startdbpg.tr_ord;
VACUUM FULL startdbpg.tr_ord_det;
SQL
복사