フェリー会社の乗船予約システムのデータベース設計に関する次の記述を読んで，設問に答えよ。
X社は，複数航路でフェリーを運航している。乗船予約システムを構築してから時間が経過していることから，改めて現行業務を分析し，更に新規要件を洗い出し，乗船予約システムを再構築することになった。予約，乗船手続，下船手続などに関する業務について，概念データモデルとテーブル構造を設計した。
1.フェリーの概要
（1）フェリー
①12隻のフェリーを運航している。フェリーはフェリー番号で識別している。
②一つの設計図で複数のフェリーを造っている。同じ設計図から造られたフェリーを同じ船型のフェリーと呼ぶ。同じ船型であれば，外見だけでなく，船内施設，宿泊区画などの構成も同じとなる。船型は船型番号で識別している。
③現在，三つの船型があり，船型ごとに4隻のフェリーがある。
④フェリーが乗船客のほかに積載できるのは，次のとおりである。
・乗用車・トラック・バス（以下，車両という）
・ペット持込み用のペットケージ
・自転車・原動機付自転車・自動二輪車（以下，二輪車という）
⑤どれだけの車両を積載できるかは，車両の長さが基準となる。船型ごとに積載可能車両長合計を決めており積載する車両の車両長の合計が，積載可能車両長合計を超えない範囲であれば積載可能となる。
⑥ペットケージ，二輪車については，船型ごとに積載可能数をそれぞれ決め6ている
（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）で答えたテーブルの主キーの列名及び列値，並びに変更する列名及び変更内容を答えて，次の表を完成させよ。
なお，表は全て埋まるとは限らない。
| 主キー | 列名 | ||||
| 列値 | |||||
| 変更する列名 | |||||
| 変更内容 | |||||
解答・解説
解答例
| 主キー | 列名 | 航路番号 | 出発年月日 | 港コード | 等級コード |
| 列値 | 01 | 2022-03-14 | 003 | DX | |
| 01 | 2022-03-14 | 004 | DX | ||
| 01 | 2022-03-14 | 005 | DX | ||
| 変更する列名 | 利用可能個室残数 | ||||
| 変更内容 | 1減算する。 | ||||
解説
ー
（2）車両ペットケージ・二輪車有りの予約の場合に挿入行に対して必要となる制約条件を表4にまとめた。表4中の a ， b に入れる適切な字句を答えよ。
表4 予約時の制約条件（未完成）
解答・解説
解答例
a：積載可能車両残長
b：車両全長以上
解説
ー
（3）顧客都合で往復予約をキャンセルした場合，往路だけにキャンセル料が発生する場合がある。そのときの条件を50字以内で具体的に答えよ。
解答・解説
解答例
・キャンセル日が往路乗船年月日の 6 日前以降，かつ，復路乗船年月日の 6 日前よりも前の場合
・キャンセル日が往路乗船年月日の 6 日前以降，かつ，復路乗船年月日の 7 日前以前の場合
・キャンセル日が往路乗船年月日の 6 日前以降，かつ，復路乗船年月日の 6 日前以降でない場合
解説
ー
設問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中の一つのテーブルに列を追加する。該当するテーブル名及び追加する列名を答えよ。
解答・解説
解答例
まとめ精算：（航路番号，出発年月日，まとめ精算番号，精算合計金額）
テーブル名：乗船客
列名：まとめ精算番号
解説
ー
IPA公開情報
出題趣旨
データベースを構築してから時間が経過すると，開発時の業務要件に加えて，新規の業務要件が発生する。本問では，フェリー会社の乗船予約システムの再構築におけるデータベース設計を題材として，現行の業務要件に基づく概念データモデルを読み取る能力，現行業務での更新対象となるテーブル及び制約条件を見抜く能力，新規業務要件を基に概念データモデルとテーブル構造を見直す能力を問う。
採点講評
問 2 では，フェリー会社の乗船予約システムの再構築を題材に，現行業務の概念データモデルとテーブル構造，更新対象となるテーブル及び制約条件，新規要件を反映した概念データモデルとテーブル構造について出題した。全体として正答率は平均的であった。
設問 1 では，(1)の航路明細とア（販売区間）とのリレーションシップ，(2)の 2 本のリレーションシップの正答率が低かった。同一のエンティティタイプ間に異なる役割をもつ複数のリレーションシップが存在するかどうか，スーパータイプとサブタイプが存在する場合にスーパータイプとサブタイプのいずれとの間にリレーションシップが存在するかを注意深く読み取ってほしい。
設問 2 では，(1)(b)の正答率が低かった。変更対象となる行を 4 行とした解答が散見された。等級別在庫テーブルには到着港の行が存在しないことを現行業務から注意深く読み取ってほしい。 設問 3 では，(4)，(5)の正答率が低かった。新規要件を注意深く読み取り，変更・追加するテーブル構造を 見極めてほしい。例えば(5)は，既存テーブルの行を集計したテーブルを追加し，既存テーブルに外部キーを追加する方法を求めたものである。これは，実務でもよくあることであり，是非知っておいてもらいたい。
