정보처리기사 실기 - SQL 응용 개념 정리 (8~9일차)

01 데이터 베이스 기본

 

트랜잭션

- 데이터 베이스 내에서 한꺼번에 수행해야할 연산들의 집합으로 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본단위

 

트랜잭션의 특성⭐⭐⭐

- Atomitcity (원자성) : 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 성공 또는 실패되어야 하는 성질

- Consistancy(일관성) : 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질 

- Isolation (격리성) : 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야한다는 성질

- Durability(영속성): 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장되어야하는 성질

 

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

- Active - Partially Committed - committed

- Active - Failed - Aborted

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

TCL 명령어

COMMIT : 트랜잭션 확정, 트랜잭션을 메모리에 영구적으로 저장하는 명령어

ROLLBACK : 트랜잭션 취소, 트랜잭션 내역을 저장 무효화시키는 명령어

CHECKPOINT : 저장 시기 설정, ROLLBACK  을 위한 시점을 지정하는 명령어

 

병행제어 (Consistency ; 일관성 주요 기법)⭐⭐⭐ (갱현모연)

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

- 병행제어 미보장시 문제점은 갱신 손실, 현황 파악 오류, 모순성, 연쇄복귀가 있다

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

 

병행제어의 목적

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

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

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

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

 

병행제어 기법

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

 

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

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

 

회복기법(Durability; 영속성 주요 기법)⭐⭐⭐

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

 

회복 기법 주요 요소(즉각 갱신 회복 기법)

구분	설명
REDO	-데이터 베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작, 완료에 대한 기록이 있는 트랜잭션들의 작업을 재작업 하는 기법
UNDO	-데이터 베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작이 있지만, 완료 기록이 없는 트랜잭션들이 작업한 변경 내용들을 모두 취소하는 기법

 

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

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

 

테이블 관련 용어

튜플(Tuple) : 테이블 내의 행을 의미, 레코드

어트리뷰트(Attribute) : 테이블내의 열을 의미

카디널리티(Cardinality) : 튜플의 개수

차수(Degree) : 어트리뷰트의 개수

 

DDL (데이터 정의어)

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

DDL 대상	설명
도메인 (Domain)	- 하나의 속성이 가질 수 있는 원자값들의 집합 - 속성의 데이터 타입과 크기, 제약조건등의 정보
스키마 (Schema)	- 데이터 베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 External Schema - 사용자나 개발자의 관점에서 필요하는 데이터 베이스의 논리적 구조 - 사용자 뷰를 나타냄 - 서브 스키마로 불림 Conceptual Schema - 데이터 베이스의 전체적 논리 구조 -전체적인 뷰를 나타냄 - 개체간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의 Internal Schema - 물리적 저장 장치의 관점에서 보는 데이터베이스 구조 - 실제로 데이터베이스 에 저장될 레코드의 형식을 정의
테이블 (Table)	- 데이터 저장공간 - 데이터를 구성하는 필드들로 구성된 데이터의 집합체 - 하나의 엔터티 혻은 릴레이션이라 불림
뷰 (View)	- 하나 이상의 물리 테이블에서 유도되는 가상의 테이블 - 뷰는 논리 테이블 - 다수의 테이블을 이용해 논리 뷰를 생성 가능 - 논리적 데이터 독립성 제공, 데이터 조작 연산 간소화, 보안 기능, 뷰 변경 불가
인덱스(Index)	- 검색을 빠르게 하기 위한 데이터 구조 - 검색 연산의 최적화를 위해 데이터 베이스 내 값의 주소 정보로 구성된 데이터 구조

 

1. CREATE TABLE

CREATE TABLE TB_TEST(

	COLUMN1 VARCHAR(10) PRIMARY KEY, --기본설정
    COLUMN2 VARCHAR(20) FOREIGN KEY REFERENCES 부서,
    COLUMN3 VARCHAR(10) UNIQUE,
    COLUMN4 CHAR(8) NOT NULL,
    COLUMN5 CHAR(1) CHECK (COLUMN5 = 'M' OR COLUMN5 = 'F'),
    COLUMN6 DATE DEFAULT SYSDATE

)

-- PRIMARY KEY :  테이블의 기본 키 정의
-- FOREIGN KEY :  외래키를 정의, 참조 대상을 테이블로 명시
-- UNIQUE : 테이블 내에 유일한 값을 갖도록 하는 제약조건
-- NOT NULL : 해당 컬럼에  NULL  을 허용하지 않는 제약조건
-- DEFAULT : 해당 컬럼을 INSERT 할때 컬럼값을 넣지않는 경우 기본 설정
-- CHECK : 개발자가 정의하는 제약조건

 

2. ALTER TABLE 

-- 테이블 컬럼 추가
ALTER TABLE 사원 ADD 사원번호 VARCHAR(10) UNIQUE; 

-- 테이블 컬럼 수정
ALTER TABLE 사원 MODIFY 이름 VARCHAR(30) NOT NULL;

-- 테이블 컬럼 삭제
ALTER TABLE 사원 DROP COLUMN 생년월일;

 

3. DROP TABLE

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

-- 외래키가 걸려있는경우
DROP TABLE 사원[CASCADE , RESTRICT]

-- CASCADE: 참조 테이블까지 제거
-- RESTRINCT: 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

-- 테이블 데이터 삭제
TRUNCATE TABLE 사원;

4. CREATE VIEW

-- 뷰 생성
CREATE VIEW 사원뷰 AS (조회쿼리);

CREATE VIEW 사원뷰 AS
SELECT 사원명
  FROM 사원
 WHERE 성별 = 'M';
-- 뷰에서는 ORDER BY 나 UNION  사용 불가능

-- 뷰 교체
CREATE OR REPLACE VIEW 뷰이름  AS 조회쿼리;

-- 뷰 삭제
DROP VIEW 뷰이름;

5. CREATE INDEX

ALTER INDEX 인덱스명 ON 테이블명(컬럼명);

-- 인덱스 생성
CREATE INDEX 사번인덱스 ON 사원(사번);

-- 인덱스 수정
ALTER INDEX 사번인덱스 ON 사원(사번);

-- 인덱스 삭제
DROP INDEX 사번인덱스;

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

-- 특정 사용자에게 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON 테이블명 TO 사용자명;

-- 특정 사용자에게 테이블에 대한 SELECT, INSERT, UPDATE 권한 부여
GRANT SELECT, INSERT, UPDATE ON 테이블명 TO 사용자명;

-- 특정 사용자에게 데이터베이스의 모든 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON DATABASE::데이터베이스명 TO 사용자명;

-- 특정 역할에게 테이블에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON 테이블명 TO 역할명;

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

-- 특정 사용자의 테이블에 대한 SELECT 권한 회수
REVOKE SELECT ON 테이블명 FROM 사용자명;

-- 특정 사용자의 테이블에 대한 SELECT, INSERT 권한 회수
REVOKE SELECT, INSERT ON 테이블명 FROM 사용자명;

-- 특정 사용자의 데이터베이스에 대한 모든 권한 회수
REVOKE ALL PRIVILEGES ON 데이터베이스명 FROM 사용자명;

-- 특정 역할의 테이블에 대한 모든 권한 회수
REVOKE ALL PRIVILEGES ON 테이블명 FROM 역할명;