[SQLD] 1. 데이터 모델링의 이해

1. 데이터 모델링의 이해 (10문제)
(1) 데이터 모델링의 이해
(2) 데이터 모델과 성능

---

 

(1) 데이터 모델링의 이해

 

• 데이터 모델링 목적

- DB구축 뿐만 아니라 데이터 모델링 자체로 업무를 설명하고 분석
- 업무정보를 구성하는 기초가 되는 정보들에 대해 일정한 표기법으로 업무 내용을 정확하게 분석함
- 분석된 모델로 실제 데이터베이스를 생성해 개발 및 데이터 관리에 사용하기 위함

• 모델링의 특징

- 추상화 (모형화, 가설적) : 현실 세계를 일정한 형식에 맞춰 표현
- 단순화 : 복잡한 현실 세계를 제한된 표기법이나 언어로 표현해 쉽게 이해할 수 있도록 함
- 명확화, 정확화 : 대상에 대한 애매모호함 제거, 정확하게 현상 기술
- 시스템 구현 뿐만 아니라 업무분석 및 업무형상화 하는 목적도 존재

• 데이터 모델링 유의점 : 중복, 비유연성, 비일관성

- 중복 (Duplication)
여러 장소에 같은 정보를 저장하는 잘못을 하지 않도록 함

- 비유연성 (Inflexibility)
사소한 업무변화에 데이터 모델이 변경되지 않게 설계해야함
데이터의 정의를 데이터의 사용 프로세스와 분리해 어플리케이션과 DB 변화 가능성을 최소화함

- 비일관성 (Inconsistency)
다른 데이터와 모순된다는 고려없이 수정하며 생기는 문제 ( ex : 신용 상태 갱신 없이 납부 이력 정보 갱신 )
중복이 없더라도 비일관성은 발생 가능함. 데이터와 데이터간의 상호 연관 관계에 대한 명확한 정의로 예방
프로그램과 테이블의 연계성을 낮추도록 해야함

• 데이터 모델링 3단계 진행

(1) 개념적 데이터 모델링

추상화 수준이 높고 업무중심적이며 포괄적인 수준의 모델링 진행
전사적 데이터 모델링

(2) 논리적 데이터 모델링

시스템으로 구축하고자 하는 업무에 대해 Key, 속성, 관계를 정확히 표현
재사용성이 높음

(3) 물리적 데이터 모델링

실제 DB에 이식할 수 있도록 성능, 저장 등 물리적 성격 고려해 설계

• 데이터 독립성 모델 : 외부, 내부, 개념

각각은 상호 독립적인 의미와 고유한 기능을 가짐

- 외부스키마 (External)
View 단계
여러 개의 사용자 관점으로 구성, 개개 사용자가 보는 개인적 DB스키마
DB의 개개 사용자나 응용프로그래머가 접근하는 DB 정의

- 내부스키마 (Internal)
DB가 물리적으로 저장된 형식
물리적 장치에서 데이터가 실제적으로 저장되는 방법을 표현하는 스키마

- 개념스키마 (Conceptual)
하나의 개념적 스키마로 구성해 모든 사용자 관점 통합한 조직 전체의 DB 기술
DB에 저장되는 데이터와 사용자들간의 관계를 표현하는 스키마

• 부모 엔티티에 데이터가 입력될 때 자식 엔티티에 해당 값이 존재하는지의 여부와 상관없이 입력될 수 있음

 

• ERD (Entity Relationship Diagram)

데이터의 흐름과 프로세스 연관성 도식화 그림

(1) PK, FK 기술해 엔티티 그림
(2) 엔티티 배치
(3) 엔티티 관계 연결 및 관계명 표시
(4) 관계 참여도 기술 (Cardinality - 관계 차수 표현)
(5) 관계 필수여부 기술

• 엔티티 배치

가장 중요한 엔티티를 왼쪽 상단에 배치하여 전개

• 엔티티 특징

- 업무에서 필요하고 관리하고자 하는 정보
- 업무 프로세스에 의해 이용되어야 함
- 유일한 식별자에 의해 식별 가능해야 함
- 영속적으로 존재하는 2개 이상 인스턴스의 집합
- 2개 이상의 속성이 있어야 함
- 다른 엔티티와 1개 이상의 관계가 있어야 함
(관계 생략하는 엔티티 : 통계 위한 엔티티, 코드성 엔티티, 내부 필요에 의한 엔티티 - 트랜젝션 로그 테이블 등)

• 엔티티 분류

- 유무에 따른 분류 : 유형 엔티티, 개념 엔티티, 사건 엔티티

(1) 유형 엔티티 - 물리적 형태 존재, 안정적/지속적으로 활용, 업무로부터 구분하기 용이 (Ex : 사원, 물품, 강사)
(2) 개념 엔티티 - 물리적 형태 존재하지 않음, 관리해야할 개념적 정보로 구분 (Ex : 조직, 보험상품)
(3) 사건 엔티티 - 업무를 수행함에 따라 발생되는 엔티티, 발생량이 많으며 각종 통계자료에 이용 (Ex : 주문, 청구, 미납)

- 발생시점에 따른 분류 : 기본/키엔티티, 중심엔티티, 행위엔티티

(1) 기본 엔티티 - 독립적으로 생성 가능, 타 엔티티의 부모 역할, 고유 주식별자 가짐 (Ex : 사원, 부서, 고객, 상품, 자재)
(2) 중심 엔티티
기본 엔티티로부터 발생, 업무에 있어 중심적 역할
다른 엔티티와의 관계로 많은 행위엔티티 생성 (Ex: 계약, 사고, 예금원장, 청구, 주문, 매출 등)
(3) 행위 엔티티
2개 이상의 부모 엔티티로부터 발생, 자주 내용이 변하거나 데이터량 증가함,
분석 초기 단계에서는 잘 나타나지 않다가 상세 설계단계나 프로세스와 상관모델링 진행하며 도출됨 (Ex : 주문 목록, 사원변경이력)

• 엔티티 명명

(1) 현업 업무에서 사용하는 용어 사용 (2) 약어 되도록 사용하지 않음 (3) 단수명사 사용 (4) 유일한 이름 (5) 엔티티 생성의미대로

• 속성 ♥️

- 업무에서 관리하기 위한 최소의 의미 단위, 더 이상 분리되지 않는 최소의 데이터 단위
- 엔티티에 대한 자세하고 구체적인 정보
- 각각의 값을 대표하는 이름들
- 1개의 속성은 1개의 속성값만 가짐

• 속성 분류

- 속성 특성에 따른 분류 : 기본속성, 설계속성, 파생속성

(1) 기본속성 - 원래 속성
업무분석을 통해 바로 정의한 속성
엔티티에 가장 일반적이고 많은 속성 차지
업무로부터 분석한 속성이라도 이미 업무상 코드로 정의한 속성은 원래 속성값을 나타내지 못해 기본속성이 아님

(2) 설계속성 - 1:1 치환 ♥️
업무상 존재하지 않지만 설계를 하며 도출해내는 속성
대개 코드성 속성이 업무상 필요에 의해 변형해 만든 설계속성
일련번호와 같은 속성은 단일한 식별자를 부여하기 위해 모델 상에서 새로 정의하는 설계속성

(3) 파생속성 - 계산값 🤦🏻‍♀️?
다른 속성으로부터 계산이나 변형되어 생성되는 속성
다른 속성의 영향을 많이 받기에 프로세스 설계 시에 데이터 정합성을 유지하기 위해 유의해야함
가급적 적게 정의할 것
데이터 조회 시에 빠른 성능을 낼 수 있도록 하기 위해 원래 속성의 값 계산해 저장할 수 있도록 만든 속성

• 도메인(Domain) ♥️

각 속성이 가질 수 있는 값의 범위, 각 속성은 도메인 이외의 값을 가지지 못함
엔티티 내에서 속성에 대한 데이터 타입, 크기, 제약사항 지정하는 것
( Ex : 학점 - 0.0에서 4.5 사이의 실수값, 주소 - 길이 20자리 이내의 문자열 )

• 속성 명명

(1) 업무에서 사용하는 이름 (2) 서술식 속성명은 사용하지 말 것 (3) 약어사용 가급적 제한
(4) 전체 데이터 모델에서 유일성 확보 (5) 복합명사

• 관계

- 존재에 의한 관계, 행위에 의한 관계로 구분되지만 ERD 관계 연결 시에는 존재와 행위를 구분하지 않고 단일화된 표기법 사용

- UML 클래스다이어그램은 연관관계, 의존관계로 구분되며 실선과 점선의 표기법으로 다르게 표현됨

• 관계의 표기법 3가지

1. 관계명 : 관계의 이름
2. 관계차수 : 1:1, 1:M, M:N
3. 관계선택사양(선택성) : 필수관계, 선택관계

• 관계 분류

(1) 존재에 의한 분류
이벤트에 의해 발생되는 의미가 아닌, 존재의 형태에 의해 관계 형성

(2) 행위에 의한 분류
주문한다는 행위에 의해 발생

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

(1) 두 개의 엔티티 사이에 관심 있는 연관규칙이 있는지
(2) 두 개의 엔티티 사이에 정보의 조합이 발생되는지
(3) 업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는지
(4) 업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는지

• 주식별자의 특징 🤦🏻‍♀️ 독립성 아님

- 유일성 : 엔티티내의 모든 인스턴스들이 유일하게 구분됨
- 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수
- 불변성 : 주식별자가 한 번 특정 엔티티에 지정되면 식별자의 값은 변하지 않아야 함
- 존재성 : 주식별자가 지정이 되면 반드시 값이 들어와야 함 (NULL 불가)

• 식별자 분류

(1) 대표성 여부 (주/보조)
- 주식별자 : 타 엔티티와 참조관계를 연결할 수 있는 식별자
- 보조식별자 : 대표성을 갖지 못해 참조관계 연결을 못함

(2) 스스로 생성 여부 (내부/외부)
- 내부식별자 : 엔티티 내부에서 스스로 만들어지는 식별자
- 외부식별자 : 타 엔티티와의 관계로 타 엔티티로부터 받아오는 식별자

(3) 속성의 수 (단일/복합)
- 단일식별자 : 하나의 속성으로 구성된 식별자
- 복합식별자 : 둘 이상의 속성으로 구성된 식별자

(4) 대체 여부 (본질/인조)
- 본질식별자 : 업무에 의해 만들어지는 식별자
- 인조식별자 : 인위적으로 만든 식별자

• 주식별자 도출 기준

- 해당 업무에서 자주 이용되는 속성을 주식별자로 지정
- 명칭, 내역 등과 같이 이름으로 기술되는 것들은 되도록 지정하지 않음
- 복합으로 주식별자로 구성할 경우 너무 많은 속성이 포함되지 않도록 함

• 식별자와 비식별자관계

- 식별자관계

(1) 부모엔티티의 주식별자를 자식, 손자엔티티까지 흘려보내기 위해서 고려
(2) 부모엔티티의 인스턴스가 자식 엔티티와 같이 소멸되는 경우

- 비식별자관계

(1) 관계의 강약을 분석해 상호 연관성이 약할 때 고려
(2) 자식테이블에서 독립적 PK 구조를 갖기 원할 때 비식별자관계 고려
(3) 모든 관계가 식별자 관계로 연결될 때, SQL문장이 길어져 복잡성이 증가되는 것을 방지하기 위해 고려
(4) 부모엔티티의 인스턴스가 자식엔티티만 남겨두고 먼저 소멸될 수 있는 경우
(5) 여러 개의 엔티티를 하나로 통합하면서 각각의 엔티티가 갖고 있던 여러 개의 개별 관계가 통합되는 경우

---

 

(2) 데이터 모델과 성능

 

• 성능 데이터 모델링 수행 절차

1. 데이터모델링을 할 때 정규화를 정확하게 수행
2. 데이터베이스 용량 산정을 수행
3. 데이터베이스에 발생되는 트랜잭션의 유형 파악
4. 용량과 트랜잭션의 유형에 따라 반정규화 수행
5. 이력모델 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 수행
6. 성능관점에서 데이터모델을 검증

집중 튜닝 XX

• 정규화

데이터 분해 과정, 이상현상 제거
기본적으로 중복된 데이터를 제거함으로써 조회성능을 향상시킬 수 있음

- 1차 정규화
속성의 원자성 확보, 다중값 속성 분리, 컬럼 단위에서 중복된 경우
- 2차 정규화
부분 함수 종속성 제거, 일부 기본키에만 종속된 속성 분리, 기본키가 하나의 컬럼일 때 생략 가능
- 3차 정규화
이행 함수 종속성 제거, 서로 종속관계가 있는 일반속성을 분리, 주식별자와 관련성이 가장 낮음
- 4차, 5차 정규화
다치 종속 분리, 결합 종속 분리

• 반정규화

- 정규화된 엔티티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법을 의미
- 데이터를 중복하여 성능을 향상시키기 위한 기법
- 데이터 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반정규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산해 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행함.
- 집계 테이블 이외에 다양한 유형에 대해 반정규화 테이블 적용 필요

- 중복 컬럼 추가 VS. 중복 테이블 추가
중복 컬럼추가 : 조인을 감소시키기 위해 / 중복 테이블 추가 : 서버가 다른 경우

- 부분 테이블 추가
전체 컬럼 중 자주 이용하는 컬럼들을 모아 별도의 반정규화된 테이블 생성

• 칼럼수가 많은 테이블

데이터가 물리적으로 저장되는 디스크 상에 넓게 분포 가능성이 커져 디스크 I/O가 대량으로 발생 가능해 성능 저하
자주 접근하는 컬럼들을 구분해 1:1로 테이블 분리해 디스크 I/O가 줄어들어 성능 향상
테이블 내의 컬럼 위치 조정해 주로 채워지는 컬럼들을 앞 쪽 위치, 주로 NULL 상태는 뒤쪽으로하면 로우길이 감소 가능, 조회 향상 X
NULL 상태이던 컬럼에 데이터가 채워지게 되면 더 많은 로우 체인 발생 가능

• 파티셔닝

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

• 트랜잭션

- 항상 전체를 대상으로 통합해 일괄 처리함
- 슈퍼-서브타입이 하나의 테이블로 통합되어있으면 하나의 테이블 데이터만 읽어 처리해 성능이 우수

• PK 순서 결정 기준

앞에 위치한 속성의 값이 비교자로 있어야 좋은 인덱스 효율성
앞쪽에 가급적 '=' 그 이후에 최소한 범위 'BETWEEN' ,'< >' 의 컬럼이 다음에 오도록 함

• 인덱스 생성

데이터들이 밀접하게 연결되어 상호간의 조인이 자주 발생하는 것은
FK 제약조건을 생성했는지 여부와 상관없이 조인 성능을 향상시키기 위한 인덱스를 생성해주는 것이 좋음

• 분산 데이터베이스

- 데이터가 여러 지역에 분산되어있지만 하나의 DB처럼 사용하기 원할 때
- 공통기준, 기준정보 등 마스터 데이터를 한 곳에 두고 운영할 때 원격지 접근이 빈번할수록 실시간 업무처리에 대한 좋은 성능은 힘듦
- 공통기준, 기준정보 등 마스터 데이터는 복제분산으로 분산 DB 구성
- 백업 사이트 구성 시에 간단하게 분산환경으로 구성 가능


- 장점
지역 자치성, 점증적 시스템 용량 확장
신뢰성과 가용성
효용성과 융통성
빠른 응답속도와 통신비용 절감
데이터의 가용성과 신뢰성 증가
시스템 규모의 적절한 조절
각 지역 사용자의 요구 수용 증대

- 단점
소프트웨어 개발 비용
오류 잠재성 증대
처리 비용의 증대
설계, 관리의 복잡성과 비용
불규칙한 응답 속도
통제의 어려움
데이터 무결성에 대한 위협

• GSI

Global Single Instance
통합된 한 개의 인스턴스, 통합 데이터베이스 구조 의미로 분산 데이터베이스와 대치되는 개념

---

(+) 출처

[한국데이터산업진흥원] SQL 자격검정 실전문제