DB 令和4年度秋期午後Ⅰ 問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を短くするために，本番インスタンスが障害になった場合，参照専用インスタンスにフェイルオーバーして，参照専用インスタンスを使用して業務を継続できるかを検討した。検討の結果，非同期型のレプリケーションを行う前提だと，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性があることが分かった。

　ー

　ー

IPA公開情報

出題趣旨

　近年，パブリッククラウドの普及に伴い，既存の業務システムをクラウド環境へ移行することがある。また，その際，既存アプリケーションプログラムに極力影響を与えずに，何らかの業務改善が期待されることが多い。
　本問では，見積業務システムのマスター保守業務及びクラウド環境への移行を題材として，マスターテーブルの設計変更，トリガーの実装，列の値の変更を前提としたマスターデータの移行についての理解を問う。また，クラウド環境の RDBMS 基盤設計でディスク障害を考慮した，RPO/RTO の見積り，レプリケーションの考慮点についての理解を問う。

採点講評

　問 2 では，専門商社における見積業務を題材に，マスターテーブルの変更履歴を保存するための設計変更，RPO 及び RTO の見積り，並びにトリガー，データ移行及びレプリケーションの考慮点について出題した。全体として正答率は平均的であった。
　設問 1 は，(5)の正答率がやや低かった。“商品”テーブルへの更新行，“商品履歴”テーブルへの挿入行を重複して解答している誤答が散見された。設問の状況に基づいて，商品テーブルには最新の状態，商品履歴テーブルには過去の状態を保存する設計であることを読み取ってほしい。
　設問 2 は，全体として正答率は平均的であった。レプリケーションは，データベースの可用性及び拡張性を確保する目的で広く用いられている。同期型と非同期型それぞれの特徴をよく理解し，要件に応じて適切に使い分ける技術を身に付けてほしい。

前問ナビ次問