과목1: 데이터모델링의 이해

모델링 : 현실세계에 대해서 표현하는 것

- 현실세계를 일정한 형식에 맞춰 표현하는 추상화 의미를 가질 수 있음

- 시스템 구현을 포함한 업무분석 및 업무형상화를 하는 목적

- 복잡한 현실을 제한된 언어나 표기법을 통해 이해하기 쉽게하는 단순화의 의미를 가짐

- 누구나 이해 가능하도록 정확하게 현실을 기술하는 정확화의 의미를 가짐

 

데이터모델링의 목적

- 업무정보를 구성하는 기초가 되는 정보들에 대해 일정한 표기법에 의해 표현함으로써 정보시스템 구축의 대상이 되는 업무 내용을 정확하게 분석하는 것

- 분석된 모델을 가지고 실제 데이터베이스를 생성하여 개발 및 데이터 관리에 사용하기 위한 것

=> 데이터베이스만을 구축하기 위한 용도로 쓰는게 아니라 데이터모델링 자체로서 업무를 설명하고 분석하는 부분에서도 매우 중요한 의미를 가지고 있음

 

데이터 모델링을 할 때, 유의사항 

1. 중복성(Duplication) : 여러 장소에 같은 정보 저장되지 않도록하기

2. 비 유연성(Inflexibility) : 수시로 변화(경)되는 데이터모델의 결합도가 낮아야 서로에게 주는 영향이 적다

3. 비 일관성(Inconsistency) : 연계성이 낮아야 정보를 수정하는데 효율이 좋음

..

 

데이터모델 설계에 따라 사소한 업무변화에도 데이터 모델이 수시로 변경됨으로 유지보수의 어려움을 가중시킬 수 있음. 데이터의 정의를 데이터의 사용 프로세스와 분리함으로써 변화를 일으킬 수 있는 가능성을 줄임 

=> 비유연성

 

개념적 데이터 모델링 : 추상화, 업무중심적, 포괄적인 수준의 모델링, 전사적 데이터 모델링, EA수립시 많이 이용

논리적 데이터 모델링 : 시스템 구축 업무에 대해 Key/속성/관계 등을 정확히 표현, 재사용성이 높음

물리적 데이터 모델링 : 데이터베이스에 이식할 수 있는 성능, 저장 등 물리적인 성격을 고려하여 설계

 

 

모든 사용자 관점을 통합한 조직 전체 관점(=통합관점)의 스키마 구조를 표현한 것 => 개념스키마

통합관점의 뷰를 가지고 있는 개념 스키마를 만들어가는 과정 => 데이터모델링

 

ERD

- ERD작성 순서 : 엔터티 그리기-> 엔터티 배치 -> 엔터티 관계 설정 -> 관계명 기술 -> 관계의 참여도 기술 -> 관계의 필수여부 기술

- 엔터티 배치는 필수사항이 아니지만 가독성 측면에서 가장 중요한 엔터티를 왼쪽 상단에 배치하고 이것을 중심으로 다른 엔터테를 나열하면서 전개하면 사람 눈이 따라가기에 편리한 데이터 모델을 작성 가능

(해당 업무에서 가장 중요한 엔터티는 왼쪽 상단에서 조금 아래쪽 중앙에 배치하여 전체 엔터티와 어울릴 수 있도록 하면 향후 관련 엔터티와 관계선을 연결할 때 선이 꼬이지 않고 효과적으로 배치가능)

 

다음 시나리오에서 엔터티로 적절한 것

S병원은 여러 명의 환자가 존재하고 각 환자에 대한 이름, 주소 등을 관리해야 한다. (단, 엄무범위와 데이터 특성은 상기 시나리오에 기술되어 있는 사항만을 근거하여 판단해야 함)

=> 환자
(2개 이상의 속성 = 이름, 주소 등의 복수 속성
2개 이상의 인스턴스 = 복수 인스턴스 = 여러명의 환자)

 

엔터티 특징

- 반드시 해당 업무에서 필요하고 관리하고자하는 정보

- 유일한 식별자에 의해 식별 가능

- 영속적으로 존재하는 2개 이상의 인스턴스 집합

- 업무 프로세스에 의해 이용될 것

- 반드시 속성을 가질 것

- 다른 엔터티와 최소 한 개 이상의 관계가 있을 것

 

업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더이상 분리되지 않는 최소 데이터 단위 => 속성

 

데이터를 조회할 때 빠른 성능을 할 수 있도록 하기 위해 원래 속성의 값을 계산하여 저장할 수 있도록 만든 속성

=> 파생속성(Derived Attribute)

 

 

데이터모델링에서는 존재적 관계와 행위에 의한 관계를 구분하는 표기법이 없으며,

UML에서는 연관관계와 의존관계에 대해 다른 표기법을 가지고 표현하게 되어 있음.

 

관계 표기법은 관계명, 관계차수, 선택성(선택사양)의 3가지 개념으로 표현

- 관계명(Membership): 관계의 이름

- 관계차수(Cardinality) : 1:1, 1:M, M:N 

- 관계선택사양(Optionality) : 필수관계, 선택관계

 

두개의 엔터티 사이에 정의한 관계를 체크하는 사항들

- 연관 규칙 존재 여부

- 정보 조합 발생 여부

- 업무기술서, 장표에 관계연결을 가능하게 하는 동사(Verb) 여부

- 업무기술서, 장표에 관계연결에 대한 규칙 서술 여부

 

관계 읽기

- 기준(Source) 엔터티를 한 개(One)또는 각(Each)로 읽기

- 대상(Target)엔터티의 관계 참여도 = 개수(하나, 하나 이상)읽기

- 관계 선택사양과 관계명을 읽기

 

식별자 종류

1) 엔터티 내의 대표성 => 주식별자 / 보조식별자

2) 엔터티 내 자생성 => 내부식별자 / 외부식별자

3) 단일 속성 식별 => 단일 식별자 / 복합식별자(둘이상의 속성으로 구성)

4) 업무적으로 의미가 있던 식별자 속성 대체하여 일련번호와 같이 새롭게 만든 식별자를 구분 할

=> 인조식별자 / 본질식별자(업무로인해 만들어지는)

 

주식별자의 특징

- 유일성, 최소성, 불변성, 존재성(null값X, 무조건 데이터 값 존재)

 

항목	식별자관계	비식별자관계
목적	강한 연결관계 표현	약한 연결관계 표현
자식 주식별자 영향	자식 주식별자의 구성에 포함	자식 일반 속성에 포함
표기법	실선	점선
연결 고려사항	- 반드시 부모엔터티 종속 - 자식 주식별자 구성에 부모 주식별자 포함 필요 - 상속받은 주식별자 속성을 타엔터티에 이전 필요	- 약한 종속 - 자식 주식별자구성을 독립적으로 구성 - 자식 주식별자구성에 부모 주식별자 부분 필요 - 상속받은 주식별자 속성을 타 엔터티에 차단 필요 - 부모쪽의 관계 참여가 선택관계

 

비식별자 관계로 연결하는 것을 고려해야 하는 경우
- 부모엔터티에 참조값이 없어도 자식엔터티의 인스턴스가 생성될 수 있는 경우
- 여러개의 엔터티를 하나로 통합하면서 각각의 엔터티가 갖고 있던 여러개의 개별 관계가 통합되는 경우
- 자식쪽 엔터티의 주 식별자를 부모엔터티와는 별도로 생성하는 것이 더 유리하다고 판단되는 경우
- 엔터티별로 데이터의 생명주기를 다르게 관리할 경우,
  1) 부모엔터티의 인스턴스가 자식 엔터티와 관계를 가지고 있었지만 자식만 남겨두고 먼저 소멸될 수 있는 경우
     비식별자 관계로 연결
  2) 부모엔터티의 인스턴스가 자식 엔터티와 같이 소멸되는 경우는 식별자관계

 

성능 데이터모델링

- 데이터베이스 성능 향상을 목적으로 설계단계의 데이터 모델링 때부터 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것

- 데이터 증가가 빠를 수록 성능저하에 따른 성능개선비용이 증가

- 데이터모델은 성능을 튜닝하면서 변경이 될 수 있는 특징이 있음

- 분석/설계 단계에서 성능을 고려한 데이터 모델링을 수행할 경우 성능저하에 따른 Rework비용을 최소화 할 수 있는 기회를 가지게 됨

문제발생 시점의 SQL을 중심으로 집중 튜닝을 하는 것은 성능 데이터모델링과 무관하다.

 

성능을 고려한 데이터 모델링의 순서

1) 데이터 모델링을 할 때, 정규화를 정확하게 수행

2) 데이터베이스 용량산정을 수행

3) 데이터베이스에 발생되는 트랜잭션의 유형을 파악

4) 용량과 트랜잭션의 유형에 따라 반정규화(역정규화)를 수행

5) 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 조정 등을 수행

6) 성능관점에서 데이터모델을 검증

 

*정규화는 기본적으로 중복된 데이터를 제거함으로써 조회성능을 향상시킬 수 있음. 과도한 정규화는 조회성능을 저하시키기도 함.

 

[보관금원장]

관서번호 납부자번호

관리점번호 관서명 상태 관서등록일자 직급명 통신번호

함수종속성(FD) : 
[관서번호, 납부자번호]->[직급명, 통신번호]
[관서번호]->[관리점번호, 관서명, 상태, 관서등록일자]

2차정규화 - 정규화테이블(관서번호, 관리점번호, 관서명, 상태, 관서등록일자)

 

[모델]

모델코드

모델명 제품류코드 물품가 출하가 A유형기능분류코드1 B유형기능분류코드2 ... I유형기능분류코드9 바코드 가로 세로 높이 모델구분 ...

- 칼럼에 의한 반복적인 속성값을 갖는 형태는 속성의 원자성을 위배한 제1차 정규화의 대상
- 반복적인 속성 나열 형태에서는 각 송성에 대해 'or'연산자로 연결된 조건들이 사용됨
- 어느 하나의 속성이라도 인덱스가 정의되어있지 않으면 'or'로 연결된 모든 조건절들이 인덱스를 사용하지 않고 한 번의 전체 데이터 스캔으로 처리됨=> 성능저하
- 모든 반복 속성에 인덱스를 생성하게되면 => 검색속도 향상,
but 많은 인덱스때문에 입력/수정/삭제의 성능이 저하 => 1차 정규화를 진행

 

*다량 데이터 탐색의 경우 인덱스가 아닌 파티션 및 데이터 클러스터링 등의 다양한 물리 저장 기법을 활용하여 성능 개선을 유도할 수 있다. 다만, 하나의 결과셋을 추출하기 위해 다량의 데이터를 탐색하는 처리가 반복적으로 빈번하게 발생한다면 이때는 반정규화를 고려하는 것이 좋다.

* 이전 또는 이후 위치의 레코드에 대한 탐색은 window function으로 접근 가능

* 집계 테이블 이외에도 다양한 유형(다수 테이블의 키 연결 테이블 등)에 대하여 반정규화 테이블 적용이 필요할 수도.

 

 

반정규화 기법

- 테이블, 속성, 관계에 대해서 반정규화 가능

- 하나의 테이블의 전체 칼럼 중 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 칼럼들을 별도로 모아 놓은 반 정규화 기법은 테이블 추가 반정규화 기법 중에서 부분테이블 추가에 해당.

 

 

테이블반정규화			칼럼 반정규화
테이블 병합	1:1 관계 테이블 병합		중복칼럼 추가
	1:M 관계 테이블 병합		파생칼럼 추가
	슈퍼/서브타입 테이블 병합		이력테이블 칼럼추가
테이블 분할	수직분할		PK에 의한 칼럼추가
	수평분할		응용시스템 오작동을 위한 칼럼 추가
테이블 추가	중복테이블 추가
	통계테이블 추가
	이력테이블 추가
	부분테이블 추가

 

- 중복칼럼을 추가 - 조인감소를 위해 여러 테이블에 동일한 칼럼을 갖도록 함

- 파생칼럼을 추가 - 조회 성능을 우수하게 하기위해 미리 계산된 칼럼을 갖도록 함

- 이력테이블에 기능 칼럼을 추가 - 최신값을 처리하는 이력의 특성을 고려하여 기능성 칼럼을 추가

*PK에 대한 속성 추가는 데이터모델링에서 관계를 연결할 때 나타나는 자연스러운 현상. (반정규화기법 X)

 

 칼럼수가 많은 테이블

- 데이터가 물리적으로 저장되는 디스크 상에 넓게 분포할 가능성이 커지게 되어 디스크 I/O가 대량으로 발생 할 수도.

=> 성능 저하

 

- 트랜잭션이 접근하는 칼럼유형을 분석해서 자주 접근하는 칼럼들과 상대적으로 접근빈도가 낮은 칼럼들을 구분하여 1:1로 테이블을 분리하면 디스크 I/O가 줄어듦 => 성능향상

 

- 테이블 내에 칼럼위치 조정은 데이터 주로 채워지는 칼럼을 앞쪽에 위치시키고, 데이터가 채워지지 않고 주로 Null 상태로 존재하는 칼럼들을 뒤쪽에 모아둠으로써 로우의 길이를 어느정도 감소시킬 수 있으나, Null 상태이던 칼럼에 나중에 데이터가 채워지게 될 경우 더 많은 로우체인이 발생할 수도 있기떄문에 바람직한 해결책은 아님X.

(데이터가 채워지지 않고 Null상태로 존재하는 칼럼들이 많이 나타나는 경우는, 너무 많은 엔터티들을 무리하게 동질성을 부여하여 통합을 수행했거나, 예측하기 어려운 미래시점을 겨냥하여 과도하게 속성을 확장한 경우로)

=> 자주 사용되는 칼럼들이나 현시점에서 주로 사용되는 칼럼을 한데 모으고 사용빈도가 낮은 칼럼들이나 미래시점에 사용될 것으로 예상되는 나머지 칼럼들을 한데 모아 별도의 1:1관계 엔터티로 분리하는 등의 데이터 모델 설계 수정을 고려하는 것이 좋음.

 

반정규화 대상 처리방법

- View : 지나치게 많은 조인이 걸려데이터를 조회하는 작업이 기술적으로 어려울 경우

- 클러스터링 적용 또는 인덱스 조정 : 대량의 데이터 처리나 부분처리에 의해 성능이 저하되는 경우, 성능 향상 가능

- 파티셔닝 : 대량의 데이터는 기본키 성격에 따라 부분적인 테이블로 분리할 수 있음

- 응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있음

 

하나의 테이블에 많은 양의 데이터가 저장되면 인덱스를 추가하고 테이블을 몇 개로 쪼개도 성능이 저하되는 경우가 있다. 이때 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하여 데이터 액세스 성능도 향상시키고, 데이터 관리방법도 개선할 수 있도록 테이블에 적용하는 기법은?

파티셔닝

 

 

* 개별테이블을 모두 조회하는 트랜잭션이 대부분이라는 가정이 있을 떄 UNION/UNION ALL 할 경우, 개별조회에 따른 시간소요와 이것을 조합하는 성능저하가 발생되니 하나의 테이블로 통합하고 대신 PK체계나 일반속성에 각 사건을 구분할 수 있도록 구분자를 부여한다.

 

 

논리데이터 모델의 슈퍼타입과 서브타입 데이터 모델을 물리적인 테이블 형식으로 변환

- 트랜잭션은 항상 전체를 대상으로 일괄 처리하는 데 테이블은 서브타입 별로 개별 유지하는 것으로 변환하려면 Union연산에 의해 성능이 저하될 수도 있음

- 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합하여 변환하면 불필요하게 많은 양의 데이터가 직접되어있어 성능이 저하됨

- 트랜잭션은 항상 슈퍼+서브타입을 함께 처리하는데 개별로 유지하면 조인에 의해 성능이 저하될 수 있음

- 트랜잭션은 항상 전체를 통합하여 분석 처리하는데 슈퍼-서브타입이 하나의 테이블로 통합되어있으면 하나의 테이블에 직접된 데이터만 읽어 처리할 수 있기떄문에 다른 형식에 비해 성능이 더 우수함

 

 

인덱스를 효율적으로 이용할 수 있는 PK순서 결정하는 기준

인덱스의 특징은 여러개의 속성이 하나의 인덱스로 구성되어있을 떄 앞쪽에 위치한 속성 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있음.

=> 앞쪽에 위치한 속성 값이 가급적 = 아니면 최소한 범위 BETWEEN < > 가 들어와야 인덱스를 이용할 수 있음.

 

 

FK(Foreign Key)

- 데이터베이스 상에서 DBMS가 제공하는 FK Constraints의 생성 여부와 상관없이 조인 성능을 향상 시키기 위해 인덱스 생성

 

 

분산데이터베이스 환경에서 데이터베이스 분산설계를 적용하여 효율성을 증대

- 공통코드, 기준 정보 등 마스터 데이터는 분산데이터베이스에 복제분산을 적용

- 거의 실시간 업무적인 특성을 가지고있을 떄 분산데이터베이스를 사용하여 구성 가능

- 백업 사이트를 구성할 때 간단하게 분산기능을 적용하여 구성할 수 있음

GSI(Global Single Instance)는 통합된 한개의 인스턴스 = 통합 데이터베이스 구조를 의미, 분산데이터베이스와 대치

 

 

 

출처: SQL자격검정실전문제