令和4年度秋期 データベーススペシャリスト 午後Ⅱ 問2

問2　フェリー会社の乗船予約システムのデータベース設計に関する次の記述を読んで、設問に答えよ。 X社は、複数航路でフェリーを運航している。乗船予約システムを構築してから時間が経過していることから、改めて現行業務を分析し、更に新規要件を洗い出し、乗船予約システムを再構築することになった。予約、乗船手続、下船手続などに関する業務について、概念データモデルとテーブル構造を設計した。 〔現行業務の分析結果〕 1. フェリーの概要 (1) フェリー ① 12隻のフェリーを運航している。フェリーはフェリー番号で識別している。 ② 一つの設計図で複数のフェリーを造っている。同じ設計図から造られたフェリーを同じ船型のフェリーと呼ぶ。同じ船型であれば、外見だけでなく、船内施設、宿泊区画などの構成も同じとなる。船型は船型番号で識別している。 ③ 現在、三つの船型があり、船型ごとに4隻のフェリーがある。 ④ フェリーが乗船客のほかに積載できるのは、次のとおりである。 ・乗用車・トラック・バス（以下、車両という） ・ペット持込み用のペットケージ ・自転車・原動機付自転車・自動二輪車（以下、二輪車という） ⑤ どれだけの車両を積載できるかは、車両の長さが基準となる。船型ごとに積載可能車両長合計を決めており、積載する車両の車両長の合計が、積載可能車両長合計を超えない範囲であれば積載可能となる。 ⑥ ペットケージ、二輪車については、船型ごとに積載可能数をそれぞれ決めている。 (2) 宿泊区画 ① 宿泊区画とは、乗客の宿泊のための販売単位であり、大部屋に設置されたベッド（定員1名）と個室（定員1名から4名まで）とがある。 ② 宿泊区画には一意な宿泊区画番号を付与している。宿泊区画番号には、個室の場合は個室番号、ベッドの場合はベッド番号を用いる。 (3) 等級 ① 宿泊区画は等級に分かれており、等級を等級コードで識別している。個室の等級には、ロイヤルスイート、スイート、デラックス、レディースデラックスなどがあり、大部屋の等級には、ステート、ツーリストなどがある。 ② 船型ごとに存在する等級が異なる。例えば、ある船型にはロイヤルスイートが存在しないということがある。 ③ 一部の等級のエリアは、後述するカードキーをタッチしないとドアが開かず立ち入ることができない。 ④ 同じ等級でも船型ごとに定員、面積が異なる場合がある。 (4) 航路 ① 出発港から幾つかの経由港を経て到着港までを航路と呼ぶ。ただし、経由港のない航路もある。A港とB港との間を運航している場合、A港発B港行とB港発A港行とは別の航路となる。航路ごとに航路番号を決めている。 ② 航路ごとに配船する船型を決めている。 ③ 航路ごとに航路明細として出発港、経由港、到着港、標準入港時刻、標準出港時刻を決めている。港は港コードで識別している。航路明細の例を表1に示す。 表1　航路明細の例 (5) 運航スケジュール ① 航路について、出発年月日ごとに配船するフェリーを決める。ある航路において、同日に複数のフェリーが出発することはない。運航スケジュールの例を表2に示す。 表2　運航スケジュールの例 ② 出発港、経由港、到着港にいつ入出港するかの運航スケジュール明細を決める。同じ航路でも出発年月日によって出発港、経由港、到着港の入港日時及び出港日時が、標準入港時刻及び標準出港時刻と異なる場合がある。運航スケジュール明細の例を表3に示す。 表3　運航スケジュール明細の例 (6) 販売区間 航路内の販売可能な乗船港と下船港との組合せを販売区間と呼ぶ。宿泊を伴わない区間は販売区間とならない。C港とF港の間を運航し、D港、E港を経由する航路の場合、C港～E港、C港～F港、D港～E港、D港～F港、E港～F港が販売区間となり得る。販売区間は販売区間名をもち、航路番号、乗船港コード、下船港コードで識別する。 (7) 運賃 ① 販売区間ごとに乗船客、車両、ペットケージ、二輪車の運賃表がある。 ② 乗船客の運賃は、等級ごとに大人運賃を決めている。小人運賃は大人運賃の半額としている。中学生以上には大人運賃、小学生には小人運賃を適用する。小学生未満の乳幼児は、大人1名につき1名分が無料となり、2人目以降は小人運賃となる。 ③ 車両の運賃は、車両の長さの範囲（4m未満、4m以上5m未満など）ごとに決めている。 ④ ペットの運賃は、ペットケージ1個ごとに決めている。 ⑤ 二輪車の1台当たりの運賃は、自転車・原動機付自転車・自動二輪車の種類ごとに決めている。 ⑥ 等級に、車両の長さの範囲、ペットケージ、二輪車の種類を併せて運賃種類と呼び、運賃種類コードで識別している。運賃種類コードのうち、乗船客の運賃を表すものは等級コードである。 ⑦ 乗船客、車両及び二輪車については、通常期運賃とは別に繁忙期運賃を設定している。 ⑧ 復路の乗船年月日が往路の乗船年月日から30日以内であれば往復割引を適用し、復路は10％割引としている。 ⑨ 運賃表は燃油の価格変動に伴い、数か月ごとに見直す。運賃表の運用開始日を決めている。 (8) 船内施設 フェリーの船内には、レストラン、ショップなどの施設があり、フェリーごとの施設コードで識別している。 (9) 船内商品 船内施設で提供する商品として、レストランでの飲食メニュー、ショップでのお土産品・雑貨品などがある。これらは、全フェリー共通の商品コードで識別している。 2. 予約業務 (1) 予約登録 ① 予約は、予約受付順の予約番号で一意に識別している。予約登録は、航路と販売区間を指定した上で次のように行う。 ・宿泊区画：個室の場合は1部屋、大部屋の場合は同じ等級の6人まで ・車両：1台まで ・ペットケージ、二輪車：数に制限無し ② 往復予約の場合は、往路と復路とは別の予約番号を振り、復路の予約に往路予約番号をもたせる。 ③ 航路、乗船港、下船港、乗船年月日、等級、大人人数、小人人数、乳幼児人数、車両の有無（有りの場合は車両の長さ）、ペットケージ、二輪車の数を登録する。併せて乗船予定者全員の氏名、性別、生年月日、住所、大人運賃・小人運賃・無料のいずれかを表す適用運賃区分を予約客として登録する。 ④ 予約内容の変更は受け付けていない。 (2) 予約キャンセル ① 予約キャンセルは、出港時刻までであれば、予約番号ごとに可能である。ただし、乗船年月日の6日前以降のキャンセルは、キャンセル料が発生する。乗船年月日までの日数によってキャンセル期間区分を決め、キャンセル料率を変えている。出港後はキャンセルできず、全額請求する。往復で予約している場合、往路復路それぞれの乗船年月日に対してキャンセル料を算出する。 ② 天候不良などによる欠航の場合、翌日以降の運航に振り替えるか、キャンセルするかを、予約客に選択してもらう。キャンセルの場合、キャンセル料は請求せず、全額を返金する。 ③ 往復予約の往路が天候不良などで欠航になったときの復路をキャンセルする場合、キャンセル料は請求せず、全額を返金する。 (3) 在庫の把握 ① 個室については利用可能個室残数を、大部屋については利用可能ベッド残数を等級別在庫としてそれぞれ記録している。出発港、経由港を出港する時点での等級ごとの残数を記録する。予約受付時又は予約無しでの乗船時に、個室であれば利用可能個室残数を、大部屋であれば利用可能ベッド残数を更新（利用分を減算）する。 ② 車両については積載可能車両残長を、ペットケージについては積載可能ペットケージ残数を、二輪車については積載可能二輪車残数をそれぞれ記録する。出発港、経由港を出港する時点の残長・残数を記録する。予約受付時又は予約無しでの乗船時に、車両であれば積載可能車両残長を、ペットケージであれば積載可能ペットケージ残数を、二輪車であれば積載可能二輪車残数を更新（積載分を減算）する。 ③ 上述の残長・残数について、乗船港と下船港との間に経由港がある場合には乗船港を出港する時点の残長・残数だけではなく、経由港を出港する時点の残長・残数も更新する。C港を出港し、D港、E港を経由し、F港に到着する航路で、C港で乗船し、F港で下船する場合、C港を出港する時点の残長・残数だけではなく、D港、E港を出港する時点の残長・残数も更新する。 ④ 予約キャンセル時には、予約登録時と逆に更新（キャンセル分を加算）する。 3. 入金業務 (1) 運賃の支払には、乗船前の支払と乗船当日の乗船窓口での支払とがある。 (2) 乗船前の支払方法には、クレジットカード決済と現金振込みとがある。支払が完了すると乗船前支払フラグを「支払済」にする。 (3) 乗船当日の乗船窓口での支払方法には、クレジットカード決済と現金払とがある。 4. 顧客管理業務 (1) リピーターを確保する目的で顧客管理を行っており、希望する顧客には、氏名、性別、生年月日、住所、電話番号、メールアドレスを登録してもらい、顧客番号が記載された顧客カードを渡す。 (2) 顧客は、顧客カードに記載された顧客番号を伝えることで、予約時及び乗船時に氏名、性別などを記入する必要がなくなる。 5. 乗船手続（チェックイン） (1) 乗船当日に乗船窓口において、予約有りの場合は予約の単位に、予約無しの場合は乗船する個人又はグループ単位に乗船客の乗船手続をする。乗船手続では運航スケジュールごとの乗船番号を発番する。 (2) 予約有りの乗船手続 ① 乗船窓口の担当者が予約を確認する。予約が確認できたら、予約の記録を乗船の記録に引き継ぐ。 ② 予約時に申請した予約客に変更がある場合には、変更後の内容を乗船客として記録する。 ③ 運賃が未払の場合は、運賃を請求する。 (3) 予約無しの乗船手続 ① 乗船窓口の担当者が、乗船客の航路、乗船港、下船港、乗船年月日、等級、大人人数、小人人数、乳幼児人数、車両の有無、ペットケージ、二輪車の数を確認する。これらは予約の記録ではなく、乗船の記録とする。 ② 次に乗船客を確認する。氏名、性別、生年月日、住所、適用運賃区分を乗船客の記録とする。顧客登録している場合、顧客番号を提示してもらうことで、氏名、性別などを確認する必要がなくなる。 ③ 運賃を請求する。 (4) 予約の有無にかかわらず、乗船手続時に乗船窓口の担当者が個室又はベッドを決定する。宿泊区画状態区分が乗船の全区間を通して「チェックイン可」の個室又はベッドを割り当てる。 (5) 乗船手続時に個室・大部屋の解錠ができるカードキーを手渡す。カードキーは乗船客ごとに作成する。カードキーには、航路番号、出発年月日、乗船客番号、宿泊区画番号を登録する。 (6) 乗船手続終了後、乗船ステータス及び宿泊区画状態区分を「チェックイン」に変更する。 6. 船内売上 (1) 乗船中に乗船客が船内商品を購入する場合の支払方法には、クレジットカード決済、現金払がある。 (2) 乗船客が購入した船内商品及び個数・金額を船内売上明細に、購入ごとの合計金額を船内売上に記録する。 7. 下船手続（チェックアウト） 下船口で乗船客から受領したカードキーを読み取り、乗船ステータス及び宿泊区画状態区分を「チェックアウト」にする。 〔概念データモデルとテーブル構造〕 現行業務の分析結果に基づいて、概念データモデルとテーブル構造を設計した。テーブル構造は、概念データモデルでサブタイプとしたエンティティタイプを、スーパータイプのエンティティタイプにまとめた。現行業務の概念データモデルを図1に、現行業務のテーブル構造を図2に示す。 図1　現行業務の概念データモデル（未完成） 図2　現行業務のテーブル構造（未完成） 〔新規要件〕 1. 予約業務 (1) 航路の出発港から到着港まで全て乗船する予約に限り、個室又はベッドを指定できるようにする。乗船手続（チェックイン）までならば、指定した個室又はベッドを変更することもできるようにする。 (2) 1回の予約で複数個室又は大部屋に7人以上の予約ができるようにする。 (3) キャンセル待ちをできるようにする。キャンセル待ちは、通常の予約と同様に航路、乗船年月日、乗船港、下船港、等級、人数などを指定する。出港までにキャンセルが発生した場合、キャンセル待ちを仮予約に変更し、予約希望者に確認の上、本予約に変更する。複数のキャンセル待ちがある場合は、キャンセル待ちの予約番号順かつ条件に合致するものを優先する。 2. 下船手続（下船時精算） (1) レストラン及びショップでの船内精算の際にカードキーを提示すると、その場で都度支払うのではなく、下船時に乗船客ごとに一括して支払うことができるようにする。この場合、下船時にフロントで精算する。下船時の一括支払方法としては、クレジットカード決済、現金払がある。下船時精算額を記録する。 (2) 下船時にフロントで精算する場合、乗船客の家族が持つ複数のカードキーをまとめて精算することができるようにする。精算合計金額を記録する。 解答に当たっては、巻頭の表記ルールに従うこと。ただし、エンティティタイプ間の対応関係にゼロを含むか否かの表記は必要ない。 なお、エンティティタイプ間のリレーションシップとして「多対多」のリレーションシップを用いないこと。エンティティタイプ名は、意味を識別できる適切な名称とすること。また、識別可能なサブタイプが存在する場合、他のエンティティタイプとのリレーションシップは、スーパータイプ又はサブタイプのいずれか適切な方との間に記述せよ。同一のエンティティタイプ間に異なる役割をもつ複数のリレーションシップが存在する場合、役割の数だけリレーションシップを表す線を記述せよ。また、テーブル構造は第3正規形の条件を満たしていること。列名は意味を識別できる適切な名称とすること。 設問1　現行業務の概念データモデルとテーブル構造について答えよ。 (1) 図1中のマスター領域は、エンティティタイプ及びリレーションシップが未完成である。【ア】に入れる適切なエンティティタイプ名を答えよ。また、欠落しているリレーションシップを補い、図を完成させよ。なお、マスター領域のエンティティタイプとトランザクション領域のエンティティタイプ間のリレーションシップは不要である。 (2) 図1中のトランザクション領域は、サブタイプ及びリレーションシップが未完成である。【イ】～【オ】に入れる適切なサブタイプ名を答えよ。また、欠落しているリレーションシップを2本補い、図を完成させよ。なお、マスター領域のエンティティタイプとトランザクション領域のエンティティタイプ間のリレーションシップは不要である。 (3) 図2中の【カ】に入れる一つ又は複数の適切な列名を答えよ。主キーを表す実線の下線、外部キーを表す破線の下線についても答えること。 設問2　現行業務の業務処理及び制約について答えよ。 (1) 出港時に乗船客が予約有りで乗船した場合には更新の必要がないが、予約無しで乗船した場合には行の更新が必要となるテーブルがある。 (a) 車両・ペットケージ・二輪車を伴わない場合について、そのテーブル名及び更新する可能性のある列名を、図2中から選び、全て答えよ。 (b) 表1～表3の例において、ある乗船客1名が出発年月日「2022-03-14」、航路番号「01」（C港発F港行）、販売区間「C港～F港」、等級コード「DX」（デラックスの等級コード）を乗船した場合、(a)で答えたテーブルの主キーの列名及び列値、並びに変更する列名及び変更内容を答えて、次の表を完成させよ。なお、表は全て埋まるとは限らない。 (2) 車両・ペットケージ・二輪車有りの予約の場合に挿入行に対して必要となる制約条件を表4にまとめた。表4中の【a】、【b】に入れる適切な字句を答えよ。 表4　予約時の制約条件（未完成） (3) 顧客都合で往復予約をキャンセルした場合、往路だけにキャンセル料が発生する場合がある。そのときの条件を50字以内で具体的に答えよ。 設問3　〔新規要件〕について答えよ。 (1) 「1. 予約業務」の(1)を実現する方法として、図2中の二つのテーブルに列を追加する案を考えた。該当するテーブル名及び追加する列名をそれぞれ答えよ。 (2) 「1. 予約業務」の(2)を実現する方法として、現行と同じ単位（個室であれば1部屋、大部屋であれば同じ等級の6人まで）に分けて複数の予約とし、予約客には複数の予約の中から代表の予約番号だけを提示して代表の予約番号以外を意識させないようにすることにした。このために、図2中の一つのテーブルに列を追加する案を考えた。該当するテーブル名及び追加する列名を答えよ。 (3) 「1. 予約業務」の(3)を実現する方法について答えよ。 (a) 図2中の「予約」テーブルに列を追加する案を考えた。追加する列の役割を25字以内で答えよ。 (b) 予約のキャンセルが発生した場合に、キャンセル待ちから仮予約への変更処理を起動するトリガーを定義する。トリガーを定義する図2中のテーブル名を答えよ。また、トリガーの実行契機を答えよ。 (4) 「2. 下船手続（下船時精算）」の(1)を実現する方法として、図2中の二つのテーブルに列を追加する案を考えた。該当するテーブル名及び追加する列名をそれぞれ答えよ。 (5) 「2.下船手続（下船時精算）」の(2)を実現する方法として、「まとめ精算」テーブルを追加する。「まとめ精算」テーブルは、運航スケジュールごとのまとめ精算番号で一意に識別することとする。「まとめ精算」テーブルの構造を答えよ。主キーを表す実線の下線、外部キーを表す破線の下線についても答えること。また、図2中の一つのテーブルに列を追加する。該当するテーブル名及び追加する列名を答えよ。