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

解答の導き方

1. (ア)(イ)：どの索引ならページが順次に読み込まれるか
   本文には「テーブルの行は追加された順に並び」とあり、“月別売上” テーブルについては「行が主索引のキー順にロードされている」、“月別売上B” テーブルについても主索引のキー順にロードしたと書かれています。つまり、どちらのテーブルもディスク上の行の並びは主索引のキー順と同じです。そのため主索引のキー順に複数行を読むと、次の行は同じページかすぐ次のページにあり、ページは順次に読み込まれます。
   一方、表1の副次索引のキーは売上年月（売上年）→商品コード→店舗コードの順で、行の格納順（売上年月（売上年）→店舗コード→商品コード）とは異なります。副次索引のキー順に読むと1行ごとに離れたページへ飛ぶことになり、設問の「次にアクセスするページはバッファにない」という前提から、1行当たり1ページをランダムに読むことになります。したがって(ア)は「主」、(イ)は「副次」です。
2. (ウ)：分析処理1で “月別売上” から読む行数
   表1から、“月別売上” は360,000,000行で、売上年月の列値個数は60、店舗コードの列値個数は200です。1か月・1店舗当たりの平均行数は360,000,000 ÷ 60 ÷ 200 = 30,000行です。分析処理1は「指定した1店舗について、任意の1年間」なので12か月分が必要になり、30,000 × 12 = 360,000行が(ウ)です。
3. (エ)：主索引で読むときの最小ページ数
   設問に「索引のバッファヒット率は100%」とあるので、数えるのはテーブルの行を格納したページだけです。主索引のキーは売上年月→店舗コード→商品コードの順なので、同じ売上年月・同じ店舗の30,000行はディスク上で連続して並んでいます。1ページ当たり100行なので、1か月分は30,000 ÷ 100 = 300ページです。
   ただし、月が変わると間に他の店舗の行が入るため、12か月分はひと続きではなく、300ページの塊が12個に分かれて並びます。各塊の先頭がページの先頭にそろう最も有利な場合で300 × 12 = 3,600ページとなり、これが「最小限」の(エ)です。
4. (オ)(カ)：“月別売上B” の場合
   “月別売上B” は1行に売上額1月〜売上額12月・販売数1月〜販売数12月を持つので、“月別売上” の12か月分の12行が1行にまとまります。行数は360,000,000 ÷ 12 = 30,000,000行、売上年の列値個数は60 ÷ 12 = 5です。1年・1店舗当たりの平均行数は30,000,000 ÷ 5 ÷ 200 = 30,000行で、これが(オ)です。
   主索引のキーは売上年→店舗コード→商品コードの順なので、この30,000行は連続して並んでいます。表1の1ページ当たりの行数30から、30,000 ÷ 30 = 1,000ページが(カ)です。
5. (キ)(ク)：1年間が年をまたぐ場合
   例えば2016年4月〜2017年3月を分析すると、売上年が2016の行からは4月〜12月の列を、2017の行からは1月〜3月の列を使います。“月別売上B” は1行に1年分が入っているので、使う月が一部だけでも、2年分の行をすべて読まなければなりません。したがって(キ)は2で、読込みページ数は1,000 × 2 = 2,000ページとなり、これが(ク)です。
6. (ケ)：分析処理2で読む行数
   分析処理2は「指定した1商品について、任意の月」を分析します。“月別売上” では、ある売上年月・ある商品コードの行は店舗ごとに1行です。“月別売上B” では月は列（売上額1月など）なので、ある売上年・ある商品コードの行もやはり店舗ごとに1行です。店舗コードの列値個数は200なので、どちらのテーブルでも最大200行となり、これが(ケ)です。「最大」なのは、すべての店舗にその商品の行があるとは限らないためです。
7. (コ)：副次索引で読む最大ページ数
   分析処理2の条件は売上年月（売上年）と商品コードで、副次索引のキーの先頭2列に一致するため、副次索引を使って読みます。1.で確かめたとおり副次索引では1行当たり1ページがランダムに読み込まれるので、200行なら最大200ページとなり、これが(コ)です。行の格納順では、同じ月・同じ商品でも店舗が違えば他の商品の行を挟んで離れているので、同じページにまとまることはありません。

誤りやすいポイント

• 1ページ当たりの行数を取り違える。“月別売上” は100行、“月別売上B” は30行です。(カ)を30,000 ÷ 100 = 300と計算しないよう注意します。
• (エ)を、12か月分がひと続きに並んでいると考えて求めてしまう。数値は同じ3,600になりますが、実際は月ごとに300ページの塊に分かれています。この並び方を押さえておかないと、「最小限」と書かれている意味や、年をまたぐ場合の考え方でつまずきます。
• 年をまたいでも12か月分なので読む量は変わらないと考え、(ク)を1,000のままにする。“月別売上B” は1行が1年分なので、2年分の行を読む必要があります。
• 副次索引でも主索引と同じように1ページに複数行をまとめて読めると考え、(コ)を200 ÷ 100 = 2ページなどと少なく見積もる。

FAQ

解答の論理構成

1. 問題設定の確認
   • 【問題文】「同じ年の二つの月、例えば,2017年1月と2017年2月の売上額の差を求めるSQL」とある。
   • さらに「ホスト変数の hv1 及び hv2 には、それぞれ ‘201701’ 及び ‘201702’ が設定」と記載。
2. 比較対象の月を列で表現
   • “月別売上B” では「売上額1月」「売上額2月」… と1行に12か月分を保持する縦持ち→横持ち（ワイドテーブル）設計に変更済み。
   • 双方の月を同一行内で参照できるため、SELECT 句は
     売上額2月 - 売上額1月
     で目的の差分が得られる。従来の自己結合は不要。
3. WHERE 句の絞り込み
   • 列として「売上年」が存在し、比較月は列名で確定しているため、条件は年のみ。
   • hv1・hv2の先頭4桁が “2017” とわかるので
     売上年 = '2017'
     が適切。
4. 以上より
   • (a) = 「売上額2月 - 売上額1月」
   • (b) = 「売上年 = '2017'」

誤りやすいポイント

• 縦持ちの “月別売上” と横持ちの “月別売上B” を混同し、自己結合を書いてしまう。
• WHERE 句で月も絞り込みたくなり 売上月 IN (1,2) などと余計な条件を付け、結果列に NULL が混在して誤差分が NULL になる。
• 年をハードコードせずSUBSTR(:hv1,1,4) と書きたくなるが、試験では具体例をそのまま記述させる設問である点を見落とす。

FAQ

解答の論理構成

1. テーブル変更の影響
   “月別売上B” は「売上年、店舗コード、商品コード」に続き「売上額1月」「売上額2月」…の列を持つワイドテーブル。
2. 既存SQL1の構造
   SQL1 では WHERE X.売上年月 = :hv1 AND Y.売上年月 = :hv2 によって行を選び、同じ列 売上額 を減算して月間差を算出していた。
3. 列名が可変になる問題
   ワイドテーブルでは比較する月ごとに引き当てる列が変わる。たとえば1月と2月なら 売上額1月 と 売上額2月、3月と5月なら 売上額3月 と 売上額5月 になる。
4. 静的SQLでは対応不能
   プレースホルダ（バインド変数）はリテラル値や演算対象に使えても、列名を置き換える機能はない。
5. 動的SQLが唯一の解決策
   したがって【問題文】にある通り「実行の都度、比較する年月に対応したSQLの構文を組み立て」る必要が生じ、動的SQLを選択した。

誤りやすいポイント

• バインド変数で列名まで可変にできると誤解する。
• 「WHERE 句だけが問題」と考え、SELECT 句の列名置換を見落とす。
• 年月を跨ぐ場合のページ読み込み増など別テーマの記述と混同し、本設問の焦点を外す。

FAQ

解答の論理構成

1. 【問題文】には電話番号列の欠陥として
   「問題3：電話番号が設定されている場合だけ行を登録すべきところ…NULLが設定されている行があった。」
   と記述されている。
2. そこでＦさんは「“社員連絡先B” テーブルの電話番号列に NOT NULL制約を定義」したと述べている。
3. NOT NULL制約を付ければNULL値（＝電話番号未設定）を持つ行そのものが登録不可能になる。
4. したがって「電話番号が設定されている行だけを登録したい」という意図と完全に一致する。

誤りやすいポイント

• 「一意性制約」も定義されているが、NOT NULLとは目的が異なる（重複防止ではなく空値防止）。
• 問題1・問題2の対策と混同し、NULL制約の役割をあいまいに答えてしまう。
• “行を更新するとき”ではなく“登録(INSERT)時”のチェックであることを忘れがち。

FAQ

解答の論理構成

1. 【問題文】「要件1：問題1〜3を解決すること」より、
   • 問題1：同一社員行内で電話番号が重複してはいけない
2. 【問題文】「なお、同じ電話番号が複数の社員で使われることがある。」より、
   • 電話番号は社員をまたいで重複してもよい
3. 【問題文】「4.“社員連絡先B” テーブルの電話番号列に NOT NULL制約を定義し、テーブルに一意性制約を定義した。」より、
   • NULLは排除済みなので一意性検証は値比較だけで済む
4. 主キー (社員ID, 表示順) では “同じ社員が1と2の両行に1111” のような重複を防げない
5. 電話番号のみでは異なる社員E1とE3が3333を共有できなくなる
6. よって「社員IDと 電話番号」の組に一意性制約を設けることで
   ・同一社員内の重複を排除（問題1解決）
   ・異なる社員間の番号共有を許容（要件2を阻害しない）
7. 以上より答えは「社員ID、電話番号」です。列順に意味はないため「電話番号、社員ID」でも同義となります。

誤りやすいポイント

• 電話番号列だけに一意性を掛けてしまい、複数社員で同じ番号を持てなくなる
• 主キーと同じ「社員ID、表示順」を再度一意にしてしまい、問題1が未解決
• 「表示順、電話番号」とすると同一番号を異なる表示順で登録できる点を見落とす

FAQ

解答の論理構成

1. 【問題文】では検査制約を
   CHECK ( ((c) AND (d) AND 電話番号1 <> 電話番号2) OR ((c) AND (e)) )
   と提示しています。
2. 目的は【問題文】の “問題1〜3を防ぐ” こと。具体的には
   • 問題1：同じ番号を重複登録
   • 問題2：電話番号1がNULL、電話番号2のみ有値
   • 問題3：両方 NULL
3. まず両パターンとも (c) が 共通 で使われているため、(c) は必ず満たすべき条件です。問題2・3を防ぐには 電話番号1の非NULLを必須にする必要があるので (c)＝① 電話番号1 IS NOT NULLと決定します。
4. 第1グループは「両列に値があり、かつ異なる」条件です。ここで (d) は 電話番号2の非NULL、つまり ③ 電話番号2 IS NOT NULLが入ります。
5. 第2グループは「電話番号2が無い」パターンを許可するため (e)＝④ 電話番号2 IS NULL。
6. 以上より
   • c：① 電話番号1 IS NOT NULL
   • d：③ 電話番号2 IS NOT NULL
   • e：④ 電話番号2 IS NULL
     が正答となります。

誤りやすいポイント

• (c) と (d) を “電話番号1・2それぞれNULL／NOT NULLを入れ替えても同じ” と早合点する。実際には (c) が両グループで共通なので入れ替え不可です。
• 問題1の「値が同じ」を <> 条件で排除できることを忘れ、(d) に 電話番号2 IS NULLを入れてしまう。
• 問題2を “電話番号2だけ有値も許可” と誤読し、(c) に ② 電話番号1 IS NULLを選んでしまう。

FAQ

解答の論理構成

1. 【問題文】図4（INSERT 文）の構造
   SELECT 社員ＩＤ, 1, 電話番号1 … WHERE □(f) UNION SELECT 社員ＩＤ, 2, 電話番号2 … WHERE 電話番号1 <> 電話番号2 UNION SELECT 社員ＩＤ, 1, 電話番号2 … WHERE □(g) AND □(h)
2. 選択肢は 【問題文】「① 電話番号1 IS NOT NULL ② 電話番号1 IS NULL ③ 電話番号2 IS NOT NULL ④ 電話番号2 IS NULL」。
3. 第1SELECT（表示順=1, 電話番号1）
   • 目的：有効な “電話番号1” をそのままコピー。
   • 必要条件：電話番号1がNULLでないこと → “①”。
   • 重複なしの要件とも矛盾しない。
     ⇒ (f)=①。
4. 第2SELECT（表示順=2, 電話番号2）
   • WHERE 句に既に 電話番号1 <> 電話番号2 がある。
   • “電話番号1 = 電話番号2” 行（【問題文】“問題1”）を除外して二件目として登録する意図。
   • 追加の述語は不要なので (g)(h) ではない。
5. 第3SELECT（表示順=1, 電話番号2）
   • 目的：電話番号1が未入力で 電話番号2だけ登録されている行（【問題文】“問題2”）を救済。
   • 条件① 電話番号1 IS NULL → “②”。
   • 条件② 電話番号2 IS NOT NULL → “③”。
   • 両条件は AND で連結、順序は問われていない。
     ⇒ (g)=②、(h)=③（順不同）。

誤りやすいポイント

• 第2SELECTに既に 電話番号1 <> 電話番号2があるため、「③」を重ねて書かないこと。
• 第3SELECTの表示順は “1” なので「2」にしてしまうミスに注意。
• NULL判定に = を使う誤記（電話番号1 = NULLなど）はSQL文法エラーとなる。

FAQ

解答の導き方

• 第1SELECT：社員ＩＤ、1、電話番号１ を挿入する（WHERE □（f））。
• 第2SELECT：社員ＩＤ、2、電話番号２ を挿入する（WHERE 電話番号１ <> 電話番号２）。
• 第3SELECT：社員ＩＤ、1、電話番号２ を挿入する（WHERE □（g） AND □（h））。

• 第1SELECTの条件fは「電話番号１ IS NOT NULL」。電話番号１が存在する場合に表示順1として挿入する。
• 第2SELECTは図示どおり「電話番号１ <> 電話番号２」で、電話番号１と電話番号２が存在し、かつ異なる場合に表示順2として電話番号２を挿入する（注意：SQLではNULLと比較すると結果はUNKNOWNになりWHEREでは除外されるため、電話番号２がNULLの行は対象にならない）。
• 第3SELECTの条件gとhは「電話番号１ IS NULL AND 電話番号２ IS NOT NULL」。電話番号１がNULLで電話番号２が存在する場合に、電話番号２を表示順1として挿入する（電話番号１が空だったときに電話番号２を繰り上げるため）。

• E1（電話番号1=1111、電話番号2=3333）
  第1SELECT: 電話番号１ IS NOT NULL → (E1,1,1111) が得られる。
  第2SELECT: 電話番号１ <> 電話番号２ → 1111 <> 3333は真 → (E1,2,3333) が得られる。
  第3SELECT: 電話番号１ IS NULLではない → 対象外。
  結果：(E1,1,1111) と (E1,2,3333)
• E2（電話番号1=2222、電話番号2=2222）
  第1SELECT: 電話番号１ IS NOT NULL → (E2,1,2222) を挿入。
  第2SELECT: 電話番号１ <> 電話番号２ → 2222 <> 2222は偽 → 対象外（電話番号２は挿入されない）。
  第3SELECT: 電話番号１ IS NULLではない → 対象外。
  結果：(E2,1,2222) のみ（同一番号が1行になる）
• E3（電話番号1=3333、電話番号2=NULL）
  第1SELECT: 電話番号１ IS NOT NULL → (E3,1,3333)。
  第2SELECT: 電話番号１ <> 電話番号２ → NULLとの比較でUNKNOWN → 対象外。
  第3SELECT: 電話番号２ IS NOT NULLではない → 対象外。
  結果：(E3,1,3333)
• E4（電話番号1=NULL、電話番号2=4444）
  第1SELECT: 電話番号１ IS NOT NULLではない → 対象外。
  第2SELECT: 電話番号１ <> 電話番号２ → NULLとの比較でUNKNOWN → 対象外。
  第3SELECT: 電話番号１ IS NULL AND 電話番号２ IS NOT NULL → 真 → (E4,1,4444)。
  結果：(E4,1,4444)
• E5（電話番号1=NULL、電話番号2=NULL）
  いずれのSELECT条件も満たさない → 行は移行されない。

誤りやすいポイント

• NULLの扱いを誤る：電話番号１ <> 電話番号２ はどちらかがNULLだとUNKNOWNになりWHEREで除外される。NULLを真偽の普通の値と同等に扱うと誤答する。
• UNION と UNIQUE の取り扱いを混同する：UNIONは全列が同一の行を統合するが、表示順が異なれば別行として残る。表示順で重複を避ける設計になっているかを確認する必要がある。
• 一意性制約のキーを混同する：本問では一意性制約を (社員ID, 電話番号) と想定した。もし (社員ID, 表示順) を一意キーと誤認すると、第1SELECTと第3SELECTが同じ表示順を出すケースを心配して誤答する可能性がある。
• 第2SELECTの条件だけで電話番号２のNULLを明示的に排除していないが、比較演算の結果で除外される点を見落とすと、不要なWHERE条件を探して迷う。
• 両方NULLの行を挿入しない意図を見落とし、NULL行が残ると誤答になる。

FAQ