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

1．模範解答のキーワード整理

2．なぜこの解答になるのか

(ア)・(イ)：主索引／副次索引による読み込み方式

「アクセス経路が(ア)索引のとき，ページは順次に読み込まれるが，アクセス経路が(イ)索引のとき，1行当たり1ページがランダムに読み込まれる。」
とあります。
主索引（プライマリインデックス）順に格納されているデータは，関連するページをまとめて順次読み込めるため(ア)＝「主」，
副次索引（セカンダリインデックス）ではインデックスのキーでヒットした行ごとにページを読み込むので(イ)＝「副次」となります。

(ウ)・(エ)：“月別売上” テーブルの年間アクセス

• 行は「売上年月×店舗コード×商品コード」の組合せ数だけある。
• 商品コードの種類数は 30,000。
• 1 年は売上年月が 12 ヶ月分。
  したがって，
  1 店舗あたりの年間行数＝ 12×30,000 ＝ 360,000 行 → (ウ)
  1 ページ当たり行数は 100 行 → 最小読み込みページ数＝ 360,000 ÷ 100 ＝ 3,600 ページ → (エ)

(オ)・(カ)・(ク)：“月別売上B” テーブルの年間アクセス

• 商品コード数は変わらず 30,000
• 指定年を跨がない場合，1 店舗あたりの年間行数＝ 30,000 行 → (オ)
• 1 ページ当たり行数は 30 行 → 最小読み込みページ数＝ 30,000 ÷ 30 ＝ 1,000 ページ → (カ)
• 年をまたいで2 年分集計するなら 1,000 ページ × 2 年 ＝ 2,000 ページ → (ク)

(ケ)・(コ)：分析処理2の副次索引アクセス

• 店舗コードの種類数は 200 → 1 商品・1 月あたり最大 200 行 → (ケ)
• 副次索引を使うため「1 行当たり 1 ページ読み込み」→ 最大 200 ページ → (コ)

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

• 「主索引と副次索引で読み込み方式が違う」
  → 主索引は順次，副次索引はランダム。
• 行数計算
  → “月別売上” は「月×店舗×商品」だが，“月別売上B” は「年×店舗×商品」。
• ページあたり行数の違い
  → 元のテーブルは 100 行/ページ，B テーブルは 30 行/ページ。
• 年をまたぐ集計
  → B テーブルは年単位で1行なので，またぐと単純に行（ページ）数が2倍になる。

4．試験対策のまとめ

• インデックスのアクセス方式
  プライマリ／クラスタ化インデックスは順次読み込み，セカンダリインデックスはランダム読み込み。
• 集計粒度の違いに注意
  テーブル設計で「列で持つか」「行で持つか」によって行数・ページ数が大きく変わる。
• ページ数の求め方
  ページ数＝行数 ÷ 1ページ当たり行数。（切り上げ・切り捨てイメージを忘れずに。）
• 設問文の数値情報を活用
  「行数」「1ページ当たりの行数」「列値個数」はすべて計算根拠になるので，読み落とし厳禁。

キーワードと論点の整理

• 元の SQL１（表２）
  • テーブル：月別売上（縦長：年月・店舗・商品コード＋売上額）
  • 比較対象：X.売上年月 = :hv1（例 ‘201701’），Y.売上年月 = :hv2（例 ‘201702’）
  • 差分計算：Y.売上額 - X.売上額
• 変更後の SQL２（表２）
  • テーブル：月別売上Ｂ（横長：売上年，売上額1月～売上額12月，…）
  • １行につき「１年・１店舗・１商品」の売上がまとめて格納
  • 比較対象の年月を年単位にまとめて WHERE で絞り込み
  • 差分計算：列同士の引き算

解答の論理的な導出

(a) 差分を取る列名

• 図２「月別売上Ｂ」には，列名として

  …，売上額1月，販売数1月，売上額2月，販売数2月，…，売上額12月，…
  

  が定義されています。
• 例として :hv1 = '201701'（2017年1月），:hv2 = '201702'（2017年2月）であれば
  → 差分は「売上額2月」－「売上額1月」で計算すれば，SQL１と同じ結果が得られます。

売上額2月 - 売上額1月

(b) 絞り込み条件

• SQL２では１行に１年間分の売上データがまとめられているため，
  比較対象の年月（例：2017年1月・2017年2月）を含む年である「売上年」をキーに絞り込めばよい，と方針が書かれています。
• ホスト変数 hv1，hv2 がともに「2017」の年を指すので，
  WHERE 句には

  売上年 = '2017'
  

  を指定します。

売上年 = '2017'

SQL２ の完成形

SELECT 店舗コード, 商品コード,
       売上額2月 - 売上額1月       -- (a)
FROM   月別売上B
WHERE  売上年 = '2017'           -- (b)
ORDER BY 店舗コード, 商品コード;

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

1. 列名の取り違え
   • 「売上額1月」と「売上額2月」を逆に計算すると，マイナスの結果になるので注意。
2. 絞り込み条件の誤解
   • もともとのテーブルでは 売上年月 = :hv1/ :hv2 と月単位で指定していたが，
     月別売上Ｂでは月ごとの列を持っているため，WHERE で月を指定せず「年だけ」を指定する点を押さえること。
3. 動的 SQL の組み立て
   • 実装時にはハードコーディングするのではなく，ホスト変数から年部分を取り出して動的に 売上年 = 'YYYY' を生成することもポイントとなる。

試験対策として覚えておくべき知識

• テーブルを縦長（EAV型／属性－値）から横長（ピボット型）に変更すると，
  WHERE や差分計算の書き方が変わること。
• 列に「売上年」「売上額〇月」といった年月の分解格納がある場合の，
  絞り込みと演算の方法。
• 動的 SQL で ホスト変数 を使うとき，
  変数に入る文字列の形式（YYYYMM→YYYY）を考慮して文字操作を行う必要があること。

1. 模範解答のキーワードと論点整理

• 「選択リストの列名を変更しなければならない」
• ホスト変数では列名をバインドできない
  → 列名を動的に組み替える必要があるため、静的SQLでは対応できず動的SQLを採用

2. 解答の根拠（問題文の引用と説明）

「テーブル構造を変更した後で, SQL1 と同じ結果行を得るために, 実行の都度, 比較する年月に対応したSQL の構文を組み立て, 動的 SQL で実行することにした。」

SELECT 店舗コード, 商品コード,
       売上額X月 – 売上額Y月
  FROM 月別売上B
 WHERE 売上年 = 'YYYY'
 ORDER BY …

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

• 「ホスト変数はWHERE句の値だけに使える」と思い込み，列名やテーブル名には使えないことを見落とす
• 「動的SQL＝セキュリティリスク」とだけ考え，必要性を正しく理解しない
• SQL1 を流用して済ませられると誤解し，構造変更後の列名変更を忘れる

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

• ホスト変数（バインド変数）は「リテラル値の置換」にしか使えず，SQL構文要素（列名・テーブル名・演算子等）には利用できない
• 列名やテーブル名を動的に変更する必要がある場合は，動的SQL（EXECUTE IMMEDIATEなど）を用いる
• 問題文の「動的に組み立てて実行した」という記述は，列名等の静的構文要素を動的に変えたかったことを示すサインになる

模範解答のキーワードと論点整理

• NOT NULL 制約
• 「電話番号が設定されている場合だけ行を登録すべきところ」

解答が導ける理由の論理的説明

問題3：電話番号が設定されている場合だけ行を登録すべきところ，電話番号1列と電話番号2列の両方に NULL が設定されている行があった。

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

• 「NOT NULL 制約」を単に「必須入力」程度に理解し，なぜ複数列で使うべきかを誤解する
• 問題1〜2（重複禁止や列の順序）と混同して，UNIQUE 制約や CHECK 制約を誤って選択肢にする
• 引用が曖昧になり，“電話番号が設定されている場合だけ” の部分を正確に抜き出せない

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

• NOT NULL 制約は「必ず値を持たせる（NULL を禁止する）」ための基本制約
• 要件や問題文中のキーワード（「～すべきところ」「～を防ぐ」など）をそのまま引用して解答文を組み立てる
• 複数の問題（ここでは問題1～3）の中で，どの問題を解決するための制約かを明確に切り分けること
• 制約の種類と役割

  制約	役割
  NOT NULL	NULL 値の登録を禁止
  UNIQUE	重複値の登録を禁止
  CHECK	任意の論理条件を満たすかどうかを検査
  FOREIGN KEY	他テーブルの参照整合性を保持

小問の論点整理

• 一意性制約：同じ値の組み合わせが重複して登録されないようにする制約
• 除外対象：問題文で「主キー制約を除くこと」とあるため、主キーとは別に定義する制約を問う
• 要件との対応
  • 要件1：問題1～3（同じ電話番号の重複、NULL登録など）を解決
  • 要件2：社員１人当たり３個以上の電話番号を登録可能にする
  • 同じ電話番号が複数の社員で使われることがある（電話番号単独の一意制約は不要）

解答の論理的説明

「社員連絡先B」テーブルの構造

要件との対応づけ

• 「社員１人当たりの電話番号を3個以上登録できること」
  → 社員ID単独を一意制約にすると、社員IDごとに１行しか登録できないため要件2に反する。
• 「同じ電話番号が複数の社員で使われることがある」
  → 電話番号単独を一意制約にすると、異なる社員間で同じ番号が登録できず、要件2にも要件3（利用例）にも反する。
• 「同一社員内で同じ電話番号が重複しないこと」（問題1の防止）
  → 社員ID＋電話番号の組合せで一意制約を設定すれば、同一社員に対して同じ電話番号を複数回登録できなくなります。

(社員ID, 電話番号)
となります。なお、順序は制約定義の記述上自由ですので、
(電話番号, 社員ID)
としても同義です。

社員ID，電話番号　又は　電話番号，社員ID

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

1. 「電話番号」単独の一意制約を考えてしまう
   • これでは社員間で同じ番号が使えないため、要件2に反します。
2. 「社員ID」単独の一意制約を考えてしまう
   • これでは社員ごとに電話番号を１件しか持てないため、要件2に反します。
3. 主キー制約と混同する
   • 主キー制約は「登録行そのものを一意に識別する」ためのもので、すでに別途設定されている可能性があります。本問では「主キー制約を除く」一意制約を問われています。

試験対策ポイント

• 一意性制約（UNIQUE）の対象設定は，要件の「どの粒度で重複を防ぐか」と「どの粒度なら重複を許容するか」を整理してから決める。
• 「主キー制約」と「一意性制約」は役割が似ているが別物。主キーはテーブル行の識別、UNIQUEは主キー以外の重複防止に用いる。
• 要件に「NULLを許容しない」「NULLも重複判定に含める」などがある場合は、NOT NULL 制約との連携も確認すること。
• 制約定義の列順は「論理的な意味順」でそろえればよく，順序が変わっても意味は同じ。

解答のキーワードと論点整理

• 前半の (c AND d AND 電話番号１ <> 電話番号２) 部分で，「両方の電話番号が設定されている場合に異なる値であること」を保証
• 後半の (c AND e) 部分で，「電話番号１だけが設定されている場合」を許容
• それ以外（両方 NULL，電話番号２のみ設定）はチェック制約違反とする

解答に至る論理的説明

1. 要件と検査制約の構造対応

• 要件1（問題1）：「電話番号1 列と電話番号2 列は，異なる電話番号であるべきところ，同じ電話番号が設定されている行があった。」
  → 両方の列に値がある場合(c AND d)，電話番号１ <> 電話番号２ をチェックする。
• 要件1（問題2）：「電話番号1 列だけに電話番号を設定すべきところ，電話番号1列にNULL が，電話番号2列に電話番号が設定されている行があった。」
  → 電話番号２ が設定されているなら，必ず 電話番号１ も設定されていなければならない。前半の枝 (d を使う) のみではなく，後半の枝に入らずにチェック違反にする。
• 要件1（問題3）：「電話番号が設定されている場合だけ行を登録すべきところ，電話番号1列と電話番号2列の両方に NULL が設定されている行があった。」
  → 少なくとも 電話番号１ が設定されている必要がある。後半の枝 c を使うことで両方 NULL を排除する。

CHECK (
  (電話番号1 IS NOT NULL
   AND 電話番号2 IS NOT NULL
   AND 電話番号１ <> 電話番号２)
  OR
  (電話番号1 IS NOT NULL
   AND 電話番号2 IS NULL)
)

• 電話番号１だけ設定 → (c AND e) が真 → 通過
• 両方設定かつ異なる → (c AND d AND 異なる) が真 → 通過
• 電話番号２のみ設定 → どちらの枝も偽 → 制約違反
• 両方 NULL → どちらの枝も偽 → 制約違反

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

1. 電話番号２のみ設定を許す条件を入れてしまう
   選択肢②「電話番号1 IS NULL」を c に誤って当てはめたり，後半の枝に「電話番号1 IS NULL AND 電話番号2 IS NOT NULL」を入れると，電話番号２のみ設定も通ってしまい，問題2を防げません。
2. 両方 NULL のチェック漏れ
   後半の枝に「電話番号1 が NULL かつ 電話番号2 が NULL」という条件を含めないと，両方 NULL の行も制約違反とならず，問題3を防げません。
3. NOT NULL と NULL の混同
   c, d, e のいずれを「IS NULL」「IS NOT NULL」に割り当てるのか迷いやすいので，要件と論理式の役割を整理して対応させることが重要です。

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

• チェック制約では AND, OR の組み合わせで「許容パターン」と「禁止パターン」を明確に分ける。
• NULL 値を伴う比較は典型的なひっかけ論点。
  • column IS NULL / column IS NOT NULL の使い分け
  • NULL 同士の比較（<>，=）は常に偽になることに注意する。
• 要件定義（問題1〜3）→ 論理式（許容条件）→ チェック制約の書き方、という順序で整理するとミスが減る。
• 複数の条件を OR で結合する場合，各枝で排他的に成り立つように構成する。

キーワードと論点整理

• 検査制約（CHECK制約）
• NULL／NOT NULL 判定
• 電話番号1, 電話番号2 の組み合わせ
• INSERT … UNION による行の分割移行
• 表示順（「1」「2」「3」など）

解答の論理的根拠

INSERT INTO 社員連絡先B
    SELECT 社員ID, 1, 電話番号1             FROM 社員連絡先 WHERE □□□f□□□
  UNION SELECT 社員ID, 2, 電話番号2         FROM 社員連絡先 WHERE 電話番号1 <> 電話番号2
  UNION SELECT 社員ID, 3, 電話番号2         FROM 社員連絡先 WHERE □□□g□□□ AND □□□h□□□

1. 「電話番号1」を表示順1で挿入
   ⇒ 電話番号1 列に有効な電話番号がある行のみを対象にする必要があります。
   「電話番号1 IS NOT NULL」(選択肢①) が該当します。
2. 「電話番号2」を表示順2で挿入（ただし，電話番号1 と異なる場合のみ）
   ⇒ WHERE 電話番号1 <> 電話番号2 で電話番号1,2 が異なる行のみ抽出します。
   （問題文中既に指定）
3. 「電話番号2」を表示順3で挿入
   ⇒ 電話番号1 列が NULL かつ，電話番号2 列に値がある行を対象にする必要があります。
   • 電話番号1 IS NULL (選択肢②)
   • 電話番号2 IS NOT NULL (選択肢③)

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

• 「電話番号1 と電話番号2 が異なる場合」に対応するのは 2番目の UNION であり，ここには既に WHERE 電話番号1 <> 電話番号2 が書かれているため，他のブランチではこの条件は不要です。
• 第3ブランチで誤って「電話番号1 IS NOT NULL」を使うと，同じ社員の同じ番号を二度登録することになります（問題2 の重複登録を防げない）。
• 第1ブランチと第3ブランチで両方とも電話番号2を扱う場合，表示順を「2」と「3」で切り分ける必要があります。問題文では「表示順列はSMALLINT型」と定義されています。

試験対策ポイント

• 複数の値を縦方向に正規化する際は，NULL 判定と重複排除の条件を各ブランチで明確に分ける。
• UNION を用いる場合は，各 SELECT の WHERE 節が相互に重複しないよう注意する。
• 「IS NULL」「IS NOT NULL」は SQL の基本。NULL と等価演算子（=, <>）を混同しない。
• 表示順など追加する列は，INSERT の列リストと SELECT の列リストで対応関係を正確に管理する。

1. 模範解答の核心となるキーワードや論点

• INSERT … UNION … SELECT による多段抽出
• 各 SELECT の WHERE 句に対応する条件
  • f: 電話番号１列に NOT NULL 制約をかけるべき条件 → 「電話番号１ IS NOT NULL」
  • 電話番号１列と電話番号２列が異なる行を抽出 → 「電話番号１ <> 電話番号２」
  • g/h: 電話番号１列が NULL かつ電話番号２列が NOT NULL の行 → 「電話番号１ IS NULL AND 電話番号２ IS NOT NULL」
• UNION では重複行が排除される
• NULL を含む比較（<>）は偽または UNKNOWN 扱いとなる点

2. 解答がこうなる理由の論理的説明

INSERT INTO 社員連絡先B
  SELECT 社員ID, 1, 電話番号１
    FROM 社員連絡先
   WHERE □□□f□□□
UNION SELECT 社員ID, 2, 電話番号２
    FROM 社員連絡先
   WHERE 電話番号１ <> 電話番号２
UNION SELECT 社員ID, 1, 電話番号２
    FROM 社員連絡先
   WHERE □□□g□□□ AND □□□h□□□

• 第１の SELECT 句：
  • 「表示順＝1, 電話番号１」を抽出
  • f の条件は「電話番号１ IS NOT NULL」で、表３の E1, E2, E3 が該当
    → (E1,1,1111)、(E2,1,2222)、(E3,1,3333)
• 第２の SELECT 句：
  • 「表示順＝2, 電話番号２」を抽出
  • WHERE「電話番号１ <> 電話番号２」
    • E1: 1111 <> 3333 → 抽出 → (E1,2,3333)
    • E2: 2222 <> 2222 → 偽 → 除外
    • E3: 3333 <> NULL → UNKNOWN → 除外
    • E4: NULL <> 4444 → UNKNOWN → 除外
    • E5: NULL <> NULL → UNKNOWN → 除外
• 第３の SELECT 句：
  • 「表示順＝1, 電話番号２」を抽出
  • WHERE「電話番号１ IS NULL AND 電話番号２ IS NOT NULL」
    • E4: NULL ＆ 4444 → 抽出 → (E4,1,4444)
    • E5: NULL ＆ NULL → 電話番号２ IS NOT NULL で偽 → 除外

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

• NULL 値同士／NULL と値との比較は「<>」では TRUE にならない
• UNION はデフォルトで重複行を排除する
• 第３の SELECT でも「表示順＝1」を指定している点を見落とすと、E4 の表示順を「2」と書いてしまう
• 問題文の「問題1〜3を解決する」「電話番号列に NOT NULL 制約」「一意性制約」を混同し、不要な行を除外しすぎる

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

• NULL の扱い
  • 比較演算子（=, <>）では NULL は UNKNOWN → WHERE句では除外される
  • IS NULL／IS NOT NULL を必ず使う
• UNION／UNION ALL の違い
  • UNION は重複排除、UNION ALL は重複も含める
• 多値属性の正規化
  • 列を増やすのではなく、行を分割して管理する手法
• INSERT … SELECT … WHERE によるデータ移行時は条件分岐を明確に書き分ける
• 表示順などの固定値を指定する場合は、全ての SELECT で誤りなく指定すること