SQL_05 하룻데엔새싹 범 무서운 줄 모른다.(1편)

지금까지 sql 이론을 공부하면서 과연 이걸 얼마나 써먹을 수 있을까 하는 의문과 함께  SQL에 한 번 덤벼보기로 했습니다.

준비물: 오래 앉아있을 수 있는 엉덩이, 코드 타이핑할 솜주먹, 노가다를 해도 알아내고야, 완성하고야 말겠다는 의-지
가보자고요~!
 
주제는 등록된 영화나 드라마 판권을 결제해 소장할 수 있는 OTT플랫폼입니다.
왜냐구요? 제 취미가 '영화 판권 사서 보기'이기 때문에 첫 코드짜기를 완수하기 위해 흥미가 있는 것으로 준비했어요.
언어 공부를 할 때도 좋아하는 것으로 먼저 시작하라고 하잖아요~
 
제 목표는 5개의 테이블에 각 5개의 레코드를 삽입하고, 5개의 검색 질의 레코드를 만드는 거예요!
 

1. 관계 데이터베이스 스키마

1.1- 테이블 5개 정하기

먼저 신나는 마음으로 관계 데이터베이스 스키마를 짭니다.
어떤 테이블을 어떤 칼럼명을 부여해서 사용할지가 가장 고민이 되더라고요. 그래서 먼저 스키마를 짜두고 해당 스키마를 기준으로 해서 하나씩 테이블을 만들기로 했어요.

먼저 영화와 드라마 테이블이 있어야겠죠?
이걸 사는 고객이 있을테니 고객과 결제정보 테이블도 있어야 할 거예요!

5개의 테이블을 만들겠다고 목표를 정했는데... 딱 생각나는 게 4개 밖에 없더라고요ㅠ
열심히 머리를 굴리다가 자주 이용하던 OTT플랫폼이 네이버 시리즈온이라서 들어가봤더니 댓글 테이블을 만들 수 있을 거 같아 우여곡절 끝에 테이블 5개 정했답니다!
 

1.2- 칼럼명 정하기

그럼 각 테이블에 해당되는 칼럼명도 만들어줘야겠죠?

먼저 영화!
[영화 아이디]
영화를 식별할 PK를 어디에 줄 거냐 하면 당연히! 영화id겠죠?
이론 공부할 때 보니까 주민등록번호가 아닌 이상 대부분 id에 주더라고요. 실제로도 모두 개별 id에 PK를 부여해 관리하는 걸까? 하는 생각을 하면서 칼럼명 하나 get~♥
 
[영화 제목]
영화 제목을 알아야 어떤 영화인지 알 수 있으니 이름도 쏙쏙 넣어줍니다. 영화 이름은 없을 수 없으니 not null 제약조건을 걸어줄 거예요! 이름 절대 알려줘~
 
[영화 등록일자, 가격]
이 영화가 언제 이 플랫폼에 등록이 되었는지도 알아야겠죠? 등록일자 넣어주고 고객이 구매를 해야하니 가장 중요한 가격도 넣어줍니다! 
 
[장르, 상영시작일자]
원래는 이렇게만 정했는데 뭔가 아쉽더라고요. 하룻데엔새싹은 괜히 범을 안 무서워하는 게 아니었답니다...
욕심 가득한 채로 열정에 활활타서 몇 개를 더 추가하고 싶었던 나머지! 장르와 상영시작일자도 포함했어요.

장르를 넣고나니까 또 봤던 걸 써보고 싶더라고요~ 이러라고 제약조건을 배운 거 아니겠습니까!
CK를 부여해 장르엔 제가 좋아하는 'SF'와 '액션'을 슬쩍 넣어 사심을 담아주고 '드라마', '스릴러', '공포'까지 5가지 입장~
그렇게 완성된,

영화(id, 제목, 등록일자, 가격, 장르, 상영시작일자)

드라마도 똑같이 만들어주면 되겠죠?

드라마(id, 제목, 등록일자, 가격, 장르, 방영시작일자)

 
 
 

다음은 우리의 소중한 고객님 입장하시겠습니다~
 
[고객 아이디, 이름]
OTT플랫폼에 가입하는 데에는 주민등록번호까지는 필요하지 않더라고요.
그래서 이번에도 id로 고객을 식별해볼 겁니다! id에 PK 부여하고, 소중한 고객님의 성함이 없으면 안 되니 not null 제약조건을 걸어줍니다!
 
[pw]
id로 로그인을 하니 pw도 당연히 필요하겠죠? 이때다 싶어 uk를 써봅니다. 히히 신나요~ 모든 제약조건 한 번씩 다 써볼 수 있을 거 같아요.
 
[가입일자, 구매영화, 구매드라마]
가입일자도 넣어주고, 마이페이지 가면 구매한 영화나 드라마가 뜨잖아요! 그래서 구매영화, 구매드라마도 넣어줍니다!
드디어 fk를 써볼 수 있게 되었어요. 각각 영화테이블의 id, 드라마테이블의 id를 참고하면 될 거 같아요.
 
[작성댓글]
그리고 하나 더 fk를 활용할 수 있는 곳이 있죠. 바로 댓글, 작성한 댓글을 고객 테이블에 넣고 fk를 부여합니다.

고객(id, 이름, pw, 가입일자, 구매영화, 구매드라마, 작성댓글)
 
 
 

이제 댓글테이블의 칼럼을 정해볼게요.
 
[댓글 아이디]
댓글도 그 많은 댓글 중 식별을 해주어야 하니 pk를 부여할 댓글id를 줍니다.
 
[고객 아이디]
아까 고객테이블의 칼럼에 작성댓글을 넣어두고 fk를 설정하고 싶다고 했으니 여기에도 댓글테이블에서 어떤 고객이 댓글을 썼는지 알 수 있도록 고객id에 fk를 부여할 거예요. 그러면 고객테이블의 고객id를 참조할 수 있겠죠?
 
[댓글 내용]
댓글내용도 포함시켜주고 uk를 한 번 더 써보고 싶어서 내용에 넣어봅니다.
 
[점수]
평점이란 게 존재한다는 사실이 생각나더라고요. 그래서 이 테이블에 점수를 넣어주었어요!
저는 5개의 레코드만 생성하지만, 이 테이블에 들어가는 레코드의 수가 더 많아지면 나중엔 연산도 할 수 있겠죠?
그리고 보통 평점은 1~5점 사이에 존재하니까 점수에 ck 제약조건을 걸어주고 '1', '2', '3', '4', '5'로 제한할 거예요.
 
[작성일자]
마지막으로 작성일자까지 포함해줍니다.

댓글(댓글id, 고객id, 내용, 점수, 작성일자)
 
 
 

드디어 마지막, 결제정보테이블이에요!
 
[결제 아이디, 고객 아이디]
결제정보에도 pk를 부여할 결제id가 떠야 하고, 결제한 고객의 id를 확인할 수 있어야 해요! fk를 활용할 수 있겠네요.
 
[결제일자, 결제수단]
결제일자도 넣어주고, 결제수단도 넣어줍니다.
결제수단은 ck를 좀 더 써보고 싶어서 제약조건을 부여해주고 '신용카드', '카카오페이', '토스페이', '무통장입금', '포인트', '무료'로 제한해줄 거예요.

결제정보(결제id, 고객id, 결제일자, 결제수단)
 
 
 

-관계형 데이터베이스 스키마-

영화(id, 제목, 등록일자, 가격, 장르, 상영시작일자)
드라마(id, 제목, 등록일자, 가격, 장르, 방영시작일자)
고객(id, 이름, pw, 가입일자, 구매영화, 구매드라마, 작성댓글)
댓글(댓글id, 고객id, 내용, 점수, 작성일자)
결제정보(결제id, 고객id, 결제일자, 결제수단)

movie(id, name, registration_date, price, genre, run_date)
drama(id, name, registraion_date, price, genre, run_date)
customer(id, name, pw, join_date, bmovie, bdrarma, comm)
reply(id, cid, content, score, reply_date)
payment_info(id, cusid, paydate, payment_option)

 
이렇게 완성된 스키마를 테이블을 만들기 위해 참고할 표로 정리를 해봤어요.

customer	id varchar2(20)/pk, name nvarchar2(10)/not null, pw number(10)/uk, join_date date, bmovie varchar2(10)/fk, bdrama nvarchar2(10)fk, comm varchar2(15),fk
movie	id varchar2(10)/pk, name nvarchar2(15)/not null, registration_date date, price number(15), genre nvarchar2(35)/ck, run_date date
drama	id nvarchar2(10)/pk, name nvarchar2(15)/not null, registration_date date, price number(15), genre nvarchar2(35)/ck, run_date date
reply	id varchar2(15)/pk, cid varchar2(20)/fk, content nvarchar2(100)/uk, score number(5,1)/ck, reply_date date
payment_info	id nvarchar2(15)/pk, cusid varchar2(20)/fk, paydate date, payment_option nvarchar2(10)/ck

이제 이 표를 토대로 테이블을 만들겁니다.

*수정) 학교 시험기간이라서 끝내고 2편으로 돌아오도록 하겠습니다!