生活用品メーカーの在庫管理システムのデータベース実装・運用に関する次の記述を読んで，設問に答えよ。
D社は，日用品，園芸用品，電化製品などのホームセンター向け商品を製造販売しており，販売物流の拠点では自社で構築した在庫管理システムを使用している。データベーススペシャリストのEさんは，マーケティング，経営分析などに使用するデ（以下，分析データという）の提供依頼を受けてその収集に着手した。
〔分析データの提供依頼〕
分析データ提供依頼の例を表1に示す。
表1 分析データ提供依頼の例
〔在庫管理業務の概要〕
在庫管理業務の概念データモデルを図1に，主な属性の意味・制約を表2に示す。在庫管理システムでは，図1の概念データモデル中のサブタイプをスーパータイプのエンティティタイプにまとめた上で，エンティティタイプをテーブルとして実装している。Eさんは，在庫管理業務への理解を深めるために，図1，表2を参照して表3の業務儿一儿整理表作成乚大。表3下，項番仁，幾つかのエンティティタイプを対象に業務ルールを列記した①〜④が，概念データモデルに合致するか否かを判定し，合致する業務ルールの番号を全て記入している。
図1 在庫管理業務の概念データモデル
表2 主な属性の意味・制約
表3 業務ルール整理表（未完成）
〔問合せの検討〕
Eさんは，依頼1に対応するために図2のZチャートの例を依頼元から入手し，Zチャートを作成するための問合せの内容を，表4に整理した。表4中のT1は月間出荷数量，T2は移動累計出荷数量，T3は累計出荷数量を求める問合せである。
図2 Zチャートの例
表4 依頼1の問合せの検討（未完成）
依頼2について図3のSQL文の検討を行い，実装したSQL文を実行して図4のヒートマップの例を作成した。
図3 依頼2の問合せを実装したSQL文（未完成）
図4 ヒートマップの例
〔依頼3への対応〕
Eさんは，在庫回転率及びその根拠となる数値（以下，計数という）の算出方法を確認した上で，分析データを作成する仕組みを検討することにした。
1.計数の算出方法確認
在庫管理業務では，次のように，年月，拠点，商品ごとに計数を算出している。
・月締めを行う。月締めは対象月の翌月の第5営業日までに実施する。
・月締めまでの間は，前月分の出荷であっても訂正できる。
・月末時点の在庫を，先入先出法によって評価し，在庫金額を確定する。在庫金額算出に際して，商品有高表及び残高集計表を作成する。商品有高表の例を表5に，残高集計表の例を表6に示す。
（1）商品有高表
・前月末時点の残高を繰り越して受入欄に記入する。残高は，入荷ごとに記録するので，複数入荷分の残高があれば入荷の古い順に繰り越す。
・受入，払出の都度，収支を反映した残高を記入する。例えば，表5中の行2の残高には行1の受入を反映した残高を転記し，行3の残高には行2の受入を反映した残高を記入している。
・当月中の入荷を受入欄に，出荷を払出欄に記入する。入荷の入荷年月日，出荷の出荷年月日を受払日付とし，受払日付順及び入出荷の登録順に記入する。
・出荷による払出は，入荷の古い順に残高を引き落とし，複数入荷分の残高を引き落とす場合は，残高ごとに行を分ける。入出荷による変更後の在庫を入荷の古い順に残高欄に記入する。
・赤伝は，受払日付に発生日ではなく，訂正元出荷と同じ受払日付でマイナスの払出を記入する。
・月末時点の残高を入荷の古い順に払出欄に記入して次月に繰り越す。
（2）残高集計表
年月，拠点，商品ごとに，商品有高表を集計・計算して月初残高，当月受入，当月払出，月末残高，在庫回転率の数量，金額をそれぞれ次のように求める。
・月初残高は，前月繰越による受入の数量，金額を集計する。
・当月受入は，当月中の入荷による受入の数量，金額を集計する。
・月末残高は，次月繰越による払出の数量，金額を集計する。
・当月払出，“月初残高+当月受入-月末残高”によって，数量，金額それぞれ求める。
・在庫回転率，“当月払出÷（（月初残高+月末残高）÷2）によって，数量，金額を求める。
表5 商品有高表の例（未完成）
表6 残高集計表の例（一部省略）
2.計数を格納するテーブル設計
Eさんは，鮮度の高い分析データを提供するために，商品有高表及び残高集計表の計数をテーブルに格納することにして図5のテーブルを設計した。
（1）“受払明細”テーブル
・商品有高表の受入，払出のどちらかに数量，単価，金額の記載のある行を格納する。
・受払#には，年月，拠点#，商品#ごとに，商品有高表中の受入又は払出の数量に記載のある行を対象に1から始まる連番を設定する。一つの出荷が複数の残高から払い出される場合には，払出の行を分け，それぞれに受払#を振る。
・摘要区分には，‘前月繰越'，'出荷'，'入荷'，'赤伝'，'黒伝'，‘次月繰越’のいずれかを設定する。
（2）“受払残高”テーブル
・受払明細ごとに，受払による収支を反映した後の残高数量を，基になる受入ごとに記録する。残高の基になった受入（前月繰越又は入荷）の受払#，単価を，受払残高の基受払#，単価に設定する。
（3）“残高集計”テーブル
・受払明細及び受払残高の対象行を“残高集計表”の作成要領に従って集計・計算して“残高集計”テーブルの行を作成する。
図5 計数を格納するテーブルのテーブル構造
3.計数を格納する処理
Eさんは，入荷又は出荷の登録ごとに行う一連の更新処理（以下，入出荷処理という）に合わせて，図5中のテーブルに入出荷を反映した最新のデータを格納する処理（以下，計数格納処理という）を行うことを考えた。
（1）計数格納処理の概要
①入出荷の明細ごとに，“受払明細”テーブルに赤伝，黒伝を含む新規受払の行を作成する。赤伝，黒伝の発生時には，同じ年月，拠点#，商品#で，その受払よりも先の行を全て削除した上で，入出荷の明細から行を再作成する。これを洗替えという。
②①によって変更が必要になる“受払残高”テーブルの行を全て削除した上で，再作成する。
③変更対象の計数を集計して“残高集計”テーブルの行を追加又は更新する。
④計数は，計数格納処理の開始時点で登録済の入出荷だけを反映した状態にする。
（2）計数格納処理の処理方式検討
Eさんは，計数格納処理の実装に当たって，次の二つの処理方式案を検討し，表7の比較表を作成した。
案1：入出荷処理と同期して行う方式。同一トランザクション内で入出荷処理及び計数格納処理を実行する。
案2：入出荷処理と非同期に行う方式。入出荷処理で，登録された入出荷のキ一値（拠点#，入荷#，出荷#）を連携用のワークテーブル（以下，連携WTという）に溜めておき，一定時間おきに計数格納処理を実行する。
・入出荷処理では，トランザクション内で一連の更新処理を行い，最後に連携WTに行を追加してトランザクションを終了する。
・計数格納処理では，実行ごとに次のように処理する。
（a）連携WT全体をロックし，連携WTの全行を処理用のワークテーブル（以下，処理WTという）に追加後，連携WTの全行を削除してコミットする。
（b）処理WT，入荷，入荷明細，出荷，及び出荷明細から必要な情報を取得し，年月，拠点#，商品#の同じ行ごとに，まとめて次のように処理する。
・赤伝，黒伝がなければ，登録TSの順に受払を作成する。
・赤伝，黒伝があれば，洗替えの起点となる行を1行選択し，その行に対応する受払を作成する。そして，起点となる行を基に，入荷，入荷明細，出荷，出荷明細から対象となる行を入荷年月日又は出荷年月日，登録TSの順に取得して洗替えを行う。
（c）処理WTの全行を削除してコミットする。
表7 処理方式案の比較表
（1）分析データの鮮度については，どちらの案でも依頼3の要件を満たす。
（2）入出荷処理への影響について，表5において，2023-10-03に次のそれぞれの出荷の登録を仮定して，“受払明細”テーブルへの追加及び削除行数を調べることで，追加処理による遅延の大きさを推測した。
・09-26の出荷数量40の出荷明細を追加入力すると，次月繰越の2行を削除，出荷1行及び新たな次月繰越1行の2行を追加することになる。
・09-04の出荷を取り消す赤伝を追加すると，“受払明細”テーブルに合計で11行の削除，12行の追加を行うことになる。
案1では，特に出荷の赤伝，黒伝から受払を作成する場合に，追加処理による入出荷処理の遅延が大きくなる。案2では，①連携WTに溜まった入出荷情報をまとめて処理することで，計数格納処理における出荷の赤伝，黒伝の処理時間を案1よりも短縮できる。
（3）案1では入出荷処理の性能及び計数格納処理エラーの業務への影響が大きいことから，案2を採用することにした。なお，導入に先立って，②計数格納処理が正しく動作することを検証することにした。
4.分析データの検証
Eさんは，計数格納処理を実行して得たデータを用いて，ある拠点，商品の過去12か月の在庫回転率を時系列に取得して表8を得た。一定の方法で，数量，金額それぞれの在庫回転率を母集団とする外れ値検定を行ったところ，2023-09の金額の在庫回転率だけが外れ値と判定された。外れ値は，業務上の要因によって生じる場合もあれば，入力ミスなどによって生じる異常値の場合もある。
表8について，Eさんは次のように推論した。
①数量と金額の在庫回転率は，ほぼ同じ傾向で推移するが，材料費の値上がりなどに起因して，製造原価が上昇する傾向にあるとき，金額による在庫回転率は m する傾向がある。
②2023-09の数量の在庫回転率は前月とほぼ同じ水準であるにもかかわらず， 金額の在庫回転率が極端に低い値になっていることから，異常値であること が疑われる。
③この推論を裏付けるには，“受払明細”テーブルから当該年月，拠点，商品の一致する行のうち，“摘要区分=‘ n ’”の行の o に不正な値がないかどうかを調べればよい。
表8 ある拠点，商品の在庫回転率（2022-10〜2023-09）
5.概念データモデルの変更
図5のテーブルをエンティティタイプ，列名を属性名として，概念データモデルに追加する。Eさんは，追加するエンティティタイプ間及び図1中のエンティティタイプとの間のリレーションシップについて追加するエンティティタイプの外部キーと参照先のエンティティタイプを表9の形式で整理した。
表9 追加するエンティティタイプの外部キーと参照先のエンティティタイプ（未完成）
設問1 〔在庫管理業務の概要〕について答えよ。
（1）表3中の a に入れる適切な業務ルールを，エンティティタイプ“生産拠点”と“商品”との間のリレーションシップに着目して25字以内で答えよ。
解答・解説
解答例
・一つの商品は一つの生産拠点だけで生産する。
・一つの生産拠点では複数の商品を生産する。
解説
ー
（2）表3中の b 〜 f に入れる適切な番号（①〜④）を全て答えよ。
解答・解説
解答例
b：①，④
c：②，③
d：③
e：②，④
f：①，③，④
解説
ー
設問2 〔問合せの検討〕について答えよ。
（1）図2において，累計出荷数量のグラフは始点から終点への直線の形状，移動累計出荷数量のグラフは右肩下がりの形状となっている。この二つのグラフから読み取れる商品の出荷量の傾向を，それぞれ30字以内で答えよ。
解答・解説
解答例
累計出荷数量：直近1年は毎月の出荷数量の増減がない。
移動累計出荷数量：
・各月の出荷数量が前年同月比で全て減少している。
・グラフ表示範囲の 1 年前の期間の出荷数量は減少傾向だった。
解説
ー
（2）表4中の ア ， カ に入れる適切な数値，及び イ 〜 オ に入れる適切な字句を答えよ。ここで， イ 〜 オ は次の字句から選択するものとし，nを含む字句を選択する場合は，演算及び選択の対象行が必要最小限の行数となるように，nを適切な数値に置き換えること
解答・解説
解答例
ア：22
イ：11行前の行
ウ：現在の行
エ：最初の行
オ：現在の行
カ：11
解説
ー
（3）図3中の キ 〜 コ に入れる適切な字句を答えよ。
解答・解説
解答例
キ：T.棚#, COUNT(S1.棚#)
ク：GROUP BY T.棚#
ケ：ORDER BY 出庫回数 DESC
コ：出庫回数順位 / COUNT(*)
解説
ー
（4）図4において，二つの棚に配置されている商品を相互に入れ替えて効率化を図る場合，最も効果が高いと考えられる，入替えを行う棚の棚#の組を答えよ。
解答・解説
解答例
307と604の組
解説
ー
（5）（4）の対応を記録するために更新が必要となるテーブル名を二つ挙げ，それぞれ行の挿入，行の更新のうち，該当する操作を◯で囲んで示せ。
| テーブル名 | 操作 |
| 行の挿入 ・ 行の更新 | |
| 行の挿入 ・ 行の更新 |
解答・解説
解答例
テーブル名：棚別在庫 操作：行の更新
テーブル名：倉庫内移動 操作：行の挿入
解説
ー
設問3 〔依頼3への対応〕について答えよ。
（1）表5中の g 〜 l に入れる適切な数値を答えよ。
解答・解説
解答例
g：210
h：85
i：260
j：85
k：150
l：90
解説
ー
（2）本文中の下線①では，どのように処理を行うべきか。次の（a），（b）における対象行の選択条件を，列名を含めて，それぞれ35字以内で具体的に答えよ。
（a）処理WTに，同じ年月，拠点#，商品#の赤伝，黒伝が複数ある場合に，洗替えの起点となる行を選択する条件
解答・解説
解答例
・入荷年月日又は出荷年月日，登録TSの昇順に並べた先頭の行であるこ と
・受払日付，登録順が最も古い入出荷であること
解説
ー
（b）（a）の洗替えの起点となる行を基に，洗替えの対象となる入荷，入荷明細，出荷，出荷明細を取得するときに，計数格納処理の開始時点で登録済の入出荷だけを反映した状態にするために指定する条件。ただし，入荷年月日又は出荷年月日が，起点となる行の入荷年月日又は出荷年月日よりも大きい条件を除く。
解答・解説
解答例
・登録TSが処理WT内で最大の登録TS以下であること
・登録TSが計数格納処理の開始日時以前であること
・拠点#，入荷#，出荷#が連携WTに存在しないこと
解説
ー
（3）本文中の下線②では，処理結果が正しいことをどのように確認したらよいか。確認方法の例を60字以内で具体的に答えよ。
解答・解説
解答例
・拠点#ごと，商品#ごとに入荷数量，出荷数量を集計した値が残高集計の当月受入数量，当月払出数量とそれぞれ一致する。
・該当月の入荷明細，出荷明細の行に対応する受払明細の行を突合し，各々一行だけ対応する行が存在する。
・該当月の入荷，入荷明細，出荷，出荷明細を基に作成した商品有高表及び残高集計表の計数が計数格納処理の結果と一致する。
解説
ー
（4）本文中の m 〜 o に入れる適切な字句を答えよ。
解答・解説
解答例
m：下降
n：入荷
o：単価
解説
ー
（5）表9中の太枠内の空欄に適切な字句を入れて表を完成させよ。ただし，空欄は全て埋まるとは限らない。
解答・解説
解答例
受払残高
外部キーの属性名：年月，拠点#，商品#，基受払#
参照先エンティティ：受払明細
受払残高
外部キーの属性名：拠点#，商品#
参照先エンティティ：物流拠点，商品
解説
ー
IPA公開情報
出題趣旨
DX への取組では，KPI を設定し，その数値を見ながら継続的に活動することも多く，KPI の算出値には高い精度及び鮮度が要求される。データベーススペシャリストは，KPI となる項目の意味を理解した上で，データベース技術を適切に活用して，利用者に情報を提供することが求められる。
本問では，生活用品メーカーの在庫管理業務を題材として，データベースの設計，実装，利用者サポートの分野において，①論理データモデルを理解する能力，②物理データモデルを設計する能力，③問合せを設計する能力，④データの意味，特性を説明する能力を問う。
採点講評
問１では，生活用品メーカーの在庫管理システムを題材に，データベースの実装・運用について出題した。全体として正答率は平均的であった。
設問 1 では，(2)d の正答率がやや低かった。スーパータイプと排他的ではないサブタイプとのリレーションシップの特徴をよく理解し，もう一歩踏み込んで考えてほしい。
設問 2 では，(2)ア，(3)の正答率が低かった。(2)アでは，11 と誤って解答した受験者が多かった。グラフの表示範囲の移動累計出荷数量のグラフを描くには，グラフの表示期間の最初の年月の 11 か月前の年月から指定年月までの計 23 か月分の月間出荷数量のデータが必要となる。グラフが表すデータの意味を正しく把握した上で設計に反映するよう心掛けてほしい。(3)では，集計，ソート，順位付けなどのヒートマップを作成する上で必要となる処理を正しく理解できていない解答が多かった。データをグラフなどで可視化する際にも役立つので，SQL の集計関数やウィンドウ関数の使い方を身に付けてほしい。
設問 3 では，(2)(b)，(3)，(5)の正答率が低かった。(2)(b)では，洗替えの際に計数格納処理開始後に登録された入出荷を除いて計数を求める必要がある点に着目していない解答が散見された。(3)では，計数格納処理の実行結果を正確に確認する方法を求めているのに対し，テストの実行方法，テストケースについての解答が散見された。(5)では，外部キーによる参照制約の有無に着目していない解答が散見された。この設問で問われている内容は，データベースを用いた処理方式の設計を行う際に必要とされることであり，是非知っておいてもらいたい。
