2015年 秋期 応用情報技術者試験 問6

人事情報のデータ構造に関する次の記述を読んで，設問1〜3に答えよ。

R社では，人事システムの改善を検討している。現行システムでは，現時点での情報しか管理していないが，過去の履歴や将来の発令予定も管理できるようにしたいと考えている。

現行システムでの社員と部署のE-R図を図1に示す。部署の階層は木構造になっており，再帰リレーションシップで表現している。最上位は会社で，下に向かって本部，部，課などが配置されている。上位部署IDには，上位部署の部署IDを保持し，最上位である会社の上位部署IDにはNULLを設定する。社員は必ず一つの部署だけに所属している。部署には部署長が必ず一人存在するが，一人の社員が複数の部署の部署長を兼任している場合もある。また，各社員に携帯電話機を1台ずつ配布しており，電話番号は部署にではなく，社員に割り当てられている。

図1のリレーションシップが，どの属性と関連しているかを表1に示す。表1の1行目は，エンティティ"社員"の属性"所属部署ID"がエンティティ"部署"の属性"部署ID"を参照する外部キーとなっていて，"社員"と"部署"の間には多対1のリレーションシップがあることを示している。多対1のリレーションシップの多側が外部キーの属性，1側が主キーの属性と対応している。

現行システムは，図1のE-R図のエンティティ名を表名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理している。

指定した部署とその配下の全ての部署の部署ID，部署名，上位部署IDを出力するSQL文を図2に示す。ここで，":部署ID"は，指定した部署の部署IDを格納する埋込み変数である。

1  WITH RECURSIVE 関連部署(部署ID, 部署名, 上位部署ID) AS (
2    SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
3      FROM 部署 WHERE 部署.部署ID = :部署ID
4    UNION ALL
5    SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
6      FROM 部署, 関連部署 WHERE 部署.上位部署ID = 関連部署.部署ID
7  )
8  SELECT 部署ID, 部署名, 上位部署ID FROM 関連部署

図2では，SQL:1999で導入されたWITH RECURSIVE構文を用いて再帰的なクエリを実現している。まず2，3行目のSELECTで，埋込み変数":部署ID"で指定した部署の部署ID，部署名，上位部署IDから成る1行の表"関連部署"が導出される。

次に5，6行目のSELECTで，"関連部署"の中にある部署IDと一致する上位部署IDをもつ部署の部署ID，部署名，上位部署IDから成る行の集まりが新たに表"関連部署"として導出される。これが，表"関連部署"の新たな行がなくなるまで繰り返される。最後に8行目のSELECTで，それまで導出された"関連部署"の全ての行について部署ID，部署名，上位部署IDが出力される。

【新システムでの履歴管理】

新システムでは，(1)〜(4)の要件を実現したいと考えている。

(1) 指定した社員が，今までに所属していた部署の履歴が分かる。

(2) 指定した日の，会社全体の部署構造が分かる。

(3) 人事異動後の部署，所属の情報をあらかじめ入力しておき，異動が発生したらすぐに有効とする。

(4) 所属情報以外の社員の情報は履歴管理する必要はなく，最新の情報だけを管理すればよい。

これらの要件を実現するために，エンティティ"社員"と"部署"に，属性"適用開始年月日"と"適用終了年月日"を追加して，各タプルの有効期間を管理する方法を考えた。指定した日が適用開始年月日から適用終了年月日までの範囲内であれば，その日の時点で有効なタプルである。適用終了年月日が未定の場合は，'9999-12-31'を設定する。新しいエンティティ"社員"と"部署"を図3に示す。

しかし，①図3のエンティティ"社員"は十分に正規化されていないとの指摘を受け，エンティティ"所属"を新たに追加し，エンティティ"社員"を第3正規形とした。新システムでの社員と部署と所属のE-R図を図4に示す。

要件(2)を実現するSQL文を図5に示す。ここで，":年月日"は，指定した日の日付を格納する埋込み変数である。

WITH RECURSIVE 関連部署(部署ID, 部署名, 上位部署ID) AS (
  SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
    FROM 部署 WHERE 部署.上位部署ID d
    AND :年月日 BETWEEN 部署.適用開始年月日 AND 部署.適用終了年月日
  UNION ALL
  SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
    FROM 部署, 関連部署 WHERE 部署.上位部署ID = 関連部署.部署ID
    AND :年月日 BETWEEN 部署.適用開始年月日 AND 部署.適用終了年月日
)
SELECT 部署ID, 部署名, 上位部署ID FROM 関連部署

現時点での部署テーブルの内容を表2に示す。

埋込み変数"年月日"にeからfまでの範囲の日付を設定して，表2の部署テーブルに対して図5のSQL文を実行すると，その結果は表3のとおりとなる。