📌 미니 프로젝트
오늘부터는 미니 프로젝트를 진행한다.
프로젝트라 해서 별건 아니고 요구사항분석 및 스키마 생성, ER-diagram, psql로 DB 생성 등...이다.
저번 JS에서도 미니 프로젝트를 했었는데, 필수조건을 제대로 인지하고 있지 않아 빼놓은 조건이 있었다. 이번에는 필요한 조건들을 모두 만족시켜야겠다.
- 필수 조건 : 요구사항 분석, SQL 질의, ERD, 제약조건, 정규화 비정규화, CURD, JOIN, 집계함수
✔ 주제
주제는 백화점(?)에서의 물건 거래 내역 관리 DB이다.
쉽게 하기 위해서 선정한 주제인데 생각보다 만들어야할 테이블이 많았다.
📃 요구사항 분석
1.고객은 고객의 일련번호(cust_id), 고객의 이름(cust_name), 고객의 전화번호(cust_phone), vip여부(is_vip)를 가진다.
2.vip는 고객의 일련번호와 이름, 전화번호를 가지며 등급(vip_level)와 응대를 위한 차번호(car_num)을 가진다.
3.직원은 각자의 일련번호(staff_id), 이름(staff_name), 전화번호(staff_phone), 입사년도(entry_year)를 가진다.
4.판매 물건은 식자재과, 장난감, 옷이 있다.
5.각각의 물건에는 id(item_id)와 이름(item_name)이 있으며, 가격(price)과 재고(amount)가 정해져 있다.
6.식자재는 유통기한(deadline)이 있다.
7.옷과 장난감은 제조회사(made_by)를 가진다.
8.판매 내역은 판매 날짜(sale_date)를 가지며, 고객, 판매한 직원, 물건의 id를 가져야하며, 각 물건에 대한 판매 개수(sale_amount)를 가진다.
9.vip의 차번호를 조회하여 결제 날짜를 확인할 수 있다.
10.직원이 판매한 판매 내역에 대해 확인할 수 있다.
11.판매 물건을 전체 조회할 수 있다.
12.고객의 결제 내역에 대해 불러올 수 있으며, 결제 가격에 대해 확인이 가능하다.
13.고객의 vip 여부를 수정할 수 있다.
14.고객이 멤버를 탈퇴하면 VIP 내역에서 삭제된다.
15.판매 물건을 삭제할 수 있다.
16.판매 물건의 개수는 수정이 가능하다.
📃 릴레이션 스키마
1.고객 스키마(customer)
(cust_Id(PK), cust_name, cust_phone, is_vip)
2.VIP 스키마(vip)
(vip_id(PK), vip_name(FK), vip_phone(FK), vip_level, car_num)
3.직원 스키마(staff)
(staff_id(PK), staff_name, staff_phone, entry_year)
4.음식 스키마(item_food)
(item_id(PK)(FK), item_name(FK), price, amount, deadline)
5.장난감 스키마(item_toy)
(item_id(PK)(FK), item_name(FK), price, amount, made_by)
6.옷 스키마(item_clothes)
(item_id(PK)(FK), item_name(FK), price, amount, made_by)
7.판매내역 스키마(sale_list)
(sale_id(PK), cust_Id(FK), staff_id(FK),item_id, sale_amount, sale_date)
8.전체 판매물건 스키마(all_item)
(item_id(PK), item_name, price, amount)
-
판매물건 조회시, 각 물건의 릴레이션을 조회하려면 각 테이블을 SELECT해서 UNION으로 묶는 과정이 필요하다.
쿼리문도 길어질 뿐만 아니라 ER 다이어그램도 잘 정렬되어있지 않은 것 같아 전체 물건의 릴레이션을 부모 릴레이션으로 설정하고 각 물건의 릴레이션을 자식 릴레이션으로 정하였다. -
각 물건의 id와 name만 FK를 설정하고 수정시 동시에 바뀌도록 ON UPDATE CASCADE를 추가하였다.
💬 SQL문 정리
요구사항 및 스키마에 따른 SQL문을 CRUD로 정리해보았다.
CREATE
CREATE DATABASE department_store; // DB 생성
CREATE TABLE customer( // 고객 테이블
cust_id NUMERIC(4) PRIMARY KEY,
cust_name VARCHAR(10) UNIQUE NOT NULL,
cust_phone VARCHAR(13) UNIQUE,
is_vip BOOLEAN NOT NULL
);
CREATE TABLE vip(
vip_id SERIAL REFERENCES customer(cust_id) ON DELETE CASCADE,
vip_name VARCHAR(10) REFERENCES customer(cust_name) ON DELETE CASCADE,
vip_phone VARCHAR(13) REFERENCES customer(cust_phone) ON DELETE CASCADE,
vip_level VARCHAR(3) NOT NULL,
car_num NUMERIC(4)
);
.
.외래키에 대한 제약조건 위반(ERROR : 40280) : 부모 릴레이션에서의 칼럼이 UNIQUE가 아닐 때 발생한다. 여러개를 받고싶다면 각각의 칼럼에 REFERNCES를 추가하면 해결된다. (왜 따로따로 해야 해결되는지는 모르겠다...)
- ON DELETE CASCADE : 부모 또는 자식이 삭제될때 같이 삭제되도록 하기 위한 코드
요구사항에 맞게 CREATE와 INSERT를 하여 값을 추가하였다.
다음은 요구사항을 바탕으로 만들어진 ER 다이아그램이다.
READ
// 판매 물건 전체를 조회
SELECT
item_id AS '일련번호', item_name AS '물건이름', price AS '가격', amount AS '재고'
FROM item_food, item_clothes, item_toy // item 릴레이션을 조인
// 고객 한명의 당일 날짜 결제 내역
SELECT
sl.sale_date AS 판매 날짜, cu.cust_id, cu.cust_name, st.staff_name, (SELECT item_id AS 물건 이름, price AS 총 가격, sale_a
FROM
customer AS cu, staff AS st, sale_list AS slUPDATE & DELETE
요구사항에 맞게 하면서 한 테이블이 수정되었을 때 연관된 테이블도 같이 수정되려면 UPDATE와 DELETE를 동시에 수행해야 한다.
SQL에서는 동시에 수행하지 못하기 때문에 값을 수정하면 DELETE하는 것으로 연관시켰다.
// 고객 릴레이션에서의 VIP 여부를 수정
UPDATE customer SET is_vip = false
WHERE cust_id = 1004;
// 고객 릴레이션 확인 후 vip 릴레이션에서 삭제
DELETE FROM vip
WHERE vip_id IN
(SELECT cust_id FROM customer
WHERE is_vip = false);
// 전체 item의 재고를 수정
UPDATE all_item SET amount = 98;
// 수정된 재고가 각 릴레이션과 다르면 수정
UPDATE item_food SET amount = all_item.amount
FROM all_item
WHERE all_item.amount <> item_food.amount;
// 전체 릴레이션에서 id값을 검색하여 그 릴레이션을 제거
DELETE FROM all_item
WHERE item_id = 20003;
// 제거시 외래키에 있는 ON DELETE CASCADE로 동시에 삭제됨여담
정규화를 위해 판매 물건들을 각 종류마다 분해했지만, 실제로 READ하는데에 불편함을 겪었다. 항상 정규화하는 것이 최고의 선택이 아니라 때에 따라 비정규화도 고려하는 것이 좋은 DB를 설계하는 방법이라는 것을 배웠다.
또, 외래키의 사용방법을 이해했다. 프로그래밍 언어에서 필요하다고 전부 참조하는게 아니라 변해서는 안되는 값들을 외래키로 하나만 설정하여 ON DELETE CASCADE처럼 전부 삭제되는 키값으로 활용하였다. 외래키 사용법에 대해 더 알아보면 좋을 것 같다.
발표를 못하면 말짱꽝...
