포스트

데이터베이스 기초: 트랜잭션·무결성과 SQL 개요

프로세스·메모리·디스크가 데이터를 다루는 흐름부터 NULL의 정확한 의미, 트랜잭션(커밋/롤백), PK·FK로 보장하는 데이터 무결성, 그리고 SQL과 실습 환경까지 데이터베이스의 토대를 정리했다.

게시 업데이트
By 이소연
21 분읽는 시간
데이터베이스 기초: 트랜잭션·무결성과 SQL 개요

Oracle SQL/DB 학습 노트 시리즈의 첫 글이다. 부트캠프에서 정리했던 노트를 다시 다듬어, 데이터베이스가 무엇이고 왜 그런 구조를 갖는지 토대부터 정리한다.

[ 목차 ]

  • 프로그램은 어떻게 실행되는가 — 디스크·프로세스·메모리
  • NULL의 정확한 의미
  • 트랜잭션 — 커밋과 롤백
  • 데이터 무결성과 키(PK·FK)
  • SQL 개요 — DBMS와 SQL의 5가지 영역
  • 실습 환경 — SQL*Plus와 계정 연결
  • 테이블과 컬럼, 그리고 실습 테이블
  • 핵심 용어 정리



ㅇ 프로그램은 어떻게 실행되는가 — 디스크·프로세스·메모리

나무 의자를 만든다고 해보자. 재료(나무), 도구(톱·망치), 작업 공간(장소)이 필요하다. 컴퓨터도 똑같다.

  • 디스크 : 저장 공간. 데이터(재료)가 물리적으로 저장되는 곳
  • 프로세스 : 작업 도구. 실행 중인 프로그램으로, CPU가 그 개수를 할당한다
  • 메모리(RAM) : 작업 공간. 실제 처리가 일어나는 곳

배달 온 나무를 그 자리에서 가공하지 않고 작업장으로 옮기듯, 데이터도 디스크에 가만히 둔 채 처리하지 않는다. 디스크의 파일을 실행하면 → 프로세스가 디스크에서 데이터를 찾아 → 메모리로 불러오고 → 메모리에서 작업한 뒤 → 다시 디스크에 저장한다.

디스크에서 일어나는 일은 결국 읽기와 쓰기(I/O) 두 가지뿐이다. 디스크를 잘게 쪼개면 블록(저장의 최소 단위)들로 나뉘고, 각 블록은 위쪽의 헤더와 아래쪽의 값으로 구성된다. 아파트에 비유하면 헤더는 호수(101호), 값은 그 안에 사는 내용이다. 데이터를 찾을 때는 주소를 보고 집을 찾아가듯 헤더를 본다.



ㅇ NULL의 정확한 의미

흔히 NULL을 “값이 없다”라고 말하지만, 정확히는 “비어 있다”가 맞다.

  • 존재하지 않는다 : 저장 공간(블록) 자체가 할당되지 않은 상태
  • NULL : 블록은 할당됐지만 그 안의 값이 비어 있는 상태

지하철 물품 보관함이 비어서 문이 열려 있는 것과 같다. 칸(블록)은 분명히 있고, “비어 있다”는 정보가 저장되어 있을 뿐이다.

그래서 숫자 0, 공백(스페이스), NULL은 전혀 다르다.

  • 0 : 숫자 데이터. 1바이트가 할당되어 저장된다
  • 공백 : 문자 데이터. 스페이스 하나마다 1바이트가 할당된다
  • NULL : 공간은 할당되지만 값이 비어 있는 상태



ㅇ 트랜잭션 — 커밋과 롤백

사용자가 DB에 무언가를 요청하면 DB는 결과를 돌려준다. 이때 DB는 두 가지를 동시에 만족시키려 한다.

  • 실시간 처리 : 사용자 요청에 즉시 피드백
  • 일괄 처리 : 작업을 모아 한 번에 처리 → 안정적이지만, 처리 시점까지 기다려야 함

여기서 “어디까지를 한 번의 작업으로 볼 것인가“의 기준이 바로 트랜잭션이다.

트랜잭션 : 데이터베이스에서 일어나는 작업의 단위. 나무를 톱질하기 시작 = 트랜잭션 시작, 의자 완성 = 트랜잭션 종료. 가공부터 완료까지가 하나의 작업이다.

  • 커밋(commit) : 트랜잭션이 정상 종료됐을 때, 변경 내용을 DB에 영구히 저장. 문서 작성 후 Ctrl+S를 누르는 것
  • 롤백(rollback) : 트랜잭션 도중 비정상 작업이 생겨 무결성이 깨졌을 때, 트랜잭션 시작 직전 시점으로 되돌리는 것

이해를 위한 예제 — 계좌 이체

원래 100원이 있던 고은 씨 계좌에 1,000억이 생겼고, 고은 씨가 원지 씨에게 10억을 보내기로 했다.

  • 상황 1 (정상) : 고은 990억, 원지 10억 → 정상 종료이므로 커밋
  • 상황 2 (비정상) : 고은 계좌에서 10억이 빠졌는데 원지 계좌에 안 들어왔다. 이대로 커밋하면 10억이 증발한다(= 무결성이 깨진다). 따라서 롤백해야 한다.

이때 얼마나 되돌릴지가 중요하다. 어제로 되돌리면 100원이 되어버린다. 정확히 트랜잭션 시작 직전(1,000억을 가진 시점)으로 되돌려야 한다.



ㅇ 데이터 무결성과 키(PK·FK)

빅데이터/AI가 고객에게 서비스를 제공하는 과정은 수집 → 저장 → 분석 → 예측 → 서비스다. 데이터베이스는 이 중 저장을 담당한다. 마트에서 사 온 물건을 냉동·냉장·실온으로 나눠 넣듯, 데이터도 특성에 맞게 분류해서 저장한다.

여기서 데이터(data)와 정보(information)를 구분해야 한다. 데이터는 가공되지 않은 원재료, 정보는 그것을 가공한 결과다. 1은 데이터, 1+1을 계산한 2는 정보다. 만약 1년 전 멈춘 낡은 데이터로 가공하면 “상장 임박” 같은 잘못된 정보가 나온다. 즉, 데이터가 가치를 가지려면 정확해야 한다. 이것이 무결성이다.

데이터베이스 : 무결성이 보장된 데이터를 모아놓은 집합. 중복을 최소화하여 무결성을 보장한다. DB의 가장 큰 적은 중복이다.

무결성은 분류로 보장한다. 땡처리 매대에서는 원하는 물건을 찾을 때까지 뒤져야 하지만, 백화점은 분류와 이정표가 있어 쉽다. 우리가 표를 그리고 제목(컬럼)을 붙이는 것도 같은 이유다.

다만 데이터베이스가 무결성을 자동으로 관리해 주지는 않는다. 그래서 키(key)라는 제약조건을 사용한다.

프라이머리 키(PRIMARY KEY, PK)

테이블을 대표하는 속성에 부여하는 키로, 두 가지 조건을 만족한다.

  • NOT NULL : 반드시 값이 있어야 한다
  • UNIQUE : 중복되면 안 된다

주민등록번호처럼 “있어야 하고(NOT NULL) 겹치면 안 되는(UNIQUE)” 속성이다. S대학 학생 테이블이라면 학번·주민번호가 둘 다 조건을 만족하는 후보키지만, 주민번호는 다른 학교 학생도 가지므로 그 테이블을 대표하기엔 학번이 더 적절하다.

PK가 없으면 무슨 문제가 생길까? 학번을 PK로 걸지 않으면 같은 학생이 연락처만 다르게 여러 줄로 들어갈 수 있다.

1
2
3

학번 100  이름 김OO  연락처 010-1111
학번 100  이름 김OO  연락처 010-2222
학번 100  이름 김OO  연락처 010-3333

이러면 이 학생의 대표 연락처가 무엇인지 알 수 없다. 데이터 중복 = 데이터 불일치 = 무결성 붕괴다. 학번을 PK로 걸면 100이 여러 줄 나오는 것 자체가 허용되지 않아 중복이 최소화된다.

정리하면, PK가 이런 특징을 “가진다”기보다, 어떤 컬럼을 PK로 지정함으로써 NULL과 중복을 걸러내도록 사용한다는 쪽이 정확하다.

포린 키(FOREIGN KEY, FK)

다른 테이블의 컬럼을 참조하는 키로, 테이블과 테이블을 연결하며 무결성을 지킨다.

부서 테이블에 10·20·30번 부서만 있는데, 사원 테이블에 “부서번호 40”인 사원을 넣으면? 물리적으로는 저장되지만 논리적으로는 잘못된 데이터다(존재하지 않는 부서). 그래서 사원 테이블의 부서번호는 부서 테이블에 실재하는 값만 쓰도록 FK로 묶는다. 참조당하는 쪽이 부모 테이블, 참조하는 쪽이 자식 테이블이다.

복합키(슈퍼키)

하나의 컬럼 - 하나의 PK가 문제를 일으킬 때가 있다. 쇼핑몰에서 장바구니에 여러 물품을 담아 한 번에 결제하는 경우, 주문번호 하나만 PK로 두면 물품마다 주문이 쪼개져 불편하다.

이럴 때 주문번호 + 물품번호를 묶어 PK로 삼는 복합키(슈퍼키)를 쓴다. 행 단위로는 (B01, A01) ≠ (B01, A02)라 중복이 없지만, 컬럼 단위로는 B01이 반복될 수 있다. 단일 PK가 유일성·최소성을 모두 만족하는 반면, 슈퍼키는 유일성은 만족하되 최소성은 포기한다. 그만큼 공간·검색 속도에서 손해를 보지만, 현실에서 꼭 필요한 트레이드오프다.



ㅇ SQL 개요 — DBMS와 SQL의 5가지 영역

DBMS(DataBase Management System)는 사용자의 요청을 받아 DB를 관리하고 처리해 돌려주는 소프트웨어다. 사용자가 DB에 직접 접근하면 복잡하니 그 사이에서 도와주는 도우미인 셈이다. Oracle, MS SQL Server, MySQL 등 종류가 많은데, 이 시리즈에서는 Oracle(실습은 11g)을 사용한다.

DB에 접근하는 유일한 방법은 SQL(Structured Query Language)이다. 서로 다른 언어를 쓰는 시스템들도 결국 DB에 접근해 데이터를 가져온다는 공통점이 있어, ANSI 협회가 그 접근 방법만이라도 표준화한 것이 SQL이다(표준 SQL-1999). 다만 표준만으로는 각 DBMS의 기능을 충분히 살리기 어려워, 회사마다 표준을 기준으로 자기 전용 SQL을 만들었다. 명령문에 차이가 있을 뿐 구조는 비슷해서, 하나의 DBMS를 잘 익히면 나머지는 쉽게 전환된다.

SQL 문은 다섯 가지 영역으로 나뉜다.

  • SELECT : 테이블에 저장된 데이터 검색
  • DML : 데이터 삽입·수정·삭제(INSERT/UPDATE/DELETE)
  • DDL : 객체(테이블 등) 생성·수정·삭제(CREATE/ALTER/DROP)
  • 트랜잭션 제어 : 커밋·롤백
  • DCL : 권한 관련

이 중 SELECT가 가장 까다롭다. 문법은 몇 줄 안 되지만 그만큼 강력해서, 이것만 잘 다뤄도 오래 쓸 수 있는 무기가 된다.



ㅇ 실습 환경 — SQL*Plus와 계정 연결

실습은 Oracle 11g Express Edition의 Run SQL Command Line(SQL*Plus)으로 진행한다. 환경이 불편하지만, 현업에서 관리자 관점으로 DB를 다루는 도구라 먼저 익혀 둔다.

처음엔 막강한 권한을 가진 system 계정(DBA)으로 접속한 뒤, 실습용 hr 계정을 풀어 전환한다.

1
2
3
4

conn system/1234        -- 시스템 계정 연결
alter user hr identified by hr;   -- hr 비밀번호 설정 (user altered)
alter user hr account unlock;     -- hr 계정 잠금 해제 (user altered)
conn hr/hr              -- 실습 계정으로 연결 (connected)

실습할 때는 반드시 hr 계정으로 접속한다. system 계정은 사용하지 않는다. ★

SQL 사용의 기본 규칙도 정리해 두자.

  • 모든 SQL 문은 세미콜론(;)으로 끝난다. ;은 “문장이 끝났다”고 알리는 문장 종결자다.
  • SQL 프롬프트(입력창)가 떠야 입력할 수 있다.

SQL*Plus는 한 번 실행한 명령문이 사라져 수정이 어렵다. 그래서 마지막 명령어가 담기는 SQL 버퍼를 메모장(afiedt.buf)으로 열어 편집하는 ED 명령을 쓴다.

  • ED : 버퍼의 명령어를 메모장으로 열어 편집. 저장 후 빠져나와야 반영된다
  • / 또는 run : 버퍼에 담긴 명령어를 실행
  • 프롬프트에서 직접 실행할 땐 ;를 붙이고, 메모장에서 편집할 땐 ;를 빼야 한다
  • 주석은 /* ... */



ㅇ 테이블과 컬럼, 그리고 실습 테이블

  • 테이블 : 데이터베이스가 데이터를 저장하는 최소 단위(표). 엑셀이 표에 데이터를 담듯 DB는 테이블에 담는다
  • 컬럼 : 테이블의 각 분류(속성). 하나의 테이블은 여러 컬럼으로 구성된다

테이블은 행과 열로 이루어진 2차원 구조이고, 각 컬럼에는 그에 맞는 데이터만 저장한다(이름 컬럼엔 이름만). 이 시리즈에서 다룰 두 실습 테이블은 다음과 같다.

departments — 부서 테이블

  • department_id : 부서 번호 (PK)
  • department_name : 부서 이름
  • manager_id : 부서장 사원번호
  • location_id : 부서가 위치한 지역번호

employees — 사원 테이블

  • employee_id : 사원 번호 (PK)
  • first_name / last_name : 이름 / 성
  • email, phone_number, hire_date, job_id, salary : 이메일·연락처·입사일·업무·급여
  • commission_pct : 보너스 비율 (없는 사원은 NULL)
  • manager_id : 나(개인)를 관리하는 사수의 사원번호 — employees 테이블의 employee_id를 자체 참조
  • department_id : 내가 근무하는 부서번호 (FK) — departments의 department_id 참조

두 테이블은 부서번호로 연결된다. 참조당하는 departments가 부모 테이블, 참조하는 employees가 자식 테이블이며, 둘은 1:다 관계다.



ㅇ 핵심 용어 정리

용어한 줄 정의
데이터베이스무결성이 보장된 데이터를 모아놓은 집합. 중복 최소화
무결성데이터의 정확성 보장. 키(제약조건)로 지정
테이블DB가 데이터를 저장하는 최소 단위(표)
컬럼테이블을 구성하는 속성(분류)
PK테이블 대표 컬럼에 부여. NOT NULL + UNIQUE
FK다른 테이블의 데이터를 참조. 테이블 간 관계 설정
트랜잭션DB에서 일어나는 작업의 단위
커밋트랜잭션 결과를 DB에 영구 저장
롤백잘못된 트랜잭션을 시작 직전으로 되돌림

다음 글에서는 본격적으로 SELECT 문의 기본기(산술연산자·ALIAS·중복 제거)를 다룬다.

CS, Database
Database SQL Oracle 트랜잭션 무결성 PK FK 트레이드오프
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.