Database Systems Engineer Examination

H17午後II 問1
機械式駐車場設備メンテナンス業務

設問２（１）
エンティティ「出動」の追加により，
スキーマの属性を「出動指示番号」から「出動番号」に変更するエンティティを列挙する問題。

とりあえず，追加した状態を書く。（リレーションの変更は未実施）

設問の指示どおり，
『出動指示書を発行せずに，修理情報や交換部品情報を登録できるようにする』
と，いうことで「駐車場設備修理」と「交換部品明細」のリレーションを変更する。

解答例：設問２（１）
駐車場設備修理，交換部品明細
で，ＴＡＣとＩＴＥＣの解答例は上のとおりであるが。。。

他の可能性を検討してみる。

さて，修正後の概念データモデルを見ると，点検と修理が随分遠くなった気がする。
点検の結果，修理を行う場合もあるので，もうちょっと近くに置けないかと。。。いうことで，
「駐車場設備点検」と「部位装置点検結果」，「検出異常」のリレーションも変更してみる。

ううーむ。。。これでも悪くない気がするが。。。
もしかして。。。この解答によっては，設問２（３）の解答も影響するんではないかい。。。

もう少し検討してみよう。

H17午後II 問1
機械式駐車場設備メンテナンス業務

設問１（１）
定番のエンティティタイプ名の穴埋め
図６関係スキーマにあって，図５の概念データモデルにないエンティティは
・装置タイプ
・部位装置
・部品
・基本部品
・汎用部品
の五つ。

「メンテナンス用部品には，基本部品と汎用部品がある」 → ａ，ｃ，ｄ
「部位装置汎用部品明細」に矢印がある方（d）が「汎用部品」
ｅは，リレーションから「部品番号」，「駐車場製品番号」を外部キーに持っているので「部位装置」で決まり，
ｄは，「装置タイプ」となる。
解答例：設問１（１）
　ａ）部品
　ｂ）装置タイプ
　ｃ）基本部品
　ｄ）汎用部品
　ｅ）部位装置

設問１（２）
リレーションシップの記述
・駐車場設備
・料金プラン
・契約
・個別サービス
・契約明細
とりあえず，スキーマから追う。

「駐車場設備」に引かれる矢印は充足している。
「料金プラン」に引かれる矢印は充足している。

「契約」に引かれる矢印は足りないようだ。
　・緊急呼出し料金プラン番号
　・修理工賃料金プラン番号
　・修理部品費料金プラン番号
の３つが足りていない。
　参照元は，属性名が「○○料金プラン番号」なので，「料金プラン」の「料金プラン番号」が妥当である。
　これは，それぞれ（緊急呼出し／修理工賃／修理部品費）に対して，インスタンスが排他的に対応しているハズであるが。。。（プラン01は修理部品費には使えない とか）
　これを区分するモノが「料金プラン」に含まれていないのが気がかりではある。。。（ま　いいけど）

「個別サービス」に引かれる矢印は充足している。

「契約明細」に引かれる矢印は属性が書かれていないため判断できない。
とりあえず，契約明細は契約の記述エンティティである（ハズ）なので，
　契約→契約明細
は，間違いない。
あとは，問題文から探る。
『個別部分は，・・・，駐車場設備ごとに提供される個別サービスについて規定している。』
と，いう記述から，契約明細は，駐車場設備の主キーと個別サービスの主キーを外部キーに持つと判断できる。
また，これで，記述対象のエンティティすべてに対して矢印の入/出が書けた。
解答例：設問１（２）
　契約＜－料金プラン
　契約＜－料金プラン
　契約＜－料金プラン （料金プランから契約へ３本矢印を書く）
　契約明細＜－契約
　契約明細＜－駐車場設備
　契約明細＜－個別サービス

設問１（３）
主キーと外部キーの指摘

部位装置点検項目は，図３と図５から，
部位装置汎用部品明細は，図５から 導ける。
解答例：設問１（３）
　部位装置点検項目（駐車場製品番号，部位装置番号，部位装置点検番号，点検項目番号）

　部位装置汎用部品明細（駐車場製品番号，部位装置番号，部品番号，個数）
焦っていると，点検項目番号を主キーに含めてしまうミスをしてしまうかもしれない。

H17午後I 問3
会員管理システムのSQL文

設問２（１）
とりあえず，やってみました。
ユーザ定義関数 GetAge10( )は，作成せずテーブル「会員」に列「年代」を追加しました。
あと，
「 BETWEEN taikan '1200' and '1700' 」がうまく動作しなかったので
「 taikan >= '1200' and taikan <='1700' 」としています。
※初めてMySQLをマトモ(?)に使いました。。。

mysql> select * from kaiin;
+---------+------+------------+------------+--------+--------+
| kaiinno | sex  | birth      | nyukai     | taikai | nendai |
+---------+------+------------+------------+--------+--------+
|   10001 | 男   | 1980-01-10 | 2004-09-07 | NULL   | 20     |
|   10002 | 男   | 1972-02-15 | 2004-10-06 | NULL   | 30     |
|   10003 | 女   | 1972-03-20 | 2004-11-05 | NULL   | 30     |
|   10004 | 男   | 1968-04-25 | 2004-12-04 | NULL   | 30     |
|   10005 | 女   | 1973-05-01 | 2005-01-03 | NULL   | 30     |
|   10006 | 男   | 1969-06-05 | 2005-02-02 | NULL   | 30     |
|   10007 | 女   | 1981-07-01 | 2005-03-01 | NULL   | 20     |
+---------+------+------------+------------+--------+--------+
7 rows in set (0.00 sec)

mysql> select * from riyourireki;
+------------+---------+--------+--------+
| riyou      | kaiinno | nyukan | taikan |
+------------+---------+--------+--------+
| 2005-03-06 |   10003 | 1030   | 1300   |
| 2005-03-07 |   10005 | 1200   | 1430   |
| 2005-03-07 |   10002 | 2000   | 2200   |
| 2005-03-07 |   10007 | 1900   | 2200   |
| 2005-03-08 |   10005 | 1200   | 1430   |
| 2005-03-08 |   10004 | 1700   | 1900   |
| 2005-03-09 |   10005 | 1200   | 1430   |
| 2005-03-09 |   10007 | 1900   | 2200   |
+------------+---------+--------+--------+
8 rows in set (0.00 sec)

mysql> select nendai,sex,count(a.kaiinno) as a1, coalesce(sum(b1),0) as a2,
    -> coalesce(sum(b2),0) as a3, coalesce(sum(b3),0) as a4
    -> from(select nendai,sex,kaiinno from kaiin
    -> where (taikai is null or taikai > '2005-03-31')
    -> and nyukai <= '2005-03-31') as a left outer join
    -> ( select kaiinno,
    -> sum(case when nyukan < '1200' then 1 else 0 end ) as b1,
    -> sum(case when taikan >= '1200' and taikan <='1700' then 1 else 0 end) as b2,
    -> sum(case when taikan > '1700' then 1 else 0 end) as b3
    -> from riyourireki where riyou between '2005-03-01' and '2005-03-31'
    -> group by kaiinno) as b
    -> on a.kaiinno = b.kaiinno
    -> group by nendai,sex
    -> order by nendai,sex;

+--------+------+----+------+------+------+
| nendai | sex  | a1 | a2   | a3   | a4   |
+--------+------+----+------+------+------+
| 20     | 男   |  1 |    0 |    0 |    0 |
| 20     | 女   |  1 |    0 |    0 |    2 |
| 30     | 男   |  3 |    0 |    0 |    2 |
| 30     | 女   |  2 |    1 |    4 |    0 |
+--------+------+----+------+------+------+
4 rows in set (0.00 sec)

設問２（２）

COALESCE関数についてはこちらの記事を参照：SQL: COALESCE関数
解答例：設問２（２）
使用目的：年代，性別によっては利用履歴が存在せず，時間帯別利用者数の集計値がＮＵＬＬとなる所を０と出力するため。（５１文字）

意図した状況が発生する年代と性別。
利用履歴が存在せずA2,A3,A4のすべてがNULLとなり，0に置き換えられるのは２０代男性である。
解答例：設問２（２）
年代：20
性別：男