データベーススペシャリスト午後I 事例記述令和4年 秋期
午後I 問2
問2 データベースの実装に関するデータベース設計・実装
テーマ：マスターテーブルの履歴管理・トリガー設計・データ移行およびクラウド基盤設計（RPO/RTO・レプリケーション）
左右2分割表示：左側の問題文をスクロールしながら右側の設問に解答できます
長文事例本文
図表クリックで拡大問2 データベースの実装に関する次の記述を読んで，設問に答えよ。
専門商社のB社では，見積業務で利用するシステム（以下，見積システムという）の，マスター保守に伴う調査業務を改善中である。また，見積システムのパブリッククラウドへの移行を計画している。
〔パブリッククラウドが提供するサービスの主な仕様〕
1. オブジェクトストレージ
オブジェクトストレージには，任意のファイルを保存することができる。RDBMSとは独立して稼働し，RDBMSの障害時にも影響を受けずに，ファイルにアクセスすることができる。
2. RDBMS
PaaSとして提供されるRDBMSは，インスタンスごとに割り当てられた仮想マシンで稼働する。
(1) ログ
ログはログファイルに記録する。ログファイルの切替え時に，切替え前に使用していたログファイル（以下，アーカイブログという）を，オブジェクトストレージに保存する。ログ切替えの時間間隔は，任意に設定することができる。
(2) バックアップ
① データベース全体のフルバックアップを，オブジェクトストレージに保存する。バックアップは，データベースを停止して，オフラインで取得する。バックアップを取るタイミングは，任意に設定することができる。
② オブジェクトストレージに保存したフルバックアップとアーカイブログを使って，データベースを回復することができる。
(3) レプリケーション
ログを使って，RDBMSのデータをほかのRDBMSに複製する。複製元のテーブルに対する変更操作（挿入・更新・削除）を複製先のテーブルに自動的に反映する。
レプリケーションには，同期型と非同期型がある。
① 同期型では，複製先でログをディスクに出力した後，複製元のトランザクションがコミットされる。
② 非同期型では，複製先へのログの到達を待たずに，複製元のトランザクションがコミットされる。
(4) トリガー
テーブルに対する変更操作（挿入・更新・削除）を契機に，あらかじめ定義した処理を実行する。
① 実行タイミングを定義することができる。BEFOREトリガーは，テーブルに対する変更操作の前に実行され，更新中又は挿入中の値を実際の反映前に修正することができる。AFTERトリガーは，変更操作の後に実行され，ほかのテーブルに対する変更操作を行うことができる。
② トリガーを実行する契機となった変更操作を行う前と後の行を参照することができる。参照するには，操作前と操作後の行に対する相関名をそれぞれ定義し，相関名で列名を修飾する。
〔見積システムの概要〕
1. テーブル
主なテーブルのテーブル構造を図1に示す。
拡大
2. 仕入先への見積依頼業務
(1) B社の社員は，顧客からの引き合いを受けて，仕入先への見積依頼を入力する。見積依頼番号を採番し，“見積依頼”，“見積依頼明細”テーブルに見積依頼の内容を登録する。
(2) 仕入先に見積りを依頼し，回答を受け取る。
(3) 仕入先からの回答を入力する。対応する見積依頼の見積依頼番号を参照し，“見積回答”，“見積回答明細”テーブルに見積回答の内容を登録する。商品のモデル名，定価が変更されたことが分かることがある。この場合，当該商品は，“見積回答明細”テーブルに変更後の内容を登録する。ただし，“商品”テーブルへの反映は後日行う。
〔“商品”テーブルの履歴管理〕
モデル名又は定価のいずれかが変更されたが，変更が“商品”テーブルへ反映されていない商品を調べるため，図2に示すSQL文を定期的に実行している。
拡大
1. “商品”テーブルの設計変更
“商品”テーブルを更新すると，過去の属性情報は失われてしまう。そこで，商品属性情報の変更を履歴として保存するために，“商品”テーブルの設計変更を行うことにした。ただし，既存のアプリケーションプログラムには，極力影響を与えないようにする必要がある。表1に示す2案を検討した結果，案2を採用した。
表1 “商品”テーブルの設計変更案
- 案1
- 変更後のテーブル構造: 商品（商品コード，メーカー名，商品名，モデル名，定価，更新日，適用開始日，適用終了日）
- 案2
- 変更後のテーブル構造:
- 商品（商品コード，メーカー名，商品名，モデル名，定価，更新日，適用開始日，適用終了日）
- 商品履歴（商品コード，メーカー名，商品名，モデル名，定価，更新日，適用開始日，適用終了日）
注記1 “商品履歴”テーブルの主キーは表示していない。
注記2 適用開始日は，その行の適用が開始される日，適用終了日は，その行の適用が終了される日。適用終了日が未定の場合はNULLが設定される。
案2の実装に当たり，“商品”テーブルへの列の追加，“商品履歴”テーブルの作成，及び主キーの追加を表2のSQL1に示すSQL文で行った。
また，同一の適用開始日に同一の商品を複数回更新することはない前提で，“商品”テーブルの更新時に行う追加の処理を，表2のSQL2に示すトリガーで実装した。
拡大
2. データ移行
“見積回答”，“見積回答明細”テーブルから“商品”，“商品履歴”テーブルへデータを移行するため，商品のモデル名又は定価のいずれかが変更されたことの履歴を，図3のSQL文で調べた。“見積回答”，“見積回答明細”テーブルの内容を表3，表4に示す。SQL文の結果を表5に示す。
表5の内容を基に，“商品”テーブルを更新，又は“商品履歴”テーブルへ挿入することでデータを移行した。移行前の“商品”テーブルの状態によらず，変更があった全商品を更新した。また，表2のSQL2に示すトリガーは未定義の状態で行った。
拡大
表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 | （非表示） |
注記 網掛け部分は表示していない。
〔基盤設計〕
1. RPO，RTOの見積り
見積システムをパブリッククラウドに移行した場合の，RDBMSのディスク障害時のRPO及びRTOを，次のように見積もった。
(1) 利用するパブリッククラウドの仕様に基づいて，データベースのフルバックアップは1日に1回取得し，ログの切替えは5分に1回行い，回復時にはオブジェクトストレージに保存したフルバックアップとアーカイブログを使って回復する，という前提で見積もる。
(2) RPOは，障害発生時に失われる ア に依存するので，最大 イ 分とみなせる。
(3) RTOのうち，データベースの回復に掛かる時間は，フルバックアップからのリストア時間と，ログを適用するのに掛かる時間の合計である。
(4) フルバックアップからのリストア時間は，データベース容量が180Gバイト，リストア時のディスク転送速度を100Mバイト／秒と仮定すると ウ 秒である。ここで，1Gバイトは10^9バイト，1Mバイトは10^6バイトとする。
(5) ログを適用する期間が最大になるのは，フルバックアップ取得後の経過時間が最大になる24時間である。ログが毎秒10ページ出力されると仮定すると，適用するログの量は最大 エ ページである。ログを適用するのに掛かる時間は，バッファヒット率を0%，同期入出力時間がページ当たり2ミリ秒と仮定すると最大 オ 秒である。
2. 参照専用インスタンス
商品の変更履歴を調べるために実行するSQL文の負荷が大きく，見積システムへの影響が懸念された。そこで，影響を最小化するために，参照専用インスタンスを本番インスタンスとは別に作成し，調査は参照専用インスタンスで行うことにした。また，全テーブルについて，本番インスタンスから参照専用インスタンスへ，非同期型のレプリケーションを行うことにした。
3. 参照専用インスタンスへのフェイルオーバーによる業務継続
RPO及びRTOを短くするために，本番インスタンスが障害になった場合，参照専用インスタンスにフェイルオーバーして，参照専用インスタンスを使用して業務を継続できるかを検討した。検討の結果，非同期型のレプリケーションを行う前提だと，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性があることが分かった。
拡大図1 主なテーブルのテーブル構造（一部省略）
拡大図2 商品の変更を調べるSQL文（未完成）
拡大表2 “商品”テーブルを変更するSQL文及びトリガーを定義するSQL文（未完成）
拡大図3 商品の変更履歴を調べるSQL文（未完成）
設問一覧 ＆ 解答・演習
まずは解答を作成してみましょう。「解答例を見る」をクリックするとIPA公式解答例と採点基準が開きます。
設問1 (1)記号・選択式
〔“商品”テーブルの履歴管理〕について答えよ。
図2中の a，b に入れる適切な字句を答えよ。
図2中の a，b に入れる適切な字句を答えよ。
※直接入力してください
設問1 (2)記号・選択式
〔“商品”テーブルの履歴管理〕について答えよ。
“商品”テーブルの設計変更について，表1中の案1を採用した場合，ほかのどのテーブルの，どの制約を変更する必要があるか。テーブル名と制約を全て答えよ。
“商品”テーブルの設計変更について，表1中の案1を採用した場合，ほかのどのテーブルの，どの制約を変更する必要があるか。テーブル名と制約を全て答えよ。
※直接入力してください
設問1 (3)記号・選択式
〔“商品”テーブルの履歴管理〕について答えよ。
表2中の c 〜 h に入れる適切な字句を答えよ。
表2中の c 〜 h に入れる適切な字句を答えよ。
※直接入力してください
設問1 (4)記号・選択式
〔“商品”テーブルの履歴管理〕について答えよ。
表5中の i 〜 k に入れる適切な字句を答えよ。
表5中の i 〜 k に入れる適切な字句を答えよ。
※直接入力してください
設問1 (5)記号・選択式
〔“商品”テーブルの履歴管理〕について答えよ。
表5のうち，“商品”テーブルへの更新行，“商品履歴”テーブルへの挿入行に当たる行を，それぞれ行番号で全て答えよ。
表5のうち，“商品”テーブルへの更新行，“商品履歴”テーブルへの挿入行に当たる行を，それぞれ行番号で全て答えよ。
※直接入力してください
設問2 (1)記号・選択式
〔基盤設計〕について答えよ。
本文中の ア 〜 オ に入れる適切な字句又は数値を答えよ。
本文中の ア 〜 オ に入れる適切な字句又は数値を答えよ。
※直接入力してください
設問2 (2)記述問題
制限字数：30 字以内
〔基盤設計〕について答えよ。
“2. 参照専用インスタンス”について，参照専用インスタンスへのレプリケーションを非同期型にすると，見積システムへの影響を最小化できるのはなぜか。レプリケーションの仕様に基づいて，30字以内で答えよ。
“2. 参照専用インスタンス”について，参照専用インスタンスへのレプリケーションを非同期型にすると，見積システムへの影響を最小化できるのはなぜか。レプリケーションの仕様に基づいて，30字以内で答えよ。
0 / 30 文字
設問2 (3)記述問題
制限字数：30 字以内
〔基盤設計〕について答えよ。
“3. 参照専用インスタンスへのフェイルオーバーによる業務継続”について，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性がある。どのような場合か。レプリケーションの仕様に基づいて，30字以内で答えよ。
“3. 参照専用インスタンスへのフェイルオーバーによる業務継続”について，参照専用インスタンスでは，本番インスタンスでコミット済みの変更が失われる可能性がある。どのような場合か。レプリケーションの仕様に基づいて，30字以内で答えよ。
0 / 30 文字
午後I長文記述の得点力を引き上げる定番対策書Amazon・楽天 公式提携
PR
IPA 厳選対策書徹底攻略 データベーススペシャリスト教科書 令和8年度株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
DB徹底攻略株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
徹底攻略 データベーススペシャリスト教科書 令和8年度
💡 推薦ポイント:午後I・午後IIの概念データモデル穴埋めや排他制御・性能設計の記述問題で確実に合格点を勝ち取る実践テクニックが満載です。
¥3,278（税込）
※本リンク先はプロモーション（アフィリエイト広告）が含まれています
【出題趣旨】IPA公式発表
【採点講評】IPA公式発表
本問に関連するデータベーススペシャリスト重要用語解説
本番形式・制限時間付きで演習する
令和4年 秋期 午後I（3問中2問選択・90分）を実戦演習モードで通し解答し、総合採点を受けられます。