令和5年秋期試験午後問題　問6

問6　データベース

⇱問題PDF
在庫管理システムに関する次の記述を読んで，設問に答えよ。
広告
　M社は，ネットショップで日用雑貨の販売を行う企業である。M社では，在庫管理について次の課題を抱えている。
  • 在庫が足りない商品の注文を受けることができず，機会損失につながっている。
  • 商品の仕入れの間隔や個数を調整する管理サイクルが長く，余計な在庫を抱える傾向にある。
〔現状の在庫管理〕
　現在，在庫管理を次のように行っている。
  • 商品の注文を受けた段階で，出荷先に最も近い倉庫を見つけて，その倉庫の在庫から注文個数を引き当てる。この引き当てられた注文個数を引当済数という。各倉庫において，引き当てられた各商品単位の個数の総計を引当済総数という。
  • 実在庫数から引当済総数を引いたものを在庫数といい，在庫数以下の注文個数の場合だけ注文を受け付ける。
  • 商品が倉庫に入荷すると，入荷した商品の個数を実在庫数に足し込む。
  • 倉庫から商品を出荷すると，出荷個数を実在庫数から引くとともに引当済総数からも引くことで，引き当ての消し込みを行う。
　M社では，月末の月次バッチ処理で毎月の締めの在庫数と売上個数を記録した分析用の表を用いて，商品ごとの在庫数と売上個数の推移を評価している。
　また，期末に商品の在庫回転日数を集計して，来期の仕入れの間隔や個数を調整している。

　M社では，商品の在庫回転日数を，簡易的に次の式で計算している。
在庫回転日数＝期間内の平均在庫数×期間内の日数÷期間内の売上個数
　在庫回転日数の計算において，現状では，期間内の平均在庫数として12か月分の締めの在庫数の平均値を使用している。

　現状の在庫管理システムのE-R図(抜粋)を図1に示す。
　在庫管理システムのデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理している。
pm06_1.png
〔在庫管理システム改修内容〕
　課題を解決するために，在庫管理システムに次の改修を行うことにした。
  • 在庫数が足りない場合は，在庫からは引き当てず，予約注文として受け付ける。なお，予約注文ごとに商品を発注することで，注文を受けた商品の個数が入荷される。
  • 商品の仕入れの間隔や個数を調整する管理サイクルを短くするために，在庫の評価を月次から日次の処理に変更して，毎日の締めの在庫数と売上個数を在庫推移状況エンティティに記録する。
　現状では，在庫数が足りない商品の予約注文を受けようとしても，在庫引当を行うと実在庫数より引当済総数の方が多くなってしまい，注文に応えられない。そこで，予約注文の在庫引当を商品の入荷のタイミングにずらすために，E-R図に予約注文の二つのエンティティを追加することにした。追加するエンティティを表1に，改修後の在庫管理システムのE-R図(抜粋)を図2に示す。
pm06_2.png

　在庫管理システムにおける予約注文を受けた商品の個数に関する処理内容を表2に示す。
pm06_3.png
〔在庫の評価〕
　より正確かつ迅速に在庫回転日数を把握するために，在庫推移状況エンティティから，期間を1週間(7日間)として，倉庫コード，商品コードごとに，各年月日の6日前から当日までの平均在庫数及び売上個数で在庫回転日数を集計することにする。
　可読性を良くするために，SQL文にはウィンドウ関数を使用することにする。
　ウィンドウ関数を使うと，FROM句で指定した表の各行ごとに集計が可能であり，各行ごとに集計期間が異なるような移動平均も簡単に求めることができる。ウィンドウ関数で使用する構文(抜粋)を図3に示す。
pm06_4.png
　ウィンドウ関数を用いて，倉庫コード，商品コードごとに，各年月日の6日前から当日までの平均在庫数及び売上個数を集計するSQL文を図4に示す。
pm06_5.png
広告

設問1

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

解答例・解答の要点

a：↓

解説

〔aについて〕
図1において、倉庫エンティティと在庫推移状況エンティティには共通の属性である"倉庫コード"があります。この属性"倉庫コード"は、倉庫エンティティ側では主キーであり、倉庫を一意に特定します。また、在庫推移状況エンティティ側では他の属性とともに（複合）主キーであり、在庫推移状況を一意に特定するための情報であり、同時に外部キーです。

〔現状の在庫管理〕に「月末の月次バッチ処理で毎月の締めの在庫数と売上個数を記録」とあること、主キーとして"年"及び"月"があることから、在庫推移状況エンティティのインスタンスは月ごとに生成されることが読み取れます。つまり、在庫推移状況エンティティには同一の倉庫コードの値をもつ複数のレコードが存在しえるということです。よって、倉庫エンティティと在庫推移状況エンティティのカーディナリティは1対多です。したがって、空欄aには「↓」が当てはまります。

∴a＝↓
広告

設問2

〔在庫管理システム改修内容〕について答えよ。
  • 図2中のb，cに入れる適切なエンティティ名を表1中のエンティティ名を用いて答えよ。
  • 図2中のdに入れる，在庫推移状況エンティティに追加すべき適切な属性名を答えよ。なお，属性名の表記は図1の凡例に倣うこと。
    (※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
  • 表2中のe～hに入れる適切な字句を答えよ。

解答例・解答の要点

  • b：引当情報
    c：引当予定
  • d：日
  • e：引当予定
    f：在庫
    g：入荷明細
    h：入荷済数

解説

  • 2つの空欄にはエンティティ名が当てはまります。図2を見ると両エンティティとも太枠で示されているので、新規に追加したエンティティ、すなわち表1に説示されている"引当情報"又は"引当予定"のいずれかが入ると判断できます。

    〔bについて〕
    空欄bのエンティティは、属性として"引当済数"及び"入荷済数"をもっています。表1に「予約注文を受けた商品の個数と入荷済となった商品の個数を管理する」とあること、表2において「引当情報エンティティの…引当済数には…，入荷済数には…」とあることから、引当情報エンティティとわかります。よって、空欄bには「引当情報」が当てはまります。

    〔cについて〕
    また、空欄cのエンティティは、属性として"未入荷引当済総数"をもっています。表1に「予約注文を受けた商品の，未入荷の引当済数の総数を管理する」とあること、表2において「引当予定エンティティの未入荷引当済総数には…」とあることから、引当予定エンティティとわかります。よって、空欄cには「引当予定」が当てはまります。

    ∴b＝引当情報
    　c＝引当予定

  • 〔dについて〕
    〔在庫管理システム改修内容〕には「在庫の評価を月次から日次の処理に変更して，毎日の締めの在庫数と売上個数を在庫推移状況エンティティに記録する」とあります。図1の改修前の在庫推移状況エンティティの主キーは、"年"・"月"・"倉庫コード"・"商品コード"の組で、これだと月次のみで日次のデータを登録するようになっていないので、改修後には「日」を表す属性を追加する必要があります。日次でデータを追加するようになると、同じ"年"と"月"の組合せをもつデータが多数できるので、データを一意に特定するためには「日」を表す属性を主キーに加える必要があります。したがって、空欄dには「日」が当てはまります。

    ∴d＝日

  • 〔eについて〕
    図2を見ると、属性"未入荷引当済総数"をもつのは引当予定エンティティのみのため、空欄eには「引当予定」が当てはまります。

    〔fについて〕
    図2を見ると、属性"実在個数"及び"引当済総数"属性をもつのは在庫エンティティのみのため、空欄fには「在庫」が当てはまります。

    〔gについて〕
    図2を見ると、属性"個数"をもつのは入荷明細エンティティと出荷明細エンティティですが、商品入荷時において入荷した商品の個数を設定する処理であることを踏まえると、空欄gには「入荷明細」が当てはまります。

    〔hについて〕
    引当情報エンティティの属性のうち、商品の個数に関わるものは「引当済数」と「入荷済数」です。表2の予約注文を受けたときの処理内容を見ると、引当情報エンティティの説明として「引当済数には注文を受けた商品の個数，入荷済数には0を設定する」とあるため、商品が入荷したときに値を増やすべきなのは属性"入荷済数"であることがわかります（商品の入荷があっても、注文個数である引当済数は変動しないのは明らかです）。したがって、空欄hには「入荷済数」が当てはまります。

    ∴e＝引当予定
    　f＝在庫
    　g＝入荷明細
    　h＝入荷済数
広告

設問3

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

解答例・解答の要点

i：OVER
j：ORDER BY

解説

ウィンドウ関数は、指定したウィンドウ内を対象として集計等を行うものです。ここでいうウィンドウとは、テーブルの一部を取り出したものです。GROUP BY句と異なり、行と列の範囲をしているだけで集約は行わないこと、現在の行から●行前までや■行後までなどの範囲指定ができるため、移動平均やある時点までの累計値の算出などに活用することができます。また、サブクエリを使うのと比較して、構文をスッキリさせることができるのもメリットのひとつです。

ウィンドウ関数の考え方としては、次のとおりです。
  1. WHERE句で選択後のテーブルからPATITION BY句で列の集合を取り出す（パーテーションの指定）
  2. 1.の部分集合をORDER BY句で整列する
  3. 2.の部分集合からROWS句を用いて、集計対象となるウィンドウ枠を指定する
  4. ウィンドウ枠に対してウィンドウ関数を適用した結果を列の値として出力する
図4のSQL文中のウィンドウ関数、ウィンドウ名、ウィンドウ指定の各部分の示すと下図のようになります。
pm06_6.png
上記の考え方を踏まえると、ウィンドウ指定では以下のような処理をしていることになります。
  1. 在庫推移状況テーブルから倉庫コード、商品コード列を取り出す
  2. 年⇒月⇒日の順になるように昇順で整列する
  3. 現在の行とそれより前の6行（1週間分の日次データ）をウィンドウ枠として指定する
〔iについて〕
図3のウィンドウ関数の構文と注記1を見ると、ウィンドウ関数内ではウィンドウ名の前に"OVER"が必要なことがわかります。したがって、ウィンドウ名"期間定義"の前の空欄iには「OVER」が当てはまります。

〔jについて〕
図3のウィンドウ指定の構文を見ると、ウィンドウ指定にはウインドウ枠以外にPARTITION BY句とORDER BY句を指定することができるとわかります。空欄の後ろに、昇順を意味する"ASC"があることからも、ORDER BY句が必要なのは明らかです。
ウィンドウ枠を●行前までや■行後までというように指定する場合、行が整列されていなければ範囲指定は意味をなしません。このため、各パーテーション内をORDER BY句で整列する処理が必要となります。

∴i＝OVER
　j＝ORDER BY
広告
模範解答
広告

前の問題次の問題

▲
Pagetop