2017年 秋期 応用情報技術者試験 問6

青果卸売業の取引システム改修

U社は，卸売市場で青果物を取り扱う卸売業者である。生産者に対して青果物の販路を提供し，仲卸業者に対して迅速かつ安定的に青果物を供給している。生産者からは複数のパレット（運搬用の荷台）に積まれた青果物を一括で仕入れ，仲卸業者にはパレット単位で販売する。仲卸業者に販売された青果物は，箱単位に小分けされ，小売業者に販売される。青果物の取引の流れを図1に示す。

U社では，青果物の仕入と販売を管理するための取引システム，青果物の入荷や出荷を管理するための物流システム，仲卸業者への代金の請求や生産者への代金の支払，卸売手数料の精算を管理するための売掛・買掛システムなどが稼働している。

U社では，更なる安定価格・安定供給を実現するために，新たに予約相対売りという販売方法を行えるよう取引システムを改修することにした。

[現行の取引システムの概要]

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

現行のデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理している。

[取引システムの改修]

予約相対売りとは，卸売業者と仲卸業者との間において，あらかじめ締結した契約に基づき青果物を仕入・販売する取引である。仲卸業者は複数の品目，複数の産地，複数の販売予定日の青果物を一括で予約できる。このとき，生産者の指定はできない。

販売担当者が仲卸業者の希望する品目，産地，パレット数，単価，販売予定日を予約日と仲卸業者の組合せを軸に取りまとめ，それが予約情報として取引システムに入力される。予約情報を取りまとめる軸は今後変更される可能性がある。予約情報は品目や産地に応じて各仕入担当者に割り当てられ，その情報を取引システムに入力される。

仕入担当者は予約情報に基づいて必要な青果物を生産者から仕入れる。

予約情報を管理するために，図2のE-R図に，図3に示す予約エンティティ，予約明細エンティティ及び予約仕入対応エンティティを追加する。また，販売明細などの予約明細に対応しているかを後から確認できるようにするために，予約明細エンティティの主キーを販売明細エンティティに外部キーとして加える。

[販売伝票及び返品伝票の入力]

取引時間は毎日午前3〜11時である。販売担当者は毎日一人当たり300件以上の取引を行っている。取引を迅速に行うために，取引の現場では販売担当者と仲卸業者が合意した販売条件を紙の販売伝票に記録している。販売伝票のヘッダ部には販売日，販売区分，仲卸業者，販売担当者が記載され，明細部には販売した青果物の仕入番号，仕入明細番号，パレット数，単価が複数記載される。販売伝票は事務員が当日の日中にまとめて取引システムに入力している。

返品が発生した場合には，販売担当者が返品伝票に返品内容の詳細を記録し，それが販売伝票と同様の流れで取引システムに入力される。

[取引日報の出力]

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

SELECT 品目コード, 品目名, 産地コード, 産地名,
       f AS 合計返品金額, SUM(t1.パレット数) AS 合計返品数量
FROM 返品 t1
INNER JOIN 販売明細 t2 USING (販売番号, 販売明細番号)
                          g
INNER JOIN 品目 USING (品目コード)
INNER JOIN 産地 USING (産地コード)
WHERE 返品日 = CURRENT_DATE
GROUP BY h
ORDER BY 品目コード ASC, 産地コード ASC