다음 코드는 회원 릴레이션을 실제로 생성하는 SQL 문의 작성 예다.

CREATE TABLE 회원 (
    회원아이디 VARCHAR(20) NOT NULL,
    비밀번호 VARCHAR(20) NOT NULL,
    이름 VARCHAR(10) NOT NULL,
    나이 INT,
    직업 VARCHAR(20),
    등급 VARCHAR(10) NOT NULL DEFAULT 'silver',
    적립금 INT NOT NULL DEFAULT 0,
    PRIMARY KEY(회원아이디),
    CHECK (나이 >= 0),
    CHECK (등급 in ('silver', 'gold', 'vip'))
);

 E-R 다이어그램을 릴레이션 스키마로 변환하는 일은 쉽지 않다. E-R 모델에서는 개체와 관계를 구분하지만, 관계 데이터 모델에서는 이를 구분하지 않고 모두 릴레이션으로 표현한다. 그리고 E-R 모델에서는 다중 값 속성이나 복합 속성의 표현을 허용하지만, 관계 데이터 모델에서는 이를 허용하지 않는다.

릴레이션 스키마 변환 규칙

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

 개체의 이름을 릴레이션의 이름 즉, 테이블 이름으로 하고 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환한다. 단, 개체가 가지고 있는 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환한다. 또한 개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환한다.

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

 관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환한다. 단, 관계를 맺고 있는 개체가 무엇인지 중요하므로, 관계를 맺고 있는 개체들을 규칙 1에 따라 변환한 후 이 릴레이션들의 기본키를 관계 릴레이션에 포함시키고 외래키로 지정한다. 그리고 이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정한다.

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

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

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

 관계를 맺고 있는 개체들을 규칙 1에 따라 변환한 릴레이션 중에서, 일대다 관계의 1측 개체 릴레이션의 기본키를 가져와 n측 개체 릴레이션에 포함시키고 외래키로 지정한다. 관계의 속성들도 n측 개체 릴레이션에 포함시킨다.

 만약 n측 개체 릴레이션의 기본키를 가져와 1측 개체 릴레이션에 외래키로 포함시키면 해당 외래키가 다중 값을 가져 릴레이션의 특성을 위반하게 된다.

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

 약한 개체가 참여하는 일대다 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다. 일반 개체들이 참여하는 일대다 관계처럼 관계를 맺고 있는 개체들을 규칙 1에 따라 릴레이션으로 변환한다. 이때 일대다 관계의 1측 개체 릴레이션의 기본키를 가져와 n측 개체 릴레이션에 포함시키고 외래키로 지정한다. 관계의 속성들도 n측 개체 릴레이션에 포함시킨다.

 일반 개체들이 참여하는 일대다 관계와 다른 점은, 외래키가 포함된 릴레이션에서 이 외래키를 포함하여 기본키를 지정해야 한다는 점이다. 즉, n측 개체 릴레이션이 가지고 있던 키 속성과 외래키 속성을 조합하여 기본키로 지정한다.

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

 일대일 관계도 일대다 관계처럼 릴레이션으로 변환하지 않고 외래키로만 표현한다. 이때, 데이터의 중복을 피하려면 개체가 관계에 참여하는 특성에 따라 약간 다르게 처리해야 하므로 규칙 4를 3개의 세부 규칙으로 나누어 적용한다.

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

 일반적인 일대일 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다. 관계를 맺는 개체들을 규칙 1에 따라 변환한 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정한다. 이때, 관계가 가지는 속성들은 관계에 참여하는 개체를 변환하는 릴레이션에 모두 포함시킨다.

 하지만 관계에 참여하고 있는 개체에 해당하는 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정하면 불필요한 데이터 중복이 발생하게 된다. 이를 해결하기 위해 다음 규칙을 살펴보자.

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

 일대일 관계를 맺고 있는 두 개체 중 관계에 필수적으로 참여하는 개체에 대응하는 릴레이션에만 외래키를 포함시킨다. 이때, 관계가 가지고 있는 속성들도 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션에 함께 포함시킨다.

 관계에 선택적으로 참여하는 개체에 해당하는 릴레이션이 외래키를 가져도 문제는 없지만, 관계에 선택적으로 참여하는 특성 때문에 외래키로 지정된 속성에 널 값이 저장되는 경우가 발생할 수 있다. 따라서 관계에 필수적으로 참여하는 개체의 릴레이션이 외래키를 가지는 것이 좋다.

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

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

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

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

기타 고려 사항

 기본 변환 규칙에서는 다대다 관계만 릴레이션으로 변환하였지만 일대일, 일대다 관계도 릴레이션으로 변환할 수 있다. 특히, 속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환하는 것을 고려할 수 있다.

릴레이션 스키마 변환 규칙을 이용한 논리적 설계

 논리적 설계를 위해 E-R 다이어그램을 릴레이션 스키마로 변환할 때는 변환 규칙을 순서대로 적용하면 된다.

 규칙 1은 모든 개체를 릴레이션으로 변환하는 것이다.

규칙 2는 다대다 관계를 릴레이션으로 변환하는 것이다.

 주문 릴레이션에서 회원아이디와 상품번호는 외래키이며, 이 둘을 조합하여 기본키로 사용한다.

 규칙 3은 일대다 관계를 외래키로 표현하는 것이다.

 상품 릴레이션의 제조업체명과 게시글 릴레이션의 회원아이디가 외래키이다.

 일대일 관계가 없으므로 규칙 4를 적용할 필요가 없고, 다중 값 속성도 없으므로 규칙 5도 적용할 필요가 없다.

 다섯 가지의 변환 규칙을 순서대로 적용하여 최종적으로 변환된 릴레이션 스키마에 대해 속성의 데이터 타입과 길이, 널 값 허용 여부, 기본값, 제약조건 등을 결정하는 것도 논리적 설계 단게에서 수행하는 작업이다. 이처럼 릴레이션 스키마에 대한 설계 정보를 기술한 문서를 테이블 명세서라고 한다.

개체와 속성 추출

 개체는 저장할 만한 가치가 있는 중요 데이터를 지닌 사람이나 사물 등을 의미한다. 일반적으로 개체는 명사로 표현된다. 단, 조직의 업무 처리와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외하고, 의미가 같은 명사가 여러 개면 대표 명사 하나만 선택한다. 명사 중에는 개체가 아닌 속성으로 분류되는 단어도 존재하기 때문에 이를 정확히 분류하는 작업이 필요하다.

 한빛 마트 요구 사항 명세서에서 명사를 추출하면 다음과 같다.

 한빛 마트, 회원, 회원아이디, 비밀번호, 이름, 나이, 직업, 등급, 적립금, 상품, 상품번호, 상품명, 재고량, 단가, 주문, 주문번호, 주문수량, 배송지, 주문일자, 제조업체, 공급, 공급일자, 공급량, 제조업체명, 전화번호, 위치, 담당자, 게시글, 작성, 글번호, 글제목, 글내용, 작성일자

 여기서 개체와 속성을 나눠야 하는데, 이에 앞서 한빛 마트는 이 전체를 아우르는 명사이기 때문에 제외한다. 여기서 개체는 총 네 가지로 분류할 수 있는데, 회원, 상품, 주문, 제조업체, 게시글이 개체에 해당한다. 그리고 각각의 개체에 속할 속성을 정리하면 다음 표와 같다.

 여기서 주문, 공급, 작성은 개체나 속성이 될 수 없다. 또한 주문번호, 주문수량, 배송지, 주문일자, 공급일자, 공급량은속성이 될 수 없다. 주문, 공급, 작성 같은 경우, 추출할 특정 관계의 속성으로 판단할 가능성이 높다. 그리고 주문번호, 주문수량 등과 같은 것은 주문을 해야 생기는 정보이므로 속성으로 보기 어렵다.

관계추출

 관계는 개체 간의 의미 있는 연관성이다. 일반적으로 관계는 요구 사항을 표현한 문장에서 동사로 표현된다. 단, 조직의 업무 처리와 관련하여 개체 간의 연관성을 의미 있게 표현한 동사만 선택하고, 의미가 같은 동사가 여러 개이면 대표 동사 하나만 선택한다.

 한빛 마트 요구 사항 명세서에서 명사를 추출하면 다음과 같다.

 입력해야 한다, 부여된다, 식별한다, 유지해야 한다, 식별한다, 주문할 수 있다, 유지해야 한다, 공급할 수 있다, 유지해야 한다, 유지해야 한다, 식별한다, 작성할 수 있다, 유지해야 한다, 식별한다

 여기서 개체 간의 관계를 결정 지을 수 있는 동사를 찾아야 하는데, 우선 '입력해야 한다'는 회원 개체의 속성에 대한 설명이고, '부여된다' 또한 회원 개체의 속성에 대한 설명이다. '식별한다'도 각 개체의 키 값을 설명하는 것이므로 제외한다. 이와 마찬가지로 '유지해야 한다' 또한 각 개체의 속성을 설명함으로 관계 추출에서 제외한다. 따라서 남은 동사인 '주문할 수 있다', '공급할 수 있다', '작성할 수 있다'가 개체 간의 관계를 결정할 수 있는 동사가 될 수 있다.

 첫 번째로, '주문할 수 있다' 는 회원 개체와 상품 개체가 맺는 관계를 설명하므로 주문 관계를 추출할 수 있다. 회원 한 명이 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있으므로 다대다 관계가 된다. 회원이 상품을 반드시 주문해야 하는 것은 아니므로 회원 개체는 주문 관계에 선택적으로 참여한다고 볼 수 있다. 또한 회원이 주문하지 않은 상품이 존재할 수 있으므로 상품 개체도 주문 관계에 선택적으로 참여한다고 볼 수 있다.

두 번째로, '공급할 수 있다' 는 제조업체 개체와 상품 개체가 맺는 관계를 설명하므로 공급 관계를 추출할 수 있다. 각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있으므로 일대다 관계가 된다. 상품은 제조업체가 반드시 공급해야 하므로 상품 개체는 공급 관계에 필수적으로 참여한다고 볼 수 있다. 그리고 상품을 공급하지 않는 제조업체도 존재할 수 있으므로 제조업체 개체는 공급 관계에 선택적으로 참여한다고 볼 수 있다.

세 번째로, '작성할 수 있다' 는 회원 개체와 게시글 개체가 맺는 관계를 설명하므로 작성 관계를 추출할 수 있다. 회원을 게시글을 여러 개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있으므로 일대다 관계가 된다. 회원이 반드시 게시글을 작성해야 하는 것이 아니므로 회원 개체는 작성 관계에 선택적으로 참여한다고 볼 수 있다. 하지만 게시글은 반드시 회원이 작성해야 하므로 게시글 개체는 작성 관계에 필수적으로 참여한다고 볼 수 있다.

 선별한 동사에서 관계를 추출한 최종 결과 표는 다음과 같다.

E-R 다이어그램 작성

 개체와 속성을 추출하고 개체 간의 관계를 추출하여 표현한 E-R 다이어그램은 다음과 같다.

E-R 다이어그램은 다음 프로그램을 통해 만들었음. https://app.diagrams.net/

 요구 사항 분석을 말 그대로 시스템의 데이터 요구 사항을 명확히 정의하는 것이다. 요구 사항 명세서를 만들기 위한 단계는 다음과 같다.

1. 사용자의 범위를 파악해야 한다.

2. 해당 사용자가 조직에서 수행하는 업무를 분석한다.

3. 사용자들과의 면담, 설문지 배포, 업무 관련 문서의 분석 등을 통해 데이터를 수집한다.

4. 수집된 요구 사항을 분석한 결과를 요구 사항 명세서로 문서화한다.

5. 이를 관계자와 검토한 후 수정하거나 확정 짓는다.

 다음 그림은 한빛 마트의 DB를 위한 요구 사항 명세서이다.

 다음 개념적 설계 파트에서는 이 요구 사항 명세서를 토대로 DB에 저장해둘 필요가 있다고 판단되는 데이터 요소를 추출한다. 그리고 데이터 요소 간의 관계를 파악하여 E-R 다이어그램으로 표현한다. 

E-R 모델과 릴레이션 변환 규칙을 이용한 DB 설계는 다음 그림과 같이 5단계로 진행된다. 설계 과정 중에 오류를 발견하여 변경이 필요하다면 이전 단계로 되돌아가 설계 내용을 변경할  수도 있다.

 여기서는 DB 설계 과정의 각 단계에서 수행하는 주요 작업을 가단히 알아보고, 구체적인 설계 방법은 각 절에서 실제 예와 함께 살펴보자.

1단계 : 요구 사항 분석

 시스템의 데이터 요구 사항을 수집하고 분석하여 정의한다.

2단계 : 개념적 설계

 E-R 다이어그램을 사용하여 DB의 개념적 모델을 설계한다.

3단계 : 논리적 설계

 개념적 모델을 관계형 모델로 변환하고, 정규화를 통해 논리적 구조를 설계한다.

4단계 : 물리적 설계

 논리적 모델을 실제 DB 스키마로 변환하고, 저장소 구조를 최적화한다.

5단계 : 구현 및 유지보수

 DB를 생성하고, 데이터를 로드한 후, 성능 모니터링과 유지보수를 수행한다.