[데이터베이스] 데이터베이스 설계 (E-R 모델과 릴레이션 변환규칙)

본 포스팅은 "데이터베이스 개론 [2판]" 도서로 공부한 내용을 요약하기 위한 포스팅입니다.

---

1. 데이터베이스 설계 단계

  조직 구성원들의 다양한 요구 사항을 모두 만족시키는 데이터베이스를 구축하는 일은 쉽지 않다.

특히, 데이터의 양이 많고 사용자 수가 많은 조직일수록 요구에 딱 맞는 데이터베이스를 구축하는 작업은 어렵다.

 

  그렇다면 조직 구성원들의 다양한 요구 사항을 고려하여 제대로 된 데이터베이스를 구축하려면 어떻게 해야할까?

사용자들의 요구 사항을 잘 분석하고, 분석한 결과를 바탕으로 데이터베이스의 논리적, 물리적 구조를 제대로 설계해야 한다.  이미 구축된 데이터베이스는 구조를 변경하기 어려우므로 체계적인 설계 과정을 통해 데이터베이스가 올바르게 구축되어야 조직 구성원들이 빠르고 정확하게 업무를 수행할 수 있다.

 

  관계 데이터 모델을 기반으로 두고 데이터베이스를 설계할 때는 두 가지 방법을 주로 사용한다.

첫째는 E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계이고,

둘째는 정규화를 이용한 데이터베이스 설계이다.

 

두 방법의 설계 결과물은 유사하기 때문에 상황에 따라 선택하면 된다.

 

E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계는 아래와 같이 5단계로 진행된다.

 

데이터베이스 설계 과정의 각 단계별 주요 작업과 결과물

---

2. 요구 사항 분석

  데이터베이스 설계의 시작인 요구 사항 분석 단계에서는 사용자들이 데이터베이스에 원하는 것이 무엇인지를 분석한다.
  즉, 데이터베이스에 대한 사용자들의 요구 사항을 수집하고 분석하여, 개발할 데이터베이스의 용도를 명확히 파악하는 게 이 단계의 목적이다.

 

  요구 사항 분석 단계는 사용자가 요구하는 데이터베이스의 용도가 결정되는 단계이기 때문에 품질 좋은 데이터베이스를 개발하는 데 중요하다. 요구 사항 분석이 잘못되면 사용자가 원치 않는 쓸모없는 데이터베이스가 개발되어, 고치거나 다시 개발하는 수고가 뒤따를 수 있다.

그러므로 어떤 단계보다 신중하고 꼼꼼하게 작업을 수행해야 한다.

 

요구 사항 분석 순서

1. 데이터베이스를 사용할 주요 사용자의 범위 결정
2. 사용자가 조직에서 수행하는 업무 분석
   (사용자의 업무와 관련해 필요한 데이터가 무엇이고, 그 데이터에 어떤 처리가 필요한지 등에 초점)
3. 요구사항 수집 및 분석
4. 요구 사항 명세서 작성

 

예시. 인터넷으로 회원들에게 상품을 판매하는 마트를 위한 데이터베이스 설계 시 요구사항 명세서

요구사항 명세서 예시

---

3. 개념적 설계

  개념적 설계에서는 요구 사항 분석 단계의 결과물을 개념적 데이터 모델을 이용하여 표현한다.
일반적으로 개념적 데이터 모델로 E-R 모델을 많이 이용한다.
주요 작업은 분석 결과를 기반으로 데이터 요소인 개체를 추출한 후 개체 관의 관계를 결정하여 표현하는 것이다.

 

  요구 사항 분석 결과를 E-R 모델을 이용해 개념적 모델링을 하려면 먼저 E-R 모델의 핵심 요소인 개체를 추출해야 한다.

그 다음 각 개체의 주요 속성과 키 속성을 선별하고, 개체 간의 관계를 결정해야 한다.

개체, 속성, 관계를 선별하는 작업이 모두 완료되면 그 결과를 E-R 다이어그램으로 표현한다.

 

 

개념적 모델링 과정

 

3-1. 개체와 속성 추출

  개념적 설계 단계에서 가장 먼저 수행해야 하는 기본 작업은 개체를 추출하는 일이다.
개체부터 결정해야 속성과 관계도 결정할 수 있다.

  개체는 현실 세계에서 어떤 조직을 운영하는 데 꼭 필요한 사람, 사물과 같이 구별되는 모든 것을 의미한다.
즉, 개체는 저장할 가치가 있는 중요 데이터를 지닌 사람이나 사물 등이며, 개념적 모델링에서 가장 중요한 요소다.

 

  요구 사항 분석 명세서에서 개체를 추출하는 방법을 표준화한 것이 없어 개체를 추출하는 것은 쉽지 않다.

이럴 때는 제시된 요구 사항의 문장들에서 명사부터 찾는다.

단, 조직의 업무 처리와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외한다.

 

  하지만 찾아낸 명사를 모두 개체로 단정하면 안된다. 명사 중에는 개체가 아닌 속성으로 분류되는 단어도 존재하기 때문에 찾아낸 명사를 개체와 속성으로 정확히 분류하는 작업이 필요하다.

 

  요구사항 명세서에의 1항 ~ 5항까지의 내용을 기준으로 개체와 속성을 추출 후 E-R 다이어그램을 표현하면 아래와 같다.

 

 

3-2. 관계 추출

  관계는 개체 간의 의미 있는 연관성이다.
개체와 속성을 추출하고 나면 개체 간의 관계를 결정할 수 있다.

 

  일반적으로 관계는 요구 사항을 표현한 문장에서 동사로 표현된다.

그러므로 개체 간의 관계를 결정할 때는 요구 사항 문장에서 동사부터 찾아야 한다. 단, 조직의 업무 처리와 관련하여 개체 간의 연관성을 의미 있게 표현한 동사만 선택하고, 의미가 같은 동사가 여러 개이면 대표 동사 하나만 선택한다.

 

  관계를 추출한 후에는 추출한 관계에 대한 매핑 카디널리티와 참여 특성을 결정한다.

매핑 카디널리티는 관계를 맺고 있는 두 개체에서, 각 개체 인스턴스가 관계를 맺고 있는 상대 개체의 개체 인스턴스 개수를 의미한다. (일대일, 일대다, 다대다)

 

  요구사항 명세서를 기준으로 회원과 상품 사이의 관계를 추출하여 표현하면 아래와 같다.

 

---

4. 논리적 설계

  논리적 설계 단계에서는 DBMS에 적합한 논리적 데이터 모델을 이용해서, 개념적 설계 단계에서 생성한 개념적 스키마를 기반으로 논리적 스키마를 설계한다.
  즉, E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마, 테이블 스키마로 변환하는 작업을 말한다.

 

  논리적 설계 단계에서 E-R 다이어그램을 논리적 데이터 모델로 변환하는 작업을 논리적 모델링 또는 단순히 데이터 모델링이라 한다. 그리고 릴레이션 스키마와 같이 논리적 데이터 데이터 모델로 표현된 논리적 설계의 결과물을 논리적 구조 또는 논리적 스키마라고 한다.

 

  E-R 모델에서는 개체와 관계를 구분하지만, 관게 데이터 모델에서는 개체와 관계를 구분하지 않고 모두 릴레이션으로 표현하며, 다중 값 속성과 복합 속성의 표현을 허용하지 않는 점과 같은 차이 때문에 E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마로 변환하는 일은 쉽지 않다.

 

  E-R 다이어그램을 릴레이션 스키마로 변환할 때 적용할 수 있는 다섯 가지 규칙을 간단하게 알아보자.

 

* 릴레이션 스키마 변환 규칙

규칙 1 : 모든 개체는 릴레이션으로 변환한다.

  E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환한다.

단, 개체가 가지고 있는 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환한다. 개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환한다.

 

규칙 2 : 다대다(n:m) 관계는 릴레이션으로 변환한다.

  E-R 다이어그램에 있는 다대다(n:m) 관계를 하나의 릴레이션으로 변환한다.

관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환한다.

단, 관계를 맺고 있는 개체가 무엇인지 중요하므로, 관계를 맺고 있는 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 지정한다. 그리고 이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정한다.

 

규칙 3 : 일대다(1:n) 관계는 외래키로 표현한다.

  E-R 다이어그램에 있는 일대다(1:n) 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

 

  규칙 3-1 : 일반적인 일대다 관계는 외래키로 표현한다.

  규칙 3-2 : 약한 개체가 참여하는 일대다 관계는 외래키를 포함해서 기본키로 지정한다.

 

규칙 4 : 일대일(1:1) 관계는 외래키로 표현한다.

  E-R 다이어그램에 있는 일대일(1:1) 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다.

 

  규칙 4-1 : 일반적인 일대일 관계는 외래키를 서로 주고 받는다.

  규칙 4-2 : 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다.

  규칙 4-3 : 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션 하나로 합친다.

 

규칙 5 : 다중 값 속성은 릴레이션으로 변환한다.

  관계 데이터 모델의 릴레이션에서는 다중 값을 가지는 속성을 허용하지 않는다.

그러므로 E-R 다이어그램에 있는 다중 값 속성은 그 속성을 가지고 있는 개체에 해당하는 릴레이션이 아닌 별도의 릴레이션을 만들어 포함시킨다.

새로 만들어진 릴레이션의 이름은 자유롭게 정하고, 기본키는 다중 값 속성과 외래키를 조합하여 지정한다.

 

---

5. 물리적 설계와 구현

  논리적 설계 단계에서 릴레이션 스키마의 설계를 완료하면, 물리적 설계 단계에서는 하드웨어나 운영체제의 특성을 고려하여 필요한 인덱스의 구조나 내부 저장 구조 등에 대한 물리적인 구조를 설계한다.

 

  그리고 마지막으로 DBMS를 이용해 SQL 문을 작성하고 이를 실행시켜서 데이터베이스를 실제로 생성하면 데이터베이스 개발이 완료된다.

 

예시. 릴레이션을 실제로 생성하는 SQL문의 작성 예시

 

릴레이션 예시

CREATE TABLE 회원 (
	회원아이디	VARCHAR(20)		NOT NULL,
	비밀번호	VARCHAR(20)		NOT NULL,
	이름		VARCHAR(20)		NOT NULL,
	나이		INT,
	직업		VARCHAR(20),
	등급		VARCHAR(10),		NOT NULL	DEFAULT 'silver',
	적립금		INT			NOT NULL	DEFAULT 0,
	PRIMARY KEY(회원아이디),
	CHECK (나이 >= 0),
	CHECK (등급 in ('silver', 'gold', 'vip'))
);