DB勉強会レポート_2019年01月15日(火)実施分【前編】

こんにちは。 今回は内容が盛り沢山なので、前編と後編に分割します。 今回の解説範囲 平成30年度 春期 データベーススペシャリスト試験 午後Ⅰ 問3 設問1 (3) ⚓ 設問1 (3) 作業W4(追加制約設計)に関する表4の索引について、①、②に答えよ。 実際に問題を解く前に、設問に関する情報を問題文中から探し整理していきましょう。 図1 主なテーブル構造(抜粋) 鍵(施設ID, 鍵番号, 券番号, 使用中フラグ) 店舗利用(利用時刻, 施設ID, 店舗ID, 鍵番号, 商品ID, 利用額, ポイント数, 未精算フラグ) 精算明細(利用年月日, 利用時刻, 施設ID, 券番号, 店舗ID, 商品ID, 利用額, ポイント数) * 実線の下線は主キーを表しています。 表2 物理DB設計及び実装の作業工程表(抜粋) 作業順 作業ID 作業名 作業内容 4 W4 追加索引設計 DMLのアクセスパスを想定し、性能向上のために追加索引を設計する ⚓ 表3 利用額の精算時に実行されるDMLの例 DML DMLの構文 DML1 SELECT 券番号  FROM 鍵  WHERE 施設ID = :施設ID AND 鍵番号 = :鍵番号 DML2 INSERT INTO 精算明細()  SELECT :利用年月日, 利用時刻, 施設ID, :券番号, 店舗ID, 利用額, ポイント数  FROM 店舗利用  WHERE 施設ID = :施設ID AND 鍵番号 = :鍵番号 AND 未精算フラグ = ‘Y’ DML3 SELECT   FROM 精算明細  WHERE 利用年月日 = :利用年月日 AND 施設ID = :施設ID AND 券番号 = :券番号 注記1 網掛け部分は表示していない。 注記2 ホスト変数の利用年月日には, 当日の現在日付が設定される。 各DMLを日本語に直すと以下の通りです。 DML1: それぞれのホスト変数と一致する施設IDと鍵番号を持つ行を鍵テーブルから選択し、鍵番号を射影する。 DML2: それぞれのホスト変数と一致する施設IDと鍵番号を持ちなおかつ未精算フラグが’Y’の行を店舗利用テーブルから選択し、利用年月日(ホスト変数)、利用時刻、施設ID、券番号(ホスト変数)、店舗ID、利用額、ポイント数列を射影して精算明細テーブルに挿入する。 DML3: それぞれのホスト変数と一致する利用年月日、施設ID、券番号を持つ行を精算明細テーブルから選択する。 * DDL、DML、DCLの定義や区別をお忘れの方は逆引きSQL構文集でおさらいしておきましょう。 ⚓ 表4 DML2及びDML3のために追加した索引 索引 設計対象のDML テーブル名 索引のキーの構成 索引1 DML2 店舗利用 施設ID, 鍵番号, 未精算フラグ 索引2 DML3 精算明細 利用年月日, 施設ID, 鍵番号 設問1 (3) ① 解説 ①: 索引1は, ユニーク制約又は非ユニーク制約のどちらに該当するか答えよ。 まずは、「ユニーク制約」と「非ユニーク制約」とは何かが分からなければ問題に答えられないので、定義を確認しておきましょう。 IBM Knowledge Centerによると、それぞれ以下のように定義されています。 ユニーク索引は、表内のデータの 2 つの行が同一のキー値を持たないようにすることによりデータ整合性の維持に貢献する索引です。 データが入っている既存の表にユニーク索引を作成する場合、索引キーを構成する列または式の値について固有性がチェックされます。キー値が重複する行を含む表では、索引の作成処理が失敗します。表にユニーク索引を定義した場合、索引内でキーが追加または変更されるときには必ず、固有性が強制されます。強制される操作の例をいくつか挙げると、挿入、更新、ロード、インポート、整合性の設定などがあります。 (一部省略) 非ユニーク索引は、その索引が関連付けられる表に対して制約を適用するために使用されることはありません。その代わり、非ユニーク索引は、頻繁に使用されるデータ値のソート順序を維持することにより照会のパフォーマンスを改善するためだけに使用されます。 IBM Knowledge Centerより引用 まとめると以下の通りになります。 制約名 一意性 NULL値の許容 特徴 ユニーク制約 保証する ユニーク制約が一つの列 => NULL値は一つのみ許容する ユニーク制約が複数列 => 値とNULL値の組み合わせで1つのみ許容する 索引の作成時に重複する行は存在出来ない 非ユニーク制約 保証しない 許容する 頻繁に使用されるデータ値のソート順序を維持し、照会のパフォーマンスを改善するためだけに使用 次に、表1でそれぞれのキーの意味・制約を確認しましょう。 列名 意味・制約 鍵番号 施設内のロッカーを識別する番号。客の精算後、鍵は再利用される。 未精算フラグ 利用額が未精算の場合:’Y’精算済みの場合:’N’ ここで、具体的なシナリオを考えてみましょう。 … Continue reading DB勉強会レポート_2019年01月15日(火)実施分【前編】