SQLD 발췌독 족보

블로그 1차 정리본이며 첨부파일에 전체 정리가 되어있습니다.

SQLD 개념 정리(노랭이 오답노트).hwp

0.16MB

SQLD 개념 정리(노랭이 오답노트).pdf

0.31MB

 

 

 

데이터 모델링

문제5) 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링

개념적 데이터 모델링	전사적 데이터 모델링을 수행할 때 많이 하며, 추상화 수준이 높고 업무중심적이며 포괄적인 수준의 모델링을 진행한다.
논리적 데이터 모델링	시스템으로 구축하고자 하는 업무에 대해 Key,  속성, 관계 등을 정확하게 표현하며 재사용성이 높다.
물리적 데이터 모델링	실제 데이터베이스에 이식할 수 있도록 성능, 저장 등의 물리적인 성격을 고려한 데이터 모델링.

 

문제6) 외부스키마, 개념스키마, 내부스키마 및 독립성

외부스키마 (사용자 관점)	각 사용자가 보는 데이터베이스의 스키마를 정의
개념스키마  (통합된 관점)	모든 사용자가 보는 데이터베이스의 스키마를 통합하여 전체 데이터베이스를 나타내는 것. 데이터 베이스에 저장되는 데이터들을 표현하고 데이터들 간의 관계를 나타낸다
내부스키마 (물리적 관점)	물리적인 저장구조를 나타낸다. 실직적인 데이터의 저장 구조나 컬럼 정의, 인덱스 등이 포함된다.

 

논리적 독립성	개념스키마가 변경되어도 외부 스키마는 영향받지 않는다.
물리적 독립성	내부 스키마가 변경되어도 외부/개념 스키마는 영향을 받지 않는다.

 

문제12) 엔티티의 분류

 

유/무형에 따른 엔티티 분류
유형 엔터티	물리적인 형태가 있고 안정적이며 지속적으로 활용되는 엔터티 업무로부터 엔터티를 구분하기가 가장 용이함 ex) 사원, 물품, 강사
개념 엔터티	물리적인 형태 존재X 관리해야 할 개념적 정보로 구분이 되는 엔터티 ex) 조직, 보험상품
사건 엔터티	업무를 수행함에 따라 발생되는 엔터티 비교적 발생량이 많으며 각종 통계자료에 이용 ex) 주문, 청구, 미납

 

발생시점에  따른 엔티티 분류
기본 엔터티	업무에 원래 존재하는 정보 다른 엔터티와 관계에 의해 생성되지 않고 독립적으로 생성이 가능 (자신은 타 엔터티의 부모 역할) 타 엔터티로부터 주식별자를 상속받지 않고 자신의 고유한 주식별자를 가짐 ex) 사원, 부서, 고객, 상품, 자재
중심 엔터티	기본 엔터티로부터 발생 업무의 중심 역할 데이터의 양이 많이 발생되고 다른 엔터티와의 관계를 통해 많은 행위 엔터티 생성 ex) 계약, 사고, 예금원장, 청구, 주문, 매출
행위 엔터티	두 개 이상의 부모 엔터티로부터 발생 자주 내용이 바뀌거나 데이터량이 증가함 상세 설계단계나 프로세스와 상관모델링을 진행하면서 도출 ex) 주문목록, 사원변경이력

 

문제 21) 존재적 관계, 행위적 관계

존재적 관계	소속한다, 소속된다 등
행위적 관계	주문한다, 출석한다 등

 

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

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

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

· 업무기술서, 장표에 관계연결을 가능하게 하는 동사(Verb) 가 있는가?
· 업무기술서, 장표에 관계연결에 대한 규칙이 서술 되어 있는가?

 

문제26) 식별자

분류	식별자	내용
대표성 여부	주식별자	엔터티 내에서 각 어커런스를 구분할 수 있는 구분자이며, 타 엔터티와 참조 관계를 연결할 수 있는 식별자	· 유일성, 최소성, 불변성, 존재성을 가진 대표식별자 · 다른 엔터티와 참조관계로 연결
	보조식별자	엔터티 내에서 각 어커런스를 구분할 수 있는 구분자이나 대표성을 가지지 못해 참조관계 연결을 못함	· 인스턴스를 식별할 수는 있지만 대표 식별자가 아님 · 다른 엔터티와 참조 관계로 연결되지 않음
스스로 생성 여부	내부식별자	엔터티 내부에서 스스로 만들어지는 식별자
	외부식별자	타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자	다른 엔터티와 연결고리 역할 (FK)
속성의 수	단일식별자	하나의 속성으로 구성된 식별자
	복합식별자	둘 이상의속성으로 구성된 식별자
대체 여부	본질(원조)식별자	업무에 의해 만들어 지는 식별자	가공되지 않은 원래의 식별자
	인조(대리)식별자	업무적으로 만들어지지는 않지만 원조 식별자가 복잡한 구성을 가지고 있기 때문에 인위적으로 만든 식별자	주식별자의 속성이 두 개 이상인 경우 그 속성들을 하나로 묶어서 사용하는 식별자

 

 

문제 32, 41, 42) 반정규화 기법 및 슈퍼/서브타입 예

 

<테이블의 반정규화>

테이블 병합, 분할, 추가 기법이 있다.

병합 : 1:1, 1:M, 서브/슈퍼 타입 → 3가지 유형의 테이블 병합

분할 : 수직, 수평 → 2가지 유형의 테이블 분할

추가 : 중복테이블, 통계테이블, 이력테이블, 부분테이블 → 3가지 유형의 테이블 추가

기법 분류	반정규화 기법	내용
중복컬럼 추가	1:1 관계 테이블 병합	1:1 관계를 통합하여 성능향상
	1:M 관계 테이블 병합	1:M 관계를 통합하여 성능향상
	슈퍼/서브타입 테이블 병합	슈퍼/서브 관계를 통합하여 성능 향상
테이블 분할	수직분할	컬럼단위의 테이블에서 디스크 I/O를 분산처리 하기 위해 테이블을 1:1로 분리하여 성능 향상 (트랜잭션의 처리되는 유형을 파악하는 것이 선행되어야 한다.)
	수평분할	로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터접근의 효율성을 높여 성능을 향상하기 위해 로우단위로 테이블을 쪼갠다. (관계가 없음)
테이블 추가	중복 테이블 추가	다른 업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격 조인을 제거하여 성능을 향상
	통계 테이블 추가	SUM, AVG 등을 미리 수행하여 계산해 둠으로써 조회시 성능을 향상
	이력 테이블 추가	이력 테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력 테이블에 존재하는 방법
	부분 테이블 추가	하나의 테이블의 전체 컬럼 중 자주 이용하는 집중화된 컬럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 컬럼들을 모아놓은 별도의 반정규화된 테이블을 생성

 

<컬럼의 반정규화>

중복컬럼, 파생컬럼, 이력테이블컬럼, PK에 의한 컬럼, 응용시스템 오작동을 위한 컬럼 → 5가지 유형의 컬럼 추가

반정규화 기법	내용
중복컬럼 추가	조인에 의해 처리할 때 성능 저하를 예방하기 위해 즉, 조인을 감소시키기 위해 중복된 컬럼을 위치시킴 조인 감소를 위해 여러 테이블에 동일한 컬럼을 갖도록 한다.
파생 컬럼 추가	트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 컬럼에 보관한다. (Derived Column 이라고 함) 조회 성능을 우수하게 하기 위해 미리 계산된 컬럼을 갖도록 한다.
이력테이블 컬럼 추가	대량의 이력데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력 테이블에 기능성 컬럼(최근값 여부, 시작과 종료일자 등) 을 추가함 최신값을 처리하는 이력의 특성을 고려하여 기능성 컬럼을 추가한다.
PK에 의한 컬럼 추가	복합의미를 갖는 PK를 단일 속성으로 구성하였을 경우 발생. 단일 PK안에서 특정 값을 별도로 조회하는 경우 성능 저하 발생 가능성 존재 이미 PK안에 데이터가 존재하지만 성능 향상을 위해 일반 속성으로 포함하는 방법
응용시스템 오작동을 위한 컬럼 추가	업무적으로는 의미가 없지만 사용자가 데이터처리를 하다가 잘못 처리하여 원래 값으로 복구하기를 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법. 컬럼으로 이것을 보관하는 방법은 오작동 처리를 위한 임시적인 기법이지만 이것을 이력데이터 모델로 풀어내면 정상적인 데이터 모델의 기법이 될 수 있음.

 

<슈퍼/서브타입 테이블 병합> 예시

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

구분	One To One Type	Plus Type	Single Type
특징	개별 테이블 유지	슈퍼 + 서브타입 테이블	하나의 테이블
확장성	우수	보통	나쁨
조인성능	나쁨	나쁨	우수함
I/O량 성능	좋음	좋음	나쁨
관리 용이성	나쁨	나쁨	좋음(1개)
트랜잭션 유형에 따른 선택 방법	개별 테이블로 접근이 많은 경우	슈퍼+서브 형식으로 데이터를 처리하는 경우	전체를 일괄적으로 처리하는 경우

 

 

문제 33) 성능을 고려한 데이터 모델링의 순서

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

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

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

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

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

 

문제 40) 빌링의 잔액 반정규화 대상 이유

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

 

재현의 적시성 : 

빌링 : 상품이나 서비스의 제공으로부터 대금청구까지 일련의 과정을 뜻함

적시성 : 데이터의 최선성 유지와 같은 품질요건에 얼마나 잘 대처하고 있는지를 의미

 

잔액 데이터는 다수 테이블에 대한 다량의 조인이 불가피하다.

 

 

 

문제 48) 논리모델의 슈퍼타입과 서브타입 데이터 모델을 물리적인 테이블 형식으로 변환할 때 성능저하 이유

1. 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 Union연산에 의해 성능이 저하될 수 있다.
2. 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우가 있다.
3. 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우가 있다.

트랜잭션 항상 일괄처리 → 테이블 개별로 유지 → UNION연산에 의해 성능저하

트랜잭션 항상 서브타입 개별로처리 → 테이블 하나로 통합 → 불필요하게 많은양의 데이터 집약으로 성능 저하
트랜잭션 항상 슈퍼/서브타입 공통처리 → 개별 or 하나 → 성능 저하

 

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

---

SQL 기본

 

문제 19) 참조무결성

표준  SQL에서 테이블 생성시 참조관계를 정의하기 위해 외래키를 선언한다. 관계형 데이터베이스에서 Child Table의 FK 데이터 생성시 Parent Table에 PK가 없는 경우, Child Table 데이터 입력을 허용하지 않는 참조동작인 것은?

 

DELETE( / UPDATE)
CASCADE	Master 삭제시 Child 같이 삭제
SET NULL	Master 삭제시 Child 해당 필드 NULL
SET DEFAULT	Master 삭제시 Child 해당 필드 Default값으로 설정
RESTRICT	Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
NO ACTION	참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

INSERT (일반적으로 외래키 제약조건은 삭제 시에 동작을 지정하는 것이 주로 사용함.  따라서 INSERT 작업을 제한하려면 애플리케이션 수준에서 해당 로직을 구현하여 제한 조건을 처리하는 것이 일반적)
AUTOMATIC	Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child입력
SET NULL	Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리
SET DEFAULT	Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력
DEPENDENT	Mater 테이블에 PK가 존재할 때만 Child 입력을 허용
NO ACTION	참조 무결성을 위반하는 입력 액션을 취하지 않음.

 

문제 28) 데이터베이스 트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점

Dirty Read, Non-repeatable Read, Phantom Read, Lost Update

Dirty Read	하나의 트랜잭션이 아직 커밋되지 않은 다른 트랜잭션의 변경사항을 읽는 경우, 잘못된 데이터를 읽을 수 있다. 이로 인해 커밋 되지 않은 변경 사항이 롤백되면 읽은 데이터는 실제로 존재하지 않는 데이터가 된다.
Non-repeatable Read	동일한 트랜잭션 내에서 동일한 쿼리를 반복해서 실행할 때, 다른 트랜잭션이 해당 데이터를 변경하거나 삭제하는 경우, 처음과 다른 결과를 얻게 된다. 이로 인해 데이터의 일관성이 깨질 수 있다.
Phantom Read	동일한 쿼리를 반복해서 실행할 때, 다른 트랜잭션이 해당 데이터를 삽입 또는 삭제하는 경우, 처음과 다른 결과를 얻게 된다. 이로 인해 조회 결과에 예상치 못한 데이터가 추가되거나 제거될 수 있다.
Lost Update	두 개 이상의 트랜잭션이 동일한 데이터를 동시에 수정하는 경우, 마지막으로 커밋된 트랜잭션의 변경사항이 덮어 씌워져 이전 변경사항이손실될 수 있다.

 

문제 63) Non Equi Join

[마] 대부분 Non Equi Join을 수행할 수있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.

어떤경우?

 

문제 103) 그룹핑 함수 집계된 컬럼 이외의 대상 컬럼값

CUBE, ROLL UP, GROUPING SETS 함수들의 대상 컬럼 중 집계된 컬럼 이외의 대상 컬럼 값은 모두 NULL 값을 반환한다.