Ch2. 정보처리기사 — 데이터베이스와 SQL 핵심

데이터베이스 개요

**데이터베이스(Database, DB)**는 여러 사용자가 공유하고 사용할 목적으로 체계적으로 통합·조직·관리되는 데이터의 집합입니다.

DBMS (Database Management System)

DBMS는 데이터베이스를 생성·관리·운영하는 소프트웨어 시스템입니다. 사용자와 데이터베이스 사이에서 중재 역할을 수행합니다.

DBMS의 기능:

정의 기능: 데이터 형식·구조·제약 조건 정의
조작 기능: 데이터 삽입·수정·삭제·검색
제어 기능: 데이터 무결성·보안·동시성 제어·회복

대표적인 DBMS: Oracle, MySQL, PostgreSQL, MS SQL Server, MariaDB

데이터 독립성

DBMS의 핵심 특성으로, 데이터 구조가 변경되어도 응용 프로그램이 영향을 받지 않는 성질입니다.

독립성 종류	설명
논리적 독립성	개념 스키마가 변경되어도 외부 스키마(응용 프로그램)에 영향 없음
물리적 독립성	내부 스키마(저장 구조)가 변경되어도 개념 스키마에 영향 없음

3단계 스키마 (ANSI/SPARC 구조)

외부 스키마 (External) — 사용자 뷰
       ↕ 논리적 독립성
개념 스키마 (Conceptual) — 전체 논리 구조
       ↕ 물리적 독립성
내부 스키마 (Internal) — 물리적 저장 구조

관계형 데이터 모델

**관계형 데이터 모델(Relational Data Model)**은 데이터를 2차원 테이블(Table/릴레이션) 형태로 표현하는 모델입니다.

릴레이션의 구성 요소

용어	설명
릴레이션(Relation)	테이블 전체
투플(Tuple)	행(Row), 레코드
속성(Attribute)	열(Column), 필드
도메인(Domain)	속성이 취할 수 있는 원자값의 집합
차수(Degree)	속성의 개수
카디널리티(Cardinality)	투플의 개수

키(Key)의 종류

키 종류	설명
슈퍼키(Super Key)	유일성을 만족하는 속성 집합
후보키(Candidate Key)	유일성 + 최소성을 만족
기본키(Primary Key, PK)	후보키 중 선택된 대표 키, NULL 불가·중복 불가
대체키(Alternate Key)	후보키 중 기본키가 아닌 나머지
외래키(Foreign Key, FK)	다른 릴레이션의 기본키를 참조하는 속성

무결성 제약 조건:

개체 무결성: 기본키는 NULL이거나 중복될 수 없음
참조 무결성: 외래키 값은 참조하는 릴레이션의 기본키 값이거나 NULL
도메인 무결성: 속성 값은 해당 도메인에 속해야 함

SQL DDL (Data Definition Language)

DDL은 데이터베이스 구조(스키마)를 정의·변경·삭제하는 명령어입니다.

CREATE — 테이블 생성

CREATE TABLE 학생 (
    학번      CHAR(8)     NOT NULL,
    이름      VARCHAR(20) NOT NULL,
    학과코드   CHAR(4),
    학년      INT         DEFAULT 1,
    입학일    DATE,
    CONSTRAINT PK_학생 PRIMARY KEY (학번),
    CONSTRAINT FK_학과 FOREIGN KEY (학과코드)
        REFERENCES 학과(학과코드)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

제약 조건(Constraint):

NOT NULL: NULL 값 불허
UNIQUE: 중복 불허 (NULL 허용)
PRIMARY KEY: 기본키 (NOT NULL + UNIQUE)
FOREIGN KEY: 외래키 참조 무결성
CHECK: 도메인 제약 조건
DEFAULT: 기본값 설정

ALTER — 테이블 구조 변경

-- 컬럼 추가
ALTER TABLE 학생 ADD 이메일 VARCHAR(50);

-- 컬럼 수정
ALTER TABLE 학생 MODIFY 이름 VARCHAR(30) NOT NULL;

-- 컬럼 삭제
ALTER TABLE 학생 DROP COLUMN 이메일;

-- 제약 조건 추가
ALTER TABLE 학생 ADD CONSTRAINT UQ_이름 UNIQUE (이름);

DROP — 테이블 삭제

-- 테이블 삭제 (참조하는 테이블 있으면 오류)
DROP TABLE 학생;

-- 참조 무결성 무시하고 삭제 (Oracle)
DROP TABLE 학생 CASCADE CONSTRAINTS;

TRUNCATE — 데이터 전체 삭제 (DDL)

-- 테이블 구조는 유지, 데이터만 전체 삭제 (롤백 불가)
TRUNCATE TABLE 학생;

TRUNCATE vs DELETE: TRUNCATE는 DDL이라 롤백 불가, 빠름. DELETE는 DML이라 롤백 가능, 조건부 삭제 가능.

SQL DML (Data Manipulation Language)

DML은 데이터를 조회·삽입·수정·삭제하는 명령어입니다.

SELECT — 데이터 조회

-- 기본 구조
SELECT 컬럼명 [, 컬럼명 ...]
FROM   테이블명
WHERE  조건
GROUP BY 그룹화_컬럼
HAVING 그룹_조건
ORDER BY 정렬_컬럼 [ASC | DESC];

실행 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

-- 예시: 학과별 평균 성적 상위 3개 조회
SELECT   학과코드, AVG(성적) AS 평균성적
FROM     수강
WHERE    성적 IS NOT NULL
GROUP BY 학과코드
HAVING   AVG(성적) >= 70
ORDER BY 평균성적 DESC
FETCH FIRST 3 ROWS ONLY; -- Oracle 12c+

조건 연산자:

연산자	예시	설명
`BETWEEN`	`성적 BETWEEN 70 AND 90`	범위 조건
`IN`	`학과코드 IN ('CS', 'EE')`	목록 포함
`LIKE`	`이름 LIKE '김%'`	패턴 매칭
`IS NULL`	`이메일 IS NULL`	NULL 확인
`EXISTS`	`WHERE EXISTS (서브쿼리)`	서브쿼리 결과 존재 여부

INSERT — 데이터 삽입

-- 단일 행 삽입
INSERT INTO 학생 (학번, 이름, 학과코드, 학년)
VALUES ('20240001', '홍길동', 'CS01', 1);

-- 서브쿼리를 이용한 다중 행 삽입
INSERT INTO 우수학생 (학번, 이름)
SELECT 학번, 이름
FROM   학생
WHERE  성적 >= 90;

UPDATE — 데이터 수정

-- 조건에 맞는 행 수정
UPDATE 학생
SET    학년 = 학년 + 1,
       수정일 = SYSDATE
WHERE  입학연도 = 2023;

DELETE — 데이터 삭제

-- 조건에 맞는 행 삭제
DELETE FROM 수강
WHERE  학번 = '20240001';

-- 전체 행 삭제 (구조 유지, 롤백 가능)
DELETE FROM 임시데이터;

SQL DCL (Data Control Language)

DCL은 데이터 접근 권한을 관리하는 명령어입니다.

GRANT — 권한 부여

-- 사용자에게 특정 권한 부여
GRANT SELECT, INSERT ON 학생 TO 사용자1;

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON 학생 TO 사용자2;

-- 권한 재부여 허용
GRANT SELECT ON 성적 TO 사용자3 WITH GRANT OPTION;

REVOKE — 권한 회수

-- 특정 권한 회수
REVOKE INSERT ON 학생 FROM 사용자1;

-- CASCADE: 재부여된 권한도 함께 회수
REVOKE SELECT ON 성적 FROM 사용자3 CASCADE;

**TCL (Transaction Control Language)**은 별도 분류로 보기도 합니다:

COMMIT: 트랜잭션 확정 (영구 저장)
ROLLBACK: 트랜잭션 취소 (이전 상태로 복원)
SAVEPOINT 이름: 중간 저장점 설정

정규화 (Normalization)

정규화는 데이터 중복을 최소화하고 데이터 무결성을 보장하기 위해 릴레이션을 더 작은 릴레이션으로 분해하는 과정입니다.

이상(Anomaly) 현상: 정규화가 되지 않으면 발생하는 문제

삽입 이상: 데이터 삽입 시 의도치 않은 데이터도 함께 삽입
삭제 이상: 데이터 삭제 시 의도치 않은 데이터도 함께 삭제
갱신 이상: 데이터 수정 시 일부만 변경되어 불일치 발생

정규화 단계

비정규형 → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF

제1정규형 (1NF):

조건: 모든 속성이 원자값(Atomic Value) — 반복 그룹 없음
예시: 전화번호 컬럼에 여러 번호가 들어있으면 1NF 위반 → 별도 행으로 분리

제2정규형 (2NF):

조건: 1NF + 부분 함수 종속 제거
기본키가 복합키일 때, 일반 속성이 기본키 전체가 아닌 일부에만 종속되는 경우 분리
예시: {학번, 과목코드} → 학번만으로 결정되는 ‘이름’ 속성은 별도 테이블로 분리

제3정규형 (3NF):

조건: 2NF + 이행적 함수 종속 제거
A → B → C 관계에서 A → C를 제거 (C를 별도 테이블로 분리)
예시: 학번 → 학과코드 → 학과명 이면, 학과코드·학과명을 별도 학과 테이블로 분리

보이스-코드 정규형 (BCNF):

조건: 3NF + 결정자이면서 후보키가 아닌 속성 제거
3NF를 만족해도 이상 현상이 발생할 수 있는 경우 처리
모든 결정자가 후보키여야 함

정규화 요약 암기법:
1NF: 원자값 → 2NF: 부분 종속 제거 → 3NF: 이행 종속 제거 → BCNF: 결정자=후보키

트랜잭션 (Transaction)

트랜잭션은 데이터베이스에서 하나의 논리적 작업 단위로, 모두 성공하거나 모두 실패해야 하는 연산의 집합입니다.

ACID 속성

속성	영문	설명
원자성	Atomicity	트랜잭션의 연산은 모두 실행되거나 모두 실행되지 않아야 함
일관성	Consistency	트랜잭션 실행 전후 데이터베이스는 일관된 상태 유지
격리성	Isolation	동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 함
지속성	Durability	성공한 트랜잭션의 결과는 영구적으로 반영

트랜잭션 상태

활동(Active) → 부분 완료(Partially Committed) → 완료(Committed)
                        ↓
                     실패(Failed) → 철회(Aborted/Rolled Back)

동시성 제어 — 잠금(Lock)

잠금 종류	읽기	쓰기
공유 잠금(Shared Lock, S)	허용	불허
배타 잠금(Exclusive Lock, X)	불허	불허

교착 상태(Deadlock): 두 트랜잭션이 서로 상대방이 점유한 자원을 기다리며 무한 대기하는 상태

예방: 타임아웃, 모든 자원 선점 획득
탐지: 대기 그래프로 사이클 탐지
회복: 트랜잭션 하나를 철회(롤백)

인덱스 (Index)

인덱스는 데이터 검색 속도를 향상시키기 위한 데이터 구조입니다. 책의 색인과 유사합니다.

인덱스 종류

종류	설명
클러스터형(Clustered)	테이블 행이 인덱스 키 순서로 물리적 저장. 테이블당 1개
비클러스터형(Non-Clustered)	별도 인덱스 구조. 테이블당 여러 개 가능
B-Tree 인덱스	가장 일반적. 범위 검색에 효율적
해시 인덱스	등호(=) 검색에 최적. 범위 검색 불가
비트맵 인덱스	카디널리티 낮은 컬럼에 효율적 (ex. 성별)

-- 인덱스 생성
CREATE INDEX IDX_학생_이름 ON 학생(이름);

-- 복합 인덱스
CREATE INDEX IDX_수강_복합 ON 수강(학번, 과목코드);

-- 인덱스 삭제
DROP INDEX IDX_학생_이름;

인덱스 주의 사항: 인덱스는 읽기 성능은 향상시키지만, 삽입·수정·삭제 시 인덱스도 갱신해야 하므로 쓰기 성능은 저하됩니다.

뷰 (View)

**뷰(View)**는 하나 이상의 테이블을 기반으로 만들어진 가상 테이블입니다. 실제 데이터를 저장하지 않고 정의(쿼리)만 저장합니다.

-- 뷰 생성
CREATE VIEW 재학생뷰 AS
SELECT 학번, 이름, 학과코드
FROM   학생
WHERE  재학상태 = '재학';

-- 뷰 조회 (일반 테이블처럼 사용)
SELECT * FROM 재학생뷰 WHERE 학과코드 = 'CS01';

-- 뷰 삭제
DROP VIEW 재학생뷰;

뷰의 장점:

보안: 민감 컬럼 은닉 (예: 급여 정보 제외)
편의성: 복잡한 쿼리를 단순하게 접근
독립성: 기본 테이블 구조 변경 시 응용 프로그램 영향 최소화

뷰의 제한:

DML(INSERT/UPDATE/DELETE)에 제약이 있음 (집계 함수, GROUP BY, DISTINCT 포함 뷰는 수정 불가)

핵심 개념 카드

정규화 단계 순서 ★★★★★ : 1NF(원자값) → 2NF(부분 종속 제거) → 3NF(이행 종속 제거) → BCNF(결정자=후보키). 암기 포인트: 1정규=원자, 2정규=부분종속, 3정규=이행종속, BCNF=결정자

ACID 속성 ★★★★★ : 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 지속성(Durability). 암기 포인트: 원일격지 — 모두 성공하거나 모두 실패해야 함이 원자성

SQL 명령어 분류 ★★★★★ : DDL(CREATE/ALTER/DROP/TRUNCATE) — DML(SELECT/INSERT/UPDATE/DELETE) — DCL(GRANT/REVOKE) — TCL(COMMIT/ROLLBACK). 암기 포인트: DDL=구조, DML=데이터, DCL=권한, TCL=트랜잭션

키 종류 ★★★★☆ : 슈퍼키(유일성) → 후보키(유일성+최소성) → 기본키(선택된 후보키, NULL불가) → 외래키(다른 릴레이션 PK 참조). 암기 포인트: 슈→후→기→외 순서로 범위가 좁아짐

인덱스 장단점 ★★★☆☆ : 읽기(SELECT) 성능↑, 쓰기(INSERT/UPDATE/DELETE) 성능↓. 암기 포인트: 인덱스는 조회용 — 잦은 쓰기 컬럼에는 부적합

실전 퀴즈

Q1. 다음 중 DDL 명령어가 아닌 것은? ① CREATE ② ALTER ③ GRANT ④ TRUNCATE

정답: ③ GRANT는 데이터 접근 권한을 부여하는 DCL 명령어입니다. DDL은 데이터 구조를 정의하는 CREATE, ALTER, DROP, TRUNCATE가 해당됩니다.

Q2. 릴레이션 정규화에서 부분 함수 종속을 제거하는 단계는?

정답: 제2정규형(2NF). 기본키가 복합키일 때, 일반 속성이 기본키 전체가 아닌 일부에만 종속되는 부분 함수 종속을 제거합니다. 이행 함수 종속을 제거하면 3NF가 됩니다.

Q3. 트랜잭션의 ACID 속성 중, 동시에 실행되는 여러 트랜잭션이 서로 영향을 주지 않아야 하는 성질은?

정답: 격리성(Isolation). 격리성이 낮으면 더티 리드(Dirty Read), 반복 불가능한 읽기(Non-Repeatable Read), 팬텀 리드(Phantom Read) 같은 문제가 발생합니다. SQL 표준은 4가지 격리 수준(READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE)을 정의합니다.

OIYO 편집부