問題プレビューID: e86aa311応用情報技術者試験 2023年(令和5年度)春期 午後

問 6
応用情報技術者試験 2023年(令和5年度)春期 午後

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図(抜粋)

[組織] ──[a]── [所属] ◀── [役職]
  │                  △
  │自己関連(→)       │(→)
  │                  │
  │              [従業員] ──→ [日別個人実績]
  │                  │          △
  │                  │          │(→)
  │                  ↓(→)   [KPI]
  │           [月別個人目標]    │
  │                  △←───────┘
  │                 (→)
  • 組織: 組織コード(主キー),組織開始年月日,組織終了年月日,組織名,上位組織コード(外部キー),上位組織開始年月日(外部キー)。上位組織への自己関連(→)をもつ。
  • 所属: 従業員コード(主キー),所属開始年月日,所属終了年月日,所属組織コード(外部キー),所属組織開始年月日(外部キー),役職コード(外部キー)。
  • 役職: 役職コード(主キー),役職職名,職務区分。所属への関連(→)をもつ。
  • 従業員: [b](主キー),氏名,生年月日,メールアドレス,住所。所属,月別個人目標,日別個人実績への関連(→)をもつ。
  • KPI: KPIコード(主キー),年度,KPI項目名,定量的成果目標。月別個人目標,日別個人実績への関連(→)をもつ。
  • 月別個人目標: 従業員コード(主キー),KPIコード,年月,月別目標値。
  • 日別個人実績: 従業員コード(主キー),KPIコード,年月日,日別実績値。

注記 属性名の実線の下線___は主キー,破線の下線_ _ _は外部キーを示す。主キーの実線が付いている属性名には,外部キーの破線を付けない。

凡例

図記号意味
エンティティ名 ─1対1
→1対多
↔多対多

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

表 3 表の管理内容と運用方法

表管理内容運用方法
KPIKPI項目と定量的成果目標を管理する。・参照だけ(更新は行わない)。
月別個人目標個人ごとの月別目標値を管理する。・年度開始時点で在籍している全従業員に対して,当該年度分のレコードを,目標値を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 集計リスト作成処理

項番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中の[a],[b]に入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。

なお,エンティティ間の関連及び属性名の表記は,図1の凡例及び注記に従うこと。

設問2

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

(1) 本文及び図2中の[c]~[i]に入れる適切な字句を答えよ。

(2) 本文中の下線①に示す事態は,年度開始年月日から集計年月日までの間に,どのデータがどのような場合に発生するか。40字以内で答えよ。

模範解答

設問1 a →,b 従業員コード

設問2 (1) c INNER JOIN,d LEFT OUTER JOIN,e BETWEEN,f B.職務区分 = '02',g GROUP BY 従業員コード,KPIコード,h 組織ごと_目標実績集計_一時,i COUNT(*) (2) 該当従業員のKPI項目に対する実績データが,1件も存在しない場合

解説

正規化された表を扱う処理を題材に,集合関数,表結合などによる関係データベースのデータ操作についての理解を問う。

設問1の[a]は,組織と所属の間の1対多の関連(→)である。[b]は,従業員エンティティの主キーである従業員コードである。

設問2(1)では,管理職の除外と年度途中入社・退職者の除外のためにINNER JOINで結合し,実績の有無にかかわらず出力するためにLEFT OUTER JOINで結合する。期間の条件はBETWEEN,一般職の条件はB.職務区分 = '02',集計単位はGROUP BY 従業員コード,KPIコード,出力先は組織ごと_目標実績集計_一時表,対象従業員数はCOUNT(*)で求める。(2)の実績個人集計がNULLになるのは,該当従業員のKPI項目に対する実績データが1件も存在しない場合である。

関連問題

本文が同じ・似ている問題を表示しています