[SQL] 산업정보관리론 Term Project

박유찬·2023년 5월 17일

프로젝트 개요

2022-2학기에 진행된 산업공학 전공 '산업정보관리론'에서 진행된 Term Project이다.

Project에는 다음과 같은 내용이 포함되어 있어야 한다.

구축하고자 하는 데이터베이스 시스템의 필요성 소개

데이터베이스 개념 설계 및 논리 설계

필수 포함사항

Database Requirement Analysis(상황 설명)

Conceptual Database Design (ER-Model)

데이터베이스 서비스 구축 및 서비스 구현

SQL (Relational Database Design (DDL), Data Insert SQL, View,
Function, Trigger), SQL/PSM(Procedure Definitions) 또는 응용프로그램

코드(Python, Java Script 등)

주제 선정

데이터베이스 주제 선정 기준의 핵심에 된 부분은 해당 주제가 정말로 RDBMS를 통한 데이터 관리가 필요한지의 여부였다. 다양한 주제의 후보안들이 나왔고, 그 중에서도 여행사 회사가 되었다고 가정하고 기차편과 숙소를 통합한 국내여행상품 데이터베이스를 구축하기로 하였다.
그 이유는 여행사 데이터베이스는 DB 관리의 측면에서도 매우 중요한 데이터베이스인데, 회원정보, 여행상품, 예약정보, 기차편, 숙박시설 등 여러 개의 테이블로 구성되어 있고, 기차, 숙박, 여행지 테이블을 결합하여 여행상품 테이블로 만들어야 하기 때문이다. 즉, 테이블 간 제약 조건이 까다롭고 치밀한 설계가 필요하기에 데이터베이스 구축이 꼭 필요하다고 판단했다.

테이블 설명 및 구현 서비스

고객, 고객등급, 예약내역, 결제내역, 리뷰, 여행상품, 도시, 행사, 기차 상품, 기차, 숙소 상품, 숙소 테이블로 데이터베이스를 구성하였고, 아래와 같은 서비스를 데이터베이스를 통해 제공하고자 하였다.

유저의 특성(연령대, 성별)과 일치하는 리뷰를 기반으로 높은 별점의 여행지를 추천하고 대표 리뷰를 제공한다.

각 행사 업체에서 발행하는 쿠폰이 유저가 예약한 여행상품에서 사용가능할 때 어플에 알람을 발송한다.

신규 가입 회원에세 쿠폰을 제공한다.

예약내역이 적고 계절에 어울리는 이색 여행지를 추천한다.

결제내역에 따라 고객 등급을 업데이트한다.

단순히 ERD 구성에서 끝나는 것이 아니라 View, Procedure, Trigger 등을 쿼리문을 사용해서 동적인 DB를 만드는 것이 최종 목표이다.

데이터베이스 구축

데이터베이스 구축 프로젝트는 크게 3가지의 과정으로 진행되었다.
첫번째로는 테이블간의 제약조건을 명확히 규정하는 일이었고, 두번째로는 규정된 내용을 바탕으로 데이터베이스의 ERD를 모델링하는 일이었다. 마지막으로는 동적인 SQL Query문을 위해 View, Procedure, Trigger를 만드는 과정이었다.

1. 제약 조건 설정

위와 같은 예시처럼 제약조건을 설정하였다. 제약 조건을 설정하는 데에는 큰 어려움은 없었고, 테이블간의 Cardinality와 Optionality를 고려하여 제약 조건을 정하였다.
특히, 아래 ERD에서도 설명하겠지만 Train 테이블과 Accommodation 테이블은 각각 TrainProduct와 AccommodationProdudct 테이블의 Subtype의 Id-Dependent인 관계로 표현되어 있어 해당 관계를 고려하여 제약조건을 설정하였다.

2. ERD

다른 테이블 ERD는 큰 문제없이 작성할 수 있었지만, 여행 상품의 경우 관계를 설정하는데 많은 고민이 들었다. 아래 내용을 ERD로 구현해야 했다.

여행사에서는 고객들에게 관광 목적의 '기차여행상품'을 판매하며, 기차여행상품은 기차표와 숙소표를 포함하고 있다. 하지만 고객들 중에서는 이미 숙소가 있어 기차표만 구매를 원할 수도 있고, 반대로 숙소 예약만 원할 수도 있기에 이를 반영하여 ERD 를 작성했다.

하지만 해당 내용을 작성하면서도 많은 선택사항이 있었다. 여행 상품의 경우 하나의 여행지만을 방문하는 것이 아니라 실제로는 다양한 여행지를 다니는 패키지 상품도 존재하며, 숙소의 경우에도 한 도시라도 여러 숙소가 포함된 상품들이 존재한다.
또한 교통 상품의 경우에도 편도 노선을 하나의 Instance로 담을지, 왕복 노선을 Instance로 담을지와 같은 선택사항이 많이 존재했고, 어떤 기준으로 모델링을 하느냐에 따라 ERD구조와 테이블이 크게 달라졌다.
아마 이번 프로젝트에 있어 가장 큰 고민거리이자 ERD 모델링에 있어 핵심이 되는 부분이었다고 생각하며, 실제 여행 상품과 유사하게 진행하지 못한 것이 한계점이라고 생각한다.

요약하자면, 하나의 기차 여행상품은 '기차 상품' + '숙소 상품'의 조합으로 구성되며, 이때 고객은 기차 여행상품, 기차 상품, 숙소 상품 모두 구매할 수 있는 선택권이 있기에 이를 Subtype 형식으로 표현했다. 만약 고객이 Subtype 에서 기차 상품, 숙소상품을 모두 선택하면 기차 여행 패키지 상품, 기차만 선택한다면 기차 상품, 숙소만 선택한다면 숙소 상품으로 간주한다. 해당 내용을 토대로 아래의 최종 ERD를 모델링하였다.

ERD(MYSQL)

3. View/Procedure/Trigger

View

welcome_coupon

신규회원인 유저들의 유저ID를 추출하여 이들을 대상으로 신규회원 이벤트 쿠폰을 발송 하는 쿼리를 welcome_coupon 뷰 형태로 구현했다.
SQL 쿼리와 이를 실행한 결과는 아래와 같다.

Stored Procedure

UserReview

여행상품번호와 성별을 입력하면 유사 연령대의 유저가 등록한 리뷰를 UX로 제공하는 서비스를 UserReview 프로시저로 구현하였다.

EventCoupon

UserID를 입력하면 해당 유저가 예약한 여행상품의 City에서 개최 예정인 EventName과 EventDetail을 추출하여 UX로 제공하는 서비스를 EventCoupon 프로시저로 구현하였다.
유저가 예약한 여행상품과 관련있는 이벤트 정보를 App Push 등의 광고성 메시지로 제 공한다면 더 많은 구매를 이끌어낼 수 있을 것이라 생각하여 해당 프로시저를 기획하게 되었다.

HighlyRatedSpots

기입된 유저와 측성이 일치하는 유저가 작성한 리뷰의 평점이 높은 여행상품을 추천하는 프로시저를 구현하였다.

RecommendSpots

과거 예약 내역이 존재하지만, 상대적으로 예약이 적은 상품을 계절별로 나누어 이색 상 품으로 소개하는 프로시저를 구현하였다.

Trigger

InsertPaymentTrigger

Insert trigger를 간단하게 요약하자면 고객이 예약을 진행했을 때, 예약 테이블에 Insert 된 데이터를 바탕으로 결제 테이블에 자동으로 데이터를 Insert하는 과정을 구현하는 트 리거이다.

UpdateGradeTrigger

UpdateGradeTrigger의 경우 InsertPaymentTrigger에 등록된 PaymentPrice를 토대로 새롭 게 유저 등급을 업데이트하는 트리거이다.

Project 후기

기존에는 단순히 SQL문을 통해 DML(Data Manipulation Langauge)로만 데이터베이스를 다뤄봤지만, 이번 프로젝트를 기회로 DDL(Data Definition Language)를 다룰 수 있는 좋은 기회가 되었다.

실제로 있는 데이터를 다루지 않고, 임의로 가상의 데이터를 샘플로만 생성해냈다는 점은 아쉽지만, 실제로 테이블을 만들어보고 테이블간의 제약조건을 설정함으로써 ERD 설계에 있어 어떠한 유의사항이 있는지를 확실하게 파악할 수 있었다.

또한 이러한 제약조건을 기반으로 데이터를 Insert, Delete, Update할 시에 어떤 데이터가 함께 사라지는지 직접 쿼리문을 통해 확인할 수 있었다. 마지막으로 실제 여행사 서비스에서 사용하는 것을 가정하고 다양한 쿠폰과 같은 서비스를 View, Stored Procedure, Trigger을 통해 구현함으로써, 실제로 어떻게 SQL쿼리를 통해 유저에게 유용한 서비스를 제공할 수 있는지 이해할 수 있었다.