5. 데이터베이스 기본

트랜잭션(Transaction): 인가받지 않은 사용자로부터 데이터를 보장하기 위해서 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적인 기능을 정상적으로 수행하기 위한 기본 단위이다.

 

트랜잭션 특성

:ACID

원자성(Atomicity)	- 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 취소되어야 하는 성질	Commit/Rollback 회복성 보장
일관성(Consistency)	- 시스템이 가지고 있는 고정 요소는 트랜잭션 수행 전과 수행 후의 상태가 같아야 하는 성질	무결성 제약조건 동시성 제어
고립성(Isolation)	- 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 하는 성질	Read Uncommitted Read Committed Repeatable Read Serialization
영속성(Durability)	- 성공이 완료된 트랜잭션 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질	회복 기법

트랜잭션 상태 변화

활동 상태(Active)	초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
부분 완료 상태(Partialy Committed)	마지막 명령문이 실행된 후 가지는 상태
완료 상태(Committed)	트랜잭션이 성공적으로 완료된 후 가지는 상태
실패 상태(Failed)	정상적인 실행이 더 이상 진행될 수 없는 상태
철회 상태(Aborted)	트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 돌아간 상태

트랜잭션 제어

: 트랜잭션 제어언어는 TCL(Transaction Control Language)이라고 하며, 트랜잭션 결과를 허용하거나 취소하는 목적으로 사용되는 언어이다

커밋(COMMIT)	트랜잭션 확정	트랜잭션을 메모리에 영구적으로 저장하는 명령어
롤백(ROLLBACK)	트랜잭션 취소	트랜잭션 내역을 저장 무효화시키는 명령어
체크포인트(CHECKPOINT)	저장 시기 설정	ROLLBACK을 위한 시점을 지정하는 명령어

병행 제어(일관성 주요 기법)

: 병행 제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법이다

 

병행 제어 목적

: 데이터베이스 공유를 최대화한다

: 시스템 활용도를 최대화한다

: 데이터베이스 일관성을 유지한다

: 사용자에 대한 응답시간을 최소화한다

 

병행 제어 미보장 시 문제점

갱신 손실(Lost Update)	먼저 실행된 트랜잭션 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
현황 파악오류(Dirty Read)	트랜잭션 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
모순성(Inconsistency)	두 트랜잭션이 동시에 실행되어 데이터베이스 일관성이 결여되는 오류
연쇄복귀(Cascading Rollback)	복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

병행 제어 기법의 종류

로킹(Locking)	: 하나의 트랜잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜색션이 동시에 접근하지 못하도록 상호배제 기능을 제공하는 기법 : 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있음 : 로킹 단위가 작아지면 로킹 오버헤드가 증가 : 로킹 단위가 작아지면 데이터베이스 공유도 증가 : 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함
낙관적 검증(Optimistic Vaildation)	트랜잭션이 어떠한 검증을 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서(Time Stamp Ordering)	트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중버전 동시성 제어(MVCC; Mutli Version Concurrency Control)	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기버

데이터베이스 고립화 수준(격리성 주요 기법)

: 고립화 수준은 다른 트랜잭션이 현재 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도

 

고립화 수준 종류

Read Uncommitted	: 한 트랜잭션에서 연산 중인 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준 : 연산 중인 데이터에 대한 연산은 불허
Read Committed	: 한 트랜잭션에서 연산을 수행할 때, 연산이 완료될 때 까지 연산 대상 데이터에 대한 읽기를 제한하는 수준 : 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용
Repeatable Read	선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신, 삭제를 제한하는 수준
Serialization Read	선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체의 접근을 제한하는 수준

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

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

 

회복 기법 주요 요소

REDO	: 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션 시작과 완료에 대한 기록이 있는 트랜잭션들의 작업을 재작업하는 기법 : 데이터베이스 내용 자체가 손상된 경우, 가장 최근의 복제본을 적재한 후 일어난 변경만을 로그를 이용하여 재실함으로써 데이터베이스를 복원하는 기법
UNDO	: 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작은 있지만, 완료된 기록이 없는 트랜잭션들이 작업한 변경 내용들을 모두 취소하는 기법 : 데이터베이스 내용 자체는 손상되지 않았지만, 변경 중이거나 변경된 내용에 대한 신뢰성을 잃어버린 경우, 모든 변경 내용을 취소하여 복원하는 기법

회복 기법 종류

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

DDL(Data Definition Language)

: 데이터 정의어는 데이터를 정의하는 언어로서 데이터를 담는 그릇을 정의하는 언어이다

 

DDL 대상

도메인(Domain)	하나의 속성이 가질 수 있는 원자값들의 집합 속성의 데이터 타입과 크기, 제약조건 등의 정보
스키마(Schema)	데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 외부 스키마(External Schema) : 사용자나 개발자의 관점에서 필요하는 데이터베이스의 논리적 구조 개념 스키마(Conceptual Schema): 데이터베이스의 전체적은 논리적 구조 내부 스키마(Internal Schema) : 물리적 저장 장치의 관점에서 보는 데이터베이스 구조
테이블(Table)	데이터 저장 공간
뷰(View)	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index)	검색을 빠르게 하기 위한 데이터 구조

테이블(Table)

: 데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체이다

: 하나의 DB 내에 여러 개의 테이블로 구성될 수 있고, 릴레이션 혹은 엔터티하고도 불린다

 

테이블 관련 용어

: 튜플 / 행

: 애트리뷰트 / 속성

: 식별자

: 카디널리티

: 차수

: 도메인

 

뷰(View)

: 논리 테이블이다

 

뷰의 특징

논리적 데이터 독립성 제공	데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
데이터 조작 연산 간소화	애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
보안 기능(접근제어) 제공	특정 필드만을 선택해 뷰를 생성한 후 애플리케이션이 선택되지 않는 필드 조회 및 접근 불가
뷰 변경 불가	뷰 정의는 ALTER 문을 이용하여 변경할 수 없음 (뷰는 CREATE 문을 사용하여 정의, 뷰를 제거할 때에는 DROP 문 사용

뷰의 목적

: 단순 질의어를 사용할 수 있다

: FROM 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블을 대체하는 단순성에 의의가 있다

: 테이블의 중요 데이터 일부만을 제공할 수 있는 장단점이 있다

장점	논리적 독립성 제공	뷰는 논리테이블
	사용자 데이터 관리 용이	복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용 가능
	데이터 보안의 용이	중요 보안 데이터를 저장 중인 테이블에는 접근 불허
단점	뷰 자체의 인덱스 불가	인덱스는 물리적으로 저장된 데이터를 대상으로 한다. 논리적 구성인 뷰는 인덱스를 가지지 못함
	뷰 정의 변경 불가	뷰 정의를 변경하려면 뷰를 삭제하고 재생성
	데이터 변경 제약 존재	뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

 

인덱스(Index)

: 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조

: 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여주는 자료 구조

: 테이블의 특정 레코드 위치를 알려주는 용도 사용

 

인덱스 특징

: 기본키 컬럼은 자동 인덱스 생성

: 테이블 전체 스캔; Table Full Scan

: 인덱스 범위 스캔; Index Range Scan

: 조건절에 '='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있다

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

DDL 명령어

CREATE

ALTER

DROP

TRUNCATE

 

CREATE TABLE 기본문법

CREATE TABLE 테이블명

(

컬럼명 데이터타입 PRIMARY KEY,

컬럼명 데이터타입 FOREIGN KEY REFERNCES 참조테이블(기본키),

컬럼명 데이터타입 UNIQUE,

컬럼명 데이터타입 NOT NULL,

컬럼명 데이터타입 CHECK(조건식),

컬럼명 데이터타입 DEFAULT 값

)

 

ALTER TABLE

컬럼 추가 문법

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

 

컬럼 수정 문법

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건];

 

컬럼 삭제 문법

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

DROP TABLE

DROP TABLE 테이블명 [CASCASE | RESTRICT]

 

DROP TABLE 명령어 옵션

CASECADE	참조하는 테이블까지 연쇄적으로 제거하는 옵션
RESTRICT	다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

TRUNCATE TABLE

: 테이블 내의 데이터들을 삭제하는 명령

TRUNCATE TABLE 테이블명;

 

CREATE VIEW 문법

CREATE VIEW 뷰이름 AS

 

조회쿼리

CREATE VIEW 뷰이름 AS

 

조회쿼리;

CREATE OR REPLACE VIEW 기본 문법

 

DROP VIEW

DROP VIEW 뷰이름;

 

CREATE INDEX 문법

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

 

ALTER INDEX 문법

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

 

DROP INDEX 문법

DROP INDEX 인덱스명

 

데이터 조작어(DML; Data Manipulation Language)

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

 

DML 명령어 유형

SELECT

INSERT
UPDATE

DELETE

 

SELECT 명령어 개념

SELECT [ALL | DISTINCT] 속성명1, 속성명2...

 FROM 테이블명1, ...

[WHERE 조건]

[GROUP BY 속성명1, ...]

[HAVING 그룹조건]

[ORDER BY 속성 [ASC | DESC] ];

 

논리적 조인 유형(상세)

내부 조인(Inner Join)	공통 존재 컬럼의 값이 같은 경우를 추출하는 기법
외부 조인(Outer Join)	왼쪽 외부 조인(Left Outer Join) 오른쪽 외부 조인(Right Outer Join) 완전 외부 조인(Full Outer Join)
교차 조인(Cross Join)	조인 조건이 없는 모든 데이터 조합을 추출하는 기법
셀프 조인(Self Join)	자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

서브쿼리 유형

FROM 절 서브쿼리	인라인 뷰라고 불림
WHERE 절 서브쿼리	중첩 서브쿼리라고도 불림

집합 연산자(Set Operator)

UNION	중복 레코드 제외
UNION ALL	중복 레코드 포함
INTERSECT	중복 레코드만 포함
MINUS	비교 레코드 제외

UPDATE 명령어

UPDATE 테이블명

SET 속성명 = 데이터, ...

WHERE 조건;

 

데이터 제어어(DCL; Data Control Language)

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

 

DCL 유형

RRANT : 사용 권한 부여

REVOKE : 사용 권한 취소

 

GRANT 명령어

GRANT 권한 ON 테이블 TO 사용자;

 

REVOKE 명령어

REVOKE 권한 ON 테이블 FROM 사용자;