데이터베이스 정리

● 데이터베이스의 특성

데이터베이스는 조직에서 업무 운영과 의사결정에 필요한 데이터들을 여러 사용자(또는 응용시스템)들이 공동으로 사용할 수 있도록 구조적으로 통합하여 컴퓨터로 저장한 데이터들의 모음을 의미

- 실시간 접근성 : 저장된 데이터는 실시간 접근성을 보장

- 계속적인 변화 : 갱신, 삽입, 삭제 등의 연산에 의해 내용이 동적으로 변경

- 동시 공유 : 저장된 데이터는 다수의 사용자에 의해 동시에 공유

- 내용에 의한 강조 : 저장된 데이터의 내용을 이용하여 데이터에 접근

데이터베이스는 Shared(공용) 데이터, Stored(저장) 데이터, Operational(운영) 데이터, Integrated(통합) 데이터로 정의됨

 

● SQL 분류

데이터 질의어(DQL)	데이터베이스에 저장된 데이터를 검색하는데 사용하는 질의어 (Query Language)	SELECT
데이터 조작어(DML)	데이터베이스에 저장된 데이터를 수정, 삭제, 추가하는 명령어 (Manipulation Language)	INSERT, UPDATE, DELETE
데이터 정의어(DDL)	데이터베이스 객체를 생성하고 수정, 삭제하는 명령어 (Definition Language) 데이터베이스의 스키마를 정의, 스키마에 대한 명세는 시스템 카탈로그에 저장	CREATE, ALTER, DROP
데이터 제어어(DCL)	데이터베이스의 규정이나 기법을 정의하고 제어하는 언어 (Control Language) 사용자 권한 부여/취소, 트랜잭션 제어	GRANT, REVOKE, COMMIT, ROLLBACK

 

● DDL (데이터 정의어)

생성	CREATE	DB 객체 생성
변경	ALTER	DB 객체 변경
삭제	DROP	DB 객체 삭제
	TRUNCATE	DB 객체 내 튜플 삭제

 

● DCL 분류

- COMMIT : 수행된 결과를 실제 물리적 디스크로 저장

- ROLLBACK : 명령 수행 실패를 의미하며 수행된 결과를 원복시킴

- SAVEPOINT(checkpoint) : 저장된 지점부터 현재까지 일부만 ROLLBACK 가능

- GRANT : 데이터베이스 사용자에게 사용권한 부여

- REVOKE : 데이터베이스 사용자에게 부여된 사용 권한 취소

 

● 와일드카드 문자

% : 문자가 없거나 하나 이상의 특정 문자를 검색 ex) '%신', '신%', '%신%'

_ : 하나의 문자 검색

 

● SQL의 부분범위 처리

SQL 부분범위 처리는 조건을 만족하는 전체범위를 처리하는 것이 아니라 일단 Array size까지만 처리하여 추출하는 처리 방식

부분범위 처리 목적 :

- 스캔 범위를 나누어서 운반 단위를 가능한 빨리 채워서 처리 속도를 향상

- 일부분만 처리하고서도 Optimizer의 특성을 이용하여 정확한 결과를 도출

- 처리 범위가 넓더라도 빠른 속도를 얻도록 하기 위함

부분범위 처리 적용 원칙

부분 범위처리의 자격	논리적으로 일부분만 처리한 결과가 전체범위를 읽어 추가적인 가공을 하지 않고도 처리한 결과와 동일하다면 자격이 있음
부분 범위처리를 할 수 없는 경우	Order By가 사용된 경우 UNION, MINUS, INTERSECT 등 조회 후 추가 연산을 사용한 경우
부분 범위처리를 할 수 없는 경우의 대체	Order by : Index를 이용하여 Order by를 하지 않아도 되는 형태로 대체 MINUS, INTERSECT : EXISTS, NOT EXISTS, IN, NOT IN 등으로 대체

sql 부분범위 처리 방안

- SQL 구문에 Order by가 있는 경우 인덱스 등을 이용하여 Order by를 삭제하는 형태로 변환

- 결과 컬럼을 얻어올 때 인덱스에서 모두 가져올 수 있는 항목인지를 살펴서 인덱스가 다시 테이블을 읽지 않아도 되는 형태로 사용

- 보통 사용하는 MAX(seq) + 1 형태를 버리고 역순 인덱스를 이용하여 Next Seq(다음 시퀀스값)를 구하는 형태로 변경

- 데이터의 존재여부를 체크하는 등의 로직을 수행해야 할 때, count()를 수행하는 것보다는 EXISTS를 이용하여 존재여부를 파악

구분	전체범위처리	부분범위처리
특징	주어진 조건의 범위가 좁은 경우는 문제가 없으나 넓은 경우는 빠른 수행 속도를 기대하기 어려움	처리할 범위가 아무리 넓다고 하더라도 그 범위 중의 일부만 처리
스캔 방법	드라이빙 조건을 만족하는 범위를 모두 스캔	드라이빙 조건을 만족하는 범위를 차례로 스캔
체크조건 처리 방법	체크조건 검증한 후 성공한 건에 대해 임시 저장공간에 저장	체크 조건을 검증하여 성공한 건을 바로 운반단위로 보냄
결과추출 방법	저장이 완료되면 필요한 2차 가공을 한 후 운반단위만큼 추출시키고 다음 요구가 있을 때까지 일단 멈춤	운반단위가 채워지면 수행을 멈추고 결과를 추출

 

● 시스템 카탈로그 (System Catalog)

- 데이터 사전 또는 시스템 테이블이라고도 하며, 데이터베이스의 객체(사용자, 릴레이션, 뷰, 인덱스, 권한 등)와 구조들에 관한 모든 데이터를 포함하는 시스템 테이블을 의미함.

- 시스템 카탈로그는 사용자 릴레이션처럼 SELECT 문을 사용하여 내용을 검색할 수 있지만 직접 갱신할 수는 없음. 즉, DELETE, UPDATE 또는 INSERT 문을 사용하여 시스템 카탈로그를 변경할 수 없음

- DBMS는 시스템 카탈로그에 릴레이션, 애트리뷰트, 인덱스, 사용자, 권한 등 스키마 객체 유형별로 별도의 정보 테이블을 유지함

 

● 트랜잭션의 상태

트랜잭션 = DB의 상태를 변화시키기 위해서 논리적 기능을 수행하는 작업의 단위

- 활동 상태 (Active) : 트랜잭션이 시작되어 연산들이 정상적으로 실행 중인 상태

- 부분완료 상태 (Partially Committed) : 트랜잭션에 정의된 모든 연산의 실행이 끝난 상태

- 완료 상태 (Committed) : 트랜잭션의 성공적 종료 상태

- 실패 상태 (Failed) : 트랜잭션이 완료되지 못하고 더이상 실행되지 못하는 상태

- 철회 상태 (Aborted) : 트랜잭션이 실패한 후 실행되기 이전으로 복귀된 상태

 

● 병행 제어

병행제어 = 다중 사용자 환경을 지원하는 데이터베이스 시스템에서 여러 트랜잭션이 성공적으로 동시에 실행될 수 있도록 지원하는 기능으로 동시성 제어라고도 하며, 다중 사용자 환경을 지원하는 데이터베이스 시스템의 경우 필수 지원 기능임

목적 → 트랜잭션의 직렬성을 보장하고 동시 수행 트랜잭션 처리량 최대화, 데이터베이스 시스템의 공유도 최대화, 응답시간 최소화, 데이터의 무결성과 일관성을 보장하기 위해 수행함

트랜잭션의 직렬성은 다중의 트랜잭션이 동시에 병행수행 되더라도 개별 트랜잭션이 차례대로 수행되는 것과 결과가 같은 데이터베이스의 일관성을 보장하는 특성

 

병행제어를 하지 않을시 문제점 :

갱신내용 손실 (Lost update)	트랜잭션들이 동일 데이터를 동시에 갱신할 경우 발생하는 문제로 한 트랜잭션이 데이터를 갱신한 후 트랜잭션을 종료하기 전에 다른 트랜잭션이 그 갱신 값을 또다시 갱신하는 경우에 발생
오류 데이터 읽기 (현황파악 오류, Dirty read)	트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조함으로써 발생하는 오류로 트랜잭션 T2는 T1이 연산을 수행하는 중간값을 읽어 합을 계산함으로써 잘못된 결과를 얻게되는 현상
모순성 (Inconsistency)	두 트랜잭션이 동시에 실행할 때 DB가 일관성이 없는 모순된 상태로 남는 문제로 복수의 사용자가 동시에 DB를 Access하여 갱신함으로써 데이터들의 값이 상호 일치하지 않거나 출력된 정보에 모순이 나타나는 경우가 발생
연쇄 복귀 (Cascading Rollback) 혹은 회복 불능 (Unrecoverability)	복수의 트랜잭션이 Data 공유시 특정 트랜잭션이 실패하여 롤백하고자 해도 다른 트랜잭션이 이미 처리한 부분에 대해서는 최소 불가한 상태가 발생하며 또는 트랜잭션이 연쇄적으로 취소하는 경우가 발생

 

● 로킹 (Locking)

로킹 = DB 관리에서 하나의 트랜잭션에 사용되는 데이터를 다른 트랜잭션이 접근하지 못하게 하는 것을 의미하며, 트랜잭션들은 갱신할 때는 반드시 로킹(Lock) → 실행(Execute) → 해제(Unlock)의 규칙을 따라 실행

로킹 단위는 병행 제어에서 한번에 잠금할 수 있는 단위로 데이터베이스, 테이블, 레코드, 필드 등에 사용됨.

- 로킹 단위가 크면 로킹 수가 작아 관리하기 쉽지만, 공유성 수준이 낮아짐

- 로킹 단위가 작으면 로킹 수가 많아 관리하기 복잡하지만, 공유성 수준이 높아짐

 

● 동시성 제어(병행 제어) 기법

로킹  (Locking)	DB 관리에서 하나의 트랜잭션에 사용되는 데이터를 다른 트랜잭션이 접근하지 못하게 하는 것을 의미하며, 트랜잭션들은 갱신할 때는 반드시 로킹(Lock) → 실행(Execute) → 해제(Unlock)의 규칙을 따라 실행
검증 기법 (Validation)	트랜잭션 처리시 먼저 메모리상에서 복사본에 대한 연산을 수행하고 검증 완료시 DBMS에 반영하는 기법 읽기(Read) 단계는 메모리상에서 데이터 연산 수행 검증(Validation) 단계는 트랜잭션의 직렬성 확인 단계 쓰기(Write) 단계는 검증 성공 시 DBMS에 반영(Commit)하고, 검증 실패시 철회(Rollback)하는 단계
타임 스탬프 기법 (Timestamp ordering)	트랜잭션 순서 규칙은 시스템 계수기, 논리적 계수기를 이용하여 해당 트랜잭션의 도착 시간 별로 타임스탬프를 할당하는 기법 → 트랜잭션이 시스템 들어오는 순서대로 고유값 부여 직렬화 기법으로 트랜잭션 간의 순서를 미리 정하는 방법

 

● 데이터베이스 회복기법

- 데이터베이스를 갱신하는 도중에 시스템이 고장이 나도 데이터베이스의 일관성을 유지하는 DBMS 기능

지연 갱신 (Deferred Update)	트랜잭션이 성공적으로 종료될 때까지 데이터베이스에 대한 실질적인 갱신을 연기하는 기법
즉시 갱신 (Immediate Update)	트랜잭션이 데이터를 변경하면 트랜잭션이 부분 완료되기 전이라도 즉시 실제 데이터베이스에 반영하는 기법
그림자 페이지 (Shadow Paging)	갱신 이전의 데이터베이스를 일정 크기의 페이지 단위로 구성하여 각 페이지마다 복사 본인 그림자 페이지로 별도 보관해 두고, 실제 페이지를 대상으로 트랜잭션에 대한 변경 작업을 수행하는 기법
검사점 (Check Point)	체크포인트(Checkpoint) 회복 기법 시스템 장애가 발생하였을 경우 검사점 이후 장애발생 이전에 완료된 경우 Undo 수행, 장애발생 시점까지 완료하지 못한 경우 Redo 수행을 통해 회복하는 기법
미디어 회복 (Media Recovery)	디스크 장애가 발생하면 가장 최근의 덤프 내용을 디스크에 적재하고, 로그를 이용하여 가장 최근 덤프 이후 완료된 트랜잭션들에 대해 Redo 작업을 수행하여 회복하는 기법

 

회복의 구성 요소

- Redo :  데이터베이스 내용 자체가 손상이 된 경우 가장 최근의 복제본을 적재 후 이후 일어난 변경만을 로그를 이용하여 재실행함으로써 데이터베이스 회복

- Undo : 데이터베이스 내용 자체는 손상되지 않았지만 변경 중이거나 변경된 내용에 대한 신뢰성을 잃어버린 경우 모든 변경 내용을 취소하여 데이터베이스 회복

 

● 데이터베이스 장애 유형

트랜잭션 장애	트랜잭션 내부에서 입력 데이터 오류, 불명확한 데이터, 시스템 자원 요구의 과다 등 비정상적인 상황으로 인하여 트랜잭션 실행이 중지되는 현상 논리적 오류 : 내부적인 오류로 트랜잭션을 완료할 수 없음 시스템 오류 : Deadlock 등의 오류 조건으로 활성 트랜잭션을 강제 종료
시스템 장애	하드웨어 오동작, 정전, 소프트웨어(운영체제나 DBMS 등) 오류, 교착 상태 등에 의해 실행중인 모든 트랜잭션들이 더 이상 실행을 계속할 수 없는 현상 전원, 하드웨어, 소프트웨어 등의 고장 시스템 장애로 인해 저장 내용이 영향 받지 않도록 무결성 체크
디스크 장애	저장 장치인 디스크 블록의 손상이나 디스크 헤드의 충돌 등에 의해 데이터베이스의 일부 또는 전부가 물리적으로 손상되는 현상 디스크 스토리지의 일부 또는 전체가 붕괴되는 경우 가장 최근의 덤프와 로그를 이용하여 덤프 이후에 완결된 트랜잭션을 재실행(Redo)
사용자 장애	사용자의 실수, 무결성 규정 위반 등으로 질의 실행이 실패하는 현상 사용자들의 데이터베이스에 대한 이해 부족으로 발생 DBA가 데이터베이스 관리를 하다가 발생시키는 실수

 

● SQL의 View

View = 허용된 자료만을 제한적으로 보여주기위해 하나 이상의 테이블로부터 유도된 이름을 가지는 가상 테이블

 

View의 특징 :

- 저장 장치내에 물리적으로 존재하지 않음 (가상 테이블)

- 데이터 보정 등 임시적인 작업을 위한 용도로 사용

- 기본 테이블과 같은 형태의 구조로 조작이 거의 비슷

- 삽입, 내용 갱신에 제약이 따름

- 논리적 독립성을 제공

- 독자적인 인덱스를 가질 수 없음

- Create를 이용해서 뷰 생성

- 필요한 데이터만 골라 뷰를 이용하여 처리

 

장점 :

- 논리적 독립성 제공 → View는 논리 테이블임 (테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨)

- 사용자 데이터 관리 용이 → 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능

- 데이터 보안 용이 → 중요 보안 데이터를 저장중인 테이블에는 접근 불허하고, 해당 테이블의 일부 정보만 View를 통해 허용하는 방식으로 데이터에 대한 접근 제어가 가능

단점 :

- 뷰 자체 인덱스 불가 → 인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못함

- 뷰 정의 변경 불가 → View의 정의를 변경하려면 View를 삭제하고 재생성

- 데이터 변경 제약 존재 → View의 내용에 대한 삽입, 삭제, 변경 제약

CREATE	View 생성 CREATE VIEW <View Name> (컬럼 목록) AS <뷰를 통해 보여줄 데이터 조회용 쿼리문>
DROP	View 삭제 DROP VIEW <View Name> {RESTRICT | CASCADE}; RESTRICT = 뷰를 다른 곳에서 참조하고 있으면 삭제가 취소 CASCADE = 뷰를 참조하는 다른 뷰나 제약조건까지 모두 삭제

 

● 인덱스

인덱스 = 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조로서 자동으로 생성은 되지 않으며 데이터 정의어(DDL)의 명령어를 이용하여 생성, 변경, 삭제를 실행

- 데이터베이스의 성능향상을 위한 DB튜닝 기법으로 가장 많이 사용하며, 과다한 인덱스 사용은 DML의 성능이 떨어지며 불필요한 인덱스가 많아지면 DB에 차지되는 공간이 커짐

- PK(Primary Key) 컬럼은 PK를 생성할 때 자동으로 PK 인덱스가 생성되며, 인덱스가 생성되어 있다면 테이블의 일부분을 검색(Range scan)하여 데이터를 빠르게 출력함

- 조건절에 '='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 대상이 줄어들어 검색 속도를 높일 수 있으며, 경우의 수가 적은 일련번호, 주민등록번호 등 자주 빈번히 사용하는 컬럼을 대상으로 생성 시 검색 성능이 우수함

 

인덱스 문법 :

[UNIQUE]	인덱스가 걸린 컬럼에 중복값을 허용하지 않음	CREATE 테이블에서 사용하는 UNIQUE 제약 조건과 동일한 의미
<index_name>	생성하고자 하는 인덱스명
<table_name>	인덱스 대상 테이블명
(<column(s)>)	인덱스 대상 테이블의 특정 컬럼 이름들	복수 컬럼 지정 가능

 

● 집합 연산자

집합 연산자 = 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식이며 여러 질의 결과를 연결하여 하나로 결합하는 방식을 사용

UNION	여러 SQL문의 결과에 대한 합집합 (중복 행 제거)
UNION ALL	여러 SQL문의 결과에 대한 합집합 (중복 행 제거하지 않음)
INTERSECTION	여러 SQL문의 결과에 대한 교집합 (중복 행 제거)
EXCEPT (MINUS)	앞의 SQL문 결과와 뒤의 SQL문 결과 사이의 차집합 (중복 행 제거, 일부 제품의 경우 MINUS 사용)

 

● 조인 (Join)

조인 = 두개 이상의 테이블로부터 연관된 데이터를 결합해서 검색하는 방법으로 논리적 조인과 물리적 조인으로 구분

논리적 조인	사용자의 SQL문에 표현되는 테이블 결합 방식 내부 조인(INNER JOIN), 외부 조인(OUTER JOIN), 셀프 조인(SELF JOIN)
물리적 조인	데이터베이스 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식 Nested Loop Join, Merge Join, Hash Join

- 조인 조건은 두 테이블 사이에 속하는 컬럼 값들을 비교 연산자로 연결한 형태이며, 조인 조건을 생략한 경우 또는 조인 조건을 잘못 작성하게 되면 카티션 프로덕트(Caresian Product 또는 Cross Join) 연산이 수행되어 원하지 않은 결과를 얻게 됨

 

논리적 조인의 유형 :

내부 조인 (INNER JOIN)	동등 조인 (EQUAL JOIN)	특정 컬럼을 비교하여 같은 값을 추출
	자연 조인 (NATURAL JOIN)	두 테이블의 모든 컬럼을 비교하여 같은 컬럼명을 가진 모든 컬럼 값이 같은 경우를 추출
	교차 조인 (CROSS JOIN)	조인 조건의 없는 모든 데이터의 조합을 추출
외부 조인 (OUTER JOIN)	왼쪽 외부 조인 (LEFT OUTER JOIN)	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
	오른쪽 외부 조인 (RIGHT OUTER JOIN)	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
	완전 외부 조인 (FULL OUTER JOIN)	양쪽의 모든 데이터 추출
셀프 조인 (SELF JOIN)	셀프 조인 (SELF JOIN)	한 테이블 내에서 조인 연산을 수행

 

● 서브쿼리

서브쿼리 = 다른 SQL문 안에 포함되어 사용된 또 다른 SQL문으로서 WHERE 절 뿐만 아니라 INSERT, DELETE, UPDATE 문에도 사용

동작 방식에 따른 서브쿼리	비상관(Uncorrelated) 서브쿼리	서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태
	상관(Correlated) 서브쿼리	서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태
반환 데이터 형태에 따른 서브쿼리	Single row(단일 행) 서브쿼리	서브쿼리의 결과가 항상 1건 이하인 서브쿼리 단일 행 비교연산 ( =, <=, >=, <>) 가 사용됨
	Multiple row(다중 행) 서브쿼리	서브쿼리의 실행 결과가 여러 건인 서브쿼리 다중 행 비교 연산자 (IN, ALL, ANY, SOME, EXISTS)가 사용됨
	Multiple column(다중 컬럼) 서브쿼리	서브쿼리 결과가 여러 컬럼으로 반환되는 서브쿼리 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 때 서브쿼리와 메인쿼리에서 비교하는 컬럼 개수와 위치가 동일

 

● SQL 구문

- GROUP BY절은 사용된 애트리뷰트에 동일한 값을 갖는 튜플(행)들이 각각 하나의 그룹으로 묶여서 각 그룹별로 하나의 결과를 생성하며, HAVING 절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함

- Having은 GROUP BY가 반드시 필요한 그룹제한조건 명령어

- SELECT 절에 그룹함수가 사용되면 그룹함수를 제외한 컬럼은 GROUP BY절에 기술되어야 함

- CUBE 그룹 함수는 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 함수이며, 내부적으로 대상 컬럼의 순서를 변경하여 또 한번의 쿼리를 수행하고 총계는 양쪽 쿼리에서 모두 수행 후 한쪽에서 제거

- ROLLUP 그룹 함수는 소계 등 중간 집계값을 산출하기 위해 사용하며 컬럼의 수보다 하나 더 큰 레벨 만큼의 중간 집계값을 생성할 때 사용

 

● 집계성 SQL

집계성 SQL = 총합, 평균 등의 데이터 분석을 위해 복수 개의 행을 기준으로 데이터를 분석하는 SQL문(함수)을 말하며 집계 함수(Aggregation function), 그룹 함수(Group function), 윈도우 함수(Window function)으로 구성됨

- 집계함수는 GROUP BY 절과 함께 사용되어 복수행에 대한 개수, 합계, 평균, 최소값, 최대값 등을 계산하는 함수

- 그룹 함수는 소그룹의 소계, 총계와 같이 레벨별로 집계 결과를 조회하기 위해 사용하는 함수로서 ROLLUP(), CUBE(), GROUPING SETS() 함수가 대표적

- 윈도우 함수는 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수로 분석(Analytic) 함수 또는 순위(Rank) 함수라고 함

 

● 윈도우 함수

순위관련 함수	RANK(), DENSE_RANK(), ROW_NUMBER()
집계관련 함수	SUM(), MAX(), MIN(), AVG(), COUNT()
행 순서관련 함수	FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()
비율관련 함수	CUME_DIST(), PERCENT_RANK(), NTILE()

CUME_DIST() = 파티션별 윈도우 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 구할때 사용

RANK() = ORDER BY절과 함께 사용되면, 특정 컬럼에 대한 순위를 구할때 사용

LEAD() = 행 순서관련 함수로서 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 때 사용

 

● 절차형 SQL의 기본 구성 요소

절차형 SQL = SQL문의 연속적인 실행이나 조건에 따른 분기, 반복 등의 제어를 활용하여 다양한 기능을 수행하는 데이터베이스 저장 모듈로서 반복 또는 자주 수행하는 DB 작업을 효율적으로 수행할 수 있으며, 잘 정의된 절차형 SQL은 소프트웨어 개발 생산성이 향상됨

- 절차형 SQL 종류(프로시저, 사용자 정의함수, 트리거)와 DBMS 벤더(Oracle PL/SQL, SQL Server T - SQL 등) 마다 약간의 차이가 있음

구성요소 :

- DECLARE : 대상이 되는 프로시저, 사용자 정의함수 등을 정의

- BEGIN : 프로시저, 사용자 정의함수가 실행되는 시작점

- END : 프로시저, 사용자 정의함수가 실행되는 종료점

 

● 프로시저

프로시저 = 매개변수를 받을 수 있고 반복해서 사용할 수 있는 이름이 있는 SQL 블록으로 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 SQL블록을 데이터베이스에 저장하기 위해 생성

- 프로시저 호출을 통해 실행되며, 일련의 SQL 작업을 포함하는 데이터 조작어(DML)를 수행하며 일일 마감작업 또는 일련의 배치 작업 등을 프로시저를 활용하여 관리하고 주기적으로 수행함

- DROP 명령문을 이용하여 삭제

 

프로시저 정의 문법 :

CREATE	DBMS 내에 프로시저 생성
[OR REPLACE]	기존 프로시저 존재 시에 현재 컴파일하는 내용으로 덮어쓴다는 의미(Overwrite)이며, 동명의 프로시저가 존재할 때 CREATE 명령문만 사용하면 컴파일 시에는 에러가 발생
PARAMETER	외부에서 프로시저 호출 시 변수를 입력 또는 출력
MODE	변수의 입력 또는 출력을 구분 IN : 운영체제에서 프로시저 전달 MODE OUT : 프로시저에서 처리된 결과가 운영체제 전달 MODE INOUT : IN과 OUT의 두 가지 기능을 동시 수행하는 MODE

 

● 트리거 (trigger)

트리거 = 데이터베이스에 특정한 변경이 가해졌을때(명시된 이벤트가 발생할 때마다) DBMS가 이에 대응해서 자동적으로 호출하는 일종의 프로시저를 말함

프로시저와 함수는 그 실행이 외부적인 실행 명령에 의해 이루어지는데 반해, 트리거의 실행은 트리거링 사건(Triggering Event)에 의해 내부적으로 이루어지며 트리거를 이벤트 - 조건 - 동작(ECA) 규칙이라고도 함

- 사건(event) : 트리거를 가동하는 사건 (테이블에 대한 INSERT, UPDATE, DELETE 문)

- 조건(condition) : 트리거 수행 여부를 결정하는 검사조건 (WHERE 조건)

- 동작(action) : 트리거가 가동될 때 수행하는 작업 (BEGIN ... END 블록)

 

● 문장 트리거와 행 트리거

문장 트리거 (Statement - Level Trigger)	행 트리거 (Row - Level Trigger)
컬럼의 각 데이터 튜플(행) 제어 불가능 컬럼의 데이터 값에 상관없이 컬럼에 변화가 일어남을 감지하여 실행되는 트리거	컬럼의 각각의 데이터 튜플(행)에 변화가 생길때마다 실행 변화가 생긴 데이터 튜플의 실제 값 제어 가능 데이터 튜플의 실제 값을 수정, 변경 또는 저장할 때 사용

 

● 관계 데이터 모델

관계 데이터 모델 = E.F CODE 박사가 1972년 제안한 데이터 모델로써 오늘날 대부분의 데이터베이스 관리시스템에서 지원하는 데이터 모델

- 관계 모델은 실세계 데이터를 행과 열로 된 표(테이블, 릴레이션) 형태로 저장하고, 한 테이블의 필드 값을 이용하여 다른 테이블에 관련된 데이터를 찾는 식으로 검색하는 데이터 모델임

- 관계 데이터 모델의 구성은 데이터베이스의 테이블(릴레이션)들의 집합으로 표현되며, 테이블은 튜플(행, 레코드)들의 집합으로 표현되고, 튜플은 애트리뷰트(컬럼, 필드, 혹은 속성)들로 구성됨

속성 (attributes)	개체의 성질, 분류, 식별, 수량, 상태 등을 나타내는 세부 정보의 관리 요소로서 개체를 구성하는 항목
튜플 (tuple)	릴레이션의 행을 구성하는 속성(attributes) 값들의 집합 행(row), 레코드(record)와 같은 개념
차수 (degree)	속성(attributes)들의 수
카디날리티 (cardinality)	튜플(tuple)들의 수
도메인 (domain)	하나의 속성(attributes)이 취할 수 있는 같은 타입의 원자(atomic) 값들의 집합 표현되는 속성 값의 범위를 나타냄
릴레이션 인스턴스 (relation instance)	릴레이션의 어느 시점에 들어있는 튜플(행)들의 집합 튜플들의 집합으로 현재 들어가 있는 실제 데이터를 지칭함

 

데이터 모델에 표시할 요소

데이터 구조 (Structure)	논리적으로 표현된 개체 타입들 간의 관계로서 데이터 구조 및 정적 성질을 표현
연산  (Operation)	데이터베이스에 저장된 실제 데이터를 처리하는 작업에 대한 명세로서 데이터베이스를 조작하는 기본 도구
제약 조건 (Constraint)	데이터베이스에 저장될 수 있는 실제 데이터의 논리적인 제약 조건

 

● 데이터 모델 속성(Attribute)의 종류

속성 = 하나의 개체는 연관된 속성들의 집합으로 설명되며 독립적인 의미를 갖지 않음. 또한 데이터의 가장 작은 논리적 단위로서 파일 구조상의 데이터 항목 또는 데이터 필드에 해당됨

- 속성 중에서 개체를 고유하게 식별할 수 있는 속성 또는 속성들의 집합을 주 식별자 또는 기본키라고 하며 E-R 다이어그램에서 기본키에 속하는 속성은 밑줄을 그어서 표시함

단순 속성 (Simple attribute)	더 이상 다른 속성으로 나눌 수 없는 속성 E-R 다이어그램에서 실선 타원으로 표현
복합 속성 (Composite attribute)	두개 이상의 속성으로 이루어진 속성 동일한 개체 타입이나 관계 타입에 속하는 속성 중에서 밀접하게 연관된 것을 모아놓은 것
단일 값 속성 (Single-valued attribute)	각 개체마다 정확하게 하나의 값을 갖는 속성 E-R 다이어그램에서 단순 속성과 동일하게 표현
다중 값 속성 (Multi-valued attribute)	각 개체마다 여러개의 값을 가질 수 있는 속성 E-R 다이어그램에서 이중선 타원으로 표현
저장된 속성 (Stored attribute)	다른 속성과 독립적으로 존재하는 속성 E-R 다이어그램에서 단순 속성과 동일하게 표현
유도된 속성 (Derived attribute)	다른 속성 값으로부터 획득한 속성 관계 데이터베이스에서 릴레이션의 속성으로 포함시키지 않는 것을 권장 E-R 다이어그램에서 점선 타원으로 표현

 

● E-R 다이어그램의 기호

	사각형	개체(Entity) 타입
	마름모	관계(Relationship) 타입
	타원	속성(Attribute)
	밑줄 타원	기본키 속성
	복수 타원	복합 속성 ex) 성명은 성과 이름으로 구성
	관계	1 : 1, 1 : N, N : M 등의 개체 관계에 대한 대응수를 선위에 기술
	선 링크	개체 타입과 속성을 연결

 

● 릴레이션의 특징

- 한 릴레이션에 포함된 튜플들은 모두 상이함

- 한 릴레이션에 포함된 튜플 사이에는 순서가 없음

- 튜플들의 삽입, 삭제 등의 작업으로 인해 릴레이션은 시간에 따라 변함

- 릴레이션 스키마를 구성하는 속성들간의 순서는 중요하지 않음

- 속성의 유일한 식별을 위해 속성의 명칭은 유일해야 하지만, 속성을 구성하는 값은 동일한 값이 있을수 있음

- 릴레이션을 구성하는 튜플을 유일하게 식별하기 위해 속성들의 부분집합을 Key로 설정

- 속성은 더 이상 쪼갤수 없는 원자값만을 저장

 

● 관계(Relationship)의 유형

- 관계는 개체들 사이에 존재하는 연관이나 연결로서 두개 이상의 개체 타입들 사이의 사상으로 생각할 수 있으며 관계 집합은 동질의 관계들의 집합을 말하며, 관계 타입은 동질의 관계들의 틀

- E-R 다이어그램에서 관계는 마름모로 표기하며, 관계 타입이 서로 연관시키는 개체 타입들을 관계 타입에 실선으로 연결하며 관계의 특징을 나타내는 속성을 가질 수 있으나, 키 속성은 갖지 않음

- 관계로 연결된 개체 타입들의 개수를 관계의 차수(degree)라고 하며, 실세계에서 가장 흔한 관계는 두개의 개체 타입을 연결하는 2진 관계

 

● 관계 데이터 언어

관계 데이터 언어 = 관계 데이터베이스의 릴레이션을 조작하기 위한 기본연산에는 관계대수(Relational algebra)와 관계해석(Relational calculus)이 있으며 사용자의 입장에서 볼때 데이터를 처리하는 데이터 언어가 되며 관계대수는 절차적 언어이며 관계해석은 비절차적 언어

- 관계대수는 관계형 데이터베이스에서 원하는 정보와 그 정보를 검색하기 위해서 어떻게 유도하는가를 기술하는 절차적인 언어이며, 릴레이션을 처리하기 위해 연산자와 연산규칙을 제공하는 언어로 피연산자가 릴레이션이고 결과도 릴레이션

- 관계해석은 Code 박사가 제안한 것으로 수학의 술어해석(Predicate calculus)에 기반을 두고있으며 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특징을 갖고있음. 또한 SQL문과 같은 질의어를 사용하며 튜플 관계해석과 도메인 관계해석으로 구성되며 원하는 데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어

 

● 관계 대수의 순수 관계 연산자

Select	시그마(σ)	릴레이션에 존재하는 튜플 중에서 선택 조건을 만족하는 튜플의 부분집합을 구하여 새로운 릴레이션 생성 릴레이션의 행에 해당하는 튜플을 구하는 것
Project	파이(π)	주어진 릴레이션에서 속성 리스트에 제시된 속성만 추출하는 연산 릴레이션의 열에 해당하는 속성을 추출하는 것
Join	⋈	공통 속성을 중심으로 두 개의 릴레이션을 하나로 합쳐서 새로운 릴레이션을 만드는 연산
Division	÷	X ⊃ Y 인 두개의 릴레이션 R(X)와 S(Y)가 있을 떄, R의 속성이 S의 속성값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만을 구하는 연산

 

● 관계 대수의 일반 관계 연산자

합집합 (Union)	∪	두 릴레이션에 존재하는 튜플의 합집합을 구하되, 결과로 생성된 릴레이션에서 중복되는 튜플 제거
교집합 (Intersection)	∩	두 릴레이션에 존재하는 튜플의 교집합을 구하는 연산
차집합 (Difference)	－	두 릴레이션에 존재하는 튜플의 차집합을 구하는 연산
교차곱 (Cartesian product)	×	두 릴레이션에 있는 튜플들의 순서쌍을 구하는 연산

 

● 관계 해석

관계 해석 = Code 박사가 제안한 것으로 수학의 술어해석(Predicate calculus)에 기반을 두고 있으며 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특징을 갖고 있음. 또한 SQL문과 같은 질의어를 사용하며 튜플 관계해석과 도메인 관계해석으로 구성되며 원하는 데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어

 

관계 해석의 특징 :

- 원하는 릴레이션을 정의하는 방법을 제공, 즉 원하는 정보가 무엇이라는 것만 정의하는 비절차 언어로서 튜플 관계 해석과 도메인 관계 해석이 있음.

- 연산들의 절차(sequence)를 사용하여 데이터를 가져오며 기본적인 연산자로 union, intersection, difference를 사용

튜플 관계해석	원하는 릴레이션을 튜플 해석식으로 정의하는 표기법 대표적인 언어로는 QUEL이 있음
도메인 관계해석	원하는 릴레이션을 도메인 해석식으로 정의하는 표기법 대표적인 언어로는 QBE가 있음

 

● 관계 해석 논리 기호

- 원자식, AND 연산자의 ∧과 OR 연산자인 ∨, NOT 연산자인 ㄱ, 그리고 정량자(∀, ∃)가 결합된 식을 말함

전칭 정량자 (Universal quantifier)	∀로 표현하며 "for all"이라고 읽는다. 전칭 정량자를 사용한 정형식은 모든 가능한 튜플 t에 대해 정형식 F(t)가 참일 때 참이 된다는 뜻	(∀t)(F(t))
존재 정량자 (Existential quantifier)	∃로 표현하며 "there exists"라고 읽는다. 존재 정량자를 사용한 정형식은 정형식 F(t)를 참으로 만드는 어떤 튜플 t가 하나라도 존재만 한다면 참이 된다는 뜻	(∃t)(F(t))

 

- 자유변수 : 정량자로 한정되지 않는 튜플 변수

- 속박변수 : 정량자로 한정된 튜플 변수.  

 

● 데이터베이스의 Key

- 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 튜플들을 서로 구분할 수 있는 기준이 되는 속성들을 말함

 

Key의 특징 :

유일성 (Uniqueness)	하나의 키 값으로 하나의 튜플만을 유일하게 식별할 수 있어야 함 기본키(primary key), 후보키(candidate key), 슈퍼키(super key)
최소성 (Minimality)	속성의 집합인 키가 릴레이션의 모든 튜플을 유일하게 식별하기 위해 꼭 필요한 속성들로 구성된 것을 의미 기본키(primary key), 후보키(candidate key)

Key의 종류 :

후보키 (candidate key)	릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별할 수 있는 하나 또는 몇개의 속성들의 집합 유일성과 최소성을 모두 만족
기본키 (primary key)	릴레이션의 유일한 식별자 (유일성, 최소성 모두 만족) 기본키로 지정된 속성은 같은 값을 갖지 못함 후보키 중에서 선정된 키로 중복값을 가질 수 없음 Not null, Unique, 외래키(foreign key)로 참조될 수 있음
대체키 (alternate key)	후보키가 둘 이상 되는 경우에 기본키로 선택되지 못한 후보키들 후보키 = 기본키 + 대체키
슈퍼키 (super key)	유일성만 있고 최소성이 없는 속성의 집합
외래키 (foreign key)	한 테이블의 키 중 다른 테이블의 튜플을 식별할 수 있는 키

 

● 데이터 무결성

- 데이터베이스에 저장된 데이터의 일관성과 정확성을 지키는 것을 말하며, 데이터가 무결성을 가지도록 하는 행위를 무결성 강화라고 함

엔티티(개체) 무결성 (Entity Integrity)	기본키는 반드시 값을 가짐 (NULL 허용 안함) 기본키는 유일성을 보장하는 최소한의 집합	Primary key NOT NULL
참조 무결성 (Referential Integrity)	외래키 속성은 참조할 수 없는 값을 지닐수 없음 외래키 값은 그 외래키가 기본키로 사용된 릴레이션의 기본키 값이거나 NULL 값일 것	Foreign key
속성 무결성 (Attribute Integrity)	컬럼은 지정된 데이터 형식을 반드시 만족하는 값만 포함	Character Date LONG VARCHAR2 NUMBER
키 무결성 (Key Integrity)	한 릴레이션에 같은 키 값을 가진 튜플들은 허용안됨	Primary key + Unique index
사용자 정의 무결성 (User defined Integrity)	모든 데이터는 업무 규칙(Buisness rule)을 준수해야 함	Trigger User define data type Check DEFAULT Value
도메인 무결성 (Domain Integrity)	특정 속성 값이 미리 정의된 도메인 범위에 속해야함	CHECK DEFAULT

 

● 테이블 제약 조건 설계

삭제 제약 조건 설계(Delete constraint) :

- 참조된 기본키의 값이 삭제될 경우의 처리내용을 정의

- Cascade : 참조한 테이블에 있는 외부키와 일치하는 모든 row가 삭제

- Restricted : 참조한 테이블에 있는 외부키에 없는 것만 삭제 가능

- Nullify : 참조한 테이블에 정의된 외부키와 일치하는 것을 null로 수정

수정 제약 조건 설계 (Update constraint) :

- 참조된 기본키의 값이 수정될 경우의 처리내용을 정의

- Cascade : 참조한 테이블에 있는 외부키와 일치하는 모든 row가 수정

- Restricted : 참조한 테이블에 있는 외부키에 없는 것만 수정 가능

- Nullify : 참조한 테이블에 정의된 외부키와 일치하는 것을 null로 수정

 

● 데이터베이스 개념적 설계 단계

개념적 설계 단계 = 한 조직체에서 사용되는 정보 모델 구축, 높은 추상화 수준의 데이터 모델을 사용. 대표적인 데이터 모델은 ER 모델

- 개체 타입, 관계 타입, 속성들을 식별하고, 속성들의 도메인을 결정, 후보키와 기본키를 결정

- 개념적 스키마(ER 스키마)는 ER 다이어그램으로 표현

- 논리적 설계 단계의 앞 단계에서 수행

요구사항 분석	DB에 저장할 대상, 제약 조건, 데이터 처리 등에 대한 요구사항 수집 개체와 애트리뷰트, 개체간 관계 파악, 데이터 처리 시 사용되는 주요 연산들, 연산 시 접근하는 데이터 양 등을 요구사항 분석
개념적 설계	개체 타입, 관계 타입, 속성들 식별, 속성들의 도메인 결정, 후보키와 기본키 결정 개념적 스키마(ER 스키마)는 ER 다이어그램으로 표현됨
논리적 설계	선택한 DBMS의 데이터 모델을 사용하여 논리적 스키마 생성 트랜잭션 인터페이스 설계, 스키마의 평가 및 설계 수행 스키마 정제(정규화) : 관계 데이터베이스 스키마를 더 좋은 관계 데이터베이스 스키마로 변환하기 위해서 정규화 과정을 적용
물리적 설계	데이터 처리 요구사항들을 만족시키기위해 저장 구조와 접근 경로등을 결정

 

● 함수 종속

함수 종속 = 속성 간 의존관계 즉, 릴레이션 R에서 속성 X가 속성 Y를 결정짓는 관계

X → Y로 표기하고,  Y는 X에 종속된다고 함 (X = 결정자, Y = 종속자)

 

암스트롱의 추론규칙 

- 릴레이션 속성간의 함수적 종속성을 유도해낼 수 있는 추론 규칙

기본규칙	반사규칙	X ⊇ Y 이면 X → Y
	증가규칙	X → Y 이면 XZ → YZ
	이행규칙	X → Y and Y → Z 이면 X → Z
유도규칙	결합규칙	X → Y and X → Z 이면 X → YZ
	분해규칙	X → YZ 이면 X → Y and X → Z
	의사이행규칙	X → Y and WY → Z 이면 WX → Z

 

완전 함수종속		일반 속성이 식별자 전부에 종속
부분 함수종속		일반 속성이 식별자 일부에 종속 제 2정규화 필요
이행 함수종속		일반 속성간 함수적 종속 제 3정규화 필요
결정자 함수종속		결정자가 후보키가 아닌 경우 존재, 후보키에 종속자 존재 BCNF 정규화 필요

 

● 이상현상

이상현상 = 정규화를 거치지 않으면 데이터베이스의 데이터들이 불필요하게 중복되어 릴레이션 조작시 문제가 발생하는데, 이를 이상현상(Anomaly)라고 함

삽입 이상	불필요한 데이터를 함께 삽입하지 않으면 데이터를 삽입하는 것이 불가능
수정(갱신) 이상	중복된 데이터 가운데 일부만 수정되어 데이터의 불일치가 발생
삭제 이상	어떤 데이터를 삭제하면 유용한 데이터도 함께 삭제가 됨

 

● 정규화

정규화 = 관계형 데이터베이스 설계 시 중복을 최소화하도록 데이터를 구조화하는 프로세스로써 제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 무손실 분해를 포함

정규화의 원칙은 낮은 차수의 정규화를 만족한 상태로 높은 차수의 정규화를 진행

 

정규화 수행 목적 :

- 하나의 테이블에서의 데이터의 삽입, 삭제, 변경이 정의된 관계들로 인하여 데이터베이스의 나머지 부분들로 전파되게 하는 것

- 어떤 관계라도 데이터베이스 내에서 표현이 가능하도록 만드는 것

- 관계에서 바람직하지 않은 삽입, 삭제, 갱신 이상이 발생하지 않도록 하는 것

- 새로운 형태의 데이터가 삽입될 때 관계를 재구성 할 필요성을 줄임

- 보다 간단한 관계 연산에 기초하여 검색을 보다 효율적으로 함

 

무손실 표현 → 같은 의미의 정보를 유지하면서 더 바람직한 구조를 만듦

자료의 중복성 감소 → 중복되는 정보는 삭제하거나 통합

분리의 원칙 → 독립적인 관계는 별개의 릴레이션으로 표현하고 릴레이션 각각에 대해 독립적 조작이 가능

 

정규화의 유형 :

제1정규형(1NF)	릴레이션 R의 모든 속성 값이 원자값을 가지는 릴레이션
제2정규형(2NF)	릴레이션 R이 제1정규형이고 기본키가 아닌 속성이 기본키에 완전 함수 종속일 때
제3정규형(3NF)	릴레이션 R이 제 2정규형이고 기본키가 아닌 속성이 기본키에 비이행적 non-transitive로 종속할 때 (직접 종속)
보이스/코드 정규형(BCNF)	릴레이션 R에서 함수 종속성 X → Y가 성립할 때 모든 결정자 X가 후보키 일 때
제4정규형(4NF)	릴레이션 R에서 MVD A → B가 존재할 때 R의 모든 애트리뷰트들이 A에 함수종속(FD)이면 R은 4NF (즉 R의 모든 애트리뷰트 X에 대해 A → X 이고 A가 후보키)
제5정규형(5NF)	릴레이션 R에 존재하는 모든 조인 종속(Join dependency)이 R의 후보키를 통해 성립되면 R은 5NF

 

● 정규화 무손실 분해 과정

 

● NoSQL (Not Only SQL)

NoSQL = 수평적 확장이 가능하며 다수 서버들에 데이터 복제 및 분산 저장이 가능한 데이터베이스

 

NoSQL 특징 :

Schema - less	데이터 모델링을 위한 고정된 데이터 스키마 없이 Key값을 이용해 다양한 형태의 저장과 접근 가능 데이터 저장 방식에는 크게 컬럼(Column), 값(Value), 문서(Document), 그래프(Graph)의 4가지로 나뉨
탄력성 (Elasticity)	시스템의 일부 장애에도 불구하고 다운타임이 없도록 하는 동시에 대용량 데이터의 생성, 업데이트, 질의에 대응할 수 있도록 시스템 규모와 성능 확장이 용이하고 입출력의 부하 분산에도 용이한 구조를 갖춤
효율적 질의기능 (Query)	수십대에서 수천대 규모로 구성된 시스템에서도 데이터의 특성에 맞게 효율적으로 데이터를 검색/처리할 수 있는 질의언어, 관련 처리기술, API를 제공
캐싱 (Caching)	대규모의 질의에도 고성능 응답속도를 제공할 수 있는 메모리 기반의 캐싱 기술의 적용이 매우 중요하고 개발 및 운영에서도 일관되게 적용할 수 있는 구조

 

● 분산 데이터베이스

분산 데이터베이스 = 하나의 데이터베이스 관리 시스템(DBMS)이 여러 CPU에 연결된 저장장치들을 제어하는 형태의 데이터베이스. DB를 연결하는 빠른 네트워크 환경을 이용, DB를 여러 지역 노드로 위치시켜 사용성과 성능 등을 극대화시킴

 

분산 DB 장/단점 :

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

 

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

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

 

● 데이터베이스 분산의 종류

테이블 위치 분산	테이블 위치 분산시 테이블의 구조는 변하지 않음 테이블이 다른 데이터베이스에 중복되어 생성되지 않으며, 설계된 테이블의 위치를 각각 다르게 위치시킴
테이블 분할 (Fragmentation) 분산	단순히 위치만 다른곳에 두는 것이 아니라 각각의 테이블을 쪼개어 분산하는 방법 - 수평분할(Horizontal fragmentation) - 수직분할(Vertical fragmentation)
테이블 복제 (Replication) 분산	동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형 - 부분복제(Segment replication) - 광역복제(Broadcast replication)
테이블 요약 (Summarization) 분산	지역 간에 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우 분산 - 분석요약(Rollup summarization) - 통합요약(Consolidation summarization)

- 수직분할 기법 : 특정 속성들만 접근이 잦을 경우 컬럼을 쪼개서 테이블을 만들어 사용하는 기법

- 수평분할 기법 : 스키마는 동일하지만, 그 데이터 값을 이용하는 방법이 row별로 구분 지어지는 경우에 사용하는 기법

 

● 데이터베이스 암호화

데이터베이스 암호화 = 외부로부터의 공격 또는 내부자의 불법행위로 인한 데이터 유출을 방지하기 위해 DB내에 저장된 데이터를 해독 불가능한 형식의 암호문으로 저장하는 행위

 

유형 :

디스크 전체(Storage level) 암호화	DB 파일을 보호하고 비인가 사용자에 의한 불법적인 파일 열람을 제한하며 인가된 서버만이 정상적인 암호화된 데이터를 해독하고 열람이 가능
컬럼(Column level) 암호화	개인정보나 기업의 민감한 정보를 담고 있는 특정 테이블의 컬럼만을 암호화 DB 서버에 접속하는 사용자들을 구분하고 정의된 암/복호화 정책에 따라 데이터를 제공

컬럼 암호화 방식 :

- Plug-in 방식 = DB 서버내의 플러그인을 장착하여 암/복호화를 수행하는 방식

- API 호출방식 = 애플리케이션 서버가 암/복호화를 위한 API를 호출하여 수행하는 방식

 

● DB 보안 접근 제어 방법

보안 정책에 따라 접근 객체(시스템 자원, 통신 자원 등)에 대한 접근 주체(사용자, 프로세스 등)의 접근 권한 확인 및 이를 기반으로 한 접근 제어를 통해 자원에 대한 비인가된 사용을 방지하는 기능

임의 접근 통제 (DAC)	주체와 객체의 신분 및 임의적 접근 통제 규칙에 기초하여 객체에 대한 주체의 접근을 통제하는 기법
강제 접근 통제 (MAC)	정보시스템 내에서 어떤 주체가 특정 객체에 접근하려 할때 양쪽의 보안 레이블에 기초하여 높은 보안 수준을 요구하는 정보가 낮은 보안 수준의 주체에게 노출되지 않도록 접근을 제한하는 통제 방법
역할기반 접근 통제 (RBAC)	사용자가 주어진 역할에 대한 접근 권한을 부여받는 방식 사용자가 바뀌어도 역할에는 변함 없음 DB 작업 권한 통제 및 운영체제 작업 권한 통제

 

● 데이터베이스 이중화

데이터베이스 이중화 = 물리적으로 떨어져 있는 여러개의 데이터베이스에 대하여 로컬 데이터베이스의 변경된 내용을 원격 데이터베이스에 복제하고 관리하는 것으로써 목적으로는 데이터베이스의 무정지 서비스

 

이중화 유형 :

Active - Active	클러스터를 구성하는 컴포넌트를 동시에 가동하는 구성	시스템 다운 시간이 짧음 전환 성능이 빠름
Active - Standby	클러스터를 구성하는 컴포넌트 중 실제 가동하는 것은 Active, 남은 것은 Standby(대기)로 구성	Cold - Standby : 평소 Standby 서버 다운 Hot - Standby : 평소 Standby 서버 가동

Hot - Standby = Standby 쪽 장비 기동 후 즉시 사용이 가능하고 Fail-over 소요시간은 필요

Warm - Standby = Standby 쪽 장비 기동 후 이용하기 위하여 어느 정도 설정 및 준비가 필요

Cold - Standby = Standby 측을 평소 정지 시켜두며 필요에 따라 직접 켜서 구성

 

● 파티셔닝

파티셔닝 = 큰 테이블이나 인덱스를 관리하기 쉬운 단위로 분리하는 방법

 

파티셔닝의 장점 :

- 가용성 → 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상

- 관리용이성 → 큰 Table들을 제거하여 관리가 쉬움

- 성능 → 특정 DML과 Query의 성능을 향상시킴. 주로 대용량 Data write 환경에서 효율적이며, 많은 Insert가 있는 OLTP 시스템에서 Insert 작업들을 분리된 파티션들로 분산시켜 경합을 줄임

파티셔닝의 단점 :

- 테이블간의 조인에 대한 비용이 증가

- 테이블과 인덱스를 별도로 파티션 작업은 불가하며, 테이블과 인덱스를 함께 파티셔닝이 가능

 

파티셔닝의 범위 :

Range partitioning	연속적인 숫자나 날짜 기준으로 파티셔닝 손쉬운 관리 기법 제공에 따른 관리 시간을 단축 ex) 우편번호, 일별, 월별, 분기별 등의 데이터에 적합
Composite partitioning	파티션의 Sub-partitioning 의미 큰 파티션에 대한 I/O 요청을 여러 파티션으로 분산 Range partitioning 할 수 있는 컬럼이 있지만 파티셔닝 결과 생성된 Partition이 너무 커서 효과적으로 관리할 수 없을때 유용 ex) Range - list, Range - Hash
Hash partitioning	Partition Key의 Hash값에 의한 파티셔닝(균등한 데이터 분할 가능) Select시 조건과 무관하게 병렬 Degree 제공(질의 성능 향상) 특정 Data가 어느 Hash Partition에 있는지 판단 불가 Hash Partition은 파티션을 위한 범위가 없는 데이터에 적합
List partitioning	특정 Partition에 저장될 Data에 대한 명시적 제어 가능 분포도가 비슷하며, 많은 SQL에서 해당 컬럼의 조건이 많이 들어오는 경우 유용 Multi-Column Partition Key 제공하기 어려움 ex) [한국, 일본, 중국 → 아시아] [노르웨이, 스웨덴, 핀란드 → 북유럽]

 

파티셔닝의 방법 :

Horizontal Partitioning	데이터의 개수를 기준으로 나누어 파티셔닝 데이터의 개수가 작아지면서 인덱스의 개수도 작아지며 성능도 향상됨 서버간의 연결 과정이 많아짐 데이터를 찾는 과정이 기존보다 복잡하기 때문에 Latency가 증가 하나의 서버가 고장나게 되면 데이터의 무결성이 깨질 수 있음
Vertical Partitioning	테이블의 컬럼을 기준으로 나누어 파티셔닝 정규화하는 과정도 이와 비슷하다고 볼 수 있지만 Vertical Partitioning은 이미 정규화된 Data를 분리하는 과정 자주 사용하는 컬럼 등을 분리시켜 성능을 향상

 

● 클러스터

클러스터 = 어떤 정해진 칼럼 값을 기준으로 동일한 값을 가진 하나 이상의 테이블의 로우를 같은 장소에 저장하는 물리적인 기법이며 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법

 

클러스터의 장점 :

- 그룹된 컬럼 데이터 행들이 같은 데이터 블록에 저장되기 때문에 디스크 I/O를 줄여줌

- 클러스터된 테이블 사이에 조인이 발생할 경우 그 처리 시간이 단축

- 클러스터 Key 열을 공유하여 한번만 저장하므로 저장 영역의 사용을 줄여줌

단점 :

- 데이터 조회 성능을 향상시키지만 데이터 저장, 수정, 삭제 또는 한 테이블 전체 Scan의 성능을 감소시킴

 

클러스터 특징 :

클러스터 하기 좋은 테이블	주로 조회가 자주 발생하고 수정이 거의 발생하지 않는 테이블 컬럼안의 많은 중복 데이터를 가지는 테이블 자주 조인되는 테이블
클러스터 Key가 되기 좋은 컬럼	데이터 값의 범위가 큰 컬럼 테이블 간의 조인에 사용되는 컬럼
클러스터 Key가 되기 나쁜 컬럼	특정 데이터 값이 적은 컬럼 자주 데이터 수정이 발생하는 컬럼 LONG, LONG RAW 컬럼은 포함할 수 없음

 

단일 테이블 클러스터링 :

- 하나의 테이블에서 특정 컬럼을 클러스터 키로 생성하여 액세스의 효율성을 높임 (동일한 값을 같은 장소에 저장)

- 지정된 클러스터에 하나의 테이블만 생성

- 클러스터 인덱스를 경유하여 여러건의 테이블 로우를 한번의 스캔을 통하여 액세스하므로 랜덤 액세스 건수가 크게 줄어듬

 

다중 테이블 클러스터링 :

- 하나의 단위 클러스터에 여러개의 테이블을 생성

- 기준되는 컬럼의 값이 동일한 각각의 테이블의 로우들이 하나의 단위 클러스터에 저장되므로 기준 컬럼 값으로 관련 테이블을 조인할 경우 추가적인 각 테이블들의 로우들이 흩어져 저장되는 경우에 발생하는 추가적인 액세스가 불필요하여 높은 성능의 조인이 가능

 

● 옵티마이저

옵티마이저 = SQL의 문법적 오류를 확인하고 통계정보를 활용한 cost를 분석하여 가장 빠른 데이터 액세스 경로를 위한 최적의 SQL 실행계획을 수립하는 DBMS 핵심 엔진

 

옵티마이저의 구성 요소 :

질의 변환기 (Query transformer)	실행 계획을 얻을 수 있도록 적절한 형태로 SQL의 모양 변환	데이터타입 변경, 뷰 병합, 조건절 삽입, 사용자 정의 바인드 변수 엿보기
비용 산정기 (Estimate)	CBO 비용계산 공식에 의해 다양한 실행 방법 중 가장 높은 성능의 실행 계획을 찾아주는 알고리즘	선택도(Selectivity), 카디널리티(Cardinality), 비용(Cost)
실행계획 생성기 (Plan generator)	주어진 쿼리를 처리할 수 있는 적용가능한 실행 계획을 선별하고, 비교검토를 거쳐 가장 최소의 비용을 가지는 것을 선택	적용적 탐색, 경험적 탐색, 초기치 선택

 

옵티마이저의 유형 :

항목	RBO	CBO
개념	사전의 정의된 Rule 기반 계획	최소 비용 계산 실행계획 수립
기준	실행 우선순위(Ranking)	액세스 비용(Cost)
성능	사용자 SQL 작성 숙련도	Optimizer 예측성능
특징	실행계획의 예측이 용이함	저장된 통계정보의 활용
고려사항	저효율, 사용자의 규칙 이해도	예측복잡, 비용산출 공식 정확성

RBO(Rule based optimizer) = 통계정보가 없는 상태에서 미리 정해진 Rule에 따라 질의 실행 계획을 수립 가능

CBO(Cost based optimizer) = 통계 정보로부터 모든 접근 경로(Access path)를 고려하여 질의 실행 계획을 수립. 또한 옵티마이저가 선택한 실행계획을 확인하고 최적화된 실행 계획 수립이 이루어지도록 힌트 등을 부여하며, 최적의 실행 계획을 수립하기 위해서는 통계정보를 주기적으로 수행해야함

 

● 힌트(Hint)

힌트 = 옵티마이저에 의해 생성된 SQL의 실행계획을 사용자가 의도하는 방향으로 수동으로 제어하는 도구. 

- 사용자가 수동으로 어떠한 경우에도 실행계획을 고정하기 위해 사용

인덱스	/*+ INDEX_ASC */	인덱스 오름차순으로 스캔
	/*+ INDEX_DESC */	인덱스 내림차순으로 스캔
	/*+ INDEX_FFS */	인덱스 FULL 스캔
	/*+ PALLEL_INDEX */	인덱스를 여러개의 프로세스로 수행
	/*+ FULL */	인덱스를 스캔하지 않고 테이블 FULL 스캔
조인 액세스	/*+ USE_NL */	Nested Loop Join 유도
	/*+ USE_HASH */	Hash Join 유도
	/*+ USE_MERGE */	Sort Merge Join 유도
조인순서	/*+ ORDERED */	FROM 절 명시된 테이블 순으로 조인
	/*+ LEADING */	파라미터에 명시된 테이블 순으로 조인
기타	/*+ PARALLEL */	여러개의 프로세스로 수행
	/*+ PUSH_SUBQ */	서브쿼리를 수행

 

SQL의 성능 개선(튜닝)을 위한 방법 :

옵티마이저 선택/변경	RBO, CBO 옵티마이저가 선택한 실행계획을 확인하고 최적화된 실행계획을 수립이 이루어지도록 Factor 부여
힌트 사용	옵티마이저가 항상 최적화된 실행 계획을 수립하는 것은 아니므로 힌트를 사용하여 원하는 실행계획으로 유도
부분범위 처리	조건을 만족하는 전체집합이 아닌 일부분만 액세스하고도 결과를 리턴할 수 있도록 하여 온라인 프로그램에서 응답시간(Response time)을 최소화
인덱스 활용	인덱스가 있음에도 불구하고 SQL을 잘못 기술함으로써 무용지물로 만드는 오류를 없앰
조인방식/조인순서	동일한 SQL문이라도 조인 방식과 조인 순서에 따라 처리속도는 매우 큰 차이를 가져올 수 있으므로 작성한 SQL이 어떤 실행계획으로 수립되는지 반드시 확인 후 조정
다중처리 (Array processing)	배치작업의 경우 한번의 DBMS 호출로 여러 건을 동시에 처리할 수 있는 다중처리 활용
병렬 쿼리 (Parallel query)	배치작업의 경우 하나의 SQL을 여러개의 CPU가 병렬로 분할 처리하게 함으로써 처리속도 향상을 가져옴
Dynamic SQL 지양	조건절에 입력된 값을 먼저 Binding 한 후 실행계획을 수립하는 Dynamic SQL은 파싱 부하가 커지므로 입력 값을 Binding 하기 전에 실행계획을 수립하는 Static SQL을 가급적 사용

 

● 하둡(Apache Hadoop, High-Availability Distributed Object-Oriented Platform)

Hadoop = 대량의 자료를 처리할 수 있는 큰 컴퓨터 클러스터에서 동작하는 분산 응용 프로그램을 지원하는 프리웨어 자바 소프트웨어 오픈소스 프레임워크

- 가상화 기술을 이용하여 여러 개의 저렴한 컴퓨터를 마치 하나인 것처럼 묶어 대용량 데이터를 처리하는 기술이며 수천대의 분산된 x86 장비에 대용량 파일을 저장할 수 있는 기능을 제공하는 분산파일 시스템과 저장된 파일데이터를 분산된 서버의 CPU와 메모리 자원을 이용해 쉽고 빠르게 분석할 수 있는 컴퓨팅 플랫폼인 맵리듀스로 구성되어 있음

 

● DB 백업

DB 백업 = DB의 고장, 데이터의 손실 등으로 인한 피해를 최소화하기 위하여 현재의 데이터를 저장하는 활동

 

백업 방식 :

- 핫 백업 (Hot backup/Open backup) : DB 서버를 온라인 상태로 유지한채 데이터 백업

- 콜드 백업 (Cold backup/Close backup) : DB 서버를 중지한 후 데이터 백업

- 물리 백업 (Physical backup) : 파일 자체를 그대로 백업

- 논리 백업 (Logical backup) : 각 오브젝트를 SQL문 등으로 저장

 

DB 백업시 대상 파일 :

- Data file : 데이터를 저장한 파일

- Control file : 데이터베이스의 구조나 정보에 대한 상태를 저장한 바이너리 파일

- Redo log file : 데이터 변경 처리사항을 저장한 파일 (복구 시 사용됨)

 

● 데이터 지역화

- 데이터베이스의 저장 데이터를 효율적으로 이용할 수 있도록 저장하는 방법

- 물리적 데이터베이스의 설계에서 데이터 지역화를 고려한 보조 기억 장치의 설계는 중요함으로 물리적 데이터베이스 설계, 보조 기억 장치의 역할, 디스크상의 파일의 배치를 지역화 관점에서 검토를 수행

 

물리적 데이터베이스 설계 :

- 논리적인 설계의 데이터 구조를 보조 기억 장치상의 파일로 사상

- 하나의 파일에 여러 릴레이션이 저장될 수 있음

- 예상 빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석

- 데이터에 대한 효율적인 접근을 제공하기 위하여 저장 구조와 접근 방법들을 다룸

- 질의를 효율적으로 지원하기 위해서 인덱스 구조를 적절히 사용

 

물리적 데이터베이스 수행 사항 :

- 저장 레코드 양식 설계 : 데이터 타입, 데이터 값의 분포, 사용될 응용 어플리케이션, 접근 빈도등을 고려하여 결정하며 데이터 표현과 압축에 대한 양식 포함

- 레코드 집중의 분석 및 설계 : 레코드의 집중은 레코드들이 물리적으로 집중되도록 저장 공간을 할당하여 물리적 순차성을 이용할 수 있도록 함

- 접근 경로 설계 : 물리적 데이터베이스를 접근하는 경로에 대한 설계 수행

 

물리적 데이터베이스 성능 기준 :

- 응답 시간 : 질의와 갱신이 평균적으로 또는 피크 시간 때 얼마나 오래 걸리는가

- 트랜잭션 처리율 : 1초당 얼마나 많은 트랜잭션들이 평균적으로 또는 피크 시간 때 처리되는가

- 스키마의 평가 : 전체 데이터베이스에 대한 보고서를 생성하는데 얼마나 오래 걸리는가

 

● 속성(Attribute)

속성 = 정보를 나타내는 최소의 단위로써 엔티티의 성질, 분류, 수량, 상태, 특성 등을 나타내는 세부 항목을 의미

속성의 유형 :

기본속성	해당 엔티티가 원래 가지고 있는 속성
설계속성	원래 업무에는 존재하지 않지만 시스템의 효율성을 위해 임의로 추가되는 속성 ex) 코드 또는 일련번호
파생속성	다른 속성으로부터 계산이나 변형되어 생성되는 속성 데이터 중복성 및 무결성 확보를 위해 가급적 적게 정의 → 트리거 이용, Computed column(계산된 컬럼) 선언

 

속성 정의시 고려사항

엔티티가 관리할 특성들인가	ex) 주문자 성명이란 속성은 주문 엔티티에 별도로 있어야 하는가? 고객 엔티티와 관계만을 맺으면 되는가를 확인
의미적으로 독립적인 최소 단위인가	속성은 멀티 파트가 되어서는 안됨 → 즉, 속성이 여러개의 부분들로 이루어져 있는 경우와 하나의 의미를 가지는 여러개의 속성이 존재하는 경우 의미
하나의 값만을 가지고 있는가	속성의 1:1 원칙 = 엔티티 타입 내에서 하나의 속성은 한 시점에 한개의 값만을 가져야 함 → 하나의 속성이 시간에 따라 여러개의 값을 가지면 그 값을 해당 업무에서 관리해야 할 필요가 있으면 새로운 엔티티를 생성
원본인가 파생된 값인가	파생된 값은 실제 성능을 위해서 사용되기도 하지만, 이런 값의 중복은 나중에 심각한 무결성 문제를 발생할 수 있으므로 조심해서 사용

 

● 데이터베이스 무결성

무결성 = 권한이 있는 사용자로부터 데이터베이스를 보호하는 것이고, 보안은 권한이 없는 사용자로부터 DB를 보호하는 것

 

DB 무결성 보장 방법 

- 응용프로그램 : 데이터를 조작하는 프로그램 내에 무결성 조건을 검증하는 코드를 추가

- 트리거 : 트리거 이벤트 시 Procedure를 실행하여 무결성 조건 실행

- 데이터베이스 제약 조건 기능을 선언하여 무결성을 유지

 

● 엔티티 통합

- 슈퍼타입 기준 엔티티 통합은 슈퍼타입 엔티티를 중심으로 서브타입 엔티티와 단일 테이블로 통합

단일 테이블 통합으로 유리한 경우	단일 테이블 통합으로 불리한 경우
데이터의 액세스가 상대적으로 용이, 수행 속도가 좋아지는 경우가 많음 뷰를 이용하여 각각의 서브 타입만을 액세스하거나 수정 서브 타입 구분이 없는 임의 집합에 대한 가공이 용이	특정 서브 타입에 대한 Not Null 제한이 어려움 테이블의 컬럼 및 블록 수가 증가 처리마다 서브 타입에 대한 구분이 필요한 경우가 많이 발생

 

- 서브타입 기준 엔티티 통합은 슈퍼타입 속성들을 각각의 서브 타입에 추가하여 서브 타입마다 하나의 테이블로 변환

복수의 테이블로 분할이 유리한 경우	복수의 테이블로 분할이 불리한 경우
각 서브 타입 속성들의 선택 사양이 명확한 경우에 유리 서브타입 유형에 대한 구분을 처리 마다할 필요가 없음 전체 테이블을 스캔하는 경우 유리	서브타입 구분없이 데이터를 처리하는 경우에 UNION이 발생 처리 속도 감소가 발생할 가능성이 높음 트랜잭션을 처리하는 경우 다수 테이블을 처리하는 경우가 자주 발생

 

● 반정규화 (역정규화)

반정규화 = 정규화된 엔티티, 속성, 관계를 시스템의 성능 향상, 개발과 운영을 단순화하기 위해 데이터 모델을 통합하는 프로세스. 데이터의 정합성과 데이터의 무결성을 우선으로 할지 데이터베이스 구성의 단순화와 성능을 우선으로 할지를 결정

 

반정규화 수행절차

반정규화(역정규화) 대상 조사	범위 처리 빈도수 조사 : 자주 사용되는 테이블에 접근하는 프로세스 수가 많고, 항상 일정한 범위만 조회하는 경우 대량의 범위 처리 조사 : 대량의 데이터 범위를 자주 처리하는 경우 통계성 프로세스 조사 : 별도의 통계 테이블 고려 테이블 조인 개수 : 지나치게 많은 조인이 걸려 데이터 조회 작업이 어려운 경우
다른 방법 유도 검토	뷰 테이블 사용 : 지나치게 많은 조인이 걸려 데이터 조회 작업이 어려운 경우 클러스터링 또는 인덱스 적용 : 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리 (파티셔닝 기법) 어플리케이션 수정 : 로직을 변경함으로써 성능을 향상
반정규화(역정규화) 적용	테이블 반정규화(역정규화) 속성의 반정규화(역정규화) 관계의 반정규화(역정규화)

 

반정규화의 방법 :

테이블 반정규화	테이블 병합	1 : 1 관계테이블 병합 1 : M 관계테이블 병합 슈퍼/서브 타입 관계테이블 병합
	테이블 분할	수직분할 수평분할
	테이블 추가	중복테이블 추가 통계테이블 추가 이력테이블 추가 부분테이블 추가
컬럼 반정규화	중복컬럼 추가	자주 사용하는 컬럼을 추가 조회 성능 향상, 수정(갱신) 비용 증가
	파생컬럼 추가	사전 값을 계산하여 컬럼에 보관하는 컬럼 추가 계산값(SUM, AVG) 저장
	이력컬럼 추가	이력테이블에 기능성 컬럼을 추가 최근 값 여부, 시작과 종료일자 등
관계 반정규화	중복 관계	조인경로가 많은 경우 관계 추가를 통해 성능을 향상

 

● 물리 데이터 모델

 

품질 기준 

- 정확성 : 데이터 모델이 표기법에 따라 정확하게 표현되었고, 업무 영역 또는 요구사항이 정확하게 반영되었음을 의미

- 완전성 : 데이터 모델의 구성요소를 정의하는데 있어서 누락을 최소화하고, 요구사항 및 업무 영역 반영에 있어서 누락이 없음을 의미

- 준거성 : 제반 준수 요건들이 누락없이 정확하게 준수되었음을 의미

- 최신성 : 데이터 모델이 현행 시스템의 최신 상태를 반영하고 있고, 이슈사항들이 지체없이 반영되고 있음을 의미

- 일관성 : 여러 영역에서 공통 사용되는 데이터 요소가 전사 수준에서 한번만 정의되고 이를 여러 다른 영역에서 참조/활용되면서, 모델 표현상의 일관성을 유지하고 있음을 의미

- 활용성 : 작성된 모델과 그 설명 내용이 이해관계자에게 의미를 충분하게 전달할 수 있으면서, 업무 변화시에 설계 변경이 최소화되도록 유연하게 설계되어 있음을 의미

 

● CRUD 매트릭스

- 프로세스와 엔티티의 상관관계를 이용하여 구축된 데이터베이스 시스템을 검증하는 방법

- 수행할 업무와 프로세스 수행에 사용된 개체간의 상관관계를 분석하기위해 2차원 구조의 행렬 구조로 표현

- 응용 시스템과 DB 간의 업무 분석을 하기 위한 상관 분석표를 말함

- CRUD 매트릭스의 분석 절차는 모든 엔티티 목록을 나열하고 각각의 프로세스가 해당 엔티티에 대하여 생성(C), 조회(R), 변경(U), 삭제(D) 하는가에 대한 여부를 표기

 

CRUD 매트릭스의 데이터 처리 기능 :

이름	조작	SQL	HTTP
CREATE	생성	INSERT	PUT/POST
READ	읽기/인출	SELECT	GET
UPDATE	갱신	UPDATE	PUT/PATCH
DELETE	삭제/제거	DELETE	DELETE

 

● SQL 성능 튜닝

SQL 성능 튜닝 = 튜닝 대상이 되는 SQL을 이해하고, SQL이 가진 정보(테이블/인덱스/컬럼의 정보 및 업무적 성격 등)를 분석하여 성능을 개선하는 활동. 최소한의 CPU, I/O, 메모리를 사용하여 최대한 빠른 시간내에 원하는 작업을 수행하도록 SQL문을 수정

- 조건절 컬럼에 함수는 사용하지 않는 것이 좋으며, 부정형 의미로 조건절에 NOT 사용은 자제하고 내부 조인을 지향하고 외부 조인은 되도록 피하는게 좋음

- IN이나 EXISTS의 다음에 오는 SELECT문의 결과내용이 많을수록 EXISTS문 사용하는 것이 좋음

 

SQL 성능 튜닝 절차 :

튜닝 대상 SQL 수집	SQL 실행에 필요 이상의 자원을 독점하여 사용하는 경우 데이터의 증가 및 사용자가 증가할 경우 서버의 자원을 대량으로 점유할 가능성이 있는 SQL
SQL 문제점 분석 및 개선사항 도출	1) 데이터가 운영 환경과 유사한 경우 - 수집된 SQL의 수행정보가 분석을 위한 이력정보를 이용 - TRACE 파일 분석, DBMS_XPLAN 패키지 활용한 분석 2) 데이터가 운영 환경에 비해 적거나 다른 경우 - 해당 SQL의 실행계획 중심으로 튜닝 대상 판단하고 분석
개선사항 적용 및 개선 효과 확인	1) 성능 튜닝을 수행하는 담당자(튜너)들은 개발자들에 비해 제한된 정보만으로 SQL을 검토할 가능성이 높기 때문에, 개발자 차원에서의 검토가 충분히 이루어져야 함 2) 개발자와 튜너 사이의 상호검증을 통해 SQL의 성능 최적화 필요 3) 개선 사항 적용 후에는 튜닝을 통해 목표하는 결과에 도달했는지 확인하고, 그렇지못한 경우 추가적인 분석 및 튜닝 필요

 

● 데이터 전환

데이터 전환 = 데이터 이행(Migration)으로도 불리며 새로운 정보 시스템을 개발하거나 기존의 시스템을 재구축한 경우 기존의 시스템에서 사용하던 데이터를 새로운 시스템으로 이행하기 위해 이전 시스템에서 데이터를 추출, 변환하여 목표 시스템으로 적재하는 일련의 작업을 의미함

- 데이터 전환 절차는 일반적으로 원천 데이터의 추출과 정제, 변환, 목표 시스템으로의 적재 및 검증 단계로 구성됨

- 데이터 추출 및 가공, 적재 과정에서 일반적으로 ETL(Extract, Transformation, Load) 도구들 사용

- 데이터 정제는 원천시스템 운영 과정에서 잘못된 데이터를 수정

- 목표 시스템을 위한 새로운 데이터 표준에 맞지 않는 데이터를 변환

- 데이터 변환 과정에서 잘못 변경된 데이터들을 식별하여 검증 과정

 

 

● 초기 데이터 구축 절차

초기 데이터 구축 = 단시일에 대량의 데이터를 전환함에 있어 발생할 수 있는 위험을 최소화하고 신속하고 안정적인 이행 작업을 수행하기 위해 원천시스템에서 특정 데이터들을 사전에 추출 및 변환해서 적재할 수 있도록 데이터베이스 혹은 데이터 파일로 구축해두는 과정

- 기존 운영 시스템에 대한 이해를 바탕으로 구축 범위를 명확하게 식별해야 하며, 초기 데이터 구축시 발생할 수 있는 예상문제점을 도출하여 사전에 대응 방안을 마련

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

 

초기 데이터 구축을 통한 기대효과 :

- 데이터 이관시 업무 중단을 최소화하고 안정성을 확보할 수 있는 방안 마련

- 데이터 이관 정합성 검증 방안과 오류 데이터에 대한 정비 방안 마련

- 데이터 이관 완료후 데이터의 품질 검증 방안 마련

- 암호화된 개인정보에 대한 처리 방안 마련

 

● ETL(Extract, Transformation, Load)

ETL = 다양한 소스 시스템으로부터 필요한 데이터를 추출(Extract)하여 변환(Transformation) 작업을 거쳐 타겟 시스템으로 전송 및 로딩(Loading)하는 모든 과정

- 조직 내외부의 복수의 Source들로부터 분석을 위한 저장소(data warehouse, data mart)로 데이터를 이동시키는 프로세스

- ETL의 변환 작업은 특수한 엔진에서 진행되며, 종종 변환 중인 데이터가 준비 테이블에서 임시로 보유되었다가 대상에 로드됨. 일반적으로 발생하는 데이터 변환에는 필터링, 정렬, 집계, 데이터 조인, 데이터 정리, 중복 제거 및 데이터 유효성 검사 등의 작업이 포함됨

 

ETL 프로세스별 방법

- 추출(E) : JDBC, ODBC 기술의 이용, Flat file 생성, CDC(Change data capture) 등

- 변형(T) : 재구성, 정제(중복 제거, 일관성 확보), 변환(target repository에 적합한 형태), 데이터필드 검사, Rule 적용(전체 작업의 80% 소요)

- 적재(L) : DBMS 고유기능/Utility(append, delete/insert, update) 이용

 

● 색인 순차 파일

- 순차 처리와 랜덤 처리가 모두 가능하도록 레코드들을 키 값순으로정렬시켜 기록하고, 레코드의 키 항목만을 모은 색인을 구성하여 편성하는 방식

- 색인을 이용한 순차적인 접근 방법을 제공하여 ISAM(Index Sequential Access Method)이라고 하며 레코드를 참조할때 색인을 탐색한 후 색인이 가리키는 포인터를 사용하여 직접 참조. 일반적으로 자기디스크에 많이 사용되며, 자기테이프에서는 사용할 수 없음

 

색인 순차 파일 장점 :

- 순차 처리와 랜덤 처리가 모두 가능하므로, 목적에 따라 융통성 있게 처리

- 효율적인 검색이 가능하고 레코드의 삽입, 삭제, 갱신이 용이

단점 :

- 색인 구역과 오버플로우 구역을 구성하기 위한 추가 기억공간이 필요

- 파일이 정렬되어 있어야 하므로 추가, 삭제가 많으면 효율이 떨어짐

- 색인을 이용한 액세스를 하기 때문에 액세스 시간이 랜덤 편성 파일보다 느림

 

VSAM(Virtual Storage Access Method) :

- 동적 인덱스 방법을 이용한 색인 순차 파일

- VSAM 파일은 데이터 레코드가 저장되는 부분인 제어구간, 몇개의 제어구간을 모아 놓은 제어구역, 제어구역에 대한 인덱스를 저장한 순차세트, 순차세트의 상위 인덱스 세트로 구성되며 레코드를 삭제하면 그 공간을 재사용함

정적 인덱스	데이터 파일 또는 레코드가 삽입되어도 인덱스의 구조가 변하지 않는 인덱스 방법
동적 인덱스	인덱스 파일 또는 데이터 파일을 블록으로 구성하고 블록은 추가적인 삽입 레코드를 감안하여 빈 공간을 준비해 둔 인덱스 방법

 

● 데이터 전환 수행 절차

전환 계획서 작성 → 체크 리스트 작성 → 데이터 변환 프로그램 개발 → 수행 결과검증 → 결과 보고서 작성

 

● 체크리스트

- 전환 프로그램의 에러, 시간의 제약, 업무 프로세스의 변경, 빈번한 데이터 요건 변경, 하드웨어 장애 등의 위험 요소를 사전에 점검하기 위해 전환 과정에서 점검

- 체크리스트에는 데이터 전환 수행자가 수행할 작업의 상세 항목, 작업 내용, 예정 시작/종료 시각, 작업 담당자를 기재

 

● 데이터 전환시 검증 방법

추출 검증	현행 시스템에서 최초 원시 데이터에 대한 검증을 수행 제공되는 원천 시스템 데이터에 대한 정합성 확인
전송 검증	전송된 추출 파일의 코드 전환 후 검증 데이터 전송시 데이터 유실을 확인
전환 검증	Tool을 이용해서 데이터를 추출하여 Staging DB를 구성한 후 검증 원천 시스템에서 작성된 검증 보고서와 Staging DB 적재 건수, 금액을 검증
적재 검증	목적 테이블(To - Be)에 대해 데이터를 검증 목적 테이블의 데이터 검증을 통해 오류 및 데이터 누락/손실 등 확인
통합 검증	매핑 후 검증(타겟 SAM 파일 또는 타겟 테이블)을 의미 오류 데이터를 분석하여 사전 정비 또는 전환 규칙 오류를 수정 반영

 

● 데이터 정제 항목

- 정제 시점에 따라 전환 테스트 전, 전환 테스트 중, 최종 전환 3단계로 구분

전환 테스트 전	정의된 기준에 의하여 전환 제외 대상 데이터의 삭제 데이터 상호 간의 정합성이 유지될 수 있도록 데이터 보완 사용하는 데이터의 경우 매핑 수행 사용하지 않을 경우에는 해당 테이블 삭제 손실된 데이터를 정당한 값으로 복원 정당한 타입의 데이터로 수정 오류 데이터를 정당한 데이터로 변경 불필요한 데이터를 삭제 또는 초기화
전환 테스트 중	사전에 업무 규칙을 적용하여 정의한 정당한 데이터 값으로 정비 동일한 타입으로 전환 1단계 정제 대상 항목의 정제 내용과 동일하게 정제
전환 테스트 후	매핑 정의서에 작성된 전환 규칙을 이용하여 전환 프로그램에 해당 내용을 추가하여 프로그램 수행시 정제

 

● 오류 데이터 측정

오류데이터 측정 = 정량적으로 측정하고 오류 원인 파악 및 정제 여부 결정

- 정량적 측정은 데이터 품질 기준에 따라 정상 데이터와 오류 데이터를 분리하고 그 수량을 정확히 측정하여 오류 관리 목록에 기재

- 정상 데이터의 건수를 정량적으로 측정하는 방법 → 전환 대상 범위의 데이터를 업무 영역별, 테이블별로 세분화하여 정상 데이터의 수량을 정확히 측정하고 기록

- 오류 데이터의 유형과 건수를 정량적으로 측정하는 방법 → 원천 데이터의 정합성 기준을 근거로 업무별로 오류 위치와 유형을 파악하고 그 수량을 정확히 측정하여 기록

- 오류 원인 파악 및 정제 여부 결정은 오류 목록의 내용을 확인하고 오류 해결 방안을 참조하여 원천 데이터의 정제를 요청할 것인지, 아니면 전환 프로그램을 수정할 것인지 데이터 정제 여부를 결정

- 발견된 데이터 오류를 분석하고 원인을 파악 → 오류 관리 목록에 기록된 오류 내용을 확인하고 오류의 상태와 심각도를 결정한 후 오류 원인을 파악

- 파악된 원인을 기반으로 원천 데이터와 전환 프로그램의 정제 필요 여부를 결정

 

● SQL 문

- 테이블에 저장된 데이터 중 특정 컬럼을 조회하고자 할떄는 해당 컬럼 이름을 SELECT절 뒤에 기술

- 테이블에 저장된 모든 컬럼의 데이터를 검색하고자 할때는 컬럼명 없이 *(Asterisk) 기호 사용

- SELECT 명령을 통해 검색한 결과는 중복이 포함된 상태로 출력됨. 중복 제거를 원하면 DISTINCT 키워드를 SELECT뒤, 컬럼명 앞에 기술

 

- GROUP BY 절은 사용된 애트리뷰트에 동일한 값을 갖는 튜플들이 각각 하나의 그룹으로 묶여서 각 그룹별로 하나의 결과를 생성함

 

● 권한 허가 GRANT 명령문

- GRANT 권한 [컬럼 리스트] ON 객체 TO {사용자 | 역할 | PUBLIC } [ WITH GRANT OPTION ] ;

 

CUST 객체에 대해 USER01에게 갱신 권한을 부여받고 USER02에게도 갱신 권한을 부여할 수 있는 권한 허가를 작성

→ GRANT UPDATE ON CUST TO USER01 WITH GRANT OPTION

 

- WITH GRANT OPTION을 사용하여 부여받은 객체의 권한은 REVOKE 명령문을 사용하여 권한 회수시 다른 사용자에게 허가했던 권한들도 연쇄적으로 취소되며 기본적으로 권한을 허가했던 사람만 그 권한을 취소할 수 있음

 

● 인덱스 생성 구문

CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column(s)>) ;

- 인덱스 설계서의 테이블명, 인덱스명, 컬럼명을 이용하여 인덱스 구문에 맞게 생성이 가능하며, 인덱스 생성시 데이터 타입은 고려되지 않음