DBデータベーススペシャリスト試験午後Ⅱ

令和4年度秋期データベーススペシャリスト午後Ⅱ 問1

事例を読んで設問の答えを考えてから、解説のポイントとIPA公式の解答例・採点講評で確認しましょう。

問題

問1 データベースの実装・運用に関する次の記述を読んで，設問に答えよ。 D社は，全国でホテル，貸別荘などの施設を運営しており，予約管理，チェックイン及びチェックアウトに関する業務に，5年前に構築した宿泊管理システムを使用している。データベーススペシャリストのBさんは，企画部門からマーケティング用の分析データ（以下，分析データという）の提供依頼を受けてその収集に着手した。〔分析データ収集〕 1. 分析データ提供依頼企画部門からの分析データ提供依頼の例を表1に示す。表1中の指定期間には分析対象とする期間の開始年月日及び終了年月日を指定する。表1 分析データ提供依頼の例 2. 宿泊管理業務の概要宿泊管理システムの概念データモデルを図1に，関係スキーマを図2に，主な属性の意味・制約を表2に示す。宿泊管理システムでは，図2中の関係“予約”，“会員予約”及び“非会員予約”を概念データモデル上のスーパータイプである“予約”にまとめて一つのテーブルとして実装している。 Bさんは，宿泊管理業務への理解を深めるために，図1，図2，表2を参照して，表3の業務ルール整理表を作成した。表3では，Bさんが想定する業務ルールの例が，図1，図2，表2に反映されている業務ルールと一致しているか否かを判定し，一致欄に“○”（一致）又は“×”（不一致）を記入する。エンティティタイプ欄には，判定時に参照する一つ又は複数のエンティティタイプ名を記入する。リレーションシップを表す線及び対応関係にゼロを含むか否かの区別によって適否を判定する場合には，リレーションシップの両端のエンティティタイプを参照する。図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)で求めた施設コード，価格区分コードについて，S1，S2の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】に入れる“○”，“×”を答えよ。また，表3中の【ア】，【イ】に入れる一つ又は複数の適切なエンティティタイプ名を答えよ。 (2) 表4中の【ウ】～【キ】に入れる適切な字句を答えよ。 (3) 表8中の太枠内に適切な数値を入れ，表を完成させよ。設問2 〔異常値の調査・対応〕について答えよ。 (1) 本文中の下線①で，調査のために表6中のS3をどのように変更したらよいか。変更内容を50字以内で具体的に答えよ。 (2) 本文中の下線②で，累計予約可能客室数が実際よりも小さくなった理由を50字以内で具体的に答えよ。 (3) 本文中の下線③で，表6中のS2において，“客室状況”テーブルに替えてほかのテーブルから累計予約可能客室数を求めることにした。そのテーブル名を答えよ。 (4) 本文中の下線④について，(a)どのようなデータの不整合が発生するか，(b)どのような無駄な作業が発生するか，それぞれ40字以内で具体的に答えよ。設問3 〔宿泊管理システムの変更〕について答えよ。 (1) 図5中の【ク】，【ケ】に入れる一つ又は複数の列名を答えよ。なお，【ク】，【ケ】に入れる列が主キーを構成する場合，主キーを表す実線の下線を付けること。 (2) 表15中の更新可能ビュー方式の手順の実施に際して，APのアクセスを停止する必要がある。APのアクセスを停止するのはどの手順の前か。表15中の手順番号を答えよ。また，APのアクセスを停止する理由を40字以内で具体的に答えよ。 (3) 表15中のトリガー同期方式において，APのアクセスを停止せずにリリースを行う場合，表15中の手順では“宿泊”テーブルと“施設利用”テーブルとが同期した状態となるが，手順[b2]，[b3]の順序を逆転させると，差異が発生する場合がある。それはどのような場合か。50字以内で具体的に答えよ。 (4) 表16中の【コ】の条件がないと問題が発生する。どのような問題が発生するか。20字以内で具体的に答えよ。また，この問題を回避するために【コ】に入れる適切な条件を30字以内で具体的に答えよ。

表1 分析データ提供依頼の例。企画部門からの三つの依頼を示す表。施設別のリピート率，会員の請求金額による5等分分類，標準単価と客室稼働率の抽出を求めている。

図1 宿泊管理システムの概念データモデル。マスター領域とトランザクション領域に分け，施設，客室タイプ，客室，予約，宿泊，請求などのエンティティタイプと対応関係を示す。

図2 宿泊管理システムの関係スキーマ（一部省略）。施設，客室タイプ，価格区分，予約，宿泊，請求などの関係スキーマを列挙した図。属性名と実線・破線の下線によるキーの指定を示す。

表2 主な属性の意味・制約。施設コード，施設区分，客室タイプコード，標準単価，予約可否，予約可能数，予約状態など，主な属性の意味と設定値の制約を示す。

表3 業務ルール整理表（未完成）。八つの業務ルールの例について，参照するエンティティタイプとモデルとの一致を整理する表。一致欄a～fとエンティティタイプ欄ア・イが空欄。

表4 依頼1の分析データ抽出に用いる問合せ（未完成）。累計新規会員数を求めるR1，リピート会員数を求めるR2，リピート率を求めるR3の処理概要とSQL文を示す。SQL文にウ～キの空欄がある。

表5 依頼2の分析データ抽出に用いる問合せ。会員別の指定期間内の請求金額を集計するT1と，NTILE関数で合計利用金額の上位から5等分し階級番号を求めるT2の処理概要とSQL文を示す。

表6 依頼3の分析データ抽出に用いる問合せ。累計稼働客室数を求めるS1，累計予約可能客室数を求めるS2，標準単価と客室稼働率を求めるS3について，処理概要とSQL文を示す。

表7 T2の試験で使用するT1のデータ。T2の試験に使う会員番号100～109の十件のデータを示す表。各会員の合計利用金額を，会員番号と対応させて掲載している。

表8 T2の試験の予想値（未完成）。会員番号100～109に対応する階級番号の予想値を記入する表。会員100の階級番号1が示され，会員101～109の欄が太枠内の空欄となっている。

図3 標準単価と客室稼働率の関係（散布図）。横軸に標準単価，縦軸に客室稼働率を取った散布図。標準単価が高くなるにつれて稼働率が低くなる点群と，稼働率120%付近の異常値が示される。

表9 (2)のS1で得た結果行。施設コード103，価格区分コードC4について，客室タイプコード71～74の累計稼働客室数がそれぞれ5，10，14，7であることを示す。

表10 (2)で得たS2の結果行。調査手順(2)で変更したS2の結果を示す表。施設コード103，価格区分コードC4，客室タイプコード71の累計予約可能客室数30が掲載される。

表11 (3)で得た“客室状況”テーブルの行（一部省略）。施設コード103，客室番号1050の客室状況を示す表。2022-09-01，2022-09-02，2022-09-30の予約可否がYで，中間の行を省略している。

表12 (3)で得た“客室”テーブルの行（一部省略）。調査手順(3)で抽出した客室テーブルの行を示す。施設コード103，客室タイプコード71，客室番号1050の対応と，省略された属性列を掲載している。

表13 (3)で得た“施設客室タイプ”テーブルの行。施設コード103の施設客室タイプを示す表。客室タイプコード71，72，73，74の四つが，いずれも価格区分コードC4に対応している。

表14 (3)で得た“客室タイプ”テーブルの行（一部省略）。客室タイプ71～74が貸会議室タイプAの9時～11時，11時～13時，13時～15時，15時～17時に対応し，定員はいずれも25であることを示す。

図4 貸会議室予約業務追加後のトランザクション領域の概念データモデル。予約を宿泊予約と貸会議室予約，施設利用を宿泊と貸会議室利用に分け，貸会議室予約明細を追加したモデル。請求やオプションとの対応関係も示す。

図5 追加するテーブルのテーブル構造（未完成）。追加する貸出時間帯，貸会議室在庫，貸会議室予約明細のテーブル構造を示す図。貸出時間帯の属性を掲載し，残る二つの属性欄はク・ケの空欄となっている。

表15 更新可能ビュー方式，トリガー同期方式の手順。テーブル名変更の二方式について，移行期間前・中・後の手順を比較する表。手順番号a1～d2と，ISOLATIONレベルがREAD COMMITTEDである旨の注記を含む。

表16 表15中の手順[b2]，[b4]のトリガーの処理内容（未完成）。宿泊と施設利用の両テーブルを同期するトリガーのINSERT・UPDATE処理を示す表。b2の更新条件コが空欄で，b4の条件は網掛けで表示されていない。

解き方のヒントを見る

扱う情報の範囲を意識して考える

扱う情報の範囲に注目し、検討の途中でその範囲が変わっていないかを確かめましょう。記述や数値を個別に判断する前に、何を対象として考えているのかを自分の言葉で整理すると、論点を保ちやすくなります。

この問題で問われていること

検討対象に含める情報と、いったん対象の外に置く情報との境界を意識してください。考えを進める際には、その境界を動かしていないかを振り返り、対象範囲が曖昧な箇所だけを確認することに集中しましょう。

視点

情報の範囲とは、今の検討で何を対象としているかという区切りです。同じ言葉が使われていても、扱う範囲まで同じとは限らないため、結論を急がずに対象を確かめる視点を持ちましょう。

確認ポイント

検討対象の範囲を言葉にできるか
同じ対象範囲を保って考えているか
対象範囲が曖昧な箇所はどこか

正解と解説を見る

解説

宿泊管理のデータモデルから業務ルールを読み取り、会員数や稼働率を求めるSQLを理解する問題です。貸会議室への転用で集計対象が不足する原因を追い、時間帯を扱うテーブル設計と、APを段階的に改定する際の移行・同期の問題まで説明します。

問題の全体像

D社は、5年前に構築した宿泊管理システムから、リピート率、会員の利用金額別分類、客室稼働率を抽出しようとしています。調査すると、一部施設が客室タイプに時間帯を組み込んで貸会議室を管理しており、当初のデータモデルと実際の運用がずれていました。出題趣旨では、論理モデルの理解・検証、問合せの設計・試験、異常データの調査・修正、継続的な改善の実践を問うとしています。設問1ではモデルとSQL、設問2では異常値の原因、設問3では業務に合うテーブル設計と移行手順を読み解きます。

設問1(1)(a)

公式解答例では「×」です。表3の項番2は「全施設共通のプランがある」という想定です。図2のプランの主キーには施設コードとプランコードが含まれ、図1でもプランは施設に属しています。プランコードだけで全施設共通のプランを識別する構造ではないため、モデルに反映された業務ルールとは一致しません。

設問1(1)(b)

公式解答例では「○」です。図2の会員には氏名、住所などが登録され、会員予約には会員番号が含まれています。図1の会員と会員予約の対応関係からも、予約時に会員番号で登録済みの会員を参照できます。したがって、会員番号を提示すれば氏名や住所などの再提示を省略できるという想定と一致します。

設問1(1)(c)

公式解答例では「×」です。表3の項番4は、同じ会員が施設、プラン、客室タイプ、利用開始年月日の全てが同じ予約を複数取れないという想定です。しかし、図2の予約は施設コードと予約番号で識別され、これらの条件の組合せを一意にする制約は示されていません。同じ条件でも予約番号を変えた複数の予約を表せるため、想定とは一致しません。

設問1(1)(d)

公式解答例では「○」です。図1の予約と宿泊の対応関係に着目すると、各宿泊は一つの予約に対応し、予約のない宿泊は表せません。図2でも宿泊には予約番号が含まれます。飛び込み客でも当日の予約を登録してから宿泊を受け付けるという表3の業務ルールと一致します。

設問1(1)(e)

公式解答例では「×」です。図2の予約には客室タイプコードや客室数がありますが、個々の客室を指定する客室番号はありません。また、客室在庫も客室タイプ、禁煙喫煙区分、年月日ごとに数を管理する構造です。この予約・在庫の情報では、連泊中に同じ客室を使うように個別の客室を割り当てることは表せないため、表3の想定とは一致しません。

設問1(1)(f)

公式解答例では「○」です。図1では各予約が一つのプラン明細に対応し、図2の予約にはプランコードと客室タイプコードが含まれています。そのため予約時にはプランと客室タイプを指定します。また、予約には客室数があり、同じ客室タイプの複数室を一つの予約で扱えます。表3の項番7の二つの条件を満たしています。

設問1(1)(ア)

公式解答例では「プラン明細，予約」です。表3の項番7は、プランと客室タイプの必須指定、および一つの予約で複数室を扱えることの判定です。前者は図1のプラン明細と予約の対応関係、後者は図2の予約の客室数に着目します。本文には対応関係を用いる場合は両端のエンティティタイプを参照するとあるので、プラン明細と予約の両方を挙げます。

設問1(1)(イ)

公式解答例では「宿泊，宿泊者」です。表3の項番8の「1名以上」は、図1の宿泊と宿泊者の対応関係がゼロを許すかどうかから判断します。各宿泊には1件以上の宿泊者情報が必要な対応関係となっています。対応関係の両端を参照するという本文の指示に従い、宿泊と宿泊者を挙げます。

設問1(2)(ウ)

公式解答例では「COUNT(DISTINCT B.会員番号)」、または「COUNT(DISTINCT 会員番号)」です。表1の依頼1で求めるのは宿泊の件数ではなく会員の人数です。宿泊と予約の結合結果には、同じ会員の複数回の宿泊や複数室の利用に対応する行が含まれます。そこで会員番号の重複をDISTINCTで除き、COUNTで人数を数えます。R1とR2の両方でこの考え方を使います。採点講評でも、この空欄の正答率が低かったとされています。

設問1(2)(エ)

公式解答例では「EXISTS」です。R2では指定期間内の宿泊について、それより前の宿泊が存在する会員を抽出します。副問合せは過去の宿泊を探すためのもので、必要なのは該当行が一つでも存在することです。EXISTSを使うと、過去の宿泊回数にかかわらず存在の有無で判定できます。

設問1(2)(オ)

公式解答例では「B.会員番号 = D.会員番号」です。外側の予約Bと、副問合せで参照する予約Dが同じ会員の予約であることを確認します。副問合せには既に施設コードの一致条件があるため、会員番号も一致させることで、同じ施設を利用した同じ会員の過去の宿泊を探せます。別の会員の宿泊をリピートの根拠にしないための条件です。

設問1(2)(カ)

公式解答例では「A.チェックイン年月日 > C.チェックイン年月日」です。外側の宿泊Aは指定期間内の宿泊、副問合せの宿泊Cは過去の宿泊です。Cのチェックイン年月日をAより前に限定することで、指定期間内の宿泊が2回目以降であることを確認できます。過去の宿泊Cを指定期間より前に限定する条件ではなく、Aより前に限定する点に注意します。公式解答例では、オとカの条件は順不同です。

設問1(2)(キ)

公式解答例では「COALESCE(リピート会員数、0) / 累計新規会員数」です。表1の定義に従い、リピート会員数を累計新規会員数で割ります。R3はR1を基準にR2をLEFT JOINするので、リピート会員がいない施設ではR2側の値がNULLになります。COALESCEでNULLを0に置き換えると、要求されたリピート率0を求められます。百分率にする100倍は空欄の外にあります。採点講評では、この空欄も正答率が低かったとされています。

設問1(3)

表5のT2は、合計利用金額の降順に並べた行をNTILE(5)で5群に分けます。表7は10人なので各群は2人です。降順に、100・103が階級1、101・109が階級2、104・108が階級3、107・105が階級4、102・106が階級5となります。公式解答例の太枠内は、会員101から109の順に「2，5，1，3，4，5，4，3，2」です。NTILEは行を分割する関数であり、金額の範囲を等幅に分割するものではありません。

設問2(1)

下線①では、S3から客室稼働率100％超の行だけを抽出します。表6の式は、累計稼働客室数を累計予約可能客室数で割って100倍しています。そのため、分子が分母を超える行をWHERE句で選べば異常値を絞り込めます。公式解答例では、この比較条件の追加、または稼働率100％超の条件の追加を示しています。50字以内の答案では「累計稼働客室数が累計予約可能客室数を超える条件のWHERE句を追加する。」と、比較対象と変更箇所を押さえます。

設問2(2)

表12では客室1050が客室タイプ71だけに対応しますが、表13・表14では同じ会議室の時間帯が客室タイプ71～74として登録されています。S2は客室状況から客室を経由して客室タイプをたどるため、客室に対応しない72～74の予約可能数を集計できません。表9の稼働数は合計36、表10の予約可能数は30なので、稼働率が120％になります。公式解答例の要点を50字以内で示すと「客室タイプ72～74に対応する客室数が累計予約可能客室数に含まれないから。」です。採点講評では、SQLの構文ではなく、テーブル構造とデータの意味の相違を説明することが求められています。

設問2(3)

公式解答例では「客室在庫」です。ヒアリング結果では、時間帯を組み込んだ客室タイプごとに予約可能数を設定しています。図2の客室在庫には、施設コード、客室タイプコード、年月日などと予約可能数があるため、個々の客室との対応を経由せず、時間帯に相当する各客室タイプの予約可能数を集計できます。集計対象は割当済数や残数ではなく、稼働率の分母となる予約可能数です。

設問2(4)(a)

図2では定員を客室タイプに持たせています。表14のように同じ貸会議室を時間帯ごとの複数の客室タイプで表すと、本来一つである会議室の定員を複数箇所で管理することになります。定員変更時に一部だけ更新すると、時間帯によって異なる定員が登録されます。公式解答例の要点を40字以内で示すと「同じ貸会議室の異なる客室タイプに異なる定員が設定される。」です。

設問2(4)(b)

ヒアリング結果では、貸会議室利用を宿泊として登録しています。一方、表3の項番8と図1では、宿泊ごとに1名以上の宿泊者情報が必要です。会議室利用には宿泊者がいないのに、この宿泊業務のルールを満たすための登録が発生します。公式解答例の要点を40字以内で示すと「宿泊者がいないのに、1名以上の宿泊者を記録する作業。」です。採点講評では、定員確認など通常の業務ではなく、宿泊のルールを転用して生じる無駄を指摘することが求められています。

設問3(1)(ク)

公式解答例では「施設コード，客室タイプコード，年月日，時間帯コード，予約可能数，割当済数」で、主キーは最初の4列です。貸会議室を表す客室タイプと貸出時間帯を分け、施設・客室タイプ・日付・時間帯ごとに在庫を管理します。予約可能数と割当済数はその単位で管理する値です。答案では「施設コード，客室タイプコード，年月日，時間帯コード，予約可能数，割当済数」と主キーに実線の下線を付けます。貸会議室は全て禁煙なので、禁煙喫煙区分は含めません。採点講評では、必要な列の欠落と主キーの誤りが指摘されています。

設問3(1)(ケ)

公式解答例では「施設コード，予約番号，時間帯コード」で、3列全てが主キーです。ヒアリング結果の「1回の予約で1室だけ」と「複数時間帯を1回の予約で登録したい」を合わせて考えます。施設コードと予約番号で予約を特定し、その予約に含まれる各時間帯を明細として登録します。客室タイプや利用日は既存の予約側で扱えるため、明細では時間帯コードを加えます。答案では「施設コード，予約番号，時間帯コード」と下線を付けます。

設問3(2)(手順番号)

公式解答例では「c1」です。表15のc1は施設利用ビューの削除、c2は宿泊テーブルを施設利用テーブルに改名する手順です。c1を実行すると、新APが参照する施設利用という名前のビューがなくなり、c2が完了するまで同名のテーブルもありません。したがって、c1の前にAPのアクセスを停止する必要があります。

設問3(2)(理由)

移行期間中、新APは施設利用ビューを通じて宿泊テーブルにアクセスします。c1でビューを削除してからc2でテーブルを改名するまでの間は、施設利用というアクセス先が存在しません。その間に新APがアクセスすると異常終了します。公式解答例の要点を40字以内で示すと「新APが施設利用テーブルにアクセスすると異常終了するから。」です。停止理由は、ビュー削除と改名の間にアクセス先がなくなることです。

設問3(3)

表15では、b2で宿泊から施設利用へ変更を反映するトリガーを作成してから、b3で既存行を複写します。順序を逆にすると、複写中には同期用トリガーがありません。既に複写した宿泊の行がその後に更新されても、施設利用側には反映されず、複写対象も施設利用に存在しない行だけなので取り直されません。公式解答例に沿う50字以内の答案は「施設利用テーブルへの複写が済んだ宿泊テーブルの行が更新された場合。」です。未複写行ではなく、複写済みの行への更新を特定することが要点です。

設問3(4)(問題)

公式解答例では「処理の無限ループが発生する。」です。RDBMSの仕様には、AFTERトリガーが連鎖して実行されるとあります。宿泊の更新を施設利用へ反映すると、その更新が逆方向のトリガーを起動して宿泊を更新します。値が変わらない更新でも反映を続けると、相互の更新が繰り返されます。20字以内では「処理の無限ループが発生する。」と、発生する問題を端的に示します。

設問3(4)(コ)

公式解答例では「“宿泊”テーブルの行の旧値と新値が一致しない。」です。RDBMSの仕様では、トリガー内で更新前後の行を参照できます。旧値と新値が異なる場合だけ施設利用へ反映すれば、逆方向のトリガーから同じ値が戻った際には、宿泊側の実質的な変更がないため連鎖を止められます。30字以内では「宿泊テーブルの行の旧値と新値が一致しない。」と記します。条件は、反映先の存在確認ではなく、更新元の行に実際の値の変化があることです。

学習のポイント

主キー・必須対応・集計単位を業務の意味と結び付け、DISTINCT、EXISTS、NULL処理、時間帯別のキー設計、移行中の更新とトリガー連鎖を理解することが重点です。

出典：独立行政法人情報処理推進機構（IPA）令和4年度秋期データベーススペシャリスト試験午後Ⅱ 問1（公式資料：問題冊子（PDF）／解答例／採点講評）

令和4年度秋期 データベーススペシャリスト 午後Ⅱ 問1

問題