2020년 정보처리기사 실기 정리 #2

논리 데이터 모델링

데이터베이스 설계 프로세스의 기초설계 단계로 비즈니스 정보의 구조와 규칙을 명확하게 표현할 수 있는 기법

 

논리 데이터 모델링 특성 (정포완독)

1. 정규화
   모든 데이터를 정규화하여 모델링
2. 포용성
   모든 엔티티 타입, 속성, 관계, 포로세스 등을 포함
3. 완전성
   모든 규칙과 관계를 완전하고 정확하게 표현
4. 독립성
   성능, 제약사항에 독립적인 모델

 

논리 데이터 모델링 속성 (개속관)

1. 개체 : 관리할 대상이 되는 실체
2. 속성 : 관리할 정보의 구체적 항목
3. 관계 : 개체 간의 대응 관계

 

개체–관계(E-R) 모델

현실 세계에 존재하는 데이터와 그들 간의 관계를 사람이 이해할 수 있는 형태로 명확하게 표현하기 위한 모델

 

정규화

관계형 데이터베이스의 설계에서 중복을 최소화하여 데이터를 구조화하는 프로세스

 

이상현상 (삽삭갱)

데이터의 중복성으로 인해 릴레이션을 조작할 때 발생하는 비합리적 현상
삽입이상 : 정보 저장 시 불필요한 세부정보를 입력해야 하는 경우
삭제이상 : 정보 삭제 시 원치 않는 다른 정보가 같이 삭제되는 경우
갱신이상 : 중복 데이터 중 특정 부분만 수정되어 중복된 값이 모슨을 일으키는 경우

 

정규화의 단계 (원부이결다조)

1. 1정규형 : 원자값으로 구성
2. 2정규형 : 부분 함수 종속 제거
3. 3정규형 : 이행 함수 종속 제거
4. 보이스-코드 정규형 : 결정자 함수이면서 후보키가 아닌 것 제거
5. 4정규형 : 다치 종속성 제거
6. 5정규형 : 조인 종속성 제거

 

물리 데이터 모델링

논리모델을 적용하고자 하는 기술에 맞도록 상세화 하는 과정

 

물리 데이터 모델링 변환 절차(개테·속컬·유기·관외·정·반)

1. 개체를 테이블로 변환
2. 속성을 컬럼으로 변환
3. UID를 기본키로 변환
4. 관계를 외래키로 변환
5. 컬럼 유형과 길이 정의
6. 반 정규화 수행
   반 정규화 : 시스템 성능 향상과 개발 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법

 

테이블 제약조건 설계

1. 제한 : 참조무결성 원칙을 위배하는 연산을 거절하는 옵션
2. 연쇄 : 참조되는 릴레이션에서 튜플을 삭제하고, 이 튜플을 참조하는 튜플들도 함께 삭제하는 옵션
3. 널 값 : 참조되는 릴레이션에서 튜플을 삭제하고, 해당 튜플을 참조하는 튜플들의 외래 키에 NULL 값을 넣는 옵션

 

인덱스 설계

인덱스 : 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터 구조

 

인덱스 컬럼 선정

1. 분포도가 좋은 컬럼은 단독적으로 생성
2. 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성
3. 결합 인덱스는 구성되는 컬럼 순서 선정에 유의
4. 가능한 수정이 빈번하지 않은 컬럼을 선정

 

설계 시 고려사항

1. 지나치게 많은 인덱스는 오버헤드로 작용
2. 인덱스는 추가 저장 공간이 필요함을 고려
3. 넓은 범위 인덱스 처리 시 전체 처리보다 많은 오버헤드 발생 시킬 수 있음 유의
4. 인덱스와 테이블의 저장 공간을 적절히 분리될 수 있도록 설계
   파티션 설계 – 파티션의 종류
5. 레인지 파티셔닝 : 연속적인 숫자나 날짜를 기준으로 하는 파티셔닝 기법
6. 해시 파티셔닝 : 파티션 키의 해시 함수 값에 의한 파티셔닝 기법
7. 리스트 파티셔닝 : 특정 파티션에 저장 될 데이터에 대한 명시적 제어가 가능한 파티셔닝 기법
8. 컴포지트 파티셔닝 : 범위분할 이후 해시 함수를 적용해 재분할 하는 파티셔닝 기법

 

파티션의 장점

1. 성능 향상 : 데이터 액세스 범위를 줄여 성능 향상
2. 가용성 향상 : 전체 데이터의 훼손 가능성이 감소 및 데이터 가용성 향상
3. 백업 가능 : 분할 영역을 독립적으로 백업하고 복구 가능
4. 경합 감소 : 디스크 스트라이핑으로 입출력 선능을 향상

 

프로시저

SQL을 이용해 생성된 데이터를 조작하는 프로그램

 

PL/SQL의 개념

표준 SQL을 기본으로 Orcle 사에서 개발한 데이터 조작 언어

 

PL/SQL의 작성 절차

1. 데이터 저장소 연결
   데이터 저장소 연결 절차
   드라이버 로딩 > 데이터베이스 연결 > 쿼리 전달 > 결과 수신
2. 데이터 저장소 정의
3. 데이터 조작 프로시저 작성
4. 데이터 검색 프로시저 작성

 

DBMS_OUTPUT 패키저 활용

메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스를 제공하는 패키지

 

DBMS_OUTPUT 실행방법

PL/SQL의 처리결과를 출력하기 위한 SERVEROUTPUT을 ON시키고 실행하고자 하는 PL/SQL을 호출한다.
PL/SQL 오류 발생 시 ‘SHOW ERRORS’ 명령어를 통해 오류 내용을 확인한다.

 

쿼리 성능 개선의 개념

DB에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

 

쿼리 성능 개선 절차

1. 문제 있는 SQL 식별
   문제있는 SQL을 식별하기 위해 애플리케이션의 성능을 관리 및 모니터링 도구인 APM 활용
2. 옵티마이저 통계 확인
   개발자가 작성한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DB 핵심모듈
3. SQL 재구성
   범위가 아닌 특정 값 지정으로 지정범위를 줄여 처리속도를 빠르게 함
4. 인덱스 재구성
   성능에 중요한 액세스 경로를 고려하여 인덱스 생성
5. 실행계획 유지관리
   DB 버전 업그레이드 등 환경의 변경 사항 발생 시 실행계획이 유지되고 있는지 관리
   옵티마이저
   SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진
   옵티마이저가 생성한 SQL 처리 경로를 실행계획이라고 부른다.

 

옵티마이저의 유형

규칙기반 옵티마이저(RBO) : 통계 정보가 없는 상태에서 등록덴 규칙에 따라 실행을 선택하는 옵티마이저
비용기반 옵티마이저(CBO) : 통계 정보로부터 접근 경로를 고려한 질의실행 계획을 선택하는 옵티마이저

 

SQL 수행 시 옵티마이저 역할

쿼리 변환 : SQL을 일반적으로 표준화된 형태로 변환
비용 산정 : 쿼리 명령어 각 단계의 선택도 비용을 계산
계획 생성 : 하나의 쿼리를 수행 시 후보군이 될 실행계획들을 생헝해내는 역할

 

힌트 사용

SQL 성능 개선의 핵심으로 옵티마이저 실행 계획을 원하는 대로 변경할 수 있게 한다
옵티마이저 항상 최선의 계획을 수립할 수 없으므로 힌트를 통해 실행계획을 변경