[정보처리기사 실기] 3장 요약 키워드 정리 _ 데이터 입출력 구현

[3] 데이터 입출력 구현

3.1 데이터 모델의 개념

데이터모델의 정의

- 현실 세계의 정보들을 컴퓨터에 표현하기 위해서 단순화, 추상화하여 체계적으로 표현한 개념적 모형

- 데이터, 데이터와의 관계, 데이터의 의미 및 일관성, 제약 조건 등을 기술하기 위한 개념적 도구들의 모임

- 현실 세계를 데이터베이스에 표현하는 중간 과정(데이터베이스 설계 과정)에서 데이터의 구조를 논리적으로 표현하기 위해 사용되는 지능적 도구.

- 데이터 모델 구성 요소: 개체, 속성, 관계

- 데이터 모델 종류: 개념적 데이터 모델, 논리적 데이터 모델, 물리적 데이터 모델(실제 컴퓨터에 데이터가 저장되는 방법을 정의하는 물리 데이터베이스 설계 과정)

- 데이터 모델에 표시할 요소: 구조, 연산, 제약조건

 

데이터모델의 구성요소

- 개체: 데이터베이스에 표현하려는 것으로 사람이 생각하는 개념이나 정보 단위 같은 현실 세계의 대상체.

- 속성: 데이터의 가장 논리적 단위로서 파일 구조상의 데이터 항목 또는 데이터 필드에 해당됨.

- 관계: 개체 간의 관계 또는 속성 간의 논리적 연결을 의미

 

개념적 데이터모델

- 현실 세계에 대한 인간의 이해를 돕기 위해 현실 세계에 대한 인식을 추상적 개념으로 표현하는 과정

- 속성들로 기술된 개체 타입과 이 개체 타입들 간의 관계를 이용하여 현실 세계를 표현함.

- 현실 세계에 존재하는 개체를 인간이 이해할 수 있는 정보 구조로 표현하기 때문에 정보 모델이라고도 함.

- 대표적 개념적 데이터 모델: E-R 모델

 

논리적 데이터모델

- 개념적 모델링 과정에서 얻은 개념적 구조를 컴퓨터가 이해하고 처리할 수 있는 컴퓨터 세계의 환경에 맞도록 변환하는 과정.

- 필드로 기술된 데이터 타입과 이 데이터 타입들 간의 관계를 이용하여 현실 세계를 표현.

- 단순히 데이터 모델이라고 하면 논리적 데이터 모델을 의미

- 특정 DBMS는 특정 논리적 데이터 모델 하나만 선정하여 사용함.

- 논리적 데이터 모델의 종류 (데이터 간의 관계를 어떻게 표현하느냐에 따라): 관계 모델, 계층 모델, 네트워크 모델

 

논리적 데이터모델의 품질 검증

- 완성된 논리 데이터 모델이 기업에 적합한지를 확인하기 위해 품질을 검증하는 것.

- 논리 데이터 모델의 품질 기준: 정확성, 완전성, 준거성, 최신성, 일관성, 활용성 등

- 기준에 따라 개체, 속성, 관계, 식별자, 모델 전반 등에 대하여 검토 체크 리스트를 작성하고 체크 리스트의 각 항목을 확인하는 방식으로 검증함.

- 개체 품질 검증 항목: 단순 명사 여부, 개체의 주 식별자, 개체 간 상호 배타성, 개체의 정규화 여부, 개체 상세 정의, 개체 관리 업무 기능, 개체에 2개 이상의 속성 존재 여부, 개체의 총 길이, 개체 동의어 여부, 개체 분산 요구 등

- 속성 품질 검증 항목: 단순 면사 여부, 속성 값 존재 여부 및 개수, 도메인 정의, 반복되는 속성, 그룹화 가능 속성, 주 식별자 및 비 식별자에 의존하는 속성, 다치 종속(A,B,C 3개의 속성을 가진 테이블 R에서 어떤 복합 속성(A,C)에 대응하는 B 값의 집합이 A값에만 종속되고 C값에는 무관하면, B는 A에 다치 종속) 속성 등

- 관계 품질 검증 항목: 관계의 명칭, 2개 이상의 노드와 관계 존재 여부, 노드의 기수성(관계에 참여하는 각 개체에 대하여 가능한 관계의 수를 의미하며, 관계 차수)과 선택성(관계가 필수인지 선택인지를 표현하는 것, 관계 선택 사양), 필수적 관계, 유효한 관계, 중복된 관계, 외부 식별자 존재 여부, 참조 무결성 여부

- 식별자 품질 검증 항목: 식별자의 명칭, 정의, 구성, 정합성, 크기, 순서

- 전반적인 품질 검증 항목: 주제 영역 구성의 적절성, 데이터 모델 상에 정규화 여부, 다대다 관계 해소 여부, 이력 관리 대상 선정 확인, 이력 관리 방법의 적절성 확인

 

데이터모델에 표시할 요소

- 구조: 논리적으로 표현된 개체 타입들 간의 관계로 데이터 구조 및 정적 성질을 표현함.

- 연산: 데이터베이스에 저장된 실제 데이터를 처리하는 작업에 대한 명세, 데이터 베이스를 조작하는 기본 도구.

- 제약 조건: 데이터베이스에 저장될 수 있는 실제 데이터의 논리적 제약 조건.

 

3.2 이상/함수적 종속/정규화

이상(Anomaly)

- 테이블에서 일부 속성들의 종속으로 인해 데이터의 중복이 발생하고, 이 중복으로 인해 테이블 조작 시 문제가 발생하는 현상

- 종류: 삽입 이상, 삭제 이상, 갱신 이상

- 삽입 이상

• 데이터를 삽입할 때 의도와는 상관없이 원하지 않은 값들로 인해 삽입할 수 없게 되는 현상.
• 예제) 학번이 600인 학생의 학년 2이라는 사실만 삽입하려고 할때 삽입 이상 발생 > 테이블의 기본키는 학번과 과목번호이기 때문에 무조건 과목 번호가 있어야 함.

- 삭제 이상

• 한 튜플을 삭제할 때 의도와는 상관없는 값들도 함께 삭제되는, 연쇄 삭제가 발생하는 현상
• 예제) 학번이 200, 과목번호 C123의 등록을 최소하면 삭제 이상 발생 > 그 학생의 학년 정보까지 삭제되어 정보 손실 발생

- 갱신 이상

• 튜플에 있는 속성 값을 갱신할 때 일부 튜플의 정보만 갱신되어 정보에 불일치성이 생기는 현상
• 예제) 학번 400인 학생을 학년을 4에서 3으로 변경할 때 갱신이상 발생 > 실수로 일부만 갱신하면 3, 4 2가지 값을 갖게 되어 불일치성이 생김

함수적 종속(Functional Dependency)

- 어떤 테이블 R에서 X와 Y를 각각 R의 속성 집합의 부분 집합이라고 하자. 속성 X의 값 각각에 대해 시간과 관계없이 항상 Y의 값이 오직 하나만 연관되어 있을 때, Y는 X에 함수적 종속 / X가 Y를 함수적으로 결정한다고 보고 X → Y 로 표기한다.

- 함수적 종속은 데이터의 의미를 표현하는 것으로 현실 세계를 표현하는 제약 조건이 되는 동시에 데이터베이스에서 항상 유지되야 할 조건임.

- 예제1) 학생 테이블에서 이름, 학년, 학과는 각각 ‘학번’ 속성에 함수적 종속이다.

• 학번 → 이름, 학년, 학과

- X → Y의 관계를 갖는 속성 X와 Y에서 X를 결정자라고 하고, Y를 종속자라고 함. 예를들어 학번→ 이름에서는 학번이 결정자, 이름이 종속자이다.

- 예제2)

• 성적은 (학번, 과목번호)에 완전 함수적 종속.
• 학년은 (학번, 과목번호)에 완전 함수적 종속이 아니므로 부분 함수적 종속.

완전/부분 함수적 종속

- 완전 함수적 종속

• 어떤 테이블 R에서 속성 Y가 다른 속성 집합 X에 대해 함수적 종속이면서, 속성 집합 X의 어떠한 진부분 집합 Z(즉, Z X)에도 함수적 종속이 아닐때, 속성 Y는 속성집합 X에 완전 함수적 종속이다.
• 어떤 속성이 기본키에 대해 완전히 종속적일 때
• 예) 성적은 학번과 과목번호가 같을 경우 항상 같은 성적이기 때문에 성적은 학번과 과목번호에 의해서만 결정되므로 성적은 기본키(학번, 과목번호)에 완전 함수적 종속임.

- 부분 함수적 종속

• 어떤 테이블 R에서 속성 Y가 다른 속성 집합 X 전체에 대해 함수적 종속이면서 속성 집합 X의 임의의 부분집합에 대해 함수적 종속일 때, 속성 Y는 집합 X에 부분 함수적 종속이라고 함.
• 예) 학년은 과목 번호에 관계 없이 학번이 같으면 항상 같은 학년이 오므로, 즉 기본키의 일부인 학번에 의해서 학년이 결정되므로 학년은 부분 함수적 종속임.

정규화의 개념

- 테이블의 속성들이 상호 종속적인 관계를 갖는 특성을 이용하여 테이블을 무손실분해하는 과정.

- 무손실 분해

• 테이블 R의 프로젝션(특정 테이블에서 일부 속성들만 추출하여 만든 테이블)인 R1, R2가 Natural Join을 통해 원래의 테이블 R로 정보 손실 없이 복귀되는 경우 R은 R1과 R2로 무손실 분해되었다고 함.
• 원래대로 복원될 수 있는 분해

- 정규화의 목적은 가능한 한 중복을 제거하여 삽입, 삭제, 갱신 이상의 발생 가능성을 줄이는 것.

- 정규형 종류: 제 1정규형(1NF), 제 2정규형(2NF), 제 3정규형(3NF), 제 4정규형(4NF), 제 5정규형(5NF) - 순서대로 정규화의 정도가 높아짐

정규화 과정

(예제) <주문목록> 테이블 - 기본키: 제품번호

- 제 1정규형(1NF)

• 테이블 R에 속한 모든 속성의 도메인이 원자값만으로 되어있는 정규형
• 테이블의 모든 속성 값이 원자값으로만 되어있는 정규형
• <주문 목록>에서는 하나의 제품에 대해 여러 개의 주문 관련 정보(주문번호, 고객번호, 주소, 주문수량)가 발생하고 있다. 따라서 제1정규형이 아니다.
• 1차 정규화 과정으로 생성된 <제품 주문> 테이블의 기본키는 (주문번호, 제품번호)이고 다음과 같은 함수적 종속이 존재함.
• 주문번호, 제품번호 -> 고객번호, 주소, 주문 수량
• 주문 번호 -> 고객 번호, 주소
• 고객번호 -> 주소

- 제 2정규형(2NF)

• 테이블 R이 제1정규형이고, 기본키가 아닌 모든 속성이 기본키에 대해 완전 함수적 종속을 만족하는 정규형. (부분 함수적 종속 제거)
• 앞의 <제품주문> 테이블에는 기본키인 주문번호, 제품 번호에 완전 함수적 종속이 되지 않는 속성이 존재함. 주문 수량은 기본키에 대해 완전 함수적 종속이지만, 고객번호와 주소는 주문 번호에 의해서도 결정될 수 있으므로 기본키에 대해 완전 함수적 종속이 아님.

• 제 2정규화 과정을 거쳐 생성된 <주문> 테이블의 기본키는 주문 번호이고 다음과 같은 함수적 종속들이 존재함.
• 주문번호-> 고객번호, 주소
• 주문번호, 제품번호 -> 주문 수량

- 제 3정규형(3NF)

• 테이블R이 제2정규형이고 기본키가 아닌 모든 속성이 기본키에 대해 이행적 함수적 종속을 만족하지 않는 정규형. (이행적 함수적 종속 제거)
• 이행적 함수적 종속: A-> B이고 B->C 일때 A->C를 만족하는 관계.
• <주문> 테이블에서 고객 번호가 주문 번호에 함수적 종속이고, 주소가 고객 번호에 함수적 종속이므로 주소는 기본키인 주문번호에 대해 이행적 함수적 종속을 만족함. 주문번호 -> 고객번호, 고객번호 -> 주소 이므로 주문번호 -> 주소. 제3정규형이 아님!
• <주문> 테이블 제 3 정규화 후

- BCNF

• 테이블R에서 모든 결정자가 후보키인 정규형. (결정자이면서 후보키가 아닌 것 제거)
• 일반적으로 제3정규형에 후보키가 여러개 존재하고 이러한 후보키들이 서로 중첩되어 나타나는 경우에 적용 가능.

• <수강교수> 테이블은 {학번, 과목명 -> 담당교수 / 학번, 담당교수 -> 과목명, 담당 교수 -> 과목명}을 만족하고 있다. <수강 교수>의 후보키는 (학번, 과목명)과 (학번, 담당교수)이다.
• <수강교수>테이블에서 결정자 중 후보키가 아닌 속성이 존재. 담당교수 -> 과목명이 존재하는데 담당교수가 후보키가 아니므로 BCNF가 아님
• <수강교수>를 BCNF 정규화 한 후(담당교수 -> 과목명을 분리해 냄)

- 제 4정규형(4NF)

• 테이블R에 다중값 종속(다치 종속) A↠B이 존재할 경우 R의 모든 속성이 A에 함수적 종속 관계를 만족하는 정규형
• 다치 종속: A, B, C 3개의 속성을 가진 테이블 R에서 어떤 복합 속성 (A, C)에 대응하는 B값의 집합이 A 값에만 종속되고 C값에는 무관하면, B는 A에 다치 종속이라고 하고 A↠B로 표기함.

- 제 5정규형(5NF)

• 테이블R의 모든 조인종속이 R의 후보키를 통해서만 성립되는 정규형.
• 조인종속: 테이블R의 속성에 대한 부분집합 X, Y, … , Z가 있을 때 테이블 R이 자신의 프로젝션 X, Y, … , Z 를 모두 조인한 결과와 동일한 경우 테이블 R은 조인종속을 만족한다고 함.

정규화 과정 정리

두부이걸다줘? = 도부이결다조

- 도메인이 원자값

- 부분 함수적 종속 제거

- 이행적 함수적 종속 제거

- 결정자이자 후보키가 아닌 것 제거

- 다치 종속

- 조인 종속성

 

3.3 논리 데이터 모델의 물리 데이터 모델로 변환

테이블

- 데이터를 저장하는 데이터베이스의 가장 기본적인 오브젝트

- 테이블은 칼럼(열)과 로우(행)으로 구성되며, 칼럼에는 지정된 유형에 따라 데이터가 저장됨.

- 테이블의 구성요소

• 로우: 튜플, 인스턴스, 어커런스
• 칼럼: 각 속성 항목에 대한 값 저장
• 기본키: 후보키 중에서 선택한 주 키
• 외래키: 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합. 한 릴레이션에 속한 A와 참조 릴레이션의 기본키인 B가 동일한 도메인 상에 정의 되었을 때, 속성 A를 외래키라고 함.

- 변환 규칙

논리적 설계(데이터 모델링)	물리적 설계
엔티티	테이블
속성	칼럼
주 식별자	기본키
외부 식별자	외래키
관계	관계

엔티티를 테이블로 변환

- 논리 데이터 모델에서 정의된 엔티티를 물리 데이터 모델로 변환하는 것.

- 엔티티를 테이블로 변환한 후 테이블 목록 정의서를 작성

- 테이블 목록 정의서: 전체 테이블을 목록으로 요약 관리하는 문서.

- 엔티티를 테이블로 변환

- 변환시 고려 사항

• 일반적으로 테이블과 엔티티 명칭은 동일하게 하는 것을 권고함.
• 엔티티는 주로 한글명을 사용하지만 테이블은 소스코드의 가독성을 위해 영문명 사용
• 메타 데이터 관리 시스템에 표준화된 용어가 있을 때는 메타에 등록된 단어를 사용하여 명명함

슈퍼타입/서브타입을 테이블로 변환

- 슈퍼타입과 서브타입은 논리 데이터 모델에서만 사용하는 형태이므로 테이블로 변환해야 함.

- 방법: 슈퍼타입 기준 테이블 변환, 서브 타입 기준 테이블 변환, 개별 타입 기준 테이블 변환

- 슈퍼타입 기준 테이블 변환

• 서브 타입을 슈퍼 타입에 통합하여 하나의 테이블로 만드는 것.
• 서브 타입에 속성이나 관계가 적을 경우 적용.
• 하나로 통합된 테이블에는 서브타입의 모든 속성이 포함되어야 함.
• 장점
  • 데이터의 엑세스가 상대적으로 용이
  • 뷰를 사용해 각각의 서브 타입만을 엑세스하거나 수정 가능
  • 서브타입 구분이 없는 임의 집합에 대한 처리가 용이
  • 여러 테이블을 조인하지 않아도 되므로 수행 속도가 빨라짐
  • SQL 문장 구성이 단순해짐
• 단점
  • 테이블의 칼럼이 증가하므로 디스크 저장 공간 증가함
  • 처리마다 서브타입에 대한 구분이 필요한 경우가 많이 발생함
  • 인덱스의 크기 증가로 인덱스의 효율이 떨어짐.

- 서브타입 기준 테이블 변환

• 슈퍼타입 속성들을 각각의 서브타입에 추가하여 서브타입들을 개별적인 테이블로 만드는 것.
• 서브 타입에 속성이나 관계가 많이 포함된 경우 적용
• 장점
  • 각 서브타입 속성들의 선택 사양이 명확한 경우에 유리
  • 처리할때마다 서브타입의 유형을 구분할 필요가 없음
  • 여러 개의 테이블로 통합하므로 테이블당 크기가 감소하여 전체 테이블 스캔시 유리함. (스캔: 앞에서부터 순차적으로 행을 비교하여 원하는 데이터를 가져오는 방식)
• 단점
  • 수행 속도 감소할 수 있음.
  • 복잡한 처리를 하는 SQL의 통합이 어려움
  • 부분 범위에 대한 처리가 곤란해짐
  • 여러 테이블을 통합한 뷰는 조회만 가능
  • 식별자의 유지 관리가 어려움

- 개별 타입 기준 테이블 변환

• 슈퍼타입과 서브타입들을 각각 개별적인 테이블로 변환하는 것.
• 슈퍼 타입과 서브 타입 테이블들 사이에은 각각 1:1관계가 형성됨
• 개별 타입 기준 테이블을 적용하는 경우
• 전체 데이터에 대한 처리가 빈번한 경우
• 서브타입의 처리가 대부분 독립적으로 발생하는 경우
• 통합하는 테이블의 칼럼수가 많은 경우
• 서브 타입의 칼럼 수가 많은 경우
• 크랜잭션이 주로 슈퍼타입에서 발생하는 경우
• 슈퍼타입의 처리 범위가 넓고 빈번하게 발생하여 단일 테이블 클러스터링이 필요한 경우
• 장점: 저장 공간이 상대적으로 작음. 슈퍼 타입 또는 서브 타입 각각의 테이블에 속한 정보만 조회하는 경우 문장 작성이 용이함
• 단점: 슈퍼타입과 서브타입의 정보를 같이 처리하면 항상 조인이 발생하여 성능이 저하됨.

속성을 컬럼으로 변환

- 일반 속성 변환

• 속성과 칼럼은 명칭이 반드시 일치할 필요는 없으나 개발자와 사용자 간 의사소통을 위하여 가능한 한 표준화된 약어를 사용하여 일치시키는 것이 좋다.
• 칼럼은 sql의 예약어 사용을 피한다.
• 칼럼은 sql의 가독성을 높이기 위해 가능한 한 짧게 지정한다.
• 복합 단어를 칼럼명으로 사용할 때는 미리 정의된 표준을 따름.
• 테이블의 칼럼을 정의한 후에는 한 로우에 해당하는 샘플 데이터를 작성하여 칼럼의 정합성을 검증함.

- Primary UID(주식별자)를 기본키로 변환

- Primary UID(관계의 UID Bar)를 기본키로 변환

• UID Bar: 엔티티에 포함된 고유한 속성의 식별자가 아니라 다른 엔티티와의 관계로 인해 생성된 식별자
• 다른 엔티티와의 관계로 인해 생성된 Primary UID는 물리 데이터 모델의 기본키로 만듦.

- Secondary UID를 유니크키로 변환

• 유니크 키: 해당 속성에 입력된 값이 유일하다는 것을 보자하기 위한 제약조건인 유니크 속성이 설정된 키.

관계를 외래키로 변환

- 논리 데이터 모델에서의 관계는 기본키와 이를 참조하는 외래키로 변환함.

- E-R모델을 관계 테이블로 변환하는 방법: 1:1관계, 1:M 관계, N:M 관계

- 1:1 관계: 개체 A의 기본키를 개체 B의 외래키로 추가하거나 개체 B의 기본키를 A의 외래키로 추가하여 표현함.

- 1:M 관계: 개체 A의 기본키를 개체 B의 외래키로 추가하여 표현하거나 별도의 테이블로 표현.

- N:M 관계: 릴레이션 A와 B의 기본키를 모두 포함한 별도의 릴레이션으로 표현함. 이때 생성된 별도의 릴레이션을 교차 릴레이션, 교차 엔티티라고도 함.

- 1:M 순환 관계: 개체 A에 개체 A의 기본키를 참조하는 외래키 컬럼을 추가하여 표현함. 데이터의 계층 구조를 표현하기 위해 주로 사용.

관리 목적의 테이블/칼럼 추가

- 논리 데이터 모델에는 존재하지 않는 테이블이나 칼럼을 데이터베이스의 관리 혹은 데이터베이스를 이용하는 프로그래밍의 수행 속도를 향상시키기 위해 물리 데이터 모델에 추가할 수 있음.

데이터 타입 선택

- 논리 데이터 모델에서 정의된 논리적 데이터 타입을 물리적인 DBMS의 물리적 특성과 성능을 고려하여 최적의 데이터 타입과 데이터 최대 길이를 선택한다.

- 주요 타입은 문자, 숫자, 날짜가 있다.

- Oracle에서 자주 사용되는 유형

• CHAR: 최대 2000Byte 까지 저장 가능
• VARCHAR2: 최대 4000Byte 까지 저장 가능
• NUMBER: 38자릿수의 숫자 저장가능
• DATE: 날짜

3.4 반정규화(Denormalization)

반정규화의 개념

- 시스템의 성능 향상, 개발 및 운영의 편의성을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 과정으로 의도적으로 정규화 원칙을 위배하는 행위.

- 반정규화를 수행하면 시스템의 성능이 향상되고 관리의 효율성은 증가하지만 데이터의 일관성 및 정합성이 저하될 수 있다.

- 과도한 반정규화는 오히려 성능을 저하시킬 수 있다.

- 반정규화를 위해서 사전에 데이터의 일관성과 무결성을 우선으로 할 지, 데이터베이스의 성능과 단순화를 우선으로 할 지를 결정해야한다.

- 반정규화 방법: 테이블 통합, 테이블 분할, 중복 테이블 추가, 중복 속성 추가

 

테이블 통합

- 두 개의 테이블이 조인되는 경우가 많아 하나의 테이블로 합쳐 사용하는 것이 성능 향상에 도움이 될 경우 수행.

- 테이블 통합 종류: 1:1 관계 테이블 통합, 1:N 관계 테이블 통합, 슈퍼타입/서브타입 테이블 통합

- 테이블 통합시 고려사항

• 데이터 검색은 간편하지만 레코드 증가로 인해 처리량이 증가함.
• 테이블 통합으로 인해 입력, 수정, 삭제 규칙이 복잡해질 수 있음.
• Not Null, Default(속성값이 생략되면 기본값 설정), Check (속성값의 범위나 조건 설정)등의 제약 조건을 설계하기 어려움

테이블 분할

- 테이블을 수직 또는 수평으로 분할 하는 것.

- 수평 분할

• 레코드를 기준으로 테이블을 분할하는 것.
• 레코드별로 사용 빈도 차이가 큰 경우 사용 빈도에 따라 테이블을 분할함

- 수직 분할

• 하나의 테이블에 속성이 너무 많을 경우 속성을 기준으로 테이블을 분할하는 것.
• 갱신 위주의 속성 분할: 데이터 갱신 시 레코드 잠금으로 인해 다른 작업을 수행할 수 없으므로 갱신이 자주 일어나는 속성들을 수직 분할하여 사용.
• 자주 조회되는 속성 분할: 테이블에서 자주 조회되는 속성이 극히 일부일 경우 자주 사용되는 속성들을 수직 분할하여 사용.
• 크기가 큰 속성 분할: 이미지나 큰 형식의 속성.
• 보안을 적용해야 하는 속성 분할: 테이블 내의 특정 속성에 대해 보안을 적용할 수 없으므로 수직분할하여 사용

- 테이블 분할 시 고려 사항

• 기본키의 유일성 관리가 어려워짐.
• 데이터 양이 적거나 사용빈도가 낮은 경우 테이블 분할이 필요한지 고려해야 함.
• 분할된 테이블로 인해 수행 속도가 느려질 수 있음
• 데이터 검색에 중점을 두어 테이블 분할 여부를 결정해야 함.

중복테이블 추가

- 여러 테이블에서 데이터를 추출해서 사용해야 하거나, 다른서버에 저장된 테이블을 사용해야 하는 경우 중복 테이블을 추가하여 작업의 효율성을 향상시킬 수 있음.

- 중복 테이블을 추가하는 경우

• 정규화로 인해 수행 속도가 느려지는 경우
• 많은 범위의 데이터를 자주 처리해야하는 경우
• 특정 범위의 데이터만 자주 처리해야 하는 경우
• 처리 범위를 줄이지 않고는 수행 속도를 개선할 수 없는 경우

- 중복 테이블 추가 방법

• 집계 테이블의 추가: 집계 데이터를 위한 테이블을 생성하고, 각 원본 테이블에 트리거를 설정하여 사용하는 것으로 트리거의 오버헤드에 유의해야 함.

• 진행 테이블의 추가: 이력 관리(속성값의 변화를 관리하기 위해 테이블에서 특정 속성값이 변경될 때 마다 이전 값을 저장하는 것) 등의 목적으로 추가하는 테이블로, 적절한 데이터 양의 유지와 활용도를 높이기 위해 기본키를 적절히 설정함.
• 특정 부분만을 포함하는 테이블의 추가: 데이터가 많은 테이블의 특정 부분만을 사용하는 경우 해당 부분만으로 새로운 테이블을 생성.

중복 속성 추가

- 조인해서 데이터를 처리할 때 데이터를 조회하는 경로를 단축하기 위해 자주 사용하는 속성을 하나 더 추가하는 것.

- 중복 속성을 추가하면 데이터의 무결성 확보가 어렵고 디스크 공간이 추가로 필요함.

- 중복 속성을 추가하는 경우

• 조인이 자주 발생하는 속성인 경우
• 접근 경로가 복잡한 속성인 경우
• 엑세스 조건으로 자주 사용되는 속성인 경우
• 기본키의 형태가 적절하지 않거나 여러개의 속성으로 구성된 경우

 

 

3.5 인덱스 설계

인덱스의 개념

- 데이터 레코드를 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조.

- 데이터가 저장된 물리적 구조와 밀접한 관계가 있음.

- 레코드가 저장된 물리적 구조에 접근하는 방법을 제공.

- 인덱스를 통해 파일의 레코드에 대한 엑세스를 빠르게 수행할 수 있음.

- 레코드 삽입과 삭제가 수시로 일어나는 경우에는 인덱스 개수를 최소로 하는 것이 효율적임.

- 인덱스가 없으면 특정 값을 찾기 위해 모든 데이터 페이지를 확인하는 TABLE SCAN이 발생함.

- 기본키를 위한 인덱스를 기본 인덱스라고 하고, 기본 인덱스가 아닌 것은 보조 인덱스라고 함. 대부분의 관계형 데이터베이스 관리 시스템에서는 모든 기본키에 대해서 자동적으로 기본 인덱스를 생성함.

- 레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치되도록 구성되는 인덱스를 클러스터드 인덱스라고 함.

- 종류: 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 비트맵 조인 인덱스, 도메인 인덱스

- 클러스터드 인덱스

• 인덱스 키의 순서에 따라 데이터가 정렬되는 방식.
• 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있음.
• 데이터 삽입, 삭제 발생시 순서를 유지하기 위해 재정렬해야 함.
• 한 개의 릴레이션에 하나의 인덱스만 생성 가능.

- 넌클러스터드 인덱스

• 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식.
• 데이터를 검색하기 위해서 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로 클러스터드 인덱스에 비해 검색 속도가 떨어짐.
• 한 개의 릴레이션에 여러개의 인덱스 만들 수 있음.

트리 기반 인덱스

- 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것.

- 상용 DBMS에서는 트리 구조 기반의 B+ 트리 인덱스를 주로 활용함.

- B 트리 인덱스

• 일반적으로 사용되는 인덱스 방식으로, 루트 노드에서 하위 노드로 키 값을 비교해 나가면서 단말 노드에서 찾고자 하는 데이터를 검색함.
• 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장됨.
• 모든 리프 노드는 같은 레벨에 있음.

- B+ 트리 인덱스

• B 트리의 변형으로 단말 노드가 아닌 노드로 구성된 인덱스 세트와 단말 노드로만 구성된 순차 세트로 구분 됨.
• 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공되며, 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킨다.
• 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드만을 이용한 순차 처리가 가능하다.

비트맵 인덱스

- 인덱스 컬럼의 데이터를 Bit 값인 0또는 1로 변환하여 인덱스 키로 사용하는 방법.

- 비트맵 인덱스의 목적은 키 값을 포함하는 로우의 주소를 제공하는 것.

- 비트맵 인덱스는 분포도가 좋은 칼럼에 적합하며 성능 향상 효과를 얻을 수 있음.

- 분포도, 선택성: 전체 레코드 중 조건에 맞는 레코드 숫자가 적은 경우 분포도가 좋다고 함. 10~15%의 분포도인 경우 효율적인 인덱스 검색을 할 수 있음. 선택성이라고도 함.

- 데이터가 Bit으로 구성되어 있기 때문에 효율적인 논리 연산이 가능하고 저장 공간이 작음.

- 다중 조건을 만족하는 튜플의 개수 계산에 적합함.

- 동일한 값이 반복되는 경우가 많아 압축 효율이 좋음.

 

함수 기반 인덱스

- 컬럼의 값 대신 칼럼에 특정 함수나 수식을 적용하여 산출된 값을 사용하는 것으로 B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용함.

- 데이터를 입력하거나 수정할 때 함수를 적용해야 하므로 부하가 발생할 수 있음.

- 사용된 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 더 큼.

- 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용하게 사용됨.

- 적용 가능한 함수의 종류: 산술식, 사용자 정의 함수, PL/SQL Function, SQL Function, Package, C callout 등

 

비트맵 조인 인덱스

- 다수의 조인된 객체로 구성된 인덱스로, 단일 객체로 구성된 일반적인 인덱스와 엑세스 방법이 다름

- 비트맵 인덱스와 물리적 구조가 동일함.

 

도메인 인덱스

- 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로 확장형 인덱스라고도 함.

- 오라클 버전 8i에서부터 도입된 새로운 개념의 인덱스.

 

인덱스 설계

- 분명하게 드러난 칼럼에 대해 기본적인 인덱스를 먼저 지정한 후 개발 단계에서 필요한 인덱스의 설계를 반복적으로 진행. (데이터나 프로세스 내용에 변경이 많기 때문)

- 인덱스 설계 순서

1. 인덱스의 대상 테이블이나 칼럼 등을 선정
2. 인덱스의 효율성을 검토하여 인덱스 최적화를 수행
3. 인덱스 정의서 작성

인덱스 대상 테이블 선정 기준

- MULTI BLOCK READ (테이블 엑세스 시 메모리에 한번에 읽어들일 수 있는 블록의 수)수에 따라 판단

- 렌덤 엑세스가 빈번한 테이블

- 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블

- 다른 테이블과 순차적 조인이 발생하는 테이블

인덱스 대상 컬럼 선정 기준

- 인덱스 컬럼의 분포도가 10~15% 이내인 칼럼

• 분포도 = (칼럼 값의 평균 Row의 수 / 테이블의 총 Row 수) * 100

- 분포도가 10~15% 이상이어도 부분 처리를 목적으로 하는 칼럼

- 입, 출력 장표 등에서 조회 및 출력 조건으로 사용되는 칼럼

- 인덱스가 자동 생성되는 기본키와 유니크키 제약조건을 사용한 칼럼

- 가능한 한 수정이 빈번하지 않는 칼럼

- ORDER BY, GROUP BY, UNION이 빈번한 칼럼

- 분포도가 좁은 칼럼은 단독 인덱스로 생성

- 인덱스들이 자주 조합되어 사용되는 경우 하나의 결합 인덱스로 생성

- 결합 인덱스

• 한 릴레이션 내 존재하는 여러 칼럼들을 묶어 하나의 인덱스로 만드는 것.
• 칼럼 순서에 따라 엑세스 하는 범위가 달라질 수 있으므로 유의해야 함.
• 우선 순위: 항상 사용되는 칼럼 > = 연산이 되는 칼럼 > 분포도가 좋은 칼럼 > 정렬이 자주 발생하는 칼럼

인덱스 설계 시 고려사항

- 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음.

- 인덱스를 지나치게 많이 만들면 오버헤드 발생

- 넓은 범위를 인덱스로 처리하면 많은 오버헤드 발생

- 인덱스를 만들면 추가적인 저장 공간이 필요함.

- 인덱스와 데이블 데이터의 저장 공간이 분리되도록 설계

 

3.6 뷰 설계

뷰의 개요

- 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가진 가상의 테이블.

- 저장장치에 물리적으로 존재하지는 않지만 사용자에게는 있는 것처럼 간주 됨.

- 데이터 보정 작업, 처리 과정 시험 등 임시적인 작업을 위한 용도로 활용됨.

- 조인문의 사용 최소화로 사용상의 편의성을 최대화 함.

- 뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 생성된 뷰 이름을 질의어에서 사용할 경우 질의어가 실행될 때 뷰에 정의된 기본 테이블로 대체되어 기본 테이블에 대해 실행 됨.

뷰의 특징

- 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며 조작도 기본 테이블과 거의 같음.

- 가상 테이블이기 때문에 물리적으로 구현되어 있지 않음.

- 데이터의 논리적 독립성을 제공할 수 있음.

- 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해짐.

- 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있음.

- 기본 테이블의 기본키를 포함한 속성 집합으로 뷰를 구성해야만 삽입, 삭제, 갱신 연산이 가능함.

 

뷰의 장 단점

- 장점

• 논리적 데이터 독립성을 제공
• 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원해줌
• 사용자의 데이터 관리를 간단하게 해줌
• 접근 제어를 통한 자동 보안이 제공됨

- 단점

• 독립적인 인덱스를 가질 수 없음.
• 뷰의 정의를 변경할 수 없음
• 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신 연산에 제약이 따름

뷰 설계 순서

1) 대상 테이블을 선정함

• 외부 시스템과 인터페이스에 관여하는 테이블
• CRUD 메트릭스를 통해 여러 테이블이 동시에 자주 조인되어 접근되는 테이블
• SQL문 작성 시 거의 모든 문장에서 인라인 뷰(FROM절 안에 사용되는 서브 쿼리) 방식으로 접근되는 테이블

2) 대상 칼럼을 선정: 보안을 유지해야 하는 칼럼은 주의하여 선멸

3) 정의서 작성

 

뷰 설계 시 고려 사항

-테이블 구조가 단순화될 수 있도록 반복적으로 조인을 설정하여 사용하거나 동일한 조건절을 사용하는 테이블을 뷰로 생성.

- 동일한 테이블이라도 업무에 따라 테이블을 이용하는 부분이 달라질 수 있으므로 사용할 데이터를 다양한 관점에서 제시해야 함.

- 데이터의 보안 유지를 고려하여 설계

 

3.7 클러스터 설계

클러스터의 개요

- 데이터 저장 시 데이터 엑세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방법

- 클러스터링키로 지정된 칼럼의 값을 순서대로 저장되고, 여러 개의 테이블이 하나의 클러스터에 저장됨.

- 클러스터링 키

• 클러스터링된 테이블에서 각각의 행을 접근할 때 기분이 되는 열로, 데이터를 조회하면 클러스터링키로 지정된 필드에서 시작하여 클러스터링 테이블의 데이터를 조회함.
• 클러스터링키인 ‘부서번호’ 필드에서 조회를 시작하여 <사원> 테이블의 ‘사원번호’ 순으로 조회.

클러스터의 특징

- 클러스터링 된 테이블은 데이터 조회 속도는 향상 시키지만, 입력 , 수정, 삭제에 대한 성능은 저하시킴.

- 데이터의 분포도가 넓을수록 유리

- 데이터 분포도가 넓은 테이블을 클러스터링 하면 저장 공간을 절약할 수 있음.

- 클러스터링된 테이블은 클러스터링키 열을 공유하므로 저장공간이 줄어듬.

- 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔하는 일이 자주 발생하므로 클러스터링을 하지 않는 것이 좋음.

- 처리 범위가 넓은 경우 단일 테이블 클러스터링, 조인이 많이 발생하는 경우 다중 테이블 클러스터링을 사용.

- 파티셔닝(대용량 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것)된 테이블에는 클러스터링을 할 수 없음.

- 클러스터링을 하면 비슷한 데이터가 동일한 데이터 블록에 저장되기 때문에 디스크 I/O가 줄어듬

- 클러스터링된 테이블에 클러스터드 인덱스를 생성하면 접근 성능이 향상됨.

 

클러스터의 대상 테이블

- 분포도가 넓은 테이블

- 대량의 범위를 자주 조회하는 테이블

- 입력, 수정, 삭제가 자주 발생하지 않는 테이블

- 자주 조인되어 사용되는 테이블

- ORDER BY, GROUP BY, UNION이 빈번한 테이블

 

3.8 파티션 설계

파티션의 개요

- 데이터베이스에서 파티션은 대용량의 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것을 말함.

- 대용량 DB의 경우 중요한 몇개의 테이블에만 집중되어 데이터가 증가되므로, 이런 테이블들을 작은 단위로 나눠 분산시키면 성능 저하를 방지할 뿐만 아니라 데이터 관리도 쉬워짐.

- 테이블이나 인덱스를 파티셔닝 하면 파티션키(파티션을 나누는 기준이 되는 열) 또는 인덱스키에 따라 물리적으로 별도의 공간에 데이터가 저장됨.

- 데이터 처리는 테이블 단위로 이루어지고, 데이터 저장은 파티션별로 수행됨.

파티션의 장단점

- 장점

• 데이터 접근 시 액세스 범위를 줄여 쿼리 성능이 향상됨.
• 파티션별로 데이터가 분산되어 저장되므로 디스크의 성능이 향상됨.
• 파티션별로 백업 및 복구를 수행하므로 속도가 빠름
• 시스템 장애 시 데이터 손상 정도를 최소화 할 수 있음
• 데이터 가용성이 향상됨
• 파티션 단위로 입 출력을 분산시킬 수 있음.

- 단점

• 하나의 테이블을 세분화하여 관리하므로 세심한 관리가 요구됨.
• 테이블간 조인에 대한 비용 증가
• 용량이 작은 테이블에 파티셔닝 수행하면 오히려 성능 저하

파티션의 종류

- 범위 분할

• 지정된 열의 값을 기준으로 분할 (일별 등)

- 해시 분할

• 해시 함수를 적용한 결과 값에 따라 데이터 분할
• 특정 파티션에 데이터가 집중되는 범위 분할의 단점을 보완한 것으로 데이터를 고르게 분산할 때 유용
• 특정 데이터가 어디에 있는지 판단할 수 없음.
• 고객번호, 주민번호 같이 데이터가 고른 칼럼에 효과적

- 조합 분할

• 범위 분할한 다음 해시 함수 적용하여 다시 분할.
• 범위 분할 파티션이 너무 커서 관리가 어려울때 유용

파티션키 선정 시 고려사항

- 테이블 접근 유형에 따라 파티셔닝이 이뤄지도록 선정.

- 데이터 관리의 용이성을 위해 이력성 데이터(수명이 다되어 삭제 되었지만 데이터 관리 규칙에 따라 별도의 저장장치에 보관데는 데이터)는 파티션의 생성 주기와 소멸 주기를 일치시켜야 함.

- 매일 생성되는 날짜 칼럼, 백업의 기준이 되는 날짜 칼럼, 파티션 간 이동이 없는 칼럼, 데이터 분포가 양호한 칼럼 등을 파티션 키로 선정

인덱스 파티션

- 파티션된 테이블의 데이터를 관리하기 위해 인덱스를 나눈 것.

- 파티션된 테이블의 종속 여부에 따른 인덱스 파티션의 종류

• 로컬 파티션 인덱스: 테이블 파티션과 인덱스 파티션이 1:1 대응이 되도록 파티셔닝 함.
• 글로벌 파티션 인덱스: 테이블 파티션과 인덱스 파티션이 독립적으로 구성되도록 파티셔닝함.
• 로컬 파티션 인덱스가 글로벌 파티션 인덱스에 비해 데이터 관리가 쉬움

- 인덱스 파티션키 칼럼의 위치에 따른 인덱스 파티션 종류

• 프리픽스 파티션 인덱스: 인덱스 파티션 키와 인덱스 첫번째 칼럼이 같음
• 논 프리픽스 파티션 인덱스:인덱스 파티션키와 인덱스 첫번째 칼럼이 다름

- 로컬과 글로벌, 프리픽스와 넌프리픽스를 조합하여 구성하여 사용됨. (글로벌 넌프리픽스 제외)

 

3.9 데이터베이스 용량 설계

데이터베이스 용량 설계

- 데이터가 저장될 공간 정의

- 테이블에 저장될 데이터 양, 인덱스, 클러스터 등이 차지하는 공간 등을 예측하여 반영해야 함.

 

데이터베이스 용량 설계의 목적

- 데이터베이스의 용량을 정확히 산정하여 디스크 저장 공간을 효과적으로 사용하고 확장성 및 가용성을 높임.

- 디스크의 특성을 고려하여 설계함으로 디스크의 입 출력 부하를 분산시키고 채널의 병목 현상을 최소화함.

- 디스크에 대한 입 출력 경합이 최소화 되도록 설계 함으로써 데이터 접근성이 향상됨.

- 데이터 접근성을 향상시키는 설계 방법

• 테이블의 테이블 스페이스와 인덱스 테이블 스페이스를 분리하여 구성함
• 테이블스페이스와 임시 테이블스페이스를 분리하여 구성
• 테이블을 마스터테이블과 트랜잭션 테이블로 분류

- 데이터베이스에 생성되는 오브젝트의 익스텐트(기본 용량이 모두 찼을 경우 추가적으로 할당되는 공간 의미) 발생을 최소화하여 성능 향상 시킴.

- 데이터베이스 용량을 정확히 분석하여 테이블과 인덱스에 적합한 저장 옵션을 지정.

 

데이터베이스 용량 분석 절차

1) 데이터 예상 건수, 로우 길이, 보존 기간, 증가율 등 기초 자료를 수집하여 용량을 분석함.

2) 분석된 자료를 바탕으로 DBMS에 이용될 테이블, 인덱스 등 오브젝트별 용량을 산정.

3) 테이블과 인덱스의 테이블 스페이스 용량 산정: 테이블 스페이스에 생성되는 테이블 용량을 모두 더한 값에 약 40%를 추가하여 산정.

4) 데이터베이스에 저장될 모든 데이터 용량과 데이터베이스 설치 및 관리를 위한 시스템 용량을 합해 디스크 용량을 산정함.