라이징 프로그래머2의 3주차 과제가 시작되었다. 이번 주차에는 데이터베이스를 다루며, 그중 일부분인 ERD 설계를 해보려고 한다. 본인은 AQueryTool을 이용해서 진행했고 데이터베이스는 MySQL을 사용했다.
ERD(Entity Relationship Diagram)
일명 개체-관계 다이어그램이라 불리는 ERD는 다음과 같다.
ER(엔티티 관계) 다이어그램은 사람, 개체 또는 개념과 같은 "엔티티"가 시스템 내에서 서로 어떻게 관련되는지를 보여주는 순서도 유형이다. ERD 또는 ER 모델이라고도 하며 직사각형, 다이아몬드, 타원 및 연결 선과 같은 정의 된 기호세트를 사용하여 엔티티, 관계 및 속성의 상호 연결성을 묘사한다. 엔티티는 명사로, 관계는 동사로 사용하여 문법 구조를 반영한다.
개체-관계 모델
데이터 모델링 분야에서 개체-관계 모델이란 구조화된 데이터에 대한 일련의 표현이다. 구조화된 데이터를 저장하기 위해 데이터베이스를 사용한다. 이때, 이 데이터의 "구조" 및 그에 수반한 제약 조건들은 다양한 기법에 의해 설계될 수 있다. 그 기법 중 하나가 개체-관계 모델링(Entity-Relationship Diagram) 이라 하며 줄여서 ERM이라고 한다. 그리고 ERM 프로세스의 산출물을 가리켜 개체-관계 다이어그램(Entity-Relationship Diagram) 이라 한다. 데이터 모델링 과정은 데이터 모델을 그림으로 표현하기 위해 표시법을 필요로 한다. ERD는 개념적 데이터 모델 혹은 시멘틱 데이터 모델의 한 타입이다.
쉽게 말하자면 데이터베이스의 여러 테이블들을 개체라고 봤을 때, 각각의 개체들의 관계를 정의한 관계도라고 보면 된다. 저~~엉말 쉽게 생각하면 벤 다이어그램이 포함 관계를 나타내는 것처럼, 개체 간의 어떠한 관계를 나타낸다고 보면 된다.
AQueryTool
무겁고 복잡, 불편한 기존 ERD 프로그램을 가볍고 깔끔하게, 사용하기 좋게 만든 ERD 프로그램이다.
- 로그인 없이 사용이 가능하다(저장하려면 로그인이 필요하다).
- 무료로 제공된다.
- UI가 깔끔하다.
- 사용법은 도움말 - AQuery.Web 공식문서를 참고하도록 하자.
- 웹 기반(HTML5) 서비스로, 모든 디바이스에서 접근가능하다.
모바일 어플리케이션 DB 클론 설계
AQueryTool을 이용해서 음악 어플리케이션인 Melon의 모바일 화면을 토대로 ERD를 설계해보았다. 전부 다 완성된 것은 아니며, 일부 화면들의 조합으로 이루어졌기 때문에 아직 부족한 부분들이 많다.
여기서 간과했던 것이, 어플리케이션의 주요 기능에 초점을 맞춰서 설계를 해야한다는 것이다. 내 경우에는 곡 재생목록, 좋아요 목록, 플레이리스트, 곡 정보, 앨범 정보 등을 토대로 설계를 하였다. 물론 이들도 중요하지만 Melon과 같은 스트리밍 서비스의 경우, 어떤 유저가 어떤 음악을 언제, 얼마나 들었는지와 같은 것들이 꽤나 중요할 것인데 이 부분을 빼먹었다. 그리고 그를 기반으로 음원 차트를 상정하고 추천 서비스도 제공할 수 있어야 한다. 따라서 이를 HISTORY와 같은 테이블을 하나 생성하던지 하는 등의 방안을 마련해 보완해줄 예정이다.
연습하며 작성해본 화면별 쿼리들
# 1. 최신곡
SELECT MUSIC_ID AS "음악 번호",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
COUNTRY AS "국가",
DATE_FORMAT(RELEASE_DATE, '%Y.%m.%d')
FROM MUSIC m
INNER JOIN (SELECT ALBUM_ID, ALBUM_IMAGE, SINGER_NAME, COUNTRY, RELEASE_DATE
FROM SINGER s
INNER JOIN ALBUM a ON a.SINGER_ID = s.SINGER_ID) SINGER_AND_ALBUM
ON m.ALBUM_ID = SINGER_AND_ALBUM.ALBUM_ID;
# WHERE COUNTRY = 'KOR'
# ORDER BY RELEASE_DATE DESC;
# 2. 최신앨범
SELECT ALBUM_ID AS "앨범 번호",
ALBUM_TITLE AS "앨범 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
DATE_FORMAT(RELEASE_DATE, '%Y.%m.%d') AS "발매일",
COUNTRY AS "국가",
CASE
WHEN SCORED_USER_COUNT = 0 THEN 0
ELSE ROUND(TOTAL_SCORE / SCORED_USER_COUNT, 1)
END AS "별점",
SCORED_USER_COUNT AS "별점 매긴 유저수"
FROM ALBUM
INNER JOIN SINGER ON ALBUM.SINGER_ID = SINGER.SINGER_ID
# WHERE COUNTRY = 'KOR'
ORDER BY ALBUM.RELEASE_DATE DESC;
# 3. 음악 번호에 해당하는 뮤직비디오
SELECT MUSIC_VIDEO_ID AS "뮤비 번호",
MUSIC_VIDEO_NAME AS "뮤비 제목",
SINGER_NAME AS "가수 이름",
MUSIC_VIDEO_IMAGE AS "뮤비 썸네일 이미지 URL",
CASE
WHEN
CAST(SUBSTRING(TIME_FORMAT(PLAYTIME, '%i:%s'), 1, 1) AS UNSIGNED) < 1
THEN SUBSTRING(TIME_FORMAT(PLAYTIME, '%i:%s'), 2)
ELSE TIME_FORMAT(PLAYTIME, '%i:%s')
END AS "재생시간",
VIEW_COUNT AS "조회수",
DATE_FORMAT(RELEASE_DATE, '%Y.%m.%d') AS "뮤비 공개일"
FROM MUSIC_VIDEO
INNER JOIN (SELECT MUSIC.MUSIC_ID, SINGER_AND_ALBUM.SINGER_NAME
FROM MUSIC
INNER JOIN (SELECT ALBUM_ID, SINGER_NAME
FROM SINGER
INNER JOIN ALBUM ON SINGER.SINGER_ID = ALBUM.SINGER_ID) SINGER_AND_ALBUM
ON MUSIC.ALBUM_ID = SINGER_AND_ALBUM.ALBUM_ID) MUSIC_AND_SINGER_AND_ALBUM
ON MUSIC_VIDEO.MUSIC_ID = MUSIC_AND_SINGER_AND_ALBUM.MUSIC_ID
ORDER BY RELEASE_DATE;
# 4. 전체 유저가 좋아요 한 음악 (서브 쿼리 + 조인 버전)
SELECT NICKNAME AS "닉네임",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
DATE_FORMAT(USER_LIKE_MUSICS.REGISTED_DATE, '%Y.%m.%d') AS "좋아요 한 날짜"
FROM USER
INNER JOIN (SELECT USER_ID,
MUSIC_LIKED_USER.MUSIC_ID,
MUSIC_TITLE,
SINGER_NAME,
ALBUM_IMAGE,
MUSIC_LIKED_USER.REGISTED_DATE
FROM MUSIC_LIKED_USER
INNER JOIN
(SELECT MUSIC.MUSIC_ID, MUSIC.MUSIC_TITLE, SINGER_NAME, ALBUM_IMAGE
FROM MUSIC
INNER JOIN (SELECT ALBUM_ID, SINGER_NAME, ALBUM_IMAGE
FROM SINGER
INNER JOIN ALBUM ON SINGER.SINGER_ID = ALBUM.SINGER_ID) SINGER_AND_ALBUM
ON MUSIC.ALBUM_ID = SINGER_AND_ALBUM.ALBUM_ID) TEMP
ON MUSIC_LIKED_USER.MUSIC_ID = TEMP.MUSIC_ID) USER_LIKE_MUSICS
ON USER.USER_ID = USER_LIKE_MUSICS.USER_ID
ORDER BY USER_LIKE_MUSICS.REGISTED_DATE DESC;
# 4. 전체 유저가 좋아요 한 음악 (조인 버전)
SELECT NICKNAME AS "닉네임",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
DATE_FORMAT(MUSIC_LIKED_USER.REGISTED_DATE, '%Y.%m.%d') AS "좋아요 한 날짜"
FROM (((MUSIC_LIKED_USER INNER JOIN MUSIC M ON MUSIC_LIKED_USER.MUSIC_ID = M.MUSIC_ID)
INNER JOIN ALBUM ON M.ALBUM_ID = ALBUM.ALBUM_ID)
INNER JOIN SINGER ON ALBUM.SINGER_ID = SINGER.SINGER_ID)
INNER JOIN USER ON USER.USER_ID = MUSIC_LIKED_USER.USER_ID
ORDER BY MUSIC_LIKED_USER.REGISTED_DATE DESC;
# 5. 전체 유저가 좋아요 한 앨범
SELECT NICKNAME AS "닉네임",
ALBUM_TITLE AS "앨범 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
CASE
WHEN SCORED_USER_COUNT = 0 THEN 0
ELSE TOTAL_SCORE / SCORED_USER_COUNT
END AS "별점",
SCORED_USER_COUNT AS "별점 매긴 유저수",
DATE_FORMAT(ALBUM_LIKED_USER.REGISTED_DATE, '%Y.%m.%d') AS "좋아요 한 날짜"
FROM ((ALBUM_LIKED_USER
INNER JOIN ALBUM A on ALBUM_LIKED_USER.ALBUM_ID = A.ALBUM_ID)
INNER JOIN SINGER ON A.SINGER_ID = SINGER.SINGER_ID)
INNER JOIN USER ON USER.USER_ID = ALBUM_LIKED_USER.USER_ID
ORDER BY ALBUM_LIKED_USER.REGISTED_DATE DESC;
# 6. 재생목록 (1번 재생목록, 1번 유저라 가정)
SELECT LISTED_MUSIC_IN_PLAYLIST.PLAYLIST_ID AS "플레이리스트 번호",
PLAYLIST_NAME AS "플레이리스트 이름",
LISTED_MUSIC_IN_PLAYLIST.MUSIC_ID AS "재생목록 중 음악 번호",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
DATE_FORMAT(LISTED_MUSIC_IN_PLAYLIST.REGISTED_DATE, '%Y.%m.%d') AS "곡 추가일",
(SELECT COUNT(*)
FROM LISTED_MUSIC_IN_PLAYLIST
WHERE LISTED_MUSIC_IN_PLAYLIST.PLAYLIST_ID = PLAYLIST.PLAYLIST_ID) AS "재생목록 곡 수"
FROM (LISTED_MUSIC_IN_PLAYLIST
INNER JOIN (SELECT MUSIC_ID, MUSIC_TITLE, SINGER_NAME, ALBUM_IMAGE
FROM (SELECT MUSIC_ID, MUSIC_TITLE, SINGER_ID, ALBUM_IMAGE
FROM MUSIC
INNER JOIN ALBUM A on MUSIC.ALBUM_ID = A.ALBUM_ID) AM
INNER JOIN SINGER ON AM.SINGER_ID = SINGER.SINGER_ID) M
ON LISTED_MUSIC_IN_PLAYLIST.MUSIC_ID = M.MUSIC_ID)
INNER JOIN PLAYLIST ON PLAYLIST.PLAYLIST_ID = LISTED_MUSIC_IN_PLAYLIST.PLAYLIST_ID
WHERE PLAYLIST.PLAYLIST_ID = 1
AND USER_ID = 1
ORDER BY REGISTED_DATE DESC;
# 7. 음악 재생 화면 (내가 2번 유저이고 1번 음악이라 가정)
SELECT MUSIC_ID AS "음악 번호",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
ALBUM_IMAGE AS "앨범 이미지 URL",
LYRICS AS "가사",
CASE
WHEN (EXISTS(SELECT *
FROM MUSIC_LIKED_USER
WHERE MUSIC_LIKED_USER.MUSIC_ID = MUSIC.MUSIC_ID
AND MUSIC_LIKED_USER.USER_ID = 2))
THEN '좋아요 함'
ELSE '좋아요 하지 않음'
END AS "좋아요 여부",
(SELECT COUNT(*) FROM MUSIC_LIKED_USER WHERE MUSIC_LIKED_USER.MUSIC_ID = MUSIC.MUSIC_ID) AS "좋아요 수",
CASE
WHEN CAST(SUBSTRING(TIME_FORMAT(PLAYTIME, '%i:%s'), 1, 1) AS UNSIGNED) = 0
THEN SUBSTRING(TIME_FORMAT(PLAYTIME, '%i:%s'), 2)
ELSE TIME_FORMAT(PLAYTIME, '%i:%s')
END AS "총 재생시간"
FROM (MUSIC INNER JOIN ALBUM A on MUSIC.ALBUM_ID = A.ALBUM_ID)
INNER JOIN (SELECT SINGER_ID, SINGER_NAME FROM SINGER) S
ON A.SINGER_ID = S.SINGER_ID
WHERE MUSIC_ID = 1;
# 8. 음악 정보 - 가사 (1번 유저라고 가정)
SELECT MUSIC_ID AS "음악 번호",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
SINGER_IMAGE AS "가수 이미지 URL",
GENRE AS "장르",
SOUND_QUALITY AS "음질",
ALBUM_TITLE AS "앨범 제목",
ALBUM_IMAGE AS "앨범 이미지 URL",
LYRICS AS "가사",
(SELECT COUNT(*)
FROM MUSIC_LIKED_USER
WHERE MUSIC_LIKED_USER.MUSIC_ID = MUSIC.MUSIC_ID) AS "좋아요 수",
CASE
WHEN EXISTS(SELECT *
FROM MUSIC_LIKED_USER
WHERE MUSIC_LIKED_USER.MUSIC_ID = MUSIC.MUSIC_ID
AND MUSIC_LIKED_USER.USER_ID = 2)
THEN '좋아요 함'
ELSE '좋아요 하지 않음'
END AS "좋아요 여부",
(SELECT COUNT(*) FROM MUSIC_REPLY WHERE MUSIC_REPLY.MUSIC_ID = MUSIC.MUSIC_ID) AS "댓글 수"
FROM (MUSIC
INNER JOIN ALBUM A on MUSIC.ALBUM_ID = A.ALBUM_ID)
INNER JOIN SINGER ON A.SINGER_ID = SINGER.SINGER_ID;
# 9. 음악 정보 - 상세정보 (1번 유저라 가정)
# 10. 음악 정보 - 댓글 (1번 유저라 가정)
SELECT MUSIC.MUSIC_ID AS "음악 번호",
MUSIC_TITLE AS "곡 제목",
SINGER_NAME AS "가수 이름",
SINGER_IMAGE AS "가수 이미지 URL",
GENRE AS "장르",
SOUND_QUALITY AS "음질",
ALBUM_TITLE AS "앨범 제목",
ALBUM_IMAGE AS "앨범 이미지 URL",
(SELECT COUNT(*)
FROM MUSIC_LIKED_USER
WHERE MUSIC_LIKED_USER.MUSIC_ID = MUSIC.MUSIC_ID) AS "좋아요 수",
CASE
WHEN EXISTS(SELECT *
FROM MUSIC_LIKED_USER
WHERE MUSIC_LIKED_USER.MUSIC_ID = MUSIC.MUSIC_ID
AND MUSIC_LIKED_USER.USER_ID = 1)
THEN '좋아요 함'
ELSE '좋아요 하지 않음'
END AS "좋아요 여부",
(SELECT COUNT(*) FROM MUSIC_REPLY WHERE MUSIC_REPLY.MUSIC_ID = MUSIC.MUSIC_ID) AS "댓글 수",
NICKNAME AS "닉네임",
REPLY_COMMENT AS "댓글 내용",
DATE_FORMAT(MUSIC_REPLY.REGISTED_DATE, '%Y.%m.%d') AS "댓글 작성일시" # 시간 차이 반영해서 수정해야함
# 댓글 좋아요 수 컬럼
# 댓글 싫어요 수 컬럼
# 댓글에 대한 답글 수 컬럼
FROM (((MUSIC
INNER JOIN ALBUM A on MUSIC.ALBUM_ID = A.ALBUM_ID)
INNER JOIN SINGER ON A.SINGER_ID = SINGER.SINGER_ID)
INNER JOIN MUSIC_REPLY ON MUSIC.MUSIC_ID = MUSIC_REPLY.MUSIC_ID)
INNER JOIN USER ON MUSIC_REPLY.USER_ID = USER.USER_ID
WHERE MUSIC_REPLY.MUSIC_ID = 1
ORDER BY MUSIC_REPLY.REGISTED_DATE DESC;참고자료
- AQueryTool
- 도움말 - AQuery.Web 공식문서
- AqueryTool(ERD)을 활용한 Database 모델링
- 데이터베이스 ERD 설계 : 스타벅스 과제
- TCP SCHOOL - MySQL 개요
- What is an Entity Relationship Diagram (ERD)?
- 개체-관계 모델
개발 블로그이지만 꼭 개발 이야기만 쓰라는 법은 없으니, 그냥 쓰고 싶은 내용이면 뭐든 쓰려고 합니다. 코드는 깃허브에다 작성할 수도 있으니까요.