平成30年度春期 データベーススペシャリスト 午後Ⅰ 問3

作業間の前提関係に注目する

物理DB設計に関わる作業を検討するときは、各作業が何を入力として必要とし、何を成果として生み出すかを一つずつ書き出す姿勢が役立ちます。前提となる情報がそろっているかという観点だけに絞って考えを整理しましょう。

この問題で問われていること

問題を読むときは、登場する作業や設計要素それぞれについて、判断に必要な情報がどの時点で確定しているのかを意識してください。ある判断が別の判断の結果に依存しているかどうかを、自分の言葉で説明できるかが確認の中心です。

視点

設計作業には、他の成果を受け取って初めて進められるものと、比較的独立して検討できるものがあります。どちらに当たるかは、その作業が扱う情報の出どころを確認すると整理しやすくなります。

確認ポイント

• 各作業が判断材料として何を必要としているかを書き出したか
• その判断材料が、別の作業の成果として得られるものかを確かめたか
• 依存関係の理由を、用語の暗記ではなく自分の言葉で説明できるか

解説

日帰り温泉施設の会員管理システム拡張を題材に、物理DB設計から実装までの作業を扱う問題です。UNIQUE制約と検査制約の設計、索引のユニーク性とクラスタ性、可変長列テーブルの容量見積り、読込みページ数の机上予測、作業工程の並行化、統計情報を取得する時期とアクセスパスの関係を問う。どの設問も、問題文にある列の意味、RDBMSの仕様、表の計算式、作業内容の依存関係を正確に読めば解答できる。

問題の全体像

A社は会員カードとシニア料金を導入するためシステムを拡張し、Fさんが物理DB設計及び実装を担当する。表2の作業工程表（W1〜W11）に沿って、制約設計（W2）、索引設計（W4）、表領域設計（W5）、DML性能予測（W6）の具体的な内容を問い、さらにG部長の指摘をもとに作業の並行化と統計情報を取得する時期を問う構成です。公式解答例の出題趣旨では、物理設計から実装までの作業の内容と、作業間の前後関係・依存関係を理解して適切な作業工程計画を作成する能力を問うとしている。採点講評では、全体として正答率は低かったとされている。

設問1(1)

公式解答例では、店舗は「施設ID，内線番号」、精算は「なし」です。表1によると、内線番号は各店舗の内線電話を「施設内で」識別する番号です。そのため、施設IDと内線番号の組合せで店舗が一意に決まり、これが主キー以外の候補キーになる。主キーの{施設ID、 店舗ID}はW1で主キー制約として定義済みなので、W2でUNIQUE制約として再定義することはない（採点講評では、主キーの列名をそのまま並べた誤答が多かったと指摘されている）。精算について採点講評は、客が非会員のときは複数台のいずれかの精算機で精算するため、主キー以外の列の組合せで行を一意にできないと説明している。会員IDはNULLになり得るし、同じ客が同じ日に同じ施設を複数回利用できるので、会員IDを含む組合せでも一意にならない。

設問1(2)(a)

公式解答例では「年齢 < 12」です。表1の年齢区分'1'は子供（12歳未満）です。したがって、年齢区分 = '1' と組み合わせる条件は、年齢が12未満であることになる。

設問1(2)(b)

公式解答例では「年齢区分 = '2'」である（cとは順不同）。図2の二つ目の括弧は、年齢区分'1'と'3'の間にあり、大人（'2'）の組合せを表す部分です。そのため、一方の述語は年齢区分が'2'であることを示す等値比較になる。

設問1(2)(c)

公式解答例では「年齢 BETWEEN 12 AND 59」である（bとは順不同）。大人は12歳以上60歳未満です。年齢列はSMALLINT型（整数）なので、12以上59以下を表すBETWEEN 12 AND 59で範囲を正しく限定できる。

設問1(2)(d)

公式解答例では「年齢 >= 60」です。シニア（'3'）は60歳以上なので、年齢区分 = '3' と組み合わせる条件は、年齢が60以上であることになる。

設問1(3)(1)

公式解答例では「非ユニーク索引」です。索引1は店舗利用テーブルの{施設ID、 鍵番号、 未精算フラグ}をキーとする。一人の客が同じ鍵で複数の商品を利用すれば、未精算フラグ'Y'の行が複数できる。また、表1にあるように鍵は精算後に再利用されるため、精算済み（'N'）の行も同じ施設ID・鍵番号で多数たまる。したがって、このキー値は一意にならない。

設問1(3)(2)

公式解答例では「同じ券番号の行が精算時にまとめて追加されるから」です。精算明細の行は、精算時にDML2のINSERT…SELECTによって、その券番号の利用分がまとめて挿入される。そのため、索引2のキー（利用年月日、 施設ID、 券番号）が同じ行は、物理的に隣接して同じページに格納されやすい。これは仕様(4)の高クラスタな索引の定義（隣接するキーが指す行が同じページに格納されている割合が高い）に当てはまる。35字以内の答案では、「同じ券番号の行」「精算時にまとめて追加」の2点を押さえる。

設問1(4)(ア)

公式解答例では「最大行長」です。手順2は、ページ長を決めるときに、どの行でも1ページに格納できるようにする手順です。可変長列があるテーブルでは、最も長い行（最大行長）が1行以上入るページ長を選ぶ必要がある。

設問1(4)(イ)

公式解答例では「有効ページ長」です。手順3の不等式は、ロード後に空けておく領域（有効ページ長 × 空き領域率 ÷ 100）に平均的な行が1行以上入る条件を表します。行を格納できるのはヘッダ部を除いた領域なので、有効ページ長を用いる。手順4のページ当たりの平均行数の式でも、割られる数（イ）は有効ページ長です。

設問1(4)(ウ)

公式解答例では「平均行長」です。表5の手順4の式は「ページ当たりの平均行数 = 有効ページ長 ÷ 平均行長（小数点以下を切捨て）」です。1ページの格納可能領域を行の平均長で割り、1ページに平均何行入るかを求める。

設問1(4)(エ)

公式解答例では「見積行数」です。必要ページ数は、テーブル全体の行数を1ページ当たりの行数で割って求める。そのため、割られる数は手順1で得た見積行数になる。

設問1(4)(オ)

公式解答例では「ページ当たりの平均行数」です。表5の手順4では、1行目で「ページ当たりの平均行数 = 有効ページ長 ÷ 平均行長」を求め、2行目の「必要ページ数 = 見積行数 ÷ オ（小数点以下を切上げ）」で、その値を割る数として使う。全行数を1ページに入る平均行数で割り、端数ページも1ページとして数えるために切り上げる、という流れです。空き領域率は手順3で決める値で、表5のこの計算式には含まれていない。式にない要素を持ち込まず、直前の行で求めた値をそのまま使う。

設問1(4)(カ)

公式解答例では「ページ長」である（キとは順不同）。ストレージに割り当てる容量はページ単位で決まるので、ヘッダ部を含むページ全体の大きさ（ページ長）に必要ページ数を掛ける。有効ページ長ではなくページ長を使う点に注意する。

設問1(4)(キ)

公式解答例では「必要ページ数」である（カとは順不同）。直前の行で求めた必要ページ数にページ長を掛けたものが、テーブルのストレージ所要量（バイト）になる。

設問1(5)(ク)

公式解答例では「必要ページ数」です。表探索ではテーブルの全ページを読むので、読込みページ数は表5で求めたテーブルの必要ページ数になる。採点講評によると、(5)は(4)より正答率が低かった。容量見積りだけでなく、机上でアクセスパスと性能を予測し、性能目標を決めてから測定することが重要だと述べられている。

設問1(5)(ケ)

公式解答例では「探索行数」です。低クラスタな索引では行へのアクセスがランダムになるため、最悪の場合は探索する1行ごとに別のページを読む。そのため、最大読込みページ数は探索行数と等しくなる。結果行数ではなく、ページから読み込む行数（探索行数）を使う点が重要です。

設問1(5)(コ)

公式解答例では「ページ当たりの平均行数」です。高クラスタな索引では、探索する行が連続したページに詰まって格納されている。そのため、最小読込みページ数は、探索行数を1ページに入る平均行数（表5で求めた値）で割って切り上げた値になる。

設問2(1)(W3)

公式解答例では、直前の作業IDがW1、直後の作業IDがW7です。W3（アクセス権限設計）でGRANT文を設計するには、W1で決まるテーブル名があればよく、制約・索引・表領域の設計（W2、W4〜W6）の結果は必要ない。一方、設計したGRANT文はW7で実行されるので、W7より前に終わっている必要がある。したがってW1の後、W7の前に置けば、W2、W4、W5、W6の四つの作業と並行して進められる。

設問2(1)(W9)

公式解答例では、直前の作業IDがW1、直後の作業IDがW10です。採点講評によると、W9の直前の作業IDは正答率が低かった。W1でCREATE TABLE文を設計すれば、性能測定用データのレイアウト設計に必要な列のデータ型と定義順が分かる。そのため、W9はW1の直後から始められる。生成したデータはW10でロードするので、直後の作業はW10になる。

設問2(2)(DML)

公式解答例では「DML2，DML3」です。仕様(5)により、ANDだけで結ばれた等値比較の対象列がユニーク索引のキー列に一致する場合は、統計情報にかかわらず索引探索になる。DML1は鍵テーブルの主キー{施設ID、 鍵番号}の等値比較なので、主キーのユニーク索引による索引探索に決まる。DML2（店舗利用、索引1は非ユニーク）とDML3（精算明細、索引2のキーは主キーの一部にすぎず非ユニーク）はこの例外に当たらない。そのため、アクセスパスは統計情報に基づいて決まる。

設問2(2)(理由)

公式解答例では「作業W7直後のテーブルは空だと判断されて表探索に決められるから」です。W8を8番目に行うと、W7でテーブルを作成した直後、データをロードする（W10）前に統計情報を取得することになる。仕様(5)により、統計情報からテーブルが空（0行）と判断されると表探索に決まる。採点講評でも、仕様(5)を読めば理由を導けるとされている。40字以内の答案では、「W7直後（ロード前）に統計情報を取得」「テーブルが空と判断」「表探索に決まる」の要素を含める。

設問2(3)

公式解答例では「W10」です。統計情報は、実際のデータ量を反映した状態で取得しなければ、適切なアクセスパスが選ばれない。したがって、W10（ロード実行）で性能測定用データをロードした後にW8を行い、アクセスパスを確認してからW11（DML性能測定）に進むのが適切です。

学習のポイント

主キーとUNIQUE制約の違い、索引のクラスタ性と読込みページ数の関係、容量見積りの計算式の流れ、統計情報を取得する時期とアクセスパスの関係、作業の成果物から考える工程の依存関係を押さえる。