남궁찬

질문이나 피드백 감사합니다.

데이터베이스 정리

업데이트:

데이터베이스 과목을 정리해둔 내용입니다.

관계 데이터 모델

키

특정 투플을 실별할 때 사용하는 속성 혹은 속성의 집합

릴레이션 간의 관계를 맺는 데도 사용됨

  • 슈퍼키

    투플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합

    속성의 개수는 고려하지 않는다

  • 후보키

    투플을 유일하게 식별할 수 있는 속성의 최소 집합

  • 기본키

    여러 후보키 중 하나를 선정하여 대표로 삼는 키

    기본키 선정 시 고려사항

    • 릴레이션 내 투플을 식별할 수 있는 고유한 값을 가져야 함
    • NULL값은 허용하지 않음
    • 키 값의 변동이 일어나지 않아야 함
    • 최대한 적은 수의 속성을 가져야 함

  • 대리키

    기본키가 보안을 요하거나, 여러 개의 속성으로 구성되어 복잡하거나, 마땅한 기본키가 없을 때는 일련번호 같은 가상의 속성을 만들어 기본키로 삼는 경우가 있음. 이러한 키를 대리키 혹은 인조키라고 함.

  • 대체키

    대체키는 기본키로 선정되지 않은 후보키를 말함

  • 외래키

    다른 릴레이션의 기본키를 참조하는 속성을 말함. 다른 릴레이션의 기본키를 참조하여 관계 데이터 모델의 특징인 릴레이션 간의 관계(Relationship)를 표현함.

    • 특징
      • 릴레이션 간의 관계를 표현함
      • 다른 릴레이션의 기본키를 참조하는 속성임
      • 참조하고(외래키) 참조되는(기본키) 양쪽 릴레이션의 도메인은 서로 같아야 함
      • 참조되는(기본키) 값이 변경되면 참조하는(외래키) 값도 변경됨
      • NULL값과 중복 값 등이 허용됨
      • 자기 자신의 기본키를 참조하는 외래키도 가능함
      • 외래키가 기본키의 일부가 될 수 있음

무결성 제약조건

  • 도메인 무결성 제약조건
    • 릴레이션 내 투플들은 각 속성의 도메인에 지정된 값만을 가져야 한다는 조건
  • 개체 무결성 제약조건
    • 기본키는 NULL값을 가져서는 안 되며 릴레이션 내에 오직 하나의 값만 존재해야 한다는 조건
  • 참조 무결성 제약조건
    • 자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 동일해야 하며, 자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다.
    • 부모 릴레이션에서 삭제
      • 즉시 작업을 중지
      • 자식 릴레이션의 관련 투플을 삭제
      • 초기 설정된 다른 값으로 변경
      • NULL 값으로 설정
    • 수정
      • 삭제와 삽입 명령이 연속해서 수생됨

관계대수

  • 조인
    • 두 릴레이션의 공통 속성을 기준으로 속성 값이 같은 투플을 수평으로 결합하는 연산
    • 세타조인
      • 조건을 만족하는 투플만 반환함.
    • 동등조인
      • 세타조인에서 = 연산자를 사용한 조인
    • 지연조인
      • 조인에 참여한 속성이 두 번 나오지 않도록 두번째 속성을 제거한 결과를 반환
    • 외부조인
      • 조인에 실패한 투플을 모두 보여주되 값이 없는 대응 속성에는 NULL 값을 반환
      • left, right, full outer join
    • 세미조인
      • 두 릴레이션 중 한쪽 릴레이션의 결과만을 반환함

SQL

개요

  • 기능에 따른 분류
    • 데이터 정의어(DDL)
      • 테이블이나 관계의 구조를 생성하는 데 사용. CREATE, ALTER, DROP 등
    • 데이터 조작어(DML)
      • 테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용하며 SELECT, INSERT, DELETE, UPDATE문 등이 있음
    • 데이터 제어어(DCL)
      • 데이터의 사용 권한을 관리하는 데 사용하며 GRANT, REVOKE 문 등이 있음

삭제 명령들의 차이

  • DROP
  • 테이블의 정의 자체를 완전히 삭제함.
  • Rollback 불가능.

  • 테이블이 사용했던 Storage를 모두 Release

  • Delete
    • 데이터만 삭제.
    • Commit이전에는 Rollback이 가능함.
    • 데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음.
  • Truncate
    • 테이블을 최초 생성된 초기상태로 만듬.
    • Rollback 불가능.
    • 테이블이 사용했던 Storage중 최초 테이블 생성시 할당된 Storage만 남기고 Release.

부속질의

하나의 SQL문 안에 다른 SQL문이 중첩된 nested 질의를 말함

보통 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 join보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋음

  • 스칼라 부속질의
    • SELECT절
    • SELECT절에서 사용되며 단일 값을 반환하기 때문에 스칼라 부속질의라고 함
  • 인라인 뷰
    • FROM절
    • FROM절에서 결과를 View 형태로 반환하기 때문에 인라인 뷰라고 함
  • 중첩질의
    • WHERE절
    • WHERE절에 술어와 같이 사용되며 결과를 한정시키기 위해 사용됨. 상관 혹은 비상관 형태

뷰

하나 이상의 테이블을 합하여 만든 가상의 테이블

  • 장점
    • 편리성 및 재사용성
      • 자주 사용되는 복잡한 질의를 뷰로 미리 정의해 놓을 수 있음
      • 복잡한 질의를 간단히 작성
    • 보안성
      • 각 사용자별로 필요한 데이터만 선별하여 보여줄 수 있음. 중요한 질의의 경우 질의 내용을 암호화할 수 있음
      • 개인정보 등 민감정보를 제외한 테이블을 만들어 사용
    • 독립성 제공
      • 원본테이블이 구조가 변해도 영향을 주지 않도록 하는 논리적 독립성 제공
      • 미리 정의된 뷰를 일반 테이블철럼 사용할 수 있기 떄문에 편리함. 또 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있음
  • 특징
    • 원본 데이터 값에 따라 같이 변함
    • 독립적인 인덱스 생성이 어려움
    • 삽입, 삭제, 갱신 연산에 많은 제약이 따름

인덱스

인덱스와 B-Tree

인덱스란 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조

  • 특징
    • 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성함
    • 빠른 검색과 함께 효율적인 레코드 접근이 가능함
    • 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지함
    • 저장된 값들은 테이블의 부분집합이 됨
    • 일반적으로 B-Tree 형태의 구조를 가짐
      • 실제로는 B+트리. 말단 노드가 링크드 리스트로 연결되어 있다.
    • 단점
      • 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요함
      • 인덱스를 설정하면 추가적인 저장공간을 필요로 함

인덱스의 생성

  • 고려사항
    • 인덱스는 WHERE절에 자주 사용되는 속성이어야 함
    • 인덱스는 조인에 자주 사용되는 속성이어야 함
    • 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있음 (테이블 당 4~5개 정도 권장)
    • 속성이 가공되는 경우 사용하지 않음
    • 속성의 선택도가 낮을 때 유리함

데이터 모델링

  • 데이터베이스 생명주기

1575695387923

  • 개념적 모델링
    • 요구사항을 수집하고 분석한 결과를 토대로 업무의 핵심적인 개념을 구분하고 전체적인 뼈대를 만드는 과정
    • 개체(entity)를 추출하고 각 개체들 간의 관계를 정의하여 ER 다이어그램을 만드는 과정까지를 말함
  • 논리적 모델링
    • 개념적 모델링에서 만든 ERD를 사용하려는 DBMS에 맞게 매핑하여 실제 데이터베이스로 구현하기 위한 모델을 만드는 과정
    • 과정
      • 개념적 모델링에서 추출하지 않은 상세 속성을 모두 추출함
      • 정규화 수행
      • 데이터 표준화 수행
  • 물리적 모델링
    • 작성된 논리적 모델을 실제 컴퓨터의 저장 장치에 저장하기 위한 물리적 구조를 정의하고 구현하는 과정

ER모델

개체(entity)와 개체 간의 관계(relationship)로 표현함

개체 Entity

  • 개체 타입의 유형
    • 강한 개체
      • 다른 개체의 도움 없이 독자적으로 존재할 수 있는 개체
    • 약한 개체
      • 독자적으로는 존재할 수 없고 반드시 상위 개체 타입을 가짐
      • 테이블을 만들기 힘들고, 만들더라도 키를 할당할 수 없는 경우가 많다

속성 attribute

개체가 가진 성질

  • 속성은 타원으로 표현
  • 개체 타입은 직사각형으로 표현
  • 속성이 키인 경우 속성 이름에 밑줄을 그음

1575696783851

1575696835378

관계와 관계 타입

  • 관계
    • 개체 사이의 연관성을 나타내는 개념
  • 관계 타입
    • 개체 타입과 개체 타입 간의 연결 가능한 관계를 정의한 것.
    • 관계 집합 : 관계로 연결된 집합

1575697042499

  • 차수에 따른 유형

    • 1진 관계
      • 한 개의 개체가 자기 자신과 관계를 맺는 경우
    • 2진 관계
      • 두 개의 개체가 관계를 맺는 경우
    • 3진 관계
      • 세 개의 개체가 관계를 맺는 경우

  • 관계 대응수

    • 두 개체 타입의 관계에 실제로 참여하는 개별 개체 수

    1575697312357

  • 관계 대응수의 최솟값과 최댓값

    1575697700687

    • 학생은 최소 1, 최대 1개의 학과에 소속된다
    • 학과에 소속되는 학생은 없어도 되고 많아도 된다

IE표기법

ERD를 축약하여 표현한 표기법

1575698217483

정규화

이상현상

  • 삭제이상
    • 투플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
    • 연쇄삭제 (triggered deletion) 문제 발생
  • 삽입이상
    • 투플 삽입 시 특정 속성에 해당하는 값이 없어 NULL값을 입력해야 하는 현상
    • NULL값 문제 발생
  • 수정이상
    • 투플 수정 시 중복된 데이터의 일부만 수정되어 데이터의 불일치 문제가 일어나는 현상
    • 불일치 문제 발생

함수 종속성

속성 A의 값을 알면 속성 B의 값이 유일하게 정해지는 의존 관계를

“속성 B는 속성 A에 종속한다” 또는 “속성 A는 속성B를 결정한다” 라고 한다.

기본키는 릴레이션의 모든 속성에 대해 결정자이다.

이상현상은 기본키가 아니면서 결정자인 속성이 있을 때 발생한다.

정규화

이상현상이 발생하는 릴레이션을 분해하여 이상현상을 없애는 과정

제 1 정규형

릴레이션의 모든 속성 값이 원자값을 가진다.

속성값은 더이상 쪼갤 수 없는 단위어야 한다

제 2 정규형

릴레이션이 제1정규형이고 기본키가 아닌 속성이 기본키에 완전 함수 종속이다.

기본키가 아닌 속성은 기본키가 아닌 속성에 함수 종속하지 말아야 한다.

완전 함수 종속 : A와 B가 릴레이션의 속성이고 A->B 종속성이 성립할 때, B가 A의 속성 전체에 함수 종속하고 부분 집합 속성에 함수 종속하지 않을 경우

제 3 정규형

  • 릴레이션이 제 2 정규형이고 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때(직접 종속) 제 3 정규형이라고 함
  • 이행적 종속이란, A->B, B->C가 성립할 때, A->C가 성립되는 함수 종속성

반정규화 (역정규화)

정규화로 인해 테이블 수가 증가하면 JOIN연산이 많아져 성능 저하가 될 수 있다.

이를 해결하고자 정규화 과정을 전부 끝내고 반정규화를 실시한다

무손실 분해

릴레이션 R1과 R2로 분해할 때, R1 join R2 = R 이면 무손실 분해

트랜잭션, 동시성제어, 회복

트랜잭션

DB에서 데이터를 다루는 논리적인 작업의 단위

  • 성질
    • 원자성
      • 트랜잭션에 포함된 작업은 전부 수행되거나 아니면 전부 수행되지 않아야 함
    • 일관성
      • 트랜잭션을 수행하기 전이나 수행한 후나 데이터베이스는 항상 일관된 상태를 유지해야 함
    • 고립성
      • 수행 중인 트랜잭션에 다른 트랜잭션이 끼어들어 변경 중인 데이터 값을 훼손하는 일이 없어야 함
    • 지속성
      • 수행을 성공적으로 완료한 트랜잭션은 변경한 데이터를 영구히 저장해야 함

동시성 제어

트랜잭션이 동시에 수행될 때, 일관성을 해치지 않도록 드랜잭션의 데이터 접근을 제어하는 기능

  • 갱신손실
    • 두 개의 트랜잭션이 한 개의 데이터를 동시에 갱신할 때 발생, 데이터베이스에서 절대 발생하면 안 되는 현상
  • 락
    • 갱신손실 문제를 해결하려면 상대방 트랜잭션이 데이터를 사용하는지 여부를 알 수 있는 규칙이 필요함
    • 공유락 : 트랜잭션이 읽기를 할 때 사용
    • 배타락 : 트랜잭션이 읽고 쓰기를 할 때 사용
    • 배타락이 하나라도 있으면 대기, 모두 공유락일 때 허용
  • 2단계 락킹
    • 락을 걸고 해제하는 시점에 제한을 두지 않으면 두 개의 트랜잭션이 동시에 실행될 때 데이터의 일관성이 깨질 수 있어 이를 방지하는 방법

데드락

두 개 이상의 트랜잭션이 각각 자신의 데이터에 대하여 락을 획득하고 상대방 데이터에 대하여 락을 요청하면 무한 대기 상태에 빠질 수 있는 현상, 교착상태

트랜잭션 고립 수준

구분 **트랜잭션 **1 **트랜잭션 **2 발생 문제 동시접근
[상황1] 읽기 읽기 읽음(읽기만 하면 아무 문제가 없음) 허용
[상황 2] 읽기 쓰기 오손 읽기, 반복불가능 읽기, 유령 데이터 읽기 허용 혹은 불가 선택
[상황 3] 쓰기 쓰기 갱신손실(절대 허용하면 안 됨) 허용불가(LOCK을 이용)

1575705856168

아래로 갈수록 일관성은 높아지지만 동시성은 떨어진다

회복

데이터베이스에 장애가 발생했을 때 데이터베이스를 일관성 있는 상태로 되돌리는 DBMS의 기능

로그 파일

  • 트랜잭션의 DB 기록을 추적한다
  • 트랜잭션이 반영한 모든 데이터의 변경사항을 DB에 기록한다

체크포인트

  • 회복 시 많은 양의 로그를 검색하고 갱신하는 시간을 줄인다
  • 몇십분 단위로 db와 트랜잭션 로그 파일을 동기화한 후
  • 동기화 시점을 로그 파일에 기록해두는 방법 또는 그 시점

댓글남기기

참고