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

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

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

問1-設問1-(1)

表3中のa～fに入れる“○”、“×”を答えよ

a

• 図1より、施設とプランのリレーションシップは1対多であって1対1ではない
• 表2より、全施設共通プランに関する制約は定義されていない

×

b

• 図2の会員テーブルを参照することで、会員番号をキーに氏名や住所を取得可能

○

c

• 図1より、予約テーブルの主キーは施設コードと予約番号だけなので、プラン、客室タイプ、利用年月日の重複は不問
• 表2にも、予約の重複に関する制約は定義されていない

×

d

• 図1より、宿泊と予約のリレーションシップにおいて予約側が●なので、宿泊に対して必ず予約が存在することになる

○

e

• 図2より、客室番号を主キー以外にもつテーブル(主キーによって客室番号が一意に決まる)は宿泊テーブルのみ
• 予約テーブルには、宿泊テーブルの主キー{施設コード&宿泊番号}も客室番号自体も含まれていない

×

f

• 図1より、予約から見てプラン明細→プランとリレーションシップを辿るとプランは必ず１つ以上存在することになる
• 図1より、予約から見てプラン明細→施設客室タイプ→客室タイプとリレーションシップを辿ると客室タイプは必ず１つ以上存在することになる
• 図2より、予約テーブルに客室タイプコードと客室数を含むので、同じ客室タイプを複数室分登録できる

○

表3中のア、イに入れる一つ又は複数の適切なエンティティタイプ名を答えよ

ア

• 図1より、予約から見てプラン明細は●、つまりゼロを含まないリレーションシップであることが判定の決め手になる

予約、プラン明細

イ

• 宿泊時には宿泊テーブルを更新、宿泊者に関する情報は宿泊者テーブル
• 図1より、宿泊から見て宿泊者は●

宿泊、宿泊者

問1-設問1-(2)

*表4中のウ～キに入れる適切な字句を答えよ

ウ

• 会員数は、重複を除いた会員番号の数をCOUNTする
• 重複を除いた会員番号は、DISTINCT 会員番号で取得できる

COUNT(DISTINCT B.会員番号)

エ

• 表4その宿泊よりも前の宿泊が「ある」会員
• 空欄以降の副問合せの中では、その宿泊よりも前の宿泊に該当するレコードを取得している
• レコードの有無はEXISTS関数で取得

EXISTS

オ、カ

• 「その宿泊よりも前の宿泊」と「同じ会員」の条件が必要

• A.チェックイン年月日 > C.チェックイン年月日
• B.会員番号 = D.会員番号

キ

• リピート率は　リピート会員数÷累計新規会員数
• リピート会員に該当がない場合、リピート会員数はNULL値になる
• NULL値の置換にはcoalesce関数

COALESCE(リピート会員数,0)/累計新規会員数

問1-設問1-(3)

表8中の太枠内に適切な数値を入れ、表を完成させよ

• 表5より、T2は「T1から会員を5等分に分類して会員ごとに階級番号を求める」
• 表7より、会員数は10なので、1～5の階級番号が2会員ずつ割り振られる
• SQL文「NTILE(5) OVER (ORDER BY 合計利用金額 DESC)」より、合計金額の多い順に並べたうえで1～5の階級番号を2個ずつ割り振られる
• 表7を合計利用金額の多い順に並べると、100>103>101>109>104>108>107>105>102=106

101: 2
102: 5
103: 1
104: 3
105: 4
106: 5
107: 4
108: 3
109: 2

問1-設問2-(1)

本文中の下線①で、調査のために表6中のS3をどのように変更したらよいか。変更内容を50字以内で具体的に答えよ

• 下線①「異常値を示している施設コード、価格区分コードの組だけを求める」
• 異常値とは、客室稼働率が100%を越える場合を指す

客室稼働率が100%を越える条件をWHERE句に追加する

問1-設問2-(2)

本文中の下線②で、累計予約可能客室数が実際よりも小さくなった理由を50字以内で具体的に答えよ

• 表9より、累計稼働客室数には客室タイプ71,72,73,74のものが含まれる
• 表10より、累計予約可能数には客室タイプ71のものだけが含まれる
• 表12より、施設103/客室番号1050には客室タイプ71のものしか含まれていない

累計予約可能客室数には、客室タイプ71以外のものがカウントされないから

問1-設問2-(3)

本文中の下線③で、表6中のS2において、“客室状況”テーブルに替えてほかのテーブルから累計予約可能客室数を求めることにした。そのテーブル名を答えよ

• 図2より、客室は施設コードと客室番号の組み合わせに対して、客室タイプコードは一つしか設定できない
• 客室在庫から、施設コードと客室タイプコードに対する予約可能数を確認できる

客室在庫

問1-設問2-(4)

本文中の下線④について、(a)どのようなデータの不整合が発生するか

• 図2より、定員は客室ごとではなく客室タイプごとの設定
• 「一つの客室(貸会議室)には時間帯に区切った複数の客室タイプがあり」とあるので、一つの客室に対して複数の定員数を設定できる

同じ客室の異なる客室タイプに異なる定員が設定される

本文中の下線④について、(b)どのような無駄な作業が発生するか

• 図1より、宿泊に対して一人以上の宿泊者が必要
• 「貸会議室利用を宿泊として登録する」とあるので、貸会議室利用時にも宿泊者を設定する

貸会議室の利用時に、宿泊者がいないのにもかかわらず宿泊者の記録が必要

問1-設問3-(1)

図5中のク、ケに入れる一つ又は複数の列名を答えよ。なお、ク、ケに入れる列が主キーを構成する場合、主キーを表す実線の下線を付けること

ク

• 図2の客室在庫を会議室予約に合うように修正する
• 「貸会議室は～1日を幾つかの連続する時間帯に区切って貸し出している」とあるので、時間帯コードを主キーへ新たに追加して、貸出時間帯とのリレーションシップを設定する必要がある
• 「貸会議室は全て禁煙である」とあるので、禁煙喫煙区分は不要

施設コード(PK)、客室タイプコード(PK)、年月日(PK)、予約可能数、割当済数、時間帯コード(PK)

ケ

• 「一つの貸会議室の複数時間帯の予約を受けることもある～1回で登録できるようにしてほしい」とあるので、各時間帯に別れた客室予約の明細とそれらをまとめる予約との間に多:1のリレーションシップを作る
• 図4において、前者を「貸会議室予約明細」、後者を「貸会議室予約」に当てはめることができる
• 貸会議室予約明細を貸会議室予約と多:1にするため、貸会議室予約の主キーに時間帯コードを加えた複合キーを設定する

施設コード(PK)、予約番号(PK)、時間帯コード(PK)

問1-設問3-(2)

表15中の更新可能ビュー方式の手順の実施に際して、APのアクセスを停止する必要がある。APのアクセスを停止するのはどの手順の前か。表15中の手順番号を答えよ。また、APのアクセスを停止する理由を40字以内で具体的に答えよ

• 表15[a1]の後は「更新可能な“施設利用”ビュー」を通して、「宿泊」テーブルにアクセスしている
• [c1]で“施設利用”ビューを削除～[c2]“宿泊”テーブルを“施設利用”テーブルに名称を変更するまでの間は、“施設利用”へのアクセス手段が存在しない
• 移行期間中に貸会議室予約機能がリリースされたAPでは、“宿泊”へのアクセスが“施設利用”へのアクセスに切り替わる

• 手順番号 : c1
• 理由 : 施設利用へのアクセスパスが一時的に存在しなくなりアクセス異常が発生するから

問1-設問3-(3)

表15中のトリガー同期方式において、APのアクセスを停止せずにリリースを行う場合、表15中の手順では“宿泊”テーブルと“施設利用”テーブルとが同期した状態となるが、手順[b2][b3]の順序を逆転させると、差異が発生する場合がある。それはどのような場合か。50字以内で具体的に答えよ

• 「一定件数ごとにコミットしながら複写」と「宿泊テーブルの変更」は、同タイミングになる場合がある
• トリガーを作成する前に「宿泊テーブルの変更」が発生してしまうと、施設利用に反映されない

• トリガーを作成する前に、施設利用テーブルに複写済みの宿泊テーブルの行に変更が発生した場合

問1-設問3-(4)

表16中のコの条件がないと問題が発生する。どのような問題が発生するか。20字以内で具体的に答えよ。また、この問題を回避するためにコに入れる適切な条件を30字以内で具体的に答えよ

• 「キー値で施設利用テーブルを検索し該当行がある場合」

• 問題 : 無限ループが発生する
• コ : 宿泊テーブルと施設利用テーブルの同一行の値が異なる場合

問2-設問1-(1)

アに入れる適切なエンティティタイプ名を答えよ。また、欠損しているリレーションシップを補い、図を完成させよ

• 図1、2には「販売区間は販売区間名をもち、航路番号、乗船港コード、下船港コードで識別する」が記載されていない
• 「航路内の販売可能な乗船港と下船港との組合せを販売区間と呼ぶ」が図1/2に無い

• ア: 販売区間

• 航路明細(航路番号, 港コード, ・・・)、航路明細の主キーは航路番号と港コード
• 販売区間は、乗船港の航路明細と下船港の航路明細との組合せと言い換えられる

• 「販売区間ごとに乗船客、車両、ペットゲージ、二輪車の運賃表がある」
• 販売区間(航路番号, 乗船港コード, 下船港コード, ・・・)
• 運賃(航路番号, 乗船港コード, 下船港コード, 運賃種類コード, 運用開始日 ・・・)

• 航路明細(航路番号, 港コード, ・・・)
• 運賃スケジュール(航路番号, 出発年月日, ・・・)
• 運賃スケジュール明細(航路番号, 出発年月日, 港コード ・・・)

• 運賃スケジュール明細(航路番号, 出発年月日, 港コード, 等級コード 　・・・)
• 等級別在庫(航路番号, 出発年月日, 港コード ・・・)

問2-設問1-(2)

イ～オに入れる適切なサブタイプエンティティタイプ名を答えよ。また、欠損しているリレーションシップを2本補い、図を完成させよ

• 「顧客カードに記載された顧客番号を伝えることで、予約時及び乗船時に氏名、性別などを記入する必要がなくなる」
• 顧客番号を持ち、顧客登録済みの予約客/乗船客とそうでない予約客/乗船客がいる
• ウとエには顧客とのリレーションシップがある

• イ: 顧客登録無予約客
• ウ: 顧客登録済予約客
• エ: 顧客登録済乗船客
• オ: 顧客登録無乗船客

• 乗船(航路番号, 出発年月日, 乗船番号,・・・ ,予約番号,　・・・)
• 予約(予約番号, ・・・)
• 予約番号をキーにした予約テーブルとのリレーションシップが不足
• 乗船のサブタイプのうち、予約無乗船は予約番号を持たない

• 乗船客(航路番号, 出発年月日, 乗船客番号,・・・ ,予約番号, 予約客番号 ・・・)
• 予約客(予約番号, 予約客番号, ・・・)
• 予約番号をキーにした予約客テーブルとのリレーションシップが不足
• 乗船客のサブタイプのうち、予約無乗船客は予約番号と予約客番号の組を持たない

問2-設問1-(3)

図2中のカに入れる一つ又は複数の適切な列名を答えよ

• 「販売区間は販売区間名をもち、航路番号、乗船港コード、下船港コードで識別する」

• カ: 航路番号, 乗船港コード, 下船港コード, 販売区間名

問2-設問2-(1)

(a)

車両・ペットケージ・二輪車を伴わない場合について、そのテーブル名及び更新する可能性のある列名を、図2中から選び、全て答えよ

• 「予約時又は予約無しでの乗船時に、個室であれば利用可能個室残数を、大部屋であれば利用可能ベッド残数を更新する」
• 予約有りの乗船時にはこれらの数の更新は発生しないことになる

• テーブル名: 等級別在庫
• 列名: 利用可能個室残数、利用可能ベッド残数

(b)

(a)で答えたテーブルの主キーの列名及び列値、並びに変更する列名及び変更内容を答えて、次の表を完成させよ

• 等級別在庫(航路番号, 出発年月日, 港コード, 等級コード, 利用可能個室残数、利用可能ベッド残数 )
• 「出発港、経由港を出発する時点での等級ごとの残数を記録する」とあるので、港コードと港名には出発港、経由港が入る
• 表1/3より、C港発F港行の出発港、経由港である、C/D/E港の港コードは、003/004/005
• 「大部屋に設置されたベッドと個室とがある」
• 「個室の等級にはロイヤルスイート、スイート、デラックス、レディースデラックスなどがあり、大部屋の等級にはステート、ツーリストなどがある」
• 「大部屋であれば利用可能ベッド残数を更新する」
• 等級はデラックスなので、大部屋ではなく、利用可能ベッド残数は更新されず、利用可能個室残数が更新される
• 1名の予約であれば、個室は1部屋必要

|主キー列名|航路番号|出発年月日|港コード|等級コード|
|主キー列値|01|2022-03-14|003|DX|
|主キー列値|01|2022-03-14|004|DX|
|主キー列値|01|2022-03-14|005|DX|
|主キー列値|||||
|変更する列名|利用可能個室残数|
|変更内容|1以上の場合元の値から1を引く|

問2-設問2-(2)

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

• 「積載する車両の車両長の合計が、積載可能車両長合計を超えない範囲であれば積載可能」
• 「車両については積載可能車両残長を、～記録する」

• a: 積載可能車両残長
• b: 車両全長以上

問2-設問2-(3)

顧客都合で往復予約をキャンセルした場合、往路だけにキャンセル料が発生する場合がある。そのときの条件を50字以内で具体的に答えよ

• 「乗船年月日の6日前以降のキャンセルは、キャンセル料が発生する」
• 「往復で予約している場合、往路復路それぞれの乗船年月日に対してキャンセル料を算出する」

復路の乗船年月日の7日以上前、往路の乗船年月日の6日前以降にキャンセルする場合

問2-設問3-(1)

該当するテーブル名及び追加する列名をそれぞれ答えよ

• 個室予約時には予約テーブルを更新、

• テーブル名: 予約
• 列名: 個室宿泊区画番号
• テーブル名: 予約客
• 列名: 大部屋宿泊区画番号

問2-設問3-(2)

該当するテーブル名及び追加する列名をそれぞれ答えよ

• 「予約登録は、航路と販売区間を指定した上で次のように行う」
• 「宿泊区画:個室の場合は1部屋、大部屋の場合は同じ等級の6人まで」
• 「予約客には複数の予約の中から代表の予約番号だけを提示して代表の予約番号以外を意識させない」とあるので、予約に対して代表の予約番号がわかるようにする

• テーブル名: 予約
• 列名: 代表予約番号

問2-設問3-(3)

(a)

追加する列の役割を25字以内で答えよ

• 「キャンセル待ちは、通常の予約と同様」
• 「キャンセルが発生した場合、キャンセル待ちを仮予約に変更し、予約希望者に確認の上、本予約に変更する」
• キャンセル待ち、仮予約、本予約を識別する列が必要

• キャンセル待ち、仮予約、本予約を識別する

(b)

トリガーを定義する図2中のテーブル名を答えよ。また、トリガーの実行契機を答えよ

• 図2「予約キャンセル(予約番号, キャンセル年月日、キャンセル期間区分、キャンセル料)」
• 「予約キャンセルは、出港時刻までであれば、予約番号ごとに可能である」
• 1件のキャンセルに対して予約キャンセルテーブルに1件のレコードが追加される

• テーブル名: 予約キャンセル
• 実行契機: 新しい行の追加

問2-設問3-(4)

該当するテーブル名及び追加する列名をそれぞれ答えよ

• 「乗船手続時に個室・大部屋の施錠ができるカードキーを手渡す。カードキーは、乗船客ごとに作成する」
• 「船内精算の際にカードキーを提示すると、その場で都度支払うのではなく、下船時に乗船客ごとに一括して支払うことができるようにする」とあるので、船内売上に乗船客の主キーを追加する

• テーブル名: 船内売上
• 列名: 航路番号、出発年月日、乗船客番号

• 「下船時にフロントで精算する。下船時の一括支払方法としては、クレジットカード決済、現金払がある。下船時精算額を記録する」
• カードキーごと、つまり乗船客ごとに一括支払方法と下船時精算額を記録する

• テーブル名: 乗船客
• 列名: 一括支払方法、下船時精算額

問2-設問3-(5)

“まとめ精算”テーブルの構造を答えよ

• 「乗船客の家族が持つ複数のカードキーをまとめて精算」とあるので、乗船客の主キーから乗船客番号を除いたものが主キーになる
• まとめ精算は家族内で複数に別れる可能性があるのでまとめ精算のとおし番後も主キーに複合させる
• 「精算合計金額を記録する」

• 航路番号、 出発年月日、　まとめ精算番号、　精算合計金額

該当するテーブル名及び追加する列名を答えよ

• 乗船客がどのまとめ精算につながるかを記録する

• テーブル名: 乗船客
• 列名: まとめ精算番号