[정보처리기사] 3장 데이터 베이스 구축 2, 3, 4, 5

2장 물리 데이터베이스 설계

85. 사전 조사 분석

물리 데이터베이스 설계 : 논리적 구조로 표현된 논리적 데이터베이스를 물리적 저장장치에 젖앙할 수 있는 물리적 구조의 데이터로 변환하는 과정. 저장 레코드가 기본 데이터 단위이다.

 

86. 데이터베이스 저장 공간 설계

테이블 : 논리 설계 단계의 개체(Entity)에 대응하는 객체이다.

일반 테이블

클러스터드 인덱스 테이블 : 기본키나 인덱스키의 순서에 따라 데이터가 저장되는 테이블

파티셔닝 테이블 : 대용량의 테이블을 파티션으로 나눈 테이블.

외부 테이블 : DB에서 일반 테이블로 사용할 수 있는 외부 파일

임시 테이블 : 트랜잭션이나 세션별로 데이터를 저장하고 처리할 수 있는 테이블.

테이블스페이스 : 테이블이 저장되는 논리적인 영역. 테이블을 저장하면 논리적으로는 테이블스페이스에 저장되고 물리적으로는 데이터 파일에 저장된다.

 

87. 트랜잭션 분석 / CRUD 분석

트랜잭션의 특성 : ACID

             Atomicity(원자성) : 트랜잭션 연산은 DB에 모두 반영되도록 완료(Commit) 되던가 아니면 전혀 안되도록 복구                                            (RollBack)되어야 한다. 완벽히 수행 되거나 전부 취소되어야 하는 특성.

             Consistency(일관성) : 트랜잭션이 성공적으로 완료하면 언제나 일관성 있는 상태로 변환한다.

             Isolation(독립성) : 둘 이상의 트랜잭션이 동시에 수행시, 중간에 다른 트랜잭션이 끼어들 수 없다.

             Durability(지속성) : 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 한다.

CRUD 분석 : 테이블에 변화를 주는 트랜잭션의 Create, Read, Update, Delete연산에 대해 CRUD 매트릭스를 작성해 분석

CRUD 매트릭스 : 2차원 테이블로 행에는 프로세스, 열에는 테이블을, 행과 열이 만나는 곳은 트랜잭션을 적는다.

                           C > D > U > R의 우선순위를 적용한다.

트랜잭션 분석 : CRUD 매트릭스를 기반으로 트랜잭션 양을 분석, DB 용량을 산정하고 구조를 최적화한다.

 

88. 인덱스 설계

인덱스 : 데이터 레코드에 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조

             인덱스는 물리적 구조와 관계있다. 삽입과 삭제가 수시로 일어나는 경우 인덱스의 개수를 최소로 해야한다.

             기본키를 위한 인덱스 : 기본 인덱스

             나머지 인덱스 : 보조 인덱스

             레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하면 클러스터드 인덱스라고 한다.

인덱스 대상 컬럼 선정 기준

             인덱스 컬럼의 분포도가 10~15% 이내.

             가능한 수정이 빈번하지 않은 컬럼

 

89. 뷰의 설계

뷰 : 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위한 가상 테이블

             물리적으로 존재하지는 않지만 있는것처럼 간주됨. 임시적인 작업을 위한 용도.

             기본 테이블과 유사. 논리적 독립성을 제공한다. 정의된 뷰는 다른 뷰의 기초가 될 수 있다.

             뷰를 제거하면 그 뷰를 기초로 정의된 다른 뷰도 제거된다.

             뷰에 삽입, 삭제, 갱신 연산은 제약이 따른다. 독립적인 인덱스를 가질 수 없다. 뷰의 정의를 변경할 수 없다.

 

90. 클러스터 설계

클러스터 : 데이터 저장 시 액세스 효율을 높이기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방법

클러스터링키 : 클러스터 된 테이블에서 각각의 행에 접근할 떄 기준이 되는 속성.

클러스터의 특징

             데이터 조회 속도는 향상시키지만, 데이터 입력, 수정, 삭제는 저하된다. 저장공간 절약이 가능하다.

             데이터 분포도가 넓을수록, 조인이 자주 사용될수록, 대량의 범위를 자주 조회할수록 유리하다.

             대용량을 처리하는 트랜잭션은 클러스터링을 하지 않는 것이 좋다.

             처리 범위가 넓은 경우엔 단일 테이블 클러스터링, 조인이 많이 발생하는 경우 다중 테이블 클러스터링을 사용.

91. 파티션 설계

파티션 : 대용량의 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것

             데이터 처리는 테이블 단위로, 데이터 저장은 파티션별로 수행된다.

             용량이 작은 테이블은 파티셔닝을 할 시, 성능이 저하된다.

파티셔닝 종류

             범위 분할 : 열의 값을 기준에 따라 분할

             해시 분할 : 해시 함수를 적용한 결과 값으로 분할. 데이터를 고르게 분산할 때 유용하다.

             조합 분할 : 범위 분할을 한 후 해시 함수를 적용한다.

인덱스 파티션 : 파티션된 테이블의 인덱스를 나눈 것.

             Local Partitioned Index : 테이블 파티션과 인덱스 파티션이 1:1 대응되도록 파티셔닝

             Global Partitioned Index : 서로 독립적으로 구성되도록 파티셔닝.

             local이 global에 비해 데이터 관리가 쉽다.

 

92. 데이터베이스 용량 설계

디스크의 특성을 고려하여 설계함으로써 입,출력 부하를 분산시키고 채널의 병목 현상을 최소화한다.

테이블의 테이블스페이스와 인덱스의 테이블스페이스를 분리한다.

 

93. 분산 데이터베이스 설계

분산 데이터베이스 : 논리적으로는 하나의 시스템에 속하지만 물리적으로는 네트워크를 통해 연결된 여러 개의 사이트에 분산되어있는 데이터베이스

분산 데이터베이스 목표

             위치 투명성(Location Transparency) : DB의 실제 위치를 알 필요 없이 논리적인 명칭만으로 접근할 수 있다.                  중복 투명성(Replication Transparency) : 동일 데이터가 여러 곳에 중복되어 있어도 마치 하나의 데이터만 존재하는                                                                      것처럼 사용한다.

             병행 투명성(Concurrency Transparency) : 분산 DB와 관련된 다수의 트랜잭션이 동시에 실현되더라도 결과는 영향을                                                             받지 않는다.

             장애 투명성(Failure Transparency) : 여러 컴퓨터 장애에도 트랜잭션을 정확하게 처리한다.

분산 데이터베이스 단점

             기능이 복잡하다. 설계가 어렵다. 개발 비용이 증가한다. 처리 비용이 증가한다. 잠재적 오류가 증가한다.

분산 데이터베이스 설계

             1) 테이블 위치 분산 : 테이블을 각기 다른 서버에 분산시켜 배치한다.

             2) 분할 : 데이터를 분할해서 분산.

             3) 할당 : 동일한 분할을 여러 개의 서버에 생성한다.

 

94. 데이터베이스 이중화 / 서버 클러스터링

데이터베이스 이중화 : 시스템 오류에서 복구하기 위해 동일한 데이터베이스를 복제하여 관리하는 것

             Eager 기법 : 트랜잭션 수행 중 데이터 변경이 발생하면 이중화된 모든 DB에 즉시 적용하는 기법

             Lazy 기법 : 트랜잭션 수행이 종료되면 변경 사실을 적용하는 방법.

데이터베이스 이중화 구성 방법

             활동-대기(Active-Standby) 기법 : 한 DB가 활성상태로 서비스하고 있으면 다른 DB는 대기하고 있다가, 장애가 발생                                                 하면 대신 수행하는 방법. 구성 방법과 관리가 쉽다.

             활동-활동(Active-Active) 기법 : 두 개의 DB가 서로 서비스를 제공하다가 장애 발생시 대신 처리하는 방법. 처리율이                                             높지만 구성 방법 및 설정이 복잡하다.

클러스터링 : 두개 이상의 서버를 하나의 서버처럼 운영하는 기술

             고가용성 클러스터링 : 하나의 서버에 장애가 발생하면 다른 서버가 처리하여 중단을 방지한다.

             병렬 처리 클러스터링 : 하나의 작업을 여러 개의 서버에서 분산 처리하는 방식.

 

95. 데이터베이스 보안 / 암호화

             권한이 없는 사용자가 액세스하는 것을 금지하는 기술.

개인키 암호 방식 : 동일한 키로 데이터를 암호화, 복호화 하는 방법.

공개키 암호 방식 : 서로 다른 키로 데이터를 암호화하고 복호화. 비대칭 암호 방식. RSA가 대표적이다.

 

96. 데이터베이스 보안 - 접근 통제

접근통제 : 데이터가 저장된 객체와 이를 사용하려는 주체 사이의 정보 흐름을 제한하는 것

임의 접근 통제(DAC) : 접근하는 사용자의 신원에 따라 권한을 부여한다. 주체가 접근 권한을 지정하고 제어한다. SQL 명령어                                로는 GRANT와 REVOKE가 있다.

강제 접근 통제(MAC) : 주체와 객체의 등급을 비교하여 접근 권한을 부여하는 방식. 제3자가 권한을 지정한다.

접근통제의 3요소 : 정책, 메커니즘, 보안모델

접근통제 보안 모델

             기밀성 모델 : 군사적인 목적으로 개발된 수학적 모델. 인가된 사용자에게만 접근이 허용된다.

             무결성 모델 : 데이터의 일관성 유지에 중점을 두어 개발. 인가된 사용자에게만 수정이 허용된다.

 

97. 데이터베이스 백업

로그 파일 : DB의 상태 병화를 시간의 흐름에 따라 모두 기록한 파일. UNDO나 REDO로 일관성을 유지한다.

98. 스토리지

스토리지 : 대용량의 데이터를 저장하기 위해 서버와 저장장치를 연결하는 기술이다.

DAS(Direct Attached Storage) : 서버와 저장장치를 전용 케이블로 직접 연결하는 방식. 속도는 빠르나 다른 서버에서 스토리지     에 접근할 수 없다.

NAS(Network Attached Storage) : 서버와 저장장치를 네트워크를 통해 연결한다. 확장성 및 유연성이 우수하나, 접속 증가시       성능이 저하될 수 있다.

SAN(Storage Area Network) : 둘을 혼합한 방식으로 서버와 저장 장치를 연결하는 전용 네트워크를 별도로 구성하는 방식

             파이버 채널(FC) 스위치를 이용해 내트워크 구성. 확장성, 유연성, 가용성이 뛰어난다. 장비 업그레이드가 필요하고      네트워크를 구축해야 하므로 비용이 많이 든다.

 

99. 논리 데이터 모델의 물리 데이터 모델 변환

논리적 설계	물리적 설계
엔티티	테이블
속성	칼럼
주 식별자	기본키
외부 식별자	외래키
관계	관계

 

슈퍼타입/서브타입을 테이블로 변환

슈퍼타입 기준 테이블 변환 : 서브타입을 슈퍼타입에 통합하여 하나의 테이블로 생성.

서브타입 기준 테이블 변환 : 슈퍼타입 속성들을 서브타입에 각각 추가하여 여러 개의 서브타입 테이블을 만드는 것.

개별타입 기준 테이블 변환 : 슈퍼타입과 서브타입들을 각각 개별적인 테이블로 생성한다.

 

100. 물리 데이터 모델 품질 검토

물리 데이터 모델 품질 기준 : 정확성, 완전성, 준거성(표준을 지킴), 최신성, 일관성, 활용성

 

3장 SQL 응용

101. SQL의 개념

DDL : Data Define Language, 데이터 정의어. CREATE, ALTER, DROP이 있다.

DML : Data Manipulation Language, 데이터 조작어. SELECT, INSERT, DELETE, UPDATE가 있다.

DCL : Data Control Language, 데이터 제어어. COMMIT, ROLLBACK, GRANT, REVOKE가 있다.

102. DDL

DROP - CASCADE : 참조하는 다른 객체도 모두 제거.

DROP - RESTRICTED : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소한다.

 

103. DCL

GRANT 권한리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];

             ex) GRANT SELECT ON STUDENT TO U2 WITH GRANT OPTION : U2에게 Student 테이블에 대한 검색 권한과 다른 사     람에게 student를 검색할 수 있는 권한을 줄 수 있는 권한을 준다.

REVOKE [GRANT OPTION FOR] 권한리스트 ON 개체 FROM 사용자 [CASCADE];

 

104. DML

INSERT INTO 테이블명

VALUES 데이터

 

DELETE

FROM 테이블명 [WHERE 조건]

 

UPDATE 테이블명

SET 속성명 = 데이터 [WHERE 조건];

 

105. SELECT - 1

SELECT *

FROM 사원

WHERE 이름 LIKE "김%";  -> 김으로 시작하는 이름 찾기

WHERE 이름 LIKE "%신%";  -> 이름에 신이 들어가는 사람 찾기.

WHERE 이름 LIKE "김_";  -> 김으로 시작하는 두 글자의 사람 찾기.

 

106. DML - SELECT - 2

그룹함수

             ROLLUP(속셩명, 속성명, …) : 속성을 대상으로 그룹별로 합계를 구한다. 속성의 개수가 N개면 N+1 레벨까지 하위       

             레벨에서 상위 레벨 순으로 데이터 집계

             CUBE(속성명, 속성명, …) : 속성의 모든 조합으로 합계를 구한다. N^2레벨 까지, 상위 레벨에서 하위 레벨 순으로 데     이터 집계

WINDOW 함수

             ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련 번호를 반환

             RANK() ; 윈도우별로 순위를 반환, 공동 순위 반영.

             DENSE_RANK() : 윈도우별로 순위를 반환, 공동 순위 무시

 

107. DML - JOIN

INNER JOIN : EQUI JOIN과 NON-EQUI JOIN으로 나뉜다.

             EQUI 조인은 공통 속성에 대해 같은 값을 갖는 행만 연결해서 결과를 생성한다.

             NON-EQUI 조인은 '='가 조건이 아닌 비교 연산자를 이용해서 JOIN 하는 방법이다.

OUTER JOIN : LEFT, RIGHT, FULL OUTER JOIN이 있다. JOIN 조건에 만족하지 않는 튜플도 결과로 출력하는 방법이다.

             LEFT OUTER JOIN : INNER JOIN 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 튜플에 NULL을 붙임.

             RIGHT OUTER JOIN : 반대

             FULL OUTER JOIN : 양쪽 모두에 NULL을 붙여 INNER JOIN 실행

SELF JOIN : 같은 테이블에서 2개의 속성을 연결하는 JOIN 방법.

 

4장 SQL 활용

108. 프로시저

프로시저 : 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어. 호출을 통해 실행된다.

프로시저 생성

             CREATE [OR REPLACE] PROCEDURE 프로시저명

             BEGIN

                           프로시저 BODY

             END;

             OR REPLACE : 동일한 이름이 이미 존재할 경우 기존의 프로시져를 대체할 수 있다.

프로시저 실행

             EXECUTE , EXEC, CALL 프로시저명;

프로시저 제거

             DROP PROCEDURE 프로시저명;

 

109. 트리거

트리거 : 데이터 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 자동으로 수행되는 절차형 SQL. 트리거의 구문에는 DCL을 사용할     수 없다.

 

110. 사용자 정의 함수

사용자 정의 함수 : 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 수행. 처리결과를 단일값으로 반환하는 절차형 SQL

SELECT를 통한 조회만 할 수 있다. 프로시져의 구성에서 RETURN을 추가해야 한다.

DROP FUNCTION 함수명; 을 통해 제거한다.

 

111. DBMS 접속 기술

DMBS 접속 : 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접속하는 것. 웹 응용 시스템을 통해 접근    할 수 있다.

웹 응용 시스템 : 웹 서버 + WAS

 

DBMS 접속 기술

JDBC : JAVA 언어로 DB에 접속하고 SQL을 수행하는 API

ODBC : 개발 언어에 관계없이 사용할 수 있는 API

MyBatis : JDBC를 답순화 하여 사용할 수 있는 프레임워크

 

동적 SQL : 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것. 속도가 느리지만 유연한 개발이 가능하다.

 

112. SQL 테스트

SQL 테스트 : SQL이 작성 의도에 맞게 기능을 수행하는지 검증하는 과정.

단문 SQL 테스트 : SQL과 TCL을 테스트 하는 것

             DDL로 작성된 테이블 옵션 : DECRIBE [개체명] 또는 DESC [개체명];을 통해 검사한다.

             DML : SELECT문을 통해 확인

             DCL : SHOW 명령어로 확인.

절차형 SQL 테스트 : 프로시져, 트리거, 사용자 정의 함수 등의 디버깅을 하는 법.

             SHOW ERRORS를 통해 오류 확인.

             DB에 영향을 줄 수 있는 SQL문은 주석으로 처리, 출력문을 이용해 출력 확인.

 

 

113. ORM

ORM(Object-Relational Mapping) : 객체지향 프로그래밍의 객체와 관계형 데이터베이스를 연결하는 기술. 유지보수, 재사용이 용이하다.

 

114. 쿼리 성능 최적화

 

5장 데이터 전환

115. 데이터 전환

데이터 전환이란 운영 중인 기존 정보 시스템에 있는 데이터를 추출(Extraction)하여 새로 개발할 정보 시스템에서 운영 가능하도록 변환(Transformation) 한 후 적재(Loading)하는 과정이다. ETL이라고 한다.

데이터 전환 계획서를 참고한다.

 

116. 데이터 전환 계획서 작성

데이터 전환 목표를 간단하고 명료하게 정의한다.

 

117. 데이터 전환 방안

데이터 전환 규칙 항목에는 전환 과정에서 공통적으로 적용해야 할 규칙들을 기술한다.

데이터 전환 절차 수립 시 작업의 이해를 위해 데이터 흐름도를 작성한다.

 

118. 데이터 검증

데이터 검증 : 원천 시스템의 데이터를 목적 시스템의 데이터로 전환하는 과정이 정상적으로 수행되었는지 확인하는 과정

검증 방법

             로그 검증, 기본 항목 검증, 응용 프로그램 검증, 응용 데이터 검증, 값 검증

 

119. 오류 데이터 측정 및 정제

데이터 품질 분석 -> 오류 데이터 측정 -> 오류 데이터 정제 순으로 진행한다.

오류 데이터 상태

             Open : 오류가 보고만 되고 분석되지 않은 상태

             Assigned : 개발자에게 오류를 전달한 상태

             Fixed : 개발자가 오류를 수정한 상태

             Closed : 수정된 오류에 대해 다시 테스트 했을 때 오류가 발견되지 않은 상태

             Defferred : 오류 수정을 연기한 상태

             Classified : 오류가 아니라고 확인된 상태

 

120. 데이터 정제 요청서 및 정제 보고서

데이터 정제 유형

             완전성 : 업무상 반드시 필요한 자료가 누락된 경우 ex. 주민번호가 누락

             유효성 : 항목의 값이 유효하지 않은 경우 ex. 생년월일이 2080년

             일치성 : 상호 관련이 있는 항목이 서로 다른 경우 ex. 주민번호가 여자인데 1로 시작

             유일성 : 서로 달라야 하는 값이 동일한 경우 ex. 서로 주민번호가 같은 경우