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

平成28年度春期データベーススペシャリスト午後Ⅱ 問1

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

問題

問1　データベースの物理設計とデータ移行に関する次の記述を読んで，設問1～3に答えよ。 D銀行は，首都圏の100支店に，投資信託などの運用商品を扱う部課があり，専任の営業員が個人顧客（以下，顧客という）への販売活動を行っている。営業員の販売活動には，顧客の個人情報，資産情報，投資経験，取引などを格納した顧客情報管理システムが利用されている。D銀行では，顧客情報管理システムのDBサーバ及びストレージの老朽化に伴うリプレース（以下，DBサーバ更改という）に当たって，RDBMSのバージョンアップ，アプリケーション（以下，APという）の機能追加などを行うことにした。〔データベースの論理設計〕顧客情報管理システムの概念データモデルを図1に，テーブル構造を図2に，主な列とその意味・制約を表1に示す。図1 概念データモデル図2 テーブル構造（一部省略）表1 主な列とその意味・制約〔顧客情報管理システムの現状とDBサーバ更改〕 1．現状（1）顧客情報管理システムは，Webサーバ，APサーバ，DBサーバから成るシステムで，内部ネットワーク上に設置されている。内部ネットワークは，外部ネットワークから隔離されているので，外部から侵入されることはない。（2）案件，取引，コンタクト履歴には，案件の登録日が10年前の月初めから現在までの全てのデータが保存されている。コンタクト履歴は，過去のデータが更新されることもある。保存期間が過ぎた案件及び案件に関連するデータは，毎月末日のバッチ処理で削除される。 2．DBサーバ更改の方針（1）新DBサーバを導入し，現行DBサーバと同じネットワーク上に設置する。（2）現行DBサーバのデータを新DBサーバに移行し，新DBのデータ検証後にAPサーバからの接続先を新DBサーバに切り替える。（3）本番開始前1か月程度の移行準備期間（以下，準備期間という）には，日々のバッチ処理終了後の6時間だけサービスを停止する。本番開始直前のデータ移行・検証期間（以下，移行期間という）は，連続して2日間サービスを停止する。本番開始後には，1か月程度の特別な監視体制の維持期間（以下，監視期間という）を設ける。（4）データ移行時には，その月の末日のバッチ処理で削除されることが分かっているデータは，移行の対象としない。また，各テーブルの最終更新TSの列値は，システムで自動設定せず，そのまま移行する。 3．DBサーバ更改時の実施事項（1）現行DBサーバで稼働するRDBMSは，上位バージョンに移行する。（2）顧客とのアポイントメントを案件と関係付けて管理するために，スケジュール管理機能を追加する。スケジュール管理機能の提供は，DBサーバ更改に合わせて開始する。（3）個人情報の漏えい対策を強化する。（4）取扱商品の増加に備えて，商品コードの桁数を4桁から6桁に増やす。移行時に，現在の商品コードの前に“00”を付加する。〔RDBMSの仕様〕 1．表領域（1）テーブル，索引などのストレージ上の物理的な格納場所を，表領域という。（2）RDBMSとストレージ間のデータ入出力単位を，データページという。データページには，テーブル，索引のデータが格納される。表領域ごとに，ページサイズ（1データページの長さ。2、000，4、000，8、000，16、000バイトのいずれかである）と，空き領域率（将来の更新に備えて，データページ内に確保しておく空き領域の割合）を指定する。（3）同じデータページに，異なるテーブルの行が格納されることはない。 2．テーブル（1）テーブルの列には，NOT NULL制約を指定することができる。NOT NULL制約を指定しない列には，NULLかどうかを表す1バイトのフラグが付加される。（2）主キー制約には，主キーを構成する列名を指定する。（3）参照制約には，列名，参照先テーブル名，参照先列名を指定する。（4）検査制約には，同一行の列に対する制約を指定する。（5）使用可能なデータ型は，表2のとおりである。表2 使用可能なデータ型 3．索引索引には，ユニーク索引と非ユニーク索引がある。 4．DBの相互接続機能同じネットワーク上にある異なるDBを，相互接続する機能である。相互接続したDB上のテーブルは，あたかも同じDB上にあるかのように，一つのSQL文で操作することができる。SQL文では，テーブル名をDBの識別子とスキーマ名の両方で修飾することができる。 5．エクスポートツール，インポートツールテーブルのデータをテキストファイルに出力するエクスポートツールと，テキストファイルのデータをテーブルに入力するインポートツールがある。エクスポートツールでは，SQLのSELECT文を指定することができる。一方，インポートツールでは，SQLのINSERT文と同じ処理が行われる。〔データベースの物理設計〕 1．テーブル定義次の方針に基づいて，テーブル定義表を作成し，テーブル定義を行う。（1）データ型欄には，データ型，データ型の適切な長さ，精度，位取りを記入する。データ型の選択は，次の規則に従う。 ① 文字列型の列が全角文字の場合は，NCHAR又はNCHAR VARYINGを選択し，それ以外の場合はCHAR又はVARCHARを選択する。 ② 数値の列が整数である場合は，取り得る値の範囲に応じて，SMALLINT又はINTEGERを選択する。それ以外の場合はDECIMALを選択する。 ③ ①及び②どちらの場合も，列の取り得る値の範囲に従って，格納領域の長さが最小になるようにデータ型を選択する。 ④ 日付，時刻，時刻印の列は，専用のデータ型を選択する。（2）NOT NULL欄には，NOT NULL制約がある場合はYを，ない場合はNを記入する。（3）格納長欄には，RDBMSの仕様に従って，格納長を記入する。可変長文字列の格納長は，表1から平均文字数が分かる場合はそれを基準に算出し，それ以外の場合は最大文字数の半分を基準に算出する。（4）索引の種類と構成列欄には，作成する索引を記入する。 ① 索引の種類には，P（主キーの索引），U（ユニーク索引），NU（非ユニーク索引）がある。 ② 主キーの索引は，必ず作成する。 ③ 主キー以外で値が一意となる列又は列の組合せには，必ずユニーク索引を作成する。それ以外の列又は列の組合せが，外部キーを構成する場合は，必ず非ユニーク索引を作成する。 ④ 各索引の構成列には，構成列の定義順に1からの連番を記入する。（5）制約欄には，参照制約，検査制約をSQLの構文で記入する。 “顧客基本”，“スケジュール”テーブルのテーブル定義表を表3，表4に示す。表3 “顧客基本”テーブルのテーブル定義表表4 “スケジュール”テーブルのテーブル定義表（作成中） 2．データ所要量の見積り “スケジュール”テーブルのデータ所要量を見積もるために，表5を作成した。なお，データ所要量は，項番1～5の値を用いて算出するものとする。表5 “スケジュール”テーブルのデータ所要量（未完成） 3．個人情報保護の強化外部ネットワークから隔離されていても，内部の人間の犯行などによる個人情報の漏えいが懸念される。強化策として，次に示す“顧客基本”テーブル実装案を検討し，想定されるセキュリティ事故A～Cへの対策の有効性を評価して，表6にまとめた。案：顧客名，住所，電話番号の列値を，次のように格納，検索する。・APは，RDBMSの暗号化関数によってデータを暗号化し，SQL文によって列に格納する。このとき，各列のデータ型，データ長の定義を変更する。・APは，SQL文によって暗号化したデータを検索し，RDBMSの復号関数によって復号する。・暗号化と復号の鍵は，AP内に埋め込み，外部から見えないようにする。表6 セキュリティ事故と対策の有効性評価（未完成）〔データ移行の前提・移行計画・データ移行処理方式設計〕 1．データ移行の前提（1）テーブルへのデータ投入順序テーブルへのデータ投入順序を，表7に示す。表7では，図2の“スケジュール”以外のテーブルを，参照制約違反が発生しない限り，できるだけ小さい枠番号のいずれかに配置し，枠番号順にデータ投入を行う。同じ枠番号内のテーブルへのデータ投入は，並行して行われる。表7 テーブルへのデータ投入順序（未完成）（2）データ量の調査現行DBの主なテーブルの統計情報を，表8に示す。表8 現行DBの主なテーブルの統計情報（3）データ移行方法現行DBから新DBにデータを移行するには，次に示す二つの方法がある。二つの方法について，それぞれ主なテーブルのデータ移行処理速度を実測し，表9を作成した。 ① エクスポート・インポート（E&I）現行DBのテーブルからデータをエクスポート（EXP）したファイルを新DBサーバに転送し，新DBのテーブルにインポート（IMP）する。EXPとIMPには，それぞれエクスポートツールとインポートツールを用いる。 ② ダイレクトインサート（DI） DBの相互接続機能を利用して，SQL文の実行によって，現行DBのテーブルのデータを読み込み，新DBのテーブルのデータを直接，追加，更新，削除する。表9 主なテーブルのデータ移行処理速度注記　ファイル転送の処理時間は，無視できるものとする。 2．移行計画移行計画に先立ち，主要な移行工程・所要時間を表10にまとめた。表10 主要な移行工程・所要時間（未完成） 3．データ移行処理方式設計表10の工程②データ移行について，データ量が最も多い“コンタクト履歴”テーブルについて，次のように検討した。（1）表8と表9から，全行のデータ移行時間は，E&Iでは【o】時間，DIでは【p】時間である。準備期間内のバッチ処理後のサービス停止時間を利用して移行処理を毎日4時間ずつ行うと，最短でも【q】日掛かる。（2）準備期間内のデータ移行処理では，エクスポートツールに指定するSQL文を処理日別に用意すると，抽出条件の誤り，実行順の誤りなどが懸念される。そこで，移行データ抽出に使用する“抽出範囲”テーブルを現行DBに追加し，抽出処理日ごとに，行数が均等になるように支店コードの範囲を決めて登録することにした。“抽出範囲”テーブルのテーブル構造を図3に，エクスポートツールに指定するSQL文を図4に示す。図3 “抽出範囲”テーブルのテーブル構造図4 エクスポートツールに指定するSQL文（未完成）（3）表10の工程③差分データ反映で行う二つの処理を表11にまとめた。なお，処理Aと処理Bは，相互に現行DB上の異なる行を対象とする。表11 差分データ反映の処理内容とデータ移行方法（未完成）〔データ移行後の検証〕 DBの相互接続機能を利用して，現行DBと新DBのテーブルを比較し，SQL文を実行して全行，全列の値一致を確認する。二つの集合が同一であることを確かめる論理式と，対応するSQL文を，表12にまとめた。さらに，表12のSQL①を基に，“取引”テーブルを検証するためのSQL文を，図5に示す。表12 論理式と対応するSQL文（未完成）図5 “取引”テーブルを検証するためのSQL文設問1　〔データベースの物理設計〕について，（1）～（3）に答えよ。（1）表4の枠内に適切な字句を記入して太枠内を完成させよ。ただし，索引の種類と構成列の欄は全て埋まるとは限らない。また，【a】～【c】に入れる適切な字句を答えよ。ただし，1～999のような，値の上限・下限に関する制約は，検査制約では規定しないものとする。（2）表5中の【d】～【g】に入れる適切な数値を答えよ。ここで，空き領域率は10％とする。（3）“顧客基本”テーブルの個人情報保護の強化策について，表6のセキュリティ事故A，Bへの対策に関する“顧客基本”テーブル実装案の有効性を評価し，【h】，【i】に“○”又は“×”を記入せよ。また，セキュリティ事故Cにおいて，“顧客基本”テーブル実装案が有効でないのは，どのような場合か。20字以内で具体的に述べよ。さらに，顧客名の暗号化によるセキュリティ強化のトレードオフとして，性能の悪化，業務機能の劣化が懸念される。その事象を一つ挙げ，次の例に倣って35字以内で具体的に述べよ。例：顧客情報の暗号化，復号のための処理時間が増加する。設問2　〔データ移行の前提・移行計画・データ移行処理方式設計〕について，（1）～（3）に答えよ。（1）表7中の【j】～【n】に入れるテーブル名を，記入済みの例に倣って，全て答えよ。（2）“3．データ移行処理方式設計”本文中の【o】～【q】に入れる適切な数値を答えよ。また，図4中の【r】，【s】に入れる適切な条件を，SQLの述語をそれぞれ一つだけ用いて答えよ。ここで，現在日付を表す予約語をCURRENT_DATEとする。（3）表11中の【t】，【u】に入れる適切な条件を，それぞれ20字以内で答えよ。また，【v】，【w】に入れる適切なデータ移行方法を答えよ。設問3　〔データ移行後の検証〕について，（1），（2）に答えよ。（1）表12中の【x】～【z】に入れる適切な字句を答えよ。（2）図5のSQL文では，データが正しく移行されていても，実行結果の列値がゼロにならない場合がある。その要因を二つ挙げ，それぞれ20字以内で述べよ。また，二つの要因に対するSQL文の修正内容を，それぞれ60字以内で述べよ。

図1 概念データモデル。支店，職業，顧客，案件，取引などのエンティティと，それらの参照関係を示す概念データモデル。

図2 テーブル構造（一部省略）。顧客情報管理システムの各テーブル名と主な列，キー列を一覧化したテーブル構造。

表1 主な列とその意味・制約。支店コード，顧客番号，顧客名，日付，時刻などの列の意味，値域，文字数，制約をまとめた表。

表2 使用可能なデータ型。文字列，整数，十進数，日付，時刻，時刻印のデータ型と格納長や値域を示す表。

表3 “顧客基本”テーブルのテーブル定義表。顧客基本テーブルの列，データ型，NOT NULL，格納長，索引，外部キーおよび検査制約を示す表。

表4 “スケジュール”テーブルのテーブル定義表（作成中）。スケジュールテーブルの列定義と未完成のデータ型，格納長，索引，参照制約，検査制約の記入欄。

表5 “スケジュール”テーブルのデータ所要量（未完成）。見積行数，ページサイズ，平均行長，ページ当たり平均行数，必要ページ数，データ所要量の表。

表6 セキュリティ事故と対策の有効性評価（未完成）。顧客情報の持出し，バックアップ媒体盗難，不正SELECTの三つの事故と暗号化案の有効性欄を示す表。

表7 テーブルへのデータ投入順序（未完成）。参照制約を考慮したテーブル投入順序を六つの枠番号に配置する表。

表8 現行DBの主なテーブルの統計情報。主なテーブルの平均行長，行数，ページサイズ，使用ページ数を示す統計表。

表9 主なテーブルのデータ移行処理速度。商品種別，顧客詳細，コンタクト履歴についてEXP，IMP，DIの行毎秒処理速度を比較した表。

表10 主要な移行工程・所要時間（未完成）。準備期間，移行期間，監視期間におけるDB準備，移行，検証，バックアップ，本番開始などの工程表。

図3 “抽出範囲”テーブルのテーブル構造。抽出処理日，開始支店コード，終了支店コードから成る抽出範囲テーブルの構造。

図4 エクスポートツールに指定するSQL文（未完成）。コンタクト履歴と抽出範囲を結合し，二つの条件をWHERE句に記入する未完成のSELECT文。

表11 差分データ反映の処理内容とデータ移行方法（未完成）。処理Aの追加処理と処理Bの更新処理について，条件と移行方法を記入する表。

表12 論理式と対応するSQL文（未完成）。集合差，和集合，積集合を用いて二つの集合の同一性を検証する論理式とSQL文の対応表。

図5 “取引”テーブルを検証するためのSQL文。現行DBと新DBの取引テーブルをEXCEPTで比較し，差集合の件数を数えるSQL文。

解き方のヒントを見る

作業の目的と段階の対応を確かめる

問われている事柄を、何を目的としてどの時点で行うものかと結び付けて捉えると整理しやすくなります。表面的な言葉だけで判断せず、なぜその時点で必要なのか、何を根拠に判断するのかを自分の言葉で説明できるかを確かめましょう。

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

確認すべき観点は一つ、「その作業の目的は何で、どの段階で行うのが筋か」です。用語の知識を思い出すより先に、作業の狙いと実施時点の関係を自分で説明し、その説明と問われている内容が整合するかを照らし合わせてください。

視点

更改の取組では、各段階の活動がそれぞれ異なる目的を持ちます。前の段階の成果が、次の段階の前提になります。ある作業が妥当かどうかは、その作業だけを見ても決まりません。何を達成するためか、どの情報が揃った時点で行えるかという文脈で決まります。目的と時点を切り離さずに考える習慣が、判断の軸になります。

確認ポイント

その作業が何を達成するために行われるのかを一文で言えるか
その作業に必要な情報や前提が、どの時点で揃うかを考えたか
目的と時点についての自分の説明が、問われている内容と矛盾していないかを照らし合わせたか

正解と解説を見る

解説

D銀行の顧客情報管理システムのDBサーバ更改を題材に、物理設計（スケジュールテーブルの定義、データ所要量の見積り、暗号化による個人情報保護）、データ移行（参照制約に沿った投入順序、E&IとDIの時間比較、抽出範囲テーブルを使った分割抽出、差分反映）、移行後の集合演算による検証までを一貫して問う問題です。RDBMSの仕様表や移行方針の細かい記述を、計算・SQL・条件に正確に落とし込めるかが得点の分かれ目です。

問題の全体像

D銀行は、顧客情報管理システムのDBサーバとストレージを更改するのに合わせて、次の作業を行います。RDBMSのバージョンアップ、スケジュール管理機能の追加、個人情報の漏えい対策の強化、商品コードの4桁から6桁への拡張です。移行の方針は二つあります。一つは、月末のバッチ処理で削除されると分かっているデータは移行しないこと。もう一つは、最終更新TSを自動設定せずそのまま移行することです。出題趣旨では、評価する能力として次の五つが挙げられています。(1)論理データモデルを物理データベースとして設計する力、(2)必要なストレージ容量を算出する力、(3)セキュリティ要件を実装する力、(4)データ移行方式を決めて移行計画を立てる力、(5)データ検証方式を設計する力です。採点講評では、全体の正答率は高かったとされています。一方で、NULLフラグの考慮漏れ、暗号化のトレードオフ、INTERSECT、検証SQLの具体的な修正内容では誤りが目立ったと指摘されています。

設問1(1)(表4)

公式解答例のデータ型は次のとおりです。開始時刻・終了時刻はTIME（3バイト）。行番号は1〜999の連番なので、最小の整数型であるSMALLINT（2バイト）。行動種別は1桁の半角コードなのでCHAR(1)（1バイト）。行動内容は全角1、000字以内の可変長なのでNCHAR VARYING(1000)。支店コードは4桁の半角英数字なのでCHAR(4)。案件番号は1〜99、999、999なのでINTEGERです。

NOT NULL欄の根拠は表1にあります。開始時刻・終了時刻は「スケジュールでは必須」、行番号は主キーの一部、行動種別は5種類の「いずれか」なので、いずれもYです。行動内容は「任意」、支店コードと案件番号は「案件に関連する場合にだけ設定」なので、Nになります。

格納長は、N列にNULLフラグの1バイトを加えるのがポイントです。行動内容は、平均58文字×2バイト＝116に、制御情報4バイトとNULLフラグ1バイトを足して121。支店コードは4＋1＝5、案件番号は4＋1＝5です。採点講評でも、このNULLフラグの考慮漏れが散見されたと指摘されています。

索引は次のとおりです。主キー（P）は、行員番号1・予定日2・開始時刻3・行番号4です。非ユニーク索引（NU）は、案件を参照する外部キーである支店コード1・案件番号2に作成します。公式解答例では、行員番号だけのNUは作成されていません。行員番号は主キー索引の先頭列なので、主キー索引で外部キーの検索を賄えるという扱いと考えられます。

設問1(1)(a)

スケジュールは、図1で案件とリレーションシップを持っています。図2では、支店コードと案件番号が外部キー（破線）です。案件の主キーはこの2列の複合キーなので、参照制約も2列の組で指定します。公式解答例は「(支店コード、案件番号) REFERENCES 案件 (支店コード、案件番号)」です。どちらか1列だけで参照しようとすると、案件を一意に特定できません。

設問1(1)(b)

表1に「終了時刻は開始時刻よりも後でなければならない」とあります。これは同一行の2列の関係なので、検査制約で表現できます。公式解答例は「開始時刻 < 終了時刻」です。なお、b と c は順不同とされています。

設問1(1)(c)

表1で、行動種別は '1'〜'5' のいずれかと定められています。表3の「CHECK (性別 IN ('0'、 '1'、 '2'))」と同じ書き方で、公式解答例は「行動種別 IN ('1'、 '2'、 '3'、 '4'、 '5')」です。行番号の1〜999のような上限・下限の制約は、問題文の指示により検査制約には書きません。

設問1(2)

表4の格納長を合計すると、平均行長 d は 4+4+3+2+3+1+121+5+5＝148 バイトです。

空き領域率が10%なので、1ページで使えるのは 4、000×0.9＝3、600 バイトです。3、600÷148≒24.3 を切り捨てて、e＝24行になります。

必要ページ数 f は 1、200、000÷24＝50、000 ページです。データ所要量 g は 50、000×4、000＝200、000、000 バイト、つまり200百万バイトです。

採点講評によると、(1)で求めた格納長を基に算出する必要があったため、正答率は低めでした。NULLフラグを落とすと d から連鎖的に誤ります。

設問1(3)(h)

事故Aは、営業員が正規の顧客情報照会APを使ってデータを検索し、持ち出したものです。このAPは、埋め込まれた鍵で復号した平文を利用者に表示します。そのため、DB上の値を暗号化していても流出は防げません。公式解答例は「×」です。

設問1(3)(i)

事故Bは、フルバックアップのメディアが盗まれたものです。バックアップ内の顧客名・住所・電話番号は暗号化された状態で格納されており、鍵はAP内にあってバックアップには含まれません。そのため、盗んだ側は平文を得られません。公式解答例は「○」です。

設問1(3)(C)

事故Cでは、DBのログインIDとパスワードが盗まれ、APを介さずにSELECT文が実行されます。暗号化データが読まれても、通常は鍵がないので復号できません。しかし、復号関数はRDBMS側に用意されています。そのため、どの復号関数を使うかと鍵が知られれば、SQLだけで平文を取り出せます。公式解答例は「復号関数名と鍵を盗まれた場合」です。20字以内なので、「何が漏れたら破られるか」を具体的に書きます。

設問1(3)(トレードオフ

表1にあるとおり、顧客名は完全一致や部分一致（あいまい検索）の条件検索に使われます。暗号化すると、列には暗号文が入ります。その結果、索引による高速検索や、SQLでの部分一致検索ができなくなります。

公式解答例では、次の三つが挙げられています。「顧客名による検索に索引を使用できないので性能が劣化する」「顧客名によるあいまい検索をAPで処理するよう変更する必要がある」「APを変更しないと顧客名による検索が機能しなくなる」。

採点講評では、問題文の例（暗号化・復号の処理時間が増加する）をそのまま述べただけの誤答が散見されたと指摘されています。35字以内で、顧客名の検索に固有の影響を書く必要があります。

設問2(1)(j)

枠1には、参照先を持たないテーブルを置きます。図1・図2で商品種別は、他のテーブルを参照していません。そのため、支店・職業・科目・取引種別と並んで最初に投入できます。公式解答例は「商品種別」です。

設問2(1)(k)

運用商品は、商品種別だけを参照します。そのため、枠1の直後の枠2に置けます。部課（支店を参照）と同じ枠です。公式解答例は「運用商品」です。

設問2(1)(l)

顧客基本は、職業（枠1）と営業員（担当行員番号。枠3）を参照します。営業員の投入が終わった後の枠4になります。公式解答例は「顧客基本」です。

設問2(1)(m)

顧客詳細は、顧客基本を参照します。顧客口座は、支店・科目・顧客基本を参照します。どちらも顧客基本（枠4）の後なので、案件と同じ枠5に入ります。公式解答例は「顧客詳細，顧客口座」です。

設問2(1)(n)

取引は、案件・顧客口座（枠5）と運用商品・取引種別を参照します。コンタクト履歴は、案件を参照します。したがって、どちらも枠6です。公式解答例は「取引，コンタクト履歴」です。採点講評では、この設問はよく理解され、正答率が高かったとされています。

設問2(2)(o)

コンタクト履歴は576、000、000行です（表8）。E&Iでは、EXPとIMPを順に行います（表9）。EXPは 576、000、000÷10、000＝57、600秒＝16時間、IMPは 576、000、000÷5、000＝115、200秒＝32時間です。合計すると、公式解答例のとおり48時間になります。

設問2(2)(p)

DIの処理速度は2、000行/秒です。576、000、000÷2、000＝288、000秒＝80時間になり、公式解答例は80です。E&Iの方が速いことが分かります。

設問2(2)(q)

最短になるのは、速いE&Iの48時間を使う場合です。1日4時間ずつ処理すると 48÷4＝12日で、公式解答例は12です。

設問2(2)(r)

抽出範囲テーブルには、抽出処理日ごとに支店コードの範囲が登録されています。当日分の行を選ぶには、抽出処理日を現在日付と比べます。公式解答例は「B.抽出処理日 = CURRENT_DATE」です（r と s は順不同）。

設問2(2)(s)

当日の範囲に含まれる支店のコンタクト履歴を選ぶ条件を、述語一つで書きます。範囲指定なので BETWEEN を使い、公式解答例は「A.支店コード BETWEEN B.開始支店コード AND B.終了支店コード」です。採点講評では、WHERE句中の述語の文法を誤った解答が散見されたと指摘されています。

設問2(3)(t)

処理Bは、新DBに既にある行を、抽出後に更新された値で上書きする処理です。抽出時点で新DBに移行済みの行は、案件の登録日が抽出処理日以前のものです。これは、処理Aの条件「登録日が抽出処理日の後」と対になっています。公式解答例は「登録日が抽出処理日以前である」です（t と u は順不同）。採点講評では、この登録日の条件をコンタクト日時の条件にした誤りが散見されたとされています。

設問2(3)(u)

コンタクト履歴は、過去のデータが更新されることがあります。移行方針では、最終更新TSはそのまま移行されます。そのため、最終更新TSを見れば、抽出後に変更された行を判別できます。公式解答例は「最終更新TSが抽出処理日よりも後である」です。

設問2(3)(v)

処理Aは、新DBにまだない行を追加するだけの処理です。インポートツールはINSERTと同じ処理をするので、E&Iで実行できます。また、表9からE&Iの方が処理が速いことも分かります。公式解答例は「E&I」です。

設問2(3)(w)

処理Bは、新DBの既存の行を更新する処理です。インポートツールはINSERTしかできないので、E&Iでは対応できません。DIは、相互接続機能を使ってSQLで追加・更新・削除ができるので、こちらを選びます。公式解答例は「DI」です。

設問3(1)(x)

SQL③は、論理式 (A∪B)−(A∩B)＝φ をSQLで表したものです。前半の括弧は和集合 A∪B なので、x は公式解答例のとおり「UNION」です（x と z は順不同）。

設問3(1)(y)

二つの括弧の結果の差集合をとるので、y は「EXCEPT」です。結果の件数が0なら、AとBは同一です。

設問3(1)(z)

後半の括弧は積集合 A∩B なので、z は「INTERSECT」です。採点講評では、UNIONとEXCEPTの正答率は高かったものの、INTERSECTは低かったとされています。

設問3(2)(要因1)

取引テーブルには商品コードがあります。移行時に、商品コードの前に'00'を付けて6桁にしています。そのため、正しく移行できていても、新旧で値が一致しない行が必ず出ます。公式解答例は「商品コードの桁数の不一致」です。採点講評では、データがソートされていないことを要因に挙げた誤答が散見されたとされています。集合演算の結果は、行の順序に左右されません。

設問3(2)(修正1)

SELECT * のままでは列の値を加工できません。そこで、選択リストを列名のコンマ区切りに書き換えます。そのうえで、現行DB側の商品コードに'00'を前に付ける演算を加え、新DBと同じ形式にそろえてから比較します。これが公式解答例の趣旨です。採点講評では、SQL文の具体的な修正内容が求められていたとされています。60字以内で、どの句をどう変えるかを明記します。

設問3(2)(要因2)

移行方針(4)により、その月末のバッチ処理で削除される予定のデータは移行されていません。一方、現行DBにはまだ残っているため、その分が差分として出ます。公式解答例は「毎月末日の削除対象データの不一致」です。

設問3(2)(修正2)

現行DB側のSELECT文にWHERE句を追加し、案件の登録日が10年前の月初めより前のデータ、つまり削除対象を抽出しないようにします。これが公式解答例の内容です。取引テーブル自体には登録日がないので、案件の登録日を条件に使う書き方になります。採点講評では、要因となるデータを除外するとだけ書いた解答が散見されたとされています。「現行DB側のSELECT文にWHERE句で条件を加える」という具体的な修正内容を書くことが大切です。

学習のポイント

NULLフラグを含めた格納長の計算と、そこからデータ所要量を導く手順を身に付けましょう。あわせて、暗号化の効果とトレードオフ、差分反映の抽出条件、EXCEPT・INTERSECTを使った同一性検証、移行時の値変換や除外データを検証SQLに反映する方法を押さえておくことが重要です。

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

平成28年度春期 データベーススペシャリスト 午後Ⅱ 問1

問題