Database Design using E-R model

E-R model에서 E는 Entity, R은 Relationship을 지칭하는데

이 용어가 무엇을 뜻하는지를 우선 알아보자.

 

 

Entity set

entity(개체)란 구분이 가능한 객체로 attribute의 집합으로 표현되기도 한다.

tuple과 헷갈리기 쉬운데 tuple은 실제 DB에서 사용되는 용어이고, attribute의 순서가 중요하지만,

entity는 DB 설계에서 사용되는 용어이고, attribute의 순서가 중요하지 않다.

 

이런 entity의 집합을 entity set이라 하고, 다음과 같이 그릴 수 있다.

<Fig. 01> Entity set

relation과 생김새가 비슷하지만, 여기에는 표현되지 않은 attribute가 나중에 구현에 가서는 생기는 등

차이점이 존재한다는 점을 알아두자.

밑줄친 attribute는 primary key로 사용된다.

 

- Complex attributes

<Fig. 01>에서는 simple attribute만 나열되어 있지만, E-R model에선 attribute도 많다.

주의할 것은 E-R model에서만 이렇게 표현을 하지만, 실제 구현에서는 각 attribute 마다 다르게 구현되어

결국은 simple attribute와 같은 형태로 변화한다.

 

<Fig. 02> complex attribute

name, address, street와 같이 하위 항목들로 나뉠 수 있는 attribute를 Composit attribute라고 하며,

들여쓰기 된 하위 항목을 Component attribute라 한다.

phone_number와 같이 {중괄호}가 쳐져있는 항목을 Mulitvalued attribute라고 하며,

그렇지 않은 보통 항목은 Signle-valued attribute라고 한다.

age()와 같이 함수형태인 항목을 Derived attribute라고 한다.

(이 항목은 의미가 있기에 적었지만, 실제 구현에서는 제거된다. date_of_birth로 계산할 수 있기 때문)

 

 

Relationship set

relation은 반드시 2개 이상의 entity의 관계로 생성된다.

추가적으로 각 관계에서 부수적인 정보(relationship set's attribute)를 포함할 수도 있다.

 

이런 relation의 집합을 relationship set이라 하고, 다음과 같이 그릴 수 있다.

<Fig. 03> Relationship set

위의 경우에는 관계가 (instructor.ID, student.ID)로 형성이 되지만,

대학에서 예시가 있듯 선.후수 과목의 경우 같은 테이블에서 두 개의 entity가 관계를 가질 수 있다.

이런 경우에는 아래와 같이 role(course_id, prereq_id)을 부여하게 된다

<Fig. 04> Roles

이 경우에는 관계가 (course_id, prereq_id)로 형성이 된다.

 

첫 번째 경우에는 2개의 entity set이 관계 형성에 참여했다.

이런 경우를 Bianry relationship 라 부르며 degree 2를 갖는다고 한다. (가장 일반적인 경우)

두 번째 경우에는 degree 1을 갖는다.

 

3개 이상의 entity set이 관계를 형성할 수 있는데 이 경우에는 Non-binary relationship 라 부르며,

degree n을 갖는다고 한다.

 

- Mapping Cardinalities

각 관계에서 n to n 관계를 맺음에 따라 E-R model의 모양이 달라지고, 구현 방식도 바뀌게 되는데

그 경우와 E-R model을 살펴보자.

<Fig. 05_1> one to one	<Fig. 05_2> one to one E-R model <Fig. 05> one to one
	<Fig. 06> one to many
	<Fig. 07> many to one
	<Fig. 08> many to many

* 모든 경우에서 관계에 참여하지 않는 원소, entity가 존재할 수 있다는 점을 알아두자.

* one side는 → 로 표시하고, many side는 — 로 표시된다.

 

하지만, 관계를 강제화 할 수도 있다. 아래의 그림을 살펴보자.

<Fig. 09> Partial / Total participation

student entity set은 relationship set에 두 줄로 연결되어 있다.

즉, 위 모델은 many to many 관계이지만 학생은 반드시 지도 교수를 가져야 한다는 뜻이다.

반대로 지도 교수는 지도 학생을 갖지 않아도 상관없다.

 

줄 모양이 아닌, 숫자로 그 수치를 강제화 할 수도 있다. 아래의 그림을 살펴보자.

<Fig. 10> min, max cardinality

최소..최대 관계 수를 나타내며, *은 제한이 없다는 뜻이다.

위의 경우 교수는 지도 학생을 0~무제한으로 갖을 수 있다는 뜻이고,

학생은 반드시 1명의 지도 교수를 가져야 한다는 뜻이다.

<Fig. 06>의 one to many 케이스와 동일하다.

 

 

Primary key

E-R model에서 primary key의 역할이 매우 중요하다.

entity set에서의 primary key는 각 entity를 구분짓는 요소로서 작동한다.

 

binary relationship set의 경우에는 관계를 형성하는 entity의 primary key를 이용해

primary key를 설정하는데, 그 Mapping Cardinalities에 따라 다르게 사용한다.

 

1. many to many

이 경우에는 양 쪽의 primary key를 전부 사용한다.

(instructor.ID, student.ID)와 같이.

 

2. one to many / many to one

이 경우에는 many side의 primary key를 사용한다.

전부 사용해도 상관 없지만, DB의 공간을 절약하기 위해서이다.

(instructor.ID): instructor -> student

(student.ID): instructor <- student 와 같이.

 

3. one to one

이 경우에는 아무 쪽이나 상관없이 한 쪽의 primary key를 사용하면 된다.

 

방금 살짝 언급했지만, DB 공간을 절약하는 것. 즉 중복된 데이터를 없애는 것은 매우 중요하다.

따라서 E-R model 설계에 있어서도 중복된 데이터를 최대한 없애야 한다.

하지만, 그 과정에서 각 entity를 구분할 수 없게 되는 문제가 생길수도 있는데, 아래의 그림을 살펴보자.

 

<FIg. 11> Strong / Weak entity set

본래 section entity set은 (course_id, sec_id, semester, year) 이 primary key가 되어 entity를 구분지었다.

하지만 sec_course 라는 관계를 course와 맺음으로서 course_id 가 중복 저장되는 문제가 발생했다.

따라서 section의 course_id를 제거했다.

그런데 (sec_id, semester, year) 만으론 entity를 구분할 수 없다.

이렇게 관계를 맺지 않고서는 entity를 구분할 수 없는 개채 집합을 weak entity set 라 부른다.

 

또한, weak entity set의 entity를 구분짓게 해주는 strong entity set의 entity를 identifying entity라 부르고,

홀로 entity를 구분할 수 없지만, identifying entity와 함께하면 entity를

구분지을 수 있게 해주는 attribute를 discriminator라 부른다.

<Fig. 11>과 같이 두 줄로 표현된 set이 weak entity set이 된다.

 

맨 처음 entity set을 설명할 때 했던 말이다.

relation과 생김새가 비슷하지만, 여기에는 표현되지 않은 attribute가 나중에 구현에 가서는 생기는 등
차이점이 존재한다는 점을 알아두자.

그렇다. E-R diagram 에서는 course_id가 없지만, 실제 구현에서는 생긴다.

하지만, 어느 조건에서 이런 일이 발생하는 걸까?

이제 E-R model을 실제로 구현하면서 알아보자.

 

 

Reduction to Relation Schemas

- Entity set

strong entity set은 E-R diagram에 표기된 attribute대로 바로 변환 가능하다.

하지만, weak entity set은 표기된 attribute + 식별 가능하게 해주는

strong entity의 attribute(primary key)를 가져와야 변환이 가능하다.

 

attribute 레벨에서 살펴보면 (<Fig. 02> 참조),

simple attribute은 바로 attribute로 사용하면 된다.

 

하위 항목들로 나뉠 수 있는 composit attribute는 저장하지 않고,

들여쓰기 된 하위 항목인 component attribute만 저장한다.

 

{중괄호}가 쳐져있는 mulitvalued attribute은 일단 무시한다. (뒤에 설명)

함수형태인 derived attribute는 저장하지 않는다.

 

mulitvalued attribute는 다음 2가지 방법으로 저장할 수 있고, 각각의 장단점이 존재한다.

 

1. 유한개의 attribute로 개별 이름을 정해 저장

(ID, name, phone_number1, phone_number2, phone_number3) 과 같이 저장하는 방법이다.

이 방법은 검색이 빠르다는 장점이 존재하지만,

저장량에 제한이 있다는 점과 null값이 존재할 수 있다는 단점이 있다.

하지만, 일반적인 경우에 이 방법을 더 선호한다고들 한다.

 

2. 별도의 테이블을 만들어 저장

(ID, name) | (ID, phone_number) 과 같이 저장하는 방법이다.

이 때 ID는 primary key이자 foreign key로 작동하게 된다.

이 방법은 null값이 없고, 갯수 제한이 없지만,

테이블이 클수록 검색(연산)이 느리다는 단점이 있다.

 

- Relationship set

관계로 형성되는 만큼 구현도 관계를 따른다.

 

1. many to many

이 경우에는 양 쪽의 primary key를 사용한다.

즉, E-R diagram의 primary key 조건과 같다.

 

2. one to many / many to one

이 경우에는 별도의 테이블을 만들지 않고,

one-side의 primary key를 many-side의 attribute(foreign key)로 삽입한다.

 

3. one to one

이 경우에도 별도의 테이블을 만들지 않고,

한 쪽의 primary key를 다른 한 쪽의 attribute(foreign key)로 삽입한다.

 

* 2, 3의 경우 many-side가 total participation인 경우에만 null값이 없게 된다(<Fig. 09> 참조).

partial인 경우에는 별도의 테이블을 만드는 것이 null값을 방지할 수 있다.

 

 

Design Issue

여러 이슈를 보며 복습하며 마치자.

 

<Fig. 12> Issue 1

위 경우는 student와 department에 dept_name attribute가 중복으로 존재하는 문제가 있다.

따라서 many-side의 attribute를 삭제했다.

단, 이 경우에 student는 dept_name이 없어도 ID 단독으로 entity를 구분할 수 있기에

여전히 strong entity set으로 남게된다.

 

<Fig. 13> Issue 2

위 경우는 하나의 stud_section entity에 대해 assignment, marks 모두 하나밖에 갖지 못하는 문제가 생긴다.

즉, 학생이 수강하는 과목 하나 당 과제와 점수가 하나밖에 안나오는 감사한(?) 상황이 생긴다.

이 경우는 아래와 같이 모델을 수정하면 해결 가능하다.

<Fig. 14> Issue 2 Solve 1

<Fig. 14> Issue 2 Solve 2

 

위에서 핸드폰 번호를 저장할 때 별도의 테이블을 만들지, 유한개의 attribure로 저장할 지 고민했었다.

이렇듯 Entities / Attributes 의 구현 방식에 따라 장단이 존재한다.

마지막으로 Entities / Relationship set 의 구현 방식에 따른 차이도 살펴보자.

<Fig. 15> Entities way

원래였다면 section - student 사이에 takes 라는 relationship set 만 존재했을 것이다.

하지만, 등록 정보를 보관하기 위해 registration 이라는 entity set을 추가한 경우이다.

(개인적으론 <Fig. 13> 과 같이 처리해도 될 거 같음...)