SQLD

1. 데이터 모델링의 이해

  A. 데이터 모델링의 이해

    1) 데이터 모델의 이해

 

      a. 모델링의 특징

        - 추상화

        - 단순화

        - 명확화

 

      b. 모델링의 세 가지 관점 

        - 데이터 관점

        - 프로세스 관점

        - 데이터와 프로세스의 상관 관점

 

      c. 데이터 모델링의 중요성

        - 파급효과 (Leverage)

        - 간결한 표현 (Conciseness)

        - 데이터 품질 (Data quality)

 

      d. 데이터 모델링의 3단계

        - 개념적 데이터 모델링 : 추상화 수준 높음, 전사적 데이터 모델링, EA 수립 시 많이 이용

        - 논리적 데이터 모델링 : 재사용성 높음

        - 물리적 데이터 모델링 : 성능, 저장 등 물리적인 성격을 고려

 

      e. 데이터 독립성 요소

        - 외부스키마 (External schema) : 사용자 관점

        - 개념스키마 (Conceptual schema) : 통합 관점

        - 내부스키마 (Internal schema) : 물리적 저장 구조

 

      f. 데이터 모델링 세 요소

        - 업무가 관여하는 어떤 것 (Things)

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

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

 

    2) 엔터티 (Entity)

 

      a. 유무형에 따른 분류

        - 유형엔터티

        - 개념엔터티

        - 사건엔터티

 

      b. 발생시점에 따른 분류

        - 기본/키엔터티

        - 중심엔터티

        - 행위엔터티

 

    3) 속성 (Attribute)

 

      a. 엔터티, 인스턴스, 속성, 속성값의 관계

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

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

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

 

      b. 속성의 분류

        - 기본 속성

        - 설계 속성

        - 파생 속성

 

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

 

    4) 관계 (Relationship)

 

      a. 관계의 표기법

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

        - 관계차수 (Degree/Cardinality) : 1:1, 1:M, M:N

        - 관계선택사양 (Optionality) : 필수관계, 선택관계

 

    5) 식별자 

 

      a. 식별자의 특징

        - 유일성 : 주식별자에 의해 엔터티 내에 모든 인스턴스들을 유일하게 구분

        - 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함

        - 불변성 : 주식별자가 한 번 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야 함

        - 존재성 : 주식별자가 지정되면 반드시 데이터 값이 존재

 

      b. 식별자 분류

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

        - 스스로 생성여부 : 내부식별자, 외부식별자

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

        - 대체여부 : 본질식별자, 인조식별자

 

  B. 데이터 모델과 성능

    1) 성능 데이터 모델링의 개요

 

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

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

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

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

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

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

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

 

    2) 정규화와 성능

 

    3) 반정규화와 성능

 

      a. 테이블 반정규화

        - 테이블 병합 : 1:1, 1:M, 슈퍼/서브타입

        - 테이블 분할 : 수직분할, 수평분할

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

 

      b. 칼럼 반정규화

        - 중복칼럼 추가

        - 파생칼럼 추가

        - 이력테이블 칼럼 추가

        - PK에 의한 칼럼 추가

        - 응용시스템 오작동을 위한 칼럼 추가

      

      c. 관계 반정규화

        - 중복관계 추가

 

    4) 대량 데이터에 따른 성능

 

      a. Partition

        - Range partition

        - List partition

        - Hash partition

 

    5) 데이터베이스 구조와 성능

 

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

 

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

 

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

 

    6) 분산 데이터베이스와 성능

 

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

        - 분할 투명성

        - 위치 투명성

        - 지역사상 투명성

        - 중복 투명성

        - 장애 투명성

        - 병행 투명성

 

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

        - 테이블 위치 분산

        - 테이블 분할 분산 (수직/수평)

        - 테이블 복제 분산 (부분/광역)

        - 테이블 요약 분산 (분석/통합)

 

2. SQL 기본 및 활용

  A. SQL 기본

    1) 관계형 데이터베이스 기본

 

    2) DDL (DATA DEFINITION LANGUAGE) - CREATE / ALTER / DROP / RENAME

 

      a. 제약조건 (Constraint)

        - Primary key : Unique key + Not NULL

        - Unique key : NULL 입력 가능

        - Not NULL : NULL 입력 금지

        - Check : True or False, 입력할 수 있는 값의 범위 등을 제한

        - Foreign key : 참조 무결성 제약 옵션

 

    3) DML (DATA MANIPULATION LANGUAGE) - SELECT / INSERT / UPDATE / DELETE

 

    4) TCL (TRANSACTION CONTROL LANGUAGE) - COMMIT / ROLLBACK

 

      a. 트랜잭션의 특징

        - 원자성 (Atomicity) : All or nothing
        - 일관성 (Consistency) 

        - 고립성 (Isolation)

        - 지속성 (Durability)

 

    5) WHERE 절

 

      a. 연산자의 종류

        - 비교연산자 : =, >, >=, <, <=

        - SQL 연산자 : Between a And b, IN, Like, Is null

        - 논리 연산자 : AND, OR, NOT

        - 부정비교 연산자 : !=, ^=, <>, NOT 칼럼명 =, NOT 칼럼명 >

 

      b. 연산자 우선 순위 : 괄호 -> Not 연산자 -> 비교 연산자, SQL 연산자 -> AND -> OR

 

    6) 함수 (FUNCTION)

 

      a. 단일행 함수

        - 문자형 함수 : LOWER, UPPER, LEN, LTRIM, RTRIM

        - 숫자형 함수 : ABS, MOD, ROUND 

        - 날짜형 함수 : SYSDATE

        - 변환형 함수 : TO_NUMBER, TO_DATE, TO_CHAR

        - NULL 관련 함수 : NVL, ISNULL, NULLIF, COALESCE

 

    7) GROUP BY, HAVING 절

 

      a. 집계함수

        - COUNT

        - SUM

        - AVG 

 

    8) ORDER BY 절

 

      a. SELECT 문장 실행 순서

        - 발췌 대상 테이블을 참조한다 (FROM)

        - 발췌 대상 데이터가 아닌 것은 제거한다 (WHERE)

        - 행들을 소그룹화 한다 (GROUP BY)

        - 그룹핑된 값의 조건에 맞는 것만을 출력한다 (HAVING)

        - 데이터 값을 출력/계산한다 (SELECT)

        - 데이터를 정렬한다 (ORDER BY)

 

    9) 조인 (JOIN)

 

  B. SQL 활용

    1) 표준 조인 (STANDARD JOIN)

 

      a. 일반 집합 연산자

        - Union

        - Intersection

        - Difference

        - Product

 

      b. 순수 관계 연산자

        - Select

        - Project

        - (Natural) Join

        - Divide

 

      c. FROM 절 JOIN 형태

        - INNER JOIN : 동일한 값이 있는 행만 반환

        - NATURAL JOIN : 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행

        - USING 조건절 : FROM절의 USING 조건절을 이용, 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 수행

        - ON 조건절 : 칼럼명이 다르더라도 JOIN 조건을 사용 가능

        - CROSS JOIN : CARTESIAN PRODUCT

        - OUTER JOIN : LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

 

    2) 집합 연산자 (SET OPERATOR)

 

      a. 집합 연산자 종류

        - UNION

        - UNION ALL

        - INTERSECT

        - EXCEPT

 

    3) 계층형 질의와 셀프 조인

 

      a. 계층형 질의

 

      b. 셀프 조인 

        - 동일 테이블 사이의 조인

 

    4) 서브쿼리 

 

      a. 서브쿼리 사용 시 주의점

        - 서브쿼리를 괄호를 감싸서 사용

        - 단일행 또는 복수행 비교 연산자와 함께 사용 가능

        - 서브쿼리에는 ORDER BY를 사용하지 못함, ORDER BY는 메인쿼리 마지막 문장에 위치

 

      b. 동작하는 방식에 따른 서브쿼리 분류

        - Uncorrelated (비연관) 서브쿼리

        - Correlated (연관) 서브쿼리 

 

      c. 반환되는 데이터의 형태에 따른 서브쿼리 분류

        - 단일행 서브쿼리

        - 다중행 서브쿼리 : IN, ALL, ANY, EXISTS

        - 다중칼럼 서브쿼리

 

      d. 서브쿼리가 사용 가능한 곳

        - SELECT 절

        - FROM 절

        - WHERE 절

        - HAVING 절

        - ORDER BY 절 

        - INSERT 문의 VALUES 절

        - UPDATE 문의 SET 절

 

    5) 그룹 함수 (GROUP FUNCTION)

 

      a. ROLLUP 함수

        - Subtotal을 생성하기 위해 사용

 

      b. CUBE 함수

        - Grouping columns이 가질 수 있는 모든 경우의  수에 대하여 Subtotal 생성

 

      c. GROUPING SETS 함수

        - 

 

    6) 윈도우 함수 (WINDOW FUNCTION)

 

      a. WINDOW FUNCTION 종류

        - 그룹 내 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER

        - 그룹 내 집계 함수 : SUM, MAX, AVG, COUNT

        - 그룹 내 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD

        - 그룹 내 비율 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

        - 통계 분석 관련 함수 : CORR, COVAR_POP, STDDEV, VARIANCE

 

    7) DCL (DATA CONTROL LANGUAGE)

 

    8) 절차형 SQL

 

      a. PL/SQL 특징

        - PL/SQL의 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.

        - 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.

        - IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

        - DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

        - PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.

        - PL/SQL은 응용 프로그램의 성능을 향상시킨다.

        - PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 

      b. PL/SQL 구조

        - DECLARE

        - BEGIN ~ END

        - EXCEPTION

        - END

 

  C. SQL 최적화 기본 원리

    1) 옵티마이저와 실행계획

      

      a. 옵티마이저

        - 규칙기반 옵티마이저 (RBO)

        - 비용기반 옵티마이저 (CBO)

 

      b. 실행계획

        

      c. SQL 처리 흐름도

 

    2) 인덱스 기본

 

      a. 인덱스 종류

        - 트리 기반 인덱스

        - 클러스터형 인덱스

 

      b. 전체 테이블 스캔과 인덱스 스캔

        - 전체 테이블 스캔

        - 인덱스 스캔

 

    3) 조인 수행 원리

 

      a. NL Join : 반복문과 유산한 방식으로 조인 수행

 

      b. Sort Merge Join : NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되던 조인 기법

 

      c. Hash Join : NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 문제점인 정렬 작업의 부담의 대안