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

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

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

問題

問1 データベースの実装に関する次の記述を読んで，設問1～3に答えよ。 D社は，マンションの開発及び販売を手掛ける不動産会社である。D社では，販売業務で利用している商談管理システムの老朽化に伴い，システムの刷新を行っている。〔業務の概要〕 1. 物件 (1) 販売対象のマンション全体を物件という。物件内の建物を棟といい，棟内の個々の住居を住戸という。 (2) 物件内の住戸を幾つかの販売期に分けて販売する。 2. 組織 (1) 物件の販売を行う拠点として，物件の近隣に販売センタを設営している。 (2) 販売センタには，販売業務を行う担当者を配置している。 (3) 販売センタの業務時間は毎日10～19時である。 3. 販売業務 (1) 物件に興味をもった人を顧客といい，顧客ごと物件ごとに，商談の進捗度，接触回数，受注確度，希望面積，購入予算などを記録する。 (2) 担当者は，資料請求の受付，資料の送付，販売センタへの来場予約受付，来場時の応接，希望条件・購入予算などを記した要望書の取得，購入申込みの受付，抽選会の案内・実施などの営業活動を行う。担当者が顧客との間で行うこれらの活動を追客（ついきゃく）といい，“受付”（資料請求，来場予約など），“実施”（資料送付，来場時の応接など），“応対”（購入申込受領，問合せ対応など）の区分に分けて記録する。 (3) 追客を行うたびに商談管理システムにその内容を登録する。 4. 販売分析業務販売促進のための分析業務である。業務に用いるミスマッチ分析表の例を図1に示す。図1は，住戸の仕様（販売価格，専有面積）と顧客の要望（購入予算，希望面積）との一致度合いを確認するための帳票で，販売価格，購入予算を範囲によって価格帯に，専有面積，希望面積を範囲によって面積帯に分け，価格帯と面積帯の組ごとに“住戸数（戸）／顧客数（名）”を表示する。空白は該当なしを表す。図1 ミスマッチ分析表の例〔商談管理システムの概要〕 1. 関係スキーマ商談管理業務の関係スキーマを図2に，主な属性とその意味・制約を表1に示す。図2 関係スキーマ（一部省略）表1 主な属性とその意味・制約 2. 外部システムとの連携商談管理システムは，次のように外部システムとのデータ連携を行っている。 (1) 物件管理システムから物件情報（大エリア，小エリア，住所，鉄道事業者，沿線，駅，物件，最寄り駅，販売期，棟，住戸）の変更データを受領する。 (2) 自社のWebサイトから資料請求及び来場予約データを受領する。 (3) 契約システムから毎日複数回，契約データを受領する。 (4) 受領したデータは専用のアプリケーションソフトウェア（以下，APという）を用いて商談管理システムのデータベースに反映する。 3. 処理の例商談管理システムにおける処理の例を表2に示す。表2 処理の例〔RDBMSの主な仕様〕商談管理システムで利用するRDBMSの主な仕様は次のとおりである。 1. ページ RDBMSとストレージ間の入出力単位をページという。同じページに異なるテーブルの行が格納されることはない。 2. テーブル・索引 (1) テーブルの列にはNOT NULL制約を指定することができる。NOT NULL制約を指定しない列にはNULLか否かを表す1バイトのフラグが付加される。 (2) 主キー制約には，主キーを構成する列名を指定する。 (3) 参照制約には，列名，参照先テーブル名，参照先列名を指定する。制約に違反する追加，更新，削除は拒否される。 (4) 検査制約には，同一行の列に対する制約を記述する。 (5) 索引には，ユニーク索引と非ユニーク索引がある。 3. トリガ機能テーブルに対する変更操作（追加，更新，削除）を契機に，あらかじめ定義した処理を実行する。 (1) 実行タイミング（変更操作の前又は後。前者のトリガをBEFOREトリガ，後者のトリガをAFTERトリガという），列値による実行条件を定義することができる。 (2) トリガ内では，変更操作を行う前の行，変更操作を行った後の行のそれぞれに相関名を指定することで，行の旧値，新値を参照することができる。 (3) BEFOREトリガの処理開始から終了までの同一トランザクション内では，どのテーブルに対しても変更操作を行うことはできない。 (4) トリガ内で例外を発生させることができる。 (5) トリガ内で発生した例外は，実行の契機となった変更操作に返却される。トリガ内で例外が発生した場合，変更操作を行うトランザクションは終了しないので，明示的なコミット又はロールバックが必要である。 4. バックアップ機能 (1) バックアップの単位には，データベース単位，テーブル単位がある。 (2) バックアップには，取得するページの範囲によって，全体，増分，差分の3種類がある。 ① 全体バックアップには，全ページが含まれる。 ② 増分バックアップには，前回の全体バックアップ取得後に変更されたページが含まれる。ただし，前回の全体バックアップ取得以降に増分バックアップを取得していた場合は，前回の増分バックアップ取得後に変更されたページだけが含まれる。 ③ 差分バックアップには，前回の全体バックアップ取得後に変更された全てのページが含まれる。 (3) 全体及び増分バックアップでは，取得ごとにバックアップファイルが作成される。差分バックアップでは，2回目以降の差分バックアップ取得ごとに，前回の差分バックアップファイルが最新の差分バックアップファイルで置き換えられる。 5. 復元機能 (1) バックアップを用いて，バックアップ取得時点の状態に復元できる。 (2) 復元の単位はバックアップの単位と同じである。 (3) データベース単位の全体バックアップは，取得元とは異なる環境に復元することができる。 6. 更新ログによる回復機能 (1) バックアップを用いて復元した後，更新ログを用いたロールフォワード処理によって，障害発生直前又は指定の時刻の状態に回復できる。データベース単位の全体バックアップを取得元と異なる環境に復元した場合も同様である。 (2) 一つのテーブルの回復に要する時間は，変更対象ページのストレージからの読込み回数に比例する。行の追加時には，バッファ上のページに順次追加し，空き領域を確保してページが一杯になるごとに空白ページを読み込む。行の更新時には，ログ1件ごとに対象ページを読み込む。バッファ上のページのストレージへの書込みは，非同期に行われるので，回復時間に影響しない。 7. エクスポート機能 (1) テーブル単位にデータをファイルに抽出することができる。 (2) エクスポート対象テーブルの行の抽出条件を指定することができる。 8. インポート機能 (1) エクスポート機能で抽出したファイルを，インポート先の環境の同じ構造のテーブルに格納することができる。 (2) インポート先のテーブルに主キーが一致するデータが存在する場合の動作として，処理しない（SKIP），又は更新する（MERGE）のいずれかのオプションを指定することができる。未指定時はSKIPを指定した場合と同等となる。〔実装するテーブルの設計〕 1. テーブルの列の追加各テーブルには，登録担当者コード，登録TS，更新担当者コード，更新TSの各列を追加する。登録TSには行追加時の時刻印を，更新TSには行更新時の時刻印をそれぞれ設定する。また，行の削除は論理削除によって行うこととし，削除フラグ列（0：未削除，1：削除済）を追加する。 2. 関係“追客”及びそのサブタイプの実装方法検討関係“追客”と，関係“受付”，“実施”，“応対”は，概念データモデルにおいてスーパタイプとサブタイプの関係にある。スーパタイプ，サブタイプのテーブルへの実装には次の方法①～③があり，それぞれに，他の方法に比べて格納効率が悪い，他の方法では不要な選択・結合・和集合が必要になる，などの短所がある。そこで，表3を作成し，テーブル構造の特質に由来する実装上の短所をまとめた。短所がない場合は“－”を記入する。方法① スーパタイプと全てのサブタイプを一つのテーブルにする。方法② スーパタイプ，サブタイプごとにテーブルにする。方法③ サブタイプだけを，それぞれテーブルにする。スーパタイプの属性は，列として各テーブルに保有する。表3 実装方法の短所（未完成）表3を基に，関係“追客”の実装方法を次のように判断した。・方法③は，項目Aの短所からサブタイプ全体での主キーの一意性を，テーブルの一意性制約以外の方法によってチェックする必要があり採用し難い。・表2中の処理5は表3の項目［エ］に，処理6は表3の項目［オ］に該当する。方法②では，処理5の応答時間に不安がある。・方法①は，項目Bの短所による不利益はあるものの，項目D，Fに該当する処理は少なく，影響が限定的であることから方法①を採用する。 3. 制約の実装検討外部システムとの連携によってデータの整合性が失われることのないように，データベースの制約を定義する。 (1) 参照制約図2の関係スキーマのうち，物件情報である大エリア，小エリア，住所，鉄道事業者，沿線，駅，物件，最寄り駅，販売期，棟及び住戸をそのままテーブルとして実装し，参照制約を定義するとしたら，表2中の処理1では，制約に違反しないように各テーブルへの変更反映処理を行わなければならない。そこで，テーブルを順序付けしてグループ番号1～6に分け，参照制約に違反しない限りできるだけ小さいグループ番号に配置することにして表4を作成した。グループ番号順に直列に処理を行い，同じグループ内のテーブルへの処理は並行して行う。表4 変更データ反映処理のグループとテーブル（未完成） (2) 参照制約以外の制約連携データの反映による整合性の喪失を防ぐための制約を，一意性制約，NOT NULL制約，検査制約，BEFOREトリガ，AFTERトリガのうち適切な方法を用いて定義する。制約とその定義内容を表5にまとめた。ここで，トリガ内の行の旧値を参照する相関名をOROW，新値を参照する相関名をNROWと定義するものとする。表5 制約とその定義内容（未完成）〔問合せの検討〕表2の処理7に用いる問合せの内容を，次の要領で表6に整理した。 (1) 行ごとに構成要素となる問合せを記述する。結果を他の問合せで参照する場合は，行に固有の名前（以下，問合せ名という）を付ける。 (2) 列名又は演算には，テーブルから射影する列名又は演算によって求まる項目を“項目名＝[演算の内容]”の形式で記述する。 (3) テーブル名又は問合せ名には，参照するテーブル名又は問合せ名を記入する。 (4) 選択又は結合の内容には，テーブル名又は問合せ名ごとの選択条件，結合の具体的な方法と結合条件を記入する。表6 表2の処理7に用いる問合せの内容（未完成）表6中の“M1”の問合せを実装するためのSQL文の案を図3に示す。案1は，問合せをそのまま実装するもので，案2は，“価格帯”及び“面積帯”テーブルを追加することで，SQL文の変更なしに価格帯，面積帯の範囲を変えられるようにするものである。案2の①は“価格帯”及び“面積帯”テーブルを作成するSQL文，②は“価格帯”テーブルに行を追加するSQL文，③は追加したテーブルを用いて問合せの結果行を得るSQL文である。図3 “M1”の問合せを実装するSQL文の検討例（未完成・一部省略）〔バックアップ・リカバリの検討〕 1. 障害発生の想定バックアップ・リカバリの検討に当たり，本番環境のデータベース（以下，本番DBという）で想定される障害ケースを検討し，表7に整理した。ここで，データベース単位の全体バックアップを毎日0時に取得しているものとする。表7 想定した障害ケース 2. 障害ケース①の検討障害ケース①のリカバリは，ディスク装置の復旧，バックアップからの復元，更新ログによる回復によって行う。このうち，“商談”及び“追客”テーブルの更新ログによる回復がリカバリに要する時間の大半を占めるので，回復に掛かる時間を試算し，業務への影響を調べることにした。試算に先立って，試算対象テーブルのデータ量及び更新ログ件数見積りを表8にまとめた。表8 試算対象テーブルのデータ量及び更新ログ件数見積り (1) 回復に要する時間の試算障害が18:00に発生し，10:00から8時間分の各テーブルへの追加，更新ログによる回復を想定し，更新ログが毎時均等に発生すると考える。また，1ページ当たりのストレージへのI/Oを10ミリ秒（0.01秒）とし，ストレージへのI/O以外のCPU処理，索引探索，ネットワーク通信などに掛かる時間を考慮しないものとして，次のように試算した。 ① 障害発生時点で，“商談”テーブルには，追加40、000件，更新400、000件の更新ログがある。回復の際，ストレージへのI/Oは，追加では1ページ当たりの平均行数まではバッファ上で処理するので4、000回，更新ではバッファヒット率を0とすると400、000回，合わせて404、000回発生する。よって，回復に必要な時間は［a］秒になる。 ② 同様に，“追客”テーブルでは，回復時のストレージへのI/Oは，追加では［b］回，更新では［c］回発生するので，回復に必要な時間は［d］秒になる。 (2) 回復時間短縮の対策 (1)の試算では回復時間が長い。回復時間を短縮するために，毎日12:00，14:00，16:00に“商談”及び“追客”テーブルのテーブル単位の増分又は差分バックアップを取ることを検討し，次の結論を得た。 ① ［e］時間分の更新ログを適用すればよいので，“商談”テーブルの回復時間は，［f］秒になる。 ② バックアップからの復元に要する時間を考慮すると，“追客”テーブルは，増分と差分のどちらの方式でもほとんど変わらないが，〈下線部〉“商談”テーブルは，増分バックアップよりも時間が掛からない差分バックアップを採用する〈下線部ここまで〉。 3. 障害ケース②の検討障害ケース②は，バックアップの復元及び更新ログによる回復によって誤登録発生直前の日時の状態にする方法では問題を解決できない。そこで，誤登録したデータを削除するため，次の処理を順次実行することにした。・誤登録した“追客”テーブルの行を削除状態に更新する。・誤登録によって作成された“商談”テーブルの行を削除状態に更新する。・誤登録によって作成された“顧客”テーブルの行を削除状態に更新する。・“商談”テーブルの最新接触日時を更新する。 “商談”テーブルの最新接触日時を更新するSQL文を図4に示す。ここで，更新担当者コード，更新TSを更新する処理は省略している。図4 “商談”テーブルの最新接触日時を更新するSQL文（未完成） 4. 障害ケース③の検討障害ケース③では，誤更新が行われた行だけを誤更新前の状態に戻し，誤更新の原因を排除した上で処理3を再実行することとした。そこで，本番DBとは別に，作業用のデータベース（以下，作業用DBという）を用意し，作業ミスに備えて本番DBの“顧客”テーブルの全ての行を退避した上で，RDBMSの機能を利用した復旧手順を検討し，表9に整理した。表9 障害ケース③の復旧手順（未完成）設問1 〔実装するテーブルの設計〕について，(1)～(4)に答えよ。 (1) 表3中の［ア］～［ウ］に入れる適切な字句を答えよ。また，本文中の［エ］，［オ］に入れる表3中の項目A～Gのいずれか一つを答えよ。 (2) 表4中の［カ］～［ク］に入れる一つ又は複数のテーブル名を答えよ。 (3) 表5中の［ケ］，［コ］に入れる適切な定義内容を，記入済みの例に倣って，［ケ］は40字以内，［コ］は60字以内で具体的に述べよ。 (4) 制約④のAFTERトリガ内で，“商談”テーブルの更新時にロック待ちタイムアウトによる例外が返却された場合，トリガの契機となる変更を行った表2中の処理4のAPではどのような処置を行うべきか。行うべき処置を二つ挙げ，それぞれ25字以内で述べよ。設問2 〔問合せの検討〕について，(1)，(2)に答えよ。 (1) 表6中の問合せ名“R1”の下線部について，完全外結合を行う理由を40字以内で述べ，［サ］に入れる演算の内容を答えよ。 (2) 図3中の［シ］～［ソ］に入れる適切な字句を答えよ。設問3 〔バックアップ・リカバリの検討〕について，(1)～(3)に答えよ。 (1) 障害ケース①について，(a)，(b)に答えよ。 (a) 本文中の［a］～［f］に入れる適切な数値を答えよ。 (b) 本文中の下線部について，差分バックアップからの復元が増分バックアップからの復元よりも時間が掛からない理由を，30字以内で具体的に述べよ。 (2) 障害ケース②について，(a)，(b)に答えよ。 (a) バックアップの復元及び更新ログによる回復によって誤登録発生直前の日時の状態にする方法では問題を解決できない理由を，60字以内で述べよ。 (b) 図4は，相関副問合せを使用して更新を行うSQL文である。［g］に適切な字句を，［h］～［j］に一つの適切な述語を入れ，SQL文を完成させよ。 (3) 障害ケース③について，表9中の［k］～［s］に適切な字句を入れ，表を完成させよ。なお，［m］，［p］，［s］に入れる作業内容は，RDBMSの機能に指定する内容を含めてそれぞれ40字以内で記入すること。

図1 ミスマッチ分析表の例。物件コード1234567の○○マンションについて、価格帯5区分（3千万円未満～6千万円以上）を行、面積帯5区分（50㎡未満～80㎡以上）を列とし、各セルに住戸数（戸）／顧客数（名）を示した分析表。空白は該当なし。

図2 関係スキーマ（一部省略）。大エリア、小エリア、住所、鉄道事業者、沿線、駅、担当者、販売センタ、物件、最寄り駅、販売期、棟、住戸、顧客、商談、追客（サブタイプの受付・実施・応対）、契約の関係スキーマ。主キーは実線、外部キーは破線の下線で示されている。

表1 主な属性とその意味・制約。顧客コード、物件コード、販売価格・購入予算、専有面積・希望面積、商談ステータス（'1'～'9'）、契約ステータス（'6'～'8'）、追客区分（'1'～'3'）、追客種別、接触日時、最新接触日時の意味と制約をまとめた表。

表2 処理の例。処理1～7の内容を示す表。物件情報の変更反映、Webからの資料請求・来場予約の反映、顧客の名寄せ、契約データ反映、商談管理画面への追客表示、追客状況表の出力、ミスマッチ分析表用の集計が記載されている。

表3 実装方法の短所（未完成）。項目A～G（一意性制約の実装、格納効率、各種参照処理の性能）について、方法①～③の短所を示す表。空欄ア（方法①のD・F）、イ（方法②のG）、ウ（方法③のG）を含む。

表4 変更データ反映処理のグループとテーブル（未完成）。グループ番号1～6ごとに反映するテーブル名を示す表。1は大エリア・鉄道事業者、2は小エリア・沿線、3～5は空欄カ～ク、6は住戸。

表5 制約とその定義内容（未完成）。制約①～④の内容と定義内容を示す表。①は販売価格>0の検査制約、②は追客の重複禁止（空欄ケ）、③は契約ステータスの後退禁止（空欄コ）、④は商談ステータスを更新するAFTERトリガ。

表6 表2の処理7に用いる問合せの内容（未完成）。問合せM1、M2、T1、T2、R1の列名又は演算、参照テーブル、選択又は結合の内容を示す表。R1は価格帯（空欄サ）、網掛けの面積帯、住戸数、顧客数を求め、M2とT2を完全外結合（下線部）する。

図3 “M1”の問合せを実装するSQL文の検討例（未完成・一部省略）。案1はCASE式で価格帯と面積帯を求めるSELECT文。案2は価格帯・面積帯テーブルのCREATE TABLE文、価格帯へのINSERT文（空欄シ～セ）、両テーブルを内結合するSELECT文（空欄ソと網掛け部分）。

表7 想定した障害ケース。障害ケース①はディスク障害によるデータ全損、②は処理2での来場予約データの誤登録（SYSRAI01、2021-07-20 15:05～15:10）、③は処理3での名寄せ先顧客コードがNULLの顧客行の誤更新。

表8 試算対象テーブルのデータ量及び更新ログ件数見積り。商談：630,000行、平均行長720バイト、1ページ当たり10行、1時間当たり追加5,000・更新50,000。追客：24,000,000行、288バイト、25行、追加50,000・更新5,000。ページサイズ8,000バイト、空き領域率10%。

図4 “商談”テーブルの最新接触日時を更新するSQL文（未完成）。UPDATE 商談 Sで最新接触日時を追客Tの相関副問合せ（SELECT 空欄g、WHERE 空欄h AND i AND j）で設定する。対象は更新TSが誤登録期間内、更新担当者コードがSYSRAI01、削除フラグ0の行。

表9 障害ケース③の復旧手順（未完成）。手順1～6のRDBMSの機能、作業対象DB、作業内容を示す表。手順1は本番DBからのエクスポート、手順2は作業用DBへのバックアップ復元、手順3～5は空欄k～s、手順6は処理3の再実行。

解き方のヒントを見る

要件と設計判断の対応を確かめる

設問が求めている条件や前提を先に書き出し、それぞれがどの設計上の判断と結び付くのかを一つずつ対応付けて考えると、見落としや思い込みを減らせます。記述された前提から外れた一般論で判断しないよう注意しましょう。

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

確認すべき観点は一つで、問題中に示された業務上の前提や条件が、検討対象の設計判断を成り立たせる根拠として本当に書かれているかどうかです。前提と結論のつながりを自分の言葉で説明できるか試してください。

視点

データベース設計では、ある方式が適切かどうかは、方式そのものの良し悪しよりも、扱うデータの性質や運用上の条件との組合せで決まることが多いです。一般に知られた長所や短所を当てはめる前に、その条件が問題の状況に本当に当てはまるかを確かめることが大切です。

確認ポイント

問題中の前提や条件を、自分の言葉で短く書き出したか
検討中の判断ごとに、それを支える記述を問題中から指し示せるか
問題に書かれていない一般論や経験だけで判断していないか

正解と解説を見る

解説

不動産会社の商談管理システムを題材に、データベースの物理設計を幅広く問う問題です。前半では、追客サブタイプの実装方法、物件情報の参照制約に沿った反映順序、一意性制約・BEFOREトリガ・AFTERトリガによる業務制約の実装を扱います。中盤では、完全外結合とCOALESCEを使う集計問合せ、範囲テーブルを使う結合条件を扱います。後半はバックアップ・リカバリで、I/O回数から回復時間を求める試算、差分バックアップが有利な理由、誤登録データを論理削除した後に相関副問合せで最新接触日時を更新するUPDATE、エクスポートとインポートを組み合わせた部分復旧を扱います。どの設問も、本文の〔RDBMSの主な仕様〕と状況の記述を根拠に解答を組み立てることが重要です。

問題の全体像

D社は、マンション販売の商談管理システムを刷新しています。物件情報は物件管理システムから、資料請求と来場予約はWebサイトから、契約データは契約システムから受領し、APでデータベースに反映します。出題趣旨によると、本問で評価するのは次の四つの能力です。①論理データモデルを基に実装するテーブルを設計する能力、②業務上の制約を実装する能力、③問合せを設計する能力、④障害からリカバリする能力。設問1ではテーブル構造と制約の実装、設問2ではミスマッチ分析表を出力する問合せ、設問3では三つの障害ケースのリカバリが問われます。解答の根拠は、〔RDBMSの主な仕様〕（トリガの制限、バックアップの種類、復元・回復、エクスポート・インポートのオプション）と、表2・表7・表8に書かれた条件です。

設問1(1)(ア)

方法①は、スーパタイプと全サブタイプを一つのテーブルにまとめます。そのため、単一のサブタイプ（例えば“受付”だけ）を参照したいときは、テーブル内の全追客行の中から該当する区分の行だけを取り出さなければなりません。項目Dと項目Fがどちらも空欄アなのはこのためです。公式解答例では、アは「追客区分による選択が必要」です。他の方法ではサブタイプごとにテーブルが分かれているので、この選択は不要です。

設問1(1)(イ)

方法②は、スーパタイプとサブタイプを別々のテーブルにします。スーパタイプの属性と全サブタイプの属性をまとめて得るには、まず“追客”テーブルと各サブタイプのテーブルを主キーで結合し、さらに“受付”“実施”“応対”の三つの結果を和集合でまとめる必要があります。項目Eには「和集合が必要」、項目Fには「結合が必要」と記入されているので、Gはその両方にあたります。公式解答例では、イは「結合及び和集合が必要」です。

設問1(1)(ウ)

方法③では、スーパタイプの属性がすでに各サブタイプのテーブルに列として含まれているため、結合は不要です。ただし、三つのサブタイプのテーブルに分かれているので、全部を参照するには和集合が必要です。これは項目Eの方法③と同じ理屈です。公式解答例では、ウは「和集合が必要」です。

設問1(1)(エ)

処理5は、指定した物件と顧客の全ての追客について、接触日時と追客種別（スーパタイプの属性）を表示します。あわせて、追客区分ごとに固有の属性値（例：受付の開始予定日時・終了予定日時）から追客概要を作ります。つまり、スーパタイプと全部のサブタイプを参照する処理です。公式解答例では、エは「G」です。本文にある「方法②では処理5の応答時間に不安がある」という記述も、方法②のGには結合と和集合の両方が必要になることと合っています。

設問1(1)(オ)

処理6は、追客種別ごと・接触日時の日ごとに件数を集計するだけです。使う属性は物件コード、接触日時、追客種別で、いずれもスーパタイプ“追客”の属性です。サブタイプ固有の属性は使いません。公式解答例では、オは「C」です。採点講評では(1)イ及びオに誤答が散見されたとされています。処理が実際に参照する属性がどちらのタイプのものかを、図2と表2で確かめることが大切です。

設問1(2)(カ)

図2の外部キーをたどり、参照先のテーブルが自分より前のグループに入るように配置します。グループ1は、どこも参照しない大エリアと鉄道事業者です。グループ2は、それらを参照する小エリアと沿線です。住所は小エリアコードだけを参照しているので、小エリアの次のグループ3に置けます。公式解答例では、カは「住所」です。

設問1(2)(キ)

駅は、沿線コード（グループ2）と住所コード（グループ3）を参照するので、グループ4になります。物件は、物件情報のうち所在地住所コードで住所を参照するので、同じくグループ4です。販売センタは物件管理システムから受領する物件情報に含まれないため、順序付けの対象外です。公式解答例では、キは「駅，物件」です。

設問1(2)(ク)

最寄り駅は物件と駅を、販売期と棟は物件を参照するので、この三つはグループ4の次のグループ5に置けます。住戸は棟と販売期を参照するので、表4のとおりグループ6になります。公式解答例では、クは「最寄り駅，販売期，棟」です。同じグループのテーブルは並行して処理されるので、互いに参照関係がないことも確認しておきましょう。

設問1(3)(ケ)

制約②は「同じ顧客、接触日時及び追客種別の追客を行うことはない」、つまり三つの列の組合せが重複しないという制約です。一意性制約で実装できます。公式解答例では、「顧客コード，接触日時，追客種別を構成列とする一意性制約を定義する。」です。答案では、構成列の三つ（顧客コード・接触日時・追客種別）と、一意性制約という手段を、40字以内で両方書くことがポイントです。

設問1(3)(コ)

制約③は、契約ステータスが後退する変更を拒否する制約です。更新前の値と更新後の値を比べる必要があるので、同一行の列しか扱えない検査制約では実装できません。旧値（OROW）と新値（NROW）を参照できるトリガを使い、変更操作の前に判定して例外でエラーを通知するため、BEFOREトリガにします。BEFOREトリガ内では変更操作ができませんが、チェックだけなら問題ありません。公式解答例では、「OROW.契約ステータスとNROW.契約ステータスの値をチェックするBEFOREトリガを定義する。」です。答案で押さえる要素は、OROWとNROWの比較、BEFOREトリガの二つです。

設問1(4)①

RDBMSの仕様(5)には、トリガ内で発生した例外は契機となった変更操作に返却され、トランザクションは終了しないので明示的なコミット又はロールバックが必要、と書かれています。契約ステータスの更新だけをコミットすると、制約④（契約ステータスと商談ステータスの一致）が崩れてしまいます。そのため、まずトランザクションを取り消す必要があります。公式解答例では、一つ目の処置は「トランザクションをロールバックする。」です。

設問1(4)②

ロック待ちタイムアウトは、他のトランザクションがロックを解放すれば解消する一時的な例外です。そのため、ロールバックしただけで終わらせず、時間をおいてもう一度処理すれば反映できる見込みがあります。公式解答例では、二つ目の処置は「間隔を空けてトランザクションを再実行する。」です。採点講評では、(4)で例外の特性を考慮しない解答が散見されたとされています。「ロールバック」と「間隔を空けた再実行」を、それぞれ25字以内で書き分けることが求められます。

設問2(1)(理由)

図1には「／2名」（住戸はないが顧客はいる）や「1戸／」（住戸はあるが顧客はいない）のセルがあります。表2の処理7にも「住戸数と顧客数の片方又は両方を対応させて」とあります。M2（住戸の集計）とT2（顧客の集計）の一方にしかない価格帯・面積帯の組も出力する必要があるので、内結合や片側の外結合ではなく完全外結合を使います。公式解答例では、理由は「価格帯，面積帯の組がM2とT2のどちらか一方にしかない場合があるから」です。

設問2(1)(サ)

完全外結合では、相手側に対応する行がないと、その側の列はNULLになります。M2.価格帯だけを出力すると顧客だけの組で価格帯がNULLになり、T2.価格帯だけを出力するとその逆になります。そのため、NULLでない方を採用する必要があります。公式解答例では、サは「M2の価格帯とT2の価格帯のどちらかNULLでない方」又は「COALESCE(M2.価格帯、 T2.価格帯)」です。採点講評でも、完全外結合の結果の列値がNULLになることを理解した解答を求めたと述べられています。

設問2(2)(シ)

価格帯1は、案1の条件で「販売価格 < 3000」の範囲です。販売価格は0～99、999の整数なので、下限値は0です。上限値は、ソの比較演算に合わせて決めます。公式解答例では、シは「0、 3000」（上限を含まない＜で比較する場合）又は「0、 2999」（上限を含む＜=やBETWEENで比較する場合）です。どちらを選んでも、ソの条件と組み合わせたときに境界の値が漏れたり重複したりしないことが必要です。

設問2(2)(ス)

価格帯2は、3000以上4000未満の範囲です。シと同じ考え方で、公式解答例では、スは「3000、 4000」又は「3000、 3999」です。下限値は前の帯の上限と連続させ、上限値の決め方はシ・セと同じ方式にそろえます。

設問2(2)(セ)

価格帯5は6000以上の範囲です。表1から販売価格の最大値は99、999なので、それを含むように上限値を設定します。公式解答例では、セは「6000、 100000」（＜で比較する場合）又は「6000、 99999」（＜=やBETWEENで比較する場合）です。上限値をNULLにすると比較が成立しなくなるので、表1の値域を根拠に具体的な数値を入れます。

設問2(2)(ソ)

住戸Aの販売価格が、価格帯Bの下限値以上かつ上限値の範囲内にあれば、その価格帯と結合します。公式解答例では、ソは「A.販売価格 >= B.下限値 AND A.販売価格 < B.上限値」です。上限値を2999のように範囲に含める値で登録した場合は、「A.販売価格 >= B.下限値 AND A.販売価格 <= B.上限値」又は「A.販売価格 BETWEEN B.下限値 AND B.上限値」です。シ～セの値と整合する組合せで答えることがポイントです。

設問3(1)(a)a

本文で、“商談”テーブルの回復時のI/Oは404、000回と示されています。1ページ当たりのI/Oは0.01秒なので、404、000 × 0.01 = 4、040秒です。公式解答例では、aは「4、040」です。

設問3(1)(a)b

表8より、“追客”テーブルの追加は1時間当たり50、000件なので、8時間分で400、000件です。追加時は1ページ当たりの平均行数（25行）まではバッファ上で処理し、ページが一杯になるごとに空白ページを読み込みます。したがって、I/Oは400、000 ÷ 25 = 16、000回です。公式解答例では、bは「16、000」です。

設問3(1)(a)c

“追客”テーブルの更新は1時間当たり5、000件なので、8時間分で40、000件です。更新時はログ1件ごとに対象ページを読み込み、バッファヒット率は0とするので、I/Oは40、000回です。公式解答例では、cは「40、000」です。

設問3(1)(a)d

“追客”テーブルのI/O回数は、b＋c＝16、000＋40、000＝56、000回です。これに0.01秒を掛けると560秒になります。公式解答例では、dは「560」です。

設問3(1)(a)e

12:00、14:00、16:00にテーブル単位のバックアップを取ると、18:00の障害では16:00のバックアップから復元できます。その後に適用する更新ログは、16:00～18:00の2時間分で済みます。公式解答例では、eは「2」です。

設問3(1)(a)f

2時間分の“商談”テーブルは、追加10、000件で10、000 ÷ 10 = 1、000回、更新100、000件で100、000回、合わせて101、000回のI/Oです。これに0.01秒を掛けると1、010秒になります。公式解答例では、fは「1、010」です。

設問3(1)(b)

増分バックアップでは、12:00、14:00、16:00の各回に変更されたページが別々のファイルに入ります。復元時は全ファイルを順番に適用するので、何度も更新されたページはそのたびに上書きされます。一方、差分バックアップは、全体バックアップ以降に変更されたページの最新状態を一つのファイルに持つので、各ページを1回反映すれば済みます。“商談”テーブルは更新が1時間当たり50、000件と多く、同じ行が繰り返し更新されやすいので、差が大きくなります。“追客”テーブルは追加が中心なので、ほとんど差が出ません。公式解答例では、「同じ行への複数の更新を反映する必要がないから」です。30字以内で、同じ行（ページ）への複数回の更新を重ねて反映しなくてよい点を書きます。採点講評では、この設問の正答率が低かったとされています。

設問3(2)(a)

表7の障害ケース②では、誤登録の前後の時間帯にも断続的に追客を記録する業務が行われています。バックアップと更新ログで誤登録直前の状態に戻すと、誤登録期間中や誤登録後に行われた正しい登録や更新まで取り消され、失われてしまいます。公式解答例では、「誤登録が発生したデータ以外も誤登録前の状態に戻ってしまい，記録した情報が失われる。」です。60字以内で、誤登録以外のデータも戻ること、正当な記録が失われることの二点を押さえます。

設問3(2)(b)g

表1で、最新接触日時は「商談に紐付く追客の最新の接触日時」と定義されています。誤登録分を削除状態にした後、残っている追客の接触日時の最大値で最新接触日時を置き換えます。公式解答例では、gは「MAX(T.接触日時)」です。

設問3(2)(b)h

相関副問合せでは、外側で更新している商談Sの行ごとに、その商談に紐付く追客Tだけを集計します。“商談”と“追客”は、顧客コードと物件コードの組で対応しています。公式解答例では、hは「S.顧客コード = T.顧客コード」で、iと順不同です。採点講評では、この(2)(b)の正答率が低かったとされ、相関副問合せはUPDATE文でも利用できるので知っておいてほしいと述べられています。

設問3(2)(b)i

hと組になる、もう一つの対応条件です。顧客コードだけでは、同じ顧客の別の物件の追客まで含まれてしまいます。物件コードも一致させる必要があります。公式解答例では、iは「S.物件コード = T.物件コード」で、hと順不同です。

設問3(2)(b)j

直前の手順で、誤登録した“追客”の行は削除フラグ＝1（論理削除）に更新されています。論理削除では行が物理的に残るので、条件を付けないとMAXに誤登録の接触日時が含まれてしまいます。未削除の行だけを対象にする条件が必要です。公式解答例では、jは「T.削除フラグ = 0」です。

設問3(3)k

手順2では、作業用DBに当日0時の全体バックアップを復元しています。誤更新の直前（2021-07-21 13:45:11）までの状態にするには、更新ログを使ったロールフォワード処理が必要です。仕様6(1)により、異なる環境に復元した場合も同じように回復できます。公式解答例では、kは「更新ログによる回復機能」です。

設問3(3)l

本番DBは誤更新後も参照系で稼働しており、誤更新の発生中に行われた正しい追加・更新も含んでいます。そのため、過去の状態に戻す作業は本番DBではなく、手順2で復元した作業用DBで行います。公式解答例では、lは「作業用DB」です。

設問3(3)m

作業用DBを、誤更新が行われる直前の日時まで回復します。回復機能には「指定の時刻の状態に回復」という指定ができるので、その時刻を作業内容に含めます。公式解答例では、mは「誤更新が行われた直前の日時の状態に回復する。」です。

設問3(3)n

作業用DBの正しい状態の“顧客”データを本番DBへ移すには、まずファイルに抽出する必要があります。テーブル単位で抽出でき、抽出条件も指定できるのはエクスポート機能です。公式解答例では、nは「エクスポート機能」です。

設問3(3)o

正しい状態の“顧客”データがあるのは、手順3で回復した作業用DBです。公式解答例では、oは「作業用DB」です。

設問3(3)p

不正な状態になったのは、“顧客”テーブルのうち名寄せ先顧客コードがNULLの行です（表7）。誤更新された行だけを戻す方針なので、エクスポート時に抽出条件を指定し、対象行だけをファイルに出します。公式解答例では、pは「“顧客”テーブルのうち名寄せ先顧客コードがNULLの行をファイルに抽出する。」です。採点講評では、(3)でエクスポートに指定するテーブルの検索条件を書いていない解答が散見されたとされています。

設問3(3)q

エクスポートしたファイルを別の環境の同じ構造のテーブルに格納するのは、インポート機能です。公式解答例では、qは「インポート機能」です。

設問3(3)r

誤更新された行を正しい状態に戻す相手は、稼働中の本番DBです。公式解答例では、rは「本番DB」です。

設問3(3)s

本番DBには、主キーが一致する行（誤更新された行）がすでにあります。オプションを指定しないとSKIPと同じ動作になり、何も更新されません。既存の行を上書きするには、MERGEオプションの指定が必要です。公式解答例では、sは「手順4で抽出したファイルを，MERGEオプションを指定して格納する。」です。採点講評では、インポート機能に指定するオプションを書いていない解答が散見されたとされています。40字以内に、対象ファイルとMERGE指定を必ず含めます。

学習のポイント

採点講評では、設問3の(1)(b)（差分バックアップからの復元が増分バックアップからの復元より時間が掛からない理由）と(2)(b)（相関副問合せを使ったUPDATE文）の正答率が低かったとされ、相関副問合せはUPDATE文でも使えるので知っておいてほしいと述べられています。ほかにも採点講評では、次の点が指摘されています。・設問1(1)イ・オの誤答（業務処理に必要な関係演算に合ったテーブル構造の設計）・設問1(4)で例外の特性を考慮していない解答・設問2(1)サで、完全外結合の結果の列値がNULLになることを理解していない誤答・設問3(3)で、エクスポートの検索条件やインポートのオプションを書いていない解答学習では、この順に理由を説明できるか確かめるとよいでしょう。

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

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

問題