令和3年度 秋期 データベーススペシャリスト試験 午後Ⅱ 問題 問1 商談管理システムのテーブル設計とRDBMSの機能の活用

テクノロジデータベース

学習ガイド

マンションの開発と販売を手掛ける不動産会社のD社が、商談管理システムを刷新する際のデータベースの物理設計を扱う問題です。スーパタイプとサブタイプのテーブルへの実装方法の比較、ミスマッチ分析表を作るSQL、参照制約を守る処理の順序、トリガで例外が返ったときの処置、バックアップと更新ログを使ったデータの復旧が問われます。表3の短所やRDBMSの仕様の記述を、業務の処理に一つずつ当てはめて考えます。

この記事で押さえる論点

  • スーパタイプとサブタイプの関係をテーブルに実装する複数の方式と、それぞれの長所・短所を説明できる
  • データ抽出の条件に応じて完全外結合や関数を用い、集計や照合を行うSQL文を記述できる
  • トリガ内で例外が発生した際のトランザクションの振る舞いを理解し、アプリケーション側で行うべき処置を述べられる
  • バックアップと更新ログの仕様を踏まえ、誤更新から特定のデータを救済する復旧手順を説明できる

出題情報

出題
2021(R3)秋 データベーススペシャリスト 午後II 問1
配点
100点満点
模範解答
公表(設問ごとに掲載)
出題趣旨・採点講評(IPA 公表)

データベースの物理設計では，概念設計及び論理設計の結果をインプットとして，業務的な制約などの機能要件，性能，運用などの非機能要件の両方を考慮して，実際のテーブル定義を行い，さらに集計機能を実装して帳票出力するなど，SQL文による効率の良い処理の設計が求められる。一方，データベースシステムの運用に当たっては，想定される障害に備えたリカバリ方法を検討した上で，適切なバックアップ計画を立案することが求められる。
本問では，不動産販売会社の商談管理システムを題材として，物理設計，データ操作，リカバリを行う能力を問う。具体的には，①論理データモデルを基に実装するテーブルの設計を行う能力，②業務上の制約を実装する能力，③問合せを設計する能力，④障害からのリカバリを行う能力を評価する。

問1では，不動産販売会社の商談管理システムを題材に，データベースの物理設計について出題した。全体として正答率は高かった。

問題本文

データベースの実装に関する次の記述を読んで，設問1〜3に答えよ。

　D社は，マンションの開発及び販売を手掛ける不動産会社である。D社では，販売業務で利用している商談管理システムの老朽化に伴い，システムの刷新を行っている。

〔業務の概要〕

  1. 物件
    (1) 販売対象のマンション全体を物件という。物件内の建物を棟といい，棟内の個々の住居を住戸という。
    (2) 物件内の住戸を幾つかの販売期に分けて販売する。

  2. 組織
    (1) 物件の販売を行う拠点として，物件の近隣に販売センタを設営している。
    (2) 販売センタには，販売業務を行う担当者を配置している。
    (3) 販売センタの業務時間は毎日10〜19時である。

  3. 販売業務
    (1) 物件に興味をもった人を顧客といい，顧客ごと物件ごとに，商談の進捗度，接触回数，受注確度，希望面積，購入予算などを記録する。
    (2) 担当者は，資料請求の受付，資料の送付，販売センタへの来場予約受付，来場時の応接，希望条件・購入予算などを記した要望書の取得，購入申込みの受付，抽選会の案内・実施などの営業活動を行う。担当者が顧客との間で行うこれらの活動を追客ついきゃくといい，“受付”（資料請求，来場予約など），“実施”（資料送付，来場時の応接など），“応対”（購入申込受領，問合せ対応など）の区分に分けて記録する。
    (3) 追客を行うたびに商談管理システムにその内容を登録する。

  4. 販売分析業務
    　販売促進のための分析業務である。業務に用いるミスマッチ分析表の例を図1に示す。図1は，住戸の仕様（販売価格，専有面積）と顧客の要望（購入予算，希望面積）との一致度合いを確認するための帳票で，販売価格，購入予算を範囲によって価格帯に，専有面積，希望面積を範囲によって面積帯に分け，価格帯と面積帯の組ごとに“住戸数（戸）／顧客数（名）”を表示する。空白は該当なしを表す。

図1 ミスマッチ分析表の例
図の説明テキスト

枠内に以下のテキストと表が含まれる。
物件コード：1234567　物件名：○○マンション

価格帯 \ 面積帯 50 ㎡未満 50 ㎡以上 60 ㎡未満 60 ㎡以上 70 ㎡未満 70 ㎡以上 80 ㎡未満 80 ㎡以上
3千万円未満 4戸 / 3名 / 2名
3千万円以上 4千万円未満 4戸 / 4名 4戸 / 10名 / 5名
4千万円以上 5千万円未満 4戸 / 1名 2戸 / 2名 1戸 / 2名 / 1名
5千万円以上 6千万円未満 1戸 / 2戸 / 1名 / 2名
6千万円以上 1戸 / 2戸 /

〔商談管理システムの概要〕

  1. 関係スキーマ

商談管理業務の関係スキーマを図2に，主な属性とその意味・制約を表1に示す。

図2 関係スキーマ（一部省略）
図の説明テキスト

枠内に次の関係スキーマが記載されている（受付・実施・応対の3行は字下げされている）。原図では主キーを構成する属性名に実線の下線、外部キーを構成する属性名に破線の下線が付いており、ここではそれぞれ「(主キー)」「(外部キー)」と記す。
大エリア（大エリアコード(主キー)，大エリア名）
小エリア（小エリアコード(主キー)，小エリア名，大エリアコード(外部キー)）
住所（住所コード(主キー)，都道府県名，市区町村名，丁目，小エリアコード(外部キー)）
鉄道事業者（鉄道事業者コード(主キー)，鉄道事業者名）
沿線（沿線コード(主キー)，沿線名，鉄道事業者コード(外部キー)）
駅（沿線コード(主キー)，駅コード(主キー)，駅名，住所コード(外部キー)）
担当者（担当者コード(主キー)，販売センタコード(外部キー)，氏名）
販売センタ（販売センタコード(主キー)，販売センタ名，所在地住所コード(外部キー)，番地その他）
物件（物件コード(主キー)，物件名，販売センタコード(外部キー)，所在地住所コード(外部キー)，番地その他，…）
最寄り駅（物件コード(主キー)，沿線コード(主キー)，駅コード(主キー)，徒歩分数）
販売期（物件コード(主キー)，販売期コード(主キー)，販売期名，販売開始日，販売終了日，…）
棟（物件コード(主キー)，棟コード(主キー)，棟名称）
住戸（物件コード(主キー)，棟コード(主キー)，住戸コード(主キー)，販売価格，販売期コード(外部キー)，間取り，専有面積，…）
顧客（顧客コード(主キー)，氏名，住所コード(外部キー)，メールアドレス，電話番号，生年月日，名寄せ先顧客コード(外部キー)，…）
商談（顧客コード(主キー)，物件コード(主キー)，商談ステータス，最新接触日時，受注確度，希望面積，購入予算，…）
追客（顧客コード(主キー)，物件コード(主キー)，連番(主キー)，追客区分，接触日時，追客種別，…）
　受付（顧客コード(主キー)，物件コード(主キー)，連番(主キー)，開始予定日時，終了予定日時，…）
　実施（顧客コード(主キー)，物件コード(主キー)，連番(主キー)，実施結果メモ，…）
　応対（顧客コード(主キー)，物件コード(主キー)，連番(主キー)，応対内容，…）
契約（顧客コード(主キー)，物件コード(主キー)，棟コード(主キー)，住戸コード(主キー)，契約ステータス，成約価格，…）

表1 主な属性とその意味・制約
図の説明テキスト
属性名 意味・制約
顧客コード 顧客を一意に識別するコード（6桁の半角英数字）
物件コード 物件を一意に識別するコード（7桁の半角英数字）
販売価格，購入予算 販売価格，購入予算の金額（単位：万円，0〜99,999）
専有面積，希望面積 住戸の面積（単位：平方メートル，0.00〜999.99）
商談ステータス ‘1’（接触開始），‘2’（商談中），‘3’（要望書取得），‘4’（購入申込済），‘5’（抽選済），‘6’（仮契約済），‘7’（本契約済），‘8’（引渡済），‘9’（追客中止）のいずれか
契約ステータス ‘6’（仮契約済），‘7’（本契約済），‘8’（引渡済）のいずれか
追客区分 ‘1’（受付），‘2’（実施），‘3’（応対）のいずれか
追客種別 資料請求，資料送付，来場予約，来場応接，購入申込受領，問合せ対応など，追客の種類を識別するコード（2桁の半角英数字）
接触日時 追客ごとに顧客との接触を開始した日時
最新接触日時 商談に紐付く追客の最新の接触日時
  1. 外部システムとの連携

商談管理システムは，次のように外部システムとのデータ連携を行っている。

(1) 物件管理システムから物件情報（大エリア，小エリア，住所，鉄道事業者，沿線，駅，物件，最寄り駅，販売期，棟，住戸）の変更データを受領する。
(2) 自社のWebサイトから資料請求及び来場予約データを受領する。
(3) 契約システムから毎日複数回，契約データを受領する。
(4) 受領したデータは専用のアプリケーションソフトウェア（以下，APという）を用いて商談管理システムのデータベースに反映する。

  1. 処理の例

商談管理システムにおける処理の例を表2に示す。

表2 処理の例
図の説明テキスト
処理名 内容
処理 1 物件管理システムから受領した物件情報の変更データを反映する。AP は，関係ごとに追加，更新を記録したファイルを読み込み，データベースへの追加，更新を行う。
処理 2 Web サイトから受信する資料請求データには，資料請求日時，物件コード，顧客コード，氏名，住所コードなどが記録されている。同様に，来場予約データには，開始予定日時，物件コード，顧客コード，氏名などが記録されている。AP は，追客の追加，新規顧客の追加，商談の追加又は最新接触日時の更新を行う。
処理 3 同一の顧客を名寄せする。AP は，一定の条件に基づいて同一の顧客を抽出し，名寄せ元の名寄せ先顧客コードを更新する。
処理 4 契約システムから受領した契約データを基に，契約の契約ステータスを更新し，追客を追加する。
処理 5 指定した物件コード，顧客コードに該当する全ての追客を対象に，接触日時，追客種別，追客概要を商談管理画面に表示する。追客概要は，本処理において追客区分ごとに固有な属性値を基にして作成した文字列である。例えば，来場予約受付の追客概要は，“予約日時：2021-10-10 11:00〜12:00”のようになる。
処理 6 指定した物件コード，期間に該当する追客状況表を出力する。追客種別ごと接触日時の日ごとに件数を集計し，物件コード，年月日，資料請求数，来場予約数，来場応接数，購入申込受領数を出力する。
処理 7 図 1 のミスマッチ分析表を出力するために，指定した物件コードに該当する住戸の販売価格から価格帯を，専有面積から面積帯を求め，両者の組ごとに住戸数を集計する。同じように，商談の購入予算から価格帯を，希望面積から面積帯を求め，両者の組ごとに顧客数を集計する。価格帯と面積帯の組ごとに，該当する住戸数と顧客数の片方又は両方を対応させて，価格帯，面積帯，住戸数，顧客数を抽出する。

〔RDBMSの主な仕様〕

商談管理システムで利用するRDBMSの主な仕様は次のとおりである。

  1. ページ

RDBMSとストレージ間の入出力単位をページという。同じページに異なるテーブルの行が格納されることはない。

  1. テーブル・索引

(1) テーブルの列にはNOT NULL制約を指定することができる。NOT NULL制約を指定しない列にはNULLか否かを表す 1 バイトのフラグが付加される。

(2) 主キー制約には，主キーを構成する列名を指定する。

(3) 参照制約には，列名，参照先テーブル名，参照先列名を指定する。制約に違反する追加，更新，削除は拒否される。

(4) 検査制約には，同一行の列に対する制約を記述する。

(5) 索引には，ユニーク索引と非ユニーク索引がある。

  1. トリガ機能

テーブルに対する変更操作（追加，更新，削除）を契機に，あらかじめ定義した処理を実行する。

(1) 実行タイミング（変更操作の前又は後。前者のトリガを BEFOREトリガ，後者のトリガを AFTERトリガという），列値による実行条件を定義することができる。

(2) トリガ内では，変更操作を行う前の行，変更操作を行った後の行のそれぞれに相関名を指定することで，行の旧値，新値を参照することができる。

(3) BEFOREトリガの処理開始から終了までの同一トランザクション内では，どのテーブルに対しても変更操作を行うことはできない。

(4) トリガ内で例外を発生させることができる。

(5) トリガ内で発生した例外は，実行の契機となった変更操作に返却される。トリガ内で例外が発生した場合，変更操作を行うトランザクションは終了しないので，明示的なコミット又はロールバックが必要である。

  1. バックアップ機能

(1) バックアップの単位には，データベース単位，テーブル単位がある。

(2) バックアップには，取得するページの範囲によって，全体，増分，差分の3種類がある。

① 全体バックアップには，全ページが含まれる。

② 増分バックアップには，前回の全体バックアップ取得後に変更されたページが含まれる。ただし，前回の全体バックアップ取得以降に増分バックアップを取得していた場合は，前回の増分バックアップ取得後に変更されたページだけが含まれる。

③ 差分バックアップには，前回の全体バックアップ取得後に変更された全てのページが含まれる。

(3) 全体及び増分バックアップでは，取得ごとにバックアップファイルが作成される。差分バックアップでは，2回目以降の差分バックアップ取得ごとに，前回の差分バックアップファイルが最新の差分バックアップファイルで置き換えられる。

  1. 復元機能

(1) バックアップを用いて，バックアップ取得時点の状態に復元できる。

(2) 復元の単位はバックアップの単位と同じである。

(3) データベース単位の全体バックアップは，取得元とは異なる環境に復元することができる。

  1. 更新ログによる回復機能

(1) バックアップを用いて復元した後，更新ログを用いたロールフォワード処理によって，障害発生直前又は指定の時刻の状態に回復できる。データベース単位の全体バックアップを取得元と異なる環境に復元した場合も同様である。

(2) 一つのテーブルの回復に要する時間は，変更対象ページのストレージからの読込み回数に比例する。行の追加時には，バッファ上のページに順次追加し，空き領域を確保してページが一杯になるごとに空白ページを読み込む。行の更新時には，ログ1件ごとに対象ページを読み込む。バッファ上のページのストレージへの書込みは，非同期に行われるので，回復時間に影響しない。

  1. エクスポート機能

(1) テーブル単位にデータをファイルに抽出することができる。

(2) エクスポート対象テーブルの行の抽出条件を指定することができる。

  1. インポート機能

(1) エクスポート機能で抽出したファイルを，インポート先の環境の同じ構造のテーブルに格納することができる。

(2) インポート先のテーブルに主キーが一致するデータが存在する場合の動作として，処理しない（SKIP），又は更新する（MERGE）のいずれかのオプションを指定することができる。未指定時は SKIP を指定した場合と同等となる。

〔実装するテーブルの設計〕

  1. テーブルの列の追加

各テーブルには，登録担当者コード，登録 TS，更新担当者コード，更新 TS の各列を追加する。登録 TS には行追加時の時刻印を，更新 TS には行更新時の時刻印をそれぞれ設定する。また，行の削除は論理削除によって行うこととし，削除フラグ列（0：未削除，1：削除済）を追加する。

  1. 関係“追客”及びそのサブタイプの実装方法検討

関係“追客”と，関係“受付”，“実施”，“応対”は，概念データモデルにおいてスーパタイプとサブタイプの関係にある。スーパタイプ，サブタイプのテーブルへの実装には次の方法①～③があり，それぞれに，他の方法に比べて格納効率が悪い，他の方法では不要な選択・結合・和集合が必要になる，などの短所がある。そこで，表3を作成し，テーブル構造の特質に由来する実装上の短所をまとめた。短所がない場合は“−”を記入する。

方法① スーパタイプと全てのサブタイプを一つのテーブルにする。
方法② スーパタイプ，サブタイプごとにテーブルにする。
方法③ サブタイプだけを，それぞれテーブルにする。スーパタイプの属性は，列として各テーブルに保有する。

表3 実装方法の短所（未完成）
図の説明テキスト
記号 項目 実装方法の短所 - 方法① 実装方法の短所 - 方法② 実装方法の短所 - 方法③
A テーブル定義による一意性制約の実装 - - 実装不可
B 属性の格納効率 サブタイプの固有属性が冗長 - スーパタイプの属性が冗長
C スーパタイプを参照する処理の性能 - - -
D 単一のサブタイプを参照する処理の性能 ア - -
E 全部のサブタイプを参照する処理の性能 - 和集合が必要 和集合が必要
F スーパタイプと単一のサブタイプを参照する処理の性能 ア 結合が必要 -
G スーパタイプと全部のサブタイプを参照する処理の性能 - イ ウ

表3を基に，関係“追客”の実装方法を次のように判断した。

・方法③は，項目Aの短所からサブタイプ全体での主キーの一意性を，テーブルの一意性制約以外の方法によってチェックする必要があり採用し難い。
・表2中の処理5は表3の項目 エ に，処理6は表3の項目 オ に該当する。方法②では，処理5の応答時間に不安がある。
・方法①は，項目Bの短所による不利益はあるものの，項目D，Fに該当する処理は少なく，影響が限定的であることから方法①を採用する。

  1. 制約の実装検討

外部システムとの連携によってデータの整合性が失われることのないように，データベースの制約を定義する。

(1) 参照制約

図2の関係スキーマのうち，物件情報である大エリア，小エリア，住所，鉄道事業者，沿線，駅，物件，最寄り駅，販売期，棟及び住戸をそのままテーブルとして実装し，参照制約を定義するとしたら，表2中の処理1では，制約に違反しないように各テーブルへの変更反映処理を行わなければならない。そこで，テーブルを順序付けしてグループ番号1〜6に分け，参照制約に違反しない限りできるだけ小さいグループ番号に配置することにして表4を作成した。グループ番号順に直列に処理を行い，同じグループ内のテーブルへの処理は並行して行う。

表4 変更データ反映処理のグループとテーブル（未完成）
図の説明テキスト
グループ番号 テーブル名
1 大エリア，鉄道事業者
2 小エリア，沿線
3 カ
4 キ
5 ク
6 住戸

(2) 参照制約以外の制約

連携データの反映による整合性の喪失を防ぐための制約を，一意性制約，NOT NULL制約，検査制約，BEFOREトリガ，AFTERトリガのうち適切な方法を用いて定義する。制約とその定義内容を表5にまとめた。ここで，トリガ内の行の旧値を参照する相関名をOROW，新値を参照する相関名をNROWと定義するものとする。

表5 制約とその定義内容 (未完成)
図の説明テキスト
制約 定義内容
制約① 住戸には必ず0よりも大きい販売価格が設定されていなければならない。 “住戸”テーブルに，“販売価格 > 0”をチェックする検査制約を定義する。
制約② 同じ顧客，接触日時及び追客種別の追客を行うことはない。例えば，重複する資料請求データがあればエラーを通知する。 “追客”テーブルに，[ ケ ]
制約③ 契約ステータスが後退する（例：本契約済が仮契約に戻る）ことはない。そのような契約の変更にはエラーを通知する。 “契約”テーブルの行の更新を契機に，[ コ ]
制約④ 契約ステータスはできるだけ最新の状態でなければならず，契約手続の開始後は，契約ステータスと商談の商談ステータスは一致していなければならない。 “契約”テーブルの行の更新を契機に，NROW.契約ステータスに対応する商談ステータスの値を設定して“商談”テーブルを更新する AFTER トリガを定義する。

〔問合せの検討〕

　表2の処理7に用いる問合せの内容を，次の要領で表6に整理した。

(1) 行ごとに構成要素となる問合せを記述する。結果を他の問合せで参照する場合は，行に固有の名前（以下，問合せ名という）を付ける。

(2) 列名又は演算には，テーブルから射影する列名又は演算によって求まる項目を“項目名＝[演算の内容]”の形式で記述する。

(3) テーブル名又は問合せ名には，参照するテーブル名又は問合せ名を記入する。

(4) 選択又は結合の内容には，テーブル名又は問合せ名ごとの選択条件，結合の具体的な方法と結合条件を記入する。

表6 表2の処理7に用いる問合せの内容 (未完成)
図の説明テキスト
問合せ名 列名又は演算 テーブル名又は問合せ名 選択又は結合の内容
M1 棟コード, 住戸コード,
価格帯=[販売価格 3000未満:1,
4000未満:2, 5000未満:3,
6000未満:4, 6000以上:5],
面積帯=[専有面積 50未満:1,
60未満:2, 70未満:3, 80未満:4,
80以上:5]		 住戸 住戸から指定された物件コードに一致する行を選択
M2 価格帯, 面積帯, 住戸数=[価格帯, 面積帯でグループ化した行数] M1 全行を選択
T1 顧客コード,
価格帯=[購入予算 3000未満:1,
4000未満:2, 5000未満:3,
6000未満:4, 6000以上:5],
面積帯=[希望面積 50未満:1,
60未満:2, 70未満:3, 80未満:4,
80以上:5]		 商談 商談から指定された物件コードに一致する行を選択
T2 価格帯, 面積帯, 顧客数=[価格帯, 面積帯でグループ化した行数] T1 全行を選択
R1 価格帯=[ サ ],
面積帯=[網掛け],
住戸数, 顧客数		 M2, T2 M2の全行とT2の全行を選択し, 価格帯, 面積帯で完全外結合

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

表6中の“M1”の問合せを実装するためのSQL文の案を図3に示す。案1は，問合せをそのまま実装するもので，案2は，“価格帯”及び“面積帯”テーブルを追加することで，SQL文の変更なしに価格帯，面積帯の範囲を変えられるようにするものである。案2の①は“価格帯”及び“面積帯”テーブルを作成するSQL文，②は“価格帯”テーブルに行を追加するSQL文，③は追加したテーブルを用いて問合せの結果行を得るSQL文である。

図3 “M1”の問合せを実装するSQL文の検討例（未完成・一部省略）
図の説明テキスト

案1 問合せの選択条件をそのまま実装する。
SELECT 棟コード, 住戸コード,
CASE WHEN 販売価格 < 3000 THEN 1 WHEN 販売価格 < 4000 THEN 2
WHEN 販売価格 < 5000 THEN 3 WHEN 販売価格 < 6000 THEN 4 ELSE 5 END AS 価格帯,
CASE WHEN 専有面積 < 50 THEN 1 WHEN 専有面積 < 60 THEN 2
WHEN 専有面積 < 70 THEN 3 WHEN 専有面積 < 80 THEN 4 ELSE 5 END AS 面積帯
FROM 住戸 WHERE 物件コード = :hv1;
案2 SQL文の変更なしに価格帯，面積帯の範囲の変更を可能にする。
① “価格帯”テーブル及び“面積帯”テーブルを作成する。
CREATE TABLE 価格帯 (価格帯 SMALLINT, 下限値 INTEGER, 上限値 INTEGER,
PRIMARY KEY (価格帯));
CREATE TABLE 面積帯 (面積帯 SMALLINT, 下限値 DECIMAL(5, 2), 上限値 DECIMAL(5, 2),
PRIMARY KEY (面積帯));
② “価格帯”テーブルに行を追加する。
INSERT INTO 価格帯 VALUES( 1, [ シ ] );
INSERT INTO 価格帯 VALUES( 2, [ ス ] );
：（中略）
INSERT INTO 価格帯 VALUES( 5, [ セ ] );
③ “価格帯”，“面積帯”テーブルを使用して問合せの結果行を得る。
SELECT A.棟コード, A.住戸コード, B.価格帯, C.面積帯 FROM 住戸 A
INNER JOIN 価格帯 B ON [ ソ ]
INNER JOIN 面積帯 C ON [網掛け]
WHERE A.物件コード = :hv1;
注記1 ホスト変数 hv1 には，指定された物件コードが設定される。
注記2 網掛け部分は表示していない。

〔バックアップ・リカバリの検討〕

  1. 障害発生の想定

バックアップ・リカバリの検討に当たり，本番環境のデータベース（以下，本番DBという）で想定される障害ケースを検討し，表7に整理した。ここで，データベース単位の全体バックアップを毎日0時に取得しているものとする。

表7 想定した障害ケース
図の説明テキスト
障害ケース 障害状況
障害ケース① ディスク装置に障害が発生し，テーブルのデータが全損した。
障害ケース② 表2の処理2で，外部システムの不具合によって誤った来場予約データが大量に登録された。誤登録の行の更新担当者コードは SYSRAI01，誤登録の期間は 2021-07-20 の 15:05:00〜15:10:00 である。誤登録発生の前後の時間帯では，断続的に追客を記録する業務を行っている。また，誤登録の期間中，当該担当者コードによって誤登録以外の処理は行われていない。
障害ケース③ 表2の処理3で，“顧客”テーブルのうち，名寄せ先顧客コードが NULL の行が誤更新によって不正な状態となった。誤更新が行われた直前の日時は 2021-07-21 13:45:11 であり，誤更新の発生中にも“顧客”テーブルへの追加及び更新が行われている。また，誤更新の発生後，商談管理システムは参照系機能だけを利用可能な状態で稼働させ，後続の処理への影響はない。
  1. 障害ケース①の検討

障害ケース①のリカバリは，ディスク装置の復旧，バックアップからの復元，更新ログによる回復によって行う。このうち，“商談”及び“追客”テーブルの更新ログによる回復がリカバリに要する時間の大半を占めるので，回復に掛かる時間を試算し，業務への影響を調べることにした。試算に先立って，試算対象テーブルのデータ量及び更新ログ件数見積りを表8にまとめた。

表8 試算対象テーブルのデータ量及び更新ログ件数見積り
図の説明テキスト
テーブル名 見積行数 平均行長(バイト) 1ページ当たりの平均行数 1時間当たりの更新ログ件数 - 追加 1時間当たりの更新ログ件数 - 更新
商談 630,000 720 10 5,000 50,000
追客 24,000,000 288 25 50,000 5,000
注記 テーブルを格納する表領域のページサイズを 8,000 バイト，空き領域率を 10％とする。

(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 秒になる。

② バックアップからの復元に要する時間を考慮すると，“追客”テーブルは，増分と差分のどちらの方式でもほとんど変わらないが，“商談”テーブルは，増分バックアップよりも時間が掛からない差分バックアップを採用する。

  1. 障害ケース②の検討

障害ケース②は，バックアップの復元及び更新ログによる回復によって誤登録発生直前の日時の状態にする方法では問題を解決できない。そこで，誤登録したデータを削除するため，次の処理を順次実行することにした。

  • 誤登録した“追客”テーブルの行を削除状態に更新する。
  • 誤登録によって作成された“商談”テーブルの行を削除状態に更新する。
  • 誤登録によって作成された“顧客”テーブルの行を削除状態に更新する。
  • “商談”テーブルの最新接触日時を更新する。

“商談”テーブルの最新接触日時を更新するSQL文を図4に示す。ここで，更新担当者コード，更新TSを更新する処理は省略している。

図4 “商談”テーブルの最新接触日時を更新するSQL文（未完成）
図の説明テキスト
UPDATE 商談 S
SET S.最新接触日時 = (
  SELECT [ <span id="q1_blank_g" class="blank-label">g</span> ] FROM 追客 T
  WHERE [ <span id="q1_blank_h" class="blank-label">h</span> ] AND [ <span id="q1_blank_i" class="blank-label">i</span> ] AND [ <span id="q1_blank_j" class="blank-label">j</span> ] )
WHERE 更新TS BETWEEN CAST('2021-07-20 15:05:00.000000' AS TIMESTAMP)
  AND CAST('2021-07-20 15:10:00.000000' AS TIMESTAMP)
  AND 更新担当者コード = 'SYSRAI01'
  AND 削除フラグ = 0;
  1. 障害ケース③の検討

障害ケース③では，誤更新が行われた行だけを誤更新前の状態に戻し，誤更新の原因を排除した上で処理3を再実行することとした。そこで，本番DBとは別に，作業用のデータベース（以下，作業用DBという）を用意し，作業ミスに備えて本番DBの“顧客”テーブルの全ての行を退避した上で，RDBMSの機能を利用した復旧手順を検討し，表9に整理した。

表9 障害ケース③の復旧手順（未完成）
図の説明テキスト
手順 RDBMSの機能 作業対象DB 作業内容
手順1 エクスポート機能 本番DB “顧客”テーブルの全ての行を抽出する。
手順2 復元機能 作業用DB 2021-07-21の0時に取得した本番DBのバックアップを復元する。
手順3 [ k ] [ l ] [ m ]
手順4 [ n ] [ o ] [ p ]
手順5 [ q ] [ r ] [ s ]
手順6 - - 誤更新の原因を排除して処理3を再実行する。

注記1 RDBMSの機能には，〔RDBMSの主な仕様〕に記載されている機能名を一つ記入する。
注記2 作業対象DBには，作業用DB，本番DBのいずれかを記入する。

設問と解答・解説

設問1

〔実装するテーブルの設計〕について，(1)〜(4)に答えよ。

(1)配点 3点

表3中の ア に入れる適切な字句を答えよ。

模範解答

追客区分による選択が必要

採点基準

知識・理解度（内容）2点
  • 2点: 識別条件を用いた選択（絞り込み）が必要であることを明確に示している。
  • 1点: 選択が必要であること、または識別条件が必要であることの片方のみが示されている。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

表3の実装方法①は、スーパタイプと全てのサブタイプを1つのテーブルにまとめる方法です。この方法では、単一のサブタイプだけを参照したい場合でもテーブル全体が検索対象となります。そのため、対象のサブタイプを絞り込むために、追客区分などサブタイプを識別する列を条件に指定した選択（フィルタリング）処理が必要となり、検索性能が低下するという短所があります。

高得点のポイント

  • サブタイプを識別するための条件指定（追客区分などによる選択）が必要であることを示していること
  • 方法①が1つのテーブルで構成されているという構造的な特徴と結びつけて説明していること
(2)配点 3点

表3中の イ に入れる適切な字句を答えよ。

模範解答

結合及び和集合が必要

採点基準

知識・理解度（内容）2点
  • 2点: 結合と和集合の両方が必要であることを明確に示している。
  • 1点: 結合または和集合のいずれか片方のみが示されている。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

表3の実装方法②は、スーパタイプとサブタイプごとに独立したテーブルを作成する方法です。この方法でスーパタイプと全てのサブタイプをあわせて参照するには、まずスーパタイプと各サブタイプのテーブルをそれぞれ 結合（JOIN） し、さらに各サブタイプごとの結合結果同士の 和集合（UNION） を求める必要があります。これら複数の操作が必要となるため、処理性能が低下します。

高得点のポイント

  • 結合（JOIN）が必要であることを示していること
  • 和集合（UNION）が必要であることを示していること
(3)配点 3点

表3中の ウ に入れる適切な字句を答えよ。

模範解答

和集合が必要

採点基準

知識・理解度（内容）2点
  • 2点: 和集合だけが必要（結合は不要）であると述べている。
  • 1点: 「結合及び和集合が必要」のように不要な結合を含む、又は和集合に触れるが不完全。
  • 0点: それ以外。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

表3の実装方法③は、サブタイプごとにテーブルを作成し、スーパタイプの属性は各テーブルに持たせる方法です。この方法ではスーパタイプとサブタイプの結合は不要ですが、すべてのサブタイプの情報をあわせて参照するためには、各サブタイプのテーブル同士の 和集合（UNION） を求める操作が必要となります。

高得点のポイント

  • 結合は不要であり、各サブタイプのテーブルの和集合が必要であることを示していること
  • テーブル構造と関連付けて理由を説明していること
(4)配点 2点

本文中の エ に入れる表3中の項目A〜Gのいずれか一つを答えよ。

A. テーブル定義による一意性制約の実装
B. 属性の格納効率
C. スーパタイプを参照する処理の性能
D. 単一のサブタイプを参照する処理の性能
E. 全部のサブタイプを参照する処理の性能
F. スーパタイプと単一のサブタイプを参照する処理の性能
G. スーパタイプと全部のサブタイプを参照する処理の性能

模範解答

選択肢G: スーパタイプと全部のサブタイプを参照する処理の性能

解説

表2の処理5は「指定した物件コード、顧客コードに該当する全ての追客を対象に、接触日時、追客種別、追客概要を商談管理画面に表示する」処理です。
接触日時や追客種別はスーパタイプである「追客」の属性であり、追客区分ごとの固有の属性（追客概要の元データ）は各サブタイプの属性です。したがって、この処理はスーパタイプとすべてのサブタイプを同時に参照することになるため、表3の項目G「スーパタイプと全部のサブタイプを参照する処理の性能」に該当します。

各選択肢の解説

  • 処理5はスーパタイプと全てのサブタイプを参照するため、単一のサブタイプのみを対象とした処理（DやF）や、スーパタイプのみを対象とした処理（C）ではありません。
(5)配点 2点

本文中の オ に入れる表3中の項目A〜Gのいずれか一つを答えよ。

A. テーブル定義による一意性制約の実装
B. 属性の格納効率
C. スーパタイプを参照する処理の性能
D. 単一のサブタイプを参照する処理の性能
E. 全部のサブタイプを参照する処理の性能
F. スーパタイプと単一のサブタイプを参照する処理の性能
G. スーパタイプと全部のサブタイプを参照する処理の性能

模範解答

選択肢C: スーパタイプを参照する処理の性能

解説

表2の処理6は「指定した物件コード、期間に該当する追客状況表を出力する」処理です。
集計に用いる「接触日時」や「追客種別」は、いずれもスーパタイプである「追客」テーブルが持つ属性です。各サブタイプ固有の属性は参照しないため、表3の項目C「スーパタイプを参照する処理の性能」に該当します。

各選択肢の解説

  • サブタイプ特有の属性を利用しないため、サブタイプを参照する処理に関する項目（D, E, F, G）は誤りです。
(6)配点 3点

表4中の カ に入れる一つ又は複数のテーブル名を答えよ。

模範解答

住所

採点基準

正確性（内容）3点
  • 3点: 「住所」が正しく抜き出されている。
  • 1点: 不要な文字が含まれているが、対象は理解できる。
  • 0点: 記述がない、または誤り。

解説

表4は、参照制約に違反しないよう、親となるテーブルから先に更新処理を行うためのグループ分けです。
図2の関係スキーマを確認すると、住所テーブルは小エリアコードを外部キーとして持っており、小エリア（グループ2）を参照しています。また、駅テーブル（グループ4）や物件テーブル（グループ4）から所在地住所コードとして参照される立場にあります。
したがって、小エリアより後であり、駅・物件より前となるグループ3には「住所」が該当します。

高得点のポイント

  • 「住所」テーブルを正しく特定できていること
(7)配点 3点

表4中の キ に入れる一つ又は複数のテーブル名を答えよ。

模範解答

駅，物件

採点基準

正確性（内容）3点
  • 3点: 「駅」および「物件」が正しく抜き出されている。
  • 1点: いずれか1つのみが抜き出されている、または不要な文字が含まれている。
  • 0点: 記述がない、または誤り。

解説

表4は、参照制約の依存関係に基づくグループ分けです。
グループ4に配置されるテーブルは、グループ1〜3のテーブルを参照し、かつグループ5以降のテーブルから参照されるものです。
図2から、駅テーブルは沿線（グループ2）と住所（グループ3）を参照するためグループ4になります。また、物件テーブルは所在地住所（グループ3）などを参照し、最寄り駅（グループ5）等から参照されるため、同様にグループ4となります。

高得点のポイント

  • 「駅」と「物件」の両方を正しく特定できていること
(8)配点 3点

表4中の ク に入れる一つ又は複数のテーブル名を答えよ。

模範解答

最寄り駅，販売期，棟

採点基準

正確性（内容）3点
  • 3点: 「最寄り駅」「販売期」「棟」がすべて正しく抜き出されている。
  • 1点: 一部のテーブルのみが抜き出されている。
  • 0点: 記述がない、または誤り。

解説

表4のグループ5には、グループ4までのテーブルを参照し、かつグループ6の「住戸」から参照されるテーブル、あるいはそれ以上他のテーブルから参照されないテーブルが入ります。
図2から、最寄り駅は物件（グループ4）と駅（グループ4）を参照します。販売期と棟は物件（グループ4）を参照します。さらに、グループ6の「住戸」は物件、棟、販売期を参照するため、これら3つのテーブルはグループ5に配置されます。

高得点のポイント

  • 「最寄り駅」「販売期」「棟」の3つのテーブルをすべて特定できていること
(9)配点 2点

表5中の ケ に入れる適切な定義内容を，記入済みの例に倣って，40字以内で具体的に述べよ。

模範解答

顧客コード，接触日時，追客種別を構成列とする一意性制約を定義する。

採点基準

知識・理解度（内容）1点
  • 1点: 必要な構成列（顧客コード、接触日時、追客種別）と制約（一意性制約など）を明記している。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

制約②は「同じ顧客、接触日時及び追客種別の追客を行うことはない」という要件です。
これをデータベースの機能で実現するためには、該当する列の組み合わせに対して重複を許さない制約、すなわち 一意性制約（またはユニーク制約）を定義します。対象となる列は「顧客コード」「接触日時」「追客種別」の3つです。

高得点のポイント

  • 構成列として「顧客コード」「接触日時」「追客種別」を挙げていること
  • 実装方法として「一意性制約」または「ユニーク制約」を定義する旨を記述していること
(10)配点 2点

表5中の コ に入れる適切な定義内容を，記入済みの例に倣って，60字以内で具体的に述べよ。

模範解答

OROW.契約ステータスとNROW.契約ステータスの値をチェックするBEFOREトリガを定義する。

採点基準

知識・理解度（内容）1点
  • 1点: OROWとNROWの契約ステータスの比較と、BEFOREトリガを挙げている。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

制約③は「契約ステータスが後退する（例：本契約済が仮契約に戻る）ことはない」という業務要件です。
行の更新時に、更新前の状態と更新後の状態を比較し、ルールに反する場合はエラーにする処理が必要です。
RDBMSの機能において、行の変更操作前にチェックを行いエラーを返すには BEFOREトリガ を用います。トリガ内では、旧値を参照する相関名 OROW と、新値を参照する相関名 NROW の「契約ステータス」の値を比較し、ステータスの値が後退していないか（値が小さくなっていないか）をチェックするロジックを実装します。

高得点のポイント

  • OROW と NROW のそれぞれの契約ステータスを比較・チェックすることを示していること
  • 実装手段として BEFOREトリガ を用いることを明示していること
(11)配点 2点

制約④のAFTERトリガ内で，“商談”テーブルの更新時にロック待ちタイムアウトによる例外が返却された場合，トリガの契機となる変更を行った表2中の処理4のAPではどのような処置を行うべきか。行うべき処置を二つ挙げ，それぞれ25字以内で述べよ。（1つ目）

模範解答

トランザクションをロールバックする。

間隔を空けてトランザクションを再実行する。

採点基準

知識・理解度（内容）1点
  • 1点: 「トランザクションをロールバックする」又は「間隔を空けてトランザクションを再実行する」のどちらか一方を解答している（再実行の場合は間隔を空けることに触れていること）。
  • 0点: 間隔に触れない再実行、それ以外、または無解答。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

制約④のAFTERトリガ内で例外（ロック待ちタイムアウトなど）が発生した場合、〔RDBMSの主な仕様〕の3(5)に「トリガ内で例外が発生した場合，変更操作を行うトランザクションは終了しないので，明示的なコミット又はロールバックが必要である」と記載されています。
エラー状態に陥ったトランザクションは続行できないため、AP（アプリケーションソフトウェア）はまず実行中の トランザクションを明示的にロールバック し、データベースを元の状態に戻す必要があります。また、ロック待ちタイムアウトは一時的な競合によるものであるため、間隔を空けてトランザクションを再実行します。

高得点のポイント

(12)配点 2点

制約④のAFTERトリガ内で，“商談”テーブルの更新時にロック待ちタイムアウトによる例外が返却された場合，トリガの契機となる変更を行った表2中の処理4のAPではどのような処置を行うべきか。行うべき処置を二つ挙げ，それぞれ25字以内で述べよ。（2つ目）

模範解答

トランザクションをロールバックする。

間隔を空けてトランザクションを再実行する。

採点基準

知識・理解度（内容）1点
  • 1点: 1つ目の解答と異なるもう一方の処置を解答している（1つ目が誤答・無解答ならどちらか一方でも可）。
  • 0点: 1つ目と同じ内容、間隔に触れない再実行、それ以外、または無解答。
論理性（構造）1点
  • 1点: 1つ目と異なる処置として文意が通る。
  • 0点: 1つ目と同じ内容、または文意が通らない。

解説

制約④のAFTERトリガ内で例外が発生した際、その原因がロック待ちタイムアウトである場合、それは他トランザクションとの一時的な競合によるものです。
したがって、APは〔RDBMSの主な仕様〕の3(5)に従いトランザクションを明示的にロールバックして状態をリセットした後、一定の 間隔を空けてからトランザクションを再実行（リトライ） すれば、競合が解消して処理が成功する可能性が高くなります。

高得点のポイント

設問1では，テーブル及び制約の実装について出題した。(1)イ及びオでは誤答が散見された。業務処理に必要な関係演算に適したテーブル構造を設計するよう心掛けてほしい。(4)では，例外の特性を考慮しない解答が散見された。ロック待ちタイムアウトなどの例外の特性に応じた処理設計を心掛けてほしい。

設問2

〔問合せの検討〕について，(1)，(2)に答えよ。

(1)配点 3点

表6中の問合せ名“R1”の下線部（完全外結合）について，完全外結合を行う理由を40字以内で述べよ。

模範解答

価格帯，面積帯の組がM2とT2のどちらか一方にしかない場合があるから

採点基準

知識・理解度（内容）2点
  • 2点: 価格帯と面積帯の組み合わせが片方にしかない場合があることを過不足なく説明している。
  • 1点: 説明が不十分、または一部要素が欠けている。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

表6の問合せ名R1では、M2（住戸数の集計結果）とT2（顧客数の集計結果）を「価格帯、面積帯」をキーにして結合します。
図1のミスマッチ分析表を見ると、住戸が存在しないが顧客の要望があるセルや、逆に住戸は存在するが顧客の要望がないセル（値が空白のセル）があります。
つまり、価格帯と面積帯の特定の組み合わせが、M2とT2のどちらか一方にしか存在しない場合があります。内部結合（INNER JOIN）を行うと、片方にしか存在しない組み合わせのデータが欠落してしまうため、両方の結果を余さず抽出できる 完全外結合（FULL OUTER JOIN） が必要となります。

高得点のポイント

  • 価格帯と面積帯の組がM2またはT2の片方にしか存在しない場合があることを示していること
  • 完全外結合がデータの欠落を防ぐ目的であることを論理的に説明していること
(2)配点 3点

表6中の サ に入れる演算の内容を答えよ。

模範解答

M2の価格帯とT2の価格帯のどちらかNULLでない方

COALESCE(M2.価格帯, T2.価格帯)

採点基準

知識・理解度（内容）2点
  • 2点: 「どちらかNULLでない方」または「COALESCE(M2.価格帯, T2.価格帯)」としている（同値なCASE式も可）。
  • 1点: NULLでない方を採る意図はあるが列の指定に誤記がある。
  • 0点: 「M2.価格帯」「T2.価格帯」の一方だけ、またはそれ以外。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成・式になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

完全外結合を行った結果、ある価格帯・面積帯の組がM2にしか存在しない場合はT2の列が NULL になり、T2にしか存在しない場合はM2の列が NULL になります。
分析表に出力するための「価格帯」を取得するには、M2の価格帯とT2の価格帯のうち、NULLでない方の値 を採用する必要があります。SQLでは COALESCE(M2.価格帯, T2.価格帯) のような演算で表現されます。

高得点のポイント

  • M2の価格帯とT2の価格帯のうち、NULLでない方を採用することを示していること
  • COALESCE 関数などの適切なSQL関数を使用、あるいは論理的に記述できていること
(3)配点 3点

図3中の シ に入れる適切な字句を答えよ。

模範解答

0, 3000

0, 2999

採点基準

知識・理解度（内容）2点
  • 2点: 下限値と上限値の数値を正しく設定できている。
  • 1点: いずれか片方の数値のみ正しい。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 適切な形式で記述されている。
  • 0点: 形式が不適切。

解説

図3の案2における「価格帯」テーブルは、価格帯のIDと、その価格帯に属する金額の「下限値」および「上限値」を管理するテーブルです。
図1のミスマッチ分析表の定義によると、価格帯 1 は「3千万円未満」です。
金額の単位は「万円」であり、0〜99,999の範囲をとるため、下限値は 0 、上限値は 3000（条件に 未満 を用いる場合）となります。3000 を上限値とし、結合条件で < 上限値 とするのが一般的です。シ〜ソは、上限値を次の帯の下限値とする（3000 など。結合条件は「< 上限値」）か、帯に含める（2999 など。結合条件は「<= 上限値」又は BETWEEN）かのどちらかにそろえます。

高得点のポイント

  • 下限値が 0、上限値が 3000（または 2999）であることを正しく挙げていること
(4)配点 3点

図3中の ス に入れる適切な字句を答えよ。

模範解答

3000, 4000

3000, 3999

採点基準

知識・理解度（内容）2点
  • 2点: シと同じ決め方の値（シが「0, 3000」なら「3000, 4000」、「0, 2999」なら「3000, 3999」。シが誤答・無解答ならどちらでも可）としている。
  • 1点: 決め方はそろっているが一方の数値に誤りがある。
  • 0点: シと決め方が食い違う、それ以外。
論理性（構造）1点
  • 1点: 適切な形式で記述されている。
  • 0点: 形式が不適切。

解説

価格帯 2 は図1のミスマッチ分析表の定義によれば「3千万円以上 4千万円未満」です。
単位は「万円」であるため、下限値は 3000、上限値は 4000（結合条件で < 上限値 とする場合）となります。

高得点のポイント

  • 下限値が 3000、上限値が 4000（または 3999）であることを正しく挙げていること
(5)配点 3点

図3中の セ に入れる適切な字句を答えよ。

模範解答

6000, 100000

6000, 99999

採点基準

知識・理解度（内容）2点
  • 2点: シと同じ決め方の値（「6000, 100000」又は「6000, 99999」）としている。上限は販売価格の最大値99,999を含むこと。
  • 1点: 決め方はそろっているが下限・上限の一方に誤りがある。
  • 0点: シと決め方が食い違う、99,999を含まない上限、それ以外。
論理性（構造）1点
  • 1点: 適切な形式で記述されている。
  • 0点: 形式が不適切。

解説

価格帯 5 は図1のミスマッチ分析表の定義によれば「6千万円以上」です。
下限値は 6000 となります。上限値については、表1の「販売価格，購入予算」の制約に「0〜99,999」とあるため、この最大値（またはそれ以上の十分に大きな値、例えば 100000）を設定します。

高得点のポイント

  • 下限値が 6000、上限値がとりうる最大値以上（99999 や 100000 など）であることを正しく挙げていること
(6)配点 3点

図3中の ソ に入れる適切な字句を答えよ。

模範解答

A.販売価格 >= B.下限値 AND A.販売価格 < B.上限値

A.販売価格 >= B.下限値 AND A.販売価格 <= B.上限値

A.販売価格 BETWEEN B.下限値 AND B.上限値

採点基準

知識・理解度（内容）2点
  • 2点: シ〜セの決め方に対応する条件（上限値を次の下限値とした場合「A.販売価格 >= B.下限値 AND A.販売価格 < B.上限値」、上限値を含む場合「A.販売価格 >= B.下限値 AND A.販売価格 <= B.上限値」又は「A.販売価格 BETWEEN B.下限値 AND B.上限値」）としている（シ〜セが誤答・無解答ならいずれでも可）。
  • 1点: 比較の向きは正しいが等号の有無が決め方と食い違う。
  • 0点: それ以外。
論理性（構造）1点
  • 1点: 正しい文法・構造の述語として記述されている。
  • 0点: 文法が不適切。

解説

図3の案2③において、住戸テーブル（A）と価格帯テーブル（B）を結合する際の条件式です。
住戸の「販売価格」が、価格帯テーブルで定義された「下限値」以上、かつ「上限値」未満（または上限値を 2999 のように含める場合は以下）の範囲に収まるものを結合する必要があります。
したがって、結合条件は A.販売価格 >= B.下限値 AND A.販売価格 < B.上限値 （または BETWEEN 等を用いた式）となります。

高得点のポイント

  • 住戸の販売価格と価格帯の下限値・上限値とを比較する条件式になっていること
  • 不等号の向きや等号の有無が論理的に正しいこと

設問2では，問合せに用いるメタデータを含めた問合せの設計について出題した。(1)サは完全外結合の結果の列値がNULLになることを理解できている解答を求めたが，誤答が散見された。関係演算についても正確な知識をもってほしい。

設問3

〔バックアップ・リカバリの検討〕について，(1)〜(3)に答えよ。

(1)配点 2点

本文中の a に入れる適切な数値を答えよ。

模範解答

4,040

解説

障害ケース①の回復時間の試算です。
「商談」テーブルにおいて、障害発生時までに追加されたログは40,000件、更新されたログは400,000件あります。

  • 追加時のI/O：行の追加時はバッファ上のページに順次追加され、ページが一杯になるごとに空白ページをストレージから読み込みます。表8より1ページ当たりの平均行数は10行なので、空白ページの読み込み回数は 40,000÷10=4,00040,000 \div 10 = 4,000 回となります。
  • 更新時のI/O：ログ1件ごとに対象ページを読み込み、バッファヒット率は0と仮定されているため、I/O回数はログ件数と同じ 400,000400,000 回となります。
    これらを合計すると、回復に伴う総I/O回数は 404,000404,000 回です。1ページ当たりのI/O時間は10ミリ秒（0.01秒）なので、回復に必要な時間 aa は 404,000×0.01=4,040404,000 \times 0.01 = 4,040 秒となります。
(2)配点 2点

本文中の b に入れる適切な数値を答えよ。

模範解答

16,000

解説

「追客」テーブルに対する追加時のストレージI/O回数を求めます。
表8より、1時間当たりの追加ログ件数は50,000件であり、障害発生時点での8時間分の追加ログは 50,000×8=400,00050,000 \times 8 = 400,000 件です。
1ページ当たりの平均行数は25行であるため、ページが一杯になるごとの空白ページの読み込み（I/O）回数 bb は、400,000÷25=16,000400,000 \div 25 = 16,000 回となります。

(3)配点 2点

本文中の c に入れる適切な数値を答えよ。

模範解答

40,000

解説

「追客」テーブルに対する更新時のストレージI/O回数を求めます。
表8より、1時間当たりの更新ログ件数は5,000件であり、8時間分の更新ログは 5,000×8=40,0005,000 \times 8 = 40,000 件です。
更新時はバッファヒット率0としてログ1件ごとにページを読み込むため、ストレージへのI/O回数 cc はそのまま 40,00040,000 回となります。

(4)配点 2点

本文中の d に入れる適切な数値を答えよ。

模範解答

560

解説

「追客」テーブルの回復に必要な総時間を求めます。
設問(1)②の試算より、追加時のI/O回数は16,000回、更新時のI/O回数は40,000回です。
これらを合計した総I/O回数は 16,000+40,000=56,00016,000 + 40,000 = 56,000 回となります。
1回のI/Oに要する時間は0.01秒であるため、回復に必要な時間 dd は 56,000×0.01=56056,000 \times 0.01 = 560 秒となります。

(5)配点 2点

本文中の e に入れる適切な数値を答えよ。

模範解答

2

解説

対策として、毎日12:00、14:00、16:00にテーブル単位のバックアップを取得することにしました。
障害の発生時刻は18:00であるため、最も新しいバックアップは16:00に取得されたものです。
バックアップファイルからの復元後、回復のために適用しなければならない更新ログの期間は、16:00から18:00までの 2時間分 となります。したがって e=2e = 2 です。

(6)配点 2点

本文中の f に入れる適切な数値を答えよ。

模範解答

1,010

解説

最新のバックアップ（16:00取得）から18:00障害発生までの 2時間分 の「商談」テーブルの更新ログによる回復時間を求めます。

  • 追加のI/O：1時間当たり5,000件なので、2時間分で10,000件。1ページ当たり10行なので、10,000÷10=1,00010,000 \div 10 = 1,000 回。
  • 更新のI/O：1時間当たり50,000件なので、2時間分で100,000件。バッファヒット率0として 100,000100,000 回。
    合計I/O回数は 1,000+100,000=101,0001,000 + 100,000 = 101,000 回となります。
    1回0.01秒であるため、回復時間 ff は 101,000×0.01=1,010101,000 \times 0.01 = 1,010 秒となります。
(7)配点 2点

本文中の 下線部 について，差分バックアップからの復元が増分バックアップからの復元よりも時間が掛からない理由を，30字以内で具体的に述べよ。

模範解答

同じ行への複数の更新を反映する必要がないから

採点基準

知識・理解度（内容）1点
  • 1点: 差分バックアップなら同じ行（ページ）への複数回の更新を繰り返し反映しなくてよい（最新の差分ファイル1つを復元すれば済む、も可）としている。
  • 0点: それ以外。
論理性（構造）1点
  • 1点: 30字以内で理由として文意が通る。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

「商談」テーブルにおいて、増分バックアップではなく差分バックアップからの復元の方が時間が掛からない理由を考えます。
増分バックアップでは「前回の増分バックアップ取得後に変更されたページだけが含まれる」ため、復元の際は12:00、14:00、16:00のバックアップを順に適用していく必要があります。このとき、同じ行（ページ）が何度も更新されていた場合、重複して上書き処理が発生します。
一方、差分バックアップには「前回の全体バックアップ取得後に変更された全てのページ」が含まれており、最新のファイルにすべて集約されています。そのため、最新（16:00）の差分バックアップファイルのみを1回復元するだけで済み、「同じ行への複数の更新を反映する必要がない」 ため、復元時間が短縮されます。

高得点のポイント

  • 差分バックアップなら最新のファイル1つで済むこと、または増分のような繰り返しの適用（同じ行への複数の更新の反映）が不要であることを説明していること
(8)配点 2点

障害ケース②について，バックアップの復元及び更新ログによる回復によって誤登録発生直前の日時の状態にする方法では問題を解決できない理由を，60字以内で述べよ。

模範解答

誤登録が発生したデータ以外も誤登録前の状態に戻ってしまい，記録した情報が失われる。

採点基準

知識・理解度（内容）1点
  • 1点: 誤登録以外の正しく記録したデータ（誤登録の期間中・後に業務で記録した追客など）も戻って失われるとしている。
  • 0点: それ以外。
論理性（構造）1点
  • 1点: 60字以内で理由として文意が通る。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

障害ケース②において、誤登録が発生した日時（15:05:00）の直前の状態までデータベース全体をバックアップと更新ログ（ロールフォワード）で戻してしまう方法（不完全回復）の欠点を考えます。
問題文には「誤登録発生の前後の時間帯では，断続的に追客を記録する業務を行っている」とあります。そのため、データベース全体を過去の特定の時点に戻してしまうと、誤登録されたデータだけでなく、同時期に他の担当者によって正常に登録・更新された正しいデータ（業務記録）までが失われてしまうことになります。

高得点のポイント

  • データベース全体を過去に戻すことで、誤登録データ以外の正常なデータ（記録した情報）まで失われてしまうことを指摘していること
(9)配点 3点

図4は，相関副問合せを使用して更新を行うSQL文である。g に適切な字句を入れ，SQL文を完成させよ。

模範解答

MAX(T.接触日時)

採点基準

知識・理解度（内容）2点
  • 2点: MAX関数を用いて接触日時を取得する式を正しく記述している。
  • 1点: 関数や列名に軽微な誤りがある。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 正しいSQLの文法・構造で記述されている。
  • 0点: 文法が不適切。

解説

図4は、誤登録データによって影響を受けた「商談」テーブルの最新接触日時を、本来の正しい値に再計算・更新するための相関副問合せ（UPDATE文）です。
「最新接触日時」は、商談に紐付く「追客」テーブルの接触日時のうち、最新（最大）のものとなります。したがって、SELECT句の穴埋めである gg には、集約関数を用いた MAX(T.接触日時) が入ります。

高得点のポイント

  • MAX 関数を用いて接触日時の最大値を取得する式となっていること
(10)配点 3点

図4の h に一つの適切な述語を入れ，SQL文を完成させよ。

模範解答

S.顧客コード = T.顧客コード

S.物件コード = T.物件コード

T.削除フラグ = 0

採点基準

知識・理解度（内容）2点
  • 2点: 「S.顧客コード = T.顧客コード」「S.物件コード = T.物件コード」「T.削除フラグ = 0」のどれかとしている（左右を入れ替えた等号、「T.削除フラグ <> 1」も可）。
  • 1点: 列名・相関名に軽微な誤りがある。
  • 0点: それ以外。
論理性（構造）1点
  • 1点: 正しいSQLの述語として記述されている。
  • 0点: 文法が不適切。

解説

図4の相関副問合せにおいて、「追客」テーブル(T)の中から、更新対象である現在の「商談」テーブル(S)の行に紐づくデータだけを抽出するための結合条件（述語）を指定します。
商談と追客は、「顧客コード」と「物件コード」の複合キーで紐付きます。
したがって、結合条件の一つとして S.顧客コード = T.顧客コード （またはその逆）が必要です。なお、h・i・j は順不同です。

高得点のポイント

  • 商談(S)と追客(T)を顧客コードで結合する述語となっていること
(11)配点 3点

図4の i に一つの適切な述語を入れ，SQL文を完成させよ。

模範解答

S.顧客コード = T.顧客コード

S.物件コード = T.物件コード

T.削除フラグ = 0

採点基準

知識・理解度（内容）2点
  • 2点: 「S.顧客コード = T.顧客コード」「S.物件コード = T.物件コード」「T.削除フラグ = 0」のどれか（左右を入れ替えた等号、「T.削除フラグ <> 1」も可）で、hの解答と異なるものとしている（hが誤答・無解答なら3つのどれでも可）。
  • 1点: 列名・相関名に軽微な誤りがある。
  • 0点: hと同じ述語、それ以外。
論理性（構造）1点
  • 1点: 正しいSQLの述語として記述されている。
  • 0点: 文法が不適切。

解説

商談と追客を紐付けるもう一つの複合キーである「物件コード」の結合条件です。
結合条件の述語として S.物件コード = T.物件コード が必要となります。なお、h・i・j は順不同です。

高得点のポイント

  • 商談(S)と追客(T)を物件コードで結合する述語となっていること
(12)配点 3点

図4の j に一つの適切な述語を入れ，SQL文を完成させよ。

模範解答

S.顧客コード = T.顧客コード

S.物件コード = T.物件コード

T.削除フラグ = 0

採点基準

知識・理解度（内容）2点
  • 2点: 「S.顧客コード = T.顧客コード」「S.物件コード = T.物件コード」「T.削除フラグ = 0」のどれか（左右を入れ替えた等号、「T.削除フラグ <> 1」も可）で、h及びiの解答と異なるものとしている。
  • 1点: 列名・相関名に軽微な誤りがある。
  • 0点: h又はiと同じ述語、それ以外。
論理性（構造）1点
  • 1点: 正しいSQLの述語として記述されている。
  • 0点: 文法が不適切。

解説

最新の接触日時を計算する対象は、有効な（論理削除されていない）追客データでなければなりません。
障害対応の手順として、直前の処理で誤登録された行の「削除フラグ」を 1 に更新しています。そのため、正しいデータを取得するための条件（述語）として、T.削除フラグ = 0 が必要です。なお、h・i・j は順不同です。

高得点のポイント

  • 削除状態でない（削除フラグ = 0）行を選択する条件となっていること
(13)配点 3点

障害ケース③について，表9中の k に適切な字句を入れ，表を完成させよ。

模範解答

更新ログによる回復機能

採点基準

正確性（内容）3点
  • 3点: 「更新ログによる回復機能」が正しく抜き出されている。
  • 1点: 不要な文字が含まれているが、対象は理解できる。
  • 0点: 記述がない、または誤り。

解説

障害ケース③の復旧手順です。
手順2で作業用DBに0時時点のバックアップを復元しました。
続く手順3では、このバックアップを起点として、誤更新が行われた直前（13:45:11）の状態までデータベースをロールフォワード（回復）させます。
〔RDBMSの主な仕様〕の6に記載されている、更新ログを用いて指定の時刻の状態に回復させる機能の名称は「更新ログによる回復機能」です。

(14)配点 3点

障害ケース③について，表9中の l に適切な字句を入れ，表を完成させよ。

模範解答

作業用DB

採点基準

正確性（内容）3点
  • 3点: 「作業用DB」が正しく抜き出されている。
  • 1点: 不要な文字が含まれているが、対象は理解できる。
  • 0点: 記述がない、または誤り。

解説

手順3において、指定時刻までのロールフォワード回復を行う対象のデータベースを指定します。
本番DBは引き続き参照機能などを稼働させているため、ロールフォワード操作は安全な別の環境である「作業用DB」に対して行われます。

(15)配点 2点

障害ケース③について，表9中の m に入れる作業内容を，RDBMSの機能に指定する内容を含めて40字以内で記入せよ。

模範解答

誤更新が行われた直前の日時の状態に回復する。

採点基準

知識・理解度（内容）1点
  • 1点: 誤更新の直前（13:45:11など）まで回復することを過不足なく記述している。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

手順3の作業内容です。
作業用DBに復元された0時時点のバックアップに対し、更新ログによる回復機能を適用して、誤更新が行われた直前の日時（2021-07-21 13:45:11）の状態に回復させます。

高得点のポイント

  • 回復機能を用いて、誤更新直前の日時の状態まで復元する旨を記述していること
(16)配点 3点

障害ケース③について，表9中の n に適切な字句を入れ，表を完成させよ。

模範解答

エクスポート機能

採点基準

正確性（内容）3点
  • 3点: 「エクスポート機能」が正しく抜き出されている。
  • 1点: 不要な文字が含まれているが、対象は理解できる。
  • 0点: 記述がない、または誤り。

解説

手順4の作業内容です。
作業用DB上で正しい状態に回復させた「顧客」テーブルの特定のデータを抽出し、ファイルに出力する必要があります。
〔RDBMSの主な仕様〕の7に記載されている、テーブルのデータを選択してファイルに出力する機能の名称は「エクスポート機能」です。

(17)配点 3点

障害ケース③について，表9中の o に適切な字句を入れ，表を完成させよ。

模範解答

作業用DB

採点基準

正確性（内容）3点
  • 3点: 「作業用DB」としている。
  • 1点: 「作業用のDB」など表記の揺れがある。
  • 0点: 「本番DB」、それ以外。

解説

手順4のエクスポートは、手順3で誤更新直前の状態に回復した作業用DBから行います（表7の障害ケース③は処理3で名寄せ先顧客コードがNULLの行が誤更新された障害です）。

(18)配点 2点

障害ケース③について，表9中の p に入れる作業内容を，RDBMSの機能に指定する内容を含めて40字以内で記入せよ。

模範解答

“顧客”テーブルのうち名寄せ先顧客コードがNULLの行をファイルに抽出する。

採点基準

知識・理解度（内容）1点
  • 1点: 抽出条件として名寄せ先顧客コードがNULLの行（“顧客”テーブル）を指定している。
  • 0点: 条件が無い、別の条件、それ以外。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

表7の障害ケース③では、名寄せ先顧客コードがNULLの行が誤更新によって不正な状態となっており、誤更新の発生中にも“顧客”テーブルへの追加及び更新が行われているため、誤更新の対象行だけを抽出します（〔RDBMSの主な仕様〕の7(2) 抽出条件の指定）。

(19)配点 3点

障害ケース③について，表9中の q に適切な字句を入れ，表を完成させよ。

模範解答

インポート機能

採点基準

正確性（内容）3点
  • 3点: 「インポート機能」が正しく抜き出されている。
  • 1点: 不要な文字が含まれているが、対象は理解できる。
  • 0点: 記述がない、または誤り。

解説

手順5の作業内容です。
手順4で出力した正しいデータのファイルを読み込み、元のデータベースに反映させます。
〔RDBMSの主な仕様〕の8に記載されている、ファイルに保存されたデータをテーブルに取り込む機能の名称は「インポート機能」です。

(20)配点 3点

障害ケース③について，表9中の r に適切な字句を入れ，表を完成させよ。

模範解答

本番DB

採点基準

正確性（内容）3点
  • 3点: 「本番DB」としている。
  • 1点: 「本番環境のDB」など表記の揺れがある。
  • 0点: 「作業用DB」、それ以外。

解説

手順4で抽出した正しい行を、誤更新された本番DBに格納します（表9の注記2により作業対象DBは作業用DB・本番DBのいずれかとなります）。

(21)配点 2点

障害ケース③について，表9中の s に入れる作業内容を，RDBMSの機能に指定する内容を含めて40字以内で記入せよ。

模範解答

手順4で抽出したファイルを，MERGEオプションを指定して格納する。

採点基準

知識・理解度（内容）1点
  • 1点: MERGEオプションを指定することを過不足なく記述している。
  • 0点: 記述がない、または誤り。
論理性（構造）1点
  • 1点: 論理的で意味が通る文構成になっている。
  • 0点: 文脈が不明瞭、または論理的でない。

解説

手順5におけるインポート時のオプション指定です。
本番のデータベースにはすでに該当の行（主キーが同一の行）が存在していますが、名寄せ先顧客コードがNULLだった行が誤更新された状態になっています。
〔RDBMSの主な仕様〕の8(2)によると、インポート機能において主キーが同一の行が既に存在する場合、未指定時は SKIP（処理しない）と同等で誤更新された行が戻らないので、MERGEオプション を指定することで既存の行を上書き（更新）することができます。

高得点のポイント

  • MERGEオプション（または既存行を更新するオプション）を指定する旨が記述されていること

設問3では，バックアップ・リカバリについて出題した。(1)(b)及び(2)(b)の正答率が低かった。相関副問合せはUPDATE文でも利用できるので，是非知っておいてもらいたい。(3)では，エクスポート機能に指定するテーブルの検索条件，インポート機能に指定するオプションに関する記述のない解答が散見された。本文中の状況記述，RDBMSの仕様，設問の指示をよく読んで解答するよう心掛けてほしい。

Spineチーム