2005年05月03日

[ テクニカルエンジニア(データベース)/H17 ]

H17午後II 問1設問2(1)

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

設問2(1)
エンティティ「出動」の追加により,
スキーマの属性を「出動指示番号」から「出動番号」に変更するエンティティを列挙する問題。

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

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

解答例:設問2(1)
駐車場設備修理,交換部品明細

で,TACとITECの解答例は上のとおりであるが。。。

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

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

ううーむ。。。これでも悪くない気がするが。。。
もしかして。。。この解答によっては,設問2(3)の解答も影響するんではないかい。。。

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

Posted by g@kko at 2005/05/03 18:20 | 個別記事表示 | コメントを見る (15) |
この記事をLicWikiに埋め込む:
[ テクニカルエンジニア(データベース)/H17 ]

H17午後II 問1設問1

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

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

「メンテナンス用部品には,基本部品と汎用部品がある」 → a,c,d
「部位装置汎用部品明細」に矢印がある方(d)が「汎用部品」
eは,リレーションから「部品番号」,「駐車場製品番号」を外部キーに持っているので「部位装置」で決まり,
dは,「装置タイプ」となる。
解答例:設問1(1)
 a)部品
 b)装置タイプ
 c)基本部品
 d)汎用部品
 e)部位装置

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

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

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

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

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

設問1(3)
主キーと外部キーの指摘

部位装置点検項目は,図3と図5から,
部位装置汎用部品明細は,図5から 導ける。
解答例:設問1(3)
 部位装置点検項目(駐車場製品番号部位装置番号部位装置点検番号点検項目番号

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

Posted by g@kko at 2005/05/03 16:24 | 個別記事表示 | コメントを見る (1) |
この記事をLicWikiに埋め込む:
[ テクニカルエンジニア(データベース)/H17 ]

H17午後I 問3設問2

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

設問2(1)
とりあえず,やってみました。
ユーザ定義関数 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)
と,いうことで。 解答例:設問2(1) h:2  i:1  j:4  k:0

設問2(2)

COALESCE関数についてはこちらの記事を参照:SQL: COALESCE関数
解答例:設問2(2)
使用目的:年代,性別によっては利用履歴が存在せず,時間帯別利用者数の集計値がNULLとなる所を0と出力するため。(51文字)


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

Posted by g@kko at 2005/05/03 00:18 | 個別記事表示 | コメントを見る (4) |
この記事をLicWikiに埋め込む: