2024年 春期 応用情報技術者試験 問6

人事評価システムの設計と実装

K社は、人事評価システムを中小企業に提供するSaaS事業者である。現在は、契約している会社ごとに仮想サーバを作成して、その中にデータベースを個別に作成している。現在のシステムのOSやフレームワークのサポート期限が迫ってきたのを機に、機能は変更せずにサーバリソース最適化を目的として、システムを再構築することにした。

【人事評価システムの機能概要】

人事評価システムの機能概要を表1に示す。

表1 人事評価システムの機能概要
機能名概要
祝日管理国民の祝日に加えて、創立記念日などの会社ごとの記念日を年月日で管理する。
入社従業員が入社した際、従業員番号を割り振り、配属先の部署及び入社年月日を登録する。
評価者管理部署の管理者を評価者として登録する。1人の従業員が複数の部署を管理する場合がある。管理者の評価者は、評価時に個別に設定する。
目標設定年度の始めに、その年度の目標を設定する。目標は複数設定することができ、重要度や達成までの期間などを考慮して重み付けする。
実績入力年度の終わりに、その年度の実績を入力する。実績は、年度の始めに設定した目標に対して、実績内容や目標達成度を自己評価として記入する。
評価年度の終わりに、管理者は評価対象の従業員が設定した目標とそれに対する実績を評価して、評価内容や達成度合を記入する。
退職従業員の退職が決まると、その退職年月日と在籍期間を登録する。さらに、部署の管理者や人事部が対象の従業員にヒアリングした退職理由を登録する。
退職分析人事部の管理者が自社及び自社と同じ業種の退職者について、在籍期間と退職理由を分析する。

【単一データベース・単一スキーマ方式の検討】

データベースのリソースを最適化するために、会社ごとに個別に作成していたデータベース及びスキーマを一つにまとめることを考える。検討したE-R図を図1に示す。

なお、再構築するシステムでは、E-R図のエンティティ名を表名に、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。

図1 E-R図
図1を関係データベースに実装した際のSQL文を考える。

(1) 指定された会社と年度における、国民の祝日と会社記念日の一覧を日付の昇順に出力するSQL文を図2に示す。ここで":会社番号"は指定された会社の会社番号を、":年度開始日"、":年度終了日"は、それぞれ指定された年度の開始日、終了日を表す埋込み変数である。

SELECT 祝日 AS 日付, 祝日名 AS 日付名
FROM 国民の祝日
WHERE 祝日 b
UNION ALL
SELECT 会社記念日 AS 日付, 会社記念日名 AS 日付名
FROM 会社記念日
WHERE 会社番号 = :会社番号
AND 会社記念日 b
c 日付
図2　国民の祝日と会社記念日の一覧を日付の昇順に出力するSQL文

(2) 指定された管理者が評価する対象の従業員の一覧を部署番号、従業員番号の昇順に出力するSQL文を図3に示す。ここで":会社番号"と":管理者番号"は、それぞれ指定された管理者の会社番号と従業員番号を表す埋込み変数である。

SELECT DEP.部署番号, DEP.部署名, EMP.従業員番号, EMP.従業員氏名
FROM 従業員 EMP INNER JOIN 部署 DEP
ON EMP.会社番号 = DEP.会社番号
AND d
AND EMP.会社番号 = :会社番号
AND DEP.管理者番号 = :管理者番号
ORDER BY c DEP.部署番号, EMP.従業員番号
図3　従業員の一覧を部署番号、従業員番号の昇順に出力するSQL文
注記　cには、図2中のcと同じ字句が入る。

【単一データベース・単一スキーマ方式のレビュー】

検討した単一データベース・単一スキーマ方式のレビューを受けたところ、次の指摘とアドバイスを受けた。

・指摘

この検討案は、サーバリソース最適化を実現することができるが、SQLインジェクションの脆弱性が見つかってしまった場合、多くの情報が漏えいしてしまうおそれがある。

・アドバイス

データベースは一つのまま、システム全体で共有するデータだけを格納する共有用のスキーマと、①システム利用者の会社ごとのスキーマに分ける方式にするとよい。共有用のスキーマに作成した表は、会社ごとのスキーマに対象表と同じ名前のビューを作成して照会できるようにすると、現在のシステムのSQL文への修正を少なくすることができる。

【単一データベース・個別スキーマ方式の検討】

【単一データベース・単一スキーマ方式のレビュー】のアドバイスを受け、複数のスキーマを作成して各スキーマに表とビューを配置する。検討したスキーマを整理した結果を表2に示す。

表2 スキーマを整理した結果
スキーマ種類スキーマ名配置する表配置するビュー
共有用PUB会社、国民の祝日—
個別会社用Cxxx
（xxxは任意の英数字）		会社記念日、従業員、部署、目標、実績、評価、退職会社、国民の祝日

次に、ビューを作成するSQL文について考える。

スキーマC001に国民の祝日ビューを作成するSQL文を図4に示す。

CREATE VIEW e (祝日, 祝日名)
AS SELECT 祝日, 祝日名
FROM f
図4　国民の祝日ビューを作成するSQL文

【単一データベース・個別スキーマ方式のレビュー】

検討した単一データベース・個別スキーマ方式のレビューを受けたところ、次の指摘を受けた。

・システム利用者ごとに、利用するスキーマを指定するために、g表にh列を追加する必要がある。

・表2の表とビューの配置のままでは②利用できない機能があるので、③配置を一部見直す必要がある。

レビューで受けた指摘に全て対応することで、システムを再構築することができた。

出典：令和6年度 春期 応用情報技術者試験 午後 問6