令和6年度 秋期 データベーススペシャリスト試験 午後I 問題 問3 データベース物理設計と性能見積り

解答の根拠

• 設問の「テーブルの定義」における空欄 a では、参照制約（外部キー）を構成する列名を答えます。
• “組織”テーブルの属性から、従業員テーブルを参照する外部キーとなるものを特定します。
• 組織の長を表す 組織長従業員コード は、“従業員”テーブルの従業員コードを参照するため適切です。

高得点のポイント

• 外部キー制約の対象となる列名を正確に抜き出していること。

解答の根拠

• “時間単価”テーブルの定義における外部キーの指定です。
• 主キーは「組織コード」「ランク」「適用開始年月日」で構成されています。
• 外部の“ランク”テーブルを参照する外部キーとしては ランク が適切です。

高得点のポイント

• 関連するテーブル構造を理解し、正しい列名を解答すること。

解答の根拠

• 外部キー制約に関する文章中の空欄 c です。
• 親テーブルのレコードが削除・更新された際の子テーブルへの影響を定義する制約の文脈において、対象となる子テーブル名が入ります。
• 文脈上、時間単価 テーブルが該当します。

高得点のポイント

• テーブル間の参照関係を理解し、該当するテーブル名を正確に解答すること。

解答の根拠

• 空欄 d は、外部キーが参照する親テーブルを指しています。
• “組織”テーブルの「組織長従業員コード」は、従業員 テーブルを参照しているため、これが正解となります。

高得点のポイント

• 外部キーがどのテーブルの主キーを参照しているか正確に把握すること。

解答の根拠

• 既存のテーブルに対して、後から外部キー制約などの定義を追加するSQL文の構文です。
• 構造を変更するためのDDLである ALTER TABLE が入ります。

高得点のポイント

• SQLのDDL文法を正確に理解していること。

解答の根拠

• “組織”テーブルの組織長従業員コードは“従業員”テーブルを参照しています。
• 挿入時に参照先のレコードがまだ存在しない場合、制約違反でエラーとなる可能性があります。
• これを回避するためには、挿入時にいったん NULL を設定しておく必要があります。

高得点のポイント

• NULLを設定する という具体的な回避策が明記されていること。
• 制約違反を回避するための手順として論理的に正しく記述されていること。

解答の根拠

• “組織”テーブルの「上位組織コード」は、同テーブルの組織コードに対する自己参照の外部キーです。
• 参照先となる上位組織が先に登録されていないと制約違反になるため、最上位組織から上位順 に登録していく必要があります。

高得点のポイント

• 上位組織から順に登録する という登録順序のロジックが明記されていること。
• 自己参照における外部キー制約の挙動を正しく説明していること。

解答の根拠

• ON DELETE SET NULL は、親テーブルの行が削除された際に子テーブルの外部キー列を NULL にする制約です。
• しかし、“時間単価”テーブルの組織コードは 主キーの一部 であり、非NULL制約がかかっています。
• したがって、主キーに対して NULL を設定しようとするため制約違反となります。

高得点のポイント

• 組織コードが 主キーの一部 であることを指摘していること。
• 主キーであるため NULLに変更できない という因果関係が論理的に説明されていること。

解答の根拠

• ON DELETE RESTRICT（または指定なしのデフォルト）は、子テーブルに関連する行が存在する場合に親テーブルの行の削除を禁止します。
• “時間単価”テーブルには、1つの組織コードに対してランク等で区別される 複数の行が存在 する可能性があります。
• そのため、RESTRICTを指定すると、関連する子行が存在する限り親である組織を削除できなくなり、エラーで失敗するおそれがあります。

高得点のポイント

• 同じ組織コードの行が存在する 可能性があることを指摘していること。
• それによって削除が制限されるという理由が論理的に説明されていること。

解答の根拠

• 設問のSQLにおける空欄 ア は、ウィンドウ関数の PARTITION BY 句に指定する列です。
• 問題文の条件から、集計の単位が組織ごとであることが読み取れます。
• したがって、グループ化の基準として 組織コード を指定します。

高得点のポイント

• 適切な集計キーである列名を正確に抜き出していること。

解答の根拠

• 空欄 イ では、実績がない場合に NULL ではなく 0 を表示するための式を答えます。
• SUM(計画時間) が NULL になった場合を考慮し、COALESCE(SUM(計画時間), 0) のように COALESCE 関数を用いてデフォルト値を設定します。

高得点のポイント

• SUM による集計処理が正しく記述されていること。
• COALESCE などの関数を用いて、NULL を 0 に変換する処理が正しく組み込まれていること。

解答の根拠

• 空欄 ウ は、FROM句またはJOIN句において結合対象となるテーブル名を指定します。
• 集計対象の従業員情報を取得するためには、従業員 テーブルを結合する必要があります。

高得点のポイント

• SQLの結合関係を読み解き、正しいテーブル名を答えること。

解答の根拠

• 空欄 エ も、結合対象となるテーブル名を指定する箇所です。
• 稼働実績と対比して稼働計画の時間を集計するためには、稼働計画 テーブルが必要となります。

高得点のポイント

• 取得すべきデータセットから逆算して、正しいテーブル名を答えること。

解答の根拠

• “稼働実績”テーブルに存在する従業員コードの異なり数が、“従業員”テーブル全体の行数より少ない理由を問われています。
• 稼働実績はプロジェクト（PJ）での活動記録です。
• 全従業員の中には、何らかの理由で PJに参加していない従業員 も含まれているため、稼働実績テーブルには現れません。

高得点のポイント

• PJに参加していない というシステム上の実態を正確に指摘していること。
• 行数の差異が生じる理由として論理的に妥当であること。

解答の根拠

• 性能見積りにおけるアクセスブロック数の算出です。
• 与えられた条件（インデックスの深さ、アクセス対象行数など）に基づいて、ルートおよび中間ブロック、リーフブロック、データブロックのアクセス数を合算します。
• 設問の条件に沿って計算すると、アクセスブロック数は $22$ と導出されます。

解答の根拠

• 稼働年月日の列値個数（日数）に対して、1人当たりの稼働実績行数が多い理由を問われています。
• システムの仕様上、1人の従業員が同じ日に 複数PJ の稼働時間を入力できるため、1日につき複数行の実績データが生成される可能性があります。

高得点のポイント

• 同じ日に複数PJ の記録が行われるという運用・仕様が明確に記述されていること。
• 日数と行数が一致しない理由として論理的であること。

解答の根拠

• クラスタ性が高い索引と低い索引でのアクセス性能の違いを計算します。
• ブロックアクセス数の計算式に該当する数値（行数、ブロック内行数など）を当てはめます。
• 所与の条件に基づく計算により、アクセスブロック数は $24$ となります。

解答の根拠

• 特定の条件下でのI/O回数やブロックへのアクセス回数を求めます。
• バッファヒット率やクラスタ性の影響を考慮して計算を進めると、アクセス回数は $20$ 回と導出されます。

解答の根拠

• クラスタ性が低い場合、行へのアクセスがランダムになるため、データブロックへのアクセス数が増大します。
• アクセス対象となる行数とランダムアクセスの特性を計算式に適用すると、アクセスブロック数は $480$ となります。

解答の根拠

• 性能改善に関する指標の計算です。
• テーブルの全行数や特定の条件を満たす行数を見積もる過程で、問題文のパラメーターを使用します。
• 計算式に従って導出される行数は $2000$ となります。

解答の根拠

• ブロックサイズやページサイズに基づくデータ格納効率の計算です。
• 1ページあたりに格納可能な行数やアクセス対象ページ数を求める式に当てはめます。
• 条件に沿った計算結果は $50$ ページとなります。