応用情報技術者試験の過去問&キーワードを動画2443本の動画で解説!スキマ時間に動画!※2017年9月11日現在

PR広告

平成27年度秋応用情報技術者試験午後過去問 問6 データベース 設問1

問6

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

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

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

平成27年度秋応用情報技術者試験午前過去問6 データベース

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

平成27年度秋応用情報技術者試験午前過去問6 データベース

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

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

WITH RECURSIVE 関連部署(部署ID, 部署名, 上位部署ID) AS (
  SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
    FROM 部署 WHERE 部署.部署ID = :部署ID
UNION ALL 
  SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
    FROM 部署, 関連部署 WHERE 部署.上位部署ID = 関連部署.部署ID
)
SELECT 部署ID, 部署名, 上位部署ID FROM 関連部署

図2 指定した部署配下の全ての部署を出力するSQL文

 図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に示す。

平成27年度秋応用情報技術者試験午前過去問6 データベース

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

平成27年度秋応用情報技術者試験午前過去問6 データベース

 要件(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 関連部署
図5 指定した日の会社全体の部署構造を出力するSQL文

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

平成27年度秋応用情報技術者試験午前過去問6 データベース

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

平成27年度秋応用情報技術者試験午前過去問6 データベース

設問1

現行システムについて、(1)、(2)に答えよ。

(1) 図1及び表1中のaに入れる適切なリレーションシップを答え、E-R図を完成させよ。図1の凡例に倣って解答すること。

(2) 表1中のbcに入れる適切な属性名を答えよ。

解説

設問1はエンティティ「社員」とエンティティ「部署」の関係が問題です。

(1)

社員は必ず一つの部署だけに所属しているので、「社員:多」対「部署:1」となります。このことは既にE-R図に記載されています。

もう一つの「社員」と「部署」の関係性が、問題文に記載されています。

「部署には部署長が必ず一人存在するが、一人の社員が複数の部署の部署長を兼任している場合もある。」

です。このことから、「部署」エンティティの「部署長社員ID」と「社員」エンティティの「社員ID」のリレーションシップがあり、「部署には部署長が必ず一人存在する」ことから部署が「1」で、「一人の社員が複数の部署の部署長を兼任している場合もある」ことから、社員が「多」となります。

aの答えは「→」となります!

(2)はエンティティ「部署」と「部署」のリレーションシップです。

E-R図にもエンティティ「部署」が「部署」に対してリレーションシップあることを示しています。問題文を見ると 「部署の階層は木構造になっており、再帰リレーションシップで表現している。最上位は会社で、下に向かって本部、部、課などが配置されている。上位部署IDには、上位部署の部署IDを保持し、最上位である会社の上位部署IDにはNULLを設定する。」

とあります。このことから、「部署ID:1」「上位部署ID:多」の関係性があることがわかります。

bの答えは「部署ID」、cの答えは「上位部署ID」となります!

平成27年度秋応用情報技術者試験午後過去問 問6 データベース 目次

平成27年度秋基本情報技術者試験過去問 午後 目次

業務経験がない組込みシステム開発の解説は控えさせていただきますご了承ください。


タグ: ,,,,,

PR広告

フェイスブックコメント

難解な応用情報技術者試験午前で80点中48点を取る優しい方法

平成28年度春 応用情報技術者試験 午後 テキスト・動画解説

平成28年度春 応用情報技術者試験 午後 動画解説再生リスト

平成28年度春 応用情報技術者試験 午前 テキスト・動画解説

平成28年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成27年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度春 応用情報技術者試験 午後 テキスト・動画解説

平成27年度春 応用情報技術者試験 午後 動画解説再生リスト

平成27年度春 応用情報技術者試験 午前 テキスト・動画解説

平成27年度春 応用情報技術者試験 午前 動画解説再生リスト

平成26年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成26年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成26年度春 応用情報技術者試験 午前 テキスト・動画解説

平成26年度春 応用情報技術者試験 午前 動画解説再生リスト