정보처리기사 실기 수제비 2과목 데이터 입출력 구현

 

논리 데이터 저장소 확인

논리 데이터 모델 검증 ★★★

논리 데이터 모델링 개념

데이터베이스 설계 프로세스의 기초 설계 단계로 비즈니스 정보의 구조와 규칙을 명확하게 표현할 수 있는 기법. 개념 모델로부터 업무 영역의 업무 데이터 및 규칙을 구체적으로 표현한 모델

논리 데이터 모델링 특성

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

논리 데이터 모델링 속성

• 개체
• 속성
• 관계

개체-관계 모델

현실 세계에 존재하는 데이터와 그들 간의 관계를 사람이 이해할 수 있는 형태로 명확하게 표현하기 위해서 가장 널리 사용되고 있는 모델. 개체, 속성, 관계로 기술한 모델.

개체-관계 다이어그램 기호

• 개체 : ㅁ
• 관계 : ◇
• 속성 : Ο
• 다중 값 속성 : ◎
• 관계 속성 연결 : -

정규화

정규화(Normalization)의 개념

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

이상 현상(Anomaly)

• 데이터의 중복성으로 인해 릴레이션을 조작할 때 발생하는 비합리적인 현상이다.

데이터베이스 이상 현상

• 삽입이상
• 삭제이상
• 갱신이상

정규화의 단계

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

물리데이터 모델 설계 ★★★

물리 데이터 모델링 개념

• 물리 데이터 모델링은 논리 모델을 적용하고자 하는 기술에 맞도록 상세화해가는 과정이다.

물리 데이터 모델링 변환 절차

1. 개체를 테이블로 변환
2. 속성을 컬럼으로 변환
3. UID를 기본키로 변환
4. 관계를 외래키로 변환
5. 칼럼 유형과 길이 정의 데이터 유형
   • CHAR : 최대 2000바이트의 고정길이 문자열 저장
   • VACHAR2 : 최대 4000 바이트의 가변 길이 문자열 저장
   • NUMBER : 38 자릿수의 숫자 저장 가능
   • BLOB, CLOB : 바이너리, 텍스트 데이터 최대 4GB 저장
6. 반 정규화 진행

• 반정규화
  • 시스템 성능 향상과 개발 및 운영의 단순화를 위해 데이터 모델을 통합하는 반 정규화 수행
  • 정규화를 위배

물리 데이터 저장소 구성★★★

DBMS를 선정한 이후의 활동

테이블 제약조건(Constraint) 설계

• 참조 무결성 제약조건
  • 릴레이션과 릴레이션 사이에 대해 참조의 일관성을 보장하기 위한 조건이다.
  • 두 개의 릴레이션이 기본키, 외래키를 통해 참조 관계를 형성할 경우, 참조하는 외래키의 값은 항상 참조되는 릴레이션에 기본키로 존재해야한다.
• 제한 : 참조무결성 원칙을 위배하는 연산을 거절하는 옵션
• 연쇄 : 참조되는 릴레이션에서 튜플을 삭제하고, 참조되는 릴레이션에서 이 튜플을 참조하는 튜플들도 함께 삭제하는 옵션
• 널 값 : 참조 되는 릴레이션에서 튜플을 삭제하고, 참조하는 릴레이션에서 해당 튜플을 참조하는 튜플들의 외래 키에 NULL 값을 넣는 옵션

인덱스 개념

• 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터구조
• 인덱스를 통해 전체 데이터의 검색 없이 필요한 정보에 대해 신속한 조회가 가능하다.

인덱스 적용 기준

• 분포도 10~15% 이내
• 부분처리를 목적으로 하는 경우
• 조회 및 출력 조건으로 사용되는 컬럼
• 인덱스 자동생성 기본키와 Unique 키의 제약조건을 사용할 경우

인덱스 컬럼 설정

• 분포도가 좋은 컬럼은 단독적으로 생성한다
• 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성
• 가능한 한 수정이 빈번하지 않은 컬럼을 선정한다.

설계 시 고려사항

• 지나치게 많은 인덱스는 오버헤드(Overhead)로 작용한다
• 인덱스는 추가적인 저장 곤간이 필요함을 고려해야한다
• 넓은 범위를 인덱스 처리 시 오히려 전체 처리보다 많은 오버헤드를 발생시킬 수 있음에 유의해야 한다.
• 인덱스와 테이블의 저장 공간을 적절히 분리될 수 있도록 설계해야한다

뷰 설계

• 뷰의 속성 및 고려사항을 참고하여 설계한다

뷰 속성

• REPLACE : 뷰가 이미 존재하는 경우 재생성
• FORCE : 본 테이블의 존재 여부에 관게없이 뷰 생성
• NOFORCE : 기본 테이블 존재할 때 뷰 생성

뷰 설계 시 고려사항

• 뷰 사용에 따라 수행속도에 문제가 발생할 수 있다.
• 뷰의 조건은 최적의 액세스 경로를 사용할 수 있도록 한다

클러스터(Cluster) 설계

• 클러스터의 적용 기준 및 고려사항을 참고하여 설계한다
• 적용 기준
  • 인덱스의 단점을 해결한 기법으로, 분포도가 넓을수록 오히려 유리하다
  • 대량의 범위를 자주 액세스하는 경우 적용한다
• 클러스터 설계 시 고려 사항
  • 검색 효율은 높여주나 입력, 수정, 삭제 시는 부하가 증가함을 고려한다
  • 수정이 자주 발생하지 않는 컬럼은 검토 대상이다

파티션(Partition) 설계

파티션의 종류

• 파티션의 종류
  • 레인지 파티셔닝, 해시 파티셔닝, 리스트 파티셔닝, 컴포지트 파티셔닝
  • 레해리컴

파티션의 장점

• 성능 향상, 가용성 향상, 백업 가능, 경합 감소
• 성가백합
  • 성가대가 백합꽃을 들고 노래한다.

데이터 조작 프로시저 작성

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

프로시저(Procedure)의 개념

• SQL을 이용해 생성된 데이터를 조작하는 프로그램이다.
• 데이터베이스 내부에 저장되고 일정한 조건이 되면 자동으로 수행된다.

PL/SQL의 개념

• 표준 SQL을 기본으로 Oracle에서 개발한 데이터 조작 언어이다.
• Oracle 기반의 모든 프로시저 작성에 사용되며 표준 SQL의 확장 기능이 우수하다
  • 저장된 프로시저(Stored Procedure)
    • 배치작업, 복잡한 트랜잭션을 수행하는 PL/SQL문을 DB에 저장하는 기능을 제공하는 프로그램
  • 저장된 함수 (Stored Function)
    • 저장 프로시저와 용도는 비슷하나 실행결과를 되돌려 받을 수 있는 프로그램
  • 저장된 패키지(Stored Package)
    • 프로시저나 함수를 효율적으로 관리하기 위해 패키지 단위로 배포할 때 사용하는 프로그램
  • 트리거(Trigger)
    • 특정 테이블에 삽입, 수정, 삭제 등의 변경 이벤트 발생 시 DBMS에서 자동 실행 되도록 구현된 프로그램

PL/SQL 작성 절차

1. JDBC를 통해 연결
   • 드라이버 로딩
   • 데이터베이스 연결 : conn = DriverManager.getConnection(url, "아이디", "비번");
   • 쿼리 전달 : pstmt = conn.prepareStatment(sql);, 쿼리를 DB로 전달하기 위해 Statement, PreparedStatement 객체를 생성.
   • 결과 수신 : ResultSet rs = pstmet.executeQurey();
2. 생성, 수정, 삭제하는 방법을 사전에 정의
3. 데이터 조작 프로시저 작성CREATE OR REPLACE PROCEDURE 프로시저이름()ISBEGINCOMMIT;END;EXEC 프로시저이름();
4. 데이터 검색 프로시저 작성

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

PL/SQL 테스트

DBMS_OUTPUT 패키지 활용메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지(END; 전에 DBMS_OUTPUT.PUT_LINE()로 변수를 찍어서 확인 가능).

DBMS_OUTPUT 패키지 실행 방법 : SET SERVEROUTPUT ONPL/SQL의 처리결과를 화면에 출력하기 위한 SERVEROUTPUT을 ON시키고, 실행하고자 하는 PL/SQL 블록 또는 저장객체 명을 호출. PL/SQL 오류발생 시 'SHOW ERRORS' 명령어를 통해 오류내용 확인.

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

데이터 조작 프로시저 성능개선

쿼리 성능 개선(튜닝)의 개념

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

SQL 성능 개선 절차

1. 문제 있는 SQL 식별 : APM 등을 활용
2. 옵티마이저(최적의 처리 경로를 생성해주는 핵심 모듈) 통계 확인
3. SQL문 재구성
4. 인덱스 재구성
5. 실행계획 유지관리

옵티마이저 통계 확인옵티마이저의 개념SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진. 옵티마이저가 생성한 SQL 처리경로를 실행계획이라고 부름.

옵티마이저 유형

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

SQL 수행 시 옵티마이저 역할

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

힌트 사용SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 함. 옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획을 변경.

주요 옵티마이저 힌트

• /\*+ RULE/ : 규칙 기반 접근 방식을 사용하도록 지정
• /\*+ CHOOSE\*/ : 오라클 옵티마이저 디폴트 값에 따름
• /\*+ INDEX(테이블명 인덱스명)\*/ : 지정된 인덱스를 강제적으로 사용
• /\*+ USE\_HASH(테이블명)\*/ : 지정된 테이블들의 조인이 Hash Join 형식으로 일어나도록 유도
• /\*+ USE\_MERGE(테이블명)\*/ : Sort Merge 형식으로
• /\*+ USE\_NL(테이블명)\*/ : Nested Loop 형식으로