午後Ⅱ試験対策：概念データモデリングに関する問題の解き方

はじめに

今回は、午後Ⅱ試験対策として、概念データモデリングに関する問題の解き方を紹介します。なお、本連載では「最小限の努力で試験に合格するためのノウハウを伝授すること」を目指していますので、第2回の解き方をそのまま活かせる平成26年度の午後Ⅱ試験を用いて解説を進めていきます。

平成26年度午後Ⅱ試験問2に挑戦！

それでは、さっそく平成26年度午後Ⅱ試験問2に挑戦してみましょう。問題文はこちらからダウンロードしてください。また、解答用紙はこちらに用意しました。解答例はこちらをご覧ください。

問題の解答にかける時間は90分を目標にしましょう。

設問1の解説

(1) マスタ領域(客室)の概念データモデルの完成
午後Ⅰ試験のデータベース設計と同様に、問題文やテーブル構造からテーブルやリレーションシップを導出します。

• 「ホテル」と「客室タイプ」間の関連
  〔現行業務の分析結果〕1.(2)①「全ホテルに共通の客室タイプが設定されている。」と〔現行業務の分析結果〕1.(2)②「客室タイプの標準料金はホテルごとに設定されている。」より、客室タイプの標準料金は、テーブル「ホテル」と「客室タイプ」の間の連関エンティティであることがわかります。図1や図2にある別のテーブル名「ホテル客室タイプ日別在庫」より、この連関エンティティの名前は「ホテル客室タイプ」にします。また、「ホテル客室タイプ」の主キーは、「ホテル」の主キー「ホテルコード」と「客室タイプ」の主キー「客室タイプコード」を組み合わせた{ホテルコード,客室タイプコード}にします(テーブル間の関連は下図。以下同)。
  
  
  


• 「ホテル客室タイプ」と「ホテル客室タイプ日別在庫」間の関連
  〔現行業務の分析結果〕1.(2)③に「予約受付前の予約可能な客室数(以下，予約可能数という)は，ホテルと客室タイプと禁煙喫煙区分の組合せごとに，日ごとに管理されている。」とあります。また、図2のテーブル「ホテル客室タイプ日別在庫」の属性に「予約可能数」があることから、テーブル「ホテル客室タイプ日別在庫」の主キーは{ホテルコード,客室タイプコード,禁煙喫煙区分,宿泊年月日}です。さらに、テーブル「ホテル客室タイプ」の主キーである{ホテルコード,客室タイプコード}を含むことと〔現行業務の分析結果〕1.(2)③「ホテルと客室タイプと禁煙喫煙区分の組合せごとに」の記述から、テーブル「ホテル客室タイプ」と「ホテル客室タイプ日別在庫」の間には関連があります。
  
  
  


• 「ホテル客室タイプ」と「客室」間の関連
  〔現行業務の分析結果〕1.(3)①「客室は，ホテルごとに客室番号で識別される。」より、ホテルと客室に関連があることがわかります。この文章だけで、テーブル「ホテル」と「客室」の間にリレーションシップを結びたくなりますが、図2のテーブル「客室」の属性に「ホテルコード」と「客室タイプコード」を含むことと〔現行業務の分析結果〕1.(3)②「客室ごとに客室タイプが決まる」より、テーブル「客室」の属性「ホテルコード」と「客室タイプコード」は、テーブル「ホテル客室タイプ」の主キーを参照する外部キーです。
  
  
  


• 「客室」と「客室状況」間の関連
  〔現行業務の分析結果〕1.(3)⑤の「修繕予定の客室は，客室ごと，日ごとの予約可能区分で管理している。」と図2のテーブル「客室状況」の属性に「予約可能区分」が含まれていること、さらに、テーブル「客室状況」には関係「客室」の主キーである{ホテルコード,客室番号}を含むことから、テーブル「客室」と「客室状況」の間には関連があります。
  
  
  


• 「ホテル」と「宿泊プラン」間の関連
  〔現行業務の分析結果〕1.(4)①の「宿泊プランはホテルごとに設定し，宿泊プランコードで識別される。全ホテルに共通の宿泊プランはない。」と、テーブル「宿泊プラン」にはテーブル「ホテル」の主キーである「ホテルコード」を含むことから、テーブル「ホテル」と「宿泊プラン」の間には関連があります。なお、テーブル「宿泊プラン」の主キーは{ホテルコード,宿泊プランコード}です。
  
  
  


• 「宿泊プラン」と「宿泊プラン明細」間の関連
  〔現行業務の分析結果〕1.(4)③の「宿泊プランと客室タイプの組合せごとに，宿泊料金が決まっている。」と、テーブル「宿泊プラン明細」にはテーブル「宿泊プラン」の主キーである{ホテルコード,宿泊プランコード}を含むこと、さらにテーブル「宿泊プラン明細」にはテーブル「宿泊料金」を含むことから、テーブル「宿泊プラン」と「宿泊プラン明細」の間には関連があります。
  
  
  


• 「ホテル客室タイプ」と「宿泊プラン明細」間の関連
  〔現行業務の分析結果〕1.(4)③の「宿泊プランと客室タイプの組合せごとに，宿泊料金が決まっている。」と、テーブル「宿泊プラン明細」にはテーブル「宿泊プラン」の主キーである{ホテルコード,客室タイプコード}を含むことから、テーブル「ホテル客室タイプ」と「宿泊プラン明細」の間には関連があります。
  
  
  

(2)マスタ領域(従業員,代理店,顧客)とトランザクション領域(予約)の概念データモデルの完成
(1)と同様に、問題文からテーブルやリレーションシップを導出します。

• 「予約」と「直接予約」、および「代理店予約」間の関連
  〔現行業務の分析結果〕4.(1)に「H社で直接予約を受け付ける場合（以下，直接予約という）と代理店経由で予約を受け付ける場合（以下，代理店予約という）がある。」とあります。また、設問には「図1中のトランザクション領域（予約）は，サブタイプ及びリレーションシップが未完成である。必要なサブタイプ及びリレーションシップを補い，図を完成させよ。」と記載されています。これらのことから、スーパタイプ「予約」にはサブタイプ「直接予約」と「代理店予約」が存在すると考えます。
  
  
  


• 「直接予約」と「インターネット予約」、および「電話予約」間の関連
  〔現行業務の分析結果〕4.(2)の「直接予約は，H社のWebサイト上でのインターネット予約（以下，インターネット予約という）と，予約センタへの電話予約（以下，予約センタ予約という）に分けられる。」より、スーパタイプ「直接予約」にはサブタイプ「インターネット予約」と「予約センタ予約」が存在すると考えます。
  
  
  


• 「予約センタ従業員」と「予約センタ予約」間の関連
  〔現行業務の分析結果〕4.(2)「予約センタ予約の場合，その予約を受け付けた予約センタの受付従業員も登録する。」より、テーブル「予約センタ予約」には、テーブル「予約センタ従業員」の主キー属性「従業員コード」を参照する外部キーを保持します。したがって、テーブル「予約センタ従業員」と「予約センタ予約」の間には関連があります。
  
  
  


• 「代理店」と「代理店予約」間の関連
  〔現行業務の分析結果〕4.(3)「代理店予約では，代理店の担当者が，H社の代理店用Webサイトで予約内容と代理店を登録する。」より、テーブル「代理店予約」はテーブル「代理店」の主キー属性「代理店コード」を参照する外部キーを保持します。したがって、テーブル「代理店」と「代理店予約」の間には関連があります。
  
  
  


• 「予約」と「顧客」間の関連
  〔現行業務の分析結果〕4.(4)に「予約に必要な情報は，顧客の基本情報（後述），宿泊プラン，客室タイプ，宿泊開始日，泊数，1客室当たりの人数，客室数である。」とあります。また、〔現行業務の分析結果〕5.(1)に「氏名，かな氏名，メールアドレス，電話番号などの基本情報を登録した顧客には，顧客番号を付けて一元管理する。」、(2)に「顧客は予約のときに顧客番号を提示することで，基本情報を提示する必要がなくなる。」と記載されています。ところが、図2のテーブル「予約」の属性には顧客の基本情報や顧客番号は含まれていません。これらのことから、テーブル「予約」はスーパタイプであり、基本情報を含むサブタイプと、顧客番号を含むサブタイプがあると考えられます。あとはサブタイプ名ですが、図2のテーブル「宿泊」の属性「顧客番号提示未提示区分」より、基本情報を含むサブタイプ名を「顧客番号未提示予約」、顧客番号を含むサブタイプ名を「顧客番号提示予約」とします。さらに、「顧客番号提示予約」の属性「顧客番号」はテーブル「顧客」の主キーを参照することで、テーブル「顧客」と「顧客番号提示予約」の間に関連を持たせます。
  
  
  

設問2の解説

(1)客室変更時点で行の追加・変更が必要となるテーブル名
まず、客室変更時の業務ルールを問題文から探します。〔現行業務の分析結果〕6.(3)に「顧客がチェックインすると，客室番号と宿泊期間ごとに宿泊として登録し，ホテルコードごとに一意な宿泊番号を付与する。」と記載されています。また、〔現行業務の分析結果〕6.(4)に「客室の変更ごとに宿泊を個別に登録する。」とあります。これらのことから、客室を変更した場合には以下の手続きが必要です。

一方、〔現行業務の分析結果〕6.(6)に「宿泊者全員の氏名～年齢を，宿泊の明細である宿泊者として登録する。」と記載されています。したがって、客室を変更した場合には以下の手続きが必要です。

さらに、〔現行業務の分析結果〕6.(2)に「フロントの従業員は，客室状態が‘チェックイン可’の客室を割り当てる。」、(7)に「チェックイン終了後，客室状態は‘チェックイン済’に変更される。」と記載されています。これらのことから、客室を変更した場合には以下の手続きが必要です。

以上のことから、客室変更時点で行の追加・変更が必要となるテーブルは「客室、宿泊、宿泊者」です。

(2)チェックイン業務での客室割当て時の制約条件

• 制約番号1
  (1)の解答にもあるように、(a)は「‘チェックイン可’」です。
• 制約番号2
  〔現行業務の分析結果〕1.(3)⑤「修繕予定の客室は，客室ごと，日ごとの予約可能区分で管理している。予約可能区分の値は，予約可能な場合を‘Y’，修繕予定の場合を’R’としている。」より、修繕可能な客室を利用しないことがわかります。したがって、(b)は「予約可能区分」、(c)は「‘Y’」です。
• 制約番号3
  〔現行業務の分析結果〕6.(8)に「客室タイプ及び禁煙喫煙区分が同じ別の客室ならば延長が可能な場合は～延長分の客室を割り当てる。」とあることから、(d)は「禁煙喫煙区分」です。また、〔現行業務の分析結果〕1.(2)③「予約可能数は，その日の“割当済数+未割当数”である。予約が入るごとに，割当済数が予約客室数分加算され，未割当数が予約客室数分減算される。」より、未割当数が0の場合は予約できません。この属性「未割当数」を含むテーブルは「ホテル客室タイプ日別在庫」であることから、(e)は「ホテル客室タイプ日別在庫」、(f)は「未割当数」です。

(3)予約なしでチェックインした場合に行の更新が必要となるテーブル名と列名、および列値の変更内容
(2)制約番号3の解説より、予約なしでチェックインした場合、テーブル「ホテル客室タイプ日別在庫」の属性「割当済数」を利用する客室数分加算し、属性「未割当数」を利用する客室数分減算します。したがって、解答は以下の通りです。

設問3の解説

(1)予約テーブル見直し後のテーブル構造
設問文より、図2の予約テーブルの構造において、複数の客室タイプを指定可能にし、1客室タイプの客室数を客室ごとに指定可能にする場合、以下を考慮する必要があります。

これらの内容をもとに第一正規化まで実施すると、以下の通りになります。

上記のテーブルには部分関数従属性も推移的関数従属性もないので、第三正規形です。

(2)(a)客室タイプと上位客室タイプの組合せ及びUG値引額を管理するテーブル構造
〔新規要件〕1.(2)⑦「客室タイプと上位客室タイプの組合せ，及びその組合せ可能な条件は，ホテルごとにあらかじめ決めておく。一つの客室タイプで複数の上位客室タイプの組合せが可能であり～」と、〔新規要件〕2.(2)「UG値引額は，ホテルごと，UG前客室タイプごと，UG後客室タイプごとに定める」より、テーブル構造は以下の通りです。

(2)(b)仮予約と本予約の対応関係を管理する方法
〔新規要件〕1.(2)①「仮予約の場合，キャンセル待ちであることと，希望客室タイプを管理する。」と、〔新規要件〕1.(2)⑤「予約状態を区別できるように，最初から本予約できた場合，仮予約中（キャンセル待ち）の場合，仮予約から本予約に変更できた場合の三つに分けて管理する。」より、テーブル「予約」に列「予約状態」「希望客室タイプコード」を追加します。

(2)(c)仮予約から本予約への変更処理を起動するトリガ
仮予約から本予約への変更処理が発生するタイミングには以下の2点が考えられます。

1. 予約がキャンセルされた場合
2. 上位客室タイプへアップグレードを希望した場合

1.は、テーブル「予約」の列「キャンセル年月日」値が設定された場合を実行条件にトリガを起動します。2.は、当日のアップグレード前の客室タイプに関するテーブル「ホテル客室タイプ日別在庫」の列「割当済数」の値が減り、「未割当数」が増えます。このいずれかの場合を実行条件にトリガを起動します。

(2)(d)“宿泊”テーブルにUG値引額の列を追加する理由
〔新規要件〕2.(2)「不定期にUG値引額を見直すことがあるが，これに伴うUG値引額の履歴管理は行わない。顧客がUGを希望した時点でのUG値引額を適用する」より、UGを希望した時点でのUG値引額を保持する必要性を解答とするか、UGを希望した時点以降にUG値引額が変更される可能性を解答とします。

(3)“まとめ請求”テーブルの構造とまとめ請求のために列を追加するテーブル
〔新規業務〕3.(1)「まとめ請求ではホテルコードと新たなまとめ請求番号で識別する。まとめ請求番号は，ホテルごとに一意とする。」より、主キーは{ホテルコード,まとめ請求番号}です。また、〔新規業務〕3.(2)「“まとめ請求”テーブルを追加し，まとめ請求書に印字する請求合計金額，売掛小計金額，宿泊料金を記録する。」より、テーブル構造は以下の通りです。

さらに〔新規業務〕3.(1)「通常の客室別の請求はホテルコードと宿泊番号で識別していたが」と図2より、{ホテルコード,宿泊番号}が主キーのテーブル「客室別請求」に列名「まとめ請求番号」を追加することで、テーブル「まとめ請求」と「客室別請求」を関連づけます。

おわりに

今回は、午後Ⅱ試験から概念データモデルに関する問題の解き方を紹介しました。午後Ⅰ試験のデータモデル設計の解法を理解しておけば午後Ⅱ試験でも解答できるので、過去問題を解いて合格につなげましょう。次回は、午前Ⅱ試験の頻出問題の解き方を紹介します。