基本情報技術者過去問題平成25年春期午後問3(データベース)

基本情報技術者過去問題平成25年春期午後問3

問3　データベース

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

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

　管理していた会員情報の例とその形式は，図1のとおりである。

　まず，会員情報と来店記録を管理するために図2に示すデータベースを設計した。下線付きの項目は主キーを表す。

設問1

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

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

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

a，b に関する解答群

会員表
会計表
店舗表
明細表
メニュー表

解答選択欄

解答

a=ア
b=イ

解説

〔aについて〕
保有ポイントは会員ごとに管理される情報のため、それぞれの会員特有の属性と考えることができます。したがって会員表に追加するのが適切です。

∴a＝ア：会員表

〔bについて〕
ポイントの付与と利用は会計の度に行われるため、その履歴は会計に紐付けて管理するのが適切です。したがって会計表になります。

∴b＝イ：会計表

設問2

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

c に関する解答群

解答選択欄

解答

c=エ

解説

まずWHERE句の「店舗表.店舗コード＝会計表.店舗コード」で店舗表と会計表が結合され、次にGROUP BY句で店舗コードと店舗名ごとにグループ化されています。

店舗ごとの「売上金額」「延べ来客数」「会計コード単位の平均会計額」は、グループ化された表に対して以下の集計関数を適用することで求めることになります。

[売上金額]
店舗ごとにグループ化されているので、そのグループごとに合計金額を合算したものが店舗ごとの売上金額になります。
値の合計を集計するには、集計関数SUMを使用します。したがって「SUM(会計表.会計金額) AS 売上金額」が適切です。(集計関数SUMのALLはデフォルトの指定なので「SUM(ALL 会計表.会計金額) AS 売上金額」も正しい。)

[延べ来客数]
来客ごとに会計表のレコードが1つずつ追加されていくため、会計表のレコード数＝来客数となります。
グループ化された店舗ごとに存在するレコード数を数えるには、集計関数COUNTを使用します。したがって「COUNT(会計表.会計コード) AS 延べ来客数」が適切です。(COUNT(*) AS 延べ来客数でもOK)

[会計コード単位の平均会計額]
会計コードごとの金額は会計表の"会計金額"列に記録されているので、グループ化された店舗ごとに会計金額の平均値を求めることになります。
平均値を求めるには、集計関数AVGを使用します。したがって「AVG(会計表.会計金額) AS 平均会計額」が適切です。

したがって正しい組合せは「エ」です。

「MAX(会計表.会員番号)」は、値が最も大きい会員番号を求める式で延べ来客数とは関係ありません。
また、「AVG(売上金額 / 延べ来客数)」ですが、集計関数の引数として他の集計関数の結果を使用することはできないため不適切です。
「MAX(会計表.会計コード)」は、値が最も大きい会計コードを求める式で延べ来客数とは関係ありません。
「AVG(売上金額 / 延べ来客数)」で、集計関数の引数として他の集計関数の結果を使用しているため不適切です。
正しい。

設問3

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

解答群

2013年になってから，1回以上，来店した会員
2013年になってから，1回も来店してない会員
2013年より前に，1回以上，来店した会員
2013年より前に，1回も来店してない会員

解答選択欄

解答

解説

GROUP BY句で会員表のレコードが会員ごとにグループ化されて、その中からHAVING句で条件を指定し絞り込みを行っています。

「MAX(会計表.来店日)」は、最も大きい"来店日"列の値を求める式で、その値はその顧客の直近の来店日を示しています。HAVING句では、この来店日を'20130101'と比較して値が小さい(<)グループのみに絞り込みが行われています。

直近の来店日が2013年1月1日より前ということは、2013年なってから一度も来店していないことを意味するため、このSQLによって表示される内容は「2013年になってから， 1回も来店してない会員」の会員番号、氏名になります。

∴イ：2013年になってから， 1回も来店してない会員

設問4

顧客サービスを向上させるために，"指名美容師"と"過去の来店時に担当した美容師"を確認できるよう，会員表と会計表に担当美容師の項目を追加した。担当美容師は，図3に示す従業員表の従業員コードを外部キーとして参照する。指名美容師がいない会員の場合，会員表の担当美容師として NULL を設定する。
　情報の追加後，従業員コード"2008005"の美容師が所属する店舗が変わるので，会員表の担当美容師に登録している会員と，当該美容師が過去に担当したことがある会員に，所属店舗移動の案内を送付することにした。該当する会員の会員番号を表示する次のSQL文中の　に入れる正しい答えを，解答群の中から選べ。

d に関する解答群

解答選択欄

解答

d=ア

解説

抽出すべき会員は「会員表の担当美容師に登録している会員と，当該美容師が過去に担当したことがある会員」なので、会員表の"担当美容師"列に"2008005"が設定されている会員と、会計表のレコードで"担当美容師"列が"2008005"の会計履歴をもつ会員の両方ということになります。

したがってWHERE句で指定する条件式は「会員表.担当美容師 = "2008005" OR 会計表.担当美容師 = "2008005"」が適切です。

正しい。
それぞれの条件式をANDで結合しているので、抽出されるのは「担当美容師が従業員コード"2008005"」かつ「現在"2008005"が所属する店舗で"2008005"に担当されたことがある」の条件を満たす会員のみになります。
担当美容師に関係なく、従業員コード"2008005"が所属している店舗に来客したことのあるすべての顧客が抽出対象となります。
「ウ」と同じく、従業員コード"2008005"が所属している店舗に来客したことのあるすべての顧客が抽出対象となります。(副問合せから返される結果は常に1レコードのみなのでANY句の指定は意味がありません。)

※「イ」「ウ」「エ」に共通している以下の副問合せは、従業員コード"2008005"が現に在籍している店の店舗コードを返します。

SELECT 店舗コード FROM 従業員表
　　WHERE 従業員コード = '2008005'

平成25年春期午後問題一覧

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　問12　問13　採点講評