웹개발자의 일상

Q1) 성능 데이터 모델링에 대한 설명

   A) 데이터의 증가가 빠를수록 성능저하에 따른 성능개선 비용은 증가한다.

       데이터모델은 성능을 튜닝하면서 변경이 될 수 있는 특징이 있다.

       분석/설계 단계에서 성능을 고려한 데이터모델링을 수행할 경우 성능저하에 따른 Rework비용을 최소화 할 수 있는

      기회를 가지게 된다. 

Q2) 성능을 고려한 데이터모델링의 순서로 가장 적절한 것은?

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

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

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

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

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

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

  A) 가-다-라-나-바-마

Q3) 성능데이터 모델링을 할때 고려사항으로 가장 부적절한 것은?

  A) 데이터 모델링의 정규화는 항상 조회 성능저하를 나타내므로 -> 조회 성능향상

Q1) 아래의 ERD에서 3차 정규화를 만족하게 할 때 엔터티의 개수는 몇개가 되는가?

    ㄱ. 평가코드 평가내역은 학번에 종속적

    ㄴ. 코스명 기간은 코스코드에 종속적

    ㄷ. 평가코드 평가내역은 속성간 종속적 관계

A) 3개

2차 정규화는 학과등록과 코스테이블로 엔터티 2개

3차 정규화를 하므로써 학과등록, 코스, 평가항복 테이블로 나누어 엔터터 3개

Q2) 아래의 데이터 모델처럼 동일한 유형의 속성이 칼럼 단위로 반복되느느 경우가 실제 프로젝트를 하면서 많이 발생 할 수 있다. 다음 중 아래와 같이 전제조건이 있을 때 테이블에서 나타날 수 있는 현상

전제조건 : 유형기능분류코드에 해당하는 속성들은 분포도가 양호하며, SQL Where 절에서 각각의 값이 상수값으로 조건 입력 될 수 있는 특징을 가진다. 

A) 유형기능분류코드 각각에 대하여 개별로 Index를 모두 생성할 경우 입력,수정,삭제 때 성능이 저하되므로 제 1차 정규화를 수행한 후 인덱스를 적용하는 것이 좋다.  -> 컬럼에 의한 반복적이 속성값을 갖는형태는 1차 정규화

Q3) '일자별매각물건' 엔터티에 대한 설명으로 가장 적절한 것은?

A) 2차 정규화가 필요한 엔터티로서 매각기일과 일자별매각물건으로 1:M 관계가 될 수 있다.

   -> 두개의 주식별자 속성 중 매각일자에만 종속되기 때문

Q4) 아래와 같이 수강지도 엔터티를 만들었을 때 이에 해당하는 정규형과 정규형의 대상으로 가장 바르게 짝지어진 것은?

함수종속성

 1. 학번||과목 -> 성적

 2. 학번 -> 지도교수명

 3. 학번 -> 학과명 

  A) 1차정규형 - 2차 정규화 대상

Q1) 아래의 설명 중 반정규화 대상이 아닌 것은?

   A) 자주 사용하는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우

       테이블의 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면

       성능을 보장할 수 없을 경우

       통계성 프로세스에 의해 통계 정보를 필요로 할때 별도의 통계 테이블을 생성해야 하는경우

       테이블에 지나치게 많은 조인과 Sorting, Order by 프로세스가 많은경우   -> 지나치게 많은 join은 맞지만

                                                                                                          Sorting과 Order by는 무관

Q2) 반정규화에 대한 설명

  A) 데이터를 조회할 때 디스크 I/O 량이 많아서 성능이 저하되거나 경로가 너무멀어 조인으로 인한 성능저하가

      예상될 때 수행한다.

      컬럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 수행한다.

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

      통계테이블, 중복테이블, 이력테이블 추가는 반정규화 기법이다.

Q3) 아래 설명을 읽고 다음 (ㄱ) 에 들어갈 단어를 작성하시오

  첫번째, 데이터 모델링을 할 때 정규화를 정확하게 수행한다.

  두번째, 데이터베이스 용량산정을 수행한다.

  세번째, 데이터베이스에 발생되는 트랜잭션의 유형을 파악한다.

  네번째, 용량과 트랜잭션의 유형에 따라 (  ㄱ  ) 을 수행한다.

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

  A) 반정규화

Q4) 하나의 테이블의 전체 칼럼 중 자주 이용하는 집중화된 컬럼들이 있을 때 디스크 I/O 를 줄이기 위해 해당 컬럼들을 별도로 모아 놓는 반정규화 기법은?

  A) 테이블 추가 - 부분테이블 추가

Q5) 데이터 모델에 대한 반정규화를 고려할 때 판단요소에 대한 설명으로 가장 적절한 것은?

  A) 반정규화 정보에 대한 재현의 적시성으로 판단한다. 예를 들어, 빌링의 잔액은 다수 테이블에 대한 다량의 조인이 불가피하므로 데이터 제공의 적시성 확보를 위한 필수 반정규화 대상 정보이다.

Q6) SQL문에서 조회를 빠르게 수행하기 위한 반정규화 방법은?

SELECT A.주문번호, SUM(C.단가)

FROM 주문 A, 주문목록 B, 제품 C

WHERE A.주문번호 = '2015-02-001'

AND A.주문번호 B. 주문번호

                                                              AND B.제품번호 = C.제품번호

                                                              GROUP BY A.주문번호

  A) 주문 엔터티에 단가를 합한 계산된 컬럼을 추가하도록 한다. 

     -> 제품 엔터티에 단가를 주문번호별로 합하는 것은 해당 제품이 여러주문에 포함될수 있으므로 안됨

         주문목록 엔터티에 주문번호별 단가 합계금액을 추가하는 것은 일관성 문제 발생

Q1) Row Migration과 Row Chaining에 대한 설명

   A) 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 두개 이상의 블록에 걸쳐 하나의 로우가 저장되는 현상을 Row Chaning이라고 한다. 

Q2) 아래 설명에서 데이터 엑세스 성능을 향상시키기 위해 적용하는 방법에 대해서 (  ㄱ  ) 을 채우시오

    하나의 테이블에 많은 양의 데이터가 저장되면 인덱스를 추가하고 테이블을 몇개로 쪼개도 성능이 저하되는 경우가 있다. 이때 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의테이블로 분리하여 데이터 엑세스 성능도 향상시키고, 데이터 관리방법도 개선할 수 있도록 테이블에 적용하는 기법을 (   ㄱ  ) 이라고 한다. 

  A) 파티셔닝

Q3) 다음 중 논리데이터모델의 슈퍼타입과 서브타입 모델을 물리적인 테이블 형식으로 변환할때 설명

  A) 트랜잭션은 항상 전체를 대상으로 일괄 처리하는데 테이블은 서브타입별로 개별 유지하는 것으로 변환하면 Union       연산에 의해 성능이 저하될 수 있다. 

     트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합하여 변환하면 불필요하게 많은 양의 데이터가       직접되어 있어 성능이 저하 될 수 있다.

     트랜잭션은 항상 슈퍼+서브 타입을 함께 처리하는 데 개별로 유지하면 조인에 의해 성능이 저하될 수 있다.

     트랜잭션은 항상 전체를 통합하여 분석처리하는데 하나로 통합되어 있으면 데이터 집접으로 인해 성능이 더 우수하       다.(조인 감소)

Q4) '현금출급기실적' 테이블과 이 테이블에서 데이터를 조회 할 때 사용되는 아래의 SQL 패턴에 대한 설명

SELECT 건수, 금액 

FROM 현금출급기실적

WHERE 거래일자 BETWEEN '20140701' AND '20140702' 

AND 사무소코드 = '000368'

(단 , PK인덱스는 설계된 순서 그대로 생성함)

  A) 사무소코드가 '=' 상수값이 들어왔고, 거래일자가 범위 'Between'으로 들어왔기 때문에 PK순서를 사무소코드 + 거래일자 + 출급기번호+명세표번호로 바꾸고 인덱스를 생성하는 것이 성능에 유리함.

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

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

Q1) 분산 데이터베이스의 특징 중 사용하려는 데이터의 저장 장소 명시가 불필요하다는 특징은?

   A) 위치 투명성

Q2) 다음 중 데이터가 여러 지역에 분산되어 있지만 하나의 데이터베이스처럼 사용하기를 원하는 분산데이터베이스 환경에서 데이터베이스 분산설계를 적용하여 효율성을 증대시킬 수 없는 것은?

  A) 공통코드, 기준정보 등 마스터 데이터는 분산데이터베이스에 복제분산을 적용한다. 

      거의 실시간 업무적인 특성을 가지고 있을 때 분산 데이터베이스를 사용하여 구성할 수 있다.

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

      Global Single Instatnce(GSI) 를 구성할 때 분산데이터베이스를 활용하여 구성하는 것이 효율적이다.

      > 통합데이터베이스 구조를 의미

- 성능데이터 모델링의 정의

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

  성능이 저하되는 데이터 모델의 경우 첫번째, 데이터 모델구조에 의해 성능이 저하될 수도 있고 두번째, 데이터가 대용량이 됨으로써 불가피하게 저하될 수도 있음. 세번째, 인텍스 특성을 충분히 고려하지못하고 생성하여 성능저하되는 경우도 있음

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

  성능을 분석/설계 단계에서부터 성능에 대한 데이터 설계를 하지 않으면 시간이 지날수록 성능개선의 비용이 증가함.

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

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

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

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

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

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

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

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

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

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

  일반적으로 정규화를 수행해야 데이터 처리의 성능이 향상되며 데이터의 조회처리 트랜잭션시에 성능저하가 나타날 수 있음 

  일반적으로 정규화가 잘되어 있으면 입력/수정/삭제의 성능이 향상되고 반정규화를 많이 하면 조회의 성능이 향상된다고 인식 됨

[정규형 정리]

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

    하나의 테이블을 부분키 종속을 정규화하여 두개의 테이블로 분리해 2차 정규화 시켰을 때 두개의 테이블을 조인해서 처리 

   아래의 경우 왼쪽테이블은 불필요하게 납부자 번호만큼 누적된 데이터를 읽어서 결과를 구분하여 보여주고 오른쪽테이블은 관서수만큼만 존재하는 데이터를 읽어 처리하기 때문에 2차정규화를 한 테이블이 훨씬 빠르다.

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

  두 개의 엔터티가 통합되어 반정규화 된 경우

  특정 매각장소에 대해 매각일자를 찾아 매각내역을 조회하려면 100만건의 데이터를 읽어 매각일자를 distinct 하여

  매각일자별 매각내역과 JOIN

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

  2차 정규화를 적용하여 매각일자를 pk로 하고 특정매각내역을 조회하려면 5천건의 매각기일과 매각일자별매각내역이

  JOIN되어 성능 향상

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

   동일한 속성 형식을 두개 이상의 속성으로 나열하여 반정규화 한 경우

   아래의 경우 인덱스를 생성하려면 9개나 되는 인덱스를 추가해야 함

  중복 속성에 대한 분리가 필요하므로 1차 정규화를 적용

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

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

   함수의 종속성(Functional Dependency) : 데이터들이 어떤 기준값에 의해 종속되는 형상

   기준값- 결정자(Determinant) , 종속되는 값 - 종속자(Dependent)

   함수의 종속성은 데이터가 가지고 있는 근본적이 속성으로 인식되어짐

   ex) 이름,출생지, 주소는 주민등록번호에 함수 종속성을 가지고 있음

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

   프로젝트 수행에서 정규화는 선택사항이 아니라 필수 사항이다. 

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

 1) 반정규화의 정의 

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

 2) 반정규화의 적용방법

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

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

    데이터가 대량이고 성능이 저하될 것으로 예상이 되면 다음 4가지 경우를 고려하여 반정규화를 고려하게 된다.

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

        를 검토한다.

     2. 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리범위를 일정하게 줄이지 않으면

        성능을 보장할 수 없을 경우에 반정규화를 검토한다.

     3. 통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계테이블(반정규화 테이블)을 생성한다.

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

        한다.

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

     1. 지나치게 많은 조인(JOIN)이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 뷰(VIEW)를 사용하면

        이를 해결할 수도 있다.

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

        성능을 향상시킬 수 있다.

     3. 대량의 데이터는 Primary Key 의 성격에 따라 부분적인 테이블로 분리할 수 있다. 즉 파티셔닝 기법(Partitioning)

       이 적용되어 성능저하를 방지할 수 있다.

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

   · 반정규화를 적용한다.

- 반정규화의 기법

   1)테이블 반정규화

   2)칼럼 반정규화

   3)관계 반정규화

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

- 정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우

  1)

공금자와 전화번호, 메일주소, 위치는 1:M 관계이므로 한명의 공급자당 여러개의 전화번호, 메일주소, 위치가 존재하므로 최근에 변경된 값을 가져오려면 각 테이블에서 MAX값을 가져와서 조인해야하므로 복잡한 SQL문을 작성

이때 반정규화를 적용하면 가장 최근에 변경 된 값을 마스터에 위치 시켜서 간단하게 SQL문을 작성할 수 있다. 

2) 데이터베이스서버가 분리 되어 분산데이터베이스가 구성되어 있을 때 반정규화를 통해 성능을 향상시킬수 있는 경우

 서버 A 에 부서와 접수 테이블이 있고 서버 B 에 연계라는 테이블이 있는데 서버 B에서 데이터를 조회 할 때 빈번하게   조회되는 부서번호가 서버 A 에 존재하기 때문에 연계, 접수, 부서 테이블이 모두 조인이 걸리게 된다. 게다가 분산데이터베이스 환경이기 때문에 다른 서버간에도 조인이 걸리게 되어 성능이 저하됨

  이때는 서버 A에 있는 부서테이블의 부서명을 서버 B의 연계테이블에 부서명으로 속성 반정규화를 함으로써 조회 성능을 향상시킬 수 있다.

- 대량 데이터에 따른 테이블 분할 개요

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

  ex) 한 테이블에 데이터가 대량으로 집중되거나 하나의 테이블에 여러 개의 칼럼이 존재하여 디스크에 많은 블록을 점유하는 경우는 모두 I/O량이 증가하면서 성능저하를 유발

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

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

- 한 테이블에 많은 수의 칼럼을 가지고 있는 경우

  트랜잭션이 발생 될때 어떤 컬럼에 대해 집중적으로 발생하는지 분석하여 테이블을 쪼개어 주면 디스크 I/O가 감소하게 되어 성능이 개선됨

도서정보테이블에서 200개의 컬럼을 동시에 조회하던 경우를 전자출판유형에 대한 트랜잭션이 독립적으로 많이 발생되는 경우가 있으므로 도서전자출발테이블을 생성하여 1:1 관계로 분리

- 대랑 데이터 저장 및 처리로 인해 성능

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

  있다

  1) RANGE PARTITION 적용

    대상 테이블이 날짜 또는 숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리된다면 Range Partition을 적용.

     또한 데이터보관주기에 따라 테이블에 데이터를 쉽게 지우는 것이 가능하므로(파티션 테이블 DROP)  데이터보관         주기에 따른 테이블관리가 용이

    ex) PK 가 요금일자+ 요금번호로 구성되어 있는 대용량테이블의 경우(데이터건수 1억2천만건) PK인 요금일자의

        년+월을 이용해 12개의 파티션 테이블(각 파티션 테이블당 평균 1000만건의 데이터) 을 만듦,

       그러면 SQL 문장을 처리 할 때는 마치 하나의 테이블처럼 보이는 요금 테이블을 이용하여 처리하면 되지만

       DBMS 내부적으로는 SQL WHERE 절에 비교된 요금일자에 의해 각 파티션에 있는 정보를 찾아가므로

       1000만건의 데이터에서 찾으면 되므로 성능이 개선

  2) LIST PARITTION 적용

     PK가 지점, 사업소, 사업장, 핵심적인 코드값 등으로 구성되어 있고 대량의 데이터가 있는 테이블이라면 값 각각에

     의해 파티셔닝이 되는 List Partition을 적용 할 수 있다. List Partition은 대용량 데이터를 특정값에 따라 분리 저장할       수는 있으나 Range Partition과 같이 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공 될 수 없다. 

    ex) 고객테이블에서 지역을 나타내는 사업소코드별로 고객_인천, 고객_서울 등등 List Partition을 적용하여 성능개선 

  3) HASH PARTITON 적용

    HASH 조건에 따라 해쉬 알고리즘이 적용되어 테이블이 분리되며 설계자는 테이블에 정확하게 어떻게 들어갔는지 알 수 없고 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공할 수 없다. 

- 테이블에 대한 수평분할/ 수직분할의 절차

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

  2) 데이터베이스 용량산정을 한다. (칼럼수가많은지 확인)

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

    (트랜잭션 특성에 따라 테이블을 1:1 형태로 분리할 수 있는 지 검증)

  4) 칼럼 단위로 집중화된 처리가 발생하는지, 로우단위로 집중화된 처리가 발생하는지 분석하여 집중화된 단위로

     테이블을 분리하는 것을 검토한다. 

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

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

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

   논리적인 데이터 모델에서 이용되는 형태이고 분석/설계 단계를 구분하자면, 분석단계에서 많이 쓰이는 모델이다.

   그러므로 물리적인 데이터 모델이 성능을 고려한 데이터 모델이 되어야한다는 점을 고려해야한다. 

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

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

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

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

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

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

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

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

      슈퍼타입에도 꼭 필요한 속성만을 가지게 하고 서브타입에도 꼭 필요한 속성 및 자신이 타입에 맞는 데이터만 가지게 하기 위해서 모두 분리하여 1:1관계를 갖게함

      ex) 공통으로 처리하는 슈퍼타입테이블인 당사자정보를 미리조회 하고 원하는 내용을 클릭하면 거기에 따라서 서브타입인 세부적인 정보를 조회하는 방식

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

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

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

   변환모델의 선택은 철저하게 데이터베이스에 발생되는 트랜잭션의 유형에 따라 선택을 해야한다. 

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

  · PK/FK 컬럼순서와 성능개요

  · PK 컬럼의 순서를 조정하지 않으면 성능의 저하 이유  

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

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

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

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

  ex) FK 인덱스 미생성으로 FULL TABLE SCAN 발생하여 성능저하 -> FK인덱스 생성으로 INDEX SCAN발생하여 성능향상 ( 개발초기에는 데이터량이 얼마 되지 않아 성능저하가 나타나지 않지만 대용량일 경우)

- 분산 데이터베이스의 개요

  데이터베이스를 연결하는 빠른 네트워크 환경을 이용하여 데이터베이스를 여러 지역 여러 노드로 위치시켜

  사용성/성능 등을 극대화 시킨 데이터베이스

- 분산 데이터베이스의 투명성(Transparency)

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

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

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

   3) 지역사상 투명성 : 지역 DBMS 와 물리적 DB사이의 Mapping 보장, 각 지역시스템 이름과 무관한 이름 사용가능

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

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

   6) 병행 투명성 : 다수 Transaction 동시 수행 시 결과의 일관성 유지, Time Stamp, 분산 2단계 Loking을 이용구현

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

   · 분산 데이터 베이스 적용 방법

    업무의 흐름을 보고 업무구성에 따른 아키텍처 특징에 따라 데이터베이스를 구성하는 것

   · 분산 데이터 베이스 장단점

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

  핵심가치 : 데이터 처리 성능,

                데이터를 분산 환경으로 구성하면 통합된 데이터베이스에서 제공할 수 없는 빠른 성능을 제공

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

  1) 테이블 위치 분산

     테이블의 위치는 변하지 않고 테이블이 다른 데이터베이스에 중복되어 생성되지도 않음, 다만 설계된 테이블의 위치

     를 각각 다르게 위치시킴

     ex) 자재품목은 본사에서 구입하여 관리하고 각 지사별로 자재품목을 이용하여 제품을 생산한다면 데이터베이스를

         본사와 지사단위로 분산, 각각 테이블마다 위치를 표기하여 테이블 생성 (자재품목 - 본사 , 생산제품-지사 )

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

  2) 테이블 분할(Frgmentation) 분산

    단순히 위치만 다른 곳에 두는 것이 아니라 각각의 테이블을 쪼개어 분산하는 방법이다.  첫번째는 테이블의 로우(ROW) 단위로 분리하는 수평분할, 두번째는 테이블을 칼럼(COLUMN) 단위로 분할하는 수직분할이 있다.

    ·수평 분할 : 데이터를 수정할 때는 타 지사에 있는 데이터를 원칙적으로 수정하지 않고 자신의 데이터에 대해서

                    수정하도록 한다.

                    데이터가 지사별로 별도로 존재하므로 중복은 발생하지 않는다.

                    한 시점에는 한 지사(Node)에서 하나의 데이터만이 존재하므로 데이터의 무결성은 보장되는 형태이다.

    ·수직 분할 : 칼럼을 기준으로 분할하였기 때문에 각각의 테이블에는 동일한 Primary Key 구조와 값을 가지고 있어야

                    한다.

                    지사별로 쪼개어진 테이블들을 조합하면 Primary Key 가 동일한 데이터의 조합이 가능해야 하며 하나의

                   완전한 테이블이 구성되어야 한다.

                   데이터를 한군데 집합시켜 놓아도 동일한 Primary Key 는 하나로 표현하면 되므로 데이터 중복은

                   발생되지 않는다.   -> 사례가 드물다

  3) 테이블 복제(Replication) 분산

    동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형

    부분복제 : 통합된 테이블을 한군데(본사)에 가지고 있으면서 각 지사별로는 지사에 해당된 로우를 가지고 있는 형태

    광역복제 : 통합된 테이블을 본사에 가지고 있으면서 각 지사에 본사와 동일한 데이터를 모두 가지고 있는 형태

  4) 테이블 요약(Summarization) 분산

    지역간에 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우

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

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

                  분산방법

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

  · 성능이 중요한 사이트에 적용해야 한다.

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

  · 실시간 동기화가 요구되지 않을 때 좋다. 거의 실시간(Near Real Time)의 업무적인 특징을 가지고 있을 때도

    분산 환경을 구성할 수 있다.

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

  · 백업 사이트(Disaster Recovery Site)를 구성할 때 간단하게 분산기능을 적용하여 구성할 수 있다

Q1) 데이터 모델링에 대한 설명 

  A)  논리모델링의 외래키는 물리모델에서 반드시 구현되지는 않는다.

      실제로 데이터베이스를 구축할 때 참고되는 모델은 물리적 데이터 모델링이다.

      개념 모델링에서 물리 모델링으로 가면서 더 구체적이며 개념모델링이 가장 추상적이다.

      데이터 모델링의 3가지 요소는 Thing, Attributes, Relationship 이다.

Q2) 데이터 모델링에 대한 단계 중 아래에서 설명하는 단계는 어떤 단계의 모델링인가?

    추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행. 전사적 데이터 모델링, EA 수립 시 많이 이용됨 

  A) 개념적 데이터 모델링

Q3) 데이터 모델링이 최종적으로 완료된 상태라고 정의할 수 있는, 즉 물리적인 스키마 설계를 하기 전 단계를 가리키는 말은 ?

  A) 논리적 모델링

Q4) 모델링의 특징 

  A) 현실세계를 일정한 형식에 맞추어 표현하는 추상화의 의미를 가질 수 있음

      복잡한 현실을 제한된 언어나 표기법을 통해 이해하기 쉽게 하는 단순화의 의미를 지니고 있음

      애매모호함을 배제하고 누구나 이해가 가능하도록 정확하게 현상을 기술하는 정확화의 의미를 가짐

Q5) 데이터모델링을 할 때 유의해야 할 사항

  A)  여러 장소의 데이터베이스에 같은 정보를 저장하지 않도록 하여 중복성을 최소화한다. 

       데이터의 정의를 데이터의 사용 프로세스와 분리하여 유연성을 높인다.

       데이터간의 상호연관관계를 명확하게 정의하여 일관성 있게 데이터가 유지되도록 한다. 

Q6) 다음 아래 설명이 의미하는 데이터 모델링의 유의점에 해당하는 특성은?

     데이터의 정의를 데이터의 사용 프로세스와 분리함으로써 데이터 모델링은 데이터 혹은 프로세스의 작은 변화가

     애플리케이션과 데이터베이스에 중대한 변화를 일으킬 수 있는 가능성을 줄인다. 

   A) 비유연성

Q7) 아래 내용이 설명하는 스키마 구조로 가장 적절한 것은?

  - 모든 사용자 관점을 통합한 조직 전체 관점의 통합적 표현

  - 모든 응용시스템들이나 사용자들이 필요로 하는 데이터를 통합한 조직 전체의 DB를 기술한 것으로  DB에 저장되는 데이터와 그들간의 관계를 표현하는 스키마

  A) 개념스키마

Q8) 정보 시스템을 모델링 할때 세가지 관점

   A) 업무가 어떤 데이터와 관련이 있는지 분석(WHAT)

       업무가 실제하는 일은 무엇인지 또는 무엇을 해야하는지 분석(HOW, PROCESS)

       업무가 처리하는 일의 방법에 따라 데이터가 어떻게 영향을 받는지 분석(INTERACTION)

Q9) 발생시점에 따라 구분할 수 있는 엔터티의 유형

  A) 기본/키 엔터티, 중심엔터티, 행위엔터티

      개념엔티티는 유형에 따른 분류

Q1) 엔터티 - 인스턴스 - 속성 - 속성값에 대한 관계 설명

  A) 한 개의 엔터티는 두 개 이상의 인스턴스의 집합이어야 한다.

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

      하나의 속성은 하나의 속성값을 가지며 하나 이상의 속성값을 가지는 경우 정규화가 필요함

      하나의 엔터티의 인스턴스는 다른 엔터티의 인스턴스간의 관계인 Paring을 가진다.

Q2) 엔터티의 종류가 아닌것

   A) 교수, 학생, 수강 

       -> 엔터티는 사람, 장소, 물건, 사건, 개념 등의 명사에 해당한다.

           엔터티는 업무상 관리가 필요한 관심사에 해당한다.

           엔터티는 저장이 되기 위한 어떤 것(Thing)이다.

       청약자   -> 관심사라기 보다 청약의 주체가 되는 속성에 가까우므로 속성값을 가지기에 애매함

Q3) 발생시점에 따른 엔터티 분류에 의한 중심 엔터티

  A) 매출, 계약, 주문  (사원  -> 기본엔터티)

Q4)  발생시점에 따른 엔터티 분류에 대해서 짝짓기

  A) 기본/키 엔터티 : 조직, 사원

      기본/키 엔터티 : 부서

      중심 엔터티 : 주문상품, 상품 -> 상품은 기본 엔터티 

      행위 엔터티 : 주문내역, 계약진행

Q5) 아래 설명이 나타내는 데이터모델의 개념으로 적절한 것은?

  주문이라는 엔터티가 있을 때 단가라는 속성 값의 범위는 100에서 10,000 사이의 실수 값이며 제품명이라는 속성은

  길이가 20자리 이내의 문자열로 정의할 수 있다.

  A) 도메인 

Q6) 다른 엔터티로부터 주식별자를 상속받지 않고 자신의 고유한 주식별자를 가지며 사원,부서,고객,상품,자재 등이 예가 될 수 있는 엔터티는?

   A) 기본 엔터티(키엔터티)

Q7) 두 개의 엔터티 사이에서 관계를 도출 할 때 체크할 사항

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

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

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

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

Q1) 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위를 무엇이라고 하는가?

   A) 속성

Q2) 속성에 대한 설명

   A) 엔터티에 대한 자세하고 구체적인 정보를 나타낸다.

       하나의 엔터티는 두 개 이상의 속성을 갖는다.

       하나의 인스턴스에서 각각의 속성은 한개의 속성값을 갖는다( 하나이상의 속성값을 가질 수 없다)

       속성도 집합이다.

Q3) 이 속성이 없어도 다른 속성을 이용하여 결과를 도출 할 수 있는 특징을 가진 속성의 이름은?

   A) 파생 속성

Q4) 데이터를 조회할 때 빠른 성능을 낼 수 있도록 하기위해 원래 속성의 값을 계산하여 저장 할 수 있도록 만든 속성으로 가장 적절한 것은?

   A) 파생 속성

Q5) 엔터티 내에서 속성에 대한 데이터 타입과 크기 그리고 제약사항을 지정하는 것

  A) 도메인

Q1) 데이터모델링의 관계에 대한 설명

   A) 관계는 존재에 의한 관계와 행위에 의한 관계로 구분 될 수 있으나 ERD에서는 관계를 연결 할 때 존재와 행위를 구분하지 않고 단일화된 표기법을 사용한다. 

      UML에는 클래스 다이어그램의 관계 중 연관관계와 의존관계가 있고 이것은 실선과 점선의 표기법으로 다르게 표현이 된다. 

Q2) 관계에 대한 설명으로 부적절 한것은?

   A) 관계는 존재적 관계와 행위에 의한 관계로 나누어 볼 수 있다. 

       관계의 표기법은 관계명, 관계차수, 식별성  선택성의 3가지 개념을 사용한다. 

       부서와 사원 엔터티 간의 '소속' 관계는 존재적 관계의 사례이다. 

       주문과 배송 엔터티 간의 '배송근거'는 행위에 대한 관계의 사례이다. 

Q1) 식별자의 대체여부에 따라 분류하는 방식은?

     A) 본질식별자 - 인조식별자

Q2)  주식별자를 지정할 때 고려해야 할 사항은?

   A ) 주식별자에 의해 엔터티 내의 모든 인스턴스들이 유일하게 구분되어야 한다.

        주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.

        지정된 주식별자의 값은 자주 변하지 않는 것이여야 한다.

        주식별자가 지정이 되면 반드시 값이 들어와야 한다.

Q3) 사원엔터티에서 식별자의 특성에 해당하는것은

   A) 주식별자, 단일식별자, 내부식별자, 본직식별자

 Q4) 식별자로 가장 부적절한 것은

   A) 주식별자가 이름인 경우 -> 명칭, 내역등과 같이 이름으로 기술되는 것들은 주식별자로 지정하기에 부적절하다.

                                         특히 사람의 이름은 동명이인이 있을수 있기 때문에 주식별자로서 더더욱 부적절하다. 

Q5) 비식별자 관계로 연결하는 것을 고려해야 하는 경우로 부적절한 것은?

  A) 부모 엔터티에 참조값이 없어도 자식 엔터티의 인스턴스가 생성될 수 있는 경우

      부모엔터티의 인스턴스가 자식 엔터티와 같이 소멸되는 경우    -> 식별자 관계

      (비식별자 관계인 경우 자식엔터티만 남겨두고 부모가 소멸되는 경우도 있음)

      여러 개의 엔터티를 하나로 통합하면서 각각의 엔터티가 갖고 있던 여러개의 개별 관계가 통합되는 경우

      자식 쪽 엔터티의 주식별자를 부모엔터티와는 별도로 생성하는 것이 더 유리하다고 판단하는 경우

Q6) 식별자의 분류체계에 대한 설명

  A) 대표성 여부에 따라 분류도는 주식별자와 보조식별자에서 주식별자는 대표성을 가지며 참조관계 연결을 할수있다.

      스스로 생성여부에 따라 분류되는 식별자는 내부 식별자와 외부식별자이다

      둘 이상의 속성으로 구성된 식별자를 복합식별자라 하며 속성의 수에 따른 식별자 분류이다.

      업무적으로 만들어 지지 않지만 필요에 따라 인위적으로 만든 식별자를 인조식별자라 한다. 

- 모델링의 정의

  복잡한 현실세계를 일정한 표기법(추상화, 단순화, 명확화) 에 의해 표현하는일

- 모델링의 특징

  1) 추상화 : 현실세계를 일정한 형식에 맞추어 표현하는 것

  2) 단순화 : 복잡한 현실세계를 약속된 규약에 의해 쉽게 이해할 수 있도록 표현하는 것

  3) 명확화 : 누구나 이해하기 쉽게 정확하게 표현하는 것

- 모델링의 3가지 관점 

  1) 데이터관점(Data, What) : 어떤데이터와 관련있는지 데이터간의 관계는 무엇인지 모델링

  2) 프로세스 관점(Process, How) : 업무가 실제하고 있는 일은 무엇인지 무엇을 해야하는지 모델링

  3) 상관관점(Data vs Process) : 업무가 처리하는 일의 방법에 따라 데이터는 어떻게 영향을 받는지 모델링

- 데이터 모델링의 정의

  정보시스템을 구축하기 위한 데이터관점의 업무분석

  현실세계의 데이터에 대해 약속된 표기법에 의해 표현하는 과정

  데이터베이스를 구축하기 위한 분석/설계의 과정

- 데이터 모델이 제공하는 기능

   · 시스템을 현재 원하는 모습으로 가시화하도록 도와준다.

   · 시스템의 구조와 행동을 명세화 할 수 있게 한다.

   · 시스템을 구축하는 구조화된 틀을 제공한다.

   · 시스템을 구축하는 과정에서 결정한 것을 문서화한다.

   · 다양한 영역에 집중하기 위해 다른 영역의 세부 사항은 숨기는 다양한 관점을 제공한다.

   · 특정 목표에 따라 구체화된 상세 수준의 표현방법을 제공한다.

- 데이터 모델링의 중요성 및 유의점

  1) 파급효과

   시스템 구축이 완성되어 가는 시점에서 데이터 모델의 변경이 불가피한 상황이 발생한다면

   데이터 구조의 변경으로 전체시스템 구축 프로젝트에서 큰 위험요소이므로 다른 어떤 설계 과정보다 데이터 설계가       더 중요하다고 볼 수 있다.

  2) 복잡한 정보 요구사항의 간결한 표현

  3) 데이터 품질

    중복 데이터의 미정의 - 중복 데이터 모델은 여러 장소에 같은 정보를 저장하는 잘못을 하지 않도록 한다. 

    데이터 구조의 비즈니스 정의의 불충분 - 비유연성 데이터 모델은 유지보수의 어려움을 가중시킬 수 있다.

    동일한 성격의 데이터를 통합하지 않고 분리함으로써의 나타나는 데이터 불일치 - 데이터와 데이터 간 상호 연관관계에 대한 명확한 정의는 이러한 위험을 사전에 예방할 수 있다.

- 데이터 모델링의 3단계

 현실세계(개체) ->  개념적 데이터모델링 -> 논리적 데이터모델링 -> 물리적 데이터모델링 -> 저장 데이터베이스

1) 개념적 데이터 모델링

    추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행, 전사적 데이터 모델링, EA수립시 많이 이용

2) 논리적 데이터 모델링

    시스템으로 구축하고자 하는 업무에 대해 Key, 속성, 관계 등을 정확하게 표현, 재사용성이 높음

    "논리 데이터 모델은 데이터 모델링이 최종적으로 완료된 상태라고 정의, 즉 물리적인 스키마 설계를 하기 전 단계의       '데이터 모델' 상태를 일컫는 말"

    "누가, 어떻게, 그리고 전산화와는 별개로 비즈니스 데이터에 존재하는 사실들은 인식하여 기록하는 것"

    " 데이터 모델링 과정에서 가장 핵심이 되는 부분, ERD라는 그림으로 그려내는 과정"

    " 이 단계에서 수행하는 중요한 활동은 정규화"

3) 물리적 데이터 모델링

    실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계

    "테이블, 칼럼 등으로 표현되는 물리적인 저장 구조와 사용될 저장 장치, 자료를 추출하기 우해 사용될 접근방법 등"

- 프로젝트 생명주기(Life Cycle)에서 데이터 모델링

  일반적으로는 계획 또는 분석단계에서 개념적 데이터 모델링, 분석단계에서 논리적 데이터 모델링, 설계단계에서

  물리적 데이터 모델링이 수행된다.

- 데이터 모델링에서 데이터독립성의이 이해

1) 데이터독립성의 필요성

   유지보수 비용 증가, 데이터 복잡도 증가, 데이터 중복성 증가, 요구사항 대응 저하

    -> 끊임없이 요구되는 사용자 요구사항에 대해 화면과 데이터베이스 간에 서로 독립성을 유지하기 위한 목적

2) 데이터베이스 3단계 구조

   외부 단계(외부스키마)          <->        개념적 단계(개념스키마)           <->           내부 단계(내부스키마) 

                                논리적 데이터독립성                           물리적 데이터독립성

    · 외부 단계 : 사용자 관점으로 접근하는 특성에 따른 스키마 구성

    · 개념적 단계 : 통합관점

    · 내부 단계 : 물리적 저장구조  

3) 데이터독립성 구성요소

  · 외부스키마(External Schema)

     - View 단계 여러개의 사용자 관점으로 구성, 즉, 개개 사용자 단계로서 개개 사용자가 보는 개인적 DB스키마 

     - DB의 개개 사용자나 응용프래머가 접근하는 DB정의

  · 개념스키마(Conceptual Schema) 

    - 개념단계 하나의 개념적 스키마로 구성 모든 사용자 관점을 통합한 조직 전체의 DB를 기술하는 것

    - 모든 응용시스템들이나 사용자들이 필요로 하는 데이터를 통합한 조직 전체의 DB를 기술한 것으로 DB에 저장되는        데이터와 그들관의 관계를 표현하는 스키마

  · 내부스키마(Internal Schema) 

    - 내부단계, 내부스키마로 구성, DB가 물리적으로 저장된 형식

    - 물리적 장치에서 데이터가 실제적으로 저장되는 방법을 표현하는 스키마

4) 두 영역의 데이터독립성

5) 사상(Mapping)

   · 외부적/개념적 사상(논리적 사상) : 외부적 뷰와 개념적 뷰의 상호 관련성을 정의

               즉, 외부 화면이나 사용자에게 인터페이스 하기 위한 스키마 구조는 전체가 통합된 개념적 스키마와 연결됨

   · 개념적/내부적 사상(물리적 사상) : 개념적 뷰와 저장된 데이터베이스의 상호관련성 정의 

                즉, 통합된 개념적 스키마 구조와 물리적으로 저장된 구조의 물리적인 테이블스페이스와 연결되는 구조

- 데이터 모델링의 중요한 세가지 개념

1) 데이터 모델링의 세가지 요소

   · 업무가 관여하는 어떤 것(Things) : 모든 사람,사물, 개념

   · 어떤 것이 가지는 성격(Attributes) 

   · 업무가 관여하는 어떤 것 간의 관계 (Relationships)

2) 단수와 집합(복수)의 명명

- 데이터 모델링의 이해관계자

  데이터 모델링 기술/이해에 있어서 프로젝트 개발자 (가장 중요함) , 현업업무전문가 (이해할 수 있는 수준) , DBA 등

-데이터 모델의 표기법인 ERD의 이해

1) 데이터 모델 표기법

 데이터 모델에 대한 표기법으로 1976년 피터첸이 Entity-relationship model(E-R Model)이라는 표기법을 만듦

 [Chen 표기법] 엔터티는 사각형, 관계는 마름모, 속성은 타원형

  [IE표기법]

  [Barker 표기법]

2) ERD(Entity Relationship Diagram) 표기법을 이용하여 모델링하는 방법

 - ERD 작업 순서

   1. 엔터티를 그린다.

   2. 엔터티를 적절하게 배치한다.

   3. 엔터티간 관계를 설정한다.

   4. 관계명을 기술한다. 

   5 관계의 참여도를 기술한다.

   6. 관계의 필수여부를 기술한다.

- 엔터티 배치

   1. 가장중요한 엔터티를 왼쪽상단에 배치하고 이것을 중심으로 다른 엔터티를 나열하면서 전개.

      해당 업무에서 가장 중요한 엔터티는 왼쪽 상단에서 조금 아래쪽 중앙에 배치하여 전체 엔터티와 어울릴 수 있도록

      하면 향후 관계를 연결할떄 선이 꼬이지 않고 효과적으로 배치 가능 ex) 고객, 주문

   2. 업무흐름에 중심이 되는 엔터티는 타 엔터티와 많은 관게를 가지고 있으므로 중앙에 배치

      ex) 주문, 출고, 주문목록, 출고목록

   3. 업무를 진행하는 중심이 되는 엔터티와 관계를 갖는 엔터티들은 중심에 배치된 엔터티들 주위에 배치. 

      ex) 창고,고객, 사원,재고

- ERD 관계의 연결

  엔터티 배치가 되면 서로 관련있는 엔터티간에 관계를 설정, 초기에는 모두 Primary Key로 속성이 상속되는 식별자 계를 설정하고 중복되는 관계가 발생하지 않도록 함

- ERD 관계명 표시

  관계설정이 완료되면 관계이름을 부여하고 이름은 현재형을 사용, 지나치게 포괄적인 용어는 사용하지 않도록 한다.

  ex) 주문하다, 접수하다, 포함하다, 포함된다

- ERD 관계 관계차수와 선택상 표시

  관계이름 지정 후 얼마나 관계에 참여하는지를 나타내는 관계차수(Cardinality) 를 표현

  IE표기법으로는 하나(1,One)의 관계는 실선으로 표기, Barker표기법으로는 점선과 실선을 혼합하여 표기

  다수참여의 관계는 까마귀발과같은 모양으로 그려줌.

  필수/선택 표시는 관계선에 원을 표현

-좋은 데이터 모델의 요소

1)  완전성(Completeness) : 업무에서 필요로 하는 모든 데이턱 데이터 모델에 정의 되어 있어야 한다. 

2) 중복배제(Non-Redundancy) : 하나의 데이터베이스 내에 동일한 사실은 반드시 한번만 기록하여야 한다.

3) 업무규칙(Business Rules) : 데이터모델에서 중요한 요소 중 하나가 데이터 모델링 과정에서 도출되고 규명되는 수많은 업무규칙을 데이터 모델에 표현하고 이를 해당 데이터 모델을 사용하는 모든 사용자가 공유할 수 있도록 제공하는 것

4) 데이터 재사용(Data Reusablity) : 데이터의 재사용성을 향상시키고자 한다면 데이터의 동합성과 독립성에 대해서 충분히 고려해야함, 통합모델이여야만 데이터 재사용성을 향상 시킬 수 있음, 데이터가 애플리케이션에 대해 독립적으로 설계되어야만 재사용을 향상시킬 수 있음.

5) 의사소통(Communication) : 많은 업무 규칙들을 해당 정보시스템을 운용, 관리하는 많은 관련자들이 설계자가 정의한 업무 규칙들을 동일한 의미로 받아들이는 역할 을 하면 진정한 의사소통의 역할 

6) 통합성(Integration) : 가장 바람직한 데이터 구조의 형태는 동일한 데이터는 조직의 전체에서 한번만 정의되고 이를 다른 영역에서 참조, 활용하는 것

-엔터티의 개념

 엔터티 : 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것(Thing) 또는 업무 활동상 지속적인 관심을 가지고 있어야 하는 대상으로서 그 대상들 간에 동질성을 지닌 인스턴스 들이나 그들이 행하는 행위의 집합

(인스턴스 : 엔터티의 하나의 값 -> 엔터티는 인스턴스의 집합 ex) 인스턴스 : 수학,과학,영어,국어 /  엔티티 : 과목 )

  · 사람, 장소, 물건, 사건, 개념등의 명사에 해당 (눈에  보이지 않는 개념 등도 해당)

  · 업무상 관리가 필요한 관심사에 해당

  · 저장이 되기위한 어떤것

ex) 과목, 강사, 사건....

- 엔터티와 인스턴스에 대한 내용과 표기법

  대부분 사각형으로 표현

- 엔터티의 특징

  1) 업무에서 필요로 하는 정보

  2) 식별이 가능해야 함( 유일한 식별자 : 그 엔터티의 인스턴스만의 고유한 이름

                                 ex) 이름은 동명이인이 되므로 유일하게 식별불가능, 사원번호는 식별자 )

  3) 영속적으로 존재하는 인스턴스의 집합('한 개' 가 아니라 '두개 이상')

      : 하나의 엔터티는 여러개의 인스턴스를 포함 ex) 인스턴스가 한개밖에 없으면 집합이 아니므로 엔터티 성립x

  4) 업무프로세스에 의해 이용

  5) 속성을 포함( 반드시 속성이 있어야 함) 

     예외적으로 관계엔터티의 경우는 주식별자 속성만 가지고 있어도 엔터티로 인정

  6) 관계의 존재 (다른 엔터티와 최소 한개 이상의 관계가 있어야 함)

- 엔터티의 분류

  1) 유무형에 따른 분류

    · 유형엔터티 : 물리적 형태 ex) 사원, 물품, 강사 등

    · 개념엔터티 : 물리적인 형태는 존재하지 않고 관리해야 할 개념적 정보 ex) 조직, 보험상품 등

    · 사건엔터티 : 업무를 수행함에 따라 발생하는 엔터티  ex) 주문, 청구, 미납 등

  2) 발생시점에 따른 분류

     · 기본/키엔터티(Fundamental Entity, Key Entity) : 기본엔터티란 그 업무에 원래 존재하는 정보로서 다른 엔터티와 관계에 의해 생성되지 않고 독립적으로 생성이 가능하고 자신은 타 엔터티의 부모역할을 함, 자신의 고유한 주식별자를 가짐 ex) 사원, 부서, 고객, 상품, 자재 등

     · 중심엔터티(Main Entity) :  기본 엔터티로부터 발생되고 그 업무에 있어서 중심 역할을 함, 데이터의 양이 많이 발생되고 다른 엔터티와의 관계를 통해 많은 행위엔터티를 생성 ex) 계약, 사고, 예금원장, 청구, 주문 매출 등

     · 행위엔터티(Active Entity) : 두개 이상의 부모엔터티로부터 발생되고 자주내용이 바뀌거나 데이터량이 증가 ex) 주문목록, 사원변경이력

  3) 엔터티 분류 방법의 예

    유무형에 따라... 유형, 사건, 개념

    발생시점에 따라... 기본/키 , 중심, 행위

- 엔터티의 명명

  첫 번째, 가능하면 현업업무에서 사용하는 용어를 사용한다.

  두 번째, 가능하면 약어를 사용하지 않는다.

  세 번째, 단수명사를 사용한다.

  네 번째, 모든 엔터티에서 유일하게 이름이 부여되어야 한다.

  다섯 번째,  엔터티 생성의미대로 이름을 부여한다.

 - 속성의 개념

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

     ex) 이름, 주소, 생년월일, 계약일자, 전문분야

     · 업무에서 필요로 한다.

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

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

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

  1) 엔터티, 인스턴스, 속성, 속성값의 관계

    ·  한 개의 엔터티는 두 개 이상의 인스턴스의 집합이어야 한다

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

    ·  한 개의 속성은 한 개의 속성값을 갖는다.

  2) 속성의 표기법

  엔터티 내에 이름을 포함하여 표현

  ex) 엔터티 - 과목      / 강사       / 사건

       속성 - 과목이름  / 강사이름  /사건이름

- 속성의 특징

  · 엔터티와 마찬가지로 반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야한다. ex) 강사의 교재이름

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

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

- 속성의 분류

  1) 속성의 특성에 따른 분류

    · 기본속성(Basic Attribute) : 업무분석을 통해 바로 정의 한 속성, 업무로부터 추출한 모든 속성이 해당 ex) 제품이름

    · 설계속성(Designed Attribute) : 원래 업무상 존재하지는 않지만 설계를 하면서 도출해내는 속성, 데이터 모델링을 위해 또는 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성 ex) 일련번호

   · 파생속성(Derived Attribute) : 다른 속성으로부터 계산이나 변형이 되어 생성되는 속성 ex) 계산된 값

     데이터 정합성을 유지하기 위해 유의해야 할 점이 많으며 가급적 파생속성을 적게 정의하는 것이 좋다.

  2) 엔터티 구성방식에 따른 분류

   · PK(Primary Key) 속성 : 엔터티를 식별할 수 있는 속성

   · FK(Foreign Key) 속성 : 다른 엔터티와의 관계에서 포함된 속성

   · 일반속성 : PK,FK에 포함되어 있지 않은 속성

   · 복합 속성(Composite Attribute) : 여러 세부속성들로 구성되는 속성   ex) 주소 속성(시,구,동,번지)

   · 단순 속성(Simple Attribute) : 더 이상 다른 속성들로 구성될 수 없는 속성   ex) 나이, 성별

- 도메인 : 각 속성이 가질 수 있는 값의 범위

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

  ex) 학생이라는 엔터티가 있을 때 학점이라는 속성의 도메인은 0.0 에서 4.0 사이의 실수 값이며

       주소라는 속성은 길이가 20자리 이내인 문자열로 정의 

- 속성의 명명

첫 번째, 해당업무에서 사용하는 이름을 부여한다.

두 번째, 서술식 속성명은 사용하지 않는다.

세 번째, 약어사용은 가급적 제한한다.

네 번째, 전체 데이터모델에서 유일성 확보하는 것이 좋다.

- 관계의 개념

  1) 관계의 정의 : 인스턴스 사이의 논리적인 연관성으로서 존재 또는 행위로서 서로에게 연관성이 부여된 상태

                       관계는 엔터티와 엔터티 간 연관성이므로 엔터티의 정의에 따라 영향을 받기도 함

  2) 관계의 패어링 : 엔터티 안에 인스턴스가 개별적으로 관계를 가지는 것

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

       (Relationship Paring)이라 한다.

- 관계의 분류

  연관관계(Association)와 의존관계(Dependeny)

  : 연관관계는 항상 이용하는 관계로 표현방법은 실선,

   의존관계는 상대방클래스의 행위에 의해 관계가 형성될 때 구분하여 표현, 표현방법은 점선

- 관계의 표기법

  1) 관계명(Membership) : 관계의 이름

      관계가 시작되는 편 - 관계시작점 , 관계를 받는 편 - 관계끝점

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

      명명된다. 

     · 애매한 동사 피하기 ex) 관계된다, 관련이 있다 등은 구체적이지 않음

     · 현재현으로 표현하기 ex) 수강을 신청했다, 강의를 할 것이다. 표현 x -> 수강 신청한다. 

  2) 관계차수(Cardinality) : 1:1 , 1:M , M:M

  3) 관계선택사양(Optionality) : 참여하는 엔터티가 항상 참여하는지 아니면 참여할 수도 있는지를 나타내는 방법으로                                             필수(Mandatory)관계와 선택(Optional)관계이다 ex) 주문서와 주문목록 - 필수참여

                                                                                                            목록과 주문목록 - 선택참여 

      관계선택사양은 관계를 통한 상대방과의 업무적인 제약조건을 표현하는 것으로서 간단하면서 아주 중요한 표기법

- 관계의 정의 및 읽는 방법

   · 관계 체크사항  

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

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

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

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

   · 관계 읽기

     1. 기준(Source) 엔터티를 한 개(One) 또는 각(Each)으로 읽는다.

     2. 대상(Target) 엔터티의 관계참여도 즉 개수(하나, 하나 이상)를 읽는다

     3. 관계선택사양과 관계명을 읽는다

- 식별자의 개념 

  하나의 엔터티에 구성되어 있는 여러개의 속성 중에 엔터티를 대표 할 수 있는 속성

  ( 엔터티내에서 인스턴스들을 구분할 수 있는 구분자)

  반드시 하나의 유일한 식별자가 존재해야함 

- 식별자의 특징

  주식별자의 특징

  1) 주식별자에 의해 엔터티내에 모든 인스턴스들이 유일하게 구분되어야 한다  - 유일성

  2) 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다. - 최소성

  3) 지정된 주식별자의 값은 자주 변하지 않는 것이여야 한다. - 불변성

  4) 주식별자가 지정이 되면 반드시 값이 들어와야 한다. - 존재정 

 외부식별자의 특징 : 주식별자 특징과 일치하지 않으며 참조무결성 제약조건에 따른 특징을 가짐

- 식별자 분류 및 표기법

  1) 식별자 분류

  대표성 여부 - 주식별자, 보조식별자

  스스로 생성 여부 - 내부식별자(엔터티내부 스스로 만들어지는 식별자), 외부식별자(타 엔터티로부터 받아오는 식별자)

  속성의 수 - 단일 식별자, 복합식별자

  대체여부 - 본질식별자(업무에 의해 만들어지는 식별자), 인조식별자 ( 예를들어 주문번호= 사번+주문일자+순번)

   [ 식별자의 분류체계] 

  2) 식별자 표기법

- 주식별자 도출기준

  해당 업무에서 자주 이용되는 속성을 주식별자로 지정, 명칭이나 내역 등과 같이 이름으로 기술되는 것들은 가능하면    주식별자로 지정하지 않음, 복합으로 주식별자로 구성할 경우 너무 많은 속성이 포함되지 않도록 함

- 식별자 관계와 비식별자관계에 따른 식별자

  1) 식별자관계와 비식별자 관계의 결정

     외부식별자(Foreign Identifier) : 자기 자신의 엔터티에서 필요한 속성이 아니라 다른 엔터티와의 관계를 통해 자식                                                  쪽에 엔터티에 생성되는 속성 - FK역할

     이때 자식엔터티에서 부모엔터티로부터 받은 외부식별자를 자신의 주식별자로 이용할 것인지 또는 부모와 연결되         는 속성으로 사용할 것인지를 결정

  2) 식별자 관계

     자식엔터티의 주식별자로 부모의 주식별자가 상속이 되는 경우

  3) 비식별자 관계

      부모엔터티로부터 속성을 받았지만 자식엔터티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 경우

      비식별자 관계에 의한 외부식별자 생성

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

      ·  엔터티별로 데이터의 생명주기(Life Cycle)을 다르게 관리할 경우, 자식만 남겨두고 먼저 소멸될 수 있는 경우

      ·  여러개의 엔터티가 하나의 엔터티로 통합되어 표현되었는데 각각의 엔터티가 별도의 관계를 가질때

      ·  자식 엔터티에서 별도의 주식별자를 생성하는 것이 더 유리하다고 판단 될때 표현

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

                                                                   식별자 관계 

      식별자 관계로 인해 증가된 PK 속성의 수 : 식별자 관계만으로 연결된 데이 터 모델의 특징은 주식별자 속성이

      지속적으로 증가할 수 밖에 없는 구조로서 개발자 복잡성과 오 류가능성을 유발시킬 수 있는 요인이 될 수 있다

      (SQL 구문의 WHERE 절이 매우 길어짐)

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

    자식엔터티에서 데이터를 처리할 때 쓸데없이 부모엔터티까지 찾아가야 하는 경우가 발생됨

    ex) 단순하게 걸리는 하나의 조회조건도 비식별자 관계로만 데이터 모델링을 전개하면 SQL구문에 많은 조인이 걸리게 되는데 주식별자 속성을 상속받음으로 인해 자식엔터티에서 바로 조회의 조건을 이용하여 원하는 정보 가져올 수 있음, 성능과 개발의 용이성 측면에서는 식별자 관계가 우위에 있음

  6) 식별자 관계와 비식별자관계 모델링

DB2쿼리를 오라클 쿼리로 변환하는 프로젝트 진행하면서 발견한 문법차이 정리내용 :)

1. NULL 처리

2. 날짜 함수

 날짜계산 1

날짜계산2 (날짜를 쉽게 계산할 수 있지만 윤달계산에 이슈있음....!!! )

 날짜 'yyyymmddhh24miss' 포맷 변경 출력

 일 더하기/빼기

 현재 시각 가져오기 & dummy테이블

3. 문자함수

 문자열 내에서 지정 문자 위치구하기

 오른쪽부터 자리수까지 추출

4. 변환함수

6. NEXTVAL 

7. 인덱스 조회

인프런 인강 참고

[문제]

1. Weather Observation Station 17 : https://www.hackerrank.com/challenges/weather-observation-station-17/problem
2. Contest Leaderboard : https://www.hackerrank.com/challenges/contest-leaderboard/problem
3. New Companies : https://www.hackerrank.com/challenges/the-company/problem
   • Join을 사용하지 말고 다른 방법으로 풀어 보세요.
4. Occupations :c

[풀이]

1. Weather Observation Station 17

SELECT ROUND(LONG_W,4) FROM STATION 
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7770 )

나는 위에 방식처럼 풀어서 결과를 도출해냈지만

인강에서 문제풀이는 나와 달랐다.

인강 문제풀이 결과 - 큰결과를 정렬하고 그중에 첫번째 값만 가져오기

SELECT ROUND(LONG_W,4) 
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N
LIMIT 1

2. Contest Leaderboard

/*
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0  from your result.
*/

SELECT H.hacker_id , H.name , SUM(A.score) AS totalScore
FROM
(
SELECT S.hacker_id, S.challenge_id , MAX(S.SCORE) AS score  FROM Submissions S
GROUP BY S.hacker_id, S.challenge_id) A , Hackers H
WHERE A.hacker_id = H.hacker_id
GROUP BY H.hacker_id, H.name
having totalScore !='0'
ORDER BY totalScore DESC, H.hacker_id ASC

3.  New Companies 

/*
 write a query to print the company_code, founder name, total number of lead managers, 
 total number of senior managers, total number of managers, and total number of employees. 
 Order your output by ascending company_code.
*/

SELECT C.company_code, C.founder,
        count(distinct LM.lead_manager_code) AS totLeadManager , 
        count(distinct SM.senior_manager_code) AS totSeniorManager , 
        count(distinct M.manager_code) AS totManager ,
        count(distinct E.employee_code) AS totEmployee
FROM Company C , Lead_Manager LM  , Senior_Manager SM  ,  Manager M , Employee E
WHERE  C.company_code =LM.company_code
AND LM.lead_manager_code = SM.lead_manager_code
AND SM.senior_manager_code = M.senior_manager_code
AND M.manager_code = E.manager_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code

  서브쿼리로 푸는 방법

/*
 write a query to print the company_code, founder name, total number of lead managers, 
 total number of senior managers, total number of managers, and total number of employees. 
 Order your output by ascending company_code.
*/

SELECT C.company_code, C.founder,
        (SELECT count(distinct LM.lead_manager_code) FROM Lead_Manager LM 
         WHERE C.company_code = LM.company_code  ) AS totLeadManager , 
          (SELECT  count(distinct SM.senior_manager_code) FROM Senior_Manager SM
         WHERE C.company_code = SM.company_code  ) AS totSeniorManager , 
          (SELECT count(distinct M.manager_code) FROM Manager M
         WHERE C.company_code = M.company_code  ) AS totManager , 
          (SELECT count(distinct E.employee_code) FROM Employee E
         WHERE C.company_code = E.company_code  ) AS totEmployee               
FROM Company C
ORDER BY C.company_code

4. Occupations ***** 어려웠음

 - Case를 활용한 피벗 테이블 

첫번째 컬럼: Doctor , 두번째 컬럼 : Professor, 세번째 컬럼 : Singer, 네번째 컬럼: Actor

- ROW_NUMBER() OVER ( PARTITION BY ...ORDER BY ) 사용... <- 오라클의 경우에는 이 window함수로 끝!

- mysql의 경우 ROW_NUM 구현하기

- mysql의 경우 ROW_NUMBER(PARTITION BY 컬럼1 ORDER BY 컬럼2) 구현하기

- CASE 문을 MIN() 으로  감싼 이유 : MAX() 나 MIN() 에서 NULL 은 비교대상으로 삼지 않는다. 

SELECT MIN(CASE WHEN A.occupation = 'Doctor' THEN A.name else null END) doctor ,
       MIN(CASE WHEN A.occupation = 'Professor' THEN A.name else null END) professor ,
       MIN(CASE WHEN A.occupation = 'Singer' THEN A.name else null END) singer ,
       MIN(CASE WHEN A.occupation = 'Actor' THEN A.name else null END) actor     
FROM (
    SELECT 
        occupation, 
        name, 
        -- ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) RNUM    
        @rownum := IF(@value=occupation,@rownum+1,1) AS RNUM  ,     
        @value := occupation
    FROM OCCUPATIONS, 
        (SELECT @rownum :=0) AS R , -- @rownum 0 reset 
        (SELECT @value := '') AS P
    ORDER BY occupation, name
) A  
GROUP BY A.RNUM

1. Weather Observation Station 3 : https://www.hackerrank.com/challenges/weather-observation-station-3/problem
2. Weather Observation Station 19 : https://www.hackerrank.com/challenges/weather-observation-station-19/problem
3. Placements: https://www.hackerrank.com/challenges/placements/problem
4. Binary Tree Nodes: https://www.hackerrank.com/challenges/binary-search-tree-1/problem

답안

1. Weather Observation Station 3

   -ID가 짝수 인 결과 출력

/*
Query a list of CITY names from STATION for cities that have an even ID number
*/
SELECT DISTINCT(CITY) FROM STATION WHERE MOD(ID,2) = 0

2. Weather Observation Station 19

/*
Consider p1(a,c) and p2(b,d) to be two points on a 2D plane where (a,b) are the respective minimum and maximum values of Northern Latitude (LAT_N) and (c,d) are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION.

Query the Euclidean Distance between points p1 and p2 and format your answer to display 4 decimal digits.
*/

-- a: minimum(lat_n), b: maximum(lat_n), c: minimum(long_w), d: maximum(long_w)
-- p1(minimum(lat_n),minimum(long_w)) , p2(maximum(lat_n), maximum(long_w))

SELECT ROUND(SQRT(POW(max(lat_n)- min(lat_n),2) + POW(max(long_w)- min(long_w),2)),4)
FROM STATION

3. Placements

/*
Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.
*/

SELECT (SELECT S.NAME FROM Students S WHERE S.ID = F.ID)  
FROM  Friends F
inner join Packages P1 on F.ID = P1.ID
inner join Packages P2 on F.Friend_ID = P2.ID
WHERE P1.salary <  P2.salary
ORDER BY P2.salary

4. Binary Tree Nodes - 어려운 문제

Root : 부모 x

Leaf : 자식 x, 부모가 아님( P컬럼에서 등장 x) 

/*
N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.
*/

SELECT DISTINCT B1.N ,
       (CASE 
            WHEN B1.P IS NULL THEN 'Root'   
            WHEN  B2.N IS NULL THEN 'Leaf' 
            else 'Inner'    
        end
        ) AS result
FROM BST B1 
LEFT JOIN BST B2 ON B1.N = B2.P
ORDER BY B1.N

1. Population Density Difference: https://www.hackerrank.com/challenges/population-density-difference/problem
2. Weather Observation Station 11: https://www.hackerrank.com/challenges/weather-observation-station-11/problem
3. Weather Observation Station 13: https://www.hackerrank.com/challenges/weather-observation-station-13/problem
4. Top Competitors: https://www.hackerrank.com/challenges/full-score/problem

답안

1. Population Density Difference

SELECT MAX(population)- MIN(population)  FROM CITY

2. Weather Observation Station 11

/*
Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates
*/
SELECT distinct(CITY) FROM STATION 
WHERE SUBSTR(CITY,1,1) NOT IN ('a','e','i','o','u')
OR SUBSTR(CITY,-1) NOT IN ('a','e','i','o','u')

--LEFT(), RIGHT()함수 사용해도 됨
--WHERE LEFT(CITY,1) NOT IN ('a','e','i','o','u')
--OR RIGHT(CITY,1) NOT IN ('a','e','i','o','u')

3. Weather Observation Station 13

/*
Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880 and less than 137.2345 . Truncate your answer to 4 decimal places.
*/

SELECT truncate(sum(LAT_N),4) FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345

-- 해당값보다 크다했는데 아래와 같은경우 해당값도 포함되므로 정답이라 할 수 없음
-- BETWEEN은 LAT_N >= 38.7880 AND LAT_N <= 137.2345와 같은의미
--WHERE LAT_N BETWEEN 38.7880 AND 137.2345

4. Top Competitors   --> 어렵다....................... group by/ having

Difficulty table에 diufficulty_level에 해당하는 점수가 full score

1. Submissions 테이블의 challenge_id에 해당하는 값을

 Challenges 테이블 challenge_id 과 매핑 후 difficulty_level 가져오기

2. Difficulty 테이블의 difficulty_level에 해당하는 score가 full score에며

Submissions테이블의 score와 비교

3. Submissions 테이블의 hacker_id가 86870, 904111인 경우 full score와 동일

4. 하나보다는 더 많은 challenge에서 full score를 받은 경우는 hacker_id가 90411 인 경우임

코드 작성

/*
Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.
*/

SELECT h.hacker_id, h.name
FROM submissions s
    INNER JOIN Challenges c on s.challenge_id = c.challenge_id
    INNER JOIN Difficulty d on c.difficulty_level = d.difficulty_level
    INNER JOIN Hackers h on s.hacker_id = h.hacker_id
WHERE s.score = d.score
group by h.hacker_id, h.name
having count(distinct(s.submission_id)) > 1
order by  count(distinct(s.submission_id)) desc, h.hacker_id asc