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

共通の文章

データベースの設計,実装に関する次の記述を読んで,設問13に答えよ。 精密電子機器メーカのA社では,新たに経費精算システムを稼働させるに当たっ CT,クラウドサービスの利用を検討中である。経費精算システムは,現行の人事管 理システム(以下,現行システムという)から分離する旅費交通費精算機能,新た に開発する一般経費精算機能及び仮払金精算機能から成る。 [業務の概要】 1.組織,科目,経費予算,締日 (1)組織 ①A社には,10の事業部があり,事業部内に複数の部がある。 ②全社の経費精算事務を行う事務センタがある。 ②)科目 ①費用を用途によって区分したものを科目という。例えば,旅費交通費,通 信費,接待交際費,消耗品費など,20科目がある。 ②科目を細分化した内訳を,内訳科目という。例えば,旅費交通費には,宿 HA,日当などの内訳科目がある。 3)経費予算 事業部ごとに,経費の予算・実績の管理を行う。事業部ごとに任命された経 費管理責任者が,年度初め(4月)に科目別月別の経費予算を作成する。 (4)締日 経費申請の締日は,毎月20日である。 2.手続の種類 (1)旅費交通費精算 ①旅費 ・社員が,出張の費用を精算する手続である。 ・社員は,出張に先立って支出予定の旅費申請を行い,帰社後に実費を精算 する。 ②交通費 ・社員が,業務で移動に利用した電車,バス,タクシーなどの料金を精算す ax ff る手続である。 HA,前月の締日の翌日から当月の締日までの交通機関の利用を記録し, 1か月分の交通費をまとめて申請する。 (2)一般経費精算 OD接待交際費,通信費,消耗品費などを精算する手続である。 OQ精算手続には,社員が立て替えた代金を社員に支払う“立替経費精算",X 払先への代金支払を事務センタに依頼する“経費支払依頼”の2種類があり, 申請種別で区分される。 ③締日までに受け付けた申請については,月末までに支払を行う。 ⑬)仮払金精算 ①社員が,宿泊費,接待交際費などに使用する目的で一定額の仮払金の支払 を事前に受け,事後に精算する手続である。仮払金を使用して旅費,AE 費の支払を行った場合,旅費交通費精算又は一般経費精算を行う必要がある。 ②申請には,社員番号,申請理由,仮払金額,精算期限を記載する。 ③事務センタは,未精算の仮払金を仮払金残高として記録し,社員が精算期 限までに全額を精算するように促している。 ④一つの仮払いに対する精算が複数の日に分かれることもある。精算時には, 精算日ごとに精算金額を記録し,仮払金残高から精算金額を減額する。 3.申請・精算の流れ Q)申請 ①申請者は,旅費交通費,BER,仮払金の申請を登録する。申請には一 意な申請番号が付与される。 ②領収書などの証BRの提出が必要な場合には,証開をスキャナで取り込んだ 画像を添付する。証差には,申請番号ごとに一意な証開番号が付与される。 (②)LAK,庶務精査経費管理責任者確認 申請の承認精査及び確認は,申請ごとに次のように行われる。 ①申請者の上司は,申請内容を確認して承認,否認,差戻しのいずれかを判 定する。承認の場合は,申請に上司承認日を記録して庶務担当者に回付する。 ②車請者の所属事業部の庶務担当者は,申請書を精査する。問題がなければ, 申請に精査日を記録する。旅費交通費,一般経費の申請は経費管理次任者に ーー 回付し,仮払金の申請は事務センタに回付する。 ③経費管理責任者は,申請内容及び経費予算を確認して承認,HR,差戻し のいずれかを判定する。承認の場合は,申請に責任者承認日を記録して,事 務センタに回付する。 (3)支払 事務センタは,申請を受けて社員又は支払先への支払を行う。 ①旅費交通費は,申請ごと支払予定日ごとに作成した支払伝票を基に支払を 行い,支払伝票に支払実行日を記録する。 ②一般経費は,申請ごとに支払伝票を作成し,申請に支払伝票の支払予定日 及び支払番号を記録する。支払伝票を基に支払を行い,支払伝票に支払実行 日を記録する。 ③仮払金は,申請を基に一定額の支払を行い,申請に支払実行日を記録する。 [システムの概要〕 1.現行システム 1)現行システムの概要 ①現行システムは,旅費交通費精算,勤怠管理,労務管理など複数の機能か ら成る。 ②事業部,部,職位,社員,通貨など複数の機能に共通するマスタ(以下, 共通マスタという)は,一元的に更新される。 ③経費予算,申請に関する情報,及び申請から派生する伝票などの情報は, 経費予算の年月又は申請の処理年月が60か月前から現在までのデータが全て 保存されている。 ④旅費交通費精算機能の運用時間帯は,平日の8時23時であり,8時て20 時にオンライン処理,20時て23時にバックアップ及びバッチ処理が行われる。 (2)現行システムの構成 、①旅費交通費精算機能こは,APサーバ2台,DBサーバ1台が割り当てられ ている。DBサーバには,コールドスタンバイの待機サーバを置いている。 ②バッチ処理では,DBサーバ上に別途構築されたバッチアプリケーションの 実行環境上でプログラム(以下,バッチPGMという)を実行している。 ーa一 2.経費精算システム (1)経費精算システムの概要 ①旅費交通費精算機能,一般経費精算機能及び仮払金精算機能から成る経費 精算システムを稼働させる。 ②経費精算システムの運用時間帯は,現行システムの旅費交通費精算機能と 同じである。 (2)経費精算システムのテーブル構造,見積行数・データ所要量 経費精算システムのテーブル構造を図1に,主な列とその意味・制約を表1に, 主なテーブルの見積行数・データ所要量を表2に示す。 I 事業部(事業部コード,事業部名,…) 部(部コード,事業部コード,部名,。…) 職位(職位コード,職位名) 社員(社員番号,社員名,職位コード,住所,電話番号,振込口座情報,…) 所属(社員番号,配属年月日,部コード) 科目(科目コード,科目名,予算使用率しきい値,…) 内訳科目(内訳科目コード,科目コード,ARMAS,~~) 通貨(通貨コード,通貨名称,換算方法,社内標準換算レート,…) 経費予算(事業部コード,科目コード,年月,予算金額 旅費申請(申請番号,社員番号,旅費申請状態,上司承認日精査日,責任者承認日 処理年月,出張区分,出張目的,期間自,期間至,コメント,…) 旅費申請明細(申請番号,GES,内訳科目コード,期間自,期間至,単価,数量, 金額,出発地,到着地出発時刻,到着時刻,便名等,宿泊地, 日当開始日,日当終了日,通貨コード,外貨金額,…) 交通費申請(申請番号,HESS,内訳科目コード,交通費申請状態,上司承認日,精査日 責任者承認日,処理年月RIA,期間至,金額,…) 交通費申請明細(申請番号,行番号,支出日,行先,交通機関,出発地,到着地, 経由地,金額,往復の有無) 一般経費申請(申請番号,社員番号,申請種別一般経費申請状態上司承認日,精査日, 責任者承認日,処理年月内訳科目コード,支払金額,通貨コード, 外貨金額,支払先,支払目的,支払予定日,支払番号) 仮払金申請(申請番号,社員番号,申請理由,仮払金申請状態,処理年月|ア|) 仮払金精算(|イ]) 経費伝票(RES,申請番号,GES,科目コード,内訳科目コード,事業部コード 部コード,社員番号,処理年月,記帳日,起算日,金額,コメント) MER(申請番号,証開番号,スキャン画像,…) 支払伝票(支払予定日,支払番号,社員番号,支払金額,振込口座情報,振込先,支払実行日) 図1経費精算システムのテーブル構造(未完成・一部省略) —9 ee Hl主な列とその意味・制約 日申請を一意に識別する番号(1999,999,.999)。申請登録時に自動的に設定 申請画面では,指定した社員番号の登録済申請を照会できる。 し|0 CREA),‘1’(HESS),‘2’(承認済),‘3’(精査済)"4(確認 omens| 上司承認日,精査|上司の承認,庶務担当者の精査,経費管理責任者の承認が行われた日付 EMH MRE(1~10,000,000).一般経費申請登録時の指定は必須であ 通貨コード.se旅費申請,一般経費申請において,外貨で支払う場合に,通貨コード(3桁 多額の半角英数字)及び支払金額に相当する外貨金額【001- 9,999,999,999.99)を指定。申請登録時の指定は任意である。 支払先の名称,所在地(全角文字100字以内,平均文字数は20文字)。申 る。 数は64文字)。申請登録時の指定は必須である。 一般経費申請において,支払完了時に,支払の基になった支払伝票の支払 番号(1~99,999)を記録する。 表2主なテーブルの見積行数・データ所要量 ーPrページサイズoeデータ所要量 |交通費申請明細|3.600000|4000|225000|~"900 |一般経偶申請。|150000|4000 J。w ww| |仮払金申請。。|250001 000| |仮払金精算。|30000|000| 注記網掛け部分は表示していない。 (3)経費精算システムの処理 主な処理のCRUDを表3に,バッチPGMの処理内容を表4に示す。 表3主な処理のCRUD po Yea RY ETE 処理名部員|訳・|明請|申|請|算 経費予算登録|R||R|[ol|||||||| 部・|・予|明申|申費|経|金|金|伝|伝]伝 業位|A|貨|費|請費]費通|般|払|払|費|部|払 °社|A算|細請|請申|費|申|精|票|票|票 所|目費|交 -|科旅|細・|請 旅費申請。|]R|IRIRIR||co||julcl jcl| オ|交通満申請|RIRIR|||fol julcl lcl| ン上服経費申請|R|RIRIR|||lcUlulcl jcl| イeen|R|RIR|||||[oul|||| ン申請承認”。|R|IRIR||Rloulululu||[Rl]| ewe”。|RIR|||julululol|]Rleul 経費申請照会|R|R|R|RIRIRIRIR|||||| バ|際費伝村人成|R|R|RIR||RIRIR|||cl|| \会計データ作成|R|R|R|||||||[Ri|| 経費分析表作成|R|R|R||R||||||R||| 注記C:追加,R:参照,U:更新 表4バッチPGMの処理内容 ・“旅費申請",“交通費申請",“一般経費申請"テーブルの処理年月が当月 に一致する行を対象に処理を行う。 経費伝票作成・旅費申請に対応する“旅費申請明細"テーブルの行ごと,交通費申請に “対応する“交通費申請明細"テーブルの行ごと,“一般経費申請"テーブ ルの行ごとに対象行をバッチPGM中に読み込む。読み込んだ行ごとに “経費伝票"テーブルの各列に値を設定して1行を追加する。 =“に,会計データ1行を編集してファイルに出力する。 ・“経費予算”テーブルの年月が,当月の12か月前から当月までの行を対 象に処理を行う。 経費分析表作成“BEB”テーブルの行ごとに,対応する“経費伝票"テーブルの金額 を,GROUP BY名を用いて事業部,科目,年月ごとに集計して実績金額 を求め,予算金額との対比表をファイルに出力する。 (RDBMSの仕様 hs io oe (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使用可能なデータ型 CHAR(n)n文字の半角固定長文字列(1ミnミ255)。文字列がn字未満の場合は,文字列 "の後方に半角の空白を埋めてnバイトの領域に格納される。 NCHAR(n)n文字の全角固定長文字列(1ミnミ127)。文字列がn字未満の場合は,文字列 の後方に全角の空白を埋めて“nX2”バイトの領域に格納される。 VARCHARG)最大n文字の半角可変長文字列(1ミnミ8,000)。値の文字数分のバイト数の領 "|域に格納され,4バイトの制御情報が付加される。 NCHAR最大n文字の全角可変長文字列(1ミnミ4.000)。“"値の文字数X2”"バイトの領 VARYINGQ)|域に格納され,4バイトの制御情報が付加される。 —32,768~32,767の範囲内の整数。2バイトの領域に格納される。 Pama~2.147,483.647の範囲内の整数。4バイトの領域に格納され 精度m(1ミmミ31),位取りn(OSnSm)の10進数。“m+2二1"の小数部 0001-01-01~9999-12-31の範囲内の日付。4バイトの領域に格納される。 [テーブルの物理設計] 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(2=—-7HS5l),NU(非ユニーク 索引)がある。 ②主キーの索引は必ず作成する。 ③キー以外で値が一意となる列又は列の組合せには,必ずユニーク索引を 作成する。それ以外の列又は列の組合せが,外部キーを構成する場合は,必 ず非ユニーク索引を作成する。 ④各索引の構成別には,構成列の定義順に1からの連番を記入する。 (5)制約欄には,参照制約,検査制約をSQLの構文で記入する。 ー13一 表6一般経費申請"テーブルのテーブル定義表(未完成) 項目co NOT|格納長 an NULL|(バイト)|P[Nu Nu[Nu]vu| ames。|mrgeBR|Y|4lil|||| [社員番号。|cmAR@⑥|Y|el jil||| Pema|cmARO|Y|il||||| |一般経費申請状態|cHARO|Y|il||||| |上司承雪上日。lpaTE sO]N ll 5l||]]| wae[pare CT N|5l||||| feceam@e[pave|N|5l||||| 処理年月。lcHAR@⑥|Y|el||||| |内訳科目コード[cwaR@)|Y|3||lil|| |支払金額”。|mrgeER_|Y|4|||||| |通貨コード|cHARG Ne|||il| |0 1 EE |—“CS~s||||||| ee es'{#l|||||| Re as ee 10 1|5 WW||||| fil 約とCHECK(一般経費申請状態INCOT,273/4 5/9)) 注記網掛け部分は表示していない。 2.データ所要量の見積り “一般経費申請"テーブルのデータ所要量を見積もるために,表7を作成した。 ここで,索引のデータ所要量は考慮しないものとする。 表7“一般経費申請"テーブルのデータ所要量(未完成) pare La a|に0寺守| 注記項番6のデータ所要量は,項番15の値を用いて算出する。 (性能見積り・評価】 1.性能見積り 現行システムの旅費交通費精算機能のDBサーバは,処理がビークとなる締日前 にCPU使用率がしきい値に達しつつある。一般経費申請も,同様に締日前にビー クが見込まれるので,一般経費申請で必要となるCPUの性能見積りを行った。 ①)性能要件 ①1トランザクション当たりのターンアラウンドタイムは,1秒以下とする。 内訳は,APサーバ0.4秒以下,DBサーバ06秒以下である。 ②ピーク時に,旅費申請及び交通偶申請では,両者を合わせて1秒間に400ト ランザクションを,一般経費申請では1秒間に200トランザクションを処理す ることができる。 (22)DBサーバの性能見積りの前提 ①CPU使用率は80%以下とする。 ②CPUの1データページ当たりの処理時間は,0.2ミリ秒である。 ③1データページ当たりのストレージへのVO時間は,20ミリ秒である。 ④トランザクションごとに,平均50データページにアクセスする。 ©CPU待ち時間,索引の探索時間,ネットワークの通信時間は考慮しない。 (3)DBサーバの性能見積り ①CPUの1トランザクション当たりの処理時間は|e|ミリ秒であり, 200トランザクションの処理時間は|{|秒となる。CPU使用率を380% とするためには,同じ性能のCPUが少なくとも| g|個必要である。 ②Mir,データベースのバッファヒット率が0%%とすると,1トランザクショ ン当たりのyo時間は,|hn|秒で要件を満たさない。しかし,バッフ ァヒット率が60%のとき,CPU処理時間とO時間の合計は,|ji|秒 となり,要件の範囲内になる。 2.評価 現行システムの旅費交通費精算機能のDBサーバには,CPU及び周辺機器の増 設が必要となるが,現行システムの機器が2年後に更新時期を迎えることから,ク ラウドサービスの利用を検討する。 ~15- [クラウドサービスの概要]〕 検討対象のクラウドサービスの概要は,次のとおりである。 1.サービス概要・利用料金 (1)サービスとして提供されるサーバには,次の2種類がある。 ・アプリケーションソフトを稼働させるためのハードウェア,OSなどを備えた IaaS型の汎用サーバ ・ハードウェア,OS,データベースのプラットフォームを備えたPaaS型の専用 サーバ (2)汎用サーバのサービス利用料金は.サーバの稼働時間に対して課金される。 (3)専用サーバのサービス利用料金は,“データベース利用時間x基本サービス MARS”で課金される。オプションを利用すると,基本サービス利用料金に オプション利用料金が追加される。基本サービス利用料金は,表8に示すように サーバのクラスごとに定められている。 (4)専用サーバでは,現行システムと同じRDBMSを使用できる。 表8専用サーバのクラス ee Poe ff 8|C1の基本サービス利用料金X25 |C4|8|32|C1の基本サービス利用料金X75 eS 注記網掛け部分は表示していない。 2.制限事項・管理機能 (1)汎用サーバでは,利用者はサーバにログインして,OSコマンドを実行するこ とができる。 (2②)専用サーバでは,利用者はサーバにログインできない。このため,バッチ PGMの実行,ファイルシステムへのアクセスを行えないが,Webベースの管理 画面を通じて,次の管理機能を利用できる。 ①データベースの起動・停止,バックアップ・リストアなどの操作を行う。 ②ジョブスケジューラの機能を利用して,データベースの起動・停止,バッ —~Kh= クアップなどを,指定した間隔又は指定した日時に実行する。 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)MERE ①オンライン処理は,性能要件を満たしている。 ②バッチ処理は,DBサーバとAPサーバ間の通信による処理時間の増加が懸 念される。そこで,【業務の概要]ROR 2を基に,次の前提で各処理の探索 対象行数,結果行数,サーバ間通信対象行数を試算し,バッチ処理の処理行 数を表9にまとめた上で,対策を検討することにした。 ・共通マスタのテーブル参照は,試算の対象としない。 ・“旅費申請",“交通費申請",“一般経費申請",“経費伝票"の各テーブルに は,それぞれ処理年月をキーとする索引があるものとする。 ・申請数は,各事業部,各科目,各処理年月に均一に分布するものとする。 ・探索対象行数は,参照する全てのテーブルについて,各テーブルの選択条 件に一致する行数の合計とする。 ・結果行数は,BAR,集約などの演算の結果として得られる行数とする。 ・サーバ間通信対象行数は,処理ごとに,行の参照,追加のためにDBサーバ とAPサーバ間で転送される行数とする。 表9バッチ処理の処理行数(未完成) james|[|」]|nioool|Le]| jamamarm| [」]|Dey]|Eel| (2)可用性,安全性,保守性 ①スタンバイサーバの設定によって,可用性は現行システムよりも向上する。 ②安全性,保守性については,現行システムと同等である。 (3)コスト ジョブスケジュールを適切に設定することによって,クラウドサービピスの 利用料金を大幅に低減できることが分かった。 ー18一