데이터 모델의 이해와 식별자

SQLP는 객관식 70문항과 서술형 2문항을 180분 동안 푸는 시험입니다. 그중 1과목 「데이터 모델링의 이해」는 문항 수가 가장 적지만 버릴 수 없는 자리입니다. 3과목의 튜닝 문제가 「이 SQL이 왜 느린가」를 물을 때, 답이 SQL이 아니라 모델에 있는 경우가 적지 않기 때문입니다. 조인이 다섯 번 일어나는 이유도, 인덱스 선두 칼럼이 그것인 이유도 모델이 정합니다. 이 노트는 그 출발점 — 모델이 어떤 단계를 거쳐 내려오는지와, 그 과정에서 가장 많은 결과를 낳는 결정인 식별자를 다룹니다.

개념·논리·물리 모델

단계마다 정하는 것

데이터 모델링은 업무가 다루는 사실을 데이터의 구조로 옮겨 적는 작업이고, 그 결과물이 데이터 모델입니다. 한 번에 테이블을 그리지 않고 추상 수준이 다른 세 단계를 거쳐 내려옵니다.

여기서 엔터티는 업무가 관리하려는 대상이고, 속성은 그 대상이 갖는 더 쪼갤 수 없는 값이며, 식별자는 한 엔터티 안에서 인스턴스 하나를 다른 것과 구별해 주는 속성입니다. 셋은 나란한 세 가지가 아니라 담고 담기는 사이입니다 — 엔터티가 속성을 갖고, 그 속성 중 일부를 골라 식별자로 세웁니다.

세 단계를 굳이 나누는 이유는 되돌릴 수 있는 자리를 남기려는 것입니다. 물리 모델에서 드러난 성능 문제를 물리에서만 손보면 인덱스만 늘어나지만, 논리 모델까지 되짚으면 정규화 수준이나 식별자 구성을 바꿔 조인 자체를 없앨 수 있습니다. SQLP가 1과목과 3과목을 이어 붙이는 방식이 정확히 이것입니다.

3층 스키마

3층 스키마는 하나의 데이터베이스를 세 층으로 나눠 보는 구조입니다. 사용자마다 보는 화면인 외부 스키마, 조직 전체가 합의한 통합 구조인 개념 스키마, 실제 저장 방식과 접근 경로를 적은 내부 스키마입니다.

이 셋을 갈라 놓으면 한 층의 변화가 다른 층으로 번지지 않습니다. 개념 스키마가 바뀌어도 응용 프로그램의 뷰가 그대로면 논리적 데이터 독립성, 인덱스를 추가하거나 저장 구조를 바꿔도 개념 스키마가 그대로면 물리적 데이터 독립성입니다.

이름이 닮아 헷갈리는 자리가 하나 있습니다. 3층 스키마의 「개념 스키마」와 앞 표의 「개념 데이터 모델」은 층위가 다릅니다. 3층 스키마는 완성된 데이터베이스를 누가 보느냐로 가른 세 시선이고, 개념·논리·물리 모델은 설계가 어디까지 내려왔느냐로 가른 세 단계입니다.

세 가지 관점

모델링은 세 관점으로 업무를 봅니다. 무엇을 저장할지 보는 데이터 관점, 업무가 실제로 하는 일을 보는 프로세스 관점, 그리고 그 프로세스가 어떤 데이터를 만들고 읽고 고치고 지우는지 맞춰 보는 데이터와 프로세스의 상관 관점입니다. 마지막 것을 표로 그린 것이 CRUD 매트릭스입니다.

셋 중 시험에서 가장 자주 걸리는 것이 상관 관점입니다. 어떤 엔터티가 어느 프로세스에서도 C(생성)를 받지 못한다면 그 엔터티는 채워질 길이 없고, 반대로 R(조회)이 한 번도 없으면 아무도 쓰지 않는 데이터입니다.

모델링의 유의점

중복

같은 사실이 두 곳에 적혀 있는 상태입니다. 저장 공간이 문제가 아니라 갱신 지점이 둘이라는 것이 문제입니다. 한쪽만 고치면 그날부터 두 값이 다르고, 어느 쪽이 맞는지 데이터만 봐서는 알 수 없습니다.

비유연성

데이터의 정의가 업무 규칙과 지나치게 붙어 있어, 규칙이 조금 바뀌면 구조를 바꿔야 하는 상태입니다. 등급을 등급1_할인율·등급2_할인율처럼 칼럼으로 늘어놓은 모델이 전형입니다. 등급이 하나 늘 때마다 테이블을 고치고 그 칼럼을 읽는 SQL을 전부 고쳐야 합니다.

비일관성

데이터끼리의 연관을 명시하지 않아 서로 모순되는 값이 들어가는 상태입니다. 중복이 원인일 때가 많지만 둘은 다릅니다 — 중복은 같은 값이 두 곳에 있는 것이고, 비일관성은 그 두 값이 서로 다르게 유지되는 결과입니다.

주식별자

도출 기준

주식별자는 엔터티를 대표하는 식별자이고 네 가지를 만족해야 합니다. 인스턴스를 하나로 특정하는 유일성, 그 유일성을 만족하는 최소한의 속성만 쓰는 최소성, 값이 도중에 바뀌지 않는 불변성, 그리고 값이 반드시 있는 존재성입니다. 존재성은 물리 모델에서 기본키의 NOT NULL 제약으로 내려앉습니다.

고르는 요령은 넷입니다.

• 업무에서 자주 이용되는 속성을 고릅니다. 조회 조건으로 안 쓰이는 값을 주식별자로 세우면 인덱스가 놀게 됩니다.
• 이름·내역·설명처럼 서술형으로 적히는 속성은 피합니다. 길이가 길고 값이 바뀝니다.
• 자주 변하는 속성은 피합니다. 주식별자가 바뀌면 그것을 물려받은 자식 행까지 전부 손봐야 합니다.
• 속성이 지나치게 많으면 새로 하나를 만드는 편을 검토합니다.

외부식별자

외부식별자는 다른 엔터티에서 관계 때문에 가져온 식별자입니다. 물리 모델에서 외래키가 되고, SQL에서는 조인 조건이 됩니다. 이 노트에서 기억해야 할 한 줄은 이것입니다 — 외부식별자는 값을 새로 만드는 자리가 아니라 이미 다른 곳에서 만들어진 값이 건너오는 통로입니다.

식별자의 분류

대표성

엔터티를 대표하면 주식별자, 유일하긴 하지만 대표는 아닌 것이 보조식별자입니다. 회원 엔터티에서 회원번호가 주식별자라면 이메일은 보조식별자입니다. 보조식별자는 물리 모델에서 유니크 인덱스로 내려가 조회 경로를 하나 더 열어 줍니다.

생성 여부

엔터티 안에서 스스로 만든 것이 내부식별자, 다른 엔터티에서 온 것이 외부식별자입니다.

속성 수

한 속성으로 되어 있으면 단일식별자, 둘 이상이 모여야 유일해지면 복합식별자입니다. 복합식별자는 그대로 조인 칼럼 수가 되므로 튜닝에서 자주 문제의 출발점이 됩니다.

식별 관계와 비식별 관계

자식의 주식별자

부모의 주식별자가 자식의 주식별자 안으로 들어가면 식별 관계이고, 자식의 일반 속성으로만 남으면 비식별 관계입니다. ERD에서 식별 관계는 실선, 비식별 관계는 점선으로 그립니다.

-- 식별 관계: 부모 키가 자식 PK의 일부
CREATE TABLE 주문상세 (
  주문번호  VARCHAR(20),
  상세순번  NUMBER,
  상품코드  VARCHAR(20),
  PRIMARY KEY (주문번호, 상세순번),
  FOREIGN KEY (주문번호) REFERENCES 주문(주문번호)
);

-- 비식별 관계: 부모 키가 일반 속성
CREATE TABLE 배송지 (
  배송지번호 NUMBER PRIMARY KEY,
  주문번호   VARCHAR(20) NOT NULL,
  주소       VARCHAR(200),
  FOREIGN KEY (주문번호) REFERENCES 주문(주문번호)
);

고르는 기준

식별 관계는 부모 키가 자식으로 계속 상속되므로 계층이 깊어질수록 주식별자가 길어집니다. 3계층을 내려가면 맨 아래 테이블의 기본키가 세 칼럼이 되고, 그 인덱스도 세 칼럼짜리가 됩니다. 대신 손자 테이블만 읽어도 조부모의 키 값을 이미 갖고 있어 조인 없이 상위 조건으로 걸러낼 수 있습니다.

비식별 관계는 주식별자가 짧게 유지되지만, 상위 조건으로 하위를 찾으려면 조인을 한 번 더 타야 합니다. 업무가 부모 키로 자식을 자주 뒤진다면 식별 관계가, 자식이 독립적으로 조회되고 부모가 나중에 바뀔 수 있다면 비식별 관계가 유리합니다.

ERD

두 표기법

IE 표기법은 관계의 끝에 까마귀발 모양을 그려 다수를 나타내고, 바커 표기법은 점선과 실선으로 선택과 필수를 나타냅니다. 어느 쪽이든 한 관계선이 담는 정보는 같습니다 — 어느 쪽이 다수인지, 참여가 필수인지 선택인지, 그리고 식별인지 비식별인지입니다.

읽는 순서

ERD를 그릴 때는 엔터티를 도출해 배치하고, 관계를 잇고, 관계명을 적고, 카디널리티를 정하고, 마지막에 필수 여부를 붙입니다. 읽을 때는 그 반대가 아니라 한 관계선을 문장 하나로 옮기는 방식이 안전합니다.

「사원은 반드시 한 부서에 소속된다. 부서는 0명 이상의 사원을 갖는다」 — 이렇게 양방향으로 두 문장을 만들면 필수·선택과 카디널리티를 빠뜨릴 수 없습니다. 실제 시험에서 모델을 주고 「이 SQL이 결과를 몇 건 내는가」를 물을 때, 그 답의 절반은 이 두 문장에서 나옵니다.

연습 문제

1. 3층 스키마에서 물리적 데이터 독립성이 지켜졌다고 말할 수 있는 것은?
   ① 인덱스를 추가했는데 개념 스키마를 고치지 않았다
   ② 테이블에 칼럼을 추가했는데 기존 뷰가 그대로 동작한다
   ③ 사용자 화면을 바꿨는데 테이블이 그대로다
   ④ 뷰를 새로 만들었는데 응용 프로그램이 그대로다

   ①. 물리적 데이터 독립성은 내부 스키마와 개념 스키마 사이의 독립입니다. ②는 개념 스키마와 외부 스키마 사이이므로 논리적 데이터 독립성이고, ③·④는 외부 스키마 안쪽의 변화라 독립성을 말할 자리가 아닙니다.
2. 다음 모델의 문제를 가장 정확히 부르는 이름은?
   회원등급 테이블에 1등급_할인율, 2등급_할인율, 3등급_할인율 칼럼이 있고, 등급이 하나 늘 때마다 칼럼을 추가하고 있다.
   ① 중복
   ② 비유연성
   ③ 비일관성
   ④ 이상현상

   ②. 업무 규칙(등급의 개수)이 구조 자체에 박혀 있어 규칙이 바뀌면 DDL과 SQL을 함께 고쳐야 하는 상태입니다. 같은 값이 두 곳에 있는 것은 아니므로 중복이 아니고, 값이 서로 어긋난 것도 아니므로 비일관성도 아닙니다.
3. 주식별자로 삼기에 가장 부적절한 속성은?
   ① 사원번호
   ② 사업자등록번호
   ③ 부서명
   ④ 계좌번호

   ③. 부서명은 조직 개편으로 바뀔 수 있어 불변성을 만족하지 못하고, 이름으로 기술되는 속성이라 길이도 깁니다. 나머지 셋은 부여된 뒤 바뀌지 않고 유일합니다.
4. 주문(주식별자: 주문번호) 아래 주문상세를, 그 아래 상세이력을 모두 식별 관계로 내려 달았다. 각 테이블에 자기 순번 속성이 하나씩 있을 때 상세이력의 기본키를 이루는 칼럼은 몇 개인가?
   ① 1개
   ② 2개
   ③ 3개
   ④ 4개

   ③. 식별 관계는 부모의 주식별자를 자식의 주식별자 안으로 넣습니다. 주문은 주문번호 1개, 주문상세는 주문번호 + 상세순번 2개, 상세이력은 거기에 자기 순번이 붙어 주문번호 + 상세순번 + 이력순번 3개입니다.
5. 식별자 분류에 대한 설명으로 옳은 것은?
   ① 보조식별자는 유일성을 만족하지 않아도 된다
   ② 외부식별자는 그 엔터티 안에서 새로 만들어진 값이다
   ③ 복합식별자는 둘 이상의 속성이 모여야 유일해지는 식별자다
   ④ 주식별자는 대표성과 무관하게 최소성만 만족하면 된다

   ③. ①은 반대입니다 — 보조식별자도 유일성은 만족하되 대표가 아닐 뿐입니다. ②는 내부식별자의 설명이고, 외부식별자는 다른 엔터티에서 관계를 타고 건너온 값입니다. ④는 주식별자를 대표성 기준으로 세운 정의와 어긋납니다.
6. 계층이 넷인 업무를 모두 식별 관계로 모델링했을 때 생기는 이득과 손실을 각각 하나씩 들고, 그 손실이 SQL의 어느 부분에서 드러나는지 함께 쓰시오.

   이득은 하위 테이블이 상위 키를 이미 갖고 있어 상위 조건으로 거를 때 조인이 필요 없다는 점입니다. 손실은 최하위 테이블의 기본키가 네 칼럼까지 길어져 인덱스가 커지고, 그 테이블을 참조하는 다른 테이블은 네 칼럼을 모두 외래키로 받아야 한다는 점입니다. SQL에서는 조인 조건이 A.a=B.a AND A.b=B.b AND A.c=B.c AND A.d=B.d처럼 길어지고, 조건 하나만 빠뜨려도 카티션 곱에 가까운 결과가 나오는 자리로 드러납니다. 채점은 이득 1점·손실 1점·SQL에서 드러나는 자리 2점으로, 「조인 칼럼 수가 늘어난다」까지 적었는지를 봅니다.