[실습으로 배우는 데이터모델링]
1. ERD에서 FDD의 변환
<심화학습 1>
9장의 심화학습은 '데이터모형 설계의 개선과 예제'를 다룹니다.
■ 데이타 모형 통합의 개요
- 정의
각 특정 업무 영역별 관점에 의해 설정된 논리적 데이타 모델들을 통합하여 궁극적으로 기업전체의 시각을 유지하는 전사적 데이타 모델로 발전
- 목적
- 복합적인 논리적 데이타 모형내의 실체유형들을 정확하게 표현 - 중복성 제거
- 실체및 관계 유형간의 불일치성 해결
- 실체유형간의 새로운 관계나 업무규칙을 추가
- 진행단계
- 실체유형과 관련 업무규칙의 통합 - 관계유형과 관련 업무규칙의 통합 - 속성유형과 관련 업무규칙의 통합
- 실체의 통합
- 동일한 주식별자와 주식별자 도메인을 갖는 실체를 통합
- subtype의 통합
- 주식별자의 도메인에 의하여 sub-type이나 super-type에 실체를 통합
<심화학습 2>
■ 데이타 모형 통합이 필요한 경우
- 테이블의 통합 같이 처리되는 경우가 빈번하면 통합을 고려
- 1 : 1 관계의 테이블의 통합을 고려, 보관 주기가 다를 때에만 분리 - SUPER-TYPE 과 SUB-TYPE의 통합을 고려
- 부분 범위 처리로 유도하고자 하는 테이블
- 공유되지 않는 컬럼은 가변길이로 저장되는 VARCHAR2를 사용 - 다량의 검색처리에 기준을 두어 판단
- 합된 테이블은 단일 테이블 클러스터링을 고려 - 조합으로 인하여 증가되는 로우 수 고려
[실습으로 배우는 데이터모델링]
■ 데이타 모형 통합의 장점
- 데이타 엑세스가 보다 간편
- 뷰를 활용하여 별도의 테이블 처럼 사용 가능 - 부분 범위 처리로 유도 가능
- 수행속도가 좋아지는 경우가 많다
- 복잡한 처리를 하나의 SQL로 통합하기가 용이
■ 데이타 모형 통합의 단점
- 로우 수가 증가하여 스캔 처리시 처리량 증가 - 인덱스의 크기가 증가
- 입력, 갱신, 삭제 규칙이 복잡해질 수 있다
- NOT NULL, DEFAULT, CHECK 등의 CONSTRAINT를 완벽히 지정하기 어려워 질 수 있다 - 엑세스의 조건이 늘어난다
<학습 정리>
ERD → FDD 작성원칙을 5가지이상 나열하세요
① 1:N일 경우 N쪽의 KEY에서 1쪽의 KEY로 화살표 표시한다.
② N:M일 경우 양쪽 KEY를 묶는다.
③ 일반화의 경우 서브 엔티티 KEY에서 슈퍼 엔티티 KEY 쪽으로 화살표를 삽입한다.
④ 1:N의 경우 관계성의 애트리뷰트는 N쪽의 KEY에서 해당 애트리뷰트 쪽으로 화살표를 삽입한다.
⑤ N:M의 경우 관계성의 애트리뷰트는 양쪽 KEY를 묶음에서 해당 애트리뷰트 쪽으로 화살표를 삽입한다.
⑥ 다중값의 경우 해당 엔티티의 KEY와 다중값 속성을 함께 묶는다.
[실습으로 배우는 데이터모델링]
2. 보험회사, 대학시스템 FDD
<심화 학습 1>
9장의 심화학습은 '데이터모형 설계의 개선과 예제'를 다룹니다.
■ 중복 테이블의 추가가 필요한 경우
- 정규화에 충실하면 데이타가 정확하고, 활용성은 향상되나 빈번한 조인에 의해 수행속도가 증가하는 경우 발생
- 다량의 범위를 자주 처리해야 하는 경우 - 특정범위의 데이타만 자주 처리되는 경우
- 처리범위를 줄이지 않고는 수행속도를 개선할 수 없는 경우 - 요약 자료만 주로 요구되는 경우
- 추가된 테이블의 동기화를 위한 오버헤드를 고려하여 결정
- 인덱스의 조정이나 부분범위처리로 유도, 클러스터링을 이용하여 해결할 수 있는 지를 철저히 검토 후 결정
■ 중복 테이블 유형(1) : 집계 테이블의 추가
- 단일 테이블의 GROUP BY - 여러 테이블의 조인 GROUP BY
- 로우 수와 활용도를 모두 만족하는 집계 레벨은 ?
- 집계 테이블에 단일 테이블 클러스터링을 한다면 집계 레벨을 보다 낮출 수 있어 활용도를 높일 수 있지 않은가?
- 클러스터링, 결합 인덱스, 고단위 SQL 을 활용하면 굳이 집계 테이블 없이도 양호한 수행속도를 낼 수 있음을 명심
- 집계 테이블을 다시 집계, 조인하면 추출할 수 있는 지를 검토하여 지나친 집계 테이블을 만들지 말 것
- 추가된 집계 테이블을 기존 응용 프로그램이 이용할 수 있는 지를 찾아 보정시킬 것 - 데이타베이스 트리거의 오버헤드에 주의하고 일관성 보장에 유의
<심화 학습 2>
■ 중복 테이블 유형(2) : 진행 테이블의 추가
- 여러 테이블의 조인이 빈번히 발생하며 처리범위도 넓은 경우 - M : M 관계가 포함된 처리의 과정을 추적, 관리하는 경우
- 검색조건이 여러 테이블에 걸쳐 다양하게 사용되며 복잡하고 처리량이 많은 경우 - 데이타량이 적절하고 활용도가 좋아지도록 키본키를 선정
- 필요에 따라 적절한 추출(Derived) 컬럼을 추가하여 집계 테이블의 역할도 하는 다목적 테이블을 구상
[실습으로 배우는 데이터모델링]
- 다중 테이블 클러스터링이나 조인 SQL을 정확히 이용하면 굳이 진행 테이블을 만들지 않아도 양호한 수행속도를 낼 수 있는 경우 많이 있음
■ 중복 테이블 유형(2) : 특정부분만 포함하는 테이블 추가
- 거대한 테이블의 특정부분만이 주로 자주 사용되는 경우
- 넓은 범위로 자주 처리되며 특정한 컬럼들만 자주 사용되는 경우 - 경우에 따라 코드명칭만 모은 테이블 추가
■ 중복 테이블 사용시 유의사항
- 사용자나 프로그램은 반드시 복사원 데이타만 처리
- 복사원 데이타의 변화에 따라 연쇄반응 지정 (DB트리거 사용)
<학습 정리>
○ 보험회사 시스템 FDD 도출을 위한 주의사항
- 일반화의 FDD 도출방법은 서브 엔티티의 키로부터 슈퍼 엔티티의 키로 화살표를 보내는 것이다.
- 고객번호(회사원), 고객번호(자유업자)의 서브 엔티티 속성을 도출시킨다.
○ 대학 시스템 FDD 도출을 위한 주의사항
- 일반화, 서브세트가 들어있는 예제이다. 보험회사 예제와 같은 방법으로 처리한다.
- 과목과 학생 엔티티 사이에는 N:M의 이중 관계가 존재하는데 이들 모두 FDD에서 별도로 표기해 야 한다.
[실습으로 배우는 데이터모델링]
3. 계약공급체계, 교과목수강체계 FDD
<심화 학습 1>
9장의 심화학습은 '데이터모형 설계의 개선과 예제'를 다룹니다.
■ 테이블의 분할이 필요한 경우
- 컬럼의 사용빈도의 차이가 많은 경우
- 각각의 사용자가 각기 특정한 부분만 지속적으로 사용하는 경우 - 엑세스 빈도나 처리할 데이타량이 적은 경우는 분할 불필요
- 상황에 따라 SUPER-TYPE을 모두 내려 SUB-TYPE 별로 분할하거나 SUPER-TYPE만은 따로 테이블을 생성
- 분할된 테이블은 오히려 수행속도를 나쁘게 하기도 한다 - 데이타 프로세싱 관점이 아니라 검색에 촛점을 두어 결정할 것
■ 테이블의 분할 시 장점
- 스캔처리시 엑세스량이 감소
- 테이블 관리가 용이(backup, recovery, 재생성) - 테이블의 Lock, Contention 감소
- 보안유지가 용이
■ 테이블의 분할 시 단점
- 테이블 구분없이 전체적인 데이타를 처리해야 하는 경우 UNION 이 발생 - 처리속도가 감소하는 경우가 많다
- 트랜젝션 처리시 여러 테이블을 처리하는 경우가 빈번해 진다 - 복잡한 처리의 SQL 통합이 어려워 진다
- 부분범위 처리가 불가능해 질 수 있다
- 여러 테이블을 합친 VIEW는 조회만 가능하다 - 기본키의 유일성 유지관리가 어렵다
<심화 학습 2>
■ 테이블의 분할의 예제(1) : 수직분할
- 특정 컬럼들의 사용빈도의 차이가 심하고 길이가 긴 경우
- 각기 다른 사용자 그룹이 특정 컬럼들만을 사용하고 같이 처리되는 경우가 드문 경우
- 하나의 트랜젝션이 분할된 여러 테이블을 동시에 처리하는 경우가 없을 것 - 돌이켜 보면 모델링 시 SUPER/SUB-TYPE 엔티티 였음
[실습으로 배우는 데이터모델링]
- 기본키는 모든 테이블에 넣고 일반 컬럼들은 각각 테이블에 하나씩만 존재시킬 것 - 컬럼 수가 지나치게 많은 테이블은 사용빈도에 따라 테이블의 분할을 검토해 볼 것 - 가능한 각 테이블에는 모든 로우가 존재하도록 할 것(Outer Join 방지)
- 수량, 금액등 GROUP BY 가 되는 컬럼들은 사용빈도가 많은 쪽 테이블에 위치시킬 것
- 사용빈도가 많은 테이블은 다른 테이블과의 클러스터링이나 단일 테이블 클러스터링을 고려해 볼 것
- 분할된 테이블간의 조인이 많이 발생하는 지 검증할 것
■ 테이블의 분할의 예제(2) : 수평분할
- 각기 다른 사용자 그룹이 특정 로우들 만을 사용하고 같이 처리되는 경우가 드문 경우 - 하나의 트랜젝션이 분할된 여러 테이블을 동시에 처리하는 경우가 없을 것
- 로우의 수가 지나치게 많은 경우 고려
- UNION을 이용한 검색이 빈번하지 않을 것
- 현재 PARTITION VIEW 에서 이 기능을 제공각 로우는 어느 한 테이블에만 존재하게 할 것 - 부분 범위 처리를 활용하고자 하는 경우는 분할된 테이블은 적용하기가 곤란함
- 복잡한 처리의 SQL 통합이 어려워 진다
- UNION ALL을 통한 처리나 여러 개의 SQL을 이용해야 하므로 수행속도가 늦어진다 - 뷰를 활용할 수 있으나 여러 테이블을 합친 뷰는 조회만 가능
- 기본키의 유일성 유지관리가 어렵다
<학습 정리>
○ 계약공급체계 FDD 도출을 위한 주의사항
- N:M 관계를 포함하고 있으므로 양쪽 KEY를 묶어서 FDD를 작성해야 한다.
○ 교과목 수강체계 FDD 도출을 위한 주의사항
- 과정번호, 강의실, 강의시작일을 복합식별자로 하는 함수적종속성이 3번이나 도출되고 있다.
- 일반수강료, 특별수강료는 N:M 관계성에 존재하나 과정에만 함수적종속됨에 유의해야 한한다.
[실습으로 배우는 데이터모델링]
4. FDD에서 테이블 변환
<심화 학습 1>
9장의 심화학습은 '데이터모형 설계의 개선과 예제'를 다룹니다.
■ ER모델 설계의 최종확인사항 (1)
- 비슷한 Attribute 나 Relationship을 가지는 엔티티는 통합하여 더 간단하면서도 더 많은 뜻이 담긴 모델링이 되도록 하라.
-위의 3 엔티티는 하나의 통합 엔티티로 되거나 SUBTYPE/SUPERTYPE 구성이 될 수 있다.
<심화 학습 2>
■ ER모델 설계의 최종확인사항 (2)
- 통합한다는 것은 비슷한 엔티티나 Relationship을 끌어 모아서 합치는 것이다.
[실습으로 배우는 데이터모델링]
- 위의 예제에서
APPLICATION 과 EMPLOYEE CONTRACT는 dates를 갖고 있다.
APPLICANT 와 EMPLOYEE는 비슷한 ATTRIBUTE들을 갖고 있다.
이를 바탕으로 모델을 개선하면 아래와 같다
<학습 정리>
FDD → 테이블 작성원칙을 정리하세요
○ 화살표 나가는 방향으로만 모두 묶는다.
화살표를 내보는 쪽이 키 칼럼이 되고 화살표를 받는 쪽이 일반 칼럼이 된다.
○ BNCF의 정의에 의해 FDD 상에서의 모든 결정자는 키가 된다.
- 즉, 1차, 2차, 3차 정규형을 거치지 않고 바로 BCNF 테이블을 도출할 수 있게 되는 것이다.
[실습으로 배우는 데이터모델링]
5. 보험회사, 대학시스템 테이블
<심화 학습 1>
9장의 심화학습은 '데이터모형 설계의 개선과 예제'를 다룹니다.
■ ER모델 설계의 최종확인사항 (3)
- 엔티티를 통합한다고 하여 너무 일반적인 개념만 가지는 모델을 만들지 마라 - 통합된 엔티티가 다른 부분과의 불일치가 없는지 테스트 해본다
- 필요시 덜 통합된 모델로 되돌아 간다
-모든 관련자가 참여하여 같이 이해하도록 하라
■ ER모델 설계의 최종확인사항 (4)
- 데이타베이스 디자인 단계로 들어가기 전에 E-R 모델을 정확히 검증하여 완성도와 질을 높여라.
[실습으로 배우는 데이터모델링]
<심화 학습 2>
■ ER모델 설계의 최종확인사항 (5)
■ ER모델 설계의 최종확인사항 (6)
- 자주 사용되는 형태의 Relationships
<학습 정리>
○ 보험회사 시스템 테이블 도출을 위한 주의사항 - 일반화를 FDD 처리하는 예제이다.
- 슈퍼 엔티티에서 별도의 서브 엔티티의 키 값을 도출하여 FDD에 나열하였다.
- 서브 엔티티 키 속성에서 슈퍼 엔티티 키 속성으로 화살표를 보내지만, 테이블 작성 시에는 화살 표를 받는 슈퍼 엔티티의 키 속성을 테이블상에 별도의 속성으로 포함시키지 않는다.
[실습으로 배우는 데이터모델링]
6. 계약공급체계, 교과목수강체계 테이블
<심화 학습 1>
9장의 심화학습은 '데이터모형 설계의 개선과 예제'를 다룹니다.
■ ER모델 설계의 최종확인사항 (7)
- 순환관계 모델링에서 관계의 표현이 정확한지를 확인하라.
- 배타적관계의 모델링에서 관계의 표현이 정확한지를 확인하라.
- E-R 모델링을 어디서 시작하여 읽더라도 매끄럽고 의미있는 문장이 되도록 하라
- 완성된 모델링은 누구나 읽을 수 있어야 하고 뛰어난 업무적 감각을 표현하고 있어야 한다.
- 관계의 Optionality를 다시 한번 검사하라.
Mandatory 관계
정말로 ... 이어야만 한다는 관계인가?
동시에 발생하는 상황의 관계가 아니고는 있을 수 없다.
Optional 관계
정말로 ... 일지도 모른다는 관계인가?
Relationship 없이도 발생할 수 있는가?
- 모든 Attribute를 다시 한번 검사하라.
모든 Attribute는 최소단위까지 세분화 되었는가?
집합적 성격의 Attribute는 없는가?
대표 의미 로만 나타나 있는 코드값들은 없는가?
■ ER모델 설계의 최종확인사항 (8)
- 모든 Attribute는 단일 값을 갖는가?
<심화 학습2>
■ ER모델 설계의 최종확인사항 (9)
- 전체 기본키의 일부분에만 종속되는 Attribute는 없는가 ?
[실습으로 배우는 데이터모델링]
■ ER모델 설계의 최종확인사항 (10)
- 기본키가 아닌 Attribute가 기본키가 아닌 다른 Attribute에 종속되는 경우는 없는가?
<학습 정리>
○ 계약공급체계 테이블 도출을 위한 주의사항
- 품목번호를 중심으로 계약번호, 품목번호, 주문번호가 삼각관계를 이루고 있다.
- 복합키 설정에 더욱 유념해야 한다.
○ 교과목수강체계 테이블 도출을 위한 주의사항
- 과정번호, 강의시작일, 강의실의 복합키를 포함하는 테이블이 3개 생성된다.
- 특히 ERD에서 별도로 일반화 시킨 강사와 고안자의 서브 엔티티 삭제, 특별과정과 일반과정의 서 브 엔티티의 삭제가 필요하다.
- 수강테이블의 수강료는 과정에 함수적 종속임을 특히 유의하여 중복값이 생기지 않도록 테이블을
