노랭이 n회독을 하면서 내가 헷갈려하는 것만 정리하였다.

과목 I. 데이터 모델링의 이해

제 1장. 데이터 모델링의 이해

데이터 모델링 시 유의해야할 사항

• 중복: 여러 장소에 같은 정보 저장 X
• 비유연성: 데이터의 정의를 데이터의 사용 프로세스와 분리하여 유연성 높임 O
• 비일관성: 상호 연관관계를 명확하게 정의하여 일관성 있게 데이터가 유지되도록 O / 프로그램과 테이블의 연계성 높이기 X

식별자 분류

• 대표성 여부
  • 주식별자:유일성, 최소성, 불변성, 존재성을 가진 대표 식별자 / 다른 엔턴티와 참조 관계로 연결
  • 보조식별자: 인스턴스를 식별할 수는 있지만 대표 식별자가 아님 / 다른 엔터티와 참조 관계로 연결되지 않음 (ex. 회원 엔터티의 아이디)
• 스스로 생성되었는지 여부
  • 내부식별자: 엔터티 내부에서 스스로 생성된 식별자
  • 외부식별자: 다른 엔터티에서 온 식별자, 다른 엔터티와의 연결고리 역학 (ex. FK)
• 단일 속성의 여부
  • 단일식별자: 하나의 속성으로 구성된 식별자
  • 복합식별자: 두 개 이상의 속성으로 구성된 식별자
• 대체 여부
  • 본질식별자: 업무 프로세스의 존재하는 식별자, 가공되지 않은 원래의 식별자
  • 인조식별자: 주식별자의 속성이 두 개 이상인 경우 그 속성들을 하나로 묶어서 사용하는 식별자 (ex. 주문번호 = 주문일자 + 순번)

제 2장. 데이터 모델과 성능

데이터 모델링의 순서

• 정규화 수행
• 용량 산정
• 트랜잭션 유형 파악
• 반정규화 수행
• 이력모델 조정, PK/FK 조정, 슈퍼/서브타입 조정 수행
• 데이터 모델 검증

정규화 설명

• 1차 정규화: 원자값이 아닌 도메인 분해
• 2차 정규화: 부분 함수 종속성 제거
• 3차 정규화: 이행 함수 종속성 제거

반정규화를 고려할 때 판단요소에 대한 설명

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

반정규화 기법 예시

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

칼럼수가 많은 테이블에 대한 설명

• 자주 접근하는 칼럼들과 상대적으로 접근 빈도가 낮은 칼럼들을 구분하여 1:1로 분리 O
• 칼럼의 위치 조정 X -> 나중에 데이터가 채워지게 될 경우 더 많은 로우 체이닝이 발생할 수도 있음

파티셔닝

• 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하여 데이터 액세스 성능도 향상시키고, 데이터 관리방법도 개선할 수 있도록 테이블에 적용하는 기법

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

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

분산 데이터베이스 장단점

장점

• 지역 자치성, 점증적 시스템 용량 확장
• 데이터의 신뢰성과 가용성 증가
• 효용성, 융통성
• 빠른 응답 속도와 통신비용 절감
• 시스템 규모의 적절한 조절
• 각 지역 사용자의 요구 증대
• 공통코드, 기준정보 등과 같은 마스터 데이터를 한 곳에 두고 운영하는 경우 원격지에서의 접근이 빈번할수록 실시간 업무처리에 대해 좋은 성능을 얻기가 어려울 수 있기 때문에 분산 환경에 복제 분산을 하는 방법으로 분산 데이터베이스를 구성할 수 있다.
• 백업 사이트 구성에 대해서도 분산 환경으로 구성하여 적용할 수 있다.

단점

• 소프트웨어 개발 비용
• 오류의 잠재성 증대
• 처리 비용의 증대
• 설계, 관리의 복잡성과 비용
• 불규칙한 응답 속도
• 통제의 어려움
• 데이터 무결성에 대한 위협
• Global Single Instance(GSI)는 통합된 한 개의 인스턴스 즉, 통합 데이터베이스 구조를 의미하므로 분산데이터베이스와는 대치되는 개념이다.

과목 II. SQL 기본 및 활용

제 1장. SQL 기본

DML: 저장된 데이터베이스를 실질적으로 접근하는데 사용

• SELECT / INSERT / UPDATE / DELETE
• 절차적 데이터 조작어: 사용자가 어떻게(How) 데이터를 접근하는지 명세. PL/SQL(오라클), T-SQL(SQL Server)
• 비절자적 데이터 조작어: 사용자가 무슨(What) 에디터를 원하는지만 명세
• DELETE FROM 테이블명 O / DELETE * FROM 테이블명 X

DDL: 데이터 정의

• CREATE / ALTER / DROP / RENAME / TRUNCATE
• 오류 발생: ALTER TABLE PRODUCT ADD PRIMARY KEY PRODUCT_PK ON (PROD_ID)
• 오류 수정: ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PROK_ID)
• 불필요한 칼럼 삭제: ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;
• 테이블 이름 변경: RENAME 원테이블명 TO 바꿀테이블명;

DCL: User 생성, 권한 부여

• GRANT / REVOKE

TCL: 트랜잭션을 제어하는 명령어

• COMMIT / ROLLBACK / SAVEPOINT
• 트랜잭션: 쪼개질 수 없는 업무처리의 단위
  • 원자성: 모두 성공하거나 모두 실패 (All or Nothing)
  • 일관성: 완료 후에도 데이터의 일관성이 있어야
  • 고립성: 고립되어 수행되어야 / 다른 트랜잭션의 영향 받지 X
  • 지속성: 영구적으로 저장되어야

제약조건

• SQL 제약조건 / 참조 무결성 규정 관련 옵션
  

DROP / TURNCATE / DELETE

DROP

• Rollback 불가
• Auto Commit
• 테이블의 정의 자체 완전 삭제

TRUNCATE

• Rollback 불가
• Auto Commit
• 테이블을 최초 생성된 초기 상태로 만듦
• UNDO를 위한 데이터를 생성하지 않기 때문에 동일량 삭제시 DELETE보다 빠름

DELETE

• Commit 이전 Rollback 가능
• 사용자 Commit
• 데이터만 삭제

NULL

NVL(값1, 값2) / ISNULL(값1, 값2)

• 값1이 Null인 경우 값2 반환
• 값1이 Null이 아닌 경우 값1 반환

NULLIF(값1, 값2)

• 값1 = 값2 -> Null 반환
• 값1이 값2와 같지 않으면 값1 반환

COALESCE

• Null이 아닌 최초의 표현식

Oracle

• ''은 NULL로 입력, 조회하기 위해서는 IS NULL로

SQL Server

• ''은 공백으로 입력, 조회하기 위해서는 =''로

Count

• Count(*): 전체 행의 수 카운트, NULL 포함
• Count(컬럼명): NULL 제외한 행 수 카운트

JOIN

• 일반적으로 Join은 PK와 FK값의 연관성에 의해 성립
• DBMS 옵티마이저는 From절에 나열된 테이블들을 항상 2개 (임의로 3개 X)씩 묶어서 Join을 처리한다
• EQUI Join은 Join에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용
• EQUI Join은 = 연산자에 의해서만 수행, 그 이외의 비교 연산자를 사용하는 경우에는 모두 Non EQUI Join
• 대부분 Non EQUI Join을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있음

이 외 헷갈리는 것

• SELECT 절에 기술되지 않은 칼럼으로도 정렬할 수 있다.

SELECT 	지역, 매출금액
FROM	지역별매출
ORDER BY 년 ASC;

• GROUP BY를 사용할 경우에는 SELECT 절에서 SUM과 같은 그룹함수를 제외한 항목은 GROUP BY 절에 먼저 사용되어야 함

// 오류 발생
SELECT 	지역, 매출금액
FROM	지역별매출
GROUP BY 지역
ORDER BY 년 DESC;

• ORDER BY 절에는 GROUP BY 절과 관련하여 그 기준이 되는 칼럼들 또는 그룹 함수가 사용 가능함
• 테이블이 같는 칼럼인 경우 조회 list가 아니어도 사용 가능
• 중첩된 그룹함수의 경우 최종 결과값은 1건이 될 수밖에 없기에 GROUP BY절에 기술된 메뉴ID와 사용유형코드는 SELECT 절에 기술될 수 없음

// 오류 발생
SELECT 	메뉴ID, 사용유형코드, AVC(COUNT(*)) AS AVGCNT
FROM	시스템사용이력
GROUP BY 메뉴ID, 사용유형코드;

제 2장. SQL 활용

서브쿼리

• ORDER BY절 INSERT문의 VALUE 절 등에 사용 가능
• 주의) 서브쿼리에서는 ORDER BY를 사용할 수 없다.
• 다중행 서브 쿼리의 경우 =조건과 함께 사용할 수 없음
• 다중 컬럼 서브쿼리의 경우 IN 절과 함께 사용할 수 있음
• 연관 서브쿼리: 메인 쿼리의 칼럼이 포함된 서브쿼리
• 비연관 서브쿼리: 메인 쿼리의 칼럼이 포함되지 않은 서브쿼리
• 서브쿼리는 메인쿼리 칼럼 사용 가능 / 메인쿼리는 서브쿼리 칼럼 사용 불가

스칼라 서브쿼리

• SELECT 절에 주로 위치, 대부분의 위치에 사용 가능
• 반드시 하나의 값을 반환

인라인 뷰

• FROM 절 등 테이블명이 올 수 있는 위치에 사용 가능

중첩 서브쿼리

• WHERE 절과 HAVING 절에 사용 가능
• 비연관 서브쿼리: 메인 쿼리와 관계를 맺고 있지 않음
• 연관 서브쿼리: 메인 쿼리와 관계를 맺고 있음
• 단일 행 서브쿼리
• 다중 행 서브쿼리
• 단일 컬럼 서브 쿼리

ALIAS

• USING 조건절을 이용한 EQUI JOIN이나 NATURAL JOIN에서 JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.
• GROUP BY 절은 SELECT 전에 이루어지기 때문에 ALIAS 사용할 수 없다.
• FROM 절에 ALIAS를 쓸 때에는 AS 키워드를 사용할 수 없다.

가장 헷갈리는 소계(총계)함수

ROLLUP: 소그룹 간의 소계 및 총계 계산

• ROLLUP(A, B)

1. A, B로 그룹핑
2. A로 그룹핑
3. 총합계

• 함수의 인자가 주어진 순서에 따라 결과가 달라진다 O
• 계층 구조로 집계값 반환

CUBE: 조합할 수 있는 모든 그룹에 대한 소계 집계

• CUBE(A, B)

1. A, B로 그룹핑
2. A로 그룹핑
3. B로 그룹핑
4. 총합계

• 함수의 인자가 주어진 순서에 따라 결과가 달라진다 X

GROUPING SETS: 특정 항목에 대한 소계를 계산

• GROUPING SETS(A, B) -> 총합계 X

1. A로 그룹핑
2. B로 그룹핑

• GROUPING SETS(A, B, ())
  = GROUPING SETS(A, ROLLUP(B)) -> 총합계 O

1. A로 그룹핑
2. B로 그룹핑
3. 총합계

PL/SQL

• Block 구조, 각 기능별로 모듈화 가능
• 변수, 상수 등을 선언. SQL 문장 간 값을 교환
• 절차형 언어 사용
• 작성자의 기준으로 랜잭션 분할 가능
• EXECUTE IMMEDIATE 사용하여 실행

프로시저

• CREATE Procedure 문법 사용
• EXECUTE 명령어로 실행
• COMMIT, ROLLBACK 실행 가능

트리거

• CREATE Trigger 문법 사용
• 생성 후 자동으로 실행
• TCL을 이용하여 트랜잭션 제어 불가 (COMMIT, ROLLBACK 실행 안됨)

기타(주관식 관련)

• PERCENT_RANK
• NTILE(n)
• CUME_DIST
• UNBOUNDED PRECEDING
• UNBOUNDED FOLLOWING

제 3장. SQL 최적화 기본 원리

옵티마이저

• 규칙 기반 옵티마이저에서 가장 높은 우선 순위는 ROWID 기반
• 같은 SQL문일 때 실행계획이 다르더라도 결과는 달라지지 않는다.
• 비용 기반 옵티마이저는 항상 인덱스 스캔이 유리하다고 판단하지 않고 전체 스캔이 유리할 때도 있다.

인덱스

• 인덱스는 인덱스 키를 기준으로 정렬되어 있다. -> 탐색이 빨라진다.
• PK는 자동적으로 INDEX가 된다.
• 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고, 하나의 인덱스는 여러 칼럼으로 구성될 수 있다.
• 인덱스는 내림차순으로 정렬된다.
• 인덱스 범위 스캔은 스캔 범위에 따라 단수의 결과 혹은 0건의 결과, 혹은 복수의 결과를 출력한다. 항상 복수의 결과만을 출력하는 것은 아니다.
• 자주 변화하는 속성을 인덱스로 설정하는 것은 좋지 않다.
• 보조 인덱스는 중복 데이터 입력이 가능하다. UNIQUE속성을 가진 인덱스가 아니라면 중복 데이터 입력이 가능하기 때문이다.
• 인덱스를 통한 스캔은 항상 전체 데이터 스캔보다 효율적인 것은 아니다. 랜덤 엑세스의 경우에는 많은 양의 데이터를 읽을 때 부하가 커서 오히려 전체 테이블 스캔이 유리할 수 있다.
• 파티션 테이블은 파티션에 대해 인덱스를 생성할 수 있다.
• 인덱스의 수가 증가할수록 입력, 삭제, 수정 속도가 느려질 수 있다.
• 인덱스 생성은 VARCHAR, NUMBER, DATE, CHAR 모두 가능하다.

옵티마이저 조인

Nested Loop Join

• 선행 테이블을 먼저 조회
• 순차적으로 일어남
• Random Access 발생
• Index 필요
• 온라인 트랜잭션 처리에 유용
• 중첩된 반복문과 동일한 형식
• 선행 테이블의 조건을 만족하는 경우의 수만큼 반복적으로 수행

Sort Merge Join

• 테이블을 각각 정렬
• 임시 디스크를 사용
• EQUI Join, Non EQUI Join 모두 가능

Hash Join

• 작은 테이블을 HASH 메모리에 로딩
• 시스템 자원을 최대한 활용 가능
• 대용량 처리에 빠른 처리 속도
• EQUI Join에서만 사용 가능
• INDEX를 사용하지 않음