スキップしてメイン コンテンツに移動

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ　問2 設問3 (1)

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ
http://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2014h26_1/2014h26h_db_pm1_qs.pdf

問2

　設問3 〔会議室予約システムの改良結果〕について，(1)，(2)に答えよ。

　(1) 図7中の　t　 に入れる適切な字句を答えよ。


〔会議室予約システムについての改良結果〕

　図5の⑤～⑦の実行時に，ディスク容量不足などのエラーが発生して，予約処理のトランザクションが中断してしまうと問題が生じることが分かったので，〔会議室予約システムについての改良案〕は不採用とし，”会議室予約”テーブルを図6のように変更した。



　会議室予約（会議室番号，予約日，予約開始時刻，予約終了時刻，社員番号，予約済フラグ）


図6 変更した”会議室予約”テーブルのテーブル構造


　会議室予約を行う予約単位をコマと呼び，1コマは30分単位とする。図6の”会議室予約”
テーブルでは，各コマを0時00分から30分間隔で設定する。予約受付対象の全てのコマは
あらかじめ登録しておく。該当するコマが予約済みか否かを予約済フラグで識別し，
予約済みであれば’Y’，予約済みでなければ’N’とする。例えば，10時00分～11時30分を
予約済みとする場合，予約開始時刻が10時00分，10時30分，11時00分の3コマの予約済
フラグを’Y’に更新する。

　変更した”会議室予約”テーブルを使用して，予約処理内容を図7のように変更した。

　なお，図7中のホスト変数のcntはコマ数を表す。また，図7中のユーザ定義関数について
次に示す。

・PERIODSTART関数は，コマの終了時刻を与えて，コマの開始時刻を求めるユーザ定義
関数とする。例えば，11時30分を指定した場合，11時00分が返却される。

・PERIODCOUNT関数は，開始時刻と終了時刻を与えて，含まれるコマ数を求めるユーザ
定義関数とする。例えば，10時00分と11時30分を指定した場合，3が返却される。

・PERIODNEXT関数は，指定の時刻とコマ数を与えて，指定の時刻からコマ数だけ後に
ずらしたコマの開始時刻を求めるユーザ定義関数とする。例えば，10時00分とコマ数2を
指定した場合，11時00分が返却される。


①指定された条件に重なる予約が入っていないことをSELECT文で確認する。結果行がない場合，予約失敗として③に進む。結果行がある場合，②に進む。
’Ｙ’に更新する。更新できたか否かを記憶する。
SELECT 会議室番号 FROM 会議室予約
WHERE 会議室番号 = :hv4 AND 予約日 = :hv1
AND 予約開始時刻 BETWEEN :hv2 AND PERIODSTART(:hv3) AND 予約済フラグ = 'N'
GROUP BY 会議室番号 HAVING 　t　 = PERIODCOUNT(:hv2，:hv3)
②指定された条件の予約のため UPDATE文で更新処理を該当コマ数分繰り返す(cntは0から該当コマ数-1まで)。全て正常に更新できた場合，予約成功としてコミットし、③に進む。繰り返した中で，1会でも更新行がなかった場合，予約失敗としてロールバックし，③に進む。
UPDATE 会議室予約 SET 予約済フラグ = 'Y', 社員番号 = :hv5
WHERE 会議室番号 = :hv4 AND 予約日 = :hv1
AND 予約開始時刻 = PERIODNEXT(:hv2, :cnt) AND 予約済フラグ = 'N'
③予約の成否を通知する。

図7 予約処理内容の改良結果

だめだ、やっぱりデータイメージが湧いてこないので、サンプルを作ってみる。

○初期値
…予約開始時刻…予約済フラグ
…09:00…N
…09:30…N
…10:00…N
…10:30…N
…11:00…N
…11:30…N
…12:00…N
…12:30…N
…13:00…N

○Ａさんが11時00分～12時00分を予約
…予約開始時刻…予約済フラグ
…09:00…N
…09:30…N
…10:00…N
…10:30…N
…11:00…Y
…11:30…Y
…12:00…N
…12:30…N
…13:00…N

○Bさんが11時00分～13時00分を予約しようとして判定するのは？
…予約開始時刻…予約済フラグ
…09:00…N
…09:30…N
…10:00…N
…10:30…N
…11:00…Y　？
…11:30…Y　？
…12:00…N　？
…12:30…N　？
…13:00…N

①のSELECT文は、予約が存在するかどうかを調べるもの

ｔ　と　比較しているのは、PRIODCOUNT( :hv2，:hv3)

なるほど、つまり指定の予約開始時刻と予約終了時刻から、コマ数を求め、
会議室の空きコマ数（会議室予約テーブルの予約済フラグが'N'であるレコード件数）
と同じになるかどうかで判定している。それをHAVINGの条件とすることで、
取得結果の有無になるのだ。

例：Bさんの場合
11時00分から13時00分まで4コマであるが、SELECTのGROUP　BYの結果では、
2コマとなって、HAVING条件が一致しない。つまり「予約失敗」という判定とするのだ。


【回答】

t　：　COUNT(*)


コメント

コメントを投稿

このブログの人気の投稿

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ 問3 設問3 (1)

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ http://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2014h26_1/2014h26h_db_pm1_qs.pdf 問3 テーブルの設計及びSQLの設計に関する次の記述を読んで，設問1～3に答えよ。 　設問3 〔注文トランザクションの設計〕について，(1)，(2)に答えよ。 　(1) 次のTR1～TR4のうち，いずれか二つの組合せのトランザクションを同時に実行したとき，デッドロックが起こるおそれがある。次の表中の 　ウ　 ～ 　カ　  にデッドロックが起きない組合わせには○を，起きるおそれがある組合わせには×を記入せよ。 　　TR1：単品商品2個を注文する。 　　TR2：単品商品1個とセット商品1個を注文する。 　　TR3：セット商品1個を注文する。 　　TR4：セット商品2個を注文する。 ＼ TR1 TR2 TR3 TR4 TR1 ○ 　ウ　 　エ　 × TR2 ＼ × × × TR3 ＼ ＼ 　オ　 　カ　 TR4 ＼ ＼ ＼ × 問題の対象となっているのは、以下の内容である。 〔注文トランザクションの設計〕 　Fさんは，注文トランザクションについて，次のように設計した。 (1) 注文単位を一つのトランザクションで処理し，最後にCOMMIT文を発行する。 (2) 注文に基づいて，”注文”テーブル及び”注文明細”テーブルに行を挿入する。 (3) 商品については，商品一覧画面に表示された順番に”在庫”テーブルの 　引当可能数を調べ，引当可能ならば注文数を減算した値で引当可能数を更新する。 (4) セット商品が在庫不足のとき，”在庫”テーブルの不足セット商品数に不足数を 　加算する。”セット商品構成”テーブルから，主キー順に当該セット商品を構成する 　単品商品の構成数を調べ，必要数を計算する。単品商品については，”在庫” 　テーブルの引当可能数には必要数を減算した値で、不足セット商品用引当済には 　必要数を加算した値で更新する。 (5) トランザクションのISOLATIONレベルは，READ COMMITTEDとする。 検討す...
コメントを投稿
続きを読む

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ 問2 設問1 (1)

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ http://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2014h26_1/2014h26h_db_pm1_qs.pdf 問2 　設問1 会議室予約システムについて，(1)～(4)に答えよ。 　(1) 図2中のSQL文の 　a　 ～ 　c　 に入れる適切な字句を答えよ。 〔会議室予約システムのテーブル〕 　会議室予約システムの主要なテーブルのテーブル構造，概要は，図1，表1のとおりである。 　社員（ 社員番号 ，社員氏名，…） 　会議室（ 会議室番号 ，収容可能人数，階数，プロジェクタ設置有無，TV会議設備設置有無，…） 　会議室予約（ 会議室番号 ， 予約日 ， 予約開始時刻 ，予約終了時刻， 社員番号 ） 図1 主要なテーブルのテーブル構造（一部省略） 表1 主要なテーブルの概要 テーブル名 概要 社員 ・社員の情報を管理する。社員番号で社員を一意に識別する。 会議室 ・会議室の情報を管理する。会議室番号で会議室を一意に識別する。 ・会議室ことに収容可能な人数，階数，設備の設置有無が設定されている。 会議室予約 ・会議室の予約状況を管理する。会議室番号，予約日，予約開始時刻で 会議室予約を一意に識別する。 ・予約開始時刻及び予約終了時刻の分の指定は00分または30分とする。 〔会議室予約システムでの検索〕 　会議室予約システムで空き会議室の検索結果一覧を表示する際に必要な情報を得るために実行するSQL文の例を図2に示す。 　なお，図2中のホスト変数のhv1は予約希望日，hv2は予約希望開始時刻，hv3は予約希望終了時刻を表す。 SELECT * FROM 会議室 X WHERE 　a　 (SELECT * FROM 会議室予約 Y WHERE X.会議室番号 = Y.会議室番号 AND Y.予約日 = :hv1 AND Y.予約開始時刻 　b　 :hv3 AND Y.予約終了時刻 　c　 :hv2) 図2 検索で実行するSQL文の例 図2のSQLから、判明できることは、 対象となる...
コメントを投稿
続きを読む

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ 問3 設問2 (2)

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ http://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2014h26_1/2014h26h_db_pm1_qs.pdf 問3 テーブルの設計及びSQLの設計に関する次の記述を読んで，設問1～3に答えよ。 　設問2 〔SQL分の設計〕について，(1)～(3)に答えよ。 　(2) SQL4及びSQL5，指定した注文について，注文されたセット商品を構成する単品商品の合計数を求めるSQL文です。SQL4及びSQL5の実行結果が同じにように， 　h　  に入れる適切な字句を答えよ。 問題の対象となっているのは、以下の内容である。 〔SQL文の設計〕 　Fさんが，案1と案2のそれぞれについて設計した主なSQL文を表4に示す。 表4　案1と案2について設計した主なSQL文（未完成） 案 SQL SQL文 案1 SQL1 SELECT M.商品番号，P.社内原価，P.化粧箱番号 FROM 注文明細 M，商品 P WHERE M.商品番号 = P.商品番号 AND M.注文番号 = :hv 案2 SQL2 SELECT M.商品番号，T.社内原価，S.化粧箱番号 FROM 注文明細 M 　f　  JOIN 単品商品 T ON M.商品番号 = T.商品番号 　f　  JOIN セット商品 S ON M.商品番号 = S.商品番号 WHERE M.注文番号 = :hv 案2 SQL3 SELECT M.商品番号，T.社内原価，CAST(NULL AS INT) 化粧箱番号 FROM 注文明細 M　 　g　  JOIN 単品商品 T   ON M.商品番号 = T.商品番号 WHERE M.注文番号 = :hv UNION ALL SELECT M.商品番号，CAST(NULL AS INT) 社内原価，S.化粧箱番号 FROM 注文明細 M　 　g　  JOIN セット商品 S   ON M.商品番号 = S.商品番号 WHERE M.注文番号 = :hv 案1 SQL4 SELECT K.単品.商品番号，SUM( 　h　  ...
コメントを投稿
続きを読む