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

問1 データベースの設計，実装に関する次の記述を読んで，設問1～3に答えよ。 精密電子機器メーカのA社では，新たに経費精算システムを稼働させるに当たって，クラウドサービスの利用を検討中である。経費精算システムは，現行の人事管理システム（以下，現行システムという）から分離する旅費交通費精算機能，新たに開発する一般経費精算機能及び仮払金精算機能から成る。 〔業務の概要〕 1．組織，科目，経費予算，締日 (1) 組織 ① A社には，10の事業部があり，事業部内に複数の部がある。 ② 全社の経費精算事務を行う事務センタがある。 (2) 科目 ① 費用を用途によって区分したものを科目という。例えば，旅費交通費，通信費，接待交際費，消耗品費など，20科目がある。 ② 科目を細分化した内訳を，内訳科目という。例えば，旅費交通費には，宿泊費，日当などの内訳科目がある。 (3) 経費予算 事業部ごとに，経費の予算・実績の管理を行う。事業部ごとに任命された経費管理責任者が，年度初め（4月）に科目別月別の経費予算を作成する。 (4) 締日 経費申請の締日は，毎月20日である。 2．手続の種類 (1) 旅費交通費精算 ① 旅費 ・社員が，出張の費用を精算する手続である。 ・社員は，出張に先立って支出予定の旅費申請を行い，帰社後に実費を精算する。 ② 交通費 ・社員が，業務で移動に利用した電車，バス，タクシーなどの料金を精算する手続である。 ・社員は，前月の締日の翌日から当月の締日までの交通機関の利用を記録し，1か月分の交通費をまとめて申請する。 (2) 一般経費精算 ① 接待交際費，通信費，消耗品費などを精算する手続である。 ② 精算手続には，社員が立て替えた代金を社員に支払う“立替経費精算”，支払先への代金支払を事務センタに依頼する“経費支払依頼”の2種類があり，申請種別で区分される。 ③ 締日までに受け付けた申請については，月末までに支払を行う。 (3) 仮払金精算 ① 社員が，宿泊費，接待交際費などに使用する目的で一定額の仮払金の支払を事前に受け，事後に精算する手続である。仮払金を使用して旅費，一般経費の支払を行った場合，旅費交通費精算又は一般経費精算を行う必要がある。 ② 申請には，社員番号，申請理由，仮払金額，精算期限を記載する。 ③ 事務センタは，未精算の仮払金を仮払金残高として記録し，社員が精算期限までに全額を精算するように促している。 ④ 一つの仮払いに対する精算が複数の日に分かれることもある。精算時には，精算日ごとに精算金額を記録し，仮払金残高から精算金額を減額する。 3．申請・精算の流れ (1) 申請 ① 申請者は，旅費交通費，一般経費，仮払金の申請を登録する。申請には一意な申請番号が付与される。 ② 領収書などの証憑（ひょう）の提出が必要な場合には，証憑をスキャナで取り込んだ画像を添付する。証憑には，申請番号ごとに一意な証憑番号が付与される。 (2) 上司承認，庶務精査，経費管理責任者確認 申請の承認，精査及び確認は，申請ごとに次のように行われる。 ① 申請者の上司は，申請内容を確認して承認，否認，差戻しのいずれかを判定する。承認の場合は，申請に上司承認日を記録して庶務担当者に回付する。 ② 申請者の所属事業部の庶務担当者は，申請書を精査する。問題がなければ，申請に精査日を記録する。旅費交通費，一般経費の申請は経費管理責任者に回付し，仮払金の申請は事務センタに回付する。 ③ 経費管理責任者は，申請内容及び経費予算を確認して承認，否認，差戻しのいずれかを判定する。承認の場合は，申請に責任者承認日を記録して，事務センタに回付する。 (3) 支払 事務センタは，申請を受けて社員又は支払先への支払を行う。 ① 旅費交通費は，申請ごと支払予定日ごとに作成した支払伝票を基に支払を行い，支払伝票に支払実行日を記録する。 ② 一般経費は，申請ごとに支払伝票を作成し，申請に支払伝票の支払予定日及び支払番号を記録する。支払伝票を基に支払を行い，支払伝票に支払実行日を記録する。 ③ 仮払金は，申請を基に一定額の支払を行い，申請に支払実行日を記録する。 〔システムの概要〕 1．現行システム (1) 現行システムの概要 ① 現行システムは，旅費交通費精算，勤怠管理，労務管理など複数の機能から成る。 ② 事業部，部，職位，社員，通貨など複数の機能に共通するマスタ（以下，共通マスタという）は，一元的に更新される。 ③ 経費予算，申請に関する情報，及び申請から派生する伝票などの情報は，経費予算の年月又は申請の処理年月が60か月前から現在までのデータが全て保存されている。 ④ 旅費交通費精算機能の運用時間帯は，平日の8時～23時であり，8時～20時にオンライン処理，20時～23時にバックアップ及びバッチ処理が行われる。 (2) 現行システムの構成 ① 旅費交通費精算機能には，APサーバ2台，DBサーバ1台が割り当てられている。DBサーバには，コールドスタンバイの待機サーバを置いている。 ② バッチ処理では，DBサーバ上に別途構築されたバッチアプリケーションの実行環境上でプログラム（以下，バッチPGMという）を実行している。 2．経費精算システム (1) 経費精算システムの概要 ① 旅費交通費精算機能，一般経費精算機能及び仮払金精算機能から成る経費精算システムを稼働させる。 ② 経費精算システムの運用時間帯は，現行システムの旅費交通費精算機能と同じである。 (2) 経費精算システムのテーブル構造，見積行数・データ所要量 経費精算システムのテーブル構造を図1に，主な列とその意味・制約を表1に，主なテーブルの見積行数・データ所要量を表2に示す。 図1 経費精算システムのテーブル構造（未完成・一部省略） 表1 主な列とその意味・制約 表2 主なテーブルの見積行数・データ所要量 (3) 経費精算システムの処理 主な処理のCRUDを表3に，バッチPGMの処理内容を表4に示す。 表3 主な処理のCRUD 表4 バッチPGMの処理内容 〔RDBMSの仕様〕 1．データページ (1) RDBMSがストレージとデータの入出力を行う単位を，データページという。データページには，テーブル，索引のデータが格納される。表領域ごとに，ページサイズ（1データページの長さで，2、000，4、000，8、000バイトのいずれか）と，空き領域率（将来の更新に備えて，データページ内に確保しておく空き領域の割合）を指定する。 (2) 同じデータページに，異なるテーブルの行が格納されることはない。 2．テーブル (1) テーブルの列には，NOT NULL制約を指定することができる。NOT NULL制約を指定しない列には，NULLか否かを表す1バイトのフラグが付加される。 (2) 主キー制約には，主キーを構成する列名を指定する。 (3) 参照制約には，列名，参照先テーブル名，参照先列名を指定する。 (4) 検査制約には，同一行の列に対する制約を指定する。 (5) 使用可能なデータ型は，表5のとおりである。 3．索引 索引には，ユニーク索引と非ユニーク索引がある。 表5 使用可能なデータ型 〔テーブルの物理設計〕 1．テーブル定義 次の方針に基づいてテーブル定義表を作成し，テーブル定義を行う。作成中の“一般経費申請”テーブルのテーブル定義表を表6に示す。 (1) データ型欄には，データ型，データ型の適切な長さ，精度，位取りを記入する。データ型の選択は，次の規則に従う。 ① 文字列型の列が全角文字の場合は，NCHAR又はNCHAR VARYINGを選択し，それ以外の場合はCHAR又はVARCHARを選択する。 ② 数値の列が整数である場合は，取り得る値の範囲に応じて，SMALLINT又はINTEGERを選択する。それ以外の場合はDECIMALを選択する。 ③ ①及び②どちらの場合も，列の値の取り得る範囲に従って，格納領域の長さが最小になるデータ型を選択する。 ④ 日付の列は，DATEを選択する。 (2) NOT NULL欄には，NOT NULL制約がある場合はYを，ない場合はNを記入する。 (3) 格納長欄には，RDBMSの仕様に従って，格納長を記入する。可変長文字列の格納長は，表1から平均文字数が分かる場合はそれを基準に算出し，それ以外の場合は，最大文字数の半分を基準に算出する。 (4) 索引の種類と構成列欄には，作成する索引を記入する。 ① 索引の種類には，P（主キーの索引），U（ユニーク索引），NU（非ユニーク索引）がある。 ② 主キーの索引は必ず作成する。 ③ 主キー以外で値が一意となる列又は列の組合せには，必ずユニーク索引を作成する。それ以外の列又は列の組合せが，外部キーを構成する場合は，必ず非ユニーク索引を作成する。 ④ 各索引の構成列には，構成列の定義順に1からの連番を記入する。 (5) 制約欄には，参照制約，検査制約をSQLの構文で記入する。 表6 “一般経費申請”テーブルのテーブル定義表（未完成） 2．データ所要量の見積り “一般経費申請”テーブルのデータ所要量を見積もるために，表7を作成した。ここで，索引のデータ所要量は考慮しないものとする。 表7 “一般経費申請”テーブルのデータ所要量（未完成） 〔性能見積り・評価〕 1．性能見積り 現行システムの旅費交通費精算機能のDBサーバは，処理がピークとなる締日前にCPU使用率がしきい値に達しつつある。一般経費申請も，同様に締日前にピークが見込まれるので，一般経費申請で必要となるCPUの性能見積りを行った。 (1) 性能要件 ① 1トランザクション当たりのターンアラウンドタイムは，1秒以下とする。内訳は，APサーバ0.4秒以下，DBサーバ0.6秒以下である。 ② ピーク時に，旅費申請及び交通費申請では，両者を合わせて1秒間に400トランザクションを，一般経費申請では1秒間に200トランザクションを処理することができる。 (2) DBサーバの性能見積りの前提 ① CPU使用率は80%以下とする。 ② CPUの1データページ当たりの処理時間は，0.2ミリ秒である。 ③ 1データページ当たりのストレージへのI/O時間は，20ミリ秒である。 ④ トランザクションごとに，平均50データページにアクセスする。 ⑤ CPU待ち時間，索引の探索時間，ネットワークの通信時間は考慮しない。 (3) DBサーバの性能見積り ① CPUの1トランザクション当たりの処理時間は【e】ミリ秒であり，200トランザクションの処理時間は【f】秒となる。CPU使用率を80%とするためには，同じ性能のCPUが少なくとも【g】個必要である。 ② 仮に，データベースのバッファヒット率が0%とすると，1トランザクション当たりのI/O時間は，【h】秒で要件を満たさない。しかし，バッファヒット率が60%のとき，CPU処理時間とI/O時間の合計は，【i】秒となり，要件の範囲内になる。 2．評価 現行システムの旅費交通費精算機能のDBサーバには，CPU及び周辺機器の増設が必要となるが，現行システムの機器が2年後に更新時期を迎えることから，クラウドサービスの利用を検討する。 〔クラウドサービスの概要〕 検討対象のクラウドサービスの概要は，次のとおりである。 1．サービス概要・利用料金 (1) サービスとして提供されるサーバには，次の2種類がある。 ・アプリケーションソフトを稼働させるためのハードウェア，OSなどを備えたIaaS型の汎用サーバ ・ハードウェア，OS，データベースのプラットフォームを備えたPaaS型の専用サーバ (2) 汎用サーバのサービス利用料金は，サーバの稼働時間に対して課金される。 (3) 専用サーバのサービス利用料金は，“データベース利用時間 × 基本サービス利用料金”で課金される。オプションを利用すると，基本サービス利用料金にオプション利用料金が追加される。基本サービス利用料金は，表8に示すようにサーバのクラスごとに定められている。 (4) 専用サーバでは，現行システムと同じRDBMSを使用できる。 表8 専用サーバのクラス 2．制限事項・管理機能 (1) 汎用サーバでは，利用者はサーバにログインして，OSコマンドを実行することができる。 (2) 専用サーバでは，利用者はサーバにログインできない。このため，バッチPGMの実行，ファイルシステムへのアクセスを行えないが，Webベースの管理画面を通じて，次の管理機能を利用できる。 ① データベースの起動・停止，バックアップ・リストアなどの操作を行う。 ② ジョブスケジューラの機能を利用して，データベースの起動・停止，バックアップなどを，指定した間隔又は指定した日時に実行する。 3．専用サーバで提供されるサーバの障害対策 (1) 基本サービス ① 利用中の仮想サーバにハードウェア故障が発生した場合，他の仮想サーバに切り替えられる。 ② データベースのフルバックアップは，毎日自動的に行われる。 ③ データベースの更新ログは，5分ごとにバックアップストレージに保存される。この場合のストレージは，データベースが使用するストレージとは別に用意される。 (2) オプション ① スタンバイサーバを設定できる。スタンバイサーバは，利用中の仮想サーバとは異なる場所に設置されたハードウェア上で稼働し，利用中の仮想サーバ上のデータベースに対して行われたSQL文による更新が，スタンバイサーバ上のデータベースに即座に反映される。 ② 障害が発生すると，数分でスタンバイサーバへと自動的に切り替えられる。 〔クラウドサービスの選定・評価〕 1．サービスの選定 (1) APサーバには汎用サーバを，DBサーバには専用サーバを利用する。 (2) DBサーバは，〔性能見積り・評価〕と同様に，次の条件で性能見積りを行い，利用するクラスは表8から選択する。 ① 表8の仮想CPU単体の性能は，DBサーバの性能見積りの前提としたCPU単体の性能に相当する。 ② メモリサイズは，12Gバイト以上必要である。 ③ 必要な仮想CPU数及びメモリサイズの条件を満たす，必要最低限のクラスを選択する。 (3) 基本サービスでは，障害発生後に復元されたデータベースに，コミット済みのトランザクションによる更新が全て反映されていないこともあり得るので，スタンバイサーバ設定のオプションを使用する。 (4) DBサーバ上でバッチPGMを実行することができないので，バッチPGMはAPサーバ上で実行する。 2．サービスの評価 選定したサービスを基に，クラウドサービスの利用を次のように評価した。 (1) 性能 ① オンライン処理は，性能要件を満たしている。 ② バッチ処理は，DBサーバとAPサーバ間の通信による処理時間の増加が懸念される。そこで，〔業務の概要〕及び表2を基に，次の前提で各処理の探索対象行数，結果行数，サーバ間通信対象行数を試算し，バッチ処理の処理行数を表9にまとめた上で，対策を検討することにした。 ・共通マスタのテーブル参照は，試算の対象としない。 ・“旅費申請”，“交通費申請”，“一般経費申請”，“経費伝票”の各テーブルには，それぞれ処理年月をキーとする索引があるものとする。 ・申請数は，各事業部，各科目，各処理年月に均一に分布するものとする。 ・探索対象行数は，参照する全てのテーブルについて，各テーブルの選択条件に一致する行数の合計とする。 ・結果行数は，選択，集約などの演算の結果として得られる行数とする。 ・サーバ間通信対象行数は，処理ごとに，行の参照，追加のためにDBサーバとAPサーバ間で転送される行数とする。 表9 バッチ処理の処理行数（未完成） (2) 可用性，安全性，保守性 ① スタンバイサーバの設定によって，可用性は現行システムよりも向上する。 ② 安全性，保守性については，現行システムと同等である。 (3) コスト ジョブスケジュールを適切に設定することによって，クラウドサービスの利用料金を大幅に低減できることが分かった。 設問1 図1及び〔テーブルの物理設計〕について，(1)～(4)に答えよ。 (1) 図1中の【ア】，【イ】に入れる一つ又は複数の列名を答えよ。なお，列名には本文又は図表中の用語を用いること。また，【ア】，【イ】に入れる列が主キーを構成する場合，主キーを表す実線の下線を付けること。 (2) 表6中の太枠内に適切な字句を記入して，太枠内を完成させよ。 (3) 表6中の【ウ】に入れる適切な字句を答えよ。ここで，1～999のような，値の上限・下限に関する制約は，検査制約では定義しないものとする。 (4) 表7中の【a】～【d】に入れる適切な数値を答えよ。ここで，空き領域率は10%とする。 設問2 〔性能見積り・評価〕の“DBサーバの性能見積り”の記述中の【e】～【i】に入れる適切な数値を答えよ。 設問3 〔クラウドサービスの選定・評価〕について，(1)～(3)に答えよ。 (1) サービスの選定について，①，②に答えよ。 ① 性能見積りによって求められた，経費精算システム全体のDBサーバに必要な仮想CPU数を答えよ。また，表8から，選定条件を満たす専用サーバのクラス名を一つ答えよ。 ② 基本サービスで，復元されたデータベースにコミット済みのトランザクションによる更新が反映されていないことがあるのはどのような場合か。具体的に35字以内で述べよ。 (2) バッチ処理の性能について，①，②に答えよ。 ① 表9中の【j】～【n】に入れる適切な数値を答えよ。 ② 表9中の処理について，サーバ間通信対象行数の削減対策による効果が最も大きい処理を一つ挙げ，その対策内容を具体的に40字以内で述べよ。 (3) クラウドサービスの利用料金を低減できるジョブスケジュールの設定内容を，具体的に30字以内で述べよ。