[정보처리기사 실기] SQL 응용

트랜잭션

하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

✅ 원자성, 일관성, 고립성, 영속성

트랜잭션 제어

commit : 트랜잭션 확정 

rollback : 트랜잭션 내역을 저장 무효화

checkPoint : 저장시기 설정-rollback위한 시점 지정

병행제어

다수 사용자 환경에서 여러 트랜잭션 수행시, db일관성 유지를 위해 상호작용을 제어

✅ 병행제어 미보장 시,

갱신 손실: 먼저 실행된 트랜잭션 결과를 나중에 실행된 트랜잭션이 덮어쓸 때  

현황 파악 오류: 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조

모순성: 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성 결여

연쇄복귀: 복수의 트랜잭션이 데이터 공유시 특정 트랜잭션이 처리를 취소할 경우, 트랜잭션이 처리한 곳의 부분을 취소하지 못함

✅ 병행제어 기법

1. Locking : 다중 트랜잭션 환경에서 db의 일관성과 무결성 유지 위해 트랜잭션 순차적 진행을 보장하는 직렬화 기법
   로킹단위가 작아지면 데이터베이스 공유도 증가, 로킹 오버헤드 증가  
2. 낙관적 검증 트랜잭션 종료 시 검증 수행하여 db에 반영
3. 타임스탬프 : 트랜잭션과 대상 데이터에 대해 실행전 타임스탬프를 부여하여 부여된 시간에따라 트랜잭션  수행
4. 다중버전 동시성 제어 : 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근

고립화 수준(격리성 주요 기법)

Read Uncommitted : 한 트랜잭션에서 연산(갱신)중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는것을 허용하는 수준, 연산(갱신)중인 데이터에대한 연산은 불허

Read Committed : 한 트랜잭션에서 연산(갱신)을 수행할때 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한하는 수준 

Repeatable Read : 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제를 제한하는 수준

Serializable : 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한하는 수준

회복 기법(영속성 주요 기법)

손상되기 이전의 정상적인 상태로 복구

로그 기반 회복 기법

1. 자연 갱신 회복 기법: 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
2. 즉각 갱신 회복 기법: 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법
3. 체크포인트 회복: 검사점 이후에 처리된 트랜잭션에 대해서만 장애발생 이전의 상태로 복원
4. 그림자 페이징 회복 기법: 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구
5.  

DDL 데이터 정의어

CREATE, ALTER, DROP, TRUNCATE

DML 데이터 조작어

SELECT, INSERT, UPDATE, DELETE

DCL 데이터 제어어

GRANT, REVOKE

 

스키마

데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조

3계층

외부: 사용자나 개발자의 관점, 데이터베이스의 논리적 구조, 사용자 뷰, 서브스키마

개념: 데이터베이스의 전체적 논리 구조, 전체적인 뷰, 개체간의 관계, 제약 조건, 접근 권한, 무결성, 보안에 대한 정의

내부: 물리적 저장장치의 관점, 데이터베이스 구조, 실제로 데이터베이스에 저장될 레코드 형식을 정의, 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 표현

테이블

데이터 저장 공간(=릴레이션, 엔티티), 데이터 저장 항목인 필드들로 구성된 데이터의 집합체

Tuple: 행, 레코드, 릴레이션에서 같은 값을 가질 수 없음

Cardinality: 튜플 개수

Attribute: 열

Degree: 차수, 열의 개수

식별자: 여러개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분 할 수 있는 논리적 개념

도메인: 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값들의 집합, 속성의 데이터 타입과 크기, 제약조건 등의 정보

뷰

논리 테이블, 단순 질의어 사용 가능, 중요 데이터 일부만을 제공 가능

인덱스

검색 연산의 최적화, 주소 정보

PK 컬럼은 자동 생성

연월일이나 이름을 기준으로 하는 인덱스는 자동 생성 안됨

종류

• 순서인덱스: B-Tree 알고리즘
• 해시인덱스: 해시함수에 의해 키값으로 접근
• 비트맵인덱스: 각 컬럼에 적은 개수 값이 저장된 경우 선택
• 함수기반인덱스: 수식이나 함수를 적용
• 단일인덱스: 하나의 컬럼으로만 구성
• 결합인덱스: 두개 이상의 컬럼으로 구성
• 클러스터드인덱스: pk기준으로 묶어 저장, 저장 데이터의 물리적 순서에 따라 인덱스 생성, 특정 범위 검색시 유리

스캔방식

• 인덱스 범위 스캔: 인덱스 루트 블록에서 리프블록까지 수직으로 탐색한 후 리프 블록을 필요한 범위만 스캔
• 인덱스 전체 스캔: 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색
• 인덱스 단일 스캔: 수직적 탐색만으로 데이터를 찾음
• 인덱스 생략 스캔: 선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔 방식

절차형 SQL

절차지향적 프로그램이 가능하도록 하는 트랜잭션 언어

1. 프로시저: 일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리 집합
   구성: 선언부, 시작종료부, 제어부, SQL, 예외부, 실행부
   실행: EXECUTE 프로시저명
2. 사용자 정의 함수: 일련의 SQL처리를 수행하고 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
   구성: 선언부, 시작종료부, 제어부, SQL, 예외부, 실행부
   RETURN값이 있다는게 프로시저와의 차이
3. 트리거: 삽입, 갱신, 삭제 등의 이벤트가 발생할때마다 관련 작업이 자동으로 수행
   선언부, 이벤트부, 시작종료부, 제어부, SQL, 예외부
   ✅TCL(트랜잭션 제어어; COMMIT, ROLLBACK) 사용불가
   
   • 행트리거: 데이터 변화 생길때마다 실행
   • 문장트리거: 트리거에 의해 단 한번 실행

출처: 수제비 정보처리기사 실기