データベーススペシャリスト午後I 事例記述令和6年 秋期
午後I 問3
問3 プロジェクト稼働管理システムに関するデータベース物理設計・SQL設計・性能
テーマ：プロジェクト稼働管理システムのデータベース物理設計・SQL設計・性能評価
左右2分割表示：左側の問題文をスクロールしながら右側の設問に解答できます
長文事例本文
図表クリックで拡大問3 情報システム会社のプロジェクト稼働管理システムのデータベース物理設計・SQL設計・性能に関する次の記述を読んで，設問に答えよ。
情報システム会社のE社は，自社のプロジェクト稼働管理システム（以下，PJシステムという）を，RDBMSを用いて更改することになり，Fさんが実装を任された。
〔RDBMSの主な仕様〕
1. DMLのアクセス経路は，RDBMSによって索引探索又は表探索が選択される。
2. 索引は，クラスタ性という性質によって，高クラスタな索引と低クラスタな索引に分けられる。
・高クラスタな索引は，キー値の順番と，キーが指す行の物理的な並び順が一致しているか，完全に一致していなくても，隣接するキーが指す行が同じページに格納されている割合が高い。
・低クラスタな索引は，キー値の順番と，キーが指す行の物理的な並び順が一致している割合が低く，行へのアクセスがランダムになる。
〔業務の概要〕
1. 組織，従業員，役職，ランク，時間単価
(1) E社には，複数の組織がある。組織は階層構造であり，最上位の組織以外はいずれか一つの上位組織に属する。
(2) 従業員は，従業員コードで識別し，いずれか一つの組織に属する。
(3) 役職には，SE，シニアSE，マネージャなどがある。役職は役職コードで識別する。従業員はいずれか一つの役職をもつ。
(4) ランクは，労務費の時間単価を区別するもので，ランクコードで識別する。役職はいずれか一つのランクに対応する。
(5) 時間単価は，ランク別組織別年月日別に決めている。組織の変更，従業員の異動などによって，月初に時間単価を見直すことがある。
2. プロジェクト，稼働計画，稼働実績
(1) プロジェクト（以下，PJという）は，従業員の稼働状況を管理する単位である。従業員は複数のPJに参加することがあり，PJに参加していない従業員も一部いる。
(2) PJに必要な人員を要員という。
(3) PJ開始前に稼働計画を立案するとき，要員ごと参加年月ごとに計画時間を見積もる。参加する従業員が確定したとき，稼働計画の要員に対して従業員を割り当てる。PJ開始後，必要に応じて計画を修正する。
(4) PJに参加している各従業員は，稼働実績として月内の日別PJ別の稼働時間を入力する。従業員は同じ日に複数PJの稼働時間を入力できる。
〔PJシステムのテーブル〕
1. テーブル構造，列の意味・制約，統計情報・索引定義
主なテーブルのテーブル構造を図1に，主な列の意味・制約を表1に示す。また，“従業員”テーブルの主な統計情報・索引定義を表2に，“稼働実績”テーブルの主な統計情報・索引定義を表3に示す。
拡大
表1 主な列の意味・制約
| 列名 | 意味・制約 |
| :--- | :--- |
| 上位組織コード | 組織が属する上位組織の組織コード。最上位組織の上位組織コードにはNULLを設定する。 |
| 更新年月日 | “従業員”テーブルの列値を更新した日付 |
| PM従業員コード | PJを管理する従業員の従業員コード |
| 要員番号 | 稼働計画においてPJ内で要員を一意に識別する通し番号。要員に従業員が割り当てられていない場合，当該要員の従業員コードにはNULLを設定する。 |
| 計画時間 | PJごと要員ごと計画年ごと計画月ごとに見積もった作業計画。単位は時間。 |
| 稼働時間 | PJごと稼働年月日ごと従業員ごとの作業実績。単位は時間。 |
表2 “従業員”テーブルの主な統計情報・索引定義（一部省略）
| 上段：行数，下段：ページ当たり行数 | 列名 | 列値個数 | 主索引（列の定義順） | 副次索引1（列の定義順） |
| :---: | :--- | :---: | :---: | :---: |
| 8,800行
20行／ページ | 従業員コード | 8,800 | 1 | | | | 従業員氏名 | 8,750 | | | | | 組織コード | 400 | | 1 | | | ： | ： | | |
拡大
注記 網掛け部分は表示していない。
2. “従業員”テーブルの行更新における更新履歴処理
“従業員”テーブルの組織コード，役職コードを更新するとき，当該従業員の更新前の行を更新の履歴として“従業員履歴”テーブルに挿入する。
3. “組織”テーブルの行削除処理
E社では，組織の改廃がある。PJ管理に不要になった組織コードを削除する場合，次のような手順で行う。
① 廃止済みの組織であり，かつ，PJが終了済みなどPJ管理に不要と判断できる組織コードを，SELECT文を用いて調べる。
② ①で調べた組織コードの行を，“組織”テーブルからDELETE文を用いて削除する。
〔テーブルの定義と実装〕
1. テーブルの定義
(1) Fさんは，図1中の各テーブルを定義するCREATE TABLE文を設計した。ここで，各CREATE TABLE文には外部キー制約を実装することとした。そのうち，“組織”テーブルを定義するCREATE TABLE文を，図2に示す。
拡大
(2) Fさんは，他の表が未定義の状態で“組織”テーブルを定義する図2のCREATE TABLE文を実行したところ失敗した。そこで，Fさんは，図2のCREATE TABLE文を見直し，次の①〜③の順番で定義を実行したところ，全ての実行が成功した。
① “組織”テーブルを定義する図2中から，a を外部キーとする指定を削除したCREATE TABLE文を実行する。
② “従業員”，“役職”，“時間単価”，“ランク”の各テーブルを定義するCREATE TABLE文を“b”，“ c”，“役職”，“d”の順番で実行する。
③ “組織”テーブルに対する e 文を用いて，a を外部キーとする指定の定義を追加する。
2. テーブルへの行登録
次に，Fさんは，“組織”テーブルにINSERT文を用いて行を挿入した。次いで“従業員”，“役職”，“時間単価”，“ランク”の各テーブルに対してもINSERT文を用いて行を挿入した。その後，UPDATE文で適宜列値を更新した。
〔稼働計画の立案・稼働実績の確認〕
Fさんは，稼働計画の立案及び稼働実績の確認を支援するためのSQL文を設計した。設計したSQL文の例を，表4に示す。
拡大
注記 ホスト変数のhv1には組織コードを，hv2には開始年月日（例：2024-06-01）を，hv3には終了年月日（例：2024-06-30）を設定する。
〔問合せの性能改善〕
Fさんは，“稼働実績”テーブルへの問合せに利用される表4中のSQL2について，性能の改善を依頼された。Fさんが調べたところ，稼働実績を一括入力する従業員が多く，1か月単位で見たとき，行の登録順が従業員，稼働年月日，PJコード順であり，従業員当たり1か月分の行が高々2ページに格納されることが分かった。そこで，索引のクラスタ性と次の三つの前提を踏まえて，(1)〜(5)の手順で性能改善を試みた。
・それぞれの列値は均等に分布していると仮定する。
・PJに参加しない従業員だけで構成される組織はないと仮定する。
・全従業員が同じ曜日で働いていると仮定し，1か月は20日として計算する。
(1) SQL2のアクセス経路として，“従業員”テーブルを外表，“稼働実績”テーブルを内表とする入れ子ループ結合を想定する。
(2) このアクセス経路では，まず外表から指定した組織コードに対して，外表の副次索引1を用いて平均22行を読み込む。外表の副次索引1は低クラスタな索引なので，最大で f ページを読み込む。
(3) 外表から読み込んだ従業員コード1件ごとに，内表の副次索引1を用いて①従業員1人当たりの稼働実績である1,200行を読み込み，行データの稼働年月日に対してBETWEEN述語を評価する。表3の稼働年月日列の列値個数は1,000（50か月分）なので，内表の集計対象の行は，1人当たり g 行である。稼働実績を計上している従業員は組織当たり h 人なので，集計対象の行は組織当たり i 行となる。内表の副次索引1は高クラスタなので，読込みページ数は組織当たり最大 j ページである。
(4) 次に，読込みページ数を削減するために，｛従業員コード，稼働年月日｝をキーとする副次索引3を追加した場合の性能を検討した。この索引を使用した場合，副次索引1と比較すると，1か月分を索引で絞り込めるので，表からの読込み行数及び読込みページ数は k 分の1に削減される。
(5) 副次索引3の利用によって，表からの読込み行数及び読込みページ数を削減できるので，副次索引3を実装することにした。
拡大図1 主なテーブルのテーブル構造
20行／ページ | 従業員コード | 8,800 | 1 | | | | 従業員氏名 | 8,750 | | | | | 組織コード | 400 | | 1 | | | ： | ： | | |
拡大表3 “稼働実績”テーブルの主な統計情報・索引定義
拡大図2 “組織”テーブルを定義するCREATE TABLE文
拡大表4 稼働計画の立案及び稼働実績の確認を支援するSQL文の例
設問一覧 ＆ 解答・演習
まずは解答を作成してみましょう。「解答例を見る」をクリックするとIPA公式解答例と採点基準が開きます。
設問1 (1)記号・選択式
“1. テーブルの定義”について，本文中の a 〜 e に入れる適切な字句を答えよ。
※直接入力してください
設問1 (2)記述問題
制限字数：25 字以内
“2. テーブルへの行登録”において，“組織”テーブルへ行を挿入する場合，外部キーである組織長従業員コード及び上位組織コードのそれぞれについて，考慮すべき事項を一つずつ，25字以内で答えよ。
0 / 25 文字
設問1 (3)記述問題
制限字数：45 字以内
Fさんは，図1中のテーブルのうち，“時間単価”テーブルの定義では，外部キーである“組織コード”のDELETEオプションをCASCADEに指定した。SET NULL又はRESTRICTを指定した場合，“時間単価”テーブルの定義時又は“組織”テーブルの行削除時に制約違反で失敗するおそれがあると考えたからである。なぜ制約に違反するのか，理由をそれぞれ45字以内で具体的に答えよ。
0 / 45 文字
設問2記号・選択式
〔稼働計画の立案・稼働実績の確認〕について，表4中のSQL1の ア 〜 エ に入れる適切な字句を答えよ。
※直接入力してください
設問3 (1)記述問題
制限字数：30 字以内
統計情報について，表3の“稼働実績”テーブルの従業員コードの列値個数が表2の“従業員”テーブルの従業員コードの列値個数より少ないのはなぜか。本文中の用語を用いて，30字以内で答えよ。
0 / 30 文字
設問3 (2)記号・選択式
本文中の f に入れる適切な数値を答えよ。
※直接入力してください
設問3 (3)記述問題
制限字数：30 字以内
下線①について，稼働年月日列の列値個数が1,000であるにもかかわらず，従業員1人当たりの稼働実績の行数が1,000よりも多いのはなぜか。本文中の用語を用いて，30字以内で答えよ。
0 / 30 文字
設問3 (4)記号・選択式
本文中の g 〜 k に入れる適切な数値を答えよ。
※直接入力してください
午後I長文記述の得点力を引き上げる定番対策書Amazon・楽天 公式提携
PR
IPA 厳選対策書徹底攻略 データベーススペシャリスト教科書 令和8年度株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
DB徹底攻略株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
徹底攻略 データベーススペシャリスト教科書 令和8年度
💡 推薦ポイント:午後I・午後IIの概念データモデル穴埋めや排他制御・性能設計の記述問題で確実に合格点を勝ち取る実践テクニックが満載です。
¥3,278（税込）
※本リンク先はプロモーション（アフィリエイト広告）が含まれています
【出題趣旨】IPA公式発表
【採点講評】IPA公式発表
本問に関連するデータベーススペシャリスト重要用語解説
本番形式・制限時間付きで演習する
令和6年 秋期 午後I（3問中2問選択・90分）を実戦演習モードで通し解答し、総合採点を受けられます。