âèâîü[âVâçâiâïâfü[â^âxü[âX

(1)

1

データベース

第 3 回リレーショナルデータベース

鈴木幸市

(2)

今回の内容

 リレーショナルデータベースの沿革

 _{リレーション}

 _関係演算

 _キー

 _制約

 _NULL _の意味

 _制約

(3)

3

リレーショナルデータベースの沿革

 ₁₉₇₀ 年の Codd の提案

 ₁₉₈₀ 年代の SQL 言語の開発、先進製品の開発

 ₁₉₉₀ 年代の SQL 言語標準化と普及

 今ではデータベースの代名詞

(4)

リレーションとは

 2 次元の「表」のこと

学籍番号姓名学部学科入学年度

00001 _秋吉 _久美子 _環境情報 _メディア 2004

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

00004 _泉 _ピン子 _環境情報 _メディア 2005

00005 _石原 _良純 _環境情報 _情報 2005

00006 _伊東 _四朗 _環境情報 _メディア 2006

00007 _うつみ _宮土理 _環境情報 _情報 2006

学籍簿

名前がつい

ている

「タプル」あるいは

「行」、「レコード」

名前はつかない

「カラム」「属性」

これにも名前がついて

いる

(5)

リレーションの構造の表し方

 「見出し」だけを使ってリレーションの構造を表すことがある。

 見出しの表現方法はいろいろだが、直感的に理解できるもの

5

学籍番号姓名学部学科入学年度

学籍番号姓

名学部

学科入学年度

(6)

留意事項 (1)

 格納できるのはあらかじめ定義した「単項目」のデータに限る

 配列やリレーションをリレーションの中に入れることはできない

 一部データベースではこの部分を拡張しているものもある

 _{通常は数値や文字列}

 それ以外に、図形やビットマップなども「単項目」に入れること

ができる

 タプルの順序には意味はない。

 _SQL 文では特に指定しない場合は、同じ SQL 文を 2 回実行して

も違う順序でタプルが表示される場合もある。

学籍番号姓名学部学科入学年度

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00001 _秋吉 _久美子 _環境情報 _メディア 2004

00002 _赤坂 _泰彦 _環境情報 _情報 2004

_同じリ

レーション

(7)

7

留意事項 (2)

 _{値だけで照合される}

 すべてのカラムの値が同じ 2 つのタプルを区別する手段はな

い

 実際には、システムカラムなどをつけることで区別する手段

を用意しているデータベースが多い。

00001 _秋吉 _久美子 _環境情報 _メディア 2004 00001 _秋吉 _久美子 _環境情報 _メディア 2004

区別する

手段なし

(8)

リレーションに対する演算 ( 関係演算 )

 リレーションにある演算を施すと結果もリレーション

 そこで、リレーションを加工しながらいろいろな結果

を得ることができる

 リレーショナルデータベースのもっとも強力な点

 _SQL 言語もこの性質を基本にしている

 簡単な仕様で強力な機能を持っている

 _{和、差、共通部}

分

 _選択

 _射影

 _直積

 _結合

(9)

9

和 (Union)

 同じカラムを持つリレーションのタプルを合わせて一つのリレーションにする

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

00004 _泉 _ピン子 _環境情報 _メディア 2005

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

+

R

S

R ∪ S

演算記号： Ullman 他、「 Database Systems, the complete book」

(10)

差 (Difference)

 2 つのリレーション共通部分を取り除いて一つのリレーションにする

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

ー

R

S

R － S

(11)

11

差 (Difference) の注意

 共通部分でないタプルは「引けない」

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

00005 _石原 _良純 _環境情報 _情報 2005

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

ー

R

S

R － S

(12)

共通部分 (Intersection)

 2 つのリレーションの共通部分

00003 _飯島 _愛 _環境情報 _メディア 2005

00005 _石原 _良純 _環境情報 _情報 2005

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

∩

R

S

R ∩ S

(13)

13

射影 (Projection)

 あるリレーションの特定のカラムだけを抜き出す

学籍番号姓名

00001 _秋吉 _久美子

00002 _赤坂 _泰彦

00003 _飯島 _愛

00004 _泉 _ピン子

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

R

π _{学籍番号 ,}

_姓

_,

_名

(R)

(14)

選択 (Selection)

 あるリレーションから条件を満たすタプルを選ぶ

00003 _飯島 _愛 _環境情報 _メディア 2005

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

00005 _石原 _良純 _環境情報 _情報 2005

00006 _伊東 _四朗 _環境情報 _メディア 2006

00007 _うつみ _宮土理 _環境情報 _情報 2006

R

σ _{学籍番号≧}

_{3 AND}

_学籍番号

_<5

^(R)

(15)

15

直積 (積、 Cartesian Product)

 2 つのリレーションの全部のタプルの組み合わせ

A R.B S.B C D

1 2 2 5 6

1 2 4 7 9

1 2 9 10 11

3 4 2 5 6

3 4 4 7 9

3 4 9 10 11

B C D

2 5 6

4 7 9

9 10 11

A B

1 2

3 4 ^×

R ^S

R × S _ _{複数のテーブルを使っ}

た処理の基本

 _{しかし、結果のタプル}

数が大きくなる

 _{実際には、直積を使わ}

ず、次の結合を使うこと

が多い

(16)

　結合 (Join)

 結合にはいくつかの種類がある

 自然結合 (natural-join)

 等結合 (equi-join)

 外結合 (outer-join)

 右外結合 (right outer-join)

 左外結合 (left outer-join)

 _{Theta Join}

 _{上記の一般的な形}

 _{Semi Join}

(17)

17

自然結合 (Natural Join)

 同じ名前のカラムの値が同じタプルをつなげる

A B C D

1 2 5 6

3 4 7 9

B C D

2 5 6

4 7 9

9 10 11

A B

1 2

3 4

R ^S

R S ^ _増えない ^{直積に比べてタプルが}

 _{カラムの名前で結合の}

方法が決まるのは使いに

くい

 _{もっと柔軟にできない}

の

→ 等結合

(18)

等結合 (Equi Join)

 違う名前のカラムの値が同じタプルをつなげる

A B D E

1 2 5 6

3 4 7 9

C D E

2 5 6

4 7 9

9 10 11

A B

1 2

3 4

R ^S

π

^A,B,D,E

(R

S)

 _{任意のカラム同士で結}

合できる

B = C

(19)

19

その他の結合演算

 外結合 (outer join)

 準結合 (semijoin)

(20)

集約演算

 _{代表的な集約演算}

 数え上げ (count)

 _{合計 (sum)}

 異なる値だけを探す (distinct)

 その他いろいろ考えられる

 上記の演算を、別のカラムの値を使って集

計

 _{世代別集計}

 _{地域別集計}

 _等々

(21)

21

リレーショナルデータベースが強力な理

 由テーブルにいろいろな操作をした結果も

テーブル

 演算を繰り返し実行するのが簡単

 考え方が単純で複雑なことが実現できる

 演算を指定すればいいので実行方法はシス

テム任せ

 データの状態に合わせて最適な演算方法を実

行時に選択できる

 途中でデータを格納するファイル編成やイン

デックスを変更してもよい

(22)

テーブルのキー

 テーブルのカラムを「キー」と呼ぶことがあ

る

 タプル（レコード）を識別するためのカラムのこ

と

 _{識別にもいろいろ}

 あるテーブルの中で所望のタプルを見つけるために

使うもの →主キー

 たとえば、学籍番号など

 他のテーブルのタプルを参照するために使うもの

→外部キー

 学籍簿から学部・学科の情報を参照するための学部記

号・番号など

(23)

23

テーブルのキー

 テーブルの 2 種類のキー

 主キー (primary key)

 テーブルの中のタプルを一意に識別するためのキー

 同じテーブルの中で同じ主キー値を持つタプルはな

い

 _{テーブルに一つ}

 外部キー (external key)

 他のテーブルと結合するために使うキー

 一つのテーブルに複数あってもよい

 同じキー値を持つタプルは複数でもよい

 これらのキーは必須ではない

 主キーのないテーブル、外部キーのないテーブルもある

 キーにはインデックスがあるとは限らない

 それでも指定したカラムにインデックスをつけることはで

きる

(24)

主キーの例

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

「学籍簿」テーブル：主キーはどれにしよう？

「姓」は？同姓の人はいっぱいいるタプル識別できない！

「名」は？同名の人もいっぱいいるタプル識別できない！

「学部」は？同じ学部にたくさん学生が

いる ^{タプル識別できない！}

「学科」は？同じ学科にたくさん学生が

いる ^{タプル識別できない！}

「入学年度」

は？同じ年度にたくさん学生が

入学してきた ^{タプル識別できない！}

「学籍番号」というカラムを人工的に作って学生を識別することになる

(25)

25

主キーをつけただけでは不十分、、、

 テーブルを一意に識別できるキーを作るのは単純

 _{実際には ...}

 システム全体でのデータの識別が問題になることが多

い

 銀行のお客様の口座は口座番号で識別できる←主キー

 お客様はどうやって識別する？

 口座番号：複数の口座を持っている人がたくさんいる

 お客様の家族はどうやって識別する？

 住所と姓だけで OK か？

 データベースの設計ではこのような要件（要救条

件）を考えてテーブルの設計、キーの割り当てを

行っていかなければならない。

(26)

外部キーの例

 他のテーブルのデータを参照するためのキー

販売通番商品コード販売個数

0001 1010 1

0002 1052 10

0003 1033 1

0004 1020 2

0005 2020 5

商品コード ^商品名 ^単価

1010 _{商品 A} 2500

1020 _{商品 B} 1750

1033 _{商品 C} 3200

1052 _{商品 D} 180

伝票明細

商品

外部キー

参考書ではここは主キーと書いてあるが、一意な値のカラムなら主キーでなくともよい

複数のカラムを組み合わせて外部テーブルを参照することも可能

参考書ではここは主キーと書いてあるが、一意な値のカラムなら主キーでなくともよい

外部キーは一つのテーブルに複数あってもよい

複数テーブルを参照する

(27)

27

「参照 (reference) する

(refer) _{」って ?}

 他のデータを「示している」こと

 いろいろな使われ方をしている

 タプルを参照するカラム

 このカラムの値がわかれば、相手のテーブルのタプルが

どれかがわかる

 テーブルを参照するカラム

 カラムにテーブル名が書いてあって、どのテーブルを調

べればいいのかがわかる

 この変数はターゲットの構造体を参照している

 変数にターゲットの構造体のアドレスが入っている

 変数にターゲットの構造体の配列番号が入っている

 よく使われているけれども意味は広範囲。要注

意！！

テーブル名をテーブルに入

れるのはよほどのこと。あ

まりやらないほうがいい。

(28)

制約 (constraint)

 テーブルに格納する値に制限をつけることが

できる

 たとえば、「性」には「男」か「女」しか値がな

い

 _{年齢は必ずゼロ以上}

 存在しない学部学科は学生名簿には入らない

 データベースとして一般的に提供している制

約

 _{NOT NULL} _制約

 一意性制約 (UNIQUE)

 参照制約 (referenctial constraint)

(29)

29

制約の前に： NULL って何？

 今までは「値」がとにかく入っていた

 ₀ とか 1 とか、「あいうえお」とか

 「値が決まっていない」、「値がない」というのもある

 _FAX がない人の情報の FAX 番号のカラム

 社長さんの人事情報の「上司」のカラム

 平社員の「部下」のカラム

NULL

であらわす

超重要 !!

(30)

NULL は「普通の値」ではない

 _NULL _{同士は比較できない}

 _FAX がない人同士で FAX 番号が同じかどうか

は意味をなさない

 等しいかどうかだけでなく、大小関係もない

SQL でも NULL は「普通の値」としては扱っていな

いので、こちらも注意！！

(31)

31

再びテーブルの制約

 _{制約を設ける理由}

 アプリケーションあるいは人為的なミスでデータベースの

内容が矛盾しないようにする

 アプリケーションで必要な制約をすべてデータベースで提

供するのは困難

 トリガやルールといった他の機構も使って必要な制約を

組み込む

 データベースが提供する制約は性能上有利なので、一般

的な制約はデータベースが提供する

 誤ったデータが入っても検出には時間がかかる

 一ヶ月たってからおかしなデータが見つかっても訂正が追

いつかない

 別のデータにもこの影響が波及しているかもしれない

 なるべく早くデータが正しいことを保証しなければならな

い

(32)

NOT NULL _制約

 あるカラムの値が NULL であってはならないという制約

学籍番号姓 ^Middle

Name ^名 ^学部 ^学科 ^入学年度

00001 _秋吉 Mary _{久美子環境情報} _メディア 2004 00002 _赤坂 George _泰彦 _環境情報 _情報 2004 00003 _飯島 Samantha _愛 _環境情報 _メディア 2005

00004 _泉 _{ピン子環境情報} _メディア 2005

これらはすべて NOT NULL

Middle Name のない人もいる： NOT NULL ではない

(33)

33

一意性制約 (unique)

 同じ値を持っている（かもしれない）タプルが同じテーブ

ル内で他にあるか？

00001 _秋吉 _久美子 _環境情報 _メディア 2004 00002 _赤坂 _泰彦 _環境情報 _情報 2004 00003 _飯島 _愛 _環境情報 _メディア 2005 00004 _泉 _ピン子 _環境情報 _メディア 2005

これらは unique ではない

学籍番号は unique

学籍簿

学部キャンパス

工世田谷

環境情報横浜

知識工学世田谷

学部

同じ「学部」でも別な

テーブルでは unique に

なることもある

(34)

参照制約 (reference constraint)

 _{外部キーに関する制約}

 外部キーが参照しているテーブルには、必ずその外

部キーの値を持ったタプルがなければならない。

(35)

35

参照制約が守られている例

販売通番商品コード販売個数

0001 1010 1

0002 1052 10

0003 1033 1

0004 1020 2

0005 2020 5

商品コー

ド ^商品名 ^単価

1010 _{商品 A} 2500

1020 _{商品 B} 1750

1033 _{商品 C} 3200

1052 _{商品 D} 180

2020 _{商品 E} 950

伝票明細

商品

外部キー

「伝票明細」のすべてのタプル

の商品コードに対応するタプル

が「商品」に存在している

(36)

参照制約が守られていない例

販売通番商品コード販売個数

0001 1010 1

0002 1052 10

0003 1033 1

0004 1020 2

0005 2020 5

商品コー

ド ^商品名 ^単価

1010 _{商品 A} 2500

1020 _{商品 B} 1750

1033 _{商品 C} 3200

2020 _{商品 E} 950

伝票明細

商品

外部キー

商品コード「 1052 」に対応す

るタプルが「商品」にない！！

(37)

37

âèâîü[âVâçâiâïâfü[â^âxü[âX

データベース

第 3 回 リレーショナルデータベース

鈴木幸市

今回の内容

 リレーショナルデータベースの沿革

 リレーション

 関係演算

 キー

 制約

 NULL の意味

 制約

リレーショナルデータベースの沿革

 1970 年の Codd の提案

 1980 年代の SQL 言語の開発、先進製品の開発

 1990 年代の SQL 言語標準化と普及

 今ではデータベースの代名詞

リレーションとは

 2 次元の「表」のこと

学籍番号 姓 名 学部 学科 入学年度

00001 秋吉 久美子 環境情報 メディア 2004

00002 赤坂 泰彦 環境情報 情報 2004

00003 飯島 愛 環境情報 メディア 2005

00004 泉 ピン子 環境情報 メディア 2005

00005 石原 良純 環境情報 情報 2005

00006 伊東 四朗 環境情報 メディア 2006

00007 うつみ 宮土理 環境情報 情報 2006

学籍簿

名前がつい

ている

「タプル」あるいは

「行」、「レコード」

名前はつかない

「カラム」「属性」

これにも名前がついて

いる

リレーションの構造の表し方

 「見出し」だけを使ってリレーションの構造を表すことがある。

 見出しの表現方法はいろいろだが、直感的に理解できるもの

学籍番号 姓 名 学部 学科 入学年度

学籍番号 姓

名 学部

学科 入学年度

留意事項 (1)

 格納できるのはあらかじめ定義した「単項目」のデータに限る

 配列やリレーションをリレーションの中に入れることはできない

 一部データベースではこの部分を拡張しているものもある

 通常は数値や文字列

 それ以外に、図形やビットマップなども「単項目」に入れること

ができる

 タプルの順序には意味はない。

 SQL 文では特に指定しない場合は、同じ SQL 文を 2 回実行して

も違う順序でタプルが表示される場合もある。

同じリ

レーショ ン

留意事項 (2)

 値だけで照合される

 すべてのカラムの値が同じ 2 つのタプルを区別する手段はな

い

 実際には、システムカラムなどをつけることで区別する手段

を用意しているデータベースが多い。

区 別 す る

手段なし

リレーションに対する演算 ( 関係演算 )

 リレーションにある演算を施すと結果もリレーション

 そこで、リレーションを加工しながらいろいろな結果

を得ることができる

 リレーショナルデータベースのもっとも強力な点

 SQL 言語もこの性質を基本にしている

 簡単な仕様で強力な機能を持っている

 和、差、共通部

分

 選択

 射影

 直積

 結合

和 (Union)

 同じカラムを持つリレーションのタプルを合わせて一つのリレーションにする

+

R

第 3 回リレーショナルデータベース

 _{リレーション}

 _関係演算

 _キー

 _制約

 _NULL _の意味

 _制約

 ₁₉₇₀ 年の Codd の提案

 ₁₉₈₀ 年代の SQL 言語の開発、先進製品の開発

 ₁₉₉₀ 年代の SQL 言語標準化と普及

学籍番号姓名学部学科入学年度

00001 _秋吉 _久美子 _環境情報 _メディア 2004

00002 _赤坂 _泰彦 _環境情報 _情報 2004

00003 _飯島 _愛 _環境情報 _メディア 2005

00004 _泉 _ピン子 _環境情報 _メディア 2005

00005 _石原 _良純 _環境情報 _情報 2005

00006 _伊東 _四朗 _環境情報 _メディア 2006

00007 _うつみ _宮土理 _環境情報 _情報 2006

学籍番号姓名学部学科入学年度

学籍番号姓

名学部

学科入学年度

 _{通常は数値や文字列}

 _SQL 文では特に指定しない場合は、同じ SQL 文を 2 回実行して

_同じリ

レーション

 _{値だけで照合される}

区別する

 _SQL 言語もこの性質を基本にしている

 _{和、差、共通部}

 _選択

 _射影

 _直積

 _結合

π _{学籍番号 ,}

_,

σ _{学籍番号≧}

_学籍番号

^(R)

3 4 ^×

R ^S

R × S _ _{複数のテーブルを使っ}

 _{しかし、結果のタプル}

 _{実際には、直積を使わ}

　結合 (Join)

 _{Theta Join}

 _{上記の一般的な形}

 _{Semi Join}