データベースの実装に関する次の記述を読んで，設問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）テーブルの列にはNOTNULL制約を指定することができる。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.関係“追客”及びそのサブタイプの実装方法検討
関係“追客”と，関係“受付”，“実施"，"応対”は，概念データモデルにおいてスーパタイプとサブタイプの関係にある。スーパタイプ，サブタイプのテーブルへの実装には次の方法1〜3があり，それぞれに，他の方法に比べて格納効率が悪い，他の方法では不要な選択・結合・和集合が必要になる，などの短所がある。そこで，表3を作成し，テーブル構造の特質に由来する実装上の短所をまとめた。短所がない場合は"-"を記入する。
方法① スーパタイプと全てのサブタイプを一つのテーブルにする。
方法② スーパタイプ，サブタイプごとにテーブルにする。
方法③ サブタイプだけを，それぞれテーブルにする。スーパタイプの属性は，列として各テーブルに保有する。
表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に用いる問合せの内容を，次の要領で表に整理した。
（1）行ごとに構成要素となる問合せを記述する。結果を他の問合せで参照する場合は，行に固有の名前（以下，問合せ名という）を付ける。
（2）列名又は演算には，テーブルから射影する列名又は演算によって求まる項目を“項目名=[演算の内容]”の形式で記述する。
（3）テーブル名又は問合せ名には，参照するテーブル名又は問合せ名を記入する。
（4）選択又は結合の内容には，テーブル名又は問合せ名ごとの選択条件，結合の具体的な方法と結合条件を記入する。
表6 表2の処理7に用いる問合せの内容（未完成）
表6中の“M1"の問合せを実装するためのSQL文の案を図3に示す。案1は，問合せをそのまま実装するもので，案2は，“価格帯”及び“面積帯”テーブルを追加することで，SQL文の変更なしに価格帯，面積帯の範囲を変えられるようにするものである。案2の1は“価格帯”及び“面積帯”テーブルを作成するSQL文，2は“価格帯”テーブルに行を追加するSQL文，3は追加したテーブルを用いて問合せの結果行を得るSQL文である。
図3 "M1"の問合せを実装するSQL文の検討例（未完成・一部省略）
1.障害発生の想定
バックアップリカバリの検討に当たり，本番環境のデータベース（以下，本番DBという）で想定される障害ケースを検討し，表7に整理した。ここで，データベース単位の全体バックアップを毎日0時に取得しているものとする。
表7 想定した障害ケース
2.障害ケース1の検討
障害ケース1のリカバリは，ディスク装置の復旧，バックアップからの復元，更新ログによる回復によって行う。このうち，“商談”及び“追客”テーブルの更新ログによる回復がリカバリに要する時間の大半を占めるので，回復に掛かる時間を試算し，業務への影響を調べることにした。試算に先立って，試算対象テーブルのデータ量及び更新ログ件数見積りを表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.障害ケース2の検討
障害ケース2は，バックアップの復元及び更新ログによる回復によって誤登録発生直前の日時の状態にする方法では問題を解決できない。そこで，誤登録したデータを削除するため，次の処理を順次実行することにした。
・誤登録した“追客”テーブルの行を削除状態に更新する。
・誤登録によって作成された“商談”テーブルの行を削除状態に更新する。
・誤登録によって作成された“顧客”テーブルの行を削除状態に更新する。
・“商談”テーブルの最新接触日時を更新する。
“商談”テーブルの最新接触日時を更新するSQL文を図4に示す。ここで，更新担当者コード，更新TSを更新する処理は省略している。
図4 “商談”テーブルの最新接触日時を更新するSQL文（未完成）
4.障害ケース3の検討
障害ケース3では，誤更新が行われた行だけを誤更新前の状態に戻し，誤更新の原因を排除した上で処理3を再実行することとした。そこで，本番DBとは別に，作業用のデータベース（以下，作業用DBという）を用意し，作業ミスに備えて本番DBの“顧客”テーブルの全ての行を退避した上で，RDBMSの機能を利用した復旧手順を検討し，表9に整理した。
表9 障害ケース3の復旧手順（未完成）
設問1 〔実装するテーブルの設計〕について，（1）〜（4）に答えよ。
（1）表3中の ア 〜 ウ に入れる適切な字句を答えよ。また，本文中の エ ， オ に入れる表3中の項目A〜Gのいずれか一つを答えよ。
解答・解説
解答例
ア：追客区分による選択が必要
イ：結合及び和集合が必要
ウ：和集合が必要
エ：G
オ：C
解説
ー
（2）表4中の カ 〜 ク に入れる一つ又は複数のテーブル名を答えよ。
解答・解説
解答例
カ：住所
キ：駅，物件
ク：最寄り駅，販売期，棟
解説
ー
（3）表5中の ケ ， コ に入れる適切な定義内容を，記入済みの例に倣って， ケ は40字以内， コ は60字以内で具体的に述べよ。
解答・解説
解答例
ケ：顧客コード，接触日時，追客種別を構成列とする一意性制約を定義する。
コ：OROW.契約ステータスと NROW.契約ステータスの値をチェックする BEFORE トリガを定義する。
解説
ー
（4）制約4のAFTERトリガ内で，“商談”テーブルの更新時にロック待ちタイムアウトによる例外が返却された場合，トリガの契機となる変更を行った表2中の処理4のAPではどのような処置を行うべきか。行うべき処置を二つ挙げ，それぞれ25字以内で述べよ。
解答・解説
解答例
・トランザクションをロールバックする。
・間隔を空けてトランザクションを再実行する。
解説
ー
設問2 〔問合せの検討〕について，（1），（2）に答えよ。
（1）表6中の問合せ名“R1"の下線部について，完全外結合を行う理由を40字以内で述べ， サ に入れる演算の内容を答えよ。
解答・解説
解答例
理由：価格帯，面積帯の組が M2 と T2 のどちらか一方にしかない場合があるから
・M2 の価格帯と T2 の価格帯のどちらか NULL でない方
・COALESCE(M2.価格帯, T2.価格帯)
解説
ー
（2）図3中の シ 〜 ソ に入れる適切な字句を答えよ。
解答・解説
解答例
シ：0, 3000
ス：3000, 4000
セ：6000, 100000
ソ：A.販売価格 >= B.下限値 AND A.販売価格 < B.上限値
別解
シ：0, 2999
ス：3000, 3999
セ：6000, 99999
ソ：A.販売価格 >= B.下限値 AND A.販売価格 <= B.上限値
又は A.販売価格 BETWEEN B.下限値 AND B.上限値
解説
ー
設問3 〔バックアップ・リカバリの検討〕について（1）〜（3）に答えよ。
（1）障害ケース1について，（a），（b）に答えよ。
（a）本文中の a 〜 f に入れる適切な数値を答えよ。
（b）本文中の下線部について，差分バックアップからの復元が増分バックアップからの復元よりも時間が掛からない理由を30字以内で具体的に述べよ。
解答・解説
解答例
a：4,040
b：16,000
c：40,000
d：560
e：2
f：1,010
同じ行への複数の更新を反映する必要がないから
解説
ー
（2）障害ケース2について，（a），（b）に答えよ。
（a）バックアップの復元及び更新ログによる回復によって誤登録発生直前の日時の状態にする方法では問題を解決できない理由を，60字以内で述べよ。
（b）図4は，相関副問合せを使用して更新を行うSQL文である。 g に適切な字句を， h 〜 j に一つの適切な述語を入れ，SQL文を完成させよ。
解答・解説
解答例
誤登録が発生したデータ以外も誤登録前の状態に戻ってしまい，記録した 情報が失われる。
g：MAX(T.接触日時)
h：S.顧客コード = T.顧客コード
i：S.物件コード = T.物件コード
j：T.削除フラグ = 0
解説
ー
（3）障害ケース3について，表9中の k 〜 s に適切な字句を入れ，表を完成させよ。
なお， m ， p ， s に入れる作業内容は，RDBMSの機能に指定する内容を含めてそれぞれ40字以内で記入すること。
解答・解説
解答例
手順3
k：更新ログによる回復機能
l：作業用 DB
m：誤更新が行われた直前の日時の状態に回復する。
手順4
n：エクスポート機能
o：作業用 DB
p：“顧客”テーブルのうち名寄せ先顧客コードが NULL の行をファイルに抽出する。
手順5
q：インポート機能
r：本番 DB
s：手順 4 で抽出したファイルを，MERGE オプションを指定して格納する。
解説
ー
IPA公開情報
出題趣旨
データベースの物理設計では，概念設計及び論理設計の結果をインプットとして，業務的な制約などの機能要件，性能，運用などの非機能要件の両方を考慮して，実際のテーブル定義を行い，さらに集計機能を実装して帳票出力するなど，SQL 文による効率の良い処理の設計が求められる。一方，データベースシステムの運用に当たっては，想定される障害に備えたリカバリ方法を検討した上で，適切なバックアップ計画を立案することが求められる。
本問では，不動産販売会社の商談管理システムを題材として，物理設計，データ操作，リカバリを行う能力を問う。具体的には，①論理データモデルを基に実装するテーブルの設計を行う能力，②業務上の制約を実装する能力，③問合せを設計する能力，④障害からのリカバリを行う能力を評価する。
採点講評
問 1 では，不動産販売会社の商談管理システムを題材に，データベースの物理設計について出題した。全体として正答率は高かった。
設問 1 では，テーブル及び制約の実装について出題した。(1)イ及びオでは誤答が散見された。業務処理に必要な関係演算に適したテーブル構造を設計するよう心掛けてほしい。(4)では，例外の特性を考慮しない解答が散見された。ロック待ちタイムアウトなどの例外の特性に応じた処理設計を心掛けてほしい。
設問 2 では，問合せに用いるメタデータを含めた問合せの設計について出題した。(1)サは完全外結合の結果の列値が NULL になることを理解できている解答を求めたが，誤答が散見された。関係演算についても正確な 知識をもってほしい。
設問 3 では，バックアップ・リカバリについて出題した。(1)(b)及び(2)(b)の正答率が低かった。相関副問合せは UPDATE 文でも利用できるので，是非知っておいてもらいたい。(3)では，エクスポート機能に指定するテーブルの検索条件，インポート機能に指定するオプションに関する記述のない解答が散見された。本文中の状況記述，RDBMSの仕様，設問の指示をよく読んで解答するよう心掛けてほしい。
