データベーススペシャリスト 平成31年 午後1 解説解答

• 問1

  • 設問1-(1)
  • 設問1-(2)
  • 設問2
  • 設問3-(1)
  • 設問3-(2)

• 問2

  • 設問1-(1)
  • 設問1-(2)
  • 設問2-(1)
  • 設問2-(2)
  • 設問3-(1)
  • 設問3-(2)

• 問3

  • 設問1-(1)
  • 設問1-(2)
  • 設問2-(1)
  • 設問2-(2)
  • 設問3-(1)
  • 設問3-(2)
  • 設問4-(1)
  • 設問4-(2)
  • 設問4-(3)
  • 設問4-(4)

公式サイトの問題冊子はこちら
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000ddiw-att/2019h31h_db_pm1_qs.pdf

問1-設問1-(1)

図2中のa～gに入れる適切な属性名を答えよ

• 「種目は、種目コードで識別し、種目分類コードで、ランニング、自転車レースなどに分類する」

• a: 種目分類コード(FK)

• 「大会は、大会番号で識別し、大会名、開催年月日、開催場所の都道府県コード、主催者番号を登録する」

• b: 主催者番号(FK)

• 「エントリ枠は、大会番号とエントリ枠番号で識別し、エントリ枠名、エントリ枠説明、種目コード、定員、参加費用、募集期間(募集開始年月日～募集終了年月日)などを登録する」

• c: 種目コード(FK)

• 「参加申込みは、大会番号、会員番号で識別し、参加申込年月日を登録する」

• d: 会員番号
• e: 大会番号

• 「A社は、会員の参加費用の支払を確認して入金年月日を登録し、参加費用に対して一定割合のポイントを会員に付与する」
• ポイント計算の為に参加費用を記録
• 支払確認のために入金年月日を記録

• f: 参加費用
• g: 入金年月日

問1-設問1-(2)

図1のリレーションシップは未完成である。必要なリレーションシップを全て記入し、図を完成させよ

• 図2の関係スキーマをチェック
• 種目分類コードは種目の外部キーなので、種目分類と種目に1対多のリレーションシップ

• 主催者番号は大会の外部キーなので、主催者と大会に1対多のリレーションシップ

• 大会番号と運営サービスコードで大会運営サービスを識別するので、大会と運営サービスは大会運営サービスと1対多のリレーションシップ

• 大会番号とアイテムコードで大会アイテムを識別するので、大会とアイテムは大会アイテムと1対多のリレーションシップ

• 大会番号とエントリ枠番号でエントリ枠を識別するので、大会はエントリ枠と1対多のリレーションシップ
• 種目コードはエントリ枠の外部キーなので、種目とエントリ枠に1対多のリレーションシップ

• 大会番号と会員番号で大会アイテムを識別するので、大会と会員は参加申込みと1対多のリレーションシップ
• エントリ枠番号は参加申込みの外部キーなので、エントリ枠と参加申込みにも1対多のリレーションシップ
• 大会と参加申込みのリレーションシップは、エントリ枠と参加申込みのリレーションシップの一部なので削除

問1-設問2

太枠外の例に倣って表を完成させよ

• 「エントリ枠の登録においては、初期値を募集前にする」に該当するのは抽選1
• 「募集期間が終わり、参加申込数が定員以下だったら、参加者確定にする」に該当するのは参加者確定かつ抽選を実施しない場合なので抽選3
• 「募集期間が終わり、参加申込数が定員を越えていれば抽選中にし」に該当するのは抽選4
• 「その後、抽選年月日に抽選を実施した上で参加者確定にする」 に該当するのは抽選5
• 抽選後は抽選6
• 「エントリ枠状態が募集中の間だけ、参加申込みを受け付ける」に該当するのは抽選2

先着順抽選区分	抽選1	抽選2	抽選3	抽選4	抽選5	抽選6
募集期間に対する本日	前	中	後	後	後	後
参加申込数	ー	ー	以下	超過	超過	超過
抽選年月日に対する本日	前	前	ー	前	当日	後

問1-設問3-(1)

①

属性を追加する関係名及び追加する属性名を答えよ

• 「多段階抽選の対象のエントリ枠には、後続のエントリ枠を一つ設定する」

• 関係名: 抽選エントリ枠
• 属性名: 後続エントリ枠番号(FK)

②

属性を削除する関係名及び削除する属性名を答えとよ

• 「エントリ枠の抽選ごとに抽選結果を登録する」
• 参加申込み内にある属性“抽選結果”を、参加申込み、エントリ枠と多対多の関係の属性にする

• 関係名: 参加申込み
• 属性名: 抽選結果

③

追加する関係の関係スキーマを答えよ

• 参加申込み内にある属性“抽選結果”を、参加申込み、エントリ枠と多対多の関係の属性にする

• 抽選結果(会員番号、大会番号、エントリ枠番号、抽選結果)

問1-設問3-(2)

変更後の関係の属性名を答えよ

• 「ポイントの有効期限は、付与された日から1年であり、有効期限を超過したポイントは消失する」
• 各会員ごと有効期限ごとに、付与ポイントの使用状況を記録すれば実現可能

• 会員ポイント(会員番号、有効期限年月日、付与ポイント、使用ポイント)

問2-設問1-(1)

図2中のア～カに入れる適切な字句を答えよ

• 「テーブルに対する変更操作(挿入・更新・削除)を契機に、あらかじめ定義した処理を実行する」
• 「出庫要求に連動した在庫引当を実行させたいので、トリガを利用するように処理を見直すことにした」
• 在庫引当は部品ごとに行われるので、出庫要求明細への行挿入をトリガにする
• 引当不可能の場合にエラーで中断させるためトリガ前に行い、可能であることを確認したトリガ後に引当済数量の更新を行うようにする
• 引当可能かどうかは、実在庫数量が出庫要求数量より多いかどうかで判定

• ア: 出庫要求明細
• イ: 挿入
• ウ: 部品番号
• エ: 在庫
• オ: 実在庫数量
• カ: 引当済数量

問2-設問1-(2)

(a)

どのようにして問題が発生するか、80字以内で述べよ

• 実在庫数量が残り1の場合

• BEFOREトリガの処理完了後、引当済数量の更新前に別のトリガが割り込み、引当済数量の参照と更新が行われると引当可能判定が正しく行われず負値になることがある

(b)

どのような対策を施す必要があるか、20字以内で述べよ

• SELECT句にfor UPDATE制約を追加することで、先行するトランザクションが終わるまで他のトランザクションが割り込むことを防止できる

• for UPDATE句を追加する

問2-設問2-(1)

図3中のa～dに入れる適切な字句を答えよ

• 在庫引当後に基準在庫数量を下回る在庫テーブルの行に対して行うようにする

CREATE TRIGGER TR1 AFTER UPDATE OF 引当済数量ON 在庫
 REFERENCING NEW ROW AS CHKROW 
 FOR EACH ROW 
 WHEN (CHKROW.実在庫数量-CHKROW.引当済数量<=CHKROW.基準在庫数量)
 BEGIN ATOMIC 
  CALL PARTSORDER (CHKROW.部品番号)
 END 

• a: ア
• b: オ
• c: ウ
• d: キ

問2-設問2-(2)

表2中のあ～えに入れる適切な字句を答えよ

• 「図3で定義したトリガが発動」するのは、「UPDATE OF 引当済数量 ON 在庫」の時
• 「出庫では、～それぞれの部品の出庫が完了したら、“在庫”テーブルの実在庫数量及び引当済数量を更新し、～」
• 「部品ごとの実在庫数量から引当済数量を差し引いた値が、基準在庫数量を下回った都度、部品ごとに決められた部材メーカーに対して、決められた数量を発注する」

• あ: 出庫
• い: 在庫
• う: 引当済数量
• え: 発注済フラグ

問2-設問3-(1)

出庫要求と入庫でデッドロックが発生することがる。対象のテーブル名を答えよ

• 「組立に必要な複数の部品を一つの出庫要求とし、1トランザクションで処理する」
• 「入庫では、～納品された複数の部品をまとめて、1トランザクションで処理する」
• 複数部品の処理が全て完了するまで、ロックが解放されない
• 出庫要求でa>b順、入庫でb>a順のトランザクションが同時に発生すると、双方とも2つめの行のロック解除待ちになる

• 在庫

問2-設問3-(2)

(1)のデッドロックの回避策を2つ挙げ、それぞれ50字以内で具体的に述べよ

• 複数部品をまとめて処理しなければ、処理完了した部品のロックは一旦解放される
• 同時に開始した別のトランザクションのなかで、複数部品が逆順で処理されると、互いにロック済みの部品を待ち続ける状態になる

• 1: 出庫要求明細への行挿入または入庫を、部品番号ごとに処理するトランザクションに変更する
• 2: 出庫要求明細への行挿入と入庫を、部品番号の小さい順に処理するように変更する

問3-設問1-(1)

図1中のア～ウに入れる適切な字句を答えよ

表2の一部

親品番	子品番	構成数
P5	P3	1
P5	P6	1
P6	P8	1
P6	P9	1

• P5はP3とP6を各1個ずつ使用
• P6はP8とP9を各1個ずつ使用

• ア: P3
• イ: P6
• ウ: P8

問3-設問1-(2)

図2中のエ～カに入れる適切な字句を答えよ

図1の一部

• AZはP7を使用
• AZはP2とP4を使用

• エ: P7
• オ: P7
• カ: P2

問3-設問2-(1)

表3中のSQL1のa、bに入れる適切な字句を答えよ

• SQL1の一部に「L1.親品番=‘AZ‘」とあるので、AZはL1の親で、L1はL2の親

• L2にとっての親はL1

• a: 子品番
• b: 親品番

問3-設問2-(2)

製品AZを1個製造するのに必要な、部品P2、P3及びP4の所要量をそれぞれ答えよ

AZ=P3×1+P7×2+P9×2

• AZ一つにP3×1
• P7一つにP2とP4がそれぞれ1つ必要なので、AZ一つにP2×2+P4×2
• P2一つにP3が2つ必要なので、更にP3×2×2

• P2: 2
• P3: 5
• P4: 2

問3-設問3-(1)

表3中のSQL2のc、dに入れる適切な字句を答えよ

• SQL2の一部に「L1.親品番=品番」とあるので、L1は品番とレベル1をつなぐ層、「L2.子品番='P9'」とあるので、L2はレベル1とレベル2をつなぐ層

• c: 親品番
• d: 子品番

SQL2を用いて得られる図1中の製品の品番を全て答えよ

• 「レベル2に部品P9を使っている全ての製品の品番」は図1よりAXのみ

• AX

問3-設問3-(2)

その索引を構成する全ての列名を定義順に答えよ

• 「構成(親品番、子品番、構成数、、、)」
• 構成テーブルのユニーク索引は、{親品番、子品番}組み合わせ
• 「索引検索に決められるためには、WHERE句のANDだけで結ばれた一つ以上の等値比較の述語の対象列が、索引キーの全体又は先頭から連続した一つ以上の列に一致していなければならない。ON句の場合も同様である」
• SQL2のWHERE句は「WHERE L2.子品番='P9' AND LLC=0」なので、「WHERE句のANDだけで結ばれた一つ以上の等値比較の述語の対象列」は、「子品番」
• SQL2の「JOIN 構成 L1」のON句は「ON L2.親品番=L1.子品番」なので、「ON句の場合」の等値比較の述語の対象列も「子品番」
• ここでの「L2.親品番」と、「JOIN 品目 ON L1.親品番=品番」の「L1.親品番」は参照元の列なので、索引探索の対象外
• 品目テーブル
• 子品番を索引キーの先頭に置けば「先頭から連続した一つ以上の列に一致」を満たし、索引検索に決められるようにできる

• 子品番、親品番

問3-設問4-(1)

指示1に対して、なぜ部品の品目区分を調べれば、SQL3の発行回数を減らすことができるのか、その理由を30字以内で述べよ

• 単体部品は「単独で使われる」ので、「SQL3を用いて、当該部品の一つ下のレベルの部品の品番、構成数及び品目区分」が存在しないため、SQL3を発行しなくてもよくなる

• 品目区分が単体部品の場合は、手順①と手順③が不要だから

問3-設問4-(2)

指示2に対して、プログラムが起こす不具合とは、処理がどのようになることか、その理由を30字以内で述べよ

• 手順③「前の手順(手順②又は⑤)で在庫を引き当てた全ての部品について、部品ごとに部品の品番を設定したSQL3を用いて、当該部品の一つ下のレベルの部品の品番、構成数及び品目区分を調べる」
• 手順④「一つでも部品が存在すれば手順⑤に進み」、手順⑤で「手順③に戻る」とあるので、一つ下の部品がなくなるまで処理が終わらない
• P1とP2は互いに他方の部品を下のレベルにもつ

• 手順③④⑤の間で無限ループする

問3-設問4-(3)

図3に倣って、他にデッドロックが起きるケースをケース2として、図4を完成させよ

• 「ISOLATIONレベルはREAD COMMITED」なので、手順①③のSQL3では共有ロックが参照後に解放されるが、手順②⑤のSQL4では占有ロックがトランザクション終了まで解放されない
  -製品AX,AZに対しての占有ロックの順序は以下の通り

• ケース1のデッドロックまで

• ケース2のデッドロックまで

• AX1層目のP4
• AX1層目のP9
• AZ1層目のP9
• AZ2層目のP4

問3-設問4-(4)

この説明に加えて、複数回のSQL4をどのように実行するべきか。20字以内で述べよ

• 「製品ごとに分割して並行処理している」ので、製品によって複数の部品に共有ロックをかける順番が逆になると(3)のようなデッドロックが発生する

• 部品の品番順に実行する