SQLd 1과목 정리

is Yoon·2023년 8월 30일

SQLD

<1과목> 데이터 모델의 이해

1-1. 데이터 모델링의 이해

모델 : 다양한 현상에 대해서 일정한 표기법에 의해 표현해 놓은 모형
모델링 : 표기법에 의해 규칙을 가지고 표기하는 것 자체

1) 데이터 모델의 이해

(1) 모델링의 특징

추상화(모형화, 가설적) - 현실세계, 다양한 현상을 일정한 형식에 맞춘 표기법에 의해 표현
단순화 - 복잡한 현실 세계를 약속된 규약에 의해 제한된 표기법이나 언어로 표현하여 쉽게 이해할 수 있도록
명확화 - 누구나 이해하기 쉽게 대상에 대한 애매모호함 제거

즉, 모델링이란 '현실세계를 추상화, 단순화, 명확화하기 위해 일정한 표기법에 의해 표현하는 기법'이다.

(2) 모델링의 세 가지 관점

데이터 관점 (Data, What) - 업무가 어떤 데이터와 관련이 있는지 or 데이터간의 관계는 무엇인지에 대해서 모델링하는 방법
프로세스 관점 (Process, How) - 업무가 실제하고 있는 일은 무엇인지 or 무엇을 해야 하는지를 모델링하는 방법
상관 관점 (Interaction) - 업무가 처리하는 일의 방법에 따라 데이터는 어떻게 영향을 받고 있는지 모델링하는 방법

(3) 데이터 모델링 정의

정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법
현실세계의 데이터(what)에 대해 약속된 표기법에 의해 표현하는 과정
데이터베이스를 구축하기 위한 분석/설계의 과정

(4) 데이터 모델링 기능

시스템을 현재 또는 원하는 모습으로 가시화하도록 도와준다.
시스템의 구조와 행동을 명세화 할 수 있게 한다.
시스템을 구축하는 구조화된 틀을 제공한다.
시스템을 구축하는 과정에서 결정한 것을 문서화 한다.
다양한 영역에 집중하기 위해 다른 영역의 세부 사항은 숨기는 다양한 관점을 제공한다.
특정 목표에 따라 구체화된 상세 수준의 표현방법을 제공한다.

(5) 데이터 모델링의 중요성

파급효과 (Leverage)가 크다.
복잡한 정보 요구사항의 간결한 표현 (Conciseness)이 가능해진다.
데이터 품질 (Data Quality)을 유지한다.

(6) 데이터 모델링할 때 유의점

중복 (Duplication) - 데이터베이스가 여러 장소에 같은 정보를 저장하는 잘못을 하지 않도록 한다.
비유연성 (Inflexibility) - 데이터 모델이 변경됨에 따라 유지보수가 어려워지면 안 된다. 데이터 정의와 데이터의 사용 프로세스를 분리하여 영향을 끼치지 않도록 해야 한다.
비일관성 (Inconsistency) - 데이터 간의 상호 연관 관계에 대한 명확한 정의가 없으면 비일관성이 발생한다. (ex. 신용 등급 떨어졌는데 대출 한도 변화 없음)

(7) 데이터 모델링의 3단계

데이터 모델링	단계	내용	수준
개념적	계획 분석	추상화, 업무중심적, 포괄적, 전사적, EA 수립 시 사용	추상적
논리적	분석	Key-속성-관계 표현, 재사용성 높음, 정규화
물리적	설계	실제 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계	구체적

개념적 데이터 모델링 (Conceptual Data Modeling)

조직, 사용자의 데이터 요구사항을 찾고 분석하는데서 시작
엔터티-관계 다이어그램 생성
⏩ 어떠한 자료가 중요하고, 유지되어야 하는지 결정
전사적 데이터 모델 (Enterprise Data Model)

논리적 데이터 모델링 (Logical Data Modeling)

데이터베이스 설계 프로세스의 Input
⏩ 비즈니스 정보의 논리적인 '구조'와 '규칙'을 명확하게 표현
데이터 모델링의 가장 핵심이 되는 부분 - 누가 (who), 어떻게 (how, process)
데이터 모델링이 최종적으로 완료된 상태
정규화 : 일관성 확보, 중복 제거, 신뢰성 확보
상세화 : 식별자 확정, 정규화, M:M 관계 해소, 참조 뮤결성 규칙 정의 등

물리적 데이터 모델링 (Physical Data Modeling)

어떻게 컴퓨터 하드웨어에 표현될 것인가
데이터가 물리적으로 컴퓨터에 어떻게 저장될 것인가 = 물리적 스키마
물리적인 저장 구조, 사용될 저장 장치, 자료 추출 접근 방법 결정

(8) 프로젝트 생명주기(LC)에서 데이터 모델링

Waterfall 기반에서는 데이터 모델링의 위치가 분석과 설계 단계로 구분되어 명확하게 정의할 수 있다.
RUP (Rational Unified Process, 마르미) 나선형 모델에서는 업무 크기에 따라 논리적, 물리적 데이터 모델이 분석, 설계 단계 양쪽에서 수행되며, 비중은 분석단계에서 논리적인 데이터 모델이 더 많이 수행되는 형태가 된다.

(9) 데이터 독립성의 필요성

유지보수 비용 증가 ⏩ 비용의 절감을 해야 한다.
데이터 중복성 증가 ⏩ 중복성을 줄여야 한다.
데이터 복잡도 증가 ⏩ 복잡도를 낮춰야 한다.
요구사항 대응 저하 ⏩ 대응을 해야 한다.

(10) ANSI/SPARC 데이터독립성 3단계 구조

단계	내용
외부 단계	사용자가 처리하고자 하는 데이터 유형과 관점, 방법에 따라 다른 스키마 구조를 가지고 있다.
논리적 데이터 독립성	- 개념 스키마가 변경되어도 외부 스키마에는 영향을 미치지 않도록 지원하는 것 - 논리적 구조가 변경되어도 응용 프로그램에 영향 없음
개념적 단계	통합된 뷰를 스키마 구조로 디자인한 형태이다.
물리적 데이터 독립성	- 내부 스키마가 변경되어도 외부/개념 스키마는 영향을 받지 않도록 지원하는 것 - 저장장치의 구조변경은 응용프로그램과 개념스키마에 영향 없음
내부적 단계	데이터가 물리적으로 저장된 방법에 대한 스키마 구조를 말한다.

데이터베이스 스키마 구조는 3단계로 구분되고, 각각은 상호 독립적인 의미를 가지고 고유한 기능을 가진다. 데이터 모델링은 통합관점의 뷰를 가지고 있는 개념 스키마를 만들어가는 과정이다.

항목	내용	비고
외부 스키마 (External Schema)	- View 단계 여러 개의 사용자 관점으로 구성, 즉 개인적 DB 스키마 - DB의 개별 사용자나 응용프로그래머가 접근하는 DB 정의	사용자 관점
개념 스키마 (Conceptual Schema)	- 모든 사용자 관점을 통합한 조직 전체의 DB를 기술하는 것 - DB에 저장되는 데이터와 그들간의 관계를 표현하는 스키마	통합 관점
내부 스키마 (Internal Schema)	- 내부 단계, 내부 스키마로 구성, DB가 물리적으로 저장된 형식 - 물리적 장치에서 데이터가 실제적으로 저장되는 방법을 표현하는 스키마	물리적 저장구조

사상 (Mapping)은 상호 독립적인 개념을 연결시켜주는 다리를 뜻한다.

사상	내용	예
외부적/개념적 사상 (논리적 사상)	외부적 뷰와 개념적 뷰의 상호 관련성을 정의	사용자가 접근하는 형식에 따라 다른 타입의 필드를 가질 수 있으며, 개념적 뷰의 필드 타입은 변화가 없다.
개념적/내부적 사상 (물리적 사상)	개념적 뷰와 저장된 DB의 상호관련성을 정의	만약 저장된 DB 구조가 바뀐다면 개념적/내부적 사상이 바뀌어야 하며, 그래야 개념적 스키마가 그대로 남아있게 된다.

(11) 데이터 모델링의 세 가지 요소

업무가 관여하는 어떤 것 (Things) = 엔터티
어떤 것이 가지는 성격 (Attributes) = 속성
업무가 관여하는 어떤 것 간의 관계 (Relationships)

개념	복수/집합개념 타입/클래스	개별/단수개념 어커런스/인스턴스
어떤 것	- 엔터티 타입 (Entity Type) - 엔터티 (Entity)	- 엔터티 (Entity) - 인스턴스 (Instance), 어커런스 (Occurrence)
관계	관계 (Relationships)	패어링 (Pairing)
성격	속성 (Attributes)	속성값 (Attributes Value)

(12) 데이터 모델링의 이해관계자

보통 응용시스템 개발자가 데이터 모델링도 같이 하게 된다. 데이터 모델링은 업무를 이해하고, 분석하여, 표현하는 것이 중요하고, 대부분 분석 및 설계에 할애하기 때문이다.

DBA (DataBase Administrator)
프로젝트 개발자 (System Engineer)
전문 모델러
현업업무전문가

(13) 데이터 모델 표기법, ERD

Entity Relationship Model
엔터티 = 사각형, 관계 = 마름모, 속성 = 타원형으로 표현
데이터의 흐름, 프로세스와의 연관성

ERD 작업 순서는 다음과 같다.

엔터티를 그린다.
엔터티를 적절하게 배치한다.
엔터티간 관계를 설정한다.
관계명을 기술한다.
관계의 참여도 (관계차수, Cardinality)를 기술한다.
IE 표기법 에서 하나의 관계는 실선 표기, Barker 표기법 하나의 관계는 점선과 실선 혼합하여 표기한다. 다수 참여의 관계는 까마귀발 모양으로, 관계의 필수/선택 표시는 관계선에 원을 표현하여 그린다.
관계의 필수여부를 기술한다.

중심이 되는 엔터티는 타 엔터티와 많은 관계를 가지고 있으므로 왼쪽 상단에서 조금 아래쪽 중앙에 배치한다.

(14) 좋은 데이터 모델의 요소

완전성 (Completeness) - 업무에서 필요로 하는 데이터가 데이터 모델에 정의되어 있어야 한다.
중복배제 (Non-Redundancy) - 하나의 DB 내에 동일한 사실은 반드시 한 번만 기록하여야 한다. (낭비 x)
업무규칙 (Business Rules) - 데이터 모델에 표현하여 사용자가 공유할 수 있도록 제공해서 해당 규칙에 대해 동일한 판단을 하고 데이터를 조작할 수 있게 한다.
데이터 재사용 (Data Reuseability) - 데이터의 재사용성을 향상시키고자 한다면 데이터의 통합성, 독립성에 대해 충분히 고려해야 한다.
의사소통 (Communication) - 데이터 모델은 대상으로 하는 업무를 데이터 관점에서 분석하고 이를 설계하여 나오는 최종 산출물로, 엔터티, 서브타입, 속성, 관계 등의 형태로 최대한 자세하게 표현되어 의사소통의 도구로서의 역할을 해야 한다.
통합성 (Integration) - 동일한 데이터는 조직의 전체에서 한 번만 정의되고 이를 여러 다른 영역에서 참조, 활용해야 한다. (데이터 아키텍처 중요!)

2) 엔터티 Entity

실체, 객체
저장이 되기 위한 어떤 것 (Thing)
사람, 장소, 물건, 사건, 개념 등의 명사에 해당한다.
업무상 관리가 필요한 관심사에 해당한다.
업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것 (Thing)
속성을 갖는 인스턴스의 집합

(1) 엔터티의 특징

필요성 - 반드시 업무에서 필요하고 관리하고자 하는 정보여야 한다.
유일성 - 유일한 식별자(Unique Udentifier)에 의해 식별이 가능해야 한다.
인스턴스 수 - 영속적으로 존재하는 인스턴스의 집합이어야 한다. (두 개 이상)
프로세스 존재 - 업무 프로세스에 의해 이용되어야 한다.
속성의 존재 - 반드시 속성이 있어야 한다. 예외적으로 관계엔터티의 경우는 주식별자 속성만 가지고 있어도 엔터티로 인정한다.
관계의 존재 - 다른 엔터티와 최소 한 개 이상의 관계가 있어야 한다. 데이터 모델링을 하면서 관계를 생략하여 표현해야 하는 경우는 통계성 엔터티 도출, 코드성 엔터티 도출, 시스템 처리 시 내부 필요에 의한 엔터티 도출과 같은 경우이다.

(2) 엔터티의 분류

유무형에 따른 분류

유무형	내용
유형 엔터티 (Tangible Entity)	물리적인 형태가 있고, 안정적이며, 지속적으로 활용되는 엔터티 업무로부터 엔터티를 구분하기 가장 용이 ex) 사원, 물품, 강사
개념 엔터티 (Conceptual Entity)	물리적인 형태는 존재하지 않고, 관리해야 할 개념적 정보로 구분되는 엔터티 ex) 조직, 보험상품
사건 엔터티 (Event Ectity)	업무를 수행함에 따라 발생되는 엔터티 비교적 발생량이 많으며 각종 통계자료에 이용됨 ex) 주문, 청구, 미납

발생시점에 따른 분류

발생시점	내용
기본/키 엔터티 (Fundamental Entity)	원래 존재하는 정보로, 다른 엔터티와 관계에 의해 생성되지 않고 독립적으로 생성 가능하다. 타 엔터티의 부모의 역할을 하고, 자신의 고유한 주식별자 갖는다. ex) 사원, 부서, 고객, 상품, 자재
중심 엔터티 (Main Entity)	기본엔터티로부터 발생되고, 그 업무에 있어서 중심적인 역할을 한다. 데이터의 양이 많이 발생되고, 다른 엔터티와의 관계를 통해 많은 행위엔터티를 생성한다. ex) 계약, 사고, 예금원장, 청구, 주문, 매출
행위 엔터티 (Active Ectity)	두 개 이상의 부모엔터티로부터 발생되고, 자주 내용이 바뀌거나 데이터량이 증가된다. 분석 초기 단계에서는 잘 나타나지 않으며 상세 설계단계나 프로세스와 상관모델링을 진행하며 도출될 수 있다. ex) 주문목록, 사원변경이력

스스로 생성될 수 있는지 여부에 따른 분류

독립 엔터티 vs 의존 엔터티

(3) 엔터티의 명명

현업업무에서 사용하는 용어를 사용한다.
약어를 사용하지 않는다.
단수명사를 사용한다.
유일하게 이름이 부여되어야 한다.
생성의미대로 이름을 부여한다.

3) 속성 Attribute

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

업무에서 필요로 한다.
의미상 더 이상 분리되지 않는다.
엔터티를 설명하고, 인스턴스의 구성요소가 된다.

(1) 엔터티-인스턴스-속성-속성값 관계

한 개의 엔터티 : 두 개 이상의 인스턴스, 두 개 이상의속성
한 개의 속성 : 한 개의 속성값

(2) 속성의 특징

반드시 해당 업무에서 필요하고, 관리하고자 하는 정보여야 한다.
정규화 이론에 근간하여 정해진 주식별자에 함수적 종속성을 가져야 한다.
하나의 속성에는 한 개의 값만 갖는다. 하나의 속성에 여러 개의 값이 있는 다중값일 경우 별도의 엔터티를 이용하여 분리한다.

(3) 속성의 분류

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

속성의 특성	내용
기본속성 (Basic Attribute)	업무분석을 통해 바로 정의한 속성 업무로부터 추출한 모든 속성. 가장 일반적. 많은 속성을 차지. ex) 코드성 데이터, 엔터티를 식별하기 위해 부여된 일련번호, 다른 속성을 계산하거나 영향을 받아 생성된 속성을 제외한 모든 속성
설계속성 (Designed Attribute)	원래 업무상 존재하지는 않지만 설계를 하면서 도출해내는 속성 데이터 모델링을 위해, 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성 ex) 코드성 속성, 일련번호
파생속성 (Derived Attribute)	다른 속성으로부터 계산이나 변형이 되어 생성되는 속성 다른 속성에 영향을 받아 발생하는 속성/ 계산된 값. 데이터 정합성을 유지하기 위해 유의해야 할 점이 많으며, 가급적 적게 정의하는 것이 좋다.

엔터티 구성방식에 따른 분류 : PK, FK, 일반

엔터티 구성방식	내용
PK(Primart Key) 속성	엔터티를 식별할 수 있는 속성
FK(Foreign Key) 속성	다른 엔터티와의 관계에서 포함된 속성
일반 속성	엔터티에 포함되어 있고, PK, FK에 포함되지 않은 속성

세부 의미에 따른 분류 : 단순형, 복합형

세부 의미	내용
단순형	더 이상 다른 속성들로 구성될 수 없는 단순한 속성 ex) 나이, 성별
복합형	여러 세부 속성들로 구성 ex) 주소(시, 구, 동, 번지) 속성

값에 따른 분류 : 단일값, 다중값

속성값	내용
단일값 속성 (Single-Valued Arrtibute)	속성 하나에 한 개의 값을 가지는 경우 ex) 주민등록번호
다중값 속성 (Multi-Valued Arrtibute)	속성 하나에 여러 개의 값을 가지는 경우 ex) 전화번호 (집전화, 휴대폰, 회사전화 등) 하나의 엔터티에 포함될 수 없으므로 1차 정규화를 하거나, 별도의 엔터티를 만들어 관계로 연결해야 한다.

(4) 도메인 Domain

가질 수 있는 값의 범위
엔터티 내에서 속성에 대한 데이터타입, 크기, 제약사항을 지정하는 것

(5) 속성의 명명 (Naming) fffff

해당 업무에서 사용하는 이름을 부여한다.
서술식 속성명은 사용하지 않는다. (명사형)
약어 사용은 가급적 제한한다.
전체 데이터모델에서 유일성 확보하는 것이 좋다.

4) 관계 Relationship

엔터티의 인스턴스 사이의 논리적인 연관성으로서 존재의 형태로서나 행위로서 서로에게 연관성이 부여된 상태

(1) 관계 패어링 (Relationship Paring)

엔터티 안에 인스턴스가 개별적으로 관계를 가지는 것
패어링 : 인스턴스의 관계
관계 : 관계 패어링의 집합

(2) 관계의 분류

목적	내용
존재에 의한 관계, 연관 관계(Association)	항상 이용하는 관계 [부서-사원] 사원은 부서에 항상 속해있다.
행위에 의한 관계, 의존 관계(Dependency)	특정 행위를 통해 연관성 생기는 관계 [고객-주문] 주문은 고객이 주문할 때 발생된다.

(3) 관계의 표기법

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

엔터티가 관계에 참여하는 형태
각각의 관계는 두 개의 관계명을 갖는다.
각각의 관계명에 의해 두 가지의 관점으로 표현될 수 있다.
명명규칙 : 애매한 동사를 피하고, 현재형으로 표현한다.

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

두 개의 엔터티간 관계에서 참여자의 수를 표현하는 것

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

필수참여관계 (Mandatory) : 필수적으로 연결 관계가 있는 것 (지하철 출발 - 지하철 문 닫힘)
필수참여는 아무런 표시도 하지 않는다.
선택참여관계 (Optional) : 정보로서 관련은 있지만 서로가 필수적인 관계는 아닌 선택적인 관계 (지하철 출발 - 지하철 방송)
선택참여 하는 엔터티 쪽을 원으로 표시

(4) 관계 체크사항

두 개의 엔터티 사이에 관심있는 연관규칙이 존재하는가?
두 개의 엔터티 사이에 정보의 조합이 발생되는가?
업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?
업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가?

(5) 관계 읽기

기준(Source) 엔터티를 한 개 또는 각으로 읽는다.
대상(Target) 엔터티의 관계참여도, 즉 개수를 읽는다.
관계선택사양과 관계명을 읽는다.

각각의/하나의	기준 엔터티	관계 차수	관련 엔터티	선택사양 (필수/선택)	관계명
각각의	사원은	한	부서에	때때로	속한다
각	부서에는	여러	사원이	항상	소속된다

5) 식별자 Identifiers

각각을 구분할 수 있는 논리적인 이름
엔터티를 대표할 수 있는 속성
하나의 엔터티는 반드시 하나의 유일한 식별자가 존재한다.

(1) 식별자의 특징

주식별자

유일성 - 주식별자에 의해 엔터티 내에 모든 인스턴스들이 유일하게 구분되어야 한다. (직원별 사원번호)
최소성/희소성 - 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다. : (사원번호 (o), 사원분류코드+사원번호 (x))
불변성 - 지정된 주식별자의 값은 변하지 않아야 한다. (사원번호는 변하지 않는다.)
존재성 - 주식별자가 지정되면 반드시 데이터 값이 존재해야 한다. (Null x, 사원번호 없는 직원은 없다)

대체식별자 - 주식별자의 특징 + 별도의 특징을 갖는다.
외부식별자 - 참조무결성 제약조건에 따른 특징을 갖는다.

(2) 식별자 분류

분류	식별자	설명
대표성 여부	주식별자	엔터티 내에서 각 어커런스 구분. 타 엔터티와 참조관계를 연결할 수 있는 식별자
	보조식별자	엔터티 내에서 각 어커런스 구분. 대표성을 가지지 못해 참조관계 연결 못함
스스로 생성 여부	내부별자	엔터티 내부에서 스스로 만들어지는 식별자
	외부식별자	타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자
속성의 수	단일별자	하나의 속성으로 구성된 식별자
	복합식별자	둘 이상의 속성으로 구성된 식별자
대체 여부	본질식별자	업무에 의해 만들어지는 식별자
	인조식별자	업무적으로 만들어지지 않지만, 원조식별자가 복잡한 구성을 가지고 있기 때문에 인위적으로 만든 식별자

(3) 주식별자 도출기준

해당 업무에서 자즈 이용되는 속성을 주식별자로 지정한다.
명칭, 내역 등과 같이 이름으로 기술되는 것들은 가능하면 주식별자로 지정하지 않는다. (보통 일련번호와 코드를 사용하여 새로운 식별자를 생성)
복합으로 주식별자로 구성할 경우 너무 많은 속성이 포함되지 않도록 한다.

(4) 식별자 관계, 비식별자 관계

외부 식별자 (Foreign Identifier) - 다른 엔터티와의 관계를 통해 자식 쪽에 엔터티에 생성되는 속성으로, 데이터베이스 생성 시에 FK 역할을 한다.
식별자 관계 (Identifying Relationship) - 자식엔터티의 주식별자로 부모의 주식별자가 상속이 되는 경우 (강한 연결관계, 실선 표현)
비식별자 관계 (Non-Identifying Relationship) - 부모엔터티로부터 속성을 받았지만 자식엔터티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 경우 (약한 연결관계, 점선 표현)

비식별자 관계에 의한 외부속성을 생성하는 경우 :
1) 자식엔터티에서 받은 속성이 반드시 필수가 아니어도 무방하기 때문에 부모 없는 자식이 생성될 수 있는 경우
2) 엔터티별로 데이터의 생명주기를 다르게 관리할 경우
3) 여러 개의 엔터티가 하나의 엔터티로 통합되어 표현되었는데 각각의 엔터티가 별도의 관계를 가질 때
4) 자식엔터티에서 별도의 주식별자를 생성하는 것이 더 유리하다고 판단될 때

1-2. 데이터 모델과 성능

1) 성능 데이터 모델링의 개요

성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것

즉, 데이터베이스 성능향상을 목적으로 설계단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블통합, 테이블분할, 조인구조, PK, FK 등 여러 가지 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것이다.
데이터 모델 구조, 대용량 데이터, 충분히 고려되지 않은 인덱스 등으로 데이터 조회의 성능이 저하될 수 있다.
분석/설계 단계에서부터 성능에 대한 데이터모델 설계를 하지 않으면 시간이 지날수록 성능개선의 비용이 증가하게 된다.

(1) 성능 데이터 모델링 고려사항

데이터 모델링을 할 때 정규화를 정확하게 수행한다. 정규화된 모델이 데이터를 주요 관심사별로 분산시키는 효과가 있다.
데이터베이스 용량산정을 수행한다. 엔터티에 대한 용량산정으로 어떤 엔터티(테이블)에 데이터가 집중되는지 파악할 수 있다.
데이터베이스에 발생되는 트랜잭션 유형을 파악한다. 그러면 SQL 문장의 조인관계 테이블에서 데이터조회의 칼럼들을 파악할 수 있게 된다.
용량과 트랜잭션의 유형에 따라 반정규화를 수행한다. 테이블, 속성, 관계에 대해 포괄적인 반정규화의 방법을 적용해야 한다.
이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행한다.
성능관점에서 데이터 모델을 검증한다.

2) 정규화와 성능

(1) 정규화를 통한 성능 향상 전략

일반적으로 정규화를 수행해야 데이터처리의 성능이 향상되며 데이터의 조회처리 트랜잭션 시에 성능저하가 나타날 수 있다.
정규화를 수행한다는 것은 데이터를 결정하는 결정자에 의해 함수적 종속을 가지고 있는 일반속성을 의존자로 하여 입력/수정/삭제 이상을 제거하는 것이다.

조회 성능 - 처리조건에 따라 성능 향상/저하
입력/수정/삭제 성능 - 성능 향상

함수적 종속성 (Functional Dependency)

데이터들이 어떤 기준값에 의해 종속되는 현상
기준값 = 결정자 (Determinant)
종속되는 값 = 종속자 (Dependent)
ex) 주민등록번호 -> (이름, 출생지, 주소)
이름, 출생지, 주소(종속자)는 주민등록번호(결정자)에 함수 종속성을 가지고 있음

(2) 반정규화된 테이블의 성능저하

2차 정규화를 적용한 테이블에서 조회해도 PK Unique Index를 이용 조인성능저하는 미미하다.
두 개의 엔터티가 통합되어 반정규화된 경우 성능이 저하될 수 있다.
한 테이블에 인덱스가 많아지면 조회 성능은 향상되지만, 입력/수정/삭제 성능은 저하된다. (인덱스 수 7~8개)

3) 반정규화와 성능

반정규화(=역정규화) : 정규화된 엔터티, 속성, 관계에 대해 시스템의 '성능향상'과 개발과 운영의 '단순화'를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법

테이블의 중복성, 칼럼의 중복성, 관계의 중복성 : 보통 칼럼의 반정규화가 많다.
반정규화를 과도하게 적용하면 데이터 무결성이 깨질 수 있는 위험이 증가하게 된다.

(1) 반정규화를 통한 성능향상 전략

1. 반정규화 대상 조사	2. 다른 방법 유도 검토	3. 반정규화 적용
- 범위처리빈도수 조사 - 대량의 범위 처리 조사 - 통계성 프로세스 조사 - 테이블 조인 개수	- 뷰 테이블 - 클러스터링 적용 - 인덱스의 조정 - 파티셔닝 기법 - 응용애플리케이션	- 테이블 반정규화 - 속성의 반정규화 - 관계의 반정규화

접근하는 프로세스의 수가 많고, 항상 일정한 범위만을 조회하는 경우에 반정규화 검토
테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없을 경우 반정규화 검토
통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계테이블(반정규화 테이블) 생성
테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 반정규화 검토

(2) 반정규화 기법

1. 테이블 반정규화

분류	기법	내용
테이블 병합	1:1 관계 테이블병합	1:1 관계를 통합하여 성능향상
	1:M 관계 테이블병합	1:M 관계를 통합하여 성능향상
	슈퍼/서브타입 테이블병합	슈퍼/서브 관계를 통합하여 성능향상
테이블 분할	수직분할	칼럼단위의 테이블을 디스크 I/O를 분산처리 하기 위해 테이블을 1:1로 분리하여 성능향상 (트랜잭션 처리 유형 파악 선행)
	수평분할	로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터접근의 효율성을 높여 성능을 향상하기 위해 로우단위로 테이블을 쪼갬
테이블추가	중복테이블 추가	다른 업무거나 다른 서비일 경우 동일한 테이블구조를 중복하여 원격조인을 제거하여 성능향상
	통계테이블 추가	SUM, AVG 등을 미리 수행해 계산하여 조회 시 성능향상
	이력테이블 추가	마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재
	부분테이블 추가	하나의 테이블의 전체 칼럼 중 자주 이용, 집중화된 칼럼들이 있을 때 디스크 I/O 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화된 테이블을 생성

2. 칼럼 반정규화

반정규화 기법	내용
중복칼럼 추가	조인에 의해 처리할 때 성능저하를 예방하기 위해 즉, 조인을 감소시키기 위해 중복된 칼럼을 위치시킴
파생칼럼 추가	트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 칼럼에 보관함 (Derived Column)
이력테이블 칼럼추가	대량의 이력데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능저하를 예방하기 위해 기능성 칼럼(최근값 여부, 시작과 종료일자 등)을 추가함
PK에 의한 칼럼추가	복합의미를 갖는 PK를 단일 속성으로 구성했을 경우 발생. 단일 PK안에서 특정 값을 별도로 조회하는 경우 성능저하 발생. 이미 PK 안에 데이터가 존재하지만 성능향상을 위해 일반속성으로 포함하는 방법
응용시스템 오작동을 위한 칼럼추가	업무적으론 의미없지만 사용자가 데이터처리를 하다가 잘못 처리하여 원래값으로 복구하기를 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법

3. 관계 반정규화

반정규화 기법	내용
중복관계 추가	데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능하지만 이 때 발생할 수 있는 성능저하를 예방하기 위해 추가적인 관계를 맺는 방법

관계의 반정규화는 데이터 무결성을 깨뜨릴 위험성이 없다.

(3) 정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우

4) 대량 데이터에 따른 성능

대량의 데이터가 존재하는 테이블에 많은 트랜잭션이 발생하여 성능이 저하되는 테이블 구조에 의해 수평/수직 분할 설계를 통해 성능저하를 예방할 수 있다.

로우체이닝 (Row Chaining) - 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태
로우마이그레이션 (Row Migration) - 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식

(1) 대량 데이터 저장 및 처리로 인해 성능

Range Partition(범위) 적용
List Partition(특정값 지정)
Hash partition(해쉬적용)
Composite Partition(범위와 해쉬가 복합)

(2) 슈퍼타입/서브타입 모델

데이터 양 & 트랜잭션의 유형에 따라 변환한다.

구분	1:1 type	Plus type	Single type
특징	개별 테이블 유지	슈퍼+서브타입 테이블	하나의 테이블
확장성	우수	보통	나쁨
조인성능	나쁨	나쁨	우수
I/O량 성능	좋음	좋음	나쁨
관리용이성	좋지않음	좋지않음	좋음(1개)
트랜잭션 유형에 따른 선택 방법	개별 테이블로 접근이 많은 경우	슈퍼+서브 형식으로 데이터를 처리하는 경우	전체를 일괄적으로 처리하는 경우

(3) 인덱스 특성을 고려한 PK/FK 데이터베이스 향상

테이블에 발생되는 트랜잭션의 조회 패턴에 따라 PK/FK 칼럼의 순서를 조정해야 한다.

PK 칼럼의 순서 조정
FK 칼럼의 인덱스 칼럼화

5) 분산 데이터베이스와 성능

업무의 흐름을 보고 업무구성에 따른 아키텍처 특징에 따라 데이터베이스를 구성하는 것
핵심 가치 : 데이터 처리 성능

(1) 분산 데이터베이스 투명성 (Transparency)

분할 투명성 (단편화) : 하나의 논리적 Relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 사이트에 저장
위치 투명성 : 사용하려는 데이터의 저장 장소 명시 불필요. 위치 정보가 시스템 카탈로그에 유지되어야 함
지역사상 투명성 : 지역 DBMS와 물리적 DB 사이의 매핑 보장. 각 지역시스템 이름과 무관한 이름 사용 가능
중복 투명성 : DB 객체가 여러 사이트에 중복되어 있는지 알 필요 없는 성질
장애 투명성 : 구성요소(DBMS, Computer)의 장애에 무관한 트랜잭션의 원자성 유지
병행 투명성 : 다수 트랜잭션 동시 수행 시 결과의 일관성 유지, Time Stamp, 분산 2단계 Locking을 이용 구현

(2) 분산 데이터베이스 장단점

장점	단점
- 지역 자치성, 점증적 시스템 용량 확장 - 신뢰성, 가용성 - 효용성, 융통성 - 빠른 응답 속도, 통신비용 절감 - 데이터의 가용성, 신뢰성 증가 - 시스템 규모의 적절한 조절 - 각 지역 사용자의 요구 수용 증대	- 소프트웨어 개발 비용 - 오류의 잠재성 증대 - 처리 비용의 증대 - 설계, 관리의 복잡성과 비용 - 불규칙한 응답 속도 - 통제의 어려움 - 데이터 무결성에 대한 위협

(3) 분산 데이터베이스 적용 기법

테이블 위치 분산 - 테이블 구조는 변하지 않고, 중복 생성되지 않고, 설계된 테이블의 위치를 각각 다르게 위치시키는 것
테이블 분할 분산 - 각각의 테이블을 쪼개어 분산하는 방법
수평분할 (Horizontal Fragmentation) - 테이블을 로우 단위로 분리. PK에 의해 중복이 발생하지 않음. 각 지사(Node)별로 사용하는 로우가 다를 때 이용.
수직분할 (Vertical Fragmentation) - 테이블을 칼럼 단위로 분할. PK는 하나로 표현되므로 데이터 중복 발생하지 않음.
테이블 복제 분산 - 동일한 테이블을 다른 지역이나 서버에 동시에 생성하여 관리
부분 복제 (Segment Replication) - 통합된 테이블을 한 군데(본사)에 가지고 있으면서 각 지사별로 지사에 해당된 로우를 가지고 있는 형태
광역 복제 (Broadcast Replication) - 통합된 테이블을 한 군데(본사)에 가지고 있으면서 각 지사에도 본사와 동일한 데이터를 모두 가지고 있는 형태
테이블 요약 분산 - 지역간에 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우 요약하는 방법
분석 요약 (Rollup Replication) - 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용해 통합된 데이터를 산출하는 방식
통합 요약 (Consolidation Summarization) - 분산되어 있는 다른 내용의 데이터를 이용해 통합된 데이터를 산출하는 방식

<2과목> SQL 기본 및 활용

2-1. SQL 기본

2-2. SQL 활용

2-3. SQL 최적화 기본 원리

1) 옵티마이저 (Optimizer)

사용자가 질의한 SQL 문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다.

규칙 기반 옵티마이저 - 규칙(우선순위)을 가지고 실행계획을 생성한다.
비용 기반 옵티마이저 - SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.

✔️ 실행계획 (Execution Plan)

SQL에서 요구한 사항을 처리하기 위한 절차와 방법
SQL을 어떤 순서로 어떻게 실행할 지 결정하는 작업
조인 순서, 조인 기법, 액세스 기법, 최적화 정보, 연산

✔️ SQL 처리 흐름도 (SQL Access Flow Diagram)

SQL의 내부적인 처리 절차를 시각적으로 표현한 도표

2) 인덱스 기본

원하는 데이터를 쉽게 찾을 수 있도록 돕는다.
트리 기반 인덱스 : B-트리 인덱스는 브랜치 블록, 리프 블록으로 구성된다.
클러스터형 인덱스 / 비클러스터형 인덱스

3) 조인 수행 원리

NL Join
Sort Merge Join
Hash Join

is Yoon

planning design development with data

이전 포스트

SQL 자격증 - SQLd, SQLp

다음 포스트