(이론) 데이터베이스 모델링(개념적 - 논리적 - 물리적)

 

개념적 데이터베이스 모델링

실체 Entity

업무 수행을 위하여 데이터로 관리 되어져야하는 항목들을 말한다. 
각 실체는 유일하게 식별 가능하며 인스턴스라 불리는 개별적인 객체들의 집합으로 반드시 주 식별자가 존재해야한다. (상호성, 식별성)

속성 Attribute

실체의 세부항목으로 정보의 요소로써 관리 되는 항목이다. 핵심단어로 정의하거나 명명 규칙을 정의하는 것이 좋다.

유형

기초 속성	추출 속성	설계 속성
원래 가지고 있는 속성	기존 속성으로부터의 가공처리를 통해 생성 및 유지되는 속성	실제로 존재하지는 않으나 시스템의 효율성을 도모하기 위해 설계자가 임의로 부여하는 속성. 외래키가 포함되는 경우

 

식별자 Identifier

한 실체 내의 특정 인스턴스를 구분할 수 있는 단일 속성 또는 속성 그룹을 말한다. 한 실체 내의 인스턴스를 유일하게 구분할 수 있어야하며
모든 실체는 반드시 하나 이상의 식별자를 보유하여야 하며 복수개의 식별자도 보유할 수 있다.

종류

식별자	의미
후보 식별자 Candidate Identifier	실체의 각 인스턴스를 유일하게 식별하기 위하여 제공되는 속성이나 속성의 그룹을 말한다.
주 식별자 Primary Identifier	실체의 각 인스턴스를 유일하게 식별하는데 가장 적합한 식별자로 후보 식별자에서 선택한 속성이나 속성의 그룹을 주 식별자로 정의할 수 있다.
부 식별자 Alternate Identifier	후보 식별자 중 주 식별자로 선정되지 않은 식별자를 말한다. (사원 테이블의 주민번호)
외부 식별자 Foreign Identifier	두 실체간의 관계 형성을 통해 부모 실체의 주 식별자가 자식 실체에 전이된 식별자
대리 식별자 Surrogate Key	복합키가 여러개의 컬럼으로 구성된 경우 이를 대체하기 위해 사용되는 인위적으로 정의한 컬럼

 

후보키와 기본키 그리고 대체키의 관계

관계 Relational

두 개의 실체 간에 명명 되어진 의미있는 연결이며 두 실체 간에 업무적인 연관성을 나타낸다.

관계 유형

1) 일대일

A 테이블에 있는 한 레코드는 B 테이블에 있는 한 레코드와 일치하는 관계이다.

2) 일대다

A테이블에 있는 각각의 레코드는 B테이블에 있는 하나 이상의 레코드와 일치하나 B테이블에 있는 레코드는 A테이블에 있는 레코드와 단지 하나 만이 일치하는 관계

3) 다대다

A테이블에 있는 각각의 레코드는 B테이블에 있는 하나 이상의 레코드와 일치하고 B테이블에 있는 레코드도 A테이블에 있는 하나
이상의 레코드와 일치하는 관계

 

선택성

두 실체 간에 관계가 설정되었을 때, 항상 두 실체의 모든 인스턴스 간에 관계가 존재해야 하는지 아니면 모든 인스턴스에 대해
존재할 필요가 없는지를 나타내는 부분

각 부서는 한 명 이상의 사원을 배치 받을지도 모른다. 각 사원은 부서에 반드시 소속되어야 한다.

 

 

논리적 데이터 베이스 모델링

관게형 데이터 모델

기본키 Primary Key 와 외래키 Foreign Key : 부모 테이블의 기본키가 자식테이블의 외래키로 전이 되어진다.

식별관계와 비식별관계

 - 식별 관계 : 부모 테이블의 기본키가 자식 테이블에 기본키 혹은 기본키 그룹의 구성원으로 전이되어진다.
 - 비식별 관계 : 부모테이블의 기본키가 자식 테이블에 일반 속성

 

Mapping Rule

개념적 데이터베이스 모델링에서 얻어진 E-R Diagram 을 관계형 데이터베이스 이론에 입각해서 데이터베이스 스키마로 변환하는 과정

1단계 : 단순 엔티티 -> 테이블로

2단계 : 속성 -> 컬럼으로

3단계 : 식별자 -> 기본키로

4단계 : 관계 -> 포린키로

 

정규화

데이터의 중복을 방지하고 보다 효율적으로 데이터를 저장하기 위함이며 속성들을 본래의 테이블에 정확히 위치시키고자 함이다.

 

제 1정규화

반복되는 속성이나 Group 속성을 제거하고 새로운 실체를 추가한 뒤 기존의 실체와 1:N 관계를 형성한다.

제 2정규화

복합키( Composit Primary Key )로 구성된 경우 해당 테이블 안의 모든 컬럼들은 복합키 전체에 의존적이어야 한다. 만일 복합키 일부에 의존적인 컬럼이 존재한다면 제거해야 한다. 복합키가 아닌 경우 제2정규화의 대상이 되지 않는다.

제 3정규화

테이블 안의 모든 키가 아닌 컬럼들은 기본키(Primary Key)에 의존해야 한다.
키가 아닌 컬럼에 종속되는 속성이 존재한다면 이를 제거해야 한다.

 

특수한 경우의 모델링

상위 실체와 하위 실체

실체는 상보 배타적인 2개 이상의 실체로 분할될 수 있다.  실체는 상호 공통의 속성이나 관계를 갖을 수 있다.

재귀적 관계

자기 자신과 관계를 맺음으로 해서 각 레코드 간의 관계를 정의한다.

 

물리적 데이터 베이스 모델링

논리 데이터베이스 구조로부터 효율적이고 구현 가능한 물리적 구조의 데이터베이스 구조를 설계하는 것

인덱스

데이터베이스 내의 테이블에서 원하는 데이터를 좀 더 빨리 찾아줄 수 있게끔 데이터의 위치 정보를 모아 놓은 데이터베이스 내의 개체(Object)이다. 인덱스는 항상 정렬된 상태를 유지한다.

 

데이터 저장 단위

PAGE	EXTENT
- 데이터베이스에 데이터가 저장되는 최소 단위 - 하나의 페이지 크기는 8kb(8192byte)이다. (한 페이지에 데이터가 저장될 수 있는 최대 사이즈는 8060byte(MySQL기준))	- 테이블이나 인덱스가 저장되기 위해 할당되는 단위 - 8개의 PAGE가 모여 하나의 EXTENT가 된다.

클러스터드 인덱스 (Clustered Index)

- 색인을 만들기 원하는 컬럼에 클러스터드 인덱스를 만들게 되면 그 행(Index Key)을 기준으로 물리적으로 데이터를 정렬
(기본 오름차순, 내림차순)
- 기본적으로 넌 클러스터드 인덱스보다 검색 속도가 빠르며 특히 범위 조회 ( Range Query )를 할 경우 빠른 속도를 나타낸다. 한 테이블에 하나의 클러스터드 인덱스만 만들 수 있다.
- 기본키를 만들면 기본적으로 기본키에 클러스터드 인덱스가 만들어진다.( 기본키에 넌 클러스터드 인덱스를 만들 수도 있다. )

넌 클러스터드 인덱스(Non Clustered Index)

- 인덱스를 만들기 원하는 필드에 넌 클러스터드 인덱스를 만들게 되면 데이터 페이지의 위치 정보를 인덱스로 구성하게 된다. (물리적인 데이터의 위치를 정렬하지 않는다.)
- 데이터 페이지 위에 인덱스 페이지가 위치하게 되며 기본적으로 클러스터드 인덱스보다 검색 속도가 느리며 범위 조회(RangeQuery)를 할 경우 거의 인덱스의 도움을 받을 수 없다

=> 데이터 입력시의 성능은 넌 클러스터드 인덱스가 클러스터드 인덱스보다 좋다.

 

인덱스 정의 시 알아 두어야 하는 기본 사항

기본키(Primary Key)	포린키(Foreign Key)	유니크(Unique) 제약조건
기본키를 설정하면 해당 컬럼에 유니크(Unique)한 클러스터드 인덱스가 기본적으로 만들어 진다.	포린키는 두 테이블간 조인의 조건으로 자주 사용되어지는 컬럼이 지만 기본적으로 인덱스가 만들어지지는 않는다.	유니크 제약조건을 정의하면 해당 컬럼에 유니크(Unique)한 넌 클러스터드 인덱스가 만들어 진다.

인덱스 페이지의 채우기 비율

데이터의 입력, 수정, 삭제가 많은 테이블의 경우 비율을 정의해서 페이지 스플릿(Split)현상을 최소화 하는 것이 좋다.

 

인덱스 적용시 고려사항

- 포린키(Foreign Key)에 인덱스를 부여하는 것을 적극적으로 고려하자.

- 기본키가 두개 이상의 컬럼으로(복합키) 구성된 경우에는 기본키 컬럼의 순서를 고려하자.

- 데이터의 사용량을 분석해야 한다.

- 파일 그룹의 사용

 

트리거 Trigger

자동으로 실행되는 프로시저의 한 형태로서 테이블에 데이터가 입력, 수정, 삭제되어질 때 다른 테이블에 연관된 작업을 정의하기 위한 목적
데이터 무결성 등을 확인하는 용도로도 사용되어 질 수 있다. 트리거는 트랜잭션의 일부로 동작한다.

테이블이나 뷰를 통해 데이터가 입력, 수정, 삭제될 경우 자동으로 실행되어 지기 때문에 연관된 작업을 처리하는데 있어서 여러 번 프로 시저를 호출해서 실행하거나 여러 번 SQL명령을 실행할 필요가 없기 때문에 사용하는 입장에서의 복잡성을 줄일 수 있다.

트리거 정의를 위해 알아 두어야 할 사항

 

- 가상의 논리적인 테이블( INSERTED, DELETED )

테이블이나 뷰는 INSERTED와 DELETED라는 가상의 논리적이 테이블을 가지고 있다. 데이터가 입력 수정 삭제되어질 때 입력, 수정, 삭제되는 데이터는 우선 가상의 논리적인 테이블에 위치하게 되고 그 내용이 테이블에 반영되어 지게 된다.
가상의 논리적인 테이블은 테이블이나 뷰와 같은 구조를 가지며 트랜잭션내에서만 존재하기 때문에 실제로 존재하지는 않는다.

- 데이터 입력 프로세스

테이블이나 뷰에 데이터를 입력하면 입력한 데이터가 테이블에 직접 입력 되어지는 것이 아니고 INSERTED라는 가상의 논리적인 테이블에 데이터가 먼저 들어오고 INSERTED 테이블의 내용이 테이블에 반영되어 진다.

 

- 데이터 삭제 프로세스

테이블이나 뷰를 통해 데이터를 삭제하면 삭제의 대상이 되는 데이터가 DELETED테이블에 옮겨지고 트랜잭션이 끝나면 DELETED테이블도 없어지기 때문에 데이터가 삭제된다.

- 데이터 수정 프로세스

테이블이나 뷰를 통해 데이터를 수정하면 수정의 대상이 되는 데이터가 DELETED테이블에 옮겨지고 새로운 값이 INSERTED테이블에 들어온 다음 테이블에 반영되기 때문에 데이터가 수정된다.

 

트리거의 종류

INSTEAD OF 트리거	AFTER 트리거
실제 작업이 일어나기 전에 동작하는 트리거	실제 작업후에 동작하는 트리거
- 실제 데이터가 입력될 수 없는 뷰를 통해서도 데이터를 입력할 수 있는 장점이 있다. ( 오라클에서는 BEFORE 트리거라고 한다. ) - WITH CHECK OPTION 이 정의된 뷰에 대해서는 INSTEAD OF 트리거를 만들 수 없다.	- 일반적으로 사용되는 트리거의 유형