데이터 모델링의 이해 - 개념 논리 물리 모델과 3층 스키마 데이터 독립성 (SQLD 기본 1장)
이 장에서 배우는 것
이 책은 SQLD(SQL 개발자, 한국데이터산업진흥원 시행) 시험의 두 과목인 데이터 모델링의 이해와 SQL 기본 및 활용 범위를 따라간다. 목표는 시험 합격만이 아니다. 문제에 나오는 SQL 의 결과를 실행하기 전에 손으로 정확히 예측하는 힘을 기르는 것이 목표다. 그래서 모든 예제는 실제로 실행했고, 출력은 복사해 붙였다. 1~6장은 모델링, 7~12장은 SQL 이다.
- 데이터 모델링이 무엇을 줄이고 무엇을 드러내는 작업인지, 개념·논리·물리 세 단계가 각각 무엇을 결정하는지 정리한다.
- 외부·개념·내부 스키마의 3층 구조와 논리적·물리적 데이터 독립성을 뷰와 인덱스로 직접 확인한다.
- 이 책의 실행 환경(SQLite)과 시험이 전제하는 제품(주로 Oracle, 일부 SQL Server) 사이의 차이를 어떻게 표시하는지 약속한다.
핵심 개념
신입 개발자가 가장 먼저 받는 요청은 대개 "화면에 이 항목 하나만 더 넣어 주세요"다. 그런데 그 항목이 어느 테이블의 어느 컬럼에 있어야 하는지는 화면을 만든 사람도 모르는 경우가 많다. 데이터 모델링은 이 질문에 미리 답해 두는 작업이다. 업무에서 다루는 대상과 그 사이의 규칙을 표로 옮길 수 있는 형태로 줄이고, 줄인 결과를 누구나 같은 뜻으로 읽을 수 있게 적는 일이다.
모델링의 세 가지 성격
모델은 현실을 그대로 복사하지 않는다. 필요한 성질만 남기고(추상화), 복잡한 것을 이해할 수 있는 크기로 줄이며(단순화), 같은 그림을 보고 두 사람이 다르게 해석하지 않도록 뜻을 하나로 고정한다(명확화). 시험은 이 세 단어를 개념 문제로 자주 묻는다. 외울 때는 "무엇을 버리는가(추상화), 얼마나 줄이는가(단순화), 오해를 없애는가(명확화)"로 구분하면 헷갈리지 않는다.
모델링을 바라보는 관점도 셋으로 나눈다. 업무가 어떤 데이터와 관련 있는지 보는 데이터 관점, 업무가 무엇을 하는지 보는 프로세스 관점, 그리고 프로세스가 데이터에 어떤 영향을 주는지 보는 상관 관점이다. SQLD 의 모델링 과목은 이 가운데 데이터 관점을 주로 다룬다.
개념·논리·물리 모델
| 단계 | 결정하는 것 | 이 장 예제에서의 모습 |
|---|---|---|
| 개념 모델 | 핵심 엔터티와 그 사이의 큰 관계. 업무 담당자와 합의하는 수준 | 학생이 과목을 수강한다(학생·과목은 다대다) |
| 논리 모델 | 식별자, 속성, 관계의 차수와 선택성, 정규화. 특정 DB 제품과 무관 | 수강(학생번호, 과목코드)을 식별자로 하는 엔터티를 새로 둔다 |
| 물리 모델 | 테이블·컬럼 이름, 데이터 타입, 인덱스, 저장 방식. 제품에 맞춘다 | enroll 테이블, TEXT/INTEGER 타입, cid 인덱스 |
시험에서 "데이터베이스 제품에 독립적인 단계"를 물으면 논리 모델이다. "가장 추상화 수준이 높은 단계"는 개념 모델, "성능과 저장 공간을 고려하는 단계"는 물리 모델이다. 정규화는 논리 모델에서 하고, 인덱스와 반정규화 판단은 물리 모델에서 한다는 구분을 기억해 두면 선택지 대부분이 정리된다.
그림 · 개념·논리·물리 모델로 내려가며 정해지는 것 — 개념 모델은 학생과 과목의 다대다 관계만 적는다. 논리 모델은 관계를 수강 엔터티로 풀어 식별자를 정하고, 물리 모델은 테이블 이름·타입·인덱스를 제품에 맞춰 정한다.
3층 스키마와 데이터 독립성
ANSI/SPARC 가 정리한 3층 스키마는 같은 데이터를 세 겹으로 본다. 사용자나 응용 프로그램마다 보이는 모습이 외부 스키마, 조직 전체의 데이터와 관계를 하나로 통합한 것이 개념 스키마, 실제로 디스크에 어떻게 저장되는지가 내부 스키마다. 층을 나누는 이유는 한 층을 바꿔도 위층이 영향을 받지 않게 하려는 것이다.
- 논리적 독립성: 개념 스키마가 바뀌어도 외부 스키마(응용 프로그램이 보는 모습)는 바뀌지 않는다.
- 물리적 독립성: 내부 스키마(저장 구조, 인덱스)가 바뀌어도 개념 스키마는 바뀌지 않는다.
말로만 들으면 추상적이다. 아래에서 실제 DB 에 뷰(외부 스키마)를 만들고, 테이블에 컬럼을 더하거나(개념 스키마 변경) 인덱스를 만들어(내부 스키마 변경) 결과가 그대로인지 확인한다.
그림 · 3층 스키마와 두 가지 데이터 독립성 — 응용은 외부 스키마(뷰)만 본다. 개념 스키마에 컬럼을 더해도 뷰 결과 4행은 그대로였고(논리적 독립성), 내부 스키마에 인덱스를 더해도 실행 계획만 SCAN 에서 SEARCH 로 바뀌고 결과는 같았다(물리적 독립성).
예제 스키마
작은 학원의 수강 관리다. 학생과 과목은 다대다 관계라서, 그 사이에 수강(enroll) 테이블을 둔다. 이 구조가 왜 필요한지는 3장에서 관계를 다룰 때 다시 본다. 이 책의 모든 데이터는 설명을 위해 지어낸 가상 데이터다.
-- 1장: 작은 학원의 수강 관리 (가상 데이터)
CREATE TABLE student (
sid INTEGER PRIMARY KEY,
name TEXT NOT NULL,
phone TEXT
);
CREATE TABLE course (
cid TEXT PRIMARY KEY,
title TEXT NOT NULL,
credit INTEGER NOT NULL
);
CREATE TABLE enroll (
sid INTEGER NOT NULL REFERENCES student(sid),
cid TEXT NOT NULL REFERENCES course(cid),
enrolled_on TEXT NOT NULL,
score INTEGER,
PRIMARY KEY (sid, cid)
);
INSERT INTO student VALUES (1, '김하린', '010-1111-2222'), (2, '서준호', NULL), (3, '문가은', '010-3333-4444');
INSERT INTO course VALUES ('DB1', '데이터베이스 입문', 3), ('ST1', '통계 기초', 2), ('PY1', '파이썬', 3);
INSERT INTO enroll VALUES
(1, 'DB1', '2026-03-02', 88), (1, 'ST1', '2026-03-02', NULL),
(2, 'DB1', '2026-03-03', 72), (3, 'PY1', '2026-03-04', 95);들어 있는 행은 다음과 같다. 서준호는 전화번호가 없고, 김하린의 통계 기초 점수는 아직 입력되지 않았다(NULL).
| sid | cid | enrolled_on | score |
|---|---|---|---|
| 1 | DB1 | 2026-03-02 | 88 |
| 1 | ST1 | 2026-03-02 | NULL |
| 2 | DB1 | 2026-03-03 | 72 |
| 3 | PY1 | 2026-03-04 | 95 |
SQL과 실행 결과
모든 예제는 SQLite 3.51 명령행 도구로 실행했다. 실행 방식은 sqlite3 -bail -header -column -nullvalue NULL -init 스키마.sql :memory: 에 SQL 을 표준 입력으로 넣는 것이다. 매 예제마다 빈 메모리 DB 에서 스키마를 새로 만들므로 앞 예제의 변경이 다음 예제로 넘어가지 않는다. 출력에서 NULL 이라고 보이는 칸은 실제 NULL 이다.
외부 스키마: 뷰로 보이는 모습 정하기
수강 명단 화면은 학생 이름, 과목명, 점수만 필요하다. 이 모습을 뷰로 고정한다.
CREATE VIEW v_roster AS
SELECT s.name, c.title, e.score
FROM enroll e
JOIN student s ON s.sid = e.sid
JOIN course c ON c.cid = e.cid;
SELECT * FROM v_roster ORDER BY name, title;실행 결과:
name title score
------ ----------------- -----
김하린 데이터베이스 입문 88
김하린 통계 기초 NULL
문가은 파이썬 95
서준호 데이터베이스 입문 72논리적 독립성: 테이블이 바뀌어도 뷰는 그대로
학생에 이메일 속성을 더했다. 개념 스키마가 바뀐 것이다. 뷰를 쓰는 쪽은 아무것도 고치지 않았다.
CREATE VIEW v_roster AS
SELECT s.name, c.title, e.score
FROM enroll e
JOIN student s ON s.sid = e.sid
JOIN course c ON c.cid = e.cid;
-- 개념 스키마 변경: 학생에 이메일 속성을 더한다
ALTER TABLE student ADD COLUMN email TEXT;
UPDATE student SET email = 'harin@example.com' WHERE sid = 1;
-- 외부 스키마(뷰)를 쓰는 쪽은 아무것도 바꾸지 않았다
SELECT * FROM v_roster ORDER BY name, title;실행 결과:
name title score
------ ----------------- -----
김하린 데이터베이스 입문 88
김하린 통계 기초 NULL
문가은 파이썬 95
서준호 데이터베이스 입문 72출력이 앞 예제와 한 글자도 다르지 않다. 화면 코드는 새 컬럼이 생긴 사실을 몰라도 된다. 반대로 뷰가 쓰는 컬럼(name)을 지우거나 이름을 바꾸면 뷰가 깨진다. 독립성은 "무엇을 바꿔도 된다"가 아니라 외부 스키마가 참조하지 않는 부분의 변경을 흡수한다는 뜻이다.
물리적 독립성: 인덱스를 만들어도 SQL 과 결과는 그대로
EXPLAIN QUERY PLAN SELECT sid, score FROM enroll WHERE cid = 'DB1';
-- 내부 스키마 변경: 저장 구조(인덱스)만 더한다
CREATE INDEX ix_enroll_cid ON enroll(cid);
EXPLAIN QUERY PLAN SELECT sid, score FROM enroll WHERE cid = 'DB1';
SELECT sid, score FROM enroll WHERE cid = 'DB1' ORDER BY sid;실행 결과:
QUERY PLAN
`--SCAN enroll
QUERY PLAN
`--SEARCH enroll USING INDEX ix_enroll_cid (cid=?)
sid score
--- -----
1 88
2 72같은 SQL 인데 실행 계획이 SCAN(전체 읽기)에서 SEARCH ... USING INDEX(인덱스로 찾기)로 바뀌었다. 결과 행은 같다. 저장 구조를 바꾼 사람은 DBA 이고, SQL 을 쓴 개발자는 아무것도 고치지 않았다. 이것이 물리적 독립성이다.
표 · 스키마 층을 하나씩 바꾼 실험과 실제 결과
| 바꾼 것 | 층 | 확인한 결과 |
|---|---|---|
뷰 v_roster 를 만든다 | 외부 스키마 | 뷰 조회 4행 |
student 에 email 컬럼을 더한다 | 개념 스키마 | 같은 뷰 조회 4행, 내용 동일 (논리적 독립성) |
enroll(cid) 인덱스를 만든다 | 내부 스키마 | 실행 계획 SCAN enroll → SEARCH enroll USING INDEX ix_enroll_cid (cid=?), 결과 2행 그대로 (물리적 독립성) |
물리 모델의 흔적 보기
물리 모델에서 정한 컬럼 타입, NOT NULL, 기본 키 순서는 DB 카탈로그에 그대로 남는다.
SELECT name, type, "notnull", pk FROM pragma_table_info('enroll');실행 결과:
name type notnull pk
----------- ------- ------- --
sid INTEGER 1 1
cid TEXT 1 2
enrolled_on TEXT 1 0
score INTEGER 0 0pk 칸의 1, 2 는 복합 기본 키에서 컬럼의 순서다. 논리 모델에서 "수강은 학생과 과목으로 식별한다"고 정한 것이 물리 모델에서 PRIMARY KEY (sid, cid) 가 되었다. 이 순서가 성능에 어떤 영향을 주는지는 5장에서 본다.
표준과 구현의 차이
SQLD 문제는 특정 제품을 명시하지 않는 경우가 많지만, 함수 이름과 동작은 Oracle 을 기준으로 삼는 경우가 많고 일부는 SQL Server 와 비교한다. 이 책은 SQLite 로 실행했으므로, 동작이 갈리는 곳은 이 절에 모아 둔다. 이 절의 Oracle·SQL Server 동작은 비교 설명이며, 이 책에서 실행하지 않았다. 실행한 것은 언제나 SQLite 결과뿐이다.
| 항목 | SQLite(이 책 실행) | Oracle | SQL Server |
|---|---|---|---|
| 뷰 | 읽기 전용 뷰만. 뷰에 INSERT 하려면 트리거 필요 | 조건을 만족하면 뷰로 DML 가능 | 조건을 만족하면 뷰로 DML 가능 |
| 실행 계획 보기 | EXPLAIN QUERY PLAN | EXPLAIN PLAN FOR 후 조회 | 예상 실행 계획 표시 기능, SET SHOWPLAN_TEXT ON |
| 테이블 구조 보기 | pragma_table_info | 데이터 사전 뷰(USER_TAB_COLUMNS 등) | INFORMATION_SCHEMA.COLUMNS, sp_help |
| 데이터 타입 | 타입 친화성. 선언과 다른 값도 저장될 수 있다(2장) | 선언한 타입으로 엄격히 저장 | 선언한 타입으로 엄격히 저장 |
[구현 차이] 이 책의 예제 DB 는 파일이 아니라 메모리에 만들었다. 제품이 달라도 3층 스키마와 독립성이라는 개념은 같다. 시험은 개념을 묻고, 제품별 명령어는 묻지 않는다.
시험에서 헷갈리는 지점
아래 판단은 이 책에서 새로 만든 문장이다. 각 문장이 맞는지 먼저 판단한 뒤 해설을 읽는다.
판단 1. "인덱스를 추가하는 것은 개념 스키마의 변경이다"
틀렸다. 인덱스는 저장 구조이므로 내부 스키마의 변경이다. 위 예제에서 인덱스를 만든 뒤에도 테이블 정의와 SQL 은 그대로였다. 이 변경을 흡수하는 성질이 물리적 독립성이다.
판단 2. "논리 모델은 특정 DBMS 의 데이터 타입까지 확정한다"
틀렸다. 데이터 타입과 물리적 이름은 물리 모델에서 정한다. 논리 모델에서는 속성과 식별자, 관계와 정규화까지 정하고, 제품에 매이지 않는다.
판단 3. "외부 스키마는 사용자마다 여러 개 있을 수 있고, 개념 스키마는 DB 하나에 하나다"
맞다. 수강 명단 화면용 뷰, 성적 통계용 뷰처럼 외부 스키마는 여러 개다. 이들을 모두 품는 통합된 전체 구조가 개념 스키마이고, DB 하나에 하나다.
연습 문제
- 데이터 모델링의 성격 중 "업무 담당자와 개발자가 같은 그림을 보고 같은 뜻으로 읽게 만드는 것"은 무엇인지 쓰라.
- 다음 변경을 내부 스키마 변경과 개념 스키마 변경으로 나누라. (가) 테이블에 컬럼 추가 (나) 인덱스 생성 (다) 테이블의 저장 파일 위치 변경 (라) 새 테이블 추가
- 예제 스키마에서 과목별 수강생 수를 구하되, 수강생이 없는 과목도 0 으로 보이게 하는 SQL 을 쓰고 결과를 예측하라.
- 화면 개발자가
v_roster뷰만 쓰고 있다. DBA 가enroll.score컬럼의 이름을point로 바꾸면 화면 쪽은 영향을 받는가? 3층 스키마 용어로 설명하라. - "우리 회사의 수강생은 한 과목을 한 번만 신청할 수 있다"는 규칙은 개념·논리·물리 모델 중 어디에서 처음 드러나고, 물리 모델에서는 무엇으로 구현되는가?
정답과 해설
1. 명확화. 추상화는 필요 없는 성질을 버리는 것, 단순화는 이해할 수 있는 크기로 줄이는 것이다.
2. 내부 스키마 변경: (나), (다). 개념 스키마 변경: (가), (라). 저장 방식과 접근 경로는 내부, 데이터의 구조 자체는 개념 스키마다.
3. 과목을 기준으로 외부 조인하고, COUNT(*) 가 아니라 COUNT(e.sid) 로 센다. COUNT(*) 를 쓰면 수강생이 없는 과목도 1 이 된다(8장에서 자세히 본다). 이 데이터에서는 모든 과목에 수강생이 있어 0 은 나오지 않는다.
SELECT c.title, COUNT(e.sid) AS students
FROM course c
LEFT JOIN enroll e ON e.cid = c.cid
GROUP BY c.cid, c.title
ORDER BY c.cid;실행 결과:
title students
----------------- --------
데이터베이스 입문 2
파이썬 1
통계 기초 14. 영향을 받는다. 뷰가 참조하는 컬럼을 바꿨기 때문이다. SQLite 는 이름 변경을 뷰 정의에까지 자동으로 반영하므로 오류는 나지 않지만, 뷰의 결과 컬럼 이름이 score 에서 point 로 바뀐다. score 라는 이름으로 값을 읽던 화면은 값을 못 찾는다.
CREATE VIEW v_roster AS
SELECT s.name, c.title, e.score
FROM enroll e
JOIN student s ON s.sid = e.sid
JOIN course c ON c.cid = e.cid;
ALTER TABLE enroll RENAME COLUMN score TO point;
SELECT * FROM v_roster ORDER BY name, title;실행 결과:
name title point
------ ----------------- -----
김하린 데이터베이스 입문 88
김하린 통계 기초 NULL
문가은 파이썬 95
서준호 데이터베이스 입문 72Oracle 에서는 참조 대상이 사라진 뷰가 무효 상태가 되어 다음 조회에서 오류가 난다(비교 설명). 어느 쪽이든 논리적 독립성은 외부 스키마가 참조하지 않는 변경만 흡수한다. 이런 변경이 필요하면 뷰를 e.point AS score 로 다시 정의해 외부 스키마의 모습을 유지한다.
5. 개념 모델에서 "학생과 과목은 수강 관계로 연결된다"는 사실이 드러나고, 논리 모델에서 수강 엔터티의 식별자를 (학생번호, 과목코드)로 정하면서 "한 번만"이라는 규칙이 식별자로 표현된다. 물리 모델에서는 PRIMARY KEY (sid, cid) 제약으로 구현된다.
다음 장에서는 논리 모델의 재료인 엔터티와 속성을 나누는 기준을 다룬다.
참고 자료
- 데이터자격검정(한국데이터산업진흥원) — 시험 과목 구성과 일정 확인용. 이 책은 과목 구성만 사실 정보로 참고했다.
- SQLite EXPLAIN QUERY PLAN
- SQLite CREATE VIEW