1주차
SQLD는 oracle 기반으로 출제
2주차
<데이터 모델링의 목적>
1. 업무에 필요한 정보를 정확하게 '정의'하고 '표현'하여 업무를 분석
2. 분석 모델을 통해 실제 데이터베이스를 생성하여 데이터를 관리
<모델링의 특징>
1. 추상화 2. 단순화 3. 명확화
<데이터 모델링의 3가지 관점>
1. What 데이터 관점 2. How 프로세스 관점 3. Intersection 데이터와 프로세스의 상관 관점
<3층 스키마>
각 계층을 뷰라고 함 (각 뷰마다 독립성 확보)
- 외부 스키마: 사용자 관점 (접근하는 특성에 따른 스키마를 구성)
- 개념 스키마: 설계자 관점 (통합 관점/구조)
- 내부 스키마: 개발자 관점 (물리적 저장 구조)
<ERD를 그리는 각각의 절차>
1. 엔터티를 정의하고 그린다
2. 엔터티를 적절하게 배치한다
3. 엔터티간의 관계를 설정한다
4. 관계명을 서술한다
5. 관계의 참여도를 기술한다
6. 관계의 필수 여부를 기술한다
3주차
<관계의 종류?>
- 존재에 의한 관계: 소속/호맣의 형태
- 행위에 의한 관계: 행동/행위의 결과
<관계차수>
- 1대다: 부서:사원
- 다대다: 주문:제품
<관계선택사양>
하나의 주문목록에는 한 개의 상품목록을 항상 포함하고, 한 상품 목록은 여러 개의 주문 목록에 의해 포함될 수 있다.
<주식별자의 특징>
유일성
최소성
불변성
존재성 (NULL 비허용)
- 주식별자: 다른 엔티티와 참조 관계를 연결할 수 있는 식별자
- 보조 식별자: 태쵸성을 갖지 못해 참조 관계 연결을 하지 못함
** 명칭, 내역 등과 같이 특정한 이름으로 기술되는 것은 가능하면 주식별자로 사용하지 않는다.
- 부서 이름 보단 부서 일련번호 / 부서 코드로 해결
4주차
<성능 데이터 모델링의 고려사항>
1. 정규화를 정확하게 수행
- 데이터를 주요 관심사별로 분산 가능 --> 성능 향상의 효과
- 중복 데이터 방지
2. 데이터베이스 용량 산정 수행
- 어떤 테이블(엔티티)에 데이터가 집중되는지 파악 가능
3.db에서 발생되는 트랜잭션의 유형을 파악
- CRUD 매트릭스 혹은 시퀀스 다이어그램을 보면 파악하기에 용이함
- 데이터 조회에 필요한 조인 관계 등을 파악 가능
4. db의 용량과 트랜잭션 유형에 따라 반정규화 수행
- 테이블, 속성, 관계 등에 대해서 포괄적인 반정규화를 통해 선응을 조정해야 함
5. 이력 모델, PK/FK, 슈퍼/서브 타입의 조정
- 성능이 우수한 순서대로 컬럼의 순서를 조정해야 함
6. 성능 관점에서 데이터 모델 검증
* 정규화: 데이터의 일관성을 유지하고 중복을 방지하며 데이터의 유연성을 유지하기 위해 분해하는 과정
- 정규화 Normalization
- 정규형: NF, Normal Form
- 함수적 종속성: FD, Functional Dependency
- 결정자: Determinant
- 다치 동속: MVD, MultiValued Dependency
<정규화 이점>
- 데이터의 유연성 (높은 응집도와 낮은 결합도)
- 데이터의 재활용성
- 데이터의 중복 최소화
1. 제1정규화: 한 속성에 여러 개의 속성이 포함되어 있거나 같은 유형의 속성이 여러 개로 나눠져 있는 경우 해당 속성을 분리
-> 컬럼에 속성값이 여러 개 나열된 형태일 경우, 이를 분리하면 (ex: 이메일, 핸드폰) 제1정규형을 만족할 수 있음
2. 제 1정규화를 만족시키도 PK가 아닌 모든 컬럼은 PK 전체에 종속
- ex: 고객 정보와 주문내역 테이블은 분리하여, 복합 식별자(이름, 등급)의 일부 컬럼에만 종속되는 문제를 해결하면 제2정규화가 만족됨
3. 제 3정규화: 제 2정규화를 만족시키고 일반 속성 간에도 함수 종속 관계가 존재하지 않아야 함
종속되는 관계의 컬럼은 테이블을 따로 구성해야 제 3정규화 만족
- ex: 직업 코드와 직업 코드 컬럼은 고객정보 테이블에 같이 구성하지 X -> 직업정보 테이블 분리, 고객 정보 테이블에는 직업 코드만 유지
- 직업정보 테이블을 분리해주면서, 직업 코드와 직업 이름 속성 간의 관계가 일반 속성의 종속 관계에서, PK 식별자와 일반 속성의 관계로 변경됨
<정규화 유형>
1. 1NF: 모든 속성은 반드시 하나의 값을 가져야 함 (속성의 원자성 확보)
2. 2NF: 부분종속 속성을 분리, 주식별자에 완전하게 함수 종속되지 않은 속성을 분리하여 종속 관계를 구성 (주문내역과 고객정보 테이블 분리)
3. 3NF: 이전종속 속성을 분리, 일반 속성간의 함수 종속성이 발생하지 않도록 분리 (고객정보과 직업정보 테이블 분리)
4. 보이스 - 코드 정규화 (BCNF): 결정자 안에 함수 종속을 가진 주식별자 속성을 분리
5. 4NF: 다치 종속성(Multi-Valued Dependency)을 제거
6. 5NF: 조인 속성(Join Dependency)을 제거
<반정규화>
- 반정규화는 정규화 (중복 제거)와 동일하게 '성능 향상'이라는 목적을 갖지만 데이터의 중복을 통해 목적 (조회 성능 향상)을 달성
<반정규화를 적용하는 상황>
- 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되는 경우
- 테이블 간 경로가 너무 멀어 조인으로 인한 성능 저하가 예상되는 경우
- 컬럼을 계산하여 읽을 때 성능이 저하될 것이라고 예상하는 경우
<반정규화 절차>
- 뷰 테이블, 클러스터링 적용, 인덱스의 조정, 응용 어플리케이션
- 1:1 관계 테이블 병합
- 1:M 관계 테이블 병합
- 슈퍼 / 서브 타입 테이블 병합
- 수직 분할: 칼럼 단위의 테이블을 디스크 입력/출력을 분산하여 처리하기 위해 테이블을 1:1로 분리하여 처리하는 방법 (ex: 게시글의 조회수를 저장하기 위한 테이블 분리 - 업데이트 많음)
- 수평 분할: 행 단위로 집중 발생하는 디스크 입/출력 및 데이터 접근 효율을 높여 성능을 향상시키기 위한 방법, 하나의 테이블에 있는 값을 기준으로 테이블을 분할 (ex: 요금 납부 테이블을 년/월/일 테이블로 분할)
- 중복 테이블 추가: 다른 업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격 조인을 제거
- 통계 테이블 추가: SUM, AVG, COUNT 등을 미리 수행하고 계산
- 이력 테이블 추가: 이력 테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력 테이블에 적용
- 부분 테이블 추가: 특정 테이블에서 자주 조회하는 칼럼을 파악하여 해당 칼럼을 모아 놓은 별도의 반정규화된 테이블 생성
- 조인 연산으로 인한 성능 저하를 방지하지 위해 중복 컬럼을 추가하여 조인 연산을 수행하지 않도록 함
- ex: 인사 테이블과 부서 테이블을 조인하여 부서 테이블에 있는 부서명 칼럼을 조회할 때, 부서명 칼럼을 인사 테이블에도 중복으로 넣어 부서 테이블에 조인하지 않고 조회함
- 트랜잭션이 처리되는 시점에 계산하는 값을 미리 계산한 컬럼을 따로 구성
- 매출 금액 컬럼이 있으면 총 매출 금액을 미리 계산한 컬럼을 추가해 놓음
- 많은 데이터를 처리할 때 불특정한 날에 대한 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력 테이블에 컬럼 추가
- ex: 최근에 조회한 값이 있는지를 확인하거나 시작 & 종료 일자 등에 대한 컬럼을 따로 지정
- 복합의미를 갖는 PK(복합키) 를 단일 속성으로 구성한 경우 단일 PK 안에서 특정 값을 별도로 조회하는 경우에 성능 저하가 나타날 수 있음
- 이때 이미 PK 안에 데이터가 존재하지만 성능 향상을 위해 일반 속성으로 생성하는 방법
- ex: 주문번호의 값 구성이 '상품코드 + 주문일자'로 구성된 경우 주문번호 안에 존재하는 주문일자를 일반 속성으로 빼내고 주문일자 컬럼을 인덱스로 설정
- 비즈니스적으로 의미는 없지만 사용자의 데이터를 처리하다가 잘못된 경우에 원래 값으로 복구를 원할 때 이전 데이터를 임시적으로 중복하여 보관하는 방법
- 데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능
- 이때 발생할 수 있는 성능 저하를 예방하기 위해 추가적인 관계를 맺는 방법이 관계의 반정규화
5주차
<대량 데이터 발생에 따른 테이블 분할>
- 수평 분할: 행 단위로 요소를 분할하여 디스크의 입/출력 비용을 감소시키는 방법
1년 치 데이터가 대용량인 경우 월별로 분할하여 저장하면 특정 월을 조회하는 경우 전체 데이터를 조회하지 않아도 되기 때문
- 수직 분할: 칼럼 단위로 요소를 분할하여 디스크의 입/출력 비용을 감소시키는 방법
ex: 고객의 생년월일, 주소 등의 개인정보와 취미/특기 등의 기타 정보를 별도로 저장
<성능 저하 현상>
- 로우 체이닝 (Row Chaining)
- 행 데이터가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 2개 이상의 블록에 걸쳐서 하나의 행이 저장된 형태
- 하나의 행을 읽을 때 2개 이상의 데이터 블록을 읽게 되는데, 절대적으로 읽어야 하는 데이터가 증가하기 때문에 성능 저하에 직접적인 영향을 주게 됩니다.
- 로우 마이그레이션 (Row Migration)
- 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고, 다른 블록의 빈 공간을 찾아서 저장하는 방식으로 처리됨
- 해당 현상이 일어나면서 하나의 행을 읽을 때 2개 이상의 데이터 블록을 읽게 됨. 로우 체이닝 현상과 마찬가지로 절대적으로 읽어야 하는 데이터 블록의 수가 늘어나기 때문에 성능이 저하됨
<테이블에 대한 수평/수직 분할 절차>
1. 데이터베이스 모델링을 진행
2. 데이터베이스 테이블의 용량 산정
3. 데이터 처리 과정에서 트랜잭션 처리 패턴 분석
4. 데이터 처리의 과정이 칼럼과 로우 중 어디에 집중되는지 분석하고 집중된 부분의 테이블을 파티셔닝
<슈퍼타입 / 서브타입 모델 변환 방법>
- 슈퍼타입은 공통적인 속성, 서브타입은 자신만의 속성
- 슈퍼 타입(Single / All in One)
- 슈퍼/서브 타입 모델을 하나의 테이블로 변환
- 고객 테이블을 싱글 테이블로 구성
- 서브 타입(Plus / Super + Sub)
- 슈퍼타입과 서브타입 테이블로 변환
- 도출된 각 서브 타입에는 변환 전에 슈퍼 엔터티에 있던 칼럼을 공통적으로 가지고 있음
- 개별 타입 (OneToOne + 1:1)
- 슈퍼/서브 타입을 슈퍼 타입과 서브 타입의 각 개별 테이블로 변환
- 슈퍼/서브 테이블 모두를 생성
<슈퍼타입 / 서브타입 데이터 모델 변환 타입 비교>
- 슈퍼타입
- 특징: 하나의 테이블
- 확장성: 나쁨
- 조인 성능: 우수
- I/O 성능: 나쁨
- 관리 용이성: 좋음
- 서브타입
- 특징: 각각의 서브타입 테이블
- 확장성: 보통
- 조인 성능: 나쁨
- I/O 성능: 좋음
- 관리 용이성: 좋지 않음
- 개별타입
- 특징: 슈퍼 서브 각각의 테이블
- 확장성: 좋음
- 조인 성능: 나쁨
- I/O 성능: 좋음
- 관리 용이성: 좋지 않음
<슈퍼타입 / 서브타입 모델 변환의 중요성>
- 트랜잭션은 항상 일괄적으로 처리: 테이블은 개별적으로 유지되기 때문에 UNION 연산에 의해 성능 저하의 가능성 존재
- 트랜잭션은 항상 서브타입을 개별로 처리: 테이블이 하나로 통합되어 있어 불필요하게 많은 데이터를 처리하는 과정에서 성능 저하의 가능성 존재
- 트랜잭션은 항상 슈퍼타입과 서브타입을 공통으로 처리: 만약 개별로 유지되거나 하나의 테이블로 집약되어 있어 성능 저하를 보일 수 있음
<PK 순서와 성능 사이의 관계>
- PK 칼럼이 테이블의 어느 곳에 위치해 있는지에 따라서 SQL 쿼리문의 성능이 달라질 수 있음
ex: 거래일자를 먼저 보고 해당 일자에 속하는 모든 범위를 스캔하지 않고 사무실 코드가 BC2011인 데이터에서 범위를 먼저 스캔
<FK와 테이블 인덱스 구성 사이의 관계>
- 물리적인 외래키(FK)의 생성 여부와 상관없이 논리/물리 FK 제약 조건은 외래키 칼럼에 대해 인덱스를 생성하는 것이 성능 상의 우위를 가질 확률이 높음
ex: 수강 정보 테이블의 학사기준번호 칼럼으로 하는 인덱스를 미리 만들어 놓으면 학사 정보 테이블에서 조인을 하는 과정에서 성능 저하 예방 가능
<분산 데이터베이스의 투명성>
1. 분할 투명성: 하나의 논리적 관계(Relation)가 여러 단편으로 분할되어 각 사본이 여러 사이트에 저장
2. 위치 투명성: 위치 정보는 System Catalog에 유지되어야 함
3. 지역 사상 투명성: 지역 DBMS와 물리적 DB 사이의 연결됨(mapping)을 보장
4. 중복 투명성: DB 객체가 여러 사이트에 중복되어 있는지 알 필요가 없음
5. 장애 투명성: 구성요소에서 발생하는 장애에 무관한 트랜잭션의 원자성(Atomicity)을 유지
6. 병행 투명성: 다수 트랜잭션이 동시에 수행 시 결과의 일관성을 유지
<분산 데이터베이스의 활용 방향성>
- 위치 중심의 분산 설계
- 업무 필요에 의한 분산 설계
<분산 데이터베이스의 적용 기법>
1. 테이블 위치(Location) 분산 (ex: 본사 / 지사)
2. 테이블 분할(Fragmentation) 분산 (ex: 수평 / 수직)
3. 테이블 복제(Replication) 분산 (ex: 부분 / 광역)
4. 테이블 요약(Summarization) 분산
- 분석요약(Rollup Summarization): 분산되어 있는 동일한 내용의 데이터를 이용한 통합된 데이터를 산출하는 방식
- 통합요약(Consolidation Summarization): 분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식 (중앙으로 모아서 다시 산출)
