[Relational database] 10. 개념적 설계와 ER 모델

Chapter 1: Entity-Relationship Model

• ER 모델 개요
• Entity, Attribute, Entity Set
• Relation, Relation Set

Chapter 2: ER 모델 기능

• 대응 수
• 키 제약조건
• 참여 제약조건
• 약 개체 집합
• 전문화와 일반화

Chapter 3: 개념적 설계 고려사항

• ER 모델을 이용한 개념적 설계
• 개체 or 속성
• 개체 or 관계
• 이전 관계 or 삼진 관계

---

Chapter 1: Entity-Relationship Model

ER 모델 개요

• DB 설계
  1. 개념적 설계: Entity와 Entity 사이의 Relation 정의 -> E-R 모델 사용
  2. 논리적 설계: Table과 Table 사이의 Relation 정의 -> 정규화
  3. 물리적 설계: Disk에 Data가 어떻게 저장될 것인지 정의.
• ER 모델
  • 데이터를 개체(Entity), 속성(Attribute), 관계(Relationship)로 나타내는 데이터 모델.
  • 실제 세계를 개체(Entity)라 불리는 기본 객체들과 그 객체들 사이의 관계(Relationship)으로 인식.
  • E-R 모델에서는 DB의 전체적인 논리 구조를 나타내는 조직의 스키마를 명시.
  • 실제 세계의 조직의 의미와 상호 작용을 개념적 스키마로 나타내는데 매우 유용.

 

Entity, Attribute, Entity Set

• 개체(Entity): 실제 세계에서 서로 구분되는 객체. ex) 회사, 부서, 부서장, 주소 ...
• 개체 집합(Entity Set): 비슷한 Entity들을 하나로 묶은 것.
• 속성(Attribute): 한 개체를 기술하기 위한 속성. ex) 직원의 속성: 사번, 이름, 주차 구역 번호...
  • 단순 속성 (Simple Attribute)과 복합 속성 (Composite Attribute)
  • 단일값 속성 (Single-value Attribute)과 다중값 속성 (Multi-valued Attribute)
• 도메인(Domain): 개체 집합에 대한 속성들에 대해 가능한 값의 집합.

 

* 개체 집합(Entity Set) 하나는 하나의 릴레이션(Relation)으로 매핑된다.

- Relation Schema: 직원(사번: 정수, 이름: 문자열, 주차구역번호: 정수)

- Relation Instance

사번	이름	주차구역번호
1	이순신	47
2	홍길동	16

- 직원 테이블을 만드는 DDL문

CREATE TABLE 직원 (
	사번	int,
    이름	varchar(10),
    주차구역번호	int,
    
    CONSTRAINT pk_employee PRIMARY KEY(사번)
);

 

Relationship, Relationship Set

• 관계 (Relationship): 둘 이상의 개체(Entity)간의 관련성.
• 관계 집합 (Relationship Set): 같은 유형의 관계들의 집합.
• 관계는 설명형 속성 (descriptive attribute)라는 속성을 가진다.

* ER 모델 -> Relational Model로 변환.

2진 관계

• 관계 집합도 관계 모델의 한 릴레이션으로 매핑된다.
• 관계 집합(Relationship Set)은 개체 집합(테이블)의 PK로 식별된다. 

직원 - 부서의 관계인 '근무'는 '근무 시작일'이라는 설명형 속성을 가지고, '근무 테이블'은 부서와 직원의 PK인 '부서번호'와 '사번'이 근무 테이블의 기본 키가 된다.

사번	부서번호	근무시작일
1	12	2022-01-23
2	21	2022-10-22

CREATE TABLE 근무 (
	사번 int,
	부서번호 int,
	근무시작일 date,

	CONSTRAINT pk_근무 PRIMARY KEY(사번, 부서번호),
	CONSTRAINT fk_근무_직원 FOREIGN KEY(사번) REFERENCES 직원(사번),
	CONSTRAINT fk_근무_부서 FOREIGN KEY(부서번호) REFERENCES 부서(부서번호)
);​

 

---

Chapter 2: ER 모델 기능

대응 수 (Mapping Cardinality)

• 참여 제약 조건(Participation Constraint)을 이루는 관계 비율.
• 대응 수 (Mapping Cardinality)
  • 1 : 1
  • 1 : M
  • M : 1
  • M : M

키 제약조건

 

참여 제약조건 (Participation Constraint)

• DB에서 두 개체 간의 관계에서 특정 개체가 반드시 참여해야 하는지, 선택적인지 여부를 정의하는 제약조건.

* ERD

 

* 관계 모델로 변환

모든 부서에는 부서장이 한 명씩 있어야 한다는 제약조건.

CREATE TABLE 부서 (
	부서번호 	int,
	부서이름 	varchar(10),
	예산		decimal,
	부서장사번	int,
	근무시작일	date,

	CONSTRAINT pk_부서 PRIMARY KEY(부서번호),
	CONSTRAINT fk_부서_부서장 FOREIGN KEY(사번) REFERENCES 직원(사번) ON DELETE NO ACTION
);

 

약 개체 집합

• Key가 존재하지 않는 개체 집합(Entity Set = Relation)
• 자신의 일부 속성과 다른 개체의 PK를 조합하여야 유일하게 식별됨.

CREATE TABLE 보험 (
	피부양자이름 	varchar(20),
	나이		int,
	가액		decimal,
	사번		int	NOT NULL,

	CONSTRAINT pk_보험 PRIMARY KEY(사번, 피부양자이름),
	CONSTRAINT fk_보험_직원 FOREIGN KEY(사번) REFERENCES 직원(사번) ON DELETE CASCADE
);

 

피 부양자는 약 개체이므로 PK는 <사번, 피 부양자 이름>이 된다.

CASCADE 조건으로 직원 레코드가 삭제되면 피부양자 레코드 역시 삭제된다.

 

전문화와 일반화

• 하나의 개체 집합은 집합내의 다른 개체들과 구분되는 개체들의 하위집합을 가질 수 있다. ex) 고객 > 회원
• 클래스 계층을 보는 관점
  • 전문화: 직원이 여러 클래스로 전문화 된 것.
  • 일반화: 시간제_직원, 계약직_직원이 모여서 직원으로 일반화 된 것.

* 직원 처리하는 2가지 방법.

1. 개체 집합인 직원, 시간제_직원, 계약직_직원을 각기 다른 릴레이션으로 매핑.

2. 시간제_직원과 계약직_직원에 해당하는 두 개의 릴레이션만 만드는 방법.

 

---

Chapter 3: 개념적 설계 고려사항

ER 모델을 이용한 개념적 설계

개체 or 속성

개체 or 관계

이전 관계 or 삼진 관계