3. 데이터베이스 구축

3. 데이터베이스 구축.pdf

5.02MB

데이터베이스 구축

SQL 응용

절차형 SQL 작성

트리거

트리거의 개념

트리거는 절차형 SQL이며 테이블 또는 뷰와 관련하여 삽입, 수정, 삭제 등 DML 문의 이벤트가 발생될 때 작동하는 프로그램

데이터 변경, 무결성 유지, 로그 메세지 출력 등의 목적으로 사용된다.

트리거의 종류

실행시점에 따른 분류

• AFTER
• BEFORE

실행범위에 따른 분류

• ROW
• STATEMENT

이벤트

이벤트의 정의

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

트리거를 실행시키는 사건, DML Event, DDL_Event, DATABASE_Event로 구분

• DML Event : INSERT, UPDATE, DELETE
• DDL_Event : CREATE, ALTER, DROP
• DATABASE_Event : SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN

이벤트와 트리거 작성 순서

1. 인식이 가능한 이벤트 정의트랜잭션의 특성
   • 원자성
   • 일관성
   • 독립성
   • 영속성
2. 트랜잭션
3. 이벤트와 연관된 테이블 및 데이터 확인
4. 
5. 기존 테이블 간의 상호 구조 및 결과값 반환을 위한 관련 데이터 분석
6. 트리거 데이터 흐름도 작성, 트리거 내부 변수 분석
7. 트리거 작성
8. 트리거 컴파일 후 DBMS에 반영

사용자 정의 함수

사용자 정의 함수의 개념

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

사용자 정의 함수의 특징

내장함수처럼 사용이 가능하다.

SQL 문법

MyBatis의 개념

MyBatis의 정의

SQL Mapping 기반 오픈소스 Access Framework

DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML 파일로 분리하고 Mapping을 통해 SQL을 실행한다.

MyBatis의 장점

• 복잡한 JDBC 코드를 단순화할 수 있다.
• SQL을 거의 그대로 사용 가능하다.
• Spring 기반 프레임워크와 통합 기능을 제공한다.
• 우수한 성능을 보여준다.

MyBatis SQL 작성 문법 기본 구조

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper 
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="[SITE URL & Project Structure Tree]" 
  <[SQL TYPE] id = [SQL ID] resultType = [RESULT TYPE]>
    [SQL]
  </[SQL TYPE]>
<mapper>

DQL, DML SQL 문장의 Input Parameter 입력 방법

MyBatis에서는 입력 변수를 #{Parameter Name} 으로 처리한다.

<mapper>
  <select id = "findId" resultType = "map">
    SELECT USER_ID
    FROM USER_INFO
    WHERE USER_NM = #{user_nm}
      AND BIRTH_DAY = #{user_birth_day} 
  </select>
</mapper>

MyBatis를 이용한 SQL 구문 변경[동적 SQL]

조건에 따라 SQL 구문을 변경할 수 있는 것을 동적 SQL이라고 한다.

동적 SQL이 필요한 경우는 컴파일 시에 SQL문이 확정되지 않거나 PL/SQL 블록 상에서 DDL문을 실행해야 하는 경우, ALTER SYSTEM/SESSION 명령어를 실행해야 하는 경우가 있다.

<mapper>
  <select id = "findId" resultType = "map">
    SELECT USER_ID
    FROM USER_INFO
    WHERE USER_NM = #{user_nm}
    <if input_para="user_birth_day != null">
      AND BIRTH_DAY = #{user_birth_day} </if>
    <if input_para="user_email != null">
      AND EMAIL = #{user_email} </if>
  </select>
</mapper>

절차형 SQL 호출

DQL, DML SQL뿐만 아니라 절차형 SQL도 실행 가능하다.

사용자 정의 함수는 DQL, DML에 포함하여 사용하므로 별도 호출의 의미가 없다.

트리거 또한 DBMS에서 바로 실행되므로 별도 호출이 필요 없다.

응용 SQL 작성

DML

DML 유형

• INSERT
• SELECT
• UPDATE
• DELETE

DCL

DCL의 정의

데이터베이스에 접근하거나 객체에 권한을 주는 등의 역할을 하는 언어

DB에서 데이터를 제외한 오브젝트에 대해 조작하는 SQL 명령

오브젝트의 종류

• 테이블
• 뷰
• 인덱스
• 시노님
• 시퀀스
• 함수
• 프로시저
• 패키지

DCL 유형

DCL 조작 대상

• 사용자 권한
• 트랜잭션

DCL 활용

권한의 유형

사용자 권한 부여

GRANT문 사용

사용자 권한 회수

REVOKE문 사용

DCL 이론적 배경인 접근 통제

접근 통제 개념

접근 객체에 대한 접근 주체의 접근 권한 확인

확인 된 권한을 기반으로 한 접근 제어를 통해 비인가된 자원 사용을 방지

접근 통제 정책에 의한 유형

• 임의 접근 통제
• 사용자, 그룹의 식별자를 기반으로 제한
• 강제 접근 통제
• 높은 보안 수준을 요구하는 정보가 낮은 보안수준의 주체에게 노출되지 않도록 접근 제한
• 역할기반 접근 통제
• 사용자가 주어진 역할에 대한 접근 권한을 부여받은 방식

TCL

트랜잭션 제어

TCL 명령어

• COMMIT
• ROLLBACK
• CHECKPOINT

윈도우 함수

윈도우 함수 개요

테이블에서 로우 집합을 대상으로 계산하는 함수

윈도우 함수 종류

윈도우 함수의 종류

윈도우 함수 구문

SELECT <WINDOWS_FUNCTION> [ARGUMENTS] OVER
  ([PARTITION BY <COLUMN_1, COLUMN_2, COLUMN_3...>]) 
  [ORDER BY <COLUMN_A, COLUMN_B, COLUMN_C...>]
FROM TABLE

윈도우 함수의 상세 분류

집계 함수

분류

• COUNT
• SUM
• AVG
• MAX
• MIN
• STDDEV
• VARIAN

순위 함수

• RANK : 레코드의 순위 계산
• ROW_NUMBER : 레코드의 순위 계산 (동일 순위 값이 존재해도 연속 번호 부여)

행 순서 함수

• FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나오는 값을 찾는다.
• LAST_VALUE : 파티션별 윈도우에서 가장 늦게 나오는 값을 찾는다.

그룹 내 비율 함수

• RATIO_TO_REPORT
• PERCENT_RANK
• CUME_DIST
• NTILE

그룹 함수

그룹 함수 개요

소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출

그룹 함수 유형

• ROLLUP지정 칼럼 수보다 하나 더 큰 레벨만큼의 중간 집계값이 생성된다.

    SELECT fact_1_id,
           fact_2_id,
           SUM(sales_value) AS sales_value
    FROM   dimension_tab
    GROUP BY ROLLUP (fact_1_id, fact_2_id)
    ORDER BY fact_1_id, fact_2_id;
  
     FACT_1_ID  FACT_2_ID SALES_VALUE
    ---------- ---------- -----------
             1          1     4363.55
             1          2     4794.76
             1          3     4718.25
             1          4     5387.45
             1          5     5027.34
             1               24291.35
             2          1     5652.84
             2          2     4583.02
             2          3     5555.77
             2          4     5936.67
             2          5     4508.74
             2               26237.04
                             50528.39

• 예제
• 중간 집계값을 산출하기 위해 사용
• CUBE예제
• SELECT fact_1_id, fact_2_id, SUM(sales_value) AS sales_value FROM dimension_tab GROUP BY CUBE (fact_1_id, fact_2_id) ORDER BY fact_1_id, fact_2_id; FACT_1_ID FACT_2_ID SALES_VALUE ---------- ---------- ----------- 1 1 4363.55 1 2 4794.76 1 3 4718.25 1 4 5387.45 1 5 5027.34 1 24291.35 2 1 5652.84 2 2 4583.02 2 3 5555.77 2 4 5936.67 2 5 4508.74 2 26237.04 1 10016.39 2 9377.78 3 10274.02 4 11324.12 5 9536.08 50528.39
• 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수
• GROUPING SETS
  • 다양한 소계 집합을 만들 수 있다.

    SELECT fact_1_id,
         fact_2_id,
         fact_3_id,
         SUM(sales_value) AS sales_value,
         GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
    FROM   dimension_tab
    GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id))
    ORDER BY fact_1_id, fact_2_id, fact_3_id;
    
    FACT_1_ID  FACT_2_ID  FACT_3_ID SALES_VALUE GROUPING_ID

  • 예제

  ---

         1          1                4363.55           1
         1          2                4794.76           1
         1          3                4718.25           1
         1          4                5387.45           1
         1          5                5027.34           1
         1                     1      2737.4           2
         1                     2     1854.29           2
         1                     3     2090.96           2
         1                     4     2605.17           2
         1                     5     2590.93           2
         1                     6      2506.9           2
         1                     7     1839.85           2
         1                     8     2953.04           2
         1                     9     2778.75           2
         1                    10     2334.06           2
         2          1                5652.84           1
         2          2                4583.02           1
         2          3                5555.77           1
         2          4                5936.67           1
         2          5                4508.74           1
         2                     1     3512.69           2
         2                     2     2847.94           2
         2                     3      2972.5           2
         2                     4     2534.06           2
         2                     5     3115.99           2
         2                     6     2775.85           2
         2                     7     2208.19           2
         2                     8     2358.55           2
         2                     9     1884.11           2
         2                    10     2027.16           2

오류 처리

오류 처리 구문 - SQL Server

Begin Try
  [SQL]
End Try
Begin Catch
  [PROCESS ERROR]
End Catch

오류 처리 구문 - ORACLE

컴파일 오류와 런타임 오류가 있다.

• 컴파일 오류 : Parsing시 발생하는 오류이다.
• 런타임 오류 : 실행하는동안 발생하는 오류로 Exception이라 불린다.

SQL 활용

기본 SQL 작성

DDL

DDL의 개념

기본 테이블, 뷰 테이블, 인덱스 등을 생성, 변경, 제거하는 데 사용하는 명령어

오브젝트 유형

DDL 대상

DDL 조작 방법

제약조건 적용

제약조건 유형

테이블 생성에 사용되는 제약조건

관계형 데이터 모델

관계형 데이터 모델 정의

관계형 데이터 모델의 용어

개체관계 모델과 관계형 데이터 모델의 관계

트랜잭션

트랜잭션의 개념

트랜잭션의 결과는 모두 반영되거나 모두 취소되어야 한다.

트랜잭션의 특성

트랜잭션의 상태

트랜잭션 상태 전이도

트랜잭션의 연산

Commit 연산

트랜잭션에 대한 작업이 성공적으로 끝나 갱신 연산이 완료된 것을 트랜잭션 관리자에게 알리는 연산

Rollback 연산

트랜잭션 처리가 비정상적으로 종료되어 데이터베이스의 일관성을 깨뜨렸을 때 트랜잭션의 원자성을 구현하기 위해 이 트랜잭션이 행한 모든 연산을 취소하는 연산

트랜잭션 제어

TCL 명령어

• COMMIT
• ROLLBACK
• CHECKPOINT

데이터 사전

데이터사전의 개념

데이터 사전은 메타데이터로 구성되어 있다.

데이터 사전의 내용을 변경하는 권한은 시스템이 가진다.

사용자에게는 읽기 전용 테이블 형태로 제공되므로 단순 조회만 가능하다.

데이터 사전 내용

데이터 사전 내의 메타데이터 유형

• 사용자 정보
• 데이터베이스 객체 정보
• 무결성 제약 정보
• 데이터베이스의 구조 정보
• 데이터베이스의 성능 정보
• 기타 일반적인 데이터베이스 정보
• 함수, 프로시저, 트리거 등

Oracle에서의 데이터 사전

데이터 딕셔너리 뷰 구분

오라클 데이터 사전 영역

• DBA_
• 데이터베이스의 모든 객체 조회 가능
• ALL_
• 자신의 계정으로 접근 가능한 객체와 타 계정의 접근 권한을 가진 모든 객체 조회 가능
• USER_
• 현재 자신이 계정이 소유한 객체 조회 가능

고급 SQL 작성

뷰

뷰의 개념

허용된 데이터를 제한적으로 보여주기 위해서 하나 이상의 테이블에서 유도된 가상 테이블

뷰는 하나 이상의 물리테이블 또는 뷰로부터 생성 가능하다.

뷰의 특징

뷰가 정의된 기본 테이블이 변경되면, 뷰도 자동적으로 변경된다.

뷰에 대한 검색은 기본 테이블과 거의 동일하지만 삽입, 삭제, 갱신은 제약을 갖는다.

보안 측면에서 뷰를 활용할 수 있다.

한번 정의된 뷰는 변경할 수 없으며, 삭제 후 재생성해야 한다.

뷰 사용

뷰의 장점과 단점

뷰의 생성

뷰 생성시의 SELECT문에는 ORDER BY, UNION을제외하고 모두 사용 가능하다.

컬럼명을 기술하지 않으면 SELECT문의 컬럼 이름이 자동으로 만들어진다.

뷰 삭제 및 변경

뷰 정의를 변경하는 것은 불가능하다.

뷰의 삭제와 재생성을 통해 뷰에 대한 정의 변경이 가능하다.

뷰 내용 변경

뷰를 통해 접근 가능한 데이터에 대한 변경이 가능하다.

인덱스

인덱스의 개념

테이블에 대한 조회 속도를 높여주는 자료구조

인덱스의 특징

B-tree 인덱스는 컬럼 안에 분포도가 높을 때 가장 좋은 효과를 낸다.

인덱스 사용 시 SQL문을 수정하지 않아도 성능 개선을 기대할 수 있다.

인덱스의 종류

인덱스 종류

비트리와 비트맵의 구조 비교

비트리

비트맵

인덱스 스캔 방식

• Index Range Scan
  
• 인덱스를 수직적으로 탐색한 후 필요한 범위를 스캔하는 방식
• Index Full Scan최선의 인덱스가 없을 때 차선으로 선택
• 
• 수직적 탐색 없이 인덱스 리프 블록을 처음부터 수평적으로 탐색하는 방법
• Index Unique Scan인덱스 컬럼에 대해 '=' 조건으로 검색시 발생
• 
• 수직적 탐색만으로 데이터를 찾는 스캔
• Index Skip Scan
  
• 데이터가 있을 가능성이 있는 리프 블록만 골라 액세스하는 방식
• Index Fast Full Scan
  
• 인덱스 트리 구조를 무시하고 인덱스 세그먼트 자체를 물리적으로 저장된 순서대로 읽는다.
• Index Range Scan Descending
  
• 내림차순으로 정렬된 결과를 얻는다는 것을 제외하면 Index Range Scan과 동일하다.

집합 연산자

집합 연산의 개념

테이블을 집합 개념으로 보고 두 테이블 연산에 집합 연산자를 사용하는 방식

집합 연산의 유형

• UNION
• SQL 결과에 대한 합집합
• UNION ALL
• SQL 결과에 대한 합집합 (중복 허용)
• INTERSECTION
• SQL 결과에 대한 교집합
• EXCEPT(MINUS)
• SQL 결과에 대한 차집합

조인

조인의 개념

RDB에서의 조인은 교집합 결합 결과를 가진다.

조인의 유형

• 논리적 조인
• 사용자의 SQL문에 표현되는 테이블 결합 방식
• 물리적 조인
• 데이터베이스의 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식

논리적 조인

논리적 조인의 종류

• 내부 조인
• 두 테이블에 공통으로 존재하는 컬럼을 이용
• 외부 조인
• 특정 테이블의 모든 데이터를 기준으로 다른 테이블의 정보를 추출

물리적 조인

• Nested Loop Join후행 테이블에는 조인을 위한 인덱스가 생성되어 있어야 한다.
• 좁은 범위에 유리하고 순차적으로 처리하며 Random Access 위주이다.
• Merge Join양쪽 테이블의 처리 범위를 각자 정렬한 결과를 차례로 Scan 하며 Merge하는 방식조인 연결시의 비교 연산자가 (<, >)일 때 Nested Loop Join보다 유리하다.
• 연결을 위해 랜덤 액세스를 하지 않고 스캔을하면서 수행
• 
• Hash Join소트 대신 해시값을 이용하는 조인대용량 데이터 사용시 메모리의 지나친 사용으로 인한 오버헤드 발생 가능성이 높다.
• 해시키 값으로 사용되는 컬럼에 중복값이 거의 없을 경우 효과적
• 

서브쿼리

서브쿼리의 개념

SQL문 안에 포함 된 또 다른 SQL문

서브쿼리의 유형

동작 방식에 따른 서브쿼리 유형

• 비연관 서브쿼리
• 연관 서브쿼리

반환 데이터 형식에 따른 서브쿼리 종류

• 단일 행 서브쿼리
• 다중 행 서브쿼리
• 다중 컬럼 서브쿼리

논리 데이터베이스 설계

관계 데이터베이스 모델

관계 데이터 모델

관계 데이터 모델 구성

DB는 테이블들의 모임으로 표현, 스키마와 인스턴스로 이루어진다.

테이블은 튜플들의 모임으로 표현되며, 튜플은 어트리뷰트들로 구성된다.

관계 데이터 모델은 데이터를 2차원 테이블 형태인 릴레이션으로 표현하며 릴레이션에 대한 제약조건과 관계연산을 위한 관계대수를 정의한다.

릴레이션의 특징

• 속성은 단일 값을 가진다.
• 속성은 서로 다른 이름을 가진다.
• 한 속성의 값들은 모두 같은 도메인 값을 가진다.
• 속성의 순서는 상관 없다.
• 릴레이션 내의 중복된 튜플은 허용하지 않는다.
• 튜플의 순서는 상관 없다.

무결성 제약조건

도메인 무결성 제약조건

릴레이션 내의 튜플들이 각 속성의 도메인에 지정된 값만을 가져야 한다는 조건

개체 무결성 제약조건

기본키는 Null 값을 가져서는 안 되며 릴레이션 내에 오직 하나의 값만 존재해야 한다는 조건

참조 무결성 제약조건

자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 동일해야 하며,
자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다.

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

관계대수의 정의

어떤 데이터를 어떻게 찾는지에 대한 처리 절차를 명시하는 절차적 언어

관계대수 연산자의 종류

순수 관계 연산자

일반 집합 연산자

관계대수의 한계

• 관계대수는 산술연산을 할 수 없다.
• 집단함수를 지원하지 않는다.
• 정렬을 나타낼 수 없다.
• DB를 수정할 수 없다.

관계해석의 정의

관계 데이터의 연산을 표현하는 방법

관계해석의 특징

비절차적인 언어

튜플 관계 해석과 도메인 관계해석이 있다.

관계해석에 질의어를 표현한다.

연산들의 절차를 사용하여 데이터를 가져온다.

튜플 관계해석

원하는 릴레이션을 튜플해석식으로 정의하는 기법

관계대수와의 차이점

관계해석은 하나의 선언적 해석식으로 검색 질의를 명시하며, 비절차적인 언어이다.

튜플 관계해석의 일반식 형태

조인 + 셀렉트 + 프로젝트

{ e.FNAME, e.LNAME | EMPLOYEE(e) and DEPARTMENT(d) and (d.DNAME = 'Research' and d.DNUMBER = e.DNO) }

튜플 변수

지정된 릴레이션의 튜플을 하나씩 그 값으로 취할 수 있는 변수

범위식이 R(t)일 때 R을 t의 범위 릴레이션이라고 한다.

한정 어트리뷰트

릴레이션 R에 대해 튜플변수 t가 나타내는 튜플의 어떤 어트리뷰트 A의 값을 표현한다.

t.A or t[A]

원자식

튜플 해석식의 가장 기본이 되는 식

정형식

원자식, AND 연산자(∧), OR 연산자(∨), NOT 연산자(￢), 정량자(∀∃)가 결합된 식

• 전칭 정량자 (Every)모든 가능한 튜플 t에 대해 정형식 F(t)가 참일 때 참
• (∀)(F(t))
• 존재 정량자 (Any)정형식 F(t)를 참으로 만드는 어떤 튜플 t가 하나라도 존재할 때 참
• (∃)(F(t))

도메인 관계해석

튜플 변수 대신 도메인 변수를 사용하는 관계해석

도메인 변수는 한 어트리뷰트 도메인을 범위로 가진다.

도메인 관계 해석의 일반식 형태

{ SALARY, ADDRESS | (EMPLOYEE(FNAME,MNAME,LNAME,SALARY,ADDRESS) and FNAME='John" and MNAME='B' and LNAME='Smith') }

| 왼편에 나온 도메인 변수들은 목표 리스트로서 | 오른편에 명세된 조건을 만족하는 도메인 값으로 만들어지는 튜플

시스템 카탈로그

시스템 카탈로그의 정의

시스템이 필요로 하는 데이터베이스, 테이블, 뷰, 인덱스, 사용자, 응용 계획, 접근 권한, 무결성, 제약사항에 관한 모든 정보를 메타 데이터 형태로 가지고 있는 시스템 데이터베이스

시스템 카탈로그의 특징

카탈로그 자체도 시스템 테이블로 구성되어 있으며, 일반 이용자도 SQL을 이용하여 내용을 검색할 수 있다.

기본테이블, 뷰, 인덱스 등이 변경되면 자동으로 시스템 카탈로그는 갱신된다.

시스템 카탈로그의 내용

데이터 모델링 및 설계

데이터 모델 개념

데이터 모델 정의

현실세계의 복잡한 개념을 단순화하고 추상화시켜 데이터베이스화하는 과정

데이터 모델링 분류

데이터 모델링 과정 이해

개념적 데이터 모델

개체를 추출하고 각 개체들간의 관계를 정의하여 ER 다이어그램을 제작

논리적 데이터 모델

개념적 모델링에서 만든 ER 다이어그램을 사용하려는 DBMS에 맞게 사상하여 실제 데이터베이스로 구현하기 위한 모델을 제작

물리적 데이터 모델

작성된 논리적 모델을 실제 컴퓨터의 저장장치에 저장하기 위한 물리적 구조를 정의하고 구현하는 과정

데이터 모델에 표시할 요소

구조 : 논리적으로 표현된 개체 타입들 간의 관계로서 데이터 구조 및 정적 성질을 표현한다.

연산 : 데이터베이스에 저장된 실제 데이터를 처리하는 작업에 대한 명세

제약조건 : 데이터베이스에 저장될 수 있는 실제 데이터의 논리적인 제약조건

데이터 모델의 구성 요소

개체 : 실세계에 독립적으로 존재하는 유 무형의 정보, 서로 연관된 몇 개의 속성으로 구성

속성 : 데이터의 가장 작은 논리적 단위

관계 : 개체 간의 관계, 속성 간의 관계

관계의 형태

• 1 : 1
• 1 : N
• N : N

개체-관계 모델

개체-관계 모델의 정의

데이터를 개체, 관계, 속성으로 묘사

E-R 다이어그램으로 표현한다.

E-R 다이어그램의 기호

논리적 데이터 모델링

논리 데이터 모델링 정의

개념적 모델링과 논리적 모델링의 비교

관계 데이터 모델

표를 이용해서 데이터 상호관계를 정의하는 DB구조

키의 정의

주어진 릴레이션에서 모든 인스턴스 가운데 유일함을 보장해 주는 하나 이상의 속성의 집합

키의 특징

• 유일성 : 하나의 키 값으로 하나의 튜플을 유일하게 식별할 수 있어야 한다.
• 최소성 : 반드시 필요한 최소의 속성으로 구성되어야 한다.

키의 종류

• 기본키 : 후보키 중 선정된 키 중복, Null값을 가질 수 없다.
• 대체키 : 기본키가 아닌 후보키
• 후보키 : 튜플을 유일하게 구분할 수 있는 속성들의 집합
• 슈퍼키 : 한 릴레이션 내의 특정 튜플을 고유하게 식별하는 하나의 속성 또는 속성들의 집합
• 외래키 : 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합
• 인조키 : 기본키로 사용할 속성이 없을 때 인위적으로 만들어 사용하는 속성

데이터 무결성

DB에 저장된 데이터의 정확성, 일관성, 유효성이 유지되는 것

데이터에 적용되는 연산에 제한을 두어 데이터 무결성을 유지한다.

데이터 무결성의 종류

• 도메인 무결성
• 열에 사용할 수 있는 데이터 값의 집합을 지정하고 Null 값을 사용할 수 있는지 여부를 나타낸다.
• 엔티티 무결성
• 테이블의 모든 행에 기본키 값으로 알려진 고유 식별자가 있어야 한다.
• 참조 무결성
• 참조된 테이블의 기본키와 참조하는 테이블의 외래키 간의 관계 항상 유지할 수 있다.

계층 데이터 모델

스키마 다이어그램이 트리 형태인 데이터 모델

데이터의 논리적 구조도가 트리 형태이며, 개체가 트리를 구성하는 노드 역할을 한다.

부모와 자식 개체는 1:N 대응 관계만 존재하며 두 개체 사이에는 하나의 관계만 정의할 수 있다.

네트워크 데이터 모델

데이터베이스의 논리적 구조가 네트워크 그래프 방식

개체 간에는 1:N 관계만 허용된다.

데이터베이스 정규화

정규화 개념

관계형 데이터베이스 설계 시 중복을 최소화하도록 데이터를 구조화하는 작업

정규화의 목적

• 불필요한 데이터를 제거, 데이터의 중복을 최소화
• 데이터베이스 구조 확장 시 재디자인을 최소화
• 다양한 관점에서의 Query를 지원
• 무결성 제약조건의 시행을 간단하게 한다.
• 각종 이상현상을 방지

정규화의 필요성

• 자료의 저장 공간을 최소화
• 자료의 불일치 최소화
• 자료의 구조 안정화
• 자료의 조작 시 이상현상 방지

정규화 대상

CRUD가 많이 일어나는 데이터베이스는 정규화되는 것이 좋다.

이상현상의 개념

이상현상이란 튜플 삽입시 NULL 값 입력, 삭제 시 연쇄 삭제, 갱신 시 일관성 훼손 등의 현상을 말한다.

이상현상은 정규화를 통해 방지할 수 있다.

이상현상의 종류

• 갱신 이상 : 반복된 데이터 일부 갱신 시 데이터 불일치 발생
• 삽입 이상 : 불필요한 정보를 함께 저장하지 않고서는 정보 저장이 불가능
• 삭제 이상 : 필요한 정보를 삭제하지 않고서는 정보 삭제가 불가능

논리 데이터 모델 품질 검증

좋은 데이터 모델의 요건

• 완전성 : 업무에서 필요로 하는 모든 데이터가 데이터 모델에 정의되어 있어야 한다.
• 중복 배제 : 하나의 DB내에 동일한 사실은 한 번만 기록해야 한다.
• 비즈니스 룰 : 데이터 모델링 과정에서 도출, 규명되는 업무규칙들을 데이터 모델에 표현하고 이를 데이터 모델 사용자에게 공유한다.
• 데이터 재사용 : 데이터의 통합성, 독립성에 대한 고려 필요
• 안정성, 확장성 : 데이터 관점의 통합 필요
• 간결성 : 잘 정돈된 방법으로 데이터를 통합하여 데이터의 집합을 정의하고 이를 데이터 모델로 표현
• 의사소통 : 데이터 분석 과정에서 도출되는 업무규칙을 데이터 모델에 최대한 자세하게 표현해야 한다.
• 통합성 : 동일한 성격의 데이터는 조직의 전체에서 한 번만 정의되고 이를 여러 다른 영역에서 참조, 활용한다.

데이터 모델 품질 검증 기준

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

물리 데이터베이스 설계

물리요소 조사 분석

스토리지

스토리지 시스템의 정의

대용량 데이터를 저장하기 위하여 구성된 시스템

스토리지 시스템의 구성

DAS

서버 안에 배치하거나 서버에 직접 연결된 가장 단순한 유형의 데이터 스토리지 하위 시스템

해당 서버에 로그인한 사용자만 DAS 하위 시스템의 데이터에 액세스 할 수 있다.

DAS 구조

DAS의 장단점

장점

• 구성비용이 저렴하고 설치가 빠르며 운영이 쉽다.
• 데이터가 크지 않고 공유가 불필요한 환경에 적합하다.

단점

• 서버가 다운되는 경우 해당 저장장치도 사용할 수 없다.
• 지속적인 저장장치 추가는 서버 성능을 저하시킬 수 있다.

NAS

서버와 저장장치를 네트워크로 연결하는 방식

모든 클라이언트가 LAN을 통해 동일한 스토리지에 액세스할 수 있다.

NAS 구조

NAS의 장단점

장점

• 네트워크에 직접 설치하여 관리하기 떄문에 조작 및 관리가 간단하다.
• NAS 장비에 추가 용량 확장이 용이하다.
• 별도의 OS를 가진 서버 한 곳에서 파일을 관리하기 때문에 서버 간 스토리지, 파일 공유가 용이하다.

단점

• 네트워크를 통한 대용량의 데이터 전송에는 상대적으로 불리하다.
• 대용량의 데이터 전송으로 인한 네트워크 정체가 야기될 수 있다.
• 네트워크 정체를 대비하여 LAN의 충분한 대역폭을 확보해야 한다.

SAN

서버와 공유 스토리지 장치 또는 여러 장치간에 구축된 스토리지 트래픽을 위한 전용 네트워크

SAN의 구조

SAN의 장단점

장점

• 저장 장치 연결로 스토리지 공유가 가능하다.
• 스토리지 시스템 중 대용량 환경의 높은 트랜잭션 처리에 가장 효과적이다.

단점

• 기존 시스템 장비에 대한 제약이 있고 SAN 환경 구축에 비용이 비싸다.
• 파이버 채널 스위치 장비를 사용해야 한다.

스토리지 구성 방식 비교

분산 데이터베이스

분산 데이터베이스의 정의

여러 곳으로 분산되어 있는 데이터베이스를 하나의 가상시스템으로 사용할 수 있도록 한 데이터베이스

분산 데이터베이스의 목표

분산 데이터베이스가 되기 위해서는 투명성을 만족해야 한다.

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

• 분할 투명성 : 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 곳에 저장된다.
• 위치 투명성 : 사용하려는 데이터의 저장 장소 명시가 불필요하다.
• 지역사상 투명성 : 지역 DBMS와 물리적 DB 사이의 매핑이 보장된다.
• 중복 투명성 : DB 객체가 여러 곳에 중복되어 있는지 알 필요가 없는 성질
• 장애 투명성 : 구성 요소의 장애에 무관하게 트랜잭션의 원자성이 유지된다.
• 병행 투명성 : 다수 트랜잭션 동시 수행 시 결과의 일관성을 유지한다.

분산 데이터베이스의 장단점

장점

• 지역 자치성, 점증적 시스템 용량 확장
• 신뢰성과 가용성
• 효용성과 융통성
• 빠른 응답 속도와 통신 비용 절감
• 데이터의 가용성과 신뢰성 증가
• 시스템 규모의 적절한 조절
• 각 지역 사용자의 요구 수용 증대

단점

• 소프트웨어 개발 비용
• 오류의 잠정적 증대
• 처리 비용의 증대
• 설계, 관리의 복잡성과 비용
• 불규칙한 응담 속도
• 통제의 어려움
• 데이터 무결성에 대한 위협

데이터베이스 분산 구성의 가치

분산 데이터베이스는 통합된 데이터베이스에서 제공할 수 없는 높은 데이터 처리 성능을 제공한다.

분산 데이터베이스의 적용 기법

테이블 위치 분산

각각의 테이블을 쪼개어 분산하는 방법

• 수평 분할
• 
• 수직 분할
• 

테이블 복제 분산

동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형

• 부분 복제
• 
• 광역 복제
• 

테이블 요약 분산

지역, 서버 간에 대이터가 비슷하지만 서로 다른 유형으로 존재하는 경우 사용되는 요약 기법에는 분석 요약과 통합 요약 방식이 있다.

• 분석 요약
• 
• 통합 요약
• 

데이터베이스 이중화 구성

이중화의 정의

장애 발생 시 데이터베이스를 보호하기 위한 방법으로 동일한 데이터베이스를 중복시켜 동신에 갱신하여 관리하는 방법

이중화의 목적

DB의 무정지 서비스를 가능하게 하고, 부하 분산을 통한 성능 개선, 장애/재해 시 데이터 손실을 최소화 한다.

이중화의 형태

• Active-Active 형태
• 클러스터를 구성하는 컴포넌트를 동시에 가동하는 형태
• Active-Standby 형태
• 클러스터를 구성하는 컴포넌트 중 실제 가동하는 것은 Active, 남은 것은 Standby 하는 형태

이중화의 활용

장애

장비에 장애서버 엔진을 구축하여 한 대의 장비가 다운되었을 경우 준비된 장비를 서비스에 투입함으로써 짧은 시간 내에 서비스 복구가 가능하다.

웹서비스의 배포

두대의 서버가 LB에 의해 Active-Active 형태로 운영되는 경우 서비스의 다운타임 없이 온라인 배포가 가능하다.

장비의 점검

불가피한 시스템 다운 시 이중화를 이용하면 서비스의 다운타임 없이 점검이 가능하다.

데이터베이스 암호화

데이터베이스 암호화의 정의

DB 암호화 방식

디스크 전체 암호화 방식

인가된 DB 호스트 서버에 접속하는 다양한 시스템, 사용자들을 식별하지 못하므로 이를 위해 컬럼 레벨 암호화가 요구된다.

컬럼 레벨 암호화 방식

개인정보, 민감정보를 담고 있는 테이블의 컬럼만을 암호화하는 방법

컬럼 암호화의 종류

• Plug-In 방식 : 서버 내에 플러그인을 장착하여 암복호화를 수행하는 방식
• API 호출 방식 : 애플리케이션 서버가 암복호화를 위한 API를 호출하여 수행하는 방식

접근 제어

DB 접근 제어의 정의

DB 접근 제어는 데이터베이스에 저장된 데이터에 대해 사용자별 접근 권한에 따라 접근을 제한하기 위한 기술

DB 접근 제어 구조

콘솔 : DB 접근 제어 및 감사를 위한 보안정책을 설정, 관리

서버 엔진 : 실제 접근을 차단하고 감사를 위한 모니터링, 로깅을 수행

DB 접근 제어의 구성 방법

게이트웨이 방식

DBMS에 접속하기 위한 통로를 별도로 설치한 후 DB 사용자가 해당 통로를 통해서만 접근하도록 하는 방식

• 프록시 게이트웨이
• 
• 인라인 게이트웨이
• 

스니핑 방식

DB사용자와 DBMS 서버 간에 주고받는 패킷을 복사하여 DB 접근 제어 서버에 전달하는 방식

TAP 기반 스니퍼 구성

에이전트 방식

DB 서버에 접근 제어를 설치하는 방식

하이브리드 방식

대용량 트랜잭션이 발생하여도 모든 감사로그와 내부 사용자에 대한 통제를 구현할 수 있는 방식

구현 방식별 비교

데이터베이스 물리 속성 설계

파티셔닝

파티셔닝 정의

큰 테이블이나 인덱스를 파티션이라는 작은 단위로 물리적으로 분할하는 것

파티셔닝의 장단점

파티셔닝 범위

범위 파티셔닝

연속적인 숫자, 날짜 기준으로 파티셔닝

리스트 파티셔닝

특정 파티션에 저장될 데이터 명시적 지정

해시 파티셔닝

파티션 키와 해시값에 의한 파티셔닝

특정 데이터가 어느 해시 파티션에 있는지 판단 불가

복합 파티셔닝

여러 파티션들을 복합적으로 사용하는 것

파티셔닝 방법

수평 파티셔닝

데이터 row 기준 파티셔닝

수직 파티셔닝

데이터 column 기준 파티셔닝

파티셔닝 유의사항

파티션 키의 구성 방법에 따라 비효율을 높이는 결과를 초래할 수 있다.

파티션 키의 결정

액세스 유형에 따라 파티셔닝이 이루어지는 것을 고려하여 선정

이력에 대한 데이터의 파티셔닝의 경우 파티션 생성 및 소멸 주기를 일치시켜야 한다.

클러스터링

클러스터 정의

정해진 컬럼값을 기준으로 동일한 값을 가진 하나 이상의 테이블 로우를 같은 장소에 저장하는 물리적 기법

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장

클러스터의 장점

• 디스크 I/O 감소
• 조인 처리시간 단속
• 저장 영역 사용 감소

테이블 클러스터링의 특징

클러스터는 데이터 조회 성능을 향상시키지만 데이터 저장, 수정, 삭제, 테이블 전체 Scan의 성능을 감소시킨다.

• 클러스터링 하기 좋은 테이블
  • 컬럼 안에 많은 중복 데이터를 가지는 테이블
  • 조회가 자주 발생하고 수정이 거의 발생하지 않는 테이블
  • 자주 JOIN되는 테이블
• 클러스터 키가 되기 좋은 컬럼
  • 테이블 간의 조인에 사용되는 컬럼
  • 데이터 값의 범위가 큰 컬럼
• 클러스터 키가 되기 나쁜 컬럼
  • 특정 데이터 값이 적은 컬럼
  • 자주 데이터 수정이 발생하는 컬럼

단일 테이블 클러스터링

넓은 범위의 데이터를 동시에 엑세스하고자 할 때 주로 사용

하나의 클러스터에 하나의 테이블만 생성

클러스터 인덱스를 경유하여 여러 건의 테이블 로우를 한 번의 스캔을 통하여 액세스하므로 랜덤 접근 건수가 줄어든다.

다중 테이블 클러스터링

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

같은 클러스터 키 칼럼 값을 가진 각 테이블의 로우는 정해진 장소에 같이 저장되므로 테이블 조인 속도를 향상시키려고 할 때 주로 사용

데이터베이스 백업

데이터베이스 백업의 정의

중요한 데이터를 보호하기 위해 다른 저장매체를 활용해 저장했다가, 데이터 손실시 원래 데이터를 복구

백업의 종류

백업 대산에 따른 분류

• 시스템 백업 : 운영체제와 같은 시스템 백업
• 데이터 백업 : 데이터베이스 백업, 파일 시스템 백업

구성 방식에 따른 분류

• 직접 연결 백업 : 서버와 백업장비를 1:1 관계로 직접 연결
• 네트워크 백업 : LAN을 통해 백업 장비와 서버를 접속
• SAN 백업 : 백업용 테이프 장치를 SAN을 통해 인식시켜 FC를 통해 백업 장치로 직접 백업받는 방식

백업의 방식에 따른 분류

• 핫 백업 : DB 서버를 온라인 상태로 유지한 채 백업
• 콜드 백업 : DB 서버를 중지한 후 데이터 백업
• 물리 백업 : 파일 자체를 그대로 백업
• 논리 백업 : 각 오브젝트를 SQL문 등으로 저장

DB 백업 시 대상 파일

• 데이터 파일 : 데이터를 저장
• 제어 파일 : DB의 구조나 정보의 상태를 저장
• Redo 로그 파일 : 데이터 변경 처리사항을 저장한 파일

백업 방법

• Data 디렉터리 전체 백업 : 파일
• 오픈소스, 외부 백업 도구 사용
• MySQLhotcopy 사용
• MySQLdump 사용

테이블 저장 사이징

용량 설계

목적

• 정확한 데이터 용량을 예측하여 저장 공간의 효과적 사용과 저장 공간에 대한 확장성을 보장하여 가용성을 높이기 위함
• HW 특성을 고려하여 디스크 채널 병목을 최소화하기 위함
• 디스크 IO를 분산하여 접근성능을 향상하기 위함
• 테이블, 인덱스에 맞는 저장 옵션을 지정하기 위함

테이블 저장 옵션에 대한 고려사항

• 초기 사이즈, 증가 사이즈
• 트랜잭션 관련 옵션
• 최대 사이즈와 자동 증가

저장 용량 설계 절차

1. 용량 분석
2. 오브젝트별 용량 산정
3. 테이블스페이스별 용량 산정
4. 디스크 용량 산정

테이블 크기 산정의 사전 정보

• Row의 Data 길이
• 항목별 크기 산정

테이블 크기 산정 방법

한 Row 저장에 필요한 공간

한 Row 저장에 필요한 공간 = Row Directory + Row Header + Row의 데이터 길이 + (1 x <250바이트 이하 컬럼 개수>)
 + (3 x <250바이트 초과 컬럼 개수>)

한 블록의 데이터 공간

한 블록의 데이터 공간 = (Block Size - Block Header - ITL 공간) x (100 - PCTFREE) / 100

한 블록에 들어갈 수 있는 Row의 개수

한 블록에 들어갈 수 있는 Row의 개수 = Data Space per Block / Row Space(한 블록의 데이터 공간 / 한 Row의 저장에 필요한 공간)

총 데이터 Row 개수

총 데이터 Row 개수 = 초기 예상 건수 + <월중 예상 건수> x (<데이터 보관 월수> + 1)

필요한 총 데이터 공간

필요한 총 데이터 공간 = Block Size x Total Row Size / Row Size per Block

데이터 지역화

물리적 데이터베이스 설계

• 논리적 설계의 데이터 구조를 물리적 데이터 모델로 매핑
• 한 파일에 다수의 릴레이션 저장 가능
• 저장 구조와 접근 방법 점검
• 인덱스 구조 사용 검토
• DB 질의와 트랜잭션 분석

DISK 상에서 파일의 레코드 배치

릴레이션의 속성은 고정, 가변의 필드로 표현된다.

연관된 필드들이 모여 고정, 가변 길이의 레코드가 된다.

레코드들의 모임이 파일이라고 부르는 블록들의 모임에 저장된다.

인접한 블록들을 읽을 경우 입출력 속도가 빠르다.
따라서 가능한 한 블록들이 인접하여 한 파일으로 구성되도록 재조직한다.

BLOB 타입

이미지, 동영상 등 대규모 크기의 데이터를 저장하는데 사용된다.

채우기 인수

각 블록에 레코드를 채우는 공간의 비율

고정길이 레코드

k번째 레코드에 접근하기 위해서 (레코드 길이) x (k - 1) + 1의 위치에서 레코드를 읽는다.

레코드 삭제로 다수의 레코드가 이동할 때는 (삭제한 레코드 개수) x (레코드 길이) Bytes 만큼 이동한다.

한 개의 레코드를 삭제하면 고정 길이 바이트만큼 이동한다.

파일 내 클러스터링

한 파일 내에서 함께 검색될 가능성이 높은 레코드들을 물리적으로 가까운 곳에 모아두는 것

파일 조직의 유형

히프 파일

가장 단순한 파일 조직

삽입된 순서대로 파일에 저장

원하는 레코드를 찾기 위해 모든 레코드를 순차적으로 접근

삭제 후 빈 공간은 재사용하지 않는다.

좋은 성능을 유지하기 위해서는 히프 파일을 주기적으로 재조직할 필요가 있다.

파일 재조직 시에는 빈 공간들을 회수해 자유 공간에 반환한다.

물리 데이터베이스 모델링

데이터베이스 무결성

데이터베이스 무결성 정의

데이터베이스에 저장된 데이터 값과 그것이 표시하는 현실 세계의 실제 값이 일치하는 정확성

무결성의 종류

• 개체 무결성 : 기본키는 값의 필수적 요소
• 참조 무결성 : 외래키 속성은 참조할 수 없는 값을 지닐 수 없다.
• 속성 무결성 : 컬럼은 지정된 데이터 형식을 반드시 만족하는 값만 포함한다.
• 키 무결성 : 한 릴레이션에 같은 키 값을 지닌 튜플들은 허용되지 않는다.
• 사용자 정의 무결성 : 모든 데이터는 업무 규칙을 준수해야 한다.
• 도메인 무결성 : 특정 속성 값이 미리 정의된 도메인 범위에 속해야 한다.

데이터베이스 무결성 보장 방법

데이터베이스 무결성 보장 방법

• 응용 프로그램 : 데이터 조작 프로그램 내에 무결성 조건을 검증하는 코드 추가
• 트리거 : 트리거 이벤트 시 프로시저를 실행하는 무결성 조건 실행
• 제약 조건 : 데이터베이스 제약조건 기능을 선언하여 무결성 유지

데이터베이스 무결성 보장 방법 간의 장단점 비교

칼럼 속성

칼럼 속성의 정의

정보를 나타내는 최소 단위

엔티티의 성질, 분류, 수량, 상태, 특성 등을 나타내는 세부 항목

엔티티 특징

• 업무에 필요한 정보이다.
• 의미 있는 식별자에 의해 인스턴스는 1개씩만 존재한다.
• 2개 이상의 인스턴스 집합으로 구성된다.
• 업무 프로세스에 의해 이용되어야 한다.
• 속성을 포함해야 한다.
• 관계가 존재해야 한다.

속성의 유형

특성에 따른 분류

• 기본 속성 : 해당 엔티티가 원래 가지고 있는 속성
• 설계 속성 : 시스템의 효율성을 위해 임의로 추가되는 속성
• 파생 속성 : 규칙화를 위해 변형하거나 새로 정의한 값

속성의 특징

• 업무에 필요한 정보
• 주 식별자에 함수적 종속
• 한 개의 속성값만 가진다.

엔티티, 인스턴스, 속성, 속성값의 관계

• 한 개의 엔티티는 두 개 이상의 인스턴스의 집합이다.
• 한 개의 엔티티는 두 개 이상의 속성을 가진다.

속성 정의시 고려사항

엔티티가 관리할 특성들인가?

의미적으로 독립적인 최소 단위인가?

하나의 값만들 가지고 있는가?

원본인가 파생된 값인가?

용어사전 정의

논리명과 물리명을 정의하는 것

엔티티의 속성명을 모두 한 곳에 모아 기술

속성명을 업무에서 사용하는 단어의 단위로 분리

각각의 단위 속성에 의미를 기술하고 물리 속성명을 업무 특성에 적합하게 정의

물리 속성명 명명규칙 정의

반 정규화

반정규화 절차

반정규화 대상 조사

• 범위 처리 빈도수 조사
• 대량 범위 처리 조사
• 통계성 프로세스 조사

다른 방법 유도 검토

• 뷰 테이블
• 클러스터링 또는 인덱스 적용
• 애플리케이션 로직 변경

반정규화 적용

• 테이블 반정규화
• 속성의 반정규화
• 관계의 반정규화

반정규화 방법

테이블 반정규화

테이블 병합

• 1 : 1 관계의 테이블 병합
• 1 : N 관계의 테이블 병합
• 슈퍼타입 / 서브타입 테이블 병합

테이블 분할

• 수직 분할
• 수평 분할

테이블 추가

• 중복 테이블 추가
• 통계 테이블 추가
• 이력 테이블 추가
• 부분 테이블 추가

컬럼 반정규화

• 중복 컬럼 추가
• 파생 컬럼 추가
• 이력 테이블 컬럼 추가
• 기본키에 의한 컬럼 추가
• 응용 시스템 오동작을 위한 컬럼 추가

관계 반정규화

데이터데이스 반정규화

정규화

정규화의 원칙

• 무손실 표현 : 같은 의미의 정보를 유지하면서 더 바람직한 구조로 변경
• 자료의 중복성 감소 : 중복되는 정보는 삭제/통합
• 분리의 원칙 : 독립된 관계는 별개의 릴레이션으로 표현

제1정규형

릴레이션 R의 모든 속성값이 원자값을 가진다.

예시

제2정규형

릴레이션 R이 제1정규형이고 기본키가 아닌 속성이 기본키에 완전 함수 종속

예시

제3정규형

릴레이션 R이 제2정규형이고 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때

예시

BCNF정규형

3차 정규형을 만족하면서 모든 결정자가 후보키 집합에 속한 정규형

예시

제4정규형

릴레이션 R에서 다치종속 A B가 존재할 때 R의 모든 애트리뷰트들이 A에 함수종속

예시

제5정규형

릴레이션 R에 존재하는 모든 조인종속이 릴레이션 R의 후보키를 통해서만 성립

예시

물리 데이터 모델 품질 검토

물리 데이터 모델 품질 기준

물리 데이터 모델 품질 기준

물리 E-R 다이어그램

논리 데이터 모델을 물리 데이터 모델로 변환

구성 요소의 변환 관계

서브타입 변환

슈퍼타입 기준 엔티티 통합

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

서브타입 기준 엔티티 통합

슈퍼타입 속성들을 각각의 서브타입에 추가하여 서브타입들을 개별적 테이블로 제작

개별타입 기준 엔티티 변환

슈퍼타입과 서브타입들을 각각의 개별적 테이블로 변환

속성, 칼럼 변환

1. 일반 속성 변환
2. Primary UID 기본키 변환
3. 관계의 UID Bar 기본키 변환
4. Secondary UID 유니크 키 변환

관계 변환

• 1 : M 변환
• 
• 1 : 1 변환
• 
• 1 : M 순환 관계 변환
• 
• 배타적 관계 변환
• 

관리 목적의 칼럼 추가

데이터 타입 선택

데이터 표준 적용

CRUD 분석

CRUD 분석의 정의

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

CRUD 분석의 절차

1. 모든 엔티티 목록을 나열하고 각각의 프로세스가 해당 엔티티에 대하여 CRUD하는가에 대한 여부 표기
2. 
3. CRUD 매트릭스 작성 후 다음 항목 점검
   • 모든 엔티티 타입에 CRUD가 한 번 이상 표기되었는가?
   • 모든 엔티티 타입에 C가 한 번 이상 존재하는가?
   • 모든 엔티티 타입에 R이 한 번 이상 존재하는가?
   • 모든 단위 프로세스가 하나 이상의 엔티티 타입에 표기 되었는가?
   • 두 개 이상의 단위 프로세스가 하나의 엔티티 타입을 생성하는가?

SQL 성능 튜닝

SQL 성능 튜닝의 정의

최소한의 CPU, I/O, 메모리를 사용하여 최대한 빠른 시간 내에 원하는 작업을 수행하도록 만드는 것

SQL이 가진 정보를 분석하여 성능을 개선하는 활동

SQL 튜닝을 해야 하는 이유

대분의 개발 작업은 개발환경에서 이루어진다.

개발 환경은 데이터 구성이나 데이터 양이 운영 환경과 차이가 있다.

개발 환경에서 작성된 SQL을 운영 환경에서 실행했을 때, 수행 속도가 과도하게 느린 경우도 있다.

SQL 튜닝 시기

1. 분석 및 설계 단계 : 정규화, 반정규화
2. 개발 및 구현 단계 : 병목현상 모니터링, 최적화
3. 운영 단계 : 예상치 못한 데이터 및 사용량 증가에 따른 성능 저하 및 최적화

SQL 성능 튜닝 절차

1. 튜닝 대상 SQL 수집
2. SQL 문제점 분석 및 개선사항 도출
3. 개선사항 적용 및 개선 효과 확인

SQL 튜닝 기법

• WHERE 안에서 연산자의 처리 능력을 숙지하고 WHERE 절에서는 가급적 인덱스 컬럼을 모두 활용= → > → >= → < → <= → LIKE → <>
• WHERE 안에서 연산자의 처리 성능 순서
• OR 보다 AND 사용
• 조건절 컬럼 관련 튜닝 기법
  • 조건절 컬럼에 함수 사용 자제
  • 조건절에 NOT 사용 자제
  • 조건절에서 범위를 줄일 때 BETWEEN 사용
• IN, EXIST 다음에 오는 SELECT문의 결과가 많을수록 EXIST문이 좋다.
• FROM 절에 나타나는 테이블 순서는 기준이 되는 테이블일수록 뒤쪽에 위치시키는 것이 좋다.
• JOIN만 한 것이 OUTER JOIN을 한 것보다 속도가 빠르다.
  OUTER JOIN 대신 UNION 사용을 고려한다.
• 인라인 뷰를 사용하기 전에 JOIN을 사용하여 처리 가능한지 확인한다.
• DISTINCT는 가급적 사용하지 않는다.

고급 SQL 튜닝

• 옵티마이저 선택 및 변경
• 힌트 사용
• 부분 범위 처리
• 인덱스 활용
• 조인방식과 조인 순서 조정
• 다중 처리
• 병렬 쿼리
• Dynamic SQL 지양

데이터 전환

데이터 전환 기술

초기 데이터 구축

초기 데이터 구축의 정의

기존의 정보 시스템에 축적된 자료를 새로운 정보 시스템에서 운용 가능하도록 변경, 저장하는 방식

기존에 운영되던 데이터를 목적 시스템으로 안정적으로 전환할 수 있도록 데이터 전환 전략을 이용하여 전환을 수행

데이터 전환 전략

초기 데이터 구축 절차

원천 시스템의 데이터 분석과 추출, 정제 및 변환, 목적 시스템으로의 데이터 적재 및 검증 단계로 구성

데이터 전환 절차

데이터 전환 검증

전환 절차 중 원천 시스템 데이터를 목적 시스템 데이터로 생성하는 과정의 정상 수행 여부를 검증

데이터 전환 검증 분류

초기 데이터 구축시 고려사항

• 원천 시스템과 목표 시스템이 동일한 데이터베이스를 사용하는가 점검
• 목표 시스템의 데이터 모델과 원천 시스템의 데이터 모델의 차이 점검
• 원천 시스템과 목표 시스템의 모델을 잘 알고있는가 점검
• 데이터 전환시 업무 중단을 최소화하고 안정성을 확보할 수 있는 방안을 마련
• 오류 데이터에 대한 정비 방안 마련
• 완료 후 데이터 품질 검증 실시
• 개인정보 대상은 암호화하여 적용

데이터 전환 시간 단축 방법

• 데이터 추출 방식 조정
• 이행 범위 조정
• 전환 시나리오 최적화
• 사전 이행

ETL

ETL의 정의

조직 내외부의 복수 Source들을 Data 분석을 위한 DB내로 이동시키는 프로세스

프로세스 : 추출 → 변형 → 적재

파일 처리 기술

파일 처리 기술의 정의

파일을 저장장치에 저장할 때 기억 공간을 최대한 효율적으로 사용하도록 하고, 필요한 자료의 탐색을 쉽게 하기 위해 파일을 효율적으로 처리하는 기술

파일 구성의 분류

• 순차 파일 (SAM)변동 사항이 크지 않고 기간별 일괄 처리를 주로 하는 경우에 적합순차파일의 장단점
  • 기록 밀도가 높아 기억 공간을 효율적으로 사용할 수 있다.
  • 매체 변환이 쉬워 어떠한 매체에도 적용할 수 있다.
  • 레코드 기록시 다른 편성법보다 속도가 빠르다.
  • 단점
  • 파일에 새로운 레코드를 삽입, 삭제하는 경우 파일 전체를 복사해야 하므로 시간이 많이 소요된다.
  • 데이터 검색 시 처음부터 순차적으로 검색하기 때문에 검색 효율이 낮다.
• 장점
• 주로 자기테이프에서 사용
• 입력되는 데이터들을 논리적 순서에 따라 물리적 연속 공간에 순차적으로 기록하는 방식
• 색인 순차 파일 (ISAM)일반적으로 자기디스크에 활용, 자기테이프에는 사용할 수 없다.
  • 기본 구역 : 실제 레코드들을 기록하는 부분
  • 색인 구역 : 색인이 기록되는 부분
  • 오버플로 구역 : 기본 구역에 빈 공간이 없어서 새로운 레코드의 삽입이 불가능할 경우를 대비해 예비적으로 확보해 둔 부분장점
  • 색인 순차 파일의 장단점
  • 순차 처리와 랜덤 처리가 모두 가능하다.
  • 효율적 검색이 가능하고 레코드 삽입, 삭제, 갱신이 용이하다.
  • 단점
  • 색인구역, 오버플로 구역을 위한 추가 기억 공간이 필요하다.
  • 파일이 정되어 있어야 하므로 삭제시 효율이 떨어진다.
  • 색인을 이용한 액세스를 하기 때문에 액세스 시간이 랜덤 편성 파일보다 느리다.
• 색인 순차 파일 구성
• 순차 처리와 랜덤 처리가 모두 가능하도록 레코드들을 키 값 순으로 정렬시켜 기록하고, 레코드의 키 항목만을 모은 색인을 구성하여 편성하는 방식
• 직접 파일 (DAM)대화형 처리 가능레코드 주소의 변환과정의 시간 소요직접 파일의 장단점
  • 접근 시간이 빠르다.
  • 레코드의 추가, 삭제의 경우 파일 전체의 복사가 필요하다.
  • 단점
  • 데이터 레코드와 그 보관장소의 대응이 어렵다.
  • 기억 공간 효율이 저하되고, 연속적, 전체적 검색이 불가능하다.
• 장점
• 기억 공간 효율 저하
• 순서에 관계없이 저장
• 해싱 함수를 계산해 물리적 주소를 직접 접근
• 가상 기억 접근 방식 (VSAM)기본 구역과 오버플로 구역을 구분할 수 없다.
• 레코드를 삭제하면 그 공간을 재사용할 수 있다.
• 동적 인덱스 방법을 이용한 색인 순차 파일
• 역파일

데이터 전환 수행

데이터 전환 수행 계획

데이터 전환 방법론 이해

데이터 전환 방법론

데이터 전환 수행 절차

데이터 전환 수행 절차

데이터 전환 범위

전환 대상의 상세 내용, 데이터 형식, 대상 건수 파악

데이터 전환 일정

팀별 업무를 분장하여 팀별 역할과 납기를 기록

데이터 전환 절차

단계별 작업내용 기록

스테이징 데이터베이스 이용

데이터 오류 확인

스테이징 데이터베이스가 아닌 원천 데이터베이스와 비교하여 데이터 오류 확인

데이터 전환 수행 계획서 작성

체크리스트

체크리스트

데이터 전환 작업자가 수행할 작업 항목, 작업 내용을 꼼꼼히 확인

전환 프로그램의 오류, 업무 프로세스의 변경, 시간의 제약, 데이터 요건 변경, 하드웨어 장애 등에 대응하기 위해 체크리스트를 작성한다.

수행 작업 확인

각 단계별 작업 내용을 가능한 한 상세하게 분할하고 작업자가 작업 시 주의해야 할 특이사항을 기록

체크리스트 작성 및 기록 내용

작업 담당자 할당, 작업 시작 시간, 종료 시간, 전환된 건수를 기록한다.

단계별 수행 작업 내용

1. 사전 준비
   • 운영 환경에 대한 설정 및 점검
   • 전환 환경에 대한 사전 점검
   • DB 상태 점검
2. 데이터 전환
   • 수작업 테이블 이관
   • 데이터 추출, 변환, 적재
   • 인덱스 리빌드, 권한 재설정
3. 데이터 점검
   • 전환 검증 요건 항목 검증
   • 업무계 테이블에 대한 후속 SQL 작업

데이터 검증

데이터 검증

데이터 전환 게획서의 내용과 체크리스트의 작업 내용 확인

데이터 검증을 위하 전환 단계별 결과를 확인하고 전환 실적, 작업별 시작 시간, 종료 시간 기록

검증 종류, 내용 확인

데이터 검증 종류

• 로그 검증
• 전환시 수행되는 추출, 적재 로그를 작성하여 검증
• 기본 항목 검증
• 로그 검증 외에 응용 파트의 별도 검증 요청 항목을 기준으로 검증 프로그램을 작성하고 파일 또는 데이터베이스를 이용하여 검증
• 응용 프로그램 검증
• 응용 애플리케이션을 통한 검증
• 응용 데이터 검증
• 사전 정의된 업무 규칙을 통하여 데이터 정합성 검증 수행

전환 단계별 전환 결과 검증

• 추출 검증
• 현행 시스템에서 최초 원시 데이터에 대한 검증 수행
• 전송 검증
• 전송된 추출 파일의 코드 전환 후 검증
• 전환 검증
• 데이터를 추출하여 스테이징 DB를 구성한 후 검증
• 적재 검증
• 목적 테이블에 대한 데이터 검증
• 통합 검증
• 매핑 후 검증

데이터 정제

데이터 정제

데이터 정제

원천 데이터와 전환된 목적 데이터베이스 데이터의 품질을 분석하여 정상 데이터와 오류 데이터를 정량적으로 측정하고, 오류의 원인을 파악하는 전 과정

데이터 정제의 일반적 방법

결측치 처리

• 해당 레코드 무시
• 수기 입력
• 전역상수 적용
• 속성 평균값 적용
• 해당 레코드와 같은 클래스에 해당하는 표본값의 평균 적용

잡음 있는 데이터 처리

• 구간화(Binning)
• 단순, 복합 회귀 값 적용
• 군집화 적용

데이터 불일치 문제 처리

데이터 불일치의 원인

• 데이터 입력 설계 오류
• 입력자 오류
• 의도적 오류
• 데이터의 노후화

단계별 데이터 정제 방안

데이터 품질 분석

데이터 품질 관리의 정의

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

데이터 품질 관리의 필요성

원천 데이터 품질 분석

원천 데이터의 품질이 보장되지 않으면 전환 후의 데이터도 정확하지 않으며, 전환 후의 데이터 정합성 검증 시 오류가 발생하면 원인을 찾기 어렵다

따라서 전환 전에 원천 데이터의 품질을 검증함으로써 전환의 정확도를 보장할 수 있으며,
이전에 발견하지 못한 데이터의 오류도 찾아낼 수 있다.

목적 데이터 품질 분석

원천 데이터와 목적 데이터베이스의 속성간 대응 관계는 N : M 대응 관계가 더 많기 때문에 양자 간의 대응 관계를 정확히 표현하기 위해서는 데이터 레이아웃을 정확하게 파악하고 품질 검증을 진행해야 한다.

오류 데이터 측정

오류 관리 목록 작성

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

1. 정상 데이터의 건수를 정량적으로 측정
2. 오류 데이터의 유형과 건수를 정량적으로 측정

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

1. 발견된 데이터 오류를 분석하고 원인을 파악
2. 파악된 원인을 기반으로 원천 데이터와 전환 프로그램 정제 필요 여부를 결정