基本情報技術者 2016年 春期 午後 問03
遊園地の入園者情報を管理する関係データベースに関する次の記述を読んで、設問1〜4に答えよ。
遊園地Yでは、各アトラクションの入り口にICカードの読取り機を設置して、入園者の利用状況を収集するシステムを導入した。入園者は、全てのアトラクションを追加料金なしで利用できるパスポートか、アトラクション利用の都度、料金が課金される入園券のいずれかを購入し、対応するICカードを受け取る。ICカードは退園時に料金を精算してから返却する。ここで、入園者は退園まで遊園地を出ることはないものとする。
遊園地Yでは、システム導入前は入園者の情報を図1に示す表で構成されるデータベースで管理していた。下線付きの項目は主キーを表す。
設問1：
問題文を見る システムの導入に当たり、データベースの表を図2に示すとおり再設計した。次の記述中のに入れる適切な答えを、解答群の中から選べ。
入園者表に退園時刻の項目を追加することによって、aが分かるようになった。また、利用表を追加することによって、bが分かるようになった。各アトラクションには一意のアトラクション番号を割り振って、利用表から分離したアトラクション表を作成した。
a, bに関する解答群
ア：アトラクションの待ち時間
イ：休園日
ウ：入園者数
エ：入園者のアトラクション利用状況
オ：入園者の滞在時間
模範解答
a：オ、b：エ
解説
解答の導き方
設問では、図2の表に追加された情報によって、何が分かるようになったかを考えます。
-
入園者表に退園時刻を加えると、滞在時間が分かります。入園者表には、入園日と入園時刻がすでに記録されています。そこに退園時刻が加わると、入園してから退園するまでの時間を求められます。つまり、入園者の滞在時間が分かるようになります。一方、アトラクションの待ち時間を知るには、列に並び始めた時刻なども必要です。入園者表に退園時刻を追加しただけでは、待ち時間までは分かりません。
-
利用表を加えると、入園者のアトラクション利用状況が分かります。利用表には、入園者番号、利用時刻、アトラクション番号が記録されています。このため、どの入園者が、いつ、どのアトラクションを利用したかを確認できます。アトラクション番号をアトラクション表と対応させれば、アトラクション名も分かります。したがって、利用表の追加によって分かるのは、入園者のアトラクション利用状況です。休園日は表に記録する情報として示されていません。また、入園者数は入園者表の行を数えることで把握できるため、利用表の追加によって新たに分かる情報ではありません。
誤りやすいポイント
- 退園時刻から分かるのは、入園してから退園するまでの滞在時間です。アトラクションの待ち時間と混同しないようにしましょう。
- 利用時刻だけでは、列に並んだ時間や待ち時間は分かりません。利用表の情報から分かるのは、いつ、どのアトラクションを利用したかです。
- 「入園者数」と「入園者のアトラクション利用状況」は別の情報です。利用表では、入園者ごとの利用記録を確認できます。
FAQ
Q: 滞在時間は、表に直接記録されているのですか？
A: 滞在時間そのものの項目があるわけではありません。入園日・入園時刻と退園時刻から、滞在した時間を求められます。
Q: 利用時刻から、アトラクションの待ち時間も分かりますか？
A: 分かりません。利用時刻は利用した時刻を示す情報で、列に並び始めた時刻などは記録されていないためです。
Q: アトラクション表を利用表と分けて作るのはなぜですか？
A: 利用表にはアトラクション番号を記録し、アトラクション名や料金はアトラクション表にまとめておけます。同じ名前や料金を利用記録ごとに繰り返して保存せずに済み、情報の管理がしやすくなります。
関連キーワード: 関係データベース、主キー、利用履歴、データの正規化
設問2：
問題文を見る アトラクションごとの延べ利用者数を表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 利用表.アトラクション番号, アトラクション表.アトラクション名,
c
FROM 利用表, アトラクション表
WHERE 利用表.アトラクション番号 = アトラクション表.アトラクション番号
GROUP BY 利用表.アトラクション番号, アトラクション表.アトラクション名
解答群
ア：AVG(利用表.アトラクション番号)
イ：COUNT(*)
ウ：MAX(利用表.アトラクション番号)
エ：SUM(利用表.アトラクション番号)
模範解答
c：イ
解説
解答の導き方
この設問では、アトラクションごとに何回利用されたかを数える方法を考えます。SQL（Structured Query Language：データベースを操作するための言語）のSELECT文で、集計方法を指定する部分が問われています。
-
何を数えるのかを確認します。
問題は「アトラクションごとの延べ利用者数」を表示するものです。「延べ」なので、同じ人が同じアトラクションを複数回利用した場合も、その利用回数分だけ数えます。利用した人の人数を重複なしで数えるのではありません。 -
利用表の1行が何を表すかを確認します。
利用表には、入園者番号、利用時刻、アトラクション番号が記録されています。つまり、利用表の1行は、ある入園者がある時刻にアトラクションを利用した記録です。利用回数を知るには、この行数を数えればよいと分かります。 -
アトラクションごとに数える仕組みを確認します。
GROUP BY句（指定した項目が同じ行をグループに分ける指定）では、アトラクション番号とアトラクション名を指定しています。そのため、利用記録はアトラクションごとのグループに分けられます。FROM句とWHERE句は、利用表とアトラクション表をアトラクション番号で対応付け、アトラクション名を取得するためのものです。 -
各グループの行数を数えます。
各アトラクションの利用回数は、そのアトラクションのグループに含まれる利用表の行数です。COUNT()は、対象となる行数を数える集約関数（複数の行をまとめて、件数や合計などを求める関数）です。したがって、cにはCOUNT()が入ります。解答群ではイです。AVGは平均、MAXは最大値、SUMは合計を求めます。アトラクション番号の平均・最大・合計では利用回数にならないため、これらは選びません。
誤りやすいポイント
-
利用した人の人数と、延べ利用者数を混同する
延べ利用者数は利用記録の件数です。同じ人の利用も、利用した回数分だけ数えます。 -
アトラクション番号を足したり、平均したりする
アトラクション番号は、どのアトラクションかを識別するための値です。利用回数を表す値ではありません。 -
GROUP BY句だけで件数も数えられると思う
GROUP BY句は行をグループに分けます。各グループの行数を求めるには、COUNT(*)のような集約関数が必要です。
FAQ
Q: COUNT(*)は何を数えますか？
A: 対象となる行の数を数えます。この設問では、利用表の1行が利用1回の記録なので、COUNT(*)で利用回数を数えられます。
A: 対象となる行の数を数えます。この設問では、利用表の1行が利用1回の記録なので、COUNT(*)で利用回数を数えられます。
Q: 同じ入園者が同じアトラクションを複数回利用した場合、どう数えますか？
A: 利用表に利用した回数分の記録があれば、その行数分だけ数えます。重複を除いた人数ではなく、利用の延べ回数を求めるためです。
A: 利用表に利用した回数分の記録があれば、その行数分だけ数えます。重複を除いた人数ではなく、利用の延べ回数を求めるためです。
Q: なぜアトラクション番号とアトラクション名の両方でグループ分けするのですか？
A: アトラクション番号で利用記録をアトラクションごとに分け、アトラクション名も結果に表示するためです。
A: アトラクション番号で利用記録をアトラクションごとに分け、アトラクション名も結果に表示するためです。
関連キーワード: SQL、集約関数、COUNT関数、GROUP BY句、表の結合
設問3：
問題文を見る 入園券でアトラクションを利用した入園者のうち、退園時の精算において2,000円以上を支払った入園者について、精算額が多い入園者から降順に、入園者番号と精算額を表示する。入園券の購入者は入園者表の券種に“01”が設定されている。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 入園者表.入園者番号, SUM(アトラクション表.料金) AS 支払金額
FROM 入園者表, 利用表, アトラクション表
WHERE 入園者表.入園者番号 = 利用表.入園者番号 AND
利用表.アトラクション番号 = アトラクション表.アトラクション番号 AND
d
ORDER BY 支払金額 DESC
解答群
ア：入園者表.券種 = '01' AND アトラクション表.料金 >= 2000 GROUP BY 利用表.アトラクション番号
イ：入園者表.券種 = '01' GROUP BY 入園者表.入園者番号 HAVING MAX(アトラクション表.料金) >= 2000
ウ：入園者表.券種 = '01' GROUP BY 入園者表.入園者番号 HAVING SUM(アトラクション表.料金) >= 2000
エ：入園者表.券種 = '01' GROUP BY 利用表.アトラクション番号 HAVING SUM(アトラクション表.料金) >= 2000
模範解答
d：ウ
解説
解答の導き方
この設問では、入園券を購入した人について、アトラクションの利用料金を合計し、合計が2,000円以上の人を支払金額の多い順に表示します。
-
入園券を購入した人に絞ります問題文には、入園券の購入者は入園者表の券種に「01」が設定されているとあります。そのため、対象者を選ぶ条件は、入園者表.券種 = '01' です。WHEREは、集計する前に各行を条件で絞る指定です。券種は入園者ごとの情報なので、この条件はWHEREに入れます。
-
利用者とアトラクションの料金を対応づけますSQL（Structured Query Language：データベースにデータを問い合わせる言語）では、複数の表を共通する項目で対応づけられます。これを表結合といいます。この問題では、入園者表と利用表を入園者番号で対応づけ、利用表とアトラクション表をアトラクション番号で対応づけています。これによって、誰がどのアトラクションを利用し、その料金がいくらかをたどれます。
-
入園者ごとに料金を合計します利用表には、同じ入園者が利用したアトラクションが複数行にわたって記録されます。入園者ごとの合計を出すには、入園者番号で行をまとめます。GROUP BYは、指定した項目の値が同じ行をひとまとめにする指定です。ここでは、GROUP BY 入園者表.入園者番号 とします。合計を求めるSUM（数値を合計する集約関数）の対象は、アトラクション表.料金です。したがって、各入園者の支払金額は SUM(アトラクション表.料金) で求められます。
-
合計額が2,000円以上の人に絞ります条件にある2,000円以上は、個々のアトラクションの料金ではなく、入園者ごとの合計額です。グループを作って合計した後の値を条件にするには、HAVINGを使います。条件は HAVING SUM(アトラクション表.料金) >= 2000 です。券種の条件、入園者番号によるグループ化、合計額の条件をすべて含むのは、解答群のウです。
-
支払金額の多い順に並べますSQL文にある ORDER BY 支払金額 DESC は、支払金額を降順、つまり多い順に並べる指定です。支払金額は、SELECT句の SUM(アトラクション表.料金) AS 支払金額 で付けた名前です。
誤りやすいポイント
- アは、アトラクション1回分の料金が2,000円以上かどうかを調べています。必要なのは、入園者ごとに合計した料金が2,000円以上かどうかです。また、利用表.アトラクション番号でグループ化しているため、入園者ごとの集計にもなりません。
- イは、MAX(アトラクション表.料金)で最も高い1回分の料金を調べています。MAXは最大値を求める関数ですが、この設問で必要なのは複数回の利用料金の合計です。
- エは、利用表.アトラクション番号でグループ化しています。同じアトラクションを利用した別々の入園者が同じグループに入ってしまうため、入園者ごとの支払金額を求められません。
- WHEREとHAVINGを取り違えないようにします。WHEREは集計前の行を絞り、HAVINGは集計後のグループを絞ります。
FAQ
Q: なぜ入園者番号でグループ化するのですか？
A: 表示したいのが入園者ごとの支払金額だからです。入園者番号でグループ化すると、同じ人の利用料金だけを合計できます。
A: 表示したいのが入園者ごとの支払金額だからです。入園者番号でグループ化すると、同じ人の利用料金だけを合計できます。
Q: 券種の条件もHAVINGに書きますか？
A: いいえ。券種は各入園者の行にある情報なので、集計前にWHEREで絞ります。HAVINGには、SUMのような集計結果に対する条件を書きます。
A: いいえ。券種は各入園者の行にある情報なので、集計前にWHEREで絞ります。HAVINGには、SUMのような集計結果に対する条件を書きます。
Q: DESCは何を表しますか？
A: 降順を表します。このSQL文では、支払金額が多い入園者から表示します。
A: 降順を表します。このSQL文では、支払金額が多い入園者から表示します。
関連キーワード: SQL、表結合、GROUP BY、HAVING、集約関数、ORDER BY
設問4：
問題文を見る パスポートを購入する際に提示することで料金が割引になる会員証を発行することになった。そこで、図3に示すとおり、会員情報を格納する会員表を作成し、入園者表に会員番号を格納する項目を追加する。次のSQL文で抽出できるようになる会員についての正しい答えを、解答群の中から選べ。ここで、1回も入園していない会員はいないものとする。また、会員登録をしていない入園者の場合、入園者表の会員番号にはNULLを設定する。
SELECT 会員表.会員番号, 会員表.氏名
FROM 会員表, 入園者表
WHERE 会員表.会員番号 = 入園者表.会員番号
GROUP BY 会員表.会員番号, 会員表.氏名
HAVING MAX(入園者表.入園日) < '20160101'
解答群
ア：2015年以前に1回も入園していない会員
イ：2016年以降に1回以上、入園した会員
ウ：2016年以降に1回だけ、入園した会員
エ：2016年以降に1回も入園していない会員
模範解答
エ
解説
解答の導き方
このSQL（Structured Query Language：データベースに問い合わせるための言語）は、「2016年以降に入園した記録がない会員」を探しています。条件を順に見ていきます。
-
会員と入園記録を対応付けます。FROM句とWHERE句では、会員番号が一致する行を選んでいます。会員表.会員番号 = 入園者表.会員番号これは、会員表と入園者表を会員番号で結び付ける処理です。このように表どうしを結び付けることを結合といいます。入園者表には会員番号がNULL（値が入っていないことを表す値）の行もありますが、会員番号が一致しないため、その行は対象になりません。また、「1回も入園していない会員はいない」とあるので、どの会員にも少なくとも1件の入園記録があります。したがって、会員と入園記録を結び付けたときに、入園記録がないために対象から外れる会員はいません。
-
会員ごとに入園記録をまとめます。GROUP BY句は、同じ会員番号と氏名の行を会員ごとにまとめます。1人の会員に複数の入園記録があっても、ここではその会員の記録をまとめて扱います。
-
会員ごとの最も新しい入園日を調べます。MAX(入園者表.入園日)は、その会員の入園日のうち最も大きい値を求めます。入園日が「YYYYMMDD」の形式なので、最も大きい値は最も新しい日付を表します。たとえば、入園記録に2015年の日付と2016年の日付の両方があれば、MAXで得られるのは2016年の日付です。
-
最も新しい入園日が2016年より前の会員だけを残します。HAVING句は、会員ごとにまとめた後、そのグループが条件を満たすかを調べます。条件は次のとおりです。MAX(入園者表.入園日) < '20160101'最も新しい入園日が20160101より前なら、その会員の入園記録はすべて2016年より前です。反対に、2016年以降の入園記録が1件でもあれば、最も新しい入園日は20160101以降になり、条件を満たしません。
したがって、このSQLで抽出されるのは、2016年以降に1回も入園していない会員です。解答群ではエに当たります。
誤りやすいポイント
- MAXは、入園回数や入園日が条件に合う記録の件数を数えるものではありません。会員ごとに、最も新しい入園日を求めます。
- 条件は「2016年以降に入園した記録があるか」ではなく、「最も新しい入園日が20160101より前か」です。最も新しい日付が2016年より前なら、2016年以降の記録はありません。
- 条件に「<」が使われているため、20160101ちょうどの入園日は条件を満たしません。
- 「2015年以前に1回も入園していない」ではありません。2015年以前に何度入園していても、2016年以降の記録がなければ抽出されます。
FAQ
Q: 2016年以降の入園記録が1件でもあると、なぜ抽出されないのですか？
A: その記録の日付以上の値がMAXで得られるからです。したがって、最も新しい入園日が20160101より前という条件を満たしません。
A: その記録の日付以上の値がMAXで得られるからです。したがって、最も新しい入園日が20160101より前という条件を満たしません。
Q: GROUP BY句がなければ、どうなりますか？
A: 会員ごとのまとまりができず、会員単位で最も新しい入園日を判定できません。GROUP BY句によって、会員ごとにMAXを求められます。
A: 会員ごとのまとまりができず、会員単位で最も新しい入園日を判定できません。GROUP BY句によって、会員ごとにMAXを求められます。
Q: 入園者表の会員番号がNULLの行は、なぜ抽出されないのですか？
A: 会員表の会員番号と一致する値がないためです。このSQLは会員番号が一致する行を対象にしています。
A: 会員表の会員番号と一致する値がないためです。このSQLは会員番号が一致する行を対象にしています。
関連キーワード: 関係データベース、内部結合、GROUP BY、集約関数、HAVING
