データベーススペシャリスト試験 2022年 午後1 問02

キーワードと論点整理

• 図２の SQL 文では、
  1. 「INNER JOIN 商品 C ON A.□a□ = C.□a□」の結合キー
  2. WHERE 句の「(A.モデル名 <> C.モデル名 □b□ A.定価 <> C.定価)」の論理演算子
• 問題文の要件：「モデル名又は定価のいずれかが変更された商品を調べる」

解答の選択肢と理由

(a) 商品コード の根拠

… INNER JOIN 商品 C ON A.□a□ = C.□a□

(b) OR の根拠

… AND (A.モデル名 <> C.モデル名 □b□ A.定価 <> C.定価)

• モデル名だけ異なる場合
• 定価だけ異なる場合
• 両方異なる場合
  すべてを抽出できます。
  逆に AND を用いると「両方が異なる場合」のみを抽出し、要件を満たせません。

受験者が陥りやすいポイント

• AND を選ぶと「モデル名も定価も両方変更された行のみ」になるため要件と合わなくなる。
• JOIN のキーはテーブル定義を確認し、共通の主キー・外部キーを確実に選ぶこと。

試験対策として押さえるべき知識

1. SQL の JOIN では、結合対象テーブル間で「共通するキー」を正しく指定する
2. 条件式の論理演算子：
   • 「A かつ B」→ AND
   • 「A または B」→ OR
3. 要件に記載されている語句（「いずれか」「かつ」「両方」など）を正確に読み取り、演算子を選ぶ
4. 問題文中の要件と SQL 構文が一致するか常に確認する癖をつける

1. キーワードと論点整理

• 案1のポイント
  「案1」は，既存の“商品”テーブルに対して
  • 適用開始日，適用終了日 の列を追加
  • 履歴用別テーブルは作らず，同一 商品コード で複数行を管理
    といった設計変更を行う案です。
    この場合，従来は主キー（またはユニーク制約）が単一の 商品コード だったものを，
    新たに 適用開始日 などを含む複合キーに変えなければ重複行を許してしまいます。
• 外部キー制約（参照整合性）
  他テーブルの 商品コード 列が，商品マスタの主キー（またはユニークキー）を参照している仕組みです。
  主キーが変わると，これら外部キー制約の参照先定義も合わせて変更する必要があります。
• 影響対象テーブル
  問題文中の「図1　主なテーブルのテーブル構造（一部省略）」から，
  商品コード を持ち，かつ商品テーブルを参照している明細テーブルは次の２つです。

  テーブル名	参照元列	参照先テーブル	参照先列
  見積依頼明細	商品コード	商品	商品コード
  見積回答明細	商品コード	商品	商品コード

2. 解答が「見積依頼明細，見積回答明細 の外部キー制約」となる理由

1. 問題文の「図1」より，

   見積依頼明細（見積依頼番号，明細番号，商品コード，数量）
   見積回答明細（見積依頼番号，明細番号，商品コード，数量，…）
   とあり，両テーブルの 商品コード は「商品」テーブルを参照する外部キー制約が前提です。

2. 案1を採用すると，
   • 「商品」テーブルの主キーやユニークキー定義が従来の単一キー（商品コード）から
     複合キー（例：(商品コード, 適用開始日)）へ変更
   • これにより，従来の外部キー制約（参照先が単一の 商品コード）は整合しなくなる
3. よって，「見積依頼明細」 と 「見積回答明細」 に定義されている 外部キー制約 を
   新しい複合キー定義に合わせて変更（再定義）する必要があります。

3. 受験者が誤りやすいポイント

• 「外部キー制約」以外の制約（主キー制約やチェック制約，トリガーなど）を変更すべきと誤解しやすい
• 明細ではなく「見積依頼」テーブルや「見積回答」テーブルを選んでしまう
  → 図1に示される参照関係から，商品コード を持つのは「見積依頼明細」「見積回答明細」のみ
• 案2の履歴管理用テーブル（商品履歴）にまで影響があると考える
  → 案1は履歴テーブルを作らないため，商品履歴テーブルは存在せず影響外

4. 試験対策として覚えておくべきポイント

• テーブル設計変更（特に主キー／ユニークキーの変更）があった場合，
  そのテーブルを参照する全ての外部キー制約を見直す必要がある。
• 外部キー制約では，参照元列と参照先列の組み合わせ（数・順序・データ型など）が完全一致しなければならない。
• 「図1」のようなERD相当の表からは，どのテーブルがどの列で参照しているかを正確に読み取る練習をしておく。
• 設計変更案を評価するときは，「影響範囲」「既存プログラムへの影響」「制約の再定義コスト」なども併せて検討する習慣をつける。

キーワードと論点整理

1. 主キー設定
   • ALTER TABLE 商品履歴 ADD PRIMARY KEY((c),(d)); で指定する列名
   • 同一の適用開始日に同一の商品を複数回更新しない前提から主キーに適用開始日を含める
2. トリガーの種類と実行タイミング
   • BEFORE トリガー：変更操作前に実行し、NEW の値を修正可能
   • AFTER トリガー：変更操作後に実行し、他テーブルへの挿入などを行う
3. 相関名（CORRELATION NAME）の利用
   • REFERENCING OLD AS OLD2 NEW AS NEW2 により、変更前の行は OLD2、変更後の行は NEW2 で参照
   • トリガー内での OLD2／NEW2 の使い分け

解答の根拠と論理的説明

(c),(d)：主キーの列名

ALTER TABLE 商品履歴 ADD PRIMARY KEY(
  (c),
  (d)
);

• 主キーは「商品コード」と「適用開始日」の組み合わせでユニーク性を担保する
• 問題文の表1「案2」や注記2にある「適用開始日は、その行の適用が開始される日」で、同一商品が異なる適用開始日で履歴管理される

(e)：BEFORE／AFTER の指定

CREATE TRIGGER トリガー1 (e) UPDATE ON 商品

• 「“商品”テーブルの更新時に、適用開始日が NULL の場合、現在日付に更新する。」
• BEFORE トリガーでないと、変更後の行 (NEW1) に値をセットできない
• 問題文：「BEFORE トリガーは, テーブルに対する変更操作の前に実行され, 更新中又は挿入中の値を実際の反映前に修正することができる。」

(f)：BEFORE／AFTER の指定

CREATE TRIGGER トリガー2 (f) UPDATE ON 商品

• 「“商品”テーブルの更新時に、対象行の更新前の行を“商品履歴”テーブルに挿入する。」
• AFTER トリガーでないと、変更処理が完了したあとに履歴用テーブルへの挿入処理が確実に実行されない
• 問題文：「AFTER トリガーは, 変更操作の後に実行され, ほかのテーブルに対する変更操作を行うことができる。」

(g),(h)：OLD／NEW の相関名

INSERT INTO 商品履歴
VALUES (
  OLD2.商品コード,
  OLD2.メーカー名,
  OLD2.商品名,
  OLD2.モデル名,
  OLD2.定価,
  OLD2.更新日,
  (g),
  ADD_DAYS((h).適用開始日, -1)
);

• 挿入行の「適用開始日」は、更新前の行の適用開始日をそのまま使う
  → 相関名 OLD2 で参照
• 挿入行の「適用終了日」は、更新後の行 (NEW2.適用開始日) の前日を計算
  → 相関名 NEW2 で参照

注意ポイント／受験者のひっかけ

• (c),(d) は順不同：主キーの列順はどちらが先でも正答とみなされる場合が多い
• BEFORE/AFTER の混同：
  • BEFORE でないと NEW の値変更ができず、適用開始日が設定されない
  • AFTER でないと、テーブル更新前に履歴挿入を行ってしまい、最新データが取得できない
• OLD と NEW の相関名の使い分け：
  • 古い属性情報には必ず OLD2 を使い、新しい適用開始日には NEW2 を使う

試験対策まとめ

• トリガーの実行タイミング
  • BEFORE：NEW の書き換え
  • AFTER：他テーブルへの書き込み
• 相関名の命名ルール
  • REFERENCING OLD AS ... NEW AS ... を正しく理解
• 履歴管理の主キー設計
  • 履歴テーブルは「対象キー + 適用開始日」などでユニークに管理
• SQL DDL／DML の流れ
  • DDL（ALTER, CREATE）→ DML（トリガー定義） → データ操作
    を意識して構文を整理することが重要です。

キーワード・論点整理

• ウィンドウ関数（LEAD）
  図３中の

  ADD_DAYS(LEAD(Q2.見積回答日) OVER (PARTITION BY Q2.商品コード ORDER BY Q2.見積回答日), -1) AS 適用終了日
  にあるように，LEAD は「ウィンドウ区画内で順序付けられた各行に対して，現在行の１行後の行の列の値を返す」関数です。次の行がなければ NULL を返します。

• ADD_DAYS 関数
  注記２にある通り

  ADD_DAYS(引数1, 引数2) は， 引数1 (日付型) から引数2 (整数型) 日後の日付を返すユーザー定義関数
  です。引数1 が NULL の場合は NULL を返します。

• 適用終了日の算出ルール
  図３では「次の適用開始日の前日」を ADD_DAYS(..., -1) で算出し，これを 適用終了日 としています。

解答の導出プロセス

(i) の算出：行番号 2 の適用終了日

• 次の行（行番号 3）の「適用開始日」は (j)
• 適用終了日は「次の行の適用開始日の前日」
• 前日を取るために

  ADD_DAYS(LEAD(Q2.見積回答日) …, -1)

• よって
  i = ADD_DAYS(2022-09-01, -1) = 2022-08-31

(j) の算出：行番号 3 の適用開始日

• 行番号 3 は，SQL の SELECT … Q2.見積回答日 AS 適用開始日 に対応
• 表４を見ると，商品コード 1，モデル名 M1-2 の見積回答日は 2022-09-01
• よって
  j = 2022-09-01

(k) の算出：行番号 3 の適用終了日

• 行番号 3 の次の行が存在しないため，

  LEAD(…) は NULL を返す

• ADD_DAYS(NULL, -1) も NULL
• よって
  k = NULL

応用的な注意点・落とし穴

1. LEAD の戻り値が NULL
   最終行に対して LEAD を使うと，常に NULL になることを忘れやすいです。これをそのまま ADD_DAYS に渡すと，結果も NULL になります。
2. ADD_DAYS の引数順序
   日付から日数を「後」あるいは「前」にずらすため，第２引数に正負を指定します。-1 を見逃すと前日になりません。
3. 見積回答日と適用開始日の混同
   図３では Q2.見積回答日 を 適用開始日 として扱います。クエリ中の列名と実際のビジネス用語をきちんと対応させましょう。

試験対策のまとめ

• ウィンドウ関数 LAG/LEAD の動作を正確に理解する。
• 最終行や最初の行で戻り値が NULL になる点に注意する。
• 日付の前後操作には ADD_DAYS(…, ±n) を使い，引数に応じて日数の加減を明確にする。
• 「次の開始日の前日」を求める典型パターンは
  ADD_DAYS(LEAD(日付) OVER (...), -1)
• SQL と業務用語（今回であれば「見積回答日」「適用開始日／終了日」）の対応を混同しない。

キーワード・論点整理

• バージョン管理の出力結果（表5）
  図３の SQL 文により、商品コード ごとに過去から順に並べた見積回答日を 適用開始日 とし、次の行の見積回答日の前日を 適用終了日 としてバージョン情報を取得している。
• 移行ルール
  「移行前の“商品”テーブルの状態によらず、変更があった全商品を更新した。」
  → 各 商品コード ごとに もっとも新しいバージョン（最大の 適用開始日）を “商品”テーブルに更新
  → その他のバージョンを “商品履歴”テーブルに挿入
• 行番号との対応
  表5 の行番号は、ROW_NUMBER() OVER (ORDER BY 商品コード, 適用開始日) による連番。

表5（再掲）

解答が「商品：3,5,6／商品履歴：1,2,4」となる理由

1. 「移行前の“商品”テーブルの状態によらず、変更があった全商品を更新した。」
   → まず 最新版を“商品”テーブルに反映
2. 各 商品コード ごとに 適用開始日 が最大 の行を探す
   • 商品コード 1：行1（2019-04-01）、行2（2020-09-01）、行3（j） ⇒ 最大は行3
   • 商品コード 2：行4（2019-04-01）、行5（2022-05-01） ⇒ 最大は行5
   • 商品コード 3：行6（2019-04-01）のみ ⇒ 行6
3. それ以外の行（過去バージョン）はすべて “商品履歴” テーブルへ挿入
   • 商品コード 1：行1,2
   • 商品コード 2：行4
   • 商品コード 3：過去版なし

• “商品” テーブルへの更新行番号：3, 5, 6
• “商品履歴” テーブルへの挿入行番号：1, 2, 4

受験者が誤りやすいポイント

1. 適用終了日が NULL（未表示）だから「最新版」と誤認
   表5 では一部の 適用終了日 が表示されていないが、これは問題文の未完成状態。最新版の判定は「最大の 適用開始日」で行うことに注意。
2. 初回バージョン（前行定価が NULL）の扱い
   前行定価が NULL の行（行1, 行6）は「初回バージョン」となるが、最新版か否かは適用開始日の大小で判断する。
3. ウィンドウ関数の理解不足
   LAG／LEAD による前後行取得の仕組みを誤解し、「レコード順＝バージョン順」と結びつけられない場合がある。

試験対策として覚えておくべきポイント

• 「最新版＝最大の適用開始日」「履歴＝それ以外」
• ウィンドウ関数 LAG, LEAD の使い方（PARTITION BY, ORDER BY）
• マスター／履歴テーブル移行では、最新行はマスター、本体以外は履歴 という基本ルール
• SQL の結果を行番号で振る ROW_NUMBER() の用途
• NULL と日付比較、並び順の扱い（NULL は並びの先頭か末尾か）

キーワードと論点整理

• RPO（Recovery Point Objective）
  障害発生時に失われるデータの量・時間を表す指標
  問文：「(2) RPO は, 障害発生時に失われる（ア）に依存するので, 最大（イ）分とみなせる。」
• RTO（Recovery Time Objective）
  データベース障害から業務を再開できるまでの許容時間
  問文：「(3) RTO のうち, データベースの回復に掛かる時間は, フルバックアップからのリストア時間と, ログを適用するのに掛かる時間の合計である。」
• バックアップ／リストア仕様
  ・フルバックアップ：1日1回、オブジェクトストレージにオフライン保存
  ・ログの切替え：5分に1回、アーカイブログとしてオブジェクトストレージに保存
  ・リストア時ディスク転送速度：100Mバイト/秒
• ログ適用仕様
  ・ログ出力レート：毎秒10ページ
  ・同期入出力時間：ページ当たり2ミリ秒
  ・バッファヒット率：0％

解答の根拠

1. （ア）・（イ）：「ログの量」「5」
   問文より

   (2) RPO は, 障害発生時に失われる（ア）に依存するので, 最大（イ）分とみなせる。
   (1) …ログ切替えは5分に1回行い…

   → 障害時にアーカイブログに切替えられていない直前の「ログの量（＝直近5分分）」が失われる。
   → ログの切替え間隔が「5分」なので RPO=最大5分。
2. （ウ）：1,800
   問文より

   (4) フルバックアップからのリストア時間は, データベース容量が 180Gバイト, リストア時のディスク転送速度を100Mバイト/秒と仮定すると（ウ）秒である。
   ここで,1Gバイトは10⁹バイト, 1Mバイトは10⁶バイトとする。

   計算

   180 Gバイト = 180 × 10⁹ バイト  
   転送速度 = 100 × 10⁶ バイト/秒  
   時間 = (180 × 10⁹) ÷ (100 × 10⁶) = 1,800 秒
   

3. （エ）：864,000
   問文より

   (5) ログを適用する期間が最大になるのは, フルバックアップ取得後の経過時間が最大になる 24時間である。
   ログが毎秒10ページ出力されると仮定すると, 適用するログの量は最大（エ）ページである。

   計算

   24 時間 = 24 × 3,600 秒  
   ページ数 = 24 × 3,600 × 10 = 864,000 ページ
   

4. （オ）：1,728
   問文より

   同期入出力時間がページ当たり2ミリ秒と仮定すると最大（オ）秒である。

   計算

   適用ページ数 = 864,000 ページ  
   1 ページ当たり時間 = 2 ms = 0.002 s  
   時間 = 864,000 × 0.002 = 1,728 秒
   

注意点

• 単位変換
  1 Gバイト＝10⁹バイト、1 Mバイト＝10⁶バイトを正しく使うこと。
  「2¹⁰」や「2³⁰」と勘違いしやすい点に注意。
• RPO とバックアップ間隔の混同
  フルバックアップ間隔（1日）ではなく、ログをアーカイブする「ログ切替え間隔（5分）」が RPO を決める。
• RTO 計算範囲
  RTO に含めるのは「フルバックアップのリストア時間＋ログ適用時間」のみ。インスタンス起動時間やマウント時間などは本問の計算対象外。

試験対策ポイント

• RPO/RTO の定義とそれぞれに影響する要素を整理する
  ・RPO：どこまでの更新を保証できるか（データ損失の許容範囲）
  ・RTO：復旧に要する時間の許容範囲
• クラウド RDBMS のバックアップ機能
  ・フルバックアップの方式（オンライン／オフライン、頻度）
  ・アーカイブログ方式によるログ管理
• パフォーマンス試算
  ・単位変換（10進表記）に慣れる
  ・データ量÷転送速度、ページ数×処理時間、乗算・除算の順序
• 問文の条件を正確に読み取り、計算対象となる「期間」「速度」「出力量」の組み合わせをミスしない

キーワード・論点整理

• レプリケーション
  「ログを使って, RDBMS のデータをほかの RDBMS に複製する。複製元のテーブルに対する変更操作…を複製先のテーブルに自動的に反映する。」
• 同期型
  「同期型では, 複製先でログをディスクに出力した後, 複製元のトランザクションがコミットされる。」
• 非同期型
  「非同期型では, 複製先へのログの到達を待たずに, 複製元のトランザクションがコミットされる。」
• 影響を最小化
  本番（プライマリ）インスタンスの処理性能や応答性を低下させないこと

回答の理由

「非同期型では, 複製先へのログの到達を待たずに, 複製元のトランザクションがコミットされる。」
この性質により、本番インスタンスの業務処理に対する遅延を抑えられ、見積システムへの影響を最小化できます。

受験者の注意点

• 「非同期＝データ損失リスクあり」と「影響を最小化」の評価ポイントを混同しない。
  非同期型は可用性／性能寄りの選択肢であり、冗長化の安全性とは別次元です。
• 「同期型が安全」とだけ覚えず、その代償として「パフォーマンスに与える影響が大きい」点も併せて理解すること。

試験対策まとめ

• レプリケーション方式の違いは「コミット応答を返すタイミング」を押さえる
  • 同期型：複製先の書き込み完了後にコミット
  • 非同期型：複製先の到達を待たずにコミット
• パフォーマンス重視なら「非同期型」、データ一貫性重視なら「同期型」と使い分ける
• 本問では「見積システムへの影響を最小化」という観点から、非同期型のメリットが問われている点を確認すること

キーワード・論点整理

• 非同期型レプリケーション
  「非同期型では, 複製先へのログの到達を待たずに, 複製元のトランザクションがコミットされる。」
• コミット済みデータの喪失要因
  • 複製先へログが未到達・未適用
  • 本番と参照専用インスタンス間の通信断
  • 参照専用インスタンス自体の停止

解答の根拠と論理展開

「非同期型では, 複製先へのログの到達を待たずに, 複製元のトランザクションがコミットされる。」

1. ログが参照専用インスタンスに届いていない場合
   非同期なので、コミット直後にログが未送信／未適用の状態があり得ます。
2. 本番と参照専用インスタンスの通信が切断している場合
   ネットワーク障害でログ伝送そのものが不能となり、最新コミット分が二次側へ到達しません。
3. 参照専用インスタンスが停止している場合
   インスタンス停止中は当然ログ適用が滞り、最新データを欠いたままになります。

受験者が誤りやすいポイント

• 同期型と混同する
  同期型では「複製先でログをディスクに出力した後, 複製元のトランザクションがコミットされる」ため、コミット済みデータ欠落は起こりません。非同期型特有のリスクです。
• 「瞬断では失われない」と誤解しやすい
  非同期型の場合、たとえ通信が瞬断しても切断期間中のコミットは取り戻せません。
• 参照専用インスタンスの停止は要因に入れ忘れ
  インスタンス停止は通信切断と似るものの、物理的にログ適用自体が止まる点で別途押さえておく必要があります。

試験対策として覚えておくべきポイント

• 同期型 vs 非同期型のコミット条件

  種別	コミット条件
  同期型	複製先にログ書込み→完了後に複製元がコミット
  非同期型	複製先への到達待たずに複製元がコミット

• 非同期レプリケーションのリスク
  ログ伝送遅延／ネットワーク断・二次側障害で最新コミット分が欠落する可能性がある。
• RPO/RTO設計との関係
  フェイルオーバー時に同期・非同期どちらを採用するかで、データ損失許容範囲（RPO）に直結する。
• 設問文のキーワード
  「フェイルオーバー」「非同期型」「コミット済みの変更が失われる」などはセットで覚えておくこと。