応用情報技術者過去問題 平成30年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
備品購買システムの設計と実装に関する次の記述を読んで，設問1～4に答えよ。
R社は，ソフトウェアパッケージの開発及び販売を行う中堅企業である。これまで備品の購買は，総務部が表計算ソフトを用いて管理し，行っていた。このたび，見積依頼や発注，納品された備品の確認などを円滑に行うために，備品購買システムを構築することになった。
備品購買の処理の流れとシステム化対象を表1に示す。
この処理の流れから検討した，備品購買システムのデータベースのE-R図を図1に示す。
このデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
〔相見積り機能の検討〕
備品購買システムに相見積り機能を追加することを検討する。相見積り機能とは，複数の取引先へ同じ内容の見積依頼を出す機能である。これによって，より安い価格を提示した取引先へ発注を行うことができるようになる。見積依頼を一度に複数の取引先へ出すために，見積依頼エンティティを二つのエンティティに分けることを考える。
一つ目のエンティティは，複数の取引先への見積依頼を束ねるエンティティとして，主キーに見積依頼番号，属性に見積依頼日と見積依頼者，aをもたせる。
二つ目のエンティティは，各取引先への見積依頼を管理するエンティティとして，主キーにdとe，属性に取引先担当者をもたせる。
この変更に伴い，fエンティティにも変更を加えることで，この機能を実装することができた。
〔検収機能の作成〕
検収のために，発注した各商品の数量と納品された数量を，商品番号の昇順に一覧表示するSQL文を図2に示す。ここで，":発注番号"は，指定された発注番号を格納する埋込み変数である。
なお，関数 COALESCE(A，B) は，A が NULL でないときは A を，A が NULL のときは B を返す。
〔返品対応〕
備品購買システムが完成し，運用が開始されてから数か月後，総務部から問合せがあった。取引先から納品された商品を登録した後，利用部門から商品の一部に問題があったので返品したが，その際の情報を記録したい，とのことであった。
納品登録したレコード中の納品数量から返品した数を減らす方法をまず考えたが，その方法では，納品された商品数量や返品したという事実を記録することができない。そこで，データベースの定義や納品登録した際のレコードには変更を加えずに，①納品表と納品明細表にそれぞれ新しいレコードを追加することで，返品に関する情報を記録することができた。
R社は，ソフトウェアパッケージの開発及び販売を行う中堅企業である。これまで備品の購買は，総務部が表計算ソフトを用いて管理し，行っていた。このたび，見積依頼や発注，納品された備品の確認などを円滑に行うために，備品購買システムを構築することになった。
備品購買の処理の流れとシステム化対象を表1に示す。
このデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
備品購買システムに相見積り機能を追加することを検討する。相見積り機能とは，複数の取引先へ同じ内容の見積依頼を出す機能である。これによって，より安い価格を提示した取引先へ発注を行うことができるようになる。見積依頼を一度に複数の取引先へ出すために，見積依頼エンティティを二つのエンティティに分けることを考える。
一つ目のエンティティは，複数の取引先への見積依頼を束ねるエンティティとして，主キーに見積依頼番号，属性に見積依頼日と見積依頼者，aをもたせる。
二つ目のエンティティは，各取引先への見積依頼を管理するエンティティとして，主キーにdとe，属性に取引先担当者をもたせる。
この変更に伴い，fエンティティにも変更を加えることで，この機能を実装することができた。
〔検収機能の作成〕
検収のために，発注した各商品の数量と納品された数量を，商品番号の昇順に一覧表示するSQL文を図2に示す。ここで，":発注番号"は，指定された発注番号を格納する埋込み変数である。
なお，関数 COALESCE(A，B) は，A が NULL でないときは A を，A が NULL のときは B を返す。
備品購買システムが完成し，運用が開始されてから数か月後，総務部から問合せがあった。取引先から納品された商品を登録した後，利用部門から商品の一部に問題があったので返品したが，その際の情報を記録したい，とのことであった。
納品登録したレコード中の納品数量から返品した数を減らす方法をまず考えたが，その方法では，納品された商品数量や返品したという事実を記録することができない。そこで，データベースの定義や納品登録した際のレコードには変更を加えずに，①納品表と納品明細表にそれぞれ新しいレコードを追加することで，返品に関する情報を記録することができた。
設問1
図1及び本文中のa図1中のb，cに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
解答入力欄
- a：
- b：
- c：
解答例・解答の要点
- a：希望回答日
- b：-
- c：→
解説
〔aについて〕
表1の見積依頼の処理の概要には「依頼された備品の商品番号と商品名を調べ，数量及び希望回答日と一緒に入力する」とあります。見積依頼エンティティには希望回答日を管理する属性がないので、追加する必要があります。したがって、aには「希望回答日」が当てはまります。
∴a＝希望回答日
〔bについて〕
見積依頼エンティティと見積エンティティのカーディナリティ（多重度）は1対1であり、見積エンティティは見積依頼エンティティの主キー"見積依頼番号"を外部キーにもちます。また、見積エンティティと発注エンティティのカーディナリティも1対1であり、発注エンティティは見積エンティティの主キー"見積番号"を外部キーにもちます。そして、見積依頼、見積、発注の各処理における商品に関する情報は、商品ごとに各明細エンティティでもっています。以上の関係より、見積依頼エンティティと見積エンティティと発注エンティティはある1回のある取引先への（関係する全商品をひとまとめにした）見積依頼から発注までの流れを表したものと考えられます。要するに、1つの発注につき、1つの見積及び見積明細が発生するということです。
このことを踏まえて、表1の検収の処理の概要を見ると「発注した商品が全て納品されたことを確認する」とあり、検収は1つの発注に関係する全商品が届いた後に1回だけ行われることがわかります。つまり、検収エンティティは1回の発注に関係する全商品の検収記録をひとまとめにしたエンティティと解釈することができます。したがって、発注エンティティと検収エンティティのカーディナリティは1対1であり、bには「―」が当てはまります。
∴b＝―
〔cについて〕
発注エンティティと納品エンティティには共通の属性"発注番号"があります。この属性"発注番号"は、発注エンティティ側では主キーであり、発注を一意に特定します。また、納品エンティティ側では外部キーであり、表1の納品の処理の概要には「複数回に分けて商品が届くことがある」とあるため、1つの発注に対する納品が異なる日時に行われることが考えられます（つまり、同一の発注番号の値を持つ複数のレコードが存在し得るということです）。したがって、発注エンティティと納品エンティティのカーディナリティは1対多であり、cには「→」が当てはまります。
∴c＝→
表1の見積依頼の処理の概要には「依頼された備品の商品番号と商品名を調べ，数量及び希望回答日と一緒に入力する」とあります。見積依頼エンティティには希望回答日を管理する属性がないので、追加する必要があります。したがって、aには「希望回答日」が当てはまります。
∴a＝希望回答日
〔bについて〕
見積依頼エンティティと見積エンティティのカーディナリティ（多重度）は1対1であり、見積エンティティは見積依頼エンティティの主キー"見積依頼番号"を外部キーにもちます。また、見積エンティティと発注エンティティのカーディナリティも1対1であり、発注エンティティは見積エンティティの主キー"見積番号"を外部キーにもちます。そして、見積依頼、見積、発注の各処理における商品に関する情報は、商品ごとに各明細エンティティでもっています。以上の関係より、見積依頼エンティティと見積エンティティと発注エンティティはある1回のある取引先への（関係する全商品をひとまとめにした）見積依頼から発注までの流れを表したものと考えられます。要するに、1つの発注につき、1つの見積及び見積明細が発生するということです。
このことを踏まえて、表1の検収の処理の概要を見ると「発注した商品が全て納品されたことを確認する」とあり、検収は1つの発注に関係する全商品が届いた後に1回だけ行われることがわかります。つまり、検収エンティティは1回の発注に関係する全商品の検収記録をひとまとめにしたエンティティと解釈することができます。したがって、発注エンティティと検収エンティティのカーディナリティは1対1であり、bには「―」が当てはまります。
∴b＝―
〔cについて〕
発注エンティティと納品エンティティには共通の属性"発注番号"があります。この属性"発注番号"は、発注エンティティ側では主キーであり、発注を一意に特定します。また、納品エンティティ側では外部キーであり、表1の納品の処理の概要には「複数回に分けて商品が届くことがある」とあるため、1つの発注に対する納品が異なる日時に行われることが考えられます（つまり、同一の発注番号の値を持つ複数のレコードが存在し得るということです）。したがって、発注エンティティと納品エンティティのカーディナリティは1対多であり、cには「→」が当てはまります。
∴c＝→
設問2
本文中のd～fに入れる適切な字句を答えよ。
解答入力欄
- d：
- e：
- f：
解答例・解答の要点
- d：見積依頼番号
- e：取引先番号
- f：見積依頼明細
解説
〔d，eについて〕
相見積り機能を追加すると、同じ見積りを複数の取引先に提示することになります。つまり、2つ目のエンティティには、同じ"見積依頼番号"をもつレコードが複数存在することになります。このため、分割前のように"見積依頼番号"だけではレコードの特定ができません。よって、2つ目のエンティティは、"見積依頼番号"と"取引先番号"の複合主キーとし、1つの見積依頼をどの取引先に行ったかでレコードを特定することになります。
したがって、dには「見積依頼番号」、eには「取引先番号」が当てはまります（順不同）。
∴d，e＝見積依頼番号、取引先番号
〔fについて〕
表1の見積依頼の処理の概要には「取引先のカタログを使って依頼された備品の商品番号と商品名を調べ…」とあります。つまり、見積依頼明細に記録される"商品番号"、"商品名"及び"商品数量"は、取引先ごとに固有の情報ということになります。
見積依頼エンティティを分割する前は、1つの見積依頼について1つの取引先が確定していたため、見積依頼明細もその取引先の商品情報だけで足りましたが、相見積り機能の追加後は取引先ごとに見積依頼明細が発生します。この場合、各見積依頼明細エンティティがどの取引先に対応するかの情報が必要なので、"見積依頼番号"と"見積依頼明細番号"の組だと主キーとして不十分です。よって、「見積依頼明細」エンティティの主キーとして"取引先番号"を加える必要があります。
∴f＝見積依頼明細
※分割された一つ目のエンティティを「見積依頼」、二つ目のエンティティを「見積依頼管理」とすると、相見積り機能に関係するE-R図は次のようになります。
相見積り機能を追加すると、同じ見積りを複数の取引先に提示することになります。つまり、2つ目のエンティティには、同じ"見積依頼番号"をもつレコードが複数存在することになります。このため、分割前のように"見積依頼番号"だけではレコードの特定ができません。よって、2つ目のエンティティは、"見積依頼番号"と"取引先番号"の複合主キーとし、1つの見積依頼をどの取引先に行ったかでレコードを特定することになります。
したがって、dには「見積依頼番号」、eには「取引先番号」が当てはまります（順不同）。
∴d，e＝見積依頼番号、取引先番号
〔fについて〕
表1の見積依頼の処理の概要には「取引先のカタログを使って依頼された備品の商品番号と商品名を調べ…」とあります。つまり、見積依頼明細に記録される"商品番号"、"商品名"及び"商品数量"は、取引先ごとに固有の情報ということになります。
見積依頼エンティティを分割する前は、1つの見積依頼について1つの取引先が確定していたため、見積依頼明細もその取引先の商品情報だけで足りましたが、相見積り機能の追加後は取引先ごとに見積依頼明細が発生します。この場合、各見積依頼明細エンティティがどの取引先に対応するかの情報が必要なので、"見積依頼番号"と"見積依頼明細番号"の組だと主キーとして不十分です。よって、「見積依頼明細」エンティティの主キーとして"取引先番号"を加える必要があります。
∴f＝見積依頼明細
※分割された一つ目のエンティティを「見積依頼」、二つ目のエンティティを「見積依頼管理」とすると、相見積り機能に関係するE-R図は次のようになります。
設問3
図2中のg～jに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。
解答入力欄
- g：
- h：
- i：
- j：
解答例・解答の要点
- g：DLI.納品数量計
- h：OD.発注番号 = :発注番号
- i：GROUP BY DE.発注番号，DD.商品番号
- j：ORDER BY ORD.商品番号
解説
SQL文の処理順序に従って h→i→j→g の順で解説していきます。
〔hについて〕
ORDを定義するSQL文は、発注テーブル（OD）と発注明細テーブル（OT）を発注番号で内部結合し、条件hを満たすレコードの発注番号、商品番号、商品名、発注数量を抜き出します。
図2のSQL文の目的は、指定された発注番号に対応する納品について一覧表示することですので、WHERE句にはOD側（またはOT側）の"発注番号"列の値が指定された発注番号となるような条件を指定します。したがって、hには「OD.発注番号 = :発注番号」が当てはまります（「OT.発注番号 = :発注番号」でも正解です）。
※実は発注テーブルと発注明細テーブルを結合しているのは冗長で、発注テーブル側にしかない列を抜き出す必要がないため、発注明細テーブルのみで十分です（発注番号は発注明細テーブルからでも抜き出せます）。
∴h＝OD.発注番号 = :発注番号
〔iについて〕
DLIを定義するSQL文は、納品テーブル（DE）と納品明細テーブル（DD）を納品番号で内部結合し、発注番号列の値が指定された発注番号であるレコードの発注番号、商品番号、iで指定した列の値（の組み合わせ）ごとの納品数量の合計（納品数量計）を抜き出します。
SELECT句で集約関数SUM以外に列名も指定されていることから、GROUP BY句が必要であると判断できます。また、GROUP BY句でグルーピングした場合にSELECT句に記述できるのは、集約関数またはGROUP BY句で指定した列名のみです。このため、SELECTで指定されているDE.発注番号とDD.商品番号は、グループ化対象としなければなりません。したがって、iには「GROUP BY DE.発注番号, DD.商品番号」が当てはまります。
∴i＝GROUP BY DE.発注番号, DD.商品番号
〔jについて〕
本文中には図2のSQL文の説明として「商品番号の昇順に一覧表示する」とあります。空欄がSQL文の最後であること及びORDER BY句がないことから、ここにはORDER BY句が入ると判断できます。主問合せのSELECT文では ORD.商品番号 が指定されているので、「ORDER BY ORD.商品番号」が当てはまります（最後にASCが付いていても可)。
∴j＝ORDER BY ORD.商品番号
〔gについて〕
〔検収機能の作成〕には「検収のために，発注した各商品の数量と納品された数量を，商品番号の昇順に一覧表示する」とあります。よって、COALESCE(g, 0)は納品された数量を表示する列と考えられます。
納品された数量を保持しているのはDLIの"納品数量計"列です。ただし、発注済の商品がまだ納品されていない場合、納品明細テーブルにその商品に対応するレコードが存在せず、DLIにも当該商品番号をもつレコードは存在しないことになります。このとき、ORDとDLIを左側外部結合すると、当該商品番号をもつレコードの納品数量計列の値がNULLとなります。このNULLを0に変換するためにCOALESCE関数が用いられています。したがって、gには「DLI.納品数量計」が当てはまります（※DLI.はつけなくてもSQL文は動作しますが、問題の指示により列名の前に表名をつける必要があるので注意してください）。
∴g＝DLI.納品数量計
〔hについて〕
図2のSQL文の目的は、指定された発注番号に対応する納品について一覧表示することですので、WHERE句にはOD側（またはOT側）の"発注番号"列の値が指定された発注番号となるような条件を指定します。したがって、hには「OD.発注番号 = :発注番号」が当てはまります（「OT.発注番号 = :発注番号」でも正解です）。
※実は発注テーブルと発注明細テーブルを結合しているのは冗長で、発注テーブル側にしかない列を抜き出す必要がないため、発注明細テーブルのみで十分です（発注番号は発注明細テーブルからでも抜き出せます）。
∴h＝OD.発注番号 = :発注番号
〔iについて〕
SELECT句で集約関数SUM以外に列名も指定されていることから、GROUP BY句が必要であると判断できます。また、GROUP BY句でグルーピングした場合にSELECT句に記述できるのは、集約関数またはGROUP BY句で指定した列名のみです。このため、SELECTで指定されているDE.発注番号とDD.商品番号は、グループ化対象としなければなりません。したがって、iには「GROUP BY DE.発注番号, DD.商品番号」が当てはまります。
∴i＝GROUP BY DE.発注番号, DD.商品番号
〔jについて〕
本文中には図2のSQL文の説明として「商品番号の昇順に一覧表示する」とあります。空欄がSQL文の最後であること及びORDER BY句がないことから、ここにはORDER BY句が入ると判断できます。主問合せのSELECT文では ORD.商品番号 が指定されているので、「ORDER BY ORD.商品番号」が当てはまります（最後にASCが付いていても可)。
∴j＝ORDER BY ORD.商品番号
〔gについて〕
〔検収機能の作成〕には「検収のために，発注した各商品の数量と納品された数量を，商品番号の昇順に一覧表示する」とあります。よって、COALESCE(g, 0)は納品された数量を表示する列と考えられます。
納品された数量を保持しているのはDLIの"納品数量計"列です。ただし、発注済の商品がまだ納品されていない場合、納品明細テーブルにその商品に対応するレコードが存在せず、DLIにも当該商品番号をもつレコードは存在しないことになります。このとき、ORDとDLIを左側外部結合すると、当該商品番号をもつレコードの納品数量計列の値がNULLとなります。このNULLを0に変換するためにCOALESCE関数が用いられています。したがって、gには「DLI.納品数量計」が当てはまります（※DLI.はつけなくてもSQL文は動作しますが、問題の指示により列名の前に表名をつける必要があるので注意してください）。
∴g＝DLI.納品数量計
設問4
〔返品対応〕について，本文中の下線①にある追加したレコードのうち，納品明細表に追加したのはどのようなレコードか。返品に関する情報を記録することを考慮して，30字以内で述べよ。
解答入力欄
- o：
解答例・解答の要点
- o：返品した商品の数量をマイナスの値に設定したレコード (25文字)
解説
本文中に「データベースの定義や納品登録した際のレコードには変更を加えずに，…」とあるので、既存の列に何らかの値を設定したレコードを追加することで、返品の記録を残せるようにしなければなりません。図2のSQL文で納品明細テーブルの"納品数量"列の値を合計していることを踏まえると、返品を表すレコードの"納品数量"列の値は数値である必要があります。また、返品したという事実がわかることも求められるため、通常の納品の際には入ることのない値を入力する必要があります。
これを実現する方法として、返品時に、納品表にその返品を記録する新たなレコードを追加し、納品明細表には"納品数量"列の値をマイナスの数値としたレコードを追加することが考えられます。そうすれば、合計したときに納品数から差し引くことができ、かつ、通常納品の際のプラス値との区別で返品であることがわかります。したがって、答えは「返品した商品の数量をマイナスの値に設定したレコード」となります。
∴返品した商品の数量をマイナスの値に設定したレコード
これを実現する方法として、返品時に、納品表にその返品を記録する新たなレコードを追加し、納品明細表には"納品数量"列の値をマイナスの数値としたレコードを追加することが考えられます。そうすれば、合計したときに納品数から差し引くことができ、かつ、通常納品の際のプラス値との区別で返品であることがわかります。したがって、答えは「返品した商品の数量をマイナスの値に設定したレコード」となります。
∴返品した商品の数量をマイナスの値に設定したレコード
