:: 작업장

[SQLD] 2과목 SQL 기본및 활용 - 1장 1절 관계형 데이터베이스 개요

1. 데이터베이스

- 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것.

* 데이터베이스의 발전

- 1960년대: 플로우 차트 중심의 개발 방법을 사용하였으며 파일 구조를 통해 데이터를 저장하고 관리하였다.

- 1970년대: 데이터베이스 관리기법이 처음 태동되던 시기였으며 계층형 데이터베이스, 망형 데이터베이스 같은 제품들이 상용화 되었다.

- 1980년대: 현재 대부분의 기업에서 사용되고있는 관계형 데이터베이스가 상용화 되었으며 Oracle, Sybase, DB2와 같은 제품이 있다.

- 1990년대: Oracle, Sybase, Informix, DB2, Teradata, SQL Server외 많은 제품들이 보다 향상된 기능으로 정보시스템의 확실한 핵심 솔루션으로 자리잡게 되었으며 인터넷 환경의 급속한 발전과 객체지향 정보를 지원하기 위해 객체 관계형 데이터베이스로 발전하게되었다.

* 관계형 데이터베이스

- 관계형 데이터베이스는 정규화를 통한 합리적인 테이블 모델링을 통해 이상 현상을 제거하고 데이터중복을 피할 수 있으며 동시성관리, 병행제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공한다.

- 관계형 데이터베이스는 메타 데이터를 총괄 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있고, 데이터 표준화를 통한 데이터 품질을 확보할 수 있는 장점을 가지고 있다.

- 테이블 생성시에 사용할 수 있는 다양한 제약조건을 이용하여 사용자가 실수로 조건에 위배되는 데이터를 입력한다든지 관계를 연결하는 중요한 데이터를 삭제하는 것을 방지하여 무결성을 보장할 수 있다.

2. SQL

- 관계형 데이터베이스에서 데이터 정의 데이터 조작, 데이터 제어를 하기위해 사용하는 언어.

3. TABLE

- 데이터는 관계형 데이터베이스에서 테이블 형태로 저장된다.

- 모든 자료는 테이블에 등록되고 우리는 테이블로부터 원하는 자료를 꺼내올 수 있다.

- 테이블은 어느 특정한 주제와 목적으로 만들어지는 일종의 집합이다.

- 테이블은 데이터를 저장하는 객체로서 관계형 데이터베이스의 기본단위이다.

- 세로방향은 컬럼, 가로방향은 로우 라고하며 칼럼과 행이 겹치는 부분을 필드라고한다.

- 선수와 관련된 데이터는 선수테이블과 구단 테이블 이라는 복수의 테이블로 분할하여 저장한다.

분할된 테이블은 그 칼럼의 값에 의해 결정된다.  이렇게 테이블을 분할하여 데이터의 불필요하는 중복을 줄이는 것을 정규화라고 한다.

- 각 행을 한가지 의미로 특정할 수 있는 한 개 이상의 칼럼을 기본키(Primary Key)라고 하며 다른 테이블의 기본 키로 사용되면서 테이블과의 관계를 연결하는 역할을 하는 칼럼을 외부키라고 한다.

4.  ERD

- ERD의 구성요소는 엔터티, 관계, 속성 3가지 이며 이 3가지 구성요소로 모두 표현이 가능하다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 6절:분산 데이터베이스와 성능

1. 분산 데이터베이스의 개요.

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

- 논리적으로 동일한 시스템에 속하지만, 컴퓨터 네트워크를 통해 물리적으로 분산되어있는 데이터들의 모임, 물리적 SITE 분산, 논리적으로 사용자 통합 공유.

=> 데이터베이스를 연결하는 빠른 네트워크 환경을 이용하여 데이터베이스를 여러 지역 여러 노드로 위치시켜 사용성/성능을 극대화 시킨 데이터베이스.

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

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

1) 분할투명성(단편화) : 하나의 논리적인 Relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 site에 저장.

2) 위치 투명성 : 사용하려는 데이터의 저장 장소 명시 불필요, 위치정보가 system catalog에 유지되어야함.

3) 지역사상 투명성 : 지역 dbms와 물리적 db 사이의 mapping 보장. 각 지역시스템 이름과 무관한 이름 사용가능.

4) 중복 투명성 : DB객체가 여러 site에 중복되어있는지 알 필요가 없는 성질.

5) 장애 투명성 : 구성요소(DBMS, Computer)의 장애에 무관한 Transaction의 원자성 유지

6) 병행 투명성 : 다수 Transaction 동시 수행시 결과의 일관성 유지. Time stamp, 분산2단계 Locking을 이용해서 구성.

3. 분산데이터베이스의 적용방법 및 장단점.

가. 분산데이터베이스의 적용방법

- 업무의 흐름을 보고 업무 구성에 따른 아키텍쳐 특징에 따라 데이터베이스를 구성.

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

4. 분산 데이터베이스의 활용 방향성

- 위치 중심의 분산설계

- 업무필요에 의한 분산설계

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

- 통합데이터베이스에서 제공할 수 없는 빠른 성능을 제공.

- 원거리 또는 다른 서버에 접속하여 처리하므로 인해 발생되는 네트워크 부하 및 트랜잭션 집중에 따른 성능저하이 원인을 분산데이터베이스 환경을 구축하므로 빠른 성능을 제공하는것이 가능해진다.

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

가. 테이블 위치 분산

- 테이블 위치분산은 테이블의 구조는 변하지 않는다.

- 다른 데이터베이스에 중복되어 생성되지도 않는다

- 테이블별 위치 분산은 정보를 이용하는 형태가 각 위치별로 차이가 있을 경우에 이용한다.

- 테이블의 위치가 위치별로 다르므로 테이블의 위치를 파악할 수 있는 도식화된 데이터베이스 문서가 필요하다.

나. 테이블 분할 분산

- 테이블 분할 분산은 위치만 다른곳에 놓는것이 아니라 각각의 테이블을 쪼개어 분산하는 방법이다.

- 테이블을 분할하여 분산하는 방법은 테이블을 나누는 기준에 따라 두 가지로 구분된다.

1) 수평분할

- 테이블의 로우단위로 분할.

- 칼럼은 분리하지 않는다.

- 모든데이터가 각 지사별로 분리되어 있는 형태를 가진다.

- 각 지사에 있는 데이터와 다른지사에 있는 데이터와 항상 배타적으로 존재하며 한군데 집합시켜도 Primary key에 의해 중복이 발생하지않는다.

- 수평분할을 이용하는 경우는 각 지사별로 사용하는 Row가 다를 때 사용한다.

- 데이터를 수정할 때는 타 지사에 있는 데이터를 원칙적으로 수정하지 않고 자신의 데이터에 대해서 수정하도록한다.

- 각지사에 존재하는 테이블에 대해서 통합처리를 해야하는 경우 조인이 발생하게 되므로 성능저하가 예상된다. 그러므로 통합처리 프로세스가 많은지 검토 후 많지않은 경우에 수평분할을 한다.

2)수직분할

- 지사에 따라 칼럼을 기준으로 칼럼을 분리한다. 로우단위로는 분리되지 않는다.

- 모든데이터가 각 지사별로 분리되어 있는 형태를 가지고 있다.

- 칼럼을 기준으로 분할 하였기 때문에 각각의 테이블에는 동일한 Primary key 구조와 값을 가지고 있어야한다.

- 통합하여 처리해야하는 프로세스가 많을 경우 이용하지않는다.

다. 테이블 복제 분산

- 테이블 복제 분산은 동일한 테이블을 다른 지역이나 서버에 동시에 생성하여 관리하는 유형이다.

- 마스터데이터베이스에서 테이블의 일부의 내용만 다른 지역이나 서버에 위치시키는 부분복제가 있고, 마스터 데이터베이스의 내용을 각 지역이나 서버에 존재시키는 광역복제가 있다.

1) 부분복제

- 통합된 테이블을 한군데에 가지고 있으면서 각 지사별로는 지사에 해당된 로우를 가지고 있는 형태이다.

- 지사에 존재하는 데이터는 반드시 본사에 존재하게된다.

- 지사에서 데이터 처리가 용이할 뿐만 아니라 전체 테이터에 대한 통합처리도 본사에 있는 통합테이블을 이용하게 되므로 조인이 발생하지 않는 빠른 작업수행이 가능하다.

- 지사간에는 데이터 중복이 발생되지 않으나, 지사와 본사 간에는 데이터 중복이 항상 발생하게된다.

- 야간에 배치작업에 의해 데이터 복제를 한다.

- 본사와 지사 양쪽 모두 데이터를 수정하여 전송하는 경우 데이터 정합성을 일치시키는게 어렵기 때문에 가능하면 지사에서 데이터 수정이 발생하여 본사로 복제하도록 한다.

2) 광역복제

- 통합된 테이블을 본사에서 가지고 있으면서 각 지사에도 본사와 동일한 데이터를 모두 가지고 있는 형태이다.

- 지사에 존재하는 데이터는 반드시 본사에 존재하게 된다.

- 모든 지사의 데이터양과 본사에 있는 데이터 양은 동일하다.

- 부분복제의 경우 지사에서 데이터에 대한 입력,수정,삭제가 발생하여 본사에 이용하는 방식이 많은 반면에 광역복제의 경우에는 본사에서 데이터가 입력 수정 삭제가 발생되어 지사에서 이용하는 형태가 많다.

- 데이터를 복제하는데 많은 시간이 소요되므로 배치에 의해 복제되도록 한다.

라. 테이블 요약 분산

- 테이블 요약 분산은 지역간에 또는 서버간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우가 있다.

요약 방식에 따라 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식의 분산석요약과 분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식의 통합요약이 있다.

1) 분석요약

- 각 지사별로 존재하는 요약정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법이다.

- 통합 통계데이터에 대한 정보제공에 용이한 분산방법이다.

2)통합요약

- 각 지사별로 존재하는 다른 내용의 정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법이다.

- 통합요약은 단지 지사에서 산출한 요약정보를 한군데 취합하여 보여주는 형태이다. 분석요약은 지사에 있는 데이터를 이용하여 본사에서 통합하여 요약데이터를 산정하지만 통합요약에서는 자사에서 요약한 정보를 취합하여 각 지사별로 데이터를 비교하기위해 있는것이다.

* 각종 통계데이터를 산정할 경우에 모든 지사의 데이터를 조인하여 처리하면 성능이 저하되고 각 지사 서버에 부하를 주기 때문에 장애가 발생할 수 있다.  본사에 통합 요약된 테이블을 생성하고 데이터는 야간에 수행하여 생성하는것이 일반적인 적용방법이다.

7. 분산 데이터베이스를 적용하여 성능이 향상된 사례

- 복제분산의 원리를 이용하면 성능을 향상시켜 설계할 수 있다.

- 트랜잭션 개별적으로 원격지 조인 -> 트랜잭션 내부조인(배치이동)

*분산데이터베이스 설계는 다음경우에 이용하면 효과적이다.

1) 성능이 중요한 사이트에 적용.

2) 공통코드, 기준정보, 마스터데이터 등에 대해 분산환경을 구성하면 성능이 좋아진다.

3) 실시간 동기화가 요구되지 않을 때 좋다.

4) 특정 서버에 부하가 집중될 때 부하를 분산할 때도 좋다.

5) 백업 사이트를 구성할 때 간단하게 분산기능을 적용하여 구성할 수 있다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능

1. 슈퍼타입/ 서브타입 모델의 성능고려 방법.

가. 슈퍼/서브타입 데이터 모델의 개요.

- Extended ER모델이라고 부르는 슈파타입/서브타입 데이터 모델은 최근에 데이터 모델링을 할 때 자주 쓰이는 모델링 방법이다.

- 업무를 구성하는 데이터의 특징을 공통점과 차이점의 특징을 고려하여 효과적으로 표현할 수 있기 때문.

- 공통의 부분을 슈퍼타입으로 모델링하고 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분하여 업무의 모습을 정확하게 표현하면서 물리적인 데이터 모델로 변환을 할 때 선택의 폭을 넓힐 수 있는 장점이 있다.

- 슈퍼/서브타입의 데이터 모델은 논리적 데이터 모델에서 이용되는 형태이며 분석/설계단계를 구분하자면 분석단계에서 많이 쓰이는 모델이다.

- 물리적인 데이터모델을 설계하는 단계에서는 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환해야한다.

- 물리적인 데이터모델이 성능을 고려한 데이터 모델이 되어야한다는 점을 고려하면 이렇게 막연하게 슈퍼/서브타입을 아무런 기준없이 변환하는것 자체가 성능이 저하될 수 있음을 기억해야한다.

나. 슈퍼/서브타입 데이터 모델의 변환

- 슈퍼 서브타입에 대한 변환을 잘못하여 성능이 저하되는 경우.

1) 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 UNION 연산에 의해 성능이 저하될 수 있다.

2) 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우가 있다.

3) 트랜잭션은 항상 슈퍼+서브타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우가 있다.

* 해당 테이블에 발생되는 성능이 중요한 트랜잭션이 빈번하게 처리되는 기준에 따라 테이블을 설계해야 성능저하 현상을 예방할 수 있다.

* 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터 양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라 결정된다.

* 데이터의 양은 소량일 경우 데이터 처리의 유연성을 고려하여 가급적 1:1관계를 유지하는것이 바랍직하다.

* 데이터의 양이 많을 경우 그리고 해당 업무적인 특징이 성능에 민감한 경우에는 트랜잭션이 해당 테이블에 어떻게 발생되는지에 따라 3가지 변환방법을 참조하여 변환해야한다.

다. 슈퍼/서브 타입 데이터 모델의 변환 기술.

- 논리적인 데이터 모델에서 설계한 슈퍼타입/서브타입 모델을 물리적인 데이터 모델로 전환할 때 주로 어떤 유형의 트랜잭션이 발생하는지 검증해야한다.

- 데이터양이 많이 존재하고 지속적으로 증가하는 양도 많다면 슈퍼타입/서브타입에 대해 물리적인 데이터모델로 변환하는 세 가지 유형에 대해 세심하게 적용해야한다.

1) 개별로 발생하는 트랜잭션에 대해서는 개별 테이블로 구성.

- 슈퍼타입과 서브타입 각각에 대해 독립적으로 트랜잭션이 발생이 되면 슈퍼타입에도 꼭 필요한 속성만 가지게 하고 서브타입에도 꼭 필요한 속성만 가지게 하기 위해서 모두 분리하여 1:1 관계를 갖도록 한다.

- 실전프로젝트에서는 데이터양이 대용량으로 존재하는 경우에 공통으로 이용하는 슈퍼타입의 속성의 수가 너무 많아져 디스크 I/O가 많아지는 것을 방지하기 위해 각각을 1:1 관계로 가져가는 경우도 있다.

2) 슈퍼타입+ 서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성

-  만약 대리인이 10만건 매수인 500만건 이해관계인 500만건의 데이터가 존재할 때, 슈퍼타입과 서브타입이 모두 하나의 테이블로 구성되어있을 경우 매수인 이해관계인에 대한 정보는 배제하고 10만건인 대리엔이데핸 데이터만 처리할 경우 다른테이블과 같이 1010만건 데이터가 저장되어있는 곳에서 처리하므로 불필요한 성능저하가 발생하게된다.

=>  이처럼 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무특징을 가지고 있을 때에는 슈퍼타입+서브타입을 각각 하나로 묶어 별도의 테이블로 구성하는것이 효율적이다.

3) 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성.

- 대리인이 10만건, 매수인이 500만건, 이해관계인 500만건의 데이터가 존재한다고 하더라도 데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리한다고 하면 테이블을 개별로 분리하면  불필요한 조인을 유발하거나 불필요한 UNION ALL과 같은 SQL 구문이 작성되어 성능이 저하된다. 비록 슈퍼타입과 서브타입의 테이블을 하나로 묶었을 때 각각의 속성별로 제약사항을 정확하기 지정하지 못할 지라도 대용량이고 성능향상에 필요하다면 하나의 테이블로 묶어서 만들어준다.

라. 슈퍼/서브타입 데이터 모델의 변환타입 비교.

2. 인덱스 특성을 고려한 PK/FK 데이터베이스 향상.

가. PK/FK 칼럼순서와 성능개요.

- 데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근경로를 제공하는 오브젝트가 인덱스이다.

- 일반적으로 데이터베이스 테이블에서는 균형잡힌 B*Tree 구조를 많이 사용한다.

- 테이블에 발생되는 트랜잭션의 조회패턴에 따라 PK/FK 칼럼의 순서를 조정해야한다.

- PK는 해당 테이블의 데이터에 접근할 때 가장 빈번하게 사용되는 유일한 인덱스를 모두 자동생성한다.

- PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK순서를 지정해야한다.

* 여러개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 낼 수 있다.

* 앞쪽에 위치한 속성값이 가급적 '=' 아니면 최소한 범위 BEETWEEN, < > 가 들어와야 인덱스를 이용할 수 있다.

* 데이터 모델링 때 결정한 PK 순서와는 다르게 DDL 문장을 통해 PK 순서를 다르게 생성할 수 있다.

(하지만 보통 데이터 모델의 PK 순서에 따라 그대로 PK를 생성한다.)

* FK라고 하더라도 데이터를 조회할 때 조인의 경로를 제공하는 역할을 수행하므로 FK에 대해서는 반드시 인덱스를 생성하도록 하며 인덱스 칼럼의 순서도 조회의 조건을 고려하여 접근이 가장 효율적인 칼럼 순서대로 인덱스를 생성하도록 주의한다.

나. PK칼럼의 순서를 조정하지 않으면 성능이 저하되는 이유.

- 먼저 데이터 모델링에서 엔터티를 설계하면 그에 때라 DDL이 생성이 되고 생성된 DDL에 따라 인덱스가 생성된다.

- PK순서에 따라 DDL이 그대로 생성되고, 테이블의 주문번호가 가장먼저 정렬되고, 주문일자가 정렬되고 주문번호코드가 정렬이된다.

 1) 맨앞에 있는 인덱스 칼럼에 조회조건이 들어올 경우

SELECT 주문단가

FROM 주문목록

WHERE 주문번호 = '1002';

=> 인덱스에 정렬된 첫 번째 칼럼에 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다.

2) 맨 앞에 있는 칼럼이 제외된 상태에서 데이터를 조회할 경우 데이터 비교버무이가 넓어지게되어 성능저하를 유발한다.

SELECT 주문단가

FROM 주문목록

WHERE 주문일자='2015.08.23';

=> 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 다 읽은 후 원하는 데이터를 찾게된다. 이러한 이유로 인덱스를 읽고 데이터 블록에서 읽어 처리하는데 IO가 많이 발생하므로 옵티마이저는 차라리 테이블 전체를 읽는 방식으로 처리한다.

* PK의 순서를 인덱스 특징에 맞게 고려하지 않게 생성하게되면 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있으므로 인덱스의 범위를 넓게 이용하거나 FULL SCAN을 유발하게 되어 성능이 저하된다고 할 수 있다.

다. PK순서를 잘못 지정하여 성능이 저하된 경우. - 간단한 오류

- 입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터의 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어있는 복합식별자 구조의 테이블이다.

- 입시마스터에는 200만 건의 데이터가 들어가있고 학사는 4학기로 구성되어 있고 데이터는 5년간 보관되어 한학기당 평균 2만 건의 데이터가 있을 때.

- 성능저하쿼리

=> 이 경우 입시마스터_I01인덱스가 수험번호+년도+학기 중 수험번호에 해당하는 값이 WHERE절에 들어오지 않으므로 FULL TABLE SCAN이 발생하게된다.

 * 입시마스터 테이블 조회시 년도와 학기가 빈번하게 들어오므로 PK순서를 변경함으로써 인덱스를 사용할 수 있도록 할 수 있다.

- PK 순서 변경 

라. PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류.

- 현금출급기의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어있는데, 대부분 SQL 문장에서 조회를 할 때 사무소코드가 '='로 들어오고 거래일자에 대해서는 BETWEEN 조회를 하고있다.

이 때 SQL은 정상적으로 인덱스를 사용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당한다.

=> 거래일자 + 사무소코드로 구성된 인덱스 이기 때문에 거래일자 범위때문에 조회범위가 넓어져서 성능저하가 발생한다.

 인덱스 구성을 사무소코드 + 거래일자로 구성하면 '='비교를 사용하므로써 범위가 좁혀질 수 있다.

* A+B 형식의 조회와 B+A 형식의 조회도 빈번하게 일어날 경우에는 좀 더 자주 이용되는 조회형태로 PK를 구성하고 순서를 바꾼 인덱스를 추가로 생성하는것이 좋다.

3. 물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하.

- 물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 의해 상속받은 FK 속성들은 SQL WHERE절에서 조인이 이용되는 경우가 많이 있으므로 FK 인덱스를 생성해야 성능이 좋은 경우가 빈번하다.

- 학사기준과 수강신청에 대한 데이터모델에서 물리적인 테이블에는 두 테이블 사이에 FK 참조무결성 관계가 걸려있지 않을 때 학사기준 데이터에는 5만건의 데이터가있고 수강신청에 데이터가 500만건이 있다고 가정하면.

=> FK 인덱스 미생성으로 FULL TABLE스캔이 발생하여 성능 저하가 생긴다.

- 학사기준번호가 SQL절에 비교자로 들어오지는 않지만 수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 인해 학사기준과 수강신청 테이블이 조인이 되면서 500만건의 수강신청 테이블이 FULL SCAN이 발생하며 성능이 저하되었다.

- 이때는 수강신청 테이블에 FK인덱스를 생성하여 성능을 개선할 수 있다.

CREATE INDEX 수강신청_FK01 ON 수강신청(학사기준번호)

* 물리적으로 학사기준과 수강신청이 연결되어 있지 않다고 하더라도 학사기준으로부터 상속받은 FK에 대해 FK인덱스를 생성함으로써 SQL문장이 조인이 발생할 때 성능저하를 예방할 수 있다.

* 물리적인 테이블에 FK제약을 걸었을 때는 반드시 FK 인덱스를 생성하도록 하고 FK 제약이 걸리지 않았을 경우에는 FK인덱스를 생성하는것을 기본 정책으로 하되 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때에만 FK 인덱스를 지우는 방법으로 하는것이 적절한 방법이다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 4절:대량 데이터에 따른 성능

1. 대량 데이터발생에 따른 테이블 분할 개요.

- 일처의 처리량이 한군데에 몰리는 현상은 어떤 업무에 있어서 중요한 업무에 해당되는 데이터가 특정 테이블에 있는 경우에 발생이 되는데 이런 경우 트랜잭션이 분산 처리될 수 있도록 테이블단위에서 분할의 방법을 적용할 필요가 있다.

- 대량의 데이터가 존재하는 테이블에 많은 트랜잭션이 발생하여 성능이 저하되는 테이블 구조에 대해 수평/수직 분할 설계를 통해 성능저하를 예방할 수 있음.

- 수평분할 : 칼럼단위로 분할하여 I/O 경감.

- 수직분할 : 로우단위로 분할하여 I/O 경감.

하나의 테이블에 대량의 데이터가 존재하는 경우에는 인덱스의 Tree구조가 너무 커져 효율성이 떨어져 데이터를 처리(입력,수정,삭제,조회) 할 때 디스크 I/O를 많이 유발하게 된다.

하나의 테이블에 많은 수의 컬럼이 존재하면 데이터가 디스크의 여러 블록에 존재하므로 디스크에서 데이터를 읽는 IO량이 많아지게되어 성능이 저하되게 된다.

- 조회조건에 따른 인덱스를 적절하게 이용하면 해당 테이블에 데이터가 아무리 많아도 원하는 데이터만 접근하기 때문에 I/O의 양이 그다지 증가하지 않을것으로 생각할 수 있으나 대량의 데이터가 하나의 테이블에 존재하게 되면 인덱스를 생성할 때 인덱스의 크기가 커지게 되고 그렇게되면 인덱스를 찾아가는 단계가 깊어지게 되어 조회성능에 영향을 미치게된다.

- 칼럼이 많아지게되면 물리적인 디스크에 여러 블록에 데이터가 저장되게 되어 데이터를 처리할 때 여러블록에서 I/O를 해야하는 SQL문장의 성능이 저하될 수 있다.

- 칼럼이 많을경우 로우체이닝과 로우마이그레이션이 많아지게되어 성능이 저하된다.

* 로우체이닝: 로우 길이가 너무 길어서 데이터블록 하나에 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우형태가 저장되어있는 형태.

* 로우 마이그레이션: 특정 데이터 블록에서 수정이 발생하게되면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈공간을 찾아 저장하는 방식.

=> 로우체이닝과 로우마이그레이션이 발생하여 많은 블록에 데이터가 저장되면 데이터베이스 메모리에서 디스크와 I/O가 발생할 때 불필요하게 많은 I/O가 발생하여 성능이 저하된다.

2. 한 테이블에 많은 수의 컬럼을 가지고 있을 경우.

- 많은 칼럼을 가지고 있는 테이블에 대해서 트랜잭션이 발생될 때 어떤 칼럼에 대해 집중적으로 발생하는지 분석하여 테이블을 쪼개어 주면 디스크 I/O가 감소하게 되어 성능이 개선된다.

- 분리된 테이블은 디스크에 적어진 칼럼이 저장되므로 로우 마이그레이션과 로우체이닝이 많이 줄어들 수 있다.

3. 대량 데이터 저장 및 처리로 인한 성능.

- 테이블에 많은 양의 데이터가 예상될 경우 파티셔닝을 적용하거나 PK에 의해 테이블을 분할하는 방법을 적용할 수있다.

- 오라클은 LIST PARTITION(특정값 지정), RANGE PARTITION(범위), HASH PARTITION(해쉬적용), COMPOSITE PARTITION(범위와 해쉬가 복합) 등이 가능하다.

- 데이터양이 몇 천만 건을 넘어서면 논리적으로는 하나의 테이블로 보이지만 물리적으로는 여러 개의 테이블스페이스에 쪼개어 저장될 수 있는 구조의 파티셔닝을 적용하도록 한다.

가. RANGE PARTITOIN 적용

- 요금 테이블에 PK가 요금일자+유금번호로 구성되어있고 데이터 건수가 1억2천만 건인 대용량 테이블의 경우 하나의 테이블로는 너무 많은 데이터가 존재하므로 성능이 느려진다. 이 때 요금의 특성상 월 단위로 데이터를 처리하는 경우가 많으므로 PK인 요금일자의 년+월을 이용하여 12개의 파티션 테이블을 만들게되면 SQL 문장을 처리할 때는 마치 하나의 테이블처럼 보이는 요금테이블을 이용하여 처리되지만 DBMS내부적으로는 SQL WHERE 절에 비교된 요금일자에 의해 각 파티션에 있는 정보를 찾아가므로 평균 10000만 건의 데이터가 있는 곳을 찾아도 되어 성능이 향상될 수 있다.

- 가장 많이 사용되는 파티셔닝의 기준으로 대상 테이블이 날자 또는 숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리된다면 RANGE PARTITOIN을 적용한다. 또한 데이터 보관주기에 따라 테이블에 데이터를 쉽게 지우는 것이 가능하므로 테이블 관리가 용이하다.

나. LIST PARTITION 적용

- 지점, 사업소, 사업장, 핵심적인 코드값 등으로 PK가 구성되어 있고 대량의 데이터가 있는 테이블이라면 값 각각에 의해 파티셔닝 되는 LIST PARTITION을 적용할 수 있다.

- 리스트 파티션은 대용량 데이터를 특정 값에 따라 분리 저장할 수는 있으나 RANGE PARTITION과 같이 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공될 수 없다.

다. HASH PARTITION 적용

- 기타  HASH PARTITION은 지정된 HASH 조건에 따라 해싱 알고리즘이 적용되어 테이블이 분리되며 설계자는 테이블에 데이터가 정확하게 어떻게 들어갔는지 알 수 없다.

- 성능 향상을 위해 사용하며 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공할 수 없다.

4. 테이블에 대한 수평분할/수직분할 절차

- 테이블에 대한 수평분할/수직분할에 대한 결정은 다음의 4가지 원칙을 적용한다.

1)데이터 모델링을 완성한다.

2)데이터베이스 용량을 산정한다.

3) 대량 데이터가 처리되는 테이블에 대해서 트랜잭션 처리 패턴을 분석한다.

4) 컬럼단위로 집중화된 처리가 발생하는지 로우단위로 집중화된 처리가 발생하는지 분석하여 집중화된 단위로 테이블 분리하는것을 검토한다.

* 칼럼의 수가 많은경우 트랜잭션의 특성에 따라 1:1 형태로 분리할 수 있는지 검증하면된다.

* 칼럼의 수가 적지만 데이터 용량이 많아 성능저하가 예상되는 경우 테이블에 대해 파티셔닝 전략을 고려하도록 한다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 3절:반정규화와 성능

1.반정규화를 통한 성능향상 전략.

가. 반정규화의 정의

- 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터모델링의 기법을 말한다.

- 협의의 반정규화는 데이터를 중복하여 성능을 향상시키기 위한 기법이라고 정의할 수 있고 좀 더 넓은 의미의 반정규화는 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리등을 수행하는 모든 과정을 말한다.

- 데이터를 중복하여 반정규화를 적용하는 이유는 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산하여 읽을 때 성닝이 저하될 것이 예상되는 경우 반정규화를 수행한다.

- 프로젝트에서는 설계 단계에서 반정규화를 적용하게 된다.

* 반정규화를 기술적으로 수행하지 않는 경우

1) 성능이 저하된 데이터베이스가 생성될 수 있다.

2) 구축단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 더 많이 들게된다.

나. 반정규화의 적용방법.

-  개발을 하다가 SQL문장 작성이 복잡해지고 그에 따라  SQL 단위 성능 저하가 예상되어 다른 테이블에서 조인하여 가져와야 할 칼럼을 기준이 되는 테이블에 중복하여 SQL문장을 단순하게 처리하도록 하기위해 요청하는 경우가 많다. 

 (무분별하게 칼럼의 반정규화를 많이 하게되는것은 데이터에 대한 무결성을 깨뜨리는 결정적인 역할을 하는 경우가 많다.)

- 반정규화에 대한 필요성이 결정되면 칼럼의 반정규화 뿐만아니라, 테이블의 반정규화와 관계의 반정규화를 종합적으로 고려하여 적용해야 한다. 또한 막연하게 중복을 유도하는 것만을 수행하기 보다는 성능을 향상시킬 수 있는 다른 방법들을 고려하고 그 이후에 반정규화를 적용하도록 해야 한다.

- 반정규화를 적용할 때는 기본적으로 데이터 무결성이 깨질 가능성이 많기 때문에 반드시 데이터 무결성을 보장할 수 있는 방법을 고려한 후 반정규화를 적용하도록 해야한다.

* 반정규화 절차

1) 반정규화의 대상을 조사한다.

- 데이터의 양을 조사하고 그 데이터가 해당 프로세스를 처리할 때 성능저하가 나타날 수 있는지 검증해야한다.

* 자주 사용되는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우 반정규화를 검토한다.

* 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없을 경우에 반정규화를 검토한다.

* 통계성 프로세스에 의해 통계정보를 필요로 할 때 별도의 통계테이블을 생성한다.

* 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 반정규화를 검토한다.

2) 반정규화의 대상에 대해 다른 방법으로 처리할 수 있는지 검토한다

- 가급적이면 데이터를 중복하여 데이터 무결성을 깨뜨릴 위험을 제어하기 위하여 반정규화를 결정하기 전에 성능을 향상시킬 수 있는 다른 방법을 모색하도록 한다.

* 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰를 사용하면 해결할 수도 있다.

(성능을 고려한 뷰를 생성하여 개발자가 뷰를 통해 접근함으로 써 성능저하의 위험을 예방할 수 있다)

* 대량의 데이터 처리나 부분처리에 의해 성능이 저하되는 경우 클러스터링을 적용하거나 인덱스를 조정함으로써 성능을 향상시킬 수 있다.

( 클러스터링을 적용하는 방법은 대량의 데이터를 특정 클러스터링 팩트에 의해 저장방식을 다르게 하는 방법이다.

이 방법의 경우 데이터를 입력,수정,삭제 하는 경우 성능이 많이 저하되므로 조회중심의 테이블이 아니라면 생성하면 안된다. )

* 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있다. 즉 파티셔닝 기법이 적용되어 성능저하를 방지할 수 있다.

* 응용애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있다.

(응용메모리에 데이터를 처리하기 위한 값을 캐쉬한다든지 중간 클래스 영역에 데이터를 캐쉬하여 공유하게 하여 성능을 향상 시키는 것도 성능을 향상시키는 방법이 될 수 있다.)

3) 반정규화를 적용한다.

- 반정규화를 적용하기 이전에 충분히 성능에 대한 고려가 이루어져 판단이 들었다면 세가지 규칙을 고려하여 반정규화를 적용한다

- 반정규화를하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가할 수도 있고 분할할 수도 있으며 제거할 수도 있다.

2. 반정규화의 기법.

가. 테이블 반정규화

나. 칼럼 반정규화

다. 관계 반정규화.

=> 테이블과 칼럼의 반정규화는 데이터 무결성에 영향을 미치게 되나 관계의 반정규화는 데이터 무결성을 깨뜨릴 위험을 갖지 않고서도 데이터 처리의 능력을 향상시킬 수 있는 반정규화의 기법이 된다.

3. 정규화가 잘 정의된 데이터 모델에서 성능이 저하될 수 있는 경우.

- 공급자라고 하는 엔터티가 마스터 이고 전화번호와 메일주소 위치가 각각 변경되는 내용이 이력형태로 관리되는 데이터 모델의 경우, 반정규화 하여 마스터 엔터티에 최신 이력의 전화번호와 메일주소 위치를 저장할 경우  간단하게 조회할 수 있다.

- 데이터베이스서버가 분리되어 분산데이터베이스가 구성되어 있을 때 속성 반정규화를 통해 성능을 향상시킬 수 있다.

* 반정규화를 적용할 때 중요한 것은 데이터를 입력, 수정, 삭제할 때는 성능이 떨어지는 점을 기억해야하며, 데이터 무결성 유지에 주의해야한다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 2절:정규화와 성능

1. 정규화를 통한 성능 향상 전략.

- 정규화를 하는 것은 기본적으로 데이터에 대한 중복성을 제거해 주고 데이터가 관심사 별로 처리되는 경우가 많기 때문에 성능이 향상되는 특징을 가지고 있다.

- 엔터티가 계속 발생되므로 SQL 문장에서 조인이 많이 발생하여 성능저하가 나타나는 경우에는 사례별로 유의하여 반정규화를 적용하는 전략이 필요하다.

 * 데이터를 처리할 때 성능이라고 하면 조회성능과 입력/수정/삭제 성능의 두 분류로 구분된다.

- 정규화를 수행한다느 것은 데이터를 결정하는 결정자에 의해 함수적 종속성을 가지고 있는 일반 속성을 의존자로 하여 입력/수정/삭제 이상을 제거하는것이다.

 * 데이터의 중복성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테ㅣ블의 데이터 용량이 최소화 되는 효과가 있다.

=> 정규화된 테이블은 데이터를 처리할 때 속도가 빨라질 수도 있고 느려질 수도 있는 특성이 있다.

일반적으로 정규화를 수행해야 데이터 처리의 능력(입력/수정/삭제)이 향상되며, 데이터 조회처리 트랜잭션시에 성능저하가 나타날 수 있다.

2.반정규화된 테이블의 성능저하 사례1.

- 2차 정규화를 적용한 테이블에 대해서 조인을 하더라도 PK Unique Index를 이용하면 조인 성능 저하는 미미하게 발생한다.

 * PK가 걸려있는 방향으로 조인이 걸려 Unique Index를 곧바로 찾아서 데이터릴 조회하기 때문에 하나의 테이블에서 조회하는 작업과 비교했을 때 미미하게 성능차이가 날 뿐 사용자에게 큰 영향을 줄만큼 저하되는 일은 없다.

3. 반정규화된 테이블의 성능저하 사례2

- 이 업무는 물건을 매각할 때 매각일자를 정하고 그 일자에 해당하는 매각시간과 매각장소가 결정하는 속성의 성격을 가지고 있다. 즉 매각일자가 결정자가 되고 매각장소가 의존자가 되는 함수적 종송관계가 형성되는 관계이다.

- 대량의 데이터에서 조인 조건이 되는 대상을 찾기 위해 인라인뷰를 사용하기 때문에 성능이 저하된다.

이를 정규화 하려면 복합식별자 중에서 일반속성이 주식별자 속성 중 일부에만 종속관계를 가지고 있으므로 2차정규화의 대상이된다.

- 2차정규화르 적용하여 매각일자를 PK로 하고 매각시간과 매각장소는 일반속성이 되었으며 정규화를 적용했기 때문에 매각일자를 PK로 사용하는 매각일자별 매각내역과도 관계가 연결된다.

4. 반정규화된 테이블의 성능저하 사례3.

- 두 개 이상의 속성으로 나열하여 반정규화한 경우.

- 유형기능분류코드 단위로 데이터처리를 해야하여 인덱스를 생성하려면 9개의 인덱스를 생성하야한다.

- 한테이블에 인덱스가 많아지면, 조회성능은 좋아질 수 있으나 데이터 입력, 수정, 삭제 성능은 저하된다.

5. 함수적 종속성에 근거한 정규화 수행 필요.

- 함수의 종속성은 데이터들이 어떤 기준값에 의해 종속되는 현상을 지칭하는것이다.

- 이 때 기준값을 결정자 라고 하고 종속되는 값을 종속자 라고 한다.

- 사람이라는 엔터티는 주민등록번호, 이름, 출생지, 호주 라는 속성이 존재한다. 여기에서 이름, 출생지, 호주라는 속성은 주민등록번호 속성에 종속된다.

 즉 주민등록번호가 이름, 출생지, 호주를 함수적으로 결정한다 라고 말할 수 있다.

- 기본적으로 데이터는 속성간의 함수종속성에 근거하여 정규화 되어야 한다.

 1과목 데이터 모델링의 이해 - 2장 1절:성능 데이터 모델링의 개요

1.성능 데이터 모델링의 정의

- 데이터 모델링을 할 때 어떤 작업 유형에 따라 성능 향상을 도모해야 하는지 목표를 분명하게 해야 정확한 성능향상 모델링을 할 수 있다.

- 성능 데이터 모델링이란 데이터베이스 성능향상을 목적으로 설계단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블통합, 테이블 분할, 조인구조, PK, FK 등 여러 가지 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것.

- 성능 데이터 모델링은 정규화를 통해서도 수행할 수 있고 인덱스의 특징을 고려해서 컬럼의 순서를 변형할 수 있다. 대량의 데이터 특성에 따라 정규화된 모델이라도 테이블을 수직 또는 수평 분할하여 적용하는 방법과 논리적인 테이블을 물리적인 테이블로 전환할 때 데이터 처리의 성격에 따라 변환하는 방법도 성능 데이터 모델의 범주에 포함될 수 있다.

2. 성능 데이터 모델링 수행 시점

- 프로젝트 수행에 있어서 사전에 할수록 비용이 적게든다.

- 분석/설계 단계에서 데이터 모델에 성능을 고려한 데이터 모델링을 수행할 경우 성능저하에 따른 재업무 비용을 최소화 할 수 있는 기회를 갖게된다.

- 만약 어떤 트랜잭션이 해당 비즈니스 처리에 핵심적이고 사용자 업무처리에 있어 중요함을 가지고 있고 성능이 저하되면 안되는 특징을 가지고 있다면 프로젝트 초기에 운영환경에 대비한 테스트 환경을 구현하고 그곳에 트랜잭션을 발생시켜 실제 성능을 테스트 해야한다.

3.성능 데이터 모델링 고려사항.

1) 데이터 모델링을 할 때 정규화를 정확하게 수행해야 한다.

- 정규화된 모델이 주요 관심사별로 분산시키는 효과가 있기 때문에 그 자체로 성능을 향상시키는 효과가 있다.

2) 데이터베이스의 용량산정을 수행한다.

- 용량산정을 수행하면 어떤 엔터티(테이블)에 데이터가 집중되는지 파악을 할 수 있다.

3) 데이터베이스에 발생되는 트랜잭션의 유형을 파악한다.

-  CRUD 메트릭스를 보고 파악하는 방법과 시퀀스 다이어그램을 보면 트랜잭션 유형을 파악하기에 용이하다.

4) 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.

-  용량산정과 트랜잭션의 유형 데이터를 근거로 정확하게 테이블 반정규화를 적용한다.

5) 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 조정 등을 수행한다.

- 대량의 데이터가 처리되는 이력모델에 대해 성능고려를 하고 PK/FK의 순서가 인덱스 특성에 따라 성능에 미치는 영향도가 크기 때문에 PK/FK를 성능이 우수한 순서대로 칼럼의 순서를 조정해야한다.

6) 성능관점에서 데이터 모델을 검증한다.

- 전체적으로 성능에 대한 충분한 고려가 되었는지를 데이터 모델 검토를 통해 다시한번 확인한다.

1장 데이터 모델링의 이해-  5절 식별자

1. 식별자 개념 : 하나의 엔터티에 구성되어 있는 여러 개의 속성 중에 엔터티를 대표할 수 있는 속성을 의미한다. 

하나의 엔터티는 반드시 하나의 유일한 식별자가 존재해야한다.

* 식별자라는 용어는 업무적으로 구분이 되는 정보로 생각할 수 있으므로 논리적 모델링 단계에서 사용하며 

Key는 데이터베이스 테이블에 접근을 위한 매개체로서 물리 데이터 모델링 단계에서 사용한다.

2.  식별자의 특징 : 주식별자와 외부식별자에 따라 특성이 다소 차이가 있다.

- 주식별자의 특징

3.식별자 분류 및 표기법

가. 식별자 분류

- 엔터티 내에서 대표성을 가지는가에 따라 주식별자와 보조식별자로 구분.

- 엔터티 내에서 스스로 생성되었는지 여부에 따라 내부식별자와 외부식별자로 구분

- 단일 속성으로 식별이 되는가에 따라 단일식별자와 복합식별자로 구분

4. 주식별자 도출기준

- 해당 업무에서 자주 이용되는 속성

ex) 직원이라는 엔터티에서 주민등록번호와 사원번호가 존재할 수 있다.

사원번호가 그 회사에서 직원을 관리할 때 흔히 사용되는 번호이므로 사원번호를 주 식별자로 사용하고

주민벊호를 보조식별자로 사용할 수 있다.

- 명칭, 내역 등과 같이 이름으로 기술되는 것들은 가능하면 주식별자로 지정하지 않는다.

ex) 부서이름이 100개 있다고 가정할 때, 부서이름은 유일하게 구별할 수 있지만 주식별자로 사용할 경우 Where절에 기술해야하는 경우가 발생할 수 있으므로 일련번호나 코드를 사용한다.

- 복합으로 주식별자로 구성할 경우 너무 많은 속성이 포함되지 않도록 한다.

5. 식별자관계와 비식별자 관계에 따른 식별자.

가. 식별자관계와 비식별자 관계의 결정

- 외부식별자는 자기 자신이 엔터티에서 필요한 속성이 아니라 다른 엔터티와의 관계를 통해 자식 쪽에 엔터티에 생성되는 속성을 외부식별자라 하며 데이터베이스 생성시에 Foreign Key 역할을 한다.

나. 식별자 관계

- 부모로부터 받은 식별자를 자식 엔터티의 주식별자로 이용하는 경우는 Null 값이 오면 안되므로 반드시 부모엔터티가 생성되어야 자기 자신의 엔터티가 생성되는 경우이다.

- 부모로부터 받은 속성을 자식엔터티가 모두 사용하고 그것만으로 주식별자로 사용한다면 부모엔터티와 자식엔터티의 관계는 1:1 관계가 된다.

- 부모로부터 받은 속성을 포함하여 다른 부모엔터티에서 받은 속성을 포함하거나 스스로 가지고 있는 속성과 함께 주 식별자로 구성이 될 경우는 1:M  관계가 된다.

=> 자식엔터티의 주식별자로 부모의 주식별자가 상속되는 경우를 식별자 관계라고 지칭한다.

다. 비식별자 관계

-부모엔터티로부터 속성을 받았지만, 자식엔터티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 경우를 비 식별자관계 라고 한다.

- 다음의 네 가지 경우에 비식별자 관계에 의한 외부속성을 생성한다.

1) 자식엔터티에서 받은 속성이 반드시 필수가 아니여도 무방하기 때문에 부모 없는 자식이 생성될 수 있는 경우이다.

2) 엔터티별로 데이터의 생명주기를 다르게 관리할 경우이다. 부모엔터티의 인스턴스가 자식의 엔터티와 관계를 가지고 있었지만, 자식만 남겨두고 먼저 소멸될 수 있는 경우가 이에 해당됨.

(방안으로 물리데이터베이스 생성 시 FK를 연결하지 앟는 임시적인 방법도 사용하지만, 데이터 모델상에서 관계를 비식별자 관계로 조정하는것이 가장 좋은 방법이다.)

3)여러 개의 엔터티가 하나의 엔터티로 통합되어 표현되었는데 각각의 엔터티가 별도의 관계를 가질 때이며 이에 해당한다.

4) 자식엔터티에 주식별자로 사용하여도 되지만, 자식엔터티에서 별도의 주식별자를 생성하는것이 더 유리하다고 판단되는경우 비식별자 관계에 의한 외부식별자로 표기한다.

라. 식별자 관계로만 설정할 경우의 문제점.

- WHERE 절이 매우길어질 수 있다.

- 식별자 관계로만 연결된 데이터 모댈의 특징은 주식별자 속성이 지속적으로 증가할 수 밖에 없는 구조로서 개발자 복잡성과 오류가능성을 유발시킬 수 있는 요인이 될 수 있다.

마. 비식별자 관계로만 설정할 경우의 문제점.

- 자식엔터티로 상속이 되지않아서 자식엔터티에서 데이터를 처리할 때 쓸데없이 부모엔터티까지 찾아가야 하는 경우가 발생한다.

바. 식별자관계와 비식별자관계 모델링

1) 비삭별자관계 선택 프로세스

- 다음 흐름에 따라 비식별자 관계를 선정한다면 합리적으로 관계를 설정하는 모습이 될 수 있다.

- 기본적으로 식별자관계로 모든 관계가 연결되면서 다음 조건에 해당할 경우 비식별자 관계로 조정하면 된다.

* 자식엔터티의 독립된 주 식별자가 필요한지 분석하는것이 가장 중요하다. 독립적으로 주식별자를 구성한다는 의미는 업무적 필요성과 성능상 필요여부를 모두 포함하는 의미로 이해하면 된다.

2) 식별자와 비식별자관계 비교

강한 관계인 식별자관계와 약한 관계인 비식별자관계를 비교.

1장 데이터 모델링의 이해-  4절 관계

1. 관계의 개념.

가. 관계의 정의

- 엔터티의 인스턴사 사이의 논리적인 연관성으로 존재의 형태로서나 행위로서 서로에게 연관성이 부여된 상태.

나. 관계의 패어링

- 엔터티 안에 인스턴스가 개별적으로 관계를 가지는 것

- 각각의 엔터티의 인스턴스들은 자신이 관련된 인스턴스들과 관계의 어커런스로 참여하는 형태를 관계 패어링 이라고 한다.

- 엔터티는 인스턴스의 집합을 논리적으로 표현했다고 한다면, 관계는 관계 패어링의 집합을 논리적으로 표현한 것.

- 관계의 표현에는 이항관계, 삼항관계, n항 관계가 존재할 수 있는데 실제에 있어서 삼항 관계 이상은 잘 나타나지 않는다.

[IE 표기법]

[Barker 표기법]

2. 관계의 분류

- 존재에 의한 관계

* 사원은 부서에 항상 속해있다.

- 행위에 의한 관계

* 주문은 고객이 주문할 때 발생된다.

@@

 UML에는 클래스 다이어그램의 관계 중 연관관계와 의존관계가 있다.

- 연관관계: 항상 이용하는 관계로 존재적 관계에 해당함. (실선으로 표현됨)

- 의존관계: 상대방 클래스의 행위에 의해 관계가 형성될 때 해당됨.(점선으로 표현됨)

3. 관계의 표기법

- 관계명 : 관계의 이름

- 관계차수 1:1, 1:M, M:N

- 관계선택사양 : 필수관계, 선택관계

가. 관계명 : 엔터티가 관계에 참여하는 형태를 지칭한다.

- 관계가 시작되는 편을 관계시작점 이라고 부르고, 받는 편을 관계끝점 이라고 부른다.

- 관계시작점과 끝점 모두 관계이름을 가져야 하며, 참여자의 관점에 따라 관계이름이 능동적이거나 수동적으로 명명된다.

IE 표기법

Barker 표기법

* 관계명명 규칙

- 애매한 동사를 피한다. 

- 현재형으로 표현한다.

나. 관계차수

- 두 개의 엔터티간 관계에서 참여자의 수를 표현하는 것을 관계차수 라고한다.

- 가장 중요하게 고려할 사항은 한 개의 관계가 존재하느냐 아니면 두 개 이상의 멤버십이 존재하는지를 파악하는 것이 중요하다,

1) 1:1 관계

- 관계에 참여하는 각각 엔터티는 관계를 맺는 다른 엔터티의 엔터티에 대해 단지 하나의 관계만 가지고 있다.

2) 1:M 관계를 표시하는 방법

- 관계에 참여하는 각각의 엔터티는 관계를 맺는 다른엔터티의 엔터티에 대해 하나나 그 이상 수와 관계를 가지고 있다.

* 한명의 사원은 한 부서에 소속되고 한 부서에는 여러 사원을 포함한다.

3) M : M 관계를 표시하는 방법.

다. 관계선택사양

- 필수참여관계: 물리속성에서 Foreign Key로 연결될 경우 Null을 허용할 수 있는 항목.

- 선택참여관계: 참여하는 모든 참여자가 반드시 관계를 가지는, 타 엔터티의 참여자와 연결이 되어야 하는 관계이다.

* 선택참여로 지정해야 할 관계를 필수참여로 잘못 지정하면 어플리케이션에서 데이터가 발생할 때 반드시 한 개의 트랜잭션으로 제어해야하는 제약사항이 발생하므로, 설계단계에서 필수참여와 선택참여는 개발시점에 업무로직과 직접적으로 관련된 부분이므로 바드시 고려되어야한다.

* 선택참여하는 엔터티쪽에 원으로 표시한다.

4. 관계의 정의 및 읽는 방법

가. 관계체크 사항

- 두 개의 엔터티 사이에 관심있는 연관규칙이이 존재하는가?

- 두 개의 엔터티 사이에 정보의 조합이 발생되는가?

- 업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?

- 업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가?

나. 관계읽기

먼저 관계에 첨야하는 엔터티 기준으로 하나 또는 각으로 읽고 대상 엔터티의 개수를 읽고 관계 선택사항과 관계명을 읽도록한다.

- 각각의 사원은 한 부서에 항상 속한다.

- 각 부서에는 여러 사원이 때때로 소속된다.

1장 데이터 모델링의 이해-  3절 속성

1. 속성의 개념 

- 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위.

- 엔터티는 속성들에 의해 설명된다.

정리

* 업무에서 필요로한다.

* 의미상 더 이상 분리되지 않는다.

* 엔터티를 설명하고 인스턴스의 구성요소가 된다.

2. 엔터티, 인스턴스와 속성, 속성값에 대한 내용과 표기법

- 엔터티에는 두 개 이상의 인스턴스가 존재하고, 각각의 엔터티에는 고유의 성격을 표현하는 속성정보를 두 개 이상 갖는다.

- 엔터티에 속한 인스턴스들의 성격을 구체적으로 나타내는 항목이 속성이다.

- 속성값은 각각의 엔터티가 가지는 속성들의 구체적인 내용이다

정리

* 한 개의 엔터티는 두 개  이상의 인스턴스의 집합이여야 한다.

* 한 개의 엔터티는 두 개 이상의 속성을 갖는다.

* 한 개의 속성은 한 개의 속성을 갖는다.

[IE 표기법]

[Barker 표기법]

나. 속성의 표기법

- 속성의 표기법은 엔터티 내에 이름을 포함하여 표현하면 된다.

3. 속성의 특징

- 엔터티와 마찬가지로 반드시 해당 업무에서 필요하고 관리하고자 하는 정보이어야 한다.

- 정규화 이론에 근간하여 정해진 주식별자에 의해 함수적 종속성을 가져야 한다.

- 하나의 속성에는 한 개의 값만을 가진다. 하나의 속성에 여러 개의 값이 있는 다중값일 경우 별도의 엔터티를 이용하여 분리한다.

4. 속성의 분류

가. 속성의 특성에 따른 분류

- 기본속성: 업무로부터 추출한 모든 속성이 여기 해당하며, 엔터티에 가장 일반적이고 많은 속성을 차지한다.

업무로부터 분석한 속성이라도 이미 업무상 코드로 정의한 속성이 많다. 

- 설계속성: 업무상 필요한 데이터 이외에 데이터 모델링을 위해, 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성이다.  대개 코드성 속성은 원래 속성을 업무상 필요에 의해 변형하여 만든 설계속성이고 일련번호와 같은 속성은 단일한 식별자를 부여하기 위해 모델 상에서 새로 정의하는 설계속성이다.

- 파생속성: 다른 속성에 영향을 받아 발생하는 속성으로서 보통 계산된 값들이 이에 해당된다. 다른 속성에 영향을 받기 때문에 프로세스 설계 시 데이터 정합성을 유지하기 위해 유의할 점이 많다.

ex) 기본속성  : 제품이름, 제조년월, 제조원가

      설계속성 :  약품용기 코드

      파생속성 :  계산값 (전체용기 수, 용기의 총금액)

나. 엔터티 구성에 따른 분류

PK   속성 : 엔터티를 식별할 수 있는 속성

FK  속성 : 다른 엔터티와의 관계에서 포함된 속성

일반 속성: PK, FK에 포함되지 않은 속성을 일반속성이라 한다.

* 세부 의미를 쪼갤 수 있는지에 따라 분류가능

복합속성: 예를들어 주소 속성은 시, 구, 동, 번지 등 세부속성들로 구성될 수 있는데, 이를 복합속성이라한다.

단순속성: 나이, 성별 등의 속성은 더 이상 다른 속성들로 구성될 수 없는 단순한 속성이므로 단순 속성이라 한다.

단일값 속성: 속성 하나에 한 개의 값을 가지는 경우 (ex 주민번호)

다중값 속성: 속성 하나에 여러 개의 값을 가지는 경우.

(ex 어떤 사람의 전화번호와 같은 속성은 집, 휴대전화 회사전화 등 여러가지를 가질 수 있다.)

* 다중값 속성의 경우 하나의 엔터티에 포함될 수 없으므로 1차 정규화를 하거나 별도의 엔터티를 생성하여 관계로 연결해야 한다.

5. 도메인 

- 각 속성은 가질 수 있는 값으 범위가 있는데, 이를 도메인 이라고 한다.

- 속성은 도메인 이외의 값을 가지지 못한다.

=> 도메인이란 엔터티 내에서 속성에 대한 데이터타입과 크기 그리고 제약사항을 지정하는것.

6. 속성의 명명

- 속성의 이름을 정확하게 부여하고 영어의 혼란을 없애기 위해 용어사전 이라는 업무사전을 프로젝트에 사용하게된다.

- 용어사전과 도메인정의를 같이 사용하여 프로젝트를 진행한 경우 용어적 표준과 데이터타입의 일관성을 확보할 수 있게 된다.

* 현업에서 사용하는 이름을 부여하는 것이 가장 중요하다.

* 서술식의 속성명은 사용하지 말아야한다

* 공유화되지 않은 업무에서 사용하지 않은 약어는 사용하지 않는게 좋다.

* 모든 속성의 이름은 유일하게 작성하는 것이 좋다.