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

問1　データベースの実装に関する次の記述を読んで、設問1～3に答えよ。 A社は、事務機器、オフィス家具、事務用品などの企業向けオフィスサプライ商品を販売している。A社では、取扱商品の多様化と受注数の増加に対応し、販売管理業務の効率向上を図るために、販売管理システムを再構築することにした。 〔A社の組織概要〕 (1) A社に商品を発注し購入している企業を顧客という。顧客には、企業単位に一意な顧客番号が付与されている。 (2) A社は、メーカから商品を仕入れて販売している。メーカには、一意なメーカコードが付与されている。 (3) A社には、本社と、販売業務を行う全国15か所の事業所がある。本社では、商品の選定、事業所の管理などを行っているが、販売業務は行っていない。本社及び事業所には、一意な事業所コードが付与されている。 (4) 事業所の担当地域内には、一つ以上の物流センタがあり、在庫管理と商品配送を行っている。物流センタには、一意な物流センタコードが付与されている。 (5) 社員は、本社又は一つの事業所に所属し、本社と事業所間又は事業所間の異動がある。社員には、一意な社員番号が付与されている。 〔商品の概要〕 (1) 約20、000アイテムの商品を、メーカから仕入れて販売している。商品には、一意な商品コードが付与されている。 (2) 商品は、商品分類コードによって分類されている。 (3) 商品によっては、返品可能なものがある。 (4) 商品は、色、柄、サイズ、パッケージの違いなどによって、在庫管理の単位であるSKUに細分化されている。SKUには、一意なSKUコードが付与されている。 〔受注と出荷指示〕 (1) 顧客は、商品カタログから商品を選択し、ファックス又は電子メールでA社の事業所あてに注文書を送付する。同じ顧客が、複数の事業所に注文を行うこともある。 (2) 注文書単位に注文を受け付ける。受注担当者は、注文書に記載された一つ以上のSKUについて、在庫があることを確認して受注伝票を作成する。受注伝票ごとに、一意な受注番号が付与される。 (3) 受注内容を確認し、出荷伝票を作成する。出荷伝票ごとに一意な出荷番号が付与される。 〔受注の取消と返品〕 (1) 顧客は、商品出荷前の注文を取り消すことができる。取消は注文書単位に行い、特定のSKUの注文だけを取り消すことはできない。 (2) 顧客は、返品可能な商品に限り返品することができる。ただし、納品後10日以内にA社の物流センタに到着することが条件となる。 (3) 取消又は返品があれば、受注数量及び出荷数量をすべてマイナスにした受注伝票及び出荷伝票（以下、赤伝という）を新たに作成する。さらに、一部のSKUだけが返品された場合は、返品されたSKUを除く受注伝票及び出荷伝票（以下、黒伝という）を新たに作成する。 (4) 返品された商品は、商品の状態に応じて、在庫に戻されるか、又は処分される。 〔代金の請求〕 (1) 事業所では、月初日から月末日までに納品が済んだ受注を確認して、月締めを行う。事業所によって月締めのタイミングは異なるが、翌月の第15営業日までには前月分の月締めを行う規則になっている。 (2) 事業所では、顧客ごとに、月締めの対象月中に納品が済んだ受注の受注金額と消費税額を合計して請求金額を求め、請求書を作成して顧客に送付する。あわせて、請求金額を未収金額に計上する。請求書には、顧客番号と振込先口座番号も記載されている。振込先口座番号は、事業所ごとに異なっている。 〔入金の確認〕 (1) 顧客は、請求書に記載されている請求年月日の翌月の月末日までに、請求金額を銀行振込によって支払う。その際、振込人欄に請求書に記載された顧客番号を記入する。 (2) 各事業所の請求担当者は、銀行から入金情報（振込人、入金日、入金金額）を取得する。振込人欄に記載された顧客番号で、当該顧客の請求済で未入金の請求書と照合して、次のように消込みを行う。 ① 入金金額を未消込金額に設定し、ゼロを不足金額に設定する。 ② 請求年月日の古いものから順に、最新の請求まで、次のいずれかを行う。 ・未消込金額が未収金額以上の場合、未消込金額から未収金額を差し引いた額を未消込金額に設定し、未収金額を消込金額として記録する。未収金額にゼロを設定する。 ・未消込金額がゼロよりも大きく、かつ、未消込金額が未収金額よりも小さい場合、未収金額から未消込金額を差し引いた額を、不足金額と未収金額に設定し、未消込金額を消込金額として記録する。未消込金額にゼロを設定する。 ・未消込金額がゼロの場合、不足金額に未収金額を加算する。 (3) 消込みの結果、顧客ごとの不足金額がゼロより大きければ、不足金額の支払を求める。また、顧客ごとの未消込金額がゼロより大きければ、未消込金額を返金する。 〔損金処理〕 請求後1年以内に回収できない請求は、未収金額をゼロにして損金処理を行う。 〔販売管理業務の流れとその概要〕 販売管理業務の流れを図1に、各業務の概要を表1に示す。ただし、損金処理を除く。 図1 販売管理業務の流れ 表1 各業務の概要 〔主な帳票とその概要〕 販売管理システムで用いられる主な帳票とその概要を、表2に示す。表2の各帳票の例を、それぞれ図2～4に示す。 表2 主な帳票とその概要 図2 未収金集計表の例 図3 商品別返品件数表の例 図4 受注金額推移表の例 〔販売管理システムのテーブル構造と処理の設計〕 (1) テーブル構造 設計された販売管理システムのテーブル構造を、図5に示す。 図5 販売管理システムのテーブル構造（一部の列名は省略） (2) テーブルと処理の関係 表1の各業務、及び図2～4に示した各帳票の作成を処理の単位として、主なテーブルに対する行の追加、参照、更新、削除との関係を表3にまとめた。 表3 主なテーブルと処理の関係 〔RDBMSの主な仕様〕 販売管理システムに使用する関係データベース管理システム（RDBMS）の主な仕様は、次のとおりである。 (1) 表領域 ① ディスク上の物理的なデータ格納領域を、表領域という。 ② 表領域には、データディクショナリなどを格納するシステム表領域、ソートなどに使用される一時表領域、ログ表領域、ロールバック表領域、及びユーザが作成するテーブルと索引のデータを格納するユーザ表領域がある。 ③ RDBMSがディスクとの間でデータの入出力を行う単位を、ブロックという。ブロックは、制御情報を格納するヘッダ部、テーブルのデータを格納するデータ部、及び更新によって増加するデータを格納する空き領域部から構成される。ブロックサイズ（1ブロックの長さ（バイト））には、1、024、2、048、4、096、8、192のような1、024の整数倍の値を指定する。表領域ごとにブロックサイズを設定できる。 (2) 排他制御 ① トランザクション内でテーブルに対する行の追加、参照、更新、削除が発生した場合は、当該行が格納されているブロックごとにロックを行い、コミット又はロールバックを発行した時点でロックを解放する。 ② デッドロックの検出機能を備えている。複数のトランザクション間でデッドロックを検出した場合、RDBMSはトランザクションを強制的にロールバックして、SQL文の発行元にエラーコードを返す。 (3) 索引 ① B木方式の索引を使用している。 ② すべての索引は、削除して再作成することができる。 (4) パーティション化 ① テーブルごとに一つ又は複数の列（以下、パーティションキーという）と、列値の範囲を指定し、列値の範囲ごとに異なる表領域に行を格納するパーティション化の機能を備えている。 ② パーティション化されたテーブルには、パーティションを特定するパーティションキーによる索引（以下、グローバル索引という）が作成される。そのほかに、一つ又は複数の列をキーとしてパーティションごとに独立した索引（以下、ローカル索引という）を作成することができる。 ③ テーブルを検索するSQL文のWHERE句に、パーティションキーに対応するグローバル索引とローカル索引の列が指定された場合、RDBMSはグローバル索引によってパーティションを特定し、そのパーティション内をローカル索引によって検索する。グローバル索引の列だけが指定された場合、RDBMSはグローバル索引によってパーティションを特定し、そのパーティション内を全件検索する。また、ローカル索引の列だけが指定された場合、RDBMSはすべてのパーティションについて、そのパーティション内をローカル索引によって検索する。 〔ディスク装置〕 販売管理システムで使用するディスク装置の構成は、表4に示すとおりである。 表4 販売管理システムで使用するディスク装置の構成 〔表領域の設計とデータベースの構築〕 表領域の設計とデータベースの構築を行うために、表5のテーブル別表領域割当と、表6の表領域のディスク装置への割当（案）を作成した。 表5 テーブル別表領域割当 表6 表領域のディスク装置への割当（案） 設問1　〔表領域の設計とデータベースの構築〕について、(1)～(3)に答えよ。 (1) 表5を基に、テーブル用のユーザ表領域の所要量見積りを次の手順で行う。この手順中の【ア】～【エ】に入れる適切な字句を答えよ。 ① 表5から、テーブルに格納するデータの平均行長を得る。 ② 表5から、見積行数を得る。 ③ ブロックサイズから、ヘッダ部と空き領域部の長さを差し引く。 ④ ③の結果を①の結果で割り、【ア】を求める。 ⑤ 【イ】を【ウ】で割り、【エ】を求める。 ⑥ ⑤の結果にブロックサイズを掛けて、表領域の所要量を求める。 (2) (1)の手順に従って、“在庫”テーブルの表領域“在庫TS”の所要量（バイト）を求めよ。ここで、ヘッダ部の長さを96バイトとし、空き領域部には、“（ブロックサイズ－ヘッダ部の長さ）×0.1”を割り当てるものとする。 (3) 表6に示した表領域のディスク装置への割当（案）の評価を行う。データ容量については、ディスクの空き領域を十分確保しているものとする。 (i) 入出力速度向上の観点から、“受注”、“受注明細”、“出荷”、“出荷明細”テーブルをHDD4、HDD5に配置することにした。このとき、これらのテーブル用の表領域を表6のように配置する理由を75字以内で述べよ。 (ii) 索引用の表領域を表6のように信頼性がほかよりも低いHDD2に配置した場合、障害の発生確率は高くなるが、それでも運用上問題が少ないと考えられる理由を30字以内で述べよ。 設問2　販売管理システムの性能について、(1)～(4)に答えよ。 なお、索引は主キーだけに定義されているものとする。 (1) 図3で例示した商品別返品件数表の作成での検索効率を上げるために、一つのテーブルに一つの列を追加する。列を追加するテーブル名と、追加する列名、及び性能向上が見込める理由を60字以内で述べよ。 (2) 図4で例示した受注金額推移表の作成では、一つのSQL文で、顧客別、月別、商品別に受注金額を集計することにした。このために、一つのテーブルに一つの列を追加する。列を追加するテーブル名と、追加する列名、及び追加した列をどのように使用するかを35字以内で述べよ。 (3) 販売管理システムのテスト中に、受注処理のレスポンスが悪化し、“顧客”テーブルへのロック待ちが多発していることが分かった。ロック待ちが多発している理由を45字以内で述べよ。 (4) トランザクションのISOLATIONレベルをREAD COMMITTEDとした場合、デッドロックの発生による処理性能の劣化が懸念される。次の①～③の処理の組合せのうち、デッドロックが起こる可能性のあるものを番号で一つ挙げ、その理由を具体的に40字以内で述べよ。 ① 受注と請求 ② 受注と出荷 ③ 受注と返品 設問3　“請求”テーブルについて、次のパーティション化の案A～Cを検討する。表7は、月締め、入金情報ごとの入金確認、未収金集計表作成の処理について、“請求”テーブルに対する検索内容をまとめたものである。記入済の欄に倣って、【a】～【c】に入れる適切な検索内容を述べよ。 〔パーティション化の案〕 A. 事業所コードをパーティションキーとし、事業所コードごとにパーティションを作成する。請求対象年月をキーとするローカル索引と顧客番号をキーとするローカル索引を作成する。 B. 請求対象年月をパーティションキーとし、請求対象年月ごとにパーティションを作成する。事業所コードをキーとするローカル索引と顧客番号をキーとするローカル索引を作成する。 C. 顧客番号をパーティションキーとし、顧客番号の値の範囲によって50個のパーティションを作成する。事業所コードをキーとするローカル索引と請求対象年月をキーとするローカル索引を作成する。 表7 “請求”テーブルのパーティション化の検討