データベーススペシャリスト 2014年 午後1 問03

解答の論理構成

1. 要求仕様の確認
   【問題文】「表示される順番は、商品全体で重複がないように、商品企画担当者が決めた表示順に基づく。」
   ─ 表示順が重複しないことが業務要件。
2. UNIQUE 制約の候補列
   ・商品番号 … 主キーで既に一意
   ・表示順 … 一意性が求められている
   ・その他の列 … 重複禁止の要件なし
   ⇒ UNIQUE を付けるべき列は 表示順。
3. 目的文の作成
   要件文のキーワード「商品全体で重複がないように」を活用し、30字以内で
   「商品全体で重複がない表示順を保証するため」
   とまとめる。

誤りやすいポイント

• 表示順を「ORDER BY 用」とだけ捉え、UNIQUE の必要性を見落とす。
• 「商品名」などに UNIQUE を付けると、同一名の別商品が登録できず要件違反。
• UNIQUE 制約はテーブル間をまたげないため、案2でも単品・セット各テーブルに個別定義が必要である点を失念しやすい。

FAQ

解答の導き方

1. 問題文に「表示される順番は、商品全体で重複がないように、商品企画担当者が決めた表示順に基づく」とあるので、ここで求められているのは「単品商品とセット商品を合わせた『商品全体』で表示順が一意であること」です。
2. 表3（案2）では「単品商品」と「セット商品」それぞれに UNIQUE 制約を定義しているため、各テーブルにユニーク索引が作成されることになります。
3. 問題文にも明記されているように「ユニーク索引は、一つのテーブル内でキー列の一意性を保証するものであり、ユニーク索引を複数のテーブルにまたがって作成することはできない。」つまり、別々のテーブルに同じ表示順の値が入ることを防げません。
4. よって「UNIQUE 制約を定義し、ユニーク索引を作成しただけでは、(a)に関する要求仕様を満たせない」の理由は、ユニーク索引がテーブル単位でしか一意性を保証できないからです。

誤りやすいポイント

• 「各テーブルに UNIQUE 制約を付ければ商品全体で重複しない」と考える誤り。テーブルを分けると同じ値が別テーブル側に入る可能性があります。
• 「CHECK 制約でテーブル間の一意性を保証できる」と考える誤り。標準 SQL の CHECK 制約は他テーブル参照を行えず、行単位／同一テーブル内の制約しか課せません。
• 「複合キー（同一テーブル内）で解決できる」との混同。複合キーも単一テーブル内の一意性を扱う手段であり、複数テーブル横断の一意性は保証できません。
• 実装方法の選択肢（トリガ／アプリケーション制御／専用マスタ等）はあるが、トリガやアプリケーションに任せる場合は同時更新による競合（排他）やトランザクション分離レベルへの配慮が必要です。

FAQ

解答の論理構成

1. 外部キーの仕様確認
   • 【問題文】「ユニーク索引は、一つのテーブル内でキー列の一意性を保証する…ユニーク索引を複数のテーブルにまたがって作成することはできない。」
   • 外部キーも同様に “1つの参照先テーブル” を必要とする。
2. 項番2（ア）の検討
   • 案1では“商品”テーブルが単品とセットを同居させる。
   • したがって“セット商品構成”テーブルの「単品商品番号」列に外部キーを張ると、実際はセット商品のレコードも選択肢に入る。
   • 結果として「単品商品番号列にセット商品番号を設定できてしまう。」
3. 項番3（イ）の検討
   • 案2では商品が「単品商品」「セット商品」の2テーブルに分割される。
   • “在庫”テーブルの「商品番号」列に外部キーを設定しようとしても、どちらか一方しか参照できない。
   • 片方を参照先にすると、もう片方の商品は登録できず「『在庫』テーブルに行を挿入できない。」事態となる。
4. したがって（ア）（イ）は上記の内容となる。

誤りやすいポイント

• 「複数テーブルをまたぐ外部キーを定義できる」と誤解する。SQL標準では不可。
• 案1・案2のメリット／デメリットを逆に覚える。スーパタイプ統合は参照しやすいが属性NULL管理が煩雑。
• UNIQUE 制約がテーブル単位であるため、テーブル分割後の一意性維持手段を見落とす。

FAQ

解答の論理構成

1. 各列がどちらのサブタイプの列かを確認する
   • 図1では、単品商品が「社内原価」を、セット商品が「化粧箱番号」「詰合せ日数」をもっています。案1では、これらを一つの“商品”テーブルにまとめ、単品区分で単品商品（‘Y’）とセット商品（‘N’）を区別します。
   • したがって、ある行のサブタイプにない列は NULL でなければなりません。
2. 単品区分が ‘Y’（単品商品）の場合
   • 社内原価は単品商品の列なので、値が必要です（図5に既に ① 社内原価 IS NOT NULL がある）。
   • 化粧箱番号と詰合せ日数はセット商品の列なので、単品商品では NULL です。
     ⇒ (b)・(c) は「④ 化粧箱番号 IS NULL」と「⑥ 詰合せ日数 IS NULL」（順不同）。
3. 単品区分が ‘N’（セット商品）の場合
   • 社内原価は単品商品の列なので、セット商品では NULL です。
     ⇒「② 社内原価 IS NULL」。
   • 【表1】に「セット商品には必ず一つの化粧箱が使われ」とあるので、化粧箱番号には値が必要です。
     ⇒「③ 化粧箱番号 IS NOT NULL」。
   • 詰合せ日数は【表1】に「未定の場合、NULLが設定される」とあるので、セット商品でも NULL があり得ます。NULL かどうかの条件は付けられません。
     ⇒ (d)・(e) は「②」と「③」（順不同）。
4. 以上より図5の検査制約は
   CHECK ( ( 単品区分 = ‘Y’ AND ① AND ④ AND ⑥ )
   OR ( 単品区分 = ‘N’ AND ② AND ③ ) )
   となります。

誤りやすいポイント

• 「詰合せ日数」をお届け予定日の計算に使うことから、単品商品で ⑤ 詰合せ日数 IS NOT NULL としてしまう。詰合せ日数はセット商品の列で、単品商品では NULL です。
• セット商品の詰合せ日数に ⑤ IS NOT NULL を付けてしまう。未定の場合は NULL が設定されます。
• 「化粧箱番号 IS NULL／NOT NULL」「社内原価 IS NULL／NOT NULL」を単品・セットで逆に設定する。

FAQ

解答の論理構成

1. 基準となる “SQL1”
   【問題文】「SELECT M.商品番号、P.社内原価、P.化粧箱番号 … FROM 注文明細 M, 商品 P …」
   “商品” にはすべての商品が格納されているため、注文に存在する行は必ず取得できる。
2. “SQL2” の要求
   • 【問題文】「SQL2の実行結果をSQL1と同じにしたい。」
   • 1行の “M.商品番号” は “単品商品” か “セット商品” のどちらか1テーブルにしか存在しない。
   • したがって “注文明細” を起点に、両テーブルに対して「存在すれば結合、存在しなければNULL」を返す必要がある。
   • この動作を保証する JOIN は “LEFT OUTER JOIN”。
   • 同じ動作を2回行うので (f) は2か所とも “LEFT OUTER”。
3. “SQL3” の要求
   • 列リストを見ると、1 本目の SELECT では “化粧箱番号” を NULL、2 本目では “社内原価” を NULL に設定し、最後に “UNION ALL” で合成している。
   • したがって各 SELECT は「一致した場合のみ取得」すればよく、行の欠落は UNION 側で補完できる。
   • 通常の “INNER JOIN”（JOIN だけでも可）で十分なので (g) は “INNER”。

誤りやすいポイント

• 「どちらのテーブルか分からないときは全結合（FULL OUTER）を使う」と思い込む
  → 実際には起点テーブルが決まっており LEFT だけで足りる。
• “SQL3” でも “LEFT OUTER JOIN” と記述してしまう
  → 分割＋UNION 方式では不要。結果が重複しパフォーマンスも低下する。
• JOIN の順番を変えると NULL の位置が変わることを見落とす
  → 起点テーブルを意識して外部結合の向きを決定する。

FAQ

解答の論理構成

1. 目的の確認
   【小問説明】に「注文されたセット商品を構成する単品商品の合計数を求める」とあります。
2. 必要な情報源
   ・セット商品が何個注文されたか → 注文明細 の 「注文数」（列名：注文数）
   ・セット商品1個に単品商品が何個入っているか → セット商品構成 の 「構成数」（列名：構成数）
3. テーブル別の別名
   SQL4, SQL5 の FROM 句に
   • 注文明細 M
   • セット商品構成 K
     が指定されています。従って両列はM.注文数、K.構成数 で参照します。
4. 合計値を出すための式
   1注文行で必要な単品数 = 注文数 × 構成数
   これを行単位で計算し SUM で集計する設計は実務でも典型です。
5. 以上より (h) は
   sql M.注文数 * K.構成数

誤りやすいポイント

• P.構成数 という列は存在しないため、Pを使うとエラーになります。
• M.注文数 + K.構成数 と加算にしてしまうと「必要個数」ではなく「数の合計」になり要件を満たせません。
• 両案とも結果を合わせる必要があるので、案2だけを見て別名をSとしてしまうミスに注意します。

FAQ

解答の論理構成

1. 【問題文】にはネストループ結合の手順が示されている。
   ① 注文明細 → ②（案1の場合）商品 → ③ セット商品構成 の順で主索引をたどる。
2. セット商品構成 は「セット商品番号」を主キーに持ち、セット商品のみが登録される構造である。
3. ところが案1の 商品 表には単品・セット両方が格納されるため、②の時点で単品行も取得してしまう。
4. 単品行に対して③を実行しても対応行は見つからず、「主索引を探したがヒットしない」無駄アクセスが発生する。
5. 表1の説明――
   「単品区分…区分値は、単品商品では ‘Y’、セット商品では ‘N’ が設定される。」
   これを利用し、WHERE 句に P.単品区分 = 'N' を追加すれば②で単品行を除外できる。
6. したがってアクセス頻度低減のための追加述語は P.単品区分 = 'N' となる。

誤りやすいポイント

• セット商品構成 表を「単品も含む」と誤解し、区分条件が不要と判断してしまう。
• 案2の構造と混同し、単品商品 / セット商品 個別表だから問題ないと考えてしまう。
• 述語を 単品区分 <> 'Y' などと書き換え、原文の値 ‘N’ を改変してしまう（採点対象外になる）。

FAQ

解答の導き方

• 「注文単位を一つのトランザクションで処理し、最後に COMMIT 文を発行する。」→ 各トランザクションは最後までロックを保持する（コミットまで解放されない）。
• 「商品一覧画面に表示された順番に…‘在庫’テーブルの引当可能数を調べ、…更新する。」→ 注文処理は、顧客が見た「表示順」に従って各商品の在庫行を順に参照・更新する（＝表示順がロック取得の順序になる）。
• 「‘セット商品構成’テーブルから、主キー順に当該セット商品を構成する単品商品の構成数を調べ」→ セット商品を処理する際、当該セットの構成単品は「主キー順」（＝単品の商品番号等の昇順と想定）で調べ、順に在庫行を更新（＝その順でロックを取得）する。
• ISOLATIONレベルはREAD COMMITTED → 更新のための排他ロックはコミットまで保持される。

• 全商品の処理は「表示順」に従う。
• 各商品pに対して
  • pが単品なら、在庫行 在庫[p] を取得（更新ロック）する。
  • pがセットなら、在庫行 在庫[セットp] を取得し（セット行の更新）、不足なら「セット商品構成」を主キー順に読み、その主キー順で各構成単品の在庫行 在庫[c] を順に取得する。

1. TR1（単品2個） vs TR2（単品1個＋セット1個） → （ウ）×
   • 構成例（可能な表示順とセット内容を一例として選ぶ）：
     • 表示順（画面の並び）: S1 → B → A → …
     • セットS1の構成単品（主キー順）: A, B（つまりセット処理はA → Bの順で在庫行を触る）
   • 並列の取り得る実行順（相互作用の一例）:
     • TR1（単品注文A,B） は表示順に従ってBを先に処理 → 在庫[B] をロックし次にAに進む。
     • TR2（単品BとセットS1）は表示順に従ってS1を先に処理 → 在庫[S1]（セット行）をロックし、主キー順で構成単品Aをロックし次にBをロックしようとする。
   • 相互作用（デッドロック発生の流れ）:
     • TR1が在庫[B] をロックする。
     • TR2が在庫[S1] をロックし、在庫[A] をロックする。
     • TR1が在庫[A] を要求するがTR2が保持しているため待ち。
     • TR2が在庫[B] を要求するがTR1が保持しているため待ち。
     • よってTR1 →（Aを待つ）→ TR2 →（Bを待つ）→ TR1となり循環待ち（デッドロック）。
   • 結論：条件に合う表示順やセット構成があり得るため、デッドロックが起きるおそれがある（×）。
2. TR1（単品2個） vs TR3（セット1個） → （エ）×
   • 構成例：表示順： B → A → …、セットS1の構成単品はA, B（主キー順A→B）とする。
   • 相互作用（代表的なタイミング）:
     • TR1は表示順でBを先に処理して在庫[B] をロックする。次にAを処理しようとする。
     • 同時にTR3はセットS1を処理し、在庫[S1] をロックしてから主キー順で在庫[A] をロックする。
     • 結果、TR1は在庫[A] のロック要求で待ち（TR3が保持）、TR3は在庫[B] のロック要求で待ち（TR1が保持）。
     • 循環待ちが発生するためデッドロック。
   • 結論：よってデッドロックが起きるおそれがある（×）。
3. TR3（セット1個） vs TR3（セット1個） → （オ）○
   • 理由（一般的な不発生の証明）:
     • あるセットsを処理する際のロック取得順は「表示順でのそのセットの位置」→「そのセットの構成単品を主キー順（＝単品番号昇順）で順に取得」です。
     • したがって「（セットの表示位置）を一次キー、（構成単品の商品番号）を二次キーとする辞書順」で表される全てのロック取得キーが定まります。
     • どのトランザクションもセット群を表示順で巡り、各セットの構成を主キー順で処理するため、任意の2つのセット専用トランザクションは同じ全体順序に従ってロックを取得します。
     • 同一の（全体）単調順序に従ってロックを取る場合、逆順で取得することがないため循環待ちは発生しません。
   • 結論：よって、セットのみのトランザクション同士ではデッドロックは起きない（○）。
4. TR3（セット1個） vs TR4（セット2個） → （カ）×
   • 構成例（存在証明用）: 単品A,B,C、セットS1 = {A,B}（主キー順A→B）、S2 = {B,C}（主キー順B→C）。表示順はS2 → S1 → … とする。
   • TR3はS1の処理（在庫[S1] をロック→Aをロック→Bをロック）。
   • TR4は表示順に従ってS2を先に処理し、在庫[S2] → B → Cとロックし、その後S1を処理してA → Bとロックしようとする。
   • 相互作用（代表的なタイミング）:
     • TR4がまず在庫[B]（S2の構成）をロックする。
     • TR3が在庫[A] をロックする。
     • TR3が次に在庫[B] を要求するがTR4が保持しているので待ち。
     • TR4がその後S1の処理で在庫[A] を要求するがTR3が保持しているので待ち。
     • TR3とTR4の間でAとBを巡る循環待ちが発生するためデッドロック。
   • 結論：よってデッドロックが起きるおそれがある（×）。

誤りやすいポイント

• 「同じ種類のトランザクションなら安全」と安易に決めつける誤り。トランザクションの種類が同じでも、注文する具体的な商品（どの単品・どのセット）や表示順の差でデッドロックが発生する場合がある。
• セット処理で「セット行だけロックする」と誤認する誤り。セットが不足する場合は構成単品の在庫行も順にロックする点を見落とすと説明が破綻する。
• ロック順序の基準を混同する誤り。単品は「表示順」で、セットの構成は「主キー順」でロックする、という二つの独立した順序がある点を明確にする。
• 「READ COMMITTEDならデッドロックは起きない」との誤解。隔離レベルは読み取りの可視性に関わるが、更新ロックの保持（コミットまで）はあり得るため循環待ちが発生する。

FAQ

解答の論理構成

1. トランザクション仕様
   「(3) 商品については、商品一覧画面に表示された順番に…更新する」「(5) ISOLATIONレベルは,READ COMMITTED」とある。
   → 更新時点で行ロックを取得する。
2. デッドロックの発生パターン
   • 取引Ａ：表示順が〔200→300〕
   • 取引Ｂ：表示順が〔300→200〕
     Ａが商品番号200をロック→Ｂが300をロック→Ａが300待ち→Ｂが200待ち で循環待ち。
3. 防止策
   全トランザクションで同一順序でロック取得すれば循環は起きない。主キー「商品番号」は「PRIMARY KEY」として表2・表3に定義され、全商品で一意。
4. よって更新は「商品番号」の順で行う。

誤りやすいポイント

• 「表示順」が指定されているのでそれを使うと思い込む。表示順は顧客操作や商品追加で変化し、統一順にならない。
• 行ロックよりテーブルロックを想定し、「ロックは一つだからデッドロックしない」と誤解。READ COMMITTEDでは行単位ロックが基本。
• 注文明細番号や登録順を使うと、一部商品だけを更新する別トランザクションとの間で順序不一致が起き得る。

FAQ