DB設計演習

テーブル分割の方針

元のテーブルには以下の問題があります。

• 推移的関数従属: 受注ID → 顧客ID → 顧客名, 顧客住所（顧客情報が顧客IDに従属）
• 推移的関数従属: 商品ID → 商品名, 単価（商品情報が商品IDに従属）
• 繰り返しグループ: 1受注に複数商品がある場合、受注情報が商品行数分だけ重複する

これらを解消するため、3つのマスタテーブルと1つの明細テーブルに分割します。

DDL（テーブル定義）

-- 顧客マスタ: 顧客に関する情報を一元管理
CREATE TABLE 顧客 (
    顧客ID   INT          PRIMARY KEY,
    顧客名   VARCHAR(100) NOT NULL,
    顧客住所 VARCHAR(200) NOT NULL
);

-- 商品マスタ: 商品に関する情報を一元管理
CREATE TABLE 商品 (
    商品ID INT          PRIMARY KEY,
    商品名 VARCHAR(100) NOT NULL,
    単価   INT          NOT NULL CHECK (単価 >= 0)
);

-- 受注ヘッダ: 受注1件につき1行
CREATE TABLE 受注 (
    受注ID INT  PRIMARY KEY,
    受注日 DATE NOT NULL,
    顧客ID INT  NOT NULL,
    FOREIGN KEY (顧客ID) REFERENCES 顧客(顧客ID)
);

-- 受注明細: 受注に含まれる商品ごとに1行
CREATE TABLE 受注明細 (
    受注ID INT NOT NULL,
    商品ID INT NOT NULL,
    数量   INT NOT NULL CHECK (数量 > 0),
    PRIMARY KEY (受注ID, 商品ID),
    FOREIGN KEY (受注ID) REFERENCES 受注(受注ID),
    FOREIGN KEY (商品ID) REFERENCES 商品(商品ID)
);

設計判断の理由

• 顧客テーブルの分離: 顧客名や住所が変更になった場合、1箇所の更新で済む。元のテーブルでは受注行すべてを更新する必要があった（更新異常の排除）。
• 商品テーブルの分離: 同様に、商品名や単価の変更が1箇所で済む。
• 受注と受注明細の分離: 受注日・顧客IDなどの受注ヘッダ情報と、商品ごとの明細情報を分けることで、1受注に複数商品を持てる構造になる。ヘッダ情報の重複もなくなる。
• 受注明細の複合主キー: (受注ID, 商品ID) の組み合わせで一意性を保証。同一受注で同一商品を2行持つのではなく、数量で管理する。

設計方針: サロゲートキー + UNIQUE制約

業務上の識別子（顧客コード）が変更される可能性がある場合、サロゲートキー（代理キー）を主キーに使い、顧客コードにはUNIQUE制約を設定するのが定石です。

DDL（テーブル定義）

-- 顧客テーブル: サロゲートキーを主キーに使用
CREATE TABLE customers (
    customer_id   INT          AUTO_INCREMENT PRIMARY KEY,  -- サロゲートキー
    customer_code VARCHAR(20)  NOT NULL,                    -- 業務上の識別子
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(255),
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (customer_code)  -- 一意性は保証するが主キーにはしない
);

-- 顧客コードでの検索を高速化（UNIQUE制約で自動作成される場合もある）
CREATE INDEX idx_customers_code ON customers(customer_code);

-- 受注テーブル: サロゲートキーで参照
CREATE TABLE orders (
    order_id    INT  AUTO_INCREMENT PRIMARY KEY,
    customer_id INT  NOT NULL,  -- customer_codeではなくcustomer_idで参照
    order_date  DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 問い合わせテーブル: 同様にサロゲートキーで参照
CREATE TABLE inquiries (
    inquiry_id  INT       AUTO_INCREMENT PRIMARY KEY,
    customer_id INT       NOT NULL,
    subject     VARCHAR(200) NOT NULL,
    inquiry_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

設計判断の理由

• サロゲートキーの利点: 主キーの値が変わらないため、外部キーの連鎖更新（CASCADE UPDATE）が不要。参照整合性が安定する。
• UNIQUE制約の意味: 顧客コードの一意性は業務ルールとして必要なので、UNIQUE制約で保証する。主キーではないため、値の変更はcustomersテーブルの1行だけで完結する。
• JOINのパフォーマンス: INT型のサロゲートキーはVARCHAR型の顧客コードよりJOINが高速。インデックスサイズも小さくなる。
• 注意点: アプリケーション層では顧客コードで検索し、内部的にはcustomer_idで処理を行う。画面表示やAPIレスポンスには顧客コードを使い、DB内部のリレーションにはサロゲートキーを使う、という使い分けが重要。

もし自然キーを主キーにしていたら？

-- アンチパターン: 変更可能な値を主キーにしてしまった場合
-- 顧客コードが CUST-0001 → CUST-A001 に変更されると...
UPDATE customers SET customer_code = 'CUST-A001' WHERE customer_code = 'CUST-0001';
-- ↑ これだけでは済まない。ordersやinquiriesの外部キー列も全行更新が必要になる。
-- ON UPDATE CASCADEがあっても大量レコードのロックが発生し、性能問題になりうる。

改善後のテーブル定義

-- 縦持ち設計: 1行 = 1商品 × 1年月
CREATE TABLE monthly_sales (
    product_id  INT  NOT NULL,
    sales_year  INT  NOT NULL,  -- 年を追加して複数年度対応
    sales_month INT  NOT NULL CHECK (sales_month BETWEEN 1 AND 12),
    amount      INT  NOT NULL DEFAULT 0 CHECK (amount >= 0),
    PRIMARY KEY (product_id, sales_year, sales_month),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 年月で絞り込む集計クエリを高速化
CREATE INDEX idx_monthly_sales_ym
    ON monthly_sales(sales_year, sales_month);

主要クエリ例

-- 1) 特定商品の2025年の月別売上一覧
SELECT sales_month, amount
  FROM monthly_sales
 WHERE product_id = 101
   AND sales_year = 2025
 ORDER BY sales_month;

-- 2) 直近3か月の売上合計（月をまたいでも同じSQL）
SELECT product_id, SUM(amount) AS total_3months
  FROM monthly_sales
 WHERE (sales_year = 2025 AND sales_month >= 10)
    OR (sales_year = 2026 AND sales_month <= 0)  -- 年またぎ対応
 GROUP BY product_id;

-- より汎用的な方法: 年月を1つの数値として比較
SELECT product_id, SUM(amount) AS total_3months
  FROM monthly_sales
 WHERE sales_year * 100 + sales_month
       BETWEEN 202511 AND 202601
 GROUP BY product_id;

-- 3) 年間売上TOP10商品
SELECT product_id, SUM(amount) AS annual_total
  FROM monthly_sales
 WHERE sales_year = 2025
 GROUP BY product_id
 ORDER BY annual_total DESC
 LIMIT 10;

-- 4) 前年同月比を算出
SELECT cur.product_id,
       cur.sales_month,
       cur.amount          AS current_amount,
       prev.amount         AS previous_amount,
       cur.amount - prev.amount AS diff
  FROM monthly_sales cur
  LEFT JOIN monthly_sales prev
    ON cur.product_id  = prev.product_id
   AND cur.sales_month = prev.sales_month
   AND prev.sales_year = cur.sales_year - 1
 WHERE cur.sales_year = 2025
 ORDER BY cur.product_id, cur.sales_month;

設計判断の理由

• 拡張性: 年度の追加はデータのINSERTだけで済む。カラム追加もALTER TABLEも不要。
• クエリの統一: WHERE句の条件を変えるだけで、任意の期間の集計が可能。月によってSQLを書き分ける必要がない。
• sales_yearの追加: 元テーブルにはなかった年の概念を追加。これにより複数年度の比較や前年同月比が容易になる。
• CHECK制約: sales_monthを1〜12に制限し、不正データを防止。

テーブル設計

-- 学生マスタ
CREATE TABLE students (
    student_id   INT          AUTO_INCREMENT PRIMARY KEY,
    student_code VARCHAR(10)  NOT NULL UNIQUE,  -- 学籍番号
    student_name VARCHAR(50)  NOT NULL,
    department   VARCHAR(50)  NOT NULL           -- 所属学部
);

-- 講座マスタ
CREATE TABLE courses (
    course_id   INT          AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(10)  NOT NULL UNIQUE,  -- 科目コード
    course_name VARCHAR(100) NOT NULL,
    credits     INT          NOT NULL CHECK (credits > 0),  -- 単位数
    instructor  VARCHAR(50)  NOT NULL                        -- 担当教員
);

-- 履修テーブル（中間テーブル）: M:N関連を解決
CREATE TABLE enrollments (
    student_id    INT         NOT NULL,
    course_id     INT         NOT NULL,
    enrolled_date DATE        NOT NULL,
    grade         CHAR(1)     NULL CHECK (grade IN ('A','B','C','D','F')),
    -- gradeはNULL許可: 履修登録時点では成績未確定のため
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

-- 講座別の学生一覧を高速に取得するためのインデックス
-- (主キーで student_id → course_id 方向は高速だが、逆方向も必要)
CREATE INDEX idx_enrollments_course
    ON enrollments(course_id, student_id);

主要クエリ例

-- 1) ある学生の履修一覧と成績
SELECT c.course_code, c.course_name, c.credits,
       e.grade, e.enrolled_date
  FROM enrollments e
  JOIN courses c ON e.course_id = c.course_id
 WHERE e.student_id = 1
 ORDER BY e.enrolled_date;

-- 2) ある講座の履修者一覧
SELECT s.student_code, s.student_name, s.department,
       e.grade
  FROM enrollments e
  JOIN students s ON e.student_id = s.student_id
 WHERE e.course_id = 5
 ORDER BY s.student_code;

-- 3) 学生ごとの取得単位数合計（成績D以上が合格とする場合）
SELECT s.student_code, s.student_name,
       SUM(c.credits) AS total_credits
  FROM enrollments e
  JOIN students s ON e.student_id = s.student_id
  JOIN courses c  ON e.course_id  = c.course_id
 WHERE e.grade IN ('A','B','C','D')
 GROUP BY s.student_id, s.student_code, s.student_name;

設計判断の理由

• 中間テーブルの必要性: 学生テーブルに「履修講座1, 履修講座2, ...」のようなカラムを持たせると、履修数の上限が固定され、検索も困難になる（ジェイウォーク・アンチパターン）。中間テーブルで行として持つことで、任意数の関連を柔軟に表現できる。
• 複合主キー (student_id, course_id): 「同じ学生が同じ講座を2回履修しない」というビジネスルールをDBレベルで強制できる。
• gradeのNULL許可: 履修登録時点では成績が存在しないため、NULLを許可し、成績確定後にUPDATEする運用を想定。
• 逆方向インデックス: 複合主キーのインデックスはstudent_id先頭のため、「講座IDで学生を検索」する場合は逆方向のインデックスが必要。

テーブル設計

-- 商品マスタ: 価格以外の基本情報を管理
CREATE TABLE products (
    product_id   INT          AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(20)  NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    category     VARCHAR(50)  NOT NULL
);

-- 価格履歴テーブル: 有効期間パターン
CREATE TABLE product_prices (
    product_id   INT  NOT NULL,
    valid_from   DATE NOT NULL,  -- この価格が有効になる開始日
    valid_to     DATE,           -- この価格の終了日（NULLは現在有効）
    price        INT  NOT NULL CHECK (price >= 0),
    reason       VARCHAR(200),   -- 改定理由（任意）
    PRIMARY KEY (product_id, valid_from),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    -- 有効期間の整合性チェック
    CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

-- 特定時点の価格検索を高速化
CREATE INDEX idx_product_prices_period
    ON product_prices(product_id, valid_from, valid_to);

-- 受注テーブル: 受注時点の価格をスナップショットとして保持
CREATE TABLE orders (
    order_id     INT  AUTO_INCREMENT PRIMARY KEY,
    product_id   INT  NOT NULL,
    order_date   DATE NOT NULL,
    quantity     INT  NOT NULL CHECK (quantity > 0),
    unit_price   INT  NOT NULL,  -- 受注時点の価格をコピー（非正規化だが意図的）
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

主要クエリ例

-- 1) ある時点で有効な価格を取得
SELECT p.product_name, pp.price
  FROM products p
  JOIN product_prices pp ON p.product_id = pp.product_id
 WHERE pp.valid_from <= '2025-06-15'
   AND (pp.valid_to IS NULL OR pp.valid_to >= '2025-06-15');

-- 2) 現在有効な全商品の価格一覧
SELECT p.product_code, p.product_name, pp.price
  FROM products p
  JOIN product_prices pp ON p.product_id = pp.product_id
 WHERE pp.valid_from <= CURRENT_DATE
   AND (pp.valid_to IS NULL OR pp.valid_to >= CURRENT_DATE)
 ORDER BY p.product_code;

-- 3) 特定商品の価格改定履歴
SELECT valid_from, valid_to, price, reason
  FROM product_prices
 WHERE product_id = 101
 ORDER BY valid_from;

-- 4) 受注登録時: 現在の価格を取得してINSERT
INSERT INTO orders (product_id, order_date, quantity, unit_price)
SELECT 101, CURRENT_DATE, 3, pp.price
  FROM product_prices pp
 WHERE pp.product_id = 101
   AND pp.valid_from <= CURRENT_DATE
   AND (pp.valid_to IS NULL OR pp.valid_to >= CURRENT_DATE);

-- 5) 新しい価格の登録（旧価格の終了日を設定してから新価格を追加）
-- Step 1: 現在有効な価格の終了日を設定
UPDATE product_prices
   SET valid_to = '2025-09-30'
 WHERE product_id = 101
   AND valid_to IS NULL;

-- Step 2: 新価格を登録
INSERT INTO product_prices (product_id, valid_from, valid_to, price, reason)
VALUES (101, '2025-10-01', NULL, 1500, '原材料費高騰による改定');

設計判断の理由

• 有効期間パターン: valid_fromとvalid_toで期間を管理。valid_toがNULLの行が「現在有効な価格」を表す。これにより、任意時点の価格を1つのクエリで取得できる。
• 受注テーブルへの価格コピー: unit_priceに受注時点の価格を保存する。これは意図的な非正規化。価格が改定されても過去の受注金額に影響しない。会計上、受注時点の金額を正確に再現できることが重要。
• 複合主キー (product_id, valid_from): 同一商品で同じ開始日の価格が2つ存在することを防ぐ。
• 期間の重複防止: CHECK制約だけでは期間の重複を完全には防げない。アプリケーション層またはトリガーで、同一商品の有効期間が重複しないよう制御する必要がある。

方法A: 悲観的ロック（SELECT ... FOR UPDATE）

-- トランザクション開始
BEGIN;

-- Step 1: 在庫を行ロックして取得（他のトランザクションはこの行のロック解除を待つ）
SELECT stock_qty
  FROM inventory
 WHERE product_id = 101
   FOR UPDATE;
-- ここで stock_qty を変数 @current_stock に格納（アプリ側で処理）

-- Step 2: 在庫チェック（アプリケーション側で判定）
-- もし @current_stock < 注文数量 ならば ROLLBACK して終了

-- Step 3: 在庫を減らす
UPDATE inventory
   SET stock_qty = stock_qty - 2   -- 注文数量=2の場合
 WHERE product_id = 101;

-- Step 4: 受注ヘッダを登録
INSERT INTO orders (customer_id, order_date, status)
VALUES (501, CURRENT_DATE, 'CONFIRMED');
-- ここで LAST_INSERT_ID() で order_id を取得

-- Step 5: 受注明細を登録
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 101, 2, 1200);

-- Step 6: コミット（すべて成功した場合のみ）
COMMIT;

方法B: 楽観的制御（UPDATE の WHERE 条件で在庫チェック）

-- トランザクション開始
BEGIN;

-- Step 1: 在庫の減算とチェックを1つのUPDATEで行う
UPDATE inventory
   SET stock_qty = stock_qty - 2
 WHERE product_id = 101
   AND stock_qty >= 2;    -- 在庫が足りる場合のみ更新

-- Step 2: 更新行数を確認（アプリ側で判定）
-- もし affected_rows = 0 ならば在庫不足 → ROLLBACK して終了

-- Step 3: 受注ヘッダを登録
INSERT INTO orders (customer_id, order_date, status)
VALUES (501, CURRENT_DATE, 'CONFIRMED');

-- Step 4: 受注明細を登録
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 101, 2, 1200);

-- Step 5: コミット
COMMIT;

エラー時のロールバック（アプリケーション側の疑似コード）

try {
    connection.setAutoCommit(false);

    // 方法Bの場合
    int affected = stmt.executeUpdate(
        "UPDATE inventory SET stock_qty = stock_qty - ? " +
        "WHERE product_id = ? AND stock_qty >= ?",
        quantity, productId, quantity
    );

    if (affected == 0) {
        connection.rollback();
        throw new InsufficientStockException("在庫不足");
    }

    // 受注登録...

    connection.commit();
} catch (SQLException e) {
    connection.rollback();  // DB障害時も確実にロールバック
    throw e;
} finally {
    connection.setAutoCommit(true);
}

設計判断の理由

• トランザクションの必要性: 在庫減算と受注登録は「すべて成功」か「すべて取り消し」のどちらかでなければならない（原子性）。在庫だけ減って受注が登録されない、またはその逆は許されない。
• 方法Aの特徴（悲観的ロック）: SELECT FOR UPDATEで行を明示的にロックするため、確実に排他制御できる。ただしロック期間中、他のトランザクションは待機するため、高頻度の更新ではボトルネックになりやすい。
• 方法Bの特徴（楽観的制御）: UPDATEのWHERE条件で在庫チェックと減算を同時に行う。行ロックはUPDATE実行時の一瞬だけなので、高並行性のシステムに向いている。ただしaffected_rowsの確認をアプリ側で忘れると不整合が生じる。
• CHECK制約の追加推奨: CHECK (stock_qty >= 0) をinventoryテーブルに設定すれば、万が一アプリ側のチェック漏れがあってもDBレベルで在庫マイナスを防止できる。
• 処理順序のポイント: 在庫の確認・減算を受注登録より先に行う。理由は、在庫不足の場合に無駄なINSERTを避けるため。また、在庫のロック時間を最小限にするために、INSERT処理は在庫UPDATEの直後に素早く実行する。

Step 1: エンティティの抽出

エンティティ	説明	種別
books	書籍情報（ISBN単位）	マスタ
authors	著者情報	マスタ
book_authors	書籍と著者の関連（M:N中間テーブル）	関連
copies	蔵書（物理的な1冊ごと）	マスタ
members	図書館利用者	マスタ
loans	貸出記録	トランザクション
reservations	予約情報	トランザクション

Step 2: リレーションシップ

• books 1 --- N copies（1つの書籍に複数冊の蔵書）
• books M --- N authors（書籍と著者は多対多 → book_authorsで解決）
• copies 1 --- N loans（1冊の蔵書に複数回の貸出履歴）
• members 1 --- N loans（1人の利用者に複数の貸出記録）
• books 1 --- N reservations（1つの書籍に複数の予約）
• members 1 --- N reservations（1人の利用者に複数の予約）

Step 3: DDL（テーブル定義）

-- ========================================
-- 書籍情報（ISBN単位の論理的な書籍）
-- ========================================
CREATE TABLE books (
    book_id    INT          AUTO_INCREMENT PRIMARY KEY,
    isbn       VARCHAR(17)  NOT NULL UNIQUE,  -- ISBN-13形式
    title      VARCHAR(200) NOT NULL,
    publisher  VARCHAR(100) NOT NULL,
    pub_year   INT          NOT NULL CHECK (pub_year > 0)
);

-- ========================================
-- 著者マスタ
-- ========================================
CREATE TABLE authors (
    author_id   INT          AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

-- ========================================
-- 書籍-著者 中間テーブル（M:N解決）
-- ========================================
CREATE TABLE book_authors (
    book_id     INT NOT NULL,
    author_id   INT NOT NULL,
    author_order INT NOT NULL DEFAULT 1,  -- 著者の表示順
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id)   REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- ========================================
-- 蔵書（物理的な1冊）
-- ========================================
CREATE TABLE copies (
    copy_id     INT          AUTO_INCREMENT PRIMARY KEY,
    book_id     INT          NOT NULL,
    copy_number VARCHAR(20)  NOT NULL UNIQUE,  -- 館内管理番号
    status      VARCHAR(10)  NOT NULL DEFAULT 'AVAILABLE'
                CHECK (status IN ('AVAILABLE','ON_LOAN','RESERVED','DAMAGED')),
    acquired_date DATE       NOT NULL,          -- 受入日
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

CREATE INDEX idx_copies_book ON copies(book_id);

-- ========================================
-- 利用者
-- ========================================
CREATE TABLE members (
    member_id     INT          AUTO_INCREMENT PRIMARY KEY,
    member_number VARCHAR(10)  NOT NULL UNIQUE,  -- 利用者番号
    member_name   VARCHAR(50)  NOT NULL,
    phone         VARCHAR(15),
    registered_at DATE         NOT NULL DEFAULT (CURRENT_DATE)
);

-- ========================================
-- 貸出記録
-- ========================================
CREATE TABLE loans (
    loan_id     INT  AUTO_INCREMENT PRIMARY KEY,
    copy_id     INT  NOT NULL,
    member_id   INT  NOT NULL,
    loan_date   DATE NOT NULL DEFAULT (CURRENT_DATE),
    due_date    DATE NOT NULL,  -- 返却期限（loan_date + 14日）
    return_date DATE,           -- NULLなら貸出中
    FOREIGN KEY (copy_id)   REFERENCES copies(copy_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    CHECK (due_date >= loan_date),
    CHECK (return_date IS NULL OR return_date >= loan_date)
);

CREATE INDEX idx_loans_member   ON loans(member_id, return_date);
CREATE INDEX idx_loans_copy     ON loans(copy_id, return_date);

-- ========================================
-- 予約
-- ========================================
CREATE TABLE reservations (
    reservation_id INT  AUTO_INCREMENT PRIMARY KEY,
    book_id        INT  NOT NULL,  -- ISBN単位で予約（特定の冊子ではなく）
    member_id      INT  NOT NULL,
    reserved_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 予約順管理用
    status         VARCHAR(10) NOT NULL DEFAULT 'WAITING'
                   CHECK (status IN ('WAITING','FULFILLED','CANCELLED')),
    FOREIGN KEY (book_id)   REFERENCES books(book_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

CREATE INDEX idx_reservations_book
    ON reservations(book_id, reserved_at);

Step 4: 主要な業務クエリ

-- ================================================================
-- 貸出登録（利用者 M-001 に 蔵書 copy_id=15 を貸出）
-- ================================================================
BEGIN;

-- 現在の貸出冊数を確認（最大5冊制限）
SELECT COUNT(*) AS current_loans
  FROM loans
 WHERE member_id = 1
   AND return_date IS NULL;
-- → アプリ側で current_loans < 5 を確認。5以上ならROLLBACK

-- 蔵書の状態を確認・ロック
SELECT status FROM copies WHERE copy_id = 15 FOR UPDATE;
-- → AVAILABLE であることを確認

-- 蔵書の状態を更新
UPDATE copies SET status = 'ON_LOAN' WHERE copy_id = 15;

-- 貸出レコードを挿入
INSERT INTO loans (copy_id, member_id, loan_date, due_date)
VALUES (15, 1, CURRENT_DATE, CURRENT_DATE + INTERVAL 14 DAY);

COMMIT;

-- ================================================================
-- 返却処理（loan_id = 42 の貸出を返却）
-- ================================================================
BEGIN;

-- 返却日を記録
UPDATE loans
   SET return_date = CURRENT_DATE
 WHERE loan_id = 42
   AND return_date IS NULL;

-- 蔵書の状態を戻す
UPDATE copies
   SET status = 'AVAILABLE'
 WHERE copy_id = (SELECT copy_id FROM loans WHERE loan_id = 42);

-- この書籍に予約がないか確認（あれば予約者に通知）
SELECT r.reservation_id, m.member_name, m.phone
  FROM reservations r
  JOIN members m ON r.member_id = m.member_id
  JOIN copies c  ON c.book_id = r.book_id
 WHERE c.copy_id = (SELECT copy_id FROM loans WHERE loan_id = 42)
   AND r.status = 'WAITING'
 ORDER BY r.reserved_at
 LIMIT 1;

COMMIT;

-- ================================================================
-- ある利用者の現在の貸出状況
-- ================================================================
SELECT b.title, c.copy_number,
       l.loan_date, l.due_date,
       CASE WHEN l.due_date < CURRENT_DATE THEN '延滞中'
            ELSE '貸出中' END AS loan_status
  FROM loans l
  JOIN copies c ON l.copy_id = c.copy_id
  JOIN books b  ON c.book_id = b.book_id
 WHERE l.member_id = 1
   AND l.return_date IS NULL
 ORDER BY l.due_date;

-- ================================================================
-- 延滞者一覧（返却期限を過ぎて未返却）
-- ================================================================
SELECT m.member_number, m.member_name, m.phone,
       b.title, l.loan_date, l.due_date,
       DATEDIFF(CURRENT_DATE, l.due_date) AS overdue_days
  FROM loans l
  JOIN members m ON l.member_id = m.member_id
  JOIN copies c  ON l.copy_id = c.copy_id
  JOIN books b   ON c.book_id = b.book_id
 WHERE l.return_date IS NULL
   AND l.due_date < CURRENT_DATE
 ORDER BY overdue_days DESC;

-- ================================================================
-- 書籍の検索（タイトルと著者名で検索）
-- ================================================================
SELECT b.isbn, b.title,
       GROUP_CONCAT(a.author_name ORDER BY ba.author_order SEPARATOR ', ') AS authors,
       b.publisher, b.pub_year,
       COUNT(CASE WHEN c.status = 'AVAILABLE' THEN 1 END) AS available_copies,
       COUNT(c.copy_id) AS total_copies
  FROM books b
  JOIN book_authors ba ON b.book_id = ba.book_id
  JOIN authors a       ON ba.author_id = a.author_id
  LEFT JOIN copies c   ON b.book_id = c.book_id
 WHERE b.title LIKE '%データベース%'
    OR a.author_name LIKE '%田中%'
 GROUP BY b.book_id, b.isbn, b.title, b.publisher, b.pub_year;

設計判断の理由

• booksとcopiesの分離: 「書籍情報（ISBN単位）」と「蔵書（物理冊子）」を分けることが最も重要な設計判断。同じ本を3冊持っていても、書籍情報（タイトル、著者、出版社）は1行で済む。貸出はcopy単位で管理する。
• authorsの分離とbook_authors中間テーブル: 1冊に複数著者、1人の著者が複数書籍を執筆する可能性があるためM:N関連。author_orderで著者の表示順序を管理できる。
• copies.statusの管理: 蔵書の状態をENUM的に管理することで、貸出可能な冊数を高速に集計できる。loansテーブルだけでも貸出中かどうかは判定可能だが、statusがあることでクエリが簡潔になる。ただし、loansとcopies.statusの整合性をトランザクションで保証する必要がある。
• 予約はbook単位: 利用者は特定の物理冊子ではなく「その本（ISBN）」を予約する。どの冊子が返却されても予約を充足できるようにするため、reservationsはbook_idで参照する。
• 貸出上限（5冊）はアプリ側で制御: DDLのCHECK制約では「他の行を参照する条件」を記述できないため、貸出上限チェックはトランザクション内でSELECT COUNT(*)を使い、アプリケーション側で制御する。
• return_dateのNULL活用: 貸出中の判定は return_date IS NULL で行う。NULLを「未返却」の意味で使うことで、シンプルにフィルタリングできる。