1 현행 시스템 분석

1 현행 시스템 파악

1 개념

현행 시스템을 구성하는 하위 시스템, 제공 기능, 연계 정보, 사용하는 기술 요소를 파악

2 절차

1. 구성 / 기능 / 인터페이스 파악
   1. 구성: 기간 업무와 지원 업무로 구분하여 파악
   2. 기능
   3. 인터페이스: 데이터의 형식, 통신규약, 연계유형 파악
2. 아키텍처 / 소프트웨어 구성 파악
   1. 아키텍처: 계층별 사용 기술 요소 파악
   2. 소프트웨어: 제품명, 용도, 라이선스 적용 방식, 라이선스 수 파악
3. 하드웨어 / 네트워크 구성 파악
   1. 파드웨어: 서버 위치, 서버 사양, 수량, 이중화 구현 여부 파악
   2. 네트워크: 네트워크 장비 파악

3 소프트웨어 아키텍처

1 개념

소프트웨어를 설계하고 전개하기 위한 지침이나 원칙

2 프레임워크

소프트웨어 집약적인 시스템에서 아키텍처가 표현해야 하는 내용 및 이들 간의 관계를 제공하는 아키텍처 기술 표준

• 아키텍처 명세서
• 이해관계자
• 관심사
• 관점
• 뷰
• 근거

3 4+1 뷰

고객의 요구사항을 정리해 놓은 시나리오를 4개의 관저에서 바라보는 소프트웨어적인 접근 방법

• 유스케이스 뷰: 다른 뷰 검증
• 논리 뷰: 설계 모듈의 추상화
• 프로세스 뷰: 런타임 시 테스크, 스레드, 프로세스 간 관계 표현
• 구현 뷰: 개발자 관점
• 배포 뷰: 실행 파일과 런타임 컴포넌트가 해당 플랫폼에 매핑되는 방식

4 현행 시스템 분석서 작성 및 검토

1. 자료 수집
2. 수집 자료 분석
3. 산출물 작성
4. 산출물 검토

2 개발 기술 환경 정의

1 시스템 분석

1 운영체제

사용자가 컴퓨터를 좀 더 쉽게 사용하기 위해 지원하는 소프트웨어

• 품질 측면
  • 신뢰도
  • 성능
• 지원 측면
  • 기술 지원
  • 주변 기기
  • 구축 비용

2 네트워크

데이터 링크를 사용하여 서로에게 데이터를 교환할 수 있도록 하는 기술

OSI 7계층

• 백본망: 다른 LAN이나 부분망 간에 정보를 교환하기 위한 경로 제공
• 라우터: 3계층, 데이터 패킷을 발신지에서 목적지까지 전달하기 위해 최적의 경로를 지정
• 스위치: 2계층, 동일 네트워크 내에서 출발지에 들어온 데이터 프레임을 목적지 MAC 주소 기반으로 빠르게 전달하는 장비
• 게이트웨이: 서로 다른 통신망, 프로토콜을 사용하는 네트워크 간의 통신을 가능하게 하는 장비
• 방화벽: 불법 침입과 불법 정보 유출 방지

3 DBMS

데이터베이스를 만들고, 저장 관리 기능 제공

• 중복 제거
• 접근 통제: 권한에 따른 접근 제어
• 인터페이스 제공
• 관계 표현
• 샤딩/파티셔닝: 구조 최적화를 위해 작은 단위로 나누는 기능 제공
• 무결성 제약조건
• 백업 및 회복

• 성능 측면
  • 가용성
  • 성능
  • 상호 호환성
• 지원 측면
  • 기술 지원
  • 구축 비용

4 미들웨어

분산 컴퓨팅 환경에서 응용 프로그램과 프로그램이 운영되는 환경 간 통신을 제어해주는 소프트웨어

• 성능 측면
  • 가용성
  • 성능
• 지원 측면
  • 기술 지원
  • 구축 비용

2 요구사항 파악

1. 자료 수집
2. 자료 분석 및 개발 기술 환경 결정
3. 요구사항 정의서, 목표 시스템 구성도 반영 및 검토

2 요구사항 확인

1 요구사항

1 개념

고객에 의해 요구되거나, 시스템이 가져야 하는 서비스 또는 제약사항

• 기능적 요구사항: 기능성,완전성, 일관성
• 비기능적 요구사항: 신뢰성, 사용성, 효율성, 유지보수성, 이식성

2 개발 절차

1. 도출: 인터뷰, 설문 조사, 브레인스토밍, 워크숍
2. 분석: 자료 흐름 지향 분석, 객체지향 분석
3. 명세: 자연어에 의한 방법, 정형화 기법 사용 방법
4. 확인: 동료 검토, 워크 스루, 인스펙션

3 관리 절차

1. 요구사항 협상: 우선순위 설정, 시뮬레이션
2. 요구사항 기준선: 공식 회의, 형상 관리
3. 요구사항 변경 관리: CCB, 영향도 분석
4. 요구사항 확인 및 검증: 확인 및 검증

4 분석 기법

• 요구사항 분류
• 개념 모델링
• 요구사항 할당
• 요구사항 협상
• 정형 분석

5 확인

• 요구사항 검토
• 프로토타이핑: 사용자가 요구한 주요 기능을 프로토 타입으로 구현하여, 피드백을 통해 개선, 보완하여 완성
• 모델 검증
• 인수 테스트

1. 요구사항 목록 확인
2. 요구사항 정의서 작성 여부 확인
3. 비기능적 요구사항 확인
4. 타 시스템 연계 및 인터페이스 요구사항 확인

2 시스템화 타당성 분석

검토 항목

• 성능 및 용량 산정의 적정성
• 시스템 간 상호 운용성
• IT 시장 성숙도 및 트렌드 부합성
• 기술적 위험 분석

분석 절차

1. 타당성 분석 결과 기록
2. 타당성 분석 결과의 이해관계자 검증
3. 타당성 분석 결과 확인 및 배포/공유

3 비용산정 모델

1 개념

소프트웨어 규모 파악을 통한 투입 자원, 소요 시간을 파악하여 실행 가능한 계획을 수립하기 위해 비용을 산정하는 기법

2 하향식 비용산정 모델

경험이 많은 전문가에게 비용산정을 의뢰 / 여러 전문가와 조정자를 통해 산정

• 전문가 판단: 경험이 많은 두 명 이상의 전문가에게 비용 산정 의뢰
• 델파이 기법: 전문가의 경험적 지식을 통한 문제해결, 한 명의 조정자와 여러 전문가로 구성

3 상향식 비용산정 모델

세부적인 요구사항과 기능에 따라 필요한 비용 계산

• LoC: 원시 코드 라인 수의 비관치, 낙관치, 기대치를 측정하여 예측치를 구하고, 이를 이용하여 비용 산정
• Man Month: 한 사람이 1개월 동안 할 수 있ㄴㄴ 일의 양을 기준으로 프로젝트 비용 산정
• COCOMO: COnstructive COst MOdel, 개발 노력 승수 결정
  • 단순형: 5만 라인 이하의 소프트웨어 개발
  • 중간형: 30만 라인 이하의 소프트웨어 개발
  • 임베디드형: 30만 라인 이상의 소프트웨어 개발
• Putnam: 소프트웨어 개발 주기의 단계별로 요구할 인력의 분포 가정, 자동화 추정 도구 - SLIM
• FP: 인자별 가중치를 부여하여 기능 점수를 계산
  • 정규법: 상세한 기능점수 측정 가능
  • 간이법: 프로젝트 초기에 개발 비용 측정 가능

3 분석 모델 확인하기

1 분석 모델 검증

요구사항 도출 기법을 활용하여 업무 분석가가 제시한 분석 모델에 대해 확인

1 방법

• 유스케이스 모델 검증
• 개념 수준의 분석 클래스 검증
• 분석 클래스 검증

3 분석 클래스 스테레오 타입

• 경계: 시스템과 외부 액터와의 상호작용 담당
• 엔티티: 시스템이 유지해야 하는 정보 관리
• 제어: 시스템이 제공하는 기능의 로직 및 제어 다당

3 절차

• 검토 의견 컬럼 추가
• 검토 의견 작성
• 검토 의견 정제

2 분석 모델의 시스템화 타당성 분석

검토 항목

• 성능 및 용량 산정의 적정성
• 시스템 간 상호 운용성
• IT 시장 성숙도 및 트렌드 부합성
• 기술적 위험 분석

절차

• 타당성 검토의견 컬럼 추가
• 타당성 검토의견 작성
• 타당성 분석 결과 검증
• 타당성 분석 결과 확인 및 배포/공유

1 데이터 전환 기술

1 초기 데이터 구축

1 구축 전략 수립

• 초기 데이터 구축: 데이터 결과 분석을 통해 대상의 기본 정보 및 기초자료 확보
• 데이터 복구 방안 마련: 기존 데이터 분석을 통한 기초자료 구축 방안 제시
• DB 구축 지침 제시: 기초 데이터와 대표 값의 비교

2 초기 데이터 구축 대상 파악

초기 데이터 구축을 위한 대상을 식별하고 대상을 목록화

3 초기 데이터 구축 범위 확정

업무 협의를 통해 초기 데이터 구축 범위 확정

4 초기 데이터 구축 시 세부 고려사항

• 데이터 이관 시 업무 중단 최소화, 안정성 확보 방안 마련
• 데이터 이관 정합성 검증을 통한 오류 방지 방안 제시
• 이관 완료 후 데이터 품질 검증 실시
• 개인정보 데이터는 암호화

2 ETL(Extraction, Transformation, Loading)

1 개념

조직 내외부의 복수의 출처들로부터 분석을 위한 저장소로 데이터를 이동

원천 시스템으로부터 데이터 추출 및 변환 -> 목적 시스템으로 전송 및 로딩

2 프로세스

시간 절약을 위한 3가지 단계를 동시 수행 가능

일반적으로 발생하는 데이터 변환에는 필터링, 정렬, 집계, 중복 제거, 유효성 검증 등의 작업 포함

특수한 엔진에서 진행

전체 추출을 기다리지 않고 변환, 로드 진행 가능

추출 방법: JDBC 기술 이용, ODBC 기술 이용, Flat File 생성

1. 데이터 전환 계획 및 요건 정의: 데이터 구조 분석
   1. 프로젝트 계획
   2. 프로젝트 환경 구축
   3. As-Is 시스템 분석
   4. To-Be 시스템 분석
   5. 데이터 이행 요건 정의
2. 데이터 전환 설계: Target / Source 매핑 및 검증 설계
   1. 논리적 매핑
   2. 코드 매핑
   3. 검증규칙 정의
   4. 이행계획 수립
3. 데이터 전환 개발: 전환 및 검증 프로그램 개발
   1. 전환개발 환경 구축
   2. 전환 프로그램 개발
   3. 검증 프로그램 개발
4. 데이터 전환 테스트 및 검증: 반복 테스트로 정합성 확보
   1. 전환 검증 설계
   2. 추출 검증
   3. 변환 결과 검증
   4. 최종 변환 설계
   5. N차 이행 검증
5. 데이터 전환: 성공적인 데이터 전환 수행
   1. 최종 전환
   2. 안정화 지원
   3. 후속 단계 데이터 전환
   4. 전환 완료 보고서 작성

3 파일 처리 기술

1 개념

많은 양의 자료를 각종 매체에 저장

2 유형

순차 파일

입력되는 데이터들을 논리적인 순서에 따라 물리적 연속 공간에 순차적으로 기록

• 장점: 높은 기억 밀도 -> 기억공간 효율적 사용, 어떤 매체에도 적용 가능, 빠른 처리 속도
• 단점: 파일에 신규 레코드 삽입/삭제 시 파일 전체 복사, 순차 검색으로 인한 검색 효율 저하

색인 순차 파일

레코드를 키 값 순으로 정렬시켜 기록하고 키 항목 색인 구성

순차 처리와 랜덤 처리 가능

포인터를 사용하여 직접 참조

• 장점: 목적기반 순차 처리, 랜덤 처리 가능, 삽입, 삭제, 갱신 용이
• 단점: 색인 구역과 오버플로 구역 구성을 위한 추가 공간 필요, 빈번한 파일 추가, 삭제 시 효율 저하

직접 파일

레코드를 순서 없이 임의의 물리적 공간에 기록

해시 함수에 의해 계산된 물리적 주소를 통해 접근

• 장점: 물리적 주소를 통해 직접 접근, 평균 접근 시간
• 단점: 레코드 주소 변환 과정에 추가 시간, 별도의 기억 공간 확보 필요, 기억 공간 효율 저하 가능

2 데이터 전환 수행

1 데이터 전환 수행 계획

1 개념

데이터 전환을 위한 상세 설계, 전환 및 검증 프로그램 개발과 통합 전환을 수행할 수 있도록 계획

데이터 전환 전략

• 데이터 전환 절차의 표준화
• 전담요원의 데이터 전환 검증
• 중요도에 따른 우선순위 결정
• 데이터의 표준화

데이터 전환 수행 절차

원천 데이터베이스를 Staging DB로 백업 후 Staging DB를 기준으로 데이터 전환

2 범위

데이터 전환의 성공 실패 기준

3 일정

팀별 업무를 분장하여 팀별 역할과 납기를 기록

4 데이터 전환 절차

현행(AS-IS) 시스템에서 목표(TO-BE) 시스템으로 전환을 위한 단계를 구분

각 단계별 작업 내용과 사용 도구를 별도로 기록

원천 데이터베이스의 데이터를 백업하여 스테이징 데이터베이스로 복구한 후 정비 및 변환

검증 단계에서는 원천 데이터베이스(스테이징 데이터베이스 X)와 비교하여 오류 확인

5 데이터 전환 계획서 작성

1. 단위 시스템별 데이터 이행 및 검증 계획
2. 전체 시스템 데이터 전환 시나리오 작성 및 검증 계획
3. 최종 데이터 전환 시뮬레이션 검증 계획

6 데이터 전환 수행 단계

1. 요구사항 분석 단계
   1. 프로젝트 계획
   2. 프로젝트 환경구축
   3. 현행 시스템 분석
   4. 목표 시스템 분석
   5. 데이터 전환 요건 정의
2. 설계 단계
   1. 로지컬 매핑
   2. 코드 매핑
   3. 검증 규칙 정의
   4. 전환 설계서 작성
3. 구현 단계
   1. 전환 개발 환경 구축
   2. 전환 프로그램 개발
   3. 검증 프로그램 개발
4. 테스트 단계
   1. 전환 검증 설계
   2. 추출 검증
   3. 변환 결과 검증
   4. 최종 전환 설계
   5. 전환 인프라 구축
   6. n차 전환 검증
5. 데이터 전환 단계
   1. 최종 전환
   2. 안정화 지원
   3. 추혹 단계 데이터 전환
   4. 전환 완료 보고서 작성

2 체크리스트

1 개념

전환 프로그램의 에러, 시간제약, 비즈니스 로직 변경, 긴급 상황 및 위험요인 대응을 위한 측정 가능한 목록

2 작성

상세항목, 작업내역, 예정 시작/종료 시간, 작업자 기재

• 사전작업: 운영환경 점검, 인프라 상태 점검, 데이터베이스 점검
• 전환단계: 테이블 이관작업, 데이터 정제/변환
• 점검단계: 전환 요건항목 검증, SQL 대상 후속 조치

3 데이터 검증

1 데이터 전환 프로그램 검증

• 조직 및 역할 정립
• 유실 데이터 방지

2 전환 계획서 / 체크리스트 기반 검증

데이터 전환 계획서의 내용과 체크리스트의 작업 내용 확인

수행 조직을 확인하고, 팀별 검증 역할 확인

전환 단계별 결과를 확인하고 성과 등을 기록

3 데이터 검증 종류

• 로그 검증: 전환 시 추행되는 추출 적재 로그 작성 검증
• 애플리케이션 검증: 응용 애플리케이션을 통한 프로그램 기반 검증
• 데이터 검증: 사전 정의된 업무 규칙을 통하여 데이터 정합성 검증 수행

4 전환 단계별 결과 검증

• 추출 검증: 원천 데이터 정합성 확인
• 전송 검증: 전송 데이터 유실 확인
• 전환 검증: Stagine DB 구성 후 데이터 검증
• 적재 검증: 전환데이터 정합성 검증
• 통합 검증: 매핑 정의서 오류 검증

3 데이터 정제

1 데이터 정제

1 요청서 작성

전환 시 발생한 오류를 해결하기 위한 데이터 정제 요청 내용 작성

작성 항목

• 정제 ID
• 정제 제목
• 관련 테이블
• 예상 처리 건수

* 오류내역 X

2 보고서 작성

원천 데이터를 확인하고 검증

결과를 반영하여 정제 보고서 작성

오류 데이터 원인과 대응 방안 함께 작성

2 데이터 품질 분석

1 원천 데이터 품질 분석

원천 데이터의 품질을 검증함으로써 전환의 정확성 보장

2 목적 데이터 품질 분석

원천 데이터와 목적 데이터베이스의 속성 간 대응 관계의 정확한 표현을 위해 데이터 레이아웃을 정확하게 파악하고 품질 검증을 진행

3 데이터 품질 관리

지속적인 데이터 및 데이터 구조를 개선하기 위한 활동

요소: 데이터 값, 데이터 구조, 관리 프로세스

3 오류 데이터 측정

1 오류 데이터 측정

데이터 품질 기준에 따라 정상 데이터와 오류 데이터 분리

정량적 측정

• 정상 데이터: 전환 대상 범위의 데이터를 업무 영역별, 테이블별로 세분화
• 오류 데이터: 정합성 기준을 근거로 업무별 오류 위치와 유형 파악

2 오류 원인 파악 / 정제 여부 결정

오류 목록의 심각도, 상태를 참조하여 원천 데이터의 정제 또는 전환 프로그램을 수정할 것인지 데이터 정제 여부 결정

심각도

• 상: 데이터 이행을 진행할 수 없게 만드는 오류
• 중: 데이터 이행 전반에 영향을 미치는 오류
• 하: 데이터 이행에 영향을 미치지 않는 오류

상태

• 열린: 보고 O, 할당 X
• 할당된: 수정을 위해 오류를 개발자에게 할당
• 수정된: 오류 수정
• 종료된: 재 테스트 시 오류 발견 X
• 연기된: 낮은 우선순위로 우류 수정을 연기
• 분류된: 프로젝트 내 오류가 아니라고 판단

1 물리 요소 조사 분석

1 스토리지

1 개념

정보를 보존하는 저장장치

2 구성

• DAS(Direct Attached Storage)
  • 데이터 서버와 외장형 저장 장치를 전용 케이블로 직접 접속
  • 장점: 성능 보장, 안정성
  • 단점: 저장 장치에 따라 다른 접속 방법 -> 공유 문제
• NAS(Network Attacked Storage)
  • LAN을 통해 스토리지와 서버를 접속
  • 파일서버를 통한 파일시스템 공유
  • 장점: 데이터 저장 장소와 서버 간의 독립성 유지
  • 단점: 데이터 접근 요청에 의한 파일 서버의 병목현상 발생 가능, 파일 서버 종료 시 스토리지 접근 불가
• SAN(Storage Area Network)
  • 서버가 광섬유 채널을 통하여 스토리지를 연결
  • 장점: DAS의 접속한계성 극복, n개의 서버가 m개의 저장장치 접속 가능
  • 단점: 이기종 서버환경 지원 X, 공유 가능한 파일시스템과 데이터 형식 제한

2 분산 데이터베이스

1 개념

네트워크의 활용을 통해 단일한 데이터베이스 관리 시스템으로 제어

논리적으로 집중, 물리적으로 분산

2 특징

장점

• 분산 제어 용이
• 지역 자치성
• 효용성과 융통성
• 데이터베이스 복제 및 분산 -> 성능 향상
• 데이터 유실 복구
• 시스템 확장

단점

• 복잡성 증가
• 성능 저하(메시지 교환 오버헤드)
• 개발 비용

3 구성

• 전역 스키마: 분산 데이터베이스에 포함된 모든 데이터 정의
• 분할 스키마: 분산을 위해 단편화된 구성에대한 정의
• 할당 스키마: 단편들의 위치 정보, 중복/비중복 결정
• 지역 스키마: 물리적 이미지를 해당 지역 DBMS가 관리할 수 있도록 저장하기 위해 변환

4 투명성

• 위치 투명성: 데이터의 물리적 위치를 몰라도 됨
• 복제 투명성: 복제 여부를 몰라도 됨
• 병행 투명성: 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우 이상 발생 X
• 분할 투명성: 하나의 논리적인 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 장소에 저장
• 장애 투명성: 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 생기더라도 데이터 무결성 보존, 2PC(Phase Commit) 활용

3 데이터베이스 이중화

1 개념

로컬 데이터베이스의 변경된 내용을 원격 데이터베이스에 복제하고 관리

2 목적

• 데이터베이스의 무정지 서비스
• 사용자는 하나의 데이터베이스에 대해서만 작업 수행
• 여러 개의 데이터베이스 동시 관리

3 형태

• 액티브-액티브: 여러 노드가 동시에 가동
• 액티브: 스탠바이: 하나의 노드만 가동, 나머지 노드들은 대기

4 데이터베이스 암호화

1 개념

데이터베이스에 저장된 데이터를 권한이 없는 사람이 볼 수 없도록 저장

2 유형

• 응용 프로그램 자체 암호화: 암복호화 모듈이 API 형태로 각 애플리케이션 서버에 설치, 응용프로그램에서 호출
• DB 서버 암호화: 암복호화 모듈이 DB 서버에 설치, DB 서버에서 호출
• DBMS 자체 암호화: DB 서버의 DBMS 커널이 자체적으로 암복호화 기능 수행
• DBMS 암호화 기능 호출: 응용 프로그램에서 DB 서버의 DBMS 커널이 제공하는 암복호화 API 호출
• 운영체제 암호화: OS에서 발생하는 물리적인 입출력을 이용한 암복호화

3 적용 방식

컬럼 암호화

• API: 애플리케이션 서버
• 플러그인: DB 서버
• 하이브리드: 플러그인 방식의 단점인 배치 업무의 성능 저하를 보완하기 위해 API 방식 이용

블록 암호화

• TDE 방식: DBMS에 추가 기능으로 제공되는 암호화 기능 이용
• 파일 암호화 방식: OS 상에서 확인 가능한 개체인 파일을 암호화

5 접근제어

1 개념

불법적인 데이터의 접근으로부터 데이터베이스 보호

2 정책

자원에 접근하는 사용자의 접근 모드 및 모든 접근 제한 조건 정의

• 신원기반 접근제어 정책(DAC): 주체나 그들이 속해있는 그룹들의 신분에 근거하여 접근 제한
  • IBP: 단일 사용자가 하나의 객체에 대해 허가를 부여받아 사용
  • GBP: 복수 사용자가 하나의 객체에 대해서 같은 허가를 함께 부여받아 사용
• 규칙기반 접근제어 정책(MAC): 객체에 포함된 정보의 비밀성과 비밀성의 접근정보에 대하여 주체가 갖는 권한에 근거하여 객체에 대한 접근 권한 제한
  • MLP: 사용자 및 객체가 각각 부여된 기밀 분류에 따른 정책
  • CBP: 조직 내 특정 집단별로 구분된 기밀 허가에 따른 정책
• 역할기반 접근제어 정책(RBAC): 중앙관리자가 주체와 객체의 상관관계 제어

3 매커니즘

• ACL: 주체가 특정 시스템 객체에 접근할 수 있는 허가 받은 접근 종류들이 기록된 목록
• CL: 주체에게 허가된 자원 및 권한의 목록

4 보안 모델

• 접근 제어 행렬: 임의적 접근 제어 관리를 위한 보안 모델
• 기밀성 모델: 기밀성에 중심을 둔 수학적 모델
• 무결성 모델: 정보 비밀성을 위해 정보의 일방향 흐름 제어를 활용

2 DB 물리 속성 설계

1 파티셔닝

1 개념

대용량의 테이블을 파티션으로 나눔으로써 성능 저하 방지 및 관리에 용이

2 특징

• 전체 데이터 훼손 가능성 감소, 데이터 가용성 향상
• 관리 편의
• 성능 향상

3 유형

• 레인지 파티셔닝: 분할 키 값
• 해시 파티셔닝: 해시 함수의 값
• 리스트 파티셔닝: 값 목록
• 컴포지트 파티셔닝: 2개 이상의 파티셔닝 결합

2 클러스터링

1 개념

• 지정된 컬럼 값의 순서대로 데이터 행 저장
• 여러 테이블을 같은 클러스터 내 저장 가능
• 액세스 효율 향상을 위한 물리적 저장 방법
• 검색 효율 향상, 입력/수정/삭제 시 부하 증가 가능
• 분포도가 넓을수록 유리

2 유형

• 단일 클러스터링: 클러스터에 하나의 테이블만 생성
• 다중 클러스터링: 단일 클러스터에 두 개 이상의 테이블 저장, 조인 속도 향상

3 설계 시 고려사항

• 대량 범위 자주 액세스
• 넓은 분포도
• 여러 개의 테이블이 자주 조인
• 반복 컬럼이 정규화에 의해 어쩔 수 없이 분할
• UNION, DISTINCT, ORDER BY, GROUP BY가 빈번
• 수정이 자주 발생 X

3 데이터베이스 백업

1 개념

데이터가 손상되거나 손실될 경우 피해를 최소화하기 위해 저장

2 종류

• 전체 백업
• 차분 백업: 마지막 전체 백업 이후 변경된 모든 데이터를 백업
• 트랜잭션 로그 백업: 로그 파일에 기록, 느린 속도(데이터 복사 X, DML 다시 수행)

4 테이블 저장 사이징

1 필요성

정확한 데이터 용량 예측 -> 효과적, 확장성 보장

디스크 채널 병목 현상 최소화

용량 설계를 통해 적합한 저장 옵션 지정

2 계산 방법

1. 레코드의 평균 길이 계산

   레코드 평균 길이 = 레코드 헤더 사이즈 + 열 헤더 사이즈 + 열 데이터 사이즈

2. 블록에 들어가는 레코드 수 계산

   블록의 빈 공간 = 블록 사이즈 - 블록 헤더 사이즈 - 예비영역 사이즈

   ​ 블록 사이즈: 기본적으로 8KB

   ​ 블록 헤더 사이즈 = 90 + 트랜잭션 예약 공간

   ​ 예비영역 사이즈 = 블록 사이즈 - 블록 헤더 사이즈 * 예비영역 비율

   블록에 들어가는 레코드 수 = 예비영역 사이즈 / 레코드 평균 길이

3. 테이블 용량 계산

   테이블 용량 = 예상 레코드 수 / 블록에 들어가는 레코드 수 * 블록 사이즈

3 옵션의 고려사항

• 트랜잭션 관련 옵션: 트랜잭션 예약 공간(INITRANS) 파라미터 값 조정
• 예비 영역 옵션: 예비 영역 비율(PCT_FREE) 파라미터 값 조정

5 데이터 지역화

1 개념

데이터베이스 저장 데이터를 효율적으로 이용할 수 있도록 저장하는 방법

필요한 위치에 가까이 배치

2 조건

모든 코드나 데이터를 균등하게 접근하지 않는다

3 종류

• 시각적 지역화: 반복문에 사용하는 조건 변수
• 공간적 지역화: 배열에 연속 접근
• 순차적 지역화분기가 없는 데이터가 기억장치에 저장된 순서대로 순차적으로 인출되고 실행

4 활용 사례

• 기억장치 계층구조: CPU -> 캐시 메모리 -> 메인 메모리 순으로 접근시간 단축
• 캐시 접근시간 단축: 캐시 적중률 극대화
  • LRU: 가장 적게 사용된 데이터 교체
  • 선인출 방식: 필요한 정보와 예상 정보를 미리 인출
• 작업세트: 하나의 페이지가 자주 접근하는 페이지들의 집합

3 물리 데이터베이스 모델링

1 데이터베이스 무결성

1 개념

데이터베이스에 저장된 데이터 값과 실제 값이 일치하는 정확성

권한이 있는 사용자로부터 데이터베이스 보호

cf ) 보안: 권한이 없는 사용자로부터 데이터베이스 보호

2 종류

• 개체 무결성: 기본키 중복 불가, 기본키 Null 불가
• 참조 무결성: 외래키가 참조하는 키가 기본키 또는 Null
• 속성 무결성: 속성의 값은 기본, Null, 도메인이 지정된 규칙을 준수
• 사용자 무결성: 사용자의 의미적 요구사항 준수
• 키 무결성: 중복 튜플 불가

2 컬럼

1 개념

테이블을 구성하는 요소, 자료형 및 길이로 정의

2 구성요소

• 내장 데이터 형식: 문자, 숫자, 시간, 대형 객체 등
• 확장 데이터 형식: 행, 컬렉션, 사용자 정의, 데이터 형식 등

3 고려사항

• 타입에 따른 물리적 순서 조정
  • 고정 길이 & Not Null인 컬럼은 앞에 배치
  • 가변 길이는 뒤에 배치
  • Null 값이 많을 것으로 예상되는 컬럼은 뒤에 배치
• DBMS별 물리적 순서 조정: 체인 발생을 억제하고 저장 공간의 효율적인 사용을 위해 필요
• 데이터 타입, 길이 지정 고려
  • 가변 길이는 최대 길이로 정의
  • 고정 길이는 최소 길이 지정
  • 소숫점 이하 반올림을 고려하여 정확성 확인
• 컬럼 간 비교 방법

3 키

1 개념

다른 튜플과 구별할 수 있는 기준이 되는 속성

2 특성

• 유일성: 식별자에 의해 엔터티 타입 내에 모든 엔터티들이 유일하게 구분 (유일한 튜플)
• 최소성: 최소한의 속성으로 식별자 구성

3 종류

• 기본키(Primary Key): 테이블의 각 튜플을 고유하게 식별하는 컬럼, Not Null
• 대체키(Alternate Key): 후보키 중 기본키로 선택되지 않은 키
• 후보키(Candidate Key): 테이블에서 각 튜플을 구별하는 기준이 되는 컬럼(기본키 + 대체키)
• 슈퍼키(Super Key): 유일성은 만족하지만 최소성은 만족하지 못하는 키
• 외래키(Foreign Key): 다른 릴레이션의 기본키로 이용되는 키, 참조 무결성을 위한 제약 조건

4 DB 반정규화

1 반정규화

1 개념

정규화된 엔터티, 속성, 관계에 대해 성능 향상과 단순화를 위해 중복, 통합, 분리

2 특징

• 장점: 성능 향상과 관리 효율성
• 단점: 이로간성 및 정합성 저하, 유지 비용 발생

3 기법

테이블

• 테이블 병합: 1:1 관계, 1:M 관계 통합 -> 조인 횟수 감소 -> 성능 향상
• 테이블 분할: 테이블을 수직 또는 수평으로 분할
• 중복 테이블 생성: 집계함수를 사용하여 효과적인 수행을 위해 테이블 추가

컬럼

• 중복 컬럼 생성: 조인 성능 저하 예방

관계

• 중복 관계 추가: 조인 시 발생할 수 있는 성능 저하를 예방하기 위해 관계 추가

5 물리 데이터 모델 품질 검토

1 물리 데이터 모델 품질 기준

1 개념

물리 데이터 모델은 시스템 성능에 직접적 영향 -> 성능 문제 사전 검토 필요

2 요소

• 정확성
• 완전성
• 준거성
• 최신성
• 일관성
• 활용성

물리 ERD

1 엔터티와 테이블의 물리 데이터 모델로 변환

2 관계 변환

• 1:1 변환
• 1:N 변환
• N:M 변환

N:M 변환 케이스

• 외래키 분리: 관계별로 관계 컬럼을 생성
• 외래키 결합: 관계들을 하나의 관계 컬럼으로 통합

3 관리 목적 테이블 또는 컬럼의 추가

프로그래밍의 수행 속도 향상을 위해 추가되는 테이블이나 컬럼으로 관리 가능

4 데이터 타입 선택

논리 모델링에서 정의된 논리적인 데이터 타입을 물리적인 DBMS 특성과 성능 고려를 통해 최적 데이터 타입 선택

5 데이터 표준 적용

1. 요구사항 수집
2. 표준 정의
3. 표준 확정
4. 표준 관리

3 CRUD 분석

1 개념

시스템 개발 시 프로세스와 데이터베이스에 저장되는 데이터 사이의 관계를 CRUD로 분석

2 CRUD 매트릭스

1 개념

프로세스와 데이터 사이의 관계 의존성을 CRUD로 표현한 매트릭스

2 필요성

• 모델링 작업 검증: 분석 단계의 데이터 모델과 프로세스 모델에 대한 작업 검증 역할 수행
• 중요 산출물: 시스템 구축 단계에서 애플리캐이션 개발 시 필요
• 테스트 시 사용 산출물: 애플리케이션을 객관적 자료를 사용하여 테스트 시 중요한 테스트 케이스 역할 수행
• 인터페이스 현황 파악: 전체 업무의 인터페이스 파악

3 구성요소

• 엔터티 타입: 프로세스에 영향을 받는 데이터
• 단위 프로세스: 엔터티에 영향을 주는 업무 단위
• CRUD: 프로세스가 엔터티에 주는 영향

4 규칙

• 엔터티 타입
  • 모든 엔터티에 C 존재: 데이터는 1번 생성
  • 모든 엔터티에 R 존재: 최소 한번 읽기
  • 모든 엔터티에 CRUD 존재: CRUD 중 최소 하나
• 프로세스
  • 두 개 이상의 단위 프로세스가 하나의 엔터티 타입 생성 불가: 같은 엔터티에 C 두 개 이상 불가
  • 모든 단위 프로세스는 하나 이상 엔터티 타입에 표기: 단위 프로세스에 CRUD 중 최소 하나

4 SQL 성능 튜닝

1 개념

최소의 자원을 이용하여 데이터베이스로부터 최적의 성능을 제공하는 개선활동

2 기법

• 옵티마이저 조정
• 힌트 사용
• 부분 범위 처리 사용
• 인덱스 활용

1 관계 데이터베이스 모델

1 관계 데이터 모델

1 개념

실세계 데이터를 행과 열로 구성된 테이블 형태로 표현하는 데이터 모델

2 구성

• 릴레이션: 테이블
• 튜플(Tuple): 행
• 속성(Attribute): 열, 데이터베이스를 구성하는 가장 작은 논리적 단위
• 카디널리티(Cardinality): 튜플 수
• 차수(Degree): 속성 수
• 스키마(Schema): 릴레이션 구조
• 인스턴스(Instance): 정의된 스키마에 따라 생성된 테이블에 저장된 데이터 집합

2 관계 데이터 언어

1 관계 대수

1 개념

2 연산자 종류

일반 집합 연산자

• 합집합 ∪
• 교집합 ∩
• 차집합 -
• 카티션 프로덕트 ×

순수 관계 연산자

• 셀렉트 σ: 조건을 만족하는 튜플 반환
• 프로젝트 π: 주어진 속성들의 값으로만 구성하는 튜플 반환
• 조인 ⋈: 공통 속성을 이용하여 튜플을 연결
• 디비전 ÷: 관련 있는 튜플 반환

2 관계 해석

1 개념

튜플 관계 해석과 도메인 관계 해석을 하는 비절차적 언어

2 특징

Predicate Calculus에 기반한 언어, 비절차적 언어

3 종류

• 튜플 관계 해석: 튜플 해석식으로 정의
• 도메인 관계 해석: 도메인 해석식으로 정의하는 표기법

관계 해석 논리기호

연산자

• OR
• AND
• NOT

정량자

• 전칭 정량자: 모든 가능한 튜플(for all) - A
• 존재 정량자: 어떤 튜플(there exists) - E

3 관계 대수와 관계 해석 비교

3 시스템 카탈로그

1 개념

시스템 관련 객체의 정보를 포함하는 시스템 데이터베이스

DDL로 생성되는 데이터베이스 구조 및 통계 정보 저장

2 특징

• = 자료 사전
• 메타 데이터: 시스템 카탈로그에 저장된 정보
• 일반 사용자가 조회는 가능하지만, 갱신 불가(DBMS가 스스로 생성 및 유지)
• 기본 테이블, 뷰, 인덱스의 변화 시 자동 갱신

4 뷰

1 개념

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위한 가상 테이블

2 특징

• 논리적으로 구성(물리적 X), 논리적 독립성 제공
• 뷰에 보이지 않는 데이터 보호
• 데이터 접근 제어에 대한 보안
• CREATE로 생성, DROP으로 삭제 (ALTER로 변경 불가)
• 삽입, 삭제, 갱신에 제약사항
• 기본 테이블 삭제 시 자동 삭제
• 뷰 위에 다른 뷰 정의 가능

2 데이터 모델링 및 설계

1 데이터 모델

1 개념

현실 세계의 정보를 추상화하여 표현한 모델

2 절차

1. 개념적 데이터 모델: ERD
2. 논리적 데이터 모델: 정규화
3. 물리적 데이터 모델: 반정규화

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

1 개념

데이터 간 관계를 이해할 수 있는 형태로 표현하기 위해 사용하는 모델

2 구성요소

• 개체: 테이블
• 속성: 컬럼
• 관계: 1:1, 1:N, N:M

3 ERD 기호

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

3 논리적 데이터 모델링

1 개념

업무의 모습을 모델링 표기법으로 형상화

2 종류

• 관계 데이터 모델: 테이블 형태
• 계층 데이터 모델: 트리 형태, 1:N만 허용
• 네트워크 데이터 모델: 그래프 형태, CODASYL DBTG 모델이라고 불린다, N:M

4 데이터베이스 정규화

1 개념

관계형 데이터베이스 설계 시 중복을 최소화하기 위해 데이터를 구조화

2 목적

• 중복 데이터의 최소화 -> 테이블 불일치 위험 최소화
• 이상 현상 최소화 -> 안정성
• 어떤 릴레이션이라도 데이터베이스 내에서 표현 가능
• 데이터 삽입 시 릴레이션 재구성 최소화
• 효과적인 검색 알고리즘 생성

3 이상 현상

• 삽입 이상: 202003 이철수 등록 시 지도교수가 없으면 삽입 불가
• 삭제 이상: 이교수 퇴사 시 김영희 삭제
• 갱신 이상: 홍길동의 지도교수 변경 시 김교수 정보 삭제

4 데이터베이스 정규화 단계

• 1차 정규형(1NF): 원자화
• 2차 정규형(2NF): 부분 함수 종속 제거 -> 완전 함수적 종속 관계
• 3차 정규형(3NF): 이행 함수 종속 제거
• BCNF: 결정자가 후보키가 아닌 함수 제거
• 4차 정규형(4NF): 다중 값 종속성 제거
• 5차 정규형(5NF): 조인 종속성 제거

5 논리 데이터 모델 품질 검증

1 개념

데이터 모델이 업무 환경에서의 요구사항을 구현한 것을 평가

2 데이터 모델 요건

• 완전성: 업무에 필요한 모든 데이터
• 중복 배제
• 비즈니스 룰: 업무 규칙을 데이터 모델에 표현하고 이를 모두 공유
• 데이터 재사용: 데이터 통합성과 독립성 고려
• 안정성 및 확장성
• 간결성
• 의사소통
• 통합성

3 데이터 모델 품질 검증 기준

• 정확성
• 완전성
• 준거성
• 최신성
• 일관성
• 활용성

1 기본 SQL 작성

1 데이터 정의어(DDL)

1 개념

데이터 구조와 관련된 명령어

2 대상

• 도메인 : 속성의 정보
• 스키마 : 데이터 구조
• 테이블 : 데이터 저장 공간
• 뷰 : 가상의 테이블
• 인덱스 : 검색을 빠르게 하기 위한 데이터 구조

3 명령어

4 활용

테이블 생성

CREATE TABLE [테이블명]
(
  [속성명] [데이터타입] (NOT NULL),
  ...
  PRIMARY KEY([기본키])
  UNIQUE([속성명])
  FOREIGN KEY([외래키]) REFERENCES [참조테이블([기본키])]
  CONSTRAINT [제약조건명] CHECK([조건식])
);

열 추가

ALTER TABLE [테이블명] ADD [속성명] [데이터타입] (DEFAULT [값]);

열 타입 변경

ALTER TABLE [테이블명] MODIFY [속성명] [데이터타입] (DEFAULT [값]);

테이블 삭제

DROP TABLE [테이블명];

테이블 내용 삭제

TRUNCATE TABLE [테이블명];

5 제약조건 적용

테이블의 행이 조작될 때마다 적용

• PRIMARY KEY : 기본키
• FOREIGH KEY : 외래키
• UNIQUE : 테이블 내에서 유일한 값을 가져야 하는 항목
• NOT NULL : NULL 불가
• CHECK : 참이어야 하는 조건

* 테이블 생성 시 제약 조건 명시, ALTER로 병경 가능

2 관계형 데이터 모델

1 개념

계층 모델과 망 모델의 구조를 단순화시킨 모델

* 계층 모델 : 트리 구조로 1:N 관계만 표현 가능, 삽입 및 삭제 연산이 복잡하다.

* 망 모델 : 그래프 구조로 1:1, 1:N, N:M 관계 표현 가능, Owner-Member 관계

2 표현 ( cf E-R 모델 )

관계형 데이터 모델

E-R 모델

3 트랜잭션

1 개념

데이터베이스 시스템에서의 작업의 기본 단위

2 특징

• 원자성(Atomicity) : 작업의 최소 단위, All or Nothing
• 일관성(Consistency) : 트랜잭션이 실행 성공 후 일관된 데이터베이스 상태 보존
• 격리성(Isolation) : 트랜잭션 실행 중 생성되는 중간 결과를 다른 트랜잭션이 접근 불가
• 영속성(Durability) : 성공 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장

고립화 수준(Isolation Level)

• Read Uncommitted : 연산 중인 데이터를 다른 트랜잭션이 읽기 허용, 연산 불허
• Read Committed : 연산이 완료될 때까지 읽기 제한
• Repeatable Read : 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터 조작 제한
• Serializable Read : 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 접근 제한

* 회복 : 트랜잭션을 수행하는 도중 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업

3 연산

• Commit : 하나의 트랜잭션이 성공적으로 끝났을 때 사용하는 연산
• Rollback : 하나의 트랜잭션이 비정상적으로 종료되었을 때 다시 시작하거나, 부분적으로 연산을 취소하는 연산

4 상태 변화

[트랜잭션 상태전이도]

• 활성 상태(Active) : 초기 트랜잭션이 실행 중일 때의 상태
• 부분 완료 상태(Partially Committed) : 마지막 명령문이 실행된 후의 상태
• 완료 상태(Committed) : 트랜잭션이 성공적으로 완료된 후의 상태
• 실패 상태(Failed) : 정상적으로 실행이 더 이상 진행될 수 없을 때 가지는 상태
• 철회 상태(Aborted) : 트랜잭션이 취소되고, 시작 전 상태로 환원된 상태

5 TCL(Transaction Control Language)

4 테이블

1 개념

필드로 구성된 데이터의 집합체, Relation 또는 Entity라고도 불린다.

2 구성 조건

• 중복 행 無
• 행 순서 無
• 열 순서 無

3 구조

컬럼 < 테이블 < 데이터베이스

4 용어

• Tuple / Row / Record : 튜플은 릴레이션에서 중복 불가
• Attribute / Column
• Identifier : 구분할 수 있는 논리적 개념
• Cardinality : # of Tuple
• Degree : # of Attribute

5 데이터 사전

1 개념

데이터베이스의 데이터를 제외한 모든 정보

내용 변경 권한은 시스템 / 사용자는 단순 조회만 가능

메타데이터로 구성

2 내용

• 사용자 정보(ID, PW, 성별, ...)
• 데이터베이스 객체 정보(테이블, 뷰, 인덱스, ...)
• 무결성 제약 정보
• 함수, 프로시저, 트리거

3 목적

데이터베이스 엔진을 이루는 구성요소가 작업을 수행하는데 필요한 참조 정보

* 옵티마이저 : 사용자가 질의한 SQL문 실행 계획을 탐색하고, 비용을 추정하여 최적의 실행 계획을 수립하는 엔진

4 검색

Oracle 데이터 사전 검색

뷰로 접근

• DBA_ : 모든 객체 조회 가능 (시스템 접근 권한)
• ALL_ : 자신의 계정으로 접근 가능한 객체 + 접근 권한이 있는 타 계정의 객체 조회 가능
• USER_ : 현재 자신의 계정이 소유한 객체 조회 가능

MySQL 데이터 사전 검색

information_schema 데이터베이스 안에 테이블 형태로 구성

2 고급 SQL 작성

1 뷰

1 개념

물리 테이블을 이용하여 생성한 가상의 테이블

2 특징

장점

• 논리적 독립성 제공 : 테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
• 사용자 데이터 관리 용이 : 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용 가능
• 데이터 보안의 용이 : 해당 테이블의 일부 정보만 제공 가능

단점

• 뷰 자체 인덱스 증가 : 인덱스는 물리적으로 저장된 데이터 대상 -> 뷰는 인덱스를 가지지 못한다.
• 뷰 정의 변경 불가 : 변경 시 삭제하고 재생성
• 데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있다.

3 조작

생성

CREATE VIEW [뷰이름] ([컬럼명, ...]) AS 
  [쿼리];

* SELECT 문에 UNION, ORDER BY 사용 불가

* 컬럼 명 생략 시 SELECT 문의 컬럼 명이 자동 사용

삭제 / 변경

DROP VIEW [뷰이름]

뷰 정의 자체 변경 불가 -> 삭제 재생성을 통해 변경

2 인덱스

1 개념

테이블의 특정 레코드 위치를 알려 주는 용도 -> 조회 속도를 높여 주는 자료 구조

자동 생성 X cf ) PK 컬럼은 자동으로 생성

조건절에 '='로 비교되는 컬럼을 대상으로 인덱스 생성 시 검색 속도 향상 가능

2 종류

• 순서 인덱스(Ordered Index) : 데이터가 정렬된 순서로 생성, B-Tree 알고리즘 활용
• 해시 인덱스(Hash Index) : 해시 함수에 의해 키 값으로 직접 데이터에 접근, 데이터 접근 비용과 튜플 양과 무관
• 비트맵 인덱스(Bitmap Index) : 각 컬럼에 적은 개수의 값이 저장된 경우 사용, 수정 변경이 적을 경우 유용
• 함수기반 인덱스(Functional Index) : 수식이나 함수를 적용하여 생성
• 단일 인덱스(Singled Index) : 하나의 컬럼으로만 구성
• 결합 인덱스(Concatenated Index) : 두 개 이상의 컬럼으로 구성
• 클러스터드 인덱스(Clutered Index) : PK 기준으로 레코드를 묶어서 저장, 데이터의 물리적 순서에 따라 생성 -> 범위 검색에 유리

3 조작

생성

CREATE (UNIQUE) INDEX [인덱스명] ON [테이블명([컬럼명, ...])];

* UNIQUE : 중복 허용 X

삭제

DROP INDEX [인덱스명];

변경

ALTER (UNIQUE) INDEX [인덱스명] ON [테이블명([컬럼명, ...]);

스캔

• 인덱스 범위 스캔 : 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색 -> 리프 블록을 필요한 범위만 스캔
• 인덱스 전체 스캔 : 수직적 탐색 X -> 리프 블록을 처음부터 끝까지 수평적으로 탐색
• 인덱스 단일 스캔 : 수직적 탐색
• 인덱스 생략 스캔 : 인덱스를 활용하여 스캔

3 집합 연산자

1 개념

여러 테이블 -> 한 테이블(집합)

2 유형

• UNION : 합집합, 중복된 레코드 제거
• UNION ALL : 합집합, 중복된 레코드 포함
• INTERSECT : 교집합
• MINUS : 차집합

4 조인

1 개념

관련 튜플 결합

2 논리적 조인

사용자 SQL문에 표현되는 테이블 결합 방식

내부 조인(Inner Join)

• 동등 조인(Equi Join) : 공통 존재 컬럼의 값이 같은 경우 추출
• 자연 조인(Natural Join) : 같은 컬럼 명을 가진 값이 모두 같은 경우 추출
• 교차 조인(Cross Join) : 조건에 따라 모든 데이터 조합 추출

외부 조인(Outer Join)

• 왼쪽 외부 조인(Left Outer Join) : 왼쪽 테이블의 모든 데이터 + 오른쪽 테이블의 동일 데이터
• 오른쪽 외부 조인(Right Outer Join) : 오른쪽 테이블의 모든 데이터 + 왼쪽 테이블의 동일 데이터
• 완전 외부 조인(Full Outer Join) : 양쪽의 모든 데이터

* USING([컬럼명]) / ON 조건절

3 물리적 조인

옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식

• 중첩 반복 조인 : 임의 접근 O
• 정렬 합병 조인 : 임의 접근 X
• 해시 조인 : 비용 기반 옵티마이저에서만 가능, CPU 성능에 의존적

5 서브쿼리

1 개념

SQL문 안에 포함된 또 다른 SQL문

2 유형

동작 방식 기준

• 연관 서브쿼리 : 서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태, 메인쿼리 수행으로 얻은 데이터를 서브쿼리에서 확인하는 용도
• 비연관 서브쿼리 : 서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태, 메인쿼리에 서브쿼리 결과 제공 용도로 사용

데이터 형태 기준

• 단일 행 서브쿼리 : 결과가 항상 1건 이하, 단일 행 비교 연산자(등호, 부등호) 사용
• 다중 행 서브쿼리 : 결과가 여러 건, 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS) 사용
• 다중 컬럼 서브쿼리 : 결과가 여러 컬럼으로 반환, 비교하는 컬럼의 개수와 위치가 동일해야 한다.

1 절차형 SQL 작성

1 트리거

1 개념

데이터베이스가 미리 정해 놓은 조건이 충족되거나, 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램

* DBMS : 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고, 데이터베이스를 관리해주는 소프트웨어

2 목적

자동 실행, 데이터 무결성 유지 및 로그 메시지 출력

* 데이터 무결성 : 데이터베이스에 저장된 데이터 값과 그것이 표현하는 실제 값이 일치하는 정확성

* 로그 메시지 : 시스템의 모든 기록을 담고 있는 데이터

3 구성

반환 無 / DML(데이터 조작어)을 주된 목적으로 한다. ( 프로시저 O )

EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지 / 외부 변수 IN, OUT 無 ( 프로시저 X )

* 프로시저 : SQL 명령문들을 하나의 함수처럼 실행하는 쿼리 집합

* 사용자 정의함수 : 사용자가 직접 함수를 정의하여 사용할 수 있는 기능

4 구문

CREATE( OR REPLACE) TRIGGER [트리거명]
BEFORE [DML] ON [테이블명]
( FOR EACH ROW ) -- 행트리거
BEGIN
    ( 실행할 SQL문 )
    ( EXCEPTION )
END;

* OR REPLACE 로 기존 트리거 존재 시 현재 컴파일하는 내용으로 Overwrite

5 주의사항

DCL(데이터 제어어) 사용 불가능 ( COMMIT, ROLLBACK, GRANT, REVOKE )

6 구현 및 실행 방법

1. 인식이 가능한 이벤트 정의
2. 대상 이벤트와 관련된 테이블 및 데이터 확인 -> 분석
3. 트리거의 설계 -> 작성 -> 컴파일
4. 이벤트 발생 -> 트리거 자동 실행

2 이벤트

1 개념

특정 시간에 특정 쿼리, 프로시저, 함수 등을 실행시키는 기능

2 구문

CREATE EVENT [이벤트명]
ON SCHEDULE [스케줄]
DO ~

스케줄 : AT 연월일시 / EVERY 간격

3 사용자 정의함수

1 개념

절차형 SQL을 활용하여 결과를 단일 값으로 반환할 수 있는 함수

2 특징

호출을 통해 실행, 반환(프로시저 X)

3 구문

CREATE( OR REPLACE) FUNCTION [사용자 정의함수 명(파라미터)]
IS [지역변수]
BEGIN
    ~
    RETURN ~
END

* OR REPLACE 로 기존 사용자 정의함수 존재 시 현재 컴파일하는 내용으로 Overwrite

* INTERVAL : 이벤트 스케줄 등록할 때 사용, 복수지정 불가능

4 호출쿼리 작성

캡슐화를 제공하는 데 많이 사용

* 캡슐화 : 객체의 속성과 행위를 하나로 묶고, 실제 구현 내용 일부를 외부에 감추어 은닉하는 기법

4 SQL 문법

1 분류

2 WHERE 조건

3 LIKE와 같이 사용하는 와일드 문자

여러 대상을 한꺼번에 지정할 목적으로 사용하는 기호

4 주석 처리

• 한 줄 : '--'
• 여러 줄 : '/' ~ '/'

5 힌트

SQL 문에 사전에 정보를 주어 빠른 결과를 가져오는 효과를 만드는 문법

• 한 줄 : '--+ 힌트 명(파라미터, ...)'
• 여러 줄 : '/*+ 힌트 명(파라미터, ...) */'

2 응용 SQL 작성

DBMS 필수 기능

• 데이터 정의 : 응용 프로그램과 데이터베이스의 인터페이스
• 데이터 조작 : 사용자와 데이터베이스의 인터페이스
• 데이터 제어 : 정확성과 안정성

1 데이터 조작어(DML)

1 개념

데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

2 유형

3 구문

-- SELECT
SELECT [(DISTINCT) 속성명1, (DISTINCT) 속성명2]
  FROM [테이블명1, ...]
  (WHERE [조건])
  (GROUP BY [속성명1, ...])
  (HAVING [그룹조건])
  (ORDER BY 속성 ASC|DESC);


-- INSERT
INSERT INTO [테이블 명(속성1, ...)]
  VALUES([데이터1], [...]);


-- DELETE
DELETE FROM [테이블 명]
  WHERE [조건];


-- UPDATE
UPDATE [테이블 명]
  SET [속성 명 = 데이터], [...]
  WHERE [조건];

* DISTINCT의 경우 동일한 튜플을 제거하고 검색

2 데이터 제어어(DCL)

1 개념

데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 하기 위해 DBA가 사용하는 제어용 언어

2 유형

3 구문

-- GRANT
GRANT [권한] ON [테이블] TO [사용자]
[WITH 권한 옵션];


-- REVOKE
REVOKE [권한] ON [테이블] FROM [사용자]
[CASCADE];

* GRANT 명령문으로 부여할 수 있는 권한 유형

• 시스템 권한
  • CREATE USER : 계정 생성
  • DROP USER : 계정 삭제
  • CREATE TABLE : 테이블 생성
  • DROP ANY TABLE : 테이블 삭제
  • CREATE SESSION : 데이터베이스 접속
  • CREATE VIEW : 뷰 생성
  • CREATE SEQUENCE : 시퀀스 생성
  • CREATE PROCEDURE : 함수 생성
• 객체 권한
  • ALTER
  • INSERT
  • DELETE
  • SELECT
  • UPDATE
  • EXECUTE : 프로시저 실행

* CASCADE : 연쇄적인 권한 해제 시 (WITH GRANT OPTION으로 부여된 사용자들의 권한까지 취소)

3 윈도우 함수

1 개념

행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수

데이터베이스를 사용한 온라인 분석 처리 용도를 사용하기 위해 추가된 기능

OLAP(Online Analytical Processing) 함수라고도 한다.

2 구문

SELECT [함수명(파라미터)]
  OVER
  ([PARTITION BY 컬럼1, ...])
  [ORDER BY 컬럼A, ...]
  FROM [테이블명]

3 분류

집계 함수

여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수, 대다수의 DBMS에서 지원

순위 함수

• RANK : 레코드의 순위 계산, 동일 순위 레코드 존재 시 후순위는 넘어간다.(2등 2개 -> 2위, 2위, 4위)
• DENSE_RANK : 레코드의 순위 계산, 동일 순위 레코드 존재 시 후순위를 넘어가지 않는다.(2등 2개 -> 2위, 2위, 3위, ...)
• ROW_NUMBER : 레코드의 순위 계산, 동일 순위 레코드 존재 시 무관하게 연속 번호 부여(2위 2개 -> 2위, 3위, 4위, ...)

행 순서 함수

• FIRST_VALUE : 파티션 별 윈도우에서 가장 먼저 나오는 값( = MIN )
• LAST_VALUE : 파티션 별 윈도우에서 가장 늦게 나오는 값( = MAX )
• LAG : 파티션별 윈도우에서 1부터 이전 몇 번째 행의 값을 가져온다.
• LEAD : 파티션별 윈도우에서 1부터 이후 몇 번째 행의 값을 가져온다.

그룹 내 비율 함수

• RATIO_TO_REPORT
• PERCENT_RANK

4 그룹 함수

1 개념

소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수

2 유형

ROLLUP

소그룹간 소계 출력

SELECT [컬럼명1], ..., [그룹함수]
  FROM [테이블명]
  (WHERE [조건])
  GROUP BY [컬럼명A], ...
  ROLLUP [컬럼명a], ...
  (HAVING ...)
  (ORDER BY ...)

* ROLLUP의 지정 컬럼은 계층별로 구성 -> 순서에 따라 결과가 다르다.

CUBE

GROUP BY 항목들과 다차원 소계 출력

SELECT [컬럼명1], ..., [그룹함수]
  FROM [테이블명]
  (WHERE [조건])
  GROUP BY [컬럼명A], ...
  CUBE [컬럼명a], ...
  (HAVING ...)
  (ORDER BY ...)

* 연산 多 -> 시스템에 부담

GROUPING SETS

특정 항목에 대한 소계 출력

SELECT [컬럼명1], ..., [그룹함수]
  FROM [테이블명]
  (WHERE [조건])
  GROUP BY [컬럼명A], ...
  ROLLUP [컬럼명a], ...
  (HAVING ...)
  (ORDER BY ...)

* 컬럼 간 순서와 무관한 결과

5 오류 처리

1 개념

프로그램 코드 상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정

2 핸들러 선언 구문

DECLARE [액션] HANDLER
  FOR [상대 값] [명령문]

액션 : CONTINUE - 명령문 계속 실행 / EXIT - 명령문 한 번 실행

상대 값 : 에러코드

• SQLWARNIG : 경고
• NOTFOUND : 레코드를 가져오지 못했을 때
• SQLEXCEPTION : 에러