[DB] DB설계 (E-R 모델)

박시은·2023년 11월 2일

DB

목록 보기

8/8

아무리 복잡한 정보도 DB 테이블에 담을 수 있게 만들어 주는 것이 데이터 모델링이다!

▶ DB 설계 단계

관계 데이터 모델을 기반으로 DB를 설계할 때는 아래의 방법을 주로 사용한다고 한다.
① E-R 모델과 릴레이션 변환 규칙을 이용한 DB 설계
② 정규화를 이용한 DB 설계

이번 포스팅에서는 "E-R 모델과 릴레이션 변환 규칙을 이용한 DB 설계" 에 대해 알아보자!

설계 과정
- "E-R 모델과 릴레이션 변환 규칙을 이용한 DB 설계" 는 5단계로 진행된다.
- 설계 과정중에 변경이 필요할 때 이전 단계로 되돌아가기 위해, 한 방향으로만 순서대로 진행되지 않는다.

▷ 1단계 : 요구 사항 분석

목적
- 사용자의 요구사항을 수집하고 분석하여 개발할 DB의 용도 파악

결과물
- 요구사항 명세서

주요 작업
- DB를 실제 사용할 주요 사용자의 범위 결정
- 사용자가 조직에서 수행하는 업무 분석
  - 업무에 필요한 데이터가 무엇인가
  - 그 데이터에 어떤 처리가 필요한가
- 면담, 설문 조사, 업무 관련 문서 분석 등의 방법을 이용해 요구사항 수집
- 요구사항 분석 결과를 요구사항 명세서로 작성

요구 사항 명세서 예시
- 📎요구 사항 명세서 예시를 모아놓은 블로그 참고

▷ 2단계 : 개념적 설계

요구사항 명세로부터 개념적 스키마를 만들어냄 (개념적 모델링)
DB에 저장해둘 필요가 있다고 판단되는 데이터 요소를 추출하고 그들 간의 관계를 파악하여 표현
- 개체 타입, 속성, 관계 타입 식별
- 속성의 도메인 결정
- 키 속성 결정
완성된 개념적 스키마는 E-R 다이어그램으로 표현

어떻게 개념적 모델링을 수행하는지 과정을 알아보자.

① 개체와 속성 도출

개체부터 결정해야 속성과 관계도 결정할 수 있다.
- ex. 병원 운영 DB를 개발할 때 환자, 의사,병실 등이 개체가 된다.

개체 후보 풀(pool)과 개체 리스트 준비해야 한다.

나와 같은 DB 개발 경험이 적은 초보자들은 개체를 추출하기 어려울 수 있다...
- 따라서 요구 사항 문장들에서 명사를 찾아 표시하면 쉽게 개체를 찾아낼 수 있다!
  (대개 개체는 명사형으로 표현되기 때문)

하지만, 찾아낸 명사를 모두 개체로 단정하면 안된다.
- 명사 중에는 개체가 아닌 속성으로 분류되는 단어도 존재하기 때문에, 찾아낸 명사를 속성인지 개체인지 분류해야한다.
- 속성인 경우 : 개체 리스트의 포함속성에 기록
- 개체인 경우 : 개체 리스트의 개체명에 기록
- 개체 인스턴스인 경우 : 소속 개체 타입을 개체명에 기록
  - (ex: ‘영업부’, ‘개발부’ → ‘부서’)
- 개체인지 속성인지 불분명할 경우 : 개체 후보 풀에 기록

1단계 "요구 사항 분석" 의 결과물인 요구사항 명세서를 바탕으로 개체를 도출해보자!

위와 같은 식으로 개체를 도출한 다음 중복 명사, 유사한 의미의 명사는 하나로 통합한다.

개체 후보 풀에 있는 명사 검토하여 개체 리스트에 기록한다.
- 여전히 불분명하다면 풀에서 제외
- 일반적이고 광범위한 의미의 명사는 제외
- 도출된 개체에 대해 구축될 시스템에서 데이터를 관리할 필요가 있는지 재검토

그 후 개체 리스트를 작성한다

마지막으로 E-R 다이어그램으로 표현한다.

② 관계 설정

개체와 속성을 추출하고 나면 개체간의 관계를 설정할 수 있다.
일반적으로 관계는 동사로 표현된다. 따라서 요구사항 명세서에서 동사를 추출해야한다.

예시를 들어보자! (S대학교 학사 업무 시스템 관계 설정)

매핑 카티널리티를 기준으로 추출한 관계를 1:1(일대일), 1:n(일대다), n:m(다대다) 중 하나로 분류한다.
- 매핑 카디널리티 : 각 개체 인스턴스가 관계를 맺고 있는 상대 개체의 인스턴스의 개수

그리고 매핑 카디널리티와 참여도를 결정한다.
- 어떤 개체 A의 정보가 만들어지기 위해서는 다른 개체 B의 정보를 필요로 하는 관계에 있을 때
  - 개체 B는 개체 A와 관계를 가지며
  - 개체 B는 개체 A의 부모 개체가 된다.

E-R 다이어그램 그리는 법

③ 속성 설정

키 속성 선정
- 개체에 소속된 인스턴스들을 유일하게 식별할 수 있는 속성
- 현재 시점 뿐만 아니라 미래의 어떤 시점에서도 중복이 있어서는 안된다.

선정 기준
- 속성 값이 변하지 않아야 한다.
- 반드시 값이 있어야 함. (Null 값을 가질 수 없다.)
- 키 속성이 여러 개일 수 있으나 너무 많은 것은 좋지 않다.
- 마땅한 키 속성이 없을 경우: 인위적으로 속성을 생성 (설계 속성)

다음과 같은 요구사항이 추가됐다고 하자
- 교수는 학생에 대해 전공지도를 할 때 학생과의 면담 시간을 관리한다.
- 학과장이 임명된 날은 임명일자로 기록하여 관리한다.
관계 타입의 속성으로 추가한다.

▷ 3단계 : 논리적 설계

2단계 개념적 설계 단계에서 생성한 개념적 스키마를 기반으로 개발에 사용할 DBMS에 적합한 논리적 스키마를 설계한다.
개념적 스키마를 관계 데이터 모델의 릴레이션으로 매핑
- 논리적 모델링(데이터 모델링) : 개념적 스키마 → 논리적 스키마
- E-R 다이어그램 → 릴레이션 스키마
- 개체 타입과 관계 타입을 모두 릴레이션으로 매핑
규칙에 따라 단계적으로 변환한다.

단계별 매핑

아래 관계를 바탕으로 논리적 설계를 진행해보자!

1단계: 강한 개체 타입 변환

각 강한 개체 타입 E에 대해 하나의 릴레이션 R을 생성
강한 개체란?
- 누구에게도 지배되지 않는 독립적인 개체를 말한다.
- ex. 고객과 계좌 관계에서 계좌는 고객이 계좌를 만들지 않으면 개체가 생성되지 않으므로 '고객' 은 강한 개체이고, '계좌'는 약한 개체이다.

단순 속성: R에 모두 포함 (더 이상 분해할 수 없는 속성)
복합 속성: 그 복합 속성을 구성하는 단순 속성들만 R에 포함(둘 이상의 속성으로 추가 분해할 수 있는 속성)

강한 개체 타입인 E의 키 속성이 릴레이션 R의 기본키가 된다.

2단계: 약한 개체 타입 변환

E-R 스키마에서 주 개체 타입 E를 갖는 각 약한(종속) 개체 타입 W에 대하여 릴레이션 R을 생성
W에 있던 모든 단순 속성: R에 포함
주 개체 타입에 해당하는 릴레이션의 기본키: 약한 개체 타입에 해당하는 릴레이션에 외래 키로 포함 (식별 관계)
약한 개체 타입에 해당하는 릴레이션 R의 기본
키: 약한 개체 타입의 부분 키와 주 개체 타입에 해당하는 릴레이션을 참조하는 외래 키의 조합으로 이루어짐

3단계: 1:1 관계 타입 변환

4단계: 1:n 관계 타입 변환

1:N 관계 타입 R에 대하여 N측의 참여 개체 타입에 대응되는 릴레이션 S를 찾음
R에 참여하는 1측의 개체 타입에 대응되는 릴레이션 T의 기본 키를 릴레이션 S에 외래 키로 포함시킴
N측의 릴레이션 S의 기본 키를 1측의 릴레이션 T에 외래 키로 포함시키면 속성에 값들의 집합이 들어가거나 정보의 중복이 많이 발생함
관계 타입 R이 가지고 있는 모든 단순 속성(복합 속성을 갖고 있는 경우에는 복합 속성을 구성하는 단순 속성)들을 S에 해당하는 릴레이션에 포함시킴

5단계: m:n 관계 타입 변환

M:N 관계 타입 R에 대해서는 릴레이션 R을 생성함
참여 개체 타입에 해당하는 릴레이션들의기본 키를 릴레이션 R에 외래 키로 포함시키고, 이들의 조합이 릴레이션 R의 기본키가 됨
관계 타입 R이 가지고 있는 모든 단순 속성(복합 속성을 갖고 있는 경우에는 복합 속성을 구성하는 단순 속성)들을 릴레이션R에 포함시킴

6단계: 3진 이상의 관계 타입 변환

3진 이상의 각 관계 타입 R에 대하여 릴레이션 R을 생성함
관계 타입 R에 참여하는 모든 개체 타입에 대응되는 릴레이션들의 기본 키를 릴레이션 R에 외래 키로 포함시킴
관계 타입 R이 가지고 있는 모든 단순 속상(복합 속성을 갖고있는 경우에는 복합 속성을 구성하는 단순 속성)들을 릴레이션 R에 포함시킴
일반적으로 외래 키들의 조합이 릴레이션 R의 기본 키가 됨
관계 타입 R에 참여하는 개체 타입들의 카디널리티가 1:N:N이면 카디널리티가 1인 릴레이션의 기본 키를 참조하는 외래키를 제외한 나머지 외래 키들의 모임이 릴레이션 R의 기본키가 됨