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

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

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

問題

問2　データベースの実装に関する次の記述を読んで，設問に答えよ。専門商社のB社では，見積業務で利用するシステム（以下，見積システムという）の，マスター保守に伴う調査業務を改善中である。また，見積システムのパブリッククラウドへの移行を計画している。〔パブリッククラウドが提供するサービスの主な仕様〕 1.　オブジェクトストレージオブジェクトストレージには，任意のファイルを保存することができる。RDBMSとは独立して稼働し，RDBMSの障害時にも影響を受けずに，ファイルにアクセスすることができる。 2.　RDBMS PaaSとして提供されるRDBMSは，インスタンスごとに割り当てられた仮想マシンで稼働する。 (1) ログログはログファイルに記録する。ログファイルの切替え時に，切替え前に使用していたログファイル（以下，アーカイブログという）を，オブジェクトストレージに保存する。ログ切替えの時間間隔は，任意に設定することができる。 (2) バックアップ ①　データベース全体のフルバックアップを，オブジェクトストレージに保存する。バックアップは，データベースを停止して，オフラインで取得する。バックアップを取るタイミングは，任意に設定することができる。 ②　オブジェクトストレージに保存したフルバックアップとアーカイブログを使って，データベースを回復することができる。 (3) レプリケーションログを使って，RDBMSのデータをほかのRDBMSに複製する。複製元のテーブルに対する変更操作（挿入・更新・削除）を複製先のテーブルに自動的に反映する。レプリケーションには，同期型と非同期型がある。 ①　同期型では，複製先でログをディスクに出力した後，複製元のトランザクションがコミットされる。 ②　非同期型では，複製先へのログの到達を待たずに，複製元のトランザクションがコミットされる。 (4) トリガーテーブルに対する変更操作（挿入・更新・削除）を契機に，あらかじめ定義した処理を実行する。 ①　実行タイミングを定義することができる。BEFOREトリガーは，テーブルに対する変更操作の前に実行され，更新中又は挿入中の値を実際の反映前に修正することができる。AFTERトリガーは，変更操作の後に実行され，ほかのテーブルに対する変更操作を行うことができる。 ②　トリガーを実行する契機となった変更操作を行う前と後の行を参照することができる。参照するには，操作前と操作後の行に対する相関名をそれぞれ定義し，相関名で列名を修飾する。〔見積システムの概要〕 1.　テーブル主なテーブルのテーブル構造を図1に示す。図1　主なテーブルのテーブル構造（一部省略） 2.　仕入先への見積依頼業務 (1) B社の社員は，顧客からの引き合いを受けて，仕入先への見積依頼を入力する。見積依頼番号を採番し，“見積依頼”，“見積依頼明細”テーブルに見積依頼の内容を登録する。 (2) 仕入先に見積りを依頼し，回答を受け取る。 (3) 仕入先からの回答を入力する。対応する見積依頼の見積依頼番号を参照し，“見積回答”，“見積回答明細”テーブルに見積回答の内容を登録する。商品のモデル名，定価が変更されたことが分かることがある。この場合，当該商品は，“見積回答明細”テーブルに変更後の内容を登録する。ただし，“商品”テーブルへの反映は後日行う。〔“商品”テーブルの履歴管理〕モデル名又は定価のいずれかが変更されたが，変更が“商品”テーブルへ反映されていない商品を調べるため，図2に示すSQL文を定期的に実行している。図2　商品の変更を調べるSQL文（未完成） 1.　“商品”テーブルの設計変更 “商品”テーブルを更新すると，過去の属性情報は失われてしまう。そこで，商品属性情報の変更を履歴として保存するために，“商品”テーブルの設計変更を行うことにした。ただし，既存のアプリケーションプログラムには，極力影響を与えないようにする必要がある。表1に示す2案を検討した結果，案2を採用した。表1　“商品”テーブルの設計変更案案2の実装に当たり，“商品”テーブルへの列の追加，“商品履歴”テーブルの作成，及び主キーの追加を表2のSQL1に示すSQL文で行った。また，同一の適用開始日に同一の商品を複数回更新することはない前提で，“商品”テーブルの更新時に行う追加の処理を，表2のSQL2に示すトリガーで実装した。表2　“商品”テーブルを変更するSQL文及びトリガーを定義するSQL文（未完成） 2.　データ移行 “見積回答”，“見積回答明細”テーブルから“商品”，“商品履歴”テーブルへデータを移行するため，商品のモデル名又は定価のいずれかが変更されたことの履歴を，図3のSQL文で調べた。“見積回答”，“見積回答明細”テーブルの内容を表3，表4に示す。SQL文の結果を表5に示す。表5の内容を基に，“商品”テーブルを更新，又は“商品履歴”テーブルへ挿入することでデータを移行した。移行前の“商品”テーブルの状態によらず，変更があった全商品を更新した。また，表2のSQL2に示すトリガーは未定義の状態で行った。図3　商品の変更履歴を調べるSQL文（未完成）表3　“見積回答”テーブルの内容表4　“見積回答明細”テーブルの内容表5　SQL文の結果（未完成）〔基盤設計〕 1.　RPO，RTOの見積り見積システムをパブリッククラウドに移行した場合の，RDBMSのディスク障害時のRPO及びRTOを，次のように見積もった。 (1) 利用するパブリッククラウドの仕様に基づいて，データベースのフルバックアップは1日に1回取得し，ログの切替えは5分に1回行い，回復時にはオブジェクトストレージに保存したフルバックアップとアーカイブログを使って回復する，という前提で見積もる。 (2) RPOは，障害発生時に失われる［ア］に依存するので，最大［イ］分とみなせる。 (3) RTOのうち，データベースの回復に掛かる時間は，フルバックアップからのリストア時間と，ログを適用するのに掛かる時間の合計である。 (4) フルバックアップからのリストア時間は，データベース容量が180Gバイト，リストア時のディスク転送速度を100Mバイト／秒と仮定すると［ウ］秒である。ここで，1Gバイトは10⁹バイト，1Mバイトは10⁶バイトとする。 (5) ログを適用する期間が最大になるのは，フルバックアップ取得後の経過時間が最大になる24時間である。ログが毎秒10ページ出力されると仮定すると，適用するログの量は最大［エ］ページである。ログを適用するのに掛かる時間は，バッファヒット率を0％，同期入出力時間がページ当たり2ミリ秒と仮定すると最大［オ］秒である。 2.　参照専用インスタンス商品の変更履歴を調べるために実行するSQL文の負荷が大きく，見積システムへの影響が懸念された。そこで，影響を最小化するために，参照専用インスタンスを本番インスタンスとは別に作成し，調査は参照専用インスタンスで行うことにした。また，全テーブルについて，本番インスタンスから参照専用インスタンスへ，非同期型のレプリケーションを行うことにした。 3.　参照専用インスタンスへのフェイルオーバーによる業務継続 RPO及びRTOを短くするために，本番インスタンスが障害になった場合，参照専用インスタンスにフェイルオーバーして，参照専用インスタンスを使用して業務を継続できるかを検討した。検討の結果，非同期型のレプリケーションを行う前提だと，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性があることが分かった。設問1　〔“商品”テーブルの履歴管理〕について答えよ。 (1) 図2中の［a］，［b］に入れる適切な字句を答えよ。 (2) “商品”テーブルの設計変更について，表1中の案1を採用した場合，ほかのどのテーブルの，どの制約を変更する必要があるか。テーブル名と制約を全て答えよ。 (3) 表2中の［c］～［h］に入れる適切な字句を答えよ。 (4) 表5中の［i］～［k］に入れる適切な字句を答えよ。 (5) 表5のうち，“商品”テーブルへの更新行，“商品履歴”テーブルへの挿入行に当たる行を，それぞれ行番号で全て答えよ。設問2　〔基盤設計〕について答えよ。 (1) 本文中の［ア］～［オ］に入れる適切な字句又は数値を答えよ。 (2) “2. 参照専用インスタンス”について，参照専用インスタンスへのレプリケーションを非同期型にすると，見積システムへの影響を最小化できるのはなぜか。レプリケーションの仕様に基づいて，30字以内で答えよ。 (3) “3. 参照専用インスタンスへのフェイルオーバーによる業務継続”について，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性がある。どのような場合か。レプリケーションの仕様に基づいて，30字以内で答えよ。

図1　主なテーブルのテーブル構造（一部省略）。仕入先，社員，商品，見積依頼，見積依頼明細，見積回答，見積回答明細の7テーブルの構造。主キーは実線下線，外部キーは破線下線で示され，見積回答明細はモデル名と定価を持つ。

図2　商品の変更を調べるSQL文（未完成）。見積回答明細A，見積回答B，商品Cを結合し，見積回答日が更新日より後で，モデル名又は定価が商品と異なる行の新旧モデル名・定価を取得するSQL文。空欄a（結合列）とb（論理演算子）がある。

表1　“商品”テーブルの設計変更案。案1は商品テーブルに適用開始日・適用終了日を追加し，主キーを商品コードと適用開始日とする案。案2は商品テーブルに列を追加し，同じ列構成の商品履歴テーブルを別に設ける案。注記で履歴の主キー非表示と適用日の意味を説明。

表2　“商品”テーブルを変更するSQL文及びトリガーを定義するSQL文（未完成）。SQL1は商品テーブルへの適用開始日・適用終了日列の追加，商品履歴テーブルの作成と主キー（空欄c，d）の追加を行う。SQL2はトリガー1（空欄e）で適用開始日を補完し，トリガー2（空欄f）で更新前の行（空欄g，h）を商品履歴に挿入する。注記1〜3付き。

図3　商品の変更履歴を調べるSQL文（未完成）。WITH句のQ1でLAG関数を使い前行のモデル名・定価を求め，Q2で変更行（空欄b）を抽出し，ROW_NUMBERで行番号を付け，LEADとADD_DAYSで適用終了日を求めるSQL文。注記1〜4でADD_DAYS，LAG，LEADを説明。

表3　“見積回答”テーブルの内容。見積依頼番号，仕入先コード，社員コード，見積回答日の4行。2019000101(2019-04-01)，2020001201(2020-09-01)，2022000201(2022-05-01)，2022001201(2022-09-01)。

表4　“見積回答明細”テーブルの内容。見積依頼番号，明細番号，商品コード，数量，仕入単価，モデル名，定価の6行。商品1はM1→M1-1→M1-2（定価1000→1100），商品2はM2→M2-1，商品3はM3の見積回答明細を示す。

表5　SQL文の結果（未完成）。行番号，商品コード，定価，モデル名，適用開始日，適用終了日の6行。商品1のM1，M1-1，M1-2，商品2のM2，M2-1，商品3のM3。空欄i，j，kがあり，網掛け部分は表示していない旨の注記付き。

解き方のヒントを見る

要件と前提条件を対応づけて読む

設問が求めている要件と、その前提として示されている条件を一つずつ対応づけることに観点を絞ります。どの条件が判断の根拠になるのかを先に明確にしてから、各候補がそれを満たすかを落ち着いて確かめましょう。

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

この問題では、状況説明に含まれる要件や制約のうち、どれが判断を左右するかを見極める力が問われていると考えられます。記述された条件を読み落とさず、根拠となる箇所を特定する姿勢が大切です。

視点

データベースの設計や運用の判断では、何を実現したいのか（要件）と、どのような状況で運用されるのか（前提）の組合せによって適否が変わります。用語の一般的な印象だけで判断せず、示された条件に照らして妥当かどうかを考えることが基本です。

確認ポイント

判断を求められている要件が何かを、自分の言葉で言い直せるか
その要件に関係する前提条件や制約が、どこに書かれているかを特定できたか
用語の印象ではなく、示された条件に照らして判断の根拠を説明できるか

正解と解説を見る

解説

見積システムのマスターである“商品”テーブルに変更履歴を保存する設計変更と、クラウド移行時の基盤設計を題材にした問題です。前半は、結合条件、主キーと外部キーの関係、BEFORE/AFTERトリガーと相関名、LAG/LEADによる適用期間の算出、最新の行と過去の行の振り分けを問います。後半は、ログ切替え間隔に基づくRPO、リストア時間とログ適用時間によるRTO、同期型と非同期型のレプリケーションの特性を仕様から読み取る力を問います。

問題の全体像

専門商社B社の見積システムでは、仕入先の見積回答で判明したモデル名や定価の変更を“見積回答明細”に登録し、“商品”テーブルには後日反映しています。今回、商品属性の変更履歴を残すため、既存のアプリケーションへの影響が少ない案2（“商品”はそのまま残し、“商品履歴”を追加する案）を採用し、トリガーとデータ移行を実装します。後半ではクラウド移行を想定して、ディスク障害時のRPO/RTOの見積りと、参照専用インスタンスへの非同期型レプリケーションの考慮点を扱います。出題趣旨では、マスターテーブルの設計変更、トリガーの実装、列の値の変更を前提としたデータ移行、RPO/RTOの見積り、レプリケーションの考慮点についての理解を問うとしています。

設問1(1)(a)

図2は、見積回答明細Aと商品Cを突き合わせ、新旧のモデル名と定価を比べるSQL文です。図1を見ると、両テーブルに共通し、“見積回答明細”から“商品”を参照している列は商品コードです。したがって、公式解答例のとおり結合条件はA.商品コード = C.商品コードとなり、aには「商品コード」が入ります。

設問1(1)(b)

本文には「モデル名又は定価のいずれかが変更されたが」とあります。モデル名だけが違う場合も、定価だけが違う場合も抽出する必要があるため、二つの不一致条件は論理和で結びます。公式解答例ではbは「OR」です。図3でも同じ字句を使い、前の行からモデル名又は定価が変わった行を抽出しています。

設問1(2)(テーブル名)

案1では“商品”の主キーが{商品コード、適用開始日}の複合キーに変わり、同じ商品コードの行が複数存在するようになります。図1の中で商品コードを外部キー（破線の下線）として持ち、“商品”を参照しているのは“見積依頼明細”と“見積回答明細”です。公式解答例でも、この2テーブルを挙げています。両方を答える必要があります。

設問1(2)(制約)

“見積依頼明細”と“見積回答明細”は、商品コードだけで“商品”を参照しています。案1では商品コードだけでは“商品”の行を一意に特定できず、被参照側のキーとして使えなくなります。このため参照関係を定義し直す必要があり、公式解答例では変更すべき制約を「外部キー制約」としています。既存アプリケーションへの影響を避けるために案2が採用された理由も、ここから読み取れます。

設問1(3)(c)

“商品履歴”には、同じ商品について過去の行が複数入ります。本文には「同一の適用開始日に同一の商品を複数回更新することはない前提」とあるため、商品コードと適用開始日の組合せで行を一意に識別できます。公式解答例ではc、dは順不同で、cの一方は「商品コード」です。

設問1(3)(d)

(c)と同じ理由で、主キーのもう一方の構成列は「適用開始日」です（公式解答例ではcと順不同）。商品コードだけでは履歴が重複するため、行の適用期間の開始を示す適用開始日を組み合わせることが根拠になります。

設問1(3)(e)

トリガー1は、SET NEW1.適用開始日 = COALESCE(...)によって、更新後の行の値を反映前に書き換えます。仕様(4)①では、更新中や挿入中の値を実際の反映前に修正できるのはBEFOREトリガーだと説明しています。したがって公式解答例のとおり、eは「BEFORE」です。

設問1(3)(f)

トリガー2は、INSERT INTO 商品履歴によって、ほかのテーブルを変更します。仕様では、変更操作の後に実行され、ほかのテーブルに対する変更操作を行えるのはAFTERトリガーです。また、トリガー1で確定した更新後の適用開始日を使う必要があるため、更新後に実行するのが自然です。公式解答例では、fは「AFTER」です。

設問1(3)(g)

“商品履歴”に挿入するのは、対象行の更新前の行です。VALUESの各列もOLD2で修飾されているため、履歴行の適用開始日には、更新前の行の適用開始日を設定します。公式解答例では、gは「OLD2」です。

設問1(3)(h)

目的欄には「挿入行の適用終了日には、更新後の行の適用開始日の前日を設定する」とあります。そのため、更新後の行を指すNEW2の適用開始日をADD_DAYSで1日戻します。公式解答例では、hは「NEW2」です。

設問1(4)(i)

図3の適用終了日は、ADD_DAYS(LEAD(見積回答日)、 -1)で求めます。つまり、同じ商品の次の変更行の適用開始日の前日です。商品コード1で行2（2020-09-01、 M1-1）の次の行は行3で、その見積回答日は2022-09-01です（表3の見積依頼番号2022001201）。その前日なので、公式解答例のとおりiは「2022-08-31」です。

設問1(4)(j)

行3（定価1100、 M1-2）は、表4の見積依頼番号2022001201の明細です。表3でこの見積回答日を確認すると2022-09-01です。適用開始日には見積回答日が入るため、公式解答例ではjは「2022-09-01」です。

設問1(4)(k)

行3は商品コード1の最後の行なので、LEADは1行後の行がないためNULLを返します。注記では、ADD_DAYSは引数1がNULLならNULLを返すとされています。その結果、適用終了日はNULLになり、公式解答例ではkは「NULL」です。表1の注記2の「適用終了日が未定の場合はNULL」という、現在適用中の行の扱いとも一致します。

設問1(5)(商品)

“商品”テーブルには、各商品の最新の状態、つまり適用終了日が未定の行を反映します。商品コード1は行3、商品コード2は行5、商品コード3は変更がなく1行だけなので行6が最新です。公式解答例では「3、5、6」です。移行前の状態によらず、変更があった全商品を更新するという記述も、最新行で上書きする根拠になります。

設問1(5)(商品履歴)

“商品履歴”には、後継の行があり適用期間が終了した過去の行を挿入します。該当するのは、商品コード1の行1と行2、商品コード2の行4です。公式解答例では「1、2、4」です。採点講評では、更新行と挿入行を重複して答える誤答が散見されたと指摘しています。“商品”は最新の状態、“商品履歴”は過去の状態を持つ設計だと読み取り、各行をどちらか一方にだけ振り分けることが重要です。

設問2(1)(ア)

回復にはオブジェクトストレージ上のフルバックアップとアーカイブログを使います。ディスク障害時には、まだアーカイブされていない現行ログファイルの内容が失われます。そのため、どこまで戻せるかは失われるログの量で決まり、公式解答例ではアは「ログの量」です。

設問2(1)(イ)

ログの切替えは5分に1回行われ、その時点でオブジェクトストレージにアーカイブされます。したがって、失われるのは最大で直前の切替え以降5分間の更新です。公式解答例では、イは「5」（分）です。

設問2(1)(ウ)

リストア時間は、データベース容量を転送速度で割って求めます。180×10^9バイト ÷ (100×10^6バイト/秒) = 1、800秒です。公式解答例では、ウは「1、800」です。

設問2(1)(エ)

ログを適用する期間は最大24時間で、ログは毎秒10ページ出力されます。24時間×3、600秒×10ページ = 864、000ページとなり、公式解答例ではエは「864、000」です。

設問2(1)(オ)

バッファヒット率が0%なので、全ページで同期入出力が発生します。864、000ページ×2ミリ秒 = 1、728、000ミリ秒 = 1、728秒です。公式解答例では、オは「1、728」です。

設問2(2)

仕様(3)②では、非同期型は複製先へのログの到達を待たずに複製元のトランザクションをコミットします。同期型は、複製先でログがディスクに出力されるまでコミットを待ちます。そのため同期型では、参照専用インスタンスの負荷や応答が本番インスタンスのコミット時間に影響します。非同期型ならこの待ちが発生しません。公式解答例では「非同期型では複製先へのログの到達を待たないから」や「同期型では複製先でのログのディスク出力を待つから」としています。30字以内で答える場合は、『複製先へのログ到達を待たずにコミットする』という仕様上の根拠を明記すれば、要素を押さえられます。

設問2(3)

非同期型では、複製元がコミットした時点で、そのログが複製先に届いているとは限りません。本番インスタンスで障害が起きたときにまだ複製先へ反映されていない変更は、フェイルオーバー後の参照専用インスタンスには存在せず、失われます。公式解答例では「複製元の変更操作が複製先で未反映だった場合」を挙げ、そうした状態を生む状況として「複製元と複製先の間の通信が切断されていた場合」「複製先のインスタンスが停止していた場合」も示しています。30字以内では、コミット済みの変更が複製先に未到達・未反映のまま障害が起きた状況を簡潔に書きます。

学習のポイント

主キーの変更が外部キーに及ぼす影響、トリガーの実行タイミングと相関名の使い分け、ウィンドウ関数の結果を手で追う力、RPO/RTOの計算手順、同期型と非同期型のレプリケーションの長所と短所を重点的に学習します。

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

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

問題