관계 모델 - 릴레이션·튜플·속성으로 세상을 표현하기
이 장에서 배우는 것
앞 장에서는 파일로 데이터를 관리할 때 생기는 중복과 불일치 문제를 살펴봤다. 이 장에서는 그 문제를 구조적으로 막기 위해 고안된 관계 모델(relational model)의 기본 어휘를 정리한다. 표처럼 생긴 것을 그냥 "표"라고 부르지 않고 "릴레이션"이라고 부르는 이유, 그리고 릴레이션에 담긴 데이터를 묻고 답하는 가장 기초적인 방법인 관계대수(relational algebra)를 SQL 연구소의 온라인 서점 데이터로 확인한다.
- 릴레이션 스키마와 인스턴스를 구분해서 말할 수 있다.
- 도메인, 차수, 카디널리티라는 용어로 릴레이션의 구조와 상태를 설명할 수 있다.
- 표(엑셀)와 릴레이션이 어떤 점에서 다른지 근거를 들어 말할 수 있다.
- 선택·투영·조인이라는 관계대수 연산이 SQL의 어떤 구문에 대응하는지 감을 잡는다.
문제 상황
SQL 연구소 편집팀은 온라인 서점을 열기 전, 도서 목록을 엑셀 한 장에 적어 관리했다. 시간이 지나면서 다음과 같은 일이 반복됐다.
첫째, 누군가 "최근에 들어온 책이 위쪽에 오도록" 매번 손으로 정렬해서 저장했다. 정렬을 깜빡한 날에는 신간 목록을 잘못 뽑아 갔다. 데이터 자체는 그대로인데, 행의 물리적 순서에 의미를 실어버린 것이다.
둘째, 같은 책이 두 번 입력되는 일이 있었다. 엑셀은 "이 두 행은 완전히 같으니 하나는 지우라"고 알려주지 않는다. 재고 수량을 조회하면 같은 책이 두 줄로 잡혀 합계가 부풀었다.
셋째, 저자 칸에 "이서연, 박도윤"처럼 여러 이름을 쉼표로 이어 적었다. 번역서는 "무라카미 하루키(지은이), 박도윤(옮긴이)"처럼 더 복잡해졌다. 이 상태로는 "박도윤이 쓰거나 옮긴 책"을 정확히 뽑아낼 방법이 없다. 한 칸에 값을 하나만 넣는다는 약속이 지켜지지 않았기 때문이다.
세 문제 모두 "표처럼 생긴 데이터"에 대한 암묵적 기대가 서로 달라서 생긴다. 관계 모델은 이 기대를 수학적으로 못 박아, 데이터베이스가 스스로 지키게 만드는 출발점이다.
릴레이션 스키마와 인스턴스, 도메인·차수·카디널리티
관계 모델에서 "표 하나"는 릴레이션(relation) 하나에 대응한다. 릴레이션을 이야기할 때는 구조와 내용을 분리해서 말해야 한다.
릴레이션 스키마(relation schema)는 릴레이션의 이름과 속성(attribute) 목록, 그리고 각 속성이 가질 수 있는 값의 범위를 정의한 것이다. 예를 들어 book 릴레이션의 스키마는 다음과 같이 적는다.
book(id, isbn, title, publisher_id, category_id, price, published_on, pages)
이 스키마는 한 번 정하면 자주 바뀌지 않는다. 반면 릴레이션 인스턴스(relation instance)는 특정 시점에 그 스키마를 만족하는 튜플(tuple, 행)들의 집합이다. 책이 새로 입고되면 book 인스턴스에 튜플 하나가 늘고, 절판되면 하나가 줄어든다. 스키마는 설계도이고 인스턴스는 지금 창고에 쌓인 실물이라고 생각하면 된다.
속성마다 가질 수 있는 값의 집합을 도메인(domain)이라고 부른다. member.grade의 도메인은 {BASIC, SILVER, GOLD, VIP} 네 값뿐이고, book.price의 도메인은 0보다 큰 정수(원 단위)다. 도메인을 벗어난 값, 이를테면 grade에 'REGULAR'를 넣거나 price에 문자열 '25000원'을 넣는 것은 릴레이션의 정의를 어기는 일이다.
속성의 개수는 차수(degree)라고 한다. book은 속성이 8개이므로 차수 8인 릴레이션이다. 차수는 스키마에 속하는 값이라 데이터가 늘어나도 바뀌지 않는다. 반대로 튜플의 개수는 카디널리티(cardinality)라고 하며, 책이 입고되거나 절판될 때마다 바뀐다. "book 릴레이션의 차수는 8이고, 오늘 카디널리티는 4다"처럼 두 용어는 항상 짝을 지어 쓰면 헷갈리지 않는다.
릴레이션의 특성과 표(엑셀)의 차이
표 형태로 데이터를 그린다고 다 릴레이션인 것은 아니다. 릴레이션은 수학의 집합(set) 개념 위에 세워져 있어서 다음 세 가지를 항상 지킨다.
첫째, 튜플 사이에 순서가 없다. 릴레이션은 튜플의 집합이지 목록이 아니므로, 같은 튜플들을 다른 순서로 늘어놓아도 같은 릴레이션이다. "정렬해서 저장해야 최신순이 유지된다"는 앞의 문제 상황은 이 원칙을 어긴 사례다. 순서가 필요하면 정렬 기준이 될 속성(published_on 등)을 따로 두고, 조회할 때마다 그 속성으로 정렬해야 한다.
둘째, 중복 튜플이 없다. 모든 속성 값이 완전히 같은 두 행은 존재할 수 없다. 릴레이션은 집합이고, 집합은 같은 원소를 두 번 세지 않기 때문이다.
셋째, 각 칸의 값은 원자값(atomic value)이다. 하나의 칸에는 더 이상 쪼갤 필요가 없는 값 하나만 들어간다. "이서연, 박도윤"처럼 값 여러 개를 몰아넣는 것은 원자성을 어기는 것이고, 이 문제는 book_author처럼 행을 나누는 별도 릴레이션을 만들어 해결한다.
표1은 엑셀 같은 표와 릴레이션이 실무에서 갈리는 지점을 정리한 것이다.
| 특성 | 표(엑셀) | 릴레이션 |
|---|---|---|
| 행 순서 | 화면에 보이는 순서 자체가 정보 | 순서 없음, 필요하면 별도로 정렬 |
| 중복 행 | 똑같은 행이 여러 번 있어도 허용 | 완전히 같은 튜플은 존재할 수 없음 |
| 셀 안의 값 | 쉼표나 줄바꿈으로 여러 값 혼합 가능 | 칸마다 값 하나(원자값) |
| 열 이름 | 병합·생략해도 그림상 문제없음 | 속성 이름과 도메인이 스키마로 고정 |
다만 SQL로 실제 구현된 테이블은 수학적 릴레이션과 완전히 같지는 않다. SQL 테이블은 기본 키를 걸지 않으면 중복 행을 허용하고(집합이 아니라 다중집합, bag), ORDER BY 없이 조회하면 행이 나오는 순서를 보장하지 않는다. 즉 SQL은 릴레이션의 이상을 최대한 흉내 내지만, 중복을 막으려면 기본 키 제약을 직접 걸어야 한다. 기본 키의 구체적인 규칙은 다음 장에서 다룬다.
관계대수 맛보기 — 선택·투영·조인
릴레이션에서 원하는 데이터를 뽑아내는 수학적인 방법을 관계대수라고 한다. 관계대수는 릴레이션을 입력받아 릴레이션을 돌려주는 연산의 모음이며, SQL 문장은 이 연산을 조합해서 표현한 것이다. 이 장에서는 가장 기본이 되는 세 연산만 맛본다.
선택(selection, 기호 σ)은 조건을 만족하는 튜플(행)만 골라낸다. "category_id가 2인 book만"이 선택이고, SQL의 WHERE 절에 대응한다.
투영(projection, 기호 π)은 속성(열) 일부만 남긴다. "title과 price만"이 투영이고, SQL의 SELECT 뒤에 오는 열 목록에 대응한다. 수학적 투영은 결과에서 중복된 튜플을 자동으로 없애지만, SQL의 SELECT는 기본적으로 중복을 남긴다. 중복을 없애고 싶다면 DISTINCT를 붙여야 원래 투영의 정의에 가까워진다.
조인(join, 기호 ⨝)은 두 릴레이션을 공통된 값(주로 외래 키)으로 이어 붙여 하나의 릴레이션으로 만든다. book과 book_author를 book.id = book_author.book_id로 잇는 것이 조인이고, SQL의 JOIN ... ON에 대응한다.
세 연산은 순서를 바꿔 조합할 수 있다. 아래 그림은 "카테고리 2인 책의 제목과 저자 이름"을 구할 때, 먼저 선택으로 후보를 줄이고, 조인으로 저자 정보를 붙인 다음, 마지막에 투영으로 필요한 열만 남기는 흐름을 보여준다.
완성 코드
CREATE TABLE publisher (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE category (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
parent_id INTEGER REFERENCES category(id)
);
CREATE TABLE author (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT
);
CREATE TABLE book (
id INTEGER PRIMARY KEY,
isbn TEXT NOT NULL UNIQUE,
title TEXT NOT NULL,
publisher_id INTEGER NOT NULL REFERENCES publisher(id),
category_id INTEGER NOT NULL REFERENCES category(id),
price INTEGER NOT NULL,
published_on TEXT NOT NULL,
pages INTEGER
);
CREATE TABLE book_author (
book_id INTEGER NOT NULL REFERENCES book(id),
author_id INTEGER NOT NULL REFERENCES author(id),
role TEXT NOT NULL,
PRIMARY KEY (book_id, author_id)
);
INSERT INTO publisher (id, name) VALUES
(1, '한빛서가'),
(2, '디비프레스');
INSERT INTO category (id, name, parent_id) VALUES
(1, '소설', NULL),
(2, '컴퓨터', NULL),
(3, '추리소설', 1);
INSERT INTO author (id, name, country) VALUES
(1, '이서연', '대한민국'),
(2, '박도윤', '대한민국'),
(3, '무라카미 하루키', '일본');
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages) VALUES
(1, '979-11-0001', '관계형 사고', 2, 2, 25000, '2023-03-02', 412),
(2, '979-11-0002', '겨울의 밑줄', 1, 3, 15800, '2022-11-10', 298),
(3, '979-11-0003', 'SQL 연구소로 가는 길', 2, 2, 28000, '2024-01-15', 356),
(4, '979-11-0004', '밤의 목격자', 1, 3, 14200, '2021-06-20', 332);
INSERT INTO book_author (book_id, author_id, role) VALUES
(1, 1, 'AUTHOR'),
(2, 2, 'AUTHOR'),
(3, 1, 'AUTHOR'),
(3, 2, 'AUTHOR'),
(4, 3, 'AUTHOR'),
(4, 2, 'TRANSLATOR');
-- 선택(σ): category_id가 2인 책만 고른다
SELECT * FROM book
WHERE category_id = 2
ORDER BY id;
-- 투영(π): 제목과 가격만 남기고 중복을 없앤다
SELECT DISTINCT title, price FROM book
ORDER BY price;
-- 조인(⨝): 책 제목에 저자 이름과 역할을 붙인다
SELECT book.title, author.name, book_author.role
FROM book
JOIN book_author ON book.id = book_author.book_id
JOIN author ON book_author.author_id = author.id
ORDER BY book.id, book_author.author_id;
줄별 해설
publisher, category, author는 book이 참조하는 작은 릴레이션이다. category.parent_id는 자기 자신을 참조하는 REFERENCES category(id)로, "추리소설"(id 3)이 "소설"(id 1)의 하위 분류임을 나타낸다.
book의 price는 INTEGER 하나로만 받는다. '25000원'처럼 단위를 섞은 문자열을 넣지 않는 것이 이 속성의 도메인을 지키는 방법이다. pages는 NOT NULL을 붙이지 않아 값이 없을 수 있음을 열어 두었다.
book_author는 책 한 권에 저자나 번역자가 여러 명 붙는 상황을 표현하기 위한 릴레이션이다. book.title에 이름을 쉼표로 이어 적는 대신, (book_id, author_id) 조합마다 행을 하나씩 둔다. PRIMARY KEY (book_id, author_id)는 같은 책에 같은 저자가 같은 역할로 두 번 등록되는 것을 막아, 릴레이션의 "중복 튜플 없음" 성질을 실제로 지켜 준다. 기본 키의 나머지 규칙은 다음 장에서 다룬다.
첫 번째 SELECT는 선택(σ) 예시다. WHERE category_id = 2 조건에 맞는 튜플만 남기고, 열은 그대로 전부(*) 가져온다. ORDER BY id는 릴레이션 자체에는 순서가 없다는 것을 보여 주기 위해 조회 시점에 명시적으로 붙인 정렬이다.
두 번째 SELECT는 투영(π) 예시다. SELECT DISTINCT title, price는 book의 여러 속성 중 title과 price 두 개만 남기고, 그 결과에서 완전히 같은 행이 생기면 하나로 합친다. DISTINCT를 빼면 SQL은 중복을 그대로 두므로, 수학적 투영과 결과가 달라질 수 있다.
세 번째 SELECT는 조인(⨝) 예시다. book과 book_author를 book.id = book_author.book_id로 잇고, 다시 book_author와 author를 book_author.author_id = author.id로 이어, 책 제목·저자 이름·역할을 한 행에 모은다. 한 책에 저자가 둘이면(3번 책) 결과에도 두 행이 나온다. 조인은 릴레이션의 카디널리티를 늘릴 수도, 줄일 수도 있다는 점을 기억해 둔다.
실행 결과
SQL 연구소 화면이나 sqlite3 CLI에서 위 스크립트를 실행한 뒤 세 쿼리를 차례로 돌리면 다음과 같은 예시 결과가 나온다. 데이터 값 자체는 위 스크립트로 고정했으므로, 이 결과는 실제로 재현된다.
-- SELECT * FROM book WHERE category_id = 2 ORDER BY id;
id | isbn | title | publisher_id | category_id | price | published_on | pages
1 | 979-11-0001 | 관계형 사고 | 2 | 2 | 25000 | 2023-03-02 | 412
3 | 979-11-0003 | SQL 연구소로 가는 길 | 2 | 2 | 28000 | 2024-01-15 | 356
-- SELECT DISTINCT title, price FROM book ORDER BY price;
title | price
밤의 목격자 | 14200
겨울의 밑줄 | 15800
관계형 사고 | 25000
SQL 연구소로 가는 길 | 28000
-- 조인 쿼리 결과
title | name | role
관계형 사고 | 이서연 | AUTHOR
겨울의 밑줄 | 박도윤 | AUTHOR
SQL 연구소로 가는 길 | 이서연 | AUTHOR
SQL 연구소로 가는 길 | 박도윤 | AUTHOR
밤의 목격자 | 박도윤 | TRANSLATOR
밤의 목격자 | 무라카미 하루키 | AUTHOR
실무에서 자주 틀리는 것
결과 순서가 항상 같다고 가정하기
ORDER BY 없이 나온 순서를 "원래 그런 순서"로 믿고 그 위에 로직을 쌓는 실수다.
-- 틀린 코드: 방금 넣은 책이 맨 위에 나온다고 가정
SELECT * FROM book LIMIT 1;
-- 고친 코드: 정렬 기준을 명시한다
SELECT * FROM book
ORDER BY published_on DESC
LIMIT 1;
기본 키 없이 설계해 중복 튜플을 허용하기
book_author에 기본 키가 없으면 같은 책·같은 저자·같은 역할 조합이 실수로 두 번 들어가도 아무도 막지 않는다.
-- 틀린 코드
CREATE TABLE book_author (
book_id INTEGER,
author_id INTEGER,
role TEXT
);
-- 실수로 같은 행을 두 번 넣어도 오류가 나지 않는다
INSERT INTO book_author VALUES (1, 1, 'AUTHOR');
INSERT INTO book_author VALUES (1, 1, 'AUTHOR');
-- 고친 코드
CREATE TABLE book_author (
book_id INTEGER NOT NULL,
author_id INTEGER NOT NULL,
role TEXT NOT NULL,
PRIMARY KEY (book_id, author_id)
);
한 칸에 여러 값을 몰아넣기
저자가 여럿인 책을 book 한 릴레이션 안에서 해결하려다 원자값 원칙을 어기는 경우다.
-- 틀린 코드: book에 저자 이름을 몰아서 저장
CREATE TABLE book (
id INTEGER PRIMARY KEY,
title TEXT,
author_names TEXT -- '이서연,박도윤'
);
-- 고친 코드: 저자마다 한 행이 되도록 릴레이션을 분리
CREATE TABLE book_author (
book_id INTEGER NOT NULL,
author_id INTEGER NOT NULL,
role TEXT NOT NULL,
PRIMARY KEY (book_id, author_id)
);
속성 하나에 서로 다른 도메인 값을 섞어 쓰기
price처럼 숫자만 담아야 할 속성에 단위를 붙인 문자열을 섞으면, 크기 비교와 정렬이 문자열 규칙을 따르게 되어 기대와 다르게 동작한다.
-- 틀린 코드
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on)
VALUES (5, '979-11-0005', '급하게 넣은 책', 1, 1, '25000원', '2024-05-01');
-- 고친 코드: price 도메인은 숫자로만 채우고, '원' 표시는 화면에서 붙인다
INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on)
VALUES (5, '979-11-0005', '급하게 넣은 책', 1, 1, 25000, '2024-05-01');
한눈에 보기
| 연산 | 기호 | 의미 | SQL 대응 |
|---|---|---|---|
| 선택 | σ | 조건에 맞는 행만 남긴다 | WHERE |
| 투영 | π | 일부 열만 남긴다(중복 제거 포함) | SELECT 열 목록 + DISTINCT |
| 조인 | ⨝ | 두 릴레이션을 공통 값으로 잇는다 | JOIN ... ON |
| 개념 | 정의 | book 예시 |
|---|---|---|
| 스키마 | 이름과 속성·도메인의 정의 | book(id, isbn, title, ..., pages) |
| 인스턴스 | 특정 시점의 튜플 집합 | 지금 저장된 책 4권 |
| 도메인 | 속성이 가질 수 있는 값의 범위 | price는 0보다 큰 정수 |
| 차수 | 속성의 개수(고정) | 8 |
| 카디널리티 | 튜플의 개수(수시로 변함) | 4 |
연습 문제
- category 릴레이션의 스키마를 적고, 이 장에서 만든 데이터를 기준으로 차수와 카디널리티를 각각 답하라.
- book_author에 PRIMARY KEY (book_id, author_id) 제약이 없다면, 릴레이션의 어떤 성질이 깨지는지 한 문장으로 설명하라.
- "가격이 20000원 이상인 책의 제목과 가격을 가격이 낮은 순으로" 조회하는 SQL을 작성하고, 선택과 투영 중 어느 것을 먼저 적용한 것인지 설명하라.
- SQL 연구소 온라인 서점 데이터에서 book_author 릴레이션의 카디널리티가 1 늘어나는 구체적인 상황을 한 문장으로 들어라.
정답과 해설
1. category(id, name, parent_id)이며 속성이 3개이므로 차수는 3이다. 이 장 스크립트에서 '소설', '컴퓨터', '추리소설' 세 행을 넣었으므로 카디널리티는 3이다.
2. 릴레이션은 완전히 같은 튜플을 두 번 가질 수 없다는 "중복 없음" 성질이 깨진다. 기본 키가 없으면 같은 (book_id, author_id, role) 조합이 실수로 여러 번 들어가도 데이터베이스가 막지 못한다.
3. 다음과 같이 작성한다.
SELECT DISTINCT title, price FROM book
WHERE price >= 20000
ORDER BY price;
WHERE절로 가격 조건에 맞는 행을 먼저 걸러내는 선택을 적용하고, 그 결과에서 title과 price 두 열만 남기는 투영을 적용한 것이다. 결과는 예시로 '관계형 사고 | 25000', 'SQL 연구소로 가는 길 | 28000' 두 행이 나온다.
4. 기존 책에 공동 저자나 번역자를 한 명 더 등록해 book_author에 새 튜플을 삽입하면 카디널리티가 1 늘어난다. 이때 book_author의 속성 개수(차수 3)는 그대로다. 차수는 스키마에 속한 값이라 데이터가 늘어나도 바뀌지 않기 때문이다.