물리 설계 - 데이터베이스 구축

논리 설계를 물리 설계 즉 관계형 테이블 전환하기 위한 순서

ERD 전환	관계형 테이블 전환, 반정규화, 무결성 제약 정의
물리 설계	트랜잭션 분석, 뷰 설계, 인덱스 설계, 용량 설계, 접근 방법 설계
분산 설계	분산 설계

 

관계형 테이블 전환

 

1. 엔티티타입

- 독립 엔티티타입은 독립 테이블로 전환 : 독립 엔티티타입의 주식별자는 스스로 발생되었기 때문에

* 사원 엔티티타입을 테이블로 변환

- 완전 종속 엔티티타입은 완전 종속 테이블로 전환 : 완전 종속 엔티티타입의 주식별자는 자신의 부모 엔티티타입으로부터 상속받아 발생

완전 종속 관계의 테이블에서는 부모로부터 받은 칼럼값이 반드시 존재해야 한다.

* 발령 엔티티타입과 사원 엔티티타입은 완전 종속 관계이다. 따라서 사원 엔티티타입의 사원번호가 발령 엔티티타입의 기본키로 상속되어 있다.

 

- 부분 종속 엔티티타입은 부분 종속 테이블로 전환 : 자신의 일반 속성은 부모 엔티티타입에서 상속받아 발생

* 사원 테이블에서 부서번호는 일반속성으로 상속받았기 때문에 사용자가 NULL값 제약조건을 설정하지 않으면 NULL값이 올 수 있다. 또한, 사원 테이블의 부서번호는 반드시 부서 테이블에 존재하는 데이터가 있어야 무결성 제약을 위반하지 않는다.

 

2. 주식별자 -> PK

- 데이터 모델의 주식별자는 PK로 전환

- 무결성 제약을 유지하는 역할을 한다

- PK는 테이블에 있는 각각의 로우를 유일하게 식별한다

- PK는 NULL값을 갖지 않는다

- PK는 변경되지 않는다

- 가능하면 모든 테이블에서 PK를 정의한다

 

3. 속성 -> 칼럼

 

4. 관계에 의한 외부 식별자 -> FK

 

---

 

반정규화

 

정규화된 엔티티타입, 속성, 관계를 시스템의 성능 향상, 개발과 운영을 단순화하기 위해 데이터 모델을 통합하는 프로세스를 말함

 

반정규화 절차

1. 반정규화 대상 조사

- 범위 처리 빈도수 조사

- 대량의 범위 처리 조사

- 통계성 프로세스 조사 

- 테이블 조인 개수

 

2. 다른 방법 유도 검토

- 뷰 테이블

- 클러스터링 적용

*클러스터란 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블이 데이터를 디스크의 같은 위치에 저장시키는 방법

- 인덱스의 조정

- 어플리케이션

 

3. 반정규화 적용

- 테이블 반정규화

  1. 테이블 병합 

  2. 테이블 분할 - 수직 분할, 수평 분할

  3. 테이블 추가 - 중복 테이블 추가, 통계 테이블 추가, 이력 테이블 추가, 부분 테이블 추가

 

- 속성의 반정규화

  1. 중복 칼럼 추가 - 해당 테이블에서 자주 사용하는 칼럼인 경우, 데이터 조회를 단축하기 위해

  2. 파생 칼럼 추가

  3. 이력 테이블 칼럼 추가

  4. PK에 의한 칼럼 추가

 

- 관계의 반정규화

 

---

 

무결성 제약 정의

 

1. 입력 참조 무결성 : 각 테이블에 레코드가 생성될 때 자신이 참조하고 있는 테이블의 PK에 대해 데이터의 정합성을 일치시켜 주는 기능

 

- 의존 : 자신의 테이블에 데이터를 입력할 때, 참조하고 있는 테이블의 PK가 존재해야만 데이터 입력이 가능

- 자동 : 자신의 테이블에 데이터를 입력할 때, 참조하고 있는 테이블의 PK가 존재하지 않으면 PK를 생성하고 자신의 테이블에 데이터를 생성

- 기본 : 자신의 테이블에 레코드를 입력할 때 참조하는 테이블의 PK를 기본값으로 바꾼 후 자신의 레코드를 입력

- 지정 : 사용자가 정의해 놓은 일정한 조건을 만족한 이후에 자신의 레코드를 입력

- NULL : 자신 테이블의 레코드를 입력할 때 참조하는 테이블의 PK가 없어도 그대로 입력. FK 컬럼값은 NULL이 된다.

- 미지정 : 자신 테이블의 레코드를 입력할 때 참조하는 테이블의 PK가 없어도 그대로 입력. FK 컬럼값은 NULL이 된다.

 

2. 수정 참조 무결성 : 자신이 참조되는 모든 테이블 FK 정보와 일치를 위해 자신의 PK가 수정되면 관련된 모든 테이블의 FK도 수정하여 데이터의 정합성을 유지

 

- 제한 : 자신의 테이블의 PK를 수정하면 자신을 참조하는 테이블의 FK가 없어야 한다. 테이블의 FK가 존재하면 자신의 테이블의 PK가 수정되지 않는다.

- 연쇄 : 자신의 테이블의 PK를 수정하면 참조되는 모든 테이블의 FK를 수정하고 자신의 PK도 수정

 

3. 삭제 참조 무결성 : 자신이 참조되는 모든 테이블의 FK 정보를 일치시키기 위해 자신의 PK가 삭제될 때 관련된 모든 테이블의 FK를 기본값이나 NULL로 수정하여 데이터의 정합성을 유지

 

- 제한 : 자신의 테이블의 레코드를 삭제하려면 자신을 참조하는 테이블의 레코드가 없어야 한다. 만약 이러한 레코드가 있으면 삭제해서는 안 된다.

- 연쇄 : 자신의 테이블 레코드를 삭제하려면 참조되는 모든 테이블의 레코드를 삭제하고 자신을 삭제

- 기본 : 자신의 테이블의 레코드를 삭제할 때 참조되는 모든 테이블의 레코드를 기본값으로 바꾼 후 자신의 레코드를 삭제

- 지정 : 사용자가 정의해 놓은 일정한 조건을 만족한 이후에 자신의 레코드를 삭제

- NULL : 자신의 테이블의 레코드를 삭제할 때 참조되는 모든 테이블의 레코드를 NULL로 바꾼 후 자신의 레코드를 삭제

 

---

 

트랜잭션

 

데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위

 

데이터베이스의 상태를 변화시킨다?? -> SELECT, INSERT, DELETE, UPDATE 쿼리를 사용하여 데이터베이스에 접근하는 것을 말함.

 

트랜잭션의 특징

1. 원자성 - 트랜잭션이 데이터베이스에 모두 반영되던가, 전혀 반영되지 않아야 한다

2. 일관성 - 트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 한다

3. 독립성 - 트랜잭션이 수행하고 있는 중 다른 트랜잭션이 끼어들 수 없다

4. 지속성 - 트랜잭션이 성공적으로 완료된 경우, 결과는 영구적으로 반영되어야 한다

 

---

 

뷰 설계

 

뷰란 데이터베이스 사용자들이 접근하도록 테이블을 이용하여 가상 테이블을 구성하는 방법을 말한다.

 

뷰의 특징 )

- 복잡한 테이블 구조를 단순화 한다 

  *여러 개의 테이블에 대해 동일한 SQL 문장이 반복적으로 조인되는 경우 뷰를 통해 관리한다.

- 다양한 관점에서 데이터를 제시할 수 있다.

- 데이터의 보안을 유지한다.

- 논리적인 데이터 독립성을 제공

  *논리적인 데이터 독립성이란, 데이터베이스에서 테이블 구조가 바뀌어도 뷰가 영향을 받이 않는것을 말함

- 데이터를 조회하는 기술에 제한이 없다.

- 뷰에는 인덱스, 클러스터링, 해시 클러스터 등을 지정할 수 없다.

- 테이블에 뷰를 이용하여 적절한 테이블 접근 방법을 유도할 수 있다.

 

** 뷰의 장점 - 데이터의 중복을 거부한다.

 

---

 

인덱스 설계

 

인덱스란 테이블의 로우를 식별할 수 있도록 칼럼값과 그 값을 포함하는 로우의 논리적인 주소를 별도의 저장 구조를 만들어 저장하는 것을 말한다. <테이블에 대한 동작의 속도를 높여주는 자료구조>

 

** PK는 반드시 인덱스를 생성하는 것이 바람직하다.

 

인덱스 생성과 삭제

 

아래와 같은 테이블이 있다고 가정하자.

CREATE TABLE SAMPLE_INDEX (

         

       ID VARCHAR2(20) PRIMARY KEY,

       NAME VARCHAR(10)

);

 

인덱스 생성

CREATE INDEX IDX ON SAMPLE_INDEX( ID );

-> IDX는 인덱스 명으로 사용자가 임의로 정한다.

 

인덱스 삭제

DROP INDEX IDX(인덱스명);

 

---

 

접근 방법 설계

 

직접 접근 - 스캔

간접 접근 - B 트리 인덱스, 비트맵 인덱스, 해싱

저장 방법에 따른 접근 - 클러스터링

 

스캔 방식

- 테이블에 있는 데이터를 검색할 때 순차적으로 로우를 비교하여 원하는 데이터를 가져오는 방법

 

B 트리 인덱스

리프 블록 - 테이블의 각 레코드의 인덱스 정보를 가지고 있는 블록. 테이블 정보에 조회, 입력, 수정, 삭제가 발생할 때 관련 인덱스가 리프 블록 내에 조회, 입력, 수정, 삭제를 발생시킴. 

브랜치 블록 - 리프 블록과 루트 블록의 중간에서 블록 사이의 정보에 대한 다리 역할을 하는 블록. 

루트 블록 - 트리의 최상위 수준에 위치하며 조회, 입력, 수정, 삭제가 발생하면 제일 먼저 접근된다.

검색 원리 

- 루트로부터 리프 블록까지 이루어지며, 해당 테이블 레코드값이 필요하면 리프 블록의 레코드 로우ID를 이용하여 해당 테이블에서 레코드를 읽어옴.

 

 

비트맵 인덱스

칼럼 정보를 0과 1을 이용하여 별도의 인덱스로 저장하는 방법. WHERE 조건의 AND나 OR 연산에 의해 데이터를 검색하는 방법

 

B 트리 인덱스와 비트맵 인덱스 비교

항목	B 트리(OLTP)	비트맵(DSS)
인덱스의 검색 속도	- B 트리 알고리즘이 필요로하는 인덱스값만 읽을 수 있다. - 인덱스 크기가 크므로 상대적으로 소량의 데이터를 검색할 때 유리하다.	- 항상 전체 인덱스를 읽는다. - 인덱스의 크기가 작으므로 대량의 데이터를 읽을 때 유리하다.
인덱스 크기	- 인덱스 크기가 비트맵 인덱스보다 크다. - 비트맵 인덱스보다 크기에서 백배 이상 차이날 수 있다.	- 인덱스 크기가 매우 작다.
인덱스의  변경 관리성	- B 트리 인덱스는 트리 알고리즘에 의해 손쉽게 인덱스 입력, 수정, 삭제가 가능하다.	- 비트맵 인덱스의 입력, 수정, 삭제는 전체 인덱스를 조정해야 하는 부담이 있다. - 인덱스 재생성과 비슷하다.
필요한 스토리지	- B 트리 인덱스는 인덱스 트리를 관리하기 위해 많은 스토리지가 요구된다.	- 비트맵 인덱스는 비트 연산으로 관리하므로 작은 스토리지가 요구된다.
연산 능력	- B 트리 인덱스는 AND 연산에는 좋은 성능을 나타내나 OR, != 등에서 성능에 취약하다.	- 비트맵 인덱스는 비트 연산을 통해 빠르게 처리할 수 있다.
분포도	- 데이터 분포도가 높은 칼럼에 적합하다.	- 데이터 분포도가 아주 낮은 칼럼에 적합하다.

 

OLTP - 업무 처리를 위한 시스템을 말한다.

<소량의 정보를 읽고, 빈번한 입력, 수정, 삭제가 발생>

 

DSS - 업무 데이터를 이용하여 기업의 의사 결정에 이용하는 시스템

<대량의 정보를 읽고 입력, 수정, 삭제가 거의 없다>

 

클러스터링 

- 데이터를 저장할 때 빠른 접근을 위해 데이터를 동일한 데이터 블록에 저장하는 것을 말한다.

 

 

 

 

 

 

 

 

[출처 : 데이터베이스 설계와 구축 - 성능까지 고려한 데이터 모델링, 한빛미디어, 저자 이춘식]