Database Systems Engineer Examination

Ｈ１１午後I 問３（ITEC 2005予想問題集 午後I 問１－５） vol.2
vol.1

設問２（３）
vol.1で

ここで，安易に
　乗務（乗員，日付，機種）　としないように。

まず，関係“フライト”の関数従属性のおさらい。
　
｛乗員，日付｝→機種　・・・　図１の関数従属性より
｛便名，日付｝→機種　・・・　問題文の記述より
｛便名，日付，乗員｝→機種　・・・　設問1(2)の解答より

設問２（２）で失われている関係は
　｛乗員，日付｝→機種 と ｛便名，日付，乗員｝→機種

では，
便（便名，機種，日付）
乗務（便名，乗員，日付）　と，分割した場合
　
｛乗員，日付｝→機種，及び｛便名，日付｝→機種は保存されるが
便名，日付，乗員｝→機種が保存されない。

便（便名，機種，日付）
乗務（便名，日付，乗員）　と，分割した場合
　
｛乗員，日付｝→機種 ，｛便名，日付｝→機種 及び ｛便名，日付，乗員｝→機種 が保存されている。
｛乗員，日付｝→機種の関係が失われている。

　この問題は，問題文に書いてある「｛乗員，日付｝→機種，｛便名，日付｝→機種 」の関係を保存すればよいと錯覚してしまう。しかし，設問1(2)で解答した「候補キー → 非キー属性」の関係，すなわち，「｛便名，日付，乗員｝→機種，｛便名，日付｝→機種 」の関係も保存しなければならない。

追記
　設問２（２）において，失われる関係を「｛便名，日付，乗員｝→機種」とせずに，この部分関数従属である，「｛便名，日付｝→機種」を指摘，もしくは両方を指摘すると設問２（３）で行き詰る。
　「｛便名，日付，乗員｝→機種」と解答すれば，設問２（３）でこの関係（だけ）保存すれば良いと考えられる。

Ｈ１１午後II 問２
設問１（２）：IN演算子

SELECT 受注番号, 明細番号, 企業コード, 取引先コード,
　　　　　受注年月日, 発注年月日, 納品年月日, 請求年月日,
　　　　　入金年月日, 商品コード, 商品名, 単価, 数量, 金額
FROM 取引実績
WHERE 取引実績.企業コード
IN( SELECT 取引先.企業コード
　　FROM 取引先, 営業担当取引先, 社員, 組織
　　WHERE 組織.所属場所コード　= '00100' AND
　　　　組織.組織コード = 社員.組織コード AND
　　　　社員.社員番号 = 営業担当取引先.社員番号 AND
　　　　営業担当取引先.取引先コード = 取引先.取引先コード )

Ｈ１１午後I 問２
設問１：UPDATE

UPDATE 従業員
　　SET 退職フラグ = '1'
　　WHERE 生年 * 10000 + 生月 * 100 + 生日 BETWEEN 19390422 AND 19390521

SELECT 従業員.従業員コード, 従業員.氏名, COUNT(*)
FROM 従業員, 従業員家族
WHERE 従業員.従業員コード = 従業員家族.従業員コード AND
　　従業員家族.生年 >= 1987 AND
　　従業員家族.扶養フラグ = '1'
GROUP BY 従業員.従業員コード, 従業員.指名
ORDER BY 従業員.従業員コード

Ｈ１１午後II 問１（ITEC 2005予想問題集 午後II 問５） vol.2 vol.1

２回目でまた悩む。。

設問１（１）
発送に発送先拠点コードは必要か？
　対応する出庫を見れば，出庫先拠点コード＝発送先拠点コード
　と，追えるので冗長ではないだろうか。
　iTAC解答例には記載がない。

発送（発送番号、発送年月日）

入庫に出庫元拠点コードは必要か？
　　「出庫元の拠点，対応する出庫番号」とあるので，出庫業務がある拠点コードであれば出庫番号から追えるので冗長と言える。

　しかし，この出庫元拠点コードに「内作部品の入庫」「生産現場からの戻入」「購入部品の入庫」「加工業者からの入庫」時の入庫元の拠点コードが入るのであれば，出庫元拠点コードは必要と言える。
　だが問題文からはいまいち読み取れない。それは，“出庫元の拠点”としている点で，同じ拠点コードが入るとは言え，取る値の範囲が違うので，属性名は変えるべきだと思う。（出庫元拠点コードではなく，入庫元拠点コードとか）

設問１（２），（３）
　「業務上どのような場合」はどう表現して良いものやら。
　iTACの解答例は淡白に書いているなぁ・・・

(2)他の事業者の拠点に対して送る目的で出庫される場合
(3)ある拠点の倉庫などの管理場所から送られてきた場合

何が言いたいのかというと，場合を示す時の「業務の粒度」をどう考えるのかという点である。
文字数が許せば，ピンポイントで押さえた方が良いと思うんだけどなぁ。。。

設問２（１）まる１
　iTAC解答例じゃないよね。あれって，「表４　物流事象一覧表」の説明を入れるってことだよな。。。
こっちが本題
　「物流事象を記録するトランザクション処理」というのは，「発送業務」を対象としているのか「時点在庫を更新する業務」を対象としているのかで若干，答えが変わると思う。
　せっかくチェックロジックを入れるのであれば，アイテック解答例の積置在庫数，輸送中在庫数だけでなく，倉庫内在庫数の更新もチェック対象とすべきではないかなと。
　gakko解答例「時点在庫の属性｛倉庫内在庫数，積置在庫数，輸送中在庫数｝を，どのように更新するのか処理が特定できるような意味であるべき」と書いてみた。
　「どのように」には「どれを」「増なのか減なのか」という意味を含ませている。「どれを」だけでも十分と思われる。

設問３（２）
vol.1でもいろいろ書いたけど，やはり，差分反映が正解。
　棚卸し作業中は営業時間外なので出庫はないが，論理在庫と実在庫の差が判明するのは，翌営業日の午前中となるため，翌営業日午前中の出庫業務の有無は判断が分かれる所であろう。

さて，時間的流れを考えると

月末の営業日の営業終了時～翌日の営業開始直前
実在庫：９個
Ａ：■■■■■■■■■

月末の営業日の翌日午前中
論理在庫：１０個（月末の営業日の営業終了後の時点在庫）
Ｂ：■■■■■■■■■■

この時はじめて，棚卸し時点で実在庫が論理在庫より，１個少ないことが判明
Ｃ：■■■■■■■■■□　←１個少ない！

（出庫業務があるとすると）
ここで出庫業務が既に始まっていていて，朝イチで２個出庫されてしまっていたとすると
Ｄ：■■■■■■■■□□　←２個出荷（論理在庫－２）

この状態で，倉庫内在庫数をＢに設定してしまうと，在庫が増えてしまうので
　倉庫内在庫数＝Ｄ－（Ｂ－Ｃ）＝８－（１０－９）＝７
もしくは
　倉庫内在庫数＝Ｃ－当日出庫数＝９－２＝７
となる。

　しかし，棚卸日の翌日に出庫業務をしているのか，していないのか明記がないため
倉庫内在庫数＝Ｄ－（Ｂ－Ｃ）
＝（現在の倉庫内在庫数）－（（前日営業終了後の倉庫内在庫数）－（実在庫数））
とするのが無難だろう。
　出庫業務をしていなければ，単に現在の倉庫内在庫数と前日営業終了後の倉庫内在庫数の値が同じだけということだ。ただ，この場合は，「現在の倉庫内在庫数」が分かることが前提となる。

　ここで矛盾に気付く。

　現在の倉庫内在庫数が分かるのなら，前日営業終了後の時点在庫の一覧表が午前中に出力されるのっておかしくないか？？
　きっとそれって，帳票印刷だから。と，勝手に納得してみたりして。。。。ｗ

この問題なんかすっきりしないんだよな。。。

Ｈ１１午後I 問３（ITEC 2005予想問題集 午後I 問１－５）
　ITEC 2005予想問題集の午後I データベース設計の基礎理論の中で一番難しい問題かな。

設問１（１）
　図２　関係“フライト”が，｛乗員，日付｝→機種を満たしているか素直に判断する。
　解答は淡白に「満たす」だけでも良いと思う。（私だったら，少し丁寧に「条件を満たす」とか，かなり丁寧に「図１の関数従属性の条件を満たす」とか書くかも。）

設問１（２）
　ここで躓く人は多いのではないだろうか。
　フライトの関数従属性は，
　　図１より，｛乗員，日付｝→機種
　　問題文：機種は，～，便名の日付ごとに決まっている。より，｛便名，日付｝→機種
　以上の条件で推移的関数従属もないので

　合併律（｛Ａ，Ｂ｝→Ｄでかつ｛Ａ，Ｃ｝→Ｄの場合，｛Ａ，Ｂ，Ｃ｝→Ｄが成り立つ）により
　　｛便名，日付，乗員｝→機種　となる。
　※候補キー内の列記の順番は意味を持っていないが，採点者を気遣いスキーマの左から順番に書こう。

　ここでややこしいのは，合併律を知らずに，問題文に書かれていない関数従属性を見つけてしまうことである。
　図２の中の関数従属性を見極め，候補キーを探していると。。。
　　｛乗員，日付｝→便名
　という関係が導ける。（これは，乗員は１日１便しか搭乗しないという業務上の制約を生む）

　　｛乗員，日付｝→｛便名，日付｝→機種

　と，推移関数従属し，候補キーは｛乗員，日付｝となる。（※｛便名，日付｝は候補キーではない）
　（※　｛乗員，日付｝→機種　の関係があるので，これを推移的関数従属と呼ぶのかよく分かりません）

　どちらにせよ，関数従属性が多く出てきて判断に迷ったら，冷静に，関数従属性の図を描いてみよう。

設問１（３）
　候補キーを｛便名，日付，乗員｝とした場合
　　部分関数従属：｛便名，日付｝→機種，｛乗員，日付｝→機種　が，あるため第１正規形
　候補キーを｛乗員，日付｝とした場合
　　推移的関数従属があるため，第３正規形ではない。
　　候補キーに対して非キー属性は完全関数従属しているため，第２正規形？
　　　　｛乗員，日付｝→便名，｛乗員，日付｝→機種　

設問２
　設問１で候補キーを｛便名，日付，乗員｝と解答したものとする。

設問２（１），（２）　定番ですね

設問２（３）
　｛便名，日付，乗員｝→機種
　　｛乗員，日付｝→機種
　　｛便名，日付｝→機種　・・・　図９と同じ
　ここで，安易に
　　搭乗乗務（乗員，日付，機種）　としないように。

設問３（１）
　アイテック解答例に，まだ納得できていない。
　NULL値を含むタプルを排除し，重複タプルも排除すればよさそうな気もするが・・・

設問３（２）
　冷静に埋めれば問題なし

設問３（３）
　射影だけで書く。勝手に選択しないこと。
　選択演算については，ＮＵＬＬの扱いが不明。射影の時点で排除しているので
　改めて，「乗員 IS NOT NULL」の条件が必要かどうか疑問だ。

Ｈ１１午後I 問１（ITEC 2005予想問題集 午後I 問３－４）
設問２（３）
　“試験成績”テーブルの構造を第２正規形にし，主キーとなる列に下線を引け。

　※ここでは，表現の都合，テーブル構造ではなく，スキーマで表現する。

　試験成績（校番号，児童番号，学力試験番号，教科，得点，校内順位，塾内順位，塾内平均点，塾内標準偏差，塾内偏差値）

　を，第２正規形に分割

　児童試験成績（児童番号，学力試験番号，教科，得点，校内順位，塾内順位，塾内偏差値）
　塾試験統計（学力試験番号，教科，塾内平均点，塾内標準偏差）

ここで，
　児童（校番号，児童番号）
を，あえて記載するかどうか悩ましい。児童テーブルは既に存在しているからだ。
ＩＴＥＣ，アイタック，どちらの解答例も，児童を記載している。
やはり，書くべきなのだろうか・・・

ちなみに，こいつの収め先を・・・

児童試験成績に校番号を含めると，
　児童番号→校番号の関係があるので，部分関数従属性が存在することになり，設問の第２正規形の条件を満たさない。

塾試験統計に校番号を含めると，
　候補キーは，｛学力試験番号，教科，校番号｝となるが，｛学力試験番号，教科｝→｛塾内平均点，塾内標準偏差｝であるため部分関数従属となり，第２正規形の条件を満たさない。

　｛校番号，児童番号，学力試験番号，教科｝→｛得点，校内順位，塾内順位，塾内偏差値｝
という見解もある。（この場合は校番号は非キー属性ではなく，候補キーの一部である）

　設問は「第２正規形の条件を満たせ」ということなんで，第２正規形の条件を満たす第３でもボイスコッドでも第４でも第５でも良い。つまりは，解答例はひとつではないということなのかな？

Ｈ１１午後I 問２
設問１

Commented by はぁはぁ at 2005-01-24 22:40
>gakkoさん
SOL関連の問題での疑問なんですけど、H11午後1問2の設問1の設問文をどうとらえていいかわかりません。
問題文中（[人事情報管理業務]（1））では
「退職フラグを’1’に変更し、退職年、退職月、退職日を設定」
と書いてあります。しかし、設問文中では、退職フラグを’1’に変更することだけを思わせるような感じで書かれています。
また、僕の解答は[人事情報管理業務]（1）から、毎月20日に退職者の更新処理（=設問1でのSQL文）が行われると考えたので

UPDATE 従業員
SET 退職フラグ = '1'
WHERE 生年 = 1939 AND
(生月 = 4 AND 生日 >= 21) AND
(生年 = 5 AND 生日 <= 20)

にしました。う～ん。。違うのかなぁ･･･

Commented by gakko at 2005-01-25 00:01
>はぁはぁ さん
　退職年，退職月，退職日に満60歳の誕生日の前日を設定しなくて良いのか？と，いうことですよね？
　設問では，一括して退職フラグを'1'に更新する処理と書いてあり，退職年月日を設定する処理とは書いていないので，フラグのみの更新でよいかと思います。

　余談ですが，１日が誕生日の退職者（昔の人は元旦が誕生日という人も多い（ｗ ）の前日を設定する。というのを，標準ＳＱＬで表現できそうな気がしません。。。できるのかな？>>9 殿

　さて，20日〆にかかるのは誕生日が２１日の人まで ですので，設問の条件だと，生年月日が１９３９年４月２２日～５月２１日の人ということになります。

ＳＱＬの方ですが，
UPDATE 従業員
　SET 退職フラグ = '1'
　WHERE 生年 = 1939 AND
　　((生月 = 4 AND 生日 >= 22) OR
　　 (生年 = 5 AND 生日 <= 21))

かな。と，思います。
>=(<=)を>(<)で表現したり，BETWEENもアリですね。

Commented by gakko at 2005-01-25 00:05
いやぁ～H11午後Ⅰ問２がITEC本に載ってなくてググってました。。。
何のことはない，本棚に眠っている2004予想問題集に載ってた（苦笑

ITECの解答例はBETWEEENを使ってありました。
BETWEENだと，４月が何日まであるのかを誤って解答を間違ったら最悪ですよね。。

Commented by はぁはぁ at 2005-01-25 00:37
>gakkoさん
レスありがとうございます。

＞さて，20日〆にかかるのは誕生日が２１日の人まで ですので，設問＞の条件だと，生年月日が１９３９年４月２２日～５月２１日の人ということ＞になります。
見逃してました。処理基準日が20日ってことは、誕生日が２１日の人まで考えないといけなかったですね。
細かいところを見逃してしまうので、こういう仕事向いていないのかも。。
あと、僕の解答の
(生月 = 4 AND 生日 >= 21) AND←
(生年 = 5 AND 生日 <= 20)
矢印の部分はORでしたね。間違えた。

参考までに、3週間本（通称=糞本）の解答を紹介します。
1.
UPDATE 従業員
　SET 退職フラグ = '1'
　WHERE 生年 < 1939 OR
　　(生年 = 1939 AND 生月 < 5) OR
　　(生年 = 1939 AND 生月 = 5 AND 生日 <= 21)

2.
UPDATE 従業員
　SET 退職フラグ = '1'
WHERE 生年 * 10000　+ 生月　* 100 + 生日 <=19390521

なんてのもありました。この本では、毎月20日に処理をしないことになっています。（これでも結果は同じだけど･･･）

Ｈ１１午後II 問１（ITEC 2005予想問題集 午後II 問５）

赤字：アイタックをソースに修正

設問１（１）
解答例には，部品から出庫明細へのリレーションシップの記載がない。
入庫明細への記載はあるのに。
しかも，入庫明細への根拠は「｛部品品目番号，部品枝番｝を持つから」
・・・出庫明細にもあるんですけど・・・・
　他の出版社じゃ，Ｈ１１の問題は載ってないだろうな。。。
　Ｗｅｂで探すか・・・

部品→出庫明細は必要
アイテックは，発送－出庫だが，アイタックは発送→出庫，
発送効率の記述から後者が正解と思われる。
と，いうことは発送伝票には“複数”の出庫番号が記入されることとなる。

設問２（２）②
　物流事象はすべての拠点の組み合せで発生するわけではなく，物流事象によって送り元の拠点と送り先の拠点の組み合せが限定されている。

解答例は
物流パターン（物流事象コード，送付元拠点コード，送付先拠点コード，物流可否）

となっているが，

表４の説明は拠点種類名毎の説明となっているんで
組み合せは拠点種類毎で十分と判断し，
物流パターン（物流事象コード，送付元拠点種類コード，送付先拠点種類コード，物流可否）

でもいいのかな。とも思ったんだが・・・ううむ。
アイタックの解答例は拠点種類毎（後者）ですね。

設問２（２）③
　出庫に外部キーとして，物流パターンの主キーで不足する属性（物流拠点コード，送付元（出庫元）拠点コード）を追加する。という解説になってるんだけど。。。
　出庫には既に出庫元拠点コードがあるんだけどな・・・
　入庫・出庫に物流事象コードを追加するだけでＯＫみたい

設問３（１）
　部品構成は「生産管理システム」への外部参照でいいんでは・・・１事実１箇所にしないと，更新漏れや矛盾が生じるよねぇ。
　でも，これ以外に何を追加するのか・・・思いつかん（苦
　アイタックも部品構成ですね。やっぱこれでいいんか

設問３（２）
　棚卸し中は出庫・入庫を停止するとは書いていないんで，入出庫も考慮すべきではないのかなと。（一般に停止するんだろうけど）
　そうすると，出庫は１日何回も行われるし，棚卸し日にトラックが到着し，輸送中在庫が入庫されることもあうだろう。
　実在庫を確認した後（同一日）に，入出庫があれば数字が合わなくなるはずだ。
　時点在庫や入庫，出庫の属性も見直す必要が出てくる。

　解答例のような都合の良い解釈でいいのかな・・・
　アイタックの趣旨もアイテックと同じ。
　アイテックは，在庫数に差異のあるものについて，差分を反映
　アイタックは，棚卸し（実）在庫数で“時点在庫”と全件更新
　設問３（２）については，vol.2を参照ください。