基本情報技術者 2012年 春期 午後 問03
社員食堂の利用記録データベースの設計と運用に関する次の記述を読んで、設問1〜4に答えよ。
A社では、社員証のICカード化に伴い、社員証を用いた社員食堂の精算システムを構築することにした。トレーに載せた料理を精算機の前に置くと、料理皿に埋め込まれたICチップのデータから料金が計算され、合計金額が表示される。合計金額を確認した後に社員証をかざすと、精算ができる。精算データはデータベースに記録され、1か月分の精算額が、まとめて翌月の給料から引き落とされる。
A社のシステム部門では、精算データを記録するデータベースとして、当初、図1に示す表を設計した。
精算システムの機能に関して関係者にヒアリングした結果、給料からの引落し額の算出以外にも次に示す四つの要望が挙がった。
要望1：ある社員の、ある日の精算の明細を表示できること
要望2：ある日の売上合計額（精算額の合計）を算出できること
要望3：料理の一覧を表示できること
要望4：ある日の、ある料理の販売皿数を算出できること
そこで、四つの要望に対応できるように図2のとおり、1回の精算に対して一つの精算コードを割り当てた三つの表で構成するように設計を変更した。下線付きの項目は主キーを表す。
設問1：
問題文を見る 図1に示した表構成のままでも対応できる要望として正しい答えを、解答群の中から選べ。
解答群
ア：要望1
イ：要望2
ウ：要望3
エ：要望4
模範解答
イ
解説
解答の導き方
図1の表には、「社員番号」「日付」「精算額」が記録されています。表の横一列をレコード（1件分のデータ）と考えると、各レコードには、いつの精算かを示す日付と、その精算でいくら支払ったかを示す精算額があります。
要望2は、ある日の売上合計額を求めることです。日付がその日と一致するレコードを集め、精算額を合計すれば求められます。つまり、「その日の各レコードの精算額を足す」ことで対応できます。料理ごとの情報がなくても、精算額があれば売上の合計は計算できます。
一方、要望1の精算明細を表示するには、どの料理を何皿精算したかという情報が必要です。図1にはその情報がありません。要望3の料理一覧に必要な料理名も、要望4の販売皿数を数えるための料理や皿数も記録されていません。
したがって、図1の表構成のままで対応できるのは、日付と精算額を使って集計できる要望2です。
誤りやすいポイント
- 「精算額が記録されているので、精算の明細も分かる」と考えないようにします。精算額だけでは、料理ごとの内容や皿数までは分かりません。
- 要望2は料理の情報を集める問題ではなく、日付ごとに精算額を合計する問題です。
- 要望4の「販売皿数」と、要望2の「売上合計額」は別の集計です。金額だけでは、何皿売れたかは分かりません。
FAQ
Q: 表に「日付」と「精算額」があれば、なぜ売上合計額を求められるのですか？
A: 日付を使って対象の日のレコードを選び、そのレコードに記録された精算額を足せるからです。
A: 日付を使って対象の日のレコードを選び、そのレコードに記録された精算額を足せるからです。
Q: 精算額が分かれば、料理の明細も逆算できますか？
A: できません。同じ精算額になる料理の組合せが複数あり得ます。料理名や皿数の情報が表にないため、精算額だけから明細を特定できません。
A: できません。同じ精算額になる料理の組合せが複数あり得ます。料理名や皿数の情報が表にないため、精算額だけから明細を特定できません。
Q: 料理の一覧を表示するには、どんな情報が必要ですか？
A: 少なくとも料理名が必要です。図1には料理名を記録する項目がないため、一覧を表示できません。
A: 少なくとも料理名が必要です。図1には料理名を記録する項目がないため、一覧を表示できません。
関連キーワード: データベース、表、レコード、集計
設問2：
問題文を見る 料理名が“肉じゃが”の単価に誤りがあることが判明したので、購入者に差額を返金することになった。“肉じゃが”購入者の社員番号と購入皿数を求める。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 精算表.社員番号, SUM(明細表.皿数) AS 購入皿数
FROM 料理表, 精算表, 明細表
WHERE
解答群
ア：精算表.精算コード = 明細表.精算コード AND 明細表.料理コード = (SELECT 料理表.料理コード FROM 料理表 WHERE 料理表.料理名 = '肉じゃが')
イ：精算表.精算コード = 明細表.精算コード AND 明細表.料理コード = 料理表.料理コード GROUP BY 精算表.社員番号 HAVING 料理表.料理名 = '肉じゃが'
ウ：精算表.精算コード = 明細表.精算コード AND 明細表.料理コード = 料理表.料理コード AND 料理表.料理名 = '肉じゃが'
エ：精算表.精算コード = 明細表.精算コード AND 明細表.料理コード = 料理表.料理コード AND 料理表.料理名 = '肉じゃが' GROUP BY 精算表.社員番号
模範解答
エ
解説
解答の導き方
求めるのは、「肉じゃが」を購入した社員ごとの購入皿数です。社員番号を取り出す表と、料理名や皿数を取り出す表を、正しくつなぎ合わせる必要があります。
SQL（Structured Query Language：データベースに問い合わせるための言語）では、複数の表を、共通するコードが一致する行どうしでつなぐことを表結合といいます。図2の表構成から、必要なつなぎ方は二つです。
- 精算表と明細表は、精算コードが一致する行どうしをつなぎます。これで、精算した社員と明細を対応させられます。
- 明細表と料理表は、料理コードが一致する行どうしをつなぎます。これで、皿数と料理名を対応させられます。
そのうえで、料理表の料理名が「肉じゃが」の行だけに絞ります。ここまでの条件は、次のようになります。
精算表.精算コード = 明細表.精算コード
AND 明細表.料理コード = 料理表.料理コード
AND 料理表.料理名 = '肉じゃが'
最後に、社員番号ごとに購入皿数を合計します。SUMは、指定した数値を合計する集約関数です。複数の行を社員ごとにまとめるには、GROUP BYを使います。選択肢エでは、SELECT句にある「精算表.社員番号」をGROUP BYにも指定しているため、社員ごとの購入皿数を求められます。
SQLでは、検索条件を書くWHEREの後に、集計の単位を書くGROUP BYを置きます。したがって、二つの表結合条件と料理名の条件に続けて「GROUP BY 精算表.社員番号」とする選択肢エが適切です。
誤りやすいポイント
-
料理コードで表をつなぐ条件を忘れる精算表と明細表をつないだだけでは、明細の料理名が分かりません。明細表と料理表も、料理コードでつなぐ必要があります。
-
料理名で絞るだけで、社員ごとに集計できると思う料理名の条件は「肉じゃが」の明細を選ぶためのものです。社員ごとに購入皿数をまとめるには、別にGROUP BYが必要です。
-
選択肢アのサブクエリだけで十分だと考えるサブクエリで料理コードを調べても、外側のFROM句にある料理表と明細表を結び付ける条件がありません。そのため、料理表の行が余分に組み合わされ、皿数の合計が重複するおそれがあります。また、社員ごとにまとめるGROUP BYもありません。
-
選択肢イのGROUP BYやHAVINGをWHEREの中に置くWHEREは集計前の行を絞る場所です。GROUP BYやHAVINGはその後に置くため、WHEREの条件の中には書けません。
-
選択肢ウのように、合計を求めるだけで終えるSUMで皿数を合計するだけでは、社員ごとの合計になりません。社員番号ごとに分けるGROUP BYが必要です。
FAQ
Q: 料理名の条件もGROUP BYに入れる必要がありますか？
A: 必要ありません。料理名は、集計する対象を「肉じゃが」に絞る条件なのでWHEREに書きます。GROUP BYには、結果を分けて集計したい社員番号を指定します。
A: 必要ありません。料理名は、集計する対象を「肉じゃが」に絞る条件なのでWHEREに書きます。GROUP BYには、結果を分けて集計したい社員番号を指定します。
Q: HAVINGはどのようなときに使いますか？
A: HAVINGは、グループごとに集計した後、その集計結果を条件にして絞り込むときに使います。今回は料理名で集計前の明細を絞るため、WHEREを使います。
A: HAVINGは、グループごとに集計した後、その集計結果を条件にして絞り込むときに使います。今回は料理名で集計前の明細を絞るため、WHEREを使います。
Q: なぜ精算コードと料理コードの両方で表をつなぐのですか？
A: 精算コードは「どの精算の明細か」を対応させ、料理コードは「どの料理の明細か」を対応させるためです。それぞれ役割が異なるので、両方の対応付けが必要です。
A: 精算コードは「どの精算の明細か」を対応させ、料理コードは「どの料理の明細か」を対応させるためです。それぞれ役割が異なるので、両方の対応付けが必要です。
関連キーワード: 表結合、WHERE句、GROUP BY句、集約関数、HAVING句
設問3：
問題文を見る 次の記述中のに入れる適切な答えを、解答群の中から選べ。
A社の健康管理部門から、精算時に料理の合計カロリーを表示する機能と、ある社員の、ある期間における1回の精算当たりの平均カロリーを求める機能の追加を要望された。このため、料理表にカロリーの列を追加することにした。
しかし、平均カロリーを求めるにはaを結合しなければならないので、多くの検索と計算の処理が必要となることが予想できた。精算時に合計カロリーを計算するので、その情報を記録しておけば、検索及び計算量は少なくなる。そこで、bに、精算単位の合計カロリーの列を追加することにした。これによって、前述の平均カロリーを求める場合は、bだけを参照すればよいので、処理の高速化が期待できる。
解答群
ア：精算表
イ：精算表と明細表
ウ：精算表と料理表
エ：精算表と明細表と料理表
オ：明細表
カ：料理表
キ：料理表と明細表
模範解答
a：エ、b：ア
解説
解答の導き方
この設問では、平均カロリーを計算するためにどの表を使うのか、また計算を速くするためにどの表へ合計カロリーを記録するのかを考えます。
まず、料理表にカロリーの列を追加すると、料理ごとのカロリーが分かります。ただし、ある社員の、ある期間の平均カロリーを求めるには、料理ごとのカロリーをそのまま平均するのではありません。まず、精算ごとに合計カロリーを求めてから、その合計を平均します。
精算ごとの合計カロリーを計算するには、次の情報が必要です。
- 精算表から、誰がいつ精算したかを確認します。
- 明細表から、その精算でどの料理を何皿購入したかを確認します。
- 料理表から、それぞれの料理のカロリーを確認します。
明細表の皿数分だけ料理のカロリーを合計すれば、その精算の合計カロリーを求められます。たとえば、同じ料理を複数皿購入していれば、その料理のカロリーを皿数分加算します。
複数の表を、精算コードや料理コードなどの共通する項目で対応付けることを、表の結合といいます。今回は、社員と日付から対象の精算を探し、その精算の料理と皿数を調べ、料理ごとのカロリーを確認する必要があります。そのため、精算表、明細表、料理表の三つすべてを結合します。したがって、aは「エ」です。
次に、計算を少なくする方法を考えます。問題文には「1回の精算に対して一つの精算コード」とあります。精算表には精算ごとの情報が記録されているので、そこに精算単位の合計カロリーを追加できます。
合計カロリーを精算表に記録しておけば、平均を求めるときは、対象の社員と期間に当たる精算表の行を選び、その合計カロリーを平均すればよくなります。明細表や料理表を毎回調べて合計カロリーを計算する必要がありません。したがって、bは「精算表」です。
誤りやすいポイント
-
明細表と料理表だけでよいと考えること
明細表と料理表から料理ごとのカロリーは分かりますが、誰の、いつの精算なのかは分かりません。社員と日付を調べるために、精算表も必要です。 -
精算表と料理表だけでよいと考えること
どの精算でどの料理を購入したかを結び付けるのが明細表です。明細表を使わずに、精算と料理を対応付けることはできません。 -
料理ごとのカロリーをそのまま平均すること
求めるのは「1回の精算当たり」の平均です。料理ごとのカロリーを平均するのではなく、精算ごとに合計カロリーを求め、その精算ごとの合計を平均します。 -
合計カロリーを明細表に追加すると考えること
明細表は料理ごとの明細を記録する表です。精算全体の合計カロリーは、精算ごとに一行で管理する精算表に記録するのが適切です。
FAQ
Q: 合計カロリーを保存せず、毎回計算する方法ではいけないのですか？
A: 計算自体はできますが、精算表、明細表、料理表を結合して、精算ごとの合計を求める必要があります。合計カロリーをあらかじめ計算して保存する方法を事前集計といい、この問題では検索や計算の量を減らすために使っています。ただし、料理のカロリーなどが後から変わる場合は、保存した合計カロリーも更新し、データのつじつまが合う状態を保つ必要があります。
A: 計算自体はできますが、精算表、明細表、料理表を結合して、精算ごとの合計を求める必要があります。合計カロリーをあらかじめ計算して保存する方法を事前集計といい、この問題では検索や計算の量を減らすために使っています。ただし、料理のカロリーなどが後から変わる場合は、保存した合計カロリーも更新し、データのつじつまが合う状態を保つ必要があります。
Q: 平均を求めるとき、明細の行数で割るのですか？
A: いいえ。求めるのは精算一回当たりの平均なので、精算ごとの合計カロリーを、精算の回数で割ります。料理の数や明細の行数で割ると、別の値になってしまいます。
A: いいえ。求めるのは精算一回当たりの平均なので、精算ごとの合計カロリーを、精算の回数で割ります。料理の数や明細の行数で割ると、別の値になってしまいます。
関連キーワード: 表結合、集計、事前集計、データの整合性
設問4：
問題文を見る 設問3のカロリーに関する機能を追加した後、食堂利用者にカロリーを意識して料理を選んでもらうために、人気料理とそのカロリーを掲示することにした。販売皿数の多い順に、料理名、カロリー及び販売皿数を求める。正しいSQL文を、解答群の中から選べ。
解答群
ア：SELECT 料理表.料理名, 料理表.カロリー, COUNT(明細表.皿数) AS 販売皿数 FROM 料理表, 明細表 WHERE 料理表.料理コード = 明細表.料理コード GROUP BY 料理表.料理名, 料理表.カロリー ORDER BY 販売皿数 DESC
イ：SELECT 料理表.料理名, 料理表.カロリー, COUNT(明細表.皿数) AS 販売皿数 FROM 料理表, 明細表 WHERE 料理表.料理コード IN (SELECT 明細表.料理コード FROM 明細表 WHERE 明細表.皿数 IS NOT NULL) GROUP BY 料理表.料理名, 料理表.カロリー ORDER BY 販売皿数 DESC
ウ：SELECT 料理表.料理名, 料理表.カロリー, SUM(明細表.皿数) AS 販売皿数 FROM 料理表, 明細表 WHERE 料理表.料理コード = 明細表.料理コード GROUP BY 料理表.料理名, 料理表.カロリー ORDER BY 販売皿数 DESC
エ：SELECT 料理表.料理名, 料理表.カロリー, SUM(明細表.皿数) AS 販売皿数 FROM 料理表, 明細表 WHERE 料理表.料理コード IN (SELECT 明細表.料理コード FROM 明細表 WHERE 明細表.皿数 IS NOT NULL) GROUP BY 料理表.料理名, 料理表.カロリー ORDER BY 販売皿数 DESC
模範解答
ウ
解説
解答の導き方
求めるのは、料理ごとの料理名、カロリー、販売皿数です。さらに「販売皿数の多い順」に並べます。
-
販売皿数は、皿数を合計して求めます。
明細表には、料理ごとの「皿数」が記録されています。明細表の1行が必ず1皿とは限らないため、行数を数えるのではなく、皿数の値を合計する必要があります。
そのため、販売皿数を求める集計には、合計を計算する SUM を使います。 -
料理と明細を料理コードで結び付けます。
料理表には料理名やカロリーがあり、明細表には料理コードや皿数があります。両方の表から同じ料理の情報を取り出すには、料理コードが一致する行どうしを対応させます。
選択肢では、料理表.料理コードと明細表.料理コードが等しいことを示す条件が必要です。 -
料理ごとに集計します。
同じ料理は複数回販売されることがあります。そのため、料理名とカロリーでグループに分け、それぞれのグループ内で皿数を合計します。SQL（Structured Query Language：データベースのデータを検索・集計するための言語）では、GROUP BYがこのグループ分けを行います。 -
販売皿数の多い順に並べます。
集計した販売皿数を基準に、ORDER BYで並べ替えます。DESCは、値の大きいものから並べる指定です。
この条件に合うのは、料理コードを一致させ、SUM(明細表.皿数)で合計し、販売皿数を降順に並べるウです。
誤りやすいポイント
-
COUNTとSUMを取り違える
COUNT(明細表.皿数)は、皿数の値を足すのではなく、皿数が記録された行の数を数えます。たとえば、1行に皿数が複数記録されていても、COUNTではその行を1件として数えます。 -
INが結合条件になると思い込む
INは、値が指定した値の集まりに含まれるかを調べる条件です。選択肢のイとエでは、料理表と明細表の料理コードを行ごとに対応させていません。そのため、料理ごとの皿数を正しく集計できません。 -
販売皿数の並べ替えを忘れる
集計結果が正しくても、ORDER BY 販売皿数 DESCがなければ、販売皿数の多い順にはなりません。
FAQ
Q: 明細表の行数を数えるだけでは、なぜ販売皿数にならないのですか？
A: 明細表には「皿数」という数量が記録されています。1行に複数皿分が記録される場合、行数だけでは実際の皿数より少なく数えてしまうためです。
A: 明細表には「皿数」という数量が記録されています。1行に複数皿分が記録される場合、行数だけでは実際の皿数より少なく数えてしまうためです。
Q: なぜ料理コードで表を結び付けるのですか？
A: 料理表の料理名・カロリーと、明細表の皿数が、同じ料理についての情報だと特定するためです。料理コードが一致する行を対応させます。
A: 料理表の料理名・カロリーと、明細表の皿数が、同じ料理についての情報だと特定するためです。料理コードが一致する行を対応させます。
Q: DESCはどのような順番ですか？
A: 値の大きいものから小さいものへ並べる順番です。この問題では、販売皿数の多い料理が先に表示されます。
A: 値の大きいものから小さいものへ並べる順番です。この問題では、販売皿数の多い料理が先に表示されます。
関連キーワード: SQL、集約関数、SUM、GROUP BY、結合、ORDER BY
