[정보처리기사] Chapter 02. 데이터베이스 구축: 물리 데이터베이스 설계

1. 사전 조사 분석

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

물리 데이터베이스 설계란 논리적 구조로 표현된 DB를 디스크 등의 물리적 저장장치에 저장할 수 있는 물리 구조의 데이터로 변환하는 과정을 말한다. 물리적 데이터베이스 구조에서 데이터의 기본 단위는 저장 레코드(Stored Record)이며, 물리적 데이터베이스 구조는 여러가지 타입의 저장 레코드 집합이라는 면에서 단순 파일과는 다르다. 물리적 데이터베이스 구조는 DB 시스템의 성능에 중대한 영향을 준다.

때문에 물리적 설계 단계에 꼭 포함되어야 하는 것은 저장 레코드 양식 설계, 레코드 집중의 분석 및 설계, 접근 경로 설계 등이다.

▶ 물리적 설계 시 고려 사항

◍ 인덱스 구조

◍ 레코드 크기

◍ 파일에 존재하는 레코드 수

◍ 파일에 대한 트랜잭션(DB의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 )의 갱신과 참조 성향

◍ 성능 향상을 위한 개념 스키마(DB의 전체적인 논리적 구조로서, 데이터를 종합한 DB로 하나만 존재한다. )의 변경 여부 검토

◍ 잦은 질의와 트랜잭션들의 수행 속도를 높이기 위한 고려, 시스템 운용 시 파일 크기의 변화 가능성 등

▶ 물리적 설계 옵션

◍ 반응시간: 트랜잭션 수행을 요구한 시점부터 처리 결과를 얻을때까지의 경과 시간

◍ 공간 활용도: 데이터베이스 파일과 액세스 경로 구조에 의해 사용되는 저장공간의 양

◍ 트랜잭션 처리량: 단위시간 동안 데이터베이스 시스템에 의해 처리될 수 있는 트랜잭션의 평균 개수

1.2. 데이터 명명 규칙 파악

데이터 명명 규칙은 물리데이터 모델에 적용해야 하는 규칙으로, 조직(회사 혹은 개발팀)마다 다를 수 있으니 물리 데이터 모델 설계 전에 파악해야 한다.

물리 DB 설계와 논리 DB 설계에 적용되는 명명 규칙은 서로 일관성을 유지해야 한다. 데이터 명명 규칙은 논리적 데이터 요소를 물리적 데이터 요소로 전환할 때 동일 몇일 부여의 근거로 사용된다. 이에 데이터 명명 규칙을 파악하면 해당 조직이나 프로그램의 데이터 표준화 및 논리 DB 설계의 결과물 등을 통해 파악할 수 있다.

데이터 명명 규칙을 정확하고 올바르게 진행하면 데이터 베이스의 중복구축 등을 방지할 수 있다. 명명 규칙을 파악하려면 도메인(객체에 포함된 속성들의 데이터 타입, 크기 등을 표준화 규칙에 따라 정의한 것 )과 데이터 사전(프로젝트에서 사용하는 명칭 부여의 근거로 사용된다. )에 대한 지식이 필요하다.

1.3. 시스템 자원 파악

시스템 자원은 DB 설치에 영향을 줄 수 있는 물리적 요소들(HW 자원, OS 및 DBMS 버전, DBMS 파라미터 정보 등)로, 사전에 미리 파악한다.

1) 하드웨어 자원

◍ 중앙처리장치: CPU의 성능과 집중적인 부하 발생시간 등을 파악한다.

◍ 메모리: 시스템 전체의 메모리 규모, 사용 중인 메모리 영역, 사용 가능한 메모리 영역 등 확보된 자원이나 실질적인 시스템 활용 정도 등을 파악한다.

◍ 디스크: 전체 디스크 크기, 확보된 디스크 자원, 디스크 분할 형태, 현재 디스크 활용률, 사용 가능한 디스크 공간 등을 파악한다.

◍ I/O Controller: 현행 시스템의 입출력 컨트롤러의 성능, 운용 적절성 등을 파악한다.

◍ 네트워크: 네트워크 처리의 양과 속도, 집중 부하 발생 시간, 동시 접속 가능 정도 등을 파악한다.

2) 운영체제 및 DBMS 버전

◍ DB 운영에 영향을 줄 수 있는 관련 요소들을 미리 파악하고 적절하게 관리한다.

3) DBMS 파라미터 정보

◍ DBMS 별로 파라미터 차이가 있기 때문에 시스템 별 종류 및 관리 대상을 파악한다.

◍ DBMS의 저장 공간, 메모리 등에 대한 파라미터, 쿼리에서 활용하는 옵티마이저의 사용 방법 등을 파악한다.

1.4. 데이터베이스 관리 요소 파악

데이터베이스 운영과 관련된 관리 요소로, DB 시스템 환경에 따라 달라질 수 있으므로 파악할 필요가 있다.

◍ 데이터베이스 관리 요소를 파악한 후 이를 기반으로 데이터베이스 시스템 조사 분석서를 작성한다.

◍ 시스템 조사 분석서를 기반으로 각종 범위와 특성을 파악한다.

데이터 베이스 구조	DB의 구조에 따라 문제 대응 방법이 다르므로, 서버와 DB 구조를 파악한다.
이중화 구성	문제 발생에 대비하여 동일 DB를 복제, 관리하는 구성을 파악한다.
분산 데이터베이스	분산 DB는 물리적 피해로 인한 데이터 유실을 최소화하고, 장애로 인한 데이터 복구에 효과적이므로 DB 분산 구조를 파악한다.
접근 제어/ 접근 통제	DB는 접근 가능한 사용자의 권한 남용으로 인한 정보 유출 및 변조가 빈번하게 발생하므로, DB의 접근 제어 방법을 파악한다.
DB 암호화	데이터 암호화, 암호 키에 대한 인증 등을 통해 데이터 유출 시 데이터 복호화를 어렵게 하므로 DB 암호화 특성을 파악한다.

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

2.1. 테이블

DB의 가장 기본적인 객체로서, 행(Row)과 열(Column)로 구성되어 있다. 테이블은 논리 설계의 개체(Entity)에 대응하는 객체로, DB의 모든 데이터가 저장된다. DBMS 종류에 따라 테이블의 명칭과 기능은 약간씩 차이를 보인다.

테이블의 종류에는 일반 테이블, 클러스터드 인덱스 테이블, 파티셔닝 테이블, 외부 테이블, 임시테이블 등이 있다.

1) 일반 테이블

현재 사용되는 대부분의 DBMS에서 표준 테이블로 사용되는 형태이다. 테이블에 저장되는 데이터의 로우 위치는 속성 값에 상관 없이 데이터가 저장되는 순서에 따라 결정된다. 즉, 일정한 기준 없이 입력 순서에 따라 저장된다는 것이다.

2) 클러스터드 인덱스 테이블(Clustered Index Table)

기본키나 인덱스키의 순서에 따라 데이터가 저장되는 테이블이다. 일반적인 인덱스를 사용하는 테이블에 비해 접근 경로가 단축된다. 다시말해, 데이터가 기본키 필드를 기준으로 정렬되어 테이블에 저장되는 것이다.

3) 파티셔닝 테이블(Partitioning Table)

대용량의 테이블을 작은 논리적 단위인 파티션으로 나눈 테이블이다. 대용량 데이터를 효과적으로 관리할 수 있지만, 파티션 키를 잘못 구성하면 성능 저하 등 역효과를 초래한다.

파티셔닝 방식에 따라 범위 분할, 해시 분할, 조합 분할 등으로 나뉜다.

범위 분할	지정한 열의 값을 기준으로 분할
해시 분할	해시 함수를 적용한 결과 값에 따라 분할
조합분할	범위 분할로 분할 한 후 해시 함수를 적용하여 다시 분할

4) 외부 테이블(External Table)

DB에서 일반 테이블처럼 이용할 수 있는 외부 파일로, DB 내에 객체로 존재한다. 데이터 웨어하우스(Data Warehouse)에서 ETL* 등의 작업에 유용하게 사용된다.

* 데이터 웨어하우스(Data Warehouse): 조직이나 기업체의 중심이 되는 주요 업무 시스템에서 추출되어 새로 생성된 DB

* ETL(Extraction, Transformation, Loading): 데이터 웨어하우스를 사용하여 추출, 변환, 적재하는 일련의 모든 과정

5) 임시 테이블(Temporary Table)

트랜잭션(DB의 상태를 변환시키는 논리적 기능을 수행하기 위한 작업의 단위 )이나 세션 별로 데이터를 저장하고 처리할 수 있는 테이블이다. 임시 테이블에 저장된 데이터는 트랜잭션이 종료되면 삭제된다. 절차적인 처리를 위해 임시로 사용하는 테이블이다.

2.2. 컬럼(Column)

테이블의 열을 구성하는 요소로, 데이터 타입과 길이 등으로 정의된다. 데이터 타입은 데이터의 일관성 유지를 위해 사용되는 것으로, 도메인을 정의한 경우 도메인에 따라 데이터 타입과 길이가 결정된다.

두 컬럼을 비교하는 연산에서 두 컬럼의 데이터 타입이나 길이가 다르면, DBMS 내부적으로 데이터 타입을 변환 후 비교 연산을 수행한다. 참조 관계인 컬럼들은 데이터 타입과 길이가 일치해야 한다.

1) 데이터 타입과 길이 지정 시 고려사항

◍ 가변 길이 데이터 타입: 예상되는 최대 길이로 정의

◍ 고정 길이 데이터 타입: 최소 길이로 지정

◍ 소수점 이하 자릿수: 반올림 되어 저장

2) 데이터 타입에 따른 컬럼의 물리적 순서

◍ 앞: 고정 길이 컬럼이고 NOT NULL인 컬럼

◍ 뒤: 가변 길이 컬럼, NULL 값이 많을 것으로 예상되는 컬럼

2.3. 테이블스페이스(Tablespace)

테이블스페이스는 테이블이 저장되는 논리적인 영역으로, 하나의 테이블 스페이스에 하나 또는 그 이상의 테이블을 저장할 수 있다. 테이블을 저장하면 논리적으로는 테이블 스페이스에 저장되고, 물리적으로는 해당 테이블 스페이스와 연관된 데이터 파일에 저장된다.

DB를 테이블, 테이블 스페이스, 데이터 파일로 나눠 관리하면 논리적 구성이 물리적 구성에 종속되지 않아 투명성(사실의 존재 여부를 염두에 두지 않아도 되는 성질 )이 보장된다. 테이블 스페이스는 DB에 저장되는 내용에 따라 테이블, 인덱스, 임시 등의 용도로 구분하여 설계한다.

▶ 테이블 스페이스 설계 시 고려사항

◍ 업무 별로 구분하여 지정한다.

◍ 대용량 테이블은 하나의 테이블 스페이스에 독립적으로 저장한다.

◍ 테이블과 인덱스는 분리하여 저장한다.

◍ LOB(Large Object) 타입의 데이터는 독립적인 공간으로 지정한다

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

3.1. 트랜잭션(Transaction) 정의

트랜잭션은 DB의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위를 말한다. 한번에 모두 수행되어야 할 일련의 연산들이며, DB 시스템에서 ‘병행 제어’ 및 ‘회복 작업’ 시 처리되는 작업의 ‘논리적 단위’로 사용된다.

다시말해, 트랜잭션은 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위를 말한다.

3.2. 트랜잭션 특징

데이터 무결성을 보장하기 위해 DBMS의 트랜잭션이 가져야 할 특성이다.

원자성

(Atomicity)

트랜잭션 연산은 DB에 모두 반영되도록 완료(Commit)되거나 아니면 전혀 반영되지 않도록 복구(Rollback) 되어야 한다. 트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 어느 하나라도 오류가 발생하면 전부 취소되어야 한다.

일관성

(Consistency)

트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 DB 상태로 변환한다. 시스템이 가지고 있는 고정 요소는 트랜잭션 수행 전과 완료 후의 상태로 같아야 한다.

독립성, 고립성

(Isolation)

둘 이상의 트랜잭션이 동시에 병행 실행되는 경우, 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션 연산이 끼어들 수 없다.

수행 중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다

영속성, 지속성

(Durability)

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

3.3. CRUD(Create Read Update Delete) 분석

CRUD는 Create + Read + Update + Delete의 줄임말로 DB 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석하는 것이다. CRUD 분석으로 테이블에 발생되는 트랜잭션의 주기별 발생 횟수를 파악하고 연관된 테이블들을 분석하면, 테이블에 저장되는 데이터 양을 유추할 수 있다.

CRUD 분석을 통해 많은 트랜잭션이 몰리는 테이블을 파악할 수 있으므로 디스크 구성 시 유용한 자료로 활용할 수 있다. 또한, 외부 프로세스 트랜잭션의 부하가 집중되는 DB 채널을 파악하고 분산시킴으로써 연결 지연이나 타임아웃 오류를 방지할 수 있다.

3.4. CRUD 매트릭스

CRUD 매트릭스는 2차원 형태의 표로서, 행(row)에는 프로세스를, 열(column)에는 테이블을, 행과 열이 만나는 위치에는 프로세스가 테이블에 발생시키는 변화를 표시하는 업무 프로세스와 데이터 간 상관 분석표이다.

CRUD 매트릭스를 통해 프로세스의 트랜잭션이 테이블에 수행하는 작업을 검증한다. 표의 각 셀에는 C/R/U/D의 앞 글자를 적고, 복수의 변화가 있으면 C>D>U>R의 우선순위를 적용하여 작성한다. C/R/U/D 중 어느 것도 적히지 않은 행/열, C/R이 없는 행을 확인하여 불필요한 테이블이나 누락된 테이블 또는 프로세스를 찾는다.

3.5. 트랜잭션 분석

트랜잭션 분석은 CRUD 매트릭스를 기반으로 테이블에 발생하는 트랜잭션 양을 분석하는 것이 목적이다. 즉, 테이블에 저장되는 데이터 양을 유추하여 이를 근거로 DB 용량을 산정하고, 구조를 최적화하는 것이다.

업무 개발 담당자가 수행한다.

이를 통해 프로세스가 과도하게 접근하는 테이블을 확인하여 여러 디스크에 배치함으로써 디스크 입출력 분산을 통한 성능 향상을 도모한다.

3.6. 트랜잭션 분석서

단위 프로세스와 CRUD 매트릭스를 이용하여 작성하고, 아래 6개의 구성 요소가 있다.

◍ 단위 프로세스: 업무를 발생시키는 가장 작은 단위의 프로세스

◍ CRUD 연산: 프로세스의 트랜잭션이 DB 테이블에 영향을 주는 연산

◍ 테이블 명, 컬럼 명: 프로세스가 접근하는 DB의 테이블 명과 컬럼 명

◍ 테이블 참조 횟수: 프로세스가 테이블을 참조하는 횟수

◍ 트랜잭션 수: 주기별로 수행되는 트랜잭션 횟수

◍ 발생 주기: 연, 분기, 월, 일, 시 등 트랜잭션 횟수를 측정하기 위한 발생 주기

4. 인덱스 (index)

인덱스 (index)는 데이터 레코드를 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조를 말한다. 데이터가 저장된 물리적 구조와 밀접한 관계가 있으며, 레코드가 저장된 물리적 구조에 접근하는 방법을 제공한다. 인덱스를 통해 파일의 레코드에 대한 액세스를 빠르게 수행할 수 있다.

레코드 삽입/삭제가 수시로 일어나는 경우, 인덱스 개수를 최소화 하는 것이 효율적이다. 인덱스가 없으면 특정 값을 찾기 위해 테이블 스캔(TABLE SCAN: 테이블에 있는 모든 레코드를 순차적으로 읽는 것 )이 발생한다.

기본키를 위한 인덱스를 기본 인덱스, 기본 인덱스가 아닌 인덱스들을 보조 인덱스라 한다. 대부분의 DBMS에서는 모든 키에 대해 자동적으로 기본 인덱스를 생성한다. 인덱스의 종류 는 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 도메인 인덱스 등이 있다.

▶ 클러스터드 인덱스(Clustered index)

◍ 레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게 유지되도록 구성되는 인덱스

◍ 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식이다.

◍ 한 개의 릴레이션에 하나의 인덱스만 생성할 수 있다.

▶ 넌클러스터드 인덱스(Non-Clustered index)

◍ 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식

◍ 한 개의 릴레이션에 여러 개의 인덱스를 만들 수 있다.

4.1. 인덱스의 종류

1) 트리 기반 인덱스

트리 기반 인덱스는 인덱스를 저장하는 블록들이 트리 구조를 이루는 것을 말한다. 상용 DBMS에서는 트리 구조 기반의 B+ 트리 인덱스를 주로 활용한다.

① B트리 인덱스

일반적으로 사용되는 인덱스 방식이다. 루트 노드에서 하위노드로 나아가면서 키 값의 크기를 비교해 단말 노드에서 찾고자 하는 데이터를 검색한다. 키 값과 레코드를 가리키는 포인터들이 트리 노드에서 오름차순으로 저장된다. 모든 리프 노드는 같은 레벨에 있다.

② B+트리 인덱스

B트리의 변형으로, 인덱스 세트(단말 노드가 아닌 노드로 구성 )와 순차 세트(단말 노드로만 구성 )로 구분하는 구조의 인덱스이다. 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로 제공하고, 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킨다. 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드만을 이용한 순차 처리가 가능하다.

2) 비트맵 인덱스

비트맵 인덱스는 인덱스 컬럼의 데이터를 비트(Bit)값 인 0 또는 1로 변환하여 인덱스 키로 사용한다. 데이터가 비트로 구성되어 효율적인 논리 연산이 가능하며 저장 공간이 작다는 특징을 가진다.

비트맵 인덱스는 키 값을 포함하는 로우의 주소를 제공하는 것이 목표이다. 동일한 값이 반복되는 경우에 압축 효율이 우수하고, 비트맵 인덱스는 분포도가 좋은 컬럼에서 성능 향상에 효과적이다. 때문에 다중 조건을 만족하는 튜플 개수 계산에 적합하다는 특징을 가진다.

3) 함수 기반 인덱스

컬럼 값 대신 컬럼에 특정 함수나 수식을 적용해 산출된 값을 사용하는 것을 말한다. B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용하며, 데이터를 입력하거나 수정할 때 함수를 적용하기 때문에 부하가 발생할 수도 있다.

사용하는 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 크며, 대소문자, 띄어쓰기에 상관없이 데이터베이스를 조회할 때 유용하게 사용할 수 있다. 함수 기반 인덱스에 적용할 수 있는 함수 종류는 산술식, 사용자 정의 함수, PL/SQL 함수, Package, C callout 등이 있다.

4) 비트맵 조인 인덱스

비트맵 조인 인덱스 다수의 조인된 객체로 구성된 인덱스이다. 단일 객체로 구성된 일반적 인덱스와 다른 액세스 방법을 가지고 있다. 비트맵 인덱스와 물리적 구조가 동일하여 비트맵 인덱스가 가진 장점과 단점을 동일하게 가지고 있다.

5) 도메인 인덱스

도메인 인덱스는 개발자가 필요한 인덱스를 직접 만들어 사용하는 것을 말한다. 정의된 함수나 인덱스를 사용하는 것이 아니기 때문에 확장형 인덱스라고 불린다.

4.2. 인덱스 설계

인덱스 설계는 확실히 드러난 컬럼에 대해 기본적인 인덱스를 먼저 지정하고, 개발 단계에서 필요한 인덱스 설계를 반복적으로 진행한다.

▶인덱스 설계 순서

① 인덱스의 대상 테이블이나 컬럼 등을 선정한다.

② 인덱스의 효율성을 검토하여 인덱스를 최적화한다.

③ 인덱스 정의서를 작성한다.

4.3. 인덱스 대상 테이블 선정 기준

MULTI BLOCK READ* 수에 따라 인덱스 대상 테이블 여부를 판단한다. MULTI BLOCK READ는 테이블 액세스 시 메모리에 한 번에 읽어 들일 수 있는 블록의수를 말하한다.

또한, 랜덤 액세스가 빈번한 테이블과 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블, 다른 테이블과 순차적 조인이 발생되는 테이블을 인덱스 대상 테이블로 선정한다.

4.4. 인덱스 대상 컬럼 선정 기준

인덱스 컬럼의 분포도가 10~15% 이내인 컬럼을 선정한다. 분포도란 [(컬럼 값의 평균 Row 수 / 테이블의 총 Row 수 ) * 100] 공식에 따라 수치화한 인덱스 칼럼에 대한 기준 데이터를 말한다.

다만, 분포도가 10~15% 이상이어도 부분 처리를 목적으로 하는 컬럼이나 입출력 장표에서 조회 및 출력 조건으로 사용되는 컬럼, 인덱스가 자동 생성되는 기본키와 Unique 키 제약 조건을 사용한 컬럼, 가능한 한 수정이 빈번하지 않은 컬럼, ORDER BY, GROUP BY, UNION이 빈번한 컬럼 등을 인덱스 대상 컬럼으로 선정한다.

분포도가 좁은 컬럼은 단독 인덱스로 생성하고, 인덱스들이 자주 조합되어 사용되는 경우 하나의 인덱스로 생성한다.

4.5. 인덱스 설계 시 고려사항

◍ 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 줄 수 있다.

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

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

◍ 인덱스를 만들면 추가적인 저장 공간이 필요하다.

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

5. 뷰(View) 설계

뷰(View) 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위한 가상 테이블이다. 하나 이상의 기본 테이블로부터 유도된 결과물로, 데이터 보정 작업이나 처리 과정 시험 등 임시 작업을 위한 용도로 활용된다. 사용상 편의성의 최대를 위해 조인문의 사용을 최소화 한다.

뷰를 생성하면, 뷰 정의가 시스탬 내에 저장되었다가, 쿼리문에서 뷰 이름을 사용할 경우 정의된 기본 테이블로 대체되어 기본 테이블에 대해 실행된다.

5.1. 뷰(View)의 특징

기본 테이블로부터 유도되었기 때문에 기본 테이블과 같은 구조를 갖고, 조작 또한 동일하다. 뷰는 가상 테이블이기 때문에 물리적 구조로 구현되어 있지 않지만, 데이터의 논리적 독립성은 제공한다. 필요한 데이터만 뷰로 정의해서 처리할 수 있어서 관리가 용이하고 명령문이 간단하다는 장점을 가진다.

뷰를 통해서만 데이터에 접근하게 하면, 뷰에 나타나지않는 데이터가 발생한다. 이를 이용하여 뷰에 나타나지 않은 데이터를 안전하게 보호하는 기법으로도 사용할 수 있다.

기본 테이블의 기본키를 포함한 열(속성) 집합으로 뷰를 구성해야만 삭입/삭제/갱신 연산이 가능하다. 이미 정의된 뷰는 다른 뷰 정의에 기초가 될 수 있으나 뷰가 정의된 기본 테이블이나 뷰를 삭제하면, 그 테이블/뷰를 기초로 정의된 다른 뷰도 자동적으로 삭제되어 관리에 주의가 필요하다.

5.2. 뷰(View)의 장 단점

장점

단점

◍ 논리적 데이터 독립성을 제공하고 접근 제어를 통한 자동 보안을 제공한다.

◍ 동일 데이터에 대해 동시에 여러 사용자의 다른 응용이나 요구를 지원해준다.

◍ 사용자의 데이터 관리를 간단하게 해준다.

◍ 독립적인 인덱스를 가질 수 없다.

◍ 뷰 정의를 변경할 수 없다.

◍ 뷰로 구성된 내용에 대한 삽입/삭제/갱신 연산에 제약이 따른다.

5.3. 뷰 설계 순서

① 대상 테이블 선정한다.

◍ 외부 시스템과 인터페이스를 관리하는 테이블

◍ CRUD 매트릭스를 통해 여러 테이블이 동시에 자주 조인되고 접근되는 테이블

◍ SQL문 작성 시 거의 모든 문장에서 인라인 뷰(inline View: From 절 안에 사용되는 서브 쿼리 ) 방식으로 접근되는 테이블

② 대상 컬럼 선정

◍ 보안을 유지해야 하는 컬럼은 주의해서 선별한다.

③ 정의서 작성한다.

◍ 뷰 정의서에는 뷰 이름과 설명, 관련 테이블, 칼럼, 데이터 타입을 정의한다.

5.4. 설계 시 고려사항

◍ 테이블 구조가 단순화 될 수 있도록 반복적으로 조인을 설정하여 사용하거나 동일한 조건절을 사용하는 테이블을 뷰로 생성한다. 예를 들어, 특정 테이블과 다른 테이블이 자주 조인된다면 별도로 두개를 포함한 뷰를 생성하는 것이다.

◍ 동일 테이블이더라도 업무에 따라 테이블을 이용하는 부분이 달라질 수 있으므로 사용할 데이터를 다양한 관점에서 제시한다. 예를 들어, 사용자에게 공개되지 않아도 되거나 혹은 공개되서는 안될 데이터를 별도로 제외하고 뷰를 구성하는 것이다.

◍ 데이터 보안 유지를 고려한다.

6. 클러스터(Cluster) 설계

클러스터(Cluster)는 데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방식을 말한다. 클러스터링 키(클러스터링된 테이블에서 각각의 행을 접근할 때 기준이 되는 열 )로 지정된 컬럼 값의 순서대로 저장되고, 여러 테이블이 하나의 클러스터에 저장된다.

6.1. 클러스터(Cluster)의 특징

◍ 클러스터링 된 테이블은 데이터 조회 속도가 향상되지만, 데이터 입력/수정/삭제에 대한 성능은 저하된다.

◍ 데이터 분포도(인덱스는 분포도가 좁은 테이블이 좋지만 클러스터링은 분포도가 넓은 테이블에 유리 )가 넓은 테이블일수록 클러스터가 유리하고, 저장 공간을 절약할 수 있다.

◍ 클러스터링 된 테이블은 클러스터링 키 열을 공유하기 때문에 저장 공간이 줄어든다.

◍ 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔하는 일이 잦기 때문에 클러스터링 하지 않는 것이 효율적이다.

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

◍ 파티셔닝 된 테이블에는 클러스터링을 할 수 없다.

◍ 클러스터링 결과, 비슷한 데이터끼리 동일한 데이터 블록에 저장되기 때문에 디스크 I/O가 줄어든다.

◍ 클러스터링 된 테이블에 클러스터드 인덱스를 생성하면 접근 성능이 향상된다.

6.2. 클러스터 대상 테이블

◍ 분포도가 넓은 테이블

◍ 대량의 범위를 자주 조회하는 테이블

◍ 입력/수정/삭제가 자주 발생하지 않는 테이블

◍ 자주 조인되어 사용되는 테이블

◍ ORDER BY, GROUP BY, UNION이 잦은 테이블

7. 파티션(Partition) 설계

파티션(Partition)은 대용량 테이블이나 인덱스를 작은 논리적 단위로 나누는 과정이다. 대용량 DB의 경우 몇 개의 테이블에 데이터가 집중되므로, 이런 테이블을 작은 단위로 나눠 분산시킴으로써 성능 저하를 방지하고 데이터 관리의 편의성을 높인다.

테이블이나 인덱스를 파티셔닝 하면 파티션 키 또는 인덱스 키에 따라 물리적으로 별도의 공간에 데이터가 저장된다. 데이터 처리는 테이블 단위로 진행되고, 데이터 저장은 파티션 별로 수행된다.

7.1. 파티션의 장 단점

장점

◍ 데이터 접근 시 액세스 범위를 줄여 쿼리 성능이 향상된다.

◍ 파티션 별로 데이터가 분산 저장되기 때문에 디스크 성능이 향상된다.

◍ 파티션 별로 백업 및 복구를 수행하기 때문에 속도가 빠르다.

◍ 시스템 장애 시 데이터 손상 정도를 최소화할 수 있다.

◍ 데이터 가용성이 향상된다.

◍ 파티션 단위로 입출력을 분산시킬 수 있다.

단점

◍ 하나의 테이블을 세분화하여 관리하므로 세심한 관리가 요구된다.

◍ 테이블 간 조인에 대한 비용이 증가한다.

◍ 용량이 작은 테이블에 파티셔닝을 수행하면 오히려 성능이 저하된다.

7.2. 파티션의 종류

파티셔닝 방식에 따라 범위 분할, 해시 분할, 조합분할 등으로 나뉜다.

범위 분할

(Range Partitioning)

◍ 지정한 열의 값을 기준으로 분할한다.(일/월/분기 등)

해시 분할

(Hash Partitioning)

◍ 해시 함수를 적용한 결과 값에 따라 데이터 분할한다.

◍ 특정 파티션에 데이터가 집중되는 범위 분할의 단점을 보완(데이터를 고르게 분산시킴)한다.

◍ 특정 데이터가 어디에 있는지는 판단할 수 없다.

◍ 데이터가 고른 컬럼에 효과적이다.(고객번호, 주민번호 등)

조합분할

(Composite Partitioning)

◍ 범위 분할로 분할 후 해시 함수를 적용해 다시 분할하는 방식이다.

◍ 범위 분할한 파티션이 너무 커서 관리가 어려울 때 유용하다.

7.3. 파티션 키 선정 시 고려사항

파티션 키는 테이블 접근 유형에 따라 파티셔닝이 이뤄지도록 선정한다. 데이터 관리 용이성을 위해 이력성 데이터는 파티션 생성~소멸 주기를 일치시킨다. 매일 생성되는 날짜 컬럼, 백업의 기준이 되는 날짜 컬럼, 파티션 간 이동이 없는 컬럼, I/O 병목을 줄일 수 있는 데이터 분포가 양호한 컬럼 등을 파티션 키로 선정한다.

7.4. 인덱스 파티션(Index Partition)

파티션 된 테이블의 데이터를 관리하기 위해 인덱스를 나눈 것이다.

▶ 인덱스 파티션은 파티션 된 테이블의 종속 여부에 따라 구분된다.

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

◍ Global Partitioned Index: 테이블 파티션과 인덱스 파티션이 독립적으로 구성되도록 파티션, Local에 비해 데이터 관리가 어렵다.

▶ 인덱스 파티션은 인덱스 파티션 키 컬럼의 위치에 따라 구분된다.

◍ Prefixed Partitioned Index: 인덱스 파티션 키와 인덱스 첫 번째 컬럼이 같다.

◍ Non-Prefixed Partitioned Index: 인덱스 파티션 키와 인덱스 첫 칼럼이 다르다.

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

데이터베이스 용량 설계는 데이터가 저장될 공간을 정의하는 것으로, 테이블에 저장할 데이터 양과 인덱스, 클러스터 등이 차지하는 공간 등을 예측 반영한다.

8.1. 데이터베이스 용량 설계의 목적

DB의 용량을 정확히 산정해 ‘디스크 저장 공간 사용’의 효율성, 확장성 및 가용성을 높인다.

또한, 테이블과 인덱스에 적합한 저장 옵션을 지정한다.

디스크의 특성을 고려해 설계함으로써 디스크의 입출력 부하를 분산시키고, 채널의 병목 현상을 최소화한다. DB에 생성되는 오브젝트의 익스텐트(Extent, 기본 용량이 모두 찬 경우 추가적으로 할당되는 공간 ) 발생을 최소화하여 성능을 향상시킨다. 디스크에 대한 입/출력 경함이 최소화되도록 설계함으로써 데이터 접근성이 향상된다.

▶ 데이터 접근성을 향상시키는 설계 방법

◍ 테이블의 테이블 스페이스와 인덱스의 테이블 스페이스를 분리하여 구성한다.

◍ 테이블 스페이스와 임시 테이블 스페이스를 분리하여 구성한다.

◍ 테이블을 마스터 테이블과 트랜잭션 테이블로 분류한다.

8.2. 데이터베이스 용량 분석 절차

① 데이터 예상 건수, 로우, 길이, 보존 기간, 증가율 등 기초 자료를 수집해 용량을 분석한다.

② 분석된 자료를 바탕으로 DBMS에 이용될 테이블, 인덱스 등 오브젝트 용량을 산정한다.

③ 테이블과 인덱스의 테이블스페이스 용량을 산정한다.

④ 데이터베이스에 저장될 모든 데이터 용량과 데이터베이스 설치 및 관리를 위한 시스템 용량을 합해 디스크 용량을 산정한다.

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

9.1. 분산 데이터베이스 정의

논리적으로는 하나의 시스템에 속하지만, 물리적으로는 네트워크를 통해 연결된 다수의 사이트에 분산되어 있는 데이터베이스를 말한다.

분산 데이터베이스는 데이터 처리나 이용이 많은 지역에 데이터베이스를 위치시킴으로써 데이터의 처리가 가능한 해당 지역에서 해결될 수 있도록 한다.

9.2. 분산 데이터베이스의 구성 요소

분산 처리기	자체적으로 처리 능력을 가지며, 지리적으로 분산되어 있는 컴퓨터 시스템을 말한다.
분산 데이터베이스	지리적으로 분산되어 있는 데이터베이스로서 해당 지역의 특성에 맞게 데이터베이스가 구성된다.
통신 네트워크	분산 처리기들을 통신망으로 연결하여 논리적으로 하나의 시스템처럼 작동할 수 있도록 하는 통신 네트워크이다.

9.3. 분산 데이터베이스 설계 시 고려 사항

◍ 작업부하의 노드별 분산 정책

◍ 지역의 자치성 보장 정책

◍ 데이터의 일관성 정책

◍ 사이트나 회선의 고장으로부터의 회복 기능

◍ 통신 네트워크를 통한 원격 접근 기능

9.4. 분산 데이터베이스의 목표

◍ 위치 투명성(Location Transparency): 액세스하려는 데이터베이스의 실제 위치를 알 필요 없이 단지 데이터베이스의 논리적인 명칭만으로 액세스 할 수 있다.

◍ 중복 투명성(Replication Transparency): 동일 데이터가 여러 곳에 중복되어 있더라도 사용자는 마치 하나의 데이터만 존재하는 것처럼 사용하고, 시스템은 자동으로 여러 자료에 대한 작업을 수행 한다.

◍ 병행 투명성(Concurrency Transparency): 분산 데이터베이스와 관련된 다수의 트랜잭션들이 동시에 실현되더라도 그 트랜잭션의 결과에는 영향을 미치지 않는다.

◍ 장애 투명성* (Failure Transparency): 트랜잭션, DBMS, 네트워크, 컴퓨터 장애에도 불구하고 트랜잭션을 정확하게 처리한다.

＊투명성: 어떠한 사실이 존재함에도 마치 투명하여 보이지 않는 것처럼, 사실의 존재 여부를 염두에 두지 않아도 되는 성질

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

장점

단점

◍ 지역 자치성이 높음

◍ 자료의 공유성이 향상됨

◍ 분산 제어가 가능

◍ 시스템 성능이 향상됨

◍ 중앙 컴퓨터의 장애가 전체 시스템에 영향을 끼치지 않음

◍ 효율성과 융통성이 높음

◍ 신뢰성 및 가용성이 높음

◍ 점진적 시스템 용량 확장이 용이함

◍ DBMS가 수행할 기능이 복잡함

◍ 데이터베이스 설계가 어려움

◍ 소프트웨어 개발 비용이 증함

◍ 처리 비용이 증가함

◍ 잠재적오류가 증가함

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

분산 데이터베이스 설계는 APP이나 사용자가 분산되어 저장된 데이터에 접근하는 것을 목적으로 한다. 잘못 설계된 분산 DB는 복잡성이 증가하여 응답 속도 저하되고 비용 증가 등의 문제가 발생한다.

전역 관계망을 논리적 측면에서 소규모 단위로 분할한 후, 분할된 결과를 복수의 노드에 할당하는 과정으로 진행된다. 노드에 할당된 소규모 단위를 분할(Fragment)이라 부른다. 분산 설계 방법에는 테이블 위치분산, 분할, 할당이 있다.

9.7. 테이블 위치 분산

테이블 위치 분산은 데이터 베이스의 테이블을 각기 다른 서버에 분산시켜 배치하는 방법을 의미한다. 테이블 위치를 분산할 때는 테이블 구조를 변경하지 않으며, 다른 데이터베이스의 테이블과 중복되지 않게 배치한다. 데이터베이스의 테이블을 각각 다른 위치에 배치하려면 해당 테이블들이 놓일 수버들을 미리 설정해야 한다.

9.8. 분할 (Fragment)

분할은 테이블의 데이터를 분할하여 분산시키는 것이다. 주요 분할 방법은 수평 분할(특정 속성의 값을 기준으로 행 단위로 분할)과 수직 분할(데이터 칼럼 단위로 분할)로 나뉜다. 분할 규칙은 다음과 같다.

◍ 완전성(Completeness): 전체 데이터를 대상으로 분할해야 한다.

◍ 재구성(Reconstruction): 분할된 데이터는 관계 연산을 활용하여 본래의 데이터로 재구성 할 수 있어야 한다.

◍ 상호 중첩 배제(Disjointness): 분할된 데이터는 서로 다른 분할의 항목에 속하지 않아야 한다.

9.9. 할당 (Allocation)

할당은 동일한 분할을 여러 개의 서버에 생성하는 분산 방법으로, ‘중복이 없는 할당’과 ‘중복이 있는 할당’으로 나뉜다.

비중복 할당방식은 최적의 노드를 선택해서 데이터베이스의 단일 노드에서만 분할이 존재하도록 하는 방식이다. 일반적으로 애플리케이션에는 릴레이션을 배타적 분할로 분리하기 힘든 요구가 포함되므로 분할된 테이블 간의 의존성은 무시되고 비용 증가, 성능 저하 등의 문제가 발생할 수 있다.

중복 할당 방식은 동일한 테이블을 다른 서버에 복제하는 방식으로, 일부만 복제하는 부분 복제와 전체를 복제하는 완전 복제가 있다.

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

10.1. 데이터베이스 이중화(Database Replication)

데이터베이스 이중화는 시스템 오류로 인한 데이터 서비스의 중단, 물리적 손상 등으로 데이터베이스 서비스에 문제가 생길 때, 해당 서비스를 복구하기위해 동일한 데이터베이스를 복제하여 관리하는 것을 말한다.

하나 이상의 데이터베이스가 항상 같은 상태를 유지하므로, 데이터베이스에 문제가 발생하면 복제된 데이터베이스를 이용해 즉시 문제를 해결할 수 있다. 여러 데이터베이스를 동시에 관리하므로 사용자가 수행하는 작업이 데이터베이스 이중화 시스템에 연결된 다른 데이터베이스에도 모두 동일하게 적용된다.

애플리케이션을 여러 데이터베이스로 분산시켜 처리하므로, 데이터베이스의 부하를 줄이고, 백업 서버를 손쉽게 운영할 수 있다. 동일한 데이터베이스를 복제하여 관리하되, 데이터를 읽고 쓰는 데이터베이스는 하나만 설정하며, 다른 하나는 읽기만 가능하도록 설정한다.

10.2. 데이터베이스 이중화의 분류

변경 내용의 전달 방식에 따라 Eager 기법과 Lazy 기법으로 나뉜다.

Eager 기법

트랜잭션 수행 중 데이터 변경이 발생하면 이중화된 모든 데이터베이스에 즉시 전달하여 변경 내용이 즉시 적용되도록 하는 기법이다.

Lazy 기법

트랜잭션의 수행이 종료되면 변경 사실을 새로운 트랜잭션에 작성하여 각 데이터베이스에 전달되는 기법이다.

데이터베이스마다 새로운 트랜잭션이 수행되는 것으로 간주된다.

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

데이터베이스 이중화 구성 방법에는 활동-대기 방법과 활동-활동 방법이 있다.

활동-대기

(Active-Standby)

방법

◍ 한 DB가 활성 상태로 서비스하고 있으면 다른 DB는 대기하고 있다가 활성 DB에 장애가 발생하면 대기 상태에 있던 DB가 자동으로 모든 서비스를 대신 수행 한다.

◍ 구성 방법과 관리가 쉬어 많은 기업에서 이용된다.

활동-활동

(Active-Active)

방법

◍ 두 개의 DB가 서로 다른 서비스를 제공하다가 둘 중 한쪽 DB에 문제가 발생하면 나머지 다른 DB가 서비스를 제공한다.

◍ 두 DB가 모두 처리를 하기 때문에 처리율이 높지만 구성 방법 및 설정이 복잡하다.

10.4. 클러스터링(Clustering)

둘 이상의 서버를 하나의 서버처럼 운영하는 기술이다. 서버 이중화 및 공유 스토리지를 사용해 서버의 고가용성을 제공한다.

고가용성 클러스터링은 하나의 서버에 장애가 발생하면 다른 노드(서버)가 처리하여 서비스 중단을 방지하는 방식으로 일반적인 클러스터링이 이에 해당한다.

벙렬 처리 클러스터링은 전체 처리율을 높이기 위해 하나의 작업을 여러 서버에서 분산 처리하는 방식이다. 처리율 높이기 위한 방안으로 서비스 중단 방지보다는 사용자가 체감하는 성능에 초점을 맞춘다.

＊로드 밸런서(Load Balancer): 특성 서버에 집중되는 부하를 덜기 위해 여러 개의 서버로 부하를 분산시키는 네트워크 서비스

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

데이터베이스 보안은 데이터베이스의 일부분 또는 전체에 대해서 권한이 없는 사용자가 액세스하는 것을 금지하기 위해 사용되는 기술이다. 보안을 위한 데이터 단위는 테이블 전체, 특정 테이블, 특정 행과 열, 특정 데이터 등 다양하다. 데이터베이스 사용자들은 일반적으로 서로 다른 객체에 대해 다른 접근 권리/권한을 가진다.

11.1. 암호화(Encryption)

데이터를 보낼 때 송신자가 지정한 수신자 외에는 그 내용을 알지 못하도록 평문을 암호문으로 변환하는 것이다.

◍ 암호화(Encryption) 과정: 암호화되지 않은 평문을 정보 보호를 위해 암호문으로 바꾸는 과정이다.

◍ 복호화(Decryption) 과정: 암호문을 원래의 평문으로 바꾸는 과정이다.

11.2. 암호화 방식

암호화 방식은 개인키 암호방식과 공개키 암호방식으로 나뉜다.

1) Private Key Encryption

개인키 암호 방식(비밀 키 암호 방식)은 동일한 키로 데이터를 암호화&복호화 한다. 대칭 암호 방식 또는 단일 키 암호화 기법이라고도 한다. 비밀키는 제 3자에게는 노출시키지 않고, 데이터베이스 사용 권한이 있는 사용자만 나눠 갖는다.

종류는 전위 기법, 대체 기법, 대수 기법, 합성 기법이 있다.

2) Public Key Encryption

공개 키 암호 방식은 서로 다른 키로 데이터를 암호화&복호화 한다. 암호화 할 때 사용하는 키(공개 키)는 데이터베이스 사용자에게 공개하고, 복호화 할 때 사용하는 키(비밀 키)는 관리자가 관리하는 방법이다. 비대칭 암호 방식이라고도 하며, RSA(Rivest Shamir Adleman)가 대표적이다.

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

12.1. 접근통제

접근통제 데이터가 저장된 객체와 이를 사용하려는 주체 사이의 정보 흐름을 제한하는 것을 말한다. 데이터에 대해 아래 행위를 통제 함으로써, 자원의 불법적인 접근 및 파괴를 예방한다.

◍ 비인가 된 사용자의 접근 감시

◍ 접근 요구자의 사용자 식별 및 요구 정당성 확인 및 기록

◍ 보안 정책에 근거한 접근의 승인 및 거부

또한, 접근통제 기술에는 임의 접근통제(DAC), 강제 접근통제(MAC)가 있다.

임의 접근통제

(DAC,

Discretionary Access Control)

데이터에 접근하는 사용자의 신원에 따라 접근 권한을 부여하는 방식

◍ 통제 권한이 주체에 있어 주체가 접근통제 권한을 지정하고 제어할 수 있다

◍ 일반적으로 특정 객체에 대한 조작 권한은 데이터베이스 관리 시스템으로부터 부여받지만 임의 접근통제에서는 객체를 생성한 사용자가 생성된 객체에 대한 모든 권한을 부여받고, 부여된 권한을 다른 사용자에게 허가할 수도 있다.

◍ 임의 접근통제에 사용되는 SQL 명령어에는 GRANT(객체에 대한 권한을 부여하는 명령어 )와 REVOKE(객체에 부여된 권한을 취소하는 명령어 )가 있음

강제 접근통제

(MAC,

Mandatory Access Control)

주체와 객체의 등급을 비교하여 접근 권한을 부여하는 방식

◍ 제 3자가 접근통제 권한을 지정한다.

◍ 데이터베이스 객체별로 보안 등급을 부여할 수 있고, 사용자별로 인가 등급을 부여할 수 있다.

◍ 주체보다 보안 등급이 높은 객체: 읽기, 수정, 등록 모두 불가능

◍ 주체와 보안 등급이 같은 객체: 읽기, 수정, 등록 가능

◍ 주체보다 보안 등급이 낮은 객체: 읽기만 가능

접근통제의 3 요소는 접근통제 정책, 접근통제 메커니즘, 접근통제 보안모델이다.

12.2. 접근통제 정책

접근통제 정책은 어떤 주체가, 언제, 어디서, 어떤 객체에게, 어떤 행위에 대한 허용 여부를 정의하는 것이다. 대표적인 정책으로는 신분 기반, 규칙 기반, 역할 기반이 있다.

① 신분 기반 정책

주체/그룹의 신분에 근거하여 객체의 접근을 제한하는 방법이다.

◍ IBP(Individual-Based Policy): 최소 권한 정책, 단일 주체에게 하나의 객체에 대한 허가를 부여한다.

◍ GBP(Group-Based Policy): 복수 주체에게 하나의 객체에 대한 허가 부여한다.

② 규칙 기반 정책

주체가 갖는 권한에 근거하여 객체의 접근을 제한하는 방법

◍ MLP(Multi-Level Policy): 사용자 및 객체 별로 지정된 기밀 분류에 따른 정책이다.

◍ CBP(Compartment-Based Policy): 집단 별로 지정된 기밀 허가에 따른 정책이다.

③ 역할 기반 정책

◍ GBP의 변형된 정책으로, 주체의 신분이 아니라 주체가 맡은 역할에 근거하여 객체의 접근을 제한한다.

12.3. 접근통제 메커니즘

접근통제 메커니즘은 정의된 접근통제 정책을 구현하는 기술적인 방법이다.

◍ 접근 통제 목록: 객체를 기준으로 특정 객체에 대해 어떤 주체가 어떤 행위를 할 수 있는지 기록한 문서를 말한다.

◍ 능력 리스트: 주체를 기준으로 주체에게 허가된 자원 및 권한을 기록한 목록이다.

◍ 보안 등급: 주체나 객체 등에 부여된 보안 속성의 집합이다. 이를 기반으로 접근 승인 여부를 결정한다.

◍ 암호화: 데이터를 보낼 때 지정된 수신자 외에는 내용을 알 수 없도록 평문을 암호문으로 변환하는 것이다. 무단 도용을 방지하기 위해 주로 사용한다.

12.4. 접근통제 보안 모델

보안 정책을 구현하기 위한 정형화 된 모델이다.

1) 기밀성 모델

군사적인 목적으로 개발된 최초의 수학적 모델, 기밀 보장이 최우선인 모델

Level	단순 보안 규칙	스타 - 보안 규칙	강한 스타 보안 규칙
Level	읽기 권한	쓰기 권한	읽기/쓰기 권한
높은 등급	통제	가능	통제
같은 등급	가능	가능	가능
낮은 등급	가능	통제	통제

◍ 단순 보안 규칙:주체는 자신보다 높은 등급의 객체를 읽을 수 없다.

◍ 스타 보안 규칙: 주체는 자신보다 낮은 등급의 객체에 정보를 쓸 수 없다.

◍ 강한 스타 보안 규칙: 주체는 자신과 등급이 다른 객체를 읽거나 쓸 수 없다.

2) 무결성 모델

무결성 모델은 기밀성 모델에서 발생하는 ‘불법 정보 변경’을 방지하기 위해 개발된 모델로, 데이터의 일관성 유지에 중점을 둔다. 기밀성 모델과 동일하게 주체 및 객체의 보안 등급을 기반으로 한다.

Level	단순 무결성 규칙	스타 - 무결성 규칙
Level	읽기 권한	쓰기 권한
높은 등급	가능	통제
같은 등급	가능	가능
낮은 등급	통제	가능

◍ 단순 무결성 규칙: 주체는 자신보다 낮은 등급의 객체를 읽을 수 없다.

◍ 스타 무결성 규칙: 주체는 자신보다 높은 등급의 객체에 정보를 쓸 수 없다.

3) 접근통제 모델

접근통제 모델은 접근통제 메커니즘을 보안 모델로 발전시킨 것이다. 접근통제 행렬이 가장 대표적이며, 임의의 접근 통제를 관리하기 위한 보안 모델이다.

접근통제 모델은 행(주체)과 열(객체)로 권한 유형을 나타낸다.

◍ 행: 주체로서 객체에 접근을 시도하는 사용자이다.

◍ 열: 객체로서 접근 통제가 이뤄지는 테이블, 컬럼, 뷰 등과 같은 DB의 개체이다.

◍ 규칙: 주체가 객체에 대하여 수행하는 입력/수정/삭제 등의 DB에 대한 조작한다.

12.6. 접근통제 조건

접근 통제 메커니즘의 취약점을 보완하기 위해 접근 통제 정책에 부가하여 적용할 수 있는 조건을 말한다.

◍ 값 종속 통제(Value Dependent Control): 일반적으로는 객체에 저장된 값에 상관없이 접근 통제를 동일하게 적용하지만, 객체에 저장된 값에 따라 다른 통제를 허용해야 하는 경우 사용한다.

예) 납입 금액에 따라 보안 등급이 설정되고, 이에 따라 접근 여부가 결정되는 경우이다.

◍ 다중 사용자 통제(Multi User Control): 지정된 객체에 다수의 사용자가 동시에 접근을 요구하는 경우에 사용한다.

예) 여러 명으로 구성된 한 팀에서 다수결에 따라 접근 여부가 결정되는 경우이다.

◍ 문맥 기반 통제(Context Based Control): 특정 시간, 네트워크 주소, 접근 경로, 인증 수준 등에 근거하여 접근을 제어하는 방법이다. 다른 보안 정책과 결합하여 보안 시스템의 취약점을 보완할 때 사용된다.

예) 근무시간 사이에만 접근 가능하도록 설정한다.

12.7. 감사 추적

감사 추적은 애플리케이션이 DB에 접근하여 수행한 모든 활동을 기록하는 기능이다. 오류가 발생한 DB를 복구하거나 부적절한 데이터 조작을 파악하기 위해 사용한다. 실행한 프로그램, 사용자, 날짜 및 시간, 접근한 데이터의 이전/이후 값 등이 저장된다.

감사 로그로 남은 키워드나 정보를 추적하면 불법사용자의 침입을 탐지하는 것이 가능하다. 또한 위반으로부터 시스템이나 DB를 회복하고, 앞으로 발생할 수 있는 문제의 예방책으로 사용할 수 있다.

다만, 감사 자료 자체가 침입자에 의해 변조된다면 추적이 어려울 수 있기 때문에 감사 기능을 운영체제 커널에 구현하여 이를 근본적으로 차단해야 한다.

13. 데이터베이스 백업

데이터베이스 백업은 전산 장비의 장애에 대비하여 데이터베이스에 저장된 데이터를 보호하고 복구하기 위한 작업이다. 치명적인 데이터 손실을 막기 위해서는 데이터베이스를 정기적으로 백업해야 한다. DBMS는 DB 파괴 및 실행 중단 발생 시 이를 복구할 수 있는 기능을 제공한다.

13.1. 데이터베이스 장애 유형

백업 전략 수립을 위해 DB 장애 유형을 정확히 파악해야 한다.

◍ 사용자 실수: 사용자 실수로 인해 테이블이 삭제되거나 잘못된 트랜잭션이 처리된 경우이다.

◍ 미디어 장애: CPU, 메모리, 디스크 등 HD 장애나 데이터가 파손된 경우이다.

◍ 구문 장애: 프로그램 오류나 사용 공간의 부족으로 인해 발생하는 장애이다.

◍ 사용자 프로세스 장애: 프로그램 비정상 종료나 네트워크 이상으로 세션이 종료되어 발생하는 오류이다.

◍ 인스턴스 장애: HW 장애, 정전, 시스템 파일 파손 등 비정상적인 요인으로 인해 메모리나 DB 서버의 프로세스가 중단된 경우이다.

13.2. 로그 파일

데이터베이스의 처리 내용이나 이용 상황 등 상태 변화를 시간의 흐름에 따라 모두 기록한 파일을 말한다. 로그는 데이터베이스 복구를 위해 필요한 가장 기본적인 자료로, 트랜잭션이 작업한 모든 내용, 트랜잭션 식별, 트랜잭션 레코드, 데이터 식별자, 갱신 이전/이후 값 등의 데이터를 가지고 있다.

로그 파일을 기반으로 데이터베이스를 과거 상태로 복귀(UNDO)시키거나 현재 상태로 재생(REDO)시켜 데이터베이스 상태를 일관성 있게 유지한다. 로그 파일은 트랜잭션의 시작 시점, Rollback 시점, 데이터 입력/수정/삭제 시점 등에서 기록된다.

13.3. DB 복구 알고리즘

NO-UNDO

REDO

◍ DB 버퍼 내용을 비동기적으로 갱신한 경우의 복구 알고리즘

◍ NO-UNDO: 트랜잭션 완료 전에는 변경 내용이 DB에 기록되지 않으므로 취소할 필요 없음

◍ REDO: 트랜잭션 완료 후 DB 버퍼에는 기록되어 있고, 저장매체에는 기록되지 않았으므로 트랜잭션 내용을 다시 실행

UNDO

NO-REDO

◍DB 버퍼 내용을 동기적으로 갱신한 경우의 복구 알고리즘

◍ UNDO: 트랜잭션 완료 전에 시스템이 파손되었다면 변경된 내용을 취소

◍ NO-REDO: 트랜잭션 완료 전 DB 버퍼 내용을 이미 저장 매체에 기록했으므로, 트랜잭션 내용을 다시 실행할 필요가 없음

UNDO

REDO

◍ DB 버퍼 내용을 동기/비동기적으로 갱신한 경우의 복구 알고리즘

◍ DB 기록 전에 트랜잭션이 완료될 수 있으므로 완료된 트랜잭션이 DB에 기록되지 못했다면 다시 실행

NO-UNDO

NO-REDO

◍ DB 버퍼 내용을 동기적으로 저장 매체에 기록하지만, DB와는 다른 영역에 기록한 경우의 복구 알고리즘

◍ NO-UNDO: 변경 내용은 DB와 다른 영역에 기록되어 있으므로 취소할 필요 없음

◍ NO-REDO: 다른 영역에 이미 기록되어 있으므로 트랜잭션 다시 실행할 필요 없음

13.4. 백업 종류

1) 물리 백업

물리 백업은 운영체제를 이용해 데이터베이스 파일을 백업하는 방법이다. 백업 속도가 빠르고 작업이 단순하지만, 문제 발생 시 원인 파악 및 문제 해결이 불가능하다는 단점이 있다. (물리 데이터베이스의 구조와 결함을 알아보기 위해 이를 분해하면 데이터 손실이나 고장우려가 있다.)

2) 논리 백업

논리 백업 DBMS 유틸리티를 이용해 데이터베이스 내의 논리 객체들을 백업하는 방법이다. 복원 시 데이터 손상을 막고 문제 발생 시 원인 파악 및 해결이 수월하지만, 백업/복원 시 시간이 많이 소요된다.

구분	설명	복구 수준
물리 백업	로그 파일 백업 실시	완전 복구
물리 백업	로그 파일 백업 없음	백업 시점까지 복구
논리 백업	DBMS 유틸리티	백업 시점까지 복구

14. 스토리지

스토리지(Storage)는 단일 디스크로 처리할 수 없는 대용량의 데이터를 저장하기 위해 서버와 저장장치를 연결하는 기술을 말한다. 스토리지의 종류로는 DAS, NAS, SAN 등이 있다.

14.1. DAS(Direct Attached Storage)

서버와 저장장치를 케이블로 직접 연결하는 방식이다. 서버에서 저장 장치를 관리하고, 직접 연결하기 때문에 속도가 빠르고 설치 및 운영이 쉽다. 또한 초기 구축 비용 및 유지보수 비용이 다른 방식에 비해 저렴하다.

다만, 직접 연결 방식이기 때문에 다른 서버에 접근할 수 없고 파일을 공유할 수 없으며, 확장성 및 유연성이 상대적으로 떨어진다는 단점을 가진다.

저장 데이터가 적고 공유가 필요 없는 환경에 적합한 방식이다.

14.2. NAS(Network Attached Storage)

NAS는 서버와 저장장치를 네트워크를 통해 연결한 방식이다. 별도의 파일 관리 기능이 있는 NAS Storage가 내장된 저장장치를 직접 관리한다. 이더넷 스위치를 통해 다른 서버에서도 스토리지에 접근하며, 파일 또한 공유할 수 있다. 때문에 DAS에 비해 확장성 및 유연성이 우수하다.

다만, 네트워크를 통해 연결되기 때문에 접속량이 증가 하면 성능이 저하된다.

14.3. SAN(Storage Area Network)

DAS의 빠른 처리와 NAS의 파일 공유 장점을 혼합한 방식이다. 서버와 저장 장치를 연결하는 전용 네트워크를 별도로 구성하는 방식으로 파이버채널(FC, Fiber Channel: 컴퓨터 장치 간 데이터 전송 속도를 기가바이트로 높이기 위한 네트워크 기술 ) 스위치를 이용해 네트워크를 구성한다.

파이버채널서버는 서버나 저장장치를 광케이블로 연결하므로 처리 속도가 빠르다. 또한, 서버들이 저장장치 및 파일을 공유할 수 있기 때문에 확장성, 유연성, 가용성이 뛰어나다.

높은 트랜잭션 처리에 효과적이지만, 기존 시스템 장비 업그레이드가 필요하고, 초기 설치 시 별도의 네트워크를 구축해야 하므로 비용이 많이 든다.

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

15.1. 테이블

데이터를 저장하는 데이터베이스의 가장 기본적인 오브젝트(객체)이다. 다음과 같이 구성된다.

◍ 로우(Row, 행): 튜플, 인스턴스, 어커런스 등을 저장한다.

◍ 컬럼(column, 열): 각 속성 항목에 대한 값을 저장한다.

◍ 기본 키(Main Key): 한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성을 말한다.

◍ 외래 키: 다른 릴레이션의 기본 키를 참조하는 속성 또는 속성들의 집합이다.

15.2. 엔티티를 테이블로 변환

논리 데이터 모델에서 정의된 엔티티를 물리 데이터 모델의 테이블로 변환하는 것을 말한다. 엔티티를 테이블로 변환한 후, 테이블 목록에 대한 정의서를 작성한다. ‘테이블 목록 정의서’는 테이블 목록, 전체 테이블을 목록으로 요약하고 관리하는 문서이다.

1) 변환 규칙

논리적 설계(데이터 모델링)	물리적 설계
엔티티, Entity	테이블, Table
속성, Attribute	컬럼, Column
주 식별자, Primary UID	기본 키, Primary Key
외부 식별자, Secondary(Alternate) UID	고유 키, Unique Key
관계, Relationship	외래 키, Foreign Key
사업 제약 조건, Business Constraints	확인 제약 조건, Check Constraints

2) 변환 시 고려 사항

◍ 일반적으로 테이블과 엔티티 명칭은 동일하게 유지하는 것을 권고한다.

◍ 엔티티는 주로 한글이지만 테이블은 소스코드 가독성을 위해 영문을 사용한다.

◍ 메타 데이터 관리 시스템에 표준화 된 용어가 있을 시, 그 단어를 사용하여 명명한다.

15.3. 슈퍼타입(공통속성)/서브타입(고유속성)을 테이블로 변환

논리 데이터 모델에서 이용되는 형태인 슈퍼/서브타입은 물리 데이터 모델로 설계할 때 테이블로 변환해야 한다. (주의할 것은 슈퍼타입과 서브타입은 절대 부모자식관꼐가 아니다.)

1) 슈퍼타입 기준 테이블 변환

◍ 서브타입을 슈퍼타입에 통합하여 하나의 테이블로 만드는 것이다.

◍ 서브타입에 속성이나 관계가 적을 경우에 적용하는 방법이다.

◍ 하나로 통합된 테이블에는 서브타입의 모든 속성이 포함되어야 한다.

장점

단점

◍ 데이터 액세스 용이, 뷰를 이용한 각각의 서브타입만을 액세스 하거나 수정 가능

◍ 서브타입 구분이 없는 임의 집합에 대한 처리 용이

◍ 여러 테이블을 조인하지 않아도 되므로 수행 속도 빠름, 단순한 SQL 문장 구성

◍ 테이블 컬럼이 증가에 따른 디스크 저장 공간 증가

◍ 처리마다 서브타입에 대한 구분이 필요한 경우가 발생

◍ 인덱스 크기 증가로 인한 인덱스 효율 저하

2) 서브타입 기준 테이블 변환

◍ 슈퍼타입 속성들을 각각의 서브타입에 추가하여, 서브타입들을 개별 테이블로 만드는 것이다.

◍ 서브타입에 속성이나 관계가 많이 포함된 경우 적용하기에 적합하다.

장점

단점

◍ 각 서브타입 속성들의 선택 사양이 명확한 경우 유리

◍ 처리할 때마다 서브타입 유형을 구분할 필요가 없음

◍ 여러 테이블로 통합하기 때문에 테이블 당 크기가 감소하여 전체 테이블 스캔 시 유리

◍ 수행 속도 감소

◍ 복잡한 처리를 위한 SQL 통합의 어려움

◍ 부분 범위에 대한 처리 곤란

◍ 여러 테이블을 통합한 뷰는 조회만 가능, 식별자의 유지 관리가 어려움

3) 개별 타입 기준 테이블 변환

슈퍼타입과 서브타입들을 각각의 개별적 테이블로 변환하는 과정을 말한다. 슈퍼타입과 서브타입 테이블 사이에 각각 1대1 관계가 형성된다. 개별 타입 기준 테이블 변환을 적용하는 경우는 다음과 같다.

◍ 전체 데이터에 대한 처리가 빈번한 경우

◍ 서브타입의 처리가 대부분 독립적으로 발생하는 경우

◍ 통합 테이블의 컬럼 수가 많은 경우

◍ 서브 타입의 컬럼수가 많은 경우

◍ 트랜잭션이 주로 슈퍼타입에서 발생하는 경우

◍ 퓨서타입 처리 범위가 넓고 빈번하게 발생하여 단인 테이블 클러스터링이 필요한 경우

장점

단점

◍ 저장 공간이 상대적으로 작다.

◍ 슈퍼타입 또는 서브타입 각각의 테이블에 속한 정보만 조회하는 경우 용이한 문장 작성

◍ 슈퍼타입과 서브타입을 같이 처리하면 조인이 발생하여 성능이 떨어짐

15.4. 속성을 컬럼으로 변환

논리 데이터 모델에서 정의한 속성을 물리 데이터 모델의 컬럼으로 변환한다.

1) 일반 속성 변환

속성과 컬럼 명칭은 반드시 일치할 필요는 없으나, 개발자와 사용자 간 의사소통을 위해 가능한 한 표준화 된 약어를 사용해 일치시키는 것이 좋다. 컬럼명은 SQL의 예약어를 피하고, 또한 가독성을 높이기 위해 가능한 한 짧게 지정한다.

복합 단어를 컬럼명으로 사용할 때는 미리 정의된 표준을 따라야한다. 테이블의 컬럼을 정의한 후에는 한 로우에 해당하는 샘플 데이터를 작성해 컬럼의 정합성을 검증한다.

2) Primary UID를 기본 키로 변환

논리 데이터 모델에서의 Primary UID는 물리 데이터 모델의 기본 키로 만든다.

3) Primary UID(관계의 UID Bar)를 기본 키로 변환

다른 엔티티와의 관계로 인해 생성된 Primary UID는 물리 데이터 모델의 기본 키로 만든다.

4)Secondary(Alternate) UID를 유니크 키로 변환

논리 모델링에서 정의된 Secondary UID 및 Alternate Key는 물리 데이터 모델에서 유니크 키로 만든다.

15.5.관계를 외래키로 변환

논리 데이터 모델에서 정의된 관계는 기본 키와 이를 참조하는 외래 키로 변환한다. 다음은 개체 A, B로 이루어진. E-R 모델을 관계 테이블로 변환하는 방법이다.

1) 1:1 관계: 개체 A의 기본 키를 개체 B의 외래 키로 추가하거나, 개체 B의 기본 키를 개체 A의 외래키로 추가하여 표현한다.

2) 1:M 관계: 개체 A의 기본 키를 개체 B의 외래키로 추가하여 표현하거나, 별도의 테이블로 표현한다.

3) N:M 관계: 릴레이션 A와 B의 기본 키를 모두 포함하는 별도의 릴레이션으로 표현한다. 이 때 생성된 별도의 릴레이션을 교차 릴레이션 또는 교차 엔티티라고 한다.

4) 1:M 순환 관계: 개체 A에 개체 A의 기본 키를 참조하는 외래 키 컬럼을 추가해 표현한다. 주로 데이터의 계층 구조를 표현하기 위해 사용된다.

15.6. 관리 목적의 테이블/컬럼 추가

논리 데이터 모델에는 존재하지 않는 테이블이나 컬럼을 데이터베이스 관리 혹은 데이터베이스를 이용하는 프로그래밍 수행 속도 향상을 위해 물리 데이터 모델에 추가하는 것이다.

예) 시스템 등록 일자, 시스템 번호 등

15.7. 데이터 타입 선택

논리 데이터 모델에서 정의된 논리적 데이터 타입을 물리적인 DBMS의 물리적 특성과 성능을 고려해 최적의 데이터 타입과 데이터의 최대 길이를 선택한다. 주요 타입은 문자(Character), 숫자(Numeric), 날짜(Date)로 나뉜다.

▶Oracle에서 대표 데이터 유형

◍ CHAR: 고정길이 문자열 Data, 최대 2,000 Byte까지 저장 가능

◍ VARCHAR2: 가변길이 문자열 Data, 최대 4,000 Byte까지 저장 가능

◍ NUMBER: 38자리수의 숫자 저장 가능

◍ DATE: 날짜 저장

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

물리데이터 모델 품질 검토는 물리 데이터 모델을 설계하고 데이터베이스 객체를 생성한 후 진행하는 과정으로 개발 단계 전 모델러와 이해관계자들이 모여 수행한다. 물리 데이터 모델 품질 검토는 성능 향상과 오류 예방을 목적으로 하는데, 물리 데이터 모델이 시스템 성능에 직접적인 영향을 주기 때문에 향후 발생할 문제까지 면밀히 검토해야 한다.

협업하여 검토를 진행하기 때문에 모든 이해관계자가 동의하는 검토 기준을 설정해야 한다. 일반적으로 정확성, 완전성, 준거성, 최신성, 일관성, 활용성을 고려하여 검토를 진행한다.

16.1. 물리 데이터 모델 기준

◍ 정확성: 데이터 모델이 요구사항, 업무 규칙, 포기법에 다라 정확하게 표현

◍ 완전성: 데이터 모델의 구성 요소, 요구사항, 업무 영역을 누락 없이 정의 및 반영

◍ 준거성: 데이터 표준, 표준화 규칙, 법적 요건 등을 정확하게 준수

◍ 최신성: 최근의 이슈나 현행 시스템을 반영하고 있음

◍ 일관성: 표현상의 일관성을 유지하고 있음

◍ 활용성: 작성된 모델과 설명을 사용자가 충분히 이해할 수 있고, 업무 변화에 따른 구조 변경이 최소화 될 수 있도록 설계되었음

16.2. 물리 데이터 모델 검토 항목

물리 데이터 모델 검토 항목은 물리 데이터의 특성을 반영한 품질 기준을 작성한 후 이를 기반으로 작성한다. 물리 데이터 모델에 정의된 테이블, 컬럼, 무결성 제약 등 물리 데이터 모델의 주요 구성 요소와 반정규화, 인덱스, 스토리지 등 물리 데이터 모델의 전반적인 항목을 포함한다. 이러한 체크리스트는 품질 검토를 용이하게 해준다.

16.3. 물리 데이터 모델 검토 순서

① 데이터 품질 정책 및 기준 확인

② 물리 데이터 품질의 특성에 따라 품질 기준 작성

③ 데이터 품질 기준에 따라 체크리스트 작성

④ 논리 데이터와 물리 데이터 모델 비교

⑤ 각 모델링 단계의 모델러와 이해관계자가 품질 검토 수행

⑥ 5번의 결과를 종합하여 물리 DB 모델의 품질 검토 보고서 작성

'정보처리기사 > 3과목 데이터베이스 구축' 카테고리의 다른 글

[정보처리기사] Chapter 05. 데이터베이스 구축: 데이터전환 (0)	2021.04.26
[정보처리기사] Chapter 04. 데이터베이스 구축: SQL 활용 (0)	2021.04.25
[정보처리기사] Chapter 03. 데이터베이스 구축: SQL 응용 (0)	2021.04.25
[정보처리기사] Chapter 01. 데이터베이스 구축: 논리 데이터베이스 설계 (0)	2021.04.25