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

問1　データベースの物理設計に関する次の記述を読んで、設問1～3に答えよ。 証券会社のG社は、個人顧客（以下、顧客という）からインターネットによる国内株式の売買注文を受け付け、市場での取引を仲介し、売買代金、株式を管理している。G社では現在、株式取引管理システムを構築中で、構築を担当するシステム部が、データベースの論理設計を終え、テーブルと索引の設計と定義、データ格納領域の所要量（以下、データ所要量という）の見積り、データの配置などの物理設計に着手したところである。 〔データベースの論理設計〕 システム部が作成した概念データモデルを図1に、関係スキーマを図2に示す。また、図2の太枠内の関係について、主な属性の意味と制約を表1に示す。 図1　株式取引管理システムの概念データモデル 図2　株式取引管理システムの関係スキーマ（一部省略） 表1　主な属性の意味と制約 〔株式取引管理システムの主な処理〕 株式取引管理システムにおける主な処理は、次のとおりである。 1．顧客が行う処理 (1) 銘柄照会 銘柄コード又は銘柄名を指定すると、該当する銘柄の属性情報、取引される市場名及び株価チャートが表示される。株価チャートは、現在を起点とした過去の一定期間の値動きを示すグラフであり、縦軸を株価、横軸を時間（時分、日、週、月、四半期、年）とし、表示期間と表示間隔を選択できる。横軸の1目盛りで表される表示間隔は、表示期間によって決められていて、選択肢は表2のようになる。図3は、表示期間1日、表示間隔5分を選択したときの14:20現在の株価チャートの表示例である。 表2　株価チャートの表示期間と表示間隔の選択肢 図3　株価チャートの表示例 (2) 注文登録 売買希望銘柄コードを指定して、注文情報の登録又は変更を行う。登録した注文情報は、10年前の注文年月日から現在までの全注文について保存される。 2．G社の社員が行う処理 (1) 銘柄登録 銘柄情報の登録又は変更を行う。関係“銘柄詳細”には、変更の都度、有効開始年月日を付けて履歴が保存される。 (2) 業種別集計表作成 顧客に提供する情報の一つに、業種別集計表がある。業種別集計表は、作成時に指定した対象期間（開始年月日と終了年月日）内に、取引依頼年月日が含まれる取引について、“取引株数×取引値段”で求めた売買金額を、取引依頼年月日時点の株式発行企業の業種の大分類ごとに集計した帳票である。 3．株式取引管理システムが行う処理 (1) 株価更新 市場から株価データを取得して、データベースに格納する。株価は、銘柄ごと1分ごとに、その1分内に取引があれば、取引値段の始値、安値、高値、終値が記録される。 (2) 取引登録 顧客からの注文を受けて、取引情報の登録を行い、注文の内容を市場に送信する。市場での取引成立を受けて、取引情報を更新する。登録した取引情報は、10年前の取引依頼年月日から現在までの全取引について保存される。 (3) 決済情報登録 処理当日に成立した取引について、代金支払予定と株式の受渡予定を決済情報として登録する。また、月末日には、処理当日の3か月よりも前に決済された決済情報を削除する。 4．エンティティタイプと処理のCRUD 主なエンティティタイプと処理のCRUDをまとめると、表3のとおりである。 表3　主なエンティティタイプと処理のCRUD 〔RDBMSの仕様とストレージの構成〕 1．表領域 テーブル、索引などのストレージ上の物理的な格納場所を、表領域という。 (1) RDBMSがストレージとの間でデータの入出力を行う単位を、データページという。データページは、制御情報を格納するヘッダ部、テーブルのデータを格納するデータ部で構成される。表領域ごとに、ページサイズを2、000、4、000、8、000、16、000バイトのいずれかで指定し、空き領域率（将来の更新に備えて、データ部内に確保しておく空き領域の割合）を指定する。 (2) 同じデータページに、異なるテーブルの行が格納されることはない。可変長列のデータを更新するときに、同じデータページの空き領域が不足する場合は、その行は他のデータページに格納され、元のデータページ内に格納先へのポインタが設定される。 (3) テーブルの再編成を行うと、データ行がデータページに再配置されて、更新、削除によって生じたデータの断片化が修正され、空き領域が再度確保される。 (4) 表領域ごとに、バッファサイズ（データバッファに入る最大ページ数）を指定する。テーブルへの操作は、対象行がデータバッファにあればデータバッファ上で行われ、データバッファにない場合はストレージへの入出力が行われる。 2．テーブル (1) テーブルの列には、NOT NULL制約を指定することができる。NOT NULL制約を指定しない列には、1バイトのNULLかどうかを表すフラグが付加される。 (2) 主キー制約は、主キーを構成する列名を指定する。 (3) 参照制約は、列名、参照先テーブル名、参照先列名を指定する。制約に違反する行の追加と削除、列値の更新は拒否される。 (4) 検査制約は、同じ行の列に対する制約と、副問合せによる他のテーブルに対する制約を記述することができる。 (5) 使用可能なデータ型は、表4のとおりである。 表4　使用可能なデータ型 3．索引 (1) 索引には、ユニーク索引と非ユニーク索引がある。 (2) 索引には、クラスタ索引と非クラスタ索引がある。クラスタ索引は、キー値の順番とキー値が指す行の物理的な並び順が一致し、非クラスタ索引は、ランダムである。 4．アクセスパスと統計情報 (1) アクセスパスは、統計情報を基に、RDBMSによって表探索又は索引探索に決められる。表探索では、索引を使用せずに全データページを探索する。一方、索引探索では、検索条件に適した索引によって対象行を絞り込んだ上で、データページを探索する。 (2) 統計情報の更新は、テーブルごとにコマンドを実行して行う。統計情報の更新によって、適切なアクセスパスが選択される確率が高くなる。 5．ストレージの構成 RDBMSのデータを格納するストレージは、高速、中速、低速の三つのレイヤで構成されていて、各レイヤは複数の装置から成る。ストレージ管理システムによって、使用頻度の高い表領域ほど、高速なレイヤに格納される。 〔データベースの物理設計〕 システム部は現在、テーブルと索引の設計と定義、データ所要量の見積り、データの配置について検討している。 1．テーブル構造の検討 図2の関係スキーマを、そのままテーブルとして実装した場合の課題を検討した。その結果、業種別集計表作成の処理は、業種の大分類を求めるので、複数テーブルの結合処理に時間が掛かるのではないかとの懸念が示された。そこで、複数テーブルの結合処理における検索条件列とテーブルの検索順序を、次のように整理した。 手順1　検索条件の列として取引依頼年月日を指定し、“取引”テーブルを検索して、取引依頼年月日、取引株数、取引値段、注文番号を求める。その結果行ごとに、次の手順2～6を行う。 手順2　検索条件の列として注文番号を指定し、“注文”テーブルを検索して、銘柄コードを求める。 手順3　検索条件の列として【a】、【b】を指定し、【c】テーブルを検索して【d】を求める。 手順4　検索条件の列として【d】を指定し、【e】テーブルを検索して【f】を求める。 手順5　【f】がNULLならば、手順3で求めた【d】を得る。【f】がNULLでなければ、【f】を得る。 手順6　手順5で得た値ごとに“取引株数×取引値段”の値を累積する。 2．テーブル定義表の作成 次の方針に基づいて、テーブル定義を行う。作成中のテーブル定義表の一部を表5～7に示す。 (1) 関係名をテーブル名に、属性名を列名に読み替える。 (2) データ型欄には、データ型、データ型の適切な長さ、精度、位取りを記入する。データ型の選択は、次の規則に従う。 ① 文字列型の列が全角文字を含む場合はNCHAR又はNCHAR VARYINGを選択し、それ以外の場合はCHAR、VARCHARを選択する。 ② 文字列型の列が主キー又は索引を構成する場合は、CHAR又はNCHARのいずれかにし、それ以外の場合は、格納領域の長さが最小になるようにする。 ③ 整数の列は、取り得る値の範囲に応じて、SMALLINT又はINTEGERを選択する。それ以外の数値の列はDECIMALにする。 ④ 日付、時刻、時刻印の列は、専用のデータ型を選択する。 (3) NOT NULL欄には、NOT NULL制約がある場合はYを、ない場合はNを記入する。 (4) 格納長欄には、RDBMSの仕様に従って、格納長（見積り）を記入する。可変長文字列は、属性の意味と制約から平均文字数が分かる場合はそれを基準にし、分からない場合は最大文字数の半分を基準にする。 (5) 索引の種類と構成列欄には、作成する索引を記入する。 ① 主キーの索引は必ず作成する。 ② 値が一意となる列又は列の組合せには、必ずユニーク索引を作成する。それ以外の列又は列の組合せが、検索条件、結合条件に使用され、索引による性能向上を見込める場合に非ユニーク索引を作成する。 ③ 索引の種類には、P（主キーの索引）、U（ユニーク索引）、NU（非ユニーク索引）があり、いずれかを記入する。 ④ 索引の構成列には、定義順に1からの連番を記入する。 (6) 制約欄には、参照制約、検査制約をSQLの構文で記入する。 表5　作成中の“銘柄”テーブル定義表 表6　作成中の“株価”テーブル定義表 表7　作成中の“取引”テーブル定義表 3．データ所要量見積りと表領域へのデータ配置 主なテーブルのデータ所要量見積りとバッファサイズ設定を、表8にまとめる。テーブルのデータ所要量見積りは、データページのヘッダ部の長さを100バイトとして、次の手順①～④で行った。 手順①　表8から、見積行数、平均行長、ページサイズを得る。 手順②　“（ページサイズ－100）×（1－空き領域率）÷平均行長”の小数点以下を切り捨てて、【g】を求める。 手順③　“【h】÷【g】”の小数点以下を切り上げて、【i】を求める。 手順④　“【i】×【j】”でデータ所要量を求める。 検討の結果、表8の各テーブルは、それぞれ異なる表領域に配置する。また、索引は、索引用の表領域に配置し、索引用のデータバッファ上で全てのキー値の検索が行えるように、十分なバッファサイズを設定する。 表8　主なテーブルのデータ所要量見積りとバッファサイズ設定（作成中） 〔物理設計のレビューと性能測定〕 システム部における物理設計のレビューで、銘柄照会の応答時間に懸念が示された。特に、株価チャートは、多くの顧客が頻繁に参照するので、応答時間について一定のサービスレベルを保証することが求められる。そこで、株価チャート表示の応答時間の試算と、テスト環境での性能測定を行うことにした。 1．応答時間の試算と改善案の検討 机上で応答時間を試算したところ、“株価”テーブルの検索時に、ストレージへのI/Oが集中し、求められる性能が得られないことが分かった。そこで、対策として次の二つの改善案を検討した。 案1　主キーの索引を、クラスタ索引に変更する。 案2　1分間隔でデータを格納するのではなく、株価チャートの表示期間と表示間隔に合わせて、時分、年月日、年週、年月などの間隔で集計したデータをそれぞれ別のテーブルに格納し、その全行をデータバッファに入れる。集計データの候補と見積行数を表9に示す。 表9　集計データの候補と見積行数 2．改善案の評価とテスト環境での性能測定 案1、2の応答時間の試算値を評価した結果、案2を採用することに決定し、テスト環境での性能測定を行った。案2実施後の応答時間の試算では、例えば、株価チャートにおいて、ある銘柄の1分間隔のデータは500行であり、バッファヒット率100％のとき、1行当たりの処理時間は0.01ミリ秒なので、検索処理時間は5ミリ秒と推定された。 設問1　〔データベースの物理設計〕の“テーブル構造の検討”について、(1)、(2)に答えよ。 (1) 手順3～5中の【a】～【f】に入れる適切なテーブル名又は列名を答えよ。 (2) (1)を踏まえて、テーブルへの列の追加によって検索効率を改善するために、表10の案A、Bについて検討することにした。案Bの“検索効率が上がる理由”を20字以内で、“他の処理の変更内容”を50字以内で述べよ。 表10　テーブル構造変更案の検討（未完成） 設問2　〔データベースの物理設計〕の“テーブル定義表の作成”及び“データ所要量見積りと表領域へのデータ配置”について、(1)～(4)に答えよ。 (1) 表5と表7の太枠内に適切な字句を入れ、表を完成させよ。 (2) 次の制約①と制約②は、データベースの論理設計と処理内容からデータベースへの実装が妥当でないと判断された。その理由をそれぞれ40字以内で述べよ。 制約①　“注文”テーブルの注文株数は、対応する“銘柄”テーブル行の売買単位が指定されている場合は、その整数倍である（検査制約）。 制約②　“取引”テーブルに、株式決済番号を参照列として、“決済”テーブルの決済番号を参照する（参照制約）。 (3) テーブルのデータ所要量見積り手順②～④中の【g】～【j】に入れる適切な字句を答えよ。また、表8中の【ア】、【イ】に入れる適切な値を答えよ。 (4) 表8では、“銘柄”テーブルの空き領域率を高めに設定している。この設定によって、“銘柄”テーブルの参照時及び更新時の検索対象ページ数を最小限にとどめる効果が期待される。その理由を50字以内で述べよ。 設問3　〔物理設計のレビューと性能測定〕について、(1)～(3)に答えよ。 (1) “応答時間の試算と改善案の検討”の案1で、主キーの索引をクラスタ索引に変更し、再編成することで、当初は、株価の検索効率が向上するが、一定期間経過後に悪化することが予想される。この二つの予想について、その根拠をそれぞれ40字以内で述べよ。 (2) “応答時間の試算と改善案の検討”の案2で、表9の集計データを組み合わせてテーブルに格納するに当たり、表11の①～④の組合せを考えた。これらの組合せのうち、表2の表示期間と表示間隔を漏れなく表示でき、他の集計データから求まる冗長なデータを保持せず、集計データの全行をデータバッファ上で検索できるようにするには、どの組合せが適切か。解答用紙の適否欄に、適切な組合せの場合は○を、適切でない組合せの場合は×を記入せよ。また、適切でない場合は、その理由を具体的に述べよ。ここで、集計データの平均行長は全て35バイト、ページサイズは4、000バイト、データバッファは30、000ページ、空き領域率は10％とする。 表11　テーブルに格納する集計データの組合せ (3) “改善案の評価とテスト環境での性能測定”において、性能測定の結果、集計データのテーブルの検索処理時間は5秒、バッファヒット率は100％、CPU使用時間は約5秒であった。試算値に対して、実測値が大幅に異なったことについて、考えられる原因とその対策を、それぞれ30字以内で述べよ。ここで、ハードウェアの障害、他の処理の影響はないものとする。