CHAPTER 01 - 데이터베이스 기본(1)

2024년도 수제비 실기책(6판) 내용 정리

---

1. 데이터베이스 기본

1) 트랜잭션

(1) 트랜잭션(Transaction)의 개념

 

• 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

(2) 트랜잭션의 특징(ACID)

특성	설명	주요기법
원자성 (Atomicity)	트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질 트랜잭션의 연산 전체가 성공 또는 실패(All or Nothing)되어야 하는 성질	Commit / Rollback 회복성 보장
일관성 (Consistency)	시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질	무결성 제약조건 동시성 제어
격리성 = 고립성 (Isolation)	동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질	고립화 수준 (레벨 0, 1, 2, 3)
영속성 (Durability)	성공이 완료된 트랜잭션의 결과는 영속성으로 데이터 베이스에 저장되어야하는 성질	회복기법

 

(3) 트랜잭션의 상태 변화(활부완실철)

트랜잭션의 상태 전이도

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

 

(4) 트랜잭션 제어(커롤체)

 

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

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

 

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

 

[1] 병행 제어(Concurrency Control) 개념

 

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

[2] 병행 제어의 목적

 

• 데이터 베이스의 공유를 최대화
• 시스템의 활용도를 최대화
• 데이터베이스의 일관성을 유지
• 사용자에 대한 응답시간을 최소화

[3] 병행 제어 기법의 종류(로 낙타다2)

기법	설명
로킹 (Locking)	하나의 트랜 잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 상호배제(Mutual Exclusion) 기능을 제공하는 기법
	로킹의 특징  - 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있음  - 로킹 단위가 작아지면 데이터베이스 공유도가 증가  - 로킹 단위가 작아지면 로킹 오버헤드가 증가  - 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함
낙관적 검증 (Optimistic Validation)	트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서 (Time Stamp Ordering)	트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프(Time Stamp)를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중버전 동시성 제어 (MVCC; Multi Version  Concurrency Control)	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법
2PC (2 Phase Commit)	데이터베이스 동시성 제어 기술 중 하나로, 여러 개의 분산 데이터베이스 시스템에서 트랜잭션의 일관성을 유지하기 위한 기법 트랜잭션을 두 단계로 분리하여 제어
	1단계 (준비단계)  - 트랜잭션 수행 결과를 다른 분산 시스템에 알리는 과정 2단계 (커밋단계)   - 모든 분산 시스템에서 트랜잭션 수행 결과가 일치하는지 확인하는 과정  - 모든 분산 시스템이 트랜잭션을 성공적으로 수행했다면 커밋을 수행하고 그렇지 않다면 롤백을 수행

 

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

 

[1] 고립화 수준(Isolation Level) 개념

 

• 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도

[2] 고립화 수준 종류

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

 

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

 

[1] 회복 기법(Recovery) 개념

 

• 트랜잭션을 수행하는 도중 장애로 인한 손상된 데이터베이서를 손상되기 이전의 상태로 복구시키는 작업

[2] 회복 기법 종류

 

• 주요 요소

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

 

• 회복 기법 종류(회로체그)

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

2) DDL

(1) 데이터 정의어(DDL; Data Definition Language)의 개념

 

• 데이터를 정의하는 언어로서 데이터를 담는 그릇을 정의하는 언어
• 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부름

(2) DDL의 대상

 

[1] 스키마(Schema)

 

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

구성	설명
외부 스키마 (External Schema)	사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조 사용자 뷰를 나타냄 서브 스키마로 불림
개념 스키마 (Conceptual Schema)	데이터베이스의 전체적인 논리적 구조 전체적인 뷰를 나타냄 개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의
내부 스키마 (Internal Schema)	물리적 저장 장치의 관점에서 보는 데이터베이스 구조 실제로 데이터베이스에 저장될 레코드의 형식을 정의하고 저장 데이터 항목의 표현방법, 내부 레코드의 물리적 순서 등을 표현

스키마의 구성

[2] 테이블(Table)

 

• 데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체
• 하나의 DB 내에 여러 개의 테이블로 구성될 수 있고, 릴레이션(Relation) 혹은 엔터티(Entity)라고도 불림

테이블의 용어

용어	설명
튜플(Tuple) / 행(Row)	테이블 내의 행을 의미하며 레코드(Record)라고도 함 튜플은 테이블에서 같은 값을 가질 수 없음
속성(Attribute) / 열(Column)	테이블 내의열을 의미 열의 개수를 차수(Degree)라고 함
카디널리티(Cardinality)	튜플(Tuple)의 개수
차수(Degree)	속성(Attribute)의 개수
도메인(Domain)	하나의 속성이 취할 수 있는 같은 타입의 원자값 들의 집합

 

[3] 뷰(View)

 

• 논리 테이블로서 사용자에게(생성관점이 아닌 사용관점) 테이블과 동일함
• 아래 그림에서 테이블 A와 테이블 B는 물리 테이블을 의미하고 뷰 C는 두개의 테이블을 이용하여 생성한 뷰를 의미함

뷰의 개념

• 뷰는 테이블 A와 같은 하나의 물리 테이블로부터 생성 가능하며, 다수의 테이블 또는 다른 뷰를 이용해 만들 수 있음
• 뷰와 같은 결과를 만들기 위해 조인 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있음

** 조인(Join) : 결합을 의미하며, 관계형 데이터베이스에서의 조인은 교집합 결과를 가지는 결합 방법을 의미함

구분	장단점	설명
장점	논리적 독립성 제공	데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
	데이터 조작 연산 간소화	애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능 SELECT * FROM 뷰 이름
	보안 기능 (접근 제어) 제공	특정 필드만을 선택해 뷰를 생성할 경우 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가 중요 보안 데이터를 저장 중인 테이블이나 컬럼에는 접근 불허
단점	뷰 자체 인덱스 불가	인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못함
	뷰 변경 불가	뷰의 정의를 변경하려면 뷰를 삭제하고 재생성 뷰 정의는 ALTER 문을 이용하여 변경할 수 없음 (뷰는 CREATE 문을 사용하여 정의하고 DROP문을 사용하여 제거)
	데이터 변경 제약 존재	뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

 

[5] 인덱스(Index)

 

• 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조
• 데이터를 빠르게 찾을 수 있는 수단으로서 테이블에 대한 조회 속도를 높여주는 자료 구조
• 테이블의 특정 레코드 위치를 알려주는 용도로 사용됨

인덱스 개념

** 데이터베이스 파일 구조

 - 순차방법구조 : 레코드들의 물리적 순서가 레코드들의 논리적 순서와 같게 순차적으로 저장하는 방법

 - 인덱스방법 : 인덱스가 가리키는 주소를 따라 원하는 레코드에 접근할 수 있도록 하는 방법 <키값, 주소>의 쌍으로 구성

 - 해싱방법 : 키값을 해시 함수(Hash Function)에 대입시켜 계산한 결과를 주소로 사용하여 레코드에 접근할 수 있게 하는 방법

 - 인덱스는 파일 구조 중 인덱스 방법에 해당함

 

[5-1] 인덱스 특징

 

• 기본 키(Primary Key) 컬럼은 자동으로 인덱스가 생성됨
• 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음
• 테이블의 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색함(테이블 전체 스캔; Table Full Scan)
• 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있음(인덱스 범위 스캔; Index Range Scan)
• 조건절에 = 로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음

(3) DDL 명령어(크알드트)

구분	DDL 명령어	설명
생성	CREATE	데이터베이스 오브젝트를 생성하는 명령어
수정	ALTER	데이터베이스 오브젝트를 변경하는 명령어
삭제	DROP	데이터베이스 오브젝트를 삭제하는 명령어
	TRUNCATE	데이터베이스 오브젝트 내용을 삭제하는 명령ㅇ어

 

(4) TABLE 관련 DDL

 

[1] CREATE TABLE : 테이블을 생성하는 명령어이며, 하나의 컬럼(속성)에 대해 '컬럼명 데이터타입 제약조건'으로 구성됨

 

• CREATE TABLE 기본 문법

-- 구조, 쿼리문은 대소문자를 가리지 않아서 소문자로 작성해도 됨
CREATE TABLE 테이블명
(
	컬럼명 데이터타입 [DEFAULT 값][NOT NULL]
	[, PRIMARY KEY(기본키, ...)]
	[, UNIQUE(컬럼명, ...)]
	[, FOREIGN KEY(외래키, ...)] REFERENCES 참조테이블(기본키)
	[, CONSTRAINT 제약조건명 CHECK(조건식)]
);

-- 예제
CREATE TABLE 사원
(
	사번 VARCHAR(10) NOT NULL,
	부서번호 VARCHAR(20),
	생년월일 DATE NOT NULL,
	성별 CHAR(1),
	PRIMARY KEY(사번),
	FOREIGN KEY(부서번호) REFERENCES 부서(부서코드),
	CONSTRAINT 성별제약 CHECK 성별 IN('M', 'F')	-- 성별컬럼은 M과 F값만 허용
);

 

• CREATE TABLE 제약조건

제약조건	설명
PRIMARY KEY	테이블의 기본키를 정의하며 유일하게 테이블의 각 행을 식별
FOREIGN KEY	외래키를 정의하며 참조 대상을 테이블(컬럼명)로 명시 열과 참조된 테이블의 열 사이의 외래 키 관계를 적용하고 설정
UNIQUE	테이블 내에서 얻은 유일한 값을 갖도록 하는 제약 조건
NOT NULL	해당 컬럼은 NULL 값을 포함하지 않도록 하는 제약 조건
CHECK	개발자가 정의하는 제약조건이며 참(TRUE)이어야 하는 조건을 지정
DEFAULT	데이터를 INSERT할 때 컬럼의 값을 넣지 않는 경우 기본값으로 설정해 주는 제약조건

 

[2] ALTER TABLE : 테이블을 수정하는 명령

 

[2-1] ALTER TABLE 컬럼 추가

 

• 테이블에 필요한 컬럼을 추가
• CREATE TABLE의 컬럼에 사용되는 제약조건인 PRIAMRY KEY, FOREGIN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT를 ALTER TABLE에서도 사용할 수 있음

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

-- 예제
ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE;
-- 사원 테이블의 전화번호라는 컬럼에 대해 타입이 VARCHAR(11)이면서 UNIQUE 제약조건을 걸도록 추가

 

[2-2] ALTER TABLE 컬럼 수정

 

• 테이블에 필요한 컬럼을 수정
• 테이블 생성을 위한 CREATE 문에 제약조건을 명시 후에 ALTER를 통해 테이블 제약조건의 변경이 가능

-- ALTER TABLE 컬럼 수정 문법
ALTER TABLE 테이블명 ALTER 컬럼명 데이터타입 [제약조건];

-- 예제
ALTER TABLE 사원 ALTER 이름 VARCHAR(30) NOT NULL;
-- 사원 테이블의 이름이라는 컬럼에 대해 타입이 VARCHAR(30)이면서 NOT NULL 제약조건을 걸도록 수정

-- 아래 처럼도 가능
-- ALTER TABLE MODIFY : 컬럼 수정 
ALTER TABLE 테이블명 MODIFY 컬럼명 타입[DEFAULT식][NOT NULL];

-- ALTER TABLE CHANGE : 컬럼명 변경
ALTER TABLE 테이블명 CHANGE 컬럼명 새컬럼명 새컬럼타입;

 

[2-3] ALTER TABLE 컬럼 삭제

 

• 테이블에 필요한 컬럼을 삭제

-- ALTER TABLE 컬럼 삭제 문법
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

-- 예제
ALTER TABLE 사원 DROP COLUMN 생년월일;
-- 사원 테이블에 생년월일이라는 컬럼을 삭제

 

[3] DROP TABLE : 테이블을 삭제하는 명령

 

• DROP TABLE 명령어의 옵션에는 CASCADE와 RESTRICT가 있으며 외래키(FORERIGN KEY)가 걸려 있을 때 해당함
• CASDADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
• RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

-- DROP TABLE 문법
DROP TABLE 테이블명 [CASCADE | RESTRICT];

-- 예제
DROP TABLE 사원; # 사원 테이블 삭제

 

[4] TRUNCATE TABLE : 테이블 내의 데이터들을 삭제하는 명령

-- TRUNCATE TABLE 문법
TRUNCATE TABLE 테이블명;

-- 예제
TRUNCATE TABLE 사원; -- 사원 테이블 내의 모든 데이터를 삭제

 

(5) VIEW 관련 DDL

 

[1] CREATE VIEW : 뷰를 생성하는 명령

-- CREATE VIEW 문법
CREATE VIEW 뷰이름 AS 조회쿼리; -- 조회쿼리 = SELECT

-- 예제 : 사원 테이블에 사번, 업무, 이름, 생년월일, 성별, 입사일이라는 컬럼이 있을 때,
-- 성별 값이 M을 가진 사번, 이름으로 생성된 사원뷰라는 이름의 뷰 생성
CREATE VIEW 사원뷰 AS SELECT 사번, 이름 FROM 사원 WHERE 성별 = 'M';

 

• VIEW 테이블의 SELECT 문에는 UNION이나 ORDER BY 절을 사용할 수 없고 컬럼명을 기술하지 않으면 SELECT문의 컬럼명이 자동으로 사용됨

** UNION : 집합 연산자로 중복 행이 제거된 쿼리 결과 집합

** ORDER BY 절 : 속성값을 정렬하고자 할 때 사용함(ASC: 오름차순, DESC: 내림차순, 생략시 오름차순 정렬)

 

[2] CREATE OR REPLACE VIEW  : 뷰를 교체하는 명령

 

• OR REPLACE라는 키워드를 추가하는 것을 제외하고는 CREATE VIEW와 사용 방법이 동일함

** VIEW는 수정 (ALTER)할 수 없으므로 삭제(DROP) 후에 다시 생성(CRATE)하거나 CREATE OR REPLACE VIEW 명령을 통해 기존 뷰를 교체해야

 

[3] DROP VIEW : 뷰를 삭제하는 명령

-- CREATE OR REPLACE VIEW 문법
CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리;

-- DROP VIEW 문법
DROP VIEW 뷰이름;

 

(5) INDEX 관련 DDL

 

[1] CREATE INDEX

 

• INDEX를 생성하는 명령
• UNIQUE는 생략 가능하고 인덱스 걸린 컬럼에 중복 값을 허용하지 않으며 복수 컬럼을 인덱스로 걸 수 있음

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

-- 예제
CREATE INDEX 사번인덱스 ON 사원(사번);
# 사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 생성

[2] ALTER INDEX

 

• INDEX를 수정하는 명령어이지만 일부 DBMS는 ALTER INDEX를 제공하지 않기 때문에 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용을 권장

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

-- 예제
ALTER INDEX 사번인덱스2 ON 사원(사번);
# 사원 테이블의 사번 컬럼에 대해 사번인덱스2라는 인덱스 명으로 인덱스 수정

 

[3] DROP INDEX : 인덱스를 삭제하는 명령

DROP INDEX 인덱스명;