AQueryTool을 이용한 DB 설계, 한 방 쿼리 작성

ERD

 

Entity Relationship Diagram의 약자로, 개체-관계 다이어그램이라고 불린다.

ERD는 데이터들의 관계를 시각적으로 표현하는 도표이며 몇 가지 규칙이 있다.

여기서 A는 부모, B는 자식을 의미하며 "~로 구성되어 있다."는 "~을 포함하고 있다."와 같은 의미이다. 그림에는 없지만 실선과 점선으로 관계를 구분하는데 실선은 부모 테이블의 기본키를 자식 테이블이 가지고 있으며 이를 기본키로 사용하는 경우에, 점선은 부모 테이블의 기본키를 자식테이블이 가지고 있지만 이를 기본키로 사용하지 않는 경우에 사용한다.

 

 

 

 

넷플릭스 ERD 설계

 

넷플릭스 UI를 참고해 직접 ERD 설계를 해보았다. 넷플릭스의 실제 ERD와는 전혀 관련없다.

 

ERD 설계 시 주의사항

◾  User의 status는 활성, 휴면, 탈퇴 등 다양하므로 넉넉한 크기를 할당해두는 것이 좋다. 

◾  비밀번호는 암호화되어 저장되기 때문에 넉넉한 크기로 잡아두어야 한다.

◾  휴대폰 번호의 경우 INT로 선언하게 되면 010의 0이 지워지므로 VARCHAR로 선언해야 한다.

◾  미디어 관련 파일은 URL을 통해 가져오기 때문에 TEXT로 선언해야 한다.

◾  테이블마다 isRegisted, isUpdated, isDeleted를 둔다.

◾  하나의 컬럼이 PK, FK 모두에 해당되지 않도록 한다. 테이블마다 다른 테이블과 독립적인, 무관한

    PK를 두는 것이 좋다.

 

◾  1:N 관계인 경우 N에 해당되는 테이블에 FK를 지정한다.

 

◾  N:M 관계인 경우 중간에 두 개의 FK를 갖는 테이블을 생성하고 FK를 통해 각 테이블을 참조한다.

     > User, Movie 간 중간 테이블 : UserFavourite, UserEval, MovieStore, UserWatchMovie,

                                                   SearchKeyword

     > Actor, Movie 간 중간 테이블 : MovieCast

     > Produer, Movie 간 중간 테이블 : MovieProduct

     > Genre, Movie 간 중간 테이블 : MovieGenre

     > Feature, Movie 간 중간 테이블 : MovieFeature

     > Country, Movie 간 중간 테이블 : MovieOpenInfo 

 

 

모든 테이블 생성 SQL을 클릭하면 모든 테이블들에 대한 CREATE문이 한 번에 txt파일로 생성된다. 

해당 파일을 Workbench 스크립트에 복사하여 실행하면 아래와 같이 테이블들이 생성된 것을 확인할 수 있다.

 

 

테이블마다 Result Grid에 데이터를 입력하는 과정을 반복했다. 

FK로 연결된 컬럼들의 경우 각 테이블의 데이터 값을 서로 동일하게 입력해야 한다는 점을 주의하자.

 

 

 

 

넷플릭스 한 방 쿼리

 

1. 모든 프로필

SELECT nickname, imagePath
FROM UserProfile;

프로필 닉네임과 이미지를 모두 출력하였다.

 

 

2. 특정 프로필

SELECT UserProfile.imagePath, UserProfile.nickname,UserLevel.levelCode, UserLevel.desc
FROM UserProfile JOIN UserLevel ON UserProfile.levelId = UserLevel.levelId
WHERE UserProfile.id = 2100;

프로필 이미지, 닉네임, 사용자가 설정한 관람등급의 이미지와 설명을 출력하였다.

 

 

3-1. TV 프로그램의 모든 카테고리

SELECT title
FROM Genre
WHERE id BETWEEN 1000 AND 1099;

TV 프로그램과 영화의 카테고리가 완전히 동일하지 않기 때문에 TV 프로그램의 카테고리 식별자는 1000~1099 사이의 숫자로, 영화의 카테고리 식별자는 1100~1199 사이의 숫자로 지정했다.

 

 

3-2. 영화의 모든 카테고리

SELECT title
FROM Genre
WHERE id BETWEEN 1100 AND 1199;

 

 

4-1. 영화의 정보

SELECT UserProfile.imagePath, Movie.previewPath, Movie.Title, Movie.prodYear, 
       MovieRating.ratingImage, Movie.movieTime, Movie.quality, Movie.moviePath, 
       (Movie.movieTime-UserWatchMovie.watchDuration) AS remaningTime, Movie.story
FROM Movie LEFT JOIN MovieRating ON Movie.ratingId = MovieRating.ratingId
	LEFT JOIN UserWatchMovie ON Movie.id=UserWatchMovie.movieId
	LEFT JOIN UserProfile ON UserWatchMovie.profileId = UserProfile.id
WHERE profileId=2100 && Movie.id=6000;

프로필 이미지, 영화 미리보기 영상, 영화 제목, 영화 제작년도, 영화의 관람등급 아이콘, 영화의 러닝타임, 영화의 화질, 남은 시청 시간, 영화의 줄거리를 출력하는 쿼리문이다.

UserProfile, Movie, MovieRating, UserWatchMovie 테이블을 JOIN 하였다.

 

 

4-2. 영화의 정보

SELECT t.actorName
FROM Movie LEFT JOIN (
	SELECT movieId, name AS actorName
	FROM MovieCast LEFT JOIN Actor ON MovieCast.actorId = Actor.id) AS t
ON Movie.id = t.movieID
WHERE Movie.id=6000;

영화의 출연한 배우들의 이름을 출력한 모습이다.

SELECT t.producerName
FROM Movie LEFT JOIN (
	SELECT movieId, name AS producerName
	FROM MovieProduct LEFT JOIN Producer 
    ON MovieProduct.producerId = Producer.id) AS t
ON Movie.id = t.movieID
WHERE Movie.id = 6000;

영화를 제작한 사람들의 이름을 출력한 모습이다.

SELECT MovieRating.ratingImage
FROM Movie LEFT JOIN MovieRating
ON Movie.ratingId=MovieRating.ratingID
WHERE Movie.id = 6000;

영화의 관람등급 아이콘을 출력한 모습이다.

SELECT t.title
FROM Movie LEFT JOIN (
	SELECT movieId, title 
    FROM MovieGenre LEFT JOIN Genre 
    ON MovieGenre.genreId = Genre.id ) AS t
ON Movie.id = t.movieId
WHERE Movie.id = 6000;

영화의 장르를 출력한 모습이다.

 

 

5-1. TV 프로그램의 정보

SELECT Movie.previewPath, Movie.prodYear, MovieRating.ratingImage, MovieSeries.season, Movie.quality, MovieSeries.seriesPath, Movie.story 
FROM Movie LEFT JOIN MovieRating ON Movie.ratingId = MovieRating.ratingId
	LEFT JOIN MovieSeries ON Movie.id = MovieSeries.movieId
WHERE MovieSeries.episode = 1 && Movie.id = 6001

TV 프로그램의 미리보기 영상, 제작년도, 관람등급 아이콘, 시즌, 화질, 1화 영상주소, 줄거리를 출력한 화면이다.

미생의 경우 미리보기 영상이 따로 없기 때문에 해당 컬럼이 비어있는 것을 볼 수 있다.

 

 

5-2. TV 프로그램의 정보

SELECT t.actorName
FROM Movie LEFT JOIN (
	SELECT movieId, name AS actorName
	FROM MovieCast LEFT JOIN Actor ON MovieCast.actorId = Actor.id) AS t
ON Movie.id = t.movieID
WHERE Movie.id = 6001;

TV 프로그램에 출연한 배우들의 이름을 출력했다.

SELECT t.producerName
FROM Movie LEFT JOIN (
	SELECT movieId, name AS producerName
	FROM MovieProduct LEFT JOIN Producer ON MovieProduct.producerId = Producer.id) AS t
ON Movie.id = t.movieID
WHERE Movie.id = 6001;

TV 프로그램을 제작한 사람들의 이름을 출력했다.

SELECT MovieRating.ratingImage
FROM Movie LEFT JOIN MovieRating
ON Movie.ratingId=MovieRating.ratingID
WHERE Movie.id = 6001;

TV 프로그램의 관람등급 아이콘을 출력한 모습이다.

SELECT t.title
FROM Movie LEFT JOIN (
	SELECT movieId, title FROM MovieGenre 
    LEFT JOIN Genre ON MovieGenre.genreId = Genre.id ) AS t
ON Movie.id = t.movieId
WHERE Movie.id = 6001;

TV 프로그램의 장르를 출력한 모습이다.

SELECT t.title
FROM Movie LEFT JOIN (
	SELECT movieId, title FROM MovieFeature 
    LEFT JOIN Feature ON MovieFeature.featureId = Feature.id ) AS t
ON Movie.id = t.movieId
WHERE Movie.id = 6001;

 영화와 달리 TV 프로그램은 프로그램 특징에 관한 정보를 출력하므로 위와 같은 쿼리를 작성했다.

 

 

5-3. TV 프로그램 회차별 정보

SELECT MovieSeries.seriesPath,UserWatchMovie.watchDuration, MovieSeries.episode, MovieSeries.seriesTitle, MovieSeries.seriesTime, MovieSeries.seriesStory
FROM Movie LEFT JOIN MovieSeries ON Movie.id = MovieSeries.movieId
	LEFT JOIN UserWatchMovie ON MovieSeries.id=UserWatchMovie.movieSeriesId
	LEFT JOIN UserProfile ON UserWatchMovie.profileId = UserProfile.id
WHERE UserWatchMovie.profileId=2100 && Movie.id=6001;

TV 프로그램의 각 회차마다 영상주소, 회차, 제목, 러닝타임, 줄거리를 출력한 모습이다. 사용자가 각 회차를 시청한 시간 또한 추가로 출력해주었다.

 

 

6. 사용자가 시청 중인 콘텐츠 목록

SELECT Movie.thumbnailPath, Movie.movieTime, Movie.moviePath, MovieSeries.season, MovieSeries.episode, MovieSeries.seriesPath
FROM UserWatchMovie LEFT JOIN Movie ON UserWatchMovie.movieId = Movie.Id
	LEFT JOIN MovieSeries ON UserWatchMovie.movieSeriesId = MovieSeries.id
 WHERE watchDuration > 0

사용자가 시청 중인 영상물의 썸네일을 출력했다. 영화의 경우 영상물의 러닝타임과 주소를 출력하고 TV 프로그램의 경우 사용자가 시청 중인 회차에 관한 정보와 주소를 함께 출력했다.

 

 

7. 공개 예정인 콘텐츠 목록

SELECT Movie.previewPath, MovieOpenInfo.openDate, Movie.title, Movie.story
FROM MovieOpenInfo LEFT JOIN Movie ON MovieOpenInfo.movieId = Movie.id
WHERE MovieOpenInfo.openDate > CURRENT_TIMESTAMP();

공개 예정인 영상물의 미리보기 영상, 영상물이 공개되는 날짜, 제목, 줄거리를 출력한 모습이다. 

SELECT t.title
FROM Movie LEFT JOIN (
	SELECT movieId, title 
    FROM MovieFeature 
    LEFT JOIN Feature ON MovieFeature.featureId = Feature.id ) AS t
ON Movie.id = t.movieId
WHERE Movie.id = 6003;

공개 예정인 영상물의 프로그램 특징을 출력한 모습이다.

 

 

8. 자주 검색된 콘텐츠 목록

CREATE VIEW MostSearched AS(
	SELECT Movie.id AS id, MovieSeries.seriesPath AS seriesPath
	FROM Movie LEFT JOIN MovieSeries ON Movie.id = MovieSeries.movieId 
	WHERE (Movie.moviePath IS NOT NULL) || (MovieSeries.episode=1));

SELECT Movie.id, Movie.title, Movie.thumbnailPath, Movie.moviePath, MostSearched.seriesPath
FROM (SELECT keyword, count(keyword)
		FROM SearchKeyword
		GROUP BY keyword
		ORDER BY count(keyword) desc) AS t 
LEFT JOIN Movie ON t.keyword = Movie.title
LEFT JOIN MostSearched ON MostSearched.id = Movie.id

가장 많이 검색된 영상물의 제목, 썸네일, 주소를 출력했다. TV 프로그램의 경우 첫 번째 회차의 주소를 출력했다.

 

 

9-1. 오프라인 저장한 콘텐츠 목록 

CREATE VIEW OfflineMovie AS (
	SELECT MovieStore.profileId, MovieStore.movieId, MovieStore.movieSeriesId, Movie.moviePath, Movie.thumbnailPath, 
		   MovieSeries.seriesPath, Movie.ratingId, Movie.title, MovieSeries.season, MovieSeries.seriesTitle, MovieSeries.seriesTime, 
		   Movie.movieTime, MovieSeries.seriesSize, Movie.movieSize
	FROM MovieStore 
		LEFT JOIN MovieSeries ON MovieStore.movieSeriesId = MovieSeries.id
		LEFT JOIN Movie ON MovieStore.MovieId = Movie.id);

SELECT distinct UserProfile.imagePath, UserProfile.nickname
FROM OfflineMovie LEFT JOIN UserProfile
ON OfflineMovie.profileId = UserProfile.id
WHERE OfflineMovie.profileId = 2100;

SELECT if(OfflineMovie.movieSize is null, OfflineMovie.thumbnailPath, OfflineMovie.moviePath) AS path, 
	   OfflineMovie.title, OfflineMovie.ratingId, if(OfflineMovie.movieSize is null, count(*), null) AS count, 
       if(OfflineMovie.movieSize is null, sum(OfflineMovie.seriesSize), OfflineMovie.movieSize) AS size
FROM OfflineMovie
WHERE OfflineMovie.profileId = 2100
GROUP BY OfflineMovie.movieId;

오프라인 저장한 영상물의 목록을 출력할 때 영화와 TV 프로그램에 조금 차이가 있다. 영화의 경우 영상물 주소, 관람등급, 크기를 출력하는 반면 TV 프로그램의 경우 썸네일, 제목, 관람등급, 저장한 회차의 개수, 저장한 회차의 크기들을 모두 합한 값이 출력되도록 했다.

 

 

9-2. 오프라인 저장한 콘텐츠의 상세정보 

CREATE VIEW OfflineMovie AS (
	SELECT MovieStore.profileId, MovieStore.movieId, MovieStore.movieSeriesId, Movie.moviePath, Movie.thumbnailPath, 
		   MovieSeries.seriesPath, Movie.ratingId, Movie.title, MovieSeries.season, MovieSeries.seriesTitle, MovieSeries.seriesTime, 
		   Movie.movieTime, MovieSeries.seriesSize, Movie.movieSize
	FROM MovieStore 
		LEFT JOIN MovieSeries ON MovieStore.movieSeriesId = MovieSeries.id
		LEFT JOIN Movie ON MovieStore.MovieId = Movie.id);

SELECT OfflineMovie.title, OfflineMovie.seriesPath, OfflineMovie.seriesTitle, OfflineMovie.seriesTime, OfflineMovie.seriesSize
FROM OfflineMovie
WHERE OfflineMovie.profileId = 2100 && OfflineMovie.movieId = 6002;

TV 프로그램의 제목, 각 회차별 영상물 주소, 제목, 러닝타임, 크기를 출력한 모습이다.

 

 

10. 내가 찜한 콘텐츠 목록

SELECT Movie.thumbnailPath
FROM UserFavourite 
	LEFT JOIN UserProfile ON UserProfile.id = UserFavourite.profileId
	LEFT JOIN Movie ON UserFavourite.movieId = Movie.id
WHERE UserProfile.id=2100;

사용자가 찜하거나 알림 설정한 영상물의 썸네일을 출력한 모습이다.

 

 

 

 

 

https://mjn5027.tistory.com/43