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

平成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文（未完成）

案
SQLSQL文
案1SQL1
SELECT M.商品番号，P.社内原価，P.化粧箱番号
FROM 注文明細 M，商品 P
WHERE M.商品番号 = P.商品番号 AND M.注文番号 = :hv
案2SQL2
SELECT M.商品番号，T.社内原価，S.化粧箱番号
FROM 注文明細 M
　f　 JOIN 単品商品 T ON M.商品番号 = T.商品番号
　f　 JOIN セット商品 S ON M.商品番号 = S.商品番号


WHERE M.注文番号 = :hv
案2SQL3
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
案1SQL4
SELECT K.単品.商品番号，SUM( 　h　 )
FROM 注文明細 M，商品 P，セット商品構成 K
WHERE M.注文番号 = :hv AND M.商品番号 = P.商品番号
 AND P.商品番号 = K.セット商品番号
GROUP BY K.単品商品番号
案2SQL5
SELECT K.単品.商品番号，SUM( 　h　 )
FROM 注文明細 M，セット商品 S，セット商品構成 K
WHERE M.注文番号 = :hv AND M.商品番号 = S.商品番号
 AND P.商品番号 = K.セット商品番号
GROUP BY K.単品商品番号
注記　hvは，ホスト変数を表す。


SQL4とSQL5の違いを確認する。

FROM句の結合している表が異なる。

SQL4 注文明細、商品、セット商品構成
SQL5 注文明細、セット商品、セット商品構成

それでは、２つのテーブルを確認する。



　商品（商品番号，商品名，商品説明，写真，販売単価，表示順，単品区分，
　　　　社内原価，化粧箱番号，詰合せ日数）


図2 案1の”商品”テーブルの構造



　単品商品（商品番号，商品名，商品説明，写真，販売単価，表示順，社内原価）
　セット商品（商品番号，商品名，商品説明，写真，販売単価，表示順，
　　　　　　　化粧箱番号，詰合せ日数）



















図3 案2の”単品商品”テーブル及び”セット商品”テーブルの構造



そして、サンプルデータ



商品テーブル





商品番号





商品名





…




TS001たまご茸…


TS002へび苺…


SS001山菜セット（松）…


SS002山菜セット（竹）…







セット商品テーブル





商品番号





商品名





…




SS001山菜セット（松）…


SS002山菜セット（竹）…


SS003山菜セット（梅）…


SS004山菜セット（桜）…







セット商品構成テーブル





セット商品番号





単品商品番号





構成数




SS001TS00150


SS001TS00240


SS001TS00340


SS002TS00130


SS002TS00320


SS002TS00420







勘違いしないように、もう一度問題を確認すると、「注文されたセット商品を構成する単品商品の合計数を求めるSQL文」である。



注文されたセット商品が"SS001","SS002"の場合は、50+40+40+30+20+20=200ということ。



SQL4の結果







単品商品番号





構成数




TS00180


TS00240


TS00360


TS00420





hには、SUM()関数が使われているの、どのテーブルのどの項目を合計しているかを回答することになる。表の別名が定義されているので、それを使って表す。





【回答】



h　：　K.構成数









































コメント











コメントを投稿



















このブログの人気の投稿



















平成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から、判明できることは、   対象となる...




















コメントを投稿









続きを読む