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

PR広告

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

問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 データベース

設問2

新システムの要件を実現するためのエンティティについて、(1)、(2)に答えよ。

(1) 本文中の下線①で、エンティティ"社員"は第1正規形、第2正規形、第3正規形のうち、どこまで正規化されているか答えよ。また、その理由を30字以内で述べよ。

(2) 図4中のエンティティ"所属"の属性を、本文中又は図中の字句を用いて答えよ。属性が主キーの一部となる場合は、実線の下線を付けること。

解説

(1)の解説

エンティティ「社員」、エンティティ「部署」共に、繰り返しが排除されているので、第1正規化がされています。 社員と部署は、これまで異動した部署も管理するので、履歴管理をします。

図3では、エンティティ「社員」とエンティティ「部署」に適用開始年月のカラムを持ち、エンティティ「社員」に「所属ID」を持たせることで、履歴管理が可能となります。

しかし、エンティティ「社員」の主キーの一つ「社員ID」に「氏名」「性別」が関数従属しています。

関数従属とは、「ある属性Aの値が決まるとき、属性Bが一意に決まる」ことをいい、この場合、社員の「社員ID」が決まるとき、「氏名」「性別」が決まります。従って「主キーの一部に関数従属している属性があるから」もしくは「社員IDだけに従属している属性があるから」第2正規化がされていないと言えます。

また、「社員ID」と「適用開始年月日」が主キーであり、その一部の「社員ID」に「氏名」「性別」が関数従属していることから、「主キーに部分関数従属している属性があるから」第2正規化されていないことがわかります。

部分関数従属性とは、キーの一部にキー以外の属性が関数従属していることです。

このことから

答えは「第1正規形」となり、理由は

「主キーの一部に関数従属している属性があるから」

「主キーに部分関数従属している属性があるから」

「社員IDだけに従属している属性があるから」

となります!※上記は、IPA公式解答です。

(2)の解説

(1)の理由より、「所属」エンティティを別テーブルにして、「社員ID」「適用開始年月日」「適用終了年月日」「所属部署ID」を持たせ、「社員ID」「適用開始年月日」を主キーにします。

こうすることで、「社員ID」がわかれば「適用開始年月日」「適用終了年月日」「所属部署ID」の履歴がわかります。

現状の所属部署は所属の「社員ID」と「適用開始年月日」をキーに部署エンティティを見るとわかります。

従って、所属の属性は

社員ID

適用開始年月日

「適用終了年月日」

「所属部署ID」

です!

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

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

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


タグ: ,,,,,

PR広告

フェイスブックコメント

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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