基本情報技術者 2013年 春期 午後 問03
会員情報を管理する関係データベースの設計と運用に関する次の記述を読んで、設問1〜4に答えよ。
ある地域で5店舗のヘアサロンを経営しているZ社では、会員登録した顧客に対して紙製の会員証を発行し、氏名や住所などの会員情報は表計算ソフトを利用して管理していた。今回、顧客サービスの向上を目的に、会員情報を管理するシステム（以下、管理システムという）を導入し、会員証のICカード化を行うとともに会員情報と来店記録のデータベース化を実施した。
管理していた会員情報の例とその形式は、図1のとおりである。
まず、会員情報と来店記録を管理するために図2に示すデータベースを設計した。下線付きの項目は主キーを表す。
設問1：
問題文を見る 会員特典として、ポイント制度を導入することにした。ポイント情報の管理に関する次の記述中のに入れる適切な答えを、解答群の中から選べ。
会員が料金を支払う際に会員証を提示すると、会計金額に応じて、千円につき1ポイントが付与される。会計時に付与されたポイント（以下、付与ポイントという）と、会員が現在保有しているポイント（以下、保有ポイントという）は、レシートに印字される。
会計の際、会員が希望すれば、保有ポイントと引き換えに表1に示す割引サービスを受けられる。このとき保有ポイントから引かれるポイントを利用ポイントという。付与ポイントは、割引後の料金から算出する。
管理システムで会員の保有ポイントを参照する機会が多いので、参照の都度、保有ポイントの集計処理を実行することは避けたい。保有ポイントを管理するのに適した表は、aである。また、会員がポイントをどのように活用しているかの傾向を分析できるよう、ポイントの付与と利用の履歴を管理したい。付与ポイント及び利用ポイントを管理するのに適した表は、bである。
解答群
ア：会員表
イ：会計表
ウ：店舗表
エ：明細表
オ：メニュー表
模範解答
a：ア、b：イ
解説
解答の導き方
まず、設問では「現在保有しているポイント」と「ポイントを付与・利用した履歴」の二つを、別々に管理する表を選びます。
関係データベース（情報を複数の表に分けて管理する仕組み）では、表（テーブル）ごとに記録する情報を分けます。図2の会員表は会員ごとの情報を、会計表は会計ごとの情報を記録する表です。
a：現在の保有ポイント
本文には、保有ポイントを参照する機会が多く、参照のたびに集計処理を実行することは避けたいとあります。集計処理とは、複数の記録を足し合わせて合計を求める処理です。
付与ポイントと利用ポイントを会計ごとに記録し、毎回それらを集計して保有ポイントを求める方法では、参照のたびに過去の会計記録を確認する必要があります。そこで、会員ごとの現在の保有ポイントを、会員ごとの情報を管理する会員表に記録します。会員表を見れば、その会員の保有ポイントをすぐに確認できます。
b：付与ポイントと利用ポイントの履歴
ポイントの付与や利用は、会員が会計を行うたびに発生します。図2の会計表には、会員番号、来店日、会計金額などが会計ごとに記録されています。そのため、付与ポイントと利用ポイントも会計ごとに記録するのが適切です。
また、本文では付与ポイントを「割引後の料金」から算出するとあります。ポイントの利用と付与は、どちらも会計内容と結び付いているため、付与ポイントと利用ポイントは会計表に記録します。これにより、会員がいつ、どのくらいポイントを利用し、付与されたかを後から確認できます。
明細表は会計ごとのメニューを記録する表です。ポイントはメニューごとではなく会計ごとに扱うため、ポイントの履歴を記録する表としては会計表が適切です。
誤りやすいポイント
- 保有ポイントを会計表だけから求めようとすることです。会計表にポイントの履歴だけを記録すると、現在の保有ポイントを知るたびに履歴を集計することになります。設問は、その集計を避けたいと述べています。
- ポイントは会員に属する情報だから、履歴もすべて会員表に記録すればよいと考えることです。会員表に現在の保有ポイントを記録するだけでは、会計ごとの付与・利用の履歴は分かりません。
- 明細表を選ぶことです。明細表は会計に含まれるメニューを記録するための表であり、ポイントの付与・利用を会計単位で記録する目的には合いません。
FAQ
Q: 保有ポイントとポイントの履歴は、なぜ別の表で管理するのですか？
A: 保有ポイントは「現在の状態」、付与・利用の記録は「過去に何が起きたか」を表す情報だからです。会員表に現在の状態を置き、会計表に履歴を残すと、それぞれを確認しやすくなります。
A: 保有ポイントは「現在の状態」、付与・利用の記録は「過去に何が起きたか」を表す情報だからです。会員表に現在の状態を置き、会計表に履歴を残すと、それぞれを確認しやすくなります。
Q: 会計表に付与ポイントと利用ポイントを記録しておけば、会員表の保有ポイントは不要ですか？
A: 不要ではありません。履歴から保有ポイントを求めることはできますが、参照のたびに集計が必要になります。設問ではその処理を避けたいので、会員表にも現在の保有ポイントを記録します。
A: 不要ではありません。履歴から保有ポイントを求めることはできますが、参照のたびに集計が必要になります。設問ではその処理を避けたいので、会員表にも現在の保有ポイントを記録します。
Q: 明細表にポイントを記録しないのはなぜですか？
A: 明細表は、会計に含まれるメニューごとの情報を記録する表だからです。ポイントは会計金額や割引後の料金に基づいて会計ごとに扱うため、会計表に記録します。
A: 明細表は、会計に含まれるメニューごとの情報を記録する表だからです。ポイントは会計金額や割引後の料金に基づいて会計ごとに扱うため、会計表に記録します。
関連キーワード: 関係データベース、テーブル、集計処理、履歴管理
設問2：
問題文を見る 2012年9月にデータベース及び管理システムの開発が完了し、10月から運用を開始した。2012年12月について、店舗ごとの売上金額、延べ来客数、会計コード単位の平均会計額を表示したい。次のSQL文中のに入れる正しい答えを、解答群の中から選べ。ここで、売上金額は会計金額の合計とする。ポイントによる割引サービスを利用した場合は、会計表の会計金額には割引後の金額が設定されている。
SELECT 店舗表.店舗コード, 店舗表.店舗名,
c
FROM 店舗表, 会計表
WHERE 店舗表.店舗コード = 会計表.店舗コード AND
会計表.来店日 BETWEEN '20121201' AND '20121231'
GROUP BY 店舗表.店舗コード, 店舗表.店舗名
解答群
ア：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 平均会計額
模範解答
c：エ
解説
解答の導き方
このSQLでは、2012年12月のデータを店舗ごとにまとめて、「売上金額」「延べ来客数」「平均会計額」を求めます。SQL（Structured Query Language：データベースにデータを問い合わせるための言語）の集計方法を、項目ごとに考えます。
-
まず、集計するデータを絞り込みます。
WHERE句では、店舗表と会計表の店舗コードが同じ行を対応付けています。また、「来店日 BETWEEN '20121201' AND '20121231'」によって、2012年12月1日から2012年12月31日までの来店記録を対象にします。BETWEENで指定した両端の日付も対象です。 -
店舗ごとに集計します。
GROUP BYは、指定した項目の値が同じ行をひとまとめにする指定です。ここでは店舗コードと店舗名でまとめるので、集計結果は店舗ごとに1行になります。 -
売上金額には、会計金額の合計を使います。
問題文では「売上金額は会計金額の合計」とされています。したがって、会計表の会計金額をSUMで合計します。ポイント割引後の金額が会計表に設定されているため、割引後の金額をそのまま合計します。SUM(会計表.会計金額)は、同じ金額の会計が複数あっても、それぞれを合計に含めます。 -
延べ来客数には、会計の件数を使います。
延べ来客数は、同じ人が何度も来店した場合、その来店ごとに数えます。会員番号の種類数ではなく、会計の件数を数える必要があります。図2では、会計表の会計コードが主キー（表の中で1行を特定する項目）として示されています。会計コードは会計ごとに付くため、COUNT(会計表.会計コード)で会計の件数を数えられます。 -
平均会計額には、会計金額の平均を使います。
会計ごとの金額の平均なので、会計表の会計金額にAVGを適用します。たとえば会計金額が10000と5000の2件なら、平均会計額は です。SQLではAVG(会計表.会計金額)と表せます。
このように、売上金額にはSUM、延べ来客数にはCOUNT、平均会計額にはAVGを使うため、空欄に入るのはエの式です。
誤りやすいポイント
-
会員番号を数えれば延べ来客数になる、と考えること
同じ会員が複数回来店することがあります。会員番号ではなく、会計の件数を数えます。 -
MAXを件数の計算に使うこと
MAXは値の最大値を求める関数です。会員番号や会計コードの最大値を求めても、来客数にはなりません。 -
COUNT(DISTINCT 会計表.会計コード)を必ず誤りと判断すること
DISTINCTは重複する値を除いて数える指定です。図2では会計コードが主キーなので、この問題ではCOUNTと同じ件数になります。ただし、ウは平均会計額の式が適切ではありません。平均会計額は、売上金額などの別名を使うのではなく、個々の会計金額にAVGを適用して求めます。 -
SUM(ALL ...)とSUM(...)を別の集計だと考えること
SUMのALLは、重複した値も合計に含める指定です。SUM(会計表.会計金額)も重複した値を含めるため、売上金額の合計に使えます。
FAQ
Q: 同じ会員が同じ月に何度も来店した場合、来客数はどう数えますか？
A: 来店ごとに数えます。会計コードは会計ごとに付くため、会計コードの件数を数えます。
A: 来店ごとに数えます。会計コードは会計ごとに付くため、会計コードの件数を数えます。
Q: 会計コードが主キーであることは、なぜ関係しますか？
A: 主キーは行を特定する項目です。会計コードが主キーなので、会計表では会計コードごとに1行です。そのため、会計コードをCOUNTで数えると会計の件数になります。
A: 主キーは行を特定する項目です。会計コードが主キーなので、会計表では会計コードごとに1行です。そのため、会計コードをCOUNTで数えると会計の件数になります。
Q: 割引前の金額から売上金額を計算しますか？
A: いいえ。問題文では、会計表の会計金額に割引後の金額が設定されているとあります。したがって、その金額を合計し、平均します。
A: いいえ。問題文では、会計表の会計金額に割引後の金額が設定されているとあります。したがって、その金額を合計し、平均します。
関連キーワード: GROUP BY、集約関数、主キー、SUM、COUNT、AVG
設問3：
問題文を見る 会員の来店を促す目的で、次のSQL文によって表示される会員に対して、割引券を送付することにした。表示される会員の説明として適切な答えを、解答群の中から選べ。ここで、会員は、1度は来店したことがあるものとする。
SELECT 会員表.会員番号, 会員表.氏名
FROM 会員表, 会計表
WHERE 会員表.会員番号 = 会計表.会員番号
GROUP BY 会員表.会員番号, 会員表.氏名
HAVING MAX(会計表.来店日) < '20130101'
解答群
ア：2013年になってから、1回以上、来店した会員
イ：2013年になってから、1回も来店してない会員
ウ：2013年より前に、1回以上、来店した会員
エ：2013年より前に、1回も来店してない会員
模範解答
イ
解説
解答の導き方
このSQL文は、来店記録を調べて、条件に合う会員の会員番号と氏名を表示します。SQL（Structured Query Language：データベースに検索や更新を指示する言語）の条件を順に見ていきます。
まず、会員表と会計表を会員番号で結び付けています。そのため、会計表に来店記録がある会員が対象になります。
次に、GROUP BY（同じ値ごとに行をまとめる指定）によって、会員番号と氏名が同じ来店記録を会員ごとにまとめています。1人の会員に複数の来店記録があっても、会員ごとに1つのグループとして扱います。
そのグループごとに、集約関数（複数の行の値をまとめて計算する関数）であるMAXを使い、最も新しい来店日を調べています。MAX(会計表.来店日) は、その会員の「最後に来店した日」です。
最後の条件は、HAVING（グループを条件で絞る指定）にある「MAX(会計表.来店日) < '20130101'」です。来店日の形式は「20130511」のように年月日を並べているため、この条件は「最後の来店日が20130101より前」という意味です。つまり、2013年になってから来店した記録がある会員は、最後の来店日が2013年以降になるので条件から外れます。
したがって、表示されるのは2013年になってから一度も来店していない会員です。設問には「会員は、1度は来店したことがある」とあるので、表示対象の会員は2013年より前には来店しています。ただし、ウの「2013年より前に、1回以上、来店した会員」だけでは、2013年以降にも来店した会員が含まれる可能性があります。このSQL文が選ぶ会員の説明として適切なのは、イです。
誤りやすいポイント
- 「2013年より前に来店したことがある」と「2013年になってから来店していない」は同じ意味ではありません。2013年より前に来店し、さらに2013年以降にも来店した会員は、前者には当てはまりますが、このSQL文では対象になりません。
- MAXは「最も古い来店日」ではなく、「最も新しい来店日」を求めます。最後の来店日が20130101より前かどうかで判定します。
- HAVINGの条件は、来店記録1件ずつではなく、会員ごとにまとめた後の結果に適用されます。
FAQ
Q: 2013年より前に来店した会員なら、全員表示されますか？
A: いいえ。2013年以降にも来店した会員は、最後の来店日が20130101より後になるため、表示されません。
A: いいえ。2013年以降にも来店した会員は、最後の来店日が20130101より後になるため、表示されません。
Q: なぜ最後の来店日を調べるのですか？
A: 最後の来店日が20130101より前なら、それより新しい来店記録もありません。そのため、2013年になってから来店していないと判断できます。
A: 最後の来店日が20130101より前なら、それより新しい来店記録もありません。そのため、2013年になってから来店していないと判断できます。
Q: 来店したことがない会員は表示されますか？
A: 設問では、会員は「1度は来店したことがある」としています。また、このSQL文は会計表の来店記録と結び付けて会員を調べるため、来店記録のない会員は対象になりません。
A: 設問では、会員は「1度は来店したことがある」としています。また、このSQL文は会計表の来店記録と結び付けて会員を調べるため、来店記録のない会員は対象になりません。
関連キーワード: SQL、GROUP BY、集約関数、MAX、HAVING
設問4：
問題文を見る 顧客サービスを向上させるために、“指名美容師”と“過去の来店時に担当した美容師”を確認できるよう、会員表と会計表に担当美容師の項目を追加した。担当美容師は、図3に示す従業員表の従業員コードを外部キーとして参照する。指名美容師がいない会員の場合、会員表の担当美容師としてNULLを設定する。
情報の追加後、従業員コード“2008005”の美容師が所属する店舗が変わるので、会員表の担当美容師に登録している会員と、当該美容師が過去に担当したことがある会員に、所属店舗移動の案内を送付することにした。該当する会員の会員番号を表示する次のSQL文中のに入れる正しい答えを、解答群の中から選べ。
SELECT DISTINCT 会員表.会員番号, 会員表.氏名, 会員表.住所
FROM 会員表, 会計表
WHERE 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')
模範解答
d：ア
解説
解答の導き方
このSQL（Structured Query Language：データベースのデータを検索・操作する言語）で表示したいのは、次のどちらかに当てはまる会員です。
- 会員表の担当美容師が「2008005」である
- 会計表の担当美容師が「2008005」である
前者は現在の指名美容師、後者は過去の来店時に担当した美容師を表します。どちらか一方に当てはまればよいので、条件は「OR」でつなぎます。
選択肢アは、会員番号で会員表と会計表を対応させたうえで、会員表の担当美容師または会計表の担当美容師が「2008005」かどうかを調べています。問題で対象としている二つの条件を、どちらか一方でも満たす会員を選ぶ形です。
また、同じ会員が複数回担当されていると、会計表の行ごとに同じ会員が何度も検索結果に現れることがあります。SELECT DISTINCTは、検索結果の重複を取り除く指定です。そのため、会員番号を重複させずに表示できます。
ただし、このSQLは会員表と会計表を会員番号で結び付けています。このように、両方の表に対応する行がある場合だけを残す結合を内部結合といいます。そのため、会計表に来店記録がない会員は、会員表の担当美容師が「2008005」であっても検索結果に含まれません。選択肢アは、提示された選択肢の中では求める二つの条件に合っていますが、会計表に記録がない会員まで必ず含めるSQLではない点に注意してください。
ほかの選択肢は、次の理由で適しません。
- イは、現在の担当美容師と過去の担当美容師の両方が「2008005」であることを求めています。どちらか一方が該当すればよい、という条件よりも厳しくなっています。また、会計表の店舗コードも現在の所属店舗と一致することを求めています。
- ウとエは、従業員表から得た店舗コードと会計表の店舗コードを比べています。店舗が同じでも、その美容師が担当したとは限らないため、担当美容師を確認する条件にはなりません。
誤りやすいポイント
- 「現在の担当」か「過去の担当」のどちらかを対象にするので、条件はANDではなくORです。
- 店舗コードが一致することと、特定の美容師が担当したことは別の情報です。担当した美容師を調べるには、担当美容師の項目を確認します。
- DISTINCTがあっても、会計表に対応する行がない会員は内部結合で除外されます。重複を除く指定は、結合で除外された行を戻すものではありません。
FAQ
Q: 会計表に来店記録がない会員も、会員表の担当美容師が「2008005」なら表示されますか。
A: 表示されません。会員表と会計表を会員番号で結び付けているため、会計表に対応する行がない会員は結果に含まれません。
A: 表示されません。会員表と会計表を会員番号で結び付けているため、会計表に対応する行がない会員は結果に含まれません。
Q: DISTINCTは何のために使っていますか。
A: 同じ会員が複数の会計記録に該当すると、結合後にその会員が複数行になることがあります。DISTINCTは、同じ内容の行を一つにまとめるために使います。
A: 同じ会員が複数の会計記録に該当すると、結合後にその会員が複数行になることがあります。DISTINCTは、同じ内容の行を一つにまとめるために使います。
Q: 同じ店舗に所属している美容師が担当したと判断できないのはなぜですか。
A: 店舗コードが表すのは店舗であり、会計表の担当美容師が表すのは担当者です。店舗が一致しても、誰が担当したかまでは分かりません。
A: 店舗コードが表すのは店舗であり、会計表の担当美容師が表すのは担当者です。店舗が一致しても、誰が担当したかまでは分かりません。
関連キーワード: SQL、内部結合、論理演算、DISTINCT、重複排除
