2020 정보처리기사

[정보처리기사 실기] 데이터 입출력 구현

SungJe 2020. 6. 24. 02:17

1. 논리 데이터 모델 설계

데이터 모델링 개요

  • 정의 : 현실 세계의 정보들을 컴퓨터에 표현하기 위해서 단순화, 추상화하여 체계적으로 표현한 개념적 모형
  • 목적
    • 정보 요구에 대한 정확한 이해
    • 이해관계자 간에 효율적인 의사소통 수단을 제공
    • 고품질 SW와 유지보수 비용의 감소 효과를 기대
    • 신규 또는 개선 시스템의 개발 기초를 제공
  • 특성
    • 데이터 중심 분석을 통한 업무 흐름 파악이 용이
    • 데이터 무결성을 보장
    • 데이터의 공유를 통한 중복을 제거하고 일관성 있는 정보를 제공 받음
  • 구성요소
    • 개체(Entity) : 데이터베이스에 표현하려는 것으로, 현실 세계의 대상체
    • 속성(Attribute) : 데이터의 가장 작은 논리적 단위로써, 데이터 항목 또는 데이터 필드에 해당
    • 관계(Relationship) : 개체 간의 관계 또는 속성 간의 논리적인 연결
  • 표시할 요소
    • 구조(Structure) : 논리적으로 표현된 개체 타입들 간의 관계, 데이터 구조 및 정적 성질을 표현
    • 연산(Operation) : DB에 저장된 실제 데이터를 처리하는 작업에 대한 명세, DB를 조작하는 기본 도구
    • 제약 조건(Constraint) : DB에 저장될 수 있는 실제 데이터의 논리적인 제약 조건

 

데이터 모델링 절차

  1. 개념 데이터 모델링 : 현실 세계에 대한 인식을 추상적 개념으로 표현, 논리 데이터 모델의 기초, 대표적으로 E-R 모델

  2. 논리 데이터 모델링 : 개념적 구조를 컴퓨터가 이해하고 처리할 수 있는 환경으로 변환, 정규화(Normalization) 하여 모델링

  3. 물리 데이터 모델링 : 데이터베이스 생성을 위한 물리 구조로 변환

  4. 데이터베이스 구축

 

이상(Anomaly)

  • 데이터들이 불필요하게 중복되어 릴레이션 조작 시 예기치 못한 곤란한 현상

  • 종류

    • 삽입 이상(Insertion Anomaly) : 릴레이션에 데이터를 삽입할 때 원하지 않은 값들도 함께 삽입되는 현상

    • 삭제 이상(Deletion Anomaly) : 릴레이션에서 한 튜플을 삭제할 때 상관없는 값들도 함께 삭제되는 현상

    • 갱신 이상(Update Anomaly) : 튜플에 있는 속성 값을 갱신할 때 일부 튜플의 정보만 갱신되어 정보에 모순이 생기는 현상

 

정규화(Normalization)

  • 중복성을 최소화하고 정보의 일관설을 보장

  • 정규화 과정

    1. 1NF : 도메인이 원자 값

    2. 2NF : 1NF를 만족, 부분적 함수 종속 제거

    3. 3NF : 2NF를 만족, 이행적 종속(A→B이고 B→C일 때, A→C를 만족하는 관계를 의미)

    4. BCNF : 3NF를 만족, 결정자이면서 후보키가 아닌 것 제거

    5. 4NF : BCNF를 만족, 다치 종속

    6. 5NF : 조인 종속성 이용

  • 장점

    • 유연한 데이터 구축이 가능

    • 데이터의 정확성이 높아짐

  • 단점

    • 물리적 접근이 복잡

    • 과도한 조인 발생

 

2. 물리 데이터 모델 설계

물리적 모델링 수행 순서

  1. 단위 엔티티를 테이블로 변환

  2. 속성을 칼럼으로 변환

  3. 주 식별자를 기본키(Primary Key)로 변환

  4. 관계를 외래키(Foreign Key)로 변환

  5. 칼럼 유형(Type)과 길이(Length)를 정의

  6. 역정규화(Denormalization)

 

역정규화(Denormalization) 또는 반정규화

  • 시스템의 성능 향상, 개발 및 운영의 편의성 등을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 과정, 의도적으로 정규화 원칙을 위배하는 행위

  • 기법

    • 테이블 통합 : 조인(Join)이 자주 사용되는 경우 사용

    • 테이블 분할

      • 수평 분할 : 레코드 별로 사용 빈도의 차이가 큰 경우 사용

      • 수직 분할 : 테이블에 속성이 너무 많을 경우 사용

    • 중복 테이블 추가 : 데이터를 추출해서 사용해야 하거나 다른 서버에 저장된 테이블을 이용해야 하는 경우 사용

    • 중복 속성 추가 : 데이터를 조회하는 경로를 단축하기 위해 자주 사용하는 속성을 하나 더 추가

 

인덱스 설계

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

  • 적용 기준

    • 인덱스 칼럼의 분포도가 10~15% 이내인 경우

    • 부분 처리를 목적으로 하는 경우

    • 조회 및 출력 조건으로 사용되는 칼럼인 경우

  • 종류

    • 트리 기반 인덱스 : 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것

    • 비트맵 인덱스 : 인덱스 칼럼의 데이터를 Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법

    • 함수 기반 인덱스 : 칼럼의 값 대신 칼럼에 특정 함수(Function)나 수식(Expression)을 적용하여 산출된 값을 사용하는 것

    • 비트맵 조인 인덱스 : 다수의 조인된 객체로 구성된 인덱스

    • 도메인 인덱스 : 개발자가 필요한 인덱스를 직접 만들어 사용하는 것

  • 고려사항

    • 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 미칠 수 있다

    • 인덱스를 지나치게 많이 만들면 오버헤드가 발생한다

    • 넓은 범위를 인덱스로 처리하면 많은 오버헤드가 발생한다

    • 추가적인 저장 공간이 필요하다

    • 인덱스와 테이블 데이터의 저장 공간이 분리되도록 설계한다

 

뷰 설계

  • 개요 : 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블

  • 특징

    • 기본 테이블과 같은 형태의 구조를 사용

    • 가상 테이블이기 때문에 물리적으로 구현되어 있지 않다

    • 데이터의 논리적 독립성을 제공

    • 관리가 용이하고 명령문이 간단함

    • 데이터를 안전하게 보호

  • 설계 순서

    • 대상 테이블을 선정

    • 대상 칼럼을 선정

    • 정의서를 작성

 

클러스터 설계

  • 개요 : 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방법

  • 적용기준

    • 분포도가 넓을수록 유리한 기법

    • 대량의 범위를 자주 액세스하는 경우 적용

    • ORDER BY, GROUP BY, UNION이 빈번한 테이블

 

파티션 설계

  • 개요 : 대용량의 테이블이나 인덱스를 작은 논리적 단위로 나누는 것

  • 장점

    • 데이터 액세스 범위를 줄여 성능 향상

    • 전체 데이터의 훼손 가능성 감소 및 데이터 가용성 향상

    • 각 분할 영역을 독립적으로 백업하고 복구 가능

  • 종류

    • 범위 분할(Range Partitioning) : 지정한 열의 값을 기준으로 분할

    • 해시 분할(Hash Partitioning) : 해시 함수를 적용한 결과 값에 따라 데이트를 분할

    • 조합 분할(Composite Partitioning) : 범위 분할로 분할한 다음 해시함수를 적용하여 다시 분할

 

3. 데이터 조작 프로시저 작성하기

데이터 정의어(DDL : Data Definition Language)

SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어

명령어 기능
CREATE SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의
ALTER TABLE에 대한 정의를 변경하는데 사용
DROP SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

 

데이터 조작어(DML : Data Manipulation Language)

데이터를 실질적으로 처리하는데 사용되는 언어

명령어 기능
SELECT 테이블에서 조건에 맞는 튜플을 검색
INSERT 테이블에 새로운 튜플을 삽입
DELETE 테이블에서 조건에 맞는 튜플을 삭제
UPDATE 테이블에서 조건에 맞는 튜플의 내용을 변경

 

데이터 제어어(DCL : Data Control Language)

데이터 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용되는 언어

명령어 기능
COMMIT 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음
ROLLBACK 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
GRANT 데이터베이스 사용자에게 사용 권한을 부여
REVOKE 데이터베이스 사용자의 사용 권한을 취소

 

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

트랜잭션을 제어하는 COMMIT, ROLLBACK, SAVEPOIN 명령

 

절차형 SQL

  • 개요 : 프로그래밍 언어와 같이 연속적인 실행이나 분기, 반복 등의 제어가 가능한 SQL

  • 특징

    • Compile이 필요 없어 Script 생성 및 변경 후 바로 실행이 가능

    • 식별자를 선언할 수 있음

    • 절차적 언어 구조로 된 프로그램을 작성

    • Error 처리가 가능

    • 성능 향상 기대

  • 종류

    • 프로시저(Procedure)

    • 트리거(Trigger)

    • 사용자 정의 함수(Function)

 

프로시저(Procedure)

  • 개요 : 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어, 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행

  • 프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
IS
[지역변수 선언]
BEGIN
  프로시저 BODY;
END;

OR REPLACE : 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체
파라미터
 - IN : 프로시저에게 값을 전달할 때 지정
 - OUT : 호출 프로그램에게 값을 반환할 때 지정
 - INOUT : 프로시저에게 값을 전달하고, 호출
 - 매개변수명 : 전달받은 값을 저장할 변수의 이름을 지정
 - 자료형 : 변수의 자료형을 지정

  • 프로시저 실행

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;
  • 프로시저 제거
DROP PROCEDURE 프로시저명;

 

트리거(Trigger)

  • 개요 : 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
  • 트리거 생성
CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
  트리거 BODY;
END;

OR REPLACE : 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체
동작시기 옵션
 - AFTER : 테이블이 변경된 후에 트리거가 실행
 - BEFORE : 테이블이 변경되기 전에 트리거가 실행
동작 옵션
 - INSERT : 테이블에 새로운 튜플을 삽입할 때 트리거가 실행
 - DELETE : 테이블에 튜플을 삭제할 때 트리거가 실행
 - UPDATE : 테이블에 튜플을 수정할 때 트리거가 실행
FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미
WHEN : 트리거를 적용할 튜플의 조건을 지정
  • 트리거 제거
DROP TRIGGER 트리거명;

 

사용자 정의 함수(Function)

  • 개요 : 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리, 종료시 처리 결과를 단일 값으로 반환하는 절차형 SQL

  • 사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
IS
[지역변수 선언]
BEGIN
  사용자 정의 함수 BODY;
  RETURN 반환값;
END;

OR REPLACE : 사용자 정의 함수의 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체
파라미터
 - IN : 사용자 정의 함수에게 값을 전달할 때 지정
 - 매개변수명 : 전달받은 값을 저장할 변수의 이름을 지정
 - 자료형 : 변수의 자료형을 지정
  • 사용자 정의 함수 실행
SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES(사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;


4. 데이터 조작 프로시저 최적화 하기

SQL 성능 개선 순서

  1. 문제 있는 SQL 식별 : 성능 측정 도구인 APM을 사용하여 문제가 있는 SQL을 식별

  2. 옵티마이저(Optimizer) 통계 확인 : SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성

  3. 실행 계획 검토 : DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차를 검토

  4. SQL문 재구성 : WHERE 절을 추가하여 조회 범위를 줄여 처리속도를 개선

  5. 인덱스 재구성 : 액세스 경로를 고려하여 인덱스화

  6. 실행 계획 유지 관리 : 데이터베이스의 변경 사항 발생 시 살행 계획이 유지되고 있는지 모니터링

 

APM(Application Performance Management)

  • 개요 : 애플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구

  • 유형

  • 리소스 모니터링 : CPU, 메모리, 네트워크, 디스크 등을 모니터링

  • 엔드 투 엔드(End to End) 모니터링 : 애플리케이션 수행 관점과 최종 사용자 관점의 모니터링

 

옵티마이저(Optimizer)

  • 개요 : 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈

  • 종류

  RBO(Rule Based Optimizer) CBO(Cost Based Optimizer)
최적화 기준 규칙에 정의된 우선순위 액세스 비용
성능 기준 개발자의 SQL 숙련도 옵티마이저의 예측 성능
특징 실행 계획 예측이 쉬움 성능 통계치 정보 활용, 예측이 복잡함
고려사항 개발자의 규칙 이해도, 규칙의 효율성 비용 산출 공식의 정확성