2024年 秋期 データベーススペシャリスト試験 問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に示す。

2. "従業員"テーブルの行更新における更新履歴処理

"従業員"テーブルの組織コード、役職コードを更新するとき、当該従業員の更新前の行を更新の履歴として"従業員履歴"テーブルに挿入する。

3. "組織"テーブルの行削除処理

E社では、組織の改廃がある。PJ管理に不要になった組織コードを削除する場合、次のような手順で行う。

① 廃止済みの組織であり、かつ、PJが終了済みなどPJ管理に不要と判断できる組織コードを、SELECT文を用いて調べる。

② ①で調べた組織コードの行を、"組織"テーブルからDELETE文を用いて削除する。

[テーブルの定義と実装]

1. テーブルの定義

(1) Fさんは、図1中の各テーブルを定義するCREATE TABLE文を設計した。ここで、各CREATE TABLE文には外部キー制約を実装することとした。そのうち、"組織"テーブルを定義するCREATE TABLE文を、図2に示す。

CREATE TABLE 組織 (
  組織コード          CHAR(8)      NOT NULL PRIMARY KEY,
  組織名              VARCHAR(50)  NOT NULL,
  発足年月日          DATE         NOT NULL,
  廃止年月日          DATE,
  組織長従業員コード  CHAR(8),
  上位組織コード      CHAR(8),
  FOREIGN KEY (組織長従業員コード) REFERENCES 従業員 (従業員コード) ON DELETE RESTRICT,
  FOREIGN KEY (上位組織コード)      REFERENCES 組織 (組織コード)      ON DELETE RESTRICT
)
  

(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に示す。

[問合せの性能改善]

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を実装することにした。