[DB] ER 모델과 데이터베이스 설계

 

 

Database Design Process

우리는 데이터베이스를 설계할 때 몇 가지 단계를 거쳐야한다.

1. Requirements collection and analysis(요구사항 수집 및 분석): 어떤 데이터를 담을지, 그 데이터로 뭘 할 건지 알아내는 단계이다. 
2. Conceptual design(개념적 설계): 실제 세상의 복잡한 데이터들을 논리적인 모델로 바꿔주는 작업.
3. Logical design(논리적 설계): 개념적 설계를 바탕으로 특정 DBMS(Oracle, MySQL 등)에 맞게 테이블 구조를 설계하는 단계.

 

 

---

 

 

Entities & Attributes & Relationships개념

 

Entity-Relationship(ER) 모델은 Entities, relationships, attributes로 데이터를 설명한다.

 

1. 엔티티 (Entities)

엔티티는 현실 세계에 존재하는 구체적인 사물이나 개념을 의미한다. 사람, 자동차, 회사와 같이 물리적인 실체가 있을 수도 있고, 직업, 대학교 강의처럼 개념적인 것일 수도 있다. 같은 속성을 가진 엔티티들의 모음을 Entity Type이라고 하고, 특정 시점에 데이터베이스에 존재하는 모든 엔티티의 모음을 Entity Set이라고 한다.

ER 다이어그램에서는 위와 같이 표시한다.  

 

 

 

Weak Entity

자기 자신을 식별할 수 있는 고유한 키 속성(Key Attributes)이 없는 엔티티 타입이다. weak entity는 혼자서 존재할 수 없고 반드시 다른 엔티티 타입에 의존해야만 한다.

 

반면에 우리가 지금까지 다뤘던 대부분의 엔티티(예: EMPLOYEE, DEPARTMENT)는 스스로를 식별할 수 있는 키(SSN, Department_Number 등)를 가지고 있어서 Regular 엔티티 또는 Strong 엔티티라고 불린다.

 

 

weak entity의 구성 요소와 식별 방법

약한 엔티티는 두 가지 정보를 조합해야만 완전히 식별될 수 있다.

 

1. Partial Key

partial key는 weak entity가 가질 수 있는 속성 중에서 같은 소유자 엔티티에 속하는 weak entity들 사이에서만 고유하게 구별되는 속성이다. 단, partial key는 전역적으로 유일한 것이 아니라, owner entity 안에서만 유일하다는 점을 주의해야 한다.

• 예시: 부양가족(DEPENDENT) 엔티티에서 Dependent_name이 부분 키가 될 수 있다. 한 직원에게는 이름이 같은 부양가족이 없다고 가정하는 것이다.

 

2. Owner Entity key

약한 엔티티가 관계를 맺고 있는 Parent Entity의 기본 키(예: EMPLOYEE의 SSN)가 필요하다.

결국, (Owner Entity의 키) + (Weak Entity Partial key) 이 둘을 합쳐야만 Weak Entity의 인스턴스를 데이터베이스 전체에서 고유하게 식별할 수 있게 된다. 

 

 

 

 

ER Diagram으로는 위와 같이 표기할 수 있다.

1. Weak Entity: 이중 사각형으로 표현한다.
2. 식별 관계 (Identifying Relationship): Weak Entity와 Owner Entity를 연결하는 관계를 식별 관계(Identifying Relationship)라고 하는데, 이는 이중 마름모꼴로 표현한다. 
3. Partial Key: Weak Entity의 partial key는 밑줄이 아닌 점선 밑줄로 표시한다.
4. Participation Constraint: weak entity는 owner entity 없이는 존재할 수 없기 때문에, 식별 관계에 항상 전체 참여(Total Participation) 제약 조건을 가진다. 그래서 weak entity 쪽에는 항상 이중 선이 연결되어야 한다.

 

3,4번은 뒤에서 나오는 '관계'를 학습하면 이해가게 될것이다. 

 

---

 

2. 속성 (Attributes)

속성은 엔티티가 가진 고유한 '특성'이다. 사람 엔티티에는 이름, 나이, 성별, 주소 같은 속성이 있다. 각 속성은 특정 '값 집합(value set)'을 가진다. 예를 들어, '나이'는 정수 값, '이름'은 문자열 값을 가진다.

 

속성(Attributes)은 여러 가지 종류로 나뉜다.

• 단순 속성(Simple/Atomic): 더 이상 쪼갤 수 없는 속성으로, 주민등록번호(SSN), 성별(Sex) 등이 여기에 해당한다.
• 복합 속성(Composite): 여러 구성 요소로 이루어진 속성이다. 예를 들어, 주소(Address)는 도시, 주, 우편번호 등으로 구성될 수 있고, 이름(Name)은 성과 이름으로 구성될 수 있다.
• 다중 값 속성(Multi-valued): 하나의 엔티티가 해당 속성에 대해 여러 값을 가질 수 있는 경우이다. 자동차(CAR)의 색상(Color)이나 학생(STUDENT)의 이전 학위(PreviousDegrees)가 이에 해당한다.
• 유도 속성(Derived): 다른 저장된 속성으로부터 값을 유도해낼 수 있는 속성이다. 직원의 생년월일(Birth_date)로부터 나이(Age)를 계산할 수 있는 것과 같다.

 

ER 다이어그램에서 Attibutes는 위와 같이 표시한다.

 

 

---

 

3. 관계 (Relationships)

관계는 둘 이상의 엔티티 간의 연결을 의미한다. 예를 들어, '직원'과 '부서'는 '소속'이라는 관계를 맺을 수 있고, '직원'과 '프로젝트'는 '참여'라는 관계를 맺을 수 있다.

 

예를 들어, 

• Manager of DEPARTMENT: 처음에는 부서(DEPARTMENT)의 속성으로 관리자(Manager)를 정의했다. 그런데 이건 사실 MANAGES라는 직원과 부서 사이의 관계이다. 
• Works_on of EMPLOYEE: 직원(EMPLOYEE)의 Works_on 속성은 '어떤 프로젝트에서 몇 시간 일하는지'를 나타내는데, 이것 역시 WORKS_ON이라는 직원과 프로젝트 사이의 관계로 봐야한다. 

이렇게 엔티티의 속성처럼 보였던 것들을 독립적인 관계로 분리해서 관계를 바라봐야한다. 

 

 

ER 다이어그램에서는 위와 같이 나타낸다.

 

 

관계에는 구조적 제약 조건(Structural Constraints)라고 하는게 존재하는데, 두 가지 주요 제약 조건이 있다:

 

1. Cardinality Ratio

Cardinality Ratio: 관계에 참여할 수 있는 엔티티의 최대 수를 나타낸다. 1:1, 1:N, N:1, M:N으로 표현한다.

• MANAGES: 한 부서는 오직 한 명의 관리자를 가질 수 있다. 그리고 한 직원은 최대 한 개의 부서만 관리할 수 있다. 이건 1:1 관계가 된다.
• WORKS_FOR: 한 부서에는 여러 명의 직원이 있을 수 있지만, 한 직원은 오직 한 부서에만 소속된다. 이건 1:N 관계이다. 부서입장에서는 여러 명(N)의 직원이 속할 수 있기 때문에 직원이 N, 부서가 1인 관계이다. 

N:1 관계에서 N, 1 결정하는 부분이 계속 헷갈리는데, 나는 JPA에서 연관관계의 주인. 즉, 외래키를 가지는 쪽이 N이라고 생각하면 편하였다. 지금 상황에서도 직원 테이블에 부서키를 외래키로 가지고 있어야 하기 때문에 직원을 N, 부서를 1로 생각하였다. 

 

 

ER Diagrams에서는 위와 같이 표시한다. 

 

 

 

2. Participation Constraint

Participation Constraint: 특정 엔티티가 관계에 필수적으로 참여해야 하는지 여부를 나타낸다.

• Total Participation: 모든 엔티티가 관계에 참여해야 한다. ER 다이어그램에서는 이중 선으로 표현한다. "모든 직원은 반드시 한 부서에 속해야 한다"는 경우에 해당한다.
• Partial Participation: 일부 엔티티만 관계에 참여해도 된다. ER 다이어그램에서는 단일 선으로 표현한다. "모든 직원이 부서를 관리할 필요는 없다"는 경우에 해당한다.

Total Participation은 구현 시 외래 키(FK)에 NOT NULL 제약을 두거나, 추가적인 제약 조건을 활용해 보장할 수 있다.

 

 

 

ER Diagrams에서는 위와 같이 표시한다. 

 

 

 

Cardinality Ratio &  Participation Constraint 표기

 

ER Diagram으로 나타내면 위와 같다. 

 

• Cardinality Ratio (1, N, M): 관계 마름모꼴 옆에 '1', 'N', 'M'과 같은 문자로 관계의 최대 참여 수를 표시한다. 
  • WORKS_FOR 관계에서 DEPARTMENT 쪽에 '1'이, EMPLOYEE 쪽에 'N'이 있는 것은 1:N 관계임을 나타낸다.
• Participation Constraint (line): 참여가 필수적인지 아닌지를 선의 종류로 구분한다.
  • 단일 선은 Partial Participation (부분 참여)을 의미하며, 일부 엔티티만 관계에 참여해도 됨을 나타낸다.
  • 이중 선은 Total Participation (전체 참여)을 의미하며, 모든 엔티티가 관계에 반드시 참여해야 함을 나타낸다.

 

 

 

(min, max) 표기법

 

(min, max): 각 엔티티가 관계에 참여하는 횟수를 정확하게 나타낸다.

 

• MANAGES 관계
  • EMPLOYEE 쪽에 (0, 1)이 있다. 이는 직원이 부서를 관리하지 않을 수도 있고(최소 0), 최대 한 개의 부서만 관리할 수 있음(최대 1)을 의미한다.
  • DEPARTMENT 쪽에 (1, 1)이 있는 것은 한 부서가 반드시 1명의 관리자를 가져야 함(최소 1, 최대 1)을 의미한다.
• WORKS_FOR 관계
  •  EMPLOYEE 쪽에 (1, 1)이 있다. 이는 모든 직원이 반드시 한 부서에 속해야 함(최소 1, 최대 1)을 나타낸다.
  • DEPARTMENT 쪽에 (4, N)이 있는데, 이는 한 부서에 최소 4명 이상의 직원이 있어야 하며, 최대 직원의 수는 제한이 없음(N)을 의미한다.