データベーススペシャリスト試験過去問 2022年（令和4年）秋期午後Ⅰ 問2

データベースの実装

専門商社のB社では、見積業務で利用するシステム（以下、見積システムという）の、マスター保守に伴う調査業務を改善中である。また、見積システムのパブリッククラウドへの移行を計画している。

パブリッククラウドが提供するサービスの主な仕様

オブジェクトストレージ
オブジェクトストレージには、任意のファイルを保存することができる。RDBMSとは独立して稼働し、RDBMSの障害時にも影響を受けずに、ファイルにアクセスすることができる。
RDBMS
PaaSとして提供されるRDBMSは、インスタンスごとに割り当てられた仮想マシンで稼働する。
1. ログ
  ログはログファイルに記録する。ログファイルの切替え時に、切替え前に使用していたログファイル（以下、アーカイブログという）を、オブジェクトストレージに保存する。ログ切替えの時間間隔は、任意に設定することができる。
2. バックアップ
  1. ① データベース全体のフルバックアップを、オブジェクトストレージに保存する。バックアップは、データベースを停止して、オフラインで取得する。バックアップを取るタイミングは、任意に設定することができる。
  2. ② オブジェクトストレージに保存したフルバックアップとアーカイブログを使って、データベースを回復することができる。
3. レプリケーション
  ログを使って、RDBMSのデータをほかのRDBMSに複製する。複製元のテーブルに対する変更操作（挿入・更新・削除）を複製先のテーブルに自動的に反映する。
  レプリケーションには、同期型と非同期型がある。
  1. ① 同期型では、複製先でログをディスクに出力した後、複製元のトランザクションがコミットされる。
  2. 同期型では、複製先でログをディスクに出力した後、複製元のトランザクションがコミットされる。
  3. ② 非同期型では、複製先へのログの到達を待たずに、複製元のトランザクションがコミットされる。
4. トリガー
  テーブルに対する変更操作（挿入・更新・削除）を契機に、あらかじめ定義した処理を実行する。
  1. ① 実行タイミングを定義することができる。BEFOREトリガーは、テーブルに対する変更操作の前に実行され、更新中又は挿入中の値を実際の反映前に修正することができる。AFTERトリガーは、変更操作の後に実行され、ほかのテーブルに対する変更操作を行うことができる。
  2. ② トリガーを実行する契機となった変更操作を行う前と後の行を参照することができる。参照するには、操作前と操作後の行に対する相関名をそれぞれ定義し、相関名で列名を修飾する。

見積システムの概要

テーブル
主なテーブルのテーブル構造を図1に示す。

図1 主なテーブルのテーブル構造（一部省略）
仕入先への見積依頼業務
1. B社の社員は、顧客からの引き合いを受けて、仕入先への見積依頼を入力する。見積依頼番号を採番し、"見積依頼"，"見積依頼明細" テーブルに見積依頼の内容を登録する。
2. 仕入先に見積りを依頼し、回答を受け取る。
3. 仕入先からの回答を入力する。対応する見積依頼の見積依頼番号を参照し、"見積回答"，"見積回答明細" テーブルに見積回答の内容を登録する。商品のモデル名，定価が変更されたことが分かることがある。この場合，当該商品は、"見積回答明細" テーブルに変更後の内容を登録する。ただし，"商品" テーブルへの反映は後日行う。

"商品" テーブルの履歴管理

モデル名又は定価のいずれかが変更されたが，変更が "商品" テーブルへ反映されていない商品を調べるため，図2に示すSQL文を定期的に実行している。

SELECT A.モデル名 AS 新モデル名, C.モデル名 AS 旧モデル名, A.定価 AS 新定価, C.定価 AS 旧定価
FROM 見積回答明細 A INNER JOIN 見積回答 B ON A.見積依頼番号 = B.見積依頼番号
INNER JOIN 商品 C ON A.a = C.a
WHERE B.見積回答日 > C.更新日
AND (A.モデル名 <> C.モデル名 b A.定価 <> C.定価)

図2 商品の変更を調べるSQL文（未完成）

"商品" テーブルの設計変更とデータ移行

"商品" テーブルの設計変更

"商品" テーブルを更新すると，過去の属性情報は失われてしまう。そこで，商品属性情報の変更を履歴として保存するために，"商品" テーブルの設計変更を行うことにした。ただし，既存のアプリケーションプログラムには，極力影響を与えないようにする必要がある。表1に示す2案を検討した結果，案2を採用した。

表1 "商品" テーブルの設計変更案
	変更後のテーブル構造
案1	商品（商品コード，メーカー名，商品名，モデル名，定価，更新日，適用開始日，適用終了日）
案2	商品（商品コード，メーカー名，商品名，モデル名，定価，更新日，適用開始日，適用終了日）商品履歴（商品コード，メーカー名，商品名，モデル名，定価，更新日，適用開始日，適用終了日）

注記1 "商品履歴" テーブルの主キーは表示していない。

注記2 適用開始日は、その行の適用が開始される日，適用終了日は，その行の適用が終了する日。適用終了日が未定の場合はNULLが設定される。

案2の実装に当たり，"商品" テーブルへの列の追加，"商品履歴" テーブルの作成，及び主キーの追加を案2のSQL1に示すSQL文で行った。

また，同一の適用開始日に同一の商品を複数回更新することはない前提で，"商品" テーブルの更新時に行う追加の処理を，表2のSQL2に示すトリガーで実装した。

表2 "商品" テーブルを変更するSQL文及びトリガーを定義するSQL文（未完成）
SQL	SQL文（上段：目的、下段：SQL文）
SQL1	"商品" テーブルへ適用開始日列，適用終了日列を追加する。 "商品履歴" テーブルを作成し，主キーを追加する。
SQL1	ALTER TABLE 商品 ADD COLUMN 適用開始日 DATE DEFAULT CURRENT_DATE NOT NULL; ALTER TABLE 商品 ADD COLUMN 適用終了日 DATE; CREATE TABLE 商品履歴 LIKE 商品; ALTER TABLE 商品履歴 ADD PRIMARY KEY (c, d); SQL1
SQL2	次のトリガーを定義する。・"商品" テーブルの更新時に，適用開始日がNULLの場合，現在日付に更新する。・"商品" テーブルの更新時に，対象行の更新前の行を "商品履歴" テーブルに挿入する。・新商品の追加の場合，挿入行の適用終了日の前日を設定する。
SQL2	CREATE TRIGGER トリガー1 e UPDATE ON 商品 REFERENCING OLD AS OLD1 NEW AS NEW1 FOR EACH ROW SET NEW1.適用開始日 = COALESCE(NEW1.適用開始日, CURRENT_DATE); CREATE TRIGGER トリガー2 f UPDATE ON 商品 REFERENCING OLD AS OLD2 NEW AS NEW2 FOR EACH ROW INSERT INTO 商品履歴 VALUES (OLD2.商品コード, OLD2.メーカー名, OLD2.商品名, OLD2.モデル名, OLD2.定価, OLD2.更新日, g, 適用開始日, NEW2.DAYS(NEW2.適用開始日, -1)); SQL2

注記1 CREATE TABLE A LIKE Bは、テーブルBを基にして同じ列構成のテーブルAを定義する。

注記2 OLD1，OLD2，NEW1，NEW2は、トリガーを実行する契機となったテーブルに対する変更操作が行われた行を参照する相関名をそれぞれ定義する。OLD1及びOLD2は変更前，NEW1及びNEW2は変更後の行を参照する。

注記3 ADD_DAYS(引数1，引数2)は，引数1（日付型）から引数2（整数型）日後の日付を返すユーザー定義関数である。ただし，引数1がNULLの場合はNULLを返す。

データ移行

"見積回答"，"見積回答明細" テーブルから "商品"，"商品履歴" テーブルへデータを移行するため，商品のモデル名又は定価のいずれかが変更されたことの履歴を，図3のSQL文で調べた。"見積回答"，"見積回答明細" テーブルの内容を表3，表4に示す。SQL文の結果を表5に示す。

WITH Q1 AS (SELECT A.商品コード, A.モデル名, A.定価, B.見積回答日,
    LAG(A.モデル名) OVER (PARTITION BY A.商品コード ORDER BY B.見積回答日) AS 前行モデル名,
    LAG(A.定価) OVER (PARTITION BY A.商品コード ORDER BY B.見積回答日) AS 前行定価
    FROM 見積回答明細 A INNER JOIN 見積回答 B ON A.見積依頼番号 = B.見積依頼番号),
Q2 AS (SELECT Q1.* FROM Q1 WHERE Q1.前行定価 IS NULL 
    OR Q1.モデル名 <> Q1.前行モデル名 b Q1.定価 <> Q1.前行定価)
SELECT ROW_NUMBER() OVER (ORDER BY Q2.商品コード, Q2.見積回答日) AS 行番号, 
    Q2.商品コード, Q2.定価, Q2.モデル名, Q2.見積回答日 AS 適用開始日,
    ADD_DAYS(LEAD(Q2.見積回答日) 
        OVER (PARTITION BY Q2.商品コード ORDER BY Q2.見積回答日), -1) AS 適用終了日
FROM Q2 ORDER BY Q2.商品コード, 適用開始日;

図3 商品の変更履歴を調べるSQL文（未完成）

表3 "見積回答" テーブルの内容
見積依頼番号	仕入先コード	社員コード	見積回答日
2019000101	A001	AB490656	2019-04-01
2020001201	A001	AH000032	2020-09-01
2022000201	B002	AA000232	2022-05-01
2022001201	A001	AH000657	2022-09-01

表4 "見積回答明細" テーブルの内容
見積依頼番号	明細番号	商品コード	数量	仕入単価	モデル名	定価
2019000101	1	1	1	800	M1	1000
2019000101	2	2	1	1750	M2	2000
2019000101	3	3	1	2600	M3	3000
2020001201	1	1	1	800	M1-1	1000
2022000201	1	2	1	1800	M2-1	2000
2022001201	1	1	1	900	M1-2	1100

表5 SQL文の結果（未完成）
行番号	商品コード	定価	モデル名	適用開始日	適用終了日
1	1	1000	M1	2019-04-01
2	1	1000	M1-1	2020-09-01	i
3	1	1100	M1-2	j	k
4	2	2000	M2	2019-04-01
5	2	2000	M2-1	2022-05-01
6	3	3000	M3	2019-04-01

注記網掛け部分は表示していない。

表5の内容を基に，"商品" テーブルを更新，又は "商品履歴" テーブルへ挿入することでデータを移行した。移行前の "商品" テーブルの状態によらず，変更があった全商品を更新した。また，表2のSQL2に示すトリガーは未定義の状態で行った。

基盤設計

RPO，RTOの見積り
見積システムをパブリッククラウドに移行した場合の，RDBMSのディスク障害時のRPO及びRTOを，次のように見積もった。
1. 利用するパブリッククラウドの仕様に基づいて，データベースのフルバックアップは1日に1回取得し，ログの切替えは5分に1回行い，回復時にはオブジェクトストレージに保存したフルバックアップとアーカイブログを使って回復する，という前提で見積もる。
2. RPOは，障害発生時に失われるアに依存するので，最大イ分とみなせる。
3. RTOのうち，データベースの回復に掛かる時間は，フルバックアップからのリストア時間と，ログを適用するのに掛かる時間の合計である。
4. フルバックアップからのリストア時間は，データベース容量が180GBバイト，リストア時のディスク転送速度を100MBバイト/秒と仮定するとウ秒である。ここで，16バイトは10⁶バイト，1MBバイトは10⁶バイトとする。
5. ログを適用する期間が最大になるのは，フルバックアップ取得後の経過時間が最大になる24時間である。ログが毎秒10ページ出力されると仮定すると，適用するログの量は最大エページである。ログを適用するのに掛かる時間は，バッファヒット率を0%，同期入出力時間がページ当たり2ミリ秒と仮定すると最大オ秒である。
参照専用インスタンス
商品の変更履歴を調べるために実行するSQL文の負荷が大きく，見積システムへの影響が懸念された。そこで，影響を最小化するために，参照専用インスタンスを本番インスタンスとは別に作成し，調査は参照専用インスタンスで行うことにした。また，全テーブルについて，本番インスタンスから参照専用インスタンスへ，非同期型のレプリケーションを行うことにした。
参照専用インスタンスへのフェイルオーバーによる業務継続
RPO及びRTOを短くするために，本番インスタンスが障害になった場合，参照専用インスタンスにフェイルオーバーして，参照専用インスタンスを使用して業務を継続できるかを検討した。検討の結果，非同期型のレプリケーションを行う前提だと，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性があることが分かった。

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