-- 非正規化テーブル: 更新異常が発生する構造
CREATE TABLE emp_dept_denormalized (
    emp_id    INTEGER PRIMARY KEY,
    emp_name  VARCHAR(100),
    dept_id   INTEGER,
    dept_name VARCHAR(100),
    dept_loc  VARCHAR(100)
);
 
INSERT INTO emp_dept_denormalized VALUES
(1, '田中', 10, '開発部', '東京'),
(2, '鈴木', 10, '開発部', '東京'),
(3, '佐藤', 20, '営業部', '大阪'),
(4, '高橋', 10, '開発部', '東京');
 
-- 挿入異常の実演: 社員なしで新部署を登録するには？
-- emp_idがPRIMARY KEYなのでNULLにできない → 登録不可能
-- INSERT INTO emp_dept_denormalized VALUES (NULL, NULL, 30, '経理部', '名古屋');
-- → ERROR: null value in column "emp_id"
 
-- 更新異常の実演: 開発部を東京から横浜に移転
UPDATE emp_dept_denormalized SET dept_loc = '横浜' WHERE dept_id = 10;
-- → 3行を更新する必要がある（漏れると不整合）
-- 10万人の部署なら10万行の更新が必要
 
-- 削除異常の実演: 佐藤を削除すると営業部の情報も消失
DELETE FROM emp_dept_denormalized WHERE emp_id = 3;
-- → dept_id=20の情報が完全に失われる

-- 非正規形: 繰り返し項目がある
-- ┌────┬──────┬─────────────────────┐
-- │ id │ name │ phones              │
-- ├────┼──────┼─────────────────────┤
-- │ 1  │ 田中 │ 090-1111, 03-2222   │ ← 1セルに複数値
-- └────┴──────┴─────────────────────┘
 
-- 第1正規形（1NF）: 各セルに原子値（Atomic Value）のみ
CREATE TABLE contacts (
    id    INTEGER,
    name  VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY (id, phone)  -- 複合主キー
);
 
INSERT INTO contacts VALUES (1, '田中', '090-1111-2222');
INSERT INTO contacts VALUES (1, '田中', '03-2222-3333');
 
-- 1NF の要件:
-- 1. 各列の値が原子的（分割不可能）
-- 2. 繰り返しグループがない
-- 3. 行の順序に意味がない
-- 4. 各行が一意に識別可能（主キーが存在）

-- 1NFだが2NFでない例:
-- 注文明細テーブル
-- PK = (order_id, product_id)
-- ┌──────────┬────────────┬──────────────┬──────────┬───────┐
-- │ order_id │ product_id │ product_name │ quantity │ price │
-- └──────────┴────────────┴──────────────┴──────────┴───────┘
--   product_name は product_id のみに従属（部分関数従属）
--
-- 関数従属の分析:
--   {order_id, product_id} → quantity（完全関数従属 ✓）
--   {order_id, product_id} → price（完全関数従属 ✓）
--   product_id → product_name（部分関数従属 ✗）
 
-- 第2正規形（2NF）: 部分関数従属を排除
CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(100)           -- product_id のみに従属
);
 
CREATE TABLE order_items (
    order_id   INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(product_id),
    quantity   INTEGER,
    price      DECIMAL(10,2),          -- 注文時の価格（スナップショット）
    PRIMARY KEY (order_id, product_id)  -- 全キーに従属
);
 
-- 2NF の要件:
-- 1. 1NFを満たす
-- 2. 非キー属性が主キーの一部にのみ従属しない
--    （主キーが単一列なら自動的に2NF）
 
-- 重要な注意: priceは注文時のスナップショットとして
-- order_itemsに残すのが正しい設計（商品の現在価格とは別）

-- 2NFだが3NFでない例:
-- ┌────┬──────┬─────────────┬────────────────┐
-- │ id │ name │ dept_id     │ dept_name      │
-- └────┴──────┴─────────────┴────────────────┘
-- dept_name は dept_id に従属し、dept_id は id に従属
-- → dept_name は id に推移的に従属
--
-- 関数従属の分析:
--   id → name（直接従属 ✓）
--   id → dept_id（直接従属 ✓）
--   dept_id → dept_name（推移的従属 ✗）
--   ∴ id → dept_id → dept_name
 
-- 第3正規形（3NF）: 推移的関数従属を排除
CREATE TABLE departments (
    dept_id   INTEGER PRIMARY KEY,
    dept_name VARCHAR(100)
);
 
CREATE TABLE employees (
    id      INTEGER PRIMARY KEY,
    name    VARCHAR(100),
    dept_id INTEGER REFERENCES departments(dept_id)
);
 
-- 3NF の要件:
-- 1. 2NFを満たす
-- 2. 非キー属性が他の非キー属性に従属しない
--    （非キー→非キーの関数従属がない）
 
-- 3NFの形式的定義（Coddの定義）:
-- テーブルRが3NFであるとは、全ての非自明な関数従属 X → A について
-- 以下のいずれかが成り立つことである:
-- (a) Xがスーパーキーである
-- (b) Aがいずれかの候補キーの一部（素属性）である

-- 3NFだがBCNFでない例:
-- 学生の講義登録（1講義に複数の教員が担当可能、
--                 各教員は1つの講義のみ担当）
-- PK = (student_id, course_id)
-- 関数従属: teacher_id → course_id
--          （教員がどの講義を担当するかは一意に決まる）
 
-- 3NFではteacher_idは非キーだがcourse_id（キーの一部）を決定
-- → BCNFに違反
 
-- BCNF化:
CREATE TABLE teacher_courses (
    teacher_id INTEGER PRIMARY KEY,
    course_id  INTEGER REFERENCES courses(id)
);
 
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    teacher_id INTEGER REFERENCES teacher_courses(teacher_id),
    PRIMARY KEY (student_id, teacher_id)
);
 
-- BCNF の定義:
-- 全ての非自明な関数従属 X → Y について、Xがスーパーキーである
--
-- 3NFとBCNFの違い:
-- 3NFは「Yが素属性ならOK」という例外を許す
-- BCNFは例外なく「決定項はスーパーキー」を要求
 
-- BCNFの注意点:
-- BCNFに分解すると、元の関数従属が保存されない場合がある
-- （従属性保存分解ができないケースがある）

-- より具体的なBCNF違反の例: 配送スケジュール
--
-- 前提条件:
-- 1. 各配送地域には複数の配送業者が対応可能
-- 2. 各配送業者は1つの配送地域のみを担当
-- 3. 1つの注文に対して1つの配送業者が割り当てられる
--
-- テーブル: deliveries
-- PK = (order_id, area_id)
-- 関数従属:
--   {order_id, area_id} → carrier_id（完全関数従属）
--   carrier_id → area_id（業者が担当地域を決定）
--
-- carrier_idは非キーだがarea_id（キーの一部）を決定 → BCNF違反
 
-- 3NF（BCNF違反を含む）
CREATE TABLE deliveries_3nf (
    order_id   INTEGER,
    area_id    INTEGER,
    carrier_id INTEGER,
    PRIMARY KEY (order_id, area_id)
    -- carrier_id → area_id の従属が問題
);
 
-- BCNF（分解後）
CREATE TABLE carrier_areas (
    carrier_id INTEGER PRIMARY KEY,
    area_id    INTEGER NOT NULL
);
 
CREATE TABLE order_carriers (
    order_id   INTEGER,
    carrier_id INTEGER REFERENCES carrier_areas(carrier_id),
    PRIMARY KEY (order_id, carrier_id)
);
 
-- 分解の結果:
-- carrier_areas: carrier_id → area_id（carrier_idがキー ✓ BCNF）
-- order_carriers: 候補キー = {order_id, carrier_id}（BCNF ✓）

-- 4NF違反の例:
-- 社員が複数のスキルと複数の言語を持つ
-- スキルと言語は互いに独立
 
-- 4NF違反テーブル
CREATE TABLE emp_skills_languages_bad (
    emp_id   INTEGER,
    skill    VARCHAR(50),
    language VARCHAR(50),
    PRIMARY KEY (emp_id, skill, language)
);
 
-- データ例:
-- emp_id=1 が skill={Java, Python} と language={日本語, 英語} を持つ場合
INSERT INTO emp_skills_languages_bad VALUES
(1, 'Java',   '日本語'),
(1, 'Java',   '英語'),
(1, 'Python', '日本語'),
(1, 'Python', '英語');
-- → 2 × 2 = 4行（直積）が必要 → 冗長
 
-- 多値従属: emp_id →→ skill, emp_id →→ language
-- skillとlanguageは互いに独立なのに、直積を保持する必要がある
 
-- 4NF（多値従属を排除）
CREATE TABLE emp_skills (
    emp_id INTEGER,
    skill  VARCHAR(50),
    PRIMARY KEY (emp_id, skill)
);
 
CREATE TABLE emp_languages (
    emp_id   INTEGER,
    language VARCHAR(50),
    PRIMARY KEY (emp_id, language)
);
 
INSERT INTO emp_skills VALUES (1, 'Java'), (1, 'Python');
INSERT INTO emp_languages VALUES (1, '日本語'), (1, '英語');
-- → 2 + 2 = 4行で済む（直積なら4行必要だった）
 
-- 4NF の定義:
-- 全ての非自明な多値従属 X →→ Y について、Xがスーパーキーである

-- 5NF違反の例:
-- 代理店が供給者から商品を仕入れる関係
-- ただし、3者間の関係が2者間の関係から復元できない場合
 
-- 以下の3つの2項関係が成り立つ:
-- 代理店Aは供給者Xから仕入れる
-- 供給者Xは商品Pを供給する
-- 代理店Aは商品Pを扱う
-- → これらが成り立っても、「代理店Aが供給者Xから商品Pを仕入れる」
--   とは限らない（結合従属）
 
CREATE TABLE supply_3way (
    agent_id    INTEGER,
    supplier_id INTEGER,
    product_id  INTEGER,
    PRIMARY KEY (agent_id, supplier_id, product_id)
);
 
-- 5NFでは3者間関係は分解できない場合がある
-- ただし、ビジネスルールにより分解可能な場合もある:
-- 「代理店が供給者と取引し、その供給者が扱う商品を
--   代理店も扱っているなら、必ずその経路で仕入れる」
-- というルールがあれば、3つの2項テーブルに分解可能
 
-- 5NF の定義:
-- 全ての非自明な結合従属が候補キーにより暗示される

-- パターン1: 計算済みカラム（集約結果のキャッシュ）
ALTER TABLE orders ADD COLUMN item_count INTEGER DEFAULT 0;
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) DEFAULT 0;
 
-- トリガーで自動更新
CREATE OR REPLACE FUNCTION update_order_totals()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders SET
        item_count = (SELECT COUNT(*) FROM order_items WHERE order_id = NEW.order_id),
        total_amount = (SELECT SUM(price * quantity) FROM order_items WHERE order_id = NEW.order_id)
    WHERE id = NEW.order_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_update_order_totals
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION update_order_totals();
 
-- パターン2: マテリアライズドビュー
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders o
    JOIN products p ON o.product_id = p.id
GROUP BY 1, 2;
 
-- 定期的にリフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;
 
-- パターン3: 非正規化カラムの追加
-- 頻繁にJOINされるカラムを冗長に保持
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(200);
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
 
-- トリガーでcustomersテーブルとの同期を維持
CREATE OR REPLACE FUNCTION sync_customer_denorm()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_TABLE_NAME = 'customers' THEN
        UPDATE orders SET
            customer_name = NEW.name,
            customer_email = NEW.email
        WHERE customer_id = NEW.id;
    ELSIF TG_TABLE_NAME = 'orders' THEN
        SELECT name, email INTO NEW.customer_name, NEW.customer_email
        FROM customers WHERE id = NEW.customer_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- パターン4: JSONBによる半構造化データの格納
-- 頻繁にJOINされる関連データをJSONBとして埋め込む
ALTER TABLE orders ADD COLUMN items_snapshot JSONB;
 
-- 注文確定時にスナップショットを作成
UPDATE orders SET items_snapshot = (
    SELECT jsonb_agg(jsonb_build_object(
        'product_name', p.name,
        'quantity', oi.quantity,
        'price', oi.price
    ))
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = orders.id
)
WHERE id = 42;

-- 正規化されたスキーマ（3NF）
-- 6テーブルをJOINして注文詳細を取得
EXPLAIN ANALYZE
SELECT
    o.id, o.order_date,
    c.name AS customer, c.email,
    p.name AS product, p.sku,
    cat.name AS category,
    oi.quantity, oi.unit_price,
    a.city, a.postal_code
FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    JOIN categories cat ON p.category_id = cat.id
    JOIN addresses a ON o.shipping_address_id = a.id
WHERE o.id = 42;
-- → 実行計画: 5つのNested Loop Join、推定50ms
 
-- 非正規化されたスキーマ（読み取り最適化）
-- 1テーブルで完結
EXPLAIN ANALYZE
SELECT
    order_id, order_date,
    customer_name, customer_email,
    product_name, product_sku,
    category_name,
    quantity, unit_price,
    shipping_city, shipping_postal_code
FROM order_details_denormalized
WHERE order_id = 42;
-- → 実行計画: Index Scan のみ、推定2ms
 
-- 中間的なアプローチ: マテリアライズドビュー
CREATE MATERIALIZED VIEW mv_order_details AS
SELECT
    o.id AS order_id, o.order_date,
    c.name AS customer_name, c.email AS customer_email,
    p.name AS product_name, p.sku AS product_sku,
    cat.name AS category_name,
    oi.quantity, oi.unit_price,
    a.city AS shipping_city, a.postal_code AS shipping_postal_code
FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    JOIN categories cat ON p.category_id = cat.id
    JOIN addresses a ON o.shipping_address_id = a.id;
 
CREATE UNIQUE INDEX idx_mv_order_details_order_id ON mv_order_details(order_id);
-- → 読み取りは高速、データ更新はREFRESHで制御

-- 3NFテーブルでも適切なインデックスで高速化
CREATE INDEX idx_employees_dept ON employees(department_id);
CREATE INDEX idx_departments_pk ON departments(dept_id);
 
-- オプティマイザはNested Loop + Index Scanを選択
EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e
    JOIN departments d ON e.department_id = d.dept_id
WHERE e.salary > 500000;
 
-- 結果例:
-- Nested Loop  (actual time=0.030..0.150 rows=100 loops=1)
--   -> Index Scan using idx_emp_salary on employees e
--      Filter: (salary > 500000)
--      Rows Removed by Filter: 400
--   -> Index Scan using departments_pkey on departments d
--      Index Cond: (dept_id = e.department_id)
-- Execution Time: 0.200 ms
-- → 正規化テーブルでも十分高速（インデックスが適切なら）

-- NG: 汎用的だが正規化の恩恵を全く受けられない
CREATE TABLE entity_attributes (
    entity_id  INTEGER,
    attr_name  VARCHAR(100),
    attr_value TEXT,
    PRIMARY KEY (entity_id, attr_name)
);
 
-- 問題点:
-- 1. 型安全性がない（全てTEXT）
-- 2. 制約が使えない（NOT NULL、CHECK等）
-- 3. JOINが複雑化（属性ごとにSelf JOIN）
-- 4. クエリが非効率
-- 5. 外部キー制約が使えない
-- 6. 集約関数が使えない（文字列なのでSUM不可）
 
-- EAVでピボットクエリを書く場合の困難さ:
SELECT
    e.entity_id,
    MAX(CASE WHEN ea.attr_name = 'name' THEN ea.attr_value END) AS name,
    MAX(CASE WHEN ea.attr_name = 'email' THEN ea.attr_value END) AS email,
    MAX(CASE WHEN ea.attr_name = 'age' THEN ea.attr_value END)::INTEGER AS age
FROM entities e
    LEFT JOIN entity_attributes ea ON e.id = ea.entity_id
GROUP BY e.entity_id;
-- → 属性が増えるたびにクエリの修正が必要
 
-- OK: JSONBでスキーマレスな部分を分離
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    attrs JSONB  -- 可変属性はJSONBに格納
);
 
-- JSONBならGINインデックスで効率的に検索可能
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);
 
-- 特定属性で検索
SELECT * FROM products WHERE attrs @> '{"color": "red"}';
 
-- 属性の存在確認
SELECT * FROM products WHERE attrs ? 'weight';

-- NG: 都道府県や性別まで別テーブルに分離
CREATE TABLE genders (id INT PRIMARY KEY, name VARCHAR(10));
CREATE TABLE prefectures (id INT PRIMARY KEY, name VARCHAR(10));
-- → JOINが増え、クエリが複雑化し、パフォーマンスが低下
-- → 47都道府県のマスタテーブルのためにJOINが1つ増える
 
-- OK: 変更されない小さなマスタはENUMやCHECK制約で十分
CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    gender   VARCHAR(10) CHECK (gender IN ('male', 'female', 'other')),
    prefecture VARCHAR(10) NOT NULL
);
 
-- 判断基準:
-- マスタデータが以下の全てを満たすなら正規化不要:
-- ✓ 値の数が少ない（50以下）
-- ✓ 変更頻度が極めて低い
-- ✓ 追加の属性を持たない
-- ✓ 他のテーブルから参照されない

-- NG: ログテーブルに冗長データを無秩序に格納
CREATE TABLE activity_logs (
    id         BIGSERIAL PRIMARY KEY,
    user_id    INTEGER,
    user_name  VARCHAR(100),   -- usersテーブルと冗長
    user_email VARCHAR(255),   -- usersテーブルと冗長
    action     VARCHAR(50),
    target_id  INTEGER,
    target_type VARCHAR(50),
    target_name VARCHAR(200),  -- 対象テーブルと冗長
    ip_address INET,
    created_at TIMESTAMPTZ
);
-- → ユーザー名変更時に過去ログとの不整合が発生
-- → ストレージが急速に肥大化
 
-- OK: ログテーブルはイベント時点のスナップショットとして設計
CREATE TABLE activity_logs (
    id         BIGSERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    action     VARCHAR(50) NOT NULL,
    target_id  INTEGER,
    target_type VARCHAR(50),
    -- スナップショット（意図的な非正規化）
    snapshot   JSONB NOT NULL DEFAULT '{}',
    ip_address INET,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
 
-- snapshotには「その時点の状態」を記録
-- → 正規化テーブルとは別の設計意図
-- → 監査・コンプライアンス要件を満たす

-- PostgreSQLの配列型は厳密には1NF違反だが、実務では有用
CREATE TABLE articles (
    id   SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags  TEXT[] NOT NULL DEFAULT '{}'
);
 
-- GINインデックスで効率的に検索可能
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
 
-- 配列内の値で検索
SELECT * FROM articles WHERE tags @> ARRAY['SQL', 'データベース'];
 
-- いつ配列型を使うべきか:
-- ✓ 値の集合を1つのエンティティに関連付ける場合
-- ✓ 値に追加属性がない場合（タグ名だけなど）
-- ✓ 値の個数が少ない場合（数十個以下）
-- ✗ 値に属性がある場合は正規化（中間テーブル）を使う
-- ✗ 値が他のエンティティへの参照の場合は正規化

-- 時系列データは正規化の適用が難しい
-- センサーデータの例: 1秒に1行、100万行/日
 
-- 純粋な正規化アプローチ
CREATE TABLE sensors (
    sensor_id   SERIAL PRIMARY KEY,
    sensor_name VARCHAR(100),
    location    VARCHAR(200)
);
 
CREATE TABLE sensor_readings (
    sensor_id INTEGER REFERENCES sensors(sensor_id),
    ts        TIMESTAMPTZ NOT NULL,
    value     DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (sensor_id, ts)
);
 
-- 時系列DBアプローチ（TimescaleDB: PostgreSQL拡張）
-- ハイパーテーブルで自動パーティショニング
-- SELECT create_hypertable('sensor_readings', 'ts');
 
-- 時系列データの正規化のポイント:
-- 1. メタデータ（センサー情報）は正規化
-- 2. 計測値は時系列最適化された構造で格納
-- 3. 集約結果は非正規化（CAGG: 連続集約）で保持

-- 中間テーブルに追加属性がある場合の正規化
CREATE TABLE students (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100)
);
 
CREATE TABLE courses (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100)
);
 
-- 中間テーブルに属性がある
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id  INTEGER REFERENCES courses(id),
    grade      CHAR(2),         -- 成績
    enrolled_at DATE,           -- 登録日
    instructor VARCHAR(100),    -- 担当教員
    PRIMARY KEY (student_id, course_id)
);
 
-- 問題: instructorが courses に対して関数従属する場合
-- course_id → instructor なら3NF違反
-- → instructorをcoursesテーブルに移動すべき
 
-- ただし、同じ科目でも学期によって教員が変わる場合は
-- {student_id, course_id} → instructor は完全関数従属
-- → 3NFを満たす（中間テーブルに残して良い）

-- 非正規形テーブル
CREATE TABLE orders_raw (
    order_id     INTEGER,
    order_date   DATE,
    customer_name VARCHAR(100),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    items        TEXT,  -- "商品A:3個:1000円, 商品B:1個:2000円"
    total_amount DECIMAL(10,2)
);

-- 教室予約テーブル
-- 前提:
-- 1. 各教員は1つの科目のみを教える
-- 2. 各科目は複数の教員が教えることができる
-- 3. 各教室は1時間枠につき1つの授業のみ
CREATE TABLE classroom_bookings (
    room_id    INTEGER,
    time_slot  INTEGER,
    teacher_id INTEGER,
    subject    VARCHAR(100),
    PRIMARY KEY (room_id, time_slot)
);

# 演習1: 基本実装のテンプレート
class Exercise1:
    """基本的な実装パターンの演習"""
 
    def __init__(self):
        self.data = []
 
    def validate_input(self, value):
        """入力値の検証"""
        if value is None:
            raise ValueError("入力値がNoneです")
        return True
 
    def process(self, value):
        """データ処理のメインロジック"""
        self.validate_input(value)
        self.data.append(value)
        return self.data
 
    def get_results(self):
        """処理結果の取得"""
        return {
            'count': len(self.data),
            'data': self.data
        }
 
# テスト
def test_exercise1():
    ex = Exercise1()
    assert ex.process(1) == [1]
    assert ex.process(2) == [1, 2]
    assert ex.get_results()['count'] == 2
 
    try:
        ex.process(None)
        assert False, "例外が発生するべき"
    except ValueError:
        pass
 
    print("全テスト合格!")
 
test_exercise1()

# 演習2: 応用パターン
from typing import List, Dict, Optional
from datetime import datetime
 
class AdvancedExercise:
    """応用パターンの演習"""
 
    def __init__(self, max_size: int = 100):
        self._items: List[Dict] = []
        self._max_size = max_size
        self._created_at = datetime.now()
 
    def add(self, key: str, value: any) -> bool:
        """アイテムの追加（サイズ制限付き）"""
        if len(self._items) >= self._max_size:
            return False
        self._items.append({
            'key': key,
            'value': value,
            'timestamp': datetime.now().isoformat()
        })
        return True
 
    def find(self, key: str) -> Optional[Dict]:
        """キーによる検索"""
        for item in reversed(self._items):
            if item['key'] == key:
                return item
        return None
 
    def remove(self, key: str) -> bool:
        """キーによる削除"""
        for i, item in enumerate(self._items):
            if item['key'] == key:
                self._items.pop(i)
                return True
        return False
 
    def stats(self) -> Dict:
        """統計情報"""
        return {
            'total_items': len(self._items),
            'max_size': self._max_size,
            'usage_percent': len(self._items) / self._max_size * 100,
            'uptime': str(datetime.now() - self._created_at)
        }
 
# テスト
def test_advanced():
    ex = AdvancedExercise(max_size=3)
    assert ex.add("a", 1) == True
    assert ex.add("b", 2) == True
    assert ex.add("c", 3) == True
    assert ex.add("d", 4) == False  # サイズ制限
    assert ex.find("b")['value'] == 2
    assert ex.remove("b") == True
    assert ex.find("b") is None
    stats = ex.stats()
    assert stats['total_items'] == 2
    print("応用テスト全合格!")
 
test_advanced()

# 演習3: パフォーマンス最適化
import time
from functools import lru_cache
 
# 最適化前（O(n^2)）
def slow_search(data: list, target: int) -> int:
    """非効率な検索"""
    for i in range(len(data)):
        for j in range(i + 1, len(data)):
            if data[i] + data[j] == target:
                return (i, j)
    return (-1, -1)
 
# 最適化後（O(n)）
def fast_search(data: list, target: int) -> tuple:
    """ハッシュマップを使った効率的な検索"""
    seen = {}
    for i, num in enumerate(data):
        complement = target - num
        if complement in seen:
            return (seen[complement], i)
        seen[num] = i
    return (-1, -1)
 
# ベンチマーク
def benchmark():
    import random
    data = list(range(5000))
    random.shuffle(data)
    target = data[100] + data[4000]
 
    start = time.time()
    result1 = slow_search(data, target)
    slow_time = time.time() - start
 
    start = time.time()
    result2 = fast_search(data, target)
    fast_time = time.time() - start
 
    print(f"非効率版: {slow_time:.4f}秒")
    print(f"効率版:   {fast_time:.6f}秒")
    print(f"高速化率: {slow_time/fast_time:.0f}倍")
 
benchmark()

# 設計判断の記録テンプレート
class ArchitectureDecisionRecord:
    """ADR (Architecture Decision Record) の作成"""
 
    def __init__(self, title: str):
        self.title = title
        self.context = ""
        self.decision = ""
        self.consequences = []
        self.alternatives = []
 
    def set_context(self, context: str):
        """背景と課題の記述"""
        self.context = context
        return self
 
    def set_decision(self, decision: str):
        """決定内容の記述"""
        self.decision = decision
        return self
 
    def add_consequence(self, consequence: str, positive: bool = True):
        """結果の追加"""
        self.consequences.append({
            'description': consequence,
            'type': 'positive' if positive else 'negative'
        })
        return self
 
    def add_alternative(self, name: str, reason_rejected: str):
        """却下した代替案の追加"""
        self.alternatives.append({
            'name': name,
            'reason_rejected': reason_rejected
        })
        return self
 
    def to_markdown(self) -> str:
        """Markdown形式で出力"""
        md = f"# ADR: {self.title}\n\n"
        md += f"## 背景\n{self.context}\n\n"
        md += f"## 決定\n{self.decision}\n\n"
        md += "## 結果\n"
        for c in self.consequences:
            icon = "✅" if c['type'] == 'positive' else "⚠️"
            md += f"- {icon} {c['description']}\n"
        md += "\n## 却下した代替案\n"
        for a in self.alternatives:
            md += f"- **{a['name']}**: {a['reason_rejected']}\n"
        return md

-- 正規化されたスキーマではRow Level Security（RLS）が適用しやすい
-- 部署テーブルと社員テーブルが分離されていれば、
-- 部署ごとのアクセス制御が容易
 
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY emp_dept_policy ON employees
    USING (department_id IN (
        SELECT dept_id FROM user_dept_access
        WHERE user_id = current_setting('app.current_user_id')::INTEGER
    ));
 
-- 非正規化テーブルではRLSの条件が複雑化する可能性がある
-- → セキュリティ要件も正規化レベルの判断材料とすべき