1. ER modeling
- webtoon은 웨툰의 제목, 연재요일, 썸네일 이미지, 줄거리, 연재 플랫폼 장르를 속성으로 갖는다. 참고로 장르는 다중값 속성으로 웹툰은 여러 장르를 가질 수 있다.
- draw는 작가가 웹툰을 연재하는 realtionship을 나타낸다. 한 명의 작가가 여러 작품을 연재할 수 있고 작품은 반드시 한명의 작가가 있어야 한다. 따라서 일 대 다 관계를 맺고 webtoon쪽은 total participation 제약이 있다.
- author는 웹툰 작가 정보를 나타낸다. 작가의 이름, 여러 작가들과 더불어 동명이인의 작가를 구분할 수 있는 id를 부여한다.
- 사용자 테이블은 id와 비밀번호를 속성으로 갖는다.
- collection은 사용자의 취향에 맞게 여러 웹툰을을 보관할 수 있는 보관함이다. 사용자마다 여러개의 보관함을 가질 수 있고 각 보관함에 주제에 맞은 웹툰들을 모아볼 수 있다. 빈 collection이 얼마든지 존재할 수 있다.
- 좋아하는 작가를 like(follow)할 수 있는 기능을 제공한다. 여러 사용자들이 각자 여러명의 작가를 follow할 수 있다. 따라서 다대다 관계를 맺는다.
- 웹툰은 매주 특정 요일에 새로운 회차가 업로드 된다. 따라서 웹툰은 결국 여러 회차들의 묶음을 나타낸다. 웹툰이 먼저 등록이 되면 시간이 지남에 따라 여러 회차들이 업로드 되기때문에 두 테이블 사이에는 의존성이 존재한다. 이 의존성을 나타내기 위해 episodes는 weak entity로 설계 하였고 webtoon이 몇화를 episode로 가지고 있는지 relationship이 존재한다.
- Episode는 반드시 적어도 하나의 webtoon에 종속되기 때문에 total participation 제약과 더불어 1대다 관계를 맺는다.
- 사용자는 이 에피소드를 보고 해당 웹툰 회차에 점수를 매기고(rate) 댓글을 쓴다(comment) Rate relationship set는 사용자가 몇점을 매겼는지를 속성값으로 갖는다. 사용자는 한번만 각 에피소드에 점수를 매길 수 있기 때문에 rate는 1대다 relationship을 갖는다.
Comment relationship은 댓글의 내용과 작성 시간을 속성값으로 갖는다.
- Read relationship은 episode에 의존하는 entity이기 때문에 weak entity로 설계하였다. 사용자가 현재 해당 웹툰의 episode를 읽는 동작을 나타낸다.
2. RDB로 변환
Strong entity set
- webtoon( id_wt, pub_day, title, thumb, summary, platform )
- genre( id_wt, genre )
- author( id_author, name )
*webtoon이 여러 장르에 속할 수 있기 때문에 별도 테이블로 genre 속성을 분리한다.
Relationship set
- draw( id_author, id_wt )
Weak entity set
- episode( id_wt, image_files, episode_num )
- webtoon( id_wt, pub_day, title, thumb, summary, platform )
*겹선 다이아몬드 무시, id_wt를 외래키로 사용하여 webtoon참조
Strong entity set
- user( user_id, pw )
- episode( id_wt, image_files, episode_num )
Relationship set
- read( user_id, id_wt, episode_num )
- rate( user_id, score, id_wt, episode_num )
- comment( user_id, content, time, id_wt, episode_num )
Strong entity set
- user( user_id, pw )
- webtoon( id_wt, pub_day, title, thumb, summary, platform )
- collection ( id_collection, user_id )
Relationship set
- create( user_id, id_collection )
- add_collection( id_collection, id_wt )
*create를 collection이나 user에 흡수시킬 수 있지만 사용자가 여러 collection 만들수 있기 때문에 단독으로 분리하였다.
*id_wt들이 collection에 다중값 속성으로 들어갈 수 있기 때문에 add_collection으로 단독 테이블을 만들었다.
Strong entity set
- author( id_author, name )
- user( user_id, pw )
Relationship set
- like( id_author, user_id )
3. 응용프로그램이 지원할 기능
1. 여러 사용자로부터 받은 회차들에 대한 평가점수를 평균내어 웹툰들의 평점, 제목을 반환
select title, rate.id_wt, avg( score ) as avg_score
from webtoon, rate
where webtoon.id_wt = rate.id_wt
group by id_wt2. 사용자가 한번이라도 평가한 웹툰 목록 조회
select title, webtoon.id_wt
from user, rate, webtoon
where user.user_id = ‘사용자 아이디’ and webtoon.id_wt = rate.id_wt
group by rate.id_wt3. 사용자가 좋아요(like)한 작가의 모든 연재작 조회
select title, webtoon.id_wt
from like, draw, webtoon
where like.user_id = ‘사용자 아이디’ and like.id_author = draw.id_author and webtoon.id_wt = draw.id_wt
group by rate.id_wt4. 특정 장르의 웹툰 목록 조회
select distinct title, id_wt
from webtoon, genre
where webtoon.id_wt = genre.id_wt and genre = ‘장르 이름'5. 사용자가 현재 읽고 있는 웹툰 목록 조회
select title, webtoon.id_wt, max( episode_num ) as recent_read
from read, webtoon
where read.user_id = ‘사용자 아이디’ and webtoon.id_wt = read.id_wt
group by read.id_wt4. DB생성 및 튜플 적재 (2개 기능만 선택)
구현할 지원 기능 2개
1. 여러 사용자로부터 받은 회차들에 대한 평가점수를 평균 내어 웹툰들의 평점, 제목을 반환
2. 특정 사용자가 평가한 웹툰 목록 반환
필요한 테이블
- user( user_id, pw )
- rate( user_id, score, id_wt, episode_num )
- webtoon( id_wt, pub_day, title, thumb, summary, platform )
- episode( id_wt, image_files, episode_num )
테이블 생성
create table episode (
image_files varchar(150) not null,
id_wt varchar(10) not null,
episode_num int not null,
primary key (id_wt, episode_num)
)create table webtoon (
title varchar(20) not null,
thumb varchar(200) not null,
summary varchar(150) not null,
platform varchar(5) not null,
id_wt varchar(10) not null,
pub_day char(2) not null,
primary key (id_wt)
)create table rate (
user_id varchar(20) not null,
score int not null,
id_wt varchar(10) not null,
episode_num int not null,
primary key (id_wt, user_id, episode_num)
)create table user (
user_id varchar(20) not null,
pw varchar(20) not null,
primary key (user_id)
)확인
1. Database 생성
2. Table 생성
3. 튜플 삽입
5. JDBC/MySQL 프로그램
지원할 기능 1. 여러 사용자로부터 받은 회차들에 대한 평가점수를 평균 내어 웹툰들의 평점, 제목을 반환
지원할 기능 2. 특정 사용자가 평가한 웹툰 목록 반환
// WebtoonDBMS.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;
public class WebtoonDBMS {
private Connection conn;
// Webtoon database를 관리할 DBMS를 class로 정의
WebtoonDBMS() {
try {
// Connect conn 객체와 로컬 sql서버 연결
this.conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/webtoon", "root", "1454");//URL,UID,PWD
System.out.println("Connection to local sql server successfully");
} catch (SQLException sqle) {
System.out.println("Connection failed : " + sqle);
}
}
// 웹툰의 여러 회차들의 평가 점수를 평균내어 해당 웹툰 평균 평점을 구하여 반환
public void printWebtoonAvgScores() {
try {
// connect to local mysql server
// check id with "status"
// print all the webtoons average score table
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select title, webtoon.id_wt, avg( score ) as avg_score\n"
+ " from webtoon, rate \n"
+ " where webtoon.id_wt = rate.id_wt\n"
+ " group by id_wt");
while (rset.next()) {
System.out.println( "title: " + rset.getString(1) + " " +
"ID:" + rset.getString(2) + " " +
"avg_score: " + rset.getInt(3));
}
System.out.println("\n");
} catch(SQLException sqle) {
System.out.println("Connection failed : " + sqle);
}
}
// 사용자로부터 user_id를 입력 받아서 동적으로 sql문을 생성한다.
// 넘겨받은 user_id로 그 사용자가 한번이라도 평가한 웹툰 목록을 반환한다.
public void printRatedWebtoonList(String userName) {
try {
// select webtoon list which user '?' rated
String sql = "select title, rate.id_wt\n"
+ " from user, rate, webtoon\n"
+ " where user.user_id = ? and webtoon.id_wt = rate.id_wt\n"
+ " group by rate.id_wt";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setString(1, userName);
ResultSet rset = pstm.executeQuery();
while (rset.next()) {
System.out.println("title:" + rset.getString(1) + " " +
"webtoon id: " + rset.getString(2));
}
} catch(SQLException sqle) {
System.out.println("Connection failed : " + sqle);
}
}
// 먼저 어떤 사용자의 평가한 웹툰을 반환할 것인지 결정하기 위해
//사용자 목록을 반환하여 선택지로 제공
public void printUserList() {
try {
// connect to local mysql server
// check id with "status"
// print all the users
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select user_id\n"
+ " from user \n");
int i = 1;
while (rset.next()) {
System.out.println( i + ". user_id: " + rset.getString(1));
i++;
}
System.out.println("\n");
} catch(SQLException sqle) {
System.out.println("Connection failed : " + sqle);
}
}
}// Manager.java
import java.io.*;
import java.util.Scanner;
public class Manager {
public static void main(String[] args) {
WebtoonDBMS dbms = new WebtoonDBMS();
while(true) {
System.out.println("\n--------------------------------------------------------");
System.out.println("1. Print webtoons' average scores");
System.out.println("2. Print webtoon list which are rated by specific user");
System.out.println("3. exit");
System.out.println("--------------------------------------------------------");
System.out.print("input : ");
int input;
Scanner sc = new Scanner(System.in);
input = sc.nextInt();
System.out.print("\n");
if (input == 1) {
dbms.printWebtoonAvgScores();
} else if (input == 2) {
System.out.println("Select User ID");
dbms.printUserList();
System.out.print("input : ");
String id;
Scanner scanner = new Scanner(System.in);
id = scanner.next();
System.out.print("\n");
dbms.printRatedWebtoonList(id);
} else {
break;
}
}
}
}
- 이 응용을 사용하고 있는 사용자들이 점수를 매긴 웹툰 회차들을 평균내어 각 웹툰의 평점을 반환한다.
- 특정 사용자가 한번이라도 평가했던 웹툰 목록들을 반환한다.
6. BCNF 정규화 및 스키마 정제
| Table | webtoon( id_wt, pub_day, title, thumb, summary, platform ) | author( id_author, name ) | user( user_id, pw ) |
| Dependency | id_wt -> pub_day, title, thumb, summary, platform id_wt은 superkey title -> pub_day, thumb, summary, platform, id_wt title은 superkey thumb -> title, summary, platform, id_wt thumb은 superkey summary -> title, thumb, platform, id_wt summary는 superkey |
id_author -> name id_author는 superkey |
user_id->pw user_id는 superkey |
| Table | episode( id_wt, image_files, episode_num ) | create( user_id, id_collection ) | genre( id_wt, genre ) |
| Dependency | id_wt, episode_num -> image_files id_wt, episode_num은 superkey image_files -> id_wt, episode_num image_files은 superkey |
Non-trivial 종속관계 없음 | Non-trivial 종속관계 없음 |
| Table | comment( user_id, content, time, id_wt, episode_num ) | like( id_author, user_id ) | draw( id_author, id_wt ) |
| Dependency | user_id, time, id_wt, episode_num -> content user_id, time, id_wt, episode_num는 superkey |
Non-trivial 종속관계 없음 | id_author->id_wt id_wt->id_author id_author, id_wt 모두 superkey |
| Table | rate( user_id, score, id_wt, episode_num ) | add_collection( id_collection, id_wt ) | collection ( id_collection, user_id ) |
| Dependency | user_id, id_wt, episode_num->score user_id, id_wt, episode_num은 superkey |
Non-trivial 종속관계 없음 | id_collection->user_id id_collection은 superkey |
| Table | read( user_id, id_wt, episode_num ) | ||
| Dependency | Non-trivial 종속관계 없음 |
'ComputerScience > Database' 카테고리의 다른 글
| DB - 25. JDBC 연습 (0) | 2022.02.18 |
|---|---|
| DB - 24. Normal Form (0) | 2021.12.14 |
| DB - 23. Functions and Procedures , Trigger (0) | 2021.12.06 |
| DB - 22. Intermediate SQL 2 (0) | 2021.12.05 |
| DB - 21. Integrity Constraints (0) | 2021.12.01 |
