Data modeling데이터 모델링 방법:개념 데이터,논리 데이터,물리 데이터 설계

목차(1차(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) 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.2 데이터 모델링의 절차 1. 데이터 모델링의 개요 Phase Task Activity 요구사항파악 □ 기존 도출 정보 요구사항 자료 수집 □ 신규 정보 요구사항 수집 . 현업 부서 인터뷰 실시 □ 수집된 정보 요구사항 정리 □ 요구사항에 대한 주제 영역 분류 분석 현행 시스템 분석 개념 데이터 모델링 □ 현행 운영시스템 분석 . 업무처리 흐름 및 운영방법 . 현행 시스템 정보관리요원 면담실시 . 현행 시스템 ERD 분석 □ 데이터 모델에 반영할 항목 선정 □ Naming Rule 설정 (명료성, 적절성, 고유성, 일관성) □ 주제 영역별 주요 Entity 도출. ( 공통 주제영역 포함) □ 개별 Entity 간의 관계 설정 (대표키와 참조키 정의) □ 외부 시스템 교환 데이터 파악 1.2 데이터 모델링의 절차 1. 데이터 모델링의 개요 Phase Task Activity 논리적 데이터 모델 설계 □ 개별 Entity 의 속성 정의 □ 정규화 적용(3차) □ 도메인 정의 □ 속성 규칙의 정의 □ 엔터티의 통합 □ 데이터 모델의 검증 설계 물리적 데이터 모델 설계 □ 관계형 테이블의 전환 □ 반 정규화 □ 트랜잭션의 분석 □ 인덱스의 설계 □ 테이블 파티션의 설계 □ 접근 방법의 설계 □ 데이터베이스 환경 설계 구현 데이터베이스 구축 □ 데이터 모델링 완료. □ DDL 생성. □ 데이터 베이스 구축. 2.1.2 엔터티의 정의 2.1 개념 데이터 모델링 엔터티의 수집 -현업장표 -유사시스템 -관련전문서적 -보고서 -현장조사 -현행시스템분석 산출 물 -요구사항분석산출물 엔터티의 분류 -기본 엔터티 : 데이터 발생의 주체 -중심 엔터티 : 업무의 중심역 할 -행위 엔터티 : 발생하는 업무이 며 자주 변경되고 증가 엔터티의 선정 -엔터티의 특성이나 속성은 제거 -중복된 명사의 제가 -주제 영역별 핵심 엔터티의 도출 엔터티의검증 산출물의 작성 -두개 이상의 관리 건수가 존재하는 -주제 영역정의서 것 -엔터티 정의서 -두개 이상의 관리 항목이 존재하는 것 -엔터티의 주어가 존재하는 것 -동질성, 독립성, 순수성을 고려. 2.1.3 관계의 정의 관계의 도출 카디낼러티의 설정 2.1 개념 데이터 모델링 참여도의 설정 식별관계의설정 -업무 기술서, 장표, -업무의 연관성을 고 -Mandatory 와 인터뷰정리 문서에 려하여 1:1, 1:M, M:N Optional 을 설정한 서 동사를 구분한다 으로 설정한다. 다. -도출된 엔터티와 -툴(ER_win) 의지원 관계를 이용하여 관 에 따라 M:N 관계는 계정의를 작성한다 1:M의 관계로 풀어낸 -관계를 더 세분화 다. 하고 정확하게 도출 하는 작업을 한다 관계의설정 관계 정의서의 작성 -재귀관계, 병렬관계, 배타 -기준 엔터티와 관계 엔터 적 관계를 업무의 규칙에 따 티의 관계의 설명과 참여도 라 관계를 설정하고 도식한 를 기술하여 정의서를 작성 다. 한다. -Identifying 는 관계 를 통하여 이주한 부 모의 식별자가 자식 의 주식별자의 일부 가 되는 경우에 설정 한다. -Non-Identifying 는 부모의 주식별자가 자식의 non-key 영역 으로 이주하고 자식 을 식별하는데 관계 하지 않는다 -부모의 식별자가 Null 경우가 발생할 경우 사용한다. 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.5 개념 ERD 의 작성 2.1 개념 데이터 모델링 □ 검증된 엔터티를 도식한다. . 중심 엔터티를 ERD의 중앙에 위치한다. □ 엔터티의 관계를 도식한다 . 관계의 Cardinality 를 설정한다 . 참여도를 설정한다 . 식별관계를 설정한다 . 관계 설정의 복잡해 지는 경우 Sub Area 로 분리한다 . 분리하는 기준은 주제영역의 단위로 나눈다. □ 엔터티의 식별자를 지정한다. 2.2.1 엔터티의 속성 정의 2.2 논리 데이터 설계 속성의 수집 . 업무의 자료를 수집 . 현 시스템 분석 . 프로세스 모델링 . 프로세스 모델과 데이터 모델의 검증 . 시스템 구축 단계 속성의 정의 . 최소 단위로 분할 : 분할 여부를 고려. . 속성의 유일성 검증 : 여러 가지의미를 갖는 속성의 분할한다. . 반복적 속성은 엔터티로 분리한다. . 추출값의 검증 : 추출값을 배제하고물리 설계시에고려 . 상세화 : 관리 속성(등록일자, 등록자)을 추가한다. 2.2.2 데이터모델의 상세화 . 정규화의 적용 2.2 논리 데이터 설계 □ 제1정규화 . 모든 속성은 반드시 하나의 값을 가져야 한다.(반복배제) □ 제2정규화 . 모든 속성은 반드시 UID 전부에 종속이 되어야 한다(식별자 부분 종속 배제) . 결합 인덱스인 경우에 고려 . 관계 엔터티 생성시에 2차 정규화가 위배되는 지 여부 확인 . Identifier 와 non-Identifier 의 설정 시에 고려 □ 제3정규화 . UID가 아닌 모든 속성간에는 서로 종속될 수 없다. 2.2.2 데이터모델의 상세화 . 1:1, M:N 관계의 해소 2.2 논리 데이터 설계 학생 강의 .관계 엔터티 추가 M:N 관계의 해소 수강학번(FK) 강의번호(FK) 강의번호학번 1:1관계의 해소 개별 엔터티의 유지 엔터티의 완전통합 엔터티의 부분통합 Subtype 생성 2.2.2 데이터모델의 상세화 . 이력관리 2.2 논리 데이터 설계 이력관리의 대상 . 변경내역을 감시할 필요가 있는가 ? . 시간의 경과에 따라 데이터가 변할 수 있는가 ? . 시간의 경과에 따라 Relationship 이 변할 수 있는가 ? . 과거의 데이터를 조회할 필요가 있는가 ? .前 버젼(version) 을 보관할 필요가 있는가 ? 이력관리의 유형 . 변경이력 : 변경이전과 변경이후의 데이터를 관리 예)주문변경, 계약변경 . 발생이력 : 시간 순으로 데이터가 발생 예)요금청구, 급여 . 진행이력 : 업무의 진행에 따라 변하는 상태를 관리 예)공사진행,접수진행 이력관리의 방법 . 이력을 관리하는 기준은 업무의 시작과 끝을 가지고 판단 . 변화가 없는 경우에 데이터를 생성해서는 안 된다. . 향후에 분석을 해야 하는 정보인지를 파악에 이력관리에 추가한다. . 이력의 유형에 따라 최종 시점의 데이터를 마스터 테이블에 가져야 할 경우를 고려 (주문 총금액을 마스터 테이블에 저장하는 경우) . 최종 데이터의 종료일자의 지정은 9999-12-31으로 한다. 2.2.3 도메인 정의 2.2 논리 데이터 설계 □ 도메인 정의 . 엔터티 내의 속성에 대한 데이터 타입과 크기, 제약사항을 지정 . 도메인의 변경이나 속성이 추가되고 변경될 때 일관성 있게 관리 □ 도메인의 정의 방법 . 데이터모델의 모든 속성을 나열한다. . 두 번째 모든 속성 중에 뒤부터 2~4정도로 분리해 본다 . 공통으로 발생하는 접미어를 분리하여 하나로 만든다. . 분리된 접미어를 비슷한 것끼리 묶어 그룹을 만들어 이름을 부여한다 . 각 도메인 별로 데이터 타입과 길이를 지정한다. . 각 엔터티의 속성에 도메인을 할당한다. 2.2.4 엔터티의 통합 2.2 논리 데이터 설계 □ 목적 . 종합적 정보의 조회 . 엔터티 중복성 제거 . 비슷한 유형의 엔터티의 동일 규칙 적용 . 모델링의 단순화 □ 문제점 . 업무 확장성의 감소 . 업무 흐름 파악의 어려움 . 시스템의 성능 저하 (?) . 경합의 우려. . 제약사항 정의의 어려움 . Not Null, Check, Default . 체크 조건의 증가 . 두 개의 엔터티가 다른 키 값으로 통합된 경우 SQL작성이 복잡(배타적 관계의 통합 시 발생) 2.2.4 엔터티의 통합 -예시 2.2 논리 데이터 설계 □ 키가 동일한 엔터티의 통합. . 예) 등록자, 접수자 ->등록자 .부동산소유자, 부동산전세자 ->부동산 관계자 □ 키 값이 유사한 엔터티의 통합 .예) 특별고객, 할인대상고객, 고객 -> 고객 □ 키 값과 도메인, 속성이 유사한 엔터티의 통합 .예) 작업요청, 작업완료 -> 작업관리 □ 엔터티에 대한 정확한 정의가 불명확함에 따라 발생한 엔터티 □ 관계 엔터티나 Sub Type 등으로 생성할 수 있다. 2.2.4 엔터티의 통합 -코드의 통합 2.2 논리 데이터 설계 . 장점 : 코드 관리를 일원화 2.2.4 엔터티의 통합 -순환관계의 통합 2.2 논리 데이터 설계 . 계층구조의 통합 . 자기 참조관계로 통합 . BOM 관계-소요량 계산 2.2.4 엔터티의 통합 -Subtype 의 통합(1) 2.2 논리 데이터 설계 하나의 테이블로 통합 여러 개의 테이블로 통합 □ 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 마다 테이블 명칭 부여 □ SUB-TYPE 의 속성을 컬럼 명으로 □ 테이블마다 SUPER-TYPE 의 속성을 컬럼으로 □ SUB-TYPE 마다 해당되는 관계들을 FK로 □ 테이블마다 SUPER-TYPE 의 관계를 FK로 2.2.4 엔터티의 통합 -Subtype 의 통합(2) 2.2 논리 데이터 설계 하나의 테이블로 통합 여러 개의 테이블로 통합 □ 장점 □ 장점 . 데이터 액세스가 보다 간편 . 각 SUB-TYPE 속성들의 선택사양 명확 . VIEW를 활용하여 각 SUB-TYPE 만을 액세스하거나 수정 . 처리 시마다 SUB-TYPE 유형구분이 불필요 가능 . 전체테이블 스캔시유리 . 수행속도가 좋아지는 경우가 많다 . 단위 테이블의 크기가 감소 . SUB-TYPE 구분 없는임의집합의가공이 용이 □ 단점 . 다수의 SUB-TYPE 을 통합한 경우 조인(JOIN) 감소효과가 . 전체적인, 혹은 SUB-TYPE 구분 없이 데이터를 처리하는 크다 경우 UNION 이 발생 . 복잡한 처리를 하나의 SQL 로 통합하기가 용이 . 처리속도가 감소하는 경우가 많다 □ 단점 . 트랜잭션 처리 시 여러 테이블을 처리하는 경우가 빈번 . 특정 SUB-TYPE 의 NOT NULL 제한 불가 해 진다 . 테이블의 컬럼 수가 증가 . 복잡한 처리의 SQL 통합이 어려워 진다 . 테이블의 블록 수가 증가 . 부분범위 처리가 불가능해 질 수 있다 . 처리 시마다 SUB-TYPE 의 구분(TYPE) 이 필요해 지는 경 . 여러 테이블을 합친 VIEW는 조회만 가능하다 우가 많다 . UID 유지관리가 어렵다 . 인덱스(INDEX) 크기가 증가 2.2.4 엔터티의 통합 -배타적 관계의 통합 2.2 논리 데이터 설계 □ 상호 배타적으로 발생하는 경우의 처리 □ 둘 중에 하나는 항상 Null □ 양쪽의 데이터 타입은 일치 해야 함 □ Erwin 에서 배타적 관계를 Role 로 처리할 수 있음 2.2.5 데이터 모델의 검증 2.2 논리 데이터 설계 □ Crud Matrix 를 통한 상관 모델링 발생경우 프로세스 모델 데이터 모델 비고 모든 엔터티의 CRUD가 한번 이상 존재하는가? . 프로세스의 추가 . 엔터티의 삭제 . 필요 없는 엔터티이거나 프로세스의 부재 모든 엔터티의 ‘C’가 한 번 이상 존재하는가? . 생성 프로세스 추가한다 . 엔터티의 생성 프로세스 가 없는 경우 모든 엔터티의 ‘R’가 한 번 이상 존재하는가? . 활용 프로세스의 추가한다 . 불필요한 엔터티 인지를 고려한다. . 생성 후 활용하지 않는 경우 모든 단위 프로세스는 하나 이상의 엔터티가 존재하는가? . 엔터티의 추가 . 엔터티 도출을 하지 못 한 경우 두 개 이상의 프로세스 가 하나의 엔터티를 생 성하는가? . 배타적 관계에서 발생 . 엔터티의 통합을 고려 2.2.5 데이터 모델의 검증 . 엔터티의 검토 2.2 논리 데이터 설계 검토 내역 사례 . 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.5 데이터 모델의 검증 . 속성의 검토 2.2 논리 데이터 설계 검토 내역 사례 . 반정규화된 속성은 식별되는가? . 반정규화된 속성이 실제로는 다른 속성이나 이름만 같은 속성이 공존함. . 명칭이 같은 속성의 타입과 크기는 동일한가? . 크기의 불일치 . 타입의 불일치 . 내부적인 속성을 가지는 속성은 존재하는가? . 병합된 속성만 관리 . 속성의 함수 사용이 빈번히 발생. . 병합되어야 할 속성은 존재하지 않는가? . 날짜와 같은 범위 질의 속성 . 전후 레코드 간 영향을 미칠 수 있는 속성은 없는가? . 중간 데이터가 변경 가능한 이력 엔터티에서 현재 데이터까지의 누적정보 를 관리하는 속성 . 감사, 통계 등을 고려하여 속성이 정의 되었는가? . 코드화할 수 있는나 텍스트로 정의된 속성 2.2.5 데이터 모델의 검증 . 관계의 검토 2.2 논리 데이터 설계 검토 내역 사례 . 엔터티 간의 관계가 M:N 인 속성은 없는가? . M:N 의 관계를 가지는 엔터티에 대해 새로운 부모 엔터티를 생성하여 관계 를 연결해 주는 경우① . 두 엔터티 중의 하나의 관계를 All or nothing 으로 하여 1:N의 관계를 정의 하는 경우 . 업무의 재정의 . M:N 의 관계를 갖는 엔터티 타입에 대해 새로운 자식 엔터티를 생성하여 관 계를 연결해 주는 경우 . 엔터티 간의 관계는 업무적 흐름과 규약이 일치하 는가? . Mandatory 와 Optional 의 잘못된 지정. . FK가 속성 생성시점이 자신의 레코드 생성시점과 다른 경우② . 업무적 흐름에 비추어 미 도출된 관계는 없는가? . 업무가 명확하지 않아 엔터티만 도출하고 관계를 정의하지 않는 경우 . 단위 시스템간의 업무적 연계가 정의되지 않은 경우 . 관계에 대한 표현은 적절한 수준으로 이루어졌는가 ? . 통계와 코드 엔터티간의 관계연결 . Primary key 를 상속 받은 엔터티와 조상 엔터티와의 관계 연결 2.3 물리 데이터 모델의 절차 2 데이터 모델링 방법론 물리설계의 준비자료 시스템 구성 설계 객체의 생성 테이블 설계 Constraint 정의컬럼 정의 Storage 설정 인덱스 설계 인덱스형태결정 컬럼 순서의 결정 뷰 설계 데이터베이스 구축 데이터베이스 생성 테이블스페이스 생성 사용자 /역할 생성 오브젝트 생성 트랜잭션의 분석 테이블스페이스설계반정규화테이블반정규화컬럼반정규화 용량산정 산출물의 작성 . 트랜잭션 분석서 . 테이블 정의서 . 인덱스 정의서 . 뷰 정의서 . 테이블스페이스 정의서 . 데이터파일 용량 산정서 . 디스크 용량 산정서 클러스터 설계 Partition 설계 디스크 구성 설계 2.4.1 시스템 구성 설계 2.4 물리 데이터 설계의 방법 시스템 분석 장비 OS CPU Memory Disk 구성 System Tablespace Rollback Segment Data Buffer Cache 2.4.2 트랜잭션 분석 2.4 물리 데이터 설계의 방법 □ 트랜잭션 : 각각의 업무에서 처리하는 업무의 기본단위 □ 단위 프로세스와 Crud matrix 를 이용하여 트랜잭션 분석서 작성 □ 트랜잭션 당 각 테이블을 참조하는 횟수를 기록 □ 단위 프로세스가 주기별로 발생하는 트랜잭션을 기록 □ 주기별로 발생하는 총 트랜잭션을 산출 □ 데이터베이스 용량산정 : 테이블에 저장되는 데이량을 유추 □ 디스크 구성의 근거자료 : 집중되는 트랜잭션의 해당하는 테이블의 I/O 를 줄이기 위해 데이터 파일의 분산을 고려 할 수 있다. 2.4.3 용량산정 2.4 물리 데이터 설계의 방법 □ 용량분석의 목적 . 정확한 용량을 산정하여 디스크 사용의 효율을 높인다 . 업무량이 집중되어 있는 디스크를 분리하여 설계함으로써 집중화 된 디스크에 대한 입출력 부하를 분산 . 입출력 경합을 최소화하여 데이터의 접근 성능을 향상 . 데이터베이스 오브젝트의 익스텐트 발생을 줄인다 □ 용량분석의 절차 . 용량 분석을 위한 기초 데이터 수집 . 기초 데이터를 이용한 DBMS 에 이용하는 오브젝트 별로 용량을 산정 . 디스크 용량 산정 □ 테이블 용량 계산 . 총 블록 헤드 계산 . 블록 당 가능한 데이터 영역 계산 . 평균 Row 의 전체 길이 계산 . 총 평균 Row 크기 계산 . 데이터 블록 내의 평균 Row 수 계산 . 테이블에 요구하는 블록과 바이트 수를 계산 □ 인덱스 용량 계산 . 총 블록 헤더 계산 . 블록당가능한데이터영역계산 . 결합된 열 길이 계산 . 인덱스 값 크기의 전체 평균 계산 . 인덱스 값 크기의 전체 평균 계산 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.5 반정규화 . 테이블의 반정규화 2.4 물리 데이터 설계의 방법 □ 테이블의 병합 . 1:1 관계의 병합 . 1:M 관계의 병합 . Super type 과 Sub Type 의 병합 □ 테이블의 분할 . 수직분할 (특정 속성들만 집중 질의) . 수평분할 (특정 범위들만 집중 질의) . 지역별 데이터 분산과 데이터 서버별 분산 . 특징 . 전체적인 스캔의 범위가 감소 . 테이블의 Locking 과 경합이 감소 . SQL 문이 복잡 . 전체 테이블을 조회 시 처리 속도 감소 □ 테이블의 추가 . 통계 테이블의 추가 . 이력 테이블의 추가 2.4.5 반정규화 . 컬럼의 반정규화 2.4 물리 데이터 설계의 방법 □ 중복 컬럼의 추가 . 자주 사용하는 컬럼의 중복 . 데이터를 조회하는 경로를 단축하기 위한 컬럼의 중복 . 컬럼에 의한 파생 컬럼의 추가 . 로우에 의한 파생 컬럼의 추가 . 이력 데이터 모델의 컬럼 추가 파생 컬럼의 추가 최신 정보를 나타내는 컬럼 추가 조회경로 단축 2.4.6 테이블 설계(1) 2.4 물리 데이터 설계의 방법 □ Entity 를 table 로 . 일반적으로 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.6 테이블 설계(2) 2.4 물리 데이터 설계의 방법 □ Constraint 설정 장점 . 데이터베이스 성능을 향상 . 선언과 변경이 가능 . 활성화/ 비활성화 할 수 있다. . 요구사항과 Rule 의 통제가 가능 □ Constraint 의 종류 . Not Null . Unique . 컬럼 Check 조건 . 테이블 Check 조건 . Primary Key . Foreign Key 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) 2.4.7 인덱스 설계(2) 2.4 물리 데이터 설계의 방법 □ 인덱스 컬럼은 수정이 자주 발생하지 않는 컬럼을 선정 □ 분포도가 기형적 불균형인 인덱스는 설정 재고(NULL 의 사용) □ 데이터의 DML 작업이 많은 테이블의 경우 인덱스의 개수 5개 이하 □ CHAR . VARCHAR2 □ Date 타입은 ‘=‘ 연산의 경우가 드물다 Substr, Like 연산이 불가능 □ 결합인덱스 . 컬럼 순서가 중요 . 접근 경로가 ‘=‘ 연산이 가능한 컬럼이 앞으로 . 분포도 좋은 컬럼이 앞으로 . 정렬이 자주 발생하는 컬럼이 앞으로 2.4.8 뷰 설계 2.4 물리 데이터 설계의 방법 □ 테이블 구조의 단순화 □ 다양한 관점에서의 데이터를 제시 □ 데이터의 보안 유지 □ 논리적인 데이터의 독립성 유지 □ 뷰의 대상 선정 . 인터페이스 정의서의 외부 시스템과 인터페이스 관여하는 테이블 . Crud Matrix 를 통해 여러 테이블을 동시에 자주 조인하는 접근하는 테이블 . SQL문 작성시 거의 모든 문자에서 인라인 뷰 방식으로 접근하는 테이블 2.4.9 클러스터 설계 2.4 물리 데이터 설계의 방법 □ 지정된 컬럼 값의 순서대로 데이터 행을 저장하는 방법 □ 하나 혹은 그 이상의 테이블을 같은 클러스터내 저장 가능 □ 액세스 기법이 아니라 액세스 효율 향상을 위한 물리적 저장 방법 □ 검색 효율은 높여주나 입력, 수정, 삭제 시는 부하 증가 □ 분포도가 넓을 수록 오히려 유리 (인덱스의 단점을 해결) □ 분포도가 넓은 테이블의 클러스터링은 저장 공간의 절약 □ 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) 2.4.10 Partition 설계 . Partition Key 의 선정 2.4 물리 데이터 설계의 방법 □ "I/O 분산을 어떻게 할 것인가?" □ 액세스 유형에 따라 Partitioning 이 이루어질 수 있도록 파티션 Key 를 선정 □ 이력 데이터의 경우에는 생성주기 또는 소멸주기가 파티션과 일치 . 고객 . 고객유형(CRM) . 가입계약상태 . 종료일자 . 가입서비스 -종료일자 . 청구내역 . 청구년월 . 청구상세내역 . 미납여부 + 청구년월 . 수납 -수납처리일자 2.4.10 Partition 설계 . 인덱스 설계 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.11 디스크 구성 설계 2.4 물리 데이터 설계의 방법 디스크 구성 디스크 1 디스크 2 디스크 3 디스크 4 디스크 4 Oracle executables Data data files 2 디스크 Index data files Redo logs Export files Control file Rollback segment file Temporary user data files Archive log files Control file Oracle executables Data data files Archive log files Redo logs Temporary user data Index data files 3 디스크 Rollback segment file Export files files Control file Control file Control file Oracle executables Data data files Index data files Archive log files Index data files Temporary user files Control file Rollback segment 4 디스크 Redo logs Export files Control file file Control file Oracle executables Data data files Index data files Rollback segment Archive log files Redo logs Temporary user files Control file file 5 디스크 SYSTEM tablespace data files Control file Export files Control file 3.2 산출물 예제(1) 3. 데이터 모델링의 산출물의 작성 □ 엔터티 정의서 엔터티명 엔터티 설명 동의어 유의어 엔터티 구분 관련 속성 비고 도서 인터넷을 통해 판매하고자 하는 책의 정보 책 기본 도서명, 도서번호 회원 인터넷을 통해 등록한 회원의 정보 일반회원 기본 회원번호, 주민번호, 주소, 전화번호, 전 자메일, 휴대폰 주문 도서를 구매하기 위해 회원이 입력한 배송지, 결재방법에 관한 정보 주문서, 주 문내역 중심 주문번호, 주문일자, 배송방법, 결재방법 포인트 도서구입이나 이벤트 행사에 의해서 주어지는 혜택으로서 양적으로 주어지는 가상 금액 중심 구매포인트 주문목록 회원이 주문한 도서목록에 대한 수량과 가격 구매도서목 록 행위 수량,단가 □ 관계 정의서 기준 엔터티 관계형태 참여방법 관련 엔터티 사원 각각의 사원은 한 부서에 속한다 각 부서에는 여러 명의 사원이 존재할 수 있다 필수 선택 부서 각각의 사원은 여러 개의 주문을 접수 할 수 있다 각각의 주문은 한명의 사원에 의해서만 접수된다 선택 필수 주문 3.2 산출물 예제(2) 3. 데이터 모델링의 산출물의 작성 □ 도메인 정의서 도메인 구분 도메인명 도메인 타입 비고 번호 계약번호 사업자번호 도서번호 배송번호 전화번호 정산번호 주문번호 회원번호 계좌번호 주민번호 우편번호 Number Varchar2(20) Number Number Varchar2(20) Number Number Number Varchar2(20) Varchar2(13) Varchar2(6) ‘-‘포함 ‘-’ 포함 ‘-’ 제외 ‘-’ 제외 ‘-’ 제외 코드 계약상태코드 배송결과코드 배송구분코드 결재방법코드 배송방법코드 거래은행코드 Varchar2(2) Varchar2(2) Varchar2(2) Varchar2(2) Varchar2(2) Varchar2(2) 날짜 일자(V) 일자(D) Varchar2(8) Date 비율 비율 Number(3) 3.2 산출물 예제(3) 3. 데이터 모델링의 산출물의 작성 □ 주제영역 정의서 주제영역 설 명 비고(담당자) 주문 -인터넷(?), 회원, 주문, 도서, 도서목록(?), 주문목록, 포인트, 체크도 서 이벤트 -이벤트, 포인트, 주문 구매 -출판사, 계약, 공급, 도서, 재고, 도서분류, 신간, 공급도서 배송 -배송, 택배업체, 배송업체, 지하철 배송, 지하철 상점, 정산 □ 용어사전 정의서 용어 용어설명 영문명 약어 비고 계좌 은행 등에 예입하기 위해 만든 예금 계좌 account acct 계좌번호: acct_no 가격 물건의 가격을 나타냄 Amount amt 3.2 산출물 예제(4) 3. 데이터 모델링의 산출물의 작성 □ 테이블 정의서 업무영역 사원 사용자 SCOTT 테이블 스페이스 TSTEST01 테이블명 사원 테이블 ID EMP Pctused 70 Pctfree 50 항목명칭 항목ID 타입 길이 NN PK FK 비고 사원번호 EMPNO VARCHAR2 6 V V 사원명 EMPNM VARCHAR2 40 V 주민번호 JUMINNO VARCHAR2 13 V 부서번호 DEPTNO VARCHAR2 2 V V 3.2 산출물 예제(5) 3. 데이터 모델링의 산출물의 작성 □ 트랜잭션 분석서 EP명 번호 테이블명 컬럼 CRUD 트랜잭션당 트랜잭션수 주기 1 고객 고객번호,고객명 R 1 200 일제품주문을 신청한다 2 주문 주문번호, 주문일자, 고객번호 C 1 200 3 주문목록 주문번호, 제품번호, 단가 C 5 1,000 4 제품 제품번호, 제품명, 재고량 R 5 1,000 □ 뷰 정의서 뷰 명 뷰 설명 관련테이블 컬럼명 데이터 타입 V_EMP 회계 시스템과 인터페이스 EMP EMPNO EMPNM HIREDATE Varchar2(6) Varchar2(40) DATE V_ORDER ITEM 주문과 주문목록을 함께 처리 ORDER ORDERNO ORDERNM ORDERDATE Varchar2(6) Varchar2(40) DATE ORDERITEM ITEMNO PRICE Varchar2(6) Number(10) 3.2 산출물 예제(6) 3. 데이터 모델링의 산출물의 작성 □ 인덱스 정의서 엔터티 테이블 인덱스 컬럼명 타입 테이블스페이스 인덱스유형 정렬 구분 부서 DEPT I_DEPT01 DEPTNO NUMBER(2) ISTEST01 UNIQUE ASC PK I_EMP01 EMPNO VARCHAR2(6) ISTEST01 UNIQUE ASC PK 사원 EMP I_EMP02 EMPNO VARCHAR2(6) ISTEST01 NOT UNIQUE DESC INDEX HIREDATE VARCHAR2(8) □ 용량산정내역 엔터티 테이블 Row 길이 (Byte) 보존기간 초기건수 (건) 발생건수 (건) 발생주 기 년증가 율 사용자코드 CODE 44 10년 2,500 100 년 5건 사원 EMP 120 10년 8, 021 80 월 50건 부서 DEPT 44 10년 2,330 10 분기 10건 제품 ITEM 260 5년 3,211,874 3,700 일 200건 3.2 산출물 예제(7) 3. 데이터 모델링의 산출물의 작성 □ 테이블 스페이스 용량 설계서 테이블 용량 테이블 스페이스 테이블 스페이스 용량(40% 확장고려 ) 데이터 파일명 TABLE1 TABLE2 TABLE3 30M 20M 100M TS01 150M + 60M = 210M DF001.DBF TABLE5 TABLE6 TABLE7 10M 5M 100M TS02 115M + 46M = 161M DF002.DBF01 DF002.DBF02 □ 데이터 파일 용량 설계서 디스크 데이터파일 디렉토리 데이터파 일 데이터파일용 량 테이블스페 이스 테이블스페이 스 용량 비고 DF001.DB TS001 210M /DISK1 /DISK1/ORADATA/ORA8/D F01 320M TS002 100M B1 DF002.DB F01 500M TS002 861M DF002,DF01 과 공유 /DISK2 /DISK2/ORADATA/ORA8/D B1 DF001.DB F02 110M TS003 100M DF002.DB F02 500M TS002 861M 3.2 산출물 예제(8) 3. 데이터 모델링의 산출물의 작성 □ 디스크 용량 설계서 디스크 데이터파일 디렉토리 디스크용량 사용된 디스 크용량 디스크사 용비율 데이터파일명 데이터파일용량 /DISK1 /DISK1/ORADATA/ORA8/D B1 2G 820M 41% DF001.DBF001 320M DF002.DBF001 500M DF001.DBF02 500M /DISK2 /DISK2/ORADATA/ORA8/D B1 2G 1510M 75% DF001.DBF02 500M DF001.DBF02 110M DF001.DBF02 900M