[DB 이론] 데이터베이스 설계

이 글은 데이터베이스 개론 (저자 김연희)의 내용을 개인적으로 정리하는 글임을 알립니다.

---

데이터데이스 설계 단계

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

사용자가 데이터베이스를 실제로 사용하면 구조를 변경하기 어렵기 때문에 설계 과정에서부터 품질 좋은 데이터베이스를 생성해야 한다.

품질 좋은 데이터베이스를 평가하는 기준은 실제로 사용하는 구성원들의 요구사항을 만족하는지가 대표적인 기준이 된다.

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

• E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계
• 정규화를 이용한 데이터베이스 설계

이 글에서는 E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계를 다룬다.

E-R 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계는 아래와 같은 5단계로 진행된다.

하지만 그림처럼 한 방향으로만 순서대로 진행되지는 않는다.

설계 과정 중에 오류를 발견하여 변경이 필요하면 이전 단계로 되돌아가 설계 내용을 변경할 수도 있다.

 

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 다이어그램으로 표현하는 것이다.

• 개념적 모델링 : 사용자의 요구 사항을 개념적 데이터 모델로 변환하는 작업
• 개념적 스키마(개념적 구조) : ERD와 같이 개념적 데이터 모델로 표현된 개념적 설계의 결과물

E-R 모델을 이용해 개념적 모델링을 하려면 먼저 E-R 모델의 핵심 요소인 개체를 추출하고 그다음 각 개체의 주요 속성과 키 속성을 선별하고, 개체 간의 관계를 결정해야 한다.

개체, 속성, 관계를 선별하는 작업이 모두 완료되면 그 결과를 ERD로 표현한다.

 

개체와 속성 추출

개체는 현실 세계에서 어떤 조직을 운영하는 데 꼭 필요한 사람, 사물과 같이 구별되는 모든 것을 의미한다.

요구 사항 명세서에서 개체를 추출할 때는, 먼저 명세서의 명사를 추출한다.

단, 조직의 업무 처리와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외한다.

위 그림에서의 파란색 글씨로 된 것들이 명사이다.(중복된 명사는 제외)

추출된 모든 명사가 개체가 되는 것은 아니다.

명사 중에서 개체와 속성으로 분류되는 단어도 존재하기 때문에, 추출한 명사를 개체와 속성으로 정확히 분류하는 작업이 필요하다.

 

이렇게 개체와 속성을 분류하였으면 ERD로 나타내기 위해 도형으로 변환한다.

속성에 밑줄이 그어져 있다면, 그 속성은 해당 개체의 키 속성을 의미한다.

 

관계 추출

개체와 속성을 추출하고 나면 개체 간의 관계를 결정할 수 있다.

관계는 개체 간의 의미 있는 연관성이다.

일반적으로 관계는 요구 사항을 표현한 문장에서 동사로 표현한다.

단, 조직의 업무 처리와 관련하여 개체 간의 연관성을 의미 있게 표현한 동사만 선택하고, 의미가 같은 동사가 여러 개이면 대표 동사 하나만 선택한다.

 

'주문할 수 있다'는 회원 개체와 상품 개체가 맺는 관계를 설명하므로 이를 통해 회원 개체와 상품 개체가 맺고 있는 주문 관계를 추출할 수 있다.

회원 한 명이 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다고 했으므로 회원 개체와 상품 개체가 맺는 주문 관계는 다대다(n:m) 관계가 된다.

회원이 상품을 반드시 주문해야 하는 것은 아니므로 회원 개체는 주문 관계에 있어서 선택적으로 참여한다고 볼 수 있다.

그리고 회원이 주문 하지 않은 상품이 존재할 수 있으므로 상품 개체도 주문 관계에 선택적으로 참여한다고 볼 수 있다.

 

'공급할 수 있다'는 상품 개체와 제조업체 개체가 맺는 관계를 설명하므로 이를 통해 상품 개체와 제조업체 개체가 맺고 있는 공급 관계를 추출할 수 있다.

하나의 상품은 제조업체 하나가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다고 했으므로 제조업체 개체와 상품 개체가 맺는 공급 관계는 일대다(1:n)가 된다.

제조업체가 상품을 공급하면 상품은 무조건 존재하므로 상품 개체는 공급 관계에 필수적으로 참여한다고 볼 수 있다.

그리고 상품을 공급하지 않는 제조업체도 존재할 수 있으므로 제조업체 개체는 공급 관계에 선택적으로 참여한다고 볼 수 있다.

 

'작성할 수 있다'는 회원 개체와 개시글 개체가 맺는 관계를 설명하므로 이를 통해 회원 개체와 개시글 개체가 맺고 있는 작성 관계를 추출할 수 있다.

회원 한 명이 게시글을 여러 개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다고 했으므로 회원 개체와 게시글 개체가 맺는 작성 관계는 일대다(1:n)가 된다.

회원이 게시글을 반드시 작성해야 하는 것은 아니므로 회원 개체는 작성 관계에 선택적으로 참여한다고 볼 수 있다.

그리고 게시글은 회원이 작성하면 무조건 존재 하므로 게시글 개체는 작성 관계에 필수적으로 참여한다고 볼 수 있다.

 

ERD에서 관계는 마름모로 표현하고, 사각형으로 표현된 개체와 선으로 연결한다.

그리고 일대일, 일대다, 다대다 관계는 선 위에 레이블로 표시한다.

필수적으로 참여하는 개체는 개체와 관계를 이중선으로 연결한다.

 

ERD 작성

한빛 마트의 데이터베이스에 대한 요구 사항 명세서에 추출한 개체, 속성, 관계를 하나의 ERD로 표현한 결과는 아래와 같다.

즉, 개념적 설계 단계의 결과물인 개념적 스키마다.

 

논리적 설계

논리적 설계 단계에서는 DBMS에 적합한 논리적 데이터 모델을 이용해서, 개념적 설계 단계에서 생성한 개념적 스키마를 기반으로 논리적 스키마를 설계한다.

즉, DBMS에 독립적인 개념적 스키마를 기반으로 하여 개발에 사용할 DBMS가 처리할 수 있는 데이터베이스의 논리적 구조를 설계하는 것이 논리적 설계 단계의 목표다.

일반적으로 모델은 관계 데이터 모델을 많이 사용한다.

ERD를 관계 데이터 모델의 릴레이션 스키마, 즉 테이블 스키마로 변환하는 작업을 한다.

• 논리적 모델링 : 논리적 설계 단계에서 E-R 다이어그램을 릴레이션 스키마로 변환하는 작업
• 논리적 구조 또는 논리적 스키마  : 릴레이션 스키마와 같이 논리적 데이터 모델로 표현된 결과물

 

릴레이션 스키마 변환 규칙

릴레이션 스키마 대신 간단히 릴레이션이라는 용어를 주로 사용하겠다.

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

개체의 이름을 릴레이션의 이름으로 하고, 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환한다.

단, 개체가 가지고 있는 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 속성으로 변환한다.

개체가 가지고 있는 키 속성은 릴레이션의 기본키로 변환한다.

 

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

ERD에 있는 다대다 관계를 하나의 릴레이션으로 변환한다.

관계의 이름을 릴레이션의 이름으로 하고, 관계의 속성도 릴레이션의 속성으로 그대로 변환한다.

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

그리고 이 외래키들을 조합하여 관계 릴레이션의 기본키로 지정한다.

외래키로 지정할 때는 가져온 기본키들의 이름이 같을 경우 하나는 이름을 변경해야 한다. 한 릴레이션에 있는 속성은 이름이 모두 달라야 하기 때문이다.

하지만 속성의 이름만 달라질 뿐 속성의 도메인은 변하지 않으므로 외래키로 사용하는 데 문제가 발생하지 않는다.

 

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

ERD에 있는 일대다 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

단, 약한 개체가 참여하는 일대다 관계는 일반 개체가 참여하는 경우와 다르게 처리해야 하므로 규칙 3을 아래와 같이 세부 규칙으로 나누어 적용한다.

 

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

일반 개체들이 참여하는 일대다 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

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

관계의 속성들도 n측 개체의 릴레이션에 포함시킨다.

단, 외래키나 관계의 속성을 포함시킬 때 해당 릴레이션의 원래 속성과 이름이 같으면 이름을 변경해야 한다.

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

그러므로 반드시 1측 개체 릴레이션의 기본키를 n 측 개체 릴레이션의 외래키로 지정해야 한다.

 

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

약한 개체가 참여하는 일대다 관계도 릴레이션으로 변환하지 않고 외래키로만 표현한다.

이때, 일대다 관계의 1측 개체 릴레이션의 기본키를 가져와 n 측 개체 릴레이션에 포함시키고 외래키로 지정한다.

관계의 속성들도 n측 개체 릴레이션에 포함시킨다.

일반 개체들이 참여하는 일대다 관계와 다른 점은, 외래키가 포함된 릴레이션에서 이 외래키를 포함하여 기본키를 지정해야 한다는 점이다.

즉, n측 개체 릴레이션이 가지고 있던 키 속성과 외래키 속성을 조합하여 기본키로 지정한다.

약한 개체는 강한 개체에 따라 존재 여부가 결정되는 만큼 강한 개체의 기본키를 이용해 식별하는 것이다.

그러므로 강한 개체인 1측 개체의 릴레이션의 기본키를 포함해서 약한 개체의 기본키를 지정한다.

 

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

ERD에 있는 일대일 관계도 일대다 관계처럼 릴레이션으로 변환하지 않고 외래키로만 표현한다.

이때, 데이터의 중복을 피하려면 개체가 관계에 참여하는 특성에 따라 약간 다르게 처리해야 하므로 규칙 4를 아래와 같이 3개의 세부 규칙으로 나누어 적용한다.

 

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

일반적인 일대일 관계는 릴레이션으로 변환하지 않고 외래키로만 표현한다.

관계를 맺는 개체들을 규칙 1에 따라 변환한 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정한다.

이때, 관계가 가지는 속성들은 관계에 참여하는 개체를 변환한 릴레이션에 모두 포함시킨다.

 

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

일대일 관계를 맺고 있는 두 개체 중 관계에 필수적으로 참여하는 개체에 대응하는 릴레이션에만 외래키를 포함시킨다.

즉, 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션이 선택적으로 참여하는 개체에 해당하는 릴레이션의 기본키를 받아 외래키로 지정한다.

이때, 관계가 가지고 있는 속성들도 관계에 필수적으로 참여하는 개체에 해당하는 릴레이션에 함께 포함시킨다.

관계에 선택적으로 참여하는 개체에 해당하는 릴레이션이 외래키를 가지면 관계를 표현하는데 문제는 없지만 관계에 선택적으로 참여하기 때문에 릴레이션이 실제로 구축되고 난 후에 외래키로 지정된 속성에는 널 값이 저장되는 경우가 많을 것이다.

그러므로 관계에 반드시 참여하는 개체에 대응하는 릴레이션이 외래키를 가지도록 하는 것이 좋다.

위 그림은 남자는 꼭 결혼해야 하고 여자는 결혼하지 않아도 되는 법이 있는 나라에서 작성될 수 있는 ERD이다.

 

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

일대일 관계를 맺고 있는 두 개체가 모두 관계에 필수적으로 참여한다면 그만큼 관련성이 있는 개체라는 의미다.

그러므로 두 개체에 해당하는 두 릴레이션을 하나로 합쳐 표현한다.

관계의 이름을 릴레이션의 이름으로 사용하고, 관계에 참여하는 두 개체의 속성들도 관계 릴레이션에 모두 포함시킨다.

그리고 두 개체 릴레이션의 키 속성을 조합하여 관계 릴레이션의 기본키로 지정한다.

 

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

관계 데이터 모델의 릴레이션에서는 다중 값을 가지는 속성을 허용하지 않는다.

그러므로 ERD에 있는 다중 값 속성은 그 속성을 가지고 있는 개체에 해당하는 릴레이션이 아닌 별도의 릴레이션을 만들어 포함시킨다.

새로 만들어진 릴레이션에는 ERD에서 다중 값 속성으로 표현된 속성뿐 아니라 그 속성을 가지고 있는 개체에 해당하는 릴레이션의 기본키를 가져와 포함시키고 이를 외래키로 지정한다.

새로 만들어진 릴레이션의 이름은 자유롭게 정하고, 기본키는 다중 값 속성과 외래키를 조합하여 지정한다.

 

기타 고려 사항

기본 변환 규칙에서는 다대다 관계만 릴레이션으로 변환하였지만 일대일, 일대다 관계도 릴레이션으로 변환할 수 있다.

특히, 속성이 많은 관계는 관계 유형에 상관없이 릴레이션으로 변환하는 것을 고려할 수 있다.

일대일 관계를 릴레이션으로 변환하는 예

 

순환 관계를 변환하는 예

 

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

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

해당되지 않는 규칙은 제외하고 다음으로 넘어간다.

 

먼저 규칙 1에 따라 4개의 개체를 개별 릴레이션으로 변환한다.

 

규칙 1을 적용한 결과에 규칙 2를 적용한다.

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

규칙 2에 따라 회원 개체와 상품 개체가 참여하는 주문 관계를 릴레이션으로 변환해야 한다.

 

이제 규칙 2를 적용한 결과에 계속해서 규칙 3을 적용해야 한다.

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

한 빛 마트의 ERD에는 일대다 관계인 공급 관계와 작성 관계가 존재한다.

주문 릴레이션이 새로 추가된 것을 확인할 수 있다.

 

이제 규칙 3까지 적용한 결과에 규칙 4와 규칙 5를 적용할 차례다.

그런데 한빛 마트의 ERD에는 일대일 관계가 없으므로 규칙 4를 적용할 필요가 없다.

그리고 다중 값 속성도 없으므로 규칙 5도 적용할 필요가 없다.

 

그러므로 한빛 마트 ERD는 논리적 모델링 과정을 통해 5개의 릴레이션 스키마로 변환된다.

릴레이션 스키마에 대해 속성의 데이터 타입과 길이, 널 값 허용 여부, 기본값, 제약조건 등을 결정하는 것도 논리적 설계 단계에 수행하는 작업이다.

DBMS를 MySQL로 정했다는 가정

 

물리적 설계와 구현

• 물리적 설계 단계: 저장 장치에 적합한 저장 레코드와 인덱스의 구조 등을 설계하고, 저장된 데이터와 인덱스에 빠르게 접근하게 할 수 있는 탐색 기법 등을 정의
• 구현 단계 : DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성