Ch.8 데이터베이스 설계 [데이터베이스 개론]
- 데이터베이스 설계의 5단계를 설명하고, 각 단계에서 주요 작업은 무엇인가?
요구사항 분석 - 요구 사항 명세서
개념적 모델링 - 개념적 스키마(E-R 다이어그램)
논리적 모델링 - 논리적 스키마
물리적 모델링 - 물리적 스키마
구현 - 데이터베이스
- E-R 모델과 관계 데이터 모델의 차이는?
E-R 모델은 개체와 관계를 구분한다. 하지만 관계 데이터 모델의 개체와 관계를 구분하지 않고 모두 릴레이션으로 표현한다.
또 E-R 모델에서는 다중 값 속성과 복합 속성의 표현을 허용하는 반면, 관계 데이터 모델에선 허용하지 않는다.
데이터베이스 설계 단계
모든 조직 구성원들의 다양한 요구 사항을 만족시키는 데이터베이스 구축은 매우 어렵다. 그리고 데이터베이스는 한 번 잘못 구축되면 데이터베이스 운영 시 계속 문제를 일으켜, 결과적으로 조직에 큰 손해를 끼칠 수 있다.
데이터베이스 설계란 사용자들의 요구 사항을 고려하여 데이터베이스를 생성하는 과정이다. 사용자가 데이터베이스를 실제로 사용하면 구조를 변경하기 어렵기 때문에 설계 과정에서부터 품질 좋은 데이터베이스를 생성해야 한다. 품질 좋은 데이터베이스란 실제로 사용하는 구성원들의 요구 사항을 만족하면서 데이터의 일관성과 무결성을 유지, 사용자의 편의성까지 고려되어야 한다.
조직 구성원들의 다양한 요구 사항을 고려하여 제대로 된 데이터베이스를 구축하는 흐름은 다음과 같다.
- 요구사항 분석
- 개념적 데이터 모델링
- 논리적 데이터 모델링
- 물리적 데이터 모델링
- 구현
< 요개논물 >
해당 단계는 한 방향으로만 순서대로 진행되지는 않는다. 설계 과정 중에 변경이 필요하면 이전 단계로 되돌아가 설계 내용을 변경할 수도 있다.
1단계 : 요구사항 분석
데이터베이스의 설계의 시작은 요구 사항 분석 단계부터 시작된다. 데이터베이스를 사용해 실제 업무를 처리하는 사용자에게서 필요한 데이터의 종류와 처리 방법 같은 다양한 요구 사항을 수집한다. 그리고 수집한 요구 사항을 분석하여 그 결과를 요구 사항 명세서로 작성하는 것이 해당 단계에서 수행하는 주요 작업이다.
요구 사항 분석 단계엣 파악한 사용자의 요구 사항은 이후 설계 단계에서 중요하게 사용되고, 구축된 데이터베이스의 품질을 결정짓는 중요한 기준이 된다.
2단계 : 개념적 설계
요구 사항 명세서를 바탕으로 시작된다. 요구 사항 분석 단계에서 파악한 요구 사항을 개념적 데이터 모델을 이용해 표현한다. 개념적 데이터 모델은 개발에 사용할 DBMS의 종류에 독립적이면서, 중요한 데이터 요소와 데이터 요소 간의 관계를 표현할 때 사용한다. 일반적으로 E-R 모델을 많이 사용하는데, 이 모델은 E-R 다이어그램으로 표현한다. 따라서 E-R 모델을 데이터 모델로써 사용자의 요구 사항을 분석한 결과를 E-R 다이어그램으로 표현하는 것이 개념적 설계 단계에서 수행하는 주요 작업이다.
E-R 다이어그램처럼 개념적 데이터 모델로 표현한 결고물을 개념적 스키마라고 한다.
3단계 : 논리적 설계
개발에 사용할 DBMS에 적합한 논리적 데이터 모델을 이용하여 개념적 구조를 기반으로 논리적 구조 설계한다. DBMS 종류에 따라 다양한 논리적 데이터 모델이 존재하는데, 일반적으로 관계 데이터 모델을 많이 사용한다. 그래서 관계 데이터 모델을 사용한다면 개념적 설계 단계에서 생성한 E-R 다이어그램을 릴레이션(테이블) 스키마로 변환하여 DBMS가 처리할 수 있도록 하는 것이 주요 작업이다.
E-R 다이어그램을 릴레이션 스키마로 변환하는 작업을 논리적 모델링이라 하고, 릴레이션 스키마와 같이 논리적 데이터 모델로 표현된 결과물을 논리적 스키마라고 한다.
4단계 : 물리적 설계
논리적 설계 단계에서 생성된 논리적 구조를 기반으로 물리적 구조를 설계한다. 데이터베이스의 물리적 구조는 데이터베이스를 저장 장치에 실제로 저장하기 위한 내부 저장 구조와 접근 경로 등을 의미한다. 따라서 물리적 설계 단계에서는 저장 장치에 적합한 저장 레코드와 인덱스의 구조 등을 설계하고, 저장된 데이터와 인덱스에 빠르게 접근하게 할 수 있는 탐색 기법 등을 정의한다.
그래서 실제로 데이터베이스를 구축할 컴퓨터의 저장 장치와 운영체제의 특성을 고려하여 DBMS로 구현이 가능한 물리적인 구조를 설계하는 것이 물리적 설계 단계의 주요 작업이다.
물리적 설계의 결과물인 물리적 구조를 물리적 스키마 또는 내부 스키마라고 한다.
5단계 : 구현
구현 단계에서는 이전 설계 단계의 결과물을 기반으로 DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성한다. 이때 사용되는 SQL 문은 테이블이나 인덱스 등을 생성할 때 사용되는 데이터 정의어, 즉 DDL이다.
요구 사항 분석
데이터베이스 설계의 시작이다. 사용자들이 데이터베이스에 원하는 것이 무엇인지 분석하는 단계로써 개발할 데이터베이스의 용도를 명확히 파악하는 것이 목적이다. 분석한 사용자 요구 사항의 내용을 요구 사항 명세서로 작성하여 이후 설계 단계에서 기초 자료로 활용한다.
가장 먼저 데이터베이스를 사용할 주요 사용자의 범위를 결정해야 한다.
사용자의 범위가 결정되면 해당 사용자가 조직에서 수행하는 업무를 분석한다. 사용자의 업무와 관련해서 필요한 데이터가 무엇인지, 어떤 처리가 필요한지 요구 사항들을 수집하고 분석한다. 그 과정에서 사용자들과의 면담, 설문지 배포, 업무 관련 문서 분석 등의 방법이 사용된다. 그리고 수집된 요구 사항을 분석한 뒤 요구 사항 명세서로 문서화한다.
만약 요구 사항 분석이 잘못되면 사용자가 원치 않는 쓸모없는 데이터베이스가 개발되어 추후 비용이 발생한다. 그러므로 요구 사항 분석 단계는 어떤 단계보다도 신중하게 수행되어야 한다.
예를 들어 인터넷으로 회원들에게 상품을 판매하는 한빛 마트를 위한 데이터베이스를 개발한다고 가정했을 때 아래와 같은 요구 사항 명세서가 나왔다고 생각하자.
개념적 설계
요구 사항 분석 단계의 결과물을 개념적 데이터 모델을 이용하여 표현하는 단계이다. 그렇다면 개념적 데이터 모델이란 무엇인가? 사용자 요구 사항에 대해 분석한 결과를 바탕으로 데이터베이스에 저장해 둘 필요가 있다고 판단되는 데이터 요소를 추출하고 데이터 요소 간의 관계를 파악하여 이를 표현하는 것이다. 따라서 실제 개발에 사용할 DBMS의 종류는 중요하지 않다.
개념적 데이터 모델은 E-R 모델을 많이 사용한다. 그래서 현실 세계의 중요한 데이터 요소인 개체를 추출한 후 개체 간의 관계를 결정하여 E-R 다이어그램으로 표현한다.
개념적 설계의 큰 흐름은 다음과 같다.
가장 먼저 가장 중요한 개체를 추출한 다음, 각 개체의 주요 속성과 키 속성을 선별한다. 그리고 관계를 결정한다. 개체, 속성, 관계를 모두 선별하고 나면 그 결과를 E-R 다이어그램으로 표현한다.
개체와 속성 추출
개념적 설계 단계에서 가장 첫 단추이다. 요구 사항 분석 단계의 결과물에서 개체를 추출한다. 가장 먼저 개체부터 결정해야 속성과 관계도 결정할 수 있다.
개체란 저장할 만한 가치가 있는 중요 데이터를 지닌 사람이나 사물 등을 의미한다. 예를 들어 PC방 운영을 위한 데이터베이스를 개발한다고 가정한다면, PC방 운영에 중요한 사람인 아르바이트생, 손님 등이 있을 것이고, 중요한 사물로선 컴퓨터, 마우스, 스피커, 키보드, 라면과 같은 부식이 있겠다. 이것들이 개체가 될 수 있다.
요구 사항 명세서에서 개체를 추출하는 표준화된 방법은 없다. 다만 몇 가지 노하우가 있는데 가잔 먼저 제시된 요구 사항의 문장들에서 명사부터 찾는다. 일반적으로 개체는 문장에서 명사로 표현된다. 이때 업무 처리와 관련이 적은 명사는 제외하고 또 동일한 명사가 여러 개면 대표 명사 하나만 선택한다. 하지만 명사라고 모두 개체인 건 아니다. 명사 중에서도 속성으로 분류되는 단어도 존재하기 때문이다. 따라서 개체와 속성으로 정확히 분류하는 작업이 필요하다.
요구 사항 문장에서 명사만 선별한다.
- 한빛 마트는 DB 개발을 요청한 조직 자체이기 때문에 개체가 아니다.
- 회원은 개체이다.
- 회원아이디, 비밀번호, 이름, 나이, 직업은 중요한 데이터지만 독립적인 대상이 아니다. 회원이 가지고 있는 중요 데이터 자체로 속성이 된다.
- 등급, 적립금은 회원 개체가 가지는 속성이다.
- 회원아이디는 회원을 식별하는 역할을 한다고 했으니 회원아이디의 키 속성이다.
- 위 문장에서 추출된 명사 중 회원과 상품은 개체로 분류한다.
- 주문번호, 주문수량, 배송지, 주문일자는 회원이 상품을 주문하면 생기는 중요한 정보이기 때문에 속성으로 분류한다. 하지만 주문을 해야 생기는 정보이므로 회원이나 상품 개체에 항상 속해 있는 속성으로 보기 어렵다. 이런 속성들은 이후에 추출할 특정 관계의 속성으로 판단할 가능성이 높다.
이와 같은 작업을 거쳐 명사들만 선별한 결과 예시는 다음과 같다.
사용자가 만족하는 데이터베이스를 개발하려면 요구 사항 명세서의 요구 사항 문장에 있는 개체와 속성을 빠짐없이 추출해야 한다.
개념적 설계의 최종 결과물은 E-R 다이어그램으로 작성해야 한다.
개체는 사각형으로, 속성은 타원형으로, 키 속성은 이름에 밑줄을 그어 표시한다.
관계 추출
개체와 속성을 추출하고 나면 개체 간의 관계를 결정할 수 있다.
관계는 개체 간의 의미 있는 연관성으로 일반적으로 요구 사항을 표현한 문장에서 동사로 표현된다. 그래서 개체 간의 관계를 결정할 때는 요구 사항 문장에서 동사부터 찾아야 한다.
관계를 추출한 후에는 관계에 대해 매핑 카디널리티와 참여 특성을 결정한다.
매핑 카디널리티는 관계를 맺고 있는 두 개체에서, 각 개체 인스턴스가 관계를 맺고 있는 상대의 개체 인스턴스 개수를 의미한다. 이를 기준으로 추출한 관계를 일대일(1:1), 일대다(1:n), 다대다(n:m) 중 하나로 분류한다.
그리고 개체가 관계에 필수적으로 참여하고 있는지, 선택적으로 참여하고 있는지를 결정한다. 관계에 대한 매핑 카디널리티와 참여 특성은 이후 논리적 설계 단계에서 중요하게 활용되는 정보이므로 정확히 판단해야 한다.
의미 있는 동사를 모두 찾아보자.
- '입력해야 한다'는 회원 개체의 속성인 회원아이디, 비밀번호, 이름, 나이, 직업에 대해 설명하는 것이다. 따라서 개체와 개체의 관계를 표현한 동사가 아니다. 제외!
- '부여된다'는 회원 개체의 속성인 등급과 적립금에 대한 설명이므로 제외한다.
- '식별한다'도 회원 개체를 설명하는 것이므로 제외한다.
- '주문할 수 있다'는 회원 개체와 상품 개체가 맺는 관계를 설명한다. 이를 통해 회원 개체와 상품 개체가 맺고 있는 주문 관계를 추출할 수 있다. 그리고 회원 한 명이 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다고 했으니 주문 관계는 다대다(n:m) 관계가 된다. 또한 회원이 상품을 꼭 주문할 필요는 없으니 주문 관계에 선택적으로 참여한다고 볼 수 있다.
- '유지해야 한다'는 주문 관계의 속성을 설명하는 동사이므로 제외한다.
이와 같은 작업을 거쳐 선별한 동사와 관계의 최종 결과는 다음과 같다.
사용자가 만족하는 데이터베이스를 개발하려면 조직에서 업무를 처리하는 흐름을 정확히 파악하여 개체 간의 관계를 제대로 추출하고, 매핑 카디널리티와 참여 특성을 올바르게 결정해야 한다.
이렇게 요구 사항 명세서에서 추출한 개체, 속성, 관계를 하나의 E-R 다이어그램으로 표현한 결과는 다음과 같다. 이 E-R 다이어그램이 한빛 마트의 데이터베이스에 대한 요구 사항 명세서를 개념적으로 모델링하여 표현한 것으로써 개념적 설계 단계의 결과물인 개념적 스키마가 된다.
논리적 설계
DBMS에 적합한 논리적 데이터 모델을 이용해서 개념적 설계 단계에서 생성한 개념적 스키마를 기반으로 논리적 스키마를 설계한다. 즉 DBMS에 독립적인 개념적 스키마를 기반으로 개발에 사용할 DBMS가 처리할 수 있는 데이터베이스의 논리적 구조를 설계하는 단계이다.
DBMS의 종류에 따라 네트워크 데이터 모델, 계층 데이터 모델, 객체지향 데이터 모델 등 다양한 논리적 데이터 모델을 사용할 수 있지만, 일반적으로 관계 데이터 모델을 많이 사용한다. 그래서 관계 데이터 모델을 이용하여 E-R 다이어그램을 관계 데이터 모델의 릴레이션 스키마, 즉 테이블 스키마로 변환한다.
하지만 이 과정은 녹록치 않다. E-R 모델과 관계 데이터 모델의 표현 방식이 매우 다르기 때문에 변환 과정은 쉽지 않다.
E-R 모델에서는 개체와 관계를 구분한다. 그러나 관계 데이터 모델에서는 개체와 관계를 구분하지 않고 모두 릴레이션으로 표현한다. 또 E-R 모델에서는 다중 값 속성과 복합 속성의 표현을 허용하지만 관계 데이터 모델에서는 다중 값 속성과 복합 속성 표현을 허용하지 않는다.
E-R 다이어그램에 표현된 개체와 관계를 릴레이션으로 표현하는 규칙이 있다. 크게 5가지 규칙으로 릴레이션 스키마를 쉽게 생성할 수 있다. 물론 다섯 가지 규칙을 적용하여 생성된 릴레이션 스키마도 이상 현상이 발생할 수도 있다. 따라서 다섯 가지 규칙을 적용해 릴레이션 스키마를 설계한 후에 정규화 과정을 통해 이상 현상이 발생하지 않도록 검증하는 작업을 수행해야 한다.
릴레이션 스키마 변환 규칙
규칙 1 : 모든 개체는 릴레이션으로 변환한다
E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환한다. 개체의 이름을 릴레이션의 이름으로 하고, 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환한다.
개체가 가지고 있는 속성이 복합 속성인 경우에는 그 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환한다. 그리고 개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환한다.
고객 객체의 단순 속성인 고객번호, 이름, 등급은 그대로 고객 릴레이션의 속성으로 변환한다. 하지만 고객 개체가 주소란 복합 속성을 가지고 있기에 이를 구성하고 있는 단순 속성들로만 릴레이션의 속성으로 변환한다. 따라서 주소 속성은 사라지고 우편번호, 기본주소, 상세주소 속성만 포함된다.
규칙 2 : 다대다(n:m) 관계는 릴레이션으로 변환한다
E-R 다이어그램에 있는 다대다(n:m) 관계는 하나의 릴레이션으로 변환한다. 관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환하는 건 규칙 1과 동일하다.
관계를 맺고 있는 개체들을 릴레이션으로 변환한 후 이 릴레이션들의 기본키를 관계 릴레이션의 외래키로 지정한다. 그리고 이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정한다.
E-R 다이어그램에 있는 고객 개체와 상품 개체를 주문 릴레이션으로 변환한다. 관계 이름을 릴레이션의 이름으로 그대로 사용하여 주문 릴레이션이 되고, 주문 관계의 주문수량 속성도 주문 릴레이션으로 그대로 변환한다. 그리고 변환한 개체 릴레이션에서 기본키들을 가져와 외래키로 지정한다. 필요하다면 주문번호 속성과 같은 별도의 기본키를 지정할 수도 있다.
규칙 3 : 일대다(1:n) 관계는 외래키로 표현한다
일반적으로 일대다(1:n) 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.
그러나 약한 개체가 참여하는 일대다(1:n) 관계는 일반 개체가 참여하는 경우와 다르게 처리해야 하므로 세부 규칙으로 다시 나뉘어 적용된다.
- 규칙 3-1 : 일반적인 일대다 관계는 외래키로 표현한다
일반 개체들이 참여하는 일대다(1:n) 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.
그리고 1측 개체 릴레이션의 기본키를 가져와 n측 개체 릴레이션에 포함시키고 외래키로 지정한다. 동시에 관계의 속성들도 n측 개체 릴레이션에 포함시킨다. 쉽게 말해서 1측 개체의 기본키 + 관계의 속성들을 n측 개체 릴레이션으로 옮기는 것이다.
만약 반대로 n측 개체의 기본키를 1측 개체의 외래키로 지정한다면 1측 개체 릴레이션에서 동일한 다수의 n측 개체 기본키를 가질 수 있게 되기 때문에 릴레이션의 특성을 위반하게 된다. 그러므로 반드시 1측 개체 릴레이션의 기본키를 n측 개체 릴레이션의 외래키로 지정해야 한다.
- 규칙 3-2 : 약한 개체가 참여하는 일대다 관계는 외래키를 포함해서 기본키로 지정한다
약한 개체가 참여하는 일대다(1:n) 관계도 따로 릴레이션으로 변환하지 않고 외래키로만 표현한다. 또한 1측 개체 릴레이션의 기본키와 관계의 속성들을 n측 개체 릴레이션의 외래키 및 속성으로 가지고 오는 것도 위와 동일하다.
그러나 일반 개체들이 참여하는 일대다 관계와 다른 점이 있다. 바로 외래키가 포함된 릴레이션에서 이 외래키를 포함하여 기본키를 지정해야 한다는 점이다. 즉, n측 개체 릴레이션이 가지고 있던 키 속성과 외래키 속성을 조합하여 기본키로 지정한다.
약한 개체의 의미를 생각해보자. 약한 개체는 강한 개체의 존재 여부에 따라 결정된다. 따라서 강한 개체의 기본키를 이용해 약한 개체의 존재를 식별하는 것이다. 따라서 강한 개체인 1측 개체 릴레이션의 기본키를 포함해서 약한 개체의 기본키를 지정한다.
좌석은 비행기가 없으면 존재할 수 없다. 따라서 좌석 개체는 약한 개체가 된다. 존재 관계의 1측 개체에 해당하는 비행기 릴레이션의 기본키인 비행기번호 속성을 n측 개체에 해당하는 좌석 릴레이션에 포함시키고 외래키로 지정한다. 그런 다음 좌석 개체 릴레이션의 키 속성인 좌석번호와 외래키인 비행기번호를 조합하여 좌석 릴레이션의 기본키로 지정한다.
만약 승객이 좌석을 찾는다고 할 때 좌석번호만으로는 자신이 예약한 좌석을 찾을 수 없다. 어떤 비행기의 좌석인지까지 알아야 한다. 그러므로 강한 개체에 해당하는 비행기 릴레이션의 기본키를 포함하여 약한 개체에 해당하는 좌석 릴레이션의 기본키를 지정하는 것이 타당하다.
규칙 4 : 일대일(1:1) 관계를 외래키로 표현한다
일대일(1:1) 관계도 일대다(1:n) 관계처럼 릴레이션으로 변환하지 않고 외래키로만 표현한다. 하지만 데이터의 중복을 피하기 위해서 개체가 관계에 참여하는 특성에 따라 3개의 세부 규칙으로 나눠 적용한다.
- 규칙 4-1 : 일반적인 일대일 관계는 외래키를 서로 주고 받는다
일반적인 일대일 관계는 릴레이션으로 변환하지 않고 외래키로만 주고받는다. 즉 관계를 맺는 개체들은 서로의 기본키를 주고받아 이를 외래키로 지정한다. 이때, 관계가 가지는 속성들은 관계에 참여하는 개체를 변환한 릴레이션에 모두 포함시킨다.
위 예시에서 남자와 여자 개체는 서로 일대일 혼인 관계를 맺는다. 규칙 4-1에 따라 각자 서로의 기본키를 외래키로 가진다. 그리고 혼인 관계의 속성인 결혼날짜도 남녀 릴레이션에 모두 포함시킨다.
이 규칙에 따르면 일대일(1:1) 관계에 참여하고 있는 개체에 해당하는 릴레이션들이 서로의 기본키를 주고받아 불필요한 데이터 중복이 발생한다. 서로의 기본키를 주고받은 이유는 혼인 관계를 맺고 있는 남자와 여자를 구분하기 위함인데 혼인 관계를 표현하기 위해 남자, 여자 릴레이션이 모두 외래키를 가질 필요는 없다. 일대다 관계처럼 한쪽 릴레이션만 외래키를 가져도 관계를 표현하는 데 충분하다. 결혼날짜라는 속성도 마찬가지로 두 릴레이션에 모두 포함시킬 필요 없이 한쪽 릴레이션에만 포함시켜도 충분하다.
그렇다면 두 릴레이션 중 어떤 릴레이션에 외래키와 관계의 속성을 포함시킬까? 결론은 어떤 릴레이션을 선택하든 문제가 없다. 다만, 관계에 참여하는 특성을 고려하면 외래키와 관계의 속성을 포함시킬 릴레이션을 좀 더 쉽게 선택할 수 있다.
- 규칙 4-2 : 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다
일대일 관계를 맺고 있는 두 개체 중 관계에 필수적으로 참여하는 개체에 대응하는 릴레이션만 외래키를 포함시킨다. 즉, 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션이 선택적으로 참여하는 개체에 해당하는 릴레이션의 기본키를 받아 외래키로 지정한다. 이때, 관계의 속성도 필수적으로 참여하는 개체에 해당하는 릴레이션에 함께 포함시킨다.
물론 관계를 선택적으로 참여하는 개체에 해당하는 릴레이션이 외래키를 가져도 동작엔 문제가 없다. 다만, 관계에 선택적이다 보니 특정 튜플들은 관계를 맺지 않을 수도 있어서 외래키에 널 값이 들어갈 확률이 높다. 따라서 관계에 필수적인 개체의 릴레이션이 외래키를 갖는 게 낫다.
위 예시에선 남자 개체가 혼인 관계에 필수적으로 참여하지만, 여자 개체는 선택적으로 참여하고 있다. 남자는 무조건 결혼해야 되는 나라에서 작성될 수 있는 ERD이다. 이런 경우에는 남자 릴레이션이 여자 릴레이션의 기본키인 여자번호를 외래키로 받는다. 그리고 혼인 관계가 가지고 있는 결혼날짜 속성도 남자 릴레이션에 포함시킨다.
- 규칙 4-3 : 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션 하나로 합친다
일대일 관계를 맺고 있는 두 개체가 모두 관계에 필수적으로 참여한다면 그만큼 관련성이 높은 개체라는 의미다. 그러므로 두 개체에 해당하는 두 릴레이션을 하나로 합쳐 표현한다.
관계의 이름을 릴레이션의 이름으로 사용하고, 관계에 참여하는 두 개체의 속성들도 관계 릴레이션에 모두 포함시킨다. 그리고 두 개체 릴레이션의 키 속성을 조합하여 관계 릴레이션의 기본키로 지정한다.
남자 개체와 여자 개체는 모두 혼인 관계에 필수적으로 참여한다. 이 경우 남자와 여자 릴레이션을 하나의 릴레이션으로 합친다. 혼인 관계를 표현하는 릴레이션이므로 합친 릴레이션은 이름을 혼인 릴레이션으로 한다. 그리고 남자 릴레이션의 속성과 여자 릴레이션의 속성 모두를 포함시킨다.
마지막으로 남자 릴레이션의 기본키와 여자 릴레이션의 기본키인 여자번호를 조합하여 혼인 릴레이션의 기본키로 지정한다.
규칙 5 : 다중 값 속성은 릴레이션으로 변환한다
개념 데이터 모델과 다르게 관계 데이터 모델의 릴레이션에서는 다중 값을 가지는 속성을 허용하지 않는다. 그래서 E-R 다이어그램에 있는 다중 값 속성은 그 속성을 가지고 있는 개체에 해당하는 릴레이션이 아닌, 별도의 릴레이션을 만들어 포함시킨다.
새로 만들어진 릴레이션에는 다중 값 속성뿐 아니라, 그 속성을 가지고 있는 릴레이션의 기본키를 가져와 포함시키고 이를 외래키로 지정한다.
새로 만들어진 릴레이션의 이름은 자유이며, 기본키는 다중 값 속성과 외래키를 조합하여 지정한다.
위 E-R 다이어그램을 보면 사원 개체가 가지고 있는 부하직원 속성은 다중 값을 가지는 속성이다. 그래서 사원 릴레이션에 포함시킬 수 없어 별도의 릴레이션을 만든다. 사원-부하직원 릴레이션에 다중 값 속성으로 분류한 부하직원 속성과 사원 릴레이션의 기본키인 사원번호를 가져와 포함시키고 이를 외래키로 지정한다. 그리고 다중 값으로 분류한 부하직원 속성과 외래키인 사원번호를 조합하여 기본키로 지정한다.
다중 값을 가지는 속성은 불가능하다. 그렇다고 그림 8-38에서 제안한 부분이 완벽한 것도 아니다. 왜냐하면 불필요한 중복 데이터가 발생하기 때문이다. 그래서 다중 값 속성을 릴레이션에 포함시키려면 릴레이션을 분해할 필요가 있다.
다중 값 속성을 별도의 릴레이션에 포함시키면 불필요한 중복을 제거하면서도 다중 값을 가지는 속성을 릴레이션에 포함시킬 수 있다. 따라서 E-R 다이어그램에 있는 다중 값 속성은 규칙 5에 따라 릴레이션으로 변환하는 것이 좋다.
기타 고려 사항
앞서 언급한 기본 변환 규칙에서는 다대다 관계만 릴레이션으로 변환시켰고, 일대일, 일대다 관계는 외래키 지정으로 해결했다. 하지만 일대일, 일대다 관계도 릴레이션으로 변환할 수 있다. 특히 속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환시킬 수 있다.
위에는 일대일 관계를 외래키가 아닌 릴레이션으로 변환하는 예시이다. 혼인 관계를 릴레이션으로 만들어 결혼날짜 속성을 혼인 릴레이션의 속성으로 변환한다. 그리고 혼인 관계를 맺고 있는 두 개체에 해당하는 릴레이션들의 기본키를 가져와 포함시키고 이를 외래키로 지정한다.
이처럼 일대다, 일대일 관계도 릴레이션으로 변환해도 된다. 하지만 그럴 경우 생성되는 릴레이션의 개수가 많아져 릴레이션 관리에 대한 DBMS의 부담이 커지게 된다. 그래서 가능하면 외래키만으로도 표현이 가능한 일대일, 일대다 관계는 릴레이션으로 변환하지 않는 것이 좋다.
개체가 자기 자신과 관계를 맺는 순환 관계도 기본 규칙을 그대로 적용한다. 즉 순환 관계가 다대다 관계일 경우에는 릴레이션으로 변환하고, 일대일, 일대다 관계일 경우에는 외래키로만 표현한다.
위 E-R 다이어그램에서 사원 개체가 자기 자신과 관리 관계를 맺고 있다. 그리고 일대다 관계를 맺고 있어서 사원의 기본키인 사원번호를 외래키로 본인이 가져간다.
릴레이션 스키마 변환 규칙을 이용한 논리적 설계
논리적 설계를 위해 E-R 다이어그램을 릴레이션 스키마로 변환할 때는 변환 규칙을 순서대로 적용하면 된다. 해당되지 않는 규칙은 제외하고 다음으로 넘어간다.
E-R 다이어그램에 다섯 가지 기본 변환 규칙을 모두 적용하면 릴레이션 스키마로 변환된다. 최종적으로 변환된 릴레이션 스키마에 대해 속성의 데이터 타입과 길이, 널 값 허용 여부, 기본값, 제약조건 등을 결정하면 된다. 이 또한 논리적 설계 단계에서 수행하는 작업이다.
완료된 릴레이션 스키마에 대한 설계 정보를 기술한 문서를 테이블 명세서라고 한다.
물리적 설계와 구현
논리적 설계 단계에서 릴레이션 스키마의 설계를 완료하면, 물리적 설계 단계에서는 하드웨어나 운영체제의 특성을 고려하여 필요한 인덱스의 구조나 내부 저장 구조, 접근 경로 등에 대한 물리적인 구조를 설계한다.
그리고 마지막으로 DBMS를 이용해 SQL 문을 작성하고 이를 실행시켜서 데이터베이스를 실제로 생성하면 데이터베이스 개발이 완료된다.