DB 05. ER 모델과 설계

개요

데이터베이스 설계는 실세계 업무 요구사항을 분석해, 데이터베이스에서 관리할 대상과 그 관계를 구조화하는 과정이다. 이 장의 핵심은 다음 흐름으로 정리할 수 있다.

요구사항 분석
→ 개념적 설계, ER model
→ DBMS 선정
→ 논리적 설계, relational schema
→ 스키마 정제, normalization
→ 물리적 설계, index / storage / tuning

ER model은 개념적 설계 단계에서 실세계를 entity, attribute, relationship으로 표현하는 모델이다. 이후 논리적 설계 단계에서 ER schema를 relational model의 relation들로 사상한다.

---

1. 데이터베이스 설계의 큰 흐름

데이터베이스 설계는 단순히 table을 만드는 작업이 아니다. 조직에서 어떤 데이터를 관리해야 하는지, 데이터 사이에 어떤 관계가 있는지, 어떤 제약조건이 필요한지를 체계적으로 정리하는 과정이다.

단계	핵심 질문	결과물
요구사항 분석	어떤 데이터를 저장하고 어떤 연산을 지원해야 하는가?	요구사항 명세
개념적 설계	실세계의 entity, relationship, constraint는 무엇인가?	ER schema
DBMS 선정	어떤 DBMS를 사용할 것인가?	DBMS 선택
논리적 설계	ER schema를 어떤 relation들로 바꿀 것인가?	relational schema
스키마 정제	중복과 이상 현상을 줄였는가?	normalized schema
물리적 설계	어떻게 저장하고 빠르게 접근할 것인가?	index, storage, tuning 전략

좋은 database design은 다음 조건을 만족해야 한다.

• 시간의 흐름에 따른 데이터의 중요한 측면을 표현한다.
• 데이터 중복을 최소화한다.
• 효율적인 접근을 제공한다.
• 데이터 무결성을 유지한다.
• 사용자와 개발자가 이해하기 쉬운 구조를 가진다.

주의할 점: 요구사항 분석 직후 바로 table을 만들면, 실세계의 관계와 제약조건이 빠질 수 있다. 먼저 ER model로 개념적 구조를 명확히 잡고, 그다음 relational schema로 변환하는 것이 안정적이다.

---

2. ER model의 기본 관점

ER model은 Entity-Relationship Model이다. 실세계를 다음 세 요소로 표현한다.

구성 요소	의미	ER diagram 표기
entity	독립적으로 존재하고 식별 가능한 실세계 객체	rectangle
attribute	entity나 relationship을 설명하는 속성	oval
relationship	entity들 사이의 연관	diamond

예를 들어 회사 업무를 ER model로 표현하면 다음처럼 생각할 수 있다.

EMPLOYEE
 ├─ Empno
 ├─ Empname
 ├─ Title
 └─ Salary

DEPARTMENT
 ├─ Deptno
 ├─ Deptname
 └─ Floor

EMPLOYEE ─ BELONGS ─ DEPARTMENT

여기서 EMPLOYEE, DEPARTMENT는 entity type이고, Empno, Empname, Deptno 등은 attribute이며, BELONGS는 relationship type이다.

---

3. Entity와 entity type

entity는 사람, 장소, 사물, 사건처럼 독립적으로 존재하면서 고유하게 식별 가능한 객체다. entity type은 동일한 attribute들을 가진 entity들의 틀이다.

Entity type: EMPLOYEE(Empno, Empname, Title, Salary)
Entity examples:
- (1001, Kim, Staff, 3000)
- (1002, Lee, Manager, 4200)

관계 모델과 연결하면 다음처럼 볼 수 있다.

ER model	Relational model
entity type	relation의 intension, schema
entity set	relation의 extension, instance

ER diagram에서 entity type은 rectangle로 표현한다.

---

4. Attribute의 종류

attribute는 entity나 relationship을 설명하는 속성이다. ER model에서는 attribute의 성격에 따라 여러 종류로 구분한다.

종류	의미	예시	관계형 변환 시 주의점
simple attribute	더 이상 나눌 수 없는 속성	Name, Salary	그대로 column
composite attribute	여러 하위 속성으로 구성	Address(City, Ku, Dong)	구성 속성만 column으로 사용
single-valued attribute	entity 하나당 값 하나	Empno	그대로 column
multi-valued attribute	entity 하나당 여러 값	Hobby, Location	별도 relation으로 분리
stored attribute	직접 저장되는 속성	Birthdate, Salary	그대로 저장
derived attribute	다른 값에서 계산되는 속성	Age	보통 저장하지 않음
key attribute	entity를 고유 식별	Empno, Deptno	primary key

예를 들어 EMPLOYEE의 attribute를 다음처럼 분석할 수 있다.

EMPLOYEE
 ├─ Empno       → key attribute
 ├─ Empname     → simple, single-valued, stored attribute
 ├─ Salary      → simple, single-valued, stored attribute
 ├─ Address     → composite attribute
 │   ├─ City
 │   ├─ Ku
 │   └─ Dong
 ├─ Hobby       → multi-valued attribute
 └─ Age         → derived attribute

Age는 Birthdate나 주민등록번호에서 계산할 수 있으므로 derived attribute다. 이런 값은 시간이 지나면 자동으로 변해야 하므로 relation의 attribute로 직접 저장하지 않는 것이 일반적으로 좋다.

---

5. Strong entity와 weak entity

Strong entity type

strong entity type은 독립적으로 존재하고, 자기 자신의 key attribute만으로 entity를 고유하게 식별할 수 있는 entity type이다. regular entity type이라고도 한다.

EMPLOYEE(Empno, Empname, Title, Salary)

여기서 Empno가 사원을 고유하게 식별한다면 EMPLOYEE는 strong entity type이다.

Weak entity type

weak entity type은 자기 자신의 attribute만으로는 key를 만들 수 없는 entity type이다. 반드시 owner entity type 또는 identifying entity type이 필요하다.

대표 예시는 DEPENDENT다.

EMPLOYEE
 └─ Empno

DEPENDENT
 ├─ Depname
 └─ Sex

Depname만으로는 회사 전체의 부양가족을 고유하게 식별할 수 없다.

사원 1001의 부양가족: 민수
사원 1002의 부양가족: 민수

따라서 DEPENDENT는 다음 조합으로 식별된다.

DEPENDENT의 식별자 = Empno + Depname

여기서 Depname은 partial key다. 한 사원 내부에서는 부양가족을 구분할 수 있지만, 회사 전체에서는 고유하지 않기 때문이다.

개념	설명
weak entity type	자체 attribute만으로 식별 불가
owner entity type	weak entity의 식별을 도와주는 entity type
identifying relationship	weak entity와 owner entity를 연결하는 관계
partial key	weak entity 내부에서 부분적으로 구분 가능한 attribute

검증 포인트: weak entity는 owner entity 없이는 존재할 수 없으므로 identifying relationship에 total participation한다.

---

6. Relationship과 relationship attribute

relationship은 entity들 사이의 연관이다. 요구사항 명세에서 동사로 나타나는 표현이 relationship 후보가 되는 경우가 많다.

사원은 부서에 속한다.
→ EMPLOYEE ─ BELONGS ─ DEPARTMENT

사원은 프로젝트에서 일한다.
→ EMPLOYEE ─ WORKS_FOR ─ PROJECT

공급자는 부품을 프로젝트에 공급한다.
→ SUPPLIER ─ SUPPLIES ─ PART ─ PROJECT

relationship도 attribute를 가질 수 있다. 중요한 판단 기준은 다음이다.

어떤 값이 entity 하나의 속성이 아니라
entity들의 조합에 따라 결정된다면
그 값은 relationship attribute일 가능성이 높다.

예를 들어 WORKS_FOR 관계에서 Duration, Responsibility는 relationship attribute다.

EMPLOYEE ─ WORKS_FOR ─ PROJECT
              ├─ Duration
              └─ Responsibility

한 사원이 여러 프로젝트에서 서로 다른 역할과 기간으로 일할 수 있으므로, 이 값들은 EMPLOYEE의 속성도 아니고 PROJECT의 속성도 아니다. EMPLOYEE-PROJECT 조합의 속성이다.

---

7. Cardinality ratio와 participation constraint

Cardinality ratio

cardinality ratio는 entity 하나가 relationship에 몇 번까지 참여할 수 있는지를 나타낸다.

유형	의미	예시
1:1	한쪽 하나가 반대쪽 하나와만 연결	사원과 전용 PC
1:N	한쪽 하나가 반대쪽 여러 개와 연결	부서와 사원
M:N	양쪽 모두 여러 개와 연결	사원과 프로젝트

예를 들어 DEPARTMENT와 EMPLOYEE는 일반적으로 1:N 관계다.

한 부서에는 여러 사원이 속할 수 있다.
한 사원은 한 부서에만 속한다.

DEPARTMENT 1 : N EMPLOYEE

EMPLOYEE와 PROJECT는 M:N 관계다.

한 사원은 여러 프로젝트에서 일할 수 있다.
한 프로젝트에는 여러 사원이 일할 수 있다.

EMPLOYEE M : N PROJECT

(min, max) notation

카디날리티를 더 정확히 표현할 때 (min, max)를 사용한다.

표기	의미
(0, 1)	참여하지 않아도 되고, 참여한다면 최대 1번
(1, 1)	반드시 1번 참여
(0, *)	참여하지 않아도 되고, 여러 번 참여 가능
(1, *)	최소 1번 참여, 여러 번 참여 가능

Participation constraint

participation constraint는 entity가 relationship에 반드시 참여해야 하는지를 나타낸다.

제약조건	의미	ER diagram 표기
total participation	모든 entity가 반드시 관계에 참여	double line
partial participation	일부 entity만 관계에 참여해도 됨	single line

예를 들어 모든 부서가 반드시 관리자를 가져야 한다면 DEPARTMENT는 MANAGES 관계에 total participation한다. 반면 모든 사원이 관리자가 되는 것은 아니므로 EMPLOYEE는 partial participation한다.

EMPLOYEE ─ MANAGES == DEPARTMENT

주의할 점: 1:1, 1:N, M:N은 최대 연결 수에 관한 제약이고, total/partial participation은 관계 참여의 필수 여부에 관한 제약이다. 두 개념을 섞으면 안 된다.

---

8. Role, multiple relationship, recursive relationship

Role

role은 relationship 안에서 entity가 맡는 의미를 구분하는 이름이다. 특히 하나의 entity type이 같은 relationship에 두 번 참여할 때 필수적이다.

EMPLOYEE ─ SUPERVISES ─ EMPLOYEE

이 구조만 보면 어느 쪽이 감독자인지 알 수 없다. 따라서 role을 붙인다.

Supervisor 역할의 EMPLOYEE
Supervisee 역할의 EMPLOYEE

Multiple relationship

두 entity type 사이에 서로 다른 relationship type이 여러 개 존재할 수 있다.

EMPLOYEE ─ WORKS_FOR ─ PROJECT
EMPLOYEE ─ MANAGES   ─ PROJECT

둘 다 EMPLOYEE와 PROJECT 사이의 relationship이지만 의미가 다르다.

Relationship	의미	Cardinality	Relationship attribute
WORKS_FOR	사원이 프로젝트에서 일함	M:N	Duration, Responsibility
MANAGES	사원이 프로젝트를 관리함	1:1	StartDate

Recursive relationship

recursive relationship은 하나의 entity type이 자기 자신과 relationship을 맺는 경우다.

PART ─ CONTAINS ─ PART

부품이 다른 부품들로 구성될 수 있다면, 같은 PART entity type이 상위 부품과 하위 부품의 역할로 동시에 참여한다.

상위 PART ─ CONTAINS ─ 하위 PART

---

9. Ternary relationship

ternary relationship은 세 개의 entity type이 하나의 relationship에 동시에 참여하는 경우다. 단순히 binary relationship 세 개로 나눌 수 없는 경우가 많다.

대표 예시는 SUPPLIES다.

SUPPLIER
   |
SUPPLIES ─ PROJECT
   |
PART

요구사항은 다음과 같다.

공급자가 어떤 부품을 어떤 프로젝트에 얼마나 공급하는가를 나타낸다.

이 사실은 세 entity가 모두 정해져야 완성된다.

Quantity = f(Supplier, Project, Part)

Quantity는 SUPPLIER의 attribute도 아니고, PROJECT의 attribute도 아니며, PART의 attribute도 아니다. SUPPLIER-PROJECT-PART 조합의 attribute이므로 SUPPLIES relationship의 attribute다.

왜 binary relationship으로 나누면 위험한가?

원래 데이터가 다음과 같다고 하자.

S1이 ProjectA에 PartX를 100개 공급
S1이 ProjectB에 PartY를 200개 공급

이를 binary relationship으로 나누면 다음 정보만 남는다.

S1 - ProjectA
S1 - ProjectB
S1 - PartX
S1 - PartY
ProjectA - PartX
ProjectB - PartY

이 정보들을 다시 조합하면 원래 존재하지 않던 다음 관계가 생길 수 있다.

S1이 ProjectA에 PartY를 공급했다
S1이 ProjectB에 PartX를 공급했다

따라서 세 요소가 동시에 하나의 업무 사실을 구성한다면 ternary relationship을 유지해야 한다.

---

10. 회사 예제 ER schema 정리

회사 설계 예제에서 식별되는 주요 entity type은 다음과 같다.

Entity type	주요 attribute	특징
EMPLOYEE	Empno, Empname, Title, Salary, Address(City, Ku, Dong)	strong entity
PROJECT	Projno, Projname, Budget, Location	Location은 multi-valued attribute
DEPARTMENT	Deptno, Deptname, Floor	strong entity
SUPPLIER	Suppno, Suppname, Credit	strong entity
DEPENDENT	Depname, Sex	weak entity
PART	Partno, Partname, Price	recursive relationship 참여

주요 relationship은 다음과 같다.

Relationship	참여 entity	의미	유형	Relationship attribute
BELONGS	EMPLOYEE - DEPARTMENT	사원이 부서에 속함	N:1	없음
WORKS_FOR	EMPLOYEE - PROJECT	사원이 프로젝트에서 일함	M:N	Duration, Responsibility
MANAGES	EMPLOYEE - PROJECT	사원이 프로젝트를 관리함	1:1	StartDate
POLICY	EMPLOYEE - DEPENDENT	사원이 부양가족을 가짐	identifying relationship	없음
CONTAINS	PART - PART	부품이 하위 부품을 포함함	recursive relationship	없음
SUPPLIES	SUPPLIER - PROJECT - PART	공급자가 프로젝트에 부품을 공급함	ternary relationship	Quantity

검증 포인트는 다음이다.

• DEPENDENT는 weak entity type이다.
• PROJECT.Location은 multi-valued attribute다.
• WORKS_FOR와 MANAGES는 같은 entity 사이의 서로 다른 relationship이다.
• SUPPLIES는 ternary relationship이며, Quantity는 relationship attribute다.

---

11. ER schema를 relation으로 사상하는 7단계

논리적 설계 단계에서는 ER schema를 relational model의 relation들로 사상한다. ER model에서는 entity type과 relationship type을 구분하지만, relational database에서는 relation만 존재한다.

단계	사상 대상	사상 규칙
1	regular entity type	relation 하나 생성
2	weak entity type	relation 생성, owner key를 foreign key로 포함
3	binary 1:1 relationship	한쪽 relation에 다른 쪽 key를 foreign key로 포함
4	binary 1:N relationship	N쪽 relation에 1쪽 key를 foreign key로 포함
5	binary M:N relationship	relationship relation 새로 생성
6	ternary 이상의 relationship	relationship relation 새로 생성
7	multi-valued attribute	별도 relation 생성

---

12. 사상 규칙별 세부 정리

단계 1: Regular entity type

각 regular entity type에 대해 relation을 하나 만든다. simple attribute는 그대로 포함하고, composite attribute는 구성 simple attribute만 포함한다.

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary)
PROJECT(Projno, Projname, Budget)
DEPARTMENT(Deptno, Deptname, Floor)
SUPPLIER(Suppno, Suppname, Credit)
PART(Partno, Partname, Price)

PROJECT.Location은 multi-valued attribute이므로 이 단계에서 PROJECT relation에 넣지 않는다.

단계 2: Weak entity type

weak entity type은 owner entity의 key를 foreign key로 가져오고, partial key와 결합해 primary key를 만든다.

DEPENDENT(Empno, Depname, Sex)

PK = (Empno, Depname)
FK = Empno → EMPLOYEE(Empno)

단계 3: Binary 1:1 relationship

1:1 relationship은 한쪽 relation에 다른 쪽 relation의 key를 foreign key로 포함한다. 가능하면 total participation하는 쪽을 선택한다. relationship attribute도 선택된 relation에 넣는다.

회사 예제에서는 MANAGES를 PROJECT에 반영한다.

PROJECT(Projno, Projname, Budget, StartDate, Manager)

Manager → EMPLOYEE(Empno)

StartDate는 MANAGES relationship의 attribute다.

단계 4: Binary 1:N relationship

1:N relationship은 N쪽 relation에 1쪽의 key를 foreign key로 넣는다.

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary, Dno)

Dno → DEPARTMENT(Deptno)

PART의 recursive relationship인 CONTAINS도 자기 참조 foreign key 방식으로 표현할 수 있다.

PART(Partno, Partname, Price, Subpartno)

Subpartno → PART(Partno)

단계 5: Binary M:N relationship

M:N relationship은 새로운 relation을 만든다. 참여 entity들의 key를 foreign key로 포함하고, 그 조합을 primary key로 사용한다. relationship attribute도 이 relation에 포함한다.

WORKS_FOR(Empno, Projno, Duration, Responsibility)

PK = (Empno, Projno)
FK = Empno → EMPLOYEE(Empno)
FK = Projno → PROJECT(Projno)

단계 6: Ternary 이상의 relationship

ternary relationship도 새로운 relation을 만든다. 참여 entity들의 key를 모두 foreign key로 포함하고, relationship attribute도 포함한다.

SUPPLY(Suppno, Projno, Partno, Quantity)

PK = (Suppno, Projno, Partno)
FK = Suppno → SUPPLIER(Suppno)
FK = Projno → PROJECT(Projno)
FK = Partno → PART(Partno)

Quantity는 SUPPLIES relationship의 attribute다.

단계 7: Multi-valued attribute

multi-valued attribute는 별도 relation으로 분리한다.

PROJ_LOC(Projno, Location)

PK = (Projno, Location)
FK = Projno → PROJECT(Projno)

---

13. 회사 예제 최종 relational schema

회사 ER schema는 최종적으로 다음 9개의 relation으로 사상된다.

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary, Dno)

PROJECT(Projno, Projname, Budget, StartDate, Manager)

DEPARTMENT(Deptno, Deptname, Floor)

SUPPLIER(Suppno, Suppname, Credit)

PART(Partno, Partname, Price, Subpartno)

DEPENDENT(Empno, Depname, Sex)

WORKS_FOR(Empno, Projno, Duration, Responsibility)

SUPPLY(Suppno, Projno, Partno, Quantity)

PROJ_LOC(Projno, Location)

각 relation의 출처는 다음과 같다.

Relation	ER model에서 온 대상	사상 단계
EMPLOYEE	regular entity + BELONGS 반영	1, 4
PROJECT	regular entity + MANAGES 반영	1, 3
DEPARTMENT	regular entity	1
SUPPLIER	regular entity	1
PART	regular entity + CONTAINS 반영	1, 4
DEPENDENT	weak entity	2
WORKS_FOR	binary M:N relationship	5
SUPPLY	ternary relationship	6
PROJ_LOC	multi-valued attribute	7

---

14. 자칫 실수하기 쉬운 부분

1:N과 M:N의 변환 방식 혼동

1:N은 새로운 relation을 만들지 않고, 보통 N쪽에 foreign key를 추가한다.

DEPARTMENT 1 : N EMPLOYEE
→ EMPLOYEE에 Dno 추가

반면 M:N은 반드시 relationship relation을 새로 만든다.

EMPLOYEE M : N PROJECT
→ WORKS_FOR(Empno, Projno, Duration, Responsibility)

Relationship attribute를 entity attribute로 잘못 넣는 경우

Duration, Responsibility, StartDate, Quantity는 relationship의 attribute다.

Attribute	붙는 위치	이유
Duration	WORKS_FOR	사원-프로젝트 조합에 따라 결정
Responsibility	WORKS_FOR	사원-프로젝트 조합에 따라 결정
StartDate	MANAGES	사원-프로젝트 관리 관계에 따라 결정
Quantity	SUPPLIES	공급자-프로젝트-부품 조합에 따라 결정

Ternary relationship을 binary relationship으로 분해하는 경우

SUPPLIES는 SUPPLIER, PROJECT, PART 세 entity가 모두 있어야 의미가 완성된다. 이를 binary relationship들로 나누면 존재하지 않는 공급 사실이 조합될 수 있다.

Multi-valued attribute를 한 column에 넣는 경우

PROJECT.Location처럼 여러 값을 가질 수 있는 attribute를 하나의 column에 넣으면 relation의 원자성 원칙에 어긋난다.

나쁜 형태:
PROJECT(Projno, Projname, Budget, Location)

좋은 형태:
PROJECT(Projno, Projname, Budget)
PROJ_LOC(Projno, Location)

Weak entity의 primary key를 partial key만으로 잡는 경우

DEPENDENT의 Depname만 primary key로 잡으면 이름 중복을 처리할 수 없다.

잘못된 key:
PK = Depname

올바른 key:
PK = (Empno, Depname)

---

15. 요약

ER model은 database conceptual design을 위한 모델이다. 실세계를 entity, attribute, relationship으로 표현하고, cardinality ratio와 participation constraint로 업무 규칙을 명확히 한다.

핵심 구분은 다음과 같다.

구분	핵심 판단 기준
entity vs attribute	독립적으로 관리할 대상인가, 다른 대상의 속성인가?
strong entity vs weak entity	자기 key만으로 식별 가능한가?
simple vs composite attribute	더 작은 속성으로 나눌 수 있는가?
single-valued vs multi-valued attribute	entity 하나당 값이 하나인가 여러 개인가?
relationship attribute	값이 entity 조합에 따라 결정되는가?
1:N vs M:N	한쪽에 foreign key로 충분한가, 별도 relation이 필요한가?
binary vs ternary relationship	두 entity만으로 사실이 완성되는가, 세 entity가 동시에 필요한가?

ER schema를 relational schema로 사상할 때는 다음 규칙을 우선 기억하면 된다.

regular entity → relation
weak entity → relation + owner key
1:1 relationship → 한쪽에 foreign key
1:N relationship → N쪽에 foreign key
M:N relationship → 새 relation
ternary relationship → 새 relation
multi-valued attribute → 새 relation

이 장의 핵심은 ER diagram을 그리는 것에서 끝나지 않는다. ER schema를 relational model로 사상할 때 어떤 요소가 table이 되고, 어떤 요소가 foreign key가 되며, 어떤 요소가 relationship relation으로 분리되는지까지 연결해서 이해해야 한다.