SQLD, 마지막 정리 - 자주 출제되는 내용 정리

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

1) 정규화를 수행하여 데이터베이스 모델의 유연성을 확보한다.
2) 데이터베이스의 전체 용량, 월간, 연간 증감율을 예측한다.
3) 애플리케이션의 트랜잭션의 유형(CRUD: Create Read Update Delete)을 파악한다.
4) 합계 및 정산 등을 수행하는 반정규화를 수행한다.(성능향상을 위한 튜닝)
5) 기본키와 외래키, 수퍼타입과 서브타입 등을 조정한다.
6) 성능관점에서 데이터 모델을 검증하고 확인한다.
 
[ 3가지 모델링 알아두기 ]

• 개념적 모델링: 개체와 개체들 간의 관계에서 ER다이어그램을 만드는 과정
• 논리적 모델링: ER다이어그램을 사용하여 관계 스키마 모델을 만드는 과정
• 물리적 모델링: 관계 스키마 모델의 물리적 구조를 정의하고 구현하는 과정

---

파생속성 → 어떤 데이터를 기반으로 만들어진 데이터, 예) 주식의 가격의 평균데이터 등 

---

엔티티 종류

종류	설명
독립 엔티티 [Kernel Entity, Master Entity]	사람, 물건, 장소 등과 같이 현실세계에 존재하는 엔터티
업무중심 엔터티 [Transaction Entity]	Transaction이 실행되면서 발생하는 엔터티
종속 엔터티 [Dependent Entity]	주로 1차 정규화로 인해 관련 중심엔티티로부터 분리된 엔터티
교차 엔티티 [Intersaction Entity]	M:M의 관계를 해소하려는 목적으로 만들어진 엔터티 [ex> M:M -> 1:M]

 
유형과 무형에 따른 엔터티 종류
     * 유형과 무형으로 구분하는 기준은 물리적 형태의 존재 여부

종    류	설    명
유형 엔터티	업무에서 도출되며 지속적으로 사용되는 Entity
개념 엔터티	개념적으로 사용되는 Entity 유형 엔터티는 물리적 형태가 있지만, 개념 엔터니는 물리적 형태가 없다
사건 엔터티	비즈니스 프로세스를 실행하면서 생성되는 Entity

 

발생시점에 따른 엔터티 종류

종    류	설    명
기본 엔터티 [Basic Entity]	다른 엔터티로부터 영향을 받지 않고 독립적으로 생성되는 엔터티 키 엔터티[Key Entity]라고도 함
중심 엔터티 [Main Entity]	기본 엔터티와 행위 엔터티 간의 중간에 있는 엔터티 기본 엔터티로부터 발생되고 행위 엔터티를 생성하는 엔터티
행위 엔터티 [Active Entity]	2개 이상의 엔터티로부터 발생하는 엔터티 지속적으로 정보가 추가되고 변경되는 엔터티

---

ERD 표기법 중 IE 표기법은 관계의 1:N 관계에서 N쪽에 새발을 표시하고 선택,
필수 참여관계에서 선택 참여(or)에 O, 필수 참여에 | 로 표시한다.

---

식별자의 특징

1. 유일성 : 주식별자에 의해 엔터티내에 모든 인스턴스들이 유일하게 구분되어야 함
2. 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함
3. 불변성 : 지정된 주식별자 값은 자주 변하지 않는 것이어야 함
4. 존재성 : 주식별자가 지정이 되면 반드시 값이 들어와야 함(NOT NULL)

 

식별자 분류	식별자	설명
대표성여부	주식별자	- 엔터티 내에서 각 행을 구분할 수 있는 구분자이며, 타 엔터티와 참조관계를 연결할 수 있는 식별자 (ex. 사원번호, 고객번호)
	보조식별자	- 엔터티 내에서 각 행을 구분할 수 있는 구분자이나 대표성을 가지지 못해 참조관계 연결을 못함(ex. 주민등록번호)
스스로 생성여부	내부식별자	- 엔터티 내부에서 스스로 만들어지는 식별자(ex. 고객번호)
	외부식별자	- 타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자(ex. 주문엔터티의 고객번호)
속성의 수	단일식별자	- 하나의 속성으로 구성된 식별자(ex. 고객엔터티의 고객번호 )
	복합식별자	- 둘 이상의 속성으로 구성된 식별자(ex. 주문상세엔터티의 주문번호+상세순번)
대체여부	본질식별자	- 업무(비즈니스)에 의해 만들어지는 식별자(ex. 고객번호)
	인조식별자	- 업무적으로 만들어지지는 않지만 원조식별자가 복잡한 구성을 가지고 있기 때문에 인위적으로 만든 식별자(ex. 주문엔터티의 주문번호(고객번호+주문번호+순번))

---

구분	설명
START WITH	계층구조 전개의 시작 위치를 지정하는 구문, 루트 데이터를 지칭
CONNECT BY	다음에 전개될 자식 데이터를 지정, 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야함(JOIN)
PRIOR	CONNECT BY 절에 사용되며 현재 읽은 칼럼을 지정
PRIOR 자식	부모형태를 사용하면 계층구조에서 부모 -> 자식 방향으로 순방향 전개
PRIOR 부모	자식 형태를 사용하면 자식 -> 부모 방향으로 역방향 전개
NOCYCLE	데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개중에 다시 나타나는 경우 CYCLE이 생성. CYCLE이 발생한 데이터는 런타임 오류를 방생시켜 NOCYCLE 구문을 통해 CYCLE이 발생하는 경우 이후 데이터 전개를 방지
ORDER SIBLINGS BY	형제노드(동일 LEVEL) 사이 데이터 정렬
WHERE	모든 전개를 수행한 뒤 지정조건을 통해 데이터 필터링

구분	설명
CONNECT_BY_ROOT	계층형 쿼리에서 최상위 로우를 반환하는 연산자다. 연산자이므로 CONNECT_BY_ROOT 다음에는 표현식이 온다.
CONNECT_BY_ISLEAF	CONNECT BY 조건에 정의된 관계에 따라 해당 로우가 최하위 자식 로우이면 1을, 그렇지 않으면 0을 반환하는 의사 컬럼이다.

---

 

인덱스에 대해서 연산을 하면 인덱스가 변형이 되므로 인덱스를 사용할수가 없다.

---

실행계획 (Execution Plan)

실행계획이란 SQL을 실행하기 위한 절차와 방법을 의미한다.
- SQL개발자가 SQL을 작성하여 실행할 때, SQL을 어떻게 실행할 것인지를 계획하게 된다. 즉, SQL실행계획을 수립후 SQL을 실행한다.
- 옵티마이저는 SQL의 실행계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.

우선순위	연산자
1	산술 연산자(*, /, +, -)
2	연결 연산자 (||)
3	비교 연산자(<, >, <=, =>, <>, =)
4	IS NULL, LIKE, IN
5	BETWEEN
6	NOT 연산자
7	AND 연산자
8	OR 연산자

---

 

ROW_NUMBER() : 중복값이 있어도 고유 등수 부여(1위, 2위, 3위, 4위)
RANK() : 중복값은 중복등수, 등수 건너뜀(1위, 1위, 3위, 4위)
DENSE_RANK() : 중복값은 중복등수, 등수 안 건너뜀(1위, 1위, 2위, 2위) *동일 등수 순위에 영향이 없다.

---

 

START WITH COL1 = 2
## COL1 = 2부터 실행
CONNECT BY PRIOR COL1 = COL2;
## COL1 = COL2 인 행을 찾는다.
## 그 행의 다시 COL1과 같은 값을 가진 COL2 행을 찾는다.

여기서 프 부 자 순 (PRIOR 부모 = 자식 은 순방향)
            프 자 부 역 (PRIOR 자식 = 부모 는 역방향)

---

GRANT : 권한부여 ( GRANT [권한] ON [테이블] TO [유저] )
REVOKE : 권한회수 (REVOKE [권한] ON [테이블] FROM [유저] )

---

기능사용 목적특징

 

ROLLUP	계층적 그룹화	부분 합계 + 전체 총합 생성	부분 합계 + 전체 총합
CUBE	모든 조합의 집계 필요할 때	가능한 모든 그룹별 집계 생성	부분 합계 + 카테고리 전체 총합 + 전체 총합
GROUPING SETS	원하는 그룹 조합만 선택할 때	특정 조합만 지정 가능 (성능 최적화)	카테고리 총합 + 전체 총합

 

---

NTILE(3)는 데이터를 3개 그룹으로 나눔

---

ORDER SIBLINGS BY

동일한 부모(형제 노드) 내에서 정렬

ORDER SIBLINGS BY C1 DESC: 같은 부모를 가진 행들(C1 기준)을 내림차순(DESC) 정렬

---

BETWEEN 0 PRECEDING AND 50 FOLLOWING

 

 

- 현재 행을 기준(0 PRECEDING)

- 앞으로 50 증가한 값까지의 범위 내에서 COUNT 수행

 

ORDER BY N1 RANGE BETWEEN 0 PRECEDING AND 50 FOLLOWING

ROWS	물리적인 N개의 행 기준으로 윈도우 크기 지정	같은 값이어도 한 행씩 계산됨
RANGE	현재 값과 범위 내(예: ±50) 기준	같은 값(N1)이 있으면 그룹 처리

---

도메인

데이터베이스에서 속성(컬럼)에 허용될 수 있는 값들의 집합

CREATE TABLE User (
    user_id INT, -- 숫자 도메인
    username VARCHAR(50), -- 문자열 도메인
    birthdate DATE -- 날짜 도메인
);

---

주식별자 특징

유일성 - 모든 인스턴스를 유일하게 구분가능 해야한다.
최소성 - 구성하는 속성 수는 1개이어야 한다.
불변성 - 자주 변경되지 않아야 한다.
존재성 - 항상 데이터 값이 있어야 한다.
대표성 - 엔터티를 대표 할 수 있어야 한다.

---

 

AVG(NVL(COL1, 0)): COL1에서 NULL 값을 0으로 바꾼 후 평균을 구합니다.
EMP.DEPTNO(+) = DEPT.DEPTNO 부분에서 (+)는 Oracle의 OUTER JOIN

---

- 외부 스키마(External Schema) - 서브 스키마, 사용자 뷰
1. 외부 스키마는 사용자나 응용 프로그래머가 개인의 입장에서 필요한 데이터베이스의 논리적 구조를 정의한다.
2. 외부 스키마는 전체 데이터베이스의 한 논리적인 부분으로 볼 수 있기 때문에 서브 스키마라고도 한다. 3
. 하나의 데이터베이스 시스템에는 여러 개의 외부 스키마가 존재할 수 있다.
4. 하나의 외부 스키마를 여러개의 응용 프로그램 혹은 사용자가 공유할 수 있다.
5. 일반 사용자는 SQL과 같은 질의어를 이용하여 DB를 쉽게 사용할 수 있다.
6. 응용 프로그래머는 C나 JAVA 등의 언어를 사용하여 DB에 접근한다.
 
- 개념 스키마(Conceptual Schema) - 전체적인 뷰
1. 개념 스키마는 데이터베이스의 전체적인 논리적 구조로, 모든 응용 프로그램이나 사용자들이 필요로 하는 데이터를 종합한 조직 전체의 데이터베이스로 하나만 존재한다.
2. 개념 스키마는 개체 간의 관계(Relationship)와 제약 조건을 나타내고 데이터베이스의 접근 권한, 보안 및 무결성 규칙에 관한 명세를 정의한다.
3. 데이터베이스 파일에 저장되는 데이터의 형태를 나타내는 것으로, 단순히 스키마라고 하면 개념 스키마를 의미한다.
4. 기관이나 조직체의 관점에서 데이터베이스를 정의한 것이다.
5. DBA에 의해서 구성된다.
 
- 내부 스키마(Internal Schema) - 시스템 설계자 뷰
1. 내부 스키마는 물리적인 저장장치 입장에서 데이터가 저장되는 방법을 기술한 것이다.
2. 내부 스키마는 실제 데이터베이스에 저장될 레코드의 물리적인 구조를 정의한다.
3. 내부 스키마는 저장 데이터 항목의 표현방법, 내부 레코드의 물리적 순서, 인덱스 유/무 등을 나타낸다.
4. 시스템 프로그래머나 시스템 설계자가 관리한다.

---

USER 에 속한 모든 OBJECT를 같이 삭제하는 명령어

DROP USER user_name CASCADE;

---

 

PARTITION BY COL4: COL4의 값에 따라 각 그룹이 나누어지고, 각 그룹 내에서 COL3 값을 기준으로 순위가 매겨집니다.

---

관계차수는 속성의 개수입니다.

튜플수는 행의 개수입니다.

뷰는 다른 테이블에서 파생된 테이블로, 물리적 데이터가 저장되는 것이 아닌 논리적으로 존재하는것

---

NULL 함수	설명
NVL 함수	- NULL이면 다른 값으로 바꾸는 함수이다. - "NVL(MGR, 0)"은 MGR칼럼이 NULL이면 0으로 바꾼다.
NVL2 함수	- NVL함수와 DECODE를 하나로 만든 것이다. - "NVL2(MGR, 1, 0)"은 MGR칼럼이 NULL이 아니면 1, NULL이면 0을 반환한다.
NULLIF 함수	- 두 개의 값이 같으면 NULL을 같지 않으면 첫 번째 값을 반환한다. - "NULLIF(exp1, exp2)"은 exp1과 exp2가 같으면 NULL을, 같지 않으면 exp1을 반환한다.
COALESCE	- "COALESCE(mgr, 1)"은 mgr이 NULL이면 1을 반환한다. NULL이 아닌 첫 번째 칼럼의 값을 반환한다.

---

[ PL/SQL의 특징 ]
1. PL/SQL은 Block구조로 되어있어 각 기능별로 모듈화가 가능하다.
2. 변수, 상수 등을 선언하여 SQL문장 간 값을 교환 한다.
3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
5. PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
6. PL/SQL은 응용 프로그램의 성능을 향상시킨다.
7. PL/SQL은 여러 SQL문장을 Block으로 묶고 한번에 Block전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
 

---

IN/NOT IN은  NULL값이 있으면 사용할 수 없다.

---

방 법	설 명
중첩 반복 조인 (Nested Loop Join)	- 좁은 범위에 유리 - 유리순차적으로 처리하며, Random Access 위주 - 후행(Driven) 테이블에는 조인을 위한 인덱스가 생성되어 있어야 함 - 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근횟수
색인된 중첩 반복 조인, 단일 반복 조인 (Single Loop Join)	- 후행(Driven) 테이블의 조인 속성에 인덱스가 존재할 경우 사용 - 선행 테이블의 각 레코드들에 대하여 후행 테이블의 인덱스 접근 구조를 사용하여 직접 검색 후 조인하는 방식
정렬 합병 조인 (Sort Merge Join)	- Sort Merge 조인은 해당 테이블의 인덱스가 없을때 수행이 된다. - 테이블을 정렬(Sort) 한 후에 정렬된 테이블을 병합(Merge) 하면서 조인을 실행한다. - 조인 연결고리의 비교 연산자가 범위 연산( >, < )인 경우 Nested Loop 조인보다 유리 - 두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적
해시 조인 (Hash Join)	- 해시(Hash)함수를 사용하여 두 테이블의 자료를 결합하는 조인 방식 - Nested Loop 조인과 Sort Merge 조인의 문제점을 해결 - 대용량 데이터 처리는 상당히 큰 hash area를 필요로 함으로, 메모리의 지나친 사용으로 오버헤드 발생 가능성