Relational Modeling & Schema Mechanics

1. Overview

관계형 모델링과 스키마 역학(Relational Modeling & Schema Mechanics)은 "데이터의 중복을 배제하고 무결성을 강제한다"는 에드거 코드(E.F. Codd)의 관계형 대수(Relational Algebra) 철학을 기반으로, 비즈니스 요구사항을 일관된 데이터베이스 테이블 구조로 변환하는 아키텍처 설계 역량을 다룹니다.

학습자는 개체-관계 모델(ERM)을 통해 현실 세계를 엔티티(Entity)와 관계(Relationship)로 추상화하고, 논리적/물리적 스키마로 변환하는 과정을 살펴봅니다. 나아가 이상 현상(Anomaly)을 제거하기 위한 제1/2/3 정규화(Normalization)와 BCNF의 수학적 규칙을 이해하며, 데이터 읽기 성능을 위해 의도적으로 중복을 허용하는 **반정규화(Denormalization)**의 트레이드오프를 정리합니다. 마지막으로 외래 키(Foreign Key)와 제약조건(Constraints)을 통해 데이터의 참조 무결성을 런타임에 강제하는 시스템적 안전망 구축 메커니즘을 익힙니다.

2. Scope & Boundaries

In-Scope

• 관계형 이론 (Relational Theory): 튜플(Tuple), 릴레이션(Relation), 속성(Attribute), 관계형 대수(선택, 투영, 조인).
• 데이터 모델링 (Data Modeling): 개념적 설계(ERD), 논리적 설계(테이블 매핑, 식별/비식별 관계), 물리적 설계(타입, 인덱스).
• 정규화 (Normalization): 함수적 종속성(Functional Dependency), 1NF(원자성), 2NF(부분 종속 제거), 3NF(이행적 종속 제거), BCNF.
• 무결성과 제약 (Integrity & Constraints): 참조 무결성(Referential Integrity), 외래 키(FK) Cascade, Check 제약.

Out-of-Scope

• SQL 쿼리 작성 및 튜닝: SELECT, JOIN 작성 및 쿼리 실행 계획 분석 → 06-01-02 SQL Engineering 영역으로 위임.
• 트랜잭션(ACID) 및 동시성 제어: 락(Lock)과 격리 수준 → 06-01-03 Transaction Mechanics 영역.

Boundaries

• 정규화(Normalization) vs 반정규화(Denormalization): 철저한 3NF 정규화는 데이터 쓰기(Insert/Update) 시 정합성을 높게 보장하지만, 읽기(Select) 시 수많은 테이블을 조인(Join)해야 하므로 성능이 저하됩니다. 반정규화는 조회 성능을 위해 의도적으로 중복 데이터를 허용하지만, 업데이트 시 애플리케이션 레벨에서 여러 곳의 데이터를 동시에 수정해야 하는 동기화 부담(데이터 불일치 위험)을 감수하는 트레이드오프입니다. OLTP 환경에서는 3NF를 기본으로 하되, 조회 병목이 심한 특정 화면에 한해서만 보수적으로 반정규화를 적용해야 합니다.

3. Counterexample

• 3NF 미준수로 인한 갱신 이상 (Update Anomaly): 학생_수강(학번, 이름, 학과, 학과장, 과목코드) 테이블. 한 학생이 3과목을 들으면 이름, 학과, 학과장 데이터가 3번 중복 저장됩니다(3NF 위반: 학과 $\rightarrow$ 학과장 이행적 종속). 학과장이 변경되었을 때, 이 학생의 레코드 3개 중 2개만 수정되는 버그가 발생하면 동일한 학과인데 어떤 과목 레코드에서는 학과장이 다르게 나오는 데이터 불일치(Inconsistency)가 발생합니다.
• 과도한 식별 관계(Identifying Relationship) 얽힘: 부모 테이블의 PK를 자식 테이블의 PK 일부로 포함시키는 식별 관계를 남용. 고객 $\rightarrow$ 주문 $\rightarrow$ 결제 $\rightarrow$ 배송. 배송 테이블의 PK가 (고객ID, 주문ID, 결제ID, 배송순번)이라는 큰 복합키로 비대해집니다. JPA 등 ORM 매핑이 복잡해지고, 중간에 요구사항이 변경되어 주문 없이 결제가 선행되는 비즈니스가 생기면 스키마 전체를 다시 고쳐야 합니다. 최신 설계에서는 논리적 연관이 있더라도 인공 키(Surrogate Key, Auto Increment ID)를 PK로 쓰고 비식별 관계로 연결하는 것이 유지보수성을 높입니다.

4. Prerequisites

• 집합론 기초 (Basic): 교집합, 합집합, 카테시안 곱 등 관계형 대수의 근간이 되는 수학. (01-01-01 Set Theory & Relations)
• 자료구조 테이블 (Basic): 해시맵이나 2차원 배열의 기본 데이터 저장 개념. (04-02 Core Data Structures)

5. Learning Map

6. Learning Topics

Basic

Core Topic 01: 세상을 표와 관계로 분해하다, 관계형 모델과 ERD (Relational Algebra & ERD)

• Why to Learn: 무질서한 비즈니스 요구사항("회원은 여러 주문을 할 수 있고, 주문에는 여러 상품이 들어간다")을 데이터베이스가 이해할 수 있는 수학적 집합(Relation)과 연결 고리로 정확히 통역하기 위함입니다.
• What to Learn:
  • Concepts: 엔티티(Entity), 속성(Attribute), 관계(Relationship), 1<1>, 1, N 관계, 카디널리티(Cardinality), 관계형 대수(Relational Algebra).
  • Skills: ERD(Entity-Relationship Diagram) 작성(Crow's Foot 표기법), N 관계를 교차 테이블(Mapping Table)로 해소.
• How to Learn:
  • 1단계: 개념적 모델링: 요구사항에서 명사(회원, 주문, 상품)는 엔티티로, 동사(주문하다, 포함되다)는 관계로, 수식어(이름, 가격)는 속성으로 추출하는 과정을 살펴봅니다.
  • 2단계: N 관계 해소: '학생'과 '과목'은 N 관계. RDBMS는 N 직접 저장할 수 없으므로, 중간에 '수강'이라는 매핑 테이블을 두어 두 개의 1 관계로 나누는 관계형 대수적 방식을 살펴봅니다.
• Implement: 식당 배달 앱 ERD 설계 시뮬레이션. User, Restaurant, Order, Menu, Order_Item 엔티티 도출. Draw.io 또는 PlantUML로 Crow's foot(까마귀 발) 표기법을 적용하여 1 식별/비식별 관계가 명시된 ER 다이어그램 텍스트 기반 작성 데모.

Recommended

Core Topic 02: 중복과 모순을 줄이는 정규화와 함수적 종속 (Normalization)

• Why to Learn: 테이블에 데이터를 Insert, Update, Delete 할 때 발생하는 데이터 이상 현상(Anomaly)을, 함수적 종속(Functional Dependency)이라는 명확한 수학적 규칙(1NF~BCNF)으로 사전에 차단하기 위함입니다.
• What to Learn:
  • Concepts: 이상 현상(삽입/수정/삭제 이상), 함수적 종속성($X \rightarrow Y$), 1NF(원자성), 2NF(부분 종속성 제거), 3NF(이행적 종속성 제거), BCNF(모든 결정자가 후보키).
  • Skills: 잘못 설계된 엑셀 시트 형태의 테이블을 3NF로 분해.
• How to Learn:
  • 1단계: 함수적 종속성 식별: 학번을 알면 이름이 고정된다 (학번 $\rightarrow$ 이름). 데이터베이스 설계는 이 $X \rightarrow Y$ 화살표를 찾아내어 같은 화살표를 공유하는 것들끼리 테이블을 분해하는 과정임을 살펴봅니다.
  • 2단계: 제3정규형(3NF): 주키(PK)가 아닌 일반 속성들끼리 종속되는 경우. (예: 주문번호 $\rightarrow$ 고객ID $\rightarrow$ 고객등급). 고객ID가 일반 속성이면서 고객등급을 결정(이행적 종속)하므로, 고객 테이블을 별도로 분리해야 갱신 이상이 발생하지 않음을 살펴봅니다.
• Implement: 파이썬 정규화 검증 스크립트. 비정규화된 JSON 데이터(order_id, cust_id, cust_grade, item_id, item_price) 리스트. 동일한 cust_id인데 cust_grade가 다른 데이터가 삽입될 때 이를 "3NF 위반: 갱신 이상 감지"로 판정하고 에러를 발생시키는 무결성 테스터 시뮬레이션.

Practical

Core Topic 03: 애플리케이션만 믿지 않는 인공 키와 참조 무결성 (Surrogate Keys & Constraints)

• Why to Learn: 애플리케이션 레벨의 버그(잘못된 로직으로 외래 키 무시 삭제 등)로 인해 DB 데이터베이스에 고아 레코드(Orphan Record)가 쌓이는 것을 막는 제약 기반 방어선을 구축하기 위함입니다.
• What to Learn:
  • Concepts: 자연 키(Natural Key, 예: 이메일, 주민번호) vs 인공 키(Surrogate Key, Auto Increment / UUID), 참조 무결성 제약(Foreign Key Constraint), ON DELETE CASCADE / RESTRICT, CHECK 제약.
  • Skills: 식별/비식별 관계 선택, 무결성 제약조건 작성.
• How to Learn:
  • 1단계: 인공 키의 역할: 비즈니스 정보인 이메일을 PK(자연 키)로 쓰면, 이메일 변경 요구사항 발생 시 수많은 자식 테이블의 FK까지 모두 업데이트(CASCADE)해야 하는 DB 락(Lock) 부담이 생깁니다. 의미 없는 숫자(Auto Increment ID)를 PK로 쓰는 이유를 살펴봅니다.
  • 2단계: 참조 무결성 방어: 부모 레코드가 지워질 때 자식 레코드 처리. RESTRICT(삭제 차단, 가장 안전), CASCADE(같이 연쇄 삭제). 비즈니스 로직에만 의존하지 않고 DB 엔진 자체가 제약 조건을 통해 고아 레코드 생성을 막는 메커니즘을 살펴봅니다.
• Implement: SQLite 기반 테이블 제약조건 데모. 파이썬 sqlite3 모듈로 FOREIGN KEY가 설정된 User와 Post 테이블 생성 (PRAGMA foreign_keys = ON;). User 삭제 시 Post에 묶인 외래 키로 인해 IntegrityError 예외가 파이썬 코드로 안전하게 전파되는 데이터베이스 쉴드 효과 시연.

Advanced

Core Topic 04: 읽기 성능을 위한 전략적 타협, 반정규화 (Denormalization Trade-offs)

• Why to Learn: 철저한 3NF 정규화는 정합성에 유리하지만, 대시보드 화면 하나를 그리기 위해 10개의 테이블을 조인(Join)하면서 서버 부하가 커질 수 있습니다. 데이터 중복의 위험을 감수하고 조회 성능을 높이는 아키텍트의 타협 기술을 익히기 위해서입니다.
• What to Learn:
  • Concepts: 반정규화(Denormalization), 파생 컬럼(Derived Column, 예: 총 결제금액), 요약 테이블(Summary Table), 조인 비용(Join Overhead), 정합성 유지 비용.
  • Skills: 읽기(Read) 빈도와 쓰기(Write) 빈도의 비율 분석, 반정규화 스키마 적용.
• How to Learn:
  • 1단계: 조인 부하와 파생 컬럼: 게시물의 총 댓글 수를 렌더링하기 위해 매번 SELECT COUNT(*) FROM Comments WHERE post_id=? 쿼리를 실행하면 DB 부하가 커집니다. Posts 테이블에 comment_count 파생 컬럼을 중복 추가(반정규화)하여 O(1) 조회를 달성하는 비용 최적화를 살펴봅니다.
  • 2단계: 정합성의 책임: 반정규화를 하면 DB가 더 이상 무결성을 단독으로 보장하지 않습니다. 댓글이 달릴 때 트랜잭션으로 comment_count += 1 업데이트를 반드시 함께 수행해야 하는, DB 엔진의 책임 일부를 애플리케이션 코드로 옮기는 아키텍처적 트레이드오프를 살펴봅니다.
• Implement: RDBMS 벤치마킹 스크립트 작성. 100만 건의 User, Order, Payment 정규화 조인 쿼리(사용자별 총 결제금액 조회) vs User 테이블에 추가된 total_payment 파생 컬럼 단일 조회. 10,000회 동시 조회 시 처리량(TPS)과 지연 시간(Latency) 비교 리포트 생성(반정규화가 보통 10~50배 빠름 증명).

7. Terminology

8. References

Primary

• [P1] CS2023 - Information Management (IM) - Relational Databases
• [P5] SFIA - Data Modeling and Design (DTAN)

Secondary

• [Database System Concepts] Abraham Silberschatz - Functional Dependency & Normalization
• [Fundamentals of Database Systems] Ramez Elmasri - Entity-Relationship Modeling

Industry

• [Oracle Database Documentation] - Schema Objects & Constraints
• [Martin Fowler's Blog] - Data Modeling & Architecture Trade-offs

9. Final Checklist

Primary

• 비즈니스 명세서에서 엔티티와 관계를 도출하여 Crow's Foot 표기법의 ERD를 작성할 수 있는가?
• 함수적 종속성을 식별하여 테이블을 1NF, 2NF, 3NF로 분해하는 논리적 과정을 설명할 수 있는가?

Secondary

• 식별 관계와 비식별 관계의 차이를 이해하고, 인공 키(Surrogate Key)를 사용해야 하는 이유를 논증할 수 있는가?
• 삽입 이상, 갱신 이상, 삭제 이상(Anomaly)의 실제 사례를 들고 정규화가 이를 어떻게 차단하는지 설명할 수 있는가?

Industry

• 데이터 읽기(Select) 속도 저하를 해결하기 위해 파생 컬럼을 추가하는 반정규화(Denormalization)의 트레이드오프를 설계할 수 있는가?
• 외래 키(Foreign Key) 제약조건과 ON DELETE CASCADE의 런타임 방어 메커니즘을 시스템에 적용할 수 있는가?