[정보처리기사] 데이터베이스

2019년 6월 정보처리기사 실기 시험을 준비하며 정리했던 내용입니다.

2과목 데이터베이스 실무 응용

1장 데이터베이스 기본

데이터베이스 개념

• 데이터베이스 정의

  특정 조직의 업무를 수행하는데 필요한 상호 관련된 데이터들의 모임

  • 통합 데이터 : 검색의 효율증대를 위한 중복 최소화 데이터
  • 저장 데이터 : 컴퓨터가 접근 가능한 저장 매체에 저장
  • 운영 데이터 : 조직의 목적을 위해 존재 가치가 확실
  • 공유 데이터 : 여러 응용 프로그램들이 공동으로 사용

• 데이터베이스 특징

  • 실시간 접근성 : 사용자의 질의에 대해 즉시 처리하여 응답
  • 계속적인 진화 : 삽입, 삭제, 갱신을 통해 항상 최근 정확한 데이터를 동적으로 유지
  • 동시 공유 : 여러 사용자가 동시에 원하는 데이터를 공용
  • 내용에 의한 참조 : 데이터베이스에 있는 데이터를 참조할 때 사용자가 요구하는 내용에 따라 참조
  • 데이터의 논리적 독립성 : 데이터의 논리적 구조를 변경시키더라도 응용 프로그램은 변경되지 않음
    • 물리적 독립성 : 보조기억장치, 물리적장치를 독립시킴으로써 관리적 측면에서 성능 향상

• 데이터베이스 시스템

  데이터베이스를 이용하여 자료를 저장하고 관리하여 정보를 얻어내는 데 필요한 컴퓨터 중심의 시스템

  • 구성 요소 : 데이터베이스, 스키마, DBMS, 데이터베이스 언어, 사용자, 데이터베이스 컴퓨터

• 데이터 언어

  데이터베이스를 구축하고 이용하기 위한 데이터베이스 관리 시스템(DBMS)과의 통신수단

  • DDL
    • 데이터베이스 구조, 데이터 형식, 접근 방식 등 데이터베이스를 구축하거나 변경목적으로 사용
    • DDL 컴파일러가 컴파일한 후 데이터 사전에 저장
    • 기능
      • 데이터베이스의 논리적, 물리적 구조를 정의 및 변경
      • 스키마에 사용되는 제약조건을 정의
      • 데이터의 물리적 순서 규정
  • DML
    • 데이터 처리를 위해 응용 프로그램과 DBMS간의 인터페이스를 위한 언어
    • 검색, 삽입, 삭제, 갱신 연산
    • DML의 형태
      • 절차적 데이터 조작어 : 어떤 데이터를 필요하는지 데이터를 어떻게 구하는지 명시하는 언어. 어렵다.
      • 비절차적 데이터 조작어 : 어떤 데이터가 필요한지만 명시. 배우기 쉽지만 코드는 비효율.
  • DCL
    • 보안 및 권한, 무결성, 회복, 병행제어를 위한 언어
    • 기능
      • 데이터 보안 : 권한이 없는 접근으로부터 보호
      • 데이터 무결성 : 삽입, 삭제, 갱신마다 제약조건을 검사
      • 데이터 회복 : 시스템 오류 등으로부터 데이터베이스를 회복
      • 병행 제어 : 여러 사용자가 동시에 데이터베이스를 공유

• 데이터베이스 사용자

  • 데이터베이스 관리자
    • DDL과 DCL을 통해 디비를 정의하고 제어하는 사람 (장애복구)
  • 데이터 관리자
    • 데이터 표준 원칙, 데이터 표준, 표준 준수 여부 등을 관리(표준, 데이터 총괄)
  • 데이터 설계자
    • 데이터 구조 원칙, 데이터 구조 정보, 데이터 구조관리 등을 정의 (구조관련, 모델생성)
  • 응용프로그래머
    • 호스트 프로그래밍 언어에 DML을 사용하여 데이터베이스에 접근하는 사람
  • 일반 사용자
    • 질의어를 통해 DBMS에 접근하는 사람

데이터베이스 관리 시스템 (DBMS)

• DBMS개념
  • 데이터베이스와 사용자 사이에서 사용자 요구에 따라 정보를 생성해주고 데이터베이스를 관리해주는 소프트웨어
  • 데이터 종속성, 데이터 중복성 문제 해결
  • DSMS : 데이터 스트림의 특성으로 데이터 처리. 정적이고 영구적인 데이터를 처리.
• 기능
  • 정의 기능(Definition) - DDL : 제약 조건 명시 등 기능
  • 조작 기능(Maniqulation) - DML : 검색, 삽입, 삭제, 갱신 기능
  • 제어 기능(Control) - DCL : 무결성, 보안, 병행 제어 기능
• 단점
  • 어렵다, 디스크 과부하, 전산비용 증가, 백업/회복 어려움, 시스템 복잡

스키마

• 스키마의 개념
  • 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세
  • 개체, 속성, 관계 및 데이터 조작 시 데이터값들이 갖는 제약 조건에 관해 정의
  • 관점에 따라 외부스키마, 개념스키마, 내부스키마로 나뉨
• 특징
  • 데이터의 구조적 특성을 의미
  • 스키마는 데이터 사전에 저장됨
  • 시간에 따라 불변
  • 현실세계의 특정 한 부분의 표현으로서 특정 데이터 모델을 이용해서 만듬
  • 데이터의 논리적 단위에 명칭을 부여하고 그 의미를 기술
• 데이터 사전
  • 디비에 저장되어 있는 모든 데이터 개체들에 대한 정보를 유지/관리하는 시스템.
  • 시스템 카탈로그
  • 이 데이터 사전에는 메타 데이터가 저장되어 있음
• 메타 데이터
  • 데이터에 관한 데이터
  • MARC, DC, ONIX, MODS 등 메타 데이터 포맷이 존재
    • MARC : 목록 레코드를 식별하여 축적/유통할수 있도록 코드화한 메타 데이터
  • 메타 데이터의 상호운용성을 확보하기 위한 방법
    • 하나의 표준 메타 데이터로 통합하여 표현
    • 자원의 특성을 감안하여 다양한 메타 데이터 형식과 기술 구조를 인정하고 상호 매핑
    • MDR(Meta Data Registry)에 의한 해결방법 (등록과 인증을 통해 관리, 명세를 공유)
• 3계층
  • 외부 스키마
    • 사용자나 응용 프로그래머가 필요로 하는 데이터베이스의 논리적 구조를 정의
    • 데이터베이스의 한 논리적인 부분으로 서브 스키마라고도 함
    • 여러 개의 외부 스키마가 존재할수 있음
    • 하나의 외부 스키마는 여러개의 응용프로그램이나 사용자에 의해 공유
    • 외부 스키마는 동일 디비에 대해 서로 다른 관점을 정의
  • 개념 스키마
    • 개체간의 관계와 제약 조건을 나타내고 접근권한, 보안 및 무결성 규정에 관한 명세
    • 데이터베이스의 전체적인 논리적 구조.
    • 모든 응용 프로그램이나 사용자들이 필요로하는 데이터를 통합한 조직 전체의 데이터베이스 명세로 하나만 존재함
    • 스키마라고하면 개념 스키마를 의미
    • 기관이나 조직의 관점에서 데이터베이스를 정의한것
    • DBA에 의해 작성됨
  • 내부 스키마
    • 데이터베이스의 물리적 구조를 정의
    • 물리적 저장장치의 관점에서 본 전체 데이터베이스의 명세로서 하나만 존재
    • 개념 스키마의 물리적 저장 구조에 대한 정의를 기술한것
    • 시스템 프로그래머나 시스템 설계자가 보는 관점의 스키마

데이터베이스 설계

• 개념
  • 데이터베이스의 구조, 스키마를 개발하는 과정
  • 요구 조건 분석, 개념적 설계, 논리적 설계, 물리적 설계 단계를 거침
  • 데이터베이스의 구조에 치중하는 데이터 중심 설계와 데이터 처리 및 응용에 치중하는 처리 중심 설계를 병행
• 요구 조건 분석
  • 데이터베이스를 사용할 사람이 필요로 하는 용도를 파악하는 작업
  • 요구 조건을 수집하고 분석하여 명세를 작성
    • 개체, 속성, 관계, 제약 조건 같은 정적 정보
    • 트랜잭션의 유형, 트랜잭션의 실행 빈도 같은 동적 데이터베이스 처리 요구 조건
      • 트랜잭션 : 디비에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합. 작업의 단위.
    • 기관의 경영 목표 및 정책, 규정 같은 범기관적 제약 조건에 대한 요구
• 개념적 설계
  • 현실 세계의 무한성과 계속성을 이해하고, 현실 세계에 대한 인식을 추성적 개념으로 표현하는 과정
  • 개념 스키마 모델링, 트랜잭션 모델링을 병행하여 수행함
  • 개념 스키마 모델링
    • 데이터의 조직과 표현에 치중 > 데이터 중심 설계
    • 개념 스키마를 기술하는 과정
    • 요구 조건 분석 결과로부터 개념적 데이터 모델을 유도하는 기본원리는 추상화(집단화, 일반화 과정)
  • 트랜잭션 모델링
    • 응용을 위한 데이터 처리에 치중 > 처리 중심 설계
    • 트랜잭션의 입/출력과 기능적 형태를 정의하는 트랜잭션 명세를 작성
    • 입력 데이터, 출력 데이터, 제어의 기능적 흐름을 명세
• 논리적 설계
  • 특정 DBMS가 지원하는 논리적 데이터 구조로 변환 시키는 과정
  • 데이터 구조 즉, 논리 스키마를 설계
  • 먼저 DBMS를 선정하고 소프트웨어 획득 비용, 유지 비용, 구축비용 등 고려
  • 관계형 DBMS를 선정한 경우, 개념적 설계단계의 ER 도형을 릴레이션 스키마로 변환
  • 정규화 과정 수행
  • 개념적 설계의 트랜잭션 명세로부터 트랜잭션의 전체적 골격을 개발하고 인터페이스 정의
• 물리적 설계
  • 데이터베이스 파일에 대한 특정한 저장 구조와 접근 경로 결정
  • 물리 스키마를 설계
  • 레코드의 양식, 순서, 저장공간 등과 접근 경로를 위한 인덱싱, 클러스터링, 해싱 등의 설계가 포함
  • DBMS 뿐만 아니라 하드웨어 및 운영체제의 특성도 고려
  • 트랜잭션을 요청해서 응답을 얻기까지의 응답시간, 디비 파일과 접근 경로 구조에 대한 저장공간 효율성, 일정 단위 시간 동안 처리되는 트랜잭션의 평균 수인 트랜잭션 처리도 등 고려
  • 논리적 설계에서 기술한 트랜잭션의 인터페이스로부터 상세 트랜잭션을 정의
• 데이터베이스 구현
  • 데이터베이스를 실제로 구축하는 과정
  • 선정된 DBMS의 DDL로 기술된 명령문을 실행시켜 데이터베이스 스키마와 데이터베이스 파일을 생성.
  • 응용 프로그래머에 의해 상세 트랜잭션이 실행 트랜잭션으로 구현됨.
    • DML로 기술된 명령문을 가지는 프로그램 코드가 작성됨

ER(Entity Relationship) 모델

• ER 모델
  • 개념적 데이터 모델의 가장 대표적인것
  • 개체, 속성, 관계 등에 대해 용이하게 표현할수 있는 ER도형을 정의함
• 개체 및 개체 타입
  • 개체는 현실세계의 객체
  • 하나의 개체를 개체 어커런스 또는 개체 인스턴스 라고함.
  • 개체 어커런스들의 집합에 대한 공통의 특성들을 갖는 개체 클래스를 개체 타입이라 함
  • "교수"라는 개체안의 "이름" 이라는 개체 타입, "홍길동"이라는 개체 어커런스
• 관계 및 관계 타입
  • 2개 이상의 개체 사이에 존재하는 연관성. 관계 타입은 같은 관계들의 집합
  • 관계에 참여하는 개체 타입의 개수에 대한 차수(Degree)
  • 관계에 참여하는 개체 어커런스의 개수에 대한 대응 카디널리티를 갖는다.
  • 차수에 따른 관계의 종류
    • 단항 관계 : 관계에 참여하고있는 개체 타입이 1개
    • 이항, 삼항, n항 관계 등이 있다.
    • ISA 관계 : 특정 개체는 서로 구별되는 여러 하위 개체로 나뉘어 질수 있음. (클래스 상속개념)
      • disjoint : 특정 상위 개체는 하나의 하위 개체에만 포함됨. (사람 - 성인, 청소년)
      • overlapping : 여러개의 하위개체에 포함될수 있음 (교직원 - 교원, 직원)
      • total : 하위 객체에 무조건 속할수 있는 경우 (사람 - 성인, 청소년), 상위 개체와 ISA 삼각형 사이를 이중선으로 표시
      • partial : 하위 객체에 속하지 않을수도 있는 경우 (사원 - 개발팀, 기획팀)
• 속성
  • 개체의 특성이나 상태를 기술한것. 관계 역시 속성을 가질 수 있다.
  • 속성이 가질 수 있는 모든 가능한 값들의 집합 : 도메인(Domain)
  • 모든 개체 타입은 기본키(Primary Key)에 속하는 속성을 포함한다.
  • 단순 속성 : 더 이상 다른 속성으로 나눌 수 없는 속성 (나이), 원자성
  • 복합 속성 : 2개 이상의 속성들로 분해할 수 있는 속성 (주소 - 시, 군, 구, 동)
• 요구사항을 이용하여 ERD 만들기
  • ERD (Entity Relationship Diagram) : 개체 관계도
  • 요구 조건 분석의 결과로 나온 요구 조건 명세를 기본으로 ERD를 만든다.

2장 관계형 데이터베이스와 관계 연산

관계형 데이터 모델

데이터를 테이블 또는 릴레이션의 구조로 표현하는 논리적 데이터 모델

• 관계형 데이터 구조 : 릴레이션
  • 데이터를 원자 값으로 갖는 이차원의 테이블로 표현하는데, 이를 릴레이션이라 함.
  • 논리적 구조이므로 다양한 정렬 기준을 통해 릴레이션을 표현
  • 구조를 나타내는 릴레이션 스키마(릴레이션 내포), 실제 값들인 릴레이션 인스턴스(릴레이션, 릴레이션 외포)로 구성
  • 열은 속성(Attribute, 컬럼, 필드)이고 그 수는 Degree/차수, 행은 튜플(row)이고 그 수는 카디널리티/기수
  • 도메인(속성이 가질수있는 모든 가능한 값)은 그 값의 합법 여부를 시스템이 검사하는데에도 이용
• ER 모델을 관계형 데이터 모델로 변환
  • ER 모델을 논리적 데이터 모델인 릴레이션 스키마로 변환하는 것. 매핑 룰이라고도 함
  • ER도형에서의 개체와 관계는 개체 릴레이션, 관계 릴레이션으로 변환
  • 속성은 컬럼, 식별자는 기본키, 릴레이션 간의 관계는 기본키와 외래키를 이용함
  • 카디널리티에 따라 관계형 모델의 관계가 정해진다.

키의 개념 및 종류

키란 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 기준이 되는 속성

• 슈퍼키
  • 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키
  • 유일성은 만족하나 최소성은 만족하지 못함
  • 슈퍼키는 최소성과 관계없이 튜플을 유일하게 구분할 수 있으면 됨
  • 슈퍼키 중에서 유일성과 최소성을 만족하면 후보키가 됨
• 후보키
  • 튜플을 유일하게 식별하기 위해 사용되는 속성들의 부분집합
  • 유일성과 최소성 만족
• 기본키
  • 후보키 중에서 특별히 선전된 키
  • NULL값을 가질 수 없음
• 대체키
  • 후보키 중 기본키를 제외한 나머지
• 외래키
  • 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합
  • 릴레이션 간의 관계를 표현할때 사용
• 복합키
  • 2개 이상의 필드를 조합하여 만든 키

무결성

저장된 데이터베이스에 잘못된 데이터가 없다는 것

• 개념
  • 데이터베이스에 저장된 데이터 값과 그것이 표현하는 현실 세계의 실제값이 일치하는 정확성
  • 무결성 제약 조건 : 정확하지 않은 데이터가 디비 내에 저장되는 것을 방지하기 위한 제약조건
• 종류
  • 개체 무결성 : 기본키를 구성하는 속성은 NULL일수 없다
  • 참조 무결성 : NULL이거나 참조 릴레이션의 기본키값과 동일해야 함
  • 도메인 무결성 : 특정 속성의 값이 그 속성이 정의된 도메인에 속한 값이어야 하는 규정
  • NULL 무결성 : 릴레이션의 특정 속성 값이 NULL이 될 수 없도록 하는 규정
  • 고유 무결성 : 각 튜플이 갖는 속성값들이 서로 달라야 한다는 규정
  • 키 무결성 : 하나의 릴레이션에는 적어도 하나의 키가 존재해야 한다는 규정
  • 관계 무결성 : 어느 한 튜플의 삽입 가능 여부 또는 다른 릴레이션의 튜플들 사이의 관계에 대한 적절성 여부

관계대수

• 개념
  • 관계형 데이터베이스에서 원하는 정보와 그 정보를 어떻게 유도하는가를 기술하는 절차적 언어
  • 기본 연산 : select, project, join, division 등
  • 집합 연산 : union, difference, interesection, cartesian product 등
• 순수 관계 연산자
  • select : o (시그마)
    • 튜플들 중 특정 조건을 만족하는 튜플들의 부분집합을 구하여 새로운 릴레이션을 만든다
    • 행에 해당하는 튜플을 구하는 것. 수평연산
    • 시그마
  • project : ㅠ(파이)
    • 릴레이션에서 속성 리스트에 제시된 속성 값만을 추출
    • 열에 해당하는 속성을 추출하는 것. 수직연산
    • 파이
  • join : l><l
    • 공통속성을 중심으로 2개의 릴레이션을 하나로 합쳐 새로운 릴레이션을 만든다
    • 연산 결과로 만들어진 차수는 조인된 두 릴레이션의 차수를 합한 것과 같다
    • cartesian product 연산을 수행한 다음 select 연산을 수행한 것과 같다
    • 자연조인 : 중복된 속성을 제거하여 같은 속성은 1번만 나타나게 하는 연산
  • division : /(나누기)
    • 두 릴레이션에 대해 공통 속성에 해당하는 튜플을 추출함
    • 연산된 조건의 속성은 출력시 제외가 된다
• 일반 집합 연산자
  • 수학적 집합 이론에서 사용하는 연산자로서 릴레이션에도 적용 가능
  • 집합 연산을 처리하기 위해 합병 조건을 만족해야함
  • 합병조건 : 두 릴레이션 간에 속성의 수가 같고, 대응되는 속성별로 도메인이 같아야 함. 속성의 이름이 같아야 되는 것은 아님
  • UNION
    • 합집합 : U
    • 결과로 생성된 릴레이션에서 중복되는 튜플은 제거됨
    • 카디널리티 : 두 릴레이션 카디널리티의 합보다 작거나 같다
  • INTERSECTION
    • 교집합 : n
    • 카디널리티 : 두 릴레이션 중 카디널리티가 적은 릴레이션의 카디널리티보다 작거나 같다.
  • DIFFERENCE
    • 차집합 : -
    • 카디널리티 : R-S 라고 했을때 릴레이션 R의 카디널리티보다 작거나 같다.
  • CARTESIAN PRODUCT
    • 교차곱 : x
    • 두 릴레이션에 존재하는 튜플들의 결합된 정보를 구함
    • 카디널리티 : 두 릴레이션 카디널리티를 곱한것과 같다
• 관계 해석
  • 관계 대수와 구분하자
  • 관계 데이터의 연산을 표현하는 방법
  • 원하는 정보를 정의할때는 계산 수식을 사용
  • 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특성
  • 튜플 관계해석, 도메인 관계해석
  • 관계해석, 관계대수 모두 관계 데이터베이스를 처리하는 기능과 능력 면에서 동등
  • 관계대수로 표현한 식은 관계해석으로 표현
  • 질의어로 표현

3장 SQL

SQL - DDL

• DDL(Data Definition Language)의 개념

  • 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할때 사용
  • DDL로 정의된 내용은 메타데이터가 되며, 시스템 카탈로그에 저장한다
  • create, alter, drop

• create schema

  • 스키마는 하나의 응용(사용자)에 속하는 테이블과 기타 구성요소 등을 그룹짓기 위한 것
  • 스키마의 소유권자나 허가권자를 정의함
  • create schema 대학교 authorization 홍길동;

• create domain

  • 임의의 속성에서 취할 수 있는 값의 범위가 전체 타입의 값이 아니고 일부분일때, 사용자는 그 값의 범위를 도메인으로 정의 할수있다.

    create domain SEX char(1)
    default '남'
    constraint VALID-SEX check (VALUE IN('남','여'));

• create table

      create table <테이블명>
  
    (속성명 데이터타입 [NOT NULL], ...
  
    [, PRIMARY KEY (기본키속성명, ...)]
  
    [, UNIQUE (대체키속성명, ...)]
  
    [, FOREIGN KEY (외래키속성명, ...) REFERENCES 참조테이블(기본키속성명, ...)]
  
        [ON DELETE 옵션]  // 옵션 : NO ACTION, CASCADE, SET NULL, SET DEFAULT
  
        [ON UPDATE 옵션]
  
    [, CONSTRAINT 제약조건명] [CHECK (조건식)]);

• create view

  • 하나 이상의 기본 테이블로부터 유도되는 가상 테이블

      create view 뷰명[(속성명[, 속성명, ...])]
  
    as select문;

  • select 문을 서브 쿼리로 사용하여 select문의 결과로서 뷰를 생성
  • 서브쿼리인 select 문에는 union 이나 order by 절을 사용할 수 없다
  • 속성명을 기술하지 않으면 select문의 속성명이 자동으로 사용됨

• create index

  • 인덱스는 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조

      create [unique] index <인덱스 명>
  
        on 테이블명({속성명 [ASC | DESC] [, 속성명 [ASC | DESC]]})
  
        [CLUSTER];

  • unique : 중복값이 있거나 없는 속성으로 인덱스를 생성
  • ASC : 오름차순 (디폴트)
  • CLUSTER : 지정된 키에 따라 튜플들을 그룹으로 지정하기 위해 사용

• create trigger

  • 트리거 : 입력, 갱신, 삭제 등의 이벤트가 발생할때마다 자동적으로 수행되는 사용자 정의 프로시저
  • SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현
  • 테이블의 데이터를 일치시킬 때 주로 사용

    create trigger 트리거명 [동작시기 옵션] [동작 옵션]  ON 테이블명
  
    referencing [NEW | OLD] table as 테이블명
  
    for each row
  
    when 조건식
  
    트리거 body

  • 동작시기 옵션 : 트리거가 실행될 때를 지정.
    • after : 테이블 변경된 후 실행
    • before : 테이블 변경되기 전 실행
  • 동작 옵션 : 트리거가 실행되게 할 작업의종류
    • insert : 레코드 삽입시 실행
    • delete : 레코드 삭제시 실행
    • update : 레코드 수정시 실행
  • 테이블 선택옵션 : 트리거가 적용될 테이블의 종류
    • new : 새로 추가되거나 변경에 참여할 튜플들의 집합(테이플)에 트리거 적용
    • old : 변경된 튜플들의 집합(테이블)에 트리거 적용
  • when : 트리거가 실행되면서 지켜야할 조건
  • 트리거 body : 트리거의 본문
    • begin으로 시작, end로 끝
    • 하나 이상의 sql문이 있어야함
    • 변수에 값을 치환할 때 set을 사용3

• ALTER

  • drop
    • alter table 테이블명 drop column 컬럼명
  • modify
    • alter table 테이블명 modify column 컬럼명 varchar(15) not null;
  • rename
    • alter table 테이블명 rename column 컬럼명 to 변경컬럼명
  • add
    • alter table 테이블명 add 컬럼명 char(15)
    • 제약조건(constraint)도 추가 가능

SQL - SELECT

• 일반 형식

  select [predicate] [테이블 명.]속성명 [ as 별칭] [,속성명...]
  from 테이블명
  where 조건
  group by 속성명...
  having 그룹조건..
  order by 속성명 [asc|desc];

  • predicate 옵션 : all(디폴트), distinct(중복제거), distinctrow(선택된 속성이아닌 전체를 대상), trim(좌우공백제거)

  • 그룹 함수 : count, max, min, sum, avg(속성명)

    • group by로 그룹을 묶었으면 일반 속성을 함께 사용 못한다

  • LIKE : %(모든문자), _(하나의 문자), #(하나의 숫자)

    • where 이름 like '김%' : '김'으로 시작하는 모든 이름

  • 하위 질의 (취미가 나이트댄스) subquery

    select 이름, 주소
    from 사원
    where 이름 in (select 이름 from 여가활동 where 취미='나이트댄스');

  • 복수 테이블 검색 (경력이 10년 이상)

    select 사원.이름, 여가활동.취미
    from 사원, 여가활동
    where 여가활동.경력 >= 10 and 사원.이름 = 여가활동.이름

  • 통합 질의

    select *
    from 임원
    union select * from 사원;

• 기본 검색

SQL - JOIN

• JOIN의 개념

  • 2개의 테이블에 대해 연관된 튜플들을 결합하여 하나의 새로운 릴레이션을 리턴
  • 일반적으로 from에 기술하지만, 릴레이션이 사용되는 어느곳에서나 사용 가능

• inner 조인

  • equi join

    • join 대상 테이블에서 공통 속성을 기준으로 '='(equal)비교에 의해 같은 값을 가지는 행을 연결
    • 이 중 동일한 속성이 두번 나타나게 되는데, 중복제거하여 속성을 한번만 표기하는 법은 natural join
    • where 절 방법 (실무에서 가장 많이 사용)

    select 테이블.속성, 테이블1.속성
    from 테이블, 테이블1
    where 테이블.속성 = 테이블1.속성

    • natural join을 이용한 equi join 표기형식
    • 속성을 지정하지 않기때문에 두 테이블에 이름이 같고 도메인이 같은 속성이 반드시 존재해야함

    select 테이블.속성, 테이블1.속성
    from 테이블 NATURAL JOIN 테이블명2;

    • join ~ using 절을 이용한 equi join 표기형식

    select 테이블.속성, 테이블1.속성
    from 테이블 join 테이블1 using(속성명);

  • non-equi join(실무에서 별로 안씀)

    • '=' 조건이 아닌 나머지 비교 연산자를 쓰는 join 방법

    select 학번, 이름, 성적, 등급
    from 학생, 성적등급
    where 학생.성적 between 성적등급.최저 and 성적등급.최고;

• outer 조인

  • 릴레이션에서 join 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 방법

  • left outer join

    • inner join의 결과 후 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL값을 붙여 inner join 결과에 추가한다
    • inner join을 쓰면 좌측 릴레이션에 나오지않는 레코드가 있을수 있다.

    select 테이블.속성, 테이블2.속성
    from 테이블 left outer join 테이블2 on 테이블.속성 = 테이블2.속성;
    
    select 테이블.속성, 테이블2.속성
    from 테이블, 테이블2
    where 테이블.속성 = 테이블2.속성(+);

  • right outer join

    • 이번엔 오른쪽 릴레이션에 추가한다

  • full outer join

    • left + right outer join

    select 테이블.속성, 테이블2.속성
    from 테이블 full outer join 테이블2 on 테이블.속성 = 테이블2.속성;

• self 조인

  • 같은 테이블에서 2개의 속성을 연결하여 equi join을 하는것

  • select 별칭.속성, 별칭2.속성
    from 테이블 as 별칭 join 테이블 as 별칭2 on 별칭.속성 = 별칭2.속성;
    
    select 별칭.속성, 별칭2.속성
    from 테이블 as 별칭, 테이블 as 별칭2 
    where 별칭.속성 = 별칭2.속성;
    

SQL - DML

• DML 개념

  • 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
  • 사용자와 DBMS간의 인터페이스 제공

• INSERT

  insert into 테이블명 (속성1, 속성2) value (값1, 값2);

  • 속성과 값의 개수와 데이터 타입이 일치해야함
  • 모든 속성 삽입시 속성명 생략 가능. 단, 값을 create table문에 기술된 순서대로 지정해야함

  insert into 편집부원(이름, 생일, 주소, 기본급)
  select 이름, 생일, 주소, 기본급 from 사원 where 부서='편집';

  • select를 이용하여 insert 가능

• DELETE

  delete from 테이블 where 조건;

• UPDATE

  update 테이블명 set 속성=값 where 조건;

SQL - DCL

• DCL 개념

  • 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의
  • DBA의 데이터 관리 목적

• COMMIT / ROLLBACK

  • commit
    • 트랜잭션의 모든 변경 내용들을 영구적으로 데이터베이스에 반영하는 명령어
    • 트랜잭션이 끝나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 수행된 모든 변경을 데이터베이스에 반영하여 완료해야함
    • 트랜잭션
      • 하나의 트랜잭션은 commit 되거나 rollback 되어야 함.
      • 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로 작업의 단위
      • DBMS에서 회복 및 병행 제어시에 처리되는 작업의 논리적 단위
  • rollback
    • 변경된 모든 내용들을 취소하고 이전 상태로 되돌리는 명령어
    • 트랜잭션의 일부를 성공적으로 끝내지 못하면 디비의 비일관성 상태가 되기 때문에 성공된 일부 트랜잭션은 롤백되어야함

• GRANT / REVOKE

  DBA가 사용자에게 권한을 부여하고 취소하기 위한 명령어

  • GRANT : 권한 부여
  • REVOKE : 권한 취소

  grant 사용자등급 to 사용자_id_리스트[identified by 암호];
  revoke 사용자등급 from 사용자_id_리스트;

grant connect to star;

• 사용자등급
  • DBA : 데이터베이스 관리자
  • RESOURCE : 데이터베이스 및 테이블 생성 가능자
  • CONNECT : 단순 사용자
    • 테이블 및 속성에 대한 권한 부여 및 취소

  grant 권한_리스트 on 개체 to 사용자[with grant option];
  revoke [grant option for]권한_리스트 on 개체 from 사용자[cascade];

• 권한 종류 : all, select, insert, delete, update, alter 등
  • with grant option : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
  • grant option for : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소 (해당 권한 자체는 취소안됨)
  • cascade : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

뷰(VIEW)

• 뷰의 개념
  • 하나 이상의 기본 테이블에서 유도되는 가상 테이블
  • 물리적으로는 존재x, 사용자에게는 있는것처럼 보임
  • 생성시 뷰 정의가 시스템 내에 저장되었다가 질의시에 실행됨
• 특징
  • 필요한 데이터만을 뷰로 정의해서 처리할수 있기때문에 관리가 용이하고 명령문이 간단함
  • 뷰를 통해서 보여주기때문에 보여주고 싶지 않은 데이터를 보호
  • 기본키를 포함한 속성 집합으로 구성해야 삽입, 삭제, 갱신이 가능
  • 정의된 뷰는 다른 뷰의 정의에 기초가 될수 있음
• 뷰 연산 시의 제약성
  • 뷰의 열이 속성이 아닌 상수, 계산식, 그룹 함수를 사용했다면 변경 불가능
  • distinct, group by, having을 사용한것도 변경못함
  • 둘 이상의 테이블에서 유도된 뷰도 변경못함
  • 변경 못하는 뷰를 토대로 만든 뷰도 변경못함
• create view
  • as select 문에서 union이나 order by를 사용할수 없음
  • with check option : 마지막에 해당 구문을 넣으면 갱신이나 삽입 연산이 실행될때 뷰의 정의 조건을 위배하면 실행 거부
  • 속성명을 기술하지 않으면 select문의 속성명이 자동으로 뷰의 속성명이 됨
• drop view
  • drop view 뷰명 [restrict | cascade];
  • restrict : 뷰를 다른 곳에서 참조하고 있으면 제거가 취소
  • cascade : 뷰를 참조하는 다른 뷰나 제약조건까지 모두 삭제

내장 SQL

• 내장 SQL의 개념
  • 데이터베이스 내의 데이터를 정의하거나 접근하는 SQL문을 응용 프로그램 내에 삽입하여 프로그램이 실행될때 함께 실행되도록한 SQL이다.
• 특징
  • 실행결과가 여러개의 튜플이어도 맨 처음 튜플 하나만 반환
  • 일반 변수를 사용해 저장가능
  • 호스트 프로그램을 컴파일할때 선행 처리기에 의해 분리되어 컴파일 됨
  • 일반 변수와 데이터베이스 필드의 이름은 같아도 됨
  • 허나 데이터 타입은 일치해야함
  • 내장 sql문이 실행되면 sql 실행상태가 sql 상태 변수(SQLSTATE : 000000은 성공, 002000은 검색결과없음 등)에 전달
• 내장 SQL과 호스트 언어의 실행문 구별
  • 명령문 구분
    • C/C++ : EXEC SQL과 세미콜론 문자사이에 기술
    • JAVA : #SQL{<내장 SQL문>};
  • 변수의 구분
    • 내장 SQL문에서 사용하는 호스트 변수는 변수 앞에 콜론(:) 붙음
    • 호스트 언어 내에서는 콜론 없이 사용
• 커서
  • 실행결과로 반환된 복수 개의 튜플들을 접근할 수 있음
  • 반환된 튜플들을 순서대로 가리키는 튜플에 대한 포인터
  • 커서 관련 명령어
    • DECLARE : 커서 선언 및 정의
    • OPEN : 커서가 질의 결과의 첫 번째 튜플을 가리키도록 설정
    • FETCH : 다음 튜플로 커서를 이동
    • CLOSE : 커서 종료

스토어드 프로시저(Stored Procedure)

• 개념

  • 특정한 작업을 수행하는 SQL문을 논리적으로 그룹화한 것
  • 완전한 프로그램의 축소판
  • 연속된 SQL문을 하나로 모아 서버에 미리 컴파일해서 저장해 놓은 것
  • 클라이언트로부터 호출문을 통해 복잡한 SQL의 일괄 작업을 수행하는데 적합
  • 구성
    • 선언부 : 타입, 커서, 상수, 변수, 내포된 서브 프로그램을 선언
    • 실행부 : 코드 실행을 제어하고 데이터를 조작하는 문장을 작성
    • 예외처리부 : 실행중 생기는 예외를 처리

• 장점

  • 모듈별 프로그래밍 허용
    • 한번 만들어 디비에 저장한 후, 여러프로그램에서 계속 스토어드 프로시저를 호출해 사용가능
    • 디비 프로그래머가 만들며 원본 코드와 상관없이 수정할 수 있음
  • 빠른 SQL 실행 시간
    • 만들어질때 구문이 분석되고 최적화됨
    • 한번 실행된 후에는 메모리에 캐시되기 때문
  • 보안성 향상
    • 사용자가 스토어드 프로시저를 통해서만 데이터에 접근하게 제한할 수 있음
  • 네트워크 통신량 감소
    • 수백줄의 sql 코드를 하나로 묶어 놓았기 때문에

• 생성

  create [or replace] procedure 프로시저명(파라미터)
  [지역변수 선언]
  프로시저 body;

  • or replace : 동일한 프로시저 이름이 있다면, 기존 프로시저를 대체함
  • 파라미터
    • in : 호출되는 프로시저에 값을 전달하라는 설정
    • out : 호출 프로그램에게 반환값을 내라는 설정
    • inout : 값도 전달하고 반환도 하고
  • 프로시저 body
    • begin으로 시작해서 end로 끝남
    • 적어도 하나의 sql이 있어야함
    • 변수에 값을 치환할때 예약어 set을 사용

• 제거

  • drop procedure 프로시저명;

• 실행

  • excute 프로시저명;
  • exec 프로시저명;

4장 정규화

이상(Anomaly)

• 개념
  • 테이블에서 일부 속성들의 종속으로 인해 데이터의 중복이 발생
  • 이 중복으로 인해 테이블 조작시 문제가 발생하는 현상
• 삽입 이상
  • 데이터를 삽입할 때 의도와 상관없이 원하지 않은 값들로 인해 삽입할 수 없게 되는 현상
  • 예) 기본키를 넣지 않아 삽입 이상이 발생
• 삭제 이상
  • 한 튜플을 삭제할 때 의도와 상관없는 값들도 삭제되는 현상 (연쇄 삭제)
  • 예) 기본키에 들어간 정보를 삭제했더니 한 튜플자체가 삭제
• 갱신 이상
  • 한 튜플에 있는 속성을 갱신할때 일부 튜플의 정보만 갱신되어 불일치성이 생김

함수적 종속

• 개념

  • 어떤 테이블에 X와 Y라는 속성이 있다
  • X값에 대해 항상 속성 Y값이 연관되어 있을때 Y는 X에 함수적 종속 또는 X가 Y를 함수적으로 결정한다고 함
  • X -> Y 라고 표기
  • X -> Y 관계에서 X를 결정자, Y를 종속자라고 함
  • 학과 400번인 튜플의 이름, 학년,학과 속성은 '이순신',4,'컴퓨터공학과' 이고 다른값은 올수가없다.

• 함수적 종속 다이어그램

  • 학번, 과목번호가 기본키라고 가정

  • 학번,과목번호 -> 성적

    학번 -> 학년

  • 성적은 (학번, 과목번호)에 완전 함수적 종속 (기본키에 대해 완전히 종속적일 때)

  • 학년은 (학번, 과목번호)에 부분 함수적 종속 (학번에게만 종속적이므로)

  • 이행적 함수 종속 : 기본키가 아닌 속성 A에 대해 종속적이지만 A는 함수적 종속적일때

    • X -> Y, Y -> Z 일때 X -> Z가 성립

정규화

• 정규화의 개념
  • 테이블의 속성들이 상호 종속적인 관계를 갖는 특성을 이용하여 테이블을 무손실 분해 하는 과정
  • 목적 : 가능한 한 중복을 제거하여 삽입, 삭제, 갱신 이상의 발생 가능성을 줄이는 것
• 제 1정규형
  • 테이블에 속한 모든 속성의 도메인이 원자값(값이 하나)만으로 되어 있는 정규형
• 제 2정규형
  • 테이블이 제 1정규형이어야 함.
  • 기본키가 아닌 모든 속성이 기본키에 대하여 완전 함수적 종속을 만족하는 정규형
• 제 3정규형
  • 테이블이 제 2정규형이어야 함.
  • 기본키가 아닌 모든 속성이 기본키에 대해 이행적 함수적 종속을 만족하지 않는 정규형
• BCNF
  • 테이블에서 모든 결정자가 후보키인 정규형
• 제 4정규형
  • 테이블에 다중 값 종속 A ->> B가 존재할 경우 모든 속성이 A에 함수적 종속 관계를 만족하는 정규형
  • 다중값 종속
    • 필드 A하나의 값에 종속되는 필드 B가 여러 개의 값
• 제 5정규형
  • 테이블의 모든 조인 종속이 후보키를 통해서만 성립되는 정규형
  • 조인종속
    • 테이블 있는데 자신의 프로젝션을 모두 조인한 결과와 동일한 경우 조인종속을 만족한다고 함
• 비정규화 (역정규화)
  • 정규화를 해놨는데 지나치게 조인하면 응답 속도가 떨어지므로 정규화에는 위배되지만 성능 향상을 위해 다시 테이블을 합치는것
• 도부이결다조

5장 데이터베이스 기타 실무 응용

시스템 카탈로그

• 개념
  • DBA의 도구
  • 모든 데이터 개체들에 대한 정의나 명세에 대한 정보가 수록
  • 데이터 사전
  • DDL의 결과로 구성되는 릴레이션, 뷰, 인덱스, 사용자, 접근 권한 등 정보 저장
  • 저장된 내용을 메타데이터 라고함
  • 사용자와 DBMS의 접근이 가능
• 시스템 카탈로그의 내용
  • 릴레이션 관련 정보
    • 이름, 저장파일이름 / 파일구조, 속성 이름과 타입, 인덱스 이름, 무결성 제약조건
  • 인덱스 관련 정보
    • 이름, 구조, 키에 대한 정보
  • 뷰 관련 정보
    • 이름, 정의, 소유자
  • 통계 관련 정보
    • 릴레이션 카디널리티 : 릴레이션에 저장된 레코드 수
    • 인덱스 카디널리티 : 인덱스에 저장된 레코드 수
    • 인덱스의 높이 : 트리 인덱스에 대한 레벨
    • 인덱스의 범위 : 인덱스에 대한 최소 키값과 최대 키값
  • 사용자 관련 정보
    • 계정정보, 권한정보
• 특징
  • 테이블로 이루어져 있어 SQL문을 이용하여 검색가능
  • DBMS에 의해 생성되고 유지 됨
  • SQL문을 실행시켜 테이블, 뷰, 인덱스 등을 변경하는데 DBMS가 자동으로 갱신함.
    • 사용자가 직접 갱신하는 것은 허용하지 않음
  • 분산 시스템에서는 위치 투명성 및 중복 투명성을 제공하기 위해 필요한 제어 정보도 포함
    • 위치 투명성 : 데이터베이스의 실제 위치를 알지 못해도 논리적인 명칭만으로 접근가능한 특성
    • 중복 투명성 : 동일 데이터가 여러곳에 중복되어 있더라도 마치 하나인냥 사용함
• 구성 요소
  • SYSOBJECTS
    • 릴레이션, 뷰, 제약조건, 규칙, 저장 프로시저 등 데이터베이스에서 만들어진 각 개체에 관한 정보를 한 행으로 관리
    • 개체명, 소유자, 개체종류, 생성일 등으로 구성
  • SYSCOLUMNS
    • 모든 테이블에 대해 모든 속성에 대한 정보를 한 행으로 관리
    • 속성명, 테이블명, 소유자, 속성타입, 자릿수 등으로 구성
  • SYSINDEXES
    • 모든 인덱스에 대한 정보를 한 행으로 관리
    • 인덱스명, 테이블명, 속성수, 튜플수 등으로 구성
  • SYSVIEWS
    • 모든 뷰에 대한 정보를 한 행으로 관리
    • 뷰명, 소유자, 뷰명령문 등으로 구성
  • SYSUSERS
    • 사용자와 그룹에 관한 정보를 한행으로 관리
    • 사용자ID, 사용자명, 그룹명, 생성일 등으로 구성
  • SYSPROTECTS
    • 사용자 권한에 관한 정보를 한 행으로 관리
    • 개체명, 사용자ID, 권한 보호타입 등으로 구성

인데스

• 개념
  • 데이터 레코드(튜플)에 빠르게 접근하기 위해 <키, 주소> 쌍으로 구성되는 데이터 구조
  • 인덱스가 없다면 full table scan을 해야함
  • 기본키를 위한 인덱스 : 기본 인덱스
  • 기본 인덱스가 아닌 인덱스 : 보조 인덱스
  • 대부분 관계형 DBMS에서는 모든 기본키에 대해 자동적으로 기본 인덱스를 생성
  • 클러스터드 인덱스 : 레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게 유지되도록 구성되는 인덱스
  • 대표적으로 m-원 검색 트리, B-트리, B*-트리, B+-트리 등
  • 행 이주(Row Migration)으로 인한 검색 효율 저하
    • varchar 형으로 입력했다가 수정할때 해당 길이가 변하면서 주소가 바뀔수있다.
    • DBMS는 이주 전 주소로 갔다가 이주 후 주소로 감. 즉, 검색효율의 저하가 일어남
    • char형으로 바꾸든지, 테이블 생성시 블록 크기와 관련된 파라미터를 적절히 쓰자
• m-원 검색 트리
  • 한 노드가 1개의 키 값과 2개의 서브 노드를 갖는 이진 검색 트리를 일반화 한 트리
  • 한 노드가 최대 m-1개의 키값과 최대 m개의 서브노드를 가짐
  • 이진 검색 트리에 비해 분기율이 향상되므로 트리 깊이가 낮아져 특정 노드에 대한 검색시간이 감소
  • 키 삽입이나 삭제시 트리의 균형을 유지하기 위해 복잡한 연산이 수행되는 단점
  • 분기율 : 한 노드에서 서브 트리로 가는 포인터의 개수
  • 구조
    • m, P, K, A ... P, K, A, Pm
    • m : 서브노드수
    • P :서브 노드에 대한 포인터
    • K : 키값(인덱스 지정키)
    • A : 레코드 주소
  • 예시
    • 학번이 24번인 녀석을 찾는다
    • 루트노드에서 첫번째 키값과 비교. 현재 키값이 24보다 작으면 왼쪽, 크면 오른쪽 서브노드로 접근
    • 이동한 서브노드의 처음부터 키값을 비교한다.
• B-트리
  • 인덱스를 구성하는 방법으로 많이 사용되는 균형된 m-원 검색 트리
  • 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장
  • 키의 삽입과 삭제시 노드의 분열과 합병이 발생할 수 있음
  • 특징
    • 모든 노드는 최대 m개의 서브 노드를 가짐
    • 루트 노드와 단말 노드를 제외한 모든 노드는 최소 m/2개, 최대 m개의 서브노드를 가짐
    • 루트 노드는 단말 노드가 아닌 이상 적어도 2개의 서브 노드를 가짐
    • 단말 노드가 아닌 노드에 있는 키 값의 수는 그 노드의 서브 노드 수 보다 하나 적음
    • 모든 단말 노드는 같은 레벨에 있음
    • 한 노드안에 있는 키 값들은 오름차순을 유지
• B*-트리
  • B-트리의 문제점인 빈번한 노드의 분할을 줄이는 목적
  • 각 노드가 가능한 최소 2/3이 채워지도록 한 것이 특징
  • 특징
    • 루트 노드를 제외한 모든 노드는 최소 2m-2/3 개, 최대 m개의 서브 노드를 가짐
• B+-트리
  • B-트리의 변형으로 단말 노드가 아닌 노드로 구성된 인덱스 세트와 단말 노드로만 구성된 순차 세트로 구분
  • 인덱스 세트 노드 : 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공 (직접 접근)
  • 순차 세트의 단말 노드 : 해당 데이터 레코드의 주소를 가리킴 (순차 접근)
  • 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드만을 이용한 순차처리 가능

트랜잭션

• 정의

  • 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로서 작업의 단위
  • DBMS에서 회복 및 병행 수행시 처리되는 작업의 논리적 단위
  • 정상적 종료 : COMMIT 연산 수행
  • 비정상적 종료 : ROLLBACK 연산 수행

• 특성(ACID)

  데이터의 무결성을 보장하기 위하여 DBMS의 트랜잭션이 갖추어야할 특성

  • 원자성 : 모두 반영되든지 아니면 전혀 반영되지 않든지
  • 일관성 : 수행전, 수행후 상태가 같아야 함
  • 독립성 : 수행되는 동안 다른 트랜잭션들이 접근하지 못해야 함
  • 영속성 : 실행 후 시스템에 오류가 나더라도 트랜잭션에 의해 변경된 내용은 보존되어야함

• 상태

  • 활동 : 실행중
  • 실패 : 실행중 오류가 발생하여 중단
  • 철회 : 비정상적 종료되어 ROLLBACK 연산 수행
  • 부분완료 : 마지막 연산까지 수행했지만 COMMIT 연산을 실행하기 전
  • 완료 : COMMIT 연산 수행

회복

• 개념
  • 트랜잭션들을 실행하는 도중 장애가 발생하여 디비가 손상되었을 경우 이전 상태로 복구하는 작업
• 장애의 유형
  • 실행 장애
    • 사용자의 실수, 무결성 위반 등으로 질의 실행이 실패
  • 트랜잭션 장애
    • 트랜잭션 내부에서 입력 데이터 오류, 불명확 데이터, 시스템 자원 요구의 과다 등으로의 트랜잭션 중지
  • 시스템 장애
    • 디비에 손상을 입히지 않으나 하드웨어 오동작, 정전, 소프트웨어 오류, 교착상태 등 트랜잭션이 실행 불가
  • 미디어 장애
    • 저장 장치인 디스크 블록의 손상이나 디스크 헤드의 충돌 등에 의해 디비가 물리적으로 손상
• 회복 관리기
  • 로그, 메모리 덤프 등을 이용하여 회복 기능을 수행하는 DBMS의 핵심 구성요소
  • 트랜잭션 실행이 실패하면 로그를 이용하여 모든 변화를 취소(Undo)시킴
  • 트랜잭션 실행이 성공적이나 디스크에는 반영되지 않았을 경우 회복시 로그를 이용하여 재작업(Redo) 수행
  • Redo : commit이 있는 트랜잭션만 재작업
  • Undo : commit이 없는 트랜잭션만 취소
• 회복 기법
  • 연기 갱신
    • 트랜잭션이 성공적으로 종료될 때까지 디비에 대한 실질적인 갱신을 연기
    • 로그에 내용을 기록하고 부분완료시점에 디비에 반영
    • 부분완료 시점 전에 rollback 하게 되더라도 디비엔 영향 없음
    • 회복 작업은 Redo만 수행
  • 즉각 갱신
    • 트랜잭션이 부분 완료되기전이라도 즉시 실제 데이터베이스에 반영
    • 변경된 모든 내용들은 로그에 보관
    • Redo, Undo 모두 수행
  • 그림자 페이지
    • 갱신 이전의 데이터베이스를 일정 크기의 페이지 단위로 구성하여 각 페이지마다 복사본인 그림자 페이지로 별도 보관해두고, 실제 페이지를 대상으로 트랜잭션에 대한 변경 작업을 수행
    • rollback시킬땐 변경 이후시점 실제 페이지 부분을 복사해둔 페이지로 대체함
    • Redo, Undo가 필요없음
  • 검사점
    • Redo, Undo를 위한 로그 전체를 조사해야하는 경우를 피하기 위한기법
    • 트랜잭션 실행중 주기적으로 변경 내용이나 시스템 상황 등 정보와 함께 검사점을 로그에 보관
    • 장애시 로그 전체가 아닌 가장 최근의 검사점으로부터 회복작업. 시간 단축
  • 미디어 회복
    • 디비 내용을 주기적으로 안전한 저장 장치에 덤프해 놓음
    • 장애시 로그에서 가장 최근 덤프 이후 완료된 트랜잭션에 대해 수행
    • Redo 수행

병행 제어

• 개념

  • 동시에 여러 개의 트랜잭션을 병행 수행할 때 트랜잭션들이 디비의 일관성을 파괴하지 않도록 트랜잭션 간의 상호작용을 제어하는 기술
  • 병행성이란 여러 개의 트랜잭션들이 동시에 인터리빙하게 실행되는 것을 의미
  • 인터리빙 : 트랜잭션들이 번갈아가며 조금씩 자기할일을 처리하는것

• 목적

  • 디비의 공유를 최대화
  • 시스템 활용도 최대화
  • 사용자 응답시간 최소화
  • 단위 시간당 트랜잭션 처리 건수 최대화
  • 디비의 일관성 유지

• 필요성

  병행제어가 없다면 생기는 문제점

  • 갱신 분실
    • 2개 이상의 트랜잭션이 같은 데이터를 공유하여 갱신할 때 갱신 결과의 일부가 없어짐
  • 모순성
    • 복수의 사용자가 동시에 같은 데이터를 갱신할 때 디비 내의 데이터들이 상호 일치하지 않음
    • 불일치 분석
  • 연쇄 복귀
    • 병행 수행되던 트랜잭션 중 하나가 rollback 되면 다른 애들도 같이 rollback 됨

• 잠금

  • 하나의 트랜잭션에서 사용되는 데이터를 다른 트랜잭션이 접근하지 못하게 하는것
  • 트랜잭션들은 데이터에 접근하기 전에 잠금을 요청하여 잠금을 허락받아야 함
  • 데이터 갱신시 잠금 > 실행 > 해제의 규칙을 따라야 함
  • 한번에 잠금할 수 있는 단위 : 데이터베이스, 테이블, 레코드, 필드 등 (공유성을 위해 레코드 많이씀)
  • 교착 상태
    • 한 트랜잭션이 좀있다 쓸라고 잠가 놓은 자원을 다른 트랜잭션이 쓰기 위해 기다리고 있는 상태
    • 작업 단위로 틈틈히 잠금 해제를 해주자
  • 공유 잠금 : 공유 잠금 서로간에 접근을 허용(읽기만 가능)
  • 배타 잠금 : 배타 잠금을 걸기 위해서 데이터에 걸린 여러개의 공유잠금이 모두 해제되어야 함(읽기, 기록 모두 불가)

• 기법의 종류

  • 2단계 잠금 규약
    • 트랜잭션 스케줄의 직렬성을 보장하는 대표적 기법
    • 단계
      • 확장단계 : 트랜잭션이 잠금만 수행할 수 있고 잠금 해제는 수행못함
      • 축소단계 : 트랜잭션이 해제만 할수있고 잠금은 수행 못함
    • 교착상태는 예방 못함
  • 타임 스탬프
    • 트랜잭션이 시스템으로 들어오면 타임스탬프 생성
    • 스템프 순서대로 실행하는 직렬 스케쥴의 실행 결과와 항상 동일하다는 것을 보장
    • 직렬성 순서를 결정하기 위해 실행 순서를 미리 정하는 가장 보편적 방법
    • 교착상태가 발생안함

보안

• 개념
  • 디비 일부분 또는 전체에 대하여 권한이 없는 사용자가 접근 수행을 금지하기 위해 사용
  • 불법적 데이터 폭로, 변경 또는 파괴로부터 보호
• 보안기술의 목표
  • 정보 보호 : 정보의 불법적 노출 방지
  • 정보 인증 : 고의적 정보 수정, 허위 정보 저장을 방지
  • 사용자 인증 : 패스워드, 음성, 지문을 이용해 신원 확인
• 기법
  • SQL을 이용한 권한 부여 기법
    • 뷰 기법 : 비공개 데이터 값은 그열을 제외한 뷰를 생성하여 보여줌
  • DCL을 이용한 기법
    • DBA가 grant/revoke 명령어로 권한을 부여 및 해제
  • 개인키 암호화 기법 (대칭 암호 or 단일키 암호화 기법)
    • 동일한 키로 데이터 암호화 및 복호화
    • 전위 기법, 대체 기법, 대수 기법, 합성 기법, DES
    • 장점 : 암복호화 속도빠름, 알고리즘 단순, 파일의 크기가 공개키 암호 기법보다 작음
    • 단점 : 사용자의 증가에 따라 관리할 키의 수가 많아짐
  • 공개키 암호화 기법(비대칭 암호, RSA)
    • 암호화 할때 사용하는 공개키는 공개하고, 복호화할때 비밀키는 관리자가 관리
    • 비밀키는 사용 권한이 있는 사용자만 나눠 가짐
    • 장점 : 키 분배 용이, 관리 키 개수 적음
    • 단점 : 느리다, 복잡하다, 파일 크기가 크다

튜닝

• 개요
  • 응용 프로그램, 데이터베이스, 운영체제 등을 조정하여 DBMS의 성능 향상시키는 작업
  • DBMS가 높은 작업 처리량과 짧은 응답 시간을 갖도록 하는게 중요
• 목표
  • SQL 실행시 디스크 블록에 대한 접근 횟수 최소화
    • 원하는 데이터가 포함된 디스크를 적절히 분산시켜 디스크 입출력이 집중되는걸 막는다
  • 디스크 블록에서 읽은 데이터는 가능한 메모리 영역에 보관하여 신속히 가져오게한다
    • 캐시용량을 충분히 확보
  • SQL문은 공유가 가능하도록 대소문자 일치 등의 이름 작성 규칙을 준수하여 작성
  • 여러 사용자가 접근할 수 있도록 잠금 기능 사용을 최소화
    • 잠금이 최소한으로 발생하도록 트랜잭션을 분산시킴
• 튜닝의 단계
  • 비즈니스 규칙 튜닝
    • DBA가 직면한 성능상의 문제들은 대부분 부정확한 분석이나 부적절 비즈니스 규칙으로 인해 발생
    • 많은 사용자들이 동시접속하는 현실적인 환경을 고려해야 함
  • 데이터 설계 튜닝
    • 응용 프로그램에서 필요한 데이터가 무엇인지 정확히 파악
    • 데이터 간의 관계와 속성을 파악후 정보의 구조화 작업을 수행
  • 응용프로그램 설계 튜닝
    • 프로세스의 성능을 조사하여 부하가 발생하는 시점에서 실행에 필요한 시간 조사
    • 튜닝 대상 응용프로그램 선정과 집중 튜닝
  • 데이터베이스의 논리적 구조 튜닝
    • 부하가 예상되는 질의와 갱신을 고려하여 스키마를 작성
  • 데이터베이스 접근 방식
    • DBMS 기능 활용
  • 접근 경로
    • 인덱스 고려
    • 튜닝 방법이 쉽고 효율이 좋음
  • 메모리 관리
    • 캐시 성능 개선, sql문의 파싱 작업 감소
  • 물리적 구조 및 입출력
    • 효율적 데이터 블록 운영
    • 디스크 간에 데이터를 분산하여 입출력 경합 감소
  • 자원의 경합
    • 블록, 공유 풀, 잠금 등의 경합 형태를 감소시키기 위한 노력
  • H/W 시스템에 특화된 부분
• 인덱스 튜닝 (접근 경로 튜닝)
  • B-트리 인덱스
    • 일반적으로 사용하는 방식
    • 데이터양에 상관없이 모든 데이터의 인덱스 탐색시간이 동일
  • 비트맵 인덱스
    • 인덱스 컬럼의 데이터를 bit 값인 0 또는 1로 변환하여 키로 사용하는 방법
    • 분포도가 좋은 컬럼에 적합, 효율적인 논리 연산 가능, 저장공간이 작음
  • 역방향 인덱스
    • 인덱스 컬럼의 데이터를 역으로 변환하여 인덱스 키로 사용하는 방법
    • B-트리에서의 불균형 문제를 해결 (검색 경로가 한쪽방향으로만 수행되는 경우)
    • 데이터의 분포도(선택성)가 좋아져 검색 성능이 좋음
  • 종류
    • 클러스터드 인덱스
      • 인덱스 키순으로 데이터가 정렬되어 저장되는 방식
      • 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾음
      • 삽입, 삭제시 데이터 재정렬
      • 한개의 릴레이션에 하나의 인덱스만 생성가능
    • 넌클러스터드 인덱스
      • 인덱스 키값만 정렬
      • 데이터 검색하려면 먼저 인덱스를 검색해야됨(속도 떨어짐)
      • 여러개의 인덱스 만들수 있음
  • 인덱스 선정 기준
    • 분포도가 좋은 컬럼은 단독으로 인덱스를 생성한다
    • 데이터의 변경이 빈번하지 않은 컬럼에 인덱스를 생성
    • 인덱스들이 자주 조합되어 사용되는 경우 하나의 결합 인덱스 생성
    • 결합 인덱스의 컬럼 순서는 분포도가 낮은 컬럼을 선행컬럼으로 하자
      • 선행 컬럼은 where절에 항상 '='로 비교되어야 함
  • 인덱스 사용 못하는 경우
    • NULL값은 인덱스 대상에 포함이 안됨.
    • 부정 연산자는 다수의 데이터를 검색하므로 인덱스를 이용하지 못함
      • is not null 대신 > '' 을 사용하자
    • LIKE '%A' 같은 경우도 그러함
    • 인덱스 컬럼에 함수나 수식을 사용하면 인덱스 키값이 변형되어 사용못함

객체 지향 인터페이스

• 개념

  • 객체 지향 프로그래밍 언어에서 영향을 받아 생긴것
  • 실세계 존재하는 객체를 표현하고 관리하는 데이터베이스 기술
  • DBMS로는 ODMG2.0 표준이 있음

• 구성

  • 객체와 객체 식별자
    • 객체 : 실세계에 존재하는 개체를 추상적으로 표현한것
    • 객체 식별자 : 많은 수의 객체들을 식별하기 위해 시스템에 의해 생성되는 값. 변경불가
  • 속성과 메소드
    • 속성 : 객체를 기술하는데 사용. 유일한 이름과 데이터 타입을 갖음
    • 메소드 : 객체에 수행될 연산. 객체의 속성값에 대한 변경이나 검색을 기술. 메시지를 보내어 실행함
  • 클래스와 인스턴스
    • 알고있는 대로
  • 상속
    • ISA 관계 : 서브 클래스와 슈퍼클래스간의 관계
  • 오버로딩과 오버라이딩
    • 알고있는 대로

• 객체 관계 데이터베이스

  관계형 데이터베이스 + 객체 지향 데이터베이스

  • 개념
    • 관계형 데이터베이스는 정형화된 데이터 타입을 필요로함 (크기를 미리 정해놓은 데이터타입)
    • 비정형화된 데이터들의 등장으로 저장하기 어려운 단점이 있음
    • 객체 관계 데이터베이스 관리 시스템(ORDBMS)는 이런 문제를 해결
  • 특징
    • 데이터 언어 표준 : ISO에서 제시한 SQL3
    • 확장 가능 데이터 타입, 사용자 정의 데이터 타입, 사용자 정의 함수, 사용자 정의 프로시저, 사용자 정의 연산자, 대형객체 타입, 상속 개념 등을 지원

고급 데이터베이스

• 분산 데이터베이스

  • 구성요소
    • 분산 처리기 : 자체적으로 처리능력을 가지며 지리적으로 분산되어 있는 컴퓨터 시스템
    • 분산 데이터베이스 : 분산되 있지만 해당 지역의 특성에 맞게 데이터베이스가 구성됨
    • 통신 네트워크 : 분산 처리기들을 통신마응로 연결하여 논리적으로 하나의 시스템처럼 작동하도록 하는 네트워크
  • 목표
    • 위치 투명성 : 실제 위치를 알 필요없이 논리적인 명칭만으로 접근
    • 중복 투명성 : 동일 데이터가 여러곳에 중복이더라도 사용자는 마치 하나인냥 사용
    • 병행 투명성 : 다수의 트랜잭션들이 동시 실행되더라도 그 트랜잭션의 결과는 영향을 안받음
    • 장애 투명성 : 트랜잭션, DBMS, 네트워크, 컴퓨터 장애에도 트랜잭션은 정확히 수행
  • 장점
    • 지역 자치성
    • 데이터 공유성
    • 분산 제어 가능
    • 성능 향상
    • 효율성 및 융통성
    • 신뢰성 및 가용성
    • 점증적 용량 확장이 용이
  • 단점
    • DBMS가 수행할 기능이 복잡
    • 설계의 어려움
    • 개발 비용
    • 처리 비용
    • 잠재적 오류

• 멀티미디어 데이터베이스

  텍스트, 그래픽, 정지 화상, 동영상, 음성 등이 복합적으로 구성된 데이터베이스

  • 특성
    • 데이터가 대용량
    • 비정형화된 데이터 이므로 미디어별 검색방법이 필요
    • 데이터의 구조가 복잡하고 관계를 구성하기 어려움
  • 구축 방법
    • 파일 기반 기법
      • DBMS를 사용하지 않고 단순 검색위주 VOD 등에 이용
      • 데이터의 동시접근이 어렵고 회복기능 등 지원이 빈약
    • 관계형 데이터베이스 기반 기법
      • 텍스트 데이터를 저장하기 위해서 CLOB 데이터 타입을 이용
      • 이미지, 비디오, 오디오는 BLOB 데이터 타입을 이용
    • 객체 지향 데이터베이스 기반 기법
      • 멀티미디어를 가장 잘 표현할수 있는 기법
      • 사용자 정의 클래스, 사용자 정의 메소드를 이용
    • 객체 관계형 데이터베이스 기반 기법
      • 관계형 디비 + 객체 지향 디비 특성 이용

• 주기억장치 데이터베이스

  디비 전체를 주기억장치에 상주시킨 후 연산을 수행하여 디스크 입출력이 발생하지 않음

  • 특성
    • 빠른 연산
    • 복구 및 회복작업의 구현이 어려움
    • 주기억장치의 구입 비용이 많이듬

• 데이터 웨어하우스

  • 다량의 데이터를 효과적으로 분석하여 정보화하고 여러 계츠의 사용자들이 효율적으로 사용
  • 정보의 효율적 분석, 신속 정확한 의사결정으로 경쟁력을 확보하기 위한 의사결정용 데이터베이스
  • 다양한 원본 데이터베이스로부터 정제되어 추출된 데이터만을 저장하고 필요한 인덱스를 생성
  • 장점
    • 높은 투자 수익률
    • 경쟁우위
    • 의사 결정자의 생산성 향상
  • 단점
    • 업무의 혼란 초래
    • 잘못된 데이터로 인한 불일치 문제
    • 데이터확보 안되면 정확한 결과도출이 안됨
    • 과다한 자원 사용과 유지보수의 어려움

• 데이터 마트

  • 데이터 웨어하우스로부터 특정 주제로 구축된 소규모 단일 주제 데이터 웨어하우스
  • 한 기업내 복수개의 데이터 마트가 존재
  • 전사적 통합성을 염두해 데이터 마트가 웨어하우스보다 먼저 구축될수도있음
  • 특징
    • 데이터 웨어하우스의 분석 요건에 적합한 구조로 재구성
    • 추세, 패턴 분석 및 데이터 접근이 용이한 요약 데이터로 구성
    • 분석에 필요한 이력 데이터만을 포함하는 제한된 규모의 데이터
    • 다양한 질의나 요구를 충족하는 유연성과 접근성이 뛰어난 다차원 구조의 데이터

• 데이터 마이닝

  데이터 웨어하우스에 저장된 데이터 집합에서 요구에 따라 유용하고 가능성 있는 정보를 발견하기 위한 기법

  • 기법
    • 연관 : 대용량 트랜잭션 데이터로부터 "A이면 B이다" 형식의 관계를 발견하기 위한 방법
    • 연속 : 트랜잭션 이력 데이터를 시계열적으로 분석하여 트랜잭션의 향후 발생 가능성을 예측
    • 분류 : 다른 그룹과의 차별적인 특성을 도출
    • 클러스터링(군집화) : 상호 간에 유사한 특성을 갖는 데이터들을 집단화
    • 특성화 : 데이터 집합의 일반적인 특성을 분석. 데이터의 요약과정을 통해 특성을 발견
    • 패턴 분석 : 디비 내의 명시된 패턴을 찾는 방법
    • 경향 분석 : 시계열 데이터들이 시간 축으로 변화하는 전개 과정을 특성화하여 변하는 데이터를 분석

• OLAP

  다차원으로 이루어진 데이터로부터 통계적인 요약 정보를 분석하여 의사결정에 활용하는 방식

  • 데이터 웨어하우스나 데이터 마트와 같은 시스템과 상호 연관됨
  • 데이터 웨어하우스의 데이터를 전략적인 정보로 변환 시키는 역할
  • 직접 컴퓨터를 이용하여 데이터에 접근하는데 있어 필수적 시스템
  • 연산
    • Roll-up : 상세 데이터로부터 요약된 형태의 데이터로 접근하는 기능
    • Drill-down : 요약된 형태의 데이터로부터 구체적인 내용의 상세 데이터로 접근
    • Drill-through : 데이터 웨어하우스나 OLTP에 존재하는 상세 데이터에 접근
    • Drill-across : 다른 데이터 큐브의 데이터에 접근
    • Pivoting : 보고서의 행, 열, 페이지 차원을 바꾸어 봄
    • Slicing : 다차원 데이터 항목들을 조회하고 비교
    • Dicing : Slicing을 더 세분화
  • 종류
    • ROLAP : 관계형 데이터베이스와 질의어를 사용하여 다차원 데이터를 저장 분석
    • MOLAP : 다차원 데이터베이스 사용. 큐브 캐시라는 주기억장치 사용으로 검색 속도 향상
    • HOLAP : ROLAP + MOLAP

• OLTP

  네트워크상의 여러 이용자가 실시간으로 디비의 데이터를 갱신하고 검색하는 등 작업 처리하는 방식

  • 단위 작업은 트랜잭션
  • 빠른 응답시간 요구, 개개의 레코드를 효율적으로 조회하고 수정할수 있도록 정규화 되어있음
  • OLTP와 OLAP 비교
    • OLTP : 복잡, 빠름, 보관짧음, 정규 데이터, 변경어려움, 데이터 크기작음, 상세 데이터, 연속처리, 예언가능
    • OLAP : 단순, 느림, 보관길다, 비정규데이터, 변경쉬움, 데이터 크기 큼, 요약 데이터, 분석 처리, 예측 어려움

• ODBC

  프로그램과 디비 종류에 상관없이 접근하여 사용할 수 있도록 만든 응용프로그램 표준 인터페이스

  • ODBC 기준에 맞게 응용 프로그램을 작성하면 됨
  • 여러 종류의 디비를 함께 사용가능
  • 기존 디비를 교체해도 응용 프로그램을 그대로 사용하여 비용 절감
  • 구조
    • Application : 디비에 접속할 때 ODBC API를 사용
    • Driver Manager : App과 ODBC Driver 간의 통신을 관리하는 라이브러리. DSN에서 정의된 형태로 로딩
    • DSN : 서버에 연결할때 필요한 드라이버와 데이터베이스 정보를 저장
    • ODBC Driver : ODBC API가 지원하는 함수를 구현하는 라이브러리
    • 데이터베이스 : ODBC Driver 에서 요청한 SQL에 대한 결과 반환