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

February 06, 2019

1. 소개

새로운 직장으로 이직하게 되어 학부 이후 거의 보지 않았던 데이터베이스 책을 다시 보게 되었습니다. 최근에 데이터베이스 스터디하면서 봤던 책은 김연희 교수님의 데이터베이스 개론 책 을 많이 참조하였습니다. 관계형 데이터베이스에 대해서 기본적인 개념을 전체 리뷰하는데 도움이 되었던 책입니다. 혹시 관계형 데이터베이스에 대한 리뷰가 필요하시다면 이 책을 구매하셔서 보시길 권합니다.

본 포스팅에서는 관계형 데이터베이스 설계 과정을 통해서 실제 테이블을 생성하는 MySql 스크립트까지 작성 해보도록 하겠습니다. 설계하면서 사용한 도구와 소스는 아래를 참조해주세요.

Tools
- draw.io : ERD 다이어그램
- mysql benchwork : EER Diagram 및 물리적 스키마 설계
- intellij Database Tool
Database : mysql 5.6.42
Source files : github

데이터베이스에서 초기 설계 과정이 중요한 부분을 차지합니다. 잘못 설계되면 현재 사용 중인 구조를 나중에 쉽게 변경하기 어려운 부분도 있고 데이터의 일관성과 무결성이 유지되지 않는 문제도 생길 수가 있어서 데이터베이스 설계 과정을 통해서 좋은 데이터베이스를 생성해야 합니다.

관계형 데이터베이스를 설계하는 방법에는 주로 2가지 방법 이 사용됩니다.

E-R 모델과 릴레이션 변환 규칙을 이용한 설계
정규화를 이용한 설계 (이 주제는 다음 포스팅에서 다루겠습니다)
- 이상 현상(ex. 삽입, 삭제, 수정시 발생하는 문제)을 제거하면서 중복을 최소화하고 더 좋은 ‘작은’ 릴레이션으로 분해하는 작업이다.

E-R 모델과 릴레이션 변환 규칙을 이용한 설계는 아래 단계를 거쳐 데이터베이스를 생성합니다.

데이터 요구사항에 대한 분석 (결과 : 요구사항 명세서)
개념 스키마 설계 (결과 : ERD)
논리 스키마 설계 (결과 : 릴레이션 스키마의 테이블 명세서)
내부 스키마 설계 (결과 : DB 스키만 생성 SQL 문)

2. 요구사항 분석하기

데이터베이스에 대한 사용자의 요구사항을 수집하고 분석해서 아래와 같이 요구사항 명세서를 작성해야 합니다. 아래 예제는 데이터베이스 개론 책에 기재된 예제문제입니다.

한빛 항공사에 회원으로 가입하려면 회원아이디, 비밀번호, 성명, 신용카드 정보를 입력해야 한다
회원의 신용카드 정보는 여러 개를 저장할 수 있는데, 세부적으로는 신용카드번호, 유효기간을 저장할 수 있다
한빛 항공사에서는 보유한 비행기에 대해 비행기번호, 출발날짜, 출발시간 정보를 저장하고 있다
한빛 항공사에서는 좌석에 대한 좌석번호, 등급 정보를 저장하고 있다
회원은 좌석을 예약하는데, 회원 한 명은 좌석을 하나만 예약할 수 있고, 한 좌석은 회원 한명만 예약할 수 있다
비행기에는 좌석이 존재하는데, 비행기 하나에는 좌석이 여러 개 존재할 수 있고 한 좌석은 반드시 하나의 비행기에만 존재해야 한다.
그리고 좌석은 비행기가 없으면 의미가 없다.

3. 개념적 설계로 E-R 다이어그램 만들기

작성한 요구사항 명세서에서 데이터베이스를 구성하는데 필요한 개체, 속성, 개체 간의 관계를 추출하여 ERD를 생성합니다.

개체와 속성을 추출한다
- 대부분 명사로 선별한다
개체 간의 관계를 추출한다
- 대부분 동사로 선별한다 (개체간의 관계를 나타내는 동사이여야 한다)
- 관계에 속한 속성도 있을 수 있다
- 1:1, 1:N, N:M
- 필수적인 참여, 선택적인 참여

3.1 개체(Entity)와 속성(attribute) 추출

개념적 설계 단계에서 가장 먼저 해야 할 일은 개체를 추출하는 일입니다. 개체는 현실에서의 사물이나 사람이 생각하는 개념입니다. 개체를 나타내는 속성이 있고 여러 관련 속성이 모여 하나의 정보 단위를 이루는 것이 개체가 됩니다. 요구사항에서 개체는 대부분 명사로 이루어져 있지만, 속성과 구별할 필요가 있습니다. 항공사 명세서에서 개체와 속성을 구분해보겠습니다.

항공사 요구사항에서는 아래와 같이 개체와 속성을 구별할 수 있습니다.

개체	속성
회원	회원아이디, 비밀번호, 성명, 신용카드
신용카드	신용카드번호, 유효기간
비행기	비행기번호, 출발날짜, 출발시간
좌석	좌석번호, 등급

3.2 개체 간의 관계 추출

개체와 속성이 구별되었다면 개체 간의 관계를 추출합니다. 개체 간의 관계도 여러 가지로 분류해서 정의됩니다.

일대일(1:1), 일대다(1:N), 다대다(N:M)
관계 : 선택적인 관계, 필수적인 관계

요구사항에서 개체 간의 관계는 동사로 묘사되기 때문에 동사부터 찾으면 됩니다. 빨간색으로 표시된 부분이 앞 써 추출한 개체 간의 관계를 나타냅니다.

관계가 필수 인지 아니면 선택적인지 관계인지를 파악하는 게 개인적으로 쉽지 않아서 조금 더 정리를 해봤습니다.

필수적으로 관계에 참여 (전체 참여 : Total Participation)
- [A : B] 관계에서 개체 집합 B의 모든 개체가 [A : B] 관계에 참여한다
- 개체 A에 대해 개체 조건을 만족하는 개체가 반드시 존재할 경우에 필수적인 관계 라고 본다.
선택적으로 관계에 참여 (부분 참여 : Partial Participation)
- [A : B] 관계에서 개체 집합 B의 일부 개체만 [A : B] 관계에 참여한다
- 객체 A에 대해 개체 조건을 만족하는 개체가 존재할수도, 존재하지 않을 수도 있는 경우에 선태적인 관계 라고 본다.
예제 모음
- 예1. [학과 : 교수]
- 학과(필수): 한 학과에는 여러 교수가 소속된다
- 교수(필수): 한 교수는 한 학과에만 소속된다
- 예2. [회원 : 주문]
- 회원(선택): 한 회원은 여러번 주문할 수 있다
- 주문(필수): 한 주문은 한 회원에 의해 주문된다
- 예3. [교수 : 과목]
- 교수(선택): 한 교수는 여러 과목을 강의할 수 있다
- 과목(필수): 한 과목은 한 교수에 의해서 강의되어야 한다
- 예4: [주문 : 주문목록]
- 주문(필수): 한 주문은 여러 개의 주문목록을 포함한다
- 주문목록(필수): 한 주문목록에 하나의 주문내용에 포함된다
- 예5 : [고객 : 책] - 구매 관계
- 고개(필수): 모든 고객이 책을 반드시 구해야 한다
- 책(선택) : 고객이 구매하지 않은 책이 존재할수 있다.

개채 간의 관계 추출 결과

관계	관계에 참여하는 개체	관계유형	관계 속성
보유	회원(선택) : 한 회원은 여러 신용카드를 가질 수 있다 신용카드(필수) : 한 신용카드는 한 회원이 보유한다	1:N
예약	회원(선택) : 한 회원은 한 좌석만 예약할 수 있다 좌석(필수) : 한 좌석은 회원 한명에 의해서 예약된다 회원(선택): 한 회원은 여러 비행기를 예약할 수 있다 비행기(필수): 비행기 한 좌석은 한 회원이 예약한다	1:N	요구사항에서는 없음 ex. 예약번호 예약일자
존재	비행기(필수) : 비행기 하나에는 좌석이 여러개 존재한다 좌석(필수) : 한 좌석은 하나의 비행기에만 존재해야 한다	1:N	비행기 없이 좌석이 존재할 수 없음

3.3 분석한 내용으로 ERD 생성하기

객체, 관계 정리만 잘 되면 쉽게 ERD를 만들어볼 수 있습니다. 추출한 내용을 가지고 릴레이션 스키마를 바로 생성할 수도 있지만, 다이어그램으로 다시 정리하면 큰 그림을 보면서 더 쉽게 전체 모델을 이해할 수 있는 장점이 있습니다. ERD 만들 수 있는 여러 도구가 있지만, 저는 draw.Io를 사용해서 ERD를 만들어봤습니다. draw.io은 오픈소스 프로그램으로 애플리에션을 다운로드 받아서사용하거나 아니면 웹 브라우저에서도 프로그램 설치 없이 사용할 수 있습니다.

맥에서 사용하시려면 brew 명령어로 설치하세요.

$ brew cask install drawio

draw.io은 IE(Information Engineering) 표기법을 사용합니다. IE 표기법은 링크 를 참조해주세요.

지금까지 언급한 개체와 관계 추출 정보 바탕으로 그린 ERD다이어그램입니다. #4.논리적 설계 단계를 거치면서 여러 번 수정하였고 아래는 최종 버전입니다. 수정한 이력은 6.ERD다이어그램 변경 이력에 기록해 두었습니다.

4. 논리적 설계로 릴레이션 스키마 및 테이블 명세서 만들기

ERD에서 릴레이션 스키마를 만들려면 다음 5가지 릴레이션 변환 규칙에 따라서 릴레이션 스키마로 변환해주면 됩니다.

규칙1 : 모든 개체는 릴레이션으로 변환한다
규칙2 : N:M 관계는 릴레이션으로 변환한다
- 관계의 이름을 릴레이션 이름으로 하고 관계의 속성도 릴레이션의 속성으로 변환한다
규칙3 : 1:N 관계는 외래키로 표현한다
- 규칙3-1: 일반적인 1:N 관계는 외래키로 표현한다
- 규칙3-2: 약한 개체가 참여하는 1:N 관계는 외래키로 포함해서 기본키로 지정한다
규칙4 : 1:1 관계는 외래키로 표현한다
- 규칙4-1: 일반적인 1:1 관계는 외래키를 서로 주고 받는다
- 규칙4-2: 1:1 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다
- 규칙4-3: 모든 개체가 1:1 관계에 필수적으로 참여하면 릴레이션 하나로 합친다
규칙5 : 다중 값 속성은 독립 릴레이션으로 변환한다

하나 하나 규칙에 따라서 릴레이션을 생성해보겠습니다.

4.1 규칙1 : 모든 개체는 릴레이션으로 변환한다

규칙1은 ER 다이어그램의 각 개체를 릴레이션으로 변환합니다. 개체는 하나의 테이블이 되고 속성은 테이블의 속성이 됩니다.

회원아이디	성명	비밀번호
1	이정수	1234
2	한정수	1234!
3	김지선	12345

4.2 규칙2 : N:M 관계는 릴레이션으로 변환한다

공항사 ER 다이어그램에서는 N:M 관계가 없는 관계(초반에는 없었습니다 ;;)로 회원 : 주문 관계로 설명을 하도록 하겠습니다. 규칙2는 N:M 관계의 이름(ex. 주문)은 릴레이션의 이름이 되고 관계의 속성도 릴레이션의 속성으로 변환하면 됩니다.

회원과 상품은 규칙1에 따라서 객체를 테이블로 변환합니다.

회원 릴레이션 | 회원번호 | 이름 | 부서

상품 릴레이션 | 제품번호 | 제품이름

N:M 관계는 주문 릴레이션을 생성하고 관계의 속성도 렐레이션에 포함시킵니다. 기본 키를 무엇으로 할지에 따라서 속성 구성이 조금씩 달라집니다.

관계의 릴레이션에서 별도의 기본키 사용
- 기본키 : 주문번호
- 외래키 : 회원번호, 제품번호
양쪽 객체의 기본키를 합쳐서 기본키로 사용
- 기본키 : 회원번호 & 제품번호
- 외래키 : 회원번호, 제품번호

주문 릴레이션 | 주문번호 | 회원번호 | 제품번호 | 주문날짜

주문 릴레이션 | 회원번호 | 제품번호 | 주문날짜

4.3 규칙3 : 1:N 관계는 외래키로 표현한다

4.3.1 규칙3-1 : 일반적인 1:N 관계는 외래키로 표현한다

규칙3은 일반적으로 1:N 관계에서 1측 개체의 기본키를 N측 릴레이션에 포함시키고 외래키로 지정합니다.

신용카드 릴레이션 | 신용카드번호 | 회원아이디(외래키) | 유효기간

4.3.2 규칙3-2 : 약한 개체가 참여하는 1:N 관계는 외래키로 포함해서 기본키로 지정한다

규칙3-2는 약한 개체를 다루기 때문에 약한 개체와 강한 개체의 차이점을 보면, 강한 개체는 일반 개체를 의미하고 약한 개체는 다른 개체가 없이는 존재하지 않는 개체를 의미합니다.

약한 개체(Weak Entity)
- 다른 개체의 존재에 종속되어 있는 개체이다
- 약한 개체에서 개별 개체를 식별하는 속성을 식별자(Discriminator)혹은 부분키(Partial Key)라고 한다
강한 개체(Strong Entity)
- 타 개체 존재와 상관없이 존재할 수 있는 일반 개체이다
- 기본 키가 있는 개체다

1:N 관계에서 약한 개체가 참여하는 경우에는 규칙 3-1과 같이 1측 개체 릴레이션의 기본 키를 가져와 N측 릴레이션에 포함시키고 외래키로 지정합니다. 또한 추가된 외래키를 포함하여 기본 키를지정해야 합니다.

좌석 릴레이션 | 좌석번호 | 비행기번호(외래키) | 등급

4.4 규칙4 : 1:1 관계는 외래키로 표현한다

규칙4-1: 일반적인 1:1 관계는 외래키를 서로 주고 받는다

1:1 관계는 외래키를 서로 주고받으면 됩니다. 항공사 관계에서는 1:1 관계가 없으므로 다른 예제로 설명하도록 하겠습니다. 한 회사에서는 자가만의 사무실이 있는 예제입니다. 좋은 회사이네요 ^^ 한 회원은 하나의 사무실을 사용할 수 있습니다. 한 사무실은 한 회원만 사용할 수 있습니다.

회원 릴레이션 | 사원번호 | 사무실번호(외래키) | 이름

사무실 릴레이션 | 사무실번호 | 사원번호(외래키) | 사무실전화번호

1:1 관계에서 일반적으로 양쪽에 각 릴레이션의 기본 키를 서로 주고받지만, 관계가 필수인지 아닌지에 따라서 조금 다른 규칙을 적용할 수 있습니다. 해당 규칙에 대해서는 추가 예제는 포함시키지않았지만, 설계 시 아래 규칙에 따라서 적용해보고 베스트 선택을 해보면 좋을 것 같습니다.

규칙4-2: 1:1 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다
규칙4-3: 모든 개체가 1:1 관계에 필수적으로 참여하면 릴레이션 하나로 합친다

4.5 규칙5 : 다중 값 속성은 독립 릴레이션으로 변환한다

릴레이션에서는 다중 값 속성을 가질 수 없으므로 다중 값 속성을 별도의 릴레이션을 생성해야 합니다. 별도의 릴레이션의 기본 키는기존 개체의 기본 키를 가져와 다중 값 속성과 함께 기본 키를구성해야 합니다.

사원 릴레이션 | 사원번호 | 이름

자녀 릴레이션 | 사원번호(외래키) | 자녀이름

5. 물리적 스키마 및 구현

지금까지 ERD 다이어그램에서 테이블로 변환하는 규칙을 알아보았습니다. 이제 실제로 데이터베이스에 테이블을 생성해보도록 하겠습니다. 개인적으로는 MySQL Workbench 프로그램을 사용하여 물리적 스키마를 생성합니다. MySQL Workbench에서 EER 모델 설계이후 schema 생성 스크립트를 생성한 이후 오류가 발생해서 script 파일로 저장이후에 Intellij 에서 수동으로 변경이후에 테이블을 생성하였습니다.

6. ERD 다이어그램 변경이력

요구사항을 충분히 이해하지 못하면 잘못된 결과가 나올 수있습니다. 하지만, 각 설계 단계를 거치면서 잘못된 부분도 같이 찾을 수 있어서 스터디하시면서 도움이 될 것 같아 기록상 남겨두었습니다.

ERD 설계	이슈사항
버전1	- 회원이 예약할때 비행기+좌석을 같이 예약하지 않나? ㅁ. 좌석은 비행기 없이는 존재하지 않음. 결론 ㅁ. 좌석은 weak entity임
비행기 릴레이션: 비행기번호, 회원아이디(외래키), 출발시간, 출발날짜 회원 릴레이션: 회원아이디, 비행기번호(외래키), 성명, 비밀번호	현실적으로 한 회원은 여러 비행기를 예약할 수 있다. ㅁ. 회원 릴레이션에 비행기번호를 추가하면 회원은 하나의 비행기만 예약할 수 있게 된다 ㅁ. 또한 한 비행기에 여러 회원이 예약될 수 있다. ㅁ. N:N이 맞아 보인다

7. 결론

오랜만에 데이터베이스 설계에 대해서 스터디를 나름 진행을 했고 생각보다 정리하는데 좀 오랜 시간이 걸렸습니다. 핑계면 핑계인데, 익숙하지 않지 않은 도구들도 사용해보고 정리해보면서 좀 시간이 걸린 듯합니다. 그래도 조금씩 정리하면서 스스로 더 많이 배울 수 있는 시간이 되었던 것 같습니다. 다음 데이터베이스 포스팅에서는 정규화를 이용해서 설계하는 방법에 대해서 알아보겠습니다.

8. 참고

책
IE 표기법
- http://www.devtimes.com/115
필수, 선택적 관계
- http://tech.devgear.co.kr/db_kb/331
- http://www.dbguide.net/db.db?cmd=view&boardUid=12851&boardConfigUid=9&categoryUid=216&boardIdx=40&boardStep=1
사례로 배우는 데이터베이스 설계와 구축
- https://view.officeapps.live.com/op/view.aspx?src=http://datamining.dongguk.ac.kr/lectures/2009-1/db/data/BS-example.ppt

Loading script...