応用情報技術者過去問題 平成21年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
注文管理システムの設計と実装に関する次の記述を読んで，設問1～3に答えよ。
S社は，園芸用品の製造及び販売を行う中堅企業である。顧客である農家やホームセンタから電話やファックスで注文を受け，注文管理システム(以下，現行システムという)で管理している。現行システムの機能概要を表1に，E-R図を図1に示す。
〔新注文管理システムについて〕
近年，家庭菜園やガーデニングの流行などによって，園芸用品の個人需要が高まってきた。そこで，販売力強化と顧客満足度向上を目的に，次の機能強化を行った新注文管理システム(以下，新システムという)を開発することになった。
なお，注文内容の確認時点では，まだ注文が確定していないので，確定した注文との区別がつくように，注文エンティティに属性"仮登録フラグ"を追加する。このフラグが"1"の場合は確認中の注文，"0"の場合は確定した注文と定義する。
新システムのE-R図を図3に示す。図3中のa～cには，図1中のa～cと同一のものが入る。
現行システム及び新システムでは，E-R図のエンティティ名を表名，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
S社は，園芸用品の製造及び販売を行う中堅企業である。顧客である農家やホームセンタから電話やファックスで注文を受け，注文管理システム(以下，現行システムという)で管理している。現行システムの機能概要を表1に，E-R図を図1に示す。
近年，家庭菜園やガーデニングの流行などによって，園芸用品の個人需要が高まってきた。そこで，販売力強化と顧客満足度向上を目的に，次の機能強化を行った新注文管理システム(以下，新システムという)を開発することになった。
- セット商品の導入
目的別に複数の商品を組み合わせたセット商品を導入する。さらに，単品で商品を購入しようとしている顧客に，その商品が含まれているセット商品を案内することによって，セット商品を購入するように誘導し，顧客単価の向上をねらう。
セット商品も，通常の商品と一緒に商品エンティティに登録する。両者を区別するために商品エンティティに属性"セットフラグ"を追加し，通常の商品の場合は"0"を，セット商品の場合は"1"を設定する。そして，セット商品エンティティを追加し，セットに含まれる商品の商品番号とその数量を管理する。 - 新モデルお知らせ機能の追加
毎年新しいモデル(以下，新モデルという)が出る商品では，その履歴を管理し，顧客が古いモデルの商品を発注しようとした場合に，アドバイスする機能を追加する。具体的には，図2のような注文確認画面を設け，担当社員が注文内容を確認するとともに，備考欄のような表示で，新モデルがあることを知ることができる。
さらに，注文明細一覧の各行末にある"詳細情報"ボタンから，各商品の詳細な情報を照会することができ，新モデルに関する情報もそこから照会できる。
新システムのE-R図を図3に示す。図3中のa～cには，図1中のa～cと同一のものが入る。
設問1
図1中のa～cに入れる適切な属性名及びエンティティ間の関連を答え，図を完成させよ。図1の凡例に従うこと。属性名は表1又は図1から選び，必要に応じて下線を付けること。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)
解答入力欄
- a：
- b：
- c：
解答例・解答の要点
- a：顧客番号
- b：↓
- c：販売単価
解説
〔aについて〕
顧客エンティティと注文エンティティの間には関連があります。関係データベースでは、共通の属性を持たせることによってエンティティ同士を関連付けますが、注文エンティティには、顧客エンティティの主キーである「顧客番号」を表す属性は含まれていません。よって、顧客エンティティの主キーを参照する属性が、注文エンティティでの外部キーとして必要であることがわかります。したがって、[b]には「顧客番号」が当てはまります。
∴a＝顧客番号
〔bについて〕
注文エンティティと注文明細エンティティには共通の属性「注文番号」があります。この属性「注文番号」は、注文側では主キーであり、注文を一意に特定します。また、注文明細エンティティ側では注文明細番号とともに（複合）主キーであり、注文された商品ごとの明細を一意に特定します。図2の注文確認画面を見れば明らかなように、1回の注文について複数の商品が購入されることがあります。つまり、注文明細エンティティには同一の注文番号をもつ複数のレコードが存在しえるということです。したがって、注文エンティティと注文明細エンティティのカーディナリティは1対多であり、[b]には「↓」が当てはまります。
∴b＝↓
〔cについて〕
表1「現行システムの機能概要」の注文管理機能の行を見ると、「注文日，顧客番号，担当社員番号．商品番号と数量及び販売単価を登録する」とあります。注文日、顧客番号および担当社員番号は注文エンティティに、商品番号と数量は注文明細エンティティに存在していますが、販売単価はどこにも記載されていません。したがって、[c]には（注文商品ごとの)「販売単価」が当てはまります。
∴c＝販売単価
顧客エンティティと注文エンティティの間には関連があります。関係データベースでは、共通の属性を持たせることによってエンティティ同士を関連付けますが、注文エンティティには、顧客エンティティの主キーである「顧客番号」を表す属性は含まれていません。よって、顧客エンティティの主キーを参照する属性が、注文エンティティでの外部キーとして必要であることがわかります。したがって、[b]には「顧客番号」が当てはまります。
∴a＝顧客番号
〔bについて〕
注文エンティティと注文明細エンティティには共通の属性「注文番号」があります。この属性「注文番号」は、注文側では主キーであり、注文を一意に特定します。また、注文明細エンティティ側では注文明細番号とともに（複合）主キーであり、注文された商品ごとの明細を一意に特定します。図2の注文確認画面を見れば明らかなように、1回の注文について複数の商品が購入されることがあります。つまり、注文明細エンティティには同一の注文番号をもつ複数のレコードが存在しえるということです。したがって、注文エンティティと注文明細エンティティのカーディナリティは1対多であり、[b]には「↓」が当てはまります。
∴b＝↓
〔cについて〕
表1「現行システムの機能概要」の注文管理機能の行を見ると、「注文日，顧客番号，担当社員番号．商品番号と数量及び販売単価を登録する」とあります。注文日、顧客番号および担当社員番号は注文エンティティに、商品番号と数量は注文明細エンティティに存在していますが、販売単価はどこにも記載されていません。したがって、[c]には（注文商品ごとの)「販売単価」が当てはまります。
∴c＝販売単価
設問2
本文中の"(1)セット商品の導入"で記述されているセット商品を導入するためには，現行システムの出荷指示機能を修正する必要がある。新システムにおいて，指定された注文番号の出荷指示を出力するSQL文を図4に示す。図4中のd～fに入れる適切な字句又は式を答えよ。ここで，":注文番号"は，指定された注文番号を格納するホスト変数である。
解答入力欄
- d：
- e：
- f：
解答例・解答の要点
- d：SUM(TempTbl.小計)
- e：UNION ALL
- f：商品.商品番号 = セット商品.セット商品番号
又は
注文明細.商品番号 = セット商品.セット商品番号
解説
出荷指示は「注文番号ごとに，商品番号と数量を一覧にした出荷指示書を作成する」機能です。問題文の説明だけでは少しわかりにくいかもしれませんが、セット商品についてはセット商品番号でいくつという形ではなく、セット商品を子商品に展開した出荷指示にする必要があります。単一商品とセット商品が同時注文された場合でも、正しく商品番号ごとの数量を算出するのが図4のSQL文です。
まずSQL文の構造をざっくり確認していきます。
このSQL文では2つのSELECT文を使って TempTbl という表を作り、それを商品番号ごとにグループ化して、商品番号列と[d]列を抜き出しています。SQL文①は注文のうち単品商品ごとの数量を集計する部分、SQL文②はセット商品を子商品に展開した上で商品ごとの数量を求める部分です。
〔dについて〕
GROUP BY句が含まれている場合、SELECT句には"GROUP BY句で指定した列"または"集約関数"しか指定できません。GROUP BY句で指定されているのは商品番号だけですから、必然的に空欄には何らかの集計関数が入ることがわかります。前述のとおり、このSQL文の目的は商品番号ごとの数量を集計することですので、商品番号ごとに2つのSELECT句の小計列の値を合計する「SUM(TempTbl.小計)」が当てはまります。
これにより、単品商品ごとの数量と、セット商品を子商品に展開したときの商品ごとの数量が合算されることになります。
∴d＝SUM(TempTbl.小計)
〔eについて〕
2つのSELECT文を結び付ける部分なので、何らかの集合演算子が入ることがわかります。集合演算子は、複数のクエリの結果をまとめ一つの結果セットにするもので、UNION／UNION ALL（和集合）、EXCEPT（差集合）、INTERSECT（共通集合）などがあります。今回は上下のSELECT文の結果を単純に足し合わせたいので、和集合演算である「UNION ALL」が当てはまります。
「UNION」だとダメなのは、上の単品商品の集計と下のセット商品の集計で同一商品番号、同一数量の行が存在する場合、重複行として消えてしまうからです。例えば、"スコップ1個、赤土5個"というセット商品の注文があったとして、同一の注文に単品商品の"スコップ1個"が含まれていた場合には、上下のクエリともに"スコップ1個"という行が現れることになります。正しくは"スコップ2個"の出荷指示になるべきですが、UNIONだと"スコップ1個"になってしまいます。
∴e＝UNION ALL
〔fについて〕
ON句に続く字句ですので、INNER JOINで2つの表を結合するための「属性 = 属性」といった字句が入ります。ここで、セット商品表には、セット商品番号列と子商品番号列があり、どちらも注文明細表の商品番号と結合することができるため、どちらが結合属性として適切かを考える必要があります。
SQL文②では、注文明細のうちセット商品について、そのセット商品を構成する子商品に展開しなければならないので、商品番号列とセット商品番号列を結び付けることになります。これにより、1つの注文明細が複数の子商品に関連付けられます。逆に子商品番号を結合属性にしてしまうと、セット商品の行がすべて結果セットから消えてしまい（単品商品の行しか残らず）正しい結果となりません。
結合元表は注文明細表と商品表を結合した表、結合するのはセット商品表なので、
∴f＝商品.商品番号 = セット商品.セット商品番号
又は、注文明細.商品番号 = セット商品.セット商品番号
まずSQL文の構造をざっくり確認していきます。
このSQL文では2つのSELECT文を使って TempTbl という表を作り、それを商品番号ごとにグループ化して、商品番号列と[d]列を抜き出しています。SQL文①は注文のうち単品商品ごとの数量を集計する部分、SQL文②はセット商品を子商品に展開した上で商品ごとの数量を求める部分です。
GROUP BY句が含まれている場合、SELECT句には"GROUP BY句で指定した列"または"集約関数"しか指定できません。GROUP BY句で指定されているのは商品番号だけですから、必然的に空欄には何らかの集計関数が入ることがわかります。前述のとおり、このSQL文の目的は商品番号ごとの数量を集計することですので、商品番号ごとに2つのSELECT句の小計列の値を合計する「SUM(TempTbl.小計)」が当てはまります。
これにより、単品商品ごとの数量と、セット商品を子商品に展開したときの商品ごとの数量が合算されることになります。
∴d＝SUM(TempTbl.小計)
〔eについて〕
2つのSELECT文を結び付ける部分なので、何らかの集合演算子が入ることがわかります。集合演算子は、複数のクエリの結果をまとめ一つの結果セットにするもので、UNION／UNION ALL（和集合）、EXCEPT（差集合）、INTERSECT（共通集合）などがあります。今回は上下のSELECT文の結果を単純に足し合わせたいので、和集合演算である「UNION ALL」が当てはまります。
「UNION」だとダメなのは、上の単品商品の集計と下のセット商品の集計で同一商品番号、同一数量の行が存在する場合、重複行として消えてしまうからです。例えば、"スコップ1個、赤土5個"というセット商品の注文があったとして、同一の注文に単品商品の"スコップ1個"が含まれていた場合には、上下のクエリともに"スコップ1個"という行が現れることになります。正しくは"スコップ2個"の出荷指示になるべきですが、UNIONだと"スコップ1個"になってしまいます。
∴e＝UNION ALL
〔fについて〕
ON句に続く字句ですので、INNER JOINで2つの表を結合するための「属性 = 属性」といった字句が入ります。ここで、セット商品表には、セット商品番号列と子商品番号列があり、どちらも注文明細表の商品番号と結合することができるため、どちらが結合属性として適切かを考える必要があります。
SQL文②では、注文明細のうちセット商品について、そのセット商品を構成する子商品に展開しなければならないので、商品番号列とセット商品番号列を結び付けることになります。これにより、1つの注文明細が複数の子商品に関連付けられます。逆に子商品番号を結合属性にしてしまうと、セット商品の行がすべて結果セットから消えてしまい（単品商品の行しか残らず）正しい結果となりません。
結合元表は注文明細表と商品表を結合した表、結合するのはセット商品表なので、
- 注文明細.商品番号 = セット商品.セット商品番号
- 商品.商品番号 = セット商品.セット商品番号
∴f＝商品.商品番号 = セット商品.セット商品番号
又は、注文明細.商品番号 = セット商品.セット商品番号
設問3
図2中の注文明細一覧を出力するために，図5に示すSQL文を作成した。ところが，このSQL文を実行したところ，同じ注文明細番号の行が複数出力されてしまった。どのような場合にこの問題は発生するのか，25字以内で述べよ。また，その解決策として，(あ)～(う)のいずれかの場所に字句を追加する必要がある。その場所と追加する字句を答えよ。ここで，":注文番号"は，指定された注文番号を格納するホスト変数である。図5中のcには，図1中のcと同一のものが入る。
解答入力欄
- 場合：
- 場所：
- 字句：
解答例・解答の要点
- 場合：一つの商品に複数の新モデルが存在する場合 (20文字)
- 場所：(あ)
- 字句：DISTINCT
解説
図5のSQL文は、入力された注文番号で抽出した注文明細の、注文明細番号、商品番号、商品名、数量、販売単価、販売金額に加え、備考として新商品モデルの存在有無を取得するSQL文です。
同一行が複数表示されるということは、注文明細表の1つの行が新商品モデル表の複数の行に結びついているということが考えられます。新商品モデルエンティティを見ると、旧商品番号と新商品番号の複合主キーになっており、1つの旧商品について複数の新商品が登録できるようになっていることがわかります。このように複数の新商品を登録している状態で結合を行うと、注文明細の1つの行について複数の新商品番号が紐付くことになり、結果として同じ注文明細番号の行が複数出力されることになります。
したがって、問題が発生する場合としては、「一つの商品に複数の新モデルが存在する場合」や「1つの旧商品に対して複数の新商品が登録されている場合」などの解答が適切となります。
抽出された複数の新商品番号は、いずれもSELECT句により「新モデルあり」という文字列に変換されるため、結果的に全く同一の行になります。したがって、(あ)の場所（SELECT句の直後）に「DINSTICT」を指定することによって重複を排除できます。
∴場合：一つの商品に複数の新モデルが存在する場合
場所：(あ)
字句：DISTINCT
同一行が複数表示されるということは、注文明細表の1つの行が新商品モデル表の複数の行に結びついているということが考えられます。新商品モデルエンティティを見ると、旧商品番号と新商品番号の複合主キーになっており、1つの旧商品について複数の新商品が登録できるようになっていることがわかります。このように複数の新商品を登録している状態で結合を行うと、注文明細の1つの行について複数の新商品番号が紐付くことになり、結果として同じ注文明細番号の行が複数出力されることになります。
したがって、問題が発生する場合としては、「一つの商品に複数の新モデルが存在する場合」や「1つの旧商品に対して複数の新商品が登録されている場合」などの解答が適切となります。
抽出された複数の新商品番号は、いずれもSELECT句により「新モデルあり」という文字列に変換されるため、結果的に全く同一の行になります。したがって、(あ)の場所（SELECT句の直後）に「DINSTICT」を指定することによって重複を排除できます。
∴場合：一つの商品に複数の新モデルが存在する場合
場所：(あ)
字句：DISTINCT
