Day 4 - 관계와 조인, 모델이 표현하는 트랜잭션

관계는 조인 조건이 된다

관계를 그려 놓은 모델은 "어느 테이블과 어느 테이블을 어떤 칼럼으로 이어야 하는가"를 이미 알려 준다.

• 정규화로 엔터티를 나누면 나눈 만큼 관계가 생기고, 그 관계가 곧 조회 시의 조인이다.
• 조인은 관계를 맺은 PK와 FK의 값이 같은 행끼리 연결하는 연산이다.

관계 하나를 SQL로 옮기면 다음과 같다.

SELECT D.DEPTNO, D.DNAME, COUNT(E.EMPNO) AS EMP_CNT, SUM(E.SAL) AS SAL_SUM
FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO
GROUP BY D.DEPTNO, D.DNAME ORDER BY D.DEPTNO;

    DEPTNO DNAME             EMP_CNT    SAL_SUM
---------- -------------- ---------- ----------
        10 ACCOUNTING              3       8750
        20 RESEARCH                5      10875
        30 SALES                   6       9400
        40 OPERATIONS              0

• 40번 부서는 사원이 없다. 모델에서 "부서는 사원을 0명 이상 가진다"는 선택 관계라는 뜻이다.
• 이 선택성을 그대로 살려 부서를 모두 보려면 OUTER JOIN이 필요하다. 일반 조인이면 40번 부서는 결과에서 빠진다.
• 즉 관계의 선택/필수 표기는 INNER와 OUTER 중 어느 조인을 쓸지 판단하는 근거가 된다.

관계가 없는 조인도 있다

모든 조인이 FK를 따르는 것은 아니다. SALGRADE는 EMP와 관계선이 없지만 급여 범위로 연결된다.

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE E.DEPTNO = 10 ORDER BY E.EMPNO;

ENAME             SAL      GRADE
---------- ---------- ----------
CLARK            2450          4
KING             5000          5
MILLER           1300          2

• 등호가 아닌 조건으로 잇는 조인을 Non-EQUI 조인이라 한다. 4주차에서 자세히 다룬다.
• 1과목에서 알아 둘 점은 "관계 = 조인 경로"가 기본이고, 조인이 관계 없이도 가능하다는 것이다.

계층형 데이터 모델

EMP의 MGR은 같은 EMP의 EMPNO를 가리킨다. 엔터티가 자기 자신과 관계를 맺는 구조다.

           EMP
        ┌────────────┐
        │ EMPNO  PK  │◄──┐
        │ ENAME      │   │  관리자-부하 관계
        │ MGR    FK  │───┘  (자기 참조, 순환 관계)
        └────────────┘

• 이런 모델을 계층형 데이터 모델이라 하고, 이런 관계를 순환 관계(재귀 관계) 라고 부른다.
• 조직도, 부품 구성표(BOM), 상위·하위 메뉴처럼 같은 종류의 데이터가 상하로 이어질 때 쓴다.
• 최상위 행은 상위가 없으므로 FK 값이 NULL이다. EMP에서는 KING이다.

같은 테이블을 두 번 읽어 관리자 이름을 붙이는 것이 셀프 조인이다.

SELECT E.EMPNO, E.ENAME, E.MGR, M.ENAME AS MGR_NAME
FROM EMP E LEFT OUTER JOIN EMP M ON E.MGR = M.EMPNO
WHERE E.DEPTNO = 10 ORDER BY E.EMPNO;

     EMPNO ENAME             MGR MGR_NAME
---------- ---------- ---------- ----------
      7782 CLARK            7839 KING
      7839 KING
      7934 MILLER           7782 CLARK

• E는 사원 역할, M은 관리자 역할이다. 같은 EMP에 별칭을 둘 붙여 서로 다른 테이블처럼 쓴다.
• KING은 MGR이 NULL이라 일반 조인이면 빠진다. 여기서도 OUTER JOIN으로 남겼다.
• 여러 단계를 한 번에 내려가려면 START WITH … CONNECT BY 계층형 질의를 쓴다. 6주차 범위다.

⚠️ 함정: 계층형 데이터 모델은 하나의 엔터티 안에서 상하 관계가 생기는 구조다. "두 개의 엔터티가 계층을 이룬다"거나 "계층형 모델에서는 셀프 조인을 쓸 수 없다"는 보기는 틀린다.

상호배타적 관계

주문을 넣는 주체가 개인고객일 수도, 법인고객일 수도 있다. 한 주문은 둘 중 한 쪽과만 관계를 맺는다.

  개인고객 ─┐
            ├── (둘 중 하나) ──< 주문
  법인고객 ─┘

• 이것을 상호배타적(Exclusive OR) 관계라 하고, 표기법에 따라 아크(Arc) 관계라고도 부른다.
• 바커 표기법에서는 두 관계선을 가로지르는 호(arc)로 표시한다.
• 물리 모델로 옮기는 방법은 여러 가지다. 고객번호 칼럼 하나와 "고객구분" 칼럼을 함께 두거나, 개인·법인 FK를 각각 두고 하나만 값을 채운다.
• 고객구분 칼럼을 쓰면 조인할 때 그 값에 따라 어느 테이블과 조인할지가 달라진다. SQL이 복잡해지는 이유다.

모델이 표현하는 트랜잭션

트랜잭션은 함께 성공하거나 함께 실패해야 하는 작업 묶음이다. 1과목에서 묻는 것은 이 묶음이 모델에 어떻게 드러나는가이다.

주문과 주문상품을 예로 든다.

• 주문 하나에는 상품이 최소 하나 있어야 한다. 상품 없는 주문은 의미가 없다.
• 주문상품도 주문 없이 혼자 존재할 수 없다.
• 그래서 주문 헤더와 주문상품 행들은 한 트랜잭션으로 함께 저장되어야 한다.

  주문                 주문상품
┌──────────┐        ┌────────────┐
│ 주문번호  │|──────<│ 주문번호    │   양쪽 필수(|)
│ 주문일자  │        │ 상품번호    │   → 주문 INSERT 와 주문상품 INSERT 를
└──────────┘        │ 수량        │     한 트랜잭션으로 묶는다
                    └────────────┘

• 관계를 필수로 그렸다면 두 엔터티의 입력은 하나의 작업 단위라는 신호다. 개발자는 두 INSERT를 같은 트랜잭션 안에서 처리하고 함께 COMMIT한다.
• 관계를 선택으로 잘못 그리면 주문만 들어가고 주문상품이 빠진 데이터가 남을 수 있다.
• 반대로 업무상 따로 저장되는 것을 필수로 묶으면 불필요하게 큰 트랜잭션이 된다.
• 트랜잭션의 ACID와 COMMIT·ROLLBACK 문법은 7주차 TCL에서 다룬다.

내일은 NULL이 연산과 집계에서 어떻게 동작하는지, 그리고 본질식별자와 인조식별자의 차이를 다룬다.

📖 용어

• 조인 : 관계를 맺은 칼럼의 값을 기준으로 두 테이블의 행을 연결하는 연산.
• 조인 경로 : 원하는 데이터에 닿기 위해 거쳐야 하는 테이블과 조인 조건의 순서.
• 계층형 데이터 모델 : 한 엔터티 안에서 상위·하위 관계가 생기는 모델. 조직도가 대표적이다.
• 순환 관계 : 엔터티가 자기 자신을 참조하는 관계. 재귀 관계라고도 한다.
• 셀프 조인 : 같은 테이블에 별칭을 둘 주고 자기 자신과 조인하는 것.
• 상호배타적 관계 : 한 엔터티가 여러 엔터티 중 하나와만 관계를 맺는 구조. 아크 관계라고도 한다.
• 트랜잭션 : 함께 성공하거나 함께 실패해야 하는 작업의 묶음.
• 필수 관계 : 상대 엔터티의 인스턴스가 반드시 있어야 하는 관계. 선택 관계의 반대다.