1. 학습 목표

1. RDBMS개념 적립
2. SQL 이해
3. DB 설계 방법 이해
4. 데이터베이스 용어 이해
5. AWS RDS 구축

2. 4주차 수업 후기

직접 DB 스키마를 설계하고 테이블을 만들어 단순히 read나 insert 하는 쿼리가 아닌 정말로 화면 상에서 데이터를 불러오기 위한 쿼리를 작성하는 경험을 통해 정말 많이 배운 것 같습니다. 특히 join 개념을 조금 더 알게 된 것 같아 굉장히 뿌듯한 실습이었습니다.

3. 실습

📝실습 체크리스트

• RDS 구축
• RDS 인코딩 및 타임존 구축
• 당근마켓, 배달의민족, 인스타그램, 야놀자 중 프로젝트를 정해서 DB 설계
• 엑셀 시트에 앱 화면들을 10개이상 캡처한 후 리소스 추출
• 실제 화면에 연결된 수많은 테이블을 조합한 복잡한 쿼리를 10개이상 만들어오기
• ✨Challenge Mission✨
• 실제 화면에 연결된 수많은 테이블을 조합한 복잡한 쿼리를 20개이상 만들어오기

실습 내용 공유

야놀자를 선정하여 이번 실습 프로젝트를 진행하였습니다.

다음과 같이 ER 다이어그램을 구성했습니다.

데이터그램 설계 pdf 문서
데이터그램 설계 png
DB 스키마 설계 및 데이터 포함 엑셀 파일

작성한 쿼리문은 다음 10개입니다. 사용자로부터 입력을 받을 값들은 SET로 미리 지정했습니다.

1. 예약 가능 숙소 검색

SET @conditionStart := '2021-10-27 18:00:00', @conditionEnd := '2021-10-28 10:00:00';
SELECT
    roomName,
    roomShortInfo,
    IF((room.bSookbak=1 and DATEDIFF(@conditionStart, @conditionEnd)=-1),dayPrice,timePrice) AS roomPrice,
    roomRemainCnt,
    defaultPersonCnt,
    maxPersonCnt
FROM room
INNER JOIN reservation ON room.roomIdx = reservation.roomIdx
INNER JOIN roomprice r on room.roomIdx = r.roomIdx
WHERE
    reserveStatus='active'
AND
    (@conditionStart >= reservation.reserveEnd
OR
    @conditionEnd <= reservation.reserveStart)
GROUP BY room.roomIdx;

바로 안 떠올라서 고민을 많이 했었지만 조금 생각해 보면 알 수 있었습니다. 예약 정보는 reservation 테이블에 저장되어 있으니, 방들을 쭉 조회해서 사용자가 원하는 숙박 시작 날짜와 끝 날짜에 대해서 사용자의 예약 시작 날짜 >= 예약 정보의 끝 OR 사용자의 예약 끝 날짜 <= 예약 정보의 시작 이면 겹치는 예약 없이 방들을 조회할 수 있었습니다.

2. 쿠폰 사용 가능한 숙소 중 예약 가능한 숙소 조회

SET @couponIdx := 1;
SELECT
    roomName,
    roomShortInfo,
    IF((room.bSookbak=1 and DATEDIFF(@conditionStart, @conditionEnd)=-1),dayPrice,timePrice) AS roomPrice,
    roomRemainCnt,
    defaultPersonCnt,
    maxPersonCnt
FROM room
INNER JOIN reservation ON room.roomIdx = reservation.roomIdx
INNER JOIN roomprice r on room.roomIdx = r.roomIdx
INNER JOIN couponavaillist c on room.roomIdx = c.roomIdx
WHERE
    reserveStatus='active'
AND
    c.couponIdx = @couponIdx
AND
    (@conditionStart >= reservation.reserveEnd
OR
    @conditionEnd <= reservation.reserveStart)
GROUP BY room.roomIdx;

1번의 연장선입니다. 각 쿠폰 적용이 가능한 숙소 couponAvailList를 참조하여 방 목록을 조회합니다.

3. 방 예약

SET @memberIdx := 2, @couponIdx := 1, @companyIdx := 3,  @roomIdx := 7, 
@reserveType := '0', @reserveStart := '2021-10-19 18:00:00', @reserveEnd := '2021-10-19 21:00:00';
INSERT INTO reservation
(memberIdx, couponIdx, companyIdx, roomIdx, price, reserveType, reserveStart, reserveEnd)
VALUES(
    @memberIdx,
    @couponIdx,
    @companyIdx,
    @roomIdx,
    (SELECT IF(@reserveType=1, dayPrice, timePrice)
                *IFNULL(1-discountRate*0.01, 1)
                -IFNULL(discountprice, 0) FROM roomprice
        INNER JOIN couponavaillist as couponavail on roomprice.roomIdx = couponavail.roomIdx
        INNER JOIN coupon as coupon on couponavail.couponIdx = coupon.couponIdx
    WHERE couponavail.roomIdx=@roomIdx and couponavail.couponidx=@couponIdx),
    @reserveType,
    @reserveStart,
    @reserveEnd)

방마다 적용 가능한 할인 쿠폰 인덱스를 입력 받아 할인을 적용한 값을 reservation 테이블에 삽입하였습니다.

4. 숙소에 후기 남기기

SET @memberIdx := 3, @companyIdx := 3, @roomIdx := 7, @reserveIdx := 9, @rating := 4.5, @reviewDescription := '재방문 의사 있어요~';
INSERT INTO review
(memberIdx, companyIdx, roomIdx, reserveIdx, rating, reviewDescription, reviewReply)
VALUES
(@memberIdx, @companyIdx, @roomIdx, @reserveIdx, @rating, @reviewDescription, null);

평범한 INSERT INTO ~ VALUES ~ 구문입니다.

5. 후기에 답변 남기기

SET  @reviewIdx := 6, @reviewReply := '감사합니다!! 더욱 노력하는 업장이 되도록 하겠습니다!!';
UPDATE review
SET reviewReply = @reviewReply
WHERE reviewIdx = @reviewIdx;

점주 버전 어플리케이션을 사용해보지 않아 잘 모르지만, 고객들이 남긴 리뷰를 보고 reply를 단다면 reviewIdx로 조회 및 업데이트 할 것이라 생각했습니다.

6. 숙소 리뷰 목록 확인하기

SET  @companyIdx := 3;
SELECT roomName, roomCategory, memberNick, rating, reviewDescription, reviewReply, createdDate
FROM review
INNER JOIN company c on review.companyIdx = c.companyIdx
INNER JOIN room r on review.roomIdx = r.roomIdx
INNER JOIN member m on review.memberIdx = m.memberIdx
WHERE c.companyIdx=@companyIdx;

이용객의 닉네임, 리뷰 별점, 리뷰와 점주 답글, 방 기본 정보와 리뷰 날짜를 반환합니다.

7. 숙소 카테고리 별 예약 제일 많은 순 조회

SELECT companyName, companyRating, minPrice, companyReviewCnt, companyImageUrl
FROM company
INNER JOIN reservation r on company.companyIdx = r.companyIdx
INNER JOIN companyimagelist c on company.companyIdx = c.companyIdx
GROUP BY companyName
HAVING COUNT(companyName) > 0
ORDER BY COUNT(companyName) DESC
LIMIT 5;

리뷰 테이블에서 가장 많이 기록된 5개의 숙소의 이름, 평균 별점, 최소 금액, 리뷰 갯수, 대표 사진 썸네일을 가져옵니다.

8. 숙소 카테고리 별 찜 제일 많은 순 조회

SELECT companyName, companyRating, minPrice, companyReviewCnt, companyImageUrl
FROM company
INNER JOIN companyimagelist c on company.companyIdx = c.companyIdx
INNER JOIN likelist l on company.companyIdx = l.companyIdx
GROUP BY l.companyIdx
HAVING COUNT(l.companyIdx) > 0
ORDER BY COUNT(l.companyIdx) DESC
LIMIT 5;

멤버별 찜한 숙소 정보를 저장하는 likelist 테이블에서 group by로 정렬하여 찜 수가 많은 숙소를 내림차순으로 5개 가져옵니다.

9. 숙소 판매자 정보 조회

SET @companyIdx='2';
SELECT ownerName, businessName, ownerAddress, ownerEmail, ownerPhoneNumber, businessNumber
FROM companyowner
INNER JOIN company c on companyowner.companyIdx = c.companyIdx
WHERE c.companyIdx=@companyIdx;

숙소 판매자 정보가 기록된 CompanyOwner 테이블에서 해당 정보를 읽어옵니다.

10. 지역별 숙소 조회

SET @searchCondition='서울특별시';
SELECT companyName, companyRating, companyReviewCnt, availTimeStart, minPrice, category, companyImageUrl
FROM company
INNER JOIN location l on company.locationIdx = l.locationIdx
INNER JOIN companyimagelist c on company.companyIdx = c.companyIdx
WHERE (l.cityInfo=@searchCondition OR l.provinceInfo=@searchCondition);

검색어 중 하나라도 도 정보 / 시,군,구 정보와 일치하는 경우 company 정보를 읽어옵니다.

4. 핵심 키워드

• RDBMS : Relational DataBase Management System 입니다. 관계형 모델을 기반으로 하는 데이터베이스 관리 시스템 유형입니다. 관계형 모델이란, 실제 세계의 데이터를 관계 (Relation)라는 개념을 사용해 표현하는 데이터 모델입니다. 예를 들어 사과를 좋아하는 사람 A가 있다고 합시다. 그렇다면 A와 사과는 관계가 있고, 이는 곧 'A가 사과를 좋아한대' 라는 데이터가 됩니다. 이런 형태의 데이터를 저장하기 위한 관리시스템이 바로 RDBMS입니다.

• SQL : Structured Query Language입니다. RDBMS의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어입니다.

  • DDL : Data Definition Language, 데이터 정의 언어입니다. 데이터 베이스를 정의하는 언어이며 CREATE, ALTER, DROP, TRUNCATE와 같은 언어가 이에 속합니다.
  • DML : Data Manipulation Language, 데이터 조작 언어입니다. 데이터베이스에 입력된 레코드를 조작하는 언어이며 SELECT, INSERT, UPDATE, DELETE와 같은 언어가 이에 속합니다.
  • DCL : Data Control Language, 데이터 제어 언어입니다. 데이터베이스에 접근하거나 권한 설정과 밀접한 연관이 있는 언어이며 GRANT, REVOKE, COMMIT, ROLLBACK와 같은 언어가 이에 속합니다.

• DataBase 용어 : RDB 기준으로 작성하였습니다.

  • 테이블(Table) : RDB에서, 관계에 대한 데이터인 필드(Field)를 2차원으로 저장하는 집합체를 테이블이라고 합니다.
  • 행(Row) : 하나 이상의 열이 들어 있는 가로 부분입니다.
  • 열(Colum) : 하나의 데이터 유형을 가진 데이터 행이 하나 이상 들어있는 세로 파트입니다.
  • 스키마(Schema) (외부 스키마 / 개념 스키마 / 내부 스키마) : https://coding-factory.tistory.com/216 를 참고하였습니다.
    • 외부 스키마 : 외부 스키마는 사용자나 프로그래머가 각각 필요로 하는 DB의 논리적 구조를 정의한 것입니다. 사용자 입장에서 뷰(View)로, 같은 데이터베이스에 대해서 서로 다른 관점을 정의할 수 있도록 허용합니다.
    • 개념 스키마 : 개념 스키마는 DB의 전체적인 논리 구조로, 모든 프로그램이나 사용자가 필요로 하는 조직 전체의 DB로 하나만 존재합니다. 전체적인 뷰(View)로, 우리가 스키마라는 용어를 말할 때는 보통 개념 스키마를 말하는 경우가 많습니다.
    • 내부 스키마 : 내부 물리적 저장장치의 입장에서 본 DB 구조로, 물리적인 저장장치와 밀접 계층입니다. 저장 스키마라고도 불립니다. 실제로 DB에 저장될 레코드의 물리적인 구조를 정의하고, 시스템을 설계하는 입장에서 보는 관점의 스키마입니다.

• 유일성과 최소성 : https://moonibot.tistory.com/61를 참고하였습니다.

• 키

  • 슈퍼키 : 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키입니다. 모든 튜플에 대해 유일성을 만족하지만 희소성은 만족하지 못합니다.
  • 대체키 : 후보키 중 기본키가 아닌 후보키들입니다.
  • 후보키 : 릴레이션을 구성하는 속성들 중 튜플을 유일하게 식별하기 위해 사용되는 속성들의 부분집합입니다. 유일성과 최소성을 모두 만족합니다.
  • 기본키 : PK로 줄여서도 말하는 기본키는 후보키들 중에서 특별히 선정된 키로 중복된 값을 가질 수 없습니다. 따라서 유일성과 최소성을 모두 만족하며 또한 NULL 값을 가질 수 없다는 특징을 가지고 있습니다.
  • 외래키 : FK로 줄여서도 말하는 외래키는 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합을 의미합니다. 릴레이션 간의 관계를 표현할 때 사용하며 참조 릴레이션의 기본키와 동일한 키 속성을 가지는 것이 특징입니다.

• 1:1 관계 / 1:N관계 / N:M관계 : 개념이 이해하는데 좀 어려워서 헤맸었는데, 정리하고 보니 딱 하나로 귀결 될 수 있을 것 같습니다.

  테이블의 같은 컬럼에 해당 값이 여러 개 존재할 수 있는가?

테이블의 한 컬럼에 FK로 다른 릴레이션을 참조한다고 할 때, 해당 외래키가 여러개 존재할 수 있으면 N관계에 속합니다.
사람 A가 좋아하는 과일에 대한 정보를 다음 테이블 PersonFruitList 테이블로 저장한다고 하면,

id	사람 이름	좋아하는 과일(의 기본키)
1	A	사과
2	A	귤
3	B	오렌지
...	...	...

과일 정보는 Fruit 테이블에 저장되어 있다고 합시다.
알아보기 쉽게 사과, 귤, 오렌지라고 적었지만 각각에 해당하는 기본키가 FK로 참조되어 있다면 설계에 따라 다음과 같이 경우가 나뉩니다.

1) Person과 Fruit 테이블이 1:1 관계를 가질 때 :
PersonFruitList에는 A라는 사람이 하나, 과일도 하나가 들어와야 합니다. 즉 서로 다른 사람은 서로 다른 과일을 좋아해야 하고 한 사람 당 하나의 과일 밖에 좋아하지 못합니다.
2) Person과 Fruit 테이블이 1:N 관계를 가질 때 :
PersonFruitList에 A라는 사람이 하나 들어와야 하는 건 동일합니다. 하지만 서로 다른 사람도 같은 과일을 좋아할 수 있습니다. 즉 사과를 서로 다른 여러 사람이 좋아할 수 있습니다.
3) PersonFruitList와 Fruit 테이블이 N:M 관계를 가질 때 :
PersonFruitList에 A 뿐만 아니라 모든 사람이 여러 개의 과일을 좋아할 수 있습니다. 동일한 사람 정보도 여러 개 들어올 수 있으며, 동일한 과일 정보도 여러 개 들어올 수 있습니다. 이제야 세상이 평화로워졌네요 🧐

5. 알아두면 좋을 SQL 연산자 및 함수 정리

SQL 연산자

추가적으로 알아보면 좋을 SQL 함수

1. COUNT , SUM 집계 함수
2. 문자형 함수
3. where 절 조건
4. 서브 쿼리
5. 반올림, 버림 함수
6. Group By
7. is, null, is not null
8. JOIN (InnerJoin / Left Join / Right Join / OuterJoin / Full Join )