2020 정보처리기사 실기 2. 데이터 입출력 구현

1 논리 데이터 저장소 확인

1 논리 데이터 모델

1 개념

비즈니스 정보의 구조와 규칙을 명확하게 표현할 수 있는 기법

2 특성

• 정규화
• 포용성
• 완전성
• 독립성

3 속성

• 개체(Entity)
• 속성(Attributes)
• 관계(Relationship)

4 ER모델

1 개념

현실 데이터와 관계를 사람이 이해할 수 있는 형태로 표현

개체, 속성, 관계로 기술

2 ERD 기호

• 개체: 사각형
• 관계: 마름모
• 속성: 타원
• 다중 값 속성: 이중타원
• 관계-속성 연결: 선

5 정규화

1 개념

관계형 데이터베이스의 설계에서 중복을 최소화

2 이상 현상

정규화를 하지 않았을 때 생기는 문제점

• 삽입 이상: 부필요한 세부정보 입력
• 삭제 이상: 원치 않는 다른 정보가 같이 삭제
• 갱신 이상: 특정 부분만 수정되어 중복된 값이 모순

3 단계

1. 1정규형(1NF): 도메인 원자값으로 구성
2. 2정규형(2NF): 부분 함수 종속 제거
3. 3정규형(3NF): 이행함수 종속 제거 - A->B & B->C => A->C
4. BCNF: 결정자 함수 이면서 후보키가 아닌 것 제거
5. 4정규형(4NF): 다치 종속성 제거
6. 5정규형(5NF): 조인 종속성 제거

2 물리 데이터 저장소 설계

1 물리 데이터 모델

1 개념

2 변환 절차

2 물리 데이터 저장소 구성

1 참조무결성 제약조건

릴레이션 사이에서 참조의 일관성 보장

외래키의 값은 항상 참조되는 릴레이션에 기본키로 존재

옵션

• 제한: 참조무결성 원칙 위배 연산 거절
• 연쇄: 참조되는 릴레이션에서 이 튜플을 참조하는 튜플도 함께 삭제
• 널 값: 삭제된 튜플을 참조하는 튜플들의 외래 키에 NULL 값

ALTER TABLE EMPLOYEE ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (deptno)
REFERENCES DEPARTMENT(deptno)
ON DELETE {RESTRICT | CASCADE | SET NULL};

2 인덱스 설계

1 개념

검색 최적화를 위해 열에 대한 정보를 구성한 데이터구조

2 적용 기준

인덱스 분포도가 10~15%

3 컬럼 선정

분포도가 좋은 컬럼 단독 선정

자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성

수정이 빈번하지 않은 커럶 선정

4 고려사항

• 지나치게 많은 인덱스로 인한 오버헤드
• 인덱스 저장 공간
• 오버헤드

3 뷰 설계

1 속성

• REPLACE: 뷰 재생성
• FORCE: 본 테이블에 관계없이 뷰 생성
• NOFORCE: 본 테이블이 존재할 때 뷰 생성
• WITH CHECK OPTION: 서브 쿼리 내 조건을 만족하는 행만 변경
• WITH READ ONLY: DML 작업 불가

2 고려사항

• 수행속도
• 최적의 액세스 경로

4 클러스터 설계

1 적용 기준

• 인덱스 단점 해결(분포도가 넓을수록 유리)
• 대량의 범위를 자주 액세스하는 경우 적용
• 빈번한 조인이 있을 때 사용

2 고려사항

• 검색 효율은 좋으나, 입력, 수정, 삭제 시 부하 증가
• 처리 범위 문제 - 단일 테이블 클러스터링 고려
• 많은 조인 문제 - 다중 테이블 클러스터링 고려

5 파티션 설계

1 종류

• 레인지 파티셔닝
• 해시 파티셔닝
• 리스트 파티셔닝
• 범위 분할 이후 재분할

2 장점

• 성능 향상
• 가용성 향상
• 백업 가능
• 경합 감소

6 디스크 구성 설계

• 정확한 용량 산정을 통해 디스크 사용 효율 향상
• 입출력 경합 최소화

3 데이터 조작 프로시저 작성

1 데이터 조작 프로시저 개발

1 프로시저 개념

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

• 저장된 프로시저
• 저장된 함수
• 저장된 패키지
• 트리거

2 PL/SQL

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

작성 절차

1. 데이터 저장소 연결
2. 데이터 저장소 정의: 생성, 수정, 삭제 방법
3. 데이터 조작 프로시저 작성: 테이블 정보 입력
4. 데이터 검색 프로시저 작성: 조건에 맞는 데이터 조회

2 데이터 조작 프로시저 테스트

1 PL/SQL 테스트

DBMS_OUTPUT 패키지 활용

2 저장 객체 테스트

• 저장된 프로시저
• 저장된 함수
• 저장된 패키지
• 트리거

4 데이터 조작 프로시저 최적화

1 쿼리 성능 개선

1 개념

프로시저 수정

2 절차

1. 문제 있는 SQL 식별
2. 옵티마이저 통계 확인
3. SQL문 재구성
4. 인덱스 재구성
5. 실행 계획 유지 관리

2 옵티마이저

1 개념

SQL의 최적의 처리 경로(실행 계획)를 생성해주는 DBMS 내부의 핵심 엔진

2 유형

• RBO: 규칙에 따라 질의 실행 계획 선택
  • 평가 기준: 인덱스 구조, 연산자, 조건절 형태
  • 장점: 사용자가 원하는 처리 경로 유도가 쉬움.
• CBO: 통계 정보로부터 모든 접근 경로를 고려한 질의 실행 계획 선택
  • 평가 기준: 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값 수, 컬럼 값 분포, 인덱스 높이
  • 장점: 성능 보장

3 역할

• 쿼리 변환
• 비용 산정
• 계획 생성

4 힌트 사용

옵티마이저의 실행 계획을 원하는 대로 변경

3 SQL문 재구성

• 특정 값 지정
• 별도의 SQL 사용
• 힌트 사용
• HAVING 미사용
• 인덱스만 질의 사용

4 인덱스 재구성

• 자주 쓰는 컬럼 선정
• SORT 명령어 생략
• 분포도 고려
• 변경 적은 컬럼 선정
• 결합 인덱스 사용