[토이프로젝트] Ecommerce 비즈니스 성과 분석하기 (Feat.SQL) (2)

이 프로젝트는 미디엄의 'Project Data Analysis : Analyzing E-Commerce Business Performance'를 참고하여 진행했다. 원문 전체를 번역한 것이 아닌, 공부하다가 필요한 부분을 정리한 것으로 원문은 하단에 링크해두었다.

 

1편 : 개요 및 데이터 분석과제 정의 바로가기

[토이프로젝트] Ecommerce 비즈니스 성과 분석하기 (Feat.SQL) (1)

 

---

원문에는 자세히 소개되지 않았지만, 다운로드 받은 csv 데이터를 DBeaver에 Import를 할 경우 기본키(PK)와 외래키(FK) 지정이 되어있지 않은 것을 확인할 수 있다. 

어딘가 많이 허전한 테이블

그래서 이번에는 이렇게 CSV 형태로 데이터를 Import 한 경우 (Create table 로 테이블을 따로 지정한 후 데이터를 Insert한 경우가 아닌 GUI로 데이터를 불러온 경우) 외래키와 기본키를 지정하였다. ALTER 문을 활용하여 이 둘을 지정할 수 있다고 한다. 자세한 내용은 이 블로그를 참고하자.

 

참고로, 기본키란 관계형 데이터베이스에서 레코드의 식별자로 이용하기에 가장 적합한 것을 테이블마다 단 한 설계자에 의해 선택, 정의된 후보 키를 말한다. (위키백과) 즉, 테이블에서 데이터들을 식별해주는 유니크하며, NULL이 아닌 값을 의미한다. 외래키는 참조하는 측의 관계 변수는 참조되는 측의 테이블의 키를 가리킨다. 쉽게 말해 다른 테이블에 있는 기본키이며 테이블 간 관계를 이어준다고 생각하면 된다. 외래키를 지정하기 위해 참조되는 테이블은 기본키가 존재해야하고 그 기본키는 1) UNIQUE 할 것 2) NULL이 아닐 것 이어야한다. 

 

원문에서 보여준 ERD를 참고하여 `customers_dataset`의 기본키를 수정하는 쿼리를 작성하였다. 

-- 1) customers_dataset
ALTER TABLE customers_dataset 
MODIFY COLUMN customer_id VARCHAR(50) PRIMARY KEY;

-- 2) orders_dataset
ALTER TABLE orders_dataset 
MODIFY COLUMN order_id VARCHAR(50) PRIMARY KEY;

위의 쿼리를 커밋하고나면 `customers_dataset`과 `orders_dataset`에 기본키가 생성되고,

ALTER TABLE orders_dataset
ADD CONSTRAINT customer_id
FOREIGN KEY (customer_id)
REFERENCES customers_dataset (customer_id);

이 쿼리는 `orders_dataset`에 `customer_id`를 `customers_dataset`으로부터 외래키로 참조해준다. (참고로, 외래키를 지정할 원본 테이블과 참조하는 테이블 모두 기본키를 설정해주어야 [1822]에러를 피할 수 있다.)

외래키를 참조하고 나면 ERD에 위와같이 참조 관계가 표시된다.

동일한 작업을 모든 테이블에 대해 완료해주려고 했는데...원문 글쓴이의 ERD는 직접 데이터를 확인해보니 이상한 점이 있었다.

1. `order_reviews_dataset` 기본키은 review_id가 PK가 될 수 없는데 PK로 표기해놓았다. 

-- 에러 대응: UNIQUE 확인
SELECT COUNT(review_id) AS 건수, COUNT(DISTINCT(review_id)) AS 유니크건수
FROM order_reviews_dataset; -- review_id는 PK로 사용할 수 없음

실행결과

 기본키의 조건인 유니크한 데이터이어야 한다는 조건이 충족되지 않아 기본키로 지정할 수가 없다. 다른 컬럼을 살펴보았으나, 기본키로 지정될 컬럼이 없어 이 테이블은 기본키 지정을 생략한다. (다행히 이 테이블이 참조되는 관계도 없다. 이 포스트는 '분석'에 초점을 맞추므로 이 부분은 깊게 다루지 않고 넘어간다.)

 

2. sellers_dataset에 customers 외래키 추가하는 과정에서 아래와 같은 에러가 나타난다.

1822 Error

다른 블로그를 확인해보니 1822 에러는 FK 로 묶으려고 하는 테이블의 문제가 아닌 기존의 테이블의 기본키 가 설정되어 있지 않은 경우 발생하는 에러라고 한다.

원문 글쓴이가 제공하는 ERD는 중복이 존재하는 데이터에 대해서도 기본키로 지정되어 있어 혼란을 불러일으키나, 실제 데이터로 확인해본 결과 해당 데이터는 기본키가 존재할 수 없어 내 생각에는 글쓴이는 ERD를 그린 이유가 데이터 무결성을 위해 엄밀하게 데이터 베이스에도 제약조건을 전부 설정해놓기 위해서가 아니라 그저 테이블 간 관계성을 보여주기위해 그린 것 같다.

나도 데이터 베이스를 설계할 때 테이블마다 반드시 기본키가 지정되어 있어야 하는 줄 알았지만, 다른 의견을 가진 글도 발견하여 기본키 및 외래키 제약 조건은 지정하지 않고 진행하고자 한다.

 

그래도 해매는 와중에 기본키, 외래키 제약조건을 배울 수 있었다는 것에 의의를 두며....다음편은 실제 분석을 해보고자 한다.

 

원문 출처

Project Data Analysis : Analyzing E-Commerce Business Performance (By M. Hamzah)

https://muhammadhamzah8.medium.com/project-data-analysis-analyzing-e-commerce-business-performance-827ed8302213

Project Data Analysis : Analyzing E-Commerce Business Performance

 

참고자료

기본키의 정의 

https://ko.wikipedia.org/wiki/%EA%B8%B0%EB%B3%B8_%ED%82%A4

[MySQL] PRIMARY KEY, FOREIGN KEY 설정하는 다양한 방법

https://prinha.tistory.com/entry/MySQL-PRIMARY-KEY-FOREIGN-KEY-%EC%84%A4%EC%A0%95%ED%95%98%EB%8A%94-%EB%8B%A4%EC%96%91%ED%95%9C-%EB%B0%A9%EB%B2%95

[1822] Failed to add the foreign key constraint MySQL에러

https://yusang.tistory.com/104

https://abbo.tistory.com/292#:~:text=%ED%95%B4%EB%8B%B9%20%EC%97%90%EB%9F%AC%EB%8A%94%20FK%20%EB%A1%9C,%EA%B2%BD%EC%9A%B0%20%EB%B0%9C%EC%83%9D%ED%95%98%EB%8A%94%20%EC%97%90%EB%9F%AC%EC%9E%85%EB%8B%88%EB%8B%A4.

기본키는 꼭 지정해야할까?

https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=212062