데이터 모델링 방법론 2003년 03월

목 차(1) 1. 데이터 모델링의 개요 2. 데이터 모델링 방법론 1.1 데이터모델링의 개요 1.2 데이터모델링의 절차 목 차(1) 1. 데이터 모델링의 개요 1.1 데이터모델링의 개요 1.2 데이터모델링의 절차 2. 데이터 모델링 방법론 2.1 개념 데이터 모델링 2.1.1 엔터티의 정의 2.1.2 관계의 정의 2.1.3 식별자의 정의 2.1.4 개념 ERD의 작성 2.2 논리 데이터 설계 2.2.1 엔터티의 속성 정의 2.2.2 데이터 모델의 상세화 2.2.3 도메인 정의 2.2.4 엔터티의 통합 2.2.5 데이터 모델의 검증

목 차(2) 2. 데이터 모델링 방법론 3. 데이터 모델링의 산출물의 작성 2.3 물리 데이터 설계의 절차 목 차(2) 2. 데이터 모델링 방법론 2.3 물리 데이터 설계의 절차 2.4 물리 데이터 설계의 방법 2.4.1 시스템 구성 분석 2.4.2 트랜잭션 분석 2.4.3 용량산정 2.4.4 테이블스페이스 설계 2.4.5 반정규화 2.4.6 테이블 설계 2.4.7 인덱스 설계 2.4.8 뷰 설계 2.4.9 클러스터 설계 2.4.10 Partition 설계 2.4.11 디스크 구성 설계 2.5 데이터베이스 구축 3. 데이터 모델링의 산출물의 작성 3.1 산출물 목록 3.2 산출물 예제

1. 데이터 모델링의 개요 정의 목적 필요성 기대효과 1.1 데이터 모델링의 개요 관리하고자 하는 유용한 정보와 그 관계를 정의하고 형상화하는 것 목적 데이터모델링이란 기업의 정보구조를 실체와 관계를 중심으로 정해진 기호와 규칙을 사용하여 명확하게 표현하고 문서화하는 기법이다. 이를 통해 구축하고자 하는 시스템의 구체적 모습을 일목요연하게 볼 수 있으며, 프로젝트 이해 당사자간의 의사소통을 위한 수단으로 활용된다. 필요성 DBMS 구축에 필요한 제반 기술들의 효율적 적용을 위한 방안 제시 업무 조직과 기술 조직 간의 의사 소통 및 중재 잠재적 위험 요소에 대한 조기 발견 및 해결방안 제시 주요 설계 사항의 추후 변경에 따른 사업수행 지연 방지 기대효과 안정적 DB설계로 신뢰성 있는 시스템 구축 최적의 기술 적용에 따른 시스템 품질 향상 추후 업무의 변화에 능동적으로 대응할 수 있는 확장성 증진 발주처에 대한 시스템의 신뢰성 및 만족도 증진

1. 데이터 모델링의 개요 1.2 데이터 모델링의 절차 Phase 분석 Task Activity 기존 도출 정보 요구사항 자료 수집 신규 정보 요구사항 수집 현업 부서 인터뷰 실시 수집된 정보 요구사항 정리 요구사항에 대한 주제 영역 분류 요구사항파악 현행 시스템 분석 개념 데이터 모델링 현행 운영시스템 분석 업무처리 흐름 및 운영방법 현행 시스템 정보관리요원 면담실시 현행 시스템 ERD 분석 데이터 모델에 반영할 항목 선정 Naming Rule설정 (명료성, 적절성, 고유성, 일관성) 주제 영역별 주요 Entity 도출. (공통 주제영역 포함) 개별 Entity 간의 관계 설정 (대표키와 참조키 정의) 외부 시스템 교환 데이터 파악

1. 데이터 모델링의 개요 1.2 데이터 모델링의 절차 Phase Task Activity 설계 개별 Entity의 속성 정의 정규화 적용(3차) 도메인 정의 속성 규칙의 정의 엔터티의 통합 데이터 모델의 검증 논리적 데이터 모델 설계 관계형 테이블의 전환 반 정규화 트랜잭션의 분석 인덱스의 설계 테이블 파티션의 설계 접근 방법의 설계 데이터베이스 환경 설계 물리적 데이터 모델 설계 구현 데이터베이스 구축 데이터 모델링 완료. DDL 생성. 데이터 베이스 구축.

2.1 개념 데이터 모델링 2.1.2 엔터티의 정의 엔터티의 수집 엔터티의 분류 엔터티의 선정 엔터티의 검증 산출물의 작성 현업장표 유사시스템 관련전문서적 보고서 현장조사 현행시스템분석 산출물 요구사항분석산출물 기본 엔터티 : 데이터 발생의 주체 중심 엔터티 : 업무의 중심 역할 행위 엔터티 : 발생하는 업무이며 자주 변경되고 증가 엔터티의 특성이나 속성은 제거 중복된 명사의 제가 주제 영역별 핵심 엔터티의 도출 엔터티의 검증 산출물의 작성 두개 이상의 관리 건수가 존재하는 것 두개 이상의 관리 항목이 존재하는 것 엔터티의 주어가 존재하는 것 동질성, 독립성, 순수성을 고려. 주제 영역정의서 엔터티 정의서

2.1 개념 데이터 모델링 2.1.3 관계의 정의 관계의 도출 카디낼러티의 설정 참여도의 설정 식별관계의 설정 업무 기술서, 장표, 인터뷰정리 문서에서 동사를 구분한다 도출된 엔터티와 관계를 이용하여 관계정의를 작성한다 관계를 더 세분화하고 정확하게 도출하는 작업을 한다 업무의 연관성을 고려하여 1:1, 1:M, M:N으로 설정한다. 툴(ER_win)의지원에 따라 M:N관계는 1:M의 관계로 풀어낸다. Mandatory와 Optional을 설정한다. Identifying는 관계를 통하여 이주한 부모의 식별자가 자식의 주식별자의 일부가 되는 경우에 설정한다. Non-Identifying 는 부모의 주식별자가 자식의 non-key영역으로 이주하고 자식을 식별하는데 관계하지 않는다 부모의 식별자가 Null 경우가 발생할 경우 사용한다. 관계의 설정 관계 정의서의 작성 재귀관계, 병렬관계, 배타적 관계를 업무의 규칙에 따라 관계를 설정하고 도식한다. 기준 엔터티와 관계 엔터티의 관계의 설명과 참여도를 기술하여 정의서를 작성한다.

식별자 : 엔터티 내의 유일성을 보장해 주는 속성 또는 속성의 하나, 혹은 하나이상의 ATTRIBUTE로 구성 2.1.4 식별자의 정의 2.1 개념 데이터 모델링 식별자 : 엔터티 내의 유일성을 보장해 주는 속성 또는 속성의 하나, 혹은 하나이상의 ATTRIBUTE로 구성 UID는 모든 ENTITY의 필수 요건 UID를 구성하는 모든 ATTRIBUTE는 반드시 존재해야 한다. (mandatory) 중심(Main) 엔터티와 활동(Action) 엔터티는 의미상의 주어가 UID 상황에 따라 의미상의 주어는 인조 키로 대체될 수 있다. 무조건 인조 키를 만들거나 상속 받은 속성만으로 UID를 구성하는 것은 아님 인조 키의 사용 조건 UID 생성을 위해 종종 인위적인 ATTRIBUTE 가 사용됨 이미 실무상에서 사용중인 경우를 활용할 것(예: 사원번호) 범용적으로 사용하고 있는 것을 활용 할 것 (예:은행코드) 너무 긴 ATTRIBUTE를 사용자가 자주 사용해야 하는 경우 사용자의 편의나 효율성을 위해 분류를 하고자 하는 경우 UID bar에 의해 너무 많은 ATTRIBUTE로 UID가 구성되어지는 경우 사용자의 데이터 처리에 관계없이 특정하게 부여된 UID가 내부적으로만 많은 RELATIONSHIP을 가지는 경우

2.1 개념 데이터 모델링 검증된 엔터티를 도식한다. 엔터티의 관계를 도식한다 엔터티의 식별자를 지정한다. 2.1.5 개념 ERD의 작성 2.1 개념 데이터 모델링 검증된 엔터티를 도식한다. 중심 엔터티를 ERD의 중앙에 위치한다. 엔터티의 관계를 도식한다 관계의 Cardinality를 설정한다 참여도를 설정한다 식별관계를 설정한다 관계 설정의 복잡해 지는 경우 Sub Area로 분리한다 분리하는 기준은 주제영역의 단위로 나눈다. 엔터티의 식별자를 지정한다.

2.2 논리 데이터 설계 2.2.1 엔터티의 속성 정의 속성의 수집 속성의 정의 업무의 자료를 수집 현 시스템 분석 프로세스 모델링 프로세스 모델과 데이터 모델의 검증 시스템 구축 단계 속성의 정의 최소 단위로 분할 : 분할 여부를 고려. 속성의 유일성 검증 : 여러 가지의미를 갖는 속성의 분할한다. 반복적 속성은 엔터티로 분리한다. 추출 값의 검증 : 추출 값을 배제하고 물리 설계 시에 고려 상세화 : 관리 속성(등록일자, 등록자)을 추가한다.

2.2 논리 데이터 설계 제1정규화 제2정규화 제3정규화 2.2.2 데이터모델의 상세화 – 정규화의 적용 모든 속성은 반드시 하나의 값을 가져야 한다.(반복배제) 제2정규화 모든 속성은 반드시 UID 전부에 종속이 되어야 한다(식별자 부분 종속배제) 결합 인덱스인 경우에 고려 관계 엔터티 생성시에 2차 정규화가 위배되는 지 여부 확인 Identifier 와 non-Identifier의 설정 시에 고려 제3정규화 UID가 아닌 모든 속성간에는 서로 종속될 수 없다.

2.2 논리 데이터 설계 2.2.2 데이터모델의 상세화 – 1:1, M:N관계의 해소 M:N관계의 해소 1:1관계의 해소 관계 엔터티 추가 수강 학번 (FK) 강의번호 (FK) 강의 강의번호 학생 학번 M:N관계의 해소 1:1관계의 해소 개별 엔터티의 유지 엔터티의 완전통합 엔터티의 부분통합 Subtype 생성

2.2 논리 데이터 설계 2.2.2 데이터모델의 상세화 – 이력관리 이력관리의 대상 변경내역을 감시할 필요가 있는가 ? 시간의 경과에 따라 데이터가 변할 수 있는가 ? 시간의 경과에 따라 Relationship이 변할 수 있는가 ? 과거의 데이터를 조회할 필요가 있는가 ? 前 버젼(version)을 보관할 필요가 있는가 ? 이력관리의 유형 변경이력 : 변경이전과 변경이후의 데이터를 관리 예)주문변경, 계약변경 발생이력 : 시간 순으로 데이터가 발생 예)요금청구, 급여 진행이력 : 업무의 진행에 따라 변하는 상태를 관리 예)공사진행,접수진행 이력관리의 방법 이력을 관리하는 기준은 업무의 시작과 끝을 가지고 판단 변화가 없는 경우에 데이터를 생성해서는 안 된다. 향후에 분석을 해야 하는 정보인지를 파악에 이력관리에 추가한다. 이력의 유형에 따라 최종 시점의 데이터를 마스터 테이블에 가져야 할 경우를 고려 (주문 총금액을 마스터 테이블에 저장하는 경우) 최종 데이터의 종료일자의 지정은 9999-12-31으로 한다.

2.2 논리 데이터 설계 도메인 정의 도메인의 정의 방법 2.2.3 도메인 정의 엔터티 내의 속성에 대한 데이터 타입과 크기, 제약사항을 지정 도메인의 변경이나 속성이 추가되고 변경될 때 일관성 있게 관리 도메인의 정의 방법 데이터모델의 모든 속성을 나열한다. 두 번째 모든 속성 중에 뒤부터 2~4정도로 분리해 본다 공통으로 발생하는 접미어를 분리하여 하나로 만든다. 분리된 접미어를 비슷한 것끼리 묶어 그룹을 만들어 이름을 부여한다 각 도메인 별로 데이터 타입과 길이를 지정한다. 각 엔터티의 속성에 도메인을 할당한다.

2.2 논리 데이터 설계 목적 종합적 정보의 조회 문제점 2.2.4 엔터티의 통합 엔터티 중복성 제거 비슷한 유형의 엔터티의 동일 규칙 적용 모델링의 단순화 문제점 업무 확장성의 감소 업무 흐름 파악의 어려움 시스템의 성능 저하 (?) – 경합의 우려. 제약사항 정의의 어려움 – Not Null, Check, Default 체크 조건의 증가 두 개의 엔터티가 다른 키 값으로 통합된 경우 SQL작성이 복잡(배타적 관계의 통합 시 발생)

엔터티에 대한 정확한 정의가 불명확함에 따라 발생한 엔터티 관계 엔터티나 Sub Type등으로 생성할 수 있다. 2.2.4 엔터티의 통합 - 예시 2.2 논리 데이터 설계 키가 동일한 엔터티의 통합. 예) 등록자, 접수자 ->등록자 부동산소유자, 부동산전세자 ->부동산 관계자 키 값이 유사한 엔터티의 통합 예) 특별고객, 할인대상고객, 고객 -> 고객 키 값과 도메인, 속성이 유사한 엔터티의 통합 예) 작업요청, 작업완료 -> 작업관리  엔터티에 대한 정확한 정의가 불명확함에 따라 발생한 엔터티 관계 엔터티나 Sub Type등으로 생성할 수 있다.

2.2.4 엔터티의 통합 - 코드의 통합 2.2 논리 데이터 설계 장점 : 코드 관리를 일원화

2.2 논리 데이터 설계 계층구조의 통합 – 자기 참조관계로 통합 BOM 관계- 소요량 계산 2.2.4 엔터티의 통합 - 순환관계의 통합 2.2 논리 데이터 설계 계층구조의 통합 – 자기 참조관계로 통합 BOM 관계- 소요량 계산

2.2 논리 데이터 설계 2.2.4 엔터티의 통합 - Subtype의 통합(1) 하나의 테이블로 통합 여러 개의 테이블로 통합 SUB-TYPE을 SUPER-TYPE 에 통합하여 하나의 테이블로 만든다 이 통합된 테이블에는 모든 SUB-TYPE의 데이터를 포함해야 한다 주로 SUB-TYPE에 적은 양의 속성이나 관계를 가진 경우에 적용된다 SUPER-TYPE으로 테이블 명칭 부여 SUB-TYPE을 구분할 수 있도록 컬럼 추가 SUPER-TYPE의 속성을 컬럼 명으로 SUB-TYPE의 속성을 컬럼 명으로 SUPER-TYPE의 관계를 FK로 SUB-TYPE의 관계를 FK로 각각의 SUB-TYPE 마다 하나의 테이블로 만든다 분할된 테이블에는 해당 SUB-TYPE의 데이터만 포함해야 한다 주로 SUB-TYPE에 많은 양의 속성이나 관계를 가진 경우에 적용된다 SUB-TYPE마다 테이블 명칭 부여 테이블마다 SUPER-TYPE의 속성을 컬럼으로 SUB-TYPE마다 해당되는 관계들을 FK로 테이블마다 SUPER-TYPE의 관계를 FK로

2.2 논리 데이터 설계 2.2.4 엔터티의 통합 - Subtype의 통합(2) 하나의 테이블로 통합 여러 개의 테이블로 통합 장점 데이터 액세스가 보다 간편 VIEW를 활용하여 각 SUB-TYPE 만을 액세스하거나 수정 가능 수행속도가 좋아지는 경우가 많다 SUB-TYPE 구분 없는 임의 집합의 가공이 용이 다수의 SUB-TYPE을 통합한 경우 조인(JOIN) 감소효과가 크다 복잡한 처리를 하나의 SQL로 통합하기가 용이 단점 특정 SUB-TYPE의 NOT NULL 제한 불가 테이블의 컬럼 수가 증가 테이블의 블록 수가 증가 처리 시마다 SUB-TYPE의 구분(TYPE) 이 필요해 지는 경우가 많다 인덱스(INDEX) 크기가 증가 각 SUB-TYPE 속성들의 선택사양 명확 처리 시마다 SUB-TYPE 유형구분이 불필요 전체 테이블 스캔 시 유리 단위 테이블의 크기가 감소 전체적인, 혹은 SUB-TYPE 구분 없이 데이터를 처리하는 경우 UNION 이 발생 처리속도가 감소하는 경우가 많다 트랜잭션 처리 시 여러 테이블을 처리하는 경우가 빈번해 진다 복잡한 처리의 SQL 통합이 어려워 진다 부분범위 처리가 불가능해 질 수 있다 여러 테이블을 합친 VIEW는 조회만 가능하다 UID 유지관리가 어렵다

Erwin에서 배타적 관계를 Role로 처리할 수 있음 2.2.4 엔터티의 통합 - 배타적 관계의 통합 2.2 논리 데이터 설계 상호 배타적으로 발생하는 경우의 처리 둘 중에 하나는 항상 Null 양쪽의 데이터 타입은 일치 해야 함 Erwin에서 배타적 관계를 Role로 처리할 수 있음

2.2 논리 데이터 설계 Crud Matrix를 통한 상관 모델링 2.2.5 데이터 모델의 검증 발생경우 프로세스 모델 비고 모든 엔터티의 CRUD가 한번 이상 존재하는가? 프로세스의 추가 엔터티의 삭제 필요 없는 엔터티이거나 프로세스의 부재 모든 엔터티의 ‘C’가 한번 이상 존재하는가? 생성 프로세스 추가한다 엔터티의 생성 프로세스가 없는 경우 모든 엔터티의 ‘R’가 한번 이상 존재하는가? 활용 프로세스의 추가한다 불필요한 엔터티 인지를 고려한다. 생성 후 활용하지 않는 경우 모든 단위 프로세스는 하나 이상의 엔터티가 존재하는가? 엔터티의 추가 엔터티 도출을 하지 못한 경우 두 개 이상의 프로세스가 하나의 엔터티를 생성하는가? 배타적 관계에서 발생 엔터티의 통합을 고려

2.2 논리 데이터 설계 2.2.5 데이터 모델의 검증 – 엔터티의 검토 검토 내역 사례 Primary Key를 통해 업무적으로 발생하는 자료의 유일성을 보장하는가? 유일성을 보장하지 않는 Primary Key의 산정 필요 이상의 항목에 Primary Key를 선정 선정된 Primary Key는 효율성을 가지는가? 선정된 속성이 업무의 대표성을 가지는지를 파악 유일한 속성이 무조건 Primary Key는 아니다 최소의 속성으로 유일성을 보장하는가? 유일성을 보장하기 위해 최대의 항목의 조합으로 Primary Key를 선정하는 경우(Identify와 Non Identify의 정확한 설정이 필요) 자료 발생유형이 유사한 엔터티는 통합되었는가? 장표단위의 엔터티 생성 집약도가 큰 여러 개의 통계 엔터티(부서별매출, 제품별 매출, 분기별 매출 -> 부서별 제품별 매출) 병합 또는 분리되어야 할 엔터티는 존재하는가? 연속적인 업무절차가 갖는 엔터티의 병합 추가적으로 도출되었거나 불필요한 엔터티는 없는가 단위 시스템간의 중복적인 엔터티 정규화가 되지 않은 엔터티는 없는가? 업무분석의 미흡으로 발생. 공통 엔터티의 경우 자료의 원천 엔터티가 추적 가능한가? 자료 원천 구분자의 부재. Primary Key순서는 적절히 정의 되었는가? 사용하는 않는 컬럼을 Primary Key 첫번째 항목을 선정하는 경우 구분 Flag와 같은 속성이 Primary Key의 첫번째 항목으로 선정하는 경우 단위 프로세스들의 액세스 접근 경로를 고려하지 않는 경우 날짜와 같은 범위 질의 속성이 Primary Key의 첫번째 항목으로 선정되는 경우

2.2 논리 데이터 설계 2.2.5 데이터 모델의 검증 – 속성의 검토 검토 내역 사례 반정규화된 속성은 식별되는가? 반정규화된 속성이 실제로는 다른 속성이나 이름만 같은 속성이 공존함. 명칭이 같은 속성의 타입과 크기는 동일한가? 크기의 불일치 타입의 불일치 내부적인 속성을 가지는 속성은 존재하는가? 병합된 속성만 관리 – 속성의 함수 사용이 빈번히 발생. 병합되어야 할 속성은 존재하지 않는가? 날짜와 같은 범위 질의 속성 전후 레코드 간 영향을 미칠 수 있는 속성은 없는가? 중간 데이터가 변경 가능한 이력 엔터티에서 현재 데이터까지의 누적정보를 관리하는 속성 감사, 통계 등을 고려하여 속성이 정의 되었는가? 코드화할 수 있는나 텍스트로 정의된 속성

2.2 논리 데이터 설계 2.2.5 데이터 모델의 검증 – 관계의 검토 검토 내역 사례 엔터티 간의 관계가 M:N인 속성은 없는가? M:N의 관계를 가지는 엔터티에 대해 새로운 부모 엔터티를 생성하여 관계를 연결해 주는 경우① 두 엔터티 중의 하나의 관계를 All or nothing으로 하여 1:N의 관계를 정의하는 경우 – 업무의 재정의 M:N의 관계를 갖는 엔터티 타입에 대해 새로운 자식 엔터티를 생성하여 관계를 연결해 주는 경우 엔터티 간의 관계는 업무적 흐름과 규약이 일치하는가? Mandatory와 Optional의 잘못된 지정. FK가 속성 생성시점이 자신의 레코드 생성시점과 다른 경우② 업무적 흐름에 비추어 미 도출된 관계는 없는가? 업무가 명확하지 않아 엔터티만 도출하고 관계를 정의하지 않는 경우 단위 시스템간의 업무적 연계가 정의되지 않은 경우 관계에 대한 표현은 적절한 수준으로 이루어졌는가? 통계와 코드 엔터티간의 관계연결 Primary key를 상속 받은 엔터티와 조상 엔터티와의 관계 연결 자재 요청과 구매요청 엔터티에서 요청한 자재 중에서 일부만 구매요청을 할 수 있고 나머지에 대해서 재 구매 요청을 할 수 있고 또한 여러 개의 자재요청을 묶어서 구매 요청을 하는 요구사항이 있으면 M:N의 관계를 이룬다. 이 경우 자재요청이라는 부모 엔터티를 만들고 기존의 엔터티를 품목별 자재요청과 품목별 구매요청 엔터티로 구성하여 1:M, 1:N으로 관계를 구성할 수 있다. 전표가 매입과 매출 그리고 급여를 통해 발생하지만 급여는 급여 테이블에 등록된 후 자동으로 전표에 등록된다면 전표와 급여 엔터티의 관계는 1:M의 mandatory이지만 발생시점이 다르므로 optional로 정의해야한다.

2 데이터 모델링 방법론 2.3 물리 데이터 모델의 절차 물리설계의 준비자료 객체의 생성 데이터베이스 구축 데이터베이스 생성 2 데이터 모델링 방법론 물리설계의 준비자료 객체의 생성 데이터베이스 구축 데이터베이스 생성 시스템 구성 설계 테이블 설계 컬럼 정의 Constraint 정의 테이블스페이스 생성 트랜잭션의 분석 Storage 설정 인덱스 설계 인덱스형태결정 사용자/역할 생성 용량산정 컬럼 순서의 결정 오브젝트 생성 테이블스페이스 설계 뷰 설계 산출물의 작성 트랜잭션 분석서 테이블 정의서 인덱스 정의서 뷰 정의서 테이블스페이스 정의서 데이터파일 용량 산정서 디스크 용량 산정서 클러스터 설계 반정규화 테이블 반정규화 Partition 설계 컬럼 반정규화 디스크 구성 설계

시스템 분석 장비 OS CPU Memory Disk 구성 System Tablespace Rollback Segment 2.4.1 시스템 구성 설계 2.4 물리 데이터 설계의 방법 시스템 분석 장비 OS CPU Memory Disk 구성 System Tablespace Rollback Segment Data Buffer Cache

2.4 물리 데이터 설계의 방법 2.4.2 트랜잭션 분석 트랜잭션 : 각각의 업무에서 처리하는 업무의 기본단위 단위 프로세스와 Crud matrix를 이용하여 트랜잭션 분석서 작성 트랜잭션 당 각 테이블을 참조하는 횟수를 기록 단위 프로세스가 주기별로 발생하는 트랜잭션을 기록 주기별로 발생하는 총 트랜잭션을 산출 데이터베이스 용량산정 : 테이블에 저장되는 데이량을 유추 디스크 구성의 근거자료 : 집중되는 트랜잭션의 해당하는 테이블의 I/O를 줄이기 위해 데이터파일의 분산을 고려 할 수 있다.

2.4 물리 데이터 설계의 방법 2.4.3 용량산정 용량분석의 목적 용량분석의 절차 테이블 용량 계산 인덱스 용량 계산 정확한 용량을 산정하여 디스크 사용의 효율을 높인다 업무량이 집중되어 있는 디스크를 분리하여 설계함으로써 집중화 된 디스크에 대한 입출력 부하를 분산 입출력 경합을 최소화하여 데이터의 접근 성능을 향상 데이터베이스 오브젝트의 익스텐트 발생을 줄인다 용량분석의 절차 용량 분석을 위한 기초 데이터 수집 기초 데이터를 이용한 DBMS에 이용하는 오브젝트 별로 용량을 산정 디스크 용량 산정 테이블 용량 계산 총 블록 헤드 계산 블록 당 가능한 데이터 영역 계산 평균 Row의 전체 길이 계산 총 평균 Row 크기 계산 데이터 블록 내의 평균 Row 수 계산 테이블에 요구하는 블록과 바이트 수를 계산 인덱스 용량 계산 총 블록 헤더 계산 결합된 열 길이 계산 인덱스 값 크기의 전체 평균 계산

테이블을 마스터 테이블과 트랜잭션 테이블로 분류한다. 예상 레코드 건수에 따라 2~4개로 분류한다. 2.4.4 테이블 스페이스의 설계 2.4 물리 데이터 설계의 방법 테이블을 마스터 테이블과 트랜잭션 테이블로 분류한다. 예상 레코드 건수에 따라 2~4개로 분류한다. 시스템의 구성(Disk의 구성)에 따라 테이블스페이스의 개수와 사이즈 등을 결정한다. 분류에 따라 테이블스페이스의 용량을 결정하고 Storage를 결정한다. OS상으로 Striping 되어 있는 않는 경우는 테이블스페이스의 작은 사이즈의 여러 개의 데이터파일로 구성할 수 있다. 수평분할(Partition)할 테이블은 별도로 분류한다. 테이블스페이스의 Extents Size를 테이블에서 동일하게 적용하거나 정수배로 결정한다. Locally Management tablespace를 생성을 고려한다. 테이블 객체를 위한 테이블 스페이스와 인덱스 객체를 위한 테이블스페이스를 분리구성 한다.

관계에 대한 정합성, 데이터의 무결성을 우선할 것인지 아니면 데이터베이스 구성의 단순화와 성능을 우선할지를 고려 2.4.5 반정규화 2.4 물리 데이터 설계의 방법 관계에 대한 정합성, 데이터의 무결성을 우선할 것인지 아니면 데이터베이스 구성의 단순화와 성능을 우선할지를 고려 반정규화의 절차 반 정규화 대상 조사 범위 처리 빈도수 조사 대량의 범위 처리 조사 통계성 프로세스 조사 테이블 조인 개수 조사 다른 방법 유도 검토 뷰 적용 클러스터링 적용 인덱스 조정 응용 어플리케이션 적용 Partitioning 적용 반 정규화 적용 테이블의 반정규화 속성의 반정규화 관계의 반정규화

2.4 물리 데이터 설계의 방법 2.4.5 반정규화 – 테이블의 반정규화 테이블의 병합 1:1 관계의 병합 테이블의 분할 1:M 관계의 병합 Super type 과 Sub Type의 병합 테이블의 분할 수직분할 (특정 속성들만 집중 질의) 수평분할 (특정 범위들만 집중 질의) 지역별 데이터 분산과 데이터 서버별 분산 특징 전체적인 스캔의 범위가 감소 테이블의 Locking 과 경합이 감소 SQL 문이 복잡 전체 테이블을 조회 시 처리 속도 감소 테이블의 추가 통계 테이블의 추가 이력 테이블의 추가

2.4 물리 데이터 설계의 방법 2.4.5 반정규화 – 컬럼의 반정규화 중복 컬럼의 추가 파생 컬럼의 추가 자주 사용하는 컬럼의 중복 데이터를 조회하는 경로를 단축하기 위한 컬럼의 중복 컬럼에 의한 파생 컬럼의 추가 로우에 의한 파생 컬럼의 추가 이력 데이터 모델의 컬럼 추가 파생 컬럼의 추가 최신 정보를 나타내는 컬럼 추가 조회경로 단축

2.4 물리 데이터 설계의 방법 2.4.6 테이블 설계(1) Entity를 table로 Attribute를 Column로 일반적으로 TABLE 명칭과 ENTITY 명칭은 동일하게 필요에 따라 ENTITY 명칭은 한글로 하고 동의어를 영문으로 표시 사례표(instance chart)에 TABLE의 역할을 간략하게 표현 SUPER-TYPE 이나 SUB-TYPE은 나중에 결정 Attribute를 Column로 컬럼의 명칭은 속성의 명칭과 반드시 일치할 필요는 없으나 가능한 표준화된 약어를 사용한다 주식별자를 Primary Key로 외부 식별자는 Foreign Key로 설정 SQL의 예약어(reserved word)의 사용을 피한다 가능한 컬럼 명칭은 짧은 것이 좋으며 짧은 명칭은 SQL 해독시간을 감소시킨다 필수입력 속성은 Nulls/Unique 란에 NN을 표시한다 용어사전과 도메인 정의에 따라 설계한 속성을 그대로 컬럼으로 사용 DATE 타입은 DATE연산이나 LOGGING, 시간 계산이 요구하는 경우에 사용하고 날짜 계산이 요구하는 컬럼은 문자로 지정 자기 참조 컬럼의 최상위는 Null로 지정하지 않고 ‘*’로 지정(인덱스 사용을 위해) Default의 지정으로 코딩의 단순화와 데이터 무결성을 보장한다.

2.4 물리 데이터 설계의 방법 2.4.6 테이블 설계(2) Constraint 설정 장점 Constraint의 종류 데이터베이스 성능을 향상 선언과 변경이 가능 활성화/ 비활성화 할 수 있다. 요구사항과 Rule의 통제가 가능 Constraint의 종류 Not Null Unique 컬럼 Check조건 테이블 Check 조건 Primary Key Foreign Key

개발 단계에서 SQL 문장 구조를 검토하여 반복적으로 인덱스 설계 진행 인덱스 설계의 순서 2.4.7 인덱스 설계(1) 2.4 물리 데이터 설계의 방법 설계 단계에서는 Primary Key와 Foreign Key 및 테이블 접근 경로가 분명하게 드러나는 컬럼에 대해서 기본적인 인덱스 지정 개발 단계에서 SQL 문장 구조를 검토하여 반복적으로 인덱스 설계 진행 인덱스 설계의 순서 인덱스 대상 선정 인덱스 최적화 인덱스 정의서 작성 인덱스 대상 테이블 선정 MULTI BLOCK READ 수에 의해 판단 MULTI BLOCK READ가 16일 때 테이블의 크기가 16블록 이상일 경우 인덱스 설정 MULTI_BLOCK_READ_COUNT는 8인 경우 (BLOCK SIZE 8K) 테이블의 크기가 64k이하이면 인덱스를 불필요. 그러나 참조 관계인 경우 인덱스를 생성 인덱스 대상 컬럼의 선정 분포도 = 데이터별 평균 로우 수 / 테이블 총 로우 수 * 100 분포도가 10 ~ 15%인 컬럼에 대해서 인덱스 지정 자주 사용하는 컬럼(ORDER BY, GROUP BY, UNION, DISTINCT)

분포도가 기형적 불균형인 인덱스는 설정 재고(NULL의 사용) 2.4.7 인덱스 설계(2) 2.4 물리 데이터 설계의 방법 인덱스 컬럼은 수정이 자주 발생하지 않는 컬럼을 선정 분포도가 기형적 불균형인 인덱스는 설정 재고(NULL의 사용) 데이터의 DML 작업이 많은 테이블의 경우 인덱스의 개수 5개 이하 CHAR  VARCHAR2 Date 타입은 ‘=‘ 연산의 경우가 드물다 Substr, Like연산이 불가능 결합인덱스 컬럼 순서가 중요 접근 경로가 ‘=‘ 연산이 가능한 컬럼이 앞으로 분포도 좋은 컬럼이 앞으로 정렬이 자주 발생하는 컬럼이 앞으로

2.4 물리 데이터 설계의 방법 테이블 구조의 단순화 다양한 관점에서의 데이터를 제시 데이터의 보안 유지 2.4.8 뷰 설계 2.4 물리 데이터 설계의 방법 테이블 구조의 단순화 다양한 관점에서의 데이터를 제시 데이터의 보안 유지 논리적인 데이터의 독립성 유지 뷰의 대상 선정 인터페이스 정의서의 외부 시스템과 인터페이스 관여하는 테이블 Crud Matrix를 통해 여러 테이블을 동시에 자주 조인하는 접근하는 테이블 SQL문 작성시 거의 모든 문자에서 인라인 뷰 방식으로 접근하는 테이블

2.4 물리 데이터 설계의 방법 2.4.9 클러스터 설계 지정된 컬럼 값의 순서대로 데이터 행을 저장하는 방법 하나 혹은 그 이상의 테이블을 같은 클러스터내 저장 가능 액세스 기법이 아니라 액세스 효율 향상을 위한 물리적 저장 방법 검색 효율은 높여주나 입력, 수정, 삭제 시는 부하 증가 분포도가 넓을 수록 오히려 유리 (인덱스의 단점을 해결) 분포도가 넓은 테이블의 클러스터링은 저장 공간의 절약 6 블록 이상의 테이블에 적용 대량의 범위를 자주 액세스하는 경우 인덱스를 사용한 처리 부담이 되는 넓은 분포도 여러 개의 테이블이 번번히 조인을 일으킬 때 반복 컬럼이 정규화에 의해 어쩔 수 없이 분할된 경우 Union, Distinct, Order by, Group by 가 빈번한 컬럼이면 고려 수정이 자주 발생하지 않는 컬럼 처리 범위가 넓어 문제가 발생하는 경우는 단일 테이블 클러스터링 조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링

순서 : 파티션의 종류 결정, 파티션 키의 선정, 파티션 수의 결정 2.4.10 Partition 설계 – 테이블 Partition 2.4 물리 데이터 설계의 방법 순서 : 파티션의 종류 결정, 파티션 키의 선정, 파티션 수의 결정 대용량DB는 몇 개의 중요한 트랜잭션 테이블에서 데이터가 증가 보다 작은 단위로 나눔으로써 성능 저하 방지와 관리 수월 장점 Data 액세스 범위를 줄여 Performance 향상 전체 데이터의 훼손 가능성이 감소 및 Data가용성 향상 각 분할 영역을 독립적으로 백업하고 복구가능 Disk Striping로  I/O Performance를 향상(Disk 암에 대한 경합의 감소) Partition 종류 Range Partitioning (범위분할) : 지정한 열의 값을 기준으로 분할(Oracle8) Hash Partitioning (해시분할) : 해시 함수에 따라 데이터를 분할(Oracle8i) Composite Partitioning (조합분할) : 범위분할에 의해 데이터를 분할한 다음 해시 함수를 적용하여 다시 분할하는 방식 (Oracle8i)

액세스 유형에 따라 Partitioning이 이루어질 수 있도록 파티션 Key를 선정 2.4.10 Partition 설계 – Partition Key의 선정 2.4 물리 데이터 설계의 방법 "I/O 분산을 어떻게 할 것인가?" 액세스 유형에 따라 Partitioning이 이루어질 수 있도록 파티션 Key를 선정 이력 데이터의 경우에는 생성주기 또는 소멸주기가 파티션과 일치 고객 – 고객유형(CRM) 가입계약상태 – 종료일자 가입서비스 - 종료일자 청구내역 – 청구년월 청구상세내역 – 미납여부 + 청구년월 수납 - 수납처리일자 가입계약상태 : 시작일자와 종료일자의 기간별 이력관리 테이블

Local prefixed partitioned index 2.4 물리 데이터 설계의 방법 Local prefixed partitioned index Index Column의 구성에서 선두 Column이 Partition Key Column인 Index 각 Partition에 분리된 값의 기준(Partitioning Key)에 따라 정렬되어 Index가 구성된다 Local non-prefixed partitioned index Index의 선두Column이 Partition Key Column로 시작하지 않는 Local Index Partition Key가 포함되지 않은 Column들로 Unique Index를 만들고자 할 경우는 Global Index Global prefixed partitioned index 전체적인 활용 측면 및 영향을 충분히 고려하여 설계하고 생성해야 한다 파티션 테이블이 Drop되면 Index Rebuild 필요 Partition Key를 포함하지 않으면서 몇 개의 Column이 결합된 Unique Index를 만들어야 할 때 Global prefixed non-partitioned index 인덱스가 Partitioned 되어 있지 않은 형태로 일반적인 형태이다 일반적인 Primary Key

2.4 물리 데이터 설계의 방법 2.4.11 디스크 구성 설계 디스크 구성 디스크 1 디스크 2 디스크 3 디스크 4 Oracle executables Index data files Redo logs Export files Control file Data data files Rollback segment file Temporary user data files Archive log files 3 디스크 Rollback segment file 4 디스크 Temporary user files 5 디스크 SYSTEM tablespace data files Temporary user files

2.5 데이터베이스 구축 2. 데이터모델링 방법론

3.1 산출물 목록 3. 데이터 모델링의 산출물의 작성

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(1) 엔터티 정의서 관계 정의서 엔터티명 엔터티 설명 동의어 유의어 구분 관련 속성 비고 도서 인터넷을 통해 판매하고자 하는 책의 정보 책 기본 도서명, 도서번호 회원 인터넷을 통해 등록한 회원의 정보 일반회원 회원번호, 주민번호, 주소, 전화번호, 전자메일, 휴대폰 주문 도서를 구매하기 위해 회원이 입력한 배송지, 결재방법에 관한 정보 주문서, 주문내역 중심 주문번호, 주문일자, 배송방법, 결재방법 포인트 도서구입이나 이벤트 행사에 의해서 주어지는 혜택으로서 양적으로 주어지는 가상 금액 구매포인트 주문목록 회원이 주문한 도서목록에 대한 수량과 가격 구매도서목록 행위 수량,단가 관계 정의서 기준 엔터티 관계형태 참여방법 관련 엔터티 사원 각각의 사원은 한 부서에 속한다 각 부서에는 여러 명의 사원이 존재할 수 있다 필수 선택 부서 각각의 사원은 여러 개의 주문을 접수 할 수 있다 각각의 주문은 한명의 사원에 의해서만 접수된다 주문

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(2) 도메인 정의서 도메인 구분 도메인명 도메인 타입 비고 번호 계약번호 사업자번호 도서번호 배송번호 전화번호 정산번호 주문번호 회원번호 계좌번호 주민번호 우편번호 Number Varchar2(20) Varchar2(13) Varchar2(6)   ‘-‘포함 ‘-’ 포함 ‘-’ 제외 코드 계약상태코드 배송결과코드 배송구분코드 결재방법코드 배송방법코드 거래은행코드 Varchar2(2) 날짜 일자(V) 일자(D) Varchar2(8) Date 비율 Number(3)

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(3) 주제영역 정의서 용어사전 정의서 주제영역 설 명 비고(담당자) 주문 인터넷(?), 회원, 주문, 도서, 도서목록(?), 주문목록, 포인트, 체크도서 이벤트 - 이벤트, 포인트, 주문 구매 - 출판사, 계약, 공급, 도서, 재고, 도서분류, 신간, 공급도서 배송 - 배송, 택배업체, 배송업체, 지하철 배송, 지하철 상점, 정산 용어사전 정의서 용어 용어설명 영문명 약어 비고 계좌 은행 등에 예입하기 위해 만든 예금 계좌 account acct 계좌번호: acct_no 가격 물건의 가격을 나타냄 Amount amt

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(4) 테이블 정의서 업무영역 사원 사용자 SCOTT 테이블 스페이스 TSTEST01 테이블명 테이블 ID EMP Pctused 70 Pctfree 50 항목명칭 항목ID 타입 길이 NN PK FK 비고 사원번호 EMPNO VARCHAR2 6 V 사원명 EMPNM 40 주민번호 JUMINNO 13 부서번호 DEPTNO 2

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(5) 트랜잭션 분석서 뷰 정의서 EP명 번호 테이블명 컬럼 CRUD 트랜잭션당 트랜잭션수 주기 제품주문을 신청한다 1 고객 고객번호,고객명 R 200 일 2 주문 주문번호, 주문일자, 고객번호 C 3 주문목록 주문번호, 제품번호, 단가 5 1,000 4 제품 제품번호, 제품명, 재고량 뷰 정의서 뷰 명 뷰 설명 관련테이블 컬럼명 데이터 타입 V_EMP 회계 시스템과 인터페이스 EMP EMPNO EMPNM HIREDATE Varchar2(6) Varchar2(40) DATE V_ORDERITEM 주문과 주문목록을 함께 처리 ORDER ORDERNO ORDERNM ORDERDATE ORDERITEM ITEMNO PRICE Number(10)

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(6) 인덱스 정의서 용량산정내역 엔터티 테이블 인덱스 컬럼명 타입 테이블스페이스 인덱스유형 정렬 구분 부서 DEPT I_DEPT01 DEPTNO NUMBER(2) ISTEST01 UNIQUE ASC PK 사원 EMP I_EMP01 EMPNO VARCHAR2(6) I_EMP02 NOT UNIQUE DESC INDEX HIREDATE VARCHAR2(8) 용량산정내역 엔터티 테이블 Row 길이 (Byte) 보존기간 초기건수(건) 발생건수(건) 발생주기 년증가율 사용자코드 CODE 44 10년 2,500 100 년 5건 사원 EMP 120 8, 021 80 월 50건 부서 DEPT 2,330 10 분기 10건 제품 ITEM 260 5년 3,211,874 3,700 일 200건

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(7) 테이블 스페이스 용량 설계서 데이터 파일 용량 설계서 테이블 용량 테이블 스페이스 용량(40% 확장고려) 데이터 파일명 TABLE1 TABLE2 TABLE3 30M 20M 100M TS01 150M + 60M = 210M DF001.DBF TABLE5 TABLE6 TABLE7 10M 5M TS02 115M + 46M = 161M DF002.DBF01 DF002.DBF02 데이터 파일 용량 설계서 디스크 데이터파일 디렉토리 데이터파일 데이터파일용량 테이블스페이스 테이블스페이스 용량 비고 /DISK1 /DISK1/ORADATA/ORA8/DB1 DF001.DBF01 320M TS001 210M TS002 100M DF002.DBF01 500M 861M DF002,DF01과 공유 /DISK2 /DISK2/ORADATA/ORA8/DB1 DF001.DBF02 110M TS003 DF002.DBF02

3. 데이터 모델링의 산출물의 작성 3.2 산출물 예제(8) 디스크 용량 설계서 디스크 데이터파일 디렉토리 디스크용량 사용된 디스크용량 디스크사용비율 데이터파일명 데이터파일용량 /DISK1 /DISK1/ORADATA/ORA8/DB1 2G 820M 41% DF001.DBF001 320M DF002.DBF001 500M /DISK2 /DISK2/ORADATA/ORA8/DB1 1510M 75% DF001.DBF02 110M 900M