이 시리즈는 오라클(Oracle)을 직접 다루며 SQL을 손에 익히는 실습 과정이다. 본격적으로 테이블을 만들고 쿼리를 던지기 전에, 첫 글에서는 무엇을 다루는가에 대한 토대를 잡는다.
관계형 데이터베이스가 무엇이고, 데이터를 담는 그릇인 릴레이션이 어떤 규칙 위에 서 있으며, 데이터가 망가지지 않게 지키는 제약조건이 무엇인지를 짚어야한다. 이 토대가 흔들리면 이후의 SELECT·조인·서브쿼리가 전부 모래 위 집이 된다.
여러명이 동시 사용할 수 있는, 검색이라는 목적을 가진.
1. 데이터베이스와 DBMS
- 데이터베이스: 조직체의 응용 시스템들이 공유해서 사용하는 운영 데이터(operational data)가 구조적으로 통합된 모임.
- DBMS: 데이터베이스를 정의하고, 질의어를 지원하며, 리포트를 생성하는 등의 작업을 수행하는 소프트웨어. 이 실습에서 다루는 오라클이 대표적인 DBMS다.
왜 엑셀 파일이 아니라 굳이 데이터베이스인가? 회원이 수만 명인 서비스를 떠올려 보면 차이가 분명하다. 엑셀은 한 사람이 수정 중이면 나머지는 읽기만 가능하고, 파일 하나가 손상되면 전체가 멈춘다. 데이터베이스는 다음 특징으로 이 한계를 넘는다.
- 여러 사용자가 동시에 같은 데이터를 사용
- 모든 데이터가 중복을 최소화하면서 통합됨
- 데이터에 관한 설명(스키마/메타데이터)까지 함께 보관
- 프로그램과 데이터 사이의 독립성 제공
- 효율적인 접근과 질의 가능
이 다섯 특징은 결국 "여럿이, 안전하게, 빠르게" 데이터를 공유하기 위한 것이다. DBMS는 사용자와 실제 저장소 사이에서 이 모든 걸 중재한다.
데이터의 중복을 완벽하게 막을 수는 없다.
2. 관계형 데이터 모델
- 모든 데이터를 동일한 구조(릴레이션, 2차원 테이블) 관점에서 논리적으로 구성한다.
- 선언적 질의어로 데이터에 접근한다. 사용자는 원하는 데이터(what)만 명시하고, 찾는 방법(how)은 명시하지 않는다.
- 링크나 포인터를 쓰지 않고, 데이터 사이의 관계를 값의 일치로 표현한다.
| 용어 | 의미 |
|---|---|
| 릴레이션(relation) | 2차원 테이블, 투플들의 집합, 애트리뷰트 값들의 집합 |
| 레코드(record) | 릴레이션의 각 행 |
| 투플(tuple) | 레코드를 더 공식적으로 부르는 용어 |
| 애트리뷰트(attribute) | 릴레이션에서 이름을 가진 하나의 열 |
| 차수(degree) | 열의 수 |
| 카디날리티(cardinality) | 행(투플)의 수 |
여기서 가장 실용적으로 중요한 건 선언적 질의어라는 성격이다. 우리는 오라클에게 "첫 번째 파일을 열어 10번째 줄로 가라"고 절차를 지시하지 않는다. 그저 "나이가 20인 사람"이라는 조건만 던지면, 어떻게 찾을지는 오라클이 알아서 결정한다.
그래서 SQL을 배운다는 건 "절차를 짜는 법"이 아니라 "원하는 결과의 조건을 정확히 기술하는 법"을 익히는 일이다.
행 = 투플 = 레코드
열 = 애트리뷰트(속성) = 필드
투플, 애트리뷰트의 순서는 중요하지 않다 > 동일한 투플 존재하지 않는다 > 레코드 자체의 중복은 없다 > 그래서 PK는 중요하다
3. 릴레이션과 키
집합이라는 점에서 몇 가지 규칙이 따라온다. 동일한 투플이 두 개 이상 존재하지 않고, 투플들의 순서나 애트리뷰트들의 순서는 의미가 없으며, 한 애트리뷰트의 모든 값은 같은 유형이고, 각 투플의 애트리뷰트는 원자값(더 쪼갤 수 없는 하나의 값)을 갖는다. 이 "한 칸엔 값 하나" 규칙이 나중에 테이블 설계의 출발점이 된다.
키 (Key)
키는 투플을 고유하게 식별하기 위한 애트리뷰트(들)다.
| 키 | 설명 |
|---|---|
| 수퍼키(superkey) | 투플을 고유하게 식별하되, 불필요한 애트리뷰트가 섞여도 됨 |
| 후보키(candidate key) | 고유 식별을 만족하는 최소한의 애트리뷰트 모임 |
| 기본키(primary key) | 후보키 중 설계자/DBA가 대표로 선정한 키 |
| 대체키(alternate key) | 기본키로 뽑히지 못한 나머지 후보키 |
| 외래키(foreign key) | 다른 릴레이션의 기본키를 참조하는 애트리뷰트 |
두 개 이상의 애트리뷰트로 이뤄진 키를 복합키(composite key)라 하고, 자연스러운 기본키가 없을 때는 레코드 번호 같은 인위적 키를 추가하기도 한다. 실습에서 시퀀스(sequence)로 자동 증가 번호를 만들어 기본키로 쓰는 경우가 바로 이 인위적 키다.
릴레이션 → 동일 투플 두 개 이상 존재하지 않음(유일성) → 슈퍼키 → 최소성의 원칙 → 후보키
→ 선택 : 기본키
→ 이외 : 대체키
외래키
외래키는 다른 릴레이션의 기본키를 참조해 릴레이션 사이의 관계를 표현한다. 예를 들어 사원 테이블의 부서번호(DNO)가 부서 테이블의 부서번호(DEPTNO)를 참조하는 식이다.
이때 외래키는 참조되는 기본키와 같은 도메인을 가져야 하며, 자기 릴레이션의 기본키 일부가 될 수도 있고 아닐 수도 있다. 실습에서 두 테이블을 조인할 때, 사실상 이 외래키와 기본키의 연결을 따라가는 것이다.
참조하는 테이블의 특정 열(들)이 참조되는 테이블의 PK 값 또는 NULL.
4. 제약조건 (Constraints)
제약조건은 잘못된 데이터가 테이블에 들어가지 못하게 막는 규칙이다. 오라클에서 테이블을 만들 때 직접 선언하게 된다.
| 제약조건 | 설명 |
|---|---|
| 도메인 제약조건 | 각 애트리뷰트 값은 원자값이어야 하며, 데이터 형식·CHECK로 값 범위를 제한 |
| 기본키 제약조건 | 키 애트리뷰트는 중복 불가 + NULL 불가 (개체 무결성) |
| 참조 무결성(외래키) | 연관된 두 릴레이션의 투플 사이 일관성 유지 |
참조 무결성과 위배 시 처리 옵션
R2의 외래키가 R1의 기본키를 참조할 때, 다음 중 하나가 성립해야 참조 무결성이 지켜진다.
- 외래키 값이 R1의 어떤 투플의 기본키 값과 같거나,
- (외래키가 자기 릴레이션의 기본키 구성요소가 아니라면) NULL 값을 가진다.
문제는 참조되던 데이터가 삭제될 때다. 부서가 폐지되어 부서 테이블에서 그 행이 사라지면, 그 부서를 가리키던 사원들의 외래키는 어떻게 될까? 오라클을 포함한 DBMS는 이 상황의 처리 방식을 테이블 정의 시 선택하게 한다.
| 옵션 | 동작 |
|---|---|
| 제한(restricted) | 참조하는 투플이 남아 있으면 삭제 연산 자체를 거절 |
| 연쇄(cascade) | 참조되던 투플 삭제 시, 참조하던 투플들도 함께 삭제 |
| 널값(nullify) | 참조되던 투플 삭제 시, 참조하던 투플들의 외래키를 NULL로 |
| 디폴트값 | NULL 대신 지정된 기본값으로 채움 |
어느 옵션을 고르느냐는 업무 규칙에 달렸다. "부서가 없어져도 사원 기록은 남겨야 한다"면 널값을, "부서에 딸린 임시 데이터라 함께 지워도 된다"면 연쇄를 택한다. 제약조건은 단순한 문법이 아니라 업무의 규칙을 데이터베이스에 새기는 일이라는 점을 처음부터 의식해두면 좋다.
data type
| 자료형 | 설명 |
| VARCHAR2(길이) | 가변 길이의 문자열 데이터를 4,000바이트만큼 저장, 최소크기는 1바이트 |
| NVARCHAR2(길이) | 가변 길이의 문자열 데이터를 32.767바이트만큼 저장. VARCHAR2의 확장 |
| NUMBER(전체 자릿수, 소숫점 이하 자릿수) | NUMBER(p,s)와 같이 표기하면 소수점 이하 s 자리만큼의 자릿수를 표현 |
| DATE | 날짜 형식 저장, 세기-연-월-일-시-분-초 저장가능 |
| CHAR(길이) | 고정 길이의 문자열 데이터 2,000바이트만큼 저장, 최소크기는 1바이트 |
| BLOB | 대용량 바이너리 데이터 |
| CLOB | 대용량 텍스트 데이터 |
| JSON | JSON 데이터 파일 |
객체
dbms 내에서 데이터를 저장하고 관리하는 논리 구조로 이루어진 구성 요소
| 객체 | 설명 |
| 테이블(table) | 데이터를 저장하는 장소 |
| 인덱스(index) | 테이블의 검색 효율을 높이고자 사용 |
| 뷰(view) | 선별한 하나 또는 여러 개의 데이터를 논리적으로 연결하여 하나의 테이블처럼 사용 |
| 시퀀스(sequence) | 일련번호를 생성 |
| 시노님(synonym) | 객체의 별칭을 지정 |
| 프로시저(procedure) | 프로그래밍 연산과 기능을 수행(반환값 없음) |
| 함수(function) | 프로그래밍 연산과 기능을 수행(반환값 있음) |
| 패키지(package) | 관련한 프로시저와 함수를 보관 |
| 트리거(trigger) | 데이터와 관련한 작업의 연결이나 방지와 관련한 기능 제공 |
PL/SQL
procedural language extension to sql
데이터 관리와 복잡한 비즈니스의 기능 구현을 지원
변수, 조건문, 반복문 등 다른 프로그래밍 언어에서도 제공하는 요소를 사용하여 데이터를 관리할 수 있고, 수준 높은 데이터 관리 기능을 추가로 구현할 수 있다.
데이터양이 급격히 증가함에 따라 dbms는 데이터 저장과 관리만으로도 많은 자원을 소모하기 때문에 비즈니스 로직은 프로그래밍 언어로 구현하여 db 서버의 부하를 줄이는 추세이다.
한 걸음 더
- 오라클에서 위 옵션은
CREATE TABLE의 외래키 정의에ON DELETE CASCADE,ON DELETE SET NULL처럼 붙는다. 흥미롭게도 오라클은ON DELETE RESTRICT/ON UPDATE구문을 표준만큼 다 지원하지는 않고, 아무 옵션도 안 적으면 기본이 '제한'처럼 동작한다. DBMS마다 제약조건 문법에 미묘한 차이가 있어, 표준을 익힌 뒤 오라클 문서로 한 번 확인하는 습관이 안전하다. - 제약조건에는 식별자(이름)를 붙일 수 있다(
CONSTRAINT pk_emp PRIMARY KEY ...). 이름을 지어두면 나중에 제약조건을 비활성화하거나 삭제할 때(ALTER TABLE ... DISABLE CONSTRAINT pk_emp) 다루기 쉽고, 무엇보다 위반 시 오류 메시지에 그 이름이 찍혀 디버깅이 빨라진다. 실습 초반부터 의미 있는 제약조건 이름을 붙이는 버릇을 들이면 두고두고 도움이 된다. - 오라클에는 실습 내내 마주칠 특이한 테이블이 하나 있다. 바로
DUAL이다. 행 하나, 열 하나짜리 더미 테이블인데,SELECT 1+1 FROM DUAL처럼 "테이블과 무관한 계산이나 함수 결과를 조회"할 때 쓴다. 모든 SELECT에 FROM이 필요한 오라클 문법을 만족시키기 위한 장치라고 보면 된다. - nosql(not only sql)은 sql뿐만 아니라 다양한 데이터 제어 방식과 데이터 분산처리를 활용하는 dbms.rdb만으로 처리하기 어려운 빅데이터와 웹 플랫폼을 기반으로 한 기하급수적인 트래픽 증가에 대응하고자 등장. 트랜잭선 처리와 데이터 무결성, 일관성과도 관련. 각 방식에 따라 데이터의 엄밀함을 보장하지 않기도함. (ACID)