総視聴再生時間43万分以上(2017年5月13日現在)の動画で基本情報技術者試験の過去問&キーワード解説!スキマ時間に動画!〜これじょIT〜

PR広告

平成27年度春 基本情報技術者試験 午後 問3 データベース

問3 データベース

自治会員の情報を管理する関係データベースの設計及び運用に関する次の記述を読んで、設問1~4に答えよ。

 X地区の自治会では、世帯数の増加と、個人情報管理の厳格化を背景に、手書きの帳票で管理していた自治会員の情報を電子化することにした。この自治会には、236世帯、667人が登録されていて、各世帯は1~8班のいずれかに所属している。

 従来は図1に示すとおり、世帯ごとに、世帯主氏名、住所、電話番号、登録日、所属する班、同居者氏名、続柄、性別、生年月日などの情報を管理していた。

平成27年度春 基本情報技術者試験 午後 問3 データベース 図1

 図1の帳票を基に、図2に示す表構成をもつ関係データベースを作成した。下線付きの項目は、主キーを表す。

〔表の説明〕

  • 世帯表の世帯番号には、各世帯に一意に割り当てた番号が格納されている。
  • 世帯表の世帯主番号には、世帯主の会員番号が格納されている。
  • 会員表の会員番号には、各会員に一意に割り当てた番号が格納されている。
  • 会員表の世帯番号には、会員が属する世帯の世帯番号が格納されている。

設問1

図2の表構成を完成するまでの設計について、次の記述中の【 】に入れる適切な答えを、解答群の中から選べ。

 図1の帳票は非正規形なので、まず、【 a 】世帯情報を取り出して会員表を作成した。次に、【 b 】続柄表と性別表を作成した。続柄表と性別表のレコード件数は少ないが、例えば、帳票の記載で"男"と"男性"のように同じ意味を表すデータに対する定義の一意性を保証できる効果がある。

a、b に関する解答群

  • ア : 障害回復のために
  • イ : 第1正規化に基づいて
  • ウ : 第2正規化に基づいて
  • エ : 第3正規化に基づいて
  • オ : 排他制御のために

解説

図1は「帳票は非正規形」と設問にも説明があります。データベースを構築する際には基本的に第3正規化にします。第3正規化にするためには第1正規化から始めます。

【 a 】は「イ : 第1正規化に基づいて」が正解

セオリーでは、第1正規化の次に第2正規化をしますが、問題をよく読みましょう。「続柄表と性別表を作成した。続柄表と性別表のレコード件数は少ないが、例えば、帳票の記載で"男"と"男性"のように同じ意味を表すデータに対する定義の一意性を保証できる効果がある。」とあります。このような作業を行うことを第3正規化といいます。難しい言葉で言うと「非キー項目どうしの関数従属関係を排除」となります。

【 b 】は「エ : 第3正規化に基づいて」が正解

設問2

地区の福祉委員会から、1940年よりも前に生まれた会員が含まれる世帯の世帯番号について、情報提供を求められた。該当する世帯番号を抽出する正しいSQL文を解答群の中から選べ。

 なお、同じ世帯番号は一つだけ抽出する。

解答群

  • ア : SELECT DISTINCT 世帯番号 FROM 会員表 WHERE 会員表.生年月日 >= 19400101
  • イ : SELECT 世帯番号 FROM 会員表 WHERE 会員表.生年月日 >= 19391231
  • ウ : SELECT 世帯番号 FROM 会員表 WHERE 会員表.生年月日 < 19400101 GROUP BY 世帯番号
  • エ : SELECT 世帯番号 FROM 会員表 WHERE 会員表.生年月日 <= 19391231 GROUP BY 世帯番号, 会員番号

解説

導きだすのは「1940年よりも前に生まれた会員が含まれる世帯の世帯番号」のデータです。まずはWHERE句に注目しましょう!

  • ア : WHERE 会員表.生年月日 >= 19400101 /* 1940年01月01日以降(当日を含む)のデータなので× */
  • イ : WHERE 会員表.生年月日 >= 19391231 /* 1939年12月31日以降(当日を含む)のデータなので× */
  • ウ : WHERE 会員表.生年月日 < 19400101 GROUP BY 世帯番号 /* 1940年01月01日以前(当日を含まない)のデータなので該当 */
  • エ : WHERE 会員表.生年月日 <= 19391231 GROUP BY 世帯番号, 会員番号 /* 1939年12月31日以前(当日を含む)のデータなので該当 */

可能性があるのは、「ウ」か「エ」となります。欲しいデータは「世帯番号」ですが、「ウ」と「エ」共に、このままだと会員が同じ世帯に属し手いる場合、重複して取得されます。DISTINCTかGROUP BYで重複をなくします。

「エ」の場合、「GROUP BY 世帯番号, 会員番号」とすると、世帯番号ごとの会員番号を取るので、結果的に会員番号まで取得でき重複を削除できません。

「ウ」のように、「GROUP BY 世帯番号」とすることで、世帯番号だけを取得することが可能です!

設問3

班ごとの会員数に偏りがあるとの意見が挙がったので、班の再編を検討することになった。現在の状況を確認するために、班ごとの世帯数と会員数を集計する。次のSQL文の【 】に入れる正しい答えを、解答群の中から選べ。

SELECT 世帯表.班, 【  c  】
    FROM 世帯表, 会員表 
    WHERE 世帯表.世帯番号 = 会員表.世帯番号
    GROUP BY 世帯表.班

c に関する解答群

  • ア : COUNT(*), COUNT(会員表.会員番号)
  • イ : COUNT(*), MAX(会員表.会員番号)
  • ウ : COUNT(DISTINCT 世帯表.世帯番号), COUNT(*)
  • エ : COUNT(世帯表.世帯番号), MAX(会員表.会員番号)

解説

設問では「班ごとの世帯数と会員数を集計する。」とあります。「世帯数」と「会員数」を求める構文が入ればよいとなります。

関数COUNT()は、レコードの数を集計します。

関数MAX()は、最大値を取得します。ということで、世帯数も会員数の集計には使えません(「イ」と「エ」は消えます)。

FROM以下をみると、班ごとの会員の一覧になっています。会員数はそのままレコードをカウントすればよいので「COUNT(*)」となり、世帯数は「COUNT(DISTINCT 世帯表.世帯番号)」とすると、世帯数が取得できます。

従って、正解は「ウ : COUNT(DISTINCT 世帯表.世帯番号), COUNT(*)」です!

設問4

地区の子供会役員から,子供会に所属する子供の情報を照会できるようにしてほしいとの要望が挙がったので,図3に示すビューを作成することにした。子供会には,生年月日が 20030402~20090401 の会員が所属する。次のSQL文の に入れる正しい答えを,解答群の中から選べ。

CREATE VIEW 子供会表 AS 
    SELECT 会員表.会員番号, 会員表.世帯番号, 
	       世帯表.電話番号, 会員表.氏名, 会員表.生年月日
    FROM 世帯表, 会員表
	WHERE 【  d  】

解答群

  • ア 会員表.会員番号 = ANY(SELECT 会員表.会員番号 FROM 会員表 WHERE 会員表.生年月日 IN (20030402, 20090401))
  • イ 会員表.会員番号 = 世帯表.世帯主番号 AND 会員表.生年月日 BETWEEN 20030402 AND 20090401
  • ウ 会員表.会員番号 = ANY(SELECT 会員表.世帯番号 FROM 会員表 WHERE 会員表.生年月日 IN (20030402, 20090401))
  • エ 会員表.会員番号 = 世帯表.世帯番号 AND 会員表.生年月日 BETWEEN 20030402 AND 20090401

解説

まずはINとBETWEENの意味から

INは範囲ではありません。IN (20030402, 20090401)の場合、「20030402」か「20090401」と一致するものとなります。従って、「ア」「ウ」では、「生年月日が 20030402~20090401 の会員が所属」するデータが取得できません。

BETWEENは範囲検索となるので、BETWEEN 20030402 AND 20090401は「生年月日が 20030402~20090401 の会員」データとなります。

イの「会員表.会員番号 = 世帯表.世帯主番号 AND 会員表.生年月日」では、世帯主の情報しか取れません。対象は子供です。

エのように「会員表.会員番号 = 世帯表.世帯番号 AND 会員表.生年月日」とすると、会員情報がすべて対象となるので正しいです。

正解は「エ」です

タグ: ,,,

PR広告

フェイスブックコメント

平成28年度秋 基本情報技術者試験 午後 テキスト・動画解説

平成28年度秋 基本情報技術者試験 午前 テキスト・動画解説

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

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

平成27年度秋 基本情報技術者試験 午後 テキスト・動画解説

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

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

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

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