이번 포스트에서는 데이터베이스 설계에 대해 정리하겠습니다.
데이터베이스 설계 단계
- 데이터베이스 설계 단계는 사용자의 다양한 요구 사항을 고려하여 데이터베이스를 생성하는 과정.
[ 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측 개체 릴레이션의 외래키로 지정해야 한다.
- 위 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을 적용한다.
위 그림은 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)