관계대수·관계해석과 DB 설계

관계대수는 관계형 데이터베이스의 질의를 연산으로 표현하는 수학적 체계이고, SQL은 그 위에 만든 표준 언어다. SQL은 "무엇을 원하는지"만 적는 비절차적 언어, 관계대수는 "어떻게 구할지"를 연산 순서로 적는 절차적 언어라는 차이가 있다.

SQL 명령어 분류(DDL·DML·DCL)와 문법, WHERE와 HAVING 같은 시험 단골 함정은 SQL 기초에 있다.

릴레이션의 구조

관계형 모델(Codd, 1970)은 데이터베이스를 릴레이션(테이블)의 집합으로 본다. 수학적으로 릴레이션은 각 속성 도메인의 카티션 곱의 부분집합이고, 실제로는 튜플의 집합이다.

릴레이션은 집합이므로 테이블과 다른 성질이 있다.

키와 무결성 제약(개체·참조·도메인)은 SQL 기초에 있다.

관계대수

관계(테이블)를 입력받아 관계를 결과로 내놓는 연산들. 피연산자와 결과가 모두 릴레이션이므로 연산을 이어 붙여 식을 만들 수 있다.

순수 관계 연산자

조인의 종류: 조인은 카티션 곱 뒤에 선택을 한 것(R ⋈θ S = σθ(R × S))으로 정의된다.

디비전 예: 등록(학번, 과목번호) ÷ 과목(과목번호)은 과목 릴레이션에 있는 모든 과목을 등록한 학번을 돌려준다. 과목이 {C123}이면 C123을 들은 학생 전부, {C123, C124, C225}이면 세 과목을 다 들은 학생만 남는다. 「모든 ~를 한」 질의는 SQL에 바로 대응하는 연산자가 없어 이중 NOT EXISTS로 쓴다(아래 「관계해석」).

일반 집합 연산자

합집합·교집합·차집합은 두 릴레이션의 속성 수와 도메인이 같아야(합병 가능) 한다.

질의를 관계대수식으로 쓰기

조회뿐 아니라 삽입·삭제도 집합 연산으로 표현된다. 같은 질의를 여러 식으로 쓸 수 있고, 그중 비용이 낮은 식을 고르는 것이 아래의 최적화다.

두 번째 식은 조인 뒤에 거르지만, 선택을 먼저 해서 π 이름,성적 (학생 ⋈N σ 과목번호='C123' (등록))으로 바꿔도 결과가 같고 조인할 행이 줄어든다.

관계해석

관계대수가 「어떤 연산을 어떤 순서로」 적용할지 쓰는 절차적 언어라면, 관계해석(relational calculus) 은 결과가 만족할 조건만 쓰는 비절차적 언어다. 수학의 술어 논리에 기반한다.

핵심은 두 한정자다.

• ∃(존재 한정자): 조건을 만족하는 것이 적어도 하나 있다. SQL의 EXISTS다.
• ∀(전칭 한정자): 모든 것이 조건을 만족한다. SQL에는 직접 대응이 없어 「만족하지 않는 것이 없다」, 곧 NOT EXISTS ... NOT EXISTS로 쓴다.

「모든 프로젝트에 참여한 직원」은 관계대수로는 디비전, 관계해석으로는 ∀, SQL로는 이중 부정이다.

-- 참여하지 않은 프로젝트가 하나도 없는 직원
SELECT e.name
FROM employee e
WHERE NOT EXISTS (
  SELECT 1 FROM project p
  WHERE NOT EXISTS (
    SELECT 1 FROM works_on w
    WHERE w.employee_id = e.id AND w.project_id = p.id
  )
);

관계대수와 관계해석은 표현력이 같다(코드의 정리). 관계대수로 표현되는 모든 질의를 표현할 수 있는 언어를 관계적으로 완전하다고 한다. SQL은 둘을 섞은 언어다. SELECT ... FROM ... WHERE로 튜플 관계해석처럼 조건을 선언하면, DBMS가 그것을 관계대수 연산 트리로 바꿔 최적화한 뒤 실행한다(아래). 서브쿼리 문법과 NOT IN의 NULL 함정은 SQL 기초에 있다.

SQL이 실행되는 과정

DBMS는 비절차적인 SQL을 받아 관계대수 같은 절차적 표현으로 바꾼 뒤 실행한다. 관계대수가 SQL의 「내부 언어」인 셈이다.

flowchart LR
  sql["SQL 문"] --> p1["① 파싱<br/>문법·이름 검사"] --> p2["② 변환<br/>관계대수 등 내부 표현으로"] --> p3["③ 최적화<br/>실행 계획 선택 (인덱스·조인 순서)"] --> p4["④ 실행<br/>계획대로 읽고 계산"] --> res["결과"]

고른 실행 계획은 EXPLAIN으로 볼 수 있다. 같은 SQL이라도 인덱스·통계에 따라 계획이 달라지므로, 느린 쿼리는 SQL 문장보다 계획부터 확인한다.

절의 논리적 평가 순서는 작성 순서와 다르다: FROM·JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. SELECT에서 붙인 별칭을 WHERE에서 못 쓰는 이유다. 실제 실행 순서는 최적화기가 결과가 같은 범위에서 바꿀 수 있다. 파서·옵티마이저·실행기·버퍼 관리자 같은 엔진 내부 구성 요소는 DB 내부 구조에 있다.

DB 설계 단계

flowchart LR
  s1["요구사항 분석"] --> s2["개념적 설계"] --> s3["논리적 설계"] --> s4["물리적 설계"] --> s5["구현"]

같은 ERD라도 물리적 설계부터는 DBMS와 트래픽에 따라 결과가 달라진다(인덱스 설계와 쿼리 튜닝, 파티셔닝).

개념적 설계: ER 모델

ER 모델(Entity-Relationship model) 은 데이터 구조를 개체·속성·관계로 그리는 도구다. 1976년 피터 첸(Peter Chen)이 제안했고, 상위·하위 타입 같은 개념이 붙어 확장 ER 모델(EER)이 됐다. ER 다이어그램(ERD)의 쓸모는 기술보다 합의에 있다. 현업·기획자·개발자가 같은 데이터를 다르게 이해할 때 그림 하나로 맞춘다. 「주문 하나에 배송지가 여럿일 수 있다」는 사실을 테이블을 만든 뒤에 알면 비싸고, ERD 단계에서 알면 선 하나만 고치면 된다.

개체 타입은 속성으로 정의된 틀(학생(학번, 이름, 학과)), 개체 인스턴스는 값이 채워진 하나((S001, 김민선, 경영)), 개체 집합은 인스턴스의 모음이다. 테이블로 치면 스키마, 행, 테이블의 내용이다.

무엇이 개체가 되나. 요구사항 문장의 명사가 후보이고, 업무에서 관리할 필요가 있고, 인스턴스를 구별할 식별자가 있고, 인스턴스가 둘 이상이며 계속 남길 정보이고, 속성과 다른 개체와의 관계가 하나 이상 있는 것만 남긴다. 「병원에 의사가 근무하고 환자가 진료를 받는다」에서 병원은 시스템 전체라 인스턴스가 하나뿐이어서 개체가 아니고, 진료는 의사와 환자 사이의 행위라 관계로 본다(진료일·처방처럼 속성이 많아지면 개체로 올리기도 한다). 이름은 현업 용어로, 약어 없이, 단수 명사로, 모델 안에서 유일하게 짓는다. 같은 것을 「고객구좌」, 「고객계좌」 두 이름으로 부르기 시작하면 테이블도 둘이 된다.

직원의 부양가족이 전형적인 약한 개체다. 「김지우」라는 부양가족은 여럿일 수 있지만 「사번 1001의 부양가족 김지우」는 하나다. 소유 개체가 사라지면 약한 개체도 뜻이 없어 삭제가 함께 전파된다.

속성 값이 가질 수 있는 범위를 도메인이라 하고(이름은 20자 이하 문자열, 입학 연도는 2001보다 큰 정수), 나중에 타입과 CHECK 제약이 된다.

관계는 카디널리티 비율(1:1 직원 ─ 사원증, 1:N 부서 ─ 직원, N:M 학생 ─ 과목)과 참여로 적는다. 비율만으로는 「반드시 있어야 하나」가 안 보여서 각 개체가 관계에 몇 번 참여하는지를 (최소, 최대)로 적기도 한다.

flowchart LR
  prof["교수"] ---|"(0, 4)"| rel{"담당"} ---|"(1, 1)"| subj["과목"]

교수는 과목을 0~4개 맡고(안 맡는 교수도 있다: 선택적 참여), 과목은 정확히 교수 한 명이 맡는다(최소가 1 이상: 필수적 참여). 양방향으로 문장을 만들어 읽어 보면 실수가 준다. 「의사 한 명은 환자 여러 명을 진료할 수 있다」, 「환자 한 명은 반드시 의사 한 명에게 진료받는다」.

• 관계도 속성을 가진다. 성적은 학생의 것도 과목의 것도 아니고 「어떤 학생이 어떤 과목을 들었다」는 사실에 붙는다.
• 같은 두 개체 사이에 관계가 여럿일 수 있다(사람과 도시 사이의 거주·출생). 관계마다 외래 키가 하나씩 생기므로 따로 그린다.
• 차수는 참여하는 개체 수다. 둘이면 이진, 셋이면 삼진(학생·과목·교수의 강의). 자기 자신과 맺으면 순환(재귀) 관계다(직원이 직원을 관리한다).

ERD는 (1) 명사에서 개체 후보를 넉넉히 모아 거르고, (2) 동사에서 관계를 찾아 이름과 비율·필수 여부를 양방향으로 정하고, (3) 개체와 관계를 이은 골격을 만들어 중복을 지우고 되도록 이진 관계로 표현한 뒤, (4) 속성·키·제약을 단다. 실무 ERD는 첸 표기법보다 속성을 사각형 안에 나열하고 선 끝 모양으로 1·N과 필수·선택을 함께 표시하는 까마귀 발(crow's foot) 표기법을 많이 쓴다.

확장 ER: 일반화·특수화와 집단화

개체끼리 「~는 ~의 한 종류다(is-a)」 관계가 있으면 상위·하위 개체로 묶는다. 같은 계층을 어느 방향에서 찾았느냐에 따라 이름이 갈린다.

하위 개체는 상위 개체의 속성과 관계를 모두 상속하고 자기만의 것을 더한다. 사람(이름, 나이) 아래의 학생은 학번을, 교사는 담당 과목을 더 가진다. 객체 지향의 클래스 상속과 같은 발상이다. 계층에는 두 가지 제약을 단다.

집단화(aggregation) 는 관계를 하나의 개체처럼 묶어 다른 관계에 참여시킨다. ER 모델에서 관계는 개체하고만 이어지므로 「직원이 프로젝트에 참여한다」는 사실 자체에 「그 참여에 장비를 쓴다」를 붙일 수 없다. 직원 ─ 참여 ─ 프로젝트 전체를 하나의 상위 개체로 감싼 뒤 장비와 사용 관계로 잇는다. 테이블로는 참여(직원ID, 프로젝트ID)의 기본 키를 사용 테이블이 외래 키로 가져간다.

논리적 설계: ERD를 테이블로

기본 키는 유일하고, 바뀌지 않고, 늘 값이 있어야 한다. 주문 번호는 맞고, 바뀔 수 있는 전화번호·주소는 맞지 않다. 그다음 ER 요소를 규칙대로 옮긴다.

관계의 필수·선택(주문은 고객 없이 존재할 수 없지만 고객은 주문 없이 존재한다)은 외래 키의 NOT NULL 여부로 옮긴다.

-- 학생 N:M 과목, 관계 속성은 성적
CREATE TABLE enrollment (
  student_id CHAR(5) REFERENCES student(id),
  course_id  CHAR(6) REFERENCES course(id),
  grade      CHAR(2),
  PRIMARY KEY (student_id, course_id)
);
 
-- 다중값 속성: 직원의 취미
CREATE TABLE employee_hobby (
  employee_id INT REFERENCES employee(id),
  hobby       VARCHAR(30),
  PRIMARY KEY (employee_id, hobby)
);
 
-- 약한 개체: 부양가족
CREATE TABLE dependent (
  employee_id INT REFERENCES employee(id) ON DELETE CASCADE,
  name        VARCHAR(30),
  relation    VARCHAR(10),
  PRIMARY KEY (employee_id, name)
);

다중값 속성을 hobby1, hobby2, hobby3 컬럼으로 펴지 않는 이유는 컬럼 하나에 원자값 하나라는 1NF를 어기고, 네 번째 취미가 생기면 스키마를 바꿔야 하기 때문이다. 옮긴 뒤에는 정규화로 이상 현상을 없앤다(정규화와 반정규화).

판단이 필요한 경우도 있다.

• 복합 속성을 쪼갤까: 부분이 혼자서도 쓰이는지 본다. 주소를 시·구로 나누면 「구별 고객 수」를 바로 센다(속성은 쓸모 있는 가장 작은 단위로). 날짜를 연·월·일 컬럼으로 나누면 함께 있을 때만 뜻 있는 값을 흩어 놓을 뿐이라 날짜 타입 하나가 낫다.
• 유도 속성을 저장할까: 원칙은 계산이다. 저장하면 원본이 바뀔 때마다 같이 고쳐야 한다. 그래도 현재 값(고객의 현재 등급, 현주소), 집계 결과(주문 총액, 부양가족 수), 대표 값(대표 이메일)은 매번 계산하기 비싸 저장하곤 한다. 성능을 위한 반정규화이므로 갱신 책임(트리거, 애플리케이션, 배치)을 함께 정한다.

스키마와 인덱스

• 스키마: 데이터베이스의 구조(테이블, 컬럼, 타입)와 제약 조건, 관계를 기술한 메타데이터의 집합. 스키마는 자주 바뀌지 않는 「틀」이고, 어느 순간 들어 있는 실제 데이터는 인스턴스라고 한다. 스키마를 내포(intension), 인스턴스를 외연(extension) 이라고도 한다. 스키마는 DDL로 정의하고 그 정의는 데이터 사전(시스템 카탈로그)에 저장된다. MySQL에서는 스키마와 데이터베이스가 같은 말이고, PostgreSQL에서는 한 데이터베이스 안에 여러 스키마(이름공간)를 둔다.
• 인덱스: <키 값, 레코드 주소> 쌍으로 이루어진 자료구조로, 검색 속도를 높인다. 파일 구조는 크게 순차, 인덱스, 해싱 방식이 있다. 대부분의 RDBMS는 인덱스를 B-트리 계열(B+ 트리)로 구현한다(기초 자료구조의 균형 트리 참고). B+ 트리를 쓰는 이유는 DB 내부 구조, 복합 인덱스 순서와 실행 계획 읽기는 인덱스 설계와 쿼리 튜닝에 있다.

3단계 스키마와 데이터 독립성

ANSI/SPARC 구조는 스키마를 보는 관점에 따라 세 층으로 나눈다. 같은 구분을 데이터 추상화의 세 수준이라고도 부른다. 아래부터 물리 수준(어떻게 저장되나), 논리 수준(무엇을 저장하고 어떻게 엮이나), 뷰 수준(사용자가 보는 일부)이다. 아래층의 복잡함을 위층에 감추는 것이 추상화다.

층을 나누는 목적은 데이터 독립성이다. 층 사이에 매핑을 두어 아래층이 바뀌어도 위층을 고치지 않게 한다.

논리적 독립성은 물리적 독립성보다 지키기 어렵다. 컬럼을 지우거나 의미를 바꾸면 뷰로 감출 수 없는 경우가 많다.

관련 노트: SQL 기초, 정규화와 반정규화, 트랜잭션과 회복, 데이터베이스 모델, DB 내부 구조, 인덱스 설계와 쿼리 튜닝