平成26年度春期 データベーススペシャリスト 午後Ⅱ 問2

問2　ホテルの宿泊管理システムのデータベース設計に関する次の記述を読んで、設問1～3に答えよ。 H社は、全国で六つのホテルを運営している。H社では、宿泊管理システムを構築してから時間が経過していることから、改めて現行の宿泊管理業務を分析し、更に新規要件を洗い出し、宿泊管理システムを再構築することにした。システムの再構築に当たって、予約管理、チェックイン、チェックアウトに関する業務について、概念データモデルを整理し、テーブル構造を設計した。 〔現行業務の分析結果〕 1. ホテルの概要 （1）ホテル ①　札幌，東京，大阪，神戸，福岡，那覇に各1か所ずつ，計6か所の大型シティホテルと予約センタを運営している。ホテルと予約センタをまとめて拠点と呼び，拠点はホテルコードで識別される。 （2）客室タイプ ①　全ホテルに共通の客室タイプが設定されている。客室タイプは，シングルA，シングルB，ツインA，ツインB，ダブルAなどで，客室タイプコードで識別される。客室タイプごとに定員が決まっている。 ②　客室タイプの標準料金はホテルごとに設定されている。標準料金は，後述の宿泊プランの宿泊料金を決定するときに参考にされる。 ③　予約受付前の予約可能な客室数（以下，予約可能数という）は，ホテルと客室タイプと禁煙喫煙区分の組合せごとに，日ごとに管理されている。全ての客室が予約可能ではなく，修繕を予定している客室は，予約不可として予約可能数から除外する。予約可能数は，その日の“割当済数＋未割当数”である。予約が入るごとに，割当済数が予約客室数分加算され，未割当数が予約客室数分減算される。 （3）客室 ①　客室は，ホテルごとに客室番号で識別される。 ②　客室ごとに客室タイプが決まる。 ③　客室の改修によって，客室タイプ及び禁煙喫煙区分を見直す場合がある。 ④　客室が，“準備中”，“チェックイン可”，“チェックイン済”，“チェックアウト済”のいずれの状態であるかを，客室状態で管理している。 ⑤　修繕予定の客室は，客室ごと，日ごとの予約可能区分で管理している。予約可能区分の値は，予約可能な場合を“Y”，修繕予定の場合を“R”としている。 （4）宿泊プラン ①　宿泊プランはホテルごとに設定し，宿泊プランコードで識別される。全ホテルに共通の宿泊プランはない。 ②　シーズン，イベントなどに合わせて，宿泊プランの提供期間（開始年月日，終了年月日）と設定曜日が決まっている。各宿泊プランは，夕食と朝食の有無，禁煙喫煙区分などによっても分かれている。 ③　宿泊プランと客室タイプの組合せごとに，宿泊料金が決まっている。 ④　予約及びチェックインでは，顧客が宿泊プランと客室タイプをそれぞれ一つ指定する。 ⑤　宿泊プランでは，1泊の利用可能時間帯（チェックイン時刻とチェックアウト時刻）を決めている。通常の利用可能時間帯は，宿泊当日15時から翌日12時までである。 （5）施設 ホテルには，フロント，レストラン，バー，スパ，エステなどの施設があり，ホテルごとに施設コードで識別される。 2. 従業員 （1）従業員は，一つの予約センタ又は一つのホテルに所属し，従業員コードで識別される。 （2）ホテルに所属する従業員は，ホテルの施設の一つを担当する。 3. 代理店 代理店は，H社に代わって予約の受付を行い，代理店コードで識別される。 4. 予約管理業務 （1）H社で直接予約を受け付ける場合（以下，直接予約という）と代理店経由で予約を受け付ける場合（以下，代理店予約という）がある。直接予約と代理店予約は，ホテル共通の予約番号で一意に識別される。 （2）直接予約は，H社のWebサイト上でのインターネット予約（以下，インターネット予約という）と，予約センタへの電話予約（以下，予約センタ予約という）に分けられる。直接予約の場合，予約内容を登録し，予約番号を発番する。さらに，予約センタ予約の場合，その予約を受け付けた予約センタの受付従業員も登録する。ホテルに予約の電話がかかってきた場合には，予約センタへ転送する。 （3）代理店予約では，代理店の担当者が，H社の代理店用Webサイト上で予約内容と代理店を登録する。予約可能であった場合，予約番号が発番されて，予約完了となり，予約確認書を顧客に渡す。 （4）予約に必要な情報は，顧客の基本情報（後述），宿泊プラン，客室タイプ，宿泊開始日，泊数，1客室当たりの人数，客室数である。 （5）予約のキャンセルは，宿泊開始日前であれば，予約番号ごとに可能である。予約済み泊数を短縮・延長したい場合は，予約を一度キャンセルし，再度予約することになる。 5. 顧客管理業務 （1）リピータを確保する目的で顧客管理を行っており，氏名，かな氏名，メールアドレス，電話番号などの基本情報を登録した顧客には，顧客番号を付けて一元管理する。 （2）顧客は予約のときに顧客番号を提示することで，基本情報を提示する必要がなくなる。インターネット予約のときには顧客番号を入力し，予約センタ予約及び代理店予約のときには顧客番号を口頭で伝える。 （3）顧客は，予約のときに顧客番号を提示した場合，宿泊情報に顧客番号が引き継がれるので，チェックインでは顧客番号を提示する必要はない。 （4）一方，予約のときに顧客番号を提示しなかった場合，チェックインのときに顧客番号を提示すれば，基本情報を提示する必要はない。 （5）顧客番号ごとの延べ泊数によって顧客を区分し，顧客区分として上位から順に“S”，“A”，“B”を設定している。3月31日時点で過去1年間の延べ泊数が所定の泊数以上の顧客を優良顧客とし，顧客区分に“S”又は“A”を設定して，施設の利用料金値引サービスを提供する。所定の泊数に満たない顧客の場合は，顧客区分に“B”を設定する。 なお，新規顧客の登録時は顧客区分に“B”が設定される。 6. チェックイン業務 （1）予約ありと予約なしの場合に分かれる。予約なしの場合は，フロントの従業員が宿泊希望（宿泊プラン，客室タイプ，泊数など）を確認する。ただし，宿泊希望は予約として登録しない。 （2）フロントの従業員は，客室状態が“チェックイン可”の客室を割り当てる。 （3）顧客がチェックインすると，客室番号と宿泊期間（チェックイン年月日とチェックアウト年月日）ごとに宿泊として登録し，ホテルコードごとに一意な宿泊番号を付与する。 （4）宿泊期間分の連泊可能な客室がない場合，客室タイプ及び禁煙喫煙区分が同じ別の客室に途中で変更することがある旨を顧客に説明し，了解を得た上で客室を割り当てる。この場合，客室の変更ごとに宿泊を個別に登録する。 （5）宿泊登録の際，予約又はチェックインのときに顧客番号が提示されている場合は，顧客番号だけを登録，それ以外は，基本情報を登録する。 （6）宿泊者全員の氏名，かな氏名，住所，電話番号，年齢を，宿泊の明細である宿泊者として登録する。 （7）チェックイン終了後，客室状態は“チェックイン済”に変更される。 （8）宿泊中の顧客が，宿泊期間の延長を希望した場合，同じ客室で延長可能かどうかを確認する。可能な場合は，宿泊期間を延長する。客室タイプ及び禁煙喫煙区分が同じ別の客室ならば延長が可能な場合は，顧客の了解を得た上で，延長分の客室を割り当てる。この場合，延長分の客室ごとに宿泊を登録する。いずれも不可能な場合は，延長を断る。 7. ホテル内サービス （1）ホテル内で提供される商品・サービスとして，レストラン，バーでの飲食，スパ，エステでのサービス，クリーニング，ルームサービスなどがある。これらは，ホテル共通の商品コードで識別される。 （2）レストラン，バー，スパ，エステでの利用代金は，その場で支払うことも，チェックアウトのときに支払うことも可能である。クリーニング，ルームサービスなどの利用代金は，チェックアウトのときに支払う。 （3）チェックアウトのときに支払う利用代金の場合は，売掛及び売掛明細として記録される。 8. チェックアウト業務 （1）チェックアウトのときに，宿泊料金及びホテル内サービスの利用代金を請求する。客室を途中で変更した場合は，変更した客室ごとに分けて，チェックアウト時に請求する。 （2）宿泊番号ごとにサービスの売掛金額を集計し，客室別請求を作成する。請求書の明細に，商品名，数量，金額を記載する。 （3）チェックアウト完了後，客室状態は“チェックアウト済”に変更される。 9. 客室清掃 （1）客室状態が“チェックアウト済”となった客室は，清掃される。 （2）客室状態は，清掃開始時に“準備中”に変更され，清掃完了後に“チェックイン可”に変更される。 〔概念データモデルとテーブル構造〕 現行業務の分析結果に基づいて，概念データモデルとテーブル構造を設計した。テーブル構造は，概念データモデルでサブタイプとしたエンティティタイプを，スーパタイプのエンティティタイプにまとめた。現行業務の概念データモデルを図1に，現行業務のテーブル構造を図2に示す。 図1 現行業務の概念データモデル（未完成） 図2 現行業務のテーブル構造（未完成） 〔新規要件〕 1. 予約管理業務 （1）現行の予約管理業務では，1回の予約で複数の客室タイプを指定できない。宿泊管理システムの再構築後は，1回の予約で指定できる宿泊プランと泊数は一つのままであるが，宿泊プランで選択可能な客室タイプを複数指定できるようにしたい。このとき，1客室当たりの人数は，客室ごとに指定してもらう。1客室タイプの客室数が2以上の場合は，指定された客室数分を指定してもらう。 （2）優良顧客へのサービス向上策として，希望する客室タイプ（以下，希望客室タイプという）が予約で満室の場合，上位の客室タイプを仮予約し，希望客室タイプにキャンセルがあったときに自動変更できるようにしたい。具体的な要件は，次のとおりである。 ①　希望客室タイプから予約可能な上位客室タイプを表示し，その中から顧客に選択してもらう。顧客が選択した上位客室タイプで予約を登録する。これを仮予約という。仮予約の場合，キャンセル待ちであることと，希望客室タイプを管理する。 なお，希望客室タイプで予約登録する場合を，本予約という。 ②　一つの予約で複数の客室を仮予約した場合は，その数以上の客室がキャンセルされたときに希望客室タイプに変更する。 ③　複数の仮予約がある場合は，仮予約した順に希望客室タイプに変更する。 ④　仮予約が連泊の場合は，連泊期間を単位に希望客室タイプに変更する。連泊期間の一部分だけを，希望客室タイプに変更することはしない。 ⑤　予約状態を区別できるように，最初から本予約できた場合，仮予約中（キャンセル待ち）の場合，仮予約から本予約に変更できた場合の三つに分けて管理する。 ⑥　希望客室タイプに変更できるキャンセルがなかった場合は，仮予約した客室タイプを本予約の客室タイプとして扱う。この場合，仮予約で選択した上位客室タイプの宿泊料金を適用する。 ⑦　客室タイプと上位客室タイプの組合せ，及びその組合せ可能条件は，ホテルごとにあらかじめ決めておく。一つの客室タイプで複数の上位客室タイプの組合せが可能であり，その組合せの具体例を表1に示す。 表1 一つの客室タイプで複数の上位客室タイプの組合せの具体例 2. チェックイン業務 （1）チェックインのときに，顧客が客室タイプの変更を申し出た場合，現行の予約管理業務では，変更後の客室タイプの宿泊料金を適用している。宿泊管理システムの再構築後は，優良顧客が上位客室タイプへのアップグレード（以下，UGという）を希望した場合，UG後の客室タイプの宿泊料金を値引きする。この場合の値引額をUG値引額という。 （2）UG値引額は，ホテルごと，UG前客室タイプごと，UG後客室タイプごとに定める。客室の利用状況を踏まえて不定期にUG値引額を見直すことがあるが，これに伴うUG値引額の履歴管理は行わない。顧客がUGを希望した時点のUG値引額を適用する。 （3）値引きする客室タイプと上位客室タイプの組合せ，及びその組合せ可能条件は，〔新規要件〕1.予約管理業務（2）と同じとし，ホテルごとにあらかじめ決めておく。さらに，組合せごとのUG値引額を設定する。 3. チェックアウト業務 （1）複数の客室の請求を一つにまとめて請求してほしいという要望がある。これをまとめ請求という。通常の客室別の請求はホテルコードと宿泊番号で識別しているが，まとめ請求ではホテルコードと新たなまとめ請求番号で識別する。まとめ請求番号は，ホテルごとに一意とする。 （2）チェックアウトのときに顧客からまとめ請求を依頼された場合，通常の客室ごとの請求書ではなく，まとめ請求書を印刷する。このために“まとめ請求”テーブルを追加し，まとめ請求書に印字する請求合計金額，売掛小計金額，宿泊料金を記録する。 解答に当たっては，巻頭の表記ルールに従うこと。ただし，エンティティタイプ間の対応関係にゼロを含むか否かの表記は必要ない。テーブル構造の解答に当たっては，主キーを表す実線の下線，外部キーを表す破線の下線も示すこと。 なお，エンティティタイプ間のリレーションシップとして“多対多”のリレーションシップを用いないこと。エンティティタイプ名，テーブル名及び列名は，それぞれ意味を識別できる適切な名称とすること。また，識別可能なサブタイプが存在する場合，他のエンティティタイプとのリレーションシップは，スーパタイプ又はサブタイプのいずれか適切な方との間に記述せよ。また，テーブル構造は第3正規形の条件を満たしていること。 設問1　現行業務の概念データモデルについて，（1），（2）に答えよ。 （1）図1中のマスタ領域（客室）は，エンティティタイプ及びリレーションシップが未完成である。必要なエンティティタイプ及びリレーションシップを補い，図を完成させよ。 なお，マスタ領域（客室）以外のエンティティタイプとのリレーションシップは不要とする。 マスタ領域（客室） （2）図1中のトランザクション領域（予約）は，サブタイプ及びリレーションシップが未完成である。必要なサブタイプ及びリレーションシップを補い，図を完成させよ。 なお，トランザクション領域（予約）内のエンティティタイプ間のリレーションシップを補うことに加えて，マスタ領域（従業員，代理店，顧客）のエンティティタイプとのリレーションシップも記入すること。 トランザクション領域（予約） 設問2　現行業務の業務処理及び制約について，（1）～（3）に答えよ。 （1）チェックイン時に宿泊期間を通して同じ客室を割り当てた後，翌日以降に宿泊者からの客室変更の要望があり，同じ客室タイプかつ同じ禁煙喫煙区分の客室に変更する場合，客室変更時点で行の追加・変更が必要となるテーブル名を，図2中から答えよ。ここで，客室状況の予約可能区分の変更はないものとする。 （2）客室の改修による客室タイプ及び禁煙喫煙区分の見直しがない場合について，チェックイン業務での客室割当て時の制約条件を表2にまとめた。表2中のa～fに入れる適切なテーブル名，列名又は列値を答えよ。 表2 客室割当て時の制約条件 （3）予約ありでチェックインした場合には更新しないテーブルの中で，予約なしでチェックインした場合は，行の更新が必要となるテーブルがある。そのテーブル名と，更新する列名を，図2中から全て答えよ。また，ホテルコードが“001”（●●東京ホテルのホテルコード），客室タイプコードが“SOA”（シングルAの客室タイプコード），禁煙喫煙区分が“禁煙”，宿泊年月日が“2014-04-19”で，二つの客室に宿泊する例について，列値の変更内容を答えよ。 設問3　新規要件を追加したテーブル構造の設計について，（1）～（3）に答えよ。 （1）〔新規要件〕1.予約管理業務（1）を実現するために，図2中の“予約”テーブルのテーブル構造を見直す必要がある。見直し後のテーブル構造を答えよ。 ここで，テーブルの変更に伴って追加する列を除き，図2中に表示されていない列については，解答する必要はない。 （2）〔新規要件〕1.予約管理業務（2），及び2.チェックイン業務について，（a）～（d）に答えよ。 （a）客室タイプと上位客室タイプの組合せ，及びUG値引額を管理するテーブルを，図2中に追加する。追加するテーブルの構造を答えよ。 （b）三つの予約状態を区別できるように仮予約と本予約の対応関係を管理する方法として，図2中の一つのテーブルに列を追加する案を考えた。該当するテーブル名と，追加する列名を答えよ。ここで，“予約”テーブルに，本予約とは別の行を仮予約として追加しないものとする。 （c）予約がキャンセルされた場合に，仮予約から本予約への変更処理を起動するトリガを定義する。トリガを定義する候補となる図2中のテーブル名を二つ答えよ。また，二つのテーブルについて，トリガの実行条件（どの列がどのように変化した場合か）を，それぞれ20字以内で述べよ。 （d）“宿泊”テーブルに，UG値引額の列を追加することにした。その理由を，40字以内で述べよ。 （3）まとめ請求を可能とするために追加する“まとめ請求”テーブルの構造を答えよ。また，図2中の一つのテーブルに，まとめ請求のための列を一つ追加する。該当するテーブル名と，追加する列名を答えよ。