応用情報技術者過去問題令和5年春期午後問6(データベース)

応用情報技術者過去問題令和5年春期午後問6

問6　データベース

KPI達成状況集計システムの開発に関する次の記述を読んで，設問に答えよ。

　G社は，創立20年を迎えた従業員500人規模のソフトウェア開発会社である。G社では，顧客企業や業種業界の変化に応じた組織変更を行ってきた。また，スキルや業務知識に応じた柔軟な人事異動によって，人材の流動性を高めてきた。
　G社の組織は，表1の例に示すように最大三つの階層から構成されている。
　従業員の職務区分には管理職，一般職の二つがあり，1階層から3階層のそれぞれの組織には1名以上の従業員が所属している。なお，複数階層，複数組織の兼務は行わない規定であり，従業員は一つの組織だけに所属する。

〔KPIの追加〕
　G社では，仕事にメリハリを付け，仕事の質を向上させることが，G社の業績向上につながるものと考え，従来のKPIに加え，働き方改革，従業員満足度向上に関するKPIの項目を今年度から追加することにした。追加したKPIの項目を表2に示す。

　追加したKPIの達成状況を把握し，計画的な目標達成を補助するためにKPI達成状況集計システム(以下，Kシステムという)を開発することになり，H主任が担当となった。
　Kシステムでは，次に示す仕組みと情報を提供する。

従業員各人が，月ごとの目標を設定する仕組み
日々の実績を月次で集計し，各組織がKPI達成状況を評価するための情報

〔データベースの設計〕
　G社では，組織変更と人事異動を管理するためのシステムを以前から運用している。H主任は，このシステムのためのE-R図を基に，KPIとその達成状況を把握するために，KPI，月別個人目標，及び日別個人実績の三つのエンティティを追加して，KシステムのためのE-R図を作成することにした。
　作成したE-R図(抜粋)を図1に示す。Kシステムでは，このE-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによってデータを管理する。

　追加した三つのエンティティを基に新規に作成された表の管理内容と運用方法を表3に示す。

　組織，所属，従業員，及び役職の各表は，以前から運用しているシステムから継承したものである。組織表と所属表では，組織や所属に関する開始年月日と終了年月日を保持し，現在を含む，過去から未来に至るまでの情報を管理している。
　組織表の"組織終了年月日"と所属表の"所属終了年月日"には，過去の実績値，又は予定を設定する。終了予定のない場合は9999年12月31日を設定する。
　なお，組織表の"上位組織コード"，"上位組織開始年月日"には，1階層組織ではNULLを2階層組織と3階層組織では一つ上位階層の組織の組織コード，組織開始年月日を設定する。また，役職表の"職務区分"の値は，管理職の場合に'01'，一般職の場合に'02'とする。

〔達成状況集計リストの作成〕
　H主任は，各組織がKPI達成状況を評価するための情報として，毎月末に達成状況集計リスト(以下，集計リストという)を作成し，提示することにした。
　集計リスト作成は，オンライン停止時間帯の日次バッチ処理終了後の月次バッチ処理によって，処理結果を一時表に出力して後続処理に連携する方式で行うことにした。
　集計リスト作成処理の概要を表4に示す。

　集計リスト作成処理のSQL文を図2に示す。ここで，TO_DATE関数は，指定された年月日をDATE型に変換するユーザー定義関数である。関数COALESCE(A，B)は，AがNULLでないときはAを，AがNULLのときはBを返す。また，":年度開始年月日"，":年度開始年月"，":集計年月日"，":集計年月"は，該当の値を格納する埋込み変数である。

　H主任は，図2の項番4のSQL文の設計の際に，次に示す考慮を行った。

表2の評価方法に従い，管理職の従業員データは対象に含めず，年度途中入社と，年度途中退職の従業員データについては出力しないように，抽出日に退職している従業員データを出力しない"従業員_所属_一時表"と，年度開始時点で入社していない従業員データを出力しない"従業員ごと_目標集計_一時表"をcによって結合しておく。
cによる結合結果と，実績がある場合だけレコードの存在する"従業員ごと_実績集計_一時表"をdによって結合しておく。また，①実績個人集計がNULLの際は，0を設定しておく。

設問1

図1中のa，bに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例及び注記に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)

解答入力欄

a：
b：

解答例・解答の要点

a：→
b：従業員コード

解説

〔aについて〕
組織エンティティには"組織コード"と"組織開始年月日"、所属エンティティには"所属組織コード"と"所属組織開始年月日"が存在します。"組織コード"と"組織開始年月日"は、組織エンティティ側では主キーであり、組織を一意に特定します。また、所属エンティティ側では外部キーであり、ぞれぞれ組織エンティティの"組織コード"と"組織開始年月日"を参照する関係があります。

問題文中の「それぞれの組織には1名以上の従業員が所属している」より、ひとつの組織には複数の従業員が所属することが前提です。つまり、所属エンティティには"所属組織コード"と"所属組織開始年月日"の値の組が同一である複数のレコードが存在しえるということです。よって、組織エンティティと所属エンティティのカーディナリティは1対多です。したがって、空欄aには「→」が当てはまります。

∴a＝→

〔bについて〕
従業員表には主キーが欠けているので、空欄bには、単独で従業員を一意に特定できる主キー属性が必要です。従業員エンティティを1対多の関連を持つ2つの多側エンティティ（日別個人実績エンティティや月別個人目標エンティティ）には、従業員エンティティの主キーである空欄bを参照する属性が存在しなければなりません。この2つの多側エンティティに共通して存在し、かつ、従業員を特定できるような属性は明らかに"従業員コード"しかありません。したがって、空欄bには"従業員コード"に主キーを表す下線を引いた「従業員コード」が当てはまります。

∴b＝従業員コード

設問2

〔達成状況集計リストの作成〕について答えよ。

本文及び図2中のc～iに入れる適切な字句を答えよ。
本文中の下線①に示す事態は，年度開始年月日から集計年月日までの間に，どのデータがどのような場合に発生するか。40字以内で答えよ。

解答入力欄

- c：
- d：
- e：
- f：
- g：
- h：
- i：

解答例・解答の要点

c：INNER JOIN
d：LEFT OUTER JOIN
e：BETWEEN
f：B.職務区分 = '02'
g：GROUP BY 従業員コード，KPIコード
h：組織ごと_目標実績集計_一時
i：COUNT(*)

該当従業員のKPI項目に対する実績データが，1件も存在しない場合 (32文字)

解説

空欄の順序とは異なりますが、図2のSQL文の項番1～4の順序にしたがって解説していきます。

【項番1のSQL文】
項番1のSQL文は、集計年月日における一般職従業員と所属組織の対応表（従業員_所属_一時表）を作成するものです。"従業員_所属_一時表"については、本文中の説明で「抽出日に退職している従業員データを出力しない」とあるので、集計年月日時点で所属している一般職従業員だけを出力しなければなりません。

〔eについて〕
空欄eは、所属開始年月日と所属終了年月日を行選択の条件としているので、集計年月日時点で所属している従業員だけに絞るための式であることがわかります。値の範囲を条件とする場合、:集計年月日 >= 所属開始年月日 AND :集計年月日 <= 所属終了年月日と2つの条件式で記述することもできます（TO_DATEは割愛）が、図1のSQL文では、
TO_DATE(：集計年月日) e A.所属開始年月日 AND A.所属終了年月日
というひとつの条件式で記述されているので、値が「●以上〇以下」の行を取得するBETWEEN構文を使うことになります。
//T1 が T2 以上 T3 以下であれば真を返す
T1 BETWEEN T2 AND T3
したがって、空欄eには「BETWEEN」が当てはまります。

∴e＝BETWEEN

〔fについて〕
"従業員_所属_一時表"には一般職従業員だけを出力するので、一般職従業員だけに絞る条件式が必要となります。〔データベースの設計〕には「役職表の"職務区分"の値は，管理職の場合に'01'，一般職の場合に'02'とする」とあるので、役職テーブル（B）の職務区分列の値が"02"とWHERE句で指定すれば、一般職従業員のみを出力の対象とすることができます。したがって、空欄fには「B.職務区分 = '02'」が当てはまります。

本問で与えられた情報だけで考えれば「B.職務区分 <> '01'」という条件式も適切と言えますが、実際には'01'と'02'以外の職務区分（例えば'00'：役員など）が存在する可能性もあるため、厳密には要件を満たしません。

∴f＝B.職務区分 = '02'

【項番2のSQL文】
〔gについて〕
"従業員ごと_目標集計_一時表"は、年度開始年月から集計月までの従業員、KPI項目ごとの目標個人集計値を求めるものです。年度開始年月から集計月という条件については、[e：BETWEEN] ：年度開始年別 AND ：集計年月の部分に記述済なので、空欄gには、従業員、KPI項目ごとの集計を実現する文が当てはまります。グループごとに集計するときたらGROUP BY句です。また、SELECT文にSUM()が使われているので、GROUP BY句が必要だと当たりを付けることもできます。

従業員、KPI項目ごとにグループ化したいので、月別個人目標表の属性名である（そしてSELECT文で指定されている）従業員コードとKPIコードでグループ化することになります。したがって、空欄gには「GROUP BY 従業員コード, KPIコード」が当てはまります。

∴g＝GROUP BY 従業員コード，KPIコード

【項番4のSQL文】
〔hについて〕
INSERT文は、以下の書式で指定の表に行を挿入する文です。
INSERT INTO 表名 (列A, 列B, 列C) VALUES (値A, 値B, 値C)
INSERT INTO 表名 (列A, 列B, 列C) SELECT …
空欄hの位置には挿入先となる表の名前が入ります。表4を見ると、項番4のSQL文の出力表は"組織ごと_目標実績集計_一時"とありますから、空欄hには「組織ごと_目標実績集計_一時」が当てはまると判断できます。

∴h＝組織ごと_目標実績集計_一時

〔iについて〕
空欄iは、"組織ごと_実績集計_一時表"の対象従業員列に相当し、組織、KPIごとの従業員数を表示する文が入ります。後述するように、"従業員_所属_一時表"、"従業員ごと_目標集計_一時表"、"従業員ごと_実績集計_一時表"の結合により従業員データは過不足がない状態であり、それを組織コード、KPIコードでグループ化しているので、単にグループごとのレコード数を合計すれば組織、KPI項目ごとの従業員数を求めることができます。したがって、空欄iには「COUNT(*)」が当てはまります。※COUNT(A.従業員コード) などでも正解です。

∴i＝COUNT(*)

〔cについて〕
問題文には「抽出日に退職している従業員データを出力しない"従業員_所属_一時表"と，年度開始時点で入社していない従業員データを出力しない"従業員ごと_目標集計_一時表"をcによって結合しておく」とあります。
- "従業員_所属_一時表"に存在する ⇒ 集計年月日時点で所属している一般職従業員
- "従業員ごと_目標集計_一時表"に存在する ⇒ 年度開始時点で所属している全従業員
  ※年度途中入社の従業員については、月別個人目標表の初期作成レコードが存在せず、月別目標値の入力も行わないため
達成状況集計リストでは、管理職の従業員データは対象に含めず、年度途中入社と年度途中退職の従業員データについては出力しないという条件があるので、集計対象となるのは、上記2つの表にともに存在する従業員のみとなります。結合したときに両表のいずれにも存在する従業員だけを残すには、従業員コードをキーとして2つの表を内部結合する必要があります。したがって、空欄cには「INNER JOIN」が当てはまります。

∴c＝INNER JOIN

〔dについて〕
問題文には「[c：内部結合]による結合結果と，実績がある場合だけレコードの存在する"従業員ごと_実績集計_一時表"をdによって結合しておく」とあります。

[c：内部結合]による結合結果には、集計対象となる従業員が過不足なくリスト化されています。一方、"従業員ごと_実績集計_一時表"については、表3の表"日別個人実績"の運用方法に「日別実績のない従業員のレコードは作成しない」とあるので、集計対象となる従業員でも、集計期間内に1日も当該KPI項目について日別実績がない場合には、その従業員とKPI項目の組を示すレコードが存在しない可能性があります。

この2つの表を内部結合をしてしまうと、その従業員とKPI項目の組が目標・実績ともに結合結果から抜け落ちてしまいます。その結果として、目標組織集計の値は実際より低く、実績組織集計の値は実際のままとなりますから、目標に対して実績値が高く見えてしまう不具合が生じます。

正しく集計するには、実績が存在しないKPI項目についても目標値を加算し、実績値を0として集計する必要があるので、実績レコードが存在しない従業員・KPI項目の組についても結合結果に残す必要があります。このためには外部結合を行います。全て残したいのは[c：内部結合]の行であり、こちらが先に記述されているので左外部結合を行うことになります。したがって、空欄dには「LEFT OUTER JOIN」が当てはまります。

∴d＝LEFT OUTER JOIN
先述したように、集計期間内に1日も当該KPI項目について日別実績がない場合には、"従業員ごと_実績集計_一時表"にその従業員とKPI項目の組が存在しません。[c：内部結合]と"従業員ごと_実績集計_一時表"が左外部結合されると、存在しない従業員とKPI項目の組の実績値はNULLとなります。したがって、実績個人集計がNULLとなる場合としては、「従業員のKPI項目の実績データが、集計期間内に1件も存在しない場合」や「集計期間内に1日も実績がない従業員とKPI項目の組合せがある場合」などが適切な解答となります。

※NULLのままだとSUM()の集計が正しく働かないため、COALESCE()でNULL値を0に変換しています。

∴該当従業員のKPI項目に対する実績データが，1件も存在しない場合

令和5年春期午後問題一覧

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　採点講評