基本情報技術者試験 - 平成25年春期(科目B) - 問3
問題
会員情報を管理する関係データベースの設計と運用に関する次の記述を読んで,設問1~4に答えよ。
ある地域で5店舗のヘアサロンを経営しているZ社では,会員登録した顧客に対して紙製の会員証を発行し,氏名や住所などの会員情報は表計算ソフトを利用して管理していた。今回,顧客サービスの向上を目的に,会員情報を管理するシステム(以下,管理システムという)を導入し,会員証のICカード化を行うとともに会員情報と来店記録のデータベース化を実施した。
管理していた会員情報の例とその形式は,図1のとおりである。
図1 管理していた会員情報の例とその形式
まず,会員情報と来店記録を管理するために図2に示すデータベースを設計した。下線付きの項目は主キーを表す。
図2 会員情報と来店記録データのデータベースへの格納例
設問1 会員特典として,ポイント制度を導入することにした。ポイント情報の管理に関する次の記述中の[ ]に入れる適切な答えを,解答群の中から選べ。
会員が料金を支払う際に会員証を提示すると,会計金額に応じて,千円につき1ポイントが付与される。会計時に付与されたポイント(以下,付与ポイントという)と,会員が現在保有しているポイント(以下,保有ポイントという)は,レシートに印字される。
会計の際,会員が希望すれば,保有ポイントと引き換えに表1に示す割引サービスを受けられる。このとき保有ポイントから引かれるポイントを利用ポイントという。付与ポイントは,割引後の料金から算出する。
管理システムで会員の保有ポイントを参照する機会が多いので,参照の都度,保有ポイントの集計処理を実行することは避けたい。保有ポイントを管理するのに適した表は,[ a ]である。また,会員がポイントをどのように活用しているかの傾向を分析できるよう,ポイントの付与と利用の履歴を管理したい。付与ポイント及び利用ポイントを管理するのに適した表は,[ b ]である。
a,bに関する解答群
設問2 2012年9月にデータベース及び管理システムの開発が完了し,10月から運用を開始した。2012年12月について,店舗ごとの売上金額,延べ来客数,会計コード単位の平均会計額を表示したい。次のSQL文中の[ ]に入れる正しい答えを,解答群の中から選べ。ここで,売上金額は会計金額の合計とする。ポイントによる割引サービスを利用した場合は,会計表の会計金額には割引後の金額が設定されている。
SELECT 店舗表.店舗コード,店舗表.店舗名,
[ c ]
FROM 店舗表,会計表
WHERE 店舗表.店舗コード = 会計表.店舗コード AND
会計表.来店日 BETWEEN '20121201' AND '20121231'
GROUP BY 店舗表.店舗コード,店舗表.店舗名
cに関する解答群
設問3 会員の来店を促す目的で,次のSQL文によって表示される会員に対して,割引券を送付することにした。表示される会員の説明として適切な答えを,解答群の中から選べ。ここで,会員は,1度は来店したことがあるものとする。
SELECT 会員表.会員番号,会員表.氏名
FROM 会員表,会計表
WHERE 会員表.会員番号 = 会計表.会員番号
GROUP BY 会員表.会員番号,会員表.氏名
HAVING MAX(会計表.来店日) < '20130101'
解答群
設問4 顧客サービスを向上させるために,“指名美容師”と“過去の来店時に担当した美容師”を確認できるよう,会員表と会計表に担当美容師の項目を追加した。担当美容師は,図3に示す従業員表の従業員コードを外部キーとして参照する。指名美容師がいない会員の場合,会員表の担当美容師としてNULLを設定する。
情報の追加後,従業員コード“2008005”の美容師が所属する店舗が変わるので,会員表の担当美容師に登録している会員と,当該美容師が過去に担当したことがある会員に,所属店舗移動の案内を送付することにした。該当する会員の会員番号を表示する次のSQL文中の[ ]に入れる正しい答えを,解答群の中から選べ。
図3 従業員表とデータの格納例
SELECT DISTINCT 会員表.会員番号,会員表.氏名,会員表.住所
FROM 会員表,会計表
WHERE [ d ]
dに関する解答群
ある地域で5店舗のヘアサロンを経営しているZ社では,会員登録した顧客に対して紙製の会員証を発行し,氏名や住所などの会員情報は表計算ソフトを利用して管理していた。今回,顧客サービスの向上を目的に,会員情報を管理するシステム(以下,管理システムという)を導入し,会員証のICカード化を行うとともに会員情報と来店記録のデータベース化を実施した。
管理していた会員情報の例とその形式は,図1のとおりである。
| 会員番号 | 氏名 | 住所 | 電話番号 | 性別 | 生年月日 |
| 00001 | 情報花子 | 東京都文京区桜丘2-28 | 03-1111-2222 | 女性 | 19870613 |
まず,会員情報と来店記録を管理するために図2に示すデータベースを設計した。下線付きの項目は主キーを表す。
| 会員番号 | 氏名 | 住所 | 電話番号 | 性別 | 生年月日 |
| 00001 | 情報花子 | 東京都文京区桜丘2-28 | 03-1111-2222 | 02 | 19870613 |
| 店舗コード | 店舗名 | 住所 | 電話番号 |
| 01 | 桜丘店 | 東京都文京区桜丘1-2 | 03-8888-9999 |
| メニューコード | 商品名 | 単価 |
| 001 | カット | 5000 |
| 002 | パーマ | 5000 |
| 会計コード | 会員番号 | 店舗コード | 来店日 | 会計金額 |
| 120001 | 00001 | 01 | 20130511 | 10000 |
| 会計コード | メニューコード |
| 120001 | 001 |
| 120001 | 002 |
設問1 会員特典として,ポイント制度を導入することにした。ポイント情報の管理に関する次の記述中の[ ]に入れる適切な答えを,解答群の中から選べ。
会員が料金を支払う際に会員証を提示すると,会計金額に応じて,千円につき1ポイントが付与される。会計時に付与されたポイント(以下,付与ポイントという)と,会員が現在保有しているポイント(以下,保有ポイントという)は,レシートに印字される。
会計の際,会員が希望すれば,保有ポイントと引き換えに表1に示す割引サービスを受けられる。このとき保有ポイントから引かれるポイントを利用ポイントという。付与ポイントは,割引後の料金から算出する。
| 利用ポイント数 | 割引率(%) |
| 10 | 5 |
| 20 | 15 |
| 30 | 30 |
管理システムで会員の保有ポイントを参照する機会が多いので,参照の都度,保有ポイントの集計処理を実行することは避けたい。保有ポイントを管理するのに適した表は,[ 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-5678 | 19850105 | 02 |
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
【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」でつないでいるので,両方に当てはまる会員しか表示されません。「ウ」と「エ」は,その美容師が所属する店舗に来店したすべての会員を表示してしまうので誤りです。