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

キーワード・論点整理

• UNIQUE 制約：指定した列（または列の組合せ）の値が、テーブル内で重複しないことを保証する制約
• 主キー制約との違い：主キー制約も一意性を保証するが、別に候補キーとして使いたい列の組合せがある場合に UNIQUE 制約を定義する
• 図１（主なテーブル構造）と表１（主な列の意味・制約）の読み取り

解答の理由

1. RDBMS の仕様より

   「主キー制約及び UNIQUE 制約は, 制約を定義する列の構成に一致したユニーク索引が RDBMS によって自動的に作成されることで一意性が保証される。」(仕様(6))

2. 店舗テーブルの構造
   図１より、店舗テーブルの主キーは

   店舗（〈施設ID〉, 〈店舗ID〉, …）
   と示されており、「施設ID ＋ 店舗ID」で一意になる。

   しかし表１の「内線番号」の定義には

   「各店舗に設置されている内線電話を施設内で識別する番号」
   とあることから、同一施設内での「内線番号」は重複してはならない。
   そこで「施設ID ＋ 内線番号」の組合せに UNIQUE 制約を追加すると、
   同一施設で異なる店舗間の内線番号重複を防止できる。

3. 精算テーブルの構造
   図１より、精算テーブルの主キーは

   精算（〈利用年月日〉, 〈施設ID〉, 〈券番号〉, …）
   と示されており、この組合せだけで一意性が保たれる。
   他に業務上「別の列の組合せで重複禁止」とする要件が記載されていないため、
   追加の UNIQUE 制約は不要であり、「なし」となる。

店舗・精算テーブルの UNIQUE 制約設計例

誤りやすいポイント

• 「内線番号」単独に UNIQUE 制約を付ける
  → 他施設間で同じ内線番号を使う可能性があるため、単独では足りない。
• 「店舗ID, 内線番号」を組合せにする
  → 店舗ID はすでに主キーの一部。内線番号の一意性要件とは別次元。
• 精算テーブルにも何か追加制約が必要と考える
  → 主キーで一意性が担保されているので、追加制約は不要。

試験対策まとめ

• 図１で下線付きの列は「主キー」。主キー制約による一意性は自動付与。
• UNIQUE 制約は主キーとは別の「候補キー」や「業務上の別の一意性要件」を実現するときに使う。
• 候補キーに不足がある場合は複数列の組合せで UNIQUE 制約を定義する。
• 要件（表１など）をよく読み、「どの範囲で重複を許さないか」を正しく把握する。

問題の概要

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

CHECK(
  ( 年齢区分 = ’1’ AND [□ a □] )
  OR ( [□ b □] AND [□ c □] )
  OR ( 年齢区分 = ’3’ AND [□ d □] )
)

2. 解答の論理的説明

1. (a)：年齢区分 = '1' のとき
   • 「子供」は 12 歳未満
   • → 年齢 < 12
   • 【引用】表１：「年齢区分 子供（12 歳未満）：'1'」
2. (b)・(c)：真ん中の OR 節
   • 年齢区分が '2' → 「大人」
   • 大人の年齢範囲は 12 歳以上 60 歳未満
   • (b) には「年齢区分 = '2'」
   • (c) には「年齢 BETWEEN 12 AND 59」
   • 【引用】表１：「大人（12 歳以上 60 歳未満）：'2'」
3. (d)：年齢区分 = '3' のとき
   • 「シニア」は 60 歳以上
   • → 年齢 >= 60
   • 【引用】表１：「シニア（60 歳以上）：'3'」

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

• 「60 歳未満」を 59 と解釈するか，60 と解釈するか混同しやすい。
  → 「60 歳未満」は最大値 59 までなので，BETWEEN 12 AND 59 と正確に書く。
• BETWEEN を使わずに 12 <= 年齢 AND 年齢 < 60 と書いてもよいが，問題の意図に合った簡潔な表現を選ぶ。
• 真ん中の OR 節で (年齢区分 = '2' AND 年齢 BETWEEN 12 AND 59) の順序を誤ると，論理構造が崩れて「年齢区分を見ずに年齢だけで判定」してしまうリスクがある。

4. 試験対策ポイント

• 表１の「年齢区分」と実際の年齢の対応関係を正確に覚えること。
  • 子供：年齢 < 12
  • 大人：12 ≤ 年齢 < 60
  • シニア：年齢 ≥ 60
• CHECK 制約では，複数の AND/OR を組み合わせる際に「どの条件を先に評価するか」に注意し，全体で排他的かつ網羅的な判定になるように設計する。
• 範囲指定では端点の含否（inclusive/exclusive）を見落とさないようにする。

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

• ユニーク索引／非ユニーク索引
  • 仕様(3)：索引は「ユニーク索引」と「非ユニーク索引」に分けられる。
  • 「ユニーク索引」はキー列の組合せが重複しないことを保証する索引。
  • 「非ユニーク索引」はキー列に重複が許される索引。
• クラスタ性（高クラスタ／低クラスタ）
  • 仕様(4)：
    • 高クラスタな索引：キー順と行の物理並び順が一致、あるいは隣接するキーが指す行が同じページに格納されている割合が高い。
    • 低クラスタな索引：キー順と物理並び順の一致度が低く，行へのアクセスがランダムになる。

2. 解答理由

① 索引1は非ユニーク索引

• 表４より索引1のキー構成：

  索引	テーブル	キーの構成
  索引1	店舗利用	施設ID, 鍵番号, 未精算フラグ

• DML2のWHERE句：「WHERE 施設ID = :施設ID AND 鍵番号 = :鍵番号 AND 未精算フラグ = 'Y'」
  → 同一の施設ID・鍵番号で未精算フラグ='Y'の行が複数存在し得る。
• 仕様(3)に照らすと，キーに重複が生じ得るため「非ユニーク索引」に該当します。

② 索引2が高クラスタな索引である理由

• 表４より索引2のキー構成：

  索引	テーブル	キーの構成
  索引2	精算明細	利用年月日, 施設ID, 券番号

• 精算時には「同じ券番号の行がまとめて追加」される。
• 仕様(4)「高クラスタな索引: …隣接するキーが指す行が同じページに格納されている割合が高い」
  → 同一キー（券番号）で追加された行が近接して格納され、クラスタ性が高くなる。

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

• ユニーク索引か否かの誤解
  「未精算フラグ」も索引キーに含まれているため一意にならないケースを見落としがち。
• クラスタ性の判定
  キーの順序だけでなく、実際の行の物理配置（挿入順やまとめ挿入）を考慮する点に注意。

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

• ユニーク索引：キーの重複を許さない。
• 非ユニーク索引：キーに重複がある。
• 高クラスタ索引：
  • 物理的な行の並びがキー順に近い、あるいは同一キーの行が同一ページにまとまる。
• 索引設計ではDMLのアクセスパスと実際のデータ追加／更新パターンを考慮すること。

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

解答に至る論理的解説

1. ステップ2
   「RDBMSで指定できるページ長（バイト）の中から、（　ア　）の行を1行以上格納できるページ長を求め、ページ頭部を除いた有効ページ長（バイト）を得る。」
   この「1行以上格納できる行長」は“最大行長”を基準に選ぶため、
   （ア）＝ 最大行長
2. ステップ3
   「平均行長 ≦ （　イ　）× 空き領域率 ÷ 100」
   「イ」はヘッダ領域を除いた「ページ頭部を除いた」バイト数を指すので、
   （イ）＝ 有効ページ長
   そして左辺の「平均行長」は、1行あたりの平均サイズそのものなので、
   （ウ）＝ 平均行長
3. ステップ4
   「ページ当たりの平均行数 = （　イ　） ÷ （　ウ　） の小数点以下を切捨て」
   ここも上で求めた「有効ページ長」と「平均行長」を用いて、1ページに何行入るかを計算します。
   「必要ページ数 = （　エ　） ÷ （　オ　） の小数点以下を切上げ」
   • 「見積行数」は、表５ステップ1で得たテーブルの「行数の見積り」です。
     ⇒ （エ）＝ 見積行数
   • 「ページ当たりの平均行数」は、直前の行数計算結果です。
     ⇒ （オ）＝ ページ当たりの平均行数
   「テーブルのストレージ所要量 = （　カ　） × （　キ　）」
   • 1ページあたりのサイズは「ページ長」そのものを使い、
     ⇒ （カ）＝ ページ長
   • テーブルの必要ページ数は上で求めた値です。
     ⇒ （キ）＝ 必要ページ数

受験者が誤りやすいポイント・ひっかけ

• 最大行長と平均行長の混同
  可変長列がある場合、ページを選ぶ基準は最悪ケースである「最大行長」です。平均行長を基準にすると、最大行長を格納できないページ長を選んでしまう恐れがあります。
• ページ長と有効ページ長の違い
  「ページ長」はヘッダ領域も含む物理的な長さですが、実際に行を格納できる部分は「有効ページ長」です。読み飛ばしやすいので注意してください。
• 切捨て・切上げの適用タイミング
  平均行数は「小数点以下切捨て」、必要ページ数は「小数点以下切上げ」と、目的に応じて違う丸め方をする点も押さえましょう。

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

1. ストレージ見積もりの流れ
   (1) 見積行数・平均／最大行長を把握
   (2) 適切なページ長を選定し、有効ページ長を計算
   (3) 平均行数＝有効ページ長 ÷ 平均行長
   (4) 必要ページ数＝見積行数 ÷ 平均行数
   (5) 総バイト数＝ページ長 × 必要ページ数
2. 各用語の定義
   • ページ長：物理的に割り当てる 1ページのバイト数
   • 有効ページ長：ページ長 − ページヘッダ丈
   • 最大行長：最も大きい可能性のある行サイズ
   • 平均行長：過去実績に基づく平均的行サイズ
3. 丸め処理
   • ページ当たり行数：切捨て
   • 必要ページ数：切上げ

キーワード・論点整理

• 作業 W6（DML 性能予測）
• 表６の下線部分（ク）～（コ）
• 用語：「必要ページ数」「探索行数」「ページ当たりの平均行数」

解答の論理的な説明

・アクセスパスが表探索の場合:
  読込みページ数 = （ク）

・アクセスパスが索引探索の場合、かつ、
  索引が低クラスタな索引の場合:
   最大読込みページ数 = （ケ） … A

  索引が高クラスタな索引の場合:
   最小読込みページ数 = （ケ） ÷ （コ） の小数点以下を切り上げ … B

・アクセスパスが索引探索の場合、読込みページ数は A と B の間の値になる。

• 探索行数：索引探索や表探索で実際に検索対象となる行数

1. 表探索ではテーブル全体を読むため、「読込みページ数」はテーブルの必要ページ数
2. 索引探索（低クラスタ）では探索対象の各行がバラバラのページにある最悪ケース → 最大読込みページ数は探索行数
3. 索引探索（高クラスタ）では行がまとめて格納される最良ケース → 最小読込みページ数は探索行数 ÷ ページ当たりの平均行数

• （ク）＝ 必要ページ数
• （ケ）＝ 探索行数
• （コ）＝ ページ当たりの平均行数

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

• 「結果行数」と「探索行数」を混同しない
  → 問題文に「結果行数＝探索行数とは限らない」と明示。性能予測では実際に探索する行数（探索行数）を用います。
• 「必要ページ数」と「ページ当たりの平均行数」を取り違えない
  → 前者はテーブル全体の読み込みに要するページ数、後者は１ページに格納できる行数を指します。

試験対策ポイント

• 表６のアクセスパス別に「読込みページ数」を予測するフローを押さえる
  1. 表探索 → 必要ページ数
  2. 索引探索（低クラスタ） → 探索行数
  3. 索引探索（高クラスタ） → 探索行数 ÷ ページ当たりの平均行数
• 表５と表６で共通して使われているキーワードの定義を正確に覚える
• 「平均行長」「最大行長」「空き領域率」など、ストレージ設計時の用語も併せて理解を深めることで、DML 性能予測の根拠が明確になります。

キーワード・論点

• 作業依存関係／並行実行
• WBS（作業工程表）における「前提が完了すれば並行可能」
• W3（アクセス権限設計）はテーブル設計後に並行可能
• W9（性能測定用データ設計・データ生成）もテーブル設計後に並行可能

解答の論理的説明

1. W3（アクセス権限設計）
   • 必要な前提：テーブルの構造（列名など）が確定していること → W1（テーブル設計）のみ
   • 実際に権限を付与するのは DDL 実行後 → W7（DDLなど実行）の前までに終えればよい
     → 直前の作業ID：W1／直後の作業ID：W7
2. W9（性能測定用データ設計・データ生成）
   • 必要な前提：テーブル構造が確定していること → W1（テーブル設計）のみ
   • 生成したデータをロードするのは W10（ロード実行） → W10 の前までに完了させる
     → 直前の作業ID：W1／直後の作業ID：W10

誤りやすいポイント

• 「並行可能＝作業順序を変えられる」ではなく、「前提条件のみを満たせば良い」ことを正しく理解する。
• W3 は「GRANT 文の設計」なので追加制約設計（W2）や索引設計（W4）は必須ではない点に注意。
• W9 はデータの「設計・生成」であり、実際のロード（W10）や性能測定（W11）とは区別される。

試験対策まとめ

• WBSの作業依存を問う問題では、「各作業がどの情報や成果物を前提にしているか」を整理することが肝要です。
• 「並行して進められる作業」は、必要最小限の前提だけを満たせばよく、他の作業とは独立に進められるものを選びます。
• 作業IDと作業内容をセットで覚え、実際に図や表を見ながら前提関係を手順立てて確認する練習をしましょう。

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

• 対象 DML：DML2，DML3
• 表探索になる理由：統計情報取得時にテーブルが空（0行）と判断されるため
• 作業順のポイント：W7（DDL 実行）の直後に W8（統計情報取得）が行われ，まだデータロード前のため統計情報上は空テーブル

2. 解答の論理的説明

1. RDBMS の仕様(5)に，「統計情報からテーブルが空 (0行) と判断した場合: 表探索に決められる」とある。

   「統計情報からテーブルが空 (0行) と判断した場合: 表探索に決められる。」

2. 作業工程表では，
   • W7：CREATE TABLE／ALTER TABLE／GRANT を実行
   • W8：統計情報取得及びアクセスパス確認
     の順に行うとされている。
3. W7 の時点では性能測定用データはまだロードされておらず，テーブルには行がない。
4. したがって，W8 で取得される統計情報上は「テーブルが空」と判断され，DML2・DML3 のアクセスパスは表探索（フルスキャン）に固定される。

対象 DML の一覧

• DML2, DML3 が表探索に決定
• DML1 は (施設ID, 鍵番号) が主キーに一致するユニーク索引探索となるため除外

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

• 「テーブルが空」の判定タイミング
  → テーブル作成直後に統計情報を取得すると，データロード前は必ず空と判断
• W8 を「データロード後」に行う想定で考えてしまう
• DML1 も WHERE 句に等値比較２列があり要件を満たすように思えるが，これはユニーク索引探索になる

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

• RDBMS は統計情報上「0行」とみなすと必ず表探索を選択する
• ユニーク索引の等値比較（AND だけ）は，統計情報にかかわらず常に索引探索
• 作業工程上の順序（特に「統計情報取得」のタイミング）がアクセスパスに影響を与える
• 問題文の「作業 W7 → W8 → データロード（W10）」の順序を正確に把握すること

1. キーワード・論点の整理

• G部長の指摘2：「作業W8（統計情報取得及びアクセスパス確認）は◯◯の後に行うべき」
• 統計情報取得前に「性能測定用データをテーブルにロード」しておく必要がある
• 「性能測定用データのロード」を行う作業IDは W10 である

2. 解答選択の論理展開

「作業W8（統計情報取得及びアクセスパス確認）の作業順が8番目では, 表3のDMLのアクセスパスが適切に決められない。作業W8を, 作業（あ）の後に行うべきである。」

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

• W9 と W10 の混同
  • W9は「性能測定用データ設計・データ生成」を行う作業です。
  • 実際にテーブルにデータを投入しないと統計情報（行数や分布）が取得できず，アクセスパス確認は正しく行えません。
• W7（DDLなど実行）直後に統計情報取得ができると考える誤り
  • DDL実行後はテーブルが空の状態なので，統計情報取得しても0行と判断され表探索になってしまいます。

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

• 統計情報取得（ANALYZE 等）は対象テーブルに必要データがロードされた後に行う
  → アクセスパスは統計情報に大きく依存する
• 工程表の前後関係を整理する
  → 「設計」「DDL実行」「データ生成」「ロード」「統計情報取得」「性能測定」の順序を理解
• 作業IDごとの役割をおさえる
  • W9：性能測定用データの設計と生成
  • W10：性能測定用データのロード
  • W8：統計情報取得及びアクセスパス確認
• DML性能チューニングの手順
  1. 実データ量想定
  2. データ生成・ロード
  3. 統計情報取得
  4. アクセスパス確認・索引設計
  5. 性能測定・再調整