정보처리기사 필기 정리 - 3과목

정보처리기사 3과목은 데이터베이스 구축이다.

대부분의 내용이 SQLD에서 공부한 내용과 겹쳐서 공부할 내용이 줄어든 느낌이다(오히려 SQLD가 더 자세하게 다룬다).

역시 이것저것 배워두면 언젠간 쓸모가 있나보다.

1. SQL 응용

1-1. 절차형 SQL 작성

사용자 정의함수

절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일값으로 반환할 수 있는 SQL

• 사용자 정의함수의 구성

DECLARE -- 변수 및 상수, 타입 선언
BEGIN -- 사용자 함수 실행시 기점
	CONTROL -- 단위 블록별 실행흐름을 제어
	SQL -- 데이터 관리를 위한 조회, 추가, 수정, 삭제
	EXCEPTION -- 실행중 발생가능한 예외상황 수행
	RETURN -- 호출문에 함수값 적용
END -- 사용자 함수 실행시 종점

트리거

특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램

• 트리거의 세 가지 구성 요소
  • 트리거가 실행될 조건이 되는 문장이나 이벤트
  • 실행 조건의 제약
  • 실행될 내용

DECLARE -- 변수 및 상수, 타입 선언
BEGIN -- 트리거가 실행되는 이벤트 명시
	CONTROL -- 단위 블록별 실행흐름을 제어
	SQL -- 데이터 관리를 위한 조회, 추가, 수정, 삭제
	EXCEPTION -- 실행중 발생가능한 예외상황 수행
END -- 트리거 실행시 종점

• 트리거 작성시 주의사항
  • 데이터 제어어 사용 불가
  • 오류에 특히 주의할 것

이벤트

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

1-2. 응용 SQL 작성

DML

데이터베이스에 있는 데이터를 변경하거나 검색하기 위하여 사용되는 명령어

• INSERT (INTO)
• UPDATE (SET)
• DELETE (FROM)
• SELECT (FROM)

DCL

사용자별로 데이터베이스에 접근할 수 있는 권한을 부여하거나 회수하는 명령어

• GRANT (TO / ON ~ TO )
• REVOKE (FROM / ON ~ FROM)
• COMMIT
• ROLLBACK

집계성 SQL 작성

단일 행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아 놓고 그룹당 단일 계산 결과를 반환

• 데이터 분석 함수 종류
  • 집계 함수
    • COUNT
    • SUM
    • AVG
    • MAX
    • MIN
  • 그룹 함수
    • ROLLUP
    • CUBE
    • GROUPING SETS
  • 윈도우 함수
    • 순위함수
    • 그룹 내 비율 함수
    • 행순서 함수

오류 처리

• 오류 처리 방법
  • 오류 복구
  • 오류 회피
  • 오류 전환

2. SQL 활용

2-1. 기본 SQL 작성

DDL

데이터를 저장하는 테이블 등의 구조를 생성하고 변경하기 위해 사용하는 명령어

• CREATE
• DROP
• RENAME (TO)
• ALTER (ADD / MODIFY / DROP / RENAME)

• TRUNCATE

• 제약 조건 유형
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK

관계형 데이터 모델

• 2차원 테이블을 구성하여 테이블 내에 있는 속성들 간의 관계를 설정하거나 테이블 간의 관계를 정의하는 DB구조
• 데이터 간의 관계를 기본키와 이를 참조하는 외래키로 표현

• 테이블 간의 관계를 1:1, 1:N, M:N 관계로 표현

• E-R(Entity Relationship) 모델
  • 개체 : 사각형
  • 관계 : 마름모
  • 속성 : 원

트랜잭션

• 트랜잭션 특징
  • 원자성(Atomicity)
  • 일관성(Consistency)
  • 고립성(Isolation)
  • 영속성(Durability)

테이블

데이터를 저장하는 객체로서 관계형 데이터베이스 기본 단위

데이터 사전

데이터 사전에는 데이터베이스의 데이터를 제외한 모든 정보가 있음

• 오라클에서 데이터 사전 검색 : DBA_ > ALL_ > USER_
• MySQL에서 데이터 사전 검색
  • use imformation_schema;
  • show tables;

2-2. 고급 SQL 작성

뷰

하나 이상의 물리 테이블로부터 생성 가능한 가상 테이블

• 장점
  • 논리적 독립성 제공
  • 사용자 데이터 관리 용이
  • 데이터 보안 용이
• 뷰의 단점
  • 뷰 자체 인덱스 불가
  • 뷰 정의 변경 불가
  • 데이터 변경 제약 존재

인덱스

인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료구조

다중 테이블 검색

• 다중 테이블 검색 방법
  • 조인 : 두 개의 테이블을 결합하여 데이터를 추출하는 기법
  • 서브쿼리 : SQL문 안에 포함된 SQL문 형태의 사용 기법
  • 집합연산 : 테이블을 집합 개념으로 조작하는 기법

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

3-1. 관계데이터베이스 모델

관계 데이터 모델

• 관계 데이터 모델 기본용어
  • 릴레이션 (=테이블)
  • 속성 (=컬럼, 릴레이션 스키마)
  • 튜플 (=로우, 릴레이션 인스턴스)
  • 도메인 (=데이터 타입)
  • 차수 (=컬럼 수)
  • 카디널리티 (=로우 수)
• 릴레이션의 특성
  • 튜플의 유일성
  • 튜플의 무순서
  • 속성의 무순서
  • 속성의 원자성

관계데이터언어(관계대수, 관계해석)

• 관계데이터 연산
  • 관계 대수(절차적 언어)
    • 일반 집합 연산자
      • 합집합
      • 교집합
      • 차집합
      • 카티션 프로덕트
        • 차수는 더한다(컬럼은 옆으로 늘어남)
        • 카디널리티는 곱한다(로우는 경우의수만큼)
    • 순수 관계 연산자
      • 셀렉트
      • 프로젝트
      • 조인
      • 디비전
  • 관계 해석(비절차적 언어)
    • 도메인 관계 해석
    • 튜플 관계 해석

시스템 카탈로그와 뷰

시스템 카탈로그는 데이터베이스의 객체(사용자, 릴레이션, 뷰, 인덱스, 권한 등)와 구조들에 관한 모든 데이터를 포함하는 시스템 데이터베이스

• 시스템 카탈로그 내용
  • 릴레이션의 정보
  • 인덱스 관련 정보
  • 뷰 관련 정보
  • 사용자 관련 정보
  • 통계 관련 정보

3-2. 데이터모델링 및 설계

데이터 모델 개념

• 데이터 모델 종류
  • 개념적 데이터 모델
    • 중요 개념을 구분
    • 핵심 개체(Entity) 도출
    • ERD 작성
  • 논리적 데이터 모델
    • 각 개념을 구체화
    • ERD-RDB모델 사상
    • 상세 속성 정의
    • 정규화 등
  • 물리적 데이터 모델
    • 개체, 인덱스 등 생성
    • DB 개체 정의
    • 테이블 및 인덱스 등 설계
• 데이터 모델 구성 요소
  • 개체
  • 속성
  • 관계
• 데이터 모델에 표시할 요소
  • 구조
  • 연산
  • 제약조건

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

E-R 모델은 개체 타입과 이들 간의 관계 타입을 이용하여 현실 세계를 개념적으로 표현

논리적 데이터모델링

E-R 다이어그램으로 표현된 개념적 구조를 데이터베이스에 저장할 형태로 표현한 논리적 구조로 정의하는 과정

데이터베이스 정규화

중복성을 최소화하고 정보의 일관성 보장을 위해 릴레이션을 관련 있는 속성들로만 구성되도록 릴레이션을 분해하는 과정

논리 데이터모델 품질검증

• 좋은 데이터 모델의 조건
  • 완전성
  • 중복배제
  • 비즈니스 룰
  • 데이터 재사용
  • 안정성 및 활용성
  • 간결성
  • 의사소통
  • 통합성
• 데이터 모델 품질 검증 기준
  • 정확성
  • 완전성
  • 준거성
  • 최신성
  • 일관성
  • 활용성

4. 물리 데이터베이스 설계

4-1. 물리요소 조사 분석

스토리지

• 스토리지 종류
  • DAS(Direct Attached Storage)
  • NAS(Network Attached Storage)
  • SAN(Storage Area Network)

분산 데이터베이스

• 분산 데이터베이스의 4가지 투명성
  • 위치투명성
  • 중복투명성
  • 병행투명성
  • 장애투명성
• 분산 데이터베이스 설계 방법
  • 테이블 위치 분산, 분할, 할당

데이터베이스 이중화 구성

• 데이터 이중화의 분류
  • Eager 기법
  • Lazy 기법
• 데이터 이중화의 구성 방법
  • Active-Active
  • Active-Standby

데이터베이스 암호화

• 데이터베이스 암호화 알고리즘
  • 대칭 키(비밀키) 암호 알고리즘
    • DES
    • ARIA
    • SEED
  • 해시 알고리즘
    • SHA
    • HAS
  • 비대칭 키(공개키) 암호 알고리즘
    • RSA
    • ECDSA
• 데이터베이스 암호화 기법
  • API 방식
  • Plug-in 방식
  • Hybrid 방식

접근통제

• 접근통제의 3요소
  • 접근통제 정책
  • 접근통제 매커니즘
  • 접근통제 보안모델

4-2. 데이터베이스 물리속성 설계

파티셔닝

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

• 파티션의 종류
  • 범위 분할
  • 해시 분할
  • 조합 분할(범위+해시)

클러스터링

• 클러스터 대상 테이블
  • 분포도가 넓은 테이블
  • 대량의 범위를 자주 조회하는 테이블
  • 입력, 수정, 삭제가 자주 발생하지 않는 테이블
  • 자주 조인되어 사용되는 테이블
  • ORDER BY, GROUP BY, UNION이 빈번한 테이블

데이터베이스 백업

• 데이터베이스 복구 알고리즘
  • NO-UNDO/REDO
  • UNDO/NO-REDO
  • UNDO/REDO
  • NO-UNDO/NO-REDO
• 백업 종류
  • 물리 백업
  • 논리 백업

데이터베이스 용량 설계

테이블스페이스는 테이블이 저장되는 논리적인 영역으로, 하나의 테이블스페이스에 하나 또는 그 이상의 테이블 저장 가능

• 테이블스페이스 설계 시 고려사항
  • 테이블스페이스는 업무별로 구분하여 지정
  • 대용량 테이블은 하나의 테이블스페이스에 독립적으로 저장
  • 테이블과 인덱스는 분리하여 저장
  • LOB(Large Object)타입의 데이터는 독립적인 공간으로 지정

데이터 지역화

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

4-3. 물리 데이터베이스 모델링

데이터베이스 무결성

• 무결성의 종류
  • 개체 무결성
  • 도메인 무결성
  • 참조 무결성
  • 사용자 정의 무결성
• 데이터 무결성 강화 방법
  • 애플리케이션
  • 데이터베이스 트리거
  • 제약 조건

칼럼 속성

• 속성의 특성에 따른 분류
  • 기본 속성
  • 설계 속성
  • 파생 속성
• 개체 구성 방식에 따른 분류
  • 기본키 속성
  • 외래키 속성
  • 일반 속성

키 종류

• 키의 종류
  • 슈퍼키(유일성 만족 / 최소성 불만족)
  • 후보키(유일성 만족 / 최소성 만족)
  • 기본키
  • 대체키
  • 외래키

반정규화

• 반정규화의 방법
  • 테이블 통합
  • 테이블 분할
  • 중복 테이블 추가
  • 중복 속성 추가

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

물리 데이터 모델 품질 기준

• 물리 데이터 모델 품질 기준 (논리 데이터 모델 품질 기준과 동일함)
  • 정확성
  • 완전성
  • 준거성
  • 최신성
  • 일관성
  • 활용성

물리 E-R 다이어그램

• 논리 데이터 모델 → 물리 데이터 모델
  • 단위 엔티티를 테이블로 변환
    • 슈퍼 타입
    • 서브 타입
    • 개별 타입
  • 속성을 칼럼으로 변환
  • UID를 기본키로 변환
  • 관계를 외래키로 변환
  • 관리 목적의 테이블/칼럼 추가
  • 칼럼 유형과 길이 정의
  • 데이터 표준 정의

CRUD 분석

데이터 프로세스를 행으로 하고 엔티티 목록을 열로 하여 CRUD Matrix를 작성한 후 사용되지 않는 프로세스와 엔티티 여부를 확인하는 분석 기법

• 점검사항
  • 모든 엔티티 타입에 CRUD가 한 번 이상 표기
  • 모든 엔티티 타입에 C가 한 번 이상 존재
  • 모든 엔티티 타입에 R이 한 번 이상 존재
  • 모든 단위 프로세스가 하나 이상의 엔티티 타입에 표기

SQL 성능 튜닝

• SQL 성능 튜닝의 순서
  • 문제 있는 SQL 식별
  • 옵티마이저 통계 확인
  • 실행 계획 검토
  • SQL문 재구성
  • 인덱스 재구성
  • 실행 계획 유지 관리

5. 데이터 전환

5-1. 데이터 전환 기술

초기 데이터 구축

• 초기 데이터 구축 절차
  • 구축 전략 수립
  • 초기 데이터 구축 대상 파악
  • 초기 데이터 구축 범위
  • 초기 데이터 구축 시 세부 고려사항

ETL(Extraction, Transformation, Loading)

다양한 소스시스템으로부터 필요한 데이터를 추출하여 새로 개발할 정보시스템에서 운영 가능하도록 변환 작업을 거쳐 타겟 시스템으로 전송 및 로딩하는 일련의 과정을 뜻함

파일 처리 기술

• 파일 처리 기술 종류
  • 순차파일
  • 색인 순차파일
  • 가상 기억 접근 방식(VSAM) 파일 (동적 인덱스)
  • 직접 파일 (해시 함수)

5-2. 데이터 전환 수행

데이터 전환 수행 계획

• 데이터 전환 순서
  • 데이터 전환 범위 설정
  • 데이터 전환 일정 선정
  • 데이터 전환 절차
  • 데이터 전환 계획서 작성
    • 데이터 전환 개요
    • 데이터 전환 대상 및 범위
    • 데이터 전환 환경구성
    • 데이터 전환 조직 및 역할
    • 데이터 전환 일정
    • 데이터 전환 방안
    • 데이터 정비 방안
    • 비상계획

체크리스트

위험요소에 최대한 대응하기 위해 체크리스트 작성

• 체크리스트 포함 내용
  • 데이터 전환 수행자가 수행할 작업의 상세 항목
  • 작업 내용
  • 작업 담당자
  • 예정 시각, 종료 시각

데이터 검증

• 검증 방법에 따른 분류
  • 로그 검증
  • 기본 항목 검증
  • 응용 프로그램 검증
  • 응용 데이터 검증
  • 값 검증
• 데이터 단계에 따른 분류
  • 추출
  • 전환
  • DB 적재
  • DB 적재 후
  • 전환 완료 후

5-3. 데이터 정제

데이터 정제

• 단계별 데이터 정제 방안
  • 전환 테스트 전
  • 전환 테스트 중
  • 최종 전환

데이터 품질 분석

데이터 품질 관리는 기관이나 조직 내외부의 정보시스템 및 DB 사용자의 기대를 만족시키기 위해 지속적으로 수행하는 데이터 관리 및 개선활동

• 데이터 품질 분석 종류
  • 원천 데이터 품질 분석
  • 목적 데이터 품질 분석 (대응관계 파악)

오류 데이터 측정

데이터 중 정상 데이터와 오류 데이터를 정량적으로 측정

• 오류 원인 파악
  • 해결 방안
  • 심각도 (상, 중, 하)
  • 상태