정보처리기사 오답 정리(11. SQL 응용)

1. 데이터베이스 기본

스키마

• 데이터베이스의 구조와 제약조건에 관한 전반적인 명세

트랜잭션

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

트랜잭션의 특성

1. 원자성(Atomicity)

• 분해가 불가능한 작업의 최소단위
• 연산 전체가 성공 또는 실패(All or Nothing)
• 하나라도 실패할 경우 전체가 취소되어야 하는 특성

2. 일관성(Consistency)

• 트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존해야 하는 특성

3. 격리성(Isolation)

• 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가능한 특성

4. 영속성(Durabillity)

• 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장하는 특성

트랜잭션 제어-TCL 명령어

1. 커밋(COMMIT)

• 트랜잭션을 메모리에 영구적으로 저장하는 명령어

2. 롤백(ROLLBACK)

• 트랜잭션 내역을 저장 무효화시키는 명령어

3. 체크포인트(CHECKPOINT)

• ROLLBACK을 위한 시점을 지정하는 명령어

병행 제어 기법의 종류

1. 로킹(Locking)

• 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
• 로킹 단위가 작아지면 데이터베이스 공유도 증가, 하지만 그에 따른 오버헤드도 증가
• 한 번에 로킹할 수 있는 단위를 로킹 단위라고 함

2. 타임 스탬프 순서(Time Stamp Ordering)

• 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션을 시작하기 전에 타임스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법

그 외에도 낙관적 검증, 다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)가 있다.

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

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

회복 기법의 종류-로그 기반 회복 기법

지연갱신 회복기법(Deferred Update)

• 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법

즉각갱신 회복기법(Immediate Update)

• 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법

그 외의 회복 기법

체크 포인트 회복기법(Checkpoint Recovery)

• 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법

그림자 페이징 회복기법(Shadow Paging Recovery)

• 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시, 이를 이용하여 복구하는 기법

DDL(Data Definition Language)

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

뷰(View)

• 논리 테이블로 사용자에게 사용 관점에서의 테이블과 동일하다.

뷰의 특징

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

1. DDL 명령어

• CREATE : 데이터베이스 오브젝트를 생성
• ALTER : 데이터베이스 오브젝트를 변경
• DROP : 데이터베이스 오브젝트를 삭제
• TRUNCATE : 데이터베이스 오브젝트의 내용을 삭제

DDL 사용 예시

ex1) CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

• 소유권자의 사용자 ID가 '홍길동'인 스키마 '대학교'를 정의

ex2) CREATE DOMAIN SEX CHAR(1)

• 정의 도메인 이름은 'SEX'이고 문자형임. 문자형의 크기를 1로 지정

DEFAULT '남'

• 지정 속성의 기본값을 '남'으로 지정

CONSTRAINT VALID -SEX CHECK(VALUE IN('남', '여'));

• SEX를 지정한 속성성에는 '남', '여' 중 하나밖에 안 됨

ex3) CREATE TABLE 학생

• <학생> 테이블 생성

(이름 VARCHAR(15) NOT NULL,

• '이름'의 최대 문자 크기는 15, NULL값이 존재하지 않음

학번 CHAR(8),

• '학번' 속성은 문자 8 자

전공 CHAR(5),

• '전공' 속성은 문자 5자

성별 SEX,

• '성별' 속성은 'SEX', 도메인을 자료형으로 사용한 형태

생년월일 DATE,

• '생년월일' 속성은 DATE 자료형을 갖는다.

PRIMARY KEY(학번)

• '학번'을 기본키로 삼는다.

FOREIGN KEY(전공) REFERENCES 학과(학과코드)

• '전공' 속성은 <학과> 테이블의 '학과코드'속성을 참조하는 외래키임

ON DELETE SET NULL,

• <학과> 테이블 튜플이 삭제되면 관련 튜플의 모든 '전공' 속성값을 NULL로 함

ON UPDATE CASCADE.

• <학과> 테이블 튜플 갱신 시 관련 튜플의 모든 ' 전공' 속성값도 같은 값으로

CONTRAINT 생년월일제약

• 제약조건의 이름을 '생년월일제약'으로 지정

CHECK(생년월일>='1980-01-01'));

• 1980-01-01 이후의 값만을 저장할 수 있음

VIEW의 생성

CREATE VIEW 뷰명(속성들)

AS SELECT구문 : 뷰와 짝꿍임

FROM

WHERE

INDEX 생성

CREATE [UNIQUE] INDEX 인덱스명

ON 테이블명(속성명 ASC|DESC) : 인덱스와 짝꿍임

ALTER TABLE

추가를 위해서는 ADD, 변경을 위해서는 ALTER

ALTER TABLE 테이블명 ADD 속성명 데이터_타입

ALTER TABLE 테이블명 ALTER 속성명

DROP TABLE

제거할 요소를 참조하는 다른 모든 개체를 제거하고 싶은 경우에는 CASCADE

다른 개체가 제거할 요소를 참조중일 때는 제거를 취소하고 싶은 경우에는 RESTRICT

2. DCL(Data Control Language)

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
• GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT 등이 존재

GRANT/REVOKE

GRANT [ALL ON] 사용자등급 TO 사용자 : 권한 부여

• ALL ON 항목 추가 시 다른 사람에게 권한 부여 권한까지 줌

REVOKE 사용자등급 TO 사용자 : 권한 취소

COMMIT/ROLLBACK/SAVEPOINT

• 트랜잭션이 성공적으로 완료되었다는 의미로 COMMIT 기능 설정
• SAVEPOINT 저장 후 ROLLBACK TO 명령어로 원하는 SAVEPOINT로의 이동이 가능

3. DML(Data Manipulation Language)

• 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는 데 사용되는 언어

DML의 종류

• SELECT : 테이블에서 튜플을 검색한다.
• INSERT : 테이블에 새로운 튜플을 삽입한다.
• DELETE : 테이블에서 튜플을 삭제한다.
• UPDATE : 테이블에서 튜플의 내용을 검색한다.

INSERT TO : 삽입문

INSERT INTO 테이블명(속성1,속성2,...)

VALUES (데이터1,데이터2,...)

• VALUES 대신 SELECT FROM WHERE 구문을 이용한 검색도 가능

DELETE FROM : 삭제문

DELETE

FROM 테이블명

WHERE 조건

UPDATE SET : 갱신문

UPDATE 테이블명

SET 속성명=데이터

WHERE 조건

DML 정리

• SELECT...FROM...WHERE
• INSERT...INTO...VALUES
• UPDATE...ON...WHERE
• DELETE...FROM....WHERE

2. 응용 SQL 작성하기

SELECT 심화

SELECT

WINDOW OVER (PARTITION BY 속성명1, ... ORDER BY 속성명 [ASC|DESC]) AS 별칭

FROM...WHERE

SELECT 심화 : GROUP BY

SELECT 속성명1,..., 그룹함수(속성)

• ...별 '속성'의 평균. 튜플이라면 그룹함수에 *을 붙인다.

FROM 테이블명

GROUP BY 속성

• '속성'별... 의 평균의 '... 별' 쪽의 속성 담당

3. 절차형 SQL 활용하기

프로시저

• 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
• 코딩에서의 함수를 생각하면 편할 듯?

사용자 정의함수

• 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

트리거

• 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

옵티마이저(Optimizer)

• SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진
• 사용자가 SQL로 결과를 요구하면 처리경로는 DBMS에 내장된 옵티마이저가 자동으로 생성해 준다.