[SQLD] 4. 데이터 모델과 SQL

관계와 조인의 이해

관계의 정의

• 엔터티의 인스턴스 간의 연관성

• 부모 엔터티의 식별자가 자식 엔터티로 상속되어 조인 키(Join Key)로 활용

• 예시: 부서와 사원 관계
  • 부서: 부서 ID, 부서명, 위치
  • 사원: 사원 ID, 사원명, 부서 ID, 급여
  • 부서와 사원은 1:N 관계 (한 부서에 여러 사원이 속함)
  • 부서 ID가 부서와 사원 사이의 관계를 설정하는 조인 키

관계의 종류

• 존재적 관계: 한 엔터티의 존재가 다른 엔터티의 존재 조건이 되는 관계
  • 예시: 부서와 사원 관계 (부서가 있어야 사원이 존재 가능)
• 행위적 관계: 한 엔터티의 행위로 인해 다른 엔터티가 생성되는 관계
  • 예시: 고객과 주문 관계 (고객의 주문 행위로 주문 데이터 생성)

조인의 정의

• 두 개 이상의 테이블을 연결하여 데이터를 조회하는 방법
• 정규화로 분리된 테이블을 조합하여 원하는 데이터 추출하기 위해 데이터를 조합하는 작업

조인의 종류

조건

DEPARTMENT 테이블

DEPT_ID	DEPT_NAME	LOCATION
10	인사부	서울
20	개발부	대전
30	영업부	부산
40	기획부	광주

EMPLOYEE 테이블

EMP_ID	EMP_NAME	DEPT_ID	SALARY
101	김철수	10	5000
102	박영희	20	6000
103	이민수	20	5500
104	정지영	30	7000
105	홍길동	NULL	4500
106	임성민	50	8000

• DEPARTMENT 테이블: 부서 정보를 저장하는 테이블
• EMPLOYEE 테이블: 직원 정보를 저장하는 테이블
• 직원 홍길동은 아직 부서 배정이 되지 않음 (DEPT_ID가 NULL)
• 직원 임성민은 존재하지 않는 부서 ID(50)를 가짐
• 기획부(40)에는 소속된 직원이 없음

INNER JOIN

• 두 테이블의 교집합 데이터 조회
• 예시

  SELECT E.EMP_NAME, E.SALARY, D.DEPT_NAME
  FROM EMPLOYEE E
  INNER JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID;
  

  결과

  EMP_NAME	SALARY	DEPT_NAME
  김철수	5000	인사부
  박영희	6000	개발부
  이민수	5500	개발부
  정지영	7000	영업부

LEFT JOIN

• 왼쪽 테이블의 모든 행과 일치하는 오른쪽 테이블 데이터를 반환하며, 오른쪽에 없는 경우 NULL
• 예시

  SELECT E.EMP_NAME, E.SALARY, D.DEPT_NAME
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID;
  

  결과

  EMP_NAME	SALARY	DEPT_NAME
  김철수	5000	인사부
  박영희	6000	개발부
  이민수	5500	개발부
  정지영	7000	영업부
  홍길동	4500	NULL
  임성민	8000	NULL

RIGHT JOIN

• 오른쪽 테이블의 모든 행과 일치하는 왼쪽 테이블 데이터를 반환하며, 왼쪽에 없는 경우 NULL

• 예시

  SELECT E.EMP_NAME, E.SALARY, D.DEPT_NAME
  FROM EMPLOYEE E
  RIGHT JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID;
  

  결과

  EMP_NAME	SALARY	DEPT_NAME
  김철수	5000	인사부
  박영희	6000	개발부
  이민수	5500	개발부
  정지영	7000	영업부
  NULL	NULL	기획부

FULL OUTER JOIN

• 양쪽 테이블의 모든 데이터를 가져오며, 일치하지 않는 곳은 NULL로 표시
• 예시

  SELECT E.EMP_NAME, E.SALARY, D.DEPT_NAME
  FROM EMPLOYEE E
  FULL OUTER JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID;
  

  결과

  EMP_NAME	SALARY	DEPT_NAME
  김철수	5000	인사부
  박영희	6000	개발부
  이민수	5500	개발부
  정지영	7000	영업부
  홍길동	4500	NULL
  임성민	8000	NULL
  NULL	NULL	기획부

셀프 조인 (Self Join)

• 동일한 테이블 내에서 관계를 설정하여 데이터를 조회하는 조인 방식
• 주로 계층적 데이터에서 사용됨

• 예시

  EMPLOYEE 테이블

  EMP_ID	EMP_NAME	DEPT_ID	SALARY	MANAGER_ID
  101	김철수	10	5000	102
  102	김영희	20	6000	NULL
  103	김민수	20	5500	102
  104	김지영	20	7000	102

  EMPLOYEE 테이블에서 직원과 관리자 관계 조회

  SELECT E1.EMP_NAME AS Employee, E2.EMP_NAME AS Manager
  FROM EMPLOYEE E1
  LEFT JOIN EMPLOYEE E2 ON E1.MANAGER_ID = E2.EMP_ID;
  

  결과

  Employee	Manager
  김철수	김영희
  김영희	NULL
  김민수	김영희
  김지영	김영희

상호 배타적 관계 (Mutually Exclusive Relationship)

• 한 엔터티가 둘 이상의 다른 엔터티와 관계를 가질 수 있으나, 동시에 둘 이상과 관계를 가질 수 없는 상태

• 예시

  • 하나의 주문은 온라인 주문 또는 오프라인 주문 중 하나일 수 있지만, 둘 다 동시에 될 수는 없음

  주문 정보 테이블

    CREATE TABLE ORDER_INFO (
        ORDER_ID INT PRIMARY KEY,
        ORDER_TYPE VARCHAR(10) CHECK (ORDER_TYPE IN ('ONLINE', 'OFFLINE'))
    );
  

모델이 표현하는 트랜잭션의 이해

트랜잭션의 정의

• 데이터베이스의 논리적 작업 단위
• 하나의 트랜잭션에는 하나 이상의 작업이 포함됨

트랜잭션의 특성 (ACID)

• 원자성(Atomicity): 전체 성공 또는 전체 실패
• 일관성(Consistency): 데이터베이스 상태의 일관성 유지
• 고립성(Isolation): 트랜잭션 간 독립적 실행

• 지속성(Durability): 완료된 트랜잭션의 영구 저장

• 예시: 계좌 이체 트랜잭션
  1. 출금 계좌 잔액 확인 (SELECT)
  2. 출금 계좌에서 금액 차감 (UPDATE)
  3. 입금 계좌에 금액 추가 (UPDATE)
     • 모든 단계 성공 → COMMIT
     • 하나라도 실패 → ROLLBACK

Null 속성의 이해

NULL 이란

• 값이 정해지지 않은 특수한 상태
• 0이나 빈 문자열(‘‘)과는 다른 개념
• 모델 설계 시 각 컬럼별로 NULL을 허용할지 결정(NULLABLE, NOT NULL)

NULL 특징

• NULL이 포함된 연산은 NULL 결과 반환
• NULL 처리를 위한 특수 함수 사용
  • NVL : NULL 값을 다른 값으로 변환

    SELECT NVL(commission, 0) AS commission_value
    FROM EMPLOYEE;  
    

  • COALESCE : 여러 값 중 NULL이 아닌 첫 번째 값 반환

NULL과 집계 함수

• COUNT(column): NULL 제외 카운트
• COUNT(*): NULL 포함 전체 행 카운트
• 기타 집계 함수: NULL 무시하고 연산

본질식별자 vs 인조식별자

식별자 유형

• 본질식별자
  • 업무상 자연적으로 존재하는 고유 값
  • 예시: 주민등록번호, 이메일
• 인조식별자
  • 시스템에서 자동 생성하는 고유 식별자
  • 장점
    • 관계 설정 및 데이터 변경에 유연함
  • 단점
    • 중복 데이터 가능성 -> 데이터 품질 저하
    • 추가 인덱스 부담 -> 저장공간 낭비 및 DML 성능 저하

식별자 선택 예시

1. PK : 주문번호 + 상품번호
   • 하나의 주문번호에 같은 상품의 주문 결과를 저장할 수 없음

   ORDER_ID	PRODUCT_ID	QTY
   1001	2001	2
   1001	2001	3
   1002	2002	1

2. PK : 주문번호 + 일련번호
   • 동일 주문번호에도 일련번호를 부여해 구분

   ORDER_ID	SEQ	PRODUCT_ID	QTY
   1001	1	2001	2
   1001	2	2001	3
   1002	1	2002	1

3. PK : 주문상세번호
   • 단일 컬럼으로 각 주문 내 고유 식별자 생성

   ORDER_DETAIL_ID	ORDER_ID	PRODUCT_ID	QTY
   10001	1001	2001	2
   10002	1001	2001	3
   10003	1002	2002	1