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

問1　データベースの物理設計に関する次の記述を読んで、設問1～3に答えよ。 R県では、県内の病院、診療所などの医療機関から診療データを収集し、標準化して蓄積し、医療機関間の情報連携、薬品の有効性などの調査、統計資料の作成などに利用する地域医療情報システムを構築することになった。システム開発を担当するM社では、現在、データベースの論理設計を終え、物理設計に着手したところである。 〔データベースの論理設計〕 地域医療情報システムの概念データモデルを図1に、主な属性の意味と制約を表1に、関係スキーマを図2に示す。 図1　地域医療情報システムの概念データモデル 表1　地域医療情報システムの主な属性の意味と制約 図2　地域医療情報システムの関係スキーマ（一部省略） 〔地域医療情報システムの処理〕 地域医療情報システムでは、次の処理が行われる。 1. データ更新処理 医療機関から診療データファイルを受信する。診療データは、患者の名寄せ、コード値の標準化などの必要な処理を行った上で、バッチ処理で地域医療情報システムのデータベースに格納される。格納される主なデータの特徴は、次のとおりである。 (1) 県内の医療機関数は200で、医療機関によって患者数、オーダの数にはばらつきがある。 (2) 名寄せ後の患者数は300万人で、大きな増減はないと見込んでいる。 (3) 10年前のオーダ発行日から現在までの全オーダを保存する。オーダの数は、毎月ほぼ一定で、年間9、000万件に達する。 2. データ分析処理 地域医療情報システムで実行されるデータ分析処理を、表2に示す。 表2　データ分析処理 〔RDBMSの仕様〕 1. 表領域 (1) テーブル、索引のストレージ上の物理的な格納場所を、表領域という。 (2) RDBMSがストレージとデータの入出力を行う単位を、データページという。 データページには、テーブル、索引のデータが格納される。表領域ごとに、ページサイズ（1データページの長さで、2、000、4、000、8、000、16、000バイトのいずれか）と、空き領域率（将来の更新に備えて、データページ内に確保しておく空き領域の割合）を指定する。 (3) 同じデータページに、異なるテーブルの行が格納されることはない。 2. テーブル (1) テーブルの列には、NOT NULL制約を指定することができる。NOT NULL制約を指定しない列には、NULLかどうかを表す1バイトのフラグが付加される。 (2) 主キー制約には、主キーを構成する列名を指定する。 (3) 参照制約には、列名、参照先テーブル名、参照先列名を指定する。制約に違反する行の追加と削除、列値の更新は拒否される。 (4) 検査制約には、同じ行の列に対する制約と、副問合せによる他のテーブルに対する制約を記述することができる。 (5) 使用可能なデータ型は、表3のとおりである。 表3　使用可能なデータ型 3. 索引 (1) 索引には、ユニーク索引と非ユニーク索引がある。 (2) 索引には、クラスタ索引と非クラスタ索引がある。クラスタ索引は、キー値の順番とキー値が指す行の物理的な並び順が一致し、非クラスタ索引はランダムである。 4. アクセスパス アクセスパスは、統計情報を基に、RDBMSによって表探索又は索引探索に決められる。表探索では、索引を使用せずに全データページを順次に探索する。一方、索引探索では、検索条件に適した索引によって対象行を絞り込んだ上で、データページを探索する。 5. テーブルの物理分割 (1) テーブルごとに一つ又は複数の列を区分キーとし、区分キー値の範囲ごとに物理的な格納領域を分ける。これを物理分割という。 (2) 物理分割されたテーブルには、区分キー値の範囲ごとに分割された索引（以下、区分内索引という）を定義することができる。 (3) テーブルを検索するSQL文のWHERE句の述語に、区分キー列を指定すると、RDBMSは区分キー値で区分を特定する。さらに、区分内索引が使用できる場合は、区分内索引によって区分内を探索する。 (4) RDBMSは、問合せの実行時に、一つのテーブルの複数の区分を探索する場合、最大で20区分の探索を同時に並行して実行し、探索結果をまとめて返却する。探索対象の区分数が20を超える場合、20区分を同時に並行して探索し、一つの区分の探索が終了するごとに、次の区分を探索する。 〔データベースの物理設計〕 データベースの物理設計に着手したM社の設計担当者は、テーブルと索引の設計と定義について検討した。 1. テーブル構造の検討 (1) 表2の処理1では、テーブルの結合処理に時間が掛かるおそれがあるという懸念から、改善方法を検討することにした。 (2) 関係“オーダ”の実装について次の案A～Cを検討した結果、案Aを採用することにした。関係“オーダ”、“処方”、“検査”以外のテーブルは、関係スキーマどおりとする。 案A　スーパタイプと全てのサブタイプを、一つのテーブルにする。 案B　スーパタイプとサブタイプを、それぞれテーブルにする。 案C　サブタイプだけを、それぞれテーブルにする。スーパタイプの属性は列として各テーブルに保有する。 検討に当たって、表2の処理2～4を、それぞれ一つのSQL文で実行する問合せにおいて、関係“オーダ”、“処方”、“検査”に対応するテーブル間で行われる内結合、和集合の演算を、表4にまとめた。 表4　テーブル間で行われる内結合、和集合の演算（未完成） 注記　“内結合”、“和集合”、“内結合及び和集合”のいずれか、又はそれ以外の場合に、“－”を記入する。 2. テーブル定義表の作成 次の方針に基づいて、テーブル定義を行う。作成中のテーブル定義表の一部を表5～7に示す。 (1) 関係名をテーブル名に、属性名を列名に読み替える。 (2) データ型欄には、次の規則に従って、格納領域の長さが最小になるようにデータ型を選択して記入し、適切な長さ、精度、位取りも併せて記入する。 ① 整数は、SMALLINT、INTEGERを優先する。 ② 日付は、専用のデータ型を選択する。 (3) NOT NULL欄には、NOT NULL制約があればYを、なければNを記入する。 (4) 格納長欄には、RDBMSの仕様に従って、格納するデータ長の見積りを記入する。可変長文字列は、列値の平均的な長さが分かる場合は、その長さを基準にし、分からない場合は、最大長の半分（小数点以下は切上げ）を基準にする。 (5) 索引の種類と構成列欄は、縦列が一つの索引を表す。索引の種類には、P（主キーの索引）、U（ユニーク索引）、NU（非ユニーク索引）のいずれかを記入する。索引の種類の下部の構成列には、索引を構成する列の定義の順を、1からの連番で記入する。 (6) 主キーの索引は必ず作成する。主キー以外の索引は、一つ以上の列が検索又は結合の条件に使用される場合に必ず作成する。 (7) 制約欄には、参照制約、検査制約をSQLの構文で記入する。参照制約の参照動作（NO ACTION、CASCADE、SET NULLなど）は、記入しない。 表5　“患者基本”テーブル定義表 表6　“患者詳細”テーブル定義表（作成中） 表7　“オーダ”テーブル定義表 注記　案Aの採用を前提としている。網掛け部分は、表示していない。 〔問合せの検討〕 1. 表2の処理5の問合せ検討 表8と表9の2通りの問合せについて、実行結果を比較することにした。 表8　処理5の問合せ（1） 表9　処理5の問合せ（2） 2. 表2の処理6の問合せ検討 処理6の問合せについて、(a)で選択される“オーダ”テーブルの行ごとに、(b)は考慮せず、(c)で必要になる問合せを個別に抽出して、表10～12にまとめた。 表10　処理6の問合せ（1） 表11　処理6の問合せ（2） 表12　処理6の問合せ（3） 3. 問合せの傾向分析 表2の処理1～6の参照テーブルを表13にまとめ、問合せで参照されるテーブルを分析した。 表13　処理1～6の参照テーブル（作成中） 注記　○：テーブルが処理で参照されることを表す。 処理5の(1)と(2)は、それぞれ表8と表9の問合せに対応している。 〔性能の見積りと物理分割の検討〕 データ分析処理では、大量のデータページにアクセスが発生し、ディスクへのI/Oがボトルネックとなることが予想される。そこで、M社では、探索対象ページ数を試算して性能の見積りを行った上で、性能改善のための対策を検討することにした。 1. 探索対象ページ数試算の前提 (1) 試算に用いる問合せ ① “オーダ”テーブルへのアクセスにおける探索対象ページ数を試算する。 ② 性別、年齢別などの条件で絞り込まれた10、000人の患者について、オーダ発行日が、指定された年月から1年以内のオーダを抽出する。 (2) 試算に用いる前提条件と基礎数値 ① “オーダ”テーブルの1ページの平均行数は、50行とする。 ② 患者ごとのオーダの数は、10年間1、800件で、各年月に均一に分布しているものとする。 ③ クラスタ索引の定義されたテーブルの行は、索引の列値ごとに異なるデータページに格納されるものとする。したがって、例えば、索引の列値が等しい1、800行のデータは、36ページに格納される。 ④ バッファヒット率は、索引のデータページでは100％、テーブルのデータページでは0％とする。 2. 探索対象ページ数の試算 次の案X～Zのいずれか一つの索引を定義することを想定して、「1. 探索対象ページ数試算の前提」に基づいて探索対象ページ数を試算し、表14にまとめた。 案X　医療機関IDをキーとするクラスタ索引 案Y　患者IDをキーとするクラスタ索引 案Z　患者IDをキーとする非クラスタ索引 表14　索引による探索対象ページ数試算値（作成中） 3. 物理分割の検討 “オーダ”テーブルについて、物理分割による処理性能の向上について検討することにした。物理分割の次の案①、②について、「1. 探索対象ページ数試算の前提」に基づいて、探索対象ページ数を試算し、表15にまとめた。試算に当たって、区分キーには、非クラスタの区分内索引が作成され、それ以外の区分内索引はないものとする。また、区分キーの値は、各区分に一様に分布するものと仮定する。 案①　オーダ発行日を区分キーとし、その年月によって120に区分する。 案②　患者IDを区分キーとし、その値の範囲によって最大120に区分する。 表15　物理分割案の探索対象ページ数試算値（作成中） 設問1　〔データベースの物理設計〕について、(1)～(4)に答えよ。 (1) テーブル構造の検討において、表2中の処理1で、処理の実行結果を変えずに、結合対象テーブル数を最小にするために、一つのテーブルに二つの列を追加する。列の追加対象となるテーブル名と追加する列名を答えよ。 (2) “患者基本”テーブルのデータ所要量（百万バイト）を求める次の記述中の【a】～【e】に入れる適切な数値を答えよ。ここで、表領域のデータページのページサイズを4、000バイト、空き領域率を10％とする。 “患者基本”テーブルの平均行長は【a】バイト、1ページの平均行数は【b】行、見積行数は【c】行である。したがって、必要ページ数は【d】ページ、データ所要量は【e】百万バイトとなる。 (3) 表6中の太枠内に適切な字句を記入して、太枠内を完成させよ。また、【ア】に入れる参照制約について、参照制約定義の対象とすべきテーブル名を全て答えよ。 (4) 関係“オーダ”の実装について、表4中の太枠内に適切な字句又は“－”を記入し、表を完成させよ。また、案Aでは、表7に記入済みの制約に加えて、他の案にはない次の検査制約が必要になる。【イ】、【ウ】に入れる適切な字句を答えよ。 検査制約：オーダ種別が処方の場合は、用法が【イ】及び検査項目数が【ウ】、並びに、オーダ種別が検査の場合は、用法が【ウ】及び検査項目数が【イ】 設問2　〔問合せの検討〕について、(1)～(3)に答えよ。 (1) 表8と表9の問合せの実行結果を比較したところ、複数の医療圏で医療コストの集計値が異なることがあった。どのような場合に集計値の相違が発生するか。具体的に45字以内で述べよ。 (2) 表2中の処理6の問合せについて、(c)に該当する患者IDの集合を得るための集合演算を答えよ。ここで、表10～12の問合せ結果の集合をそれぞれR1～R3とし、集合演算は、次の凡例に従うものとする。 凡例　A∩B：AとBの積集合 A∪B：AとBの和集合 ¬A：Aの補集合 A∩(B∪C)：演算の順位は括弧を用いて表す。 (3) 表13中の太枠内に“○”を記入し、表を完成させよ。 設問3　〔性能の見積りと物理分割の検討〕について、(1)～(3)に答えよ。 (1) 表14中の太枠内に適切な数値を入れて表を完成させよ。 (2) 表15中の太枠内に適切な数値を入れて表を完成させよ。 (3) 物理分割の案①について、区分内索引として、患者IDをキーとするクラスタ索引を使用した場合の探索対象ページ数を試算した。その結果、索引の定義は案Yと同じであるが、試算値が表14と異なっていた。試算値が異なる理由を、80字以内で具体的に述べよ。