データベーススペシャリスト 令和2年 午後1 解説解答

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

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

問1-設問1-(1)

図1中のア、イに入れる適切なエンティティタイプ名を答えよ。また、必要なリレーションシップを全て記入し、概念データモデルを完成させよ

• 「店舗は、必要な自社製品とその発注数量を設定して発注する」
• 「一つの発注の中で同一の自社商品を複数回登録することができる」
• 「発注は、発注番号で識別し、発注明細は発注番号と発注明細番号で識別する」
• 発注と自社製品が多対多なので、それらを発注明細と1対多で接続する
• 「生産明細は、生産番号と商品コードで識別」
• 「生産の対象とした発注明細に対して、生産番号を記録する」
• 「配送明細は、配送番号と商品コードで識別」
• 「配送の対象とした発注明細に対して、配送番号を記録する」

• ア: 発注
• イ: 発注明細

問1-設問1-(2)

図2中のa～hに一つ又は複数の適切な属性名を入れ、図を完成させよ

a

• 「店舗は、一つの配送ルートに属し、そのルート番号をもつ。また、配送ルート上何番目に配送されるかを表す配送順序をもつ」

• a: ルート番号(FK)、配送順序

b

• 「自社製品ごとに生産ロットサイズを決めている」

• b: 生産ロットサイズ

c

• 「配送ルートは、ルート番号で識別し、ルート名称と一つの配送元の拠点コードをもつ」

• c: 生産工場拠点コード(FK)

d

• dはア:発注
• 「発注は、発注番号で識別」
• 「A社本部は、店舗からの発注について、店舗の拠点コード、発注登録日時を確認し、配送予定日時を記録する」

• d: 発注番号、店舗拠点コード(FK)、発注登録日時、配送予定日時

e

• eはイ:発注明細
• 「店舗は、必要な自社製品とその発注数量を設定して発注する」
• 「発注明細は発注番号と発注明細番号で識別する」
• 発注明細は、発注を自社製品ごとにわけたものなので、商品コードを外部キーとしてもつ
• 「A社本部は、店舗からの発注について、店舗の拠点コード、発注登録日時を確認し、配送予定日時を記録する」
• 「生産の対象とした発注明細に対して、生産番号を記録する」
• 「配送の対象とした発注明細に対して、配送番号を記録する」

• e: 発注番号、発注明細番号、発注数量、商品コード(FK)、生産番号(FK)、配送番号(FK)、

f

• 「生産は、生産番号で識別し、生産工場の拠点コード、生産完了予定日時を記録する」

• f: 生産工場拠点コード(FK)

g

• 「生産明細は、生産番号と商品コードで識別し、生産数量を記録する」

• g: 商品コード

h

• 「配送は、配送番号で識別し、配送完了予定日時と配送先の拠点コードを記録する」

• h: 店舗拠点コード(FK)

問1-設問2-(1)

図3中のウ～カに入れる適切なエンティティタイプ名を答えよ。また、必要なリレーションシップを全て記入し、概念データモデルを完成させよ

• 「個々の委託商品を生産する委託工場は、一つに決まっている」
• 委託工場と委託商品は1対多で接続
• 「自社工場と委託工場を併せて工場と呼ぶ」

• ウ: 委託工場
• エ: 自社工場
• 自社工場と委託工場は工場のサブタイプ

• 「既存の配送ルートを使って自社商品と委託商品を併せて店舗へ配送する」
• 既存の配送ルート

• 「委託工場の追加に伴って、生産工場を自社工場と呼ぶ」
• 「これまで自社工場内で仕分けと配送を行っていた役割に、工場の拠点コードとは別に物流センタとしての拠点コードを付与する」
• つまり、既存の配送ルートにおいて、生産工場は物流センタに差し替わる
• 「各自社工場と自社工場内の物流センタ、各委託工場と各物流センタの組を納入ルートと呼ぶ」
• 工場、物流センタと納入ルートは1対多
• 「配送ルートと納入ルートを併せてルートと呼ぶ」

• オ: 納入ルート
• カ: 配送ルート
• 配送ルートと納入ルートはルートのサブタイプ

• 「納入は、納入番号で識別し、納入するルート番号と納入予定日時を記録する」

• 「自社商品と委託商品を併せて自社仕様商品と呼ぶ」
• 「納入明細は、納入番号と商品コードで識別」
• 「納入の対象となる発注明細に対して、納入番号を記録する」

• 「自社商品と委託商品を併せて自社仕様商品と呼ぶ」
  -　「委託工場は、生産の指示に基づいて、生産を行い、自社工場と同様の記録を行う」
• 発注、生産、配送については、自社商品を自社仕様商品に、自社工場を工場に差し替える以外、委託商品追加前のリレーションシップをそのまま引き継ぐ

問1-設問2-(2)

図4中のi～kに一つ又は複数の適切な属性名を入れ、図を完成させよ

• 委託工場は拠点コードを主キーとする工場のサブタイプ
• 「委託工場は委託開始年月日をもつ」

• i: 拠点コード、委託開始年月日

• 納入ルートはルート番号を主キーとするルートのサブタイプ
• 「これまで自社工場内で仕分けと配送を行っていた役割に、工場の拠点コードとは別に物流センターとしての拠点コードを付与する」

• j: 工場拠点コード(FK)、物流拠点コード(FK)

• 配送ルートはルート番号を主キーとするルートのサブタイプ
• ルート(ルート番号、ルート名称)
• 追加前の配送ルート(ルート番号、ルート名称、拠点コード(FK))
• 「配送ルートの配送元を自社工場から物流センタに変更し、物流センタに対する配送の指示及び店舗への配送は、現状業務と同様に行う」

• k: 物流拠点コード(FK)

問1-設問2-(3)

カーディナリティの値(数値)と、どのような場合に発生するかを25字以内で具体的に答えよ

• 「A社には3か所の生産工場がある」
• 「これまで自社工場内で仕分けと配送を行っていた役割に、工場の拠点コードとは別に物流センタとしての拠点コードを付与する」
• 「各自社工場と自社工場内の物流センタ、各委託工場と各物流センタの組を納入ルートと呼ぶ」
• 各自社工場は各自社工場内の物流センタだけを利用
• 各委託工場は自社工場3か所にある物流センタを利用

• 1個: 自社工場から物流センタへの納入ルート
• 3個: 委託工場から物流センタへの納入ルート

問2-設問1

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

• 「空席情報は、○:空席あり、×:売切れ」
• CASE WHEN THEN ELSE構文を使って、空席数がNULLの場合に×を、そうでない場合に○を出力する

• a: CASE WHEN
• b: IS NULL

• A1は全ての{公演番号、公演日、開演時間、席種}が網羅されるが、A2は一部の{公演番号、公演日、開演時間、席種}が存在しない場合もあるため、LEFT JOINで結合する

• c: LEFT JOIN

• 「空席管理システムから、公演日が2020年4月かつ都道府県が東京のチケット情報一覧の出力の例」
• BETWEEN 開始日 AND 最終日

• d: BETWEEN
• e: AND

• 「チケット情報は、公演名・公演日・開演時刻の昇順、料金の降順に出力される」

• f: ORDER BY
• g: DESC

問2-設問2-(1)

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

• 「ロックは行単位で掛ける。共有ロックを掛けている間は、他のトランザクションからの対象行の参照は可能でり、変更は共有ロックの解放待ちとなる」
• 「全ての購入希望チケットの購入フラグをオンにする」は対象行の変更なので、同じ{公演番号、公演日、開演時刻、座席番号}の行(座席)に対して専有ロックがかかる
• ②のあとに、他のトランザクションによって③の対象行に専有ロックが掛けられ、トランザクションが正常終了するとロックは解除されるが、空席フラグがオフになり空席管理システムの処理において、「空席フラグがオンか確認する」でエラーになる

• あ: 同じ座席
• い: 購入不可である

• 「②全ての購入希望チケットの購入番号がNULLか確認する」によって空席確認する
• 「⑦全ての購入希望チケットの購入情報を登録し、購入番号を設定する」によって購入が確定する

• う: 購入番号
• え: NULL

• 掛けられたロックはコミットによって解除される

• お: コミット

問2-設問2-(2)

①か②で答えよ。また、その理由を30字以内で述べよ

• トランザクションxが行う「仮予約フラグがオフか確認する」と「仮予約フラグをオンにする」の間のタイミングで、同じ座席に対してトランザクションyの「仮予約フラグがオフか確認する」が行われようとするケースを考える
• ①READ COMMITEDの場合は、トランザクションxが仮予約フラグをオンにして決済処理に進んだ後にトランザクションyも仮予約フラグをオンにして決済処理に進むために、同じ座席に対して決済処理が重複してしまう可能性がある
• ②REPEATABLE READの場合、トランザクションx、yともに「仮予約フラグをオンにする」を実施するために必要な専有ロックを取得できずにデッドロックする。この場合、トランザクションxかyのどちらかの処理をキャンセルして共有ロックを解放すれば他方のみ「仮予約フラグをオンにする」を実施後、決済処理に移行できる。双方をキャンセルして、やり直す

• ②: 同時に発生した他方の処理に同座席の仮予約をさせないため

問2-設問3-(1)

図6中のア～ウに入れる適切な字句を、本文中の字句を用いて答えよ

• 「空席管理システムを介して、空席情報を表示するサービスも提供」
• 「座種在庫には、座種ごとの空席数をもつ」
• 「座席状況には、公演開催明細ごとの全ての座席の状況をもつ」
• 他に、空席情報を含むテーブルは無い

ア: 座種在庫
イ: 座席状況

• 「レプリケーション機能: 1か所のデータを複数か所に複製する機能、複数か所のデータを1か所に集約する機能、及び両者を組み合わせて双方向に反映する機能がある」
• 「サービスの提供先ごとにレプリカデータを用意する」

ウ: 1か所のデータを複数か所に複製

問2-設問3-(2)

表3中のエに入れる文章を、1.に倣って30字以内で述べよ

• 「1.購入された座席が空席として表示される」
• 「座席の購入が確定したら空席数を減らし、購入された座席がキャンセルされたら空席数を戻す」

キャンセルされた座席が空席として表示されない

問2-設問3-(3)

イベント型レプリケーション機能の対象とするテーブルとその例を答えて、表4を完成させよ

• 座種在庫(公演番号、公演日、開演時刻、座種、空席数)
• 座席状況(公演番号、公演日、開演時刻、座席番号、空席フラグ、仮予約フラグ)

レプリケーション元テーブル	レプリケーション対象列
座種在庫	空席数
座席状況	空席フラグ

問3-設問1-(1)

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

• 地域(地域コード、店舗名、、、)
• 会員地域コードは、地域テーブルの地域コードと紐付く

• a: 地域(地域コード)

• 社員(社員ID、社員名、店舗コード、、、)
• 担当社員IDは、社員テーブルの社員IDと紐付く

• b: 社員(社員ID)

問3-設問1-(2)

会員区分を追加することなく既存の会員番号をそのまま移行すれば、どのような場合にどのような不都合が起きると考えられるか、それぞれ25字以内で述べよ

• 「個人会員番号及び法人会員番号の付与は、互いに無関係でそれぞれ独自に行っている」
• 個人会員番号と法人会員番号は重複してしまう場合がある

• 場合: 個人会員番号と法人会員番号が同じ場合
• 不都合: 会員番号の主キー違反でエラーが発生

問3-設問1-(3)

表4に示したNOT NULL欄中のc～g、及び制約欄中のh～mに入れる適切な字句を答えよ

• 個人会員(個人会員コード、氏名、会員地域コード(FK)、性別、年代(FK)、、、)
• 法人会員(法人会員コード、社名、会員地域コード(FK)、担当社員ID(FK)、、、)
• 会員区分は主キーなので、NOT NULL
• 「会員には個人会員と法人会員があり、会員地域コードが設定される。法人会員には、担当する社員が登録後に1名決められる」
• 性別、年代は法人会員には含まれない

• c: Y
• d: Y
• e: N
• f: N
• g: N

• 年代に対する参照制約は表2のものを引き継ぐ

• h: RESTRICT

• 担当社員IDに対する参照制約は表3のものを引き継ぐ

• i: SET NULL

• 氏名、社名は、会員名に移行する
• 性別に対する検査制約は表2のものを引き継ぐ
• 個人会員には、担当社員IDは含まれない
• 個人会員「年代には、次のいずれかが設定される」
• 法人会員には、性別、年代は含まれない

• j: 性別 IN ('M','F')
• k: 担当社員ID IS NULL
• l: 年代 IS NOT NULL
• m: 年代 IS NULL

問3-設問2-(1)

図3中のあ～くに入れる適切な字句を、回答群の中から選び、記号で答えよ

• 「2020年3月の店舗コード別商品コード別売上高を調べる」SQLなので、(店舗コード、商品コード、販売額)を含む図2の売上テーブルを参照すれば事足りるはず
• SELECT句を見ると、Uは(商品コード、販売額、クーポン額)を含むので、売上テーブルが該当
• Mは店舗コードを含むので、店舗テーブルが該当
• 店舗 LEFT OUTER JOIN 売上として、売上ゼロの店舗コードが除外されないようにしようとしていることが読み取れる
• 年は2020、月は3
• 「店舗コードはM1、M2及びM3に、商品コードはP1及びP2に限定する」

• あ: ②
• い: ①
• う: ⑥
• え: ④

• ON句に指定した条件はFROMのテーブルには適用されないので、FROMのテーブルに指定したい条件はWHERE句に

• お: ⑧
• か: ⑨
• き: ⑩
• く: ⑫

問3-設問2-(2)

図3中の選択リスト中の販売額又はクーポン額がNULLになるのはどのような場合か、本文中の用語を用いて、それぞれ25字以内で述べよ

• 売上テーブルに記録されていない店舗が存在するとき
• 「販売額は商品の店頭価格。クーポン額は、会員が広告チラシ又はスマートフォンでクーポンを提示したときに適用した金額で、適用しなかった場合、NULLが設定される。」
• 売上のない店舗はJOINする相手が存在しないのでNULLが設定される

• 販売額: 売上記録が無い店舗の場合
• クーポン額: クーポンを適用しなかった場合

問3-設問3-(1)

表6中のイ～ハに入れる適切な字句を答えよ

イ

• 「2020年3月と2019年3月」で2パターン
• 「店舗地域コード別」で10パターン
• 「大分類コード別」で10パターン

2×10×10=200

• イ: 200

ロ

• 「2019年の月別」で12パターン
• 「店舗コード別」で300パターン
• 「大分類コード別」で10パターン

12×300×10=36000

• ロ: 36000

ハ

• 「店舗コード別」で300パターン
• 「性別」で2パターン
• 「年代別」で7パターン

300×2×7=4200

• ハ: 4200

問3-設問3-(2)

Aに入れる複数の列名を答えよ

• 表6の各分析名に対して、サマリーテーブルに必要な属性
  B2: 年、月、会員区分、会員地域コード、売上額
  B3: 年、月、店舗地域コード、大分類コード、売上額
  B4: 年、月、店舗コード、大分類コード、売上額
  B5: 年、月、店舗コード、性別、年代、売上額
  B6: 年、月、小分類コード、売上額
• 「S(年、月、店舗コード、会員区分、会員地域コード、A、売上額)」
• サマリーテーブルに必要な属性のうち、Sに足りないのは、店舗地域コード、大分類コード、性別、年代、小分類コード
• 店舗地域コードは、店舗コードによって決定する
• 大分類コードは、小分類コードによって決定する

• 小分類コード、性別、年代