Colin's Blog
[SQL] 데이터 모델링의 이해

오답노트

데이터 모델링의 이해

  1. 데이터 모델링이 필요한 주요 이유
    • 업무 정보를 구성하는 기초가 되는 정보들에 대해 일정한 표기법에 의해 표혐
    • 정보 시스템 구축의 대상이 되는 업무 내용을 정확하게 분석
    • 분석된 모델을 가지고 데이터베이스를 생성하여 개발 및 데이터 관리에 사용

  2. 데이터 모델링의 유의점

    • 비유연성: 데이터 모델을 어떻게 설계했느냐에 따라 사소한 업무변화에도 데이터 모델이 수시로 변경 -> 유지 보수 어려움 가중

  3. 개념적 데이터 모델링: 전사적 데이터 모델링 수행, 추상화 수준이 높고 업무 중ㅅ미적이고 포괄적인 수준의 모델링 진행

    물리적 데이터 모델링: 실제 데이터 베이스에 이식할 수 있도록 성능, 저장 등 물리적 성격 고려

  4. 스키마

    • 외부 스키마

    • 개념 스키마: 통합관점의 스키마 구조 표현

      -> 데이터 모델링 = 통합 관점의 뷰를 가지고 있는 개념 스키마를 만듦

    • 내부 스키마

  5. 엔터티의 특징

    1) 반드시 해당 업무에서 필요하고 관리하고자 하는 정보

    2) 유일한 식별자에 의해 식별 가능

    3) 영속적으로 존재하는 인스턴스의 집합(두 개 이상)

    4) 엔터티는 업무 프로세스에 의해 이용되어야 함

    5) 반드시 속성 존재

    6) 다른 엔터티와 최소 한 개 이상의 관계 존재

  6. 엔터티 이름 부여 방법
    • 현업 업무에서 사용하는 용어
    • 가능하면 약어 사용X
    • 단수 명사
    • 유일하게 이름 부여
    • 엔터티 생성의미대로 이름 부여
  7. 속성
    • 더 이상 분리되지 않는 최소 데이터 단위
    • 업무상 관리가 가능한 최소한의 단위
  8. 속성 특성에 따른 부류
    • 기본 속성: 원래 가지고 있어야 하는 속성
    • 설계 속성
    • 파생 속성: 데이터 조회시 빠른 성능을 냄, 원래 속성의 값을 계산하여 저장

  9. 도메인: 각 언테터의 속성에 대해 어던 유형의 값이 들어가는지를 정의하는 개념

  10. 데이터 모델링 관계
    • 존재적 관계와 행위에 의한 관계를 구분하는 표기법X
    • UML은 연관관계와 의존관계에 대해 다른 표기법을 가지고 표현
  11. 관계
    • 존재적 관계와 행위에 의한 관계로 나뉨
    • 관계명, 관계차수, 선택성(선택사양)
  12. 엔터티 사이에 정의한 관계 체크
    • 두 개의 엔터티 사이에 관심 있는 연관규칙이 존재하는가?
    • 두 개의 엔터티 사이에 정보 조합이 발생되는가?
    • 업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가?
    • 업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?

  13. 주식별자 도출 기준

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

    특징

    • 유일성
    • 최소성
    • 불변성
    • 존재성: Null값 X

    데이터 모델과 성능

    1. 데이터 모델링

      • 분석/설계 단계에서 데이터베이스 처리 성능 향상 방법을 고려
      • 프로젝트 초기에 운영환경에 대비한 테스트 환경 구현 -> 트랜잭션을 발생시켜 실제 성능 테스트
      • 데이터 모델의 구조 변경하여 어떤 구조가 해당 사이트에 성능상 가장 적절한 구조인지를 검토
      • 데이터 증가가 빠를수록 성능저하 개선 비용 증가

    2. 반정규화

      • 성능을 충분히 고려하기 위해 데이터 모델링 단계에서 성능 데이터 모델링 수행 절차에 대한 설명

        정규화 -> 용량 산정 -> 트랜잭션 유형 파악 -> 용량과 틀내잭션의 유형에 따라 반정규화 -> 이력모델 조정, PK/FK조정, 슈퍼타입/서브타입 조정 -> 성능관 점에서 데이터모델 검증

    3. 함수종속성: 2차 정규화

    4. 반복적인 속성값을 갖는 형태 - 제 1 정규화 대상
      • 반복 속성에 인덱스 생성 시 검색 속도 향상, 입력, 수정, 삭제 성능 저하
    5. 반정규화 대상: 다수 테이블에 대한 다량의 조인이 필요할 때
      • 반정규화는 성능 향상
      • 이전 또는 이후 위치 레코드는 window function으로 가능
    6. 디스크 I/O를 줄이기 위한 반정규화 기법: 테이블 추가 - 부분 테이블 추가

    7. FK 속성추가: 데이터모델링 관계 연결에서 나타나는 자연스러운 현상

    8. 파티셔닝: 성능저하 방지, 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리

      • 논리적으로 하나의 테이블이지만 물리적으로 여러 개의 테이블로 분리하여 데이터 액세스 성능 향상, 데이터 관리방법 개선

    9. UNION: 두 개의 SELECT 결과를 합함 -> 중복된 행은 하나만 표시

      • 컬럼의 개수가 같아야 함
      • 데이터 타입이 같아야 함

      UNION ALL: 중복을 제거하지 않음

    10. 슈퍼/서브 타입 데이터 모델

      • 개별로 발생되는 트랜잭션은 개별 테이블로 구성
      • 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입 + 서브타입 테이블로 구성
      • 전체를 하나로 묶어 트랜잭션이 발생할 때 하나의 테이블로 구성

      트랜잭션

      • 전체를 대상으로 일괄 처리, 테이블 서브타입 별로 개별 유지 시 UNION 연산에 의해 성능 저하
      • 항상 서브타입 개별로 처리, 테이블 하나로 통합 시 불필요한 많은 양의 데이터 직접하여 성능 저하
      • 트랜잭션은 항상 슈퍼+서브 타입, 개별로 유지 시 조인에 의해 성능 저하
      • 트랜잭션은 항상 전체를 통합하여 분석 처리 시 , 하나의 테이블로 통합되어 있으면 성능이 우수 (조인 감소)

    11. 인덱스

      • EQUAL 조건 -> 범위 조회

    12. 분산 데이터베이스: 여러 곳으로 분산되어있는 데이터베이스를 하나의 가상 시스템으로 사용

      • 지역 자치성, 점증적 시스템 용량 확장
      • 신뢰성, 가용성
      • 효용성, 융통성
      • 빨느 응답 속도와 통신비용 절감
      • 데이터의 가용성과 신뢰성 증가
      • 시스템 규모의 적절한 조절
      • 각 지역 사용자의 요구 수용 증대

      효율성 증대

      • 공통 코드, 기준 정보 등 마스터 데이터는 분산 데이터 베이스에 복제 분산을 적용
      • 실시간 업무적인 특성을 가지고 있을 때 분산 데이터베이스를 사용하여 구성
      • 백업 사이트를 구성할 때 간단하게 분산 기능 적용하여 구성

      GSI: 통합된 한개의 인스턴스, 통합 데이터베이스 구조를 의미 -> 분산데이터베이스와 대치되는 개념

SQL 기본

  1. 데이터 제어어(DCL)

    • REVOKE, GRANT

    데이터 정의어(DDL)

    • CREATE, ALTER, DROP, RENAME

    트랜잭션 제어어(TCL)

    • COMMIT, ROLLBACK, SavePoint

  2. 데이터 구조를 정의: 데이터 정의어(DDL)

    • CREAT, ALTER, DROP

  3. TCL: 트랜젝션 별로 제어하는 명령어 -> DCL이기도 함

  4. 비절차적 데이터 조작어: 무슨 데이터를 원하는 지 명세

    • DML: 응용프로그램이나 질의어를 통해 db에 실질적 접근

      SELECT, INSERT, DELETE, UPDATE

    • DDL: 스키마, 도메인, 테이블, 뷰, 인덱스 정의

    • 데이터 부속어: 호스트 프로그램 속 삽입되어 사용되는 DML 명령어

    절자척 데이터 조작어: 어떻게 데이터를 접근하는 지 명세

    • PL/SQL, T-SQL
  5. NULL
    • 모르는 값
    • 값의 부재
    • 비교할 때 알 수 없음 반환(IS NULL 제외)
    • 공백문자 또는 0과 일치X

  6. primary key 지정: ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMP_NO);

    index 생성: CREATE INDEX IDX_EMP_01 ON EMP (JOIN_DATE);

  7. 외래키
    • Null 값을 가질 수 있음
    • 참조 무결성의 제약을 받을 수 있음
    • 테이블 생성시 설정 가능
    • 한 테이블에 여러개 존재 가능

  8. 컬럼 삭제: Alter table 테이블명 drop column 컬럼명

  9. Casecade: 부모 삭제 - 자식 삭제

    Set Null: 부모 삭제 - 자식의 Null 삭제

    Set Default: 부모 삭제 - 자식의 Default 값 설정

    Restrict: 자식 테이블에 PK 값이 없는 경우만 부모 삭제

    No Action: 참조 무결성 위반 액션을 취하지 않음

  10. 이름 변경: Rename stadium to stadium_jsc;

  11. Insert Action

    Automatic: 부모에 PK가 없는 경우 부모의 PK 생성 후 자식 입력

    Set Null: 부모의 PK가 없는 경우 자식의 외부키를 Null값으로 처리

    Dependent: 부모에 PK가 존재할 때만 자식 입력 허용

  12. Truncate, Drop -> 로그를 남기지 않음

  13. DISTINCT: 데이터 중복 제거

  14. Dirty Read: 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽음

    Non-Repeatable Read:한 트랜잭션 내에서 같은 쿼리를 두번 수행 했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리의 결과가 다르게 나타나는 현상

    Phantom Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번재 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

  15. 오라클: DDL 문장 수행 후 자동으로 commit 수행, DDL 실행 후 트랜젝션 종료

    SQL Serve: DDL 수행 후 자동 commit 수행X

  16. Insert into 서비스 Values(‘999’, ‘’, ‘2015-11-11’);

    ’’: 공백 데이터 삽입

    SQL Server에서 조회 가능( ‘‘로 찾으면 됨)

    Oracle에서 is null 조건으로 조회

  17. Length: 문자열 길이

    Replace(C1, CHR(10)): C1을 CHR(10) -> 줄바꿈제거로 치환

    CHR: 주어진 아스키코드에 대한 문자를 반환 -> CHR(10): 줄바꿈

    Concat: 문자열1과 문자열 2 연결 (   , +와 같음)

    Substr/Substring(문자열, m, n): 문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자를 돌려줌

    Ltrim(문자열, 지정문자): 문자열의 첫 문자부터 확인해서 지정문자가 나타나면 해당 문자 제거 -> SQL Server는 지정 문자 사용x(공백만 제거)

    Rtrim: 문자열 마지막 부터 확ㅇ니하여 지정문자가 나타나는 동안 해당 문자 제거

    Trim(leading   trailing   both 지정문자 From 문자열): 문자열에서 지정 문자 제거

  18. 1/24/60 = 1분

    1/24/(60/10) = 10분

  19. searched -> simple

    case when loc = ‘new york’ then ‘east’ -> case loc when ‘new york’ then ‘east’

  20. NULLIF(EXPR1, EXPR2): EXPR1 == EXPR2이면 NULL, 같지 않으면 EXPR1을 RETURN

    NVL(EXPR1, EXPR2) / ISNULL(EXPR1, EPXR2): EXPR1 == NULL이면 EPXR2의 값을 출력

    COLAESCE(EXPR1, EXPR2): NULL이 아닌 최초의 표현식을 나타냄, 모든 표현식이 NULL이면 NULL리턴

  21. NULL이 포함된 연산 결과는 NULL 출력

  22. COALESCE: NULL이 아닌 첫번째 값

  23. NULL은 COUNT에서 제외됨

  24. GROUP BY에서 HAVING절 사용: FILTER 역할

    중첩된 그룹 함수 -> 최종 결과값은 1건

  25. CASE를 이용해 순서 정렬 -> A 우선
  26. SELECT 실행 순서: FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
  27. 동일한 결과값 함께 출력: WITH TIES
  28. 여러 테이블 중 원하는 데이터 조회: N-1개의 JOIN 조건 필요
© 2018 Colin. All rights reserved.