데이터베이스(8), 데이터베이스 설계와 ER 모델

데이터베이스 설계와 ER 모델

---

1. 데이터베이스 설계의 개요

1) 데이터 베이스 설계의 종류

(1) 개념적 데이터베이스 설계

실제로 데이터 베이스를 어떻게 구현할 것인가와는 독립적으로 정보 사용의 모델을 개발하는 과정

 

조직체의 엔티티, 관계, 프로세스, 무결성 제약조건 등을 나타내는 추상화 모델을 구축한다. 

 

엔티티: 서로 구분되면서 조직체에서 데이터베이스에 나타내려는 객체(사람, 장소, 사물 등등)

관계: 두 개 이상의 엔티티들 간의 연관

프로세스: 관련된 활동

무결성 제약조건: 데이터의 정확성과 비즈니스 규칙

특정 데이터 모델과 독립적으로 응용 세계를 모델링할 수 있도록 한다.

데이터베이스 구조나 스키마를 하향식으로 개발할 수 있기 위한 틀(framework)을 제공

엔티티 관계 데이터 모델이 가장 인기 있다.

 

개념적 수준을 모델링 할 땐 구현 데이터 모델을 생각하지 않고 도메인에 대해 정의하는 것이 해당 단계에서 이뤄진다.

 

데이터베이스 설계의 개요

데이터베이스 개발은 일반적은 프로젝트 라이프 사이클을 따른다.

이는 요구사항 분석 -> 요구사항 명세서 작성 -> 설계의 과정을 따른다.

 

훌륭한 데이터 베이스 설계의 조건

 

데이터베이스 개발의 라이프 사이클

요구사항 분석 단계

요구사항 수집과 분석을 진행함

 

설계 단계

어떤 식으로 설계를 진행할 것인지 설정함

개념적 설계: 어떤 엔티티와 관계들이 도메인에 필요한가? ER모델이나 객체지향 모델, UML 등을 도구로서 사용한다.

DBMS의 선정: 어떤 DBMS를 사용할지 결정한다. // 데이터베이스 모델을 선정한다.

논리적 설계: 데이터베이스가 무엇을 모델링하는가? -> 개념적 설계를 데이터베이스 스키마로 사상

물리적 설계

보안 설계

 

스키마정제는 논리적 설계의 일부로 볼 수 있다. 중복을 최소화하도록 정제하는 과정이 된다.

 

일반적으로 데이터베이스의 설계는 단계를 왔다갔다하며 진행한다.

 

 

데이터 베이스는 전세계의 일부만 다루게 된다.  -> 설계의 대상은 작은 세계이다.

 

개념적 설계의 아웃풋으로는 개념적 스키마(ER 모델을 사용하면 ER 스키마)가 나온다.

정규화과정은 스키마에 대해 정규화한다.

물리적 스키마는 키 값으로 정렬할 것인가, 인덱스를 만들것인가 등등을 정하게 된다.

 

요구사항 수집과 분석

문서를 조사하거나 인터뷰, 설문조사 등등을 실시한다. 인터뷰는 특히 요구사항 분석에서 가장 흔히 사용된다. 설문 조사는 자유롭게 의견을 적어내도록 하는 방식과 주어진 질문에 대해서만 답을 하는 방식으로 구분된다.

해당 과정에서 엔티티, 어트리뷰트, 엔티티들의 관계, 연산 등을 정의해야한다. 

 

ex) 도서관

엔티티: 사람, 책 등

어트리뷰트: 사람(id, 대출 정보 등), 책(도서번호, 저자, 책제목 등)

연산: 대출, 반납, 등록, 벌금 등등

 

개념적 설계

높은 추상화 수준의 데이터 모델을 기반으로 정형적인 언어로 데이터구조를 명시

 

cf) 높은 추상화 수준의 데이터 모델: 사람이 관심을 가지지 않아도 되는 요소는 배제하고, 관심을 가져야하는 부분에 대해 표현

ex) 이름 어트리뷰트가 필요하지만 몇 비트까지 필요한지는 나타내지 않아도 된다.

 

DBMS 선정

내가 표현하고자하는 내용을 DBMS가 충분히 표현할 수 있는가에 대해 검토해야한다.

 

논리적 설계

물리적 설계

처리 요구사항: 분당 1000회 이상 처리해야한다 등등에 대한 요구사항

평균시간과 피크시간을 따로 고려할 수 있다.

 

트랜잭션 설계

 

 

구현 단계

데이터베이스의 구축과 튜닝을 담당함

 

물리적 데이터베이스

물리적인 저장 장치에 실질적으로 어떻게 저장하는지, 접근 방식을 다룸

 

---

2. ER 모델

1) ER 모델이란?

(1) 개념

설계를 용이하게 하기위해 고안된 모델

지금은 ER 모델에서 더 풍성하게 발전한 EER 모델이 많이 사용된다. 이를 통틀어서 ER 모델이라 한다.

개념적 설계를 위한 인기있는 모델로서 많은 CASE 도구들에서 이를 지원한다.

ER 모델에서는 실세계를 엔티티, 어트리뷰트, 엔티티들의 관계로 표현한다. 

관계 데이터 모델로 알고리즘을 이용해 쉽게 사상할 수 있다.

 

쉽게 이용할 수 있다. 이는 정형적인 언어이기에 누가 보더라도 해석이 한가지로만 가능하다. 

구현에 독립적이어서 어떤 데이터베이스 모델을 사용하더라도 그 데이터베이스 모델로 사용이 가능하다.

 

2) ER 모델의 요소

(1) 엔티티

실세계의 객체

사람이나 교과목과 같이 실체가 있거나 실체가 있지 않은 추상적인 것들도 엔티티의 대상이 될 수 있다.

 

엔티티 타입

사람 간에는 공통의 특징이 있을 수 있기에 사람이라고하는 엔티티타입을 가질 수 있고, 교과목 또한 공통의 특징을 가질 수 있기에 교과목이라는 엔티티타입을 가질 수 있다.

 

엔티티 집합은 엔티티의 모임을 의미한다.

하나의 엔티티는 한 개 이상의 엔티티 집합에 속할 수 있다. 

엔티티 타입은 내포, 엔티티 집합은 외연에 해당된다. 하지만 실질적으로는 이를 엄격하게 구분할 필요는 없다.

사각형은 엔티티타입을 의미한다.

 

강한 엔티티 타입: 정규 엔티티 타입으로 독자적으로 존재할 수 있다. 자신의 키 어트리뷰트를 사용해서 고유하게 엔티티를 식별할 수 있다.

약한 엔티티 타입: 키를 형성하기에 충분한 어트리뷰트를 갖지 못했다. 따라서 이는 고유하게 식별하기 위해 강한 엔티티 타입이 추가적으로 필요하다.

 

ex) 사원들의 부양가족이 있을 때, 부양가족의 이름에 대한 엔티티 타입이 있다고 했을 때 부양가족만 봐서는 엔티티를 유니크하게 구분할 수 없게된다. 하지만 사원이라고 하는 엔티티타입의 도움을 받으면 약한 엔티티 타입을 구현할 수 있게된다.

 

외래키가 기본키가 되는 경우...?

 

(2) 애트리뷰트

한 어트리뷰트의 도메인은 그 어트리뷰트가 가질 수 있는 모든 가능한 값들의 집합을 의미한다. 

어트리뷰트는 타원으로 표현하고, 키 어트리뷰트는 밑줄을 그어서 표시한다.

관계 데이터 모델에서의 어트리뷰트보다 추상적인 개념으로 고려하면 된다.

 

엔티티는 독립적인 의미를 갖는데, 어트리뷰트는 독립적인 의미를 갖지 않는다.

 

하나의 어트리뷰트가 여러 어트리뷰트들로 구성된다. 한 어트리뷰트에 여러 어트리뷰트가 붙어있으면 이를 복합 에트리뷰트로 고려하면 된다.

 

 

어트리뷰트 값의 개수에 따른 분류

하나의 값을 가지는 애트리뷰트

대부분 이에 해당한다.

 

여러 값을 가질 수 있는 애트리뷰트

관계 데이터 모델에서는 허용하지 않지만 정보 모델에서는 이를 허용해준다.

 

데이터베이스에 저장된 어트리뷰트

 

한 어트리뷰트의 값으로부터 얻어진 어트리뷰트

데이터 베이스에 저장하지는 않지만 자주 사용하고, 주요한 어트리뷰트에 대해 이를 유도된 어트리뷰트라고 한다.

예를들어 주민번호를 통해 나이를 계산할 수 있다.

 

복합 애트리뷰트: address

다치 애트리뷰트: hobby

유도된 애트리뷰트: age

나머지는 단순 애트리뷰트, 단일값 애트리뷰트, 저장된 애트리뷰트가 된다.

키 어트리뷰트는 Empno가 된다.

 

약한 엔티티 타입

약한 엔티티 타입에게 키 어트리뷰트를 제공하는 엔티티 타입을 소유 엔티티 타입, 식별 엔티티 타입이라 한다.

dependent에서 employee 방향으로는 이중선으로 나타낸다.

다이아몬드는 관계에 대한 내용이다.

 

(3) 관계

관계, 관계 집합, 관계 타입이 있다. 엔티티, 엔티티 집합, 엔티티 타입에서 이야기한 개념과 유사하다.

 

ex) 

사원: e1, e2, e3

부서: d1, d2

e1 - d1

e2 - d2

d3 - d3

가 모두 각각 관계가 된다. 

 

여기에 부양 가족에 대한 정보 d1, d2가 있을 때

e1-d1

e2-d2

의 관계로 등장할 수 있다. 이때 부양가족과 사원에 대한 관계들, 사원과 부서와의 관계들은 성격이 다르다. 이때 사원과 부양가족에 대한 관계 집합, 사원과 부서와의 관계 집합을 따로 둘 수 있다. 

 

관계의 어트리뷰트

관계도 어트리뷰트를 가질 수 있다.

공급자가 부품을 공급할 때 quantity의 어트리뷰트를 가질 수 있다.

s1 -> p1으로 50개

s2 -> p2로 30개 등등

각 관계마다 관계 타입의 어트리뷰트로 이를 따로 표현할 수 있다. 

 

엔티티 타입과는 다른 점은 키 어트리뷰트가 존재하지 않는다는 점이다.

 

차수

1진 관계: 관여되는 엔티티가 하나인 경우로, 셀프 조인이 이에 해당한다.

2진 관계: 대부분 2진 관계이다.

3진 관계: 회사에서 어떤 부서에 어떤 부품을 공급할지 등을 정할 수 있다.

ex) s1이 p2를 50개 공급하는데 project1에 공급할 수 있다. 또한 s2이 p1을 30개 공급하는데 project1에 공급할 수 있다. 

 

관계, 관계 집합, 관계 타입을 구분할 수 있어야한다.

 

복습

작은 세계 -> 요구사항분석(명세서 도출) -> 개념적 설계(개념적 스키마(ERD) 도출) -> DBMS 선정 작업 -> 논리적 설계(논리적스키마 도출 ex) 관계 데이터베이스 스키마)  + 스키마 정제화 -> 물리적 설계

 

카디날리티 비율

관계 정보에 대해 제약을 둘 수 있다. 카디날리티는 그 예시이다.

1:1

A 엔티티 타입에는 엔티티가 6개 있다. A의 엔티티 하나가 B에 있는 엔티티와 맺을 수 있는 값이 하나있고, B도 마찬가지로 A에 있는 엔티티와 하나만 관계를 맺을 수 있는 경우

ex) 결혼을 할 때 남성과 여성 

 

 

1:N

A엔티티는 B엔티티의 값을 여러개 가질 수 있지만 B엔티티는 A엔티티의 값을 하나만 가질 수 있는 경우를 의미한다.

ex) 사람과 email

 

M:N

엔티티들이 여러개로 연결될 수 있는 경우

ex) 학생과 과목

 

 

카디날리티 비율의 최소값과 최대값

 

왼쪽과 오른쪽의 값이 모두 다르게 된다.

1:1의 경우

(0, 1), (0, 1)이 된다.

 

1:N의 경우

(0, *), (0, 1) or (0, *), (1, 1)

N:M의 경우

(0, *), (0, *)

 

그러나 이는 케이스에 따라 달라질 수 있다.

 

화살표 표기를 하는 경우에는 문자로 표현하는 것이 아닌 그래프를 통해 표시를 한다.

• 화살표의 머리가 있으면 1
• 화살표의 머리가 없으면 N

차는 최대 한대만 팔려야하기에 0부터 1까지의 범위를 가진다.

고객들은 차를 두 대 이상 살 수 있기에 최대값은 m, 판매원은 차를 두 대 이상 팔 수 있기에 최대값은 n이다. 

이때 m은 n과 다르다. 

 

역할

엔티티가 관계에 참여할 때 어떤 역할로 참여하는지 적으면 의미를 명확하게 할 수 있다. 특히 하나의 엔티티가 동일 관계에 두 번 참여할 때는 다른 역할로 참여하기에 이 역할을 적여야한다. ex) 셀프조인

위의 경우에는 역할을 명시해야한다.

 

전체 참여와 부분 참여

엔티티 타입에 속하는 모든 엔티티가 그 관계에 참여할 때 전체 참여라하고, 참여하지 않아도 되는 경우에는 부분 참여라 한다.

employee는 부서장을 의미한다. 따라서 employee는 일부만 이에 참여한다. department는 모든 부서에 장이 있어야하기에 전체 참여를 한다.

 

ex) 국가와 국민은 모두 전체 참여를 한다.

 

다중 관계

두 개 이상의 관계 타입이 있다.

 

순환적 관계

하나의 엔티티 타입이 동일한 관계 타입에 두 번 이상 참여하는 것

부품이 이의 예시이다.

 

 

ER 스키마를 작성하기 위한 가이드라인

다치 애트리뷰트(여러 값을 가지는 경우)는 어트리뷰트가 아닌 엔티티로 구성해야한다.

 

공급자는 다치 어트리뷰트이기에 이는 엔티티여야한다. 

공급자 도시는 단순히 어트리뷰트로만 이를 처리할 수도 있겠지만, 조직체에서 관심있어하는 대상이고 거리 등의 추가적인 정보가 필요하다면 엔티티로 구성할 수 있다.

 

 

 

데이터 베이스 설계 과정

(1) 요구사항 분석 후 명세서 작성 

모든 요구사항이 명세서에 포함되어야한다.

(2) 엔티티 타입 식별

(3) 관계 타입 식별

(4) 카디날리티 비율 설정

(5) 엔티티 타입과 관계 타입 중 필요한 어트리뷰트 식별 및 도메인 식별

(6) 엔티티 타입을 위한 기본키 식별

(7) 위의 정보를 모아 ER 스키마 다이어그램을 그림

(8) ER 스키마 다이어그램과 요구사항에 부합하는지 검사

(9) 데이터베이스 모델로 변환

 

 

ER 모델의 다른 표기법

 

엔티티 타입은 박스 위쪽에 위치한다.

어트리뷰트는 박스 아래쪽에 위치한다.

실선 위에 관계 이름을 적는다.

카디널리티 비율은 선의 바깥 쪽에 나타낸다. 세로줄은 1, 세모는 1 이상, o는 0을 의미한다.

내부 기호는 참여 제약조건으로 부분 참여인지, 전체 참여인지를 의미한다. 실선이 하나 있다면 전체 참여를 의미하고, o은 부분참여를 의미한다.

 

설계 사례

(1) 요구사항 분석

 

(2) 엔티티 타입 및 애트리뷰트 식별

DEPENDENT는 약한 엔티티 타입

 

관계와 어트리뷰트들을 식별

 

...

 

이러한 내용을 바탕으로 ER 스키마 다이어그램을 그린다.

 

4. ER 스키마를 관계 모델의 릴레이션으로 사상

1) 논리적 설계

- RDBMS를 사용한다고 가정

 

ER 다이어그램을 관계 데이터베이스의 모델로 매핑하는 것은 알고리즘이 존재한다.

릴레이션으로 사상할 대상에 따라 달라진다.

 

 

각 단계별로 매핑 대상이 달라진다.

3~5단계는 카디날리티 비율에 따라 단계가 구분된다.

 

2) ER-관계 사상 알고리즘

(1) 정규 엔티티 타입과 단일값 어트리뷰트

복합 어트리뷰트가 있다면 그를 구성하는 어트리뷰트를 포함시킨다.

 

(2) 약한 엔티티 타입과 단일 값 어트리뷰트

 

약한 엔티티 타입의 키는 부분키이다. 이에 따라 소유 엔티티의 키를 가져와서 함께 키를 구성해야한다.

예제에선 PK1과 K가 함께 기본키가 된다.

PK1은 외래키이며 기본키를 구성하는 키이다.

 

(3) 2진 1:1 관계 타입

 

관계가 있는 엔티티 중 하나의 테이블에 외래키로 값을 포함한다. 이는 관계 타입을 위해 추가된다. 

외래키로 FK1이 E2 테이블에 추가하거나 FK2가 E1 테이블에 추가될 수 있다.

- 해당 방법에서 E1과 E2의 엔티티 개수가 차이가 난다면 엔티티 개수가 더 작은 테이블에 FK를 추가하는 것이 좋다.

ex) 1000개: 100개 -> 70개 연결 => 1000개 중 70개만 연결하면 930개가 null, 100개 중 70개만 연결하면 30개가 null

 

3번째 방법은 별도의 릴레이션을 생성하는 것이다. R은 관계 타입을 나타내는 별도의 릴레이션을 생성하는 것이다. 이는 각 테이블의 외래키를 연결해 가지고 있게 되고, FK1과 FK2가 모여 기본키를 이루게 된다.

- 해당 방법은 릴레이션 개수가 많아지기에 복잡도가 많아지게 된다. 

 

네번째 방법은 두 릴레이션을 하나의 릴레이션으로 합하는 것이다.

- 양 엔티티의 개수가 많이 차이나고 부분 참여를 하는 경우에는 메모리가 더 많이 소비되기에 이를 사용하기 위해서는 양 엔티티가 모두 전체 참여이거나 전체 참여에 가까운 경우에 사용하면 메모리 공간을 아낄 수 있다.

- 이는 추천하는 방법이 아니다. 서로 다른 엔티티는 독립적이기 때문에 하나의 테이블로 만드는 것은 좋지 않다.

 

(4) 정규 2진 1:N 관계 타입

 

방법 1

외래키를 집어넣는 테이블은 N으로 연결된 것으로 정해진다. 

 

방법 2

별도의 릴레이션을 사용하는 위의 방법과 동일하다.

 

(5) 2진 M:N 관계 타입

별도의 릴레이션을 생성하는 방법밖에 없다.

 

 

(6) 3진 이상의 관계 타입

릴레이션을 별도로 생성하고 각 엔티티 타입의 개수만큼 외래키를 생성해 이를 기본키로 설정한다. 

관계 타입에 어트리뷰트가 있으면 생성된 릴레이션이나 추가한 릴레이션에 해당 어트리뷰트를 추가해야한다.

 

1대 N대 N의 관계에서는 나머지 값의 쌍을 가지고 판단할 수 있다.

위의 예시로는 E2와 E3의 쌍이 E1의 엔티티와 몇 개 대응되는지를 의미한다. 

ex) E2의 1, E3의 1이 E1의 1, 2와 연결되는 것은 불가능하다. // E3의 1, E1의 1은 E2의 1, 2와 연결되는 것이 가능하다.

따라서 FK2와 FK3의 값이 주어지면 E1의 값을 알 수 있기에 FK2와 FK3만 기본키의 일부로서 만들고, FK1은 기본키로 포함되지 않는다.

 

(7) 다치 어트리뷰트

MVA: Multi Valued Attribute

다치 어트리뷰트를 새로운 릴레이션으로 만들고, 외래키로 연결한다.

이때 취미나 외래키를 결합한 것이 기본키가 된다. 

 

예시

 

 

ER 개념과 데이터베이스 개념들의 대응 관계