データベーススペシャリスト試験 午後対策ポイント

データベーススペシャリスト試験 午後対策ポイント

午後1

データベース設計

主キー(候補キー)を見つける

  • 関係スキーマのうち主キーの下線が引かれてないものに対して、文書・画面・帳票の内容などから関数従属性を読み取り主キーを見つける。

外部キーを見つけリレーションシップを記入する

  • 主キーと同じ属性名は基本的に外部キー。
  • 正規化が完了なら非キー属性の重複は無い。

非キー属性を見つける

  • 外部キー属性の場合も非キー属性の場合もある。

第何正規形か、またその理由を答える

  • 部分関数従属性を見つける。非キー属性が主キーの一部に関数従属する物を見つける。{主キー(候補キー)}→{非キー属性}に、{主キー(候補キー)の一部}→{非キー属性}を含む。
  • 推移関数従属性を見つける。非キー属性が主キーに他の属性を経由して関数従属する物を見つける。{主キー(候補キー)}→{キー属性、非キー属性}→{非キー属性}。

テーブル構造の再設計

  • 最終的な関係スキーマは第3正規形(又はBCNF)が原則。同じ名前の非キー属性が複数のテーブルに存在しない事を確認する事が重要。

同時実行制御

ロックを用いる排他制御の観点から、

同時実行時の「スループットの低下(ロック待ち)」の理由を対策を答える

  • READ COMMITTED → 更新時は長期の専有ロック(READ UNCOMMITTED以外は同じ)。
  • 長期の専有ロック期間がループ処理などで長くなる→ロックの解放を早める(COMMITを早く)。

「デッドロック」の理由と対策を答える

  • デッドロック発生 → READ COMMITTED の場合は、更新時の長期ロックが関係。
  • 行単位に長期ロックを逆順に掛けるようとする → 行単位のロックの順番を揃える。

「2相ロック方式になっていない」ための不具合現象を答える

  • 2相ロック方式になっていない → 後で更新するデータを読み込んだ後、共有ロックを解放。
  • 他のトランザクションが同一のデータを更新 → 前に読み込んだデータとの不一致発生。
  • 2相ロック方式に変更 → READ COMMITTED を変更しない場合、カーソルの FOR UPDATE 句に。

データベース性能に関係する要素と午後問題

探索設計に関するまとめ

探索設計

  • 索引構成・・・B+ツリー索引(単一・複合列索引)(クラスタ性、ユニーク性)。またはビットマップ索引(種類の水区内データ向け)
  • 探索(アクセス)方法・・・表探索(非索引)/索引探索

探索コスト(物理入出力回数)計算

  • 非クラスタのコスト=全行数×絞り込み率(絞り込み行数/全行数)
  • クラスタのコスト=全ページ数×絞り込み率(絞り込みページ数/全ページ数)

例題

  • 索引使用せずにデータページ物理的な順番で探索 →全ページ数(=400,000ページ)
  • 主索引(ユニーク、非クラスタ)を使用してデータページを探索 →全行数(=10,000,000行)
  • 探索2(非ユニーク、クラスタ)を使用してデータページを探索 →全ページ数×絞り込み率(400,000ページ×0.05=20,000ページ)
  • 探索3(非ユニーク、非クラスタ)を使用してデータページを探索 →全行数×絞り込み率(10,000,000×0.01=100,000行)
  • 変更した主索引(ユニーク、クラスタ)を使用してデータページを探索 →全ページ数(=400,000ページ)。
  • 非クラスタ(ランダム)のコスト(物理入出力回数)=全行数×絞り込み率
  • クラスタ(連続)のコスト(物理入出力回数)=全ページ数×絞り込み率

SQL

  • CASE …WHEN … THEN … ELSE … END
  • WITH句 WITH SAMPLE AS (SELECT …)
  • WITH RECURSIVE句 再帰問合せ
  • COALESCE(コアレス)関数
  • NULLIF関数