Rdbms qpstudy-okuno
- 1. 後悔しないための後悔しないための RDBMSRDBMS はじめの第一歩はじめの第一歩 奥野 幹也 @nippondanji mikiya (dot) okuno (at) gmail (dot) com @qpstudy 2013.07
- 7. 集合の性質 ● 重複がない ● NULL がない – 実際に存在する値のみ ● 要素間に順序がない – 例え数値でも。 米国 中華人民共和国 日本 オーストラリア スウェーデン カメルーン
- 8. リレーションの構成部品 ● 見出し（ヘッダ）＋本体（ボディ） ● 見出し（ヘッダ、 headding ） – 属性の集合 – 集合なので順序はない ● 本体（ボディ） – ヘッダと同じ属性を持つ組（タプル）の集合 ● 属性（アトリビュート） – 名前と型（タイプ） ● 属性値 – 属性で定義された型を持つ値 – ≒ 列（カラム） ● 組（タプル） – ヘッダで定義された属性に対応した属性値の集合 – ≒ 行
- 9. リレーションのイメージ 見出し 国名 / 文字列 国番号 / 整数 本体 地域 / 文字列 国名：日本 , 国番号： 81, 地域：アジア 国番号： 86, 国名：中華人民共和国 , 地域：アジア 国番号： 61, 国名：オーストラリア , 地域：オセアニア 国名：米国 , 国番号： 1, 地域：北米 地域：アフリカ , 国名：カメルーン , 国番号： 237 地域：欧州 , 国名：スウェーデン , 国番号： 46
- 10. SQL との対応 リレーショナルモデル SQL 関係（リレーション） テーブル 属性（アトリビュート） カラム 組（タプル） 行 対応する概念だが性質は異なる。
- 13. 例題 「２次元の平面上で 半径 2 の円に含まれ、 かつ x と y が共に 整数である座標の集合」 ※ 円上の点は含まれるものとする。
- 15. さらに次元が増えると？ ３次元空間上で半径 2 の球に含まれ、 かつ x 、 y 、 z が共に整数である座標の集合 ４次元空間上で（以下略）
- 16. リレーションのイメージ ● ２次元に見えるのは錯覚 – ２次元（紙）の上に描けばどんな絵でも２次元 – 表現されているものが２次元というわけではない ● n 個の属性を持つリレーションは n 次元空間上にプロット された点の集合ようなもの ● 数値の代わりにドメインが次元の軸になるイメージ。
- 17. データ型＝ドメイン ● 属性が取りうる値の有限集合 – 無限でないのはコンピュータで扱う値だから – 32 ビット整数なら 232 通り ● 同じ種類のデータ型であっても属性ごとに意味は異なる – 身長と体重の比較にどんな意味が？ – 演算結果には意味がある：例） BMI
- 18. リレーショナルモデルの特徴 ● ポインタがない – 扱いが容易 ● 実質的な標準的インターフェイス＝ SQL – 集合の操作 – 宣言型言語 – ただし SQL とリレーショナルモデルには齟齬があり！ ● リレーショナルモデルを端から無視した使い方は大抵 アンチパターン
- 19. リレーションの演算
- 20. リレーションの演算 ● 演算の入力も結果もリレーション – n 個のリレーションの演算の結果、 1 個のリレーションが 返る – 整数と整数の足し算の結果が整数なのと同じ ● 集合操作に基づく演算 – 和、差、直積、射影、制限、結合 etc
- 21. リレーションのイメージ（再掲） 見出し 国名 / 文字列 国番号 / 整数 本体 地域 / 文字列 国名：日本 , 国番号： 81, 地域：アジア 国番号： 86, 国名：中華人民共和国 , 地域：アジア 国番号： 61, 国名：オーストラリア , 地域：オセアニア 国名：米国 , 国番号： 1, 地域：北米 地域：アフリカ , 国名：カメルーン , 国番号： 237 地域：欧州 , 国名：スウェーデン , 国番号： 46
- 22. 制限
- 23. 制限
- 24. 射影
- 25. 射影
- 26. 属性名変更 国名 / 文字列 国番号 / 整数 大陸 / 文字列
- 27. 属性名変更 国名 / 文字列 国番号 / 整数 地域 / 文字列
- 28. 拡張 国名 / 文字列 人口 / 整数 面積 / Decimal (km2 )
- 29. 拡張 国名 / 文字列 人口 / 整数 面積 / Decimal (km2 ) 人口密度 / Decimal ( 人 /km2 )
- 30. 和（ UNION ）
- 31. 和（ UNION ）
- 32. 積（ INTERSECT ）
- 33. 積（ INTERSECT ）
- 34. 差（ DIFFERENCE ）
- 35. 差（ DIFFERENCE ）
- 36. 直積（ PRODUCT ） a b c d x y z
- 37. 直積（ PRODUCT ） xa b za bya b yc dxc d zc d
- 38. 結合（ JOIN ） b y c z a x y 2 z 3 x 1
- 39. 結合（ JOIN ） 1a x 2b y 3c z
- 40. 豆知識 ● 直積（ Product ）と積（ Intersect ）はいずれも結合 （ Join) の特殊なケース – 直積・・・共通する属性がひとつも存在しないケース – 積・・・すべての属性がまったく同じケース
- 42. SELECT の実態 SELECT select_list FROM table_reference WHERE where_condition
- 44. クイズ３！！ 次の SELECT は正しく実行されるか？ ○ × SELECT weight / POWER(height, 2) AS bmi FROM body_shapes WHERE bmi > 25
- 46. SELECT における評価の順序 SELECT FROM WHERE 3 射影 1 直積 2 制限
- 48. 関係変数（ Relvar ） ● リレーションは「値」 ● 値と変数 – C 言語の場合 – int a = 1; ● 変数 a に代入される値は変更できるが 1 という値その ものは不変 ● 関係変数 – リレーションを格納するための変数 – 変数は刻々と変化する
- 49. SQL における更新とは ● テーブルはリレーションと関係変数の両方を兼ている。 ● リレーショナルモデル的には、 SQL の更新は変数への代 入と操作のショートカットと考えると良い。 ● 例） INSERT – r = r UNION new_rel
- 50. リレーションの正規化
- 53. ミッション： ”データの 論理的”整合性を保つ 論理的な不整合とは？ name fighting_style age 範馬刃牙 総合格闘技 19 範馬勇次郎 総合格闘技 38 愚地独歩 空手 57 ビスケットオリバ 怪力 40 ビスケットオリバ 柔道 42 花山薫 素手喧嘩 20 烈海王 中国拳法 30 烈海王 ボクシング 30 どっちが 正しい？ 重複があると矛盾が起きる！！ （原因になる）
- 54. 用語 1 ● 命題 – ある物事について記述した文章で、その意味が正しいかどうか、つ まり真なのか偽なのかを問えるもののこと – 例）ポチは犬である ● 述語 – 命題の中の固有名詞をパラメータ化したもの – 例） x は犬である ● 命題関数 – 述語から意味を取り除いて関数化したもの。値を代入した場 合の評価結果は述語と同じ。 – 例） F(x) ● 閉世界仮説 – リレーションは事実の集合 ● 事実＝真となる命題 – →リレーションに含まれる組の属性値の組み合わせ 真 – →それ以外の属性値の組み合わせ すべて偽
- 60. 正規化理論 ● リレーションから重複を排除するためのデータベース設計 理論 – 関係理論の上に成り立っているが、関係理論とは別の理 論 ● 第一正規形（ 1NF ）〜第六正規形（ 6NF ） – 3NF と 4NF の間に BCNF というものがある。超重要。 – より高次の正規形のほうが重複が少ない（望ましい）状 態になる。 – ただし最終目標は 5NF
- 61. 用語 2 ● 候補キーとスーパーキー – あるリレーションにおいて、タプルの値を一意に決めるこ とができる属性の集合で、規約のもの（それ以上減らすこ とができないもの）を候補キーという – 候補キーのスーパーセット、つまり余分な属性を含んだも のをスーパーキーという ● すべての属性を含む集合は常にスーパーキー ● 関数従属性（ Functional Dependency - FD ） – あるリレーション R の見出しの 2 つの部分集合を A 、 B とする。 R の要素の全てのタプルにおいて、 A の値が同 じならば B の値も同じである場合かつその場合だけに限 り、 B は A に関数従属すると言い、 A → B と記述する。 – →スーパーキー 見出しの任意の部分集合 ● 自明な FD
- 62. 用語 2 解説 name fighting_style age 範馬刃牙 総合格闘技 19 範馬勇次郎 総合格闘技 38 愚地独歩 空手 57 ビスケットオリバ 怪力 40 ビスケットオリバ 柔道 40 花山薫 素手喧嘩 20 烈海王 中国拳法 30 烈海王 ボクシング 30 FD name の値が同じなら age の値は常に同じ 候補キー 図に描くときは アンダーラインをひく
- 64. 繰り返しグループ name fighting_style 範馬刃牙 総合格闘技 範馬勇次郎 総合格闘技 愚地独歩 空手 ビスケットオリバ 怪力 花山薫 素手喧嘩 海王烈 中国拳法 name fighting_style1 fighting_style2 範馬刃牙 総合格闘技 NULL 範馬勇次郎 総合格闘技 NULL 愚地独歩 空手 NULL ビスケットオリバ 怪力 柔道 花山薫 素手喧嘩 NULL 海王烈 中国拳法 ボクシング 属性追加 同じ性質の ものじゃね？
- 65. アトミックな属性とは？ name fighting_style 範馬刃牙 総合格闘技 範馬勇次郎 総合格闘技 愚地独歩 空手 ビスケットオリバ 怪力 柔道 花山薫 素手喧嘩 海王烈 中国拳法 ボクシング ２つ入ってるので 分解可能 それ以上分解できないような属性 ※ 属性の中に繰り返しグループがある。
- 66. 属性のデータ型 ● アトミック – ≠ スカラ – 文字列はアトミックか？ ● substring で分解可能 ● データ型＝ドメイン – ドメインという有限集合に含まれる要素の「ひとつ」 – ひとつの値として扱えるものならどんなデータ型でも良い ● 配列、ベクトルやリレーションを属性にすることも可 – SQL にはないデータ型でもリレーショナルモデル的には アリ ● SQL にはないデータ型を扱いたい場合には工夫が必 要 – ポインタ、 NULL は不可
- 67. 1NF の例 name fighting_style 範馬刃牙 総合格闘技 範馬勇次郎 総合格闘技 愚地独歩 空手 ビスケットオリバ 怪力 ビスケットオリバ 柔道 花山薫 素手喧嘩 海王烈 中国拳法 海王烈 ボクシング ●繰り返しグループなし ●重複なし ●NULL なし
- 68. 2NF 候補キーの真部分集合（それ自身は含まないもの） から非キー属性への FD を取り除いた状態。 タプル 候補キー 非キー属性 ・・・ FD
- 69. 2NF でないリレーションの例 name fighting_style age 範馬刃牙 総合格闘技 19 範馬勇次郎 総合格闘技 38 愚地独歩 空手 57 ビスケットオリバ 怪力 40 ビスケットオリバ 柔道 40 花山薫 素手喧嘩 20 烈海王 中国拳法 30 烈海王 ボクシング 30 FD name の値が同じなら age の値は常に同じ
- 70. FD を解消する ● 無損失分解する – 必要な操作は射影 – {name,fighting_style,age} ● FD: name → age ● FD を含む属性の射影 +FD の非キー属性（この場合 は age ）を取り除いた属性の射影 ● {name,age},{name,fighting_style} ● 逆説 – FD があるかどうか疑わしいときには射影によって無損失 分解ができるかどうかを試してみるのもアリ。 – 射影 ＝ SELECT DISTINCT
- 71. 2NF の例 name fighting_style 範馬刃牙 総合格闘技 範馬勇次郎 総合格闘技 愚地独歩 空手 ビスケットオリバ 怪力 ビスケットオリバ 柔道 花山薫 素手喧嘩 烈海王 中国拳法 烈海王 ボクシング name age 範馬刃牙 19 範馬勇次郎 38 愚地独歩 57 ビスケットオリバ 40 花山薫 20 烈海王 30
- 73. 3NF 非キー属性同士の FD を解消した状態。 タプル 候補キー 非キー属性 ・・・ FD
- 74. 宿題：次のリレーションの FD を解消せよ name fighting_style groundwork 範馬刃牙 総合格闘技 Yes 範馬勇次郎 総合格闘技 Yes 愚地独歩 空手 No ビスケットオリバ 怪力 No 花山薫 素手喧嘩 Yes 烈海王 中国拳法 No
- 75. BCNF ボイス・コッド正規形の略。 すべての自明でない FD が取り除かれた状態。 残る FD は、非キー属性から 候補キーの真部分集合への FD 。 タプル 候補キー 非キー属性 ・・・ FD
- 76. 3NF であって BCNF でない例 name fighting_style school 松尾象山 空手 北辰館 姫川勉 空手 北辰館 丹波文七 柔術 竹宮流 丹波文七 空手 丹波流 長田弘 プロレス FAW 長田弘 柔術 竹宮流 藤巻十三 柔術 竹宮流 FD: school → fighting_style
- 77. 用語 3 ● 結合従属性（ Join Dependencies - JD ） – BCNF で FD の話は終わり – 4NF 〜 6NF は JD の話 – Join すると元に戻るような無損失分解 – A,B,...,C をリレーション R の見出しの部分集合であると する。もし A,B,...,C に対応するリレーションを結合した結 果と R が同じ場合かつその場合に限り、 R は以下の JD を満たすという。 ☆{A,B,...,C}
- 78. JD の例 A B A1 B1 A1 B2 A2 B1 A2 B2 A3 B1 A3 B2 B B1 B2 A A1 A2 A3
- 79. 4NF 一般的には多値従属性（ MVD ）を取り除いた ものという解説がなされているが、 MVD は JD の特殊なパターン。
- 80. 用語 4 ● MVD の JD を用いた定義 – A,B,C をリレーション R の見出しの部分集合であるとす る。 A,B,C が以下の JD を満たす場合かつその場合に限 り、 B および C は A に多値従属するという。 ☆{AB,AC} ● MVD の記述法 – A →→ B – A →→ C
- 81. BCNF であって 4NF でない例 name fighting_style match 松尾象山 空手 試合 松尾象山 空手 道場破り 松尾象山 空手 喧嘩 丹波文七 空手 試合 丹波文七 空手 喧嘩 丹波文七 空手 路上 丹波文七 柔術 試合 丹波文七 柔術 喧嘩 丹波文七 柔術 路上 長田弘 プロレス 試合 長田弘 プロレス 道場破り 長田弘 柔術 試合 長田弘 柔術 道場破り
- 82. 4NF 〜 6NF のヒント ● 非キー属性がある BCNF に自明でない JD はない – 何故ならば、候補キーがバラバラになるような射影をとる と FD が消失してしまう。 ● FD: →候補キー 非キー属性 – 自明な JD とは、複数の非キー属性があるリレーションに おいて、それぞれ候補キーを含んだ複数の異なるリレー ションに無損失分解できるような JD 。 ● 非キー属性のある BCNF は自動的に 5NF または 6NF に なる。
- 84. 宿題：接続の罠について調べよ ● ヒント – 3 つ以上のリレーションに分解可能な JD で起きる問題 – 接続の罠が本当は罠じゃない理由は JD
- 89. 用語 5 ● 直交したリレーションとは、互いに重複したタプルを含まな いものを指す。 – 見出しが同じ型でもタプルの重複がなければ OK
- 91. 直交性を確認する ● 6NF まで分解して比較する ● 重複があればデータベース設計を見なおそう – リレーションの統合と再編
- 92. リレーショナルモデルの限界
- 93. クイズ 7 ！！ データの保全と検索に関する すべての課題は RDBMS で解決できる。 ○ ×
- 95. リレーショナルモデルでは 扱いが難しいテーマ ● 履歴データ ● グラフの探索 ● ツリーの探索 ● 行列 ● 全文検索 ● 正規表現 ● ソート ● 集計 ● 空間データ
- 96. リレーショナルモデルの外側の世界 ● ≒リレーショナルモデル SQL – SQL なら何とかできる部分がある ● 非常に容易なものもある ● 例） GROUP BY, ORDER BY – 扱う対象によってはストアドプロシージャが必要 ● SELECT を書くときの鉄則 – リレーショナルモデルで解決できる部分はリレーショナル モデルを適用すべし。 – リレーショナルモデルにない操作を行う場合は細心の注 意を払うべし。 – リレーショナルモデルに準ずる演算を先にすべし。 ● オプティマイザが一番活躍できるのはリレーショナルモ デルの領域
- 97. おすすめの書籍 ● SQL and Relational Theory – データベースの実践講義は内容が少ないし古いのでお すすめはしない。 ● The Art of SQL ● プログラマのための SQL ● SQL アンチパターン ● WEB+DB PRESS – Vol.68 〜