基本情報技術者 2009年 春期 午後 問03
関係データベースの設計と操作に関する次の記述を読んで、設問1〜4に答えよ。
設問1：
問題文を見る データベースを設計するに当たって、エンティティの関係を表すE-R図を作成した。図1の構成におけるE-R図を、解答群の中から選べ。
解答群
模範解答
イ
解説
解答の導き方
まず、E-R図（Entity-Relationship図：管理するものと、それらの関係を表す図）では、表にする対象と、その対象同士のつながりを整理します。
図1には、料理名や作り方を管理する「料理表」、商品名や種別を管理する「商品表」、そして料理ごとに使う商品とその分量を管理する「分量表」があります。主キー（表の中で行を特定する項目）である料理IDと商品IDを使って、分量表の各行が「どの料理に、どの商品を、どれだけ使うか」を表しています。
ここから、料理と商品は直接結び付く関係だと分かります。一つの料理には複数の商品を使います。反対に、一つの商品も複数の料理に使われることがあります。実際、本文には食材名を入力して「その食材を使った料理名」を表示するとあります。つまり、料理と商品は、互いに複数の相手と結び付く関係です。
その結び付きを表すのが分量表です。分量は、料理だけ、または商品だけに決まるのではありません。「どの料理に、どの商品を使うか」という組合せごとに決まります。たとえば、商品が同じでも、料理が違えば必要な分量は変わることがあります。
したがって、E-R図では「料理」と「商品」を結び、その関係を「分量」と表す形が適切です。解答群では、料理と商品が結ばれ、関係に「分量」とあるイがこれに当たります。
アは「分量」と「商品」を結び、その関係を「料理」としています。ウは「料理」と「分量」を結び、その関係を「商品」としています。どちらも、料理と商品を結び付ける関係が分量であることを正しく表していません。
誤りやすいポイント
- 「分量表」があるため、「分量」を料理や商品と同じ種類の対象として考えてしまうことがあります。分量は、料理と商品の組合せに対して記録される情報です。
- 料理と商品を、一対一の関係だと考えないようにしましょう。一つの料理に複数の商品を使い、商品も複数の料理に使われるため、どちらも複数と結び付く関係です。
- 表の名前だけで判断せず、各表が何を記録しているかを確認しましょう。分量表は、料理と商品を結び付ける役割も持っています。
FAQ
Q: 分量は、料理だけに対して決まるのではないのですか？
A: いいえ。同じ料理でも商品ごとに分量が異なります。また、同じ商品でも料理によって使う量が異なることがあります。そのため、料理と商品の組合せに対して記録します。
A: いいえ。同じ料理でも商品ごとに分量が異なります。また、同じ商品でも料理によって使う量が異なることがあります。そのため、料理と商品の組合せに対して記録します。
Q: 分量表の料理IDと商品IDは、何を表していますか？
A: その行が、どの料理とどの商品についての分量なのかを示します。両方のIDを組み合わせて見ることで、分量がどの組合せに対応するか分かります。
A: その行が、どの料理とどの商品についての分量なのかを示します。両方のIDを組み合わせて見ることで、分量がどの組合せに対応するか分かります。
Q: 料理と商品を直接結び付けるのはなぜですか？
A: システムでは、料理から使う商品を調べるだけでなく、入力された商品から使っている料理も調べます。両方向から対応を確認できるよう、料理と商品を関係付けて管理します。
A: システムでは、料理から使う商品を調べるだけでなく、入力された商品から使っている料理も調べます。両方向から対応を確認できるよう、料理と商品を関係付けて管理します。
関連キーワード: E-R図、エンティティ、主キー、多対多、関係データベース
設問2：
問題文を見る 図1の表は、料理ごとに作り方、使用する商品（食材）とその分量を記入して作成したレシピの表を正規化して得られたものである。データベース設計における第1正規化に基づいて実施した処置を、解答群の中から選べ。
解答群
ア：実行性能の向上を図り、料理表と分量表に分けた。
イ：主キーを一意にするため、料理名に料理IDを割り振った。
ウ：商品とその分量という繰返しの要素を排除した。
エ：情報の独立性を高めるため、商品名に商品IDを割り振った。
模範解答
ウ
解説
解答の導き方
この設問は、表を整理する「正規化」のうち、第1段階でどのような処置をするかを問うています。正規化とは、データを扱いやすくするために表の構成を整えることです。
第1正規形では、表の中に同じ種類の項目を繰り返し持たせず、1つの項目に1つの値を入れる形にします。たとえば、1つの料理の行に「商品1・分量1・商品2・分量2」のような項目を並べるのではなく、商品とその分量を1組ずつ記録できるようにします。
図1の分量表では、料理ID、商品ID、分量が1行に記録されています。同じ料理IDに対して複数の商品を登録できるため、食材と分量の組を繰り返し項目として持たずに済みます。つまり、「商品とその分量という繰返しの要素を排除した」という説明が、第1正規化に基づく処置に当たります。
一方、料理IDや商品IDのようなID（Identifier：識別子）を割り当てることは、データを識別しやすくするための工夫です。主キー（表の各行を一意に識別する項目）を設定することにも関係しますが、繰り返し項目をなくす処置そのものではありません。また、表を分けることが正規化の結果として行われる場合はありますが、「実行性能の向上」が第1正規化の目的というわけではありません。
誤りやすいポイント
- 「表を分けた」という操作だけに注目しないようにします。第1正規形で大切なのは、繰り返し項目をなくし、1つの項目に1つの値を記録することです。
- 主キーを設定することと、第1正規形にすることは別の観点です。IDを割り当てても、繰り返し項目が残っていれば第1正規形になったとはいえません。
- 表を分ける理由を「性能向上」と決めつけないようにします。この設問では、データの持ち方に繰り返しがあるかどうかに注目します。
FAQ
Q: 1つの項目に複数の商品名をまとめて入れてもよいですか？
A: 第1正規形では、1つの項目に複数の値をまとめて入れず、商品ごとに分けて記録します。
A: 第1正規形では、1つの項目に複数の値をまとめて入れず、商品ごとに分けて記録します。
Q: 商品と分量を別々の表にするのですか？
A: この図では、分量表に商品IDと分量を同じ行で記録しています。料理と商品を結び付けながら、その料理で使う分量も表せる形です。
A: この図では、分量表に商品IDと分量を同じ行で記録しています。料理と商品を結び付けながら、その料理で使う分量も表せる形です。
Q: IDを割り当てることには意味がありますか？
A: あります。料理や商品を識別したり、別の表から参照したりしやすくなります。ただし、それ自体が第1正規化の処置というわけではありません。
A: あります。料理や商品を識別したり、別の表から参照したりしやすくなります。ただし、それ自体が第1正規化の処置というわけではありません。
関連キーワード: 第1正規形、正規化、繰返し項目、主キー、関係データベース
設問3：
問題文を見る 野菜売場の端末で来店客が好みの野菜を一つ選択したときに、その野菜を使用する料理名をすべて抽出して表示したい。来店客が“じゃがいも”を選択した場合、次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 料理表.料理名 FROM 料理表, 分量表, 商品表
WHERE
解答群
ア：料理表.料理ID = (SELECT 分量表.料理ID FROM 分量表, 商品表 WHERE 商品表.商品名 = 'じゃがいも')
イ：料理表.料理ID = 分量表.料理ID AND 分量表.商品ID = 商品表.商品ID AND 商品表.商品名 = 'じゃがいも'
ウ：料理表.料理ID = 分量表.料理ID AND 分量表.商品ID = 商品表.商品ID OR 商品表.商品名 = 'じゃがいも'
エ：料理表.料理ID IN (SELECT 分量表.料理ID FROM 分量表, 商品表 WHERE 商品表.商品名 = 'じゃがいも')
模範解答
イ
解説
解答の導き方
求めたいのは、選んだ“じゃがいも”を使う料理の名前です。そのためには、料理名と食材名を、表にあるIDをたどって結び付ける必要があります。
SQL（Structured Query Language：データベースに検索や操作を指示する言語）では、SELECTで表示する項目を指定し、WHEREで表示する行の条件を指定します。この問題では、SELECT 料理表.料理名によって、料理名を表示します。
図1の表は、それぞれ次の役割を持っています。
- 料理表には、料理IDと料理名があります。
- 分量表には、料理IDと商品IDがあり、どの料理にどの商品を使うかが記録されています。
- 商品表には、商品IDと商品名があり、商品IDから商品名を確認できます。
したがって、料理表から分量表、分量表から商品表へ、IDを使って順につなぎます。必要な条件は、次の3つです。
- 料理表.料理ID = 分量表.料理ID
料理名と、その料理に使う食材の情報を結び付けます。 - 分量表.商品ID = 商品表.商品ID
分量表の商品IDと、商品表の商品IDを結び付けます。 - 商品表.商品名 = 'じゃがいも'
選んだ食材を“じゃがいも”に絞ります。
これらをすべて満たす必要があるので、条件はANDでつなぎます。選択肢イは、2つのIDを結び付ける条件と、商品名を絞る条件のすべてを含んでいます。
一方、選択肢アとエでは、副問合せ（SQL文の中に含めて、別の検索結果を得る処理）を使っています。しかし、その副問合せには分量表.商品ID = 商品表.商品IDという条件がありません。そのため、商品表で“じゃがいも”を選んでも、分量表の商品IDと結び付かず、じゃがいもを使わない料理の料理IDまで検索結果に含まれてしまいます。さらにアは、複数の料理IDが返る可能性がある副問合せの結果を、=で一つの値のように比較している点も不適切です。
選択肢ウには、IDを結び付ける2つの条件がありますが、最後の商品名の条件がORでつながっています。ANDはORより先に評価されるため、条件全体は「IDを結び付ける2条件を満たす、または商品名が“じゃがいも”である」となります。商品名が“じゃがいも”であれば、IDが正しく結び付いていない行も対象になるため、正しい料理名だけに絞れません。
誤りやすいポイント
- 3つの表をFROMに並べただけでは、表どうしは結び付きません。IDを使った条件もWHEREに指定する必要があります。
- 食材名だけを指定しても、その食材を使う料理は分かりません。分量表の商品IDと商品表の商品IDを結び付ける条件が必要です。
- ANDをORに置き換えると、条件をすべて満たす行ではなく、ORの後の条件だけを満たす行も検索結果に含まれます。
- INは複数の値との照合に使えますが、副問合せの中で表どうしの結び付けが不足していれば、正しい検索結果にはなりません。
FAQ
Q: なぜ料理表と商品表を直接結び付けないのですか？
A: 料理表には商品IDがなく、商品表には料理IDがありません。両方のIDを持つ分量表を間に置くことで、料理と食材を結び付けられます。
A: 料理表には商品IDがなく、商品表には料理IDがありません。両方のIDを持つ分量表を間に置くことで、料理と食材を結び付けられます。
Q: ANDはどのようなときに使いますか？
A: 複数の条件をすべて満たす行だけを選びたいときに使います。この問題では、2つのIDを結び付ける条件と、商品名を絞る条件のすべてが必要です。
A: 複数の条件をすべて満たす行だけを選びたいときに使います。この問題では、2つのIDを結び付ける条件と、商品名を絞る条件のすべてが必要です。
Q: 選択肢エのINは、なぜ使えないのですか？
A: INは複数の値との照合に使えます。しかし、この選択肢の副問合せには商品IDどうしを結び付ける条件がないため、“じゃがいも”を使う料理だけを取り出せません。
A: INは複数の値との照合に使えます。しかし、この選択肢の副問合せには商品IDどうしを結び付ける条件がないため、“じゃがいも”を使う料理だけを取り出せません。
関連キーワード: 関係データベース、SQL、結合条件、副問合せ
設問4：
問題文を見る 設問3のSQL文では、当日に在庫がない商品を材料に使用する料理名まで表示してしまう。このため、図2に示すとおり、開店時に在庫がある場合は1、ない場合は0を設定する項目“在庫”を追加し、使用する食材すべてについて在庫がある料理名だけを表示できるように商品表を変更する。また、同時にSQL文の可読性を向上させるため、表の結合にはJOIN句を用いる。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 料理表.料理名 FROM 料理表
JOIN 分量表 ON 料理表.料理ID = 分量表.料理ID
JOIN 商品表 ON 分量表.商品ID = 商品表.商品ID
WHERE
解答群
ア：商品表.在庫 != 0 AND 商品表.商品名 = 'じゃがいも'
イ：商品表.在庫 != 0 OR 商品表.商品名 = 'じゃがいも'
ウ：料理表.料理ID IN (SELECT 分量表.料理ID FROM 分量表 JOIN 商品表 ON 分量表.商品ID = 商品表.商品ID WHERE 商品表.在庫 = 0) AND 商品表.商品名 = 'じゃがいも'
エ：料理表.料理ID NOT IN (SELECT 分量表.料理ID FROM 分量表 JOIN 商品表 ON 分量表.商品ID = 商品表.商品ID WHERE 商品表.在庫 = 0) AND 商品表.商品名 = 'じゃがいも'
模範解答
エ
解説
解答の導き方
このSQL文で求めるのは、「じゃがいもを使い、さらに使う食材すべての在庫がある料理名」です。条件を二つに分けて考えます。
-
じゃがいもを使う料理に絞るSQL（Structured Query Language：関係データベースを操作するための言語）では、JOIN句を使って、共通する項目の値が一致する表を組み合わせます。料理表と分量表は料理IDで、分量表と商品表は商品IDで結び付けています。これにより、料理ごとに使う商品名や在庫を確認できます。表示する料理名をじゃがいもを使う料理に絞るには、「商品表.商品名」が「じゃがいも」と一致する条件が必要です。
-
使う食材すべての在庫を確認するじゃがいも自体の在庫があっても、ほかの食材が品切れなら、その料理は条件を満たしません。そこで、料理に使う食材の中に、在庫が0の商品が一つでもあるかを調べます。サブクエリ（SQL文の中に書く、別のSQL文）で、分量表と商品表を結び付け、在庫が0の商品を使う料理IDを取り出します。この料理IDの中に該当する料理は、材料のどれかが品切れなので、表示対象から除く必要があります。選択肢のエは、サブクエリが取り出した料理IDに含まれないことをNOT INで確認し、さらに商品名がじゃがいもであることをANDで指定しています。つまり、「じゃがいもを使う料理」のうち、「在庫が0の商品を使う料理」を除外できます。商品表の在庫は1か0なので、在庫が0の商品を使う料理を除けば、残った料理は使う食材すべての在庫がある料理です。したがって、条件として適切なのはエです。
誤りやすいポイント
- 商品名がじゃがいもであることだけを確認しても、ほかの材料の在庫までは確認できません。アの条件では、じゃがいもの在庫があっても、別の材料が品切れの料理が表示される可能性があります。
- 在庫が0の商品を使う料理を取り出すだけでは、条件を満たさない料理を選ぶことになります。ウは、その料理IDが含まれることを指定しているため、必要な条件と反対です。
- ANDとORの意味を取り違えないようにします。両方の条件を満たす必要がある場合はANDを使います。イのORでは、商品名がじゃがいもでなくても在庫があれば条件に合ってしまいます。
- 在庫があるかを調べる条件と、在庫切れの商品を使う料理を除外する条件は別です。料理全体の材料を確認するには、料理IDを使って品切れの有無を調べる必要があります。
FAQ
Q: なぜ、商品表の在庫を直接確認するだけでは不十分なのですか？
A: 外側のSQL文では、商品名をじゃがいもに絞っています。そのため、そこで在庫を確認しても、ほかの材料の在庫は確認できません。サブクエリで料理IDごとに、品切れの材料があるかを調べます。
A: 外側のSQL文では、商品名をじゃがいもに絞っています。そのため、そこで在庫を確認しても、ほかの材料の在庫は確認できません。サブクエリで料理IDごとに、品切れの材料があるかを調べます。
Q: NOT INは何を確認していますか？
A: 「サブクエリで取り出した料理IDの中に、この料理IDが含まれていないこと」を確認しています。サブクエリは在庫が0の商品を使う料理IDを取り出すので、NOT INによって、品切れの材料を使う料理が除かれます。
A: 「サブクエリで取り出した料理IDの中に、この料理IDが含まれていないこと」を確認しています。サブクエリは在庫が0の商品を使う料理IDを取り出すので、NOT INによって、品切れの材料を使う料理が除かれます。
Q: なぜ、じゃがいもの条件と品切れの除外条件をANDで結ぶのですか？
A: どちらも満たす料理だけを表示したいからです。じゃがいもを使うことと、品切れの材料を使わないことの両方が必要です。
A: どちらも満たす料理だけを表示したいからです。じゃがいもを使うことと、品切れの材料を使わないことの両方が必要です。
関連キーワード: 関係データベース、SQL、JOIN、サブクエリ、IN述語
