令和6年度 春期 応用情報技術者試験 午後 問6 SaaSマルチテナント化のスキーマ設計

人事評価システムの設計と実装に関する次の記述を読んで，設問に答えよ。

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

〔人事評価システムの機能概要〕

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

〔単一データベース・単一スキーマ方式の検討〕

データベースのリソースを最適化するために，会社ごとに個別に作成していたデータベース及びスキーマを一つにまとめることを考える。検討したE-R図を図1に示す。
なお，再構築するシステムでは，E-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。

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

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

SELECT 祝日 AS 日付, 祝日名 AS 日付名
FROM 国民の祝日
WHERE 祝日 [ <span id="q6_blank_b" class="blank-label">b</span> ]
UNION ALL
SELECT 会社記念日 AS 日付, 会社記念日名 AS 日付名
FROM 会社記念日
WHERE 会社番号 = :会社番号
  AND 会社記念日 [ <span id="q6_blank_b" class="blank-label">b</span> ]
[ <span id="q6_blank_c" class="blank-label">c</span> ] 日付

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

SELECT DEP.部署番号, DEP.部署名, EMP.従業員番号, EMP.従業員氏名
FROM 従業員 EMP INNER JOIN 部署 DEP
ON EMP.会社番号 = DEP.会社番号
  AND [ <span id="q6_blank_d" class="blank-label">d</span> ]
  AND EMP.会社番号 = :会社番号
  AND DEP.管理者番号 = :管理者番号
[ <span id="q6_blank_c" class="blank-label">c</span> ] DEP.部署番号, EMP.従業員番号

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

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

・指摘

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

・アドバイス

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

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

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

次に，ビューを作成するSQL文について考える。
スキーマ C001 に国民の祝日ビューを作成するSQL文を図4に示す。

CREATE VIEW [ <span id="q6_blank_e" class="blank-label">e</span> ] (祝日, 祝日名)
AS SELECT 祝日, 祝日名
FROM [ <span id="q6_blank_f" class="blank-label">f</span> ]

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

検討した単一データベース・個別スキーマ方式のレビューを受けたところ，次の指摘を受けた。
・システム利用者ごとに，利用するスキーマを指定するために，g表にh列を追加する必要がある。
・表2の表とビューの配置のままでは②利用できない機能があるので，③配置の一部を見直す必要がある。

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