基本情報技術者 2012年 秋期 午後 問02
購買情報を管理する関係データベースの設計及び運用に関する次の記述を読んで、設問1～4に答えよ。
B社では、購買業務の効率化を目的に、社内組織を変更して、これまでX事業部とY事業部に個別に存在していた購買部門を統合した。
これに伴い、それぞれの事業部が関係データベースで個別に管理していた購買データも統合した。統合後の表構成は図1のとおりであり、統合前からX事業部とY事業部ともに同じ表構成で管理していた。下線付きの項目は主キーを表す。
設問1：
問題文を見る データを統合したときに実施した“名寄せ”と呼ばれる作業に関する次の記述中のに入れる適切な答えを、解答群の中から選べ。
同じ情報が、表現が異なるデータとして社内に複数存在する場合がある。それぞれが、関連性のないデータベースで個別に管理されているのであれば、データの重複といった問題は発生しない。しかし、データベースの統合が必要になった場合、単純にデータを寄せ集めると、同じ情報を表すデータであるにもかかわらず別のデータとして格納されてしまい、a、bといった問題が発生する。
このような場合、同じ情報を表すデータを一つのデータにまとめる“名寄せ”と呼ばれる作業が必要となる。
B社では、名寄せを次の手順で実施した。
(1) 統合対象の表のデータを見比べて、表現は異なっていても同じ情報を表しているデータかどうかを識別するのに最も適した項目を特定する。
(2) (1)で特定した項目で突合せを行い、同じ情報を表すデータを一つのデータにまとめる。
それぞれの事業部の取引先表から(1)の作業のために抽出した、名寄せが必要となる典型的なデータを、図2及び図3に示す。これらのデータから、突合せを行う項目はcが適切と判断できる。
a, bに関する解答群
ア：更新すべきデータが更新されない
イ：削除すべきデータが残る
ウ：情報が漏えいする
エ：挿入したはずのデータが消える
オ：トランザクションが遅延する
cに関する解答群
ア：社名
イ：所在地
ウ：電話番号
エ：取引先コード
模範解答
a：ア、b：イ、c：ウ（a,bは順不同）
解説
解答の導き方
まず、ここでいう名寄せとは、表記の違いがあっても同じ相手や情報を表しているデータを見つけ、一つにまとめる作業です。問題では、データを単純に集めると、同じ情報が別々のデータとして残ることによる問題を問うています。
同じ情報が別々のデータとして残っていると、片方だけを更新してもう片方を更新し忘れることがあります。その結果、同じ相手について異なる情報が登録されてしまいます。これは「更新すべきデータが更新されない」という問題なので、aにはアが入ります。
また、重複しているデータの片方を削除しても、もう片方が残ることがあります。これが「削除すべきデータが残る」という問題です。したがって、bにはイが入ります。
次に、図2と図3の取引先表で、同じ取引先を見分けるのに適した項目を探します。
- 社名は、「情報商事株式会社」「情報商事（株）」のように表記が異なります。また、似た社名でも別の取引先の場合があります。
- 所在地も、「東京都文京区桜坂2-28」と「文京区桜坂2丁目28番」のように書き方が異なります。
- 取引先コードは、同じ取引先と考えられるデータでも、図2では「XK001」、図3では「0105」と異なっています。取引先コードは表の中で行を識別する主キー（1行を特定する項目）として使えても、事業部をまたいで共通する値ではありません。
- 電話番号は、図2の「XK001」と図3の「0105」で、どちらも「03-1111-2222」です。
このように、社名や所在地の表記には違いがありますが、電話番号は一致しています。したがって、突合せを行う項目として適切なのは電話番号であり、cにはウが入ります。
誤りやすいポイント
- 社名や所在地の文字列が完全に一致しないからといって、別の取引先だとは限りません。表記の違いを考慮して、同じ情報かどうかを判断します。
- 取引先コードが表の中で行を特定するために使われていても、異なる事業部で別々に付けられていれば、事業部をまたいだ照合には使えません。
- データの重複による問題を、情報漏えいや処理の遅延と混同しないようにします。この問題で問われているのは、更新や削除の際に重複したデータが残ることによる不整合です。
FAQ
Q: 社名や所在地が違っていても、同じ取引先と判断してよいのですか？
A: 表記が違うだけで、すぐに別の取引先だとは判断できません。この問題では、社名や所在地の表記に違いがあっても、電話番号が一致するデータがあることを手掛かりにします。
A: 表記が違うだけで、すぐに別の取引先だとは判断できません。この問題では、社名や所在地の表記に違いがあっても、電話番号が一致するデータがあることを手掛かりにします。
Q: 電話番号が一致すれば、どのような場合でも同じ取引先だと判断できますか？
A: この問題では、与えられたデータを照合する項目として電話番号が最も適しています。実際の運用では電話番号が変更されたり、複数の取引先で共有されたりする可能性もあるため、ほかの情報も合わせて確認することがあります。
A: この問題では、与えられたデータを照合する項目として電話番号が最も適しています。実際の運用では電話番号が変更されたり、複数の取引先で共有されたりする可能性もあるため、ほかの情報も合わせて確認することがあります。
Q: 名寄せをしないと、なぜ更新の問題が起きるのですか？
A: 同じ取引先が複数のデータとして登録されていると、更新時に片方だけを書き換えてしまうことがあります。その結果、登録内容に食い違いが生じます。
A: 同じ取引先が複数のデータとして登録されていると、更新時に片方だけを書き換えてしまうことがあります。その結果、登録内容に食い違いが生じます。
関連キーワード: 関係データベース、名寄せ、データ重複、主キー、更新異常、削除異常
設問2：
問題文を見る 統合された購買部門で分析したところ、同じ商品であっても、取引先によって購入単価が異なることが分かった。商品名“エコ鉛筆黒”について、取引先コードと平均購入単価を、金額が安い順に表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。ここで、商品名は一意に管理できているものとする。
SELECT 発注表.取引先コード,
SUM(明細表.購入額) / SUM(明細表.数量) AS 平均購入単価
FROM 発注表, 商品表, 明細表
WHERE d
GROUP BY 発注表.取引先コード
ORDER BY 平均購入単価
解答群
ア：発注表.発注コード = 明細表.発注コード AND 商品表.商品コード = 明細表.商品コード AND 商品表.商品名 = 'エコ鉛筆黒'
イ：発注表.発注コード = 明細表.発注コード AND 明細表.購入額 = (SELECT AVG(商品表.定価) FROM 商品表 WHERE 商品表.商品名 = 'エコ鉛筆黒')
ウ：発注表.発注コード = 明細表.発注コード AND 明細表.購入額 = (SELECT SUM(商品表.定価) FROM 商品表 WHERE 商品表.商品名 = 'エコ鉛筆黒')
エ：発注表.発注コード = 明細表.発注コード AND 明細表.商品コード = (SELECT COUNT(*) FROM 商品表 WHERE 商品表.商品名 = 'エコ鉛筆黒')
模範解答
d：ア
解説
解答の導き方
求めるのは、商品名が「エコ鉛筆黒」の明細だけを対象にした、取引先ごとの平均購入単価です。問題文では、購入単価を取引先コードと一緒に安い順で表示するとあります。
まず、3つの表から必要な情報を集めます。表結合（複数の表から、対応する行をつないで情報を取り出すこと）では、表どうしの対応関係を条件に指定します。
-
発注表と明細表を発注コードでつなぎます。発注表には取引先コードがあり、明細表には商品ごとの数量と購入額があるためです。条件は次のとおりです。
発注表.発注コード = 明細表.発注コード -
明細表と商品表を商品コードでつなぎます。明細表の商品コードに対応する商品名を商品表から確認するためです。条件は次のとおりです。
商品表.商品コード = 明細表.商品コード -
商品表の商品名で「エコ鉛筆黒」に絞ります。条件は次のとおりです。
商品表.商品名 = 'エコ鉛筆黒'
この3つの条件をすべて満たす選択肢はアです。問題文に「商品名は一意に管理できている」とあるので、この商品名で対象商品を特定できます。
次に、購入単価の計算方法を確認します。明細表の購入額は、商品を購入した金額の合計です。例えば、数量が20で購入額が900なら、1個当たりの購入単価は900 ÷ 20で求められます。複数の明細をまとめる場合も、購入額の合計を数量の合計で割ります。
そのため、SQLの計算式である SUM(明細表.購入額) / SUM(明細表.数量) は、対象商品の合計購入額を合計数量で割った値になります。GROUP BY 発注表.取引先コードによって取引先ごとに集計し、ORDER BY 平均購入単価によって平均購入単価の安い順に並べます。
誤りやすいポイント
- 商品名を条件にするだけでは、明細表の商品がどの商品のものかは特定できません。商品コードで商品表と明細表を結び付ける条件も必要です。
- 購入額は、1個当たりの単価ではなく、明細に記録された購入金額です。単価を求めるには、購入額の合計を数量の合計で割ります。
- 商品表の定価と、実際に購入したときの購入額は別の情報です。定価を使っても、取引先ごとの実際の平均購入単価にはなりません。
- 複数の表を結び付ける条件が足りないと、関係のない行まで組み合わさり、正しい集計にならないことがあります。
FAQ
Q: 平均購入単価は、明細ごとの単価を平均するのではないのですか？
A: このSQLでは、購入額の合計を数量の合計で割ります。購入数量が異なる明細を同じ重みで扱わず、購入した個数を反映した平均になります。
A: このSQLでは、購入額の合計を数量の合計で割ります。購入数量が異なる明細を同じ重みで扱わず、購入した個数を反映した平均になります。
Q: GROUP BY 発注表.取引先コードは何をしていますか？
A: 取引先コードが同じ明細をまとめて集計しています。そのため、結果は取引先ごとに1つの平均購入単価になります。
A: 取引先コードが同じ明細をまとめて集計しています。そのため、結果は取引先ごとに1つの平均購入単価になります。
Q: ORDER BY 平均購入単価は、なぜ安い順になりますか？
A: ORDER BYで昇順を指定すると、小さい値から大きい値の順に並びます。このSQLでは昇順が指定されているため、購入単価の安い順になります。
A: ORDER BYで昇順を指定すると、小さい値から大きい値の順に並びます。このSQLでは昇順が指定されているため、購入単価の安い順になります。
関連キーワード: SQL、表結合、集約関数、GROUP BY、ORDER BY
設問3：
問題文を見る より安く商品を購入するために、購買部門の発注担当者は、商品の発注時に割引率を確認してから発注先を選定することにした。発注先選定時の検索処理に関する次の記述中のに入れる適切な答えを、解答群の中から選べ。
商品コードごとの平均割引率は、eとfを結合すると求められる。加えて、取引先コードごとに集計したり、“以前は高かったが、最近は安くなった”といった傾向を把握したりしたい場合は、更にgを結合することで求められる。
解答群
ア：商品表
イ：取引先表
ウ：発注表
エ：品目表
オ：部表
カ：明細表
模範解答
e：ア、f：カ、g：ウ（e,fは順不同）
解説
解答の導き方
この設問では、商品の割引率を求めるためにどの表が必要か、さらに取引先や発注時期ごとに比べるためにどの表を追加するかを考えます。
まず、割引率を計算するには、通常の価格と実際に購入した金額の両方が必要です。
図1の表構成では、商品ごとの通常の価格である「定価」は商品表にあります。一方、実際に購入した「数量」と「購入額」は明細表にあります。商品表と明細表を結合（共通する項目を手掛かりに、複数の表の情報を組み合わせること）すると、商品ごとに定価と購入額を対応させられます。
明細表の1行について考えると、割引がなかった場合の金額は「定価×数量」です。そこから購入額を引くと割引された金額が分かるので、割引率は次のように求められます。
これを商品コードごとに集計して平均すれば、商品コードごとの平均割引率が分かります。したがって、eとfには商品表と明細表が入ります。どちらをe、fにしても、必要な2表は同じです。
次に、取引先ごとの集計や、以前と最近の価格の比較を考えます。商品表と明細表を結合しただけでは、取引先や発注日が分かりません。明細表の発注コードを手掛かりに発注表を結合すると、取引先コードや発注日を対応させられます。取引先コードで集計したり、発注日を基に時期ごとの割引率を比べたりできるため、gに入るのは発注表です。
誤りやすいポイント
- 品目表には品目名はありますが、商品ごとの定価や購入額、数量はありません。品目の分類を確認する表であり、割引率の計算に必要な情報はそろいません。
- 取引先表には取引先の名称や所在地などがありますが、商品ごとの購入額や発注日はありません。取引先ごとの集計に必要な取引情報は、発注表を通じて結び付けます。
- 商品表だけでは実際の購入額が分からず、明細表だけでは定価が分かりません。割引率を求めるには、両方の情報が必要です。
- 発注表を加える目的は、割引率そのものを計算することではありません。取引先や発注時期の情報を追加するためです。
FAQ
Q: 商品表と明細表は、どの項目を手掛かりに結合しますか？
A: どちらの表にもある商品コードを手掛かりにします。これにより、明細表の購入額や数量を、商品表の定価と対応させられます。
A: どちらの表にもある商品コードを手掛かりにします。これにより、明細表の購入額や数量を、商品表の定価と対応させられます。
Q: 発注表は、なぜ取引先ごとの集計に必要ですか？
A: 明細表には発注コードがありますが、取引先コードはありません。発注コードで発注表と結合すると、各明細を取引先や発注日と結び付けられます。
A: 明細表には発注コードがありますが、取引先コードはありません。発注コードで発注表と結合すると、各明細を取引先や発注日と結び付けられます。
Q: 「以前は高かったが、最近は安くなった」という傾向は、どの情報を使って確認しますか？
A: 発注表にある発注日を使って、時期ごとに割引率を比べます。取引先ごとの傾向を見る場合は、取引先コードも使います。
A: 発注表にある発注日を使って、時期ごとに割引率を比べます。取引先ごとの傾向を見る場合は、取引先コードも使います。
関連キーワード: 関係データベース、表結合、主キー、集計、割引率
設問4：
問題文を見る 複数の取引先から文具を購入していたが、品ぞろえは同じだったので、1社に一括発注することによって割引率を上げてもらうよう交渉することにした。文具について10回以上の発注実績がある取引先を対象に、購入金額が多い順に取引先コードと金額を表示する。次のSQL文のに入れる正しい答えを、解答群の中から選べ。
SELECT 発注表.取引先コード, SUM(明細表.購入額) AS 合計購入金額
FROM 発注表, 明細表, 商品表, 品目表
WHERE 発注表.発注コード = 明細表.発注コード AND
明細表.商品コード = 商品表.商品コード AND
商品表.品目コード = 品目表.品目コード AND
品目表.品目名 = '文具'
GROUP BY 発注表.取引先コード
h
ORDER BY 合計購入金額 DESC
解答群
ア：HAVING COUNT(*) >= 10
イ：HAVING COUNT(DISTINCT 発注表.発注コード) >= 10
ウ：HAVING COUNT(発注表.発注コード) >= 10
エ：HAVING MAX(明細表.購入額) >= 10
オ：HAVING MAX(明細表.数量) >= 10
カ：HAVING SUM(明細表.購入額) >= 10
キ：HAVING SUM(明細表.数量) >= 10
模範解答
h：イ
解説
解答の導き方
-
何を数えるのかを確認します。
設問は「文具について10回以上の発注実績がある取引先」を探しています。数える対象は、購入した商品の行数や数量ではなく、発注した回数です。 -
表を結合すると、同じ発注が複数行になることがあります。
SQLでは、発注表と明細表を発注コードで結合しています。発注表は発注ごとの情報を持ち、明細表は発注した商品ごとの情報を持つ表です。そのため、1回の発注で文具を複数種類購入していると、結合後には同じ発注コードが複数行に現れます。たとえば、1回の発注に文具の商品が2種類含まれていれば、結合後の行は2行になります。しかし、発注した回数は2回ではなく1回です。 -
発注回数は、重複しない発注コードの数で求めます。
「品目名 = '文具'」で文具の明細に絞った後、取引先ごとに発注回数を数えます。このとき、同じ発注コードが複数行にあっても1回と数える必要があります。そこで、発注コードの重複を除いて数える「COUNT(DISTINCT 発注表.発注コード)」を使います。これに「10以上」という条件を付けるため、hに入るのは「HAVING COUNT(DISTINCT 発注表.発注コード) >= 10」です。HAVINGは、GROUP BYで取引先ごとにまとめた後、そのまとまりに条件を付けるための句です。したがって、発注回数が10回以上の取引先だけを残せます。 -
購入金額の合計を求め、金額の多い順に並べます。
SELECT句では「SUM(明細表.購入額)」により、取引先ごとの文具の購入金額を合計しています。最後の「ORDER BY 合計購入金額 DESC」は、その合計金額が大きい順に並べる指定です。
誤りやすいポイント
- 「COUNT(*)」は、結合後の行数を数えます。1回の発注に文具の明細が複数あれば、その発注を複数回と数えてしまう可能性があります。
- 「COUNT(発注表.発注コード)」も、結合後に現れた発注コードを行ごとに数えます。重複を除く指定がないため、発注回数を正しく数えられない場合があります。
- 「MAX(明細表.購入額)」や「MAX(明細表.数量)」は、明細1行ごとの購入額や数量の最大値を調べる指定です。発注回数を数えるものではありません。
- 「SUM(明細表.購入額)」や「SUM(明細表.数量)」は、購入金額や数量の合計を調べる指定です。設問が条件としている「10回以上の発注実績」とは別のものです。
- WHEREとHAVINGの役割を混同しないようにします。WHEREは集計前の行を絞り込み、HAVINGは集計後のグループを絞り込みます。
FAQ
Q: 発注コードが明細表に複数回現れるのはなぜですか？
A: 1回の発注で複数の商品を注文すると、商品ごとに明細表の行が作られるためです。その結果、同じ発注コードが複数の行に現れることがあります。
A: 1回の発注で複数の商品を注文すると、商品ごとに明細表の行が作られるためです。その結果、同じ発注コードが複数の行に現れることがあります。
Q: COUNT(DISTINCT ...)のDISTINCTは何をしていますか？
A: 同じ値を重複して数えないようにします。この問題では、同じ発注コードが複数行にあっても、発注1回として数えられます。
A: 同じ値を重複して数えないようにします。この問題では、同じ発注コードが複数行にあっても、発注1回として数えられます。
Q: なぜ発注回数の条件にHAVINGを使うのですか？
A: 発注回数は、取引先ごとにまとめてから数える値です。グループごとの集計結果に条件を付けるため、HAVINGを使います。
A: 発注回数は、取引先ごとにまとめてから数える値です。グループごとの集計結果に条件を付けるため、HAVINGを使います。
関連キーワード: SQL、GROUP BY、HAVING、COUNT、DISTINCT、集約関数
