[250216] Developing Time-Oriented Applications in SQL

차이를 구별하고, 분리하고, 대조하는 것은 인간의 본성이다. 이분법은 이를 다루는 기본적인 전략이다. 우리는 회색의 음영, 가능성의 스펙트럼, 글로벌 커뮤니티보다는 흑과 백, 이것과 저것, 우리와 그들을 선호한다. 식물 또는 동물, 다수 또는 소수, 옮고그름 등등

이러한 이분법은 단순함과 환원주의에서 매력적이지만 비슷한 중요성을 가진 3가지의 모음은 더 깊은 울림을 준다.

세 단계 논리는 알 수 없음의 가치를 포함한다. 그리스인들은 세계를 지구, 바다, 하늘(천국)으로 구성된 것으로 보았다. 기독교인들은 삼위일체를 기뻐하며, 또한 지구, 천국, 지옥을 이야기한다. 불교에는 세 가지 악의 뿌리가 있다: 욕망, 증오, 망상. 많은 종교들이 마음, 몸, 영혼을 구분한다. 피타고라스는 가장 단순한 기하학적 도형인 삼각형을 찬양했다. 우리는 세 가지 공간 차원을 인식한다. 무지개는 세 가지 기본 색의 조합이다. 많은 정부들이 세 개의 지부로 나뉘는데, 이는 의자가 세 개의 다리를 가진 것과 비슷한 이유에서다. 서양 음악의 화성적 기초는 근음과 3도, 5도로 구성된 세 음의 화음이다. 문학적 삼부작은 그 자체로 만족스러운 완성도를 가진다.

이 책에서 우리는 SQL 언어에서 시간에 변화하는 애플리케이션을 구현하는 방법을 살펴볼 것이다. 우리는 세 가지 직교하는 개념들에 초점을 맞출 것이다:

1. Temporal data type
2. Kinds of time
3. Temporal statements

SQL은 솔직히 그러한 변화하는 측면들을 포착하거나 효과적으로 모델링, 쿼리, 수정하기 위한 구조를 제공하는 데 매우 부족하다.

시간적 데이터 타입(temporal data types)]에는 세가지 타입이 있다.

1. instant : 시간점 (예: 2025-01-01)
2. interval : 시간 간격 (예: 3개월)
3. period : 시간 기간 (예: 2024-01-01 ~ 2024-12-31)

시간의종류(Kinds of time)에는 세가지 종류가 있다:

1. user-defined time : 사용자가 정의한 시간 (해석되지 않은 시간 값)
2. valid time : 유효한 시간 (모델링된 현실에서 fact 참이었던 때)
3. transaction time : 트랜잭션 시간 (fact 가 데이터베이스에 저장된 시간)

위 시간의 종류들은 모두 직교한다.

시간 지향 구문(time-oriented statements)에도 세가지 종류가 있다:

1. Current : 지금 시점에서의 상태를 다루는 구문
2. Sequenced : at each instant of time
3. Nonsequenced : ignoring time

feed yard 에서 cattle 은 "lots" 라는 무리로 관리된다. 이들은 PEN 에서 다른 PEN 으로 움직인다.

LOT_LOC table의 각 행은 feed yard의 각 축사(pen)에 있는 각 로트(lot)의 소가 몇 마리인지 기록한다. Brad는 질병이 소에서 소로 전파되는 것을 연구하기 위해 각 소가 함께 있었던 시간을 기록하고 싶다. 그는 FROM_DATE, TO_DATE 열을 추가하여 각 소가 함께 있었던 시간을 기록한다.

LOT_LOC(LOT_ID_NUM, PEN_ID, HD_CNT, =FROM_DATE=, =TO_DATE=)

이 두 컬럼으로 레코드가 실제로 언제 유효했는지 알 수 있다. FROM_DATE, TO_DATE 는 유효기간의 시작 순간과 유효기간의 종료 순간을 나타낸다.

| LOT_ID_NUM | PEN_ID | HD_CNT | FROM_DATE  | TO_DATE    |
|------------+--------+---------+------------+------------|
| 137        | 1      | 17      | 1998-02-07 | 1998-02-18 |
| 219        | 1      | 43      | 1998-02-25 | 1998-03-01 |
| 219        | 1      | 20      | 1998-03-01 | 1998-03-14 |
| 219        | 2      | 23      | 1998-03-01 | 1998-03-14 |
| 219        | 2      | 43      | 1998-03-14 | 9999-12-31 |
| 374        | 1      | 14      | 1998-02-20 | 9999-12-31 |

valid-time state tables capture the history of reality.(현실의 역사를 포착)

Brad는 LOT_CONTAINS 테이블을 추가한다.

LOT_CONTAINS(LOT_ID_NUM, BKP_ID, A_NAME)

이 테이블은 백업 테이블이다. 각 행은 백업 ID와 어플리케이션 이름을 기록한다. 이 데이터는 데이터 불일치 및 누락이 있는 등 데이터가 더러운 경향이 있어서 Brad는 LOT_CONTAINS 테이블의 데이터를 추적하고자한다.

특히 과거 어느 날짜의 상태를 재구성하고 싶어한다.

• START_DATE indicating when that row was first inserted into the table
• STOP_DATE indicating when that row was last updated or deleted

우리는 이 두 트랜젝션 타임을 넣어서 재구성을 할 수 있고, 이렇게 과거를 재구성할 수 있는 테이블을 트랜젝션 타임 상태 테이블이라고 부른다.

LOT_CONTAINS(LOT_ID_NUM, BKP_ID, A_NAME, START_DATE, STOP_DATE)

An instant is an anchored location on the timeline.

인스턴트는 타임라인에서 고정된 위치이다.

ex) 2025-01-01 12:00:00

인터벌은 타임라인에서 고정되지 않은 연속적인 부분이다. 인터벌은 인스턴트에 더해지면 다른 인스턴트를 생성한다.

ex) 하루 기간(Interval) : 2025-01-01 12:00:00 + 1 day = 2025-01-02 12:00:00

• An interval is an unanchored continuous portion of the timeline.
• An interval is relative; an instant is absolute.
• An interval can be added to an instant, yielding another instant.

아래와 같은 식으로 쿼리를 작성할 수 있다.

OVERLAPS 는 두 인터벌이 겹치는 부분이 있는지 확인하는 연산자이다.

-- Code Fragment 3.3 1970.1.1에 태어난 사람의 정보
SELECT * FROM Employee
WHERE BirthDate BETWEEN DATE '1970-01-01' AND DATE '1970-01-01';

-- overlap predicate
SELECT * FROM Employee
WHERE (BirthDate, INTERVAL '0' DAY) OVERLAPS (DATE '1970-01-01', INTERVAL '0' DAY);

UIS(University Information System) 은 애리조나 대학교의 대학정보시스템이다.

키값으로 사용되는 값

• SSN : Social Security Number (사회보장번호)
• PCN : Position Control Number (직위 관리 번호)

EMPLOYEES(SSN, LAST_NAME, FIRST_NAME, ANNUAL_SALARY)
INCUMBENTS(SSN, PCN)
POSITIONS(PCN, JOB_TITLE_CODE1)
JOB_TITLES(JOB_TITLE_CODE, JOB_TITLE)

1. EMPLOYEES 테이블

1. INCUMBENTS 테이블

1. POSITIONS 테이블

1. JOB_TITLES 테이블

테이블 간 관계

• EMPLOYEES ←→ INCUMBENTS (1:N, SSN으로 연결)
• POSITIONS ←→ INCUMBENTS (1:N, PCN으로 연결)
• JOB_TITLES ←→ POSITIONS (1:N, JOB_TITLECODE로 연결)

특이사항

1. 각 테이블의 전체 컬럼 수
   • EMPLOYEES: 88개
   • INCUMBENTS: 12개
   • POSITIONS: 16개
   • JOB_TITLES: 11개
2. JOB_TITLES 특징
   • 7000개 이상의 직무 정의 포함
   • 직무 코드는 미래 확장성 고려하여 10자리로 설계

-- Bob의 Salary
SELECT * FROM EMPLOYEES
WHERE FIRST_NAME = 'Bob';

-- Bob의 의 POSITION
SELECT JOB_TITLE_CODE1 
FROM EMPLOYEES, INCUMBENTS, POSITIONS
WHERE FIRST_NAME = 'Bob'
  AND EMPLOYEES.SSN = INCUMBENTS.SSN
  AND INCUMBENTS.PCN = POSITIONS.PCN;

-- Bob의 생일
SELECT BIRTH_DATE
FROM EMPLOYEES
WHERE FIRST_NAME = 'Bob';

INCUMBENTS 테이블에 히스토리를 추가하기 위해 두 개의 컬럼을 추가하자.

1. START_DATE : 행에 있는 정보가 유효해진 시점. 즉, 해당 직원이 배정된 시점.
2. END_DATE : 행에 있는 정보가 유효하지 않아지는 시점. 즉, 해당 직원이 배정을 해제된 시점.

-- 추가 스크립트
ALTER TABLE INCUMBENTS ADD START_DATE DATE;
ALTER TABLE INCUMBENTS ADD END_DATE DATE;

-- 기존 테이블
INCUMBENTS(SSN, PCN)

-- 변경된 테이블
INCUMBENTS(SSN, PCN, START_DATE, END_DATE)

BIRTH_DATE 도 시간을 표현하는 DATE 타입이지만 그 의미는 전혀 다르다. BIRTH_DATE 는 기본키를 제외하고는 테이블의 다른 부분과 독립적이다. 반면 (START_DATE, END_DATE) 는 다른 컬럼들의 유효기간(valid-time)을 지정한다는 점에서 밀접하게 상호작용한다.

이러한 테이블을 valid-time 테이블이라고 한다. INCUMBENTS 테이블에서는 PCN이 유효한 기간을 지정한다. (SSN은 PK이다.) PCN이 변경된다면 기존 유효기간은 끝나고(END_DATE) 새로운 유효기간을 가진 다른 행이 추가된다.

만약 이러한 시간적 지원(temporal support)가 없다면 원래 테이블은 스냅샷 테이블(snapshot table)이라고 할 수 있다. 특정 시점의 상태를 캡처한다. 스냅샷 테이블은 일반적으로 최신 상태로 유지되어 "crrent" 현실을 포착한다.

다음은 INCUMBENTS 테이블 예시:

먼저 테이블을 org mode 형식으로 표현하겠습니다:

다른 직원 444-33-2222는 다음 해 초부터 채용되어 같은 직위를 계속 유지하고 있다.

이 테이블에는 몇 가지 흥미로운 특징이 있다.

1. 첫째, Bob(SSN=111223333)이 네 개의 행에 나타나지만, 어느 한 시점에서도 오직 하나의 직위(PCN)만 가지고 있었다. (이 테이블에서는 닫힌-열린, closed-open interval, 기간이 사용됨). 이 특정 테이블의 후보 키는 (SSN, PCN, START_DATE), (SSN, PCN, END_DATE), 그리고 (SSN, PCN, START_DATE, END_DATE). 곧 보겠지만, 이들 중 어느 것도 원하는 제약조건을 포착하지 못한다.
2. 둘째, 두 직원 모두 직위 이력에 공백이 없다.(공백은 SSN과 연관된 PCN이 없는 기간을 의미함). 공백을 허용하지 않는 것이 적절할 수도 있고 허용할 수 없을 수도 있는데, 이는 애플리케이션의 의미에 따라 다르다.

3. 세 번째 관찰은 이 테이블이 특정 날짜에 유효한 스냅샷 테이블들의 시퀀스를 컴팩트하게 표현한 것으로 볼 수 있다는 점. 이 긴 스냅샷 테이블 시퀀스는 각 행에 기간을 연관시킴으로써 이 다섯 행에 매우 효율적으로 인코딩되어 있다.

   즉, valid-time 테이블은 시간에 따라 회사의 모든 상태변화를 효율적으로(이해하기 쉽게) 저장한다. START_DATE, END_DATE 로 테이블의 변화가 있을 때만 저장하기 때문에 효율적으로 저장된다고 볼 수 있는 것이다. (초단위, 일단위 백업 같은 것이 아니라 변화가 있을 때만 저장한다)

   1. 1996년 1월 1일에 유효한 스냅샷 테이블은 한 행(111223333, 900225)을 포함.
   2. 1996년 9월 13일에 유효한 스냅샷 테이블도 한 행(111223333, 120033)을 포함.
   3. 1997년 2월 22일에 유효한 테이블은 두 행(111223333, 137112)과 (444332222, 120033)을 포함.

또한 두 번째와 세 번째 행이 동일한 SSN과 PCN 값을 가지고 있지만, 이 기간들이 겹치지 않기 때문에 스냅샷 테이블들의 어느 것에서도 중복을 나타내지 않는다는 점에 주목. 실제로 이 행들을 [1996-06-01 Sat] 기간과 연관된 단일 행으로 대체하는 것이 가능할 수도 있다.

valid time 테이블 이전에는 시간이 없이도 PK를 만들 수 있었다.

-- INCUBMENTS의 기본키
INCUMBENTS(SSN, PCN)
ALTER TABLE INCUMBENTS ADD PRIMARY KEY (SSN, PCN);

이는 어떤 직원(SSN 값으로 식별됨)도 동시에 같은 직위(PCN)을 중복으로 가질 수 없다는 의미.

이력(history)가 추가되면 중복이 발생할 수 있다. 이를 막기 위해 둘 중 하나의 시간 축이 PK 에 추가되어야 한다고 볼 수 있다. 예를들어 (SSN, PCN, START_DATE) 는 유효기간이 겹치지 않는 경우 유효하다. 하지만 이는 페이크이다. 어떤 날짜를 선택하든 이 키는 유효기간이 겹치는 경우가 있을 수 있다.

아래 데이터는 PK 제약은 통과하지만 유효기간이 겹치는 경우이다. 시간 정보 컬럼을 PK에 추가했다고 non-temporal PK가 temporal PK로 변환되는 것이 아니다.

| SSN       | PCN     | START_DATE | END_DATE   |
|-----------+---------+------------+------------|
| 111223333 | 900225  | 1996-01-01 | 1996-06-01 |
| 111223333 | 120033  | 1996-04-01 | 1996-10-01 |

우리에게 지금 필요한 것은 ~sequenced contraint(순차적제약)~ 이다.
이는 (SSN, PCN)이 동일한 시점에 동시에 존재하면 안된다는 것을 보장한다.

#+begin_src sql
CREATE ASSERTION seq_primary_key
-- 첫번째 조건
-- 동일한 SSN과 PCN을 가진 행이 내가 주입하려는 (START_DATE, END_DATE) 기간동안 동시에 존재하면 안된다.
CHECK (NOT EXISTS (SELECT *
		   FROM INCUMBENTS AS I1,
		   WHERE 1 < (SELECT COUNT(SSN)
			      FROM INCUMBENTS AS I2
			      WHERE I1.SSN = I2.SSN
				AND I1.PCN = I2.PCN
				-- 이 부분은 SQL-92 의 OVERLAPS 연산자를 사용하면 더 쉽게 표현할 수 있다.
				AND I1.START_DATE < I2.END_DATE
				AND I1.END_DATE > I2.START_DATE))
      -- 두번째 조건
      -- SSN과 PCN이 NULL이면 안된다.
      AND NOT EXISTS (SELECT *
		      FROM INCUMBENTS AS I1,
		      WHERE I.SSN IS NULL OR I.PCN IS NULL)
)

종합하면, 이 ASSERTION은

1. (SSN, PCN)이 같으면서 시간 구간이 겹치는 행이 존재하지 않아야 한다 (즉, 시점마다 (SSN, PCN) 쌍이 유일해야 한다)
2. SSN, PCN은 절대 NULL이 될 수 없다

위 SQL에서 중첩된 SELECT 문의 마지막 조건절은 SQL-92 의 OVERLAPS 연산자를 사용하면 더 쉽게 표현할 수 있다.

AND (I1.START_DATE, I1.END_DATE) OVERLAPS (I2.START_DATE, I2.END_DATE)

지금까지는 날짜의 범위가 open-closed (START_DATE, END_DATE] 형태였다. 만약에 closed-closed 형태면 조건절이 다음과 같이 변경된다.

CREATE ASSERTION seq_primary_key
-- 첫번째 조건
-- 동일한 SSN과 PCN을 가진 행이 내가 주입하려는 (START_DATE, END_DATE) 기간동안 동시에 존재하면 안된다.
CHECK (NOT EXISTS (SELECT *
		   FROM INCUMBENTS AS I1,
		   WHERE 1 < (SELECT COUNT(SSN)
			      FROM INCUMBENTS AS I2
			      WHERE I1.SSN = I2.SSN
				AND I1.PCN = I2.PCN
				-- 이부분에서 동등(eq) 연산이 추가됨
				AND I1.START_DATE <= I2.END_DATE
				AND I1.END_DATE >= I2.START_DATE))
      -- 두번째 조건
      -- SSN과 PCN이 NULL이면 안된다.
      AND NOT EXISTS (SELECT *
		      FROM INCUMBENTS AS I1,
		      WHERE I.SSN IS NULL OR I.PCN IS NULL)
)

MYSQL 에서는 ASSERTION 을 지원하지 않는다. 대신 트리거를 사용해야 한다.

mysql 에서 테스트를 해보자.

-- 테이블 생성
CREATE TABLE INCUMBENTS (
    SSN VARCHAR(9) NOT NULL,
    PCN VARCHAR(6) NOT NULL,
    START_DATE DATE NOT NULL,
    END_DATE DATE NOT NULL,
    POSITION_NAME VARCHAR(100),  -- 직위명 (테스트용 추가 컬럼)
    DEPARTMENT VARCHAR(50),      -- 부서 (테스트용 추가 컬럼)
    SALARY DECIMAL(10,2),        -- 급여 (테스트용 추가 컬럼)
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 트리거 생성
DELIMITER //

CREATE TRIGGER incumbents_insert_check
BEFORE INSERT ON INCUMBENTS
FOR EACH ROW
BEGIN
    -- NULL 체크
    IF NEW.SSN IS NULL OR NEW.PCN IS NULL THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'SSN and PCN cannot be NULL';
    END IF;

    -- 기간 중복 체크
    IF EXISTS (
	SELECT 1 
	FROM INCUMBENTS 
	WHERE SSN = NEW.SSN 
	  AND PCN = NEW.PCN
	  AND START_DATE < NEW.END_DATE 
	  AND END_DATE > NEW.START_DATE
    ) THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Overlapping period exists for the same SSN and PCN';
    END IF;
END//

CREATE TRIGGER incumbents_update_check
BEFORE UPDATE ON INCUMBENTS
FOR EACH ROW
BEGIN
    -- NULL 체크
    IF NEW.SSN IS NULL OR NEW.PCN IS NULL THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'SSN and PCN cannot be NULL';
    END IF;

    -- 기간 중복 체크 (자기 자신 제외)
    IF EXISTS (
	SELECT 1 
	FROM INCUMBENTS 
	WHERE SSN = NEW.SSN 
	  AND PCN = NEW.PCN
	  AND START_DATE < NEW.END_DATE 
	  AND END_DATE > NEW.START_DATE
	  AND NOT (START_DATE = OLD.START_DATE AND END_DATE = OLD.END_DATE)
    ) THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'Overlapping period exists for the same SSN and PCN';
    END IF;
END//

DELIMITER ;

-- 테스트 데이터 삽입
-- 1. 정상 케이스: 겹치지 않는 기간
INSERT INTO INCUMBENTS (SSN, PCN, START_DATE, END_DATE, POSITION_NAME, DEPARTMENT, SALARY)
VALUES ('111223333', '900225', '1996-01-01', '1996-06-01', 'Junior Developer', 'IT', 50000.00);

INSERT INTO INCUMBENTS (SSN, PCN, START_DATE, END_DATE, POSITION_NAME, DEPARTMENT, SALARY)
VALUES ('111223333', '900225', '1996-06-02', '1996-12-31', 'Senior Developer', 'IT', 70000.00);

-- 2. 에러 케이스: 기간 중복
-- 이 INSERT는 에러를 발생시킴
INSERT INTO INCUMBENTS (SSN, PCN, START_DATE, END_DATE, POSITION_NAME, DEPARTMENT, SALARY)
VALUES ('111223333', '900225', '1996-06-01', '1996-07-01', 'Lead Developer', 'IT', 90000.00);
-- Query 1 ERROR at Line 1: : Overlapping period exists for the same SSN and PCN


-- 3. 다른 직원의 같은 포지션 (정상 케이스)
INSERT INTO INCUMBENTS (SSN, PCN, START_DATE, END_DATE, POSITION_NAME, DEPARTMENT, SALARY)
VALUES ('444556666', '900225', '1996-01-01', '1996-12-31', 'Junior Developer', 'IT', 55000.00);

-- 테스트 쿼리
SELECT * FROM INCUMBENTS ORDER BY SSN, START_DATE;

-- 특정 날짜의 재직자 조회
SELECT * 
FROM INCUMBENTS 
WHERE '1996-06-01' BETWEEN START_DATE AND END_DATE;

데이터베이스의 현재(NOW) 날짜에 존재하는 데이터를 알기 위해 END_DATE를 사용하는 것이 가장 쉬운 방법이다.

문제점으로는 어떻게 END_DATE를 설정할 것인가이다.

1. 시도방안 1: 1860-01-01 사용 (존재하지 않을 특정 과거값) WHERE INCUMBENTS.END_DATE = DATE '1860-01-01'
   • 장점 : 종료일 정렬 시 현재 데이터가 먼저 표시됨
   • 단점 : 너무 이상한 매직넘버
2. 시도방안 2 : NULL 주입 WHERE INCUMBENTS.END_DATE IS NULL
   • 장점 : 더 읽기 쉬운 조건식
   • 단점 : 사용자 혼란, NULL 값 비교(<=, < 등)에서 null이 존재하면 필터에서 빠지는 경우 발생, 알수없음과 현재를 구분못함
3. 시도방안 3 : 최대 가능한 미래날짜 사용

WHERE INCUMBENTS.START_DATE <= CURRENT_DATE
AND CURRENT_DATE < INCUMBENTS.END_DATE

• 장점 : 대부분 데이터베이스 호환
• 단점 : 현재가 미래는 아니지만 (영원으로 대충 넘어감)

1. temporal table 기본 개념
2. valid-time state table 은 모델링된 현실(modeled reality)의 이력을 유지(retain)한다.
3. 테이블은 하나 이상의 타임스탬프 열을 추가하여 각 행의 유효기간을 지정함으로써 템포럴(시간성)을 표시한다.
4. SQL은 이런 유형을 제공하지 않기 때문에, 일반적으로 두 개의 datetime 열을 추가하여 기간의 시작,끝을 표시하다.
5. 표현은 open-closed를 선호한다.
6. 현재 시간("now") 처리
7. 현재 유효한 데이터를 나타낼 때는 종료시간을 논리적 'now' 로 표현한다.
8. SQL은 CURRENT DATE를 지원하지만 저장된 값으로 지원하진 않는다. (개인적으로 이런개념은 생각해본 적도 없다)
9. 가능한 값으로 고유한 값 (책에서는 1860-01-01 이나 3000-01-01 을 상수로 사용한다 혹은 NULL 도 가능)
10. 가장 중요한 것은 '영원히'에 가까운 값을 사용하는 것이다.
11. 고유성 제약조건의 4가지 유형
12. 현재(Current): 현재 유효한 행들 간의 고유성
13. 값-동등(Value-equivalent): 비시간 열들의 고유성
14. 순차적(Sequenced): 모든 시점에서의 고유성
15. 비순차적(Nonsequenced): 모든 열에 대한 고유성
16. 참조 무결성 처리
17. 참조하는/참조되는 테이블의 시간적 특성에 따라 다른 제약조건 적용
18. 두 테이블이 모두 시간적일 경우 순차적 제약조건 사용