DBMS 실전 가이드

-- 민원 접수 시스템 (SQLite 방언 — 우리 코드와 동일)
CREATE TABLE citizen (
  citizen_id  INTEGER PRIMARY KEY,          -- 대리키
  name        TEXT NOT NULL,
  phone       TEXT UNIQUE                   -- 후보키
);

CREATE TABLE department (
  dept_id     INTEGER PRIMARY KEY,
  dept_name   TEXT NOT NULL UNIQUE,
  floor       INTEGER CHECK (floor BETWEEN 1 AND 20)
);

CREATE TABLE complaint (
  complaint_id INTEGER PRIMARY KEY AUTOINCREMENT,
  citizen_id   INTEGER NOT NULL REFERENCES citizen(citizen_id),
  dept_id      INTEGER REFERENCES department(dept_id)
               ON DELETE SET NULL,         -- 부서 폐지 → 미배정으로
  status       TEXT NOT NULL DEFAULT '접수'
               CHECK (status IN ('접수','처리중','완료','반려')),
  created_at   TEXT NOT NULL DEFAULT (datetime('now'))
  -- PostgreSQL: AUTOINCREMENT 대신 GENERATED ALWAYS AS IDENTITY
);

-- 복합키는 별도 테이블로: 민원 1 : N 유형
CREATE TABLE complaint_type (
  complaint_id INTEGER NOT NULL REFERENCES complaint(complaint_id),
  type_name    TEXT    NOT NULL,
  PRIMARY KEY (complaint_id, type_name)    -- 복합 PK
);

-- BCNF 검증: "모든 결정자가 후보키인가?"
-- 3NF 통과 후에도 결정자가 후보키가 아닌 경우가 남을 수 있다.
-- 예) (처리자) → (부서) 면서 처리자가 후보키가 아니면 BCNF 위반.
--     → 처리자 테이블을 별도 분해. 실무 면접은 3NF 설계 + BCNF 개념 설명이면 충분.
-- 4NF(다치 종속)·5NF(조인 종속)는 다면 후보 속성·수강 이력 같은 특수 사례 — "존재는 안다"로 충분.

-- INNER: 부서가 확정된 민원만 (ON이 일치하는 행)
SELECT c.complaint_id, c.handler, d.dept_name
FROM complaint c
JOIN department d ON d.dept_id = c.dept_id;

-- LEFT: 미배정(dept_id IS NULL) 민원도 누락 없이
SELECT c.complaint_id, COALESCE(d.dept_name, '미배정') AS dept
FROM complaint c
LEFT JOIN department d ON d.dept_id = c.dept_id;

-- SELF JOIN: 같은 민원인의 다른 민원 찾기
SELECT a.complaint_id AS mine, b.complaint_id AS other
FROM complaint a
JOIN complaint b
  ON a.citizen_id = b.citizen_id
 AND a.complaint_id < b.complaint_id;   -- 자기 자신·중복 제거

-- 상태별 민원 수: GROUP BY(그룹화) + HAVING(그룹 필터)
SELECT status, COUNT(*) AS n
FROM complaint
GROUP BY status
HAVING COUNT(*) >= 10        -- WHERE로는 불가: 집계 이후 조건
ORDER BY n DESC;

-- 비율까지: 윈도우 함수로 전체 합을 각 행에 병렬 계산
SELECT status,
       COUNT(*) AS n,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM complaint
GROUP BY status;             -- SUM(COUNT(*)) OVER () = 전체 민원 수

-- 같은 문제: "부서별 최신 민원 1건씩"을 3가지로

-- (1) 상관 서브쿼리 — 부서마다 다시 SELECT (N+1 실행, 느림)
SELECT c.* FROM complaint c
WHERE c.created_at = (SELECT MAX(x.created_at)
                      FROM complaint x
                      WHERE x.dept_id = c.dept_id);

-- (2) CTE + 윈도우 함수 — 그룹 내 순위 매겨 1등만 (권장)
WITH ranked AS (
  SELECT c.*,
         ROW_NUMBER() OVER (PARTITION BY dept_id
                            ORDER BY created_at DESC) AS rn
  FROM complaint c
)
SELECT complaint_id, dept_id, status, created_at
FROM ranked
WHERE rn = 1;

-- (3) 윈도우 함수 직접 — 전체 대비 부서 비중·누적까지 한 번에
SELECT dept_id, COUNT(*) AS n,
       SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC) AS running_total
FROM complaint
GROUP BY dept_id;

-- DML: 갱신·삭제는 반드시 WHERE와 함께 — 먼저 SELECT로 대상 확인이 안전 습관
UPDATE complaint SET status = '완료' WHERE complaint_id = 1001;
DELETE FROM complaint WHERE status = '접수'
  AND created_at < datetime('now', '-2 years');

-- DDL: TRUNCATE는 전행 삭제 (DDL — 빠르지만 WHERE 불가)
TRUNCATE TABLE staging_complaint;

BEGIN;                                   -- 트랜잭션 시작
UPDATE complaint SET status = '처리중' WHERE complaint_id = 1001;
INSERT INTO handling_log(complaint_id, actor, action)
  VALUES (1001, '박처리', '담당자 배정');
SAVEPOINT sp1;                           -- 부분 저장점
UPDATE complaint SET handler = NULL WHERE complaint_id = 1001;
ROLLBACK TO sp1;                         -- 저장점까지만 되돌림
COMMIT;                                  -- 전부 확정 (또는 ROLLBACK; 전체 폐기)

-- 격리 수준 설정
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;          -- PostgreSQL
SET SESSION transaction_isolation = 'READ-COMMITTED';    -- MySQL

-- 복합 인덱스: 컬럼 순서가 곧 사용 가능성 (leftmost prefix)
CREATE INDEX idx_complaint_dept ON complaint(dept_id, created_at);
-- WHERE dept_id = 3                      → 인덱스 사용 O
-- WHERE dept_id = 3 AND created_at > ...  → 인덱스 사용 O (범위까지)
-- WHERE created_at > ...                 → 인덱스 사용 X (선행 컬럼 없음)

-- 커버링 인덱스: 조회 컬럼까지 인덱스에 담아 테이블 접근 자체를 제거
CREATE INDEX idx_complaint_dept_status ON complaint(dept_id, status);
SELECT status FROM complaint WHERE dept_id = 3;   -- 테이블 안 열어도 됨

-- 실행 계획 확인 — 튜닝은 측정에서
EXPLAIN QUERY PLAN
SELECT status FROM complaint WHERE dept_id = 3;
-- SQLite:  SEARCH complaint USING COVERING INDEX idx_complaint_dept_status
--          (vs SCAN = 전체 스캔, 수백만 행이면 경보)
-- PostgreSQL은 EXPLAIN ANALYZE — 실제 실행 시간까지

-- SI 면접 표준 답변 뼈대: "공공 시스템이라 정규화 3NF가 기본값,
-- 통계성 대시보드 조회가 heavy하면 측정 후 반정규화 요약 테이블을 예외로 둔다.
-- 트랜잭션 경계는 민원 상태 전이(UPDATE + 이력 INSERT)처럼
-- '같이 성공하거나 같이 실패해야 하는 단위'로 설계한다."
-- → 개념 암송이 아니라 판단 기준을 말하는 것 — 이 페이지 전체의 요약.