[Database] 8. 데이터베이스 설계

CS(Computer Science)/Database

[Database] 8. 데이터베이스 설계

seunghwaan 2024. 12. 5. 21:28

이번 포스트에서는 데이터베이스 설계에 대해 정리하겠습니다.

데이터베이스 설계 단계

데이터베이스 설계 단계는 사용자의 다양한 요구 사항을 고려하여 데이터베이스를 생성하는 과정.

[ E-R 모델과 릴레이션 변환 규칙을 이용한 설계 ]

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

1. E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계

2. 정규화를 이용한 데이터베이스 설계 (이상 현상을 제거하면서 올바르게 설계해나가는 방법으로 다음 포스트에서 정리 예정)

아래와 같이 5단계로 진행된다.

[ 1단계: 요구사항 분석 ]

요구사항 분석 단계는 데이터베이스의 용도를 파악하고 요구사항을 수집한다.
결과물: 요구 사항 명세서

[ 2단계: 개념적 설계 ]

개념적 설계 단계는 1단계의 요구 사항 명세서를 바탕으로 DBMS에 독립적인 개념적 구조 설계
결과물: 개념적 스키마(E-R 다이어그램)

[ 3단계: 논리적 설계 ]

논리적 설계 단계는 개념적 구조를 기반으로 DBMS에 적합한 논리적 구조 설계 (E-R 다이어그램 -> 릴레이션(테이블) 스키마로 변환)
결과물: 논리적 스키마(릴레이션 스키마)

[ 4단계: 물리적 설계 ]

물리적 설계 단계는 논리적 구조를 기반으로 DBMS로 구현 가능한 물리적 구조 설계
저장 장치에 적합한 저장 레코드와 인덱스 구조 설계 및 저장된 데이터와 인덱스에 빠르게 접근할 수 있는 탐색 기법 정의
결과물: 물리적 스키마

[ 5단계: 구현 ]

구현 단계는 SQL 문을 작성한 후 이를 DBMS에서 실행하여 데이터베이스를 생성

요구 사항 분석

요구 사항 분석에서는 데이터베이스에 대한 사용자의 다양한 요구 사항을 수집하고 분석하여, 개발할 데이터베이스의 용도를 파악하여 사용자 요구 사항의 내용을 요구 사항 명세서로 작성한다.
분석 전에 데이터베이스를 사용할 주요 사용자의 범위를 정해 불필요한 요구 사항을 수집하지 않도록 한다.
범위가 결정되면 사용자가 조직에서 수행하는 업무를 분석하여 요구사항들을 수집 및 정리하고 이를 분석하여 요구사항 명세서를 작성한다.

개념적 설계

개념적 설계에서는 요구 사항 명세서를 개념적 데이터 모델(개념적 스키마)로 표현한다.
개념적 데이터 모델은 사용자 요구 사항에 대해 분석한 결과를 바탕으로 데이터베이스에 저장해둘 필요가 있다고 판단되는 데이터 요소를 추출하고 데이터 요소간의 관계를 파악하여 이를 표현한 것이다.
개념적 데이터 모델로 E-R 모델을 많이 사용한다.
개념적 모델링 -> 개념적 설계 단계에서 사용자의 요구 사항을 개념적 데이터 모델로 변환하는 작업
개념적 스키마(개념적 구조) -> E-R 다이어그램과 같이 개념적 데이터 모델로 표현된 개념적 설계의 결과물

요구 사항의 분석 결과를 E-R 모델을 이용해 개념적 모델링을 하려면 먼저 E-R 모델의 핵심 요소인 개체를 추출해야 한다. 그 다음 각 개체의 주요 속성과 키 속성을 선별하고, 개체 간의 관계를 결정해야 한다. 개체, 속성, 관계를 선별하는 작업이 모두 완료되면 그 결과를 E-R 다이어그램으로 표현한다.

[ 개체와 속성 추출 ]

개체와 속성을 추출하기 위해 제시된 요구 사항의 문장들에서 명사를 찾아 개체와 속성으로 정확히 분류하는 작업을 수행한다

요구 사항 명세서에서 개체와 개체의 속성을 추출하면 아래와 같이 추출할 수 있다.

[ 개체와 속성을 통한 E-R 다이어그램 작성 ]

관계와 속성만을 가지고 E-R 다이어그램으로 표현할 때 개체와 속성은 아래와 같이 표시한다.

개체 -> 사각형

속성 -> 타원형

키 -> 밑줄

[ 관계 추출 ]

관계는 개체 간의 의미 있는 연관성이다.
개체와 속성을 추출하기 위해 제시된 요구 사항의 문장들에서 개체 간의 연관성을 의미 있게 표현한 동사를 찾아서 관계를 추출한다.
관계를 추출한 후에 추출한 관계에 대한 매핑 카디널리티와 참여 특성을 결정한다.
매핑 카디널리티란, 관계를 맺고 있는 두 개체에서, 각 개체 인스턴스가 관계를 맺고 있는 상대 개체의 개체 인스턴스 개수를 의미한다.(일대일(1:1), 일대다(1:N), 다대다(N:N))

[ 최종 E-R 다이어그램 작성 ]

아래 E-R 다이어그램이 요구 사항 명세서를 개념적으로 모델링하여 표현한 것이다. 즉, 개념적 설계 단계의 결과물인 개념적 스키마다.

관계와 속성만을 가지고 E-R 다이어그램으로 표현할 때 관계는 아래와 같이 표시한다.

관계 -> 마름모

일대일(1:1), 일대다(1:n), 다대다(n:n) 관계 -> 선 위에 레이블로 표시

필수적으로 참여하는 개체와의 관계 -> 이중선

논리적 설계

논리적 설계는 다섯 가지 릴레이션 변환 규칙에 따라 개념적 설계를 릴레이션 스키마(논리적 스키마)로 변환한다. (논리적 모델링 or 데이터 모델링이라 한다.)
즉, 개념적 설계 단계의 E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마(테이블 스키마)로 변환하는 작업을 한다.
네트워크 데이터 모델, 계층 데이터 모델, 객체지향 데이터 모델 등 다양한 논리적 데이터 모델이 있지만, 일반적으로 관계 데이터 모델을 많이 사용한다.

[ E-R 다이어그램과 릴레이션 스키마 ]

E-R 모델에서는 개체와 관계를 구분 <-> 관계 데이터 모델에서는 개체 관계를 구분하지 않고 모두 릴레이션으로 표현한다.
E-R 모델에서는 다중 값 속성이나 복합 속성의 표현을 허용 <-> 관계 데이터 모델에서는 다중 값 속성과 복합 속성의 표현을 허용하지 않는다.

[ 릴레이션 스키마 변환 규칙 5가지 ]

E-R 다이어그램을 릴레이션 스키마로 변환하는 다섯 가지 규칙이다.
단, 다섯 가지 규칙을 적용하여 생성된 릴레이션 스키마는 완벽하지 않을 수 있고, 이상 현상이 발생할 수도 있다.
그래서 정규화를 통해 이상 현상이 발생하지 않도록 검증하는 작업을 수행하는 것이 좋다

규칙을 요약하면 아래와 같다.

규칙 1: 모든 개체는 릴레이션으로 변환한다.
규칙 2: 다대다(n:m) 관계는 릴레이션으로 변환한다.
규칙 3: 일대다(1:n) 관계는 외래키로 표현한다.
규칙 4: 일대일(1:1) 관계는 외래키로 표현한다.
규칙 5: 다중값 속성은 독립 릴레이션으로 변환한다.

* 앞으로 릴레이션 스키마 대신 간단히 릴레이션이라는 용어를 주로 사용한다.

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

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

위 그림은 E-R 다이어그램에 있는 고객 개체를 고객 릴레이션으로 변환한다.

고객번호, 이름, 등급은 그대로 고객 릴레이션의 속성으로 변환한다.
고객 개체의 키 속성인 고객번호를 고객 릴레이션의 기본키로 지정한다.
단순 속성들만 릴레이션의 속성으로 변환해야 하므로 복합 속성인 주소 속성은 우편번호, 기본주소, 상세주소 속성으로 변환한다.

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

E-R 다이어그램에 있는 다대다(n:m) 관계를 하나의 릴레이션으로 변환한다.
즉, 관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환한다.
단, 관계를 맺고 있는 개체가 무엇인지 중요하므로, 관계를 맺고 있는 개체들을 규칙 1에 따라 변환 후, 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 지정한다. 그리고 외래키들을 조합하여 관계 릴레이션의 기본키로 지정한다.
단, 기본키들의 이름이 같을 경우 하나는 이름을 변경해야 한다. 한 릴레이션에 있는 속성은 이름이 달아야 하기 때문.(번호 -> 고객번호, 상품번호)

위 그림은 E-R 다이어그램의 다대다(n:m) 관계를 릴레이션 스키마로 변환한다.

규칙 1에 따라 고객릴레이션과 상품 릴레이션으로 변환한다.
규칙 2에 따라 주문 관계를 릴레이션으로 변환한다.
고객과 상품 릴레이션의 기본키들을 가져와 외래키로 지정한다.
두 외래키를 조합하여 주문 릴레이션의 기본키로 지정한다.
필요 시, 주문번호 속성과 같은 별도의 기본키를 지정한다.

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

E-R 다이어그램에 있는 일대다(1:n) 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.
단, 약한 개체가 참여하는 일대다(1:n) 관계는 일반 개체가 참여하는 경우와 다르게 처리해야 하므로 2개의 세부 규칙으로 나누어 적용한다.

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

일반 개체들이 참여하는 일대다(1:n) 관계는 릴레이션으로 변환하지 않고 외래키로만 표현.
관계를 맺고 있는 개체들을 규칙 1에 따라 변환한 릴레이션 중에서, 일대다(1:n) 관계의 1측 개체 릴레이션의 기본키를 가져와 n측 개체 릴레이션에 포함시키고 외래키로 지정한다.
관계의 속성들도 n측 개체 릴레이션에 포함시킨다.
단, 외래키 or 관계의 속성에 포함 시킬 때 해당 릴레이션의 원래 속성과 이름이 같으면 이름을 변경한다.
만약, n측 개체 릴레이션의 기본키를 가져와 1측 개체 릴레이션에 외래키로 포함시키면 해당 외래키가 다중 값을 가지기 때문에 릴레이션의 특성을 위반하므로, 반드시 1측 개체 릴레이션의 기본키를 n측 개체 릴레이션의 외래키로 지정해야 한다.

그림 8-31 일반적인 개체가 참여하는 일대다 관계를 외래키로 표현하는 규칙을 적용한 예시.

위 E-R 다이어그램에서 제조업체와 상품 개체가 맺고 있는 공급 관계는 일대다(1:n) 관계이므로 규칙 3-1에 따라 외래키로 표현한다.
즉, 공급 관계의 1측 개체에 해당하는 제조업체 릴레이션의 기본키인 제조업체명 속성을 n측 개체에 해당하는 상품 릴레이션에 포함시키고 외래키로 지정한다.

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

약한 개체가 참여하는 일대다(1:n) 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다.
약한 개체가 참여하는 일대다(1:n) 관계처럼 관계를 맺고 있는 개체들을 규칙 1에 따라 릴레이션으로 변환하고, 이때, 일대다(1:n) 관계의 1측 개체 릴레이션의 기본키를 가져와 n측 개체 릴레이션에 포함시키고 외래키로 지정한다.
일반 개체들이 참여하는 일대다(1:n) 관계와 다른 점은, 외래키가 포함된 릴레이션에서 이 외래키를 포함하여 기본키를 지정해야 한다는 점이다. 즉, n측 개체 릴레이션이 가지고 있던 키 속성과 외래키 속성을 조합하여 기본키를 지정한다.
-> 약한 개체는 강한 개체에 따라 존재 여부가 결정되므로, 강한 개체의 기본키를 이용해서 식별하는 것이다.
-> 그러므로 강한 개체인 1측 개체 릴레이션의 기본키를 포함하여 약한 개체의 기본키를 지정한다.

약한 개체가 참여하는 일대다 관계를 외래키로 표현하는 규칙을 적용한 예시.

위 그림은 일대다(1:n) 관계의 E-R 다이어그램을 릴레이션으로 변환하는 예시이다.

E-R 다이어그램에서 비행기 개체와 좌석 개체가 맺고 있는 존재 관계는 일대다(1:n) 관계.
좌석은 비행기 개체에 의해 존재 여부가 결정되는 약한 개체이다. (비행기는 강한 개체)
-> 존재 관계의 1측 개체에 해당하는 비행기 릴레이션의 기본키인 비행기번호 속성을 n측 개체에 해당하는 좌석 릴레이션에 포함시키고 외래키로 지정한다.
-> 그리고 좌석 개체 릴레이션의 키 속성인 좌석번호와 외래키인 비행기번호를 조합하여 좌석 릴레이션의 기본키로 지정한다.

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

E-R 다이어그램에 있는 일대일(1:1) 관계도 일대다(1:n) 관계처럼 릴레이션으로 변환하지 않고 외래키로 표현한다.
단, 데이터 중복을 피하기 위해 개체가 관계에 참여하는 특성에 따라 3개의 세부 규칙으로 나누어 적용한다.

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

일반적인 일대일(1:1) 관계는 각 릴레이션에서 외래키로만 표현한다.
관계를 맺는 개체들을 규칙 1에 따라 변환한 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정하고, 관계가 가지고 있는 속성을 릴레이션에 포함시킨다.
단, 규칙 4-1은 불필요한 중복이 생성된다.

위 그림은 E-R 다이어그램에 있는 일반적인 일대일(1:1) 관계를 외래키로 표현하는 예시다.

남자 개체와 여자 개체가 맺고 있는 혼인 관계는 일대일(1:1) 관계이므로 규칙 4-1에 따라 각 릴레이션에 외래키로만 표현한다.
즉, 남자 릴레이션의 기본키인 남자번호 속성을 여자 릴레이션의 외래키로 지정, 여자 릴레이션의 기본키인 여자번호 속성을 남자 릴레이션의 외래키로 지정하고, 혼인 관계가 가지고 있는 결혼날짜 속성을 각 릴레이션에 포함한다.
단, 양쪽 릴레이션(남자 릴레이션, 여자 릴레이션)에 외래키를 모두 가지게 되어 불필요한 중복이 생겼다. (일대다(1:n) 관계처럼 한쪽 릴레이션에만 외래키를 가져도 충분.)

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

일대일 (1:) 관계를 맺고 있는 두 개체 중 관계에 필수적으로 참여하는 개체에 대응하는 릴레이션에만 외래키를 포함시킨다.
-> 즉, 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션이 선택적으로 참여하는 개체에 해당하는 릴레이션의 기본키를 받아 외래키로 지정한다. (불필요한 중복이 해결된다.)
만약, 관계에 선택적으로 참여하는 개체에 해당하는 릴레이션이 외래키를 가지면, 선택적으로 참여하기 때문에 외래키로 지정된 속성에 널 값이 지정되는 경우가 많을 수 있다.
만약, 일대일(1:1) 관계를 맺는 두 개체가 모두 선택적으로 참여하는 경우에는 외래키를 포함시킬 릴레이션을 자유롭게 선택한다.
하지만, 두 개체가 관계에 모두 필수적으로 참여해야 한다면 규칙 4-3을 적용한다.

그림 8-34. 일대일 관계에 필수적으로 참여하는 개체의 릴레이션이 외래키를 가지는 예시.

위 그림은 E-R 다이어그램에서 일대일(1:1) 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션에 외래키를 포함하는 예시다.

남자 개체는 혼인 관계에 필수적이지만, 여자 개체는 선택적으로 참여하고 있다. (원래는 그럴 수 없지만 그런 나라라고 가정한다.)
이런 경우에는 남자 릴레이션이 여자 릴레이션의 기본키인 여자번호를 외래키로 받고, 혼인 관계가 가지고 있는 결혼날짜 속성을 남자 릴레이션에 포함시킨다.
-> 불필요한 중복이 해결되었다.

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

일대일(1:1) 관계를 맺고 있는 두 개체가 모두 필수적이라면 두 릴레이션을 하나로 합쳐 표현한다.
관계의 이름을 릴레이션의 이름으로 사용하고, 관계에 참여하는 두 개체의 속성들도 관계 릴레이션에 모두 포함시킨다.
두 개체의 릴레이션의 키 속성을 조합하여 관계 릴레이션의 기본키로 지정한다.

위 그림은 E-R 다이어그램에 있는 두 개체가 일대일(1:1) 관계에 모두 필수적으로 참여하여 이를 하나의 릴레이션으로 합쳐서 표현하는 예시다.

남자 개체는 혼인 관계에 필수적이지만, 여자 개체는 필수적으로 참여한다.
남자 개체와 여자 개체를 먼저 규칙 1에 따라 남자 릴렝션과 여자 릴레이션으로 변환한다.
남자 개체와 여자 개체가 일대일(1:1)인 혼인 관계에 필수적으로 참여하므로 규칙 4-3에 따라 남자, 여자 릴레이션을 합친다.
혼인 관계를 표현하는 릴레이션이므로 합친 릴레이션의 이름은 혼인 릴레이션으로 한다.
각 릴레이션의 속성을 혼인 릴레이션에 모두 포함시킨다. 단, 이름, 나이 속성이 같으므로 속성의 이름을 변경한다.
남자 릴레이션의 기본키인 남자번호와 여자 릴레이션의 기본키인 여자번호를 조합하여 혼인 릴레이션의 기본키로 지정한다.

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

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

위 그림은 E-R 다이어그램에 있는 다중 값 속성을 릴레이션으로 변환하는 예시다.

E-R 다이어그램에 있는 사원 개체를 먼저 규칙 1에 따라 사원 릴레이션으로 변환한다.
부하직원 속성은 다중 값을 가지는 속성이므로 릴레이션에 포함시킬 수 없으므로 별도의 릴레이션을 만든다.
새로 만든 사원-부하직원 릴레이션에 다중 값 속성으로 분류한 부하직원 속성과 사원 릴레이션의 기본키인 사원 번호를 가져와 포함시키고 이를 외래키로 지정한다.
다중 값 속성으로 분류한 부하직원 속성과 외래키인 사원번호를 조합하여 사원-부하직원 릴레이션의 기본키로 지정한다.

물리적 설계와 구현

물리적 설계 단계는 필요한 인덱스 구조나 내부 저장 구조 등 물리적 구조를 설계한다.
구현 -> SQL로 작성한 명령문을 DBMS에서 실행하여 데이터베이스를 실행한다.

References

데이터베이스 개론, 김연희(2022)

저작자표시 비영리 변경금지