平成29年秋期試験午後問題 問6

問6 データベース

⇱問題PDF
青果卸売業の取引システム改修に関する次の記述を読んで,設問1~3に答えよ。
 U社は,卸売市場で青果物を取り扱う卸売業者である。生産者に対して青果物の販路を提供し,仲卸業者に対して迅速かつ安定的に青果物を供給している。生産者からは複数のパレット(運搬用の荷台)に積まれた青果物を一括で仕入れ,仲卸業者にはパレット単位で販売する。仲卸業者に販売された青果物は,箱単位に小分けされ,小売業者に販売される。青果物の取引の流れを図1に示す。
pm06_1.gif
 U社では,青果物の仕入と販売を管理するための取引システム,青果物の入荷や出荷を管理するための物流システム,仲卸業者への代金の請求や生産者への代金の支払,卸売手数料の精算を管理するための売掛・買掛システムなどが稼働している。
 U社では,更なる安定価格・安定供給を実現するために,新たに予約相対(あいたい)売りという販売方法を行えるように取引システムを改修することにした。

〔現行の取引システムの概要〕
 現行の取引システムのE-R図(抜粋)を図2に示す。U社の仕入担当者が生産者から青果物を仕入れ,U社の販売担当者が仲卸業者に販売している。仕入方法は,生産者から委託された商品を販売し,その代金から卸売手数料を差し引いた金額を生産者に支払う委託仕入が主である。近年はU社が自ら商品を購入する買付仕入も増加している。販売方法には,複数の仲卸業者が互いに価格を競い合い,最も高い価格を付けた仲卸業者に販売する競りと,一人の販売担当者と一人の仲卸業者が話合いで価格を決める相対売りの二つがある。仕入方法は仕入区分として管理され,販売方法は販売区分として管理される。仕入金額は仕入明細のパレット数と単価との積の総和であり,仕入伝票の入力時に取引システムによって算出される。販売金額も仕入金額と同様の方法で求める。青果物は毎日売り切ることが原則となっており,在庫はもたない。販売した青果物が傷んでいた場合は,販売日の取引時間内だけ仲卸業者からの返品を受け付ける。
pm06_2.gif
 現行のデータベースでは,E-R図のエンティテイ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理している。

〔取引システムの改修〕
 予約相対売りとは,卸売業者と仲卸業者との間において,あらかじめ締結した契約に基づき青果物を仕入・販売する取引である。仲卸業者は複数の品目,複数の産地,複数の販売予定日の青果物を一括で予約できる。このとき,生産者の指定はできない。販売担当者が仲卸業者の希望する品目,産地,パレット数,単価,販売予定日を予約日と仲卸業者の組合せを軸に取りまとめ,それが予約情報として取引システムに入力される。予約情報を取りまとめる軸は今後変更される可能性がある。予約情報は品目や産地に応じて各仕入担当者に割り当てられ,その情報も取引システムに入力される。仕入担当者は予約情報に基づいて必要な青果物を生産者から仕入れる。
 予約情報を管理するために,図2のE-R図に,図3に示す予約エンティティ,予約明細エンティティ及び予約仕入対応エンティティを追加する。また,販売明細がどの予約明細に対応しているかを後から確認できるようにするために,予約明細エンティティの主キーを販売明細エンティティに外部キーとして加える。
pm06_3.gif
〔販売伝票及び返品伝票の入力〕
 取引時間は毎日午前3~11時である。販売担当者は毎日一人当たり300件以上の取引を行っている。取引を迅速に行うために,取引の現場では販売担当者と仲卸業者が合意した販売条件を紙の販売伝票に記録している。販売伝票のヘッダ部には販売日,販売区分,仲卸業者,販売担当者が記載され,明細部には販売した青果物の仕入番号,仕入明細番号,パレット数,単価が複数記載される。販売伝票は事務員が当日の日中にまとめて取引システムに入力している。
 返品が発生した場合には,販売担当者が返品伝票に返品内容の詳細を記録し,それが販売伝票と同様の流れで取引システムに入力される。

〔取引日報の出力〕
 各営業日の販売実績は取引日報としてまとめられ,販売部門長に報告される。取引日報は,各営業日の全伝票の入力が完了した後,当日中に出力する。販売部門長から各営業日の返品実績も報告するよう指示があり,新たに合計返品金額と合計返品数量を取引日報に出力することになった。出力結果は品目ごとに産地別に当日中に集計する。合計返品金額と合計返品数量を算出するためのSQL文を図4に示す。ここで,USING句は名前付き列結合を示し,USING句内の列名は内部結合における等比較結合の結合条件に用いられる。
pm06_4.gif

設問1

現行の取引システムのE-R図について,(1),(2)に答えよ。
  • 図2中のacに入れる適切なエンティテイ間の関連及び属性名を答え,E-R図を完成させよ。エンティティ間の関連及び属性名の表記は図2の凡例及び注記に倣うこと。
    (※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)
  • 図2中で,他の属性から求めることが可能であるが,処理性能を改善するために追加されている属性の属性名を全て答えよ。

解答例・解答の要点

  • a:
    b:
    c:販売区分コード
  • 仕入金額,販売金額

解説

  • aについて〕
    仕入明細エンティティと販売明細エンティティには共通の属性"仕入番号"と"仕入明細番号"があります。この2つの属性は、仕入明細エンティティ側では(複合)主キーであり、仕入明細(仕入れた青果物のうち、1品目ずつの詳細)を一意に特定します。また、販売明細エンティティ側では(複合)外部キーとなっています。

    本文中には「生産者からは複数のパレット(運搬用の荷台)に積まれた青果物を一括で仕入れ,仲卸業者にはパレット単位で販売する。」とあるため、仕入明細の1つの品目が複数の仲卸業者に販売された場合には、1つの仕入明細について複数の販売明細が紐付くことが考えられます。つまり、販売明細エンティティには同一の仕入番号・仕入明細番号の値の組を持つ複数のレコードが存在しえるということです。例えば、ある日に青果物Xを10パレット仕入れ、それを仲卸業者Aに5パレット、仲卸業者Bに3パレット、仲卸業者Cに2パレット販売するようなケースです。

    したがって、仕入明細エンティティと販売明細エンティティのカーディナリティ(多重度)は1対多であり、[a]には「→」が入ります。

    a=→

    bについて〕
    販売明細エンティティの(複合)主キーは"販売番号"と"販売明細番号"であり、このうち"販売番号"は販売エンティティの主キーとなっています。販売エンティティは販売明細エンティティをひとまとめにしたものと考えられ、1つの販売に対して複数の販売明細が発生することが想定されています(同一日に同一の販売担当者が同一の仲卸業者に複数種類の品目を販売した場合)。

    したがって、販売エンティティと販売明細エンティティのカーディナリティは1対多であり、bには「↓」が当てはまります。

    b=↓

    cについて〕
    〔現行の取引システムの概要〕には、「販売方法は販売区分として管理される。」とあります。販売エンティティには販売区分を表す属性がないので、これが追加すべき属性となります。販売区分エンティティと販売エンティティのカーディナリティは1対多であるにもかかわらず、販売エンティティ側には販売区分エンティティの主キーである"販売区分コード"に相当する属性がありません。よって、cは"販売区分コード"とすればよいと考えられます。この場合、販売エンティティの属性"販売区分コード"は、販売区分エンティティの主キーを参照する外部キーです。外部キーには破線の下線を付けるので、cには「販売区分コード」が入ります。

    c販売区分コード

  • 〔現行の取引システムの概要〕には「仕入金額は仕入明細のパレット数と単価との積の総和であり,仕入伝票の入力時に取引システムによって算出される。販売金額も仕入金額と同様の方法で求める。」とあります。よって、仕入エンティティの属性"仕入金額"は、仕入明細エンティティの属性"パレット数"と"単価"から求めることができます。また同様に、販売エンティティの属性"販売金額"は、販売明細エンティティの属性"パレット数"と"単価"から求めることができます。

    計算式などにより、既存の属性から論理的に導ける値を持つ属性を「導出属性(または派生属性)」といいます。上記2つの属性は、パレット数×単価の総和として求めることも可能であり、最小かつ完備の原則から言えば冗長と言えます。ただ、2つの金額を計算で求める場合、行ごと明細をグループ化してパレット数×単価の総和を求める、という計算処理を表参照の度にしなければならず処理性能の低下が懸念されます。"仕入金額"と"販売金額"は、この計算処理を不要にし、処理性能を改善する目的で意図的に追加されていると考えられます。

    ∴仕入金額,販売金額

設問2

〔取引システムの改修〕について,(1),(2)に答えよ。
  • 図3中のdeに入れる適切な属性名を,図2中の用語を用いて,全て答えよ。属性名の表記は図2の凡例及び注記に倣うこと。
    (※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください。)
  • 図3中の予約エンティティにおいて,主キーに代用キーとして予約番号を用いる理由を,本文中の用語を用いて,35字以内で述べよ。

解答例・解答の要点

  • d:仲卸業者コード
    e:品目コード産地コード,パレット数,単価,仕入担当者コード
  • 予約情報を取りまとめる軸は今後変更される可能性があるから (28文字)

解説

  • dについて〕
    〔取引システムの改修〕には「販売担当者が(中略)予約日と仲卸業者の組合せを軸に取りまとめ,それが予約情報として取引システムに入力される。」とあり、予約には仲卸業者の情報を記録することがわかります。しかし、予約エンティティには仲卸業者についての属性がないので、仲卸業者を特定できる"仲卸業者コード"を追加する必要があります。この場合、予約エンティティの属性"仲卸業者コード"は、仲卸業者エンティティの主キーを参照する外部キーです。したがって、[d]には「仲卸業者コード」が入ります。

    仲卸業者コード

    eについて〕
    〔取引システムの改修〕には「販売担当者が仲卸業者の希望する品目,産地,パレット数,単価,販売予定日を(中略)それが予約情報として取引システムに入力される。(中略)予約情報は品目や産地に応じて各仕入担当者に割り当てられ,その情報も取引システムに入力される。」とあります。よって、予約明細エンティティには品目を示す"品目コード"、産地を示す"産地コード"、"パレット数"、"単価"、そして仕入担当者を示す"仕入担当者コード"を追加する必要があります。このとき、予約明細エンティティの外部キー(追加分)は次のようになります。
    • "品目コード":品目エンティティの主キーを参照
    • "産地コード":産地エンティティの主キーを参照
    • "仕入担当者コード":従業員エンティティの主キー"従業員コード"を参照
    したがって[e]に入る属性は、以下の5つです。
    品目コード
    産地コード
    パレット数
    単価
    仕入担当者コード

    e品目コード産地コード,パレット数,単価,仕入担当者コード

    ※予約情報に対応するために変更されたE-R図(関係する部分のみ)は次のようになります。
    pm06_5.gif
  • 〔取引システムの改修〕には「販売担当者が(中略)予約日と仲卸業者の組合せを軸に取りまとめ,それが予約情報として取引システムに入力される。」とあり、予約エンティティは"予約番号"がなくとも、"予約日","仲卸業者コード"の組み合わせによっても予約を一意に特定できることがわかります。

    複合主キーには、本来持ち合わせている属性だけを使用してテーブルを構成することができるという利点もあるのですが、①SQLの結合演算記述が煩雑になる、②テーブル間の依存度が高くなる、③テーブル構造の仕様変更の影響を受けやすいなどの弊害があります。このようなときはデータをシンプルに扱うために、単一でレコードを特定可能な属性をテーブルに追加し、複合キーの代わりにそれを主キーとする手法が用いられます。この追加されて主キーとなる属性を代用キー(サロゲートキー)といい、通常は連番コードなどのように値の固有性を容易に確保しやすいものを設定します。値自体に業務上の意味がない注文Noや社員IDなどがこれに該当します。予約エンティティで言えば、"予約番号"は代用キー(サロゲートキー)であり、"予約日","仲卸業者コード"の組は代理キー(代替キー)という位置付けになります。

    本文中には「予約情報を取りまとめる軸は今後変更される可能性がある」とあるので、"予約日","仲卸業者コード"を(複合)主キーとしてしまうと、変更時に問題が起こり得ることが想定されます。したがって、これが主キーに代用キーである"予約番号"を用いる理由となります。

    ∴予約情報を取りまとめる軸は今後変更される可能性があるから

設問3

図4中のfhに入れる適切な字句を答えよ。

解答例・解答の要点

f:SUM(t2.単価 * t1.パレット数)
g:INNER JOIN 仕入明細 USING(仕入番号,仕入明細番号)
h:品目コード,品目名,産地コード,産地名
又は
品目コード,産地コード

解説

fについて〕
合計返品金額を求める式を考えます。返品金額は「単価×パレット数」で求めることができます。返品表(t1)には返品されたパレット数は記録されているものの、単価はないので、販売明細表(t2)のものを使用します。そして、集約関数 SUM を用いて返品金額を合計すれば合計返品金額を求めることができます。したがって、[f]には「SUM(t2.単価 * t1.パレット数)」が入ります。

f=SUM(t2.単価 * t1.パレット数)

gについて〕
SELECT句で品目名と産地名が指定されているため、品目表と産地表を参照する必要があります。このために図4のSQL文では、品目表の品目コード列、産地表の産地コード列で内部結合していますが、返品表や販売明細表には品目コードや産地コードという列はありません。よって、販売明細エンティティと対応関係がある仕入明細エンティティを経由して、"品目コード"と"産地コード"を参照することになります。販売明細表と仕入明細表には、共に仕入番号列と仕入明細番号列があるので、これらの列で内部結合すればよいことがわかります。したがって、[g]には「INNER JOIN 仕入明細 USING(仕入番号, 仕入明細番号)」が入ります。

g=INNER JOIN 仕入明細 USING(仕入番号,仕入明細番号)

hについて〕
〔取引日報の出力〕には「出力結果は品目ごとに産地別に当日中に集計する。」とありますので、GROUP BY句を用いて品目コード列と産地コード列を指定することがわかります。GROUP BY句を使用する場合、SELECT句に指定できるのは集約関数とGROUP BY句で指定した列のみです(※)から、GROUP BY句ではSELECT句で指定されている4つの列名を指定します。したがって、[h]には「品目コード, 品目名, 産地コード, 産地名」が入ります。

※SQLの規格であるSQL99からは、GROUP BY句で指定した列から一意に特定できる列(この問題だと品目コード→品目名、産地コード→産地名)はGROUP BY句で指定しなくてもSELECT句に記述することができるようになっています。したがって、「品目コード, 産地コード」も正解です。

h=品目コード,品目名,産地コード,産地名
 または 品目コード,産地コード
模範解答

Pagetop