[DB] ER-to-Relational Mapping 알고리즘

 

지난번엔 ER 다이어그램 설계와 제약조건에 대해 이야기했었다. 오늘은 그다음 단계, ER 다이어그램을 실제 데이터베이스 테이블로 바꾸는 과정, 즉 ER-to-Relational Mapping 을 알아보자.

 

쉽게 말하면 "그림으로 그려둔 관계들을 실제 DB 테이블로 옮기는 작업"이다. 개념 모델을 논리 모델로 옮기는 과정이라고 보면 된다.

 

 

위 ER 다이어그램과 관계형 데이터베이스를 예시로 계속 설명하겠다.

 

 

---

 

우리가 지켜야 할 원칙들

본격적으로 매핑을 시작하기 전에, 먼저 ER-to-Relational Mapping의 세 가지 목표(Goals) 를 짚고 가자.

 

목표 1. 모든 정보를 잃지 말자 (Preserve All Information)

ER 다이어그램에 있는 모든 속성과 관계는 릴레이션 스키마로 옮겼을 때 하나도 빠지면 안 된다.
이건 말 그대로 "손실 없는 변환(lossless mapping)"을 보장하는 첫 번째 원칙이다.

 

 

목표 2. 제약 조건을 가능한 한 유지하자 (Maintain Constraints)

ER 모델에서는 참여(Participation), 카디널리티(Cardinality) 같은 세밀한 제약 조건을 표현할 수 있다. 하지만 릴레이션 모델로 바꾸면 그중 일부는 사라진다.

예를 들어, ER 다이어그램에서 "한 부서는 최대 10명의 직원만 가질 수 있다" 같은 max cardinality (1:10) 제약은 SQL 스키마에서는 직접 표현하기 어렵다. 그래서 가능한 한 보존하려는 게 목표다.

 

 

 

목표 3. NULL을 최소화하자 (Minimize Null Values)

릴레이션 설계에서 NULL이 많다는 건, 설계가 덜 정제되었다는 신호다. NULL은 불필요한 공백이 생긴다는 뜻이고, 이는 결국 쿼리의 복잡도와 저장 효율에 영향을 준다. 그래서 매핑할 때 속성의 위치를 가능한 한 명확하게 지정해서 NULL을 줄이는 방향으로 설계한다.

 

 

---

 

ER-to-Relational Mapping 알고리즘 개요

그럼 이제 본격적으로, ER 다이어그램을 테이블로 바꾸는 과정을 단계별로 살펴보자.
아래는 그 전체 7단계 알고리즘의 개요다.

1. Regular Entity Types — 강한 엔티티를 각각 테이블로 만든다.
2. Weak Entity Types — 약한 엔티티는 주인 엔티티의 키를 포함시킨다.
3. Binary 1:1 Relationships — 1:1 관계는 한쪽에 FK를 둔다.
4. Binary 1:N Relationships — 1:N 관계는 N 쪽에 FK를 둔다.
5. Binary M:N Relationships — M:N 관계는 별도 테이블을 만든다.
6. Multivalued Attributes — 다중값 속성은 따로 테이블을 만든다.
7. N-ary Relationships — 3개 이상 관계도 별도 관계 테이블을 만든다.

 

---

 

1. Regular Entity → 테이블 하나씩 만들어주기

가장 기본적인 건 강한 엔티티(Strong Entity) 다. 이건 말 그대로 자기 혼자서도 존재 가능한 객체들이다.
예를 들어 EMPLOYEE, DEPARTMENT, PROJECT 같은 것들 말이다. 이들은 각각 독립적인 테이블로 변환된다. 엔티티의 속성(Attribute)들은 컬럼으로 들어가고, Primary Key 는 엔티티의 키 속성을 그대로 가져온다.

 

예: EMPLOYEE(Ssn, Name, Address, ...) DEPARTMENT(Dnumber, Dname, ...) PROJECT(Pnumber, Pname, ...)

 

즉, 강한 엔티티는 "혼자서도 존재할 수 있는 엔티티"이니까, DB에서도 당당히 자기 테이블을 갖는다.

 

 

 

---

 

2. Weak Entity → 주인공의 Entiity key 포함

그다음은 약한 엔티티(Weak Entity)이다. 이건 혼자서는 존재할 수 없는, 의존적인 존재다.

 

예를 들어 DEPENDENT은 EMPLOYEE 없이는 의미가 없다. 그래서 테이블을 만들긴 하지만, 주인 엔티티의 PK를 FK로 포함시켜야 한다. 그리고 자기만의 구별값(Partial Key)이 있다면, FK와 Partial key를 합쳐서 PK로 만든다. 

 

예: DEPENDENT(Essn, Dependent_name, Relationship, ...) PK: {Essn, Dependent_name}

 

이렇게 하면 "누구의 dependent인지 + 이름" 조합으로 유일해진다.

 

 

---

 

3. 1:1 관계 → 한쪽에 FK 박아주기

두 테이블이 1대1 관계라면, 사실 열쇠(FK)는 한쪽만 들고 있으면 된다.
예를 들어 "하나의 부서(DEPARTMENT)는 한 명의 관리자가 있다(EMPLOYEE)" 라면, Total Participation 하는 쪽(항상 관계가 존재하는 쪽)에 외래키를 둔다.

예시: DEPARTMENT(Dnumber, Dname, Mgr_ssn) EMPLOYEE(Ssn, Ename, ...)
여기서 Mgr_ssn은 EMPLOYEE.Ssn을 참조한다.

 

이게 바로 (1) Foreign Key Approach 다. 즉, 두 테이블 중 하나에 외래키를 추가해서 관계를 표현하는 방식이다. 이때 테이블은 총 2개로 각 엔티티 하나씩 존재한다.

 

Department쪽에 외래키를 둬서 처리하는것이 NULL 데이터가 적기 때문에 훨씬 효율적이다. 즉, Total Participation하는 쪽과 관계를 맺어야한다. 

 

 

(2) Merged Relation Approach: "둘이 너무 붙어있다면, 그냥 합쳐버리자"

두 엔티티가 너무 밀접해서 굳이 나눠둘 이유가 없는 경우도 있다. 둘 다 Total Participation, 즉 항상 서로 1:1로 연결되어 있다면 그냥 하나의 테이블로 합쳐버리는 게 낫다.

예를 들어, EMPLOYEE 와 EMPLOYEE_DETAILS 라는 두 엔티티가 있다고 해보자. 그런데 모든 직원이 반드시 한 개의 상세 정보 레코드를 가진다. 이럴 경우엔 이렇게 합칠 수 있다:  EMPLOYEE(Ssn, Name, Address, Phone, Birth_date, ...)
굳이 EMPLOYEE_DETAILS 를 따로 둘 이유가 없다. 오히려 쿼리할 때 join 비용만 생긴다.

 

즉, "이 둘은 사실상 하나의 객체인데, 괜히 나눠놓은 게 아닌가?"라는 생각이 든다면 병합하는 게 맞다.

 

적용 조건:

• 양쪽 모두 Total Participation
• 관계가 진짜 1:1 (하나가 여러 개랑 연결되지 않음)
• 논리적으로 하나의 개체로 취급해도 무방함

 

 

(3) Cross-Reference Relation Approach: "중간 다리 두기"

세 번째 방법은 교차 테이블(cross-reference relation) 을 하나 더 두는 것이다. 즉, 기존의 두 엔티티 테이블은 그대로 두고, 그 둘의 관계를 표현하는 세 번째 테이블(총 3개) 을 만든다.

예시: EMPLOYEE(Ssn, Name, ...)

DEPARTMENT(Dnumber, Dname, ...)
MANAGES(Essn, Dno, Mgr_start_date) ← 이게 관계 테이블

 

여기서 MANAGES 테이블이 EMPLOYEE와 DEPARTMENT의 1:1 관계를 연결하는 다리 역할을 한다.

• MANAGES.Essn → EMPLOYEE.Ssn
• MANAGES.Dno → DEPARTMENT.Dnumber

이때 MANAGES 테이블의 기본키(PK)는 보통 {Essn, Dno}의 조합으로 설정한다. 이는 두 엔티티(EMPLOYEE와 DEPARTMENT)의 관계 인스턴스를 유일하게 구분하기 위함이다.
단, 두 엔티티가 완전한 1:1 관계이고, 한쪽이 항상 상대를 유일하게 식별할 수 있는 경우에는 예외적으로 한쪽 속성만을 PK로 사용할 수도 있다.

 

이 방식은 언뜻 보면 테이블이 세 개라 비효율적으로 보일 수 있다.
그러나 관계 자체에 속성이 존재하거나, 관계가 자주 변하거나, 완전한 1:1이 보장되지 않을 경우에는 관계를 별도의 테이블로 분리하는 것이 훨씬 안정적이다. 예를 들어 “부서장이 언제부터 부서를 관리하기 시작했는가(Mgr_start_date)” 같은 속성은 관계에 속하므로, 이런 경우 교차 테이블(MANAGES) 을 두는 것이 일반적이다.

 

---

 

4. 1:N 관계 → N 쪽이 FK 가짐

1:N 관계의 핵심은 단 하나: N쪽이 FK를 가진다. 예를 들어 "한 부서에 여러 직원이 있다"면, EMPLOYEE(N쪽)에 DEPARTMENT의 PK를 FK로 추가한다.

예시: DEPARTMENT(Dnumber, Dname)
EMPLOYEE(Ssn, Name, Dno)

여기서 EMPLOYEE.Dno → DEPARTMENT.Dnumber

 

이렇게 하면 각 직원이 어느 부서 소속인지 FK로 표현할 수 있다.

 

 

만약 반대로 FK를 넣으면?

가끔 반대로 생각하는 경우가 있다.
"부서 쪽에 직원의 FK를 넣으면 안 돼?" 그렇게 하면 DEPARTMENT 하나에 직원 수만큼 컬럼이 생기거나 NULL이 생기게 된다.

예를 들어 DEPARTMENT(Emp1, Emp2, Emp3, …) 이런 식으로. 이건 정규화(1NF) 위반이다. 데이터베이스에서 하나의 컬럼은 하나의 값만 가져야 하니까. 그래서 항상 N쪽에 FK를 두는 것이 정석이다.

 

Null 최소화의 관점에서도

FK를 N쪽에 두는 이유는 NULL을 최소화하기 위해서이기도 하다. 만약 부서가 1개뿐이고 직원이 없는 경우를 생각해보자. FK를 1쪽(부서)에 두면, 부서마다 직원 FK 컬럼이 NULL이 되어버린다. 즉, "참조할 게 없음"을 의미하는 NULL이 쓸데없이 생긴다. 하지만 FK를 N쪽에 두면, 단순히 직원 레코드가 없는 것뿐이니까 NULL 자체가 생기지 않는다. 이게 데이터베이스 설계에서 아주 중요한 포인트다.

 

 

 

---

 

 

5. M:N 관계 → 관계 테이블 하나 새로 만들기

이제부터는 조금 다른 상황이다. "여러 직원이 여러 프로젝트에 참여한다"처럼 양쪽이 모두 다수인 경우에는, 한쪽에 FK만으로 표현할 수 없다.이럴 땐 중간 테이블을 새로 만든다. 그 테이블은 두 엔티티의 PK를 FK로 포함하고, 그 둘의 조합이 PK가 된다.

 

예: WORKS_ON(Essn, Pno, Hours) PK: {Essn, Pno}
FK: Essn → EMPLOYEE.Ssn, Pno → PROJECT.Pnumber

 

이걸 흔히 relationship relation 이라고 부른다. 교차 테이블(cross-reference table)이라고도 한다.

 

 

---

 

 

6. 다중값 속성 (Multivalued Attribute) → 따로 테이블 만들어주기

한 부서가 여러 위치를 가질 수 있다면, 이는 다중값 속성(Multivalued Attribute)에 해당한다.
만약 DEPARTMENT 테이블 안에 Locations 속성을 {Seoul, Busan, Daegu}처럼 여러 값을 한 셀에 넣으면, 이는 1정규형(1NF)을 위반하게 된다.

따라서 이 속성은 별도의 테이블로 분리해야 하며, 부서의 PK(Dnumber)를 FK로 포함하고, {Dnumber, Dlocation}의 조합을 새로운 테이블의 PK로 설정한다.

 

 

예: DEPT_LOCATIONS(Dnumber, Dlocation)
PK: {Dnumber, Dlocation}

 

이렇게 하면 중복 없이 깔끔하게 관리된다. 즉, multi-valued attribute 를 atomic 하게 바꾸는 것이다.

 

 

---

 

 

7. N-ary 관계 (3개 이상 엔티티 참여) → 관계 테이블 만들어서 전부 연결

마지막은 세 개 이상이 얽혀 있는 관계다. 예를 들어 "공급자(Supplier)가 어떤 프로젝트(Project)에 어떤 부품(Part)을 공급한다" 같은 상황. 이때도 마찬가지로 관계 테이블을 만든다. 다만 FK가 세 개 이상이다.

 

예: SUPPLY(Sname, Partno, ProjName, Quantity)
PK: {Sname, Partno, ProjName}

 

이렇게 하면 "누가, 무엇을, 어디에" 공급했는지를 깔끔하게 표현할 수 있다.

 

 

---

 

 

Summary

 

 

 

ER 다이어그램을 릴레이션으로 바꾸는 과정은 결국 "정보를 잃지 않고, null을 최소화하면서, 제약 조건을 최대한 보존하는 것" 이 핵심이다.

 

이 과정을 손으로 직접 몇 번 해보면 ERD를 그릴 때부터 "아, 이건 결국 테이블이 이렇게 되겠네"라고 감이 온다. 결국 설계는 그림으로 생각하고, 테이블로 표현하는 것이라고 생각한다.

 

 

 

 

 

출처: 경북대학교 이천희 교수님, “데이터베이스” 강의 자료