データベーススペシャリスト 2016年 春期 午後II 問題

共通の文章

データベースの物理設計とデータ移行に関する次の記述を読んで,設問1こ3(CF KE. D銀行は,首都圏の100支店に,投資信託などの運用商品を扱う部課があり,専 任の営業員が個人顧客(以下,顧客という)への販売活動を行っている。営業員の 販売活動には,顧客の個人情報,資産情報,投資経験,取引などを格納した顧客情 報管理システムが利用されている。D銀行では,顧客情報管理システムのDBサーバ 及びストレージの老朽化に伴うリプレース(以下,DBサーバ更改という)に当たっ て,RDBMSのバージョンアップ,アプリケーション(以下,APという)の機能追 加などを行うことにした。 [データベースの論理設計〕 顧客情報管理システムの概念データモデルを図1に,テーブル構造を図2に,主 な列とその意味・制約を表1に示す。 《b 1 《D,C)@(い ーーーィイーディ OO@® M‘a 4)OO QO QO tT スケジコンタク 図1概念データモデル ーー6ーー 支店(支店コード,支店名,最終更新TS) 部課(部課コード,支店コード,部課名,最終更新TS) 営業員(行員番号,行員名,部課コード,最終更新TS) 科目(科目コード,科目名,最終更新TS) 職業(職業コード,職業名,最終更新TS) 取引種別(取引種別コード,取引種別名,最終更新TS) 商品種別(商品種別コード,商品種別名,最終更新TS) 運用商品(商品コード,商品種別コード,商品名,商品説明,最終更新TS) BSA(顧客番号,MEL,住所,電話番号,生年月日,性別,職業コード,勤務先, 担当行員番号,最終更新TS) |MSRM(顧客番号,更新年月日,家族構成,資産総額,投資可能額,投資信託経験年数 外貨預金経験年数。…) 顧客口座(支店コード,科目コード,OBES,顧客番号,開設日,期首残高,現在残高 最終更新TS,…) 案件(支店コード,案件番号,行員番号,WEES,登録日,状態,最終更新TS,…) 取引(支店コード,案件番号,取引番号,最終更新TS,取引種別コード,商品コード, 取引日,科目コード,口座番号,取引品数取引金額,…) コンタクト履歴(支店コード,案件番号,コンタクト日時,最終更新TS,会話1, 会話2,…) スケジュール(行員番号,FEA,開始時刻,GES,終了時刻,行動種別行動内容, 支店コード,案件番号) 図2テーブル構造(一部省略) —_aーー Rl主な列とその意味・制約 意味制約 支店を一意に識別するコード(4桁の半角英数字) BEES銀行内で顧客を一意に識別する番号(1999,999,999) 顧客の氏名(全角文字30字以内。平均文字数は5 KE),“MMA”テーブルの顧客 願客名名は,指定した文字列との完全一致。部分一致(あいまい検索)などの条件検索に使用 される。 顧客の住所(全角文字30字以内。平均文字数は19文字) 0'′(未記入),"1'(男性),"2'(女性)のいずれか 銀行内で役員・行員を一意に識別する番号(1.000.001~9,999,999) 科目コード普通預金,定期預金,投資信託などの種別を識別するコード(3桁の半角英数字) 顧客の口座を支店コードごと,科目コードごとに一意に識別する番号(7桁の半角英数 字) 期首残高現在|残高は,顧客の各口座における,ある時点の保有金額である。期首残高は期首日(4月 残高1日)時点の残高,現在残高は最新の残高を表す。 支店ごとに案件を一意に識別する番号(1ご99.999,999)。“"スケジュール"テーブルの支 案件番号店コードと案件番号は,案件に関連する顧客との面談予定などを登録する場合にだけ設 定する。 案件を最初に起案した年月日。“案件"テーブルの場合は必須で,変更することはな い。 会議,顧客往訪などの行動が予定されている年月日 mines,a7|ARM,終了時刻は,スケジュールの開始時分と終了時分。"スケジュール"テープ Hi,GER|ルの場合は必須で,終了時刻は開始時刻よりも後でなければならない。行番号は,同じ A TES|開始時刻で複数の予定を登録できるように,1ご999の連番で区別する。 1'(会議),"2'(打合せ),"3'(EER),"4(願客来訪),"5'(作業)のいずれ 行動種別on 本動内具体的な行動内容(全角文字1,000字以内。平均文字数は58文字)。“"スケジュール" nealテーブルの場合は任意である。 meatsテーブルの行が,追加又は最後に更新された時刻印(年月日時分秒)。システムで自動 デー行さ+4 ee ra re¥)4==;> 設定する。 顧客情報管理システムの現状とDBサーバ更改] 1.現状 (1)顧客情報管理システムは,Webサーバ,APサーバ,DBサーバから成るシス テムで,内部ネットワーク上に設置されている。内部ネットワークは,外部ネ ットワークから隔離されているので,外部から侵入されることはない。 (2)案件,取引,コンタクト履歴には,案件の登録日が10年前の月初めから現在 までの全てのデータが保存されている。コンタクト履歴は,過去のデータが更 新されることもある。保存期間が過ぎた案件及び案件に関連するデータは,毎 月末日のバッチ処理で削除される。 2.DBサーバ更改の方針 1 8<= (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.デーツル ー9一 (1)テーブルの列には,NOT NULL制約を指定することができる。NOT NULL制 約を指定しない列には,NULLかどうかを表す1バイトのフラグが付加される。 (2)主キー制約には,主キーを構成する列名を指定する。 (3)参照制約には,列名,参照先テーブル名,参照先列名を指定する。 (4)検査制約には,同一行の列に対する制約を指定する。 (5)使用可能なデータ型は,表2のとおりである。 表2使用可能なデータ型 CHAR(n)n文字の半角固定長文字列(1ミSnミ255)。文字列がn字未満の場合は,文字 列の後に半角の空白を挿入し,nバイトの領域に格納される。 NCHAR)n文字の全角固定長文字列(1ミnミ127)。文字列がn字未満の場合は,文字 列の後に全角の空白を挿入し,“nX2"バイトの領域に格納される。 VARCHAR(n)最大n文字の半角可変長文字列(1SnS8,000),“文字列の文字数”バイトの |領域に格納され,4バイトの制御情報が付加される。 NCHAR最大n文字の全角可変長文字列(1ミnミ4.000)。“文字列の文字数X2”"バイ VARYING(n)トの領域に格納され,4バイトの制御情報が付加される。 ー32,768~32,767の範囲内の整数。2バイトの領域に格納される。 nape ae 2,147,483,647の範囲内の整数。4バイトの領域に格納され ー~範領域に格納さ 精度m(1ミmミ31),位取りn(OSnSm)の10進数。“m=2二1”の小数部 0001-01-01~9999-12-31の範囲内の日付。4バイトの領域に格納される。 00:00:00~23:59:59の範囲内の時刻。3バイトの領域に格納される。 0001-01-01 00:00:00.000000~9999-12-31 23:59:59.999999の範囲内の時刻 3.索引 索引には,ユニーク索引と非ユニーク索引がある。 4.DBの相互接続機能 同じネットワーク上にある異なるDBを,相互接続する機能である。相互接続し たDB上のテーブルは,あたかも同じDB上にあるかのように,一つのSQL文で 操作することができる。SQL MCL,テーブル名をDBの識別子とスキーマ名の 両方で修飾することができる。 5.エクスポートツール,インポートツール テーブルのデータをテキストファイルに出力するエクスポートツールと,テキ ストファイルのデータをテーブルに入力するインポートツールがある。エクスポ ートツールでは,SQLのSELECT文を指定することができる。一方,インポート ツールでは,SQLのINSERT文と同じ処理が行われる。 [データベースの物理設計] 1.テーブル定義 次の方針に基づいて,テーブル定義表を作成し,テーブル定義を行う。 (1)データ型欄には,データ型,データ型の適切な長さ,ME,位取りを記入す る。データ型の選択は,次の規則に従う。 ①文字列型の列が全角文字の場合は,NCHAR又はNCHAR VARYINGを選択 し,それ以外の場合はCHAR又はVARCHARを選択する。 ②数値の列が整数である場合は,取り得る値の範囲に応じて,SMALLINT又 はINTEGERを選択する。それ以外の場合はDECIMALを選択する。 ③及び②どちらの場合も,列の取り得る値の範囲に従って,格納領域の長 さが最小になるようにデータ型を選択する。 ④日付,時刻,時刻印の列は,専用のデータ型を選択する。 (2)NOT NULL欄には,NOT NULL制約がある場合はYを,ない場合はNを記入 する。 (3)格納長欄には,RDBMSの仕様に従って,格納長を記入する。可変長文字列の 格納長は,表1から平均文字数が分かる場合はそれを基準に算出し,それ以外 の場合は最大文字数の半分を基準に算出する。 (4)索引の種類と構成列欄には,作成する索引を記入する。 ①索引の種類には,P(主キーの索引),U(ユニーク索引),NU(非ユニーク 索引)がある。 ②主キーの索引は,必ず作成する。 ③主キー以外で値が一意となる列又は列の組合せには,必ずユニーク索引を 作成する。それ以外の列又は列の組合せが,外部キーを構成する場合は,必 ず非ユニーク索引を作成する。 ④各索引の構成列には,構成列の定義順に1からの連番を記入する。 «fi= (5)制約欄には,参照制約,検査制約をSQLの構文で記入する。 “顧客基本",“スケジュール”テーブルのテーブル定義表を表3,表4に示す。 表3“顧客基本'"テーブルのテーブル定義表 |顧客番号。|mTEgeER_|Y|4[i]||| |ee||ot)eps pe NOLL|Os7 by[PT NU[NU|NO mee|NCHARVARYINGGO)|Y|14|[1{|| |住所|NCHARVARYING@00)|Y|4||||| |電話番号。|VARCHARG2O|Y|14|||| |生年朋日[pare|Y|4||||| |尾別。|cHARO TY|1|||| |職業コード|CHARの9|Y|4|||il |勤務先|NcHARVARYNedoO|N|io5||||| |担当行員番号|INTEGER|N|5|||iu |最終更新TS[Timestamp|Y|10||||| 制約FOREIGN KEY(担当行員番号)REFERENCES営業員(行員番号) CHECK(性別IN(0,'1',2’) 表4“スケジュール"”テーブルのテーブル定義表(作成中) 人生|mrgegR lrl 4 1|| pee|sa oe) 8[suelo F TT |||| a間間 es間昌還較間昌四半是間間間較 ee還間還間間還四国是間間間 li a es男還四半間還間是間 es| FOREIGN KEY(行員番号)REFERENCES営業員(行員番号) FOREIGNKEY|a| ae cHECK((__b__]) cHECK([c|) 2.データ所要量の見積り “スケジュール"”テーブルのデータ所要量を見積もるために,表5を作成した。 なお,データ所要量は,ABI~Sの値を用いて算出するものとする。 表5“スケジュール"”テーブルのデータ所要量(ATM) 1データベページ当たりの平均行数 |6[remem|Ee at| 3.個人情報保護の強化 外部ネットワークから隔離されていても,内部の人間の犯行などによる個人情 報の漏えいが懸念される。強化策として,次に示す“顧客基本"テーブル実装案 を検討し,想定されるセキュリティ事故ACへの対策の有効性を評価して,KE だまとめた。 案:顧客名,住所,電話番号の列値を,次のように格納,検索する。 ・APは,RDBMSの暗号化関数によってデータを暗号化し,SQL文によって 列に格納する。このとき,各列のデータ型,データ長の定義を変更する。 ・APは,SQL文によって暗号化したデータを検索し,RDBMSの復号関数に よって復号する。 ・暗号化と復号の鍵は,AP内に埋め込み,外部から見えないようにする。 表6セキュリティ事故と対策の有効性評価(未完成) A営業員が顧客情報照会のAPを利用して,複数顧客の顧客名,住所,電話Cir] 番号を検索して得られたデータを不正に持ち出した。 顧客情報管理システムのDBのフルバックアップを格納したメディアがにュ iS Ino,: 顧客情報管理システムのDBへのログインIDとパスワードが盗まれ,AP C|を利用せずに,"顧客基本"テーブルに対するSELECT文が不正に実行 された。 注記〇:有効であるへ:有効でない場合があるX:有効でない [データ移行の前提・移行計画・データ移行処理方式設計] 1.データ移行の前提 (1)テーブルへのデータ投入順序 テーブルへのデータ投入順序を,表7に示す。表7では,図2の“スケジュ ール”以外のテーブルを,参照制約看反が発生しない限り,できるだけ小さい 枠番号のいずれかに配置し,枠番号順にデータ投入を行う。同じ枠番号内のテ ーブルへのデータ投入は,並行して行われる。 表7テーブルへのデータ投入順序(未完成) 支店,職業,科目,取引種別,[| 」 ] Ba, 案人|。nm|] ーー|ee ee (2)データ量の調査 現行DBの主なテーブルの統計情報を,表8に示す。 表8現行DBの主なテーブルの統計情報 _平均行長レーページサイズ|使用ページ数 商品種別|1i0o|eo|2000|4 |運用商品”。|oo]1300|4000|217| 顧客基本 顧客詳細 (3)データ移行方法 現行DBから新DBにデータを移行するには,次に示す二つの方法がある。二 つの方法について,それぞれ主なテーブルのデータ移行処理速度を実測し,表9 を作成した。 ①エクスポート・インポート(ERD 現行DBのテーブルからデータをエクスポート(EXP)したファイルを新 DBサーバに転送し,新DBのテーブルにインポート(IMP)する。EXPと IMPには,それぞれエクスポートツールとインポートツールを用いる。 ②ダイレクトインサート(CD DBの相互接続機能を利用して,SQL文の実行によって,現行DBのテーブ ルのデータを読み込み,新DBのテーブルのデータを直接,追加,更新,削除 する。 表9主なテーブルのデータ移行処理速度 EXP(行/秒)|IMP(行/秒)|DI(行/秒) 商品種別 Ee 注記ファイル転送の処理時間は,無視できるものとする。 2.移行計画: 移行計画に先立ち,主要な移行工程・所要時間を表10にまとめた。 R10主要な移行工程・所要時間(未完成) 所要時間 準備期間ODE Hetil新DBにDBオプジェクトを作成する。 _現行DBのデータを新DBに移行する。(検討中) ⑧差分データ反映新DBに反映する。(検討中) ーー②の後で追加又は更新されたデータを,っ ae現行DBと新DBのデータの整合性を検証 移行する。“ LORYZT YS|新DBのフルバックアップを取得する。 ii可否を判定する。 6先行工程の結果を評価して,本番開始の の本番開始iyサーバからの接続先を新DBサーバに 監視期間ae 本障害,性能。業務データの整合性を監相| し,問題があれば対処する。 3.データ移行処理方式設計 表10の工程②データ移行について,データ量が最も多い“コンタクト履歴"テ ーブルについて,次のように検討した。 (①)表8と表9から,全行のデータ移行時間は,Eaでは| o時間,D では,|p|時間である。準備期間内のバッチ処理後のサービス停止時間 を利用して移行処理を毎日4時間ずつ行うと,最短でも| q|日掛かる。 (2)準備期間内のデータ移行処理では,エクスポートツールに指定するSQL文を 処理日別に用意すると,抽出条件の誤り,実行順の誤りなどが懸念される。そ こで,移行データ抽出に使用する“抽出範囲"テーブルを現行DBに追加し,Hh 出処理日ごとに,行数が均等になるように支店コードの範囲を決めて登録する ことにした。“抽出範囲'テーブルのテーブル構造を図3に,エクスポートツー ルに指定するSQL文を図4に示す。 抽出範囲(抽出処理日,開始支店コード,終了支店コード) 図3“抽出範囲"テーブルのテーブル構造 SELECT A.*FROMコンタクト履歴A抽出範囲B WHERE AN[s| 図4エクスポートツールに指定するSQL文(未完成) (3)R10の工程③差分データ反映で行う二つの処理を表11にまとめた。 なお,処理Aと処理Bは,相互に現行DB上の異なる行を対象とする。 表11差分データ反映の処理内容とデータ移行方法(未完成) “抽出範囲「テーブルの行の開始支店コードと終了支店コード の範囲内で,支店コードが一致する“案件”テーブルの行に対 処理A|応し,PO,次の条件に一致する“コンタクト履歴"テーブル の行を,新DBに追加する。 条件:登録日が抽出処理日の後である "抽出範囲′テーブルの行の開始支店コードと終了支店コード の範囲内で,支店コードが一致する“案件”テーブルの行に対 処理B|応し,かつ,次の条件に一致する“コンタクト履歴"テーブル の行の列値で新DBを更新する。 条件:[t]ヵかっ[・] [データ移行後の検証〕 DBの相互接続機能を利用して,現行DBと新DBのテーブルを比較し,SQL文を 実行して全行,全列の値一致を確認する。二つの集合が同一であることを確かめる 論理式と,対応するSQL文を,表12にまとめた。さらに,表12のSQL①を基に, "取引”テーブルを検証するためのSQL文を,図5に示す。 表12論理式と対応するSQL文(未完成) 対応するSQL X a 6 FROM(SELECT*FROM A EXCEPT SELECT*FROM B)R1 aL ーーSELECT COUNT(*)— ーーSELECT COUNT(*) SELECT COUNT(*) (AUB)—(ANB)=|PROM(GELECT+FROM A SELECT*FROM B)—_— (SELECT*FROM A[__z_]SELECT*FROM B))R3 凡例AUB:AとBの和集合A-B:AとBの差集合AB:AとBの積集合g:BRS SELECT COUNT(*) FROM((SELECT*FROM OLD DB.C61.取引) EXCEPT (SELECT*FROM NEW DB.C61.取引))R1 注記OLD DB:現行DBの識別子,NEW DB:新DBの識別子,CO1:スキーマ名 図5“取引"テーブルを検証するためのSOL文