SQL 활용

(1) 데이터 정의어(DDL)

1. 데이터 정의어(DDL)이란

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

- 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들이 데이터 정의어

 

 

2. DDL의 대상

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

 

3. DDL 명령어

 

▼ CREATE 문법 예시

CREATE TABLE 사원
(
	이름 VARCHAR(10) NOT NULL,
    사번 VARCHAR(10) NOT NULL,
    생녕월일 VARCHAR(8),
    입사일 DATE,
    PRIMARY KEY(사번),
    FOREIGN KEY(업무) REFERENCES 부서(부서코드)
    CONSTRAINT 나이제한 CHECK(생년월일 < '19800101')
);

CREATE TABLE 속성

- PRIMARY KEY : 테이블의 기본 키를 정의, 유일하게 테이블의 각 행을 식별(테이블에서 하나의 데이터를 식별할 수 있는 열의 조합)

- FOREIGN KEY : 외래 키를 정의, 참조 대상을 테이블로 명시, 열과 열 사이의 외래 키 관계를 적용하고 설정( 두 테이블을 연결하는데 사용되는 키)

- UNIQUE : 테이블 내에서 얻은 유일한 값을 갖도록 하는 속성

- NOT NULL : 해당 컬럼은 NULL 값을 포함하지 않도록 하는 속성

- CHECK : 개발자가 정의하는 제약조검, 참이어야 하는 조건을 지정

- DEFAULT : 해당 필드의 기본값을 설정

 

▼ALTER 명령어

• 컬럼 추가 : ALTER TABLE 테이블명 ADD 컬럼명 데이터_타입; -> 테이블에 필요한 컬럼을 추가하는 명령어
• 컬럼 수정 : ALTER TABLE 테이블명 MODIFY 컬럼명 데이터_타입 [DEFAULT 값] [NOT NULL]; -> 기존 테이블에 존재하는 컬럼의 데이터 유형, 기본값, NOT NULL 등의 제약조건에 대한 변경
• 컬럼 삭제 : ALTER TABLE 테이블명 DROP 컬럼명; -> 테이블에 존재하는 컬럼을 삭제하는 명령어
• 컬럼명 수정 : ALTER TABLE 테이블명 RENAME COLUMN 변경 전_컬럼명 TO 변경 후_컬럼명; -> 테이블의 컬럼명을 변경하는 명령어

▼DROP 명령어

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

 

- CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션

- RESTRICT : 다른 테이블에 삭지할 테이블을 참조 중이면 제허하지 않는 옵션

 

▼TRUNCATE 명령어 -데이터 오브젝트의 내용을 삭제

TRUNCATE TABLE 테이블명;

 

(2) 관계형 데이터 모델

1. 관계형 데이터 모델이란

- 보편적인 데이터 모델로, 계층 모델과 망 모델의 복잡한 구조를 단순화시킨 모델

- 관계 모델의 대표적인 언어로는 SQL이 있다.

- 테이블 간의 관계를 기본 키와 이를 참조하는 외래 키로 표현한다.

- 테이블 간 관계를 1:1, 1:N, M:N의 관계로 목적에 맞게 표현한다. 

 

 

(3) 트랜잭션

1. 트랜잭션의 개념

- 트랜잭션은 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

 

 

2. 트랜잭션의 특징

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

 

3. 트랜잭션 연산

- 트랜잭션 연산에는 커밋과 롤백이 있다.

- Commit과 Rollback 명령어에 의해 원자성을 보장받는다.

 

• Commit : 하나의 트랜잭션이 성공적으로 끄나고, 데이터베이스가 일관성 있는 상태에 있거나 하나의 트랜잭션이 끝났을 때 사용하는 연산
• Rollback : 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산

▼ 병행 제어

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

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

 

 

※ 병행 제어 미보장 시 문제점

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

 

 

▼ 병행 제어 기법의 종류

• 로킹(Locking) : 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법, 로킹 단위가 작아지면 병행성 수준이 낮아짐
• 낙관적 검증(Optimistic Validation) : 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
• 타임 스탬프 순서(Time Stamp Ordering) : 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
• 다중버전 동시성 제어(MVCC) : 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

▼ 데이터베이스 고립화 수준

1. 고립화 수준이란

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

 

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

 

▼ 회복 기법

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

 

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

 

4. 트랜잭션의 상태 변화

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

 

5. 트랜잭션 제어(TCL)

- 커밋 : 트랜잭션 확정, 트랜잭션을 메모리에 영구적으로 저장

- 롤백 : 트랜잭션 취소, 트랜잭션 내역을 저장 무효화시킴

- 체크 포인트 : 저장시기 설정, ROLLBACK을 위한 시점을 지정

 

 

(4) 테이블(Table)

1. 테이블이란

- 테이블은 데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체이다.

- 하나의 DB 내에 여러 개의 테이블들로 구성될 수 있고, 릴레이션 혹은 엔티티(Entity)라고도 불린다. 

- 테이블에 포함된 행들은 유일해야하고 중복된 행이 존재하지 않아야 한다.

- 테이블에 포함된 행들 간에는 순서가 존재하지 않는다.

- 테이블을 구성하는 열들 간에는 순서가 존재하지 않는다. 

 

• 튜플(Tuple) / 행(Row ) : 테이블 내의 행을 의미하며 레코드라고도 함, 튜플은 릴레이션에서 같은 값을 가질 수 없음
• 애트리뷰트/열 : 테이블 내의 열을 의미, 열의 개수를 디그리라고 함
• 식별자(Identifier) : 여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 개념
• 카디널리티(Cardinality) : 튜플의 개수(행의 개수)
• 치수 : 애트리뷰트의 개수(열의 개수)
• 도메인 : 하나의 애트리뷰트(열)이 취할 수 있는 같은 타입의 원자값 들의 집합

 

(5) 데이터 사전

1. 데이터 사전의 개념

- 데이터 사전은 데이터베이스에 저장되는 테이블, 뷰, 인덱스, 접근 권한 등에 대한 정보를 저장하는 데이터베이스다. 

- 데이터 사전은 데이터의 데이터를 의미하는 메타데이터로 구성된다. 

- 사용자에게 데이터 사전은 단순 조회의 대상일 뿐이다.

- 데이터베이스 엔진을 이루는 컴파일러, 옵티마이저를 수행하는 데 필요한 정보다.

 

▷ Oracle 데이터 사전 검색

- 주로 뷰로 데이터 사전에 접근할 수 있다. 

- 뷰는 DBA_, ALL_, USER_  세 가지 영역이 있다. 

- 오라클에서는 영역 지시자 뒤에 오브젝트 명을 붙이는 형태로 뷰의 이름이 결정된다.