令和5年度 春期 応用情報技術者試験 午後問題 問6 KPI集計システムのE-R図とSQLの結合方式
テクノロジデータベース
この問題は2023(R5)春 応用情報技術者 午後に出題されたものです。出題時点の法令・制度に基づく内容のため、現行の内容と一致しない場合があります。
本ページの問題文・選択肢は、原本の体裁を Web 表示用に正規化しています（改行・記号・数式・図表参照の調整）。設問の趣旨および正解に影響する変更は加えていません。
学習ガイド
働き方改革のKPIを集計するシステムを、E-R図とSQLから設計する問題です。目標データと実績データが作られる条件をデータフローに重ね、内部結合と左外部結合を使い分ける理由や、実績がない従業員にNULLが生じる仕組みを解説します。本文・表・図の根拠を行き来し、用語だけを暗記するのではなく、短い記述にも判断の理由を残して解答する手順を示します。
この記事で押さえる論点
- 組織・所属・従業員のE-R図に追加した3エンティティのリレーションと主キーを読み取る
- 評価対象外の従業員を除外する条件をINNER JOINとLEFT OUTER JOINで使い分ける理由を説明できる
- BETWEENによる期間抽出とGROUP BYによる従業員・KPI別集計を組み立てる
- 左外部結合で右側の表がNULLになる条件をデータの発生タイミングから特定する
出題情報
- 出題
- 2023(R5)春 応用情報技術者 午後 問6
- 配点
- 20点満点
- 模範解答
- 公表(設問ごとに掲載)
出題趣旨・採点講評(IPA 公表)
正規化された表を扱う処理では，複数のシンプルな処理への機能分割や，機能分割した処理結果の統合，再利用によって，構築工数の削減を図れることがある。 本問では，KPI達成状況集計システムの開発を題材に，集合関数，表結合などによる関係データベースのデータ操作に関する知識，技能を問う。
問6では，KPI達成状況集計システム機能の開発を題材に，組織，人事管理の表に，KPI管理のための表が追加された関係データベースにおいて，集計リストを作成するための一連のSQL文について出題した。全体として正答率は平均的であった。
問題本文
問6 KPI達成状況集計システムの開発に関する次の記述を読んで，設問に答えよ。
G社は，創立20年を迎えた従業員500人規模のソフトウェア開発会社である。G社では，顧客企業や業種業界の変化に応じた組織変更を行ってきた。また，スキルや業務知識に応じた柔軟な人事異動によって，人材の流動性を高めてきた。
G社の組織は，表1の例に示すように最大三つの階層から構成されている。
従業員の職務区分には管理職，一般職の二つがあり，1階層から3階層のそれぞれの組織には1名以上の従業員が所属している。なお，複数階層，複数組織の兼務は行わない規定であり，従業員は一つの組織だけに所属する。
図の説明テキスト
表1 G社の組織の例
| 1階層 | 2階層 | 3階層 | 組織の説明 |
|---|---|---|---|
| 監査室 | － | － | 単独階層の組織 |
| 総務部 | 人事課 | － | 全社共通のスタッフ組織 |
| 技術開発部 | オープンソース推進課 | － | 全社共通の開発組織 |
| 金融システム本部 | 証券システム部 | 証券開発課 | 業種業界ごとの開発組織 |
〔KPIの追加〕
G社では，仕事にメリハリを付け，仕事の質を向上させることが，G社の業績向上につながるものと考え，従来のKPIに加え，働き方改革，従業員満足度向上に関するKPIの項目を今年度から追加することにした。追加したKPIの項目を表2に示す。
図の説明テキスト
表2 追加したKPIの項目
| 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図（抜粋）】
エンティティと属性一覧（実線下線は主キー、破線下線は外部キー）：
- 組織：組織コード(主)、組織開始年月日(主)、組織終了年月日、組織名、上位組織コード(外)、上位組織開始年月日(外)
- 所属：従業員コード(主,外)、所属開始年月日(主)、所属終了年月日、所属組織コード(外)、所属組織開始年月日(外)、役職コード(外)
- 役職：役職コード(主)、役職名、職務区分
- 従業員：b(主)、氏名、生年月日、メールアドレス、住所
- KPI：KPIコード(主)、年度、KPI項目名、定量的成果目標
- 月別個人目標：従業員コード(主,外)、KPIコード(主,外)、年月(主)、月別目標値
- 日別個人実績：従業員コード(主,外)、KPIコード(主,外)、年月日(主)、日別実績値
リレーション（矢印の先が「多」）：
- 組織から所属（1対多）。この関係線上に a が配置されている。
- 組織から組織自身（上位組織との関連、1対多）。
- 役職から所属（1対多）。
- 従業員から所属（1対多）。
- 従業員から月別個人目標（1対多）。
- 従業員から日別個人実績（1対多）。
- KPIから月別個人目標（1対多）。
- KPIから日別個人実績（1対多）。
注記：属性名の実線の下線は主キー、破線の下線は外部キーを示す。主キーの実線が付いている属性名には、外部キーの破線を付けない。
凡例：線分で「1対1」、片側矢印で「1対多」、両側矢印で「多対多」を示す。
追加した三つのエンティティを基に新規に作成された表の管理内容と運用方法を表3に示す。
図の説明テキスト
表3 表の管理内容と運用方法
| 表 | 管理内容 | 運用方法 |
|---|---|---|
| KPI | KPI項目と定量的成果目標を管理する。 | ・参照だけ（更新は行わない）。 |
| 月別個人目標 | 個人ごとの月別目標値を管理する。 | ・年度開始時点で在籍している全従業員に対して，当該年度分のレコードを，目標値を0として初期作成する。 ・初期作成したレコードに対して，各人で定量的成果目標を意識した月別目標値を入力し，定期的に見直し，更新する。 ・年度途中入社の従業員については，初期作成レコードが存在しない。月別目標値の入力も行わない。 ・管理職従業員はKPI評価対象外であるが，月別目標値の入力は一般職従業員と同様に行う。 |
| 日別個人実績 | 個人ごとの日別実績値を管理する。 | ・勤怠管理システム，研修管理システムで管理している追加したKPI項目に関する全従業員の実績値を基に，日次バッチ処理によってレコードを作成する。 ・日別実績のない従業員のレコードは作成しない。 |
組織，所属，従業員，及び役職の各表は，以前から運用しているシステムから継承したものである。組織表と所属表では，組織や所属に関する開始年月日と終了年月日を保持し，現在を含む，過去から未来に至るまでの情報を管理している。
組織表の“組織終了年月日”と所属表の“所属終了年月日”には，過去の実績値，又は予定を設定する。終了予定のない場合は9999年12月31日を設定する。
なお，組織表の“上位組織コード”，“上位組織開始年月日”には，1階層組織ではNULLを，2階層組織と3階層組織では一つ上位階層の組織の組織コード，組織開始年月日を設定する。また，役職表の“職務区分”の値は，管理職の場合に'01'，一般職の場合に'02'とする。
〔達成状況集計リストの作成〕
H主任は，各組織がKPI達成状況を評価するための情報として，毎月末に達成状況集計リスト（以下，集計リストという）を作成し，提示することにした。
集計リスト作成は，オンライン停止時間帯の日次バッチ処理終了後の月次バッチ処理によって，処理結果を一時表に出力して後続処理に連携する方式で行うことにした。
集計リスト作成処理の概要を表4に示す。
図の説明テキスト
表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 集計リスト作成処理
| 項番 | SQL文 |
|---|---|
| 1 | INSERT INTO 従業員_所属_一時(従業員コード, 組織コード) SELECT A.従業員コード, A.所属組織コード FROM 所属 A, 役職 B WHERE TO_DATE(:集計年月日) e A.所属開始年月日 AND A.所属終了年月日 AND A.役職コード = B.役職コード AND f |
| 2 | INSERT INTO 従業員ごと_目標集計_一時(従業員コード, KPIコード, 目標個人集計) SELECT 従業員コード, KPIコード, SUM(月別目標値) FROM 月別個人目標 WHERE 年月 e :年度開始年月 AND :集計年月 g |
| 3 | INSERT INTO 従業員ごと_実績集計_一時(従業員コード, KPIコード, 実績個人集計) SELECT 従業員コード, KPIコード, SUM(日別実績値) FROM 日別個人実績 WHERE 年月日 e TO_DATE(:年度開始年月日) AND TO_DATE(:集計年月日) g |
| 4 | INSERT INTO h (組織コード, KPIコード, 目標組織集計, 実績組織集計, 対象従業員数) SELECT A.組織コード, B.KPIコード, SUM(B.目標個人集計), SUM(COALESCE(C.実績個人集計, 0)), i FROM 従業員_所属_一時 A c 従業員ごと_目標集計_一時 B ON A.従業員コード = B.従業員コード d 従業員ごと_実績集計_一時 C ON B.従業員コード = C.従業員コード AND B.KPIコード = C.KPIコード GROUP BY A.組織コード, B.KPIコード |
| 5 | SELECT A.*, A.目標組織集計/A.対象従業員数, A.実績組織集計/A.対象従業員数 FROM h A ORDER BY A.組織コード, A.KPIコード |
設問と解答・解説
設問1
(1)
図1中の a に入れる適切なエンティティ間の関連を答えよ。
模範解答
→
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「→」と正しく解答している。
- 1点: 該当なし。
- 0点: 無解答、または誤った記号を解答している。
解説
E-R図におけるエンティティ間の関連を補完する設問です。
従業員エンティティとKPI目標エンティティの関係を考えると、1人の従業員に対して複数のKPI目標が存在するため、両者は「1対多」の関連となります。
図1の凡例に従い、「1対多」を表す適切な記号は → となります。
高得点のポイント
- エンティティ間の多重度（1対多）を正しく分析・理解できること。
- 問題文および図の凡例に従い、正確な記号を用いて解答していること。
(2)
図1中の b に入れる適切な属性名を答えよ。
模範解答
従業員コード
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「従業員コード」と正しく解答している。
- 1点: 「従業員」など、意味は近いが属性名として不正確な解答。
- 0点: 無解答、または全く異なる属性名を解答している。
解説
E-R図におけるエンティティの属性名を補完する設問です。
KPI目標エンティティは、それが「どの従業員の目標であるか」を識別できなければなりません。
そのため、従業員エンティティの主キーである 従業員コード を外部キーとして保持することが適切です。
高得点のポイント
- リレーショナルデータベースにおける主キーと外部キーの関係を理解していること。
- 関連するエンティティから適切な属性名を正確に導出できていること。
設問2
〔達成状況集計リストの作成〕について答えよ。
(1)
本文及び図2中の c に入れる適切な字句を答えよ。
模範解答
INNER JOIN
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「INNER JOIN」と正しく解答している。
- 1点: 「JOIN」など、部分的に正答の要素を含む解答。
- 0点: 無解答、または全く異なる字句を解答している。
解説
表の結合処理において、両方の表に結合条件を満たすレコードが存在する場合にのみ抽出する結合方式は 内部結合 です。
SQL文の構文において、内部結合を指定する適切な字句は INNER JOIN となります。
高得点のポイント
- 内部結合の概念を理解し、SQLの構文として正しいキーワードを記述できていること。
(2)
本文及び図2中の d に入れる適切な字句を答えよ。
模範解答
LEFT OUTER JOIN
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「LEFT OUTER JOIN」と正しく解答している。
- 1点: 「OUTER JOIN」など、部分的に正答の要素を含む解答。
- 0点: 無解答、または誤った字句を解答している。
解説
結合処理において、左側の表（基準となる表）の全レコードを保持しつつ、右側の表のレコードを結合する方式は 左外部結合 です。
SQL文の構文において、左外部結合を指定する適切な字句は LEFT OUTER JOIN となります。
右側の表に対応するレコードがない場合、その項目の値は NULL となります。
高得点のポイント
- 左外部結合の概念を理解し、SQLの構文として正しいキーワードを記述できていること。
(3)
本文及び図2中の e に入れる適切な字句を答えよ。
模範解答
BETWEEN
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「BETWEEN」と正しく解答している。
- 1点: 該当なし。
- 0点: 無解答、または誤った字句を解答している。
解説
SQL文において、特定の日付や数値が指定した範囲内にあるかどうかを判定する条件式には BETWEEN 句を使用します。
通常は BETWEEN A AND B の形式で記述され、値がA以上かつB以下であることを示します。
高得点のポイント
- 範囲指定の条件式に関するSQL構文を正しく理解し、キーワードを記述できていること。
(4)
本文及び図2中の f に入れる適切な字句を答えよ。
模範解答
B.職務区分 = '02'
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「B.職務区分 = '02'」と正しく解答している。
- 1点: 「職務区分 = '02'」など、テーブル別名が欠けているが条件の意図は合致している解答。
- 0点: 無解答、または誤った条件式を解答している。
解説
WHERE句の抽出条件を補完する設問です。
処理の目的は「営業部員」のデータを抽出することです。従業員表（別名 B）において、営業部員であることを示す属性とその値を指定する必要があります。
したがって、別名を正しく付与した B.職務区分 = '02' が適切な条件式となります。
高得点のポイント
- 抽出目的を正しく読み解き、適切な列と条件値を指定できていること。
- テーブルの別名（エイリアス）を適切に付与できていること。
(5)
本文及び図2中の g に入れる適切な字句を答えよ。
模範解答
GROUP BY 従業員コード, KPIコード
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「GROUP BY 従業員コード, KPIコード」と正しく解答している。
- 1点: 「GROUP BY 従業員コード」など、片方の列のみ指定している不完全な解答。
- 0点: 無解答、または誤った句を解答している。
解説
集計関数（SUMやCOUNTなど）を適用する際のグループ化の単位を指定する設問です。
本処理では「従業員ごと」かつ「KPIコードごと」に集計を行っているため、この2つの列をグループ化のキーとして指定する必要があります。
したがって、GROUP BY 従業員コード, KPIコード が正答となります。
高得点のポイント
- GROUP BY句の役割と構文を正しく理解していること。
- 集計の単位となる複数の列を過不足なく指定できていること。
(6)
本文及び図2中の h に入れる適切な字句を答えよ。
模範解答
組織ごと_目標実績集計_一時
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「組織ごと_目標実績集計_一時」と正しく解答している。
- 1点: 該当なし。
- 0点: 無解答、または異なる表名を解答している。
解説
集計リスト作成処理の流れを読み解き、適切な参照先テーブル名を指定する設問です。
この処理ステップでは、直前の処理で作成・格納された一時的な集計結果を利用します。
文脈から、直前で作成された 組織ごと_目標実績集計_一時 が適切なテーブル名となります。
高得点のポイント
- 一連のSQL処理の流れをトレースし、一時表の入出力を正確に把握できていること。
(7)
本文及び図2中の i に入れる適切な字句を答えよ。
模範解答
COUNT(*)
採点基準(配点 2点)
正確性（内容）(2点)
- 2点: 「COUNT(*)」と正しく解答している。
- 1点: 「COUNT(列名)」など、部分的に合っているが完全ではない解答。
- 0点: 無解答、または誤った集計関数を解答している。
解説
レコードの件数を取得するために使用する集計関数を問う設問です。
SQLにおいて、条件に合致する行の総数をカウントする場合は COUNT 関数を使用します。
すべての列を含む行全体の件数を取得するため、COUNT(*) が正答となります。
高得点のポイント
- 集合関数に関する知識を有し、件数取得に適切な関数を選択できていること。
(8)
本文中の下線①に示す事態は，年度開始年月日から集計年月日までの間に，どのデータがどのような場合に発生するか。40字以内で答えよ。
模範解答
該当従業員のKPI項目に対する実績データが，1件も存在しない場合
採点基準(配点 2点)
知識・理解度（内容）(1点)
- 1点: 実績データが1件も存在しないという事実（NULLになる条件）を正しく指摘できている。
- 0点: 実績データの有無に言及していない、または誤った条件を記載している。
論理性（構造）(1点)
- 1点: 「該当従業員のKPI項目に対する」という対象データが明確に示され、事態の発生条件が論理的に記述されている。
- 0点: 主語や対象データが不明確で、文章としての論理構造が破綻している。
解説
LEFT OUTER JOIN（左外部結合） の仕様と、結合結果にNULLが発生する条件についての理解を問う設問です。
- 左外部結合の特性
左側の表（基準となる表）の全レコードは必ず出力されます。しかし、右側の表に結合条件（ON句）を満たすレコードが存在しない場合、右側の表から取得する項目の値はすべて NULL となります。 - 本システムにおける事象
KPI目標（左側）に対してKPI実績（右側）を結合する際、値がNULLになるということは、「該当従業員の対象KPI項目について、指定期間内に実績データ（活動記録など）が1件も登録されていない」ことを意味します。
高得点のポイント
- 知識・理解度（内容）: 実績データが1件も存在しない状況が、外部結合時にNULLを発生させる要因であることを正しく説明できていること。
- 論理性（構造）: 「該当従業員の」「KPI項目に対する」「実績データが1件も存在しない」という必須要素を含め、40字以内で論理的に記述できていること。
設問2(1)のfは，正答率がやや低かった。WHERE句の条件式をSQL文の処理内容などから読み解き，注意深く解答してほしい。設問2(2)は，正答率が低かった。この設問で問うている知識は，LEFT OUTER JOIN（左外部結合）において，右側の表に条件に合致するレコードがない場合，右側の表の項目値がNULLになるという仕様であり，是非知っておいてもらいたい。どのデータがどのような場合に発生するかを問うており，LEFT OUTER JOINで結合する表間のデータについて，保持する値がどのような状態になるかを注意深く考えて，正答を導き出してほしい。