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

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

問3　データベース

遊園地の入園者情報を管理する関係データベースに関する次の記述を読んで，設問1～4に答えよ。

　遊園地Yでは，各アトラクションの入り口にICカードの読取り機を設置して，入園者の利用状況を収集するシステムを導入した。入園者は，全てのアトラクションを追加料金なしで利用できるパスポートか，アトラクション利用の都度，料金が課金される入園券のいずれかを購入し，対応するICカードを受け取る。ICカードは退園時に料金を精算してから返却する。ここで，入園者は退園まで遊園地を出ることはないものとする。
　遊園地Yでは，システム導入前は入園者の情報を図1に示す表で構成されるデータベースで管理していた。下線付きの項目は主キーを表す。

設問1

システムの導入に当たり，データベースの表を図2に示すとおり再設計した。次の記述中の　に入れる適切な答えを，解答群の中から選べ。

　入園者表に退園時刻の項目を追加することによって，aが分かるようになった。また，利用表を追加することによって，bが分かるようになった。各アトラクションには一意のアトラクション番号を割り振って，利用表から分離したアトラクション表を作成した。

a，b に関する解答群

アトラクションの待ち時間
休園日
入園者数
入園者のアトラクション利用状況
入園者の滞在時間

解答選択欄

解答

a=オ
b=エ

解説

〔aについて〕
入園者表にはシステム導入前から"入園時刻"の項目があります。表に"退園時刻"の項目を加えることで以下のように入園者の滞在時間を計算できるようになります。

　入園者の滞在時間＝退園時刻－入園時刻

∴a＝オ：入園者の滞在時間

〔bについて〕
利用表には"利用時刻"と"アトランクション番号"の項目があり、入園者がいつ、どのアトラクションを利用したかの情報が格納されていきます。これによってシステム導入前は不明だった入園者のアトラクション利用状況(入園者ごとの利用回数、回転率、アトラクションごとの人気など)が分かるようになります。なおアトラクションの待ち時間については、表にはアトラクションの待ち行列に到着した時刻が記録されていないため測定することはできません。

∴b＝エ：入園者のアトラクション利用状況

設問2

アトラクションごとの延べ利用者数を表示する。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。

c に関する解答群

AVG(利用表.アトラクション番号)
COUNT(*)
MAX(利用表.アトラクション番号)
SUM(利用表.アトラクション番号)

解答選択欄

解答

c=イ

解説

WHERE句で利用者表とアトラクション表を結合した後、GROUP BY句でアトラクションごとにグルーピングが行われています。利用表にはアトラクション1回の利用につき1レコードが追加されていくため、述べ利用者数を知るにはグループごとのレコード数を数えます。COUNT(*)は、グループにおける行(レコード)数を返す集計関数なので、述べ人数を表示する列としてSELECT句にCOUNT(*)を追加します。

∴c＝イ：COUNT(*)

SQLの処理の流れは次のようになります。

WHERE 利用者表.アトラクション名 = アトラクション表.アトラクション名
→"アトラクション名"列で利用者表とアトラクション表を結合
GROUP BY 利用者表.アトラクション番号, アトラクション表.アトラクション名
→結合した表をアトラクション番号とアトラクション名でグループ化
COUNT(*)でグループごとに行数を数える
SELECT句で"アトラクション番号"列、"アトラクション名"列、COUNT(*)列を抽出して表示する

AVG()はグループ内での平均を返す集計関数です。
正しい。
MAX()はグループ内での最大値を返す集計関数です。
SUM()はグループ内での合計値を返す集計関数です。

設問3

入園券でアトラクションを利用した入園者のうち，退園時の精算において2,000円以上を支払った入園者について，精算額が多い入園者から降順に，入園者番号と精算額を表示する。入園券の購入者は入園者表の券種に"01"が設定されている。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。

d に関する解答群

解答選択欄

解答

d=ウ

解説

2,000円以上を支払った入園者を特定するためには、利用表をもとに入園者ごとの利用金額を算出する必要があります。

SQLでは「1.WHERE句で行を選択」→「2.GROUP BY句でグループ化」という順序で処理が行われるため、WHERE句には集計関数を使用した条件は指定できない決まりになっています。このケースのように、グループ化した後の集計値を行選択の条件に使いたい場合はHAVING句を使用することになります。

HAVING句を使用すると、「1.WHERE句で行を選択」→「2.GROUP BY句でグループ化」→「3.HAVING句でグループを選択」という処理の流れになるため、指定した条件に合致するグループだけを抽出対象とすることができます。この設問では入園者ごとの利用金額の合計が条件になるため、「GROUP BY 入園者表.入園者番号」で入園者ごとに行をグループ化し、「HAVING SUM(アトラクション表.料金) >= 2000」で"料金"列の合計が2,000円以上のグループだけを選択している「ウ」が適切なSQL文です。

なお、同じくHAVING SUM(…)を使用している「エ」ですが、GROUP BY句で指定していない列をSELECT句で使っているため構文エラーとなります。

∴d＝ウ

設問4

パスポートを購入する際に提示することで料金が割引になる会員証を発行することになった。そこで，図3に示すとおり，会員情報を格納する会員表を作成し，入園者表に会員番号を格納する項目を追加する。次のSQL文で抽出できるようになる会員についての正しい答えを，解答群の中から選べ。ここで，1回も入園していない会員はいないものとする。また，会員登録をしていない入園者の場合，入園者表の会員番号にはNULLを設定する。

解答群

2015年以前に1回も入園していない会員
2016年以降に1回以上，入園した会員
2016年以降に1回だけ，入園した会員
2016年以降に1回も入園していない会員

解答選択欄

解答

解説

設問3と同じように「GROUP BY → HAVING」が含まれています。

設問のSQL文は、会員表と入園者表を結合し、会員番号と会員名でグループ化した後、HAVING句で抽出条件を指定しています。グループ化によって入園記録が会員ごとに集まった状態になっているため、集計関数「MAX(入園者表.入園日)」は、その会員に関連付けられた入園記録の"入園日"列のうち、最も値が大きいもの、すなわち最後に入園した日付を返します。
HAVING句では、この最後の入園日が「20160101」より小さいグループを抽出対象としています。日付の値が「20160101」より小さいということは、最終入園日が2015年12月31日以前ということを示すため、このSQL文は2016年以降に1回も入園していない会員を表示するものだとわかります。

∴エ：2016年以降に1回も入園していない会員

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

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