Downloaded 826 times
![正しいデータ型を使う〜 配列の操作の場合 〜a = Array.new# 要素の挿入a.push(' 要素 ')# N 番目の要素a[N]a = ''# 要素の挿入a = a << (a.size == 0 ? ' 要素 ' : ', 要素 '# N 番目の要素n = 0s = ''a.each_char do |c|if c == ','return s if n == Nn += 1s = ''elses << cendendreturn s if n == N](/image.pl?url=https%3a%2f%2fimage.slidesharecdn.com%2fdb-engineer-study-anim-131128093024-phpapp02%2f75%2fslide-6-2048.jpg&f=jpg&w=240)
![正しいデータ型を使う〜 配列の操作の場合 〜a = Array.new# 要素の挿入a.push(' 要素 ')# N 番目の要素a[N]a = ''# 要素の挿入a = a << (a.size == 0 ? ' 要素 ' : ', 要素 '# N 番目の要素n = 0s = ''a.each_char do |c|if c == ','return s if n == Nn += 1s = ''elses << cendendreturn s if n == N](/image.pl?url=https%3a%2f%2fimage.slidesharecdn.com%2fdb-engineer-study-anim-131128093024-phpapp02%2f75%2fslide-7-2048.jpg&f=jpg&w=240)
Uploaded byMikiya Okuno
115,415 views
データベース設計徹底指南
DBエンジニアのための技術勉強会（第3回）で使用した資料です。主にリレーショナルモデルと正規化について解説しています。リレーショナルモデルの限界について正しく認識してこそ、リレーショナルモデルを理解したと言えると思います。
More Related Content
What's hot
Viewers also liked
Similar to データベース設計徹底指南
More from Mikiya Okuno
データベース設計徹底指南
- 1.データベース設計徹底指南！！@ 第３回 DBエンジニアのための技術勉強会奥野 幹也Twitter: @nippondanjimikiya (dot) okuno (at) gmail (dot) com
- 2.
- 3.
- 4.
- 5.正しいデータ型を使う〜 カラムの場合 〜●整数–●実数–●NUMBER,DECIMAL文字列–●FLOAT, DOUBLE固定小数点–●INT, BIGINTVARCHAR, CHARバイナリ–BLOB
- 6.正しいデータ型を使う〜 配列の操作の場合 〜a= Array.new# 要素の挿入a.push(' 要素 ')# N 番目の要素a[N]a = ''# 要素の挿入a = a << (a.size == 0 ? ' 要素 ' : ', 要素 '# N 番目の要素n = 0s = ''a.each_char do |c|if c == ','return s if n == Nn += 1s = ''elses << cendendreturn s if n == N
- 7.正しいデータ型を使う〜 配列の操作の場合 〜a= Array.new# 要素の挿入a.push(' 要素 ')# N 番目の要素a[N]a = ''# 要素の挿入a = a << (a.size == 0 ? ' 要素 ' : ', 要素 '# N 番目の要素n = 0s = ''a.each_char do |c|if c == ','return s if n == Nn += 1s = ''elses << cendendreturn s if n == N
- 8.正しいデータ型を使う〜 テーブルの場合 〜●演算のためのデータ型–テーブル●●カラムのデータ型だけではない！！演算–クエリ（SELECT, INSERT, UPDATE, DELETE... ）テーブルを正しく定義しないとまともなクエリは書けない！！
- 9.正しくない DB 設計のままクエリを書くと・・・●●●●遅いSQL文が無駄に長いJOIN やサブクエリ、 UNION などが異常に多い検索条件が複雑怪奇––●●OR が多数出現するなどLIKE 句による中間一致難解であるロジックが間違っている
- 10.
- 11.技術的負債がたまる原因●データモデルに対する無理解–リレーショナルモデルを知らない●●–結果、セオリーを無視したデータベース設計に●●テーブルをただのデータの入れ物として使う文化正規化をしない→ クエリはスパゲティにリレーショナルモデルを知らなくても RDBMSは便利––インデックスを用いた高速なアクセストランザクションによる処理の簡素化
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.リレーションの構成部品●●リレーション＝見出し（ヘッダ）＋本体（ボディ）見出し（ヘッダ、 headding ）––●属性（アトリビュート）–●–属性で定義された型を持つ値≒列（カラム）組（タプル）––●名前と型（タイプ）属性値–●属性の集合集合なので順序はない見出しに対応した属性値の集合≒ 行（ロー）本体（ボディ）–組（タプル）の集合
- 20.リレーションのイメージ見出し国名 / 文字列国番号/ 整数地域 / 文字列本体国番号： 86,国名：中華人民共和国 ,地域：アジア国番号： 61,国名：オーストラリア ,地域：オセアニア国名：日本 ,国番号： 81,地域：アジア国名：米国 ,国番号： 1,地域：北米地域：アフリカ ,国名：カメルーン ,国番号： 237地域：欧州 ,国名：スウェーデン ,国番号： 46
- 21.
- 22.
- 23.リレーションの実体●●●n 個のアトリビュートを持つリレーションは、 n次元空間にプロットされた点（のようなもの）の集合タプル ＝ 点２次元に見えるのは縦横の軸がある表として表現するから––紙に描かれた絵は２次元になる絵が表すものが２次元だとは限らない●風景や人物などはすべて３次元
- 24.データ型＝ドメイン●属性が取りうる値の有限集合–––●無限でないのはコンピュータで扱う値だから32 ビット整数なら 232通りデータサイズが増えれば表現できる要素数は増えるけど、絶対に無限にはならないドメインの要素の性質–要素はコンピュータで表現できるものなら何でも可●–ベクトル、行列、配列、リレーションそのものなどただしポインタと NULL は NG
- 25.
- 26.リレーションの演算●演算の入力も結果もリレーション––●n 個のリレーションの演算の結果、 1個のリレーションが返る整数と整数の足し算の結果が整数なのと同じ集合操作に基づく演算–和、差、直積、射影、制限、結合 etc
- 27.リレーションのイメージ（再掲）見出し国名 / 文字列国番号/ 整数地域 / 文字列本体国番号： 86,国名：中華人民共和国 ,地域：アジア国番号： 61,国名：オーストラリア ,地域：オセアニア国名：日本 ,国番号： 81,地域：アジア国名：米国 ,国番号： 1,地域：北米地域：アフリカ ,国名：カメルーン ,国番号： 237地域：欧州 ,国名：スウェーデン ,国番号： 46
- 28.
- 29.
- 30.
- 31.
- 32.属性名変更（ RENAME ）国名/ 文字列国番号 / 整数大陸 / 文字列
- 33.属性名変更（ RENAME ）国名/ 文字列国番号 / 整数地域 / 文字列
- 34.拡張（ EXTEND ）国名/ 文字列人口 / 整数面積 /Decimal (km2)
- 35.拡張（ EXTEND ）国名/ 文字列人口 / 整数人口密度 /Decimal ( 人 /km2)面積 /Decimal (km2)
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.豆知識●直積（ Product ）と積（Intersect ）はいずれも結合（ Join) の特殊なケース––●直積・・・共通する属性がひとつも存在しないケース積・・・すべての属性がまったく同じケースリレーショナルモデルに存在する Join は Inner Join だけ
- 47.Outer Join の意味●OuterJoin (LEFT JOIN または RIGHT JOIN ）はプリミティブな演算ではない。–●他の演算で置き換え可能Outer Join は UNION 相当–(Inner Join) ∪ ( 駆動表にマッチしないものの集合 )
- 48.
- 49.
- 50.SELECT の実態＝ 3つの集合演算SELECT射影FROM直積WHERE制限
- 51.クイズ 3次の SELECTは正しく実行されるか？SELECTweight / POWER(height, 2) AS bmiFROMbody_shapesWHEREbmi > 25○ ×
- 52.SELECT の評価順序SELECT3 射影FROM1直積WHERE2 制限
- 53.その他の操作●和（ UNION ）–●SELECT… UNION DISTINCT SELECT …差（ DIFFERENCE ）––SELECT … MINUS SELECT …SELECT … WHERE NOT EXISTS (SELECT …) …
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.正規化理論●リレーションから重複を排除するためのデータベース設計理論–●重複は矛盾の原因になる第一正規形（ 1NF ）〜第六正規形（6NF ）–––より高次の正規形のほうが重複が少ない（望ましい）状態になる。3NF と 4NF の間に BCNF というものがある。超重要。ただし最終目標は 5NF
- 62.用語 1●命題––●述語––●命題の中の固有名詞をパラメータ化したもの例） xは犬である命題関数– 述語から意味を取り除いて関数化したもの。値を代入した場合の評価結果は述語と同じ。–●ある物事について記述した文章で、その意味が正しいかどうか、つまり真なのか偽なのかを問えるもののこと例）ポチは犬である例） F(x)閉世界仮説–リレーションは事実の集合●––事実＝真となる命題リレーションに含まれる組の属性値を代入 → 真それ以外の属性値を代入 → すべて偽
- 63.
- 64.
- 65.
- 66.
- 67.NG ！！こんな設計にはご用心●●主キーの”一部”に意味を持たせる例：基礎年金番号–1234-567890事務所の ID●個人のID問題点––アトミックでない値の一部に依存した処理●SELECT … WHERE ID LIKE '1234-%' AND …
- 68.1NF の例名前格闘スタイル範馬刃牙総合格闘技範馬勇次郎総合格闘技愚地独歩空手ビスケットオリバ 怪力ビスケットオリバ柔道花山薫素手喧嘩海王烈中国拳法海王烈ボクシング繰り返しグループなし●重複なし●NULL なし●
- 69.用語 2●候補キーとスーパーキー––あるリレーションにおいて、タプルの値を一意に決めることができる属性の集合で、規約のもの（それ以上要素を減らすことができないもの）を候補キーという候補キーのスーパーセット、つまり余分な属性を含んだものをスーパーキーという●●すべての属性を含む集合は常にスーパーキー関数従属性（ FunctionalDependency - FD ）––あるリレーション R の見出しの 2 つの部分集合を A 、 Bとする。 R の要素の全てのタプルにおいて、 A の値が同じならば B の値も同じである場合かつその場合だけに限り、 B は A に関数従属すると言い、 A → B と記述する。スーパーキー → 任意の属性の集合●自明な FD
- 70.
- 71.
- 72.FD を解消する●無損失分解する–必要な操作は射影●–SELECT DISTINCT{名前 , 格闘スタイル , 年齢 }●●●FD: 名前 → 年齢FD を含む属性の射影 +FD の非キー属性（この場合は年齢）を取り除いた属性の射影{ 名前 , 年齢 },{ 名前 , 格闘スタイル }
- 73.
- 74.
- 75.テーブルが増えても問題ない !!テーブルを減らそうと誤った DB設計になるほうが問題
- 76.
- 77.2NF であって 3NFでない例名前格闘スタイル寝技範馬刃牙総合格闘技Yes範馬勇次郎総合格闘技Yes愚地独歩空手Noビスケットオリバ怪力No花山薫素手喧嘩Yes烈海王中国拳法No
- 78.BCNFボイス・コッド正規形の略。すべての自明でない FD が取り除かれた状態。残るFD は、非キー属性から候補キーの真部分集合への FD 。候補キー非キー属性タプル・・・FD
- 79.3NF であって BCNFでない例名前格闘スタイル流派松尾象山空手北辰館姫川勉空手北辰館丹波文七柔術竹宮流丹波文七空手丹波流長田弘プロレスFAW長田弘柔術竹宮流藤巻十三柔術竹宮流
- 80.
- 81.候補キー内部には FD がない●もし仮に候補キーにFD があると・・・–––{A,B} が候補キーA→BB の値は A によって決まる●規約ではない！
- 82.用語 3●結合従属性（ JoinDependencies - JD ）––●Join すると元に戻るような無損失分解A,B,...,C をリレーション R の見出しの部分集合であるとする。もし A,B,...,C に対応するリレーションを結合した結果と R が同じ場合かつその場合に限り、 R は以下の JDを満たすという。☆{A,B,...,C}自明な JD–複数の非キー属性があるリレーションにおいて、それぞれ候補キーを含んだ複数の異なるリレーションに無損失分解できるような JD 。
- 83.
- 84.4NF/5NF のヒント●非キー属性がある BCNFに自明でない JD はない–何故ならば、候補キーがバラバラになるような射影をとると FD が消失してしまう。●●–●FD: 候補キー → 非キー属性無損失ができない！！非キー属性のある BCNF は自動的に 5NF になる。4NF/5NF––非キー属性がないリレーションのみが対象自明でない JD がある場合に無損失分解
- 85.自明でない JD の本質●自明でないJD のあるリレーションとは、複数の「非キー属性がないリレーション」を JOIN したリレーション––= 「見出し全体が候補キーであるようなリレーション」JOIN する前のリレーション同士は対等な関係性●●●FD のような方向性はない発見は難しい非キー属性がない場合だけ考えれば良い
- 86.一般的な 4NF の解説一般的には多値従属性（MVD ）を取り除いたものという解説がなされているが、MVD は JD の特殊なパターン。
- 87.4NF共通の属性を含む 2 つのリレーションに無損失分解可能なJD を解消した状態☆{AB,AC}
- 88.BCNF であって 4NFでない例名前格闘スタイル戦歴松尾象山空手試合松尾象山空手道場破り松尾象山空手喧嘩丹波文七空手試合丹波文七空手喧嘩丹波文七空手路上丹波文七柔術試合丹波文七柔術喧嘩丹波文七柔術路上長田弘プロレス試合長田弘プロレス道場破り長田弘柔術試合長田弘柔術道場破り☆{{ 名前 , 格闘スタイル },{ 名前 , 戦歴 }}
- 89.5NF全ての自明でない JD が取り除かれた状態。3つ以上のリレーションに無損失分解可能なJD を解消した状態。最終目標地点。
- 90.
- 91.4NF であって 5NFでない例nameartmatch丹波文七虎王FAW マッチ丹波文七ハイキックFAW マッチ丹波文七ストレートFAW マッチ丹波文七ストレート道場破り丹波文七ハイキック道場破り長田弘バックドロップ北心館トーナメント長田弘虎王北心館トーナメント長田弘ストレート北心館トーナメント長田弘ボディスラム道場破り長田弘脇がため道場破り長田弘ストレート道場破り
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.リレーショナルモデルの外側の世界●現実世界––––●リレーショナルモデルの範疇とそれ以外が入り乱れている。境界線を見極めることが重要。リレーショナルモデルで解決できる部分はリレーショナルモデルを適用すべし！！リレーショナルモデル以外の領域は創意工夫が必要リレーショナルモデル≒ SQL––SQL はリレーショナルモデル以外の分野も扱えるSQLなら何とかできるものがある●●非常に容易なものもある例） GROUP BY, ORDER BY
- 103.
- 104.
- 105.
- 106.
- 107.グラフを表現した例●グラフ ＝ ノードの集合＋ エッジの集合NodesEdgesNodeNode1Node2 Weightaab3bae8cbc3dbd8be7cd4cf8de5df3ef9ef
- 108.グラフに対するクエリの問題点●グラフ特有の問題を表現できない–––●何度 JOIN すれば良いのか事前に分からない––●連結グラフかどうか閉路があるかどうか最短距離はどれかグラフを辿ることで得た値によって決まるJOINを何度するかで、 SQL の構文が異なる解決策––ストアドプロシージャグラフデータベース
- 109.ツリーとは●次の条件を満たすグラフのこと––––閉路がなく連結しているすべてのエッジはブリッジである任意の 2 つのノードを結ぶパスはただひとつだけである隣接していないどの2 つのノードを結んでも閉路ができるebicgafhd
- 110.
- 111.
- 112.隣接リストモデル●一般グラフと同じように隣接リストを使って表現する––●子ノードが親ノードの ID を持つ根ノードの親はNULL再帰クエリあるいはストアドプロシージャnode_idQ: f の深さはいくつか？WITH RECURSIVE r AS (SELECT 1 AS level, node_id, parent_idFROM TreeWHERE parent_id IS NULLUNION ALLSELECT r.level + 1, t.node_id, t.parent_idFROM r JOIN Tree tON r.node_id = t.parent_id)SELECT * FROM r WHERE node_id = 'f';parent_idaNULLbacadaebfbgchg
- 113.パス列挙モデル●根ノードからのフルパスで経路を表現するモデル–––LIKE 句で検索深さを求めるのは超簡単非正規化されたデータそのものnode_idpathQ: bの子孫をすべて挙げよa/aSELECT * FROM Tree WHERE pathLIKE CONCAT((SELECT path FROM TreeWHERE node_id='b'), '%');b/a/bc/a/cd/a/de/a/b/ef/a/b/fg/a/c/gh/a/c/g/h
- 114.入れ子集合モデル●●ノードごとに lft 、rgt という 2 つの数値を使って、ノードの包含関係を表現するモデル親ノードは全ての子ノードを含んでいるabecgfhd
- 115.入れ子集合モデルlft と rgtによる表現10321465ef97hg8b111213 14c15d16aQ: b の子孫をすべて挙げよSELECT t1.node_id FROM Tree t1 JOIN Tree t2WHERE t2.node_id = 'b'AND t1.lft BETWEEN t2.lft AND t2.rgtnode_idlftrgta116b27c813d1415e34f56g912h1011
- 116.クロージャーテーブルモデル●祖先、子孫の関係にあるノードをすべてリストアップ–テーブルサイズは大更新は容易Q: c の子孫をすべて挙げよSELECTdescendant FROM TreeWHERE ancestor = 'c'ancestordescendantaba–cadaeafagahbebfcgchgh
- 117.
- 118.
- 119.履歴データの問題点 1時間軸との直交性●リレーションとはある時点での事実の集合––●変数の中身と同じように、刻々と関係変数（ Relvar）の内容は刻々と変化するある時点において事実はひとつ履歴データは時間軸と直交していない！–時刻が変わればクエリの結果が変わる（例）懸垂マシンの価格を調べるクエリSELECT priceFROM price_listWHERE item = ' 懸垂マシン 'AND NOW() BETWEEN start_dateAND end_date
- 120.
- 121.
- 122.履歴データ対策 1テーブルを分割する●意味の異なるリレーションに分ける––●意味の違いが解消日付はあるものの、クエリの結果には影響を与えない元のテーブルは UNIONで。過去の価格現在の価格itempricestart_dateitempricestart_date100002010-01-01グリッパー40002012-04-01グリッパー50002013-04-01懸垂マシン180002010-01-01懸垂マシン200002012-01-01ダンベルセット
- 123.
- 124.
- 125.対策 2 の問題点●重複したデータがある––直交していない更新異常（矛盾）に注意●●2つのテーブルで同じアイテム、同じ日付なのに価格が異なるどちらか一方だけにしか該当する行がない
- 126.履歴データ対策 3擬似 (pseudo)キーの利用●外部キーは利用したいけどデータの重複は避けたいキーマスター現在の価格price_idprice_iditem11 ダンベルセット23pricestart_date100002010-01-013 グリッパー50002013-04-015 懸垂マシン200002012-01-0145過去の価格price_iditempricestart_date2 グリッパー40002012-04-014 懸垂マシン180002010-01-01
- 127.
- 128.NG!! こんな設計にご用心●フラグを立てたら？–––end_date →validフラグは自動的に更新されないフラグだらけになってしまうitempricestart_dateend_datevalid100002010-01-019999-12-31 yグリッパー40002012-04-012013-03-31 nグリッパー50002013-04-019999-12-31 y懸垂マシン180002010-01-012011-12-31 n懸垂マシン200002012-01-012013-12-31 y懸垂マシン220002014-01-019999-12-31 yダンベルセット
- 129.
- 130.
- 131.おすすめの書籍●SQL and RelationalTheory–●●●●データベースの実践講義は内容が少ないし古いのでおすすめはしない。The Art of SQLプログラマのための SQLSQL アンチパターンWEB+DB PRESS–Vol.68 〜
- 132.