データベーススペシャリスト午後II 総合事例記述令和4年 秋期
午後II 問1
問1 データベースの実装・運用に関するデータベース設計・実装
テーマ：宿泊施設の予約業務における分析データ抽出とシステム変更
左右2分割表示：左側の問題文をスクロールしながら右側の設問に解答できます
長文事例本文
図表クリックで拡大問1 データベースの実装・運用に関する次の記述を読んで，設問に答えよ。
D 社は，全国でホテル，貸別荘などの施設を運営しており，予約管理，チェックイン及びチェックアウトに関する業務に，5 年前に構築した宿泊管理システムを使用している。データベーススペシャリストの B さんは，企画部門からマーケティング用の分析データ（以下，分析データという）の提供依頼を受けてその収集に着手した。
〔分析データ収集〕
1. 分析データ提供依頼
企画部門からの分析データ提供依頼の例を表 1 に示す。表 1 中の指定期間には分析対象とする期間の開始年月日及び終了年月日を指定する。
表 1 分析データ提供依頼の例
| 依頼番号 | 依頼内容 |
| :--- | :--- |
| 依頼 1 | 施設ごとにリピート率を抽出してほしい。リピート率は，累計新規会員数に対する指定期間内のリピート会員数の割合（百分率）である。累計新規会員数は指定期間終了年月日以前に宿泊したことのある会員の総数，リピート会員数は過去 1 回以上宿泊し，かつ，指定期間内に 2 回目以降の宿泊をしたことのある会員数である。リピート会員がいない施設のリピート率はゼロにする。 |
| 依頼 2 | 会員を指定期間内の請求金額の合計値を基に上位から 5 等分に分類したデータを抽出してほしい。 |
| 依頼 3 | 客室の標準単価と客室稼働率との関係を調べるために，施設コード，標準単価及び客室稼働率を抽出してほしい。客室稼働率は，指定期間内の予約可能な客室数に対する同期間内の予約中又は宿泊済の客室数の割合（百分率）である。 |
2. 宿泊管理業務の概要
宿泊管理システムの概念データモデルを図 1 に，関係スキーマを図 2 に，主な属性の意味・制約を表 2 に示す。宿泊管理システムでは，図 2 中の関係 “予約”，“会員予約” 及び “非会員予約” を概念データモデル上のスーパータイプである “予約” にまとめて一つのテーブルとして実装している。
B さんは，宿泊管理業務への理解を深めるために，図 1，図 2，表 2 を参照して，表 3 の業務ルール整理表を作成した。表 3 では，B さんが想定する業務ルールの例が，図 1，図 2，表 2 に反映されている業務ルールと一致しているか否かを判定し，一致欄に “○”（一致）又は “×”（不一致）を記入する。エンティティタイプ欄には，判定時に参照する一つ又は複数のエンティティタイプ名を記入する。リレーションシップを表す線及び対応関係にゼロを含むか否かの区別によって適否を判定する場合には，リレーションシップの両端のエンティティタイプを参照する。
拡大
拡大
表 2 主な属性の意味・制約
| 属性名 | 意味・制約 |
| :--- | :--- |
| 施設コード | 施設を識別するコード（3 桁の半角英数字） |
| 施設区分 | ‘H’（ホテル），‘R’（貸別荘） |
| 客室タイプコード | ホテルはシングル，ツインなど，貸別荘はテラスハウス，グランピングなど客室の構造，定員などによる分類である。 |
| 標準単価，価格設定規則 | 標準単価は，各施設が利用料金を決める際に基準となる金額，価格設定規則は，その際に従うべきルールの記述である。 |
| 予約可否 | ‘Y’（予約可），‘N’（修繕中） |
| 予約可能数，割当済数 | 予約可能数は，客室状況の予約可否が ‘Y’ の客室数で，手動で設定することもある。割当済数は，予約に割り当てられた客室数の合計である。 |
| 予約状態 | ‘1’（予約中），‘2’（宿泊済），‘9’（キャンセル済） |
| 会員予約区分 | ‘1’（会員予約），‘2’（非会員予約） |
| オプション番号 | 施設ごとに有償で提供する設備，物品，サービスを識別する番号である。 |
| 客室状態 | ‘1’（準備中），‘2’（チェックイン可），‘3’（チェックイン済），‘4’（チェックアウト） |
表 3 業務ルール整理表（未完成）
| 項番 | 業務ルールの例 | エンティティタイプ | 一致 |
| :---: | :--- | :--- | :---: |
| 1 | 施設ごと客室タイプごとに価格区分を設定し，価格区分ごとに標準単価を決めている。客室は施設ごとに一意な客室番号で識別する。 | 施設，客室タイプ，価格区分，施設客室タイプ，客室 | ○ |
| 2 | 全施設共通のプランがある。 | プラン | a |
| 3 | 会員は，予約時に登録済の会員番号を提示すれば氏名，住所などの提示を省略できる。 | 会員，会員予約 | b |
| 4 | 同一会員が，施設，プラン，客室タイプ，利用開始年月日が全て同じ複数の予約を取ることはできない。 | 会員，予約 | c |
| 5 | 予約のない宿泊は受け付けていない。飛び込みの場合でも当日の予約手続を行った上で宿泊を受け付ける。 | 予約，宿泊 | d |
| 6 | 連泊の予約を受け付ける場合に，連泊中には同じ客室になるように在庫の割当てを行うことができる。 | 予約 | e |
| 7 | 予約の際にはプラン及び客室タイプを必ず指定する。一つの予約で同じ客室タイプの複数の客室を予約できる。 | ア | f |
| 8 | 宿泊時には 1 名以上の宿泊者に関する情報を記録しなければならない。 | イ | ○ |
3. 問合せの設計
B さんは，表 1 の依頼 1 〜依頼 3 の分析データ抽出に用いる問合せの処理概要及び SQL 文をそれぞれ表 4 〜表 6 に整理した。hv1，hv2 はそれぞれ指定期間の開始年月日，終了年月日を表すホスト変数である。問合せ名によって，ほかの問合せの結果行を参照できるものとする。
表 4 依頼 1 の分析データ抽出に用いる問合せ（未完成）
| 問合せ名 | 処理概要（上段）と SQL 文（下段） |
| :---: | :--- |
| R1 | チェックイン年月日が指定期間の終了日以前の宿泊がある会員数を数えて施設ごとに累計新規会員数を求める。 |
| | SELECT A.施設コード, ウ AS 累計新規会員数 FROM 宿泊 A
INNER JOIN 予約 B ON A.施設コード = B.施設コード AND A.予約番号 = B.予約番号
WHERE B.会員予約区分 = '1' AND A.チェックイン年月日 <= CAST(:hv2 AS DATE)
GROUP BY A.施設コード | | R2 | チェックイン年月日が指定期間内の宿泊があり，指定期間にかかわらずその宿泊よりも前の宿泊がある会員数を数えて施設ごとにリピート会員数を求める。 | | | SELECT A.施設コード, ウ AS リピート会員数 FROM 宿泊 A
INNER JOIN 予約 B ON A.施設コード = B.施設コード AND A.予約番号 = B.予約番号
WHERE B.会員予約区分 = '1'
AND A.チェックイン年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
AND エ (SELECT * FROM 宿泊 C
INNER JOIN 予約 D ON C.施設コード = D.施設コード AND C.予約番号 = D.予約番号
WHERE A.施設コード= C.施設コード AND オ AND カ)
GROUP BY A.施設コード | | R3 | R1, R2 から施設ごとのリピート率を求める。 | | | SELECT R1.施設コード, 100 * キ AS リピート率 FROM R1
LEFT JOIN R2 ON R1.施設コード = R2.施設コード | 表 5 依頼 2 の分析データ抽出に用いる問合せ | 問合せ名 | 処理概要（上段）と SQL 文（下段） | | :---: | :--- | | T1 | 会員別に指定期間内の請求金額を集計する。 | | | SELECT C.会員番号, SUM(A.請求合計金額) AS 合計利用金額 FROM 請求 A
INNER JOIN 宿泊 B ON A.施設コード = B.施設コード AND A.宿泊番号 = B.宿泊番号
INNER JOIN 予約 C ON B.施設コード = C.施設コード AND B.予約番号 = C.予約番号
WHERE B.チェックイン年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
AND C.会員予約区分 = '1'
GROUP BY C.会員番号 | | T2 | T1 から会員を 5 等分に分類して会員ごとに階級番号を求める。 | | | SELECT 会員番号, NTILE(5) OVER (ORDER BY 合計利用金額 DESC) AS 階級番号 FROM T1 | 表 6 依頼 3 の分析データ抽出に用いる問合せ | 問合せ名 | 処理概要（上段）と SQL 文（下段） | | :---: | :--- | | S1 | 予約から利用開始年月日が指定期間内に含まれる予約中又は宿泊済の行を選択し，施設コード，価格区分コードごとに客室数を集計して累計稼働客室数を求める。 | | | SELECT A.施設コード, B.価格区分コード, SUM(A.客室数) AS 累計稼働客室数
FROM 予約 A
INNER JOIN 施設客室タイプ B
ON A.施設コード = B.施設コード AND A.客室タイプコード = B.客室タイプコード
WHERE A.利用開始年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
AND A.予約状態 <> '9'
GROUP BY A.施設コード, B.価格区分コード | | S2 | 客室状況から年月日が指定期間内に含まれる予約可能な客室の行を選択し，施設コード，価格区分コードごとに行数を数えて累計予約可能客室数を求める。 | | | SELECT A.施設コード, C.価格区分コード, COUNT(A.客室番号) AS 累計予約可能客室数
FROM 客室状況 A
INNER JOIN 客室 B ON A.施設コード = B.施設コード AND A.客室番号 = B.客室番号
INNER JOIN 施設客室タイプ C ON B.施設コード = C.施設コード
AND B.客室タイプコード = C.客室タイプコード
WHERE A.予約可否 = 'Y'
AND A.年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
GROUP BY A.施設コード, C.価格区分コード | | S3 | S1, S2 及び価格区分から施設コード，価格区分コードごとに標準単価，客室稼働率を求める。 | | | SELECT A.施設コード, A.価格区分コード, C.標準単価,
100 * COALESCE(B.累計稼働客室数, 0) / A.累計予約可能客室数 AS 客室稼働率
FROM S2 A
LEFT JOIN S1 B ON A.施設コード = B.施設コード
AND A.価格区分コード = B.価格区分コード
INNER JOIN 価格区分 C ON A.価格区分コード = C.価格区分コード | 4. 問合せの試験 B さんは，各 SQL 文の実行によって期待どおりの結果が得られることを確認する試験を実施した。B さんが作成した，表 5 の T2 の試験で使用する T1 のデータを表 7 に，T2 の試験の予想値を表 8 に示す。 表 7 T2 の試験で使用する T1 のデータ | 会員番号 | 合計利用金額 | | :---: | :---: | | 100 | 50,000 | | 101 | 42,000 | | 102 | 5,000 | | 103 | 46,000 | | 104 | 25,000 | | 105 | 8,000 | | 106 | 5,000 | | 107 | 12,000 | | 108 | 17,000 | | 109 | 38,000 | 表 8 T2 の試験の予想値（未完成） | 会員番号 | 階級番号 | | :---: | :---: | | 100 | 1 | | 101 | | | 102 | | | 103 | | | 104 | | | 105 | | | 106 | | | 107 | | | 108 | | | 109 | | 5. 問合せの実行 B さんは，実データを用いて，2022-09-01 から 2022-09-30 を指定期間として表 4 〜表 6 の SQL 文を実行して結果を確認したところ，表 6 の結果行を反映した図 3 の標準単価と客室稼働率の関係（散布図）に客室稼働率 100% を超える異常値が見られた。 ``` 客室稼働率 (%) 140 | 120 | ● 100 | ● ●● 80 | ●●●●●●● ● 60 | ●●● ● ● ● ● ● 40 | ● ● ● ● ● 20 | 0 +-------+-------+-------+-------+-------+-------+ 0 20,000 40,000 60,000 80,000 100,000 120,000 標準単価 (円) ``` 図 3 標準単価と客室稼働率の関係（散布図） 〔異常値の調査・対応〕 1. 異常値発生原因の調査手順 B さんは，次の (1) 〜 (3) の手順で調査を行った。 (1) ①S3 の SQL 文を変更して再度問合せを実行し，異常値を示している施設コード，価格区分コードの組だけを求める。 (2) (1) で求めた施設コード，価格区分コードについて，S1，S2 の SQL 文を変更して，施設コード，価格区分コード，客室タイプコードごとの累計稼働客室数，累計予約可能客室数をそれぞれ求める。 (3) (2) の結果から累計稼働客室数，累計予約可能客室数のいずれかに異常が認められたら，その集計に関連するテーブルの行を抽出する。 2. 異常値発生原因の調査結果 調査手順の (1) から施設コード ‘103’，価格区分コード ‘C4’ を，調査手順の (2) から表 9，表 10 を得た。調査手順の (3) では，累計予約可能客室数に異常があると判断して表 11 〜 14 を得た。 表 9 (2) の S1 で得た結果行 | 施設コード | 価格区分コード | 客室タイプコード | 累計稼働客室数 | | :---: | :---: | :---: | :---: | | 103 | C4 | 71 | 5 | | 103 | C4 | 72 | 10 | | 103 | C4 | 73 | 14 | | 103 | C4 | 74 | 7 | 表 10 (2) で得た S2 の結果行 | 施設コード | 価格区分コード | 客室タイプコード | 累計予約可能客室数 | | :---: | :---: | :---: | :---: | | 103 | C4 | 71 | 30 | 表 11 (3) で得た “客室状況” テーブルの行（一部省略） | 施設コード | 客室番号 | 年月日 | 予約可否 | | :---: | :---: | :---: | :---: | | 103 | 1050 | 2022-09-01 | Y | | 103 | 1050 | 2022-09-02 | Y | | : | : | : | : | | 103 | 1050 | 2022-09-30 | Y | 表 12 (3) で得た “客室” テーブルの行（一部省略） | 施設コード | 客室タイプコード | 客室番号 | ... | | :---: | :---: | :---: | :---: | | 103 | 71 | 1050 | ... | 表 13 (3) で得た “施設客室タイプ” テーブルの行 | 施設コード | 客室タイプコード | 価格区分コード | | :---: | :---: | :---: | | 103 | 71 | C4 | | 103 | 72 | C4 | | 103 | 73 | C4 | | 103 | 74 | C4 | 表 14 (3) で得た “客室タイプ” テーブルの行（一部省略） | 客室タイプコード | 客室タイプ名 | 定員 | ... | | :---: | :--- | :---: | :---: | | 71 | 貸会議室タイプ A 9時〜11時 | 25 | ... | | 72 | 貸会議室タイプ A 11時〜13時 | 25 | ... | | 73 | 貸会議室タイプ A 13時〜15時 | 25 | ... | | 74 | 貸会議室タイプ A 15時〜17時 | 25 | ... | 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 では，マスター領域のエンティティタイプとのリレーションシップを省略している。
拡大
2. テーブル構造の変更
B さんは，施設へのヒアリングで聴取した要望に対応しつつ，現行のテーブル構造は変更せずに，貸会議室の予約，利用を管理するためのテーブルを追加することにして図 5 の追加するテーブルのテーブル構造を設計した。
拡大
3. テーブル名の変更
図 4 の概念データモデルでは，エンティティタイプ “宿泊” 及び “貸会議室利用” は，エンティティタイプ “施設利用” のサブタイプである。現行の “宿泊” テーブルはエンティティタイプ “施設利用” を実装したものだが，概念データモデル上サブタイプのエンティティタイプ名をテーブル名に用いることによる誤解を防ぐために，“宿泊” テーブルは “施設利用” に名称を変更することにした。
D 社では，アプリケーションプログラム（以下，AP という）の継続的な改善を実施しており，AP のアクセスを停止することなく AP のリリースを行う仕組みを備えている。
貸会議室予約機能のリリースに合わせてテーブル名の変更を行いたいが，“宿泊” テーブルには多くの AP で行の挿入，更新を行っていて，これら全ての AP の改定，試験を行うとリリース時期が遅くなる。そこで，一定の移行期間を設け，移行期間中は新旧両方のテーブル名を利用できるようにデータベースを実装し，必要な全ての AP の改定後に移行期間を終了して “宿泊” テーブルを廃止することにした。
実装に当たって，更新可能なビューを利用した更新可能ビュー方式，トリガーを利用したトリガー同期方式の 2 案を検討し，移行期間前，移行期間中，移行期間後の手順を表 15 に，表 15 中の手順 b2，b4 のトリガーの処理内容を表 16 に整理した。
表 15 更新可能ビュー方式，トリガー同期方式の手順
| 実施時期 | 更新可能ビュー方式の手順 | トリガー同期方式の手順 |
| :--- | :--- | :--- |
| 移行期間前 | a1 更新可能な “施設利用” ビューを作成する。 | b1 “施設利用” テーブルを新規作成する。
b2 “宿泊” テーブルの変更を “施設利用” テーブルに反映するトリガーを作成する。
b3 “宿泊” テーブルから，施設コード，宿泊番号順に，“施設利用” テーブルに存在しない行を一定件数ごとにコミットしながら複写する。
b4 “施設利用” テーブルの変更を “宿泊” テーブルに反映するトリガーを作成する。 | | 移行期間中 | なし | なし | | 移行期間後 | c1 “施設利用” ビューを削除する。
c2 “宿泊” テーブルを “施設利用” テーブルに名称を変更する。 | d1 作成したトリガーを削除する。
d2 “宿泊” テーブルを削除する。 | 注記 1 [ ] で囲んだ英数字は，手順番号を表す。 注記 2 手順内で発生するトランザクションの ISOLATION レベルは，READ COMMITTED である。 表 16 表 15 中の手順 b2，b4 のトリガーの処理内容（未完成） | 手順 | 変更操作 | 処理内容 | | :---: | :---: | :--- | | b2 | INSERT | “宿泊” テーブルの追加行のキー値で “施設利用” テーブルを検索し，該当行がない場合に “施設利用” テーブルに同じ行を挿入する。 | | | UPDATE | “宿泊” テーブルの変更行のキー値で “施設利用” テーブルを検索し，該当行があり，かつ，コ 場合に，“施設利用” テーブルの該当行を更新する。 | | b4 | INSERT | “施設利用” テーブルの追加行のキー値で “宿泊” テーブルを検索し，該当行がない場合に “宿泊” テーブルに同じ行を挿入する。 | | | UPDATE | “施設利用” テーブルの変更行のキー値で “宿泊” テーブルを検索し，該当行があり，かつ，コ 場合に，“宿泊” テーブルの該当行を更新する。 | 注記 網掛け部分は表示していない。
拡大図1 宿泊管理システムの概念データモデル
拡大図2 宿泊管理システムの関係スキーマ（一部省略）
INNER JOIN 予約 B ON A.施設コード = B.施設コード AND A.予約番号 = B.予約番号
WHERE B.会員予約区分 = '1' AND A.チェックイン年月日 <= CAST(:hv2 AS DATE)
GROUP BY A.施設コード | | R2 | チェックイン年月日が指定期間内の宿泊があり，指定期間にかかわらずその宿泊よりも前の宿泊がある会員数を数えて施設ごとにリピート会員数を求める。 | | | SELECT A.施設コード, ウ AS リピート会員数 FROM 宿泊 A
INNER JOIN 予約 B ON A.施設コード = B.施設コード AND A.予約番号 = B.予約番号
WHERE B.会員予約区分 = '1'
AND A.チェックイン年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
AND エ (SELECT * FROM 宿泊 C
INNER JOIN 予約 D ON C.施設コード = D.施設コード AND C.予約番号 = D.予約番号
WHERE A.施設コード= C.施設コード AND オ AND カ)
GROUP BY A.施設コード | | R3 | R1, R2 から施設ごとのリピート率を求める。 | | | SELECT R1.施設コード, 100 * キ AS リピート率 FROM R1
LEFT JOIN R2 ON R1.施設コード = R2.施設コード | 表 5 依頼 2 の分析データ抽出に用いる問合せ | 問合せ名 | 処理概要（上段）と SQL 文（下段） | | :---: | :--- | | T1 | 会員別に指定期間内の請求金額を集計する。 | | | SELECT C.会員番号, SUM(A.請求合計金額) AS 合計利用金額 FROM 請求 A
INNER JOIN 宿泊 B ON A.施設コード = B.施設コード AND A.宿泊番号 = B.宿泊番号
INNER JOIN 予約 C ON B.施設コード = C.施設コード AND B.予約番号 = C.予約番号
WHERE B.チェックイン年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
AND C.会員予約区分 = '1'
GROUP BY C.会員番号 | | T2 | T1 から会員を 5 等分に分類して会員ごとに階級番号を求める。 | | | SELECT 会員番号, NTILE(5) OVER (ORDER BY 合計利用金額 DESC) AS 階級番号 FROM T1 | 表 6 依頼 3 の分析データ抽出に用いる問合せ | 問合せ名 | 処理概要（上段）と SQL 文（下段） | | :---: | :--- | | S1 | 予約から利用開始年月日が指定期間内に含まれる予約中又は宿泊済の行を選択し，施設コード，価格区分コードごとに客室数を集計して累計稼働客室数を求める。 | | | SELECT A.施設コード, B.価格区分コード, SUM(A.客室数) AS 累計稼働客室数
FROM 予約 A
INNER JOIN 施設客室タイプ B
ON A.施設コード = B.施設コード AND A.客室タイプコード = B.客室タイプコード
WHERE A.利用開始年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
AND A.予約状態 <> '9'
GROUP BY A.施設コード, B.価格区分コード | | S2 | 客室状況から年月日が指定期間内に含まれる予約可能な客室の行を選択し，施設コード，価格区分コードごとに行数を数えて累計予約可能客室数を求める。 | | | SELECT A.施設コード, C.価格区分コード, COUNT(A.客室番号) AS 累計予約可能客室数
FROM 客室状況 A
INNER JOIN 客室 B ON A.施設コード = B.施設コード AND A.客室番号 = B.客室番号
INNER JOIN 施設客室タイプ C ON B.施設コード = C.施設コード
AND B.客室タイプコード = C.客室タイプコード
WHERE A.予約可否 = 'Y'
AND A.年月日 BETWEEN CAST(:hv1 AS DATE) AND CAST(:hv2 AS DATE)
GROUP BY A.施設コード, C.価格区分コード | | S3 | S1, S2 及び価格区分から施設コード，価格区分コードごとに標準単価，客室稼働率を求める。 | | | SELECT A.施設コード, A.価格区分コード, C.標準単価,
100 * COALESCE(B.累計稼働客室数, 0) / A.累計予約可能客室数 AS 客室稼働率
FROM S2 A
LEFT JOIN S1 B ON A.施設コード = B.施設コード
AND A.価格区分コード = B.価格区分コード
INNER JOIN 価格区分 C ON A.価格区分コード = C.価格区分コード | 4. 問合せの試験 B さんは，各 SQL 文の実行によって期待どおりの結果が得られることを確認する試験を実施した。B さんが作成した，表 5 の T2 の試験で使用する T1 のデータを表 7 に，T2 の試験の予想値を表 8 に示す。 表 7 T2 の試験で使用する T1 のデータ | 会員番号 | 合計利用金額 | | :---: | :---: | | 100 | 50,000 | | 101 | 42,000 | | 102 | 5,000 | | 103 | 46,000 | | 104 | 25,000 | | 105 | 8,000 | | 106 | 5,000 | | 107 | 12,000 | | 108 | 17,000 | | 109 | 38,000 | 表 8 T2 の試験の予想値（未完成） | 会員番号 | 階級番号 | | :---: | :---: | | 100 | 1 | | 101 | | | 102 | | | 103 | | | 104 | | | 105 | | | 106 | | | 107 | | | 108 | | | 109 | | 5. 問合せの実行 B さんは，実データを用いて，2022-09-01 から 2022-09-30 を指定期間として表 4 〜表 6 の SQL 文を実行して結果を確認したところ，表 6 の結果行を反映した図 3 の標準単価と客室稼働率の関係（散布図）に客室稼働率 100% を超える異常値が見られた。 ``` 客室稼働率 (%) 140 | 120 | ● 100 | ● ●● 80 | ●●●●●●● ● 60 | ●●● ● ● ● ● ● 40 | ● ● ● ● ● 20 | 0 +-------+-------+-------+-------+-------+-------+ 0 20,000 40,000 60,000 80,000 100,000 120,000 標準単価 (円) ``` 図 3 標準単価と客室稼働率の関係（散布図） 〔異常値の調査・対応〕 1. 異常値発生原因の調査手順 B さんは，次の (1) 〜 (3) の手順で調査を行った。 (1) ①S3 の SQL 文を変更して再度問合せを実行し，異常値を示している施設コード，価格区分コードの組だけを求める。 (2) (1) で求めた施設コード，価格区分コードについて，S1，S2 の SQL 文を変更して，施設コード，価格区分コード，客室タイプコードごとの累計稼働客室数，累計予約可能客室数をそれぞれ求める。 (3) (2) の結果から累計稼働客室数，累計予約可能客室数のいずれかに異常が認められたら，その集計に関連するテーブルの行を抽出する。 2. 異常値発生原因の調査結果 調査手順の (1) から施設コード ‘103’，価格区分コード ‘C4’ を，調査手順の (2) から表 9，表 10 を得た。調査手順の (3) では，累計予約可能客室数に異常があると判断して表 11 〜 14 を得た。 表 9 (2) の S1 で得た結果行 | 施設コード | 価格区分コード | 客室タイプコード | 累計稼働客室数 | | :---: | :---: | :---: | :---: | | 103 | C4 | 71 | 5 | | 103 | C4 | 72 | 10 | | 103 | C4 | 73 | 14 | | 103 | C4 | 74 | 7 | 表 10 (2) で得た S2 の結果行 | 施設コード | 価格区分コード | 客室タイプコード | 累計予約可能客室数 | | :---: | :---: | :---: | :---: | | 103 | C4 | 71 | 30 | 表 11 (3) で得た “客室状況” テーブルの行（一部省略） | 施設コード | 客室番号 | 年月日 | 予約可否 | | :---: | :---: | :---: | :---: | | 103 | 1050 | 2022-09-01 | Y | | 103 | 1050 | 2022-09-02 | Y | | : | : | : | : | | 103 | 1050 | 2022-09-30 | Y | 表 12 (3) で得た “客室” テーブルの行（一部省略） | 施設コード | 客室タイプコード | 客室番号 | ... | | :---: | :---: | :---: | :---: | | 103 | 71 | 1050 | ... | 表 13 (3) で得た “施設客室タイプ” テーブルの行 | 施設コード | 客室タイプコード | 価格区分コード | | :---: | :---: | :---: | | 103 | 71 | C4 | | 103 | 72 | C4 | | 103 | 73 | C4 | | 103 | 74 | C4 | 表 14 (3) で得た “客室タイプ” テーブルの行（一部省略） | 客室タイプコード | 客室タイプ名 | 定員 | ... | | :---: | :--- | :---: | :---: | | 71 | 貸会議室タイプ A 9時〜11時 | 25 | ... | | 72 | 貸会議室タイプ A 11時〜13時 | 25 | ... | | 73 | 貸会議室タイプ A 13時〜15時 | 25 | ... | | 74 | 貸会議室タイプ A 15時〜17時 | 25 | ... | 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 貸会議室予約業務追加後のトランザクション領域の概念データモデル
拡大図5 追加するテーブルのテーブル構造（未完成）
b2 “宿泊” テーブルの変更を “施設利用” テーブルに反映するトリガーを作成する。
b3 “宿泊” テーブルから，施設コード，宿泊番号順に，“施設利用” テーブルに存在しない行を一定件数ごとにコミットしながら複写する。
b4 “施設利用” テーブルの変更を “宿泊” テーブルに反映するトリガーを作成する。 | | 移行期間中 | なし | なし | | 移行期間後 | c1 “施設利用” ビューを削除する。
c2 “宿泊” テーブルを “施設利用” テーブルに名称を変更する。 | d1 作成したトリガーを削除する。
d2 “宿泊” テーブルを削除する。 | 注記 1 [ ] で囲んだ英数字は，手順番号を表す。 注記 2 手順内で発生するトランザクションの ISOLATION レベルは，READ COMMITTED である。 表 16 表 15 中の手順 b2，b4 のトリガーの処理内容（未完成） | 手順 | 変更操作 | 処理内容 | | :---: | :---: | :--- | | b2 | INSERT | “宿泊” テーブルの追加行のキー値で “施設利用” テーブルを検索し，該当行がない場合に “施設利用” テーブルに同じ行を挿入する。 | | | UPDATE | “宿泊” テーブルの変更行のキー値で “施設利用” テーブルを検索し，該当行があり，かつ，コ 場合に，“施設利用” テーブルの該当行を更新する。 | | b4 | INSERT | “施設利用” テーブルの追加行のキー値で “宿泊” テーブルを検索し，該当行がない場合に “宿泊” テーブルに同じ行を挿入する。 | | | UPDATE | “施設利用” テーブルの変更行のキー値で “宿泊” テーブルを検索し，該当行があり，かつ，コ 場合に，“宿泊” テーブルの該当行を更新する。 | 注記 網掛け部分は表示していない。
設問一覧 ＆ 解答・演習
まずは解答を作成してみましょう。「解答例を見る」をクリックするとIPA公式解答例と採点基準が開きます。
設問1 (1)記号・選択式
表 3 中の a 〜 f に入れる “○”， “×” を答えよ。また，表 3 中の ア， イ に入れる一つ又は複数の適切なエンティティタイプ名を答えよ。
【選択肢】 クリックして選択（複数選択可）
選択中の解答:未選択（上の選択肢ボタンをクリックしてください）
設問1 (2)記号・選択式
表 4 中の ウ 〜 キ に入れる適切な字句を答えよ。
※直接入力してください
設問1 (3)記号・選択式
表 8 中の太枠内に適切な数値を入れ，表を完成させよ。
※直接入力してください
設問2 (1)記述問題
制限字数：50 字以内
本文中の下線①で，調査のために表 6 中の S3 をどのように変更したらよいか。変更内容を 50 字以内で具体的に答えよ。
0 / 50 文字
設問2 (2)記述問題
制限字数：50 字以内
本文中の下線②で，累計予約可能客室数が実際よりも小さくなった理由を 50 字以内で具体的に答えよ。
0 / 50 文字
設問2 (3)記号・選択式
本文中の下線③で，表 6 中の S2 において，“客室状況” テーブルに替えてほかのテーブルから累計予約可能客室数を求めることにした。そのテーブル名を答えよ。
※直接入力してください
設問2 (4)記述問題
制限字数：40 字以内
本文中の下線④について，(a) どのようなデータの不整合が発生するか，(b) どのような無駄な作業が発生するか，それぞれ 40 字以内で具体的に答えよ。
0 / 40 文字
設問3 (1)記号・選択式
図 5 中の ク, ケ に入れる一つ又は複数の列名を答えよ。なお，ク, ケ に入れる列が主キーを構成する場合，主キーを表す実線の下線を付けること。
※直接入力してください
設問3 (2)記述問題
制限字数：40 字以内
表 15 中の更新可能ビュー方式の手順の実施に際して，AP のアクセスを停止する必要がある。AP のアクセスを停止するのはどの手順の前か。表 15 中の手順番号を答えよ。また，AP のアクセスを停止する理由を 40 字以内で具体的に答えよ。
0 / 40 文字
設問3 (3)記述問題
制限字数：50 字以内
表 15 中のトリガー同期方式において，AP のアクセスを停止せずにリリースを行う場合，表 15 中の手順では “宿泊” テーブルと “施設利用” テーブルとが同期した状態となるが，手順 b2，b3 の順序を逆転させると，差異が発生する場合がある。それはどのような場合か。50 字以内で具体的に答えよ。
0 / 50 文字
設問3 (4)記述問題
制限字数：30 字以内
表 16 中の コ の条件がないと問題が発生する。どのような問題が発生するか。20 字以内で具体的に答えよ。また，この問題を回避するために コ に入れる適切な条件を 30 字以内で具体的に答えよ。
0 / 30 文字
午後II長文記述の得点力を引き上げる定番対策書Amazon・楽天 公式提携
PR
IPA 厳選対策書徹底攻略 データベーススペシャリスト教科書 令和8年度株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
DB徹底攻略株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
徹底攻略 データベーススペシャリスト教科書 令和8年度
💡 推薦ポイント:午後I・午後IIの概念データモデル穴埋めや排他制御・性能設計の記述問題で確実に合格点を勝ち取る実践テクニックが満載です。
¥3,278（税込）
※本リンク先はプロモーション（アフィリエイト広告）が含まれています
【出題趣旨】IPA公式発表
【採点講評】IPA公式発表
本問に関連するデータベーススペシャリスト重要用語解説
本番形式・制限時間付きで演習する
令和4年 秋期 午後II（2問中1問選択・120分）を実戦演習モードで通し解答し、総合採点を受けられます。