데이터베이스 설계

이 글은 김연희 저 - "데이터베이스 개론 3판"을 공부하고 정리하여 작성하였습니다.

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

1️⃣ 1단계: 요구 사항 분석

요구 사항 분석 단계에서는 조직의 구성원들이 데이터베이스를 사용하는 용도를 파악함.
데이터베이스를 사용해 실제 업무를 처리하는 사용자에게서 필요한 데이터의 종류와 처리 방법과 같은 다양한 요구 사항을 수집
수집한 요구 사항을 분석하여 그 결과를 요구 사항 명세서로 작성함.
요구 사항 분석 단계에서 구축된 데이터베이스의 품질이 결정됨

2️⃣ 2단계: 개념적 설계

요구 사항 분석 단계에서 파악한 사용자의 요구 사항을 개념적 데이터 모델을 이용해 표현
개념적 데이터 모델은 개발에 사용할 DBMS의 종류에 독립적이면서, 중요한 데이터 요소 간 관계를 표현할 때 사용
개념적 데이터 모델은 E-R 모델을 많이 사용하고 중요한 데이터 요소 간 관계를 E-R 다이어그램으로 표현
요구 사항 명세서를 개념적 데이터 모델로 변환하는 일을 개념적 모델링이라 함.

3️⃣ 3단계: 논리적 설계

개발에 사용할 DBMS에 적합한 논리적 데이터 모델을 이용해 개념적 구조를 기반으로 논리적 구조를 설계함
DBMS의 종류에 따라 다양한 논리적 데이터 모델을 사용할 수 있지만, 일반적으로 관계 데이터 모델을 가장 많이 사용함
관계 데이터 모델을 사용할 경우, E-R 다이어그램을 릴레이션(테이블) 스키마로 변환하여 DBMS가 처리할 수 있도록 하는 것이 주요 작업이고 논리적 모델링 또는 데이터 모델링이라 함.
릴레이션 스키마와 같이 논리적 데이터 모델로 표현된 결과물을 논리적 구조 또는 논리적 스키마라 함.

4️⃣ 4단계: 물리적 설계

논리적 구조를 기반으로 물리적 구조를 설계함.
데이터베이스의 물리적 구조는 데이터베이스를 저장 장치에 실제로 저장하기 위한 내부 저장 구조와 접근 경로 등을 의미
그러므로 물리적 설계 단계에서는 저장 장치에 적합한 저장 레코드와 인덱스의 구조 등을 설계하고 탐색 기법 등을 정의함.
데이터베이스를 컴퓨터 시스템의 저장 장치와 운영체제의 특성을 고려해 효율적이면서 구현 가능한 물리적 구조를 설계하는 것이 주요 작업
응답 시간을 최소화하고 저장 공간을 효율적으로 활용하면서 데이터베이스 시스템의 처리 능력을 향상시킬 수 있도록 물리적 구조를 설계해야 함
물리적 구조를 내부 스키마 또는 물리적 스키마라 함.

5️⃣ 5단계: 구현

이전 설계 단계의 결과물을 기반으로 DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성
이 때 사용하는 SQL문은 테이블이나 인덱스 등을 생성할 때 쓰이는 **데이터 정의어(DDL)**이다.

2. 요구 사항 분석

먼저 데이터베이스를 사용할 주요 사용자의 범위부터 결정해야 하며 이 작업은 요구 사항을 분석하기 전에 먼저 이루어져야 함.
사용자의 범위가 결정되면 해당 사용자가 조직에서 수행하는 업무를 분석
사용자의 업무에 관련해 필요한 데이터와 필요한 처리 등에 초점을 맞춰 요구 사항 수집 및 분석이 이루어짐
- 요구 사항 수집: 설문, 면담, 업무 관련 문서 분석 등
요구 사항 분석이 잘못된 경우, 사용자가 원치 않는 쓸모없는 데이터베이스가 개발되어 후에 수정해야 되는 문제가 생기기 때문에 중요한 과정

📋 예시) 요구 사항 명세서

3. 개념적 설계

요구 사항 분석 단계의 결과물을 개념적 데이터 모델을 이용하여 표현
개념적 데이터 모델은 사용자 요구 사항 분석 결과를 바탕으로 핵심 데이터 요소를 추출하고 데이터 요소 간 관계를 파악해 표현함.
일반적으로 개념 데이터 모델은 E-R 모델을 많이 이용함.
개념적 설계는 요구 사항 분석 결과 기반으로 현실 세계에서 중요한 데이터 요소인 개체를 추출한 후 개체 간 관계를 결정하여 E-R 다이어그램으로 표현하는 작업
사용자 요구 사항을 개념적 데이터 모델로 변환하는 작업을 개념적 모델링이라 함.
그리고 개념적 모델링의 결과물을 개념적 구조 또는 개념적 스키마라 함.
개념적 모델링 과정
개체와 속성 추출 → 관계 추출 → E-R 다이어그램 작성

3.1. 개체와 속성 추출

개체는 현실 세계에서 어떤 조직을 운영하는 데 필요한 사람, 사물과 같이 구별되는 모든 것을 의미
즉, 개체는 저장할 만한 가치가 있는 중요 데이터를 지닌 사람이나 사물 등이며, 개념적 모델링의 가장 중요한 요소
개체를 추출하는 방법은 일반적으로 요구 사항 명세서의 문장들에서 명사를 추출함.
회원 → 개체
회원아이디, 비밀번호, 이름, 나이, 직업, 등급, 적립금 → 회원의 속성

명사 선별 결과

개념적 설계의 최종 결과물을 E-R 다이어그램으로 작성해야 함
그래서 요구 사항 명세서에서 추출한 개체와 속성을 E-R 다이어그램으로 표현해 둔다.
개체: 사각형, 속성: 타원형

3.2. 관계 추출

일반적으로 관계는 요구 사항을 표현한 문장에서 동사로 표현됨
관계를 추출한 후, 추출한 관계에 대한 매핑 카디널리티와 참여 특성을 결정한다.
매핑 카디널리티는 관계를 맺고 있는 두 개체에서 각 개체 인스턴스가 관계를 맺고 있는 상대 개체의 개체 인스턴스 개수를 의미함 (일대일, 일대다, 다대다)
그리고 개체가 관계에 필수적/선택적으로 참여하는지를 의미하는 참여 특성을 결정
동사 추출 예시

주문할 수 있다. → 주문 관계/다대다 관계

관계와 관계 속성을 추출한 최종결과

3. E-R 다이어그램 작성

4. 논리적 설계

DBMS에 적합한 논리적 데이터 모델을 이용해 개념적 스키마를 기반으로 논리적 스키마를 설계
DBMS에 독립적인 개념적 스키마를 기반으로 개발에 사용할 DBMS가 처리할 수 있는 데이터베이스 논리적 구조를 설계하는 것이 논리적 설계 단계의 목표
일반적으로 관계 데이터 모델을 많이 사용하며 관계 데이터 모델을 이용해 E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마, 즉 테이블 스키마로 변환하는 작업을 수행
E-R 다이어그램을 논리적 데이터 모델로 변환하는 작업을 논리적 모델링 또는 데이터 모델링이라 함.
관계 데이터 모델은 개체와 관계를 구분하지 않고 모두 릴레이션으로 표현함
관계 데이터 모델에서는 다중 값 속성과 복합 속성의 표현을 허용하지 않음

4.1. 릴레이션 스키마 변환 규칙

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

E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환
개체의 이름을 릴레이션의 이름으로 하고 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환
개체가 가지고 있는 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환
개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환
상품(상품번호, 상품명, 재고량, 단가)

2️⃣ 규칙 2: 다대다 관계는 릴레이션으로 변환한다.

E-R 다이어그램에 있는 다대다 관계를 하나의 릴레이션으로 변환한다.
관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환
관계를 맺고 있는 개체들은 규칙 1에 따라 변환한 후 이 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 지정
그리고 외래키들을 조합하여 관계 릴레이션의 기본키로 지정
개체를 변환한 릴레이션의 기본키를 외래키로 지정할 때는 가져온 기본키들의 이름이 같은 경우 하나는 이름을 변경해야 함.

3️⃣ 규칙 3: 일대다 관계는 외래키로 표현한다.

E-R 다이어그램에 있는 일대다 관계는 릴레이션으로 변환하지 않고 외래키로만 표현함
단, 약한 개체가 참여하는 일대다 관게는 일반 개체가 참여하는 경우와 다르게 처리함

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

일반 개체들이 참여하는 일대다 관계는 릴레이션으로 변환하지 않고 외래키로만 표현
관계를 맺고 있는 개체들을 변환한 릴레이션 중, 일대다 관계의 1측 개체 릴레이션의 기본키를 가져와 N측 개체의 릴레이션에 포함시키고 외래키로 지정함.
N측 개체 릴레이션의 기본키를 가져와 1측 개체 릴레이션에 외래키로 포함시키면 다중값을 가져 릴레이션의 특성을 위반함.

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

약한 개체가 참여하는 일대다 관계도 릴레이션으로 변환하지 않고 외래키로만 표현
일반 개체들이 참여하는 일대다 관계처럼 관계를 맺고 있는 개체들을 릴레이션으로 변환함
다른 점은 외래키가 포함된 릴레이션에서 이 외래키를 포함하여 기본키를 지정해야 한다는 점
즉, N측 개체 릴레이션이 가지고 있던 키 속성과 외래키 속성을 조합하여 기본키를 구성
약한 개체는 강한 개체에 따라 존재 여부가 결정되기 때문에 강한 개체의 기본키를 이용해 식별

4️⃣ 규칙 4: 일대일 관계를 외래키로 표현한다.

E-R 다이어그램에 있는 일대일 관계도 일대다 관계처럼 릴레이션으로 변환하지 않고 외래키로만 표현
데이터 중복을 피하려면 개체가 관게에 참여하는 특성에 따라 다르게 처리해야 함

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

일반적인 일대일 관계는 외래키를 서로 주고 받는다.
관계가 가지는 속성들은 관계에 참여하는 개체를 변환한 릴레이션에 모두 포함시킨다.
사실 그림처럼 남자 릴레이션, 여자 릴레이션 모두 외래키를 가지는 건 불필요한 데이터 중복
릴레이션의 활용도에 따라 외래키와 관계의 속성을 포함시킬 릴레이션을 선택하는 방법
→ 4-2, 4-3

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

일대일 관계를 맺고 있는 두 개체 중 관계에 필수적으로 참여하는 개체의 릴레이션에만 외래키를 포함시킴
관계에 필수적으로 참여하는 릴레이션이 선택적으로 참여하는 개체의 릴레이션의 기본키를 받아 외래키로 지정
이때, 관계가 가지고 있는 속성들도 관계에 필수적으로 참여하는 릴레이션에 포함

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

일대일 관계를 맺고 있는 두 개체가 모든 관계에 필수적으로 참여한다면 관련성이 있는 개체라는 의미
그러므로 두 개체에 해당하는 두 릴레이션을 하나로 합쳐 표현함
관계의 이름을 릴레이션의 이름으로 사용하고, 관계에 참여하는 두 개체의 속성들로 관계 릴레이션에 포함

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

관계 데이터 모델의 릴레이션은 다중 값 속성을 허용하지 않음
E-R 다이어그램에 있는 다중 값 속성은 그 속성을 가지고 있는 개체에 해당하는 릴레이션이 아닌 별도의 릴레이션을 만들어 포함함
새로 만들어진 릴레이션에는 다중 값 속성 뿐만 아니라 그 속성을 가지고 있는 개체 릴레이션의 기본키를 가져와 포함시키고 이를 외래키로 지정

🎸 기타 고려 사항

사실 일대일, 일대다 관계도 릴레이션으로 변환할 수 있음
속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환하는 것을 고려할 수 있음
다만 다대다 외 관계 릴레이션 변환이 많아질 경우, 릴레이션을 관리하는 DBMS의 부담이 커짐
- 순환 관계의 경우, 상사-부하직원 관계를 맺지만 관리 관계에 참여하는 실제 개체는 사원 개체 하나일 뿐
- 사원 릴레이션의 외래키가 사원 릴레이션 내의 튜플의 기본키인 경우