관계형 DB 설계

https://advenoh.tistory.com/31

관계형 데이터베이스 설계 및 구축

 

위 링크를 많이 참조하였습니다.

 

DB 설계 시작

뭐든 처음 프로젝트나 db를 구축하면서 무턱대고 작성하거나 들어가는 것은 프로젝트가 점차 커지면서 반드시 개발자에게 악수로 돌아온다고 생각합니다. 관계형 DB를 설계하는 방법은 2가지가 사용됩니다. 

 

1. E-R 모델과 릴레이션 변환 규칙을 이용한 설계
2. 정규화를 이용한 설계

 

E-R 모델 릴레이션 변환 규칙은 다음의 순서를 갖습니다.

 

1. 요구사항 분석

2. 개념스키마 설계 (output: ERD)

3. 논리스키마 설계 (output: 릴레이션 스키마 테이블 명세서)

4. 내부스키마 설계 (output: 스키마 생성 sql 스크립트)

 

요구사항 분석

 

요구사항을 수집하고 분석하여 명세서를 작성합니다. 제 사이드 프로젝트인 Mine Sweeper로 예제를 들겠습니다.

 

• 사이트에 가입하기 위해 아이디, 닉네임, 비밀번호, 이메일 정보를 입력해야 한다.
• 하나의 유저 아이디에 쉬움, 보통, 어려움 게임 기록을 저장해야 한다. 
• 게임 기록은 유저, 성공여부, 난이도, 소요된 시간을 기록한다.
• 게시판 테이블은 제목, 작성날짜, 내용, 작성자, 조회수 정보를 저장한다.
• 하나의 유저아이디는 여러 게시글을 작성할 수 있다.
• 하나의 유저아이디는 여러 게임을 할 수 있다.
• 하나의 게시글에 여러 댓글이 달릴 수 있다.
• 댓글은 하나의 게시글을 참조한다.
• 회원 가입을 하지 않은 사용자라도 게임을 할 수 있다.

 

E-R 다이어그램 분석

 

요구사항 명세서에서 개체, 속성, 개체 간 관계를 추출하여 ERD를 그립니다.

 

개체와 속성은 명사로 선별한다.

개체 간 관계는 동사로 선별한다. (1:1, 1:N, N:N)

 

그럼 개체와 속성을 구분해보겠습니다.

 

개체	속성
유저	유저번호, 아이디, 비밀번호, 이메일, 등급
게임	아이디(외래키), 기록, 난이도, 성공여부, 게임날짜
게시글	게시글 번호, 작성자, 타이틀, 작성날짜, 조회수, 내용, 수정날짜
댓글	게시글 번호(외래키), 내용, 작성날짜, 작성자, 수정날짜

 

개체를 추출하였다면 개체간 관계를 추출해야 합니다. 개체 관계는 여러가지로 분류합니다.

 

• 1:1, 1:N, N:N 관계
• 선택적인 관계, 필수적인 관계

말로만 해선 어렵군요... 예제를 찾아봐야 겠습니다. 

 

필수적인 관계

A:B 관계에서 개체 집합 B의 모든 개체가 A:B 관계에 참여한다.

개체 A에 대해 개체 조건을 만족하는 개체가 반드시 존재할 경우 필수적인 관계라 본다.

선택적 관계

A:B 관계에서 집합 B의 일부 개체만이 A:B 관계에 참여한다.

개체 A에 대해 개체 조건을 만족하는 개체가 존재할 수, 존재하지 않을 수 있다. 

 

예제)

1. 학과, 교수

• 학과(필수): 한 학과에 여러 교수가 소속
• 교수(필수): 한 교수는 한 학과에 소속된다.

2. 회원, 주문

• 회원(선택): 한 회원은 여러번 주문 가능
• 주문(필수): 한 주문은 한 회원에 주문된다.

3. 교수, 과목

• 교수(선택): 한 교수는 여러 과목을 강의할 수 있다.
• 과목(필수): 한 과목은 한 교수에 의해서 강의되어야 한다.

4. 고객: 책

• 고객(필수): 모든 고객이 책을 반드시 구매해야 한다.
• 책(선택): 고객이 구매하지 않은 책이 존재할 수 있다.

 

작성하면서 생각하니 얼추 알것 같기도 모르는것 같기도 합니다. 

 

1. 유저, 게임

• 유저(선택): 한 유저는 여러 게임을 할 수 있다. 
• 게임(필수): 한 게임은 한 유저가 플레이 한 것.

2. 유저, 게시글

• 유저(선택): 한 유저는 여러 게시글을 작성할 수 있다.
• 게시글(필수): 한 게시글은 한 유저에 의해 작성되어야 한다.

3. 게시글, 댓글

• 게시글(필수): 한 게시글에 여러 댓글이 존재
• 댓글(필수): 댓글은 하나의 게시글에 속한다. 게시글 없이 댓글은 존재할 수 없다. 필수

개체간 관계 추출

 

관계	관계에 참여하는 개체	유형	속성
기록	유저(선택): 한 유저는 여러 게임을 할 수 있다. 게임(필수): 한 게임은 한 유저가 플레이 한 것.	1:N
작성	유저(선택): 한 유저는 여러 게시글을 작성 게시글(필수): 한 게시글은 한 유저가 작성	1:N
의존	게시글(필수): 게시글은 여러 댓글이 존재한다. 댓글(필수): 댓글은 게시글의 의존한다.	1:N	게시글 없이 댓글 존재 불가

 

이제 ERD를 그려봅시다. 한눈에 보면 좀 더 이해가 쏙쏙 되리라 믿으면서....

 

릴레이션 스키마 및 테이블 명세서 만들기

릴레이션 변환규칙 다섯가지

1. 모든 개체는 릴레이션으로 변환한다.
2. N:M관계는 릴레이션으로 변환한다.
3. 1:N 관계는 외래키로 표현한다.
4. 1:1 관계는 외래키로 표현한다.
5. 다중 값 속성은 독립 릴레이션으로 변환한다.

 

 

게임 릴레이션은 게임번호와 유저 아이디를 합쳐서 기본키로 사용합니다.

• 유저는 여러 게임을 할 수 있다.

게시글 릴레이션은 게시글 번호와 유저아이디를 합쳐서 기본키로 사용합니다.

• 유저는 여러 게시글을 작성할 수 있다.

댓글 릴레이션은 댓글번호와 댓글번호를 합쳐서 기본키로 사용

• 한 게시글에 동일한 작성자가 여러번 작성 할 수 있다.