1. 논리 모델인 ERD 를 테이블 관계도 (Table Relationship Diagram) 로 전환한다.
2. 테이블 관계 이외의 데이터베이스와 관련된 기타사항을 설계한다.
3. 분산 데이터베이스를 설계한다.
1. 관계형 테이블로 전환
논리적인 데이터 모델링을 통해 산출한 ERD 를 관계형 데이터베이스로 구축할 수 있도록 테이블 관계도로 전환하는 작업을 진행한다.
관계형 테이블로 변환해야 하는 항목은
- 엔티티타입은 테이블로 전환한다.
- 주식별자는 PK 로 변환한다.
- 속성은 컬럼으로 변환한다.
- 관계에 의한 외부 식별자는 FK 로 변환한다.
엔티티타입을 테이블로 전환한다.
(1) 독립 엔티티타입은 독립 테이블로 전환된다.
(2) 완전 종속 엔티티타입은 완전 종속 테이블로 전환된다.
(3) 부분 종속 엔티티타입은 부분 종속 테이블로 전환된다.
주식별자를 PK로 전환한다.
(1) 데이터 모델의 주식별자는 PK로 전환된다.
(2) 무결성 제약 (Referential Integrity) 을 유지하는 역할을 한다.
(3) PK 는 테이블에 있는 각각의 로우를 유일하게 식별한다.
(4) PK 는 Null 값을 갖지 않는다.
(5) PK 는 변경되지 않는다.
(6) 가능하면 모든 테이블에서 PK 를 정의한다.
관계에 의한 외부 식별자는 FK 로 변환된다.
(1) 1:1 주식별자 관계 변환
(2) 1:1 비식별자 관계 변환
(3) 1:M 관계 변환
(4) 자기 참조 관계 변환
(5) 슈퍼타입/서브타입 관계 변환
1) 각각의 테이블로 변환
2) 서브타입 테이블로 변환
3) 통합 테이블로 변환
2. 반정규화
반정규화란 정규화된 엔티티타입, 속성, 관계를 시스템의 성능 향상 개발 (Development) 과 운영 (Maintenance) 을 단순화하기 위해
데이터 모델을 통합하는 프로세스를 의미한다.
반정규화를 할 때 가장 중요하게 검토해야 할 기준은
각각의 엔티티타입과 속성, 관계에 대해 데이터의 정합성과 데이터의 무결성을 우선으로 할지
데이터베이스 구성의 단순화와 성능을 우선으로 할지에 달려있다.
반정규화 주의사항
테이블의 복잡성과 시스템의 성능을 고려하지 않을 수 없으므로 기본적으로는 정규화한 테이블을 그대로 유지하는 것을 목표로 하고,
문제가 되는 테이블에 대해서 뷰의 생성, 파티셔닝 테이블 생성, 인덱스 조정, 클러스터링 적용 등 여러 가지 방안을 먼저 조사하도록 한 후
반정규화를 고려한다.
반정규화 절차
(1) 반정규화의 대상을 조사한다.
(2) 반정규화의 대상을 다른 방법으로 처리할 수 있는지 검토한다.
(3) 반정규화를 적용한다.
반정규화 방법
- 테이블 반정규화 방법
1) 테이블 병합
1:1 관계의 테이블 병합
1:M 관계의 테이블 병합
슈퍼타입 서브타입 테이블 병합
2) 테이블 분할
테이블의 수직적 분할
테이블의 수평적 분할
3) 테이블 추가
중복 테이블 추가
통계 테이블 추가
이력 테이블 추가
부분 테이블 추가
- 컬럼 반정규화 방법
1) 중복 컬럼 추가
2) 파생 컬럼 추가 (컬럼(Column), 로우(Row) 에 의한 파생 컬럼)
3) 이력 테이블 컬럼 추가
4) PK 에 의한 컬럼 추가
5) 응용 시스템의 오작동 처리를 위한 컬럼 추가
(1) 중복 컬럼 방법
(2) 중복 컬럼 특징
1) 데이터 무결성의 확보가 어려워진다.
2) 중복 데이터를 저장하기 위해서 추가적인 디스크 저장 공간이 필요하다.
3) 시스템 운영자가 중복 속성에 대한 소유권을 혼동할 수 있다.
(3) 중복 컬럼 추가
1) 해당 테이블에서 자주 사용되는 컬럼인 경우에 컬럼을 중복시킨다.
2) 데이터를 조회하는 경로를 단축하기 위해 컬럼을 중복시킨다.
(4) 파생 컬럼 추가
1) 컬럼에 의한 파생 컬럼 추가
2) 로우에 의한 파생 컬럼 추가
(5) 이력 데이터 모델의 컬럼 추가
1) 변경 이력, 발생 이력에 대한 최신 정보 컬럼 추가
2) 진행 이력에 대한 종료 일자 컬럼 추가
(6) PK 에 의한 컬럼 추가
1) 복합 PK 에 의한 컬럼 추가
2) PK 에 의한 컬럼 추가
(7) 시스템 오작동 처리를 위한 컬럼 추가
- 관계 반정규화 방법
3. 무결성 제약 정의
데이터베이스의 가장 중요한 역할 중 하나인 정보의 정확한 저장, 정합성을 유지하기 위해 데이터베이스에 무결성 제약을 적용한다.
입력 참조 무결성
각 테이블에 레코드가 생성될 때 자신이 참조하고 있는 테이블의 PK 에 대해 데이터의 정합성을 일치시켜주는 기능이다.
- 의존 (Dependent)
- 자동 (Automatic)
- 기본 (Default)
- 지정 (Customized)
- Null
- 미지정
수정 참조 무결성
- 제한 (Restrict)
- 연쇄 (Cascade)
삭제 참조 무결성
- 제한 (Restrict)
- 연쇄 (Cascade)
- 기본 (Default)
- 지정 (Customized)
- Null
- 미지정
참조 무결성 적용시 주의 사항
아무리 데이터베이스의 성능이 중요하다 하여도 우선적으로 데이터의 정합성, 무결성 보존 방법을 철저히 검토하여 적용한 이후에
FK 제약을 사용하지 않도록 해야할 것이다.
FK 제약이 걸려 있는 컬럼들의 인덱스 생성
DBMS 내부적으로 이러한 추가적인 작업이 필요하므로 FK 제약이 걸려있는 컬럼들은 가능하면 인덱스를 걸어줄 것을 권유한다.
4. 트랜잭션 분석
트랜잭션 정의
트랜잭션이란 데이터베이스에 행해지는 작업의 논리적인 단위다.
오라클에서 사용하는 트랜잭션 제어 SQL 문장의 예
- BEGIN TRANSACTION 과 END TRANSACTION: 트랜잭션 시작과 종료를 제어할 때
- COMMIT: 트랜잭션을 데이터베이스에 반영할 때
- ROLLBACK: 트랜잭션을 데이터베이스에 반영하지 않고 취소할 때
- SAVEPOINT/ROLLBACK TO: 트랜잭션 반영을 취소할 때 취소 지점을 명시하는 경우
- SET TRANSACTION: 읽기 전용으로 트랜잭션을 발생시킬 것인지 읽기/쓰기로 발생시킬 것인지를 지정
- FOR UPDATE/LOCK TABLE: 트랜잭션이 발생하는 동안 다른 사용자에 의해 데이터가 변형되는 것을 방지
트랜잭션 분석
트랜잭션은 각각의 업무에서 처리하는 업무의 기본 단위 이므로 데이터베이스 담당자가 하는 것이 아니라
애플리케이션을 분석, 설계하는 업무 개발 담당자가 분석한다.
트랜잭션 분석도 이용
- 용량 산정의 근거 자료로 이용
- 디스크 구성의 이용
- 데이터베이스와 연결되는 채널의 분산
5. 뷰 설계
뷰란 데이터베이스사용자들이 접근하도록 테이블을 이용하여 가상 (Virtual) 테이블을 구성하는 데이터베이스 오브젝트로,
별도의 저장 공간은 없다.
뷰의 특징
- 테이블의 구조를 단순화한다.
- 다양한 관점에서 데이터를 제시할 수 있다.
- 데이터의 보안을 유지한다.
- 논리적인 데이터의 독립성을 제공한다.
뷰 정의
(1) 뷰의 대상이 되는 테이블을 선정한다.
(2) 뷰의 대상이 되는 컬럼을 선정한다.
(3) 뷰 정의서를 작성한다.
6. 인덱스 설계
인덱스란 테이블의 로우를 식별할 수 있도록 컬럼값과 그 값을 포함하는 로우의 논리적인 주소를 별도의 저장 구조를 만들어 저장하는 것이다.
인덱스를 이용하여 테이블의 존재하는 데이터 검색 속도를 빠르게 한다.
인덱스를 선정하기 위해서는 먼저 인덱스를 사용할 테이블과 컬럼에 대해 조사한다.
두번째는 대상이 되는 인덱스 컬럼에 대해 가장 최적으로 이용할 수 있는 방법을 적용하며
마지막으로 설계된 인덱스를 인덱스 정의서에 기록한다.
인덱스 대상 선정
- 대상 테이블 선정
- PK 컬럼 인덱스
- FK 컬럼 인덱스
- 인덱스 대상 컬럼 선정
인덱스 최적화
- 인덱스 효율 검토
- 인덱스 데이터타입 적용
- 인덱스 정렬
- 클러스터링 검토
클러스터링이란 데이터베이스뿐만 아니라 서버를 구성하거나 소프트웨어를 구성할 때 비슷한 종류의 무엇인가를 묶어준다는 개념으로 이용된다.
7. 데이터베이스 용량 설계
물리 데이터베이스 설계에서 데이터가 저장되는 공간을 정의하는 작업을 데이터베이스 용량 설계라 한다.
데이터베이스의 용량 설계는 데이터 모델링 과정을 통해 분석/설계된 테이블을 데이터베이스에 설치할 때,
테이블에 저장해야 할 데이터양과 각종 오브젝트 (인덱스, 클러스터, 시퀀스 등) 가 차지하는 볼륨이
얼마나 많은 디스크 공간을 차지할 것인지 예측하여 반영하는 것이다.
데이터베이스 용량 분석의 목적
- 정확한 데이터 용량을 산정하여 디스크 사용의 효율을 높인다.
- 디스크 구성방법에 따라 입출력이 달라질 수 있지만, 기본적으로 업무량이 집중되어 있는 디스크를 분리하여 설계함으로써
집중화된 디스크에 대한 입출력 부하를 분산시킬 수 있다.
- 똑같은 자원에 여러 프로세스가 동시에 접근할 때 발생하는 디스크 입출력 경합을 최소화하여 데이터의 접근 성능을 향상시킨다.
- 데이터베이스 오브젝트의 익스텐트 발생을 줄인다.
데이터베이스 용량 분석 절차
(1) 용량 분석을 위한 기초데이터를 수집한다
(2) 기초 데이터를 이용하여 DBMS 에 이용하는 오브젝트별로 용량을 선정한다.
- 오브젝트 설계
- 테이블 스페이스 용량 산정
- 디스크 용량 산정
8. 접근 방법 설계
- 접근방법 구분
직접 접근 - 스캔
간접 접근 - B트리 인덱스, 비트맵 인덱스, 해싱
저장 방법에 따른 접근 - 클러스터링
OLTP 와 DSS
업무 처리를 위한 시스템들은 일반적으로 온라인 트랜잭션 시스템, 즉 OLTP (Online Transaction Processing) 라고 하고,
업무 데이터를 이용하여 기업의 의사 결정에 이용하는 시스템을 DSS (Decision Support Systems) 라고 한다.
OLTP 는 소량의 데이터를 빠르게 처리하는 것이 필수적이고
DSS 에서는 대량의 데이터를 분석하여 기업의 정확한 현황을 분석할 수 있도록 자료를 보여주는 것이 주 관심사다.
스캔 방식
스캔방식을 적용하는 경우는
- 테이블의 크기가 작을 때, 즉 물리적인 블록에 6블록 정도까지 데이터가 점유하고 있을 때는 스캔 방식으로 데이터를 읽어도 처리속도가 빠르다.
- 가져오려는 데이터의 분포도가 테이블의 20% 를 초과할 때 스캔 방식으로 처리한다.
B 트리 인덱스
- B 트리 인덱스의 기본구조
B 트리 인덱스가 OLTP 시스템에서 주로 사용된다면,
비트맵 인덱스는 DSS 시스템에서 주로 사용된다.
B 트리 인덱스의 블록
1) 리프 블록 (Leaf Block)
테이블의 각 레코드의 인덱스 정보를 가지고 있는 블록이다.
2) 브랜치 블록 (Branched Block)
리프 블록과 루트 블록의 중간에서 블록 사이의 정보에 대한 다리 역할을 하는 블록이다.
3) 루트 블록 (Root Block)
트리의 최상위 수준에 위치하며 조회, 입력, 수정, 삭제가 발생하면 제일 먼저 접근된다.
- B 트리 인덱스의 검색 원리
- B 트리 인덱스의 입력, 수정, 삭제 원리
비트맵 인덱스
비트맵 인덱스는 컬럼 정보를 0과 1을 이용하여 별도의 인덱스로 저장하는 방법으로,
WHERE 조건의 AND 나 OR 연산에 의해 데이터를 검색하는 방법이다.
- 비트맵 인덱스의 검색 원리
비트맵 인덱스는 레코드의 물리적인 로우 ID 값을 가지고 있지 않다.
그러나 로우 ID 값을 계산해서 추적할 수는 있다.
그리고 비트맵 인덱스간의 비트 연산도 가능하여 대용량 정보를 빠르게 검색할 수 있다.
- 인덱스의 입력, 수정, 삭제 원리
B 트리 인덱스와 비트맵 인덱스 비교
항목 |
B 트리 |
비트맵 |
인덱스의 검색 속도 |
- B 트리 알고리즘이 필요로 하는 인덱스값만 읽을 수 있다. - 인덱스 크기가 크므로 상대적으로 소량의 데이터를 검색할 때 유리하다. |
- 항상 전체 인덱스를 읽는다. - 인덱스 크기가 적으므로 상대적으로 대량의 데이터를 읽을 때 유리하다. |
인덱스 크기 |
- 인덱스 크기가 비트맵 인덱스보다 매우 크다. - 비트맵 인덱스보다 크기에서 백배 이상 차이날 수 있다. |
- 인덱스 크기가 매우 적다. |
인덱스의 변경 관리성 |
- B 트리 인덱스는 트리 알고리즘에 의해 손쉽게 인덱스 입력, 수정, 삭제가 가능하다. |
- 비트맵 인덱스의 입력, 수정, 삭제는 전체 인덱스를 조정해야 하는 부담이 있다. - 인덱스 재생성과 비슷하다. |
필요한 스토리지 |
- B 트리 인덱스는 인덱스 트리를 관리하기 위해 많은 스토리지가 요구된다. |
- 비트맵 인덱스는 비트 연산으로 관리하므로 작은 스토리지가 요구된다. |
연산 능력 |
- B 트리 인덱스는 AND 연산에는 좋은 성능을 나타내나 OR, != 등에서 성능에 취약하다. |
- 비트맵 인덱스는 비트 연산을 통해 빠르게 처리할 수 있다. |
분포도 |
- 데이터 분포도가 높은 컬럼에 적합하다. |
- 데이터 분포도가 아주 낮은 컬럼에 적합하다. |
해싱 기법 적용
해싱 기법이란 데이터의 빠른 접근을 위해 해시 알고리즘에 의해 테이블 정보를 읽는 것을 말한다.
간단한 해시 알고리즘
1) 나눗셈을 이용한 나머지 방법
2) 접기
3) 기수 변환
4) 데이터의 자리 재배열
해시 인덱스의 특징
1) 6블록 이상의 물리적인 블록의 크기를 갖는 테이블에 적용한다.
2) 정렬 순서에 따른 접근 방식이 아니라 임의대로 접근하는 경우가 많이 발생되는 경우에 적용한다.
3) 자주 변경되지 않는 컬럼값에 대해 해시키를 적용한다.
4) 클러스터 키를 사용하는 비슷한 검색 조건으로부터 해시 클러스터 인덱스는 인덱스 클러스터보다 훨씬 빠른 성능을 제공한다.
5) 하나의 테이블에는 하나의 해시키만 가질 수 있으므로 가장 많이 조회하거나 중요한 컬럼에 대해 해시키를 지정한다.
6) 해시 알고리즘은 값의 범위로 표시될 때는 사용할 수 없다.
7) 정렬되어 테이블의 데이터를 조회할 때 해시 알고리즘은 이용되지 않는다.
8) 여러 개의 컬럼을 하나의 해시키로 구성하였을 때 만약 일부에 대해서만 비교한다면 해시 알고리즘은 이용되지 않는다.
클러스터링
클러스터링이란 개념은 데이터베이스뿐만 아니라 서버를 구성하거나 소프트웨어를 구성할 때 비슷한 종류의 무엇인가를 묶어준다는 개념이다.
클러스터링의 특징
1) 6블록 이상의 테이블에 적용한다.
2) 인덱스만을 이용하여 처리하라면 데이터의 분포도가 낮은 경우에 적용한다.
3) 일정한 순서로 조회되는 경우가 많을 때 적용한다.
4) 입력, 수정, 삭제가 자주 발생되지 않을 때 적용한다.
5) 클러스터링을 생성한 기준값은 수정되지 않아야 한다.
6) 테이블이 분할되어 있지만, 거의 동시에 조인하여 조회하는 때가 많은 경우에 적용한다.
7) 전체 테이블을 스캔할 경우에 일반적인 테이블보다 저장 공간을 많이 차지하게 되므로 클러스터링을 적용한 테이블의 검색속도가 더 느리다.
클러스터링을 적용하면 안되는 경우
1) 테이블에 대해 전체 스캔이 종종 발생한다면 클러스터링을 적용하지 않는다.
2) 파티셔닝을 적용하면 클러스터링 기능을 사용할 수 없다.
3) 동일한 클러스터 키를 가진 클러스터링된 데이터의 크기가 하나의 블록을 초과할 경우 클러스터링을 적용하지 않는다.
9. 데이터베이스 분산 설계
데이터베이스를 한 곳에 집중시켜 관리하던 중앙 집중식 시스템 환경 구축에서 빨라진 네트워크를 이용하여 데이터베이스를 여러 서버나 지역에 분산시켜 놓고,
하나의 데이터베이스처럼 사용할 수 있게 만든 데이터베이스 체계가 분산 데이터베이스다.
테이블 위치 분산
테이블 위치 분산에서는 테이블의 구조가 변하지 않는다.
또한 테이블이 다른 데이터베이스에 중복되어 생성되지도 않는다.
다만 설계된 테이블의 위치를 각각 다르게 위치시키는 것이다.
테이블 분할 분산
테이블 분할 분산은 단순히 위치만 다른 곳에 두는 것이 아니라 각각의 테이블을 쪼개어 분산하는 방법이다.
- 수평 분할
테이블의 특정 컬럼 값을 기준으로 로우를 분리한다.
- 수직 분할
테이블 컬럼을 기준으로 컬럼을 분리한다.
테이블 복제 분산
테이블 복제 (Replication) 분산은 동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형이다.
- 부분 복제
통합된 테이블을 한 군데 가지고 있으면서 각 지사별로 해당 로우를 가지고 있는 형태다.
- 광역 복제
통합된 테이블을 한 군데 가지고 있으면서 각 지사에서도 본사와 동일한 데이터를 모두 가지고 있는 형태다.
테이블 요약 분산
테이블 요약 (Summarization) 분산은 지역간 또는 서버간에 데이터가 비슷하지만, 서로 다른 유형으로 존재하는 경우다.
- 분석 요약
각 지사별로 존재하는 요약 정보를 본사에 통합하고, 다시 전체에 대해서 요약 정보를 산출하는 분산 방법이다.
- 통합 요약
각 지사별로 존재하는 내용이 다른 정보를 본사에서 통합하여 다시 전체에 대해 요약 정보를 산출하는 분산 방법이다.