データベースの実装運用に関する次の記述を読んで，設問に答えよ。
D社は，全国でホテル，貸別荘などの施設を運営しており，予約管理，チェックイン及びチェックアウトに関する業務に，5年前に構築した宿泊管理システムを使用している。データベーススペシャリストのBさんは，企画部門からマーケティング用の分析データ（以下，分析データという）の提供依頼を受けてその収集に着手した。
〔分析データ収集〕
1.分析データ提供依頼
企画部門からの分析データ提供依頼の例を表1に示す。表1中の指定期間には分析対象とする期間の開始年月日及び終了年月日を指定する。
表1 分析データ提供依頼の例
2.宿泊管理業務の概要
宿泊管理システムの概念データモデルを図1に，関係スキーマを図2に，主な属性の意味制約を表2に示す。宿泊管理システムでは，図2中の関係“予約”，“会員予約”及び“非会員予約”を概念データモデル上のスーパータイプである“予約”にまとめて一つのテーブルとして実装している。
Bさんは，宿泊管理業務への理解を深めるために，図1，2，表2を参照して，表3の業務ルール整理表を作成した。表3では，Bさんが想定する業務ルールの例が，図1，図2，表2に反映されている業務ルールと一致しているか否かを判定し，一致欄に“◯”（一致）又は"x"（不一致）を記入する。エンティティタイプ欄には，判定時に参照する一つ又は複数のエンティティタイプ名を記入する。リレーションシップを表す線及び対応関係にゼロを含むか否かの区別によって適否を判定する場合には，リレーションシップの両端のエンティティタイプを参照する。
図1 宿泊管理システムの概念データモデル
図2 宿泊管理システムの関係スキーマ（一部省略）
表2 主な属性の意味制約
表3 業務ルール整理表（未完成）
3.問合せの設計
Bさんは，表1の依頼1〜依頼3の分析データ抽出に用いる問合せの処理概要及びSQL文をそれぞれ表4〜表6に整理した。hv1，hv2はそれぞれ指定期間の開始年月日，終了年月日を表すホスト変数である。 問合せ名によって，ほかの問合せの結果行を参照できるものとする。
表4 依頼1の分析データ抽出に用いる問合せ（未完成）
表5 依頼2の分析データ抽出に用いる問合せ
表6 依頼3の分析データ抽出に用いる問合せ
4.問合せの試験
Bさんは，各SQL文の実行によって期待どおりの結果が得られることを確認する試験を実施した。Bさんが作成した，表5のT2の試験で使用するT1のデータを表7にT2の試験の予想値を表8に示す。
表7 T2の試験で使用するT1のデータ
表8 T2の試験の予想値（未完成）
5.問合せの実行
Bさんは，実データを用いて，2022-09-01から2022-09-30を指定期間として表4表6のSQL文を実行して結果を確認したところ，表6の結果を反映した図3の標準単価と客室稼働率の関係（散布図）に客室稼働率100%を超える異常値が見られた。
図3 標準単価と客室稼働率の関係（散布図）
1.異常値発生原因の調査手順
Bさんは，次の（1）〜（3）の手順で調査を行った。
（1）①S3のSQL文を変更して再度問合せを実行し，異常値を示している施設コード，価格区分コードの組だけを求める。
（2）（1）で求めた施設コード，価格区分コードについて，S1S2のSQL文を変更して，施設コード，価格区分コード，客室タイプコードごとの累計稼働客室数，累計予約可能客室数をそれぞれ求める。
（3）（2）の結果から累計稼働客室数，累計予約可能客室数のいずれかに異常が認められたら，その集計に関連するテーブルの行を抽出する。
2.異常値発生原因の調査結果
調査手順の（1）から施設コード'103'，価格区分コード‘C4'を，調査手順の（2）から表9，表10を得た。調査手順の（3）では，累計予約可能客室数に異常があると判断して表11〜14を得た。
表9 （2）のS1で得た結果行
表10 （2）で得たS2の結果行
表11 （3）で得た“客室状況”テーブルの行（一部省略）
表12 （3）で得た“客室”テーブルの行（一部省略）
表13 （3）で得た“施設客室タイプ”テーブルの行
表14 （3）で得た“客室タイプ”テーブルの行（一部省略）
3.異常値発生原因の推測
Bさんは，調査結果を基に，施設コード‘103'の施設で異常値が発生する状況を次のように推測した。
・客室を会議室として時間帯に区切って貸し出している。
・客室タイプに貸会議室のタイプと時間帯とを組み合わせて登録している。一つの客室（貸会議室）には時間帯に区切った複数の客室タイプがあり，客室と客室タイプとの間に事実上多対多のリレーションシップが発生している。
・②これをS2のSQL文によって集計した結果，累計予約可能客室数が実際よりも小さくなり，客室稼働率が不正になった。
4.施設へのヒアリング
該当施設の管理者にヒアリングを行い，異常値の発生原因は推測どおりであることを確認した。さらに，貸会議室の運用について次の説明を受けた。
・客室の一部を改装し，会議室として時間貸しする業務を試験的に開始した。
・貸会議室は，9時〜11時，11時〜13時，13時〜15時のように1日を幾つかの連続する時間帯に区切って貸し出している。
・貸会議室ごとに，定員，価格区分を決めている。定員，価格区分は変更することがある。
・宿泊管理システムの客室タイプに時間帯を区切って登録し，客室タイプごとに予約可能数を設定している。さらに，貸会議室利用を宿泊として登録することで，宿泊管理システムを利用して，貸会議室の在庫管理，予約，施設利用，及び請求の手続を行っている。
・貸会議室は全て禁煙である。
・1回の予約で受け付ける貸会議室は1室だけである。
・音響設備，プロジェクターなどのオプションの予約，利用を受け付けている。
・一つの貸会議室の複数時間帯の予約を受けることもある。現在は時間帯ごとに異なる予約を登録している。貸会議室の業務を拡大する予定なので，1回の予約で登録できるようにしてほしい。
5.対応の検討
（1）分析データ抽出への対応
Bさんは，③表6中のS2の処理概要及びSQL文を変更することで，異常値を回避して施設ごとの客室稼働率を求めることにした。
（2）異常値発生原因の調査で判明した問題への対応
Bさんは，異常値発生原因の調査で，④このまま貸会議室の業務に宿泊管理システムを利用すると，貸会議室の定員変更時にデータの不整合が発生する，宿泊登録時に無駄な作業が発生する，などの問題があることが分かったので，宿泊管理システムを変更する方がよいと判断した。
〔RDBMSの主な仕様〕
宿泊管理システムで利用するRDBMSの主な仕様は次のとおりである。
1.テーブル定義
テーブル定義には，テーブル名を変更する機能がある。
2.トリガー機能
テーブルに対する変更操作（挿入，更新，削除）を契機に，あらかじめ定義した処理を実行する。
（1）実行タイミング（変更操作の前又は後。前者をBEFOREトリガー後者をAFTERトリガーという），列値による実行条件を定義することができる。
（2）トリガー内では，変更操作を行う前の行，変更操作を行った後の行のそれぞれに相関名を指定することで，行の旧値，新値を参照することができる。
（3）あるAFTERトリガーの処理実行が，ほかのAFTERトリガーの処理実行の契機となることがある。この場合，後続のAFTERトリガーは連鎖して処理実行する。
1.概念データモデルの変更
Bさんは，施設へのヒアリング結果を基に，宿泊管理業務の概念データモデルに，貸会議室の予約業務を追加することにした。Bさんが作成した貸会議室予約業務追加後のトランザクション領域の概念データモデルを図4に示す。図4では，マスタ一領域のエンティティタイプとのリレーションシップを省略している。
図4 貸会議室予約業務追加後のトランザクション領域の概念データモデル
2.テーブル構造の変更
Bさんは，施設へのヒアリングで聴取した要望に対応しつつ，現行のテーブル構造は変更せずに，貸会議室の予約，利用を管理するためのテーブルを追加することにして図5の追加するテーブルのテーブル構造を設計した。
図5 追加するテーブルのテーブル構造（未完成）
3.テーブル名の変更
図4の概念データモデルでは，エンティティタイプ“宿泊”及び"貸会議室利用”は，エンティティタイプ“施設利用”のサブタイプである。現行の“宿泊”テーブルはエンティティタイプ“施設利用”を実装したものだが，概念データモデル上サブタイプのエンティティタイプ名をテーブル名に用いることによる誤解を防ぐために，“宿泊”テーブルは“施設利用”に名称を変更することにした。
D社では，アプリケーションプログラム（以下，APという）の継続的な改善を実施しており，APのアクセスを停止することなくAPのリリースを行う仕組みを備えている
貸会議室予約機能のリリースに合わせてテーブル名の変更を行いたいが，“宿泊”テーブルには多くのAPで行の挿入，更新を行っていて，これら全てのAPの改定，試験を行うとリリース時期が遅くなる。そこで，一定の移行期間を設け，移行期間中は新旧両方のテーブル名を利用できるようにデータベースを実装し，必要な全てのAPの改定後に移行期間を終了して“宿泊”テーブルを廃止することにした。
実装に当たって，更新可能なビューを利用した更新可能ビュー方式，トリガーを利用したトリガー同期方式の2案を検討し，移行期間前，移行期間中，移行期間後の手順を表15に，表15中の手順[b2]，[b4]のトリガーの処理内容を表16に整理した。
表15 更新可能ビュー方式，トリガー同期方式の手順
表16 表15中の手順[b2]，[b4]のトリガーの処理内容（未完成）
設問1 〔分析データ収集〕について答えよ。
（1）表3中の a 〜 f に入れる“◯”，“x”を答えよ。また，表3中の ア ， イ に入れる一つ又は複数の適切なエンティティタイプ名を答えよ。
解答・解説
解答例
a：×
b：○
c：×
d：○
e：×
f：○
ア：プラン明細，予約
イ：宿泊，宿泊者
解説
ー
（2）表4中の ウ 〜 キ に入れる適切な字句を答えよ。
解答・解説
解答例
ウ：COUNT(DISTINCT B.会員番号) 又は COUNT(DISTINCT 会員番号)
エ：EXISTS
オ：B.会員番号 = D.会員番号
カ：A.チェックイン年月日 > C.チェックイン年月日
キ：COALESCE(リピート会員数,0) / 累計新規会員数
解説
ー
（3）表8中の太枠内に適切な数値を入れ，表を完成させよ。
解答・解説
解答例
| 会員番号 | 階級番号 |
| 100 | 1 |
| 101 | 2 |
| 102 | 5 |
| 103 | 1 |
| 104 | 3 |
| 105 | 4 |
| 106 | 5 |
| 107 | 4 |
| 108 | 3 |
| 109 | 2 |
解説
ー
設問2 〔異常値の調査・対応〕について答えよ。
（1）本文中の下線①で，調査のために表6中のS3をどのように変更したらよいか。変更内容を50字以内で具体的に答えよ。
解答・解説
解答例
・累計稼働客室数が累計予約可能客室数よりも大きい行を選択する条件の WHERE 句を追加する。
・客室稼働率が 100％よりも大きい行を選択する条件を WHERE 句に追加する。
解説
ー
（2）本文中の下線②で，累計予約可能客室数が実際よりも小さくなった理由を50字以内で具体的に答えよ。
解答・解説
解答例
・時間帯に区切った客室タイプのうち，客室に対応しないものが累計予約可能 客室数に含まれないから
・客室タイプ 72～74 に対応する客室数が累計予約可能客室数にカウントされないから
解説
ー
（3）本文中の下線③で，表6中のS2において，“客室状況”テーブルに替えてほかのテーブルから累計予約可能客室数を求めることにした。そのテーブル名を答えよ。
解答・解説
解答例
客室在庫
解説
ー
（4）本文中の下線④について，（a）どのようなデータの不整合が発生するか，（b）どのような無駄な作業が発生するか，それぞれ40字以内で具体的に答えよ。
解答・解説
解答例
(a) 同じ貸会議室の異なる客室タイプの定員に異なる値が設定される。
(b) 宿泊者がないにもかかわらず，1 名以上の宿泊者を記録しなければならない。
解説
ー
設問3 〔宿泊管理システムの変更〕について答えよ。
（1）図5中の ク ， ケ に入れる一つ又は複数の列名を答えよ。なお， ク ， ケ に入れる列が主キーを構成する場合，主キーを表す実線の下線を付けること。
解答・解説
解答例
ク：施設コード，客室タイプコード，年月日，時間帯コード，予約可能数， 割当済数
ケ：施設コード，予約番号，時間帯コード
解説
ー
（2）表15中の更新可能ビュー方式の手順の実施に際して，APのアクセスを停止する必要がある。APのアクセスを停止するのはどの手順の前か。表15中の手順番号を答えよ。また，APのアクセスを停止する理由を40字以内で具体的に答えよ。
解答・解説
解答例
手順番号：c1
理由：新 AP が“施設利用”テーブルにアクセスすると異常終了するから
解説
ー
（3）表15中のトリガー同期方式において，APのアクセスを停止せずにリリースを行う場合，表15中の手順では“宿泊”テーブルと“施設利用”テーブルとが同期した状態となるが，手順[b2]，[b3]の順序を逆転させると，差異が発生する場合がある。それはどのような場合か。50字以内で具体的に答えよ。
解答・解説
解答例
“施設利用”テーブルへのデータの複写が済んだ“宿泊”テーブルの行への更 新が発生した場合
解説
ー
（4）表16中の コ の条件がないと問題が発生する。どのような問題が発生するか。20字以内で具体的に答えよ。また，この問題を回避するために コ に入れる適切な条件を30字以内で具体的に答えよ。
解答・解説
解答例
問題：処理の無限ループが発生する。
コ：“宿泊”テーブルの行の旧値と新値が一致しない。
解説
ー
IPA公開情報
出題趣旨
長年運用を続けたデータベースは，開発時の論理モデルから逸脱したデータをテーブル構造の変更なしに格納していることがある。
本問では，宿泊施設の予約業務における分析データ抽出を題材として，データ設計後の論理モデルを理解・検証する能力，問合せを設計・試験する能力，データの異常を調査し修正する能力，継続的な改善をデータベース領域で実践する能力を問う。
採点講評
問 1 では，宿泊施設の予約業務における分析データ抽出を題材に，データベースの実装・運用について出題した。全体として正答率は平均的であった。
設問１では，(2)ウ，キの正答率が低かった。SQL 文をデータ分析の一環に用いるケースも増えている。SQL の構文及び関数を理解し，データを操作する技術を身に付けてほしい。
設問 2 では，(2)及び(4)(b)の正答率が低かった。(2)では，テーブル構造とデータの意味との相違に着目した解答を求めたが，SQL 文の構文の問題を指摘する解答が散見された。(4)(b)では，宿泊施設予約の業務ルールをそのまま貸会議室予約に適用することで生じる問題の指摘を求めたが，定員を確認するなど通常の業務を指摘する解答が散見された。
設問 3 では，(1)及び(4)の正答率が低かった。(1)では，定義すべき列が欠落している解答，主キーが誤っている解答が散見された。テーブル上で管理する具体的な業務データをイメージしながら，設計したテーブル構造が業務要件を満たしていることを入念に確認するように心掛けてほしい。(4)では，アプリケーションプログラムの継続的な改善を行うために，システムを停止せずにデータベースを変更する手法について理解を深めてほしい。
