관계와 카디널리티, 조인으로의 변환

앞 노트에서 엔터티와 속성을 봤습니다. 상자를 다 그렸으면 이제 상자를 잇는 선 차례입니다. 관계는 1과목에서 두세 문항을 가져가는 자리이면서, 2과목의 조인이 어디서 나왔는지를 설명해 주는 자리이기도 합니다. 이 노트의 마지막 두 절이 그 다리입니다.

관계와 페어링

관계는 두 엔터티의 인스턴스 사이에 존재하는 연관성입니다. 「고객이 주문한다」처럼 업무 규칙 하나가 선 하나가 됩니다.

페어링

관계선은 엔터티끼리 이어져 있지만 실제로 맺어지는 것은 인스턴스와 인스턴스입니다. 고객 「김팔딘」과 주문 「20260909-001」이 맺어지는 이 짝을 페어링이라 부르고, 페어링의 집합이 곧 관계입니다.

이 구별이 중요한 이유는 카디널리티를 셀 때 드러납니다. 「고객 : 주문 = 1 : M」이라는 말은 고객 엔터티가 주문 엔터티보다 작다는 뜻이 아니라, 고객 인스턴스 하나에 주문 인스턴스 여럿이 페어링된다는 뜻입니다. 관계를 셀 때는 항상 인스턴스 한 개를 집어 놓고 반대편에 몇 개가 붙는지 세면 됩니다.

관계의 세 요소

관계를 완성하려면 셋을 적어야 합니다.

관계는 성격에 따라 둘로도 나눕니다. 「사원이 부서에 소속된다」처럼 존재만으로 성립하는 것이 존재적 관계, 「고객이 주문한다」처럼 어떤 행위가 있어야 생기는 것이 행위적 관계입니다. UML의 연관 관계와 의존 관계에 각각 대응하는 구분이지만, SQLD에서는 이름과 예를 짝지어 묻는 정도로 나옵니다.

관계명은 양방향을 따로 적습니다. 고객에서 주문을 볼 때와 주문에서 고객을 볼 때 쓰는 말이 다르기 때문입니다. 「~와 관련이 있다」처럼 두루뭉술한 이름은 안 쓴 것과 같아서, 이런 이름이 붙어 있으면 관계를 잘못 그렸는지 다시 봐야 합니다.

카디널리티

카디널리티는 한쪽 인스턴스 하나에 반대쪽 인스턴스가 몇 개 대응하는지를 나타낸 값입니다. 관계차수라고도 부릅니다.

1:1과 1:M

1:1은 실무에서 드뭅니다. 정말 1:1이면 대개 한 테이블로 합칠 수 있기 때문이고, 그런데도 갈라 두는 것은 접근 권한이 다르거나 컬럼이 너무 많아서입니다.

1:M이 가장 흔한 관계이고, 여기서 M 쪽에 외래키가 생깁니다. 주문 한 건은 고객 한 명에게 속하므로 주문 쪽에 고객번호를 두면 되지만, 반대로 고객 쪽에 주문번호를 두려면 칸이 몇 개 필요한지 알 수 없습니다.

M:N과 그 해소

양쪽 모두 여럿이 대응하면 M:N입니다. 학생과 과목이 대표적입니다 — 한 학생이 여러 과목을 듣고 한 과목을 여러 학생이 듣습니다.

M:N 관계는 관계형 데이터베이스에서 그대로 구현할 수 없습니다. 외래키를 어느 쪽에 둬도 값이 여럿 필요하기 때문입니다. 그래서 논리 모델에서 물리 모델로 내려갈 때 가운데에 엔터티를 하나 세워 1:M 둘로 나눕니다.

학생 ─── M:N ─── 과목
        ↓ 해소
학생 ─1:M─ 수강 ─M:1─ 과목

이때 생기는 「수강」이 앞 노트에서 본 행위 엔터티입니다. 학번과 과목코드를 함께 받아 식별하고, 수강신청일·성적처럼 그 관계에만 있는 속성이 여기에 붙습니다. M:N을 풀면 행위 엔터티가 생긴다는 이 연결이 자주 나옵니다.

필수와 선택

카디널리티가 「몇 개」를 정한다면 관계선택사양은 「0개여도 되는가」를 정합니다.

필수적 관계와 선택적 관계

같은 관계선이라도 양쪽 끝의 선택사양이 다를 수 있습니다. 고객과 주문 사이가 그렇습니다 — 주문 쪽에서 보면 고객이 없는 주문은 있을 수 없으니 필수이고, 고객 쪽에서 보면 가입만 하고 아직 주문하지 않은 고객이 있으니 선택입니다. 한쪽 끝만 보고 「이 관계는 필수」라고 단정하는 것이 가장 흔한 실수입니다.

관계를 문장으로 읽기

관계는 정해진 틀에 넣어 읽습니다.

각각의 (한쪽 엔터티)는 한 개 이상의 / 하나의 (반대쪽 엔터티)를 (관계명)한다.

「각각의 고객은 0개 이상의 주문을 주문한다」, 「각각의 주문은 반드시 하나의 고객에 의해 주문된다」처럼 읽으면 차수와 선택사양이 문장 안에 다 들어옵니다. 모델을 검증하는 가장 값싼 방법이 이 낭독이고, 소리 내어 읽어서 말이 안 되면 대개 선이 잘못 그어져 있습니다.

관계가 조인이 되는 자리

논리 모델의 관계선은 물리 모델에서 사라집니다. 그 자리를 외래키가 대신하고, 조회할 때는 조인이 대신합니다.

외래키로 내려앉는 관계

1:M 관계에서 1 쪽의 주식별자가 M 쪽에 외래키로 복사됩니다. 고객과 주문이라면 이렇게 됩니다.

CREATE TABLE 고객 (
  고객번호 NUMBER PRIMARY KEY,
  고객명   VARCHAR2(50) NOT NULL
);

CREATE TABLE 주문 (
  주문번호 NUMBER PRIMARY KEY,
  고객번호 NUMBER NOT NULL REFERENCES 고객(고객번호),
  주문일자 DATE
);

NOT NULL이 붙은 자리를 보면 관계선택사양이 그대로 옮겨 온 것을 알 수 있습니다. 필수적 관계는 외래키 컬럼의 NOT NULL이 되고, 선택적 관계는 NULL을 허용합니다. 논리 모델의 동그라미와 세로 막대가 물리 모델에서 이 한 줄로 바뀝니다.

조인 SQL로 옮기기

관계를 따라 두 엔터티의 값을 함께 보려면 조인합니다. 조인 조건은 외래키와 주식별자를 맞추는 것이고, 그 두 컬럼이 곧 논리 모델에서 그었던 관계선입니다.

SELECT o.주문번호, o.주문일자, c.고객명
  FROM 주문 o
  JOIN 고객 c ON o.고객번호 = c.고객번호;

관계가 선택적이면 조인의 종류가 갈립니다. 주문이 없는 고객까지 보고 싶다면 위의 JOIN으로는 그 고객이 결과에서 빠지므로 LEFT OUTER JOIN을 써야 합니다. 어느 조인을 쓸지는 취향이 아니라 관계선택사양이 정합니다 — 선택적 관계 쪽을 살려서 보려면 외부 조인입니다.

모델이 표현하는 트랜잭션

관계는 조회 방법만 정하는 것이 아니라 어디까지가 한 덩어리인가도 정합니다.

필수 관계가 묶는 두 행

주문과 주문상세가 양쪽 다 필수 관계라면, 주문 없는 주문상세도 없고 상세 없는 주문도 없다는 뜻입니다. 그러면 주문을 넣는 일과 상세를 넣는 일은 함께 성공하거나 함께 실패해야 합니다. 하나만 들어가는 순간 모델이 금지한 상태가 데이터베이스에 남기 때문입니다.

INSERT INTO 주문 VALUES (1001, 7, SYSDATE);
INSERT INTO 주문상세 VALUES (1001, 'A-01', 2);
COMMIT;   -- 둘을 한 트랜잭션으로 묶는다

트랜잭션 범위를 읽는 법

반대로 선택적 관계라면 한쪽만 넣어도 됩니다. 고객을 등록하고 주문을 나중에 받는 것이 정상이므로 두 작업을 한 트랜잭션에 묶을 이유가 없습니다.

그래서 모델을 보면 트랜잭션의 경계가 읽힙니다 — 양쪽이 필수인 관계로 묶인 엔터티들이 한 트랜잭션의 범위입니다. 이 관점은 「다음 중 반드시 하나의 트랜잭션으로 처리해야 하는 것은」 같은 형태로 나오고, 답은 관계선의 양 끝에 세로 막대가 둘 다 서 있는 짝입니다.

연습 문제

1. 관계의 세 요소에 해당하지 않는 것은?
   ① 관계명
   ② 관계차수
   ③ 관계선택사양
   ④ 관계도메인

   ④. 관계도메인이라는 요소는 없습니다. 도메인은 속성이 가질 수 있는 값의 범위입니다.
2. M:N 관계에 대한 설명으로 옳지 않은 것은?
   ① 관계형 데이터베이스에 그대로 구현할 수 없다
   ② 가운데 엔터티를 두어 1:M 관계 둘로 나눈다
   ③ 이때 생기는 엔터티는 행위 엔터티가 된다
   ④ 해소한 뒤 가운데 엔터티에는 속성을 둘 수 없다

   ④. 수강신청일·성적처럼 그 관계에서만 생기는 속성이 가운데 엔터티에 붙습니다. 붙을 속성이 있다는 것이 오히려 M:N을 풀어야 하는 이유 중 하나입니다.
3. 「가입만 하고 아직 주문한 적 없는 고객이 존재할 수 있다」는 업무 규칙이 모델에 표현되는 방식으로 옳은 것은?
   ① 고객과 주문 사이를 1:1 관계로 그린다
   ② 주문 쪽에서 고객 쪽으로 가는 관계를 선택적으로 그린다
   ③ 고객 쪽에서 주문 쪽으로 가는 관계를 선택적으로 그린다
   ④ 주문 테이블의 고객번호에 NULL을 허용한다

   ③. 고객 하나에 주문이 0개일 수 있다는 뜻이므로 고객에서 주문으로 가는 방향이 선택적입니다. ④는 반대쪽 이야기로, 고객 없는 주문을 허용하게 되어 규칙이 어긋납니다.
4. 다음 두 테이블을 조인할 때, 주문이 한 건도 없는 고객까지 결과에 나오게 하려면?
   「고객(고객번호, 고객명), 주문(주문번호, 고객번호)」
   ① FROM 고객 c JOIN 주문 o ON c.고객번호 = o.고객번호
   ② FROM 고객 c LEFT OUTER JOIN 주문 o ON c.고객번호 = o.고객번호
   ③ FROM 고객 c, 주문 o
   ④ FROM 고객 c JOIN 주문 o ON c.고객명 = o.고객번호

   ②. 왼쪽인 고객을 다 살려야 하므로 LEFT OUTER JOIN입니다. ①과 ③은 짝이 있는 행만 나오고(③은 조건이 없어 교차 조인이 됩니다), ④는 이름과 번호를 비교하는 잘못된 조건입니다.
5. 관계선택사양이 물리 모델에서 나타나는 방식으로 옳은 것은?
   ① 필수적 관계는 외래키 컬럼이 NOT NULL이 된다
   ② 필수적 관계는 외래키 컬럼이 NULL을 허용한다
   ③ 선택적 관계는 외래키 컬럼이 기본키가 된다
   ④ 관계선택사양은 물리 모델에 반영되지 않는다

   ①. 반드시 짝이 있어야 하므로 외래키가 비어 있으면 안 됩니다. 선택적 관계는 NULL을 허용합니다.
6. 반드시 하나의 트랜잭션으로 처리해야 하는 짝으로 가장 적절한 것은?
   ① 고객 등록과 고객의 첫 주문 등록
   ② 주문 등록과 그 주문의 주문상세 등록
   ③ 상품 등록과 상품 조회
   ④ 회원 탈퇴와 신규 회원 가입

   ②. 주문과 주문상세는 양쪽이 필수 관계라 하나만 들어가면 모델이 금지한 상태가 됩니다. ①은 선택적 관계라 나눠도 되고, ③④는 서로 무관한 작업입니다.
7. 페어링에 대한 설명으로 옳은 것은?
   ① 엔터티와 엔터티가 맺어지는 것이다
   ② 인스턴스와 인스턴스가 맺어지는 것이다
   ③ 속성과 속성값이 맺어지는 것이다
   ④ 주식별자와 외래키가 같은 값을 갖는 것이다

   ②. 관계선은 엔터티 사이에 그리지만 실제로 맺어지는 것은 인스턴스와 인스턴스이고, 그 짝들의 집합이 관계입니다.

1과목의 관계 문항은 그림을 주고 문장으로 옳게 읽은 것을 고르게 하는 형태가 가장 많습니다. 관계선을 볼 때마다 양쪽 끝을 따로 읽는 습관을 들이면 대부분 걸러집니다 — 틀린 보기는 거의 항상 한쪽 끝의 기호를 반대쪽에 갖다 붙여 놓습니다.