Database - # 08

 

8.1 데이터베이스 설계 단계

데이터베이스 설계

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

DB 기본 요건 - 요구 사항 만족, 일관성과 무결성 유지, 이해 쉽고 접근 편리

관계 데이터베이스의 대표적 설계 방법 - E-R 모델과 릴레이션 변환 규칙을 이용한 설계 / 정규화를 이용한 설계

E-R 모델과 리레이션 변환 규칙을 이용한 설계 과정

2단계 - 개념적 데이터 모델링

3단계 - 논리적 데이터 모델링

 

8.2 요구 사항 분석

설계 1단계 - 요구 사항 분석

목적

사용자의 요구 사항을 수집, 분석 -> 개발할 DB 용도 파악 (필요한 데이터, 필요한 처리 등)

결과물

요구 사항 명세서

주요 작업

DB를 실제로 사용할 주요 사용자 범위 결정

사용자가 조직에서 수행하는 업무 분석

면담, 설문 조사, 업무 관련 문서 분석 등 방법 이용 -> 요구 사항 수집

수집된 요구 사항에 대한 분석 결과 -> 요구 사항 명세서로 작성

 

요구 사항 분석의 예 [한빛 마트 DB]

인터넷으로 회원들에게 상품을 판매하는 한빛 마트의 DB 개발

1. 한빛 마트에 회원으로 가입하려면 회원아이디, 비밀번호, 이름, 나이, 직업을 입력해야 한다. 2. 가입한 회원에게는 등급과 적립금이 부여된다. 3. 회원은 회원아이디로 식별한다. 4. 상품에 대한 상품번호, 상품명, 재고량, 단가 정보를 유지해야 한다. 5. 상품은 상품번호로 식별한다. 6. 회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다. 7. 회원이 상품을 주문하면 주문에 대한 주문번호, 주문수량, 배송지, 주문일자 정보를 유지해야 한다. 8. 각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다. 9. 제조업체가 상품을 공급하면 공급일자와 공급량 정보를 유지해야 한다. 10. 제조업체에 대한 제조업체명, 전화번호, 위치, 담당자 정보를 유지해야 한다. 11. 제조업체는 제조업체명으로 식별한다. 12. 회원은 게시글을 여러 개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다. 13. 게시글에 대한 글번호, 글제목, 글내용, 작성일자 정보를 유지해야 한다. 14. 게시글은 글번호로 식별한다

 

8.3 개념적 설계

설계 2단계 - 개념적 설계 (개념적 데이터 모델링)

목적

DBMS에 독립적인 개념적 구조 설계

요구 사항 분석 결과물을 개념적 데이터 모델을 이용해 개념적 구조로 표현

일반적으로 E-R 모델을 많이 이용

결과물

개념적 구조: E-R 다이어그램

주요 작업

요구 사항 분석 결과를 기반으로 중요한 개체를 추출하고 개체 간의 관계를 결정 -> E-R 다이어그램 표현

작업 과정

1. 개체 추출, 각 개체의 주요 속성, 키 속성 선별
2. 개체 간의 관계 결정
3. E-R 다이어그램으로 표현

개념적 모델링 과정

 

개념적 설계 - [STEP 1] 개체와 속성 추출

개체: 저장할 만한 가치가 있는 중요 데이터를 가진 사람이나 사물 등

ex) 병원 DB 개발에 필요한 개체

• 병원 운영에 필요한 사람: 환자, 의사, 간호사 등
• 병원 운영에 필요한 사물: 병실, 수술실, 의료 장비 등

개체 추출 방법

요구 사항 문장에서 업무와 관련이 깊은 의미 있는 명사를 찾기

• 업무와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외
• 의미가 같은 명사가 여러 개일 경우 - 대표 명사 하나만 선택

  -> 찾아낸 명사를 개채와 속성으로 분류

 

요구 사항 명세서에서 개체와 속성을 추출하는 과정 - 요구 사항 문장에서 명사를 선별

“한빛 마트”는 일반적이고 광범위한 의미의 명사이므로 제외

“회원아이디”, “비밀번호”, “이름”, “나이”, “직업”, “등급”, “적립금”은 회원의 속성으로 분류

“회원아이디”는 키 속성으로 분류

 

요구 사항 명세서에서 개체와 속성을 추출하는 과정 - 요구 사항 문장에서 속성을 추출

[추출 결과]
개체: 회원

"회원" 개체의 속성: 회원아이디, 비밀번호, 이름, 나이, 직업

"회원" 개체의 키 속성: 회원아이디

 

요구 사항 명세서에서 개체와 속성을 추출하는 과정 - 요구 사항 문장에서 개체와 속성을 추출

[추출 결과]

개체 : 회원, 상품
속성 : 주문번호, 주문수량, 배송지, 주문일자
회원이 상품을 주문을 해야 생기는 중요한 정보이기 때문에
회원이나 상품 개체의 속성으로 보기는 어렵고
이후 추출할 특정 관계의 속성일 가능성이 높음

개념적 설계의 최종 결과물은 E-R 다이어그램으로 작성해야 하므로,

요구 사항 명세서에서 추출한 개체와 속성을 E-R 다이어그램으로 작성

 

개념적 설계 - [STEP 2] 관계 추출

관계: 개체 간의 의미 있는 연관성

관계 추출 방법

• 요구 사항 문장에서 개체 간의 연관성을 의미 있게 표현을 동사 찾기 - 의미가 같은 동사가 여러 개의 경우는 대표 동사 하나만 선택
• 찾아낸 관계에 대해 매핑 카디널리티와 참여 특성을 결정
• 매핑 카디널리티: 일대일(1:1), 일대다(1:n), 다대다(n:m) 두 개체에서, 각 개체 인스턴스가 관계를 맺고 있는 상대 개체의 개체 인스턴스 개수를 의미  – 참여 특성: 필수적 참여 / 선택적 참여

요구 사항 명세서에서 관계를 추출하는 과정 - 요구 사항 문장에서 동사를 선별

“입력해야 한다”는 개체와 개체의 관계를 표현하는 동사로 볼 수 없으므로 제외

“부여된다”는 개체와 개체의 관계를 표현하는 동사로 볼 수 없으므로 제외

“식별한다”는 개체와 개체의 관계를 표현하는 동사로 볼 수 없으므로 제외

[추출 결과]
관계: 주문

“회원” 개체와 “상품” 개체가 맺는 관계, 다대다(n:m) 관계

“회원” 개체는 관계에 선택적으로 참여 / “상품” 개체는 관계에 선택적으로 참여
=> 부분 참여

“주문” 관계의 속성: 주문번호, 주문수량, 배송지, 주문일자

[추출 결과]
관계: 공급

“제조업체” 개체와 “상품” 개체가 맺는 관계, 일대다(1:n) 관계

“제조업체” 개체는 관계에 선택적으로 참여 / “상품” 개체는 관계에 필수적으로 참여
=> 선택적(부분) 참여, 필수적(전체) 참여

“공급” 관계의 속성: 공급일자, 공급량

[추출 결과]

관계: 작성

“회원” 개체와 “게시글” 개체가 맺는 관계, 일대다(1:n) 관계

“회원” 개체는 관계에 선택적으로 참여 / “게시글” 개체는 관계에 필수적으로 참여

주문 관계의 E-R 다이어그램

 

공급 관계의 E-R 다이어그램

작성 관계의 E-R 다이어그램

 

개념적 설계 – [STEP 3] E-R 다이어그램 작성

 

8.4 논리적 설계

설계 3단계 - 논리적 설계 (논리적 데이터 모델링)

목적

DBMS에 적합한 논리적 구조 설계

개념적 구조를 논리적 데이터 모델을 이용해 논리적 구조로 표현

결과물

논리적 구조: 릴레이션 스키마, 테이블 명세서

주요 작업

개념적 설계 단계의 결과물인 E-R 다이어그램을 릴레이션 스키마로 변환

릴레이션 스키마로 변환 후 속성의 데이터 타입, 길이, 널 값 허용, 기본 값, 제약조건 등을 세부적으로 결정하고 결과를 문서화

 

E-R 다이어그램 vs 릴레이션 스키마 (차이점)

E-R 모델	관계 데이터 모델
개체, 관계 구분	개체, 관계 구분 x / 모두 릴레이션으로 표현
다중 값 속성, 복합 속성 허용	다중 값 속성, 복합 속성 허용 x

 E-R 다이어그램 -> 관계 데이터 모델의 릴레이션 스키마 변환 시 고려사항 많음

 

E-R 다이어그램을 릴레이션 스키마로 변환하는 규칙 5가지

[규칙 1] 모든 개체 -> 릴레이션 (변환)

E-R 다이어그램의 각 개체 -> 하나의 릴레이션 (변환)

• 개체 이름 -> 릴레이션 이름
• 개체 속성 -> 릴레이션 속성
• 개체 키 속성 -> 릴레이션 기본키
• if) 개체 속성=복합 속성 -> 복합 속성 구성하는 단순 속성 -> 릴레이션 속성

개체 -> 릴레이션 변환

복합 속성 가지는 개체 -> 릴레이션

 

[규칙 2] 다대다(n:m) -> 릴레이션 (변환)

E-R 다이어그램 다대다 관계 -> 하나의 릴레이션 (변환)

• 관계 이름 -> 릴레이션 이름
• 관계 속성 -> 릴레이션 속성
• 관계 참여 개체 -> 릴레이션 (규칙 1에 따라 변환)

     - 변환한 개체 릴레이션에서 기본키들 -> 관계 릴레이션에서 외래키로 지정

     - 외래키들 조합 -> 관계 릴레이션 기본키로 지정

 

[규칙 3] 일대다(1:m) -> 외래키 (표현)

E-R 다이어그램의 일대다 관계 -> 외래키로만 표현

 

  (규칙 3-1) 일반적인 일대다 관계 -> 외래키로 표현

• 관계를 맺고 있는 개체들을 규칙 1에 따라 변환한 릴레이션 중에서,
• 일대다(1:n) 관계에서 1측 개체 릴레이션의 기본키를 n측 개체 릴레이션에 포함시켜 외래키로 지정
• 관계의 속성들도 n측 개체 릴레이션에 포함시킴
• 단, 외래키나 관계의 속성을 포함시킬 때 해당 릴레이션의 원래 개체 속성과 이름이 같으면 이름을 변경해야 함

 

  (규칙 3-2) 약한개체가 참여하는 일대다 관계 -> 외래키로 표현 후 기본키로 지정

• 관계를 맺고 있는 개체들을 규칙 1에 따라 릴레이션으로 변환
• 일대다(1:n) 관계에서 1측 개체 릴레이션의 기본키를 n측 개체 릴레이션에 포함시켜 외래키로 지정
• 관계의 속성들도 n측 개체 릴레이션에 포함시킴
• 약한 개체를 포함한 n측 개체 릴레이션은 외래키를 포함하여 기본키를 지정함                                                                 -> 약한 개체는 강한 개체에 따라 존재 여부가 결정되므로 강한 개체의 기본키를 이용해 식별해야 함

 

[규칙 4] 일대일(1:1) 관계 -> 외래키 (표현)

E-R 다이어그램의 일대일 관계 -> 외래키로만 표현  

 

 (규칙 4-1) 일반적인 일대일 관계 -> 외래키를 서로 주고 받음

• 관계를 맺는 개체들을 규칙 1에 따라 변환한 릴레이션들이 서로의 기본키를 주고받아 이를 외래키로 지정
• 관계의 속성들은 모든 개체 릴레이션에 포함시킴
• 불필요한 데이터 중복이 발생할 수 있음

 

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

• 관계에 필수적으로 참여하는 개체의 릴레이션이 선택적으로 참여하는 개체의 릴레이션의 기본키 받아 외래키로 지정    -> 선택적으로 참여하는 릴레이션에 외래키로 지정시 널 값이 저장되는 경우가 많을 것임
• 관계의 속성들은 관계에 필수적으로 참여하는 개체 릴레이션에 포함시킴

 

 (규칙 4-3) 모든 개체가 일대일 관계에 필수적으로 참여 -> 릴레이션 하나로 합침

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

 

[규칙 5] 다중 값 속성 -> 릴레이션 (변환)

E-R 다이어그램의 다중 값 속성 -> 독립적인 릴레이션 (변환)

• 관계 데이터 모델의 릴레이션에서는 다중 값을 가지는 속성을 허용하지 않음으로, 별도 릴레이션을 만들어 포함
• 새로 만들어진 릴레이션에는 다중 값 속성과 함께 그 속성을 가지고 있던 개체 릴레이션의 기본키를 외래키로 포함
• 새로운 릴레이션의 기본키는 다중 값 속성과 외래키를 조합하여 지정하고, 릴레이션의 이름은 자유롭게 정함

다중 값 속성 -> 릴레이션 (변환) 규칙 적용

 

다중 값 속성인 부하직원 속성이 사원 릴레이션에 그래로 포함

 -> 사원 릴레이션은 "속성에 다중 값을 저장할 수 없다"는 릴레이션 특성 위반

 

사원 릴레이션을 릴레이션의 특성에 맞게 구성하기 위하여 부하직원 속성에 단 하나의 값만 저장한 경우

사원 릴레이션은 릴레이션 특성을 위반하지는 않지만

사원번호, 사원명, 직위 속성의 값이 불필요하게 중복 저장되는 문제가 발생함

 -> 다중 값 속성을 릴레이션에 포함시키려면 규칙 5를 적용해서 릴레이션을 분해함

 

규칙 5를 적용하여 다중 값 속성 -> 독립적인 릴레이션 (변환)

- 불필요한 중복을 제거하면서도 다중 값을 가지는 속성을 릴레이션에 포함시킬 수 있음

규칙 5를 적용한 후의 사원 릴레이션과 사원-부하직원 릴레이션

 

기타 고려 사항

일대일(1:1), 일대다(1:n) 관계도 독립적인 릴레이션으로 변환가능

 - 속성이 많은 관계는 유형에 상관없이 릴레이션으로의 변환을 고려할 수 있음

 

개체가 자기 자신과 관계를 맺는 순환 관계도 기본 규칙을 그대로 적용

 – 순환 관계가 다대다 관계일 경우 릴레이션으로 변환하고
 – 일대일 이나 일대다 관계일 경우 외래키로 표현

 

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

[그림 8-26] 한빛 마트의 E-R 다이어그램을 릴레이션으로 변환하는 과정

 STEP 1) 규칙 1 적용 / 적용 결과: 4개체의 릴레이션

 

 STEP 2) 규칙 2 적용 / 적용 결과: 주문 관계 -> 주문 릴레이션

 

 STEP 3) 규칙 3 적용 / 적용 결과: 공급, 작성 관계 -> 외래키로 표현

 

 STEP 4) 규칙 4 적용 / 적용 결과: 일대일 관계가 없으므로 적용할 필요 x 

 STEP 5) 규칙 5 적용 / 적용 결과: 다중 값 속성 없으므로 적용할 필요 x

 

한빛 마트 E-R 다이어그램에 5가지 기본 변환 규칙을 모두 적용한 최종 결과

 

테이블 명세서 작성

릴레이션 스키마 변환 후 속성의 데이터 타입, 길이, 널 값 허용 여부, 기본값, 제약조건 등 세부적으로 결정 및 문서화

테이블 명세서: 릴레이션 스키마에 대한 설계 정보를 기술한 문서

회원 릴레이션 스키마의 테이블 명세서

설계 4단계 - 물리적 설계

하드웨어, 운영체제 등의 특성을 고려 -> 필요한 인덱스 구조, 내부 저장 구조 등에 대한 물리적 구조 설계

설계 5단계 - 구현

DBMS에서 SQL문을 실행 -> DB를 실제로 생성