問題プレビューID: 498f23ce応用情報技術者試験 2023年(令和5年度)秋期 午後

問 6
応用情報技術者試験 2023年(令和5年度)秋期 午後

在庫管理システムに関する次の記述を読んで,設問に答えよ。

M社は,ネットショップで日用雑貨の販売を行う企業である。M社では,在庫管理について次の課題を抱えている。

・在庫が足りない商品の注文を受けることができず,機会損失につながっている。

・商品の仕入れの間隔や個数を調整する管理サイクルが長く,余計な在庫を抱える傾向にある。

〔現状の在庫管理〕

現在,在庫管理を次のように行っている。

・商品の注文を受けた段階で,出荷先に最も近い倉庫を見つけて,その倉庫の在庫から注文個数を引き当てる。この引き当てられた注文個数を引当済数という。各倉庫において,引き当てられた各商品単位の個数の総計を引当済総数という。

・実在庫数から引当済総数を引いたものを在庫数といい,在庫数以下の注文個数の場合だけ注文を受け付ける。

・商品が倉庫に入荷すると,入荷した商品の個数を実在庫数に足し込む。

・倉庫から商品を出荷すると,出荷個数を実在庫数から引くとともに引当済総数からも引くことで,引き当ての消し込みを行う。

M社では,月末の月次バッチ処理で毎月の締めの在庫数と売上個数を記録した分析用の表を用いて,商品ごとの在庫数と売上個数の推移を評価している。

また,期末に商品の在庫回転日数を集計して,来期の仕入れの間隔や個数を調整している。

M社では,商品の在庫回転日数を,簡易的に次の式で計算している。

在庫回転日数 = 期間内の平均在庫数×期間内の日数÷期間内の売上個数

在庫回転日数の計算において,現状では,期間内の平均在庫数として12か月分の締めの在庫数の平均値を使用している。

現状の在庫管理システムのE-R図(抜粋)を図1に示す。

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

図1 現状の在庫管理システムのE-R図(抜粋)

倉庫: 倉庫コード(主キー),倉庫名,所在地

入荷: 入荷番号(主キー),倉庫コード(外部キー),入荷日

入荷明細: 入荷番号(主キー),入荷明細番号(主キー),商品コード(外部キー),個数

出荷: 出荷番号(主キー),倉庫コード(外部キー),出荷日,出荷先情報

出荷明細: 出荷番号(主キー),出荷明細番号(主キー),商品コード(外部キー),個数

商品: 商品コード(主キー),商品名,型番,標準単価

在庫: 倉庫コード(主キー),商品コード(主キー),実在庫数,引当済総数

在庫推移状況: 年(主キー),月(主キー),倉庫コード(主キー),商品コード(主キー),在庫数,売上個数

倉庫と入荷,入荷と入荷明細,倉庫と出荷,出荷と出荷明細,倉庫と在庫は1対多の関連をもつ。商品と入荷明細,商品と出荷明細,商品と在庫,商品と在庫推移状況は1対多の関連をもつ。倉庫と在庫推移状況の間の関連が[a]である。

凡例

エンティティ名
属性名
属性名
属性名
⋮

――:1対1,―→:1対多,←→:多対多

注記 属性名の実線の下線は主キー,破線の下線は外部キーを示す。主キーの実線が付いている属性名には,外部キーの破線を付けない。

〔在庫管理システム改修内容〕

課題を解決するために,在庫管理システムに次の改修を行うことにした。

・在庫数が足りない場合は,在庫からは引き当てず,予約注文として受け付ける。なお,予約注文ごとに商品を発注することで,注文を受けた商品の個数が入荷される。

・商品の仕入れの間隔や個数を調整する管理サイクルを短くするために,在庫の評価を月次から日次の処理に変更して,毎日の締めの在庫数と売上個数を在庫推移状況エンティティに記録する。

現状では,在庫数が足りない商品の予約注文を受けようとしても,在庫引当を行うと実在庫数より引当済総数の方が多くなってしまい,注文に応えられない。そこで,予約注文の在庫引当を商品の入荷のタイミングにずらすために,E-R図に予約注文用の二つのエンティティを追加することにした。追加するエンティティを表1に,改修後の在庫管理システムのE-R図(抜粋)を図2に示す。

表1 追加するエンティティ

エンティティ名内容
引当情報予約注文を受けた商品の個数と入荷済となった商品の個数を管理する。
引当予定予約注文を受けた商品の,未入荷の引当済数の総計を管理する。

図2 改修後の在庫管理システムのE-R図(抜粋)

[b](太枠): 引当番号(主キー),倉庫コード(外部キー),商品コード(外部キー),引当済数,入荷済数

[c](太枠): 倉庫コード(主キー),商品コード(主キー),未入荷引当済総数

在庫推移状況: 年(主キー),月(主キー),[d](主キー),倉庫コード(主キー),商品コード(主キー),在庫数,売上個数

入荷明細には引当番号(外部キー)が追加される。その他のエンティティ及び属性は図1と同じである。

注記 新規に追加したエンティティは太枠で表す。

在庫管理システムにおける予約注文を受けた商品の個数に関する処理内容を表2に示す。

表2 在庫管理システムにおける予約注文を受けた商品の個数に関する処理内容

処理タイミング処理内容
予約注文を受けたとき引当情報エンティティのインスタンスを生成して,引当済数には注文を受けた商品の個数を,入荷済数には0を設定する。引当予定エンティティの未入荷引当済総数に注文を受けた商品の個数を足す。
予約注文された商品が入荷したとき[e]エンティティの未入荷引当済総数から入荷した商品の個数を引く。[f]エンティティの実在庫数と引当済総数に入荷した商品の個数を足す。入荷した商品の個数を[g]エンティティの個数に設定し,引当情報エンティティの[h]に足す。
予約注文された商品を出荷したとき出荷した商品の個数を出荷明細エンティティの個数に設定し,在庫エンティティの商品の実在庫数及び引当済総数から引く。

〔在庫の評価〕

より正確かつ迅速に在庫回転日数を把握するために,在庫推移状況エンティティから,期間を1週間(7日間)として,倉庫コード,商品コードごとに,各年月日の6日前から当日までの平均在庫数及び売上個数で在庫回転日数を集計することにする。

可読性を良くするために,SQL文にはウィンドウ関数を使用することにする。

ウィンドウ関数を使うと,FROM句で指定した表の各行ごとに集計が可能であり,各行ごとに集計期間が異なるような移動平均も簡単に求めることができる。ウィンドウ関数で使用する構文(抜粋)を図3に示す。

図3 ウィンドウ関数で使用する構文(抜粋)

〈ウィンドウ関数〉::=
  〈ウィンドウ関数名〉(〈列〉) OVER {〈ウィンドウ名〉 | (〈ウィンドウ指定〉)}

〈WINDOW句〉::=
  WINDOW 〈ウィンドウ名〉 AS (〈ウィンドウ指定〉) [{, 〈ウィンドウ名〉 AS (〈ウィンドウ指定〉)}...]

〈ウィンドウ指定〉::=
  [〈PARTITION BY句〉] [〈ORDER BY句〉] [〈ウィンドウ枠〉]

〈PARTITION BY句〉::=
  PARTITION BY 〈列〉 [{, 〈列〉}...]

注記1 OVERの後に(〈ウィンドウ指定〉)を記載する代わりに,WINDOW句で名前を付けて,〈ウィンドウ名〉で参照することができる。

注記2 PARTITION BY句は指定した列の値ごとに同じ値をもつ行を部分集合としてパーティションにまとめるオプションである。

注記3 ウィンドウ枠の例として,ROWS BETWEEN n PRECEDING AND CURRENT ROWと記載した場合は,n行前(n PRECEDING)から現在行(CURRENT ROW)までの範囲を対象として集計することを意味する。

注記4 …は,省略符号を表し,式中で使用される要素を任意の回数繰り返してもよいことを示す。

ウィンドウ関数を用いて,倉庫コード,商品コードごとに,各年月日の6日前から当日までの平均在庫数及び売上個数を集計するSQL文を図4に示す。

図4 倉庫コード,商品コードごとに,各年月日の6日前から当日までの平均在庫数及び売上個数を集計するSQL文

SELECT 年, 月, 日, 倉庫コード, 商品コード,
  AVG(在庫数) [i] 期間定義 AS 平均在庫数,
  SUM(売上個数) [i] 期間定義 AS 期間内売上個数
FROM 在庫推移状況
WINDOW 期間定義 AS (
  PARTITION BY 倉庫コード, 商品コード
  [j] 年, 月, 日 ASC
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

設問1

図1及び図2中の[a]に入れる適切なエンティティ間の関連を答え,E-R図を完成させよ。なお,エンティティ間の関連の表記は図1の凡例に倣うこと。

設問2

〔在庫管理システム改修内容〕について答えよ。

(1) 図2中の[b],[c]に入れる適切なエンティティ名を表1中のエンティティ名を用いて答えよ。

(2) 図2中の[d]に入れる,在庫推移状況エンティティに追加すべき適切な属性名を答えよ。なお,属性名の表記は図1の凡例に倣うこと。

(3) 表2中の[e]~[h]に入れる適切な字句を答えよ。

設問3

図4中の[i],[j]に入れる適切な字句を答えよ。

模範解答

設問1 a ↓

設問2 (1) b 引当情報,c 引当予定 (2) d 日 (3) e 引当予定,f 在庫,g 入荷明細,h 入荷済数

設問3 i OVER,j ORDER BY

解説

本問では,在庫管理システムの改修を題材として,データベース設計に関する基本的な理解,OLAPに役立つウィンドウ関数の理解,及びBNFからSQL文を作成する能力を問う。

設問1では,倉庫と在庫推移状況の関連を問う。一つの倉庫に対して日ごとの在庫推移状況が複数存在するので,倉庫から在庫推移状況への1対多(↓)である。

設問2(1)では,引当番号をもち予約注文ごとの個数を管理するのが引当情報(b)であり,倉庫・商品ごとの未入荷引当済総数を管理するのが引当予定(c)である。(2)では,在庫の評価を月次から日次に変更するので,在庫推移状況エンティティに追加すべき属性は日(d)である。(3)では,表2の処理内容から,eは未入荷引当済総数をもつ引当予定,fは実在庫数と引当済総数をもつ在庫,gは入荷した商品の個数を設定する入荷明細,hは引当情報エンティティの入荷済数である。

設問3では,ウィンドウ関数の構文から,集計関数の後の指定はOVER(i)であり,年月日の並べ替えの指定はORDER BY(j)である。

関連問題

本文が同じ・似ている問題を表示しています