[2022 정보처리기사] [필기 요약] 제 3과목 - 데이터베이스 구축

시뮝 연구소의 내용을 참고해 작성했습니다.

---

 3-1 SQL 응용

---

절차형 SQL 작성

 

(1) 트리거(Trigger)

트리거 구성

구성요소		설명
선언부	DECLARE	트리거 명칭 정의
이벤트부	EVENT	트리거 실행 타이밍, 이벤트 명시
시작/종료부	BEGIN/END	트리거 시작과 종료 표현, 블록으로 구성
제어부	CONTROL	순차처리, 비교 조건에 따라 블록 또는 문장 실행, 조건에 따라 반복 실행
SQL	SQL	DML을 주로 사용, 자주 사용되지 않지만 DDL(TRUNCATE 등) 사용
예외부	EXCEPTION	BEGIN~END 절의 SQL문 예외 발생 시 예외 처리 방법을 정의하는 처리부

 트리거 작성 예시

CREATE TRIGGER T_STUDENT
BEFORE INSERT ON STUDENT
BEGIN
	IF (AGE < 19) THEN
    	RAISE_APPLICATION_ERROR(-20502, '미성년자 추가 불가');
    END IF;
END;

 트리거 작성 시 주의사항

• 트리거 내에는 COMMIT, ROLLBACK 등의 트랜잭션 제어어(TCL) 사용 시 컴파일 에러 발생
• 트리거 실행 중 오류가 발생하게 되면 트리거 실행의 원인을 제공한 데이터 작업에도 영향을 준다.

(2) 이벤트(Event)

이벤트는 특정 시간에 특정한 쿼리, 프로시저, 함수 등을 실행시키는 기능이다.

 

이벤트 등록 구문

CREATE EVENT [IF NOT EXISTS] /* 해당 이벤트를 호출할 때 사용할 이름을 지정 */
ON SCHEDULE /* 이벤트의 실행시간과 간격을 지정 */
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '주석']
DO
[BEGIN]
	[실행할 SQL문;]
[END]

 이벤트 등록 구문 예시

CREATE EVENT T_TEST
	ON SCHEDULE
    	EVERY 50 SECOND
    DO INSERT INTO TEST
    	  SET REGDATE=NOW();

 

(3) 사용자 정의함수(User-Defined Function)

• 사용자 정의함수는 절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일 값으로 반환할 수 있는 함수이다.
• SQL 작성 시 데이터를 조작하는 INSERT, DELETE, UPDATE는 사용할 수 없다.
• 정보 은닉을 통해 캡슐화를 제공하는 데에도 많이 사용한다.
• 데이터 조작어(DML) 문장을 활용하여 사용자 정의함수를 호출한다.

 

사용자 정의함수 구성

DECLARE / BEGIN/END / CONTROL / SQL / EXCEPTION / RETURN

 

사용자 정의함수 예시

CREATE FUNCTION GET_AGE
	(V_BIRTH_YEAR IN CHAR(4))
IS
	V_THIS_YEAR CHAR(4);
BEGIN
	SELECT TO_CHAR(SYSDATE, 'YYYY')
      INTO V_THIS_YEAR
      FROM DUAL;
      
    RETURN V_THIS_YEAR
           - V_BIRTH_YEAR + 1;
END;

(4) SQL(Structured Query Language) 문법

SQL 문법은 데이터베이스를 접근하고 조작하는 데 필요한 표준 언어를 활용할 수 있게 해주는 규칙이다.

 

SQL 문법의 분류 [2022년 1회]

분류	설명
데이터 정의어(DDL)	CREATE, ALTER, DROP, TRUNCATE 다양한 응용 프로그램과 데이터베이스가 서로 인터페이스를 할 수 있는 방법을 제공하는 기능
데이터 조작어(DML)	SELECT, INSERT, UPDATE, DELETE 사용자와 데이터베이스 사이의 인터페이스를 위한 수단을 제공하는 기능
데이터 제어어(DCL)	GRANT, REVOKE  DBA가 사용하는 제어용 언어

 데이터 정의어(DDL) 대상 [2022년 2회]

DDL 대상	설명
도메인 (Domain)	하나의 속성이 가질 수 있는 원자값들의 집합
스키마 (Schema)	데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
테이블 (Table)	데이터 저장 공간
뷰 (View)	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index)	검색을 빠르게 하기 위한 데이터 구조

 

데이터 제어어(DCL) 기능

데이터 보안 / 무결성 유지 / 병행수행 제어 / 회복

 

WHERE 조건 연산자 종류

구분	연산자
비교	=,<>,<,<=,>,>=
범위	[A] BETWEEN [B] AND [C]: A는 B보다 크거나 같고 C보다 작거나 같다
집합	IN, NOT IN
패턴	LIKE
NULL	IS NULL, IS NOT NULL
복합조건	AND, OR, NOT

 

LIKE와 같이 사용하는 와일드 문자 [2022년 2회]

와일드 문자	설명	예시	예시결과
+	문자열 연결	'연결된' + ' ' + '문자열'	'연결된 문자열' 문자열 검색
%	0개 이상 문자열 일치	LIKE '노란색%'	'노란색'으로 시작하는 문자열 검색
		LIKE '%노란색'	'노란색'으로 끝나는 문자열 검색
		LIKE '%노란색%'	'노란색'이 포함된 문자열 검색
[ ]	1개 문자 일치	'[0-8]%'	0-8 사이 숫자로 시작하는 문자열 검색
[ ^ ]	1개 문자 불일치	'[^0-8]%'	0-8 사이 숫자로 시작하지 않는 문자열 검색
_	특정 위치의 1개 문자 일치	'_동%'	두 번째 위치에 '동'이 들어가는 문자열 검색

응용 SQL 작성

집계 함수 [2020년 3회]

집계 함수	설명
COUNT	줄 수를 구하는 함수
SUM	합계를 구하는 함수
AVG	평균을 구하는 함수
MAX	최댓값을 구하는 함수
MIN	최솟값을 구하는 함수
STDDEV	표준편차를 구하는 함수
VARIAN	분산을 구하는 함수

 

순위 함수

순위 함수	설명
RANK	순위를 구하는 함수 (2위,2위,2위,5위,6위...)
DENSE_RANK	레코드의 순위를 계산하는 함수, 동일 순위는 후순위로 넘어감 (1위,2위,2위,3위,4위...)
ROW_NUMBER	레코드의 순위를 계산하는 함수, 동일 순위는 동일 순위로 부여 (1위,2위,3위,4위,5위,...)

 

OLAP(On-Line Analytical Processing)

• OLAP은 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 의사결정 지원 시스템이다.
• OLAP의 연산은 Roll-Up, Drill-Down, Drill-Up, Slicing, Dicing, Pivoting 이 있다.
• 윈도우 함수를 OLAP함수라고도 한다.

 

윈도우 함수의 분류

• 집계 함수
• 순위 함수
• 행순서 함수
• 비율 함수

 

그룹 함수(Group Function)

• 그룹 함수는 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹하하여 그룹별로 결과를 출력하는 함수이다.
• 그룹 함수의 유형에는 ROLLUP, CUBE, GROUPING SETS 이 있다.

 

오류 처리(Error Handling)

오류 처리는 프로그램 코드상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정이다.

 

핸들러 선언 구문

핸들러를 선언하기 위해서는 DECLARE HANDLER 문을 사용한다.

 

---

 3-2 SQL 활용

---

관계형 데이터 모델(Relation Data Model)

• 데이터 간의 관계를 기본 키와 이를 참고하는 외래 키로 표현한다.
• 테이블 간 관계를 1:1, 1:N, M:N 관계로 목적에 맞게 표현한다.

 

테이블 관련 용어 [2022년 1회]

용어	설명
튜플(Tuple) / 행(Row)	테이블 내의 행, 레코드(Record)
애트리뷰트(Attribute) / 열(Column)	테이블 내의 열
식별자(Identifier)	구분할 수 있는 논리적 개념
카디널리티(Cardinality)	튜플(Tuple)의 수
차수(Degree)	애트리뷰트(Attribute)의 수
도메인(Domain)	하나의 속성이 가질 수 있는 원자값들의 집합

 

CASECADE

참조하는 테이블까지 연쇄적으로 제거하는 옵션

 

RESTRICT

참조 중이면 제거하지 않는 옵션

 

 

트랜잭션(Transaction)

일련의 연산 집합으로 데이터베이스의 상태를 변환시키기 위하여 논리적 기능을 수행하는 하나의 작업 단위

 

트랜잭션의 특성 (ACID) [2022년 2회]

특성	설명
원자성(Atomicity)	트랜잭션 연산은 데이터베이스에 모두 반영되든지 아니면 전혀 반영되지 않아야 함
일관성(Consistency)	트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환
격리성(Isolation)	트랜잭션 실행 중 실행하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가
영속성(Durability)	성공적으로 완료된 트랜잭션의 결과는 시스템이 고장 나더라도 영구적으로 반영되어야 함

 

트랜잭션 연산 [2022년 2회]

커밋(Commit) / 롤백(Rollback)

 

트랜잭션의 상태

활동 / 부분완료 / 완료 / 실패 / 철회 상태

 

트랜잭션 제어(TCL; Transaction Control Language)

트랜잭션 제어 언어는 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어이다.

 

트랜잭션 제어(TCL) 명령어

COMMIT / ROLLBACK / CHECKPOINT

병행 제어(일관성 주요 기법)

병행 제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호작용을 제어하는 기법이다.

 

병행 제어의 목적

• 데이터베이스의 공유를 최대화한다.
• 시스템의 활용도를 최대화한다.
• 데이터베이스의 일관성을 유지한다.
• 사용자에 대한 응답시간을 최소화한다.

 

병행 제어 미보장 시 문제점

• 갱신 손실(Lost Update)
• 현황 파악 오류(Dirty Read)
• 모순성(Inconsistency)
• 연쇄복귀(Cascading Rollback)

 

병행 제어 기법의 종류

• 로킹(Locking)
• 낙관적 검증
• 타임 스탬프 순서(Time Stamp Ordering)
• 다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)

 

로킹(Locking)의 특징

• 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있다.
• 로킹 단위가 작아지면 데이터베이스 공유도, 로킹 오버헤드가 증가한다. [2022년 1회]
• 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 한다.

회복 기법(영속성 주요 기법)

회복(Recovery) 기법은 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업이다.

 

회복 기법 종류

• 로그 기반 회복 기법 [2020년 3회]
  • 지연 갱신 회복 기법
  • 즉각 갱신 회복 기법
• 체크 포인트 회복 기법
• 그림자 페이징 회복 기법

제 2과목 소프트웨어 개발 > SQL 활용 > 고급 SQL 작성

(1) 뷰(View)

• 뷰는 논리 테이블이다.
• 뷰는 저장장치 내에 물리적으로 존재하지 않는다.
• 뷰 테이블은 ALTER문으로 변경이 불가능하다. 변경이 필요할 경우 DROP하고 CREATE한다.
• 뷰가 정의된 기본 테이블이 제거되면 뷰도 자동적으로 제거된다.

 

뷰의 특징

논리적 데이터 독립성 제공 / 데이터 조작 연산 간소화 / 보안 기능(접근제어) 제공 / 뷰 변경 불가

 

뷰의 장점과 단점 [2020년 3회]

장점

• 논리적 독립성 제공
• 사용자 데이터 관리 용이
• 데이터 보안의 용이

단점

• 뷰 자체 인덱스 불가
• 뷰 정의 변경 불가
• 데이터 변경 제약 존재

 

뷰 생성 명령어

CREATE VIEW 뷰 이름 컬럼 목록 AS 데이터 조회 쿼리;

(2)인덱스(Index) 

• 인덱스는 데이터를 빠르게 찾을 수 있는 수단이다.
• 인덱스는 자동으로 생성되지 않는다.
• 기본키(PK; Primary Key) 컬럼은 자동으로 생성된다.

 

인덱스 종류

유형	설명
순서 인덱스 (Ordered Index)	데이터가 정렬된 순서로 생성되는 인댁스, B-Tree 알고리즘 활용(오름차순/내림차순 지정가능)
해시 인덱스 (Hash Index)	해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
비트맵 인덱스 (Bitmap Index)	각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
함수기반 인덱스 (Functional Index)	수식이나 함수를 적용하여 만든 인덱스
단일 인덱스 (Singled Index)	하나의 컬럼으로만 구성한 인덱스, 주 사용 컬럼이 하나일 경우 사용
결합 인덱스 (Concatenated Index)	두 개 이상의 컬럼으로 구성한 인덱스, WHERE 조건으로 사용하는 빈도가 높은 경우 사용
클러스터드 인덱스 (Clustered Index)	기본 키(PK) 기준으로 레코드를 묶어서 저장하는 인덱스

 

인엑스 스캔 방식

범위 스캔 / 전체 스캔 / 단일 스캔 / 생략 스캔

 

(3) 집합 연산자(Set Operator)

집합 연산자는 두 개 이상의 질의 결과를 하나의 결과로 결합하는 연산자이다.

 

집합 연산자 유형

UNION / UNION ALL / INTERSECT / MINUS

 

 

 

(4) 조인(Join)

조인은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

 

조인 유형

조인	유형	설명
논리적 조인	내부 조인 (Inner Join)	공통 존재 컬럼의 값이 같은 경우를 추출
	외부 조인 (Outer Join)	Left Outer Join: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
		Right Outer Join: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
		Full Outer Join: 양쪽의 모든 데이터를 추출
	교차 조인 (Cross Join)	조인 조건이 없는 모든 데이터 조합을 추출
	셀프 조인 (Self Join)	자기 자신에게 별칭을 지정한 후 다시 조인
물리적 조인	중첩 반복 조인 (Nested-Loop Join)	2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 조인 방식
	정렬 합병 조인 (Sort-Merge Join)	조인의 대상 범위가 넓을 경우 발생하는 임의 접근(Random Access)을 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인 방식
	해시 조인 (Hash Join)	해싱 함수를 활용하여 테이블 간 조인을 수행하는 방식

 

---

3-3 논리 데이터베이스 설계

---

관계 데이터 모델(Relation Data Model)

• 실세계 데이터를 행과 열로 구성된 테이블 형태로 구성된 데이터 모델이다.
• 수학자 E.F.Codd 박사가 제안한 모델이다.

관계 데이터 모델의 구성요소 [2022년 1, 2회]

구성요소	설명
릴레이션	행(Row)과 열(Column)로 구성된 테이블
튜플(Tuple)	릴레이션의 행(Row)에 해당되는 요소
속성(Attribute)	릴레이션의 열(Column)에 해당되는 요소
카디널리티(Cardinality)	튜플(Row)의 수
차수(Degree)	Attribute(Column)의 수
스키마(Schema)	데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
인스턴스(Instance)	정의된 스키마에 따라 생성된 테이블에 실제 저장된 데이터의 집합

 

 

관계 대수와 관계 해석 

구분	관계 대수	관계 해석
특징	절차적 언어(순서 명시)	비 절차적 언어, 프레디킷 해석(Predicate Calculus) 기반
목적	어떻게 유도하는가?(How)	무엇을 얻을 것인가?(What)
종류	일반 집합 연산자, 순수 관계 연산자	튜플 관계 해석, 도메인 관계 해석

관계 대수

일반 집합 연산자

∪ 합집합(Union) / ∩ 교집합(Intersection) / - 차집합(Difference) / × 카티션 프로덕트(CARTESIAN Product)

순수 관계 연산자 [2022년 2회]

σ 셀렉트(Select) / π 프로젝트(Project) / ⋈ 조인(Join) / ÷ 디비전(Division)

 

 

관계 해석

관계 해석 논리 기호

구분	구성요소	기호	설명
연산자	OR 연산	∨	원자식 간 "또는"이라는 관계로 연결
	AND 연산	∧	원자식 간 "그리고"이라는 관계로 연결
	NOT 연산	ㄱ	원자식에 대해 부정
정량자	전칭 정량자 (Universal Quantifier)	∀	모든 가능한 튜플("for all"로 읽음, All의 'A'를 뒤집어 놓은 형태)
	존재 정량자 (Existential Quantifier)	ꓱ	어떤 튜플 하나라도 존재("there exists"로 읽음, exist의 'E'를 뒤집어 놓은 형태

 

시스템 카탈로그(System Catalog)

• DDL 실행으로 생성되는 테이블, 뷰, 인덱스, 패키지, 접근 권한 등의 데이터베이스 구조 및 통계 정보를 저장한다.
• 자료 사전이라고도 부른다.
• 시스템 카탈로그에 저장된 정보를 메타 데이터(Metadata)라고 부른다. [2022년 1회]

데이터 모델(Data Model)

현실 세계의 정보를 인간과 컴퓨터가 이해할 수 있도록 추상화하여 표현한 모델이다.

 

데이터 모델에 표시해야 할 요소  [2020년 4회]

논리적 데이터 구조 / 연산 / 제약조건

 

데이터 모델 절차 (개논물)

개념적 데이터 모델 / 논리적 데이터 모델 / 물리적 데이터 모델

 

논리적 설계 단계(논리적 데이터 모델)에서 수행하는 작업

• 논리적 데이터베이스 구조로 매핑(Mapping)
• 트랜잭션 인터페이스 설계
• 스키마의 평가 및 정제

물리적 설계 단계(물리적 데이터 모델)에서 수행하는 작업

• 저장 레코드 양식 설계
• 레코드 집중의 분석 및 설계
• 접근 경로 설계

 

 

개체-관계(E-R) 모델

개체-관계(E-R) 모델 구성요소

개체(Entity) / 속성(Attribute) / 관계(Relationship)

 

개체-관계(E-R) 다이어그램 기호 [2022년 1, 2회]

구성	기호
개체	□ (사각형)
관계	◇ (마름모)
속성	○ (타원)
다중 값 속성	◎ (이중타원)
관계-속성 연결	ㅡ (선)

 

 

데이터베이스 정규화(DB Normalization)

• 관계형 데이터 모델에서 데이터의 중복성을 제거하여 이상 현상을 방지한다.
• 데이터의 일관성과 정확성을 유지하기 위해 무손실 분해하는 과정이다.

데이터베이스 정규화 목적

• 중복 데이터를 최소화하여 테이블 불일치 위험을 최소화한다.
• 수정, 삭제 시 이상 현상을 최소화함으로써 데이터 구조의 안정성을 최대화한다.
• 어떠한 릴레이션이라도 데이터베이스 내에서 표현을 가능하게 만든다.
• 데이터 삽입 시 릴레이션의 재구성에 대한 필요성을 줄인다.
• 효과적인 검색 알고리즘을 생성할 수 있다.

이상 현상(Anomaly)의 종류 [2022년 2회]

삽입 이상 / 삭제 이상 / 갱신 이상

 

데이터베이스 정규화 단계 [2022년 1, 2회] (두부 이걸 다 줘?)

단계	조건
1정규형(1NF)	원자 값으로 구성, 도메인
2정규형(2NF)	부분 함수 종속 제거(완전 함수적 종속 관계)
3정규형(3NF)	이행 함수 종속 제거
보이스-코드 정규형(BCNF)	결정자가 후보 키가 아닌 함수 종속 제거
4정규형(4NF)	다치(다중 값) 종속성 제거
5정규형(5NF)	조인 종속성 제거

 

 

 

논리 데이터 모델 품질 검증

데이터 모델이 업무 환경에서 요구하는 사항을 시스템적으로 구현할 수 있는가를 객관적으로 평가하는 검증이다.

 

데이터 모델 요건

완정성 / 중복 배제 / 비즈니스 룰 / 데이터 재사용 / 안정성 및 확장성 / 간결성 / 의사소통 / 통합성

 

데이터 모델 품질 검증 기준

정확성 / 완전성 / 준거성 / 최신성 / 일관성 / 활용성

 

 

---

3-4 물리 데이터베이스 설계

---

스토리지(Storage)

정보를 보존하는 저장장치

 

스토리지 구성 [2022년 1회]

접속 방식	설명	장점	단점
DAS (Direct Attached Storage)	데이터 서버와 외장형 저장장치를 전용 케이블로 직접 접속하는 방식	전용라인의 사용으로 주어진 성능이 보장되며 안정성도 뛰어남	저장장치에 따른 접속방법이 서로 상이하여 저장장치 공유에 문제가 있음
NAS (Network Attached Storage)	LAN을 통해 스토리지와 서버를 접속하는 방식, 파일서버를 통한 파일 시스템 공유	데이터 저장 장치와 서버 간의 독립성을 유지	데이터 접근 요청에 의한 파일 서버의 병목현상 발생 가능, 셧다운 시 스토리지 접근이 불가능
SAN (Storage Area Network)	서버가 광섬유 채널(Fiber Channel)을 통하여 스토리지를 연결하는 기법	DAS의 접속 한계성을 극복하여, n개의 서버가 m개의 저장장치 접속 가능	이기종 서버 환경을지원하지 않음, 공유 가능한 파일 시스템과 데이터형식이 제한

 

 

분산 데이터베이스(Distributed Database)

분산 데이터베이스는 네트워크상에서 여러 컴퓨터에 물리적으로 분산되어 있지만, 하나의 데이터베이스처럼 인식하도록 논리적으로 통합된 데이터베이스이다.

 

분산 데이터베이스 장점

• 분산 제어가 용이
• 지역 자치성이 높음
• 효용성과 융통성이 높음
• 데이터베이스 복제 및 분산을 통해 사용자 측면에서는 향상된 성능을 제공
• 장애로 인한 데이터 유실 복구에 효과적
• 시스템 확장이 용이

 

분산 데이터베이스 단점

• 복잡성이 증가
• 성능 저하
• 개발 비용의 증가

 

분산 데이터베이스 구성

전역 스키마 / 분할 스키마 / 할당 스키마 / 지역 스키마

 

분산 데이터베이스의 투명성

*영어로 나온 적이 있습니다. [2020년 3회]

구분	설명
위치 투명성(Location Transparency)	사용자나 응용 프로그램이 접근할 데이터의 물리적 위치를 알아야 할 필요가 없는 성질
복제 투명성(Replication Transparency)	사용자나 응용 프로그램이 접근할 데이터가 물리적으로 여러 곳에 복제되어 있는지의 여부를 알 필요가 없는 성질
병행 투명성(Concurrency Transparency)	여러 사용자나 응용 프로그램이 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우에도 결과에 이상이 발생하지 않는 성질 로킹(Locking), 타임 스탬프(Time Stamp) 순서 기법 이용
분할 투명성(Fragmentation Transparency)	사용자가 하나의 논리적인 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 장소에 저장되어 있음을 알 필요가 없는 성질
장애 투명성(Failure Transparency)	데이터베이스가 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 생기더라도, 데이터의 무결성을 보존할 수 있는 성질 2PC(Phase Commit) 활용

 

데이터베이스 이중화(Database Replication)

데이터베이스 이중화는 물리적으로 떨어져 있는 여러 개의 데이터베이스에 대하여 데이터베이스의 변경된 내용을 원격데이터베이스에 복제하고 관리하는 기술이다.

 

 

데이터베이스 암호화(Database Encryption)

데이터베이스 암호화 유형

유형	설명
응용 프로그램 자체 암호화	암·복호화 모듈이 DB 서버에 설치되고 DB 서버에서 암·복호화 모듈을 호출하는 방식
DB 서버 암호화	DB 서버의 DBMS 커널이 자체적으로 암·복호화 기능을 수행하는 방식
DBMS 자체 암호화	응용 프로그램에서 DB 서버의 DBMS 커널이 제공하는 암·복호화 API를 호출하는 방식
DBMS 암호화 기능 호출	OS에서 발생하는 물리적인 입출력을 이용한 암·복호화 방식으로 DBMS의 데이터 파일을 암호화하는 방식
운영체제 암호화

 

데이터베이스 암호화 적용 방식

컬럼 암호화 

API / Plug-in / Hybrid

 

블록 암호화 

TDE 방식 / 파일 암호화 방식

 

 

접근제어(Access Control)

접근제어 구성요소

정책 / 메커니즘 / 보안 모델

파티셔닝(Partitioning)

파티셔닝은 대용량의 테이블을 파티션이라는 보다 작은 논리적인 단위로 나눔으로써 성능 저하 방지 및 관리를 상대적으로 보다 용이하게 하고자 하는 기법이다.

 

파티셔닝 특징

• 물리적 파티셔닝으로 인해 전체 데이터 훼손 가능성이 줄어들고 데이터 가용성이 향상된다.
• 데이터베이스를 작은 단위로 관리하여 편리하다.
• 부하를 각각 파티션들로 분산시켜 성능을 향상시킨다.

 

파티셔닝 장점

장점 : 성능 향상 / 가용성 향상 / 백업 가능 / 경합 감소

 

파티셔닝 유형  [2020년 3회]

• 레이지 파티셔닝(=범위분할, Range Partitioning)
• 해시 파티셔닝(=해시분할, Hash Partitioning)
• 리스트 파티셔닝(List Partitioning)
• 컴포지트 파티셔닝(=조합분할, Composite Partitioning)

 

 

 

클러스터링(Clustering)

클러스터링은 지정된 컬럼 값의 순서대로 데이터 행을 저장하는 방법이다.

 

클러스터링 유형

단일 클러스터링 / 다중 클러스터링

 

클러스터링 설계 시 고려사항

• 대량의 범위를 자주 액세스하는 경우
• 인덱스를 사용한 처리 부담이 되는 넓은 분포도
• 여러 개의 테이블이 자주 조인을 일으킬 때
• 반복 컬럼이 정규화에 의해 어쩔 수 없이 분할된 경우
• UNION, DISTINCT, ORDER BY, GROUP BY가 빈번한 컬럼이면 고려
• 수정이 자주 발생하지 않는 컬럼

 

데이터베이스 백업(Database Backup)

데이터베이스 백업 종류

구분	설명
전체 백업 (Full Backup)	백업하고자 하는 데이터 전체에 대해 백업하는 방식
차등 백업 (Differential Backup)	마지막 전체 백업 이후 변경된 모든 데이터를 백업하는 방식
증분 백업 (Incremental Backup)	정해진 시간을 기준으로 그 이후에 변경된 파일만을 백업하는 방식
트랜잭션 로그 백업 (Transaction Log Backup)	실제 데이터 파일의 내용을 백업하는 것이 아니라 로그 파일에 기록된 로그를 백업하는 방식

 

 

테이블 저장 사이징(Sizing)

정확한 데이터 용량을 예측하여 저장 공간을 효과적으로 사용하고 확장성을 보장하여 가용성을 높이기 위해 사용한다.

 

 

데이터 지역화(Data Locality)

• 데이터 지역화는 데이터베이스의 저장 데이터를 효율적으로 이용할 수 있도록 저장하는 방법이다.
• 필요한 위치에 가까이 데이터를 배치하는 것을 의미한다.

 

데이터 지역화의 종류 

시간적(Temporal) / 공간적(Spacial) / 순차적(Sequential) 지역화

 

데이터 지역화를 이용한 관리 기법

기억장치 계층구조(Hierarchy) / 캐시 접근시간(Cache Access Time) 단축 / 작업세트(Working Set)

 

 

데이터베이스 무결성(Database Integrity)

데이터를 인가되지 않은 방법으로 변경할 수 없도록 보호하는 특성이다.

 

데이터 무결성 종류 [2022년 2회]

종류	설명
개체 무결성	한 엔터티에서 동일한 기본 키(PK)를 가질 수 없거나, 기본 키(PK)의 속성이 NULL을 허용할 수 없음
참조 무결성	외래 키가 참조하는 다른 개체의 기본 키에 해당하는 값이 기본 키 값이나 NULL이어야 함
속성 무결성	속성의 값은 기본값, NULL 여부, 도메인(데이터 타입, 길이)이 지정된 규칙을 준수해야 함
사용자 무결성	사용자의 의미적 요구사항을 준수해야 함
키 무결성	한 릴레이션에 같은 키 값을 가진 튜플들을 허용할 수 없음

 

 

키(Key)

• 키는 다른 튜플들과 구별할 수 있는 기준이 되는 속성이다.
• 키 특성으로는 유일성, 최소성이 있다.

 

키 종류 [2022년 1회]

종류	설명
기본 키(Primary Key)	테이블의 각 튜플들을 고유하게 식별하는 컬럼
대체 키(Alternate Key)	후보 키 중에서 기본 키로 선택되지 않은 키
후보 키(Candidate Key)	테이블에서 각 튜플들을 구별하는데 기준이 되는 컬럼 기본 키와 대체 키를 합친 키
슈퍼 키(Super Key)	릴레이션을 구성하는 모든 튜플에 대해 유일성은 만족하지만, 최소성은 만족하지 못하는 키
외래 키(Foreign Key)	테이블 간의 참조 데이터 무결성을 위한 제약 조건 한 릴레이션의 컬럼이 다른 릴레이션의 기본 키로 이용되는 키

 

반 정규화(De-Normalization)

• 정규화된 엔티티, 속성, 관계를 시스템의 성능 향상과 개발 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법이다.
• 비정규화, 역정규화라고도 불린다.

 

반정규화 장단점

장점	단점
성능 향상과 관리의 효율성이 증가	데이터의 일관성 및 정합성 저하
	유지를 위한 비용이 별도로 발생하여 성능에 나쁜 영향을 미칠 수 있음

반 정규화 기법

(테이블)병합 / 분할 / 중복, (컬럼)중복, (관계)중복

 

 

CRUD 분석

• CRUD 분석은 데이터베이스에 영향을 주는 생성, 읽기, 갱신, 삭제 연산으로 프로세스와 테이블 간에 매트릭스를 만들어서 트랜잭션을 분석하는 기법이다.
• 프로세스와 데이터 사이에 관계 의존성을 CREATE, READ, UPDATE, DELETE 연산으로 테이블 간에 매트릭스를 만들어서 트랜잭션을 분석한다.

CRUD 매트릭스(CRUD Matrix)

프로세스와 데이터 사이에 관계 의존성을 CRUD(Create, Read, Update, Delete)로 표현한 매트릭스이다.

 

CRUD 매트릭스 구성요소 

엔터티 타입 / 단위 프로세스 / CRUD

 

CRUD 매트릭스 필요성

• 모델링 작업 검증
• 중요 산출물
• 테스트 시 사용 산출물
• 인터페이스 현황 파악

 

 

SQL 성능 튜닝

SQL 성능 튜닝은 최소의 자원을 이용하여 데이터베이스로부터 최적의 성능을 제공하도록 하는 개선활동이다.

 

SQL 성능 튜닝 기법 

옵티마이저 조정 / 힌트 사용 / 부분 범위 처리 사용 / 인덱스 사용

 

---

3-5 데이터 전환 기술

---

 

데이터 전환

• 원천의 데이터베이스로부터 목적 데이터베이스를 만드는 활동이다.
• 목적 시스템의 데이터 모델에 적합하게 ETL 활동을 거친다.
• 목적 데이터베이스에 데이터 적재 후 그 결과를 검증한다.

 

 

초기 데이터 구축

초기 데이터 구축 전략

초기 데이터 구축 / 데이터 복구 방안 마련 / DB 구축 지침 제시

초기 데이터 구축 절차

구축 전략수립 → 대상파악 → 범위 확정 → 고려사항 도출

 

 

 

ETL(Extraction, Transformation, Loading)

• ETL은 원천 시스템에서 데이터를 추출(Extraction)하여 변환(Transfor-mation) 작업을 거쳐 목적 시스템으로 적재(Load)하는 프로세스이다.
• 조직 내/외부의 여러 소스들로부터 분석을 위한 저장소로 데이터를 이동시키는 일련의 프로세스이다.

ETL 프로세스

• 때때로 시간 절약을 위한 3가지 ETL 단계를 동시에 수행한다.
• 일반적으로 발생하는 데이터 변환에는 필터링, 정렬, 집계, 중복 제거, 유효성, 검증 등의 작업이 포함된다.
• ETL의 변환 작업은 특수한 엔진에서 진행되며, 종종 변환 중인 데이터가 준비 테이블에서 임시로 보유되었다가 대상에 로드된다.

ETL 프로세스 추출 방법

JDBC 기술 이용 / ODBC 기술 이용 / Flat File 생성

 

 

 

파일 처리 기술

파일 처리 기술의 유형

순차 파일(Sequential File) / 색인 순차 파일(Indexed Sequential File) / 직접 파일(Direct File)

 

색인 순차 파일(Indexed Sequential File)

• 레코드의 삽입, 삭제, 갱신이 모두 용이하다.
• 순차처리와 랜덤처리가 모두 가능하다.
• 레코드들을 키 값 순으로 정렬시켜 기록한다.

데이터 전환 수행

데이터 전환 절차

• 현행(AS-IS) 시스템에서 목표(TO-BE) 시스템으로 전환을 위한 단계를 구분한다.
• 각 단계별 작업 내용과 사용되는 도구를 별도로 기록한다.
• 원천 데이터베이스의 데이터는 백업하여 스테이징(Staging) 데이터베이스로 복구한 후 정비 및 변환 작업을 수행한다.
• 데이터 전환이 완료된 후 검증 단계에서는 스테이징 데이터베이스가 아닌 원천 데이터베이스와 비교하여 데이터 오류를 확인해야 한다.

 

데이터 전환 수행 단계

1. 요구사항 분석 단계(전환 계획 및 요건정의)
2. 설계 단계(전환 설계)
3. 구현 단계(전환 개발)
4. 테스트 단계(전환 테스트 및 검증)
5. 데이터 전환 단계

 

체크리스트(Checklist)

체크리스트는 전환 프로그램의 에러, 시간제약, 비즈니스 로직 변경, 긴급 상황 및 위험요인 대응을 위한 측정 가능한 목록이다.

체크리스트 작성 내용

• 수행 작업의 상세항목
• 작업내역
• 예정 시작/종료 시간
• 작업자

데이터 정제

데이터 정제 요청서 작성 항목

정제 아이디(ID) / 정제 제목 / 관련 테이블 / 예상 처리건수

 

데이터 정제 보고서 작성 원칙

정제 아이디(ID) / 정제 건수 / 전환 결과 / 미처리 사유 / 대응 방안

 

 

 

데이터 품질 분석

원천데이터의 품질을 검증함으로써 전환의 정확성을 보장할 수 있다.

 

데이터 품질 요소

데이터 값(Value) / 데이터 구조(Data Hierarchy) / 관리 프로세스(Data Management Process)

 

원천 데이터 품질 분석

• 필수 항목의 데이터가 모두 존재하는가?
• 데이터의 유형이 정확하게 관리되고 있는가?
• 날짜의 경우 날짜로서 유효한 형태를 가지고 있는가?
• 금액의 경우 유효한 값의 범위인가?
• 모든 일자의 시점이 업무 규칙에 위배되지 않고 정확하게 설정되어 있는가?
• 업무 규칙에 위배되는 잘못된 정보가 존재하는가?
• 잔액의 총합이 회계 정보와 동일한가?
• 보고서 값과 실제 데이터 값이 일치하는가?

 

목적 데이터 품질 분석

• 특정 기준으로 분류된 데이터가 일치하는가?
• 보고서 항목 또는 통계 수치는 정확한가?
• 샘플링 한 목적데이터의 대상 항목이 모두 일치하는가?
• 특수한 관계가 있는 고객의 추출 데이터는 정확한가?

 

 

오류 데이터 측정

• 데이터 품질 기준에 따라 정상 데이터와 오류 데이터를 분리한다.
• 정량적 측정을 통해 나온 결과를 토대로 오류 관리 목록에 기재한다.

 

오류 원인 파악 / 정제 여부 결정

구분	지표	설명
심각도	상(High)	데이터 이행을 진행할 수 없게 만드는 오류
	중(Middle)	데이터 이행 전반에 영향을 미치는 오류
	하(Low)	데이터 이행의 영향을 미치지 않는 오류
상태	열린(Open)	오류가 보고되었지만 아직 분석되지 않은 상태
	할당된(Assigned)	수정을 위해 오류를 개발자에게 할당한 상태
	수정된(Fixed)	개발자가 오류를 수정한 상태
	종료된(Closed)	재 테스트 시 오류가 발견되지 않은 상태 수정된 오류 미 충족 시 오류 상테 'Open'으로 변경
	연기된(Deferred)	낮은 우선순위로 오류 순위를 연기한 상태
	분류된(Classified)	프로젝트 내 오류가 아니라고 판단된 상태

 

참고
https://simuing.tistory.com/entry/2021-정보처리기사-필기요약-데이터-전환?category=845723 [시뮝 연구소]

https://simuing.tistory.com/entry/2021-정보처리기사-필기요약-물리-데이터베이스-설계?category=845723 [시뮝 연구소]

https://simuing.tistory.com/entry/2021-정보처리기사-필기요약-논리-데이터베이스-설계?category=845723 [시뮝 연구소]

https://simuing.tistory.com/entry/2021-정보처리기사-필기요약-SQL-응용?category=845723 [시뮝 연구소]