基本情報技術者試験 - 平成25年春期(科目B) - 問3

問題

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

 ある地域で5店舗のヘアサロンを経営しているZ社では,会員登録した顧客に対して紙製の会員証を発行し,氏名や住所などの会員情報は表計算ソフトを利用して管理していた。今回,顧客サービスの向上を目的に,会員情報を管理するシステム(以下,管理システムという)を導入し,会員証のICカード化を行うとともに会員情報と来店記録のデータベース化を実施した。

 管理していた会員情報の例とその形式は,図1のとおりである。
会員番号氏名住所電話番号性別生年月日
00001情報花子東京都文京区桜丘2-2803-1111-2222女性19870613
図1 管理していた会員情報の例とその形式

 まず,会員情報と来店記録を管理するために図2に示すデータベースを設計した。下線付きの項目は主キーを表す。
会員表
会員番号氏名住所電話番号性別生年月日
00001情報花子東京都文京区桜丘2-2803-1111-22220219870613
店舗表
店舗コード店舗名住所電話番号
01桜丘店東京都文京区桜丘1-203-8888-9999
メニュー表
メニューコード商品名単価
001カット5000
002パーマ5000
会計表
会計コード会員番号店舗コード来店日会計金額
12000100001012013051110000
明細表
会計コードメニューコード
120001001
120001002
図2 会員情報と来店記録データのデータベースへの格納例

設問1  会員特典として,ポイント制度を導入することにした。ポイント情報の管理に関する次の記述中の[   ]に入れる適切な答えを,解答群の中から選べ。

 会員が料金を支払う際に会員証を提示すると,会計金額に応じて,千円につき1ポイントが付与される。会計時に付与されたポイント(以下,付与ポイントという)と,会員が現在保有しているポイント(以下,保有ポイントという)は,レシートに印字される。
 会計の際,会員が希望すれば,保有ポイントと引き換えに表1に示す割引サービスを受けられる。このとき保有ポイントから引かれるポイントを利用ポイントという。付与ポイントは,割引後の料金から算出する。
表1 ポイントによる割引サービス
利用ポイント数割引率(%)
105
2015
3030

 管理システムで会員の保有ポイントを参照する機会が多いので,参照の都度,保有ポイントの集計処理を実行することは避けたい。保有ポイントを管理するのに適した表は,[ a ]である。また,会員がポイントをどのように活用しているかの傾向を分析できるよう,ポイントの付与と利用の履歴を管理したい。付与ポイント及び利用ポイントを管理するのに適した表は,[ b ]である。

a,bに関する解答群
ア会員表
イ会計表
ウ店舗表
エ明細表
オメニュー表

設問2  2012年9月にデータベース及び管理システムの開発が完了し,10月から運用を開始した。2012年12月について,店舗ごとの売上金額,延べ来客数,会計コード単位の平均会計額を表示したい。次のSQL文中の[   ]に入れる正しい答えを,解答群の中から選べ。ここで,売上金額は会計金額の合計とする。ポイントによる割引サービスを利用した場合は,会計表の会計金額には割引後の金額が設定されている。

SELECT 店舗表.店舗コード,店舗表.店舗名,
 [ c ]
 FROM 店舗表,会計表
 WHERE 店舗表.店舗コード = 会計表.店舗コード AND
    会計表.来店日 BETWEEN '20121201' AND '20121231'
 GROUP BY 店舗表.店舗コード,店舗表.店舗名

cに関する解答群
アSUM(ALL 会計表.会計金額) AS 売上金額,
 MAX(会計表.会員番号) AS 延べ来客数,
 AVG(売上金額 / 延べ来客数) AS 平均会計額
イSUM(ALL 会計表.会計金額) AS 売上金額,
 MAX(会計表.会計コード) AS 延べ来客数,
 AVG(会計表.会計金額) AS 平均会計額
ウSUM(会計表.会計金額) AS 売上金額,
 COUNT(DISTINCT 会計表.会計コード) AS 延べ来客数,
 AVG(売上金額 / 延べ来客数) AS 平均会計額
エSUM(会計表.会計金額) AS 売上金額,
 COUNT(会計表.会計コード) AS 延べ来客数,
 AVG(会計表.会計金額) AS 平均会計額

設問3  会員の来店を促す目的で,次のSQL文によって表示される会員に対して,割引券を送付することにした。表示される会員の説明として適切な答えを,解答群の中から選べ。ここで,会員は,1度は来店したことがあるものとする。

SELECT 会員表.会員番号,会員表.氏名
 FROM 会員表,会計表
 WHERE 会員表.会員番号 = 会計表.会員番号
 GROUP BY 会員表.会員番号,会員表.氏名
 HAVING MAX(会計表.来店日) < '20130101'

解答群
ア2013年になってから,1回以上,来店した会員
イ2013年になってから,1回も来店してない会員
ウ2013年より前に,1回以上,来店した会員
エ2013年より前に,1回も来店してない会員

設問4  顧客サービスを向上させるために,“指名美容師”と“過去の来店時に担当した美容師”を確認できるよう,会員表と会計表に担当美容師の項目を追加した。担当美容師は,図3に示す従業員表の従業員コードを外部キーとして参照する。指名美容師がいない会員の場合,会員表の担当美容師としてNULLを設定する。
 情報の追加後,従業員コード“2008005”の美容師が所属する店舗が変わるので,会員表の担当美容師に登録している会員と,当該美容師が過去に担当したことがある会員に,所属店舗移動の案内を送付することにした。該当する会員の会員番号を表示する次のSQL文中の[   ]に入れる正しい答えを,解答群の中から選べ。
従業員表
従業員コード氏名住所電話番号生年月日店舗コード
2008005美容太郎東京都文京区青葉3丁目03-1234-56781985010502
図3 従業員表とデータの格納例

SELECT DISTINCT 会員表.会員番号,会員表.氏名,会員表.住所
 FROM 会員表,会計表
 WHERE [ d ]

dに関する解答群
ア会員表.会員番号 = 会計表.会員番号 AND
 (会員表.担当美容師 = '2008005' OR
  会計表.担当美容師 = '2008005')
イ会員表.会員番号 = 会計表.会員番号 AND
 会員表.担当美容師 = '2008005' AND
 会計表.担当美容師 = '2008005' AND
 会計表.店舗コード = (SELECT 店舗コード FROM 従業員表
           WHERE 従業員コード = '2008005')
ウ会員表.会員番号 = 会計表.会員番号 AND
 会計表.店舗コード = (SELECT 店舗コード FROM 従業員表
           WHERE 従業員コード = '2008005')
エ会員表.会員番号 = 会計表.会員番号 AND
 会計表.店舗コード = ANY (SELECT 店舗コード FROM 従業員表
             WHERE 従業員コード = '2008005')

正解と解説

正解設問1 (a)ア (b)イ 設問2 (c)エ 設問3 イ 設問4 (d)ア 
分野テクノロジ系 › 技術要素 › データベース › データ操作

解説

■ 問題の概要

 ヘアサロンの会員情報や来店記録を,関係データベースで管理する問題です。関係データベースでは,データを「表」の形で管理し,SQLという言語で必要なデータを取り出します。図2の表の役割を整理しておきましょう。
  • 会員表:会員1人につき1行
  • 店舗表:店舗1つにつき1行
  • メニュー表:メニュー1つにつき1行
  • 会計表:1回の会計(来店)につき1行
  • 明細表:1回の会計で受けたメニュー1つにつき1行

■ 設問1

【a】「保有ポイント」は会員1人につき1つの値です。参照のたびに集計をしたくないので,現在の値をそのまま保存しておく必要があります。会員1人につき1行の会員表に項目を追加するのが適切です。正解は「ア」です。

【b】「付与ポイント」と「利用ポイント」は,会計のたびに発生する値です。会計ごとの履歴を残したいので,1回の会計につき1行の会計表に項目を追加するのが適切です。正解は「イ」です。

■ 設問2

 求めたい値は,店舗ごとの次の3つです。
  • 売上金額:会計金額の合計 → SUM(会計表.会計金額)
  • 延べ来客数:会計の件数 → COUNT(会計表.会計コード)
  • 平均会計額:会計金額の平均 → AVG(会計表.会計金額)
 これらをすべて含むのは「エ」です。

 ほかの選択肢が誤りである理由は次のとおりです。「ア」と「イ」はMAX(最大値)で延べ来客数を求めているので誤りです。「ア」と「ウ」はAVGの中で 「売上金額」 「延べ来客数」 という,同じSELECT句の中で付けた別名を使っています。SQLでは,このような使い方はできません。

■ 設問3

 このSQL文は,会員ごとにグループ化し,「最後に来店した日(来店日の最大値)」が2013年1月1日より前である会員を表示しています。最後の来店が2012年以前ということは,2013年になってから一度も来店していないということです。正解は「イ」です。

■ 設問4

 案内を送る相手は,次の2種類の会員です。
  • 会員表の担当美容師(指名美容師)が "2008005" の会員
  • 会計表の担当美容師が "2008005" である会計がある会員(過去に担当した会員)
 どちらか一方に当てはまればよいので,2つの条件を「OR」でつなぎます。正解は「ア」です。

 「イ」は「AND」でつないでいるので,両方に当てはまる会員しか表示されません。「ウ」と「エ」は,その美容師が所属する店舗に来店したすべての会員を表示してしまうので誤りです。
ホーム画面への追加方法
1.ブラウザの 共有ボタンのアイコン 共有ボタンをタップ
2.メニューの「ホーム画面に追加」をタップ
閉じる