データベーススペシャリスト　平成１１年度　午後�� 解答

問１
設問1
　　担当
　┌────┬───┬─────┐
　│教師番号｜校番号｜クラス番号｜
　｜────｜───｜─────｜
　└────┴───┴─────┘
　　座席
　┌────┬────┬───┬─────┬────┐
　│児童番号｜変更日時｜校番号｜クラス番号｜座席番号｜
　｜────｜────｜　　　｜　　　　　｜　　　　｜
　└────┴────┴───┴─────┴────┘


設問2
（１）非候補キー｛塾内平均点，塾内標準偏差｝は候補キーの一部｛校番号，学力試験番号｝に部分関数従属するため

（２）入力ミスがない限り更新処理がなく検索処理のみであるため

（３）試験成績
　　┌────┬──────┬──┬──┬────┬────┬─────┐
　　│児童番号｜学力試験番号｜教科｜得点｜校内順位｜塾内順位｜塾内偏差値｜
　　｜────｜──────｜──｜　　｜　　　　｜　　　　｜　　　　　｜
　　└────┴──────┴──┴──┴────┴────┴─────┘
　　　試験結果統計
　　┌──────┬──┬─────┬──────┐
　　│学力試験番号｜教科｜塾内平均点｜塾内標準偏差｜
　　｜──────｜──｜　　　　　｜　　　　　　｜
　　└──────┴──┴─────┴──────┘


設問3
（１）“クラス”テーブルに“教科”列を主キーの一部として追加する

（２）座席
　　┌────┬────┬───┬─────┬──┬────┐
　　│児童番号｜変更日時｜校番号｜クラス番号｜教科｜座席番号｜
　　｜────｜────｜　　　｜　　　　　｜　　｜　　　　｜
　　└────┴────┴───┴─────┴──┴────┘
　　　担当
　　┌────┬───┬─────┬──┐
　　│教師番号｜校番号｜クラス番号｜教科｜
　　｜────｜───｜─────｜──｜
　　└────┴───┴─────┴──┘

問２
設問1
　UPDATE 従業員 SET 退職フラグ = '1'
　WHERE 退職フラグ <> '1' AND
　　　　生年*10000 + 生月*100 + 生日 <= 19390521

　※定年を迎えるのは満６０歳の誕生日の前日なので21日までになる。


設問2
　SELECT 従業員.従業員コード, 従業員.氏名, COUNT(従業員家族.*)
　FROM　従業員, 従業員家族
　WHERE 従業員.従業員コード = 従業員家族.従業員コード AND
　　　　従業員家族.扶養フラグ = '1' AND
　　　　従業員家族.生年 >= 1987
　　　　GROUP BY 従業員.従業員コード, 従業員.氏名
　　　　ORDER BY 従業員.従業員コード;


設問3
（１）新規組織コードへの移行の場合，組織テーブルにデータを挿入後に従業員テーブルの該当データを更新する。
　　　統合の場合，従業員テーブルの該当データを統合組織コードへ更新した後に組織テーブルのデータ削除を実行する。

（２）
　（ア）読み込んだ旧組織コードと一致する“組織”テーブルの組織コードを，新組織コードに変更する。
　（イ）“組織”テーブルからダミーレコードを削除する。

問３
設問1
（１）満たさない
　　※解説
　　｛井上，6/5，210｝→A400
　　｛鈴木，6/5，210｝→A400　のように全然主キーになってないのが分かる。

（２）｛便名，日付，乗員｝

（３）第１正規形
　　　ドメインが単純でかつ非候補キー“機種”が候補キーの一部｛日付，乗員｝の部分関数従属するため。


設問2
（１）｛便名，日付｝→“機種”が繰り返し出現する。

（２）｛乗員，日付｝→“機種”の関係が失われている。

（３）便　（便名，日付，機種名）
　　　乗務（便名，日付，乗員）


設問3
（１）｛出発地，便名｝のどちらにもデータがあるレコードが存在しないため

（２）
　　┌───┬──┬──┬──┬──┬───┬────┬──┬──┬────┐
　　｜班番号｜乗員｜便名｜時間｜肩書｜総時間｜ 出発地 ｜曜日｜資格｜　言語　｜
　　┝━━━┿━━┿━━┿━━┿━━┿━━━┿━━━━┿━━┿━━┿━━━━┥
　　｜Ｇ１　｜　　｜　　｜　　｜　　｜　　　｜　東京　｜月　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜Ｇ２　｜　　｜　　｜　　｜　　｜　　　｜ロンドン｜水　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜Ｇ１　｜井上｜210 ｜５０｜Ｃ　｜７００｜　東京　｜月　｜２級｜ドイツ語｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜Ｇ１　｜鈴木｜210 ｜７０｜Ｃ　｜８００｜　東京　｜月　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜Ｇ２　｜阿部｜320 ｜９０｜Ｐ　｜９００｜ロンドン｜水　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜Ｇ２　｜鈴木｜320 ｜４０｜Ｃ　｜８００｜ロンドン｜水　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜　　　｜井上｜　　｜　　｜Ｃ　｜７００｜　　　　｜　　｜２級｜ドイツ語｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜　　　｜鈴木｜　　｜　　｜Ｃ　｜８００｜　　　　｜　　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜　　　｜阿部｜　　｜　　｜Ｐ　｜９００｜　　　　｜　　｜　　｜　　　　｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤
　　｜　　　｜井上｜　　｜　　｜Ｃ　｜７００｜　　　　｜　　｜２級｜ドイツ語｜
　　├───┼──┼──┼──┼──┼───┼────┼──┼──┼────┤

（３）Ｑ２
　　┌───┬──┬──┬──┐
　　｜班番号｜乗員｜便名｜時間｜
　　┝━━━┿━━┿━━┿━━┥
　　｜Ｇ１　｜井上｜210 ｜５０｜
　　├───┼──┼──┼──┤
　　｜Ｇ１　｜鈴木｜210 ｜７０｜
　　├───┼──┼──┼──┤
　　｜Ｇ２　｜阿部｜320 ｜９０｜
　　├───┼──┼──┼──┤
　　｜Ｇ２　｜鈴木｜320 ｜４０｜
　　├───┼──┼──┼──┤

　　選択演算：班番号＝"Ｇ１" AND 乗務員 IS NOT NULL

　　Ｑ３
　　┌──┬────┐
　　｜肩書｜ 出発地 ｜
　　┝━━┿━━━━┥
　　｜Ｃ　｜　東京　｜
　　├──┼────┤
　　｜Ｐ　｜ロンドン｜
　　├──┼────┤
　　｜Ｃ　｜ロンドン｜
　　├──┼────┤

　　選択演算：出発地＝"東京" AND 便名 IS NOT NULL

問４
設問1
（１）ａ：40レコード
　　　ｂ：20MByte
　　※解説
　　ａ：
　　　売上実績１レコードのデータ容量：6+10+10+12+42＝80Byte
　　　ブロックサイズ：4096KByte
　　　ブロック毎に80Byteの管理情報
　　　レコード毎に20Byteの管理情報
　　　(4096Byte-80Byte)/(80Byte+20Byte)＝40.16＝40レコード
　　ｂ：
　　　部課数500×商品数400＝200000レコード
　　　200000レコード/(40レコード×4KByte)/1024＝19.53MByte


（２）ａ：最大80レコード　最小53レコード
　　　ｂ：10MByte
　　※解説
　　ａ：
　　　売上実績１レコードのキー容量：6+10+10＝26Byte
　　　複合索引時の管理情報：12+6*2＝24Byte
　　　ブロック毎に80Byteの管理情報
　　　(4096Byte-80Byte)/(26Byte+24Byte)＝80.32＝80レコード
　　　B*木構造では最小レコード数は最大レコード数の2/3なので80×2/3＝53.333＝53レコード
　　ｂ：
　　　最大数で見積もればよいから、データ部同様に
　　　200000レコード/(80レコード×4KByte)/1024＝9.76MByte


設問2
（１）処理１
　　※明らかにDISK01へのI/Oが多いので売上テーブルもしくは売上実績へのアクセスが問題

　　　表名　　　　　　　　　　　　　　売上　　　　　　売上明細　　　　売上実績
　　　主キー　　　　　　　　　　　　　10　　　　　　　12　　　　　　　26
　　　索引　　　　　　　　　　　　　　12　　　　　　　18　　　　　　　24
　　　ブロック内レコード数最大(最小)　182(121)　　　　133(88)　　　　 80(53)
　　　必要ブロック数最大(最小)　　　　11050(166661)　 75757(114942)　 32912(49999)
　　　最大レコード数　　　　　　　　　2000000　　　　 10000000　　　　2600000
　　　索引階層数最大(最小)　　　　　　4(3)　　　　　　4(4)　　　　　　4(4)
　　　
　　　・３処理において物理I/Oの発生はほぼ無いことを鑑み、索引をキャッシュする程度のメモリはそれなり
　　　　にあると思われる。→他のテーブルの必要ブロック数はより小さいのでメモリ不足によるI/Oは考えにくい。
　　　・２処理において担当者テの検索は正しく主キーで検索しているので、適切に索引が使用され物理I/Oは少な
　　　　いと考えられる。
　　　・４処理において売上実績の検索は正しく複合主キーで検索しているので、適切に索引が使用され物理I/Oは
　　　　少ないと考えられる。
　　　・１処理は年月日が検索条件に必要かつカーディナリティと非常に低いので索引が存在したとしても全表検索
　　　　となる。一見シーケンシャルにデータが格納されているように見えるが、データの削除を定期的に実施して
　　　　いるため、ソート済みとは限らない。従って2000000×2/3＝133万回の論理I/Oが発生する。売上テーブル全体
　　　　がキャッシュされているとは考えにくく、ここで物理I/Oが発生していると思われる。

（２）全表検索に耐えうるだけのメモリがなく物理Ｉ／Ｏが多発している
　　※仮に年月日に索引がないとすれば、全件検索になるためI/Oは多発するはず。

（３）各テーブルの索引の種類および使用状況のログの調査
　　　各テーブルのＩ／Ｏの論理値，物理値からキャッシュ率を調査


設問3
（１）売上テーブルに対するＩ／Ｏの競合が多発している

（２）ロックの粒度を小さくするか、テーブルパーティショニングを検討する