KPI達成状況集計システムの開発に関する次の記述を読んで，設問に答えよ。
G社は，創立20年を迎えた従業員500人規模のソフトウェア開発会社である。G社では，顧客企業や業種業界の変化に応じた組織変更を行ってきた。また，スキルや業務知識に応じた柔軟な人事異動によって，人材の流動性を高めてきた。
G社の組織は，表1の例に示すように最大三つの階層から構成されている。
従業員の職務区分には管理職，一般職の二つがあり，1階層から3階層のそれぞれの組織には1名以上の従業員が所属している。なお，複数階層，複数組織の兼務は行わない規定であり，従業員は一つの組織だけに所属する。
| 1階層 | 2階層 | 3階層 | 組織の説明 |
| 監査室 | ー | ー | 単独階層の組織 |
| 総務部 | 人事課 | ー | 全社共通のスタッフ組織 |
| 技術開発部 | オープンソース推進課 | ー | 全社共通の開発組織 |
| 金融システム本部 | 証券システム部 | 証券開発課 | 業種業界ごとの開発組織 |
〔KPIの追加〕
G社では，仕事にメリハリを付け，仕事の質を向上させることが，G社の業績向上につながるものと考え，従来のKPIに加え，働き方改革，従業員満足度向上に関するKPIの項目を今年度から追加することにした。追加したKPIの項目を表2に示す。
| KPI項目名 | 定量的成果目標 | 評価方法 |
| 年間労働時間 | 1,980時間以内/人 |
・一般職従業員の個人実績を組織単位で集計し，平均値の達成状況を評価する。 ・年度途中入社，年度途中退職した従業員は，評価対象外とする。 ・個人実績の集計は，集計日時点で従業員の所属している直属の組織に対して行う。所属組織の上位階層，又は下位階層の組織の集計には含めない。 |
| 年次有給休暇取得日数 | 16日以上/人 | |
| 年間研修受講日数 | 6日以上/人 |
追加したKPIの達成状況を把握し，計画的な目標達成を補助するためにKPI達成状況集計システム（以下，Kシステムという）を開発することになり，H主任が担当となった。
Kシステムでは，次に示す仕組みと情報を提供する。
・従業員各人が，月ごとの目標を設定する仕組み
・日々の実績を月次で集計し，各組織がKPI達成状況を評価するための情報
G社では，組織変更と人事異動を管理するためのシステムを以前から運用している。H主任は，このシステムのためのE-R図を基に，KPIとその達成状況を把握するために，KPI，月別個人目標，及び日別個人実績の三つのエンティティを追加して，KシステムのためのE-R図を作成することにした。
作成したE-R図（抜粋）を図1に示す。Kシステムでは，このE-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによってデータを管理する。
図1 KシステムのためのE-R図（抜粋）
追加した三つのエンティティを基に新規に作成された表の管理内容と運用方法を表3に示す。
| 表 | 管理内容 | 運用方法 |
| ΚΡΙ | KPI項目と定量的成果目標を管理する。 |
参照だけ（更新は行わない）。 |
| 月別個人目標 | 個人ごとの月別目標値を管理する。 |
・年度開始時点で在籍している全従業員に対して，当該年度分のレコードを，目標値を0として初期作成する。 ・初期作成したレコードに対して，各人で定量的成果目標を意識した月別目標値を入力し，定期的に見直し，更新する。 ・年度途中入社の従業員については，初期作成レコードが存在しない。月別目標値の入力も行わない。 ・管理職従業員はKPI評価対象外であるが，月別目標値の入力は一般職従業員と同様に行う。 |
| 日別個人実績 | 個人ごとの日別実績値を管理する。 |
・勤怠管理システム，研修管理システムで管理している追加したKPI項目に関する全従業員の実績値を基に，日次バッチ処理によってレコードを作成する。 ・日別実績のない従業員のレコードは作成しない。 |
組織，所属，従業員，及び役職の各表は，以前から運用しているシステムから継承したものである。組織表と所属表では，組織や所属に関する開始年月日と終了年月日を保持し，現在を含む，過去から未来に至るまでの情報を管理している。
組織表の“組織終了年月日”と所属表の“所属終了年月日”には，過去の実績値，又は予定を設定する。終了予定のない場合は9999年12月31日を設定する。
なお，組織表の“上位組織コード"，"上位組織開始年月日”には，1階層組織ではNULLを2階層組織と3階層組織では一つ上位階層の組織の組織コード，組織開始年月日を設定する。また，役職表の“職務区分”の値は，管理職の場合に'01'，一般職の場合に'02'とする。
H主任は，各組織がKPI達成状況を評価するための情報として，毎月末に達成状況集計リスト（以下，集計リストという）を作成し，提示することにした。
集計リスト作成は，オンライン停止時間帯の日次バッチ処理終了後の月次バッチ処理によって，処理結果を一時表に出力して後続処理に連携する方式で行うことにした。
集計リスト作成処理の概要を表4に示す。
| 項番 | 入力表 | 出力表 | 集計日における処理内容 |
| 1 | 所属，役職 | 従業員_所属_一時 | 一般職従業員と所属組織の対応表を作成する。 |
| 2 | 月別個人目標 | 従業員ごと_目標集計_一時 | 年度開始年月から集計月までの従業員，KPI項目ごとの目標個人集計値を求める。 |
| 3 | 日別個人実績 | 従業員ごと_実績集計_一時 | 年度開始年月日から集計日までの従業員，KPI項目ごとの実績個人集計値を求める。 |
| 4 | 項番1〜3の出力表 | 組織ごと_目標実績集計_一時 | 組織，KPI項目ごとの目標集計値，実績集計値，従業員数を求める。 |
| 5 | 項番4の出力表 | ー | 組織，KPI項目ごとの目標集計値，実績集計値，従業員数，目標平均値，実績平均値を一覧化した集計リストを作成する。 |
集計リスト作成処理のSQL文を図2に示す。ここで，TO_DATE関数は，指定された年月日をDATE型に変換するユーザー定義関数である。関数COALESCE（A，B）は，AがNULLでないときはAを，AがNULLのときはBを返す。また，“:年度開始年月日”，“:年度開始年月”，“集計年月日”，“集計年月”は，該当の値を格納する埋込み変数である。
H主任は，図2の項番4のSQL文の設計の際に，次に示す考慮を行った。
・表2の評価方法に従い，管理職の従業員データは対象に含めず，年度途中入社と，年度途中退職の従業員データについては出力しないように，抽出日に退職している従業員データを出力しない“従業員所属一時表”と，年度開始時点で入社していない従業員データを出力しない“従業員ごと_目標集計_一時表”を c によって結合しておく。
・ c による結合結果と，実績がある場合だけレコードの存在する“従業員ごと実績集計一時表”を d によって結合しておく。また，①実績個人集計がNULLの際は，0を設定しておく。
図2 集計リスト作成処理
出典：令和5年度春期 問6
設問1 図1中の a ， b に入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例及び注記に倣うこと。
解答・解説
解答例
a：→ b：従業員コード
解説
設問2 〔達成状況集計リストの作成〕について答えよ。
（1）本文及び図2中の c 〜 i に入れる適切な字句を答えよ。
解答・解説
解答例
c：INNER JOIN
d：LEFT OUTER JOIN
e：BETWEEN
f：B.職務区分 = '02'
g：GROUP BY 従業員コード, KPIコード
h：組織ごと_目標実績集計_一時
i：count(*)
解説
（2）本文中の下線①に示す事態は，年度開始年月日から集計年月日までの間に，どのデータがどのような場合に発生するか。40字以内で答えよ。
解答・解説
解答例
該当従業員のKPI項目に対する実績データが，1件も存在しない場合
解説
IPA公開情報
出題趣旨
正規化された表を扱う処理では，複数のシンプルな処理への機能分割や，機能分割した処理結果の統合，再利用によって，構築工数の削減を図れることがある。
本問では，KPI 達成状況集計システムの開発を題材に，集合関数，表結合などによる関係データベースのデータ操作に関する知識，技能を問う。
採点講評
問 6 では，KPI 達成状況集計システム機能の開発を題材に，組織，人事管理の表に，KPI 管理のための表が追加された関係データベースにおいて，集計リストを作成するための一連の SQL 文について出題した。全体として正答率は平均的であった。
設問 2(1)の f は，正答率がやや低かった。WHERE 句の条件式を SQL 文の処理内容などから読み解き，注意深く解答してほしい。
設問 2(2)は，正答率が低かった。この設問で問うている知識は，LEFT OUTER JOIN（左外部結合）において，右側の表に条件に合致するレコードがない場合，右側の表の項目値が NULL になるという仕様であり，是非知っておいてもらいたい。どのデータがどのような場合に発生するかを問うており，LEFT OUTER JOIN で結合する表間のデータについて，保持する値がどのような状態になるかを注意深く考えて，正答を導き出してほしい。
