데이터베이스 시스템
데이터베이스 시스템의 정의
DBS (Database System) = 데이터베이스 + DBMS + 사용자 + 데이터 언어 + 컴퓨터
┌─────────────────────────────────────────────────────────────────────┐
│ 데이터베이스 시스템 (DBS) │
├─────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ │
│ │ 사용자 │ DBA, 응용 프로그래머, 최종 사용자 │
│ └──────┬──────┘ │
│ │ 데이터 언어 (SQL) │
│ ▼ │
│ ┌─────────────────────────────────────────┐ │
│ │ DBMS │ │
│ │ ┌─────────────┐ ┌─────────────────┐ │ │
│ │ │ 질의 처리기 │ │ 저장 데이터 관리자│ │ │
│ │ └─────────────┘ └─────────────────┘ │ │
│ └──────────────────────┬──────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────┐ │
│ │ 데이터베이스 (DB) │ │
│ │ ┌──────────────┐ ┌──────────────┐ │ │
│ │ │ 사용자 데이터 │ │ 데이터 사전 │ │ │
│ │ └──────────────┘ └──────────────┘ │ │
│ └─────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────┐ │
│ │ 컴퓨터 (HW) │ │
│ └─────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────┘용어 구분
| 용어 | 정의 |
|---|---|
| 데이터베이스 (DB) | 저장된 데이터의 집합 |
| DBMS | DB를 관리하는 소프트웨어 |
| 데이터베이스 시스템 (DBS) | DB + DBMS + 사용자 + 언어 + HW 전체 |
스키마와 인스턴스
┌─────────────────────────────────────────────────────────────────┐
│ 스키마 (Schema) │
│ = 데이터 구조 + 제약조건 정의 │
│ = 정적 (거의 변하지 않음) │
├─────────────────────────────────────────────────────────────────┤
│ CREATE TABLE orders ( │
│ id INT PRIMARY KEY, -- 제약조건: 기본키 │
│ customer_id INT NOT NULL, -- 제약조건: NOT NULL │
│ amount DECIMAL(10,2) CHECK(amount > 0), -- 제약조건 │
│ status VARCHAR(20) DEFAULT 'pending' │
│ ); │
└─────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ 인스턴스 (Instance) │
│ = 특정 시점의 실제 데이터 값 │
│ = 동적 (자주 변함) │
├─────────────────────────────────────────────────────────────────┤
│ | id | customer_id | amount | status | │
│ |-----|-------------|-----------|-----------| │
│ | 1 | 101 | 50000.00 | completed | ← 인스턴스 │
│ | 2 | 102 | 30000.00 | pending | ← (실제 값) │
│ | 3 | 101 | 15000.00 | shipped | │
└─────────────────────────────────────────────────────────────────┘스키마 vs 인스턴스 비교
| 구분 | 스키마 | 인스턴스 |
|---|---|---|
| 성격 | 정적 (구조) | 동적 (값) |
| 정의 | DDL로 정의 | DML로 조작 |
| 변경 빈도 | 거의 변하지 않음 | 수시로 변경 |
| 저장 위치 | 데이터 사전 | 데이터베이스 |
3단계 데이터베이스 구조 (ANSI/SPARC)
┌─────────────────────────────────────────────────────────────────────┐
│ 외부 단계 (External Level) │
│ = 개별 사용자 관점 │
├─────────────────────────────────────────────────────────────────────┤
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ 외부 스키마 1 │ │ 외부 스키마 2 │ │ 외부 스키마 3 │ │
│ │ (인사팀 뷰) │ │ (영업팀 뷰) │ │ (고객 앱) │ │
│ └──────┬───────┘ └──────┬───────┘ └──────┬───────┘ │
│ └─────────────────┼─────────────────┘ │
│ │ │
│ ┌─────────────────┴─────────────────┐ │
│ │ 외부/개념 사상 (Application IF) │ ← 논리적 데이터 독립성 │
│ └─────────────────┬─────────────────┘ │
├───────────────────────────┼─────────────────────────────────────────┤
│ 개념 단계 (Conceptual Level) │
│ = 조직 전체 관점 │
├───────────────────────────┼─────────────────────────────────────────┤
│ ┌────────┴────────┐ │
│ │ 개념 스키마 │ ← 1개만 존재 │
│ │ (전체 DB 구조) │ │
│ └────────┬────────┘ │
│ ┌─────────────────┴─────────────────┐ │
│ │ 개념/내부 사상 (Storage IF) │ ← 물리적 데이터 독립성 │
│ └─────────────────┬─────────────────┘ │
├───────────────────────────┼─────────────────────────────────────────┤
│ 내부 단계 (Internal Level) │
│ = 저장 장치 관점 │
├───────────────────────────┼─────────────────────────────────────────┤
│ ┌────────┴────────┐ │
│ │ 내부 스키마 │ ← 1개만 존재 │
│ │ (물리적 저장구조) │ │
│ └────────┬────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ 저장 장치 (Disk) │ │
│ └─────────────────┘ │
└─────────────────────────────────────────────────────────────────────┘3단계 구조 상세
| 단계 | 스키마 | 개수 | 관점 | 정의 내용 |
|---|---|---|---|---|
| 외부 단계 | 외부 스키마 (서브 스키마) | 여러 개 | 개별 사용자 | 사용자별 필요 데이터 |
| 개념 단계 | 개념 스키마 | 1개 | 조직 전체 | 전체 DB 논리 구조, 제약조건, 보안 |
| 내부 단계 | 내부 스키마 | 1개 | 저장 장치 | 레코드 구조, 인덱스, 접근 경로 |
3단계 구조 SQL 예제
-- ═══════════════════════════════════════════════════════════════
-- 개념 스키마: 전체 테이블 정의 (DBA가 정의)
-- ═══════════════════════════════════════════════════════════════
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
ssn VARCHAR(20), -- 주민번호 (민감 정보)
salary DECIMAL(12,2), -- 급여 (민감 정보)
department VARCHAR(50),
hire_date DATE
);
-- ═══════════════════════════════════════════════════════════════
-- 외부 스키마 1: 인사팀용 (전체 정보 접근)
-- ═══════════════════════════════════════════════════════════════
CREATE VIEW hr_view AS
SELECT id, name, ssn, salary, department, hire_date
FROM employees;
-- ═══════════════════════════════════════════════════════════════
-- 외부 스키마 2: 일반 직원용 (민감 정보 숨김)
-- ═══════════════════════════════════════════════════════════════
CREATE VIEW employee_view AS
SELECT id, name, department, hire_date
FROM employees;
-- ssn, salary는 보이지 않음
-- ═══════════════════════════════════════════════════════════════
-- 외부 스키마 3: 부서별 통계용
-- ═══════════════════════════════════════════════════════════════
CREATE VIEW dept_stats_view AS
SELECT department,
COUNT(*) as emp_count,
AVG(salary) as avg_salary
FROM employees
GROUP BY department;-- ═══════════════════════════════════════════════════════════════
-- 내부 스키마 관련 (물리적 저장 구조)
-- ═══════════════════════════════════════════════════════════════
-- 인덱스 생성 (빠른 검색을 위한 접근 경로)
CREATE INDEX idx_emp_dept ON employees(department);
CREATE INDEX idx_emp_name ON employees(name);
-- 파티셔닝 (대용량 데이터 분산 저장)
-- MySQL 예시
CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);데이터 독립성
논리적 데이터 독립성
개념 스키마 변경 → 외부 스키마 영향 없음
-- 개념 스키마 변경: 컬럼명 변경
ALTER TABLE employees RENAME COLUMN name TO full_name;
-- 외부/개념 사상 수정으로 기존 View 유지 가능
CREATE OR REPLACE VIEW employee_view AS
SELECT id, full_name AS name, department, hire_date
FROM employees;
-- 기존 앱은 여전히 'name'으로 접근 가능변경 전: employees.name
변경 후: employees.full_name
외부/개념 사상이 full_name → name 으로 매핑
→ 사용자/앱은 변경 사실을 모르고 기존처럼 사용물리적 데이터 독립성
내부 스키마 변경 → 개념 스키마 영향 없음
-- 내부 스키마 변경: 인덱스 추가/삭제
CREATE INDEX idx_salary ON employees(salary);
DROP INDEX idx_emp_dept;
-- 내부 스키마 변경: 저장 위치 변경 (테이블스페이스)
ALTER TABLE employees MOVE TABLESPACE new_storage;
-- 개념 스키마는 그대로!
-- SELECT * FROM employees; -- 동일하게 동작┌────────────────────────────────────────────────────────────┐
│ 데이터 독립성 요약 │
├──────────────────────┬─────────────────────────────────────┤
│ 논리적 독립성 │ 물리적 독립성 │
├──────────────────────┼─────────────────────────────────────┤
│ 개념 → 외부 영향 없음 │ 내부 → 개념 영향 없음 │
├──────────────────────┼─────────────────────────────────────┤
│ 외부/개념 사상 │ 개념/내부 사상 │
│ (Application IF) │ (Storage IF) │
├──────────────────────┼─────────────────────────────────────┤
│ 테이블 구조 변경 시 │ 인덱스/저장방식 변경 시 │
│ 앱 수정 불필요 │ 논리 구조 수정 불필요 │
└──────────────────────┴─────────────────────────────────────┘데이터 사전 (시스템 카탈로그)
메타 데이터 (Data about Data) 저장소
┌─────────────────────────────────────────────────────────────────────┐
│ 데이터베이스 │
├────────────────────────────┬────────────────────────────────────────┤
│ 사용자 데이터베이스 │ 시스템 데이터베이스 │
├────────────────────────────┼────────────────────────────────────────┤
│ - 실제 업무 데이터 │ ┌──────────────┐ ┌────────────────┐ │
│ - 사용자가 직접 조작 │ │ 데이터 사전 │ │ 데이터 디렉터리 │ │
│ │ │ (Data Dict) │ │ (Data Dir) │ │
│ 예: customers, orders, │ └──────────────┘ └────────────────┘ │
│ products 테이블 │ 사용자 접근 O 시스템만 접근 │
│ │ (읽기 전용) (위치 정보) │
└────────────────────────────┴────────────────────────────────────────┘데이터 사전이 저장하는 정보
| 정보 유형 | 내용 |
|---|---|
| 스키마 정보 | 테이블/뷰/인덱스 구조 정의 |
| 사상 정보 | 외부/개념, 개념/내부 매핑 |
| 제약조건 | PRIMARY KEY, FOREIGN KEY, CHECK 등 |
| 사용자 정보 | 계정, 권한, 역할 |
| 통계 정보 | 테이블 크기, 행 수, 인덱스 통계 |
데이터 사전 조회 예제
-- ═══════════════════════════════════════════════════════════════
-- MySQL: information_schema
-- ═══════════════════════════════════════════════════════════════
-- 테이블 목록
SELECT table_name, table_rows, create_time
FROM information_schema.tables
WHERE table_schema = 'mydb';
-- 컬럼 정보
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'employees';
-- 제약조건 정보
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';
-- ═══════════════════════════════════════════════════════════════
-- PostgreSQL: pg_catalog
-- ═══════════════════════════════════════════════════════════════
SELECT tablename, tableowner FROM pg_catalog.pg_tables
WHERE schemaname = 'public';
SELECT attname, typname FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE attrelid = 'employees'::regclass;
-- ═══════════════════════════════════════════════════════════════
-- Oracle: 데이터 사전 뷰
-- ═══════════════════════════════════════════════════════════════
SELECT table_name, num_rows FROM user_tables;
SELECT column_name, data_type FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';
-- DBA 전용 뷰
SELECT username, account_status FROM dba_users;데이터베이스 사용자
┌─────────────────────────────────────────────────────────────────────┐
│ 데이터베이스 사용자 │
├────────────────────┬────────────────────┬───────────────────────────┤
│ DBA │ 응용 프로그래머 │ 최종 사용자 │
│ (관리자) │ (Developer) │ (End User) │
├────────────────────┼────────────────────┼───────────────────────────┤
│ - DB 설계/구축 │ - 응용 프로그램 │ ┌─────────────────────┐ │
│ - 스키마 정의 │ 개발 │ │ 캐주얼 │ 초보 │ │
│ - 보안/권한 관리 │ - SQL 임베딩 │ │ 사용자 │ 사용자 │ │
│ - 성능 튜닝 │ - API 개발 │ ├───────────┼─────────┤ │
│ - 백업/복구 │ │ │ SQL 직접 │ GUI/앱 │ │
│ - 시스템 모니터링 │ │ │ 사용 │ 통해 접근│ │
│ │ │ └───────────┴─────────┘ │
├────────────────────┼────────────────────┼───────────────────────────┤
│ DDL, DCL 주로 사용 │ DML 삽입 │ DML (간접적) │
└────────────────────┴────────────────────┴───────────────────────────┘DBA 주요 업무
| 업무 | 설명 | SQL/도구 |
|---|---|---|
| 스키마 정의 | DB 구조 설계 및 생성 | CREATE TABLE/INDEX |
| 접근 권한 관리 | 사용자별 권한 부여/회수 | GRANT, REVOKE |
| 무결성 관리 | 제약조건 정의 | PRIMARY KEY, CHECK |
| 백업/복구 | 데이터 보호 | mysqldump, pg_dump |
| 성능 튜닝 | 쿼리/인덱스 최적화 | EXPLAIN, 실행 계획 분석 |
| 모니터링 | 시스템 상태 감시 | 성능 모니터링 도구 |
-- DBA 업무 예시
-- 1. 사용자 생성 및 권한 부여
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'developer'@'localhost';
-- 2. 제약조건 정의
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- 3. 백업 (MySQL)
-- mysqldump -u root -p mydb > backup.sql
-- 4. 성능 분석
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
SHOW INDEX FROM orders;데이터 언어
┌─────────────────────────────────────────────────────────────────────┐
│ 데이터 언어 (SQL) │
├─────────────────────┬─────────────────────┬─────────────────────────┤
│ DDL │ DML │ DCL │
│ (Data Definition) │ (Data Manipulation) │ (Data Control) │
├─────────────────────┼─────────────────────┼─────────────────────────┤
│ CREATE │ SELECT │ GRANT │
│ ALTER │ INSERT │ REVOKE │
│ DROP │ UPDATE │ │
│ TRUNCATE │ DELETE │ TCL (트랜잭션 제어) │
│ │ │ COMMIT, ROLLBACK │
├─────────────────────┼─────────────────────┼─────────────────────────┤
│ 스키마 정의/수정 │ 데이터 조작 │ 보안, 무결성, 회복 │
│ 데이터 사전에 저장 │ 인스턴스 조작 │ 동시성 제어 │
└─────────────────────┴─────────────────────┴─────────────────────────┘절차적 vs 비절차적 DML
| 구분 | 절차적 DML | 비절차적 DML |
|---|---|---|
| 설명 방식 | What + How | What만 |
| 사용자 | 어떻게 처리할지 직접 명시 | DBMS에 처리 방법 위임 |
| 예시 | 저수준 API, 커서 반복 | SQL (SELECT, INSERT…) |
| 별칭 | - | 선언적 언어 (Declarative) |
-- ═══════════════════════════════════════════════════════════════
-- 비절차적 DML (선언적) - "무엇을" 원하는지만 명시
-- ═══════════════════════════════════════════════════════════════
SELECT name, salary
FROM employees
WHERE department = '개발팀'
ORDER BY salary DESC;
-- DBMS가 최적의 방법으로 처리
-- ═══════════════════════════════════════════════════════════════
-- 절차적 DML - "어떻게" 처리할지 명시 (PL/SQL, 커서)
-- ═══════════════════════════════════════════════════════════════
DECLARE
CURSOR emp_cursor IS
SELECT name, salary FROM employees WHERE department = '개발팀';
v_name employees.name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;DCL - 제어 기능
| 특성 | 설명 | 구현 방법 |
|---|---|---|
| 무결성 | 정확하고 유효한 데이터만 유지 | 제약조건, 트리거 |
| 보안 | 허가된 사용자만 접근 | GRANT, REVOKE |
| 회복 | 장애 시 일관성 유지 | 트랜잭션, 로그 |
| 동시성 | 여러 사용자 동시 접근 | 락, 트랜잭션 격리 |
-- 무결성 (제약조건)
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);
-- 보안 (권한 제어)
GRANT SELECT ON employees TO public_user;
REVOKE DELETE ON employees FROM public_user;
-- 회복/동시성 (트랜잭션)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE id = 2;
COMMIT;DBMS 구성요소
┌─────────────────────────────────────────────────────────────────────┐
│ DBMS │
├─────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 질의 처리기 (Query Processor) │ │
│ ├─────────────────────────────────────────────────────────────┤ │
│ │ │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌────────────────┐ │ │
│ │ │ DDL 컴파일러 │ │ DML 프리컴파일러│ │ DML 컴파일러 │ │ │
│ │ │ │ │ │ │ │ │ │
│ │ │ 스키마 해석 │ │ SQL 추출 │ │ 처리 요구 해석 │ │ │
│ │ └──────────────┘ └──────────────┘ └────────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────────────────────────────────────────────┐ │ │
│ │ │ 런타임 데이터베이스 처리기 │ │ │
│ │ │ (Run-time DB Processor) │ │ │
│ │ └─────────────────────────────────────────────────────┘ │ │
│ │ │ │ │
│ │ ┌─────────────────────────────────────────────────────┐ │ │
│ │ │ 트랜잭션 관리자 (Transaction Manager) │ │ │
│ │ │ - 접근 권한 검사 │ │ │
│ │ │ - 무결성 제약 확인 │ │ │
│ │ │ - 회복/병행 수행 │ │ │
│ │ └─────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────┘ │
│ │ │
│ ┌───────────────────────────▼─────────────────────────────────┐ │
│ │ 저장 데이터 관리자 (Stored Data Manager) │ │
│ │ - 디스크 데이터 관리 │ │
│ │ - 운영체제 연동 │ │
│ └─────────────────────────────────────────────────────────────┘ │
│ │ │
└──────────────────────────────┼──────────────────────────────────────┘
▼
┌─────────────────────┐
│ 데이터베이스 │
│ + 데이터 사전 │
└─────────────────────┘구성요소 역할
| 구성요소 | 역할 |
|---|---|
| DDL 컴파일러 | 스키마 정의 해석 → 데이터 사전 저장 |
| DML 프리컴파일러 | 응용 프로그램에서 SQL 추출 |
| DML 컴파일러 | SQL 해석 → 실행 계획 생성 |
| 런타임 DB 처리기 | 실제 데이터 처리 실행 |
| 트랜잭션 관리자 | 권한, 무결성, 동시성, 회복 관리 |
| 저장 데이터 관리자 | 물리적 저장소 접근 (OS 연동) |
SQL 처리 흐름 예시
사용자: SELECT * FROM employees WHERE salary > 50000;
┌─────────────────────────────────────────────────────────────────┐
│ 1. DML 컴파일러: SQL 파싱 및 분석 │
│ → 문법 검사, 테이블/컬럼 존재 확인 (데이터 사전 참조) │
└──────────────────────────────┬──────────────────────────────────┘
▼
┌─────────────────────────────────────────────────────────────────┐
│ 2. 트랜잭션 관리자: 권한 검사 │
│ → 사용자가 employees 테이블 SELECT 권한 있는지 확인 │
└──────────────────────────────┬──────────────────────────────────┘
▼
┌─────────────────────────────────────────────────────────────────┐
│ 3. 쿼리 최적화: 최적 실행 계획 수립 │
│ → 인덱스 사용 여부, 조인 순서 등 결정 │
└──────────────────────────────┬──────────────────────────────────┘
▼
┌─────────────────────────────────────────────────────────────────┐
│ 4. 런타임 DB 처리기: 실행 │
│ → 저장 데이터 관리자를 통해 디스크에서 데이터 읽기 │
└──────────────────────────────┬──────────────────────────────────┘
▼
┌─────────────────────────────────────────────────────────────────┐
│ 5. 결과 반환 │
│ → 조건(salary > 50000)에 맞는 레코드 반환 │
└─────────────────────────────────────────────────────────────────┘핵심 용어 정리
| 용어 | 설명 |
|---|---|
| DBS | 데이터베이스 시스템 (DB + DBMS + 사용자 + 언어 + HW) |
| 스키마 | 데이터 구조와 제약조건 정의 |
| 인스턴스 | 특정 시점의 실제 데이터 값 |
| 외부 스키마 | 사용자별 DB 뷰 (서브 스키마) |
| 개념 스키마 | 전체 DB 논리 구조 (1개) |
| 내부 스키마 | 물리적 저장 구조 (1개) |
| 논리적 독립성 | 개념 변경 → 외부 영향 없음 |
| 물리적 독립성 | 내부 변경 → 개념 영향 없음 |
| 데이터 사전 | 메타데이터 저장소 (시스템 카탈로그) |
| DBA | 데이터베이스 관리자 |
| DDL | 데이터 정의어 |
| DML | 데이터 조작어 (절차적/비절차적) |
| DCL | 데이터 제어어 |
| 질의 처리기 | SQL 해석 및 처리 담당 |
| 저장 데이터 관리자 | 물리적 저장소 접근 담당 |