Database Systems Engineer Examination

Ｈ１６午後II 問2
　商品配送業務の概念データモデル設計に関する問題。
　このまま，物流系は定番化していくのだろうか。。。

設問1
　マスタ系概念データモデルと在庫品モデルの設計に関する設問
　〔概念データモデルの作成〕の
　1.概念データモデルの記述規則と作成順序～2.マスタ系概念データモデルと在庫品モデルが設問範囲
　〔業務分析結果〕の1.取扱商品～5.在庫品配送業務の内容までの範囲をチェック。

設問1(1)
　適切なエンティティ名を入れる。。。初見では結構面食らった。
　あの三つの四角がスーパータイプ／サブタイプの関係であるなんて疑いもしなかった。。。

過去問のスーパータイプ／サブタイプのエンティティ／リレーション充足においては，もっとヒントがあったのに。。。
　・スーパータイプ側が書いてあったり(H12午後2問1)
　・線引きが書いてないだけだったり(H15午後2問1,H13午後2問1設問1)
　・類似エンティティでヒントが示してあったり(H13午後2問1設問3)
　今回，線引きもエンティティ名もなしで出てくるなんて思いもせず，「商品」以外の何かを一生懸命探して挫折したものだった。。。
　スーパータイプ／サブタイプの設問は意地悪になって行くものと思われる。

話はそれたが。。本設問のポイントとしては，

〔業務分析結果〕の「配送対象商品は，在庫品と直送品に分類される。」

これだけでピンとくる人は解答欄が埋まるんだろうけど。。

　ちと，慎重に。
　ｂは，図12の在庫品受注明細のスキーマから主キーは商品番号と想定できる。ここで，「商品」と仮置きするのか，「在庫品」と仮置きするかで過去問熟練度(?)の差が出るところだろう。
　ｃは，直送品メーカから矢印が伸びている。つまりは直送品メーカ毎に決まる「何か」ということだが。。明確に書いてある文章が見つからない。

〔業務分析結果〕の3.メーカと工場から「直送品の仕入先メーカをほかの商品のメーカと区別するため，“直送品メーカ”と呼ぶ。」

とはあるが，いまいち弱い。仕方なく「メーカ」と書いてる箇所を片っ端から読む。すると，

6.直送品配送業務の内容の(2)メーカへの発注で「店舗からの受注を仕入先の直送品メーカ単位に分割し，直送品メーカに発注している。」

とある。図８と図９をチェックし，商品番号と直送品メーカ（発注先）の対応関係が必要であることが確認できた。で，ｃは「直送品」と判断できる。
　ここで，ｂを「商品」としていた場合は「在庫品」に訂正し，aに「商品」と書く。
設問1(1)
　ａ：商品
　ｂ：在庫品
　ｃ：直送品

設問1(2)
　リレーションシップを補う問題。この手の問題の解法は３つのアプローチがある。
　・関係スキーマから導く。
　・問題文からビジネスロジックとなるリレーションを読み解く。
　・問題文（帳票）からスキーマを導いてリレーションを完成させる。
　問題の傾向として「関係スキーマの完成」とセットで出題されることが多い。
　（H14午後2問2，H13午後2問1，H13午後2問2，H11午後2問1）
　この場合，「関係スキーマの完成」と合せて解く方が賢いと思われる。

設問1(3)
　以降，(2)と(3)を合せて進める。
　まず，(1)のスーパータイプ／サブタイプの線引きを忘れずに記入する。
　次に，「関係スキーマから導く」。図12の関係スキーマを見て，外部キーと思われるもの「○○番号」にとりあえず下線(点線)を引き，リレーションを充足していく。
　在庫品受注（店舗番号）←店舗　・・・　線を引く
　在庫品受注明細（受注番号）←在庫品受注　･･･　線が引いてあることを確認
　在庫品受注明細（商品番号）←[b]在庫品　･･･　線が引いてあることを確認
　在庫品出庫（配送センタ番号）←配送センタ　・・・　線を引く
　在庫品出庫（商品番号）←[b]在庫品　・・・　線を引く
　在庫品配車（配送エリアコード）←配送エリア　･･･　線が引いてあることを確認
　在庫品配車（車両番号）　・・・　エンティティがないため保留

　さて。。。保留した「車両番号」ってのは。。。車両っていうエンティティはマスタ系にないし。。。と，表1直送品の属性対応表を見てみると「車両番号」はA（従属属性）となっている。外部キー属性ではないらしい。図12の「車両番号」の下線を消しておこう。

　あとは，関係スキーマを完成させないとリレーションは完成しないようだ。アプローチを「問題文（帳票）からスキーマを導いてリレーションを完成させる」に変更する。

　未完成のスキーマに目を移す。モデル図及びスキーマから（一般的に）明らかな箇所を記入する。
　在庫品仕分（受注番号，　　　）

　完成させなきゃいけないスキーマは，在庫品仕分，在庫品仕分明細，在庫品出荷の三つ。対応する仕分指示書（図５），出荷指示書（図６）に飛びつきたいのを我慢して，帳票もスキーマもある出庫指示書（図４）を観察する。帳票上，明細行の一部に見える「出庫番号」が主キーねぇ。。。へぇ。。。配送日付と配送時間帯はどこから持ってきているんだろうか。。。

〔業務分析結果〕4.配送形態より「在庫品の配送は１日１回」，「配送時間帯は，全店舗で共通」

　表１の直送品の属性対応表では，直送品配車に持っていると。。。よく分からん。。設問じゃない所で悩んでも仕方ないか。。。

　次に図５在庫品仕分指示書に目を移す。対応するスキーマは在庫品仕分と在庫品仕分明細。帳票のヘッダ部分と明細部がそれぞれに対応するのは想像に容易い。ヘッダ部，ボディ部から属性を拾う。
　ヘッダ（受注番号，配送先，配送エリア，配送日付，配送時間帯）
　ボディ（No.，商品番号，商品名，数量）

　スキーマに置き換えていく。。
　ヘッダ：受注番号はチェック済。配送先は店舗番号であるが，在庫品受注から導出可能であるため，省く。配送エリアは配送エリアコードであり先ほどの店舗番号から導出可能であるため省略。配送日付は「在庫品受注.納品予定年月日」か「在庫品配車.配送年月日」か。。保留，配送時間帯は「全店舗で共通」なのでデータとして持たなくても良い。

　在庫品仕分（ 受注番号，　　）　・・・　く。。増えてない。。。

　ボディ：No.は受注明細番号，商品番号は在庫品受注明細から導出（在庫品納品明細でも保持していない），数量は

〔業務分析結果〕4.配送形態より，業務の各段階で実績の数量を記録

となるので，仕分数量として属性を持つ。（直送品の属性名と一致していることを横目で確認）

　在庫品仕分明細（ 受注番号，受注品明細番号，仕分数量 ）

　消化不良な感じで在庫品出荷指示書（図６）に目を移す。在庫品出荷は明細がないのに，帳票は明細がある。。厄介な。とりあえずヘッダ部，ボディ部から属性を拾う。

　ヘッダ（出荷番号，配送エリア，配車番号，出荷日付，車両番号，配送時間帯，配送センタ）
　ボディ（受注番号，店舗名，店舗番号）

　在庫品“出荷”なので，出荷番号を主キーとする。
　ヘッダのうち（配車番号，配送エリアコード，出荷日付=配送年月日，車両番号）は，在庫品配車から参照すると考えるのが普通（１事実１箇所）だろう。
　配送センタは在庫品配車－配送エリア－配送センタと導出可能であるため省略。配送時間帯は「全店舗で共通」なのでデータとして持たなくても良い。
　と，いうことで

　在庫品出荷（ 出荷番号，配車番号 ）

　リレーションを在庫品配車から在庫品出荷に線を引く。多重度は

〔業務分析結果〕4.配送形態より，配送エリア単位に商品が出荷され，配送車が担当の配送エリア内の全店舗に納品

という記述から１：１とする。

　ボディの方は。。。ちょっと悩む。何を出荷するのか，図３を確認。配送仕分を行ったものを出荷するんだなと。。。在庫品仕分指示書（図５）と在庫品出荷指示書（図６）を見比べる。出荷の明細１行目と仕分のヘッダの値が一致するじゃないですか！

　在庫品仕分（ 受注番号，出荷番号 ）

　リレーションを在庫品出荷→在庫品仕分と線を引く。

5.在庫品発送業務の内容 (5)出荷に，出荷された商品と受注の関係を把握するために，受注番号を記録する。

そのまま読むと在庫品出荷に受注番号を持つように思えるなぁ。。仕分の方に持つとは。。。

設問1(2)
　［ａ：商品］－△－［ｂ：在庫品］
　［ａ：商品］－△－［ｃ：直送品］
　店舗→在庫品受注
　配送センタ→在庫品出庫
　［ｂ：在庫品］→在庫品出庫
　在庫品配車－在庫品出荷
　在庫品出荷→在庫品仕分

設問1(3)
　在庫品仕分（ 受注番号，出荷番号 ）
　在庫品仕分明細（ 受注番号，受注品明細番号，仕分数量 ）
　在庫品出荷（ 出荷番号，配車番号 ）

公式解答例には，在庫品仕分明細に出庫番号を保持している（対応するリレーションもある）。
帳票にないんだよなぁ。。。在庫品仕分指示書に出庫番号が。

設問2，設問3は次の機会に。

H16午後I 問1 vol.2 vol.1

※H16午後I 問1を自分なりに理解していないと混乱する恐れがありますのでご注意ください。 m(_ _)m

設問3(1)
解答例は
　旅程番号→→顧客番号｜｛搭乗日，便名｝
と，なっているが，

　旅程番号→→｛顧客番号，搭乗日｝｜便名
と，した場合，絶対ダメという理由が浮かばない。

　表１の意味と制約で「同じ旅程番号の顧客は，同一日程で同一の便を利用する」としてあるだけで，「同じ旅程番号の顧客は同一日程で，同一の便を利用する」と点を打つ場所を変えれば後者でも良い気がする。。。（※公式解答例は前者である）

　「搭乗日」というのが，乗客が航空便に搭乗する日　なのか
　「搭乗日」というのが，航空便が乗客に搭乗される日　なのか
　（※明らかに前者の方が一般的な解釈である）

　多値従属性の解説では，顧客番号と｛搭乗日，便名｝の間に特別な関係がないことが重要であるとしているが，搭乗日は顧客番号と便名の間をつなぐ「特別な何か」のような気もする。

　もうちょっと考えてみるか。。。
　→考えた結果はコメントを参照してください。
　多値従属性についての詳しい説明が欲しい人は，コメントの9と10にあります。

Ｈ１６午後I 問２
設問１（１）：LEFT JOIN

SELECT プロモーション.プロモーションID, プロモーション名, 割引率,
　　　　　商品.商品コード, 商品名, 売上個数, 売上金額, 前月売上個数,
　　　　　前月売上金額
FROM プロモーション, 商品, プロモーション対象商品 LEFT JOIN
　( SELECT 商品コード, SUM( 売上個数 ) AS 売上個数, SUM( 売上金額 ) AS 売上金額
　　FROM 注文履歴
　　WHERE 年月日 BETWEEN '2004-03-01' AND '2004-03-31'
　　GROUP BY 商品コード ) AS 当月
　ON 当月.商品コード = プロモーション対象商品.商品コード
　LEFT JOIN
　( SELECT 商品コード, SUM( 売上個数 ) AS 前月売上個数, SUM( 売上金額 ) AS 前月売上金額
　　FROM 注文履歴
　　WHERE 年月日 BETWEEN '2004-02-01' AND '2004-02-29'
　　GROUP BY 商品コード ) AS 前月
　ON 前月.商品コード = プロモーション対象商品.商品コード
WHERE 実施年月 = '2004-03'
AND プロモーション.プロモーションID = プロモーション対象商品.プロモーションID
AND プロモーション対象商品.商品コード = 商品.商品コード
ORDER BY プロモーション.プロモーションID, 商品.商品コード

---

設問２（２）：副問合せ

SELECT A.職業, COUNT(*) AS 顧客数 FROM 顧客 A, (
　　SELECT DISTINCT 顧客コード FROM 注文履歴
　　WHERE 売上金額 >= 50000
　　AND 年月日 BETWEEN '2004-03-01' AND '2004-03-31' ) B
WHERE A.顧客コード = B.顧客コード
GROUP BY A.職業

H16午後I 問1
設問1
　定番問題

設問2(1)
　候補キーを問う定番問題。
　旅券番号が候補キーであることはすぐに気付く。
　ここで，図2が主な関数従属性であることを忘れてはいけない。
　他に候補キーがないかチェックしなければ安心できません。
　旅券番号を関数決定できる何かを探す。

　表1　意味と制約より
　旅券番号が変われば有効年月日も変わる。
　しかし，有効年月日だけでは，旅券番号は特定できない。
　じゃぁ，何があればいいのか。。。そう，顧客番号である。
　｛顧客番号，有効年月日｝→旅券番号　これで2個目。

　｛氏名，連絡先，生年月日，性別，有効年月日｝→旅券番号
　なんて，強引なことを思いついたりするが，これは前提もなくやり過ぎです（ｗ

設問2(2),(3),(4)
　定番問題

設問3
　ついに出ました多値従属性です。(漢字変換は出てきません）
　「自明でない多値従属性」でググって4件
　「多値従属性」でググって8,530件
　多値従属性についてはここの解説がすっきりしてて良いと思った。

多値従属性についての正確な定義は以下の通りである。

1.R(A,B,C)というタプルがある。
2.Aが決まれば、Cとは無関係にBの複数の値が一意に決まる。
　※これは、 A->>Bと表記される。
3.上記の関係が成り立つならば、自動的に、Aが決まれば、Bとは無関係にCの複数の値が一意に決まる。
　※これは、 A->>Cと表記される。
4.上記「A->>B」「A->>C」が同時に成り立つことを、 特に「A->>B|C」と表記し、多値従属関係がある、と呼ぶ。
5.A->>B|Cである時、表(関係)Rは、情報を保ったまま、R1(A,B) と R2(A,C) に分解できる。(Faginの定理)

設問3(1)
　お客様指向が根強いのか，ついつい
　顧客番号→→旅程番号｜｛搭乗日，便名｝　と，してしまう。ダメだなｗ

設問3(2)
　第４正規形の条件を知っているかにかかる。
　第４正規形については，的中の素がコンパクトに整理してあると思う。

関係Rに多値従属性X→→Yがあるとき、その多値従属性X→→Yが自明もしくはXがスーパーキー（関係Rのすべての属性に対しても多値従属性が成立する）

設問3(3)
　設問3(1)の指摘が出来ていれば間違わないと思う。

Ｈ１６午後Ⅰ問３ vol.2 → vol.1

設問３（２）

　履歴に終了年月は必要なのか？
　（ウチの部門の要員管理ＤＢには，開始年月日しかない）
　※公式解答例には「終了年月」は入っている。

　無くても問題ないと思うんだけどな。。。
　でも，この要件で使うのなら終了年月を持っていた方が処理は楽そう。

　休職とか退職とかをどう管理するのかも，ちょっと気になる。
　まぁいいっか。

さて，

---

700 ：はぁはぁ ：05/02/01 00:28:17
＞社員人事履歴(*社員コード,*開始年月日,組織コード,役職コード,終了年月日)
開始年月日/終了年月日ではなくて「○○年月」ですね。
異動および役職変更は月初めに行う。って書いてあるし。
また、見落とした…_|￣|〇

701 ：名無し検定１級さん ：05/02/01 00:54:57
>>700
「日」に月初めの日が入ればいいと思え
１日かもしれないし、最初の営業日かもしれないだろｗ

と，いう話もある。

問題によると「異動は月初め」らしいが・・・
　不祥事（個人情報流出など）を起こして，社内の懲罰で降格とか免職とか
　交通事故死とか，心労による自殺とかで死亡除籍とか
を，考えると履歴は「年月」ではなく「年月日」ではないかと。

　「日」があれば，人事管理上良くある不用意な事態に対応できるのではないかと。
　たとえば，
　　不祥事（個人情報流出など）を起こして，社内の懲罰で降格とか免職とか
　　交通事故死とか，心労による自殺とかで死亡除籍とか

　10日に不祥事を起こして処分は来月初めですか？世論が許しませんｗ
　死亡除籍の場合は，日別稼動実績を入力しなければ良いだけかもしれませんが・・・

---

補足：05/02/05 14:53
　本記事の後半「日」に関する内容は，解答例にケチをつけるものではなく，問題文の「月初」という表現に曖昧さを感じ，「日」が必要だと思う根拠を捻出しているだけの駄文です。

Ｈ１６午後I 問２
設問１（３）
iTAC@解答例速報掲示板より

午後１　問２　設問１（３）　投稿者：MyVitzRs　投稿日：4/18 22:38:05

解答例を基準にして、、、、
「実施年月」は、プロモーションIDにより一意に識別できるため、月別プロモーション別
商品売上集計テーブルに入れるのは、冗長では？

よって、「売上個数（売上個数累計）」「売上金額（売上金額累計）」の２項目でOKだと思います。

午後１　問２　設問１（３）　投稿者：Gypsy　投稿日：4/18 23:17:38

"前月"と"今月"の集計結果として別個に格納しておくために
「実施年月」は必要なのではないでしょうか?
そのために'月別'プロモーション別商品売上集計テーブルと
月別であることを明記してるんだと思います。

ただ前月の結果も入れるので「実施年月」ではなくて
「年月」の方が正しそうな気がします。

(といいつつ実施年月と解答してしまいましたが、、、)

午後１　問２　設問１（３） 　投稿者：Gan　投稿日：4/18 23:55:51

私も年月は冗長だと思ったので、
「売上個数（売上個数累計）」「売上金額（売上金額累計）」
のみとしました。

---

公式解答例は「年月」は入っている。
　素直に考えれば，設問で「月別プロモーション別商品売上集計テーブル」となっているので，当然，「年月」は入るものと思うんだが・・・

年月が無かった場合を考えてみる

　前月の時点では，プロモーション対象商品ではない（一つの商品が２か月続けてプロモーションの対象となることはない）ことから
プロモーションＩＤ→実施年月（当月）　は求められるが
プロモーションＩＤ→前月　は，実施年月－１ヶ月しなければならない。だが，標準ＳＱＬでは日付関数がないため，求められない。

設問もおかしい！
　「月別プロモーション別商品売上集計テーブル」の名前から，素直に受け取れば，主キーは｛年月，プロモーションＩＤ，商品コード｝である。
　しかし，前月時点では対象商品はプロモーションの対象となっていないため，プロモーションＩＤがない。つまりは，主キーの一部がＮＵＬＬになってしまい，集計が出来ない。
　ここでの主キーは｛年月，商品コード｝であり，プロモーションＩＤは外部キーである。
　ゆえに，テーブル名は「月別商品別売上集計テーブル」とすべきである。

Ｈ１６午後I 問３

696 ：はぁはぁ ：05/01/31 23:48:15
設3-1
社員の人事異動および役職変更があった場合。
設3-2
社員(*社員コード,社員氏名)
社員人事履歴(*社員コード,*開始年月日,組織コード,役職コード,終了年月日)

公式では、他の問題は合ってた。この二つが微妙な線なので
判定おながいします。

---

ツッコミを。

設問３（１）
　人事異動及び役職変更があってどうなるのか具体性に欠ける。
　要求される文字数に合わせた装飾が必要

設問３（２）
　社員人事履歴が冗長。
　通常人事系の暦は個別（テーブル別）に設定する。

Ｈ１６午後II 問１

見直しナシで，１時間で解けた。

列テーブルは列IDだけで一意。
表IDは外部キーってのに気付かず，連鎖的に間違った。。。 （遠い目）
これがケアできてれば，示現塾配点で合格レベル。

午後I　問１　設問１（２）
　第１正規形について，“その根拠を具体的に述べよ”

〔解答例〕
・候補キー｛Ａ，Ｂ｝の一部であるＡに，非キー属性｛Ｃ，Ｄ，Ｅ｝が，部分関数従属している。
・候補キー｛Ａ，Ｂ｝に，非キー属性｛Ｃ，Ｄ，Ｅ｝が完全関数従属していない。

第２正規形でない理由を書けば良いみたい。
設問は第１，２，３正規形のどれか，という質問が前にあるので
第１正規形の条件を満たしていることは書かなくていいということね

午後I　問１　設問２（３）
　第2正規形の更新不具合について（○○の際の不具合）

〔解答例〕
・ある条件の情報は，主キーがＮＵＬＬになるので，登録できない。
・ある条件の登録で，｛　｝の情報が冗長になる。

いわゆる「１事実複数箇所」の問題点を記述すれば良いみたい。
事前登録に関すること。
重複登録に関すること。
この設問では，重複更新，関係の喪失を例示するのはふさわしくない。