12. 正規形,正規化

>100 Views

January 12, 22

スライド概要

(リレーショナルデータベースの基本)(全15回)
URL: https://www.kkaneko.jp/cc/ds/index.html

profile-image

金子邦彦(かねこくにひこ) 福山大学・工学部・教授 ホームページ: https://www.kkaneko.jp/index.html 金子邦彦 YouTube チャンネル: https://youtube.com/user/kunihikokaneko

シェア

埋め込む »CMSなどでJSが使えない場合

各ページのテキスト
1.

12. 正規形,正規化 URL: https://www.kkaneko.jp/cc/ds/index.html 金子邦彦 謝辞:この資料では「いらすとや」のイラストを使用しています 1

2.

アウトライン 番号 項目 説明時間の 目安 12-1 正規形 7分 12-2 種々の正規形 7分 12-3 第三正規形 10分 12-4 正規化 6分 12-5 MySQL を用いた分解と正規化の実演・ 実習 6分 2

3.

正規形は,リレーショナルデータベースで,次を防 ぐための考え方. • データの重複(冗長) • データベース操作における異状 正規化は,テーブルの分解などにより、正規形のレ ベルを上げること 3

4.

12-1. 正規形 4

5.

正規形とは 正規形は,リレーショナルデータベースで,次を防 ぐための考え方. • データの重複(冗長) • データベース操作における異状 5

6.

正規形の必要性 • 次のテーブルは、冗長である 「情報工」と「工」のペアが複数個所に 学生番号 氏名 学科 学部 001 XX 情報工 工 002 YY 情報工 工 003 ZZ 情報工 工 ・次のテーブルは、料金を更新したときに異状が発生 名前 A 昼食 料金 カレーライス 400 B C うどん カレーライス 250 350 ◆昼食の料金が1つのはず なのに、違った料金が記録 されていてつじつまが合わ ない 6

7.

正規形とテーブルの分解 • 1つのテーブルを、複数のテーブルに分解すると • そのテーブルの、正規形のレベルが変化する場合 がある. • なぜ、テーブルを分解すると異状が起きにくくな る場合があるのか? 正規形のレベルという考え 方で、理解ができる 7

8.

正規形のレベルの変化 名前 A B C 昼食 カレー ライス うどん カレー ライス 名前 昼食 A カレーライス B うどん C カレーライス 第三正規形である 料金 400 250 400 第三正規形ではない 分解 昼食 カレーライス うどん 料金 400 250 第三正規形である ※「第三正規形」につい ては、別資料で説明 分解後、情報は失わ れていない 8

9.

12-2. 種々の正規形 9

10.

正規形には、種々の段階(レベル)がある • 第一正規形 テーブルのセルには,1つの値を入れる.セルの合併はしない. • 第二正規形 候補キーに含まれない属性は、すべて候補キーに従属する.そし て,候補キーの部分集合には従属しない. • 第三正規形 主キー以外の属性は,すべて主キーにのみ直接,従属する • ボイスコッド正規形(3.5 正規形ともいう) すべての従属関係 X→Y について、それは自明であるか、Xが超 キーである. • 第四正規形 すべての多値従属関係 X Y について、それは自明であるか、Xが 候補キーであるか、Xがその超集合である. • 第五正規形 すべての結合従属性について、それは自明であるか、候補キーに より含意される 10

11.

第一正規形でないもの 次のテーブルは第一正規形か? 時 8 12 17 分 0, 20, 45 30 20, 40 第一正規形でない 次のテーブルは第一正規形か? 時 分 8 12 17 0 20 45 30 20 40 第一正規形でない 11

12.

第三正規形でないもの 次のテーブルは第三正規形か? 名前 A 昼食 カレー ライス うどん カレー ライス B C 料金 400 主キー 第三正規形でない 250 400 従属 ①名前 → 昼食 ②昼食 → 料金 ③名前 → 料金 第三正規形の条件 主キー以外の属性は, すべて主キーにのみ 直接,従属する 従属の②昼食 → 料金 がこの条件に違反 12

13.

正規形には、種々の段階(レベル)がある • • • • • • 第一正規形 第二正規形 第三正規形 ボイスコッド正規形(3.5 正規形ともいう) 第四正規形 第五正規形 下のものほど、 ・より厳しい条件 ・データの重複、異状の解消の度合いが高い(メリット) ・性能低下、テーブル数の増加、制約の記述不可 能になるなど、デメリットもある 13

14.

12-3. 第三正規形 14

15.

第三正規形 ☑ 主キー以外の属性は,すべて主キーにのみ直 接,従属するとき,第三正規形という ※ 主キー以外に従属している属性があるときは 第三正規形でない 主キー ID 氏名 1 徳川家康 2 豊臣秀吉 3 徳川家康 住所 XX YY ZZ 第三正規形である 従属 ①ID → 氏名 ②ID → 住所 第三正規形の条件 主キー以外の属性は, すべて主キーにのみ 直接,従属する 15

16.

分析手順 次の2つのテーブルについて、第三正規形であるか を分析 会員番号 住所 100 101 福山市野上町 4-3-2 福山市曙町12-3-4 会員番号 注文した商 品 100 りんご 101 りんご 100 ばなな 16

17.

① 主キーを見る 主キー 主キー 会員番号 住所 100 101 福山市野上町 4-3-2 福山市曙町12-3-4 会員番号 注文した商 品 100 りんご 101 りんご 100 ばなな 2属性(会員番号, 注文した 商品)のペアは主キーである 17

18.

② 従属を見る 主キー 主キー 会員番号 住所 100 101 福山市野上町 4-3-2 福山市曙町12-3-4 従属 ① 会員番号 → 住所 会員番号 注文した商 品 100 りんご 101 りんご 100 ばなな 従属 なし 18

19.

③ 第三正規形の条件にあてはめる 第三正規形の条件 主キー 主キー 会員番号 住所 100 101 主キー以外の属性は, すべて主キーにのみ 直接,従属する 福山市野上町 4-3-2 福山市曙町12-3-4 従属 ① 会員番号 → 住所 第三正規形である 会員番号 注文した商 品 100 りんご 101 りんご 100 ばなな 従属 なし 条件に反する 従属がない 第三正規形である 19

20.

従属 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 「単価」は「商品名」に従属する 「商品名 → 単価」のように書く 「ある属性 X の値が1つに決まると,別の属性 Y の値が1つに決まる(属性 Y の値が複数になること はあり得ない)」とき、 属性 Y は 属性 X に従属(X → Y のように書く) 20

21.

分析その2 次のテーブルについて、第三正規形であるかを分析 会員番号 住所 100 101 100 福山市野上町4-3-2 福山市曙町1-2-3-4 福山市野上町4-3-2 注文した商品 りんご りんご ばなな 21

22.

① 主キーを見る 主キー 主キー 会員番号 住所 注文した商品 100 101 100 福山市野上町4-3-2 福山市曙町1-2-3-4 福山市野上町4-3-2 りんご りんご ばなな ・会員番号だけでは、主キーにならない ・2属性(会員番号, 注文した商品)のペアは主キーで ある ※「主キーが2つある」という意味ではない 22

23.

② 従属を見る 主キー 主キー 会員番号 住所 注文した商品 100 101 100 福山市野上町4-3-2 福山市曙町1-2-3-4 福山市野上町4-3-2 りんご りんご ばなな 従属 ① 会員番号 → 住所 23

24.

③ 第三正規形の条件にあてはめる 第三正規形の条件 主キー以外の属性は, すべて主キーにのみ 直接,従属する 主キー 主キー 会員番号 住所 注文した商品 100 101 100 福山市野上町4-3-2 福山市曙町1-2-3-4 福山市野上町4-3-2 従属 ① 会員番号 → 住所 第三正規形でない りんご りんご ばなな 条件に反する 24

25.

正規形のレベルの変化 第三正規形である 分解 第三正規形ではない 第三正規形である 分解後、情報は失わ れていない 25

26.

次のテーブルは第三正規形か? 主キー ID First_Name Last_Name Department Room 001234 Ignacio Fleta Accounting A 002000 Christian Martin Computer Support B 002122 Orville Gibson Human Resources C 003000 Jose Ramirez Research & Devel B 003400 Ben Smith Accounting A 003780 Allison Chong Computer Support B 従属 従属 従属 従属 答え) 第三正規形ではない 従属 Department → Room は、 主キー以外のものに従属している. 第三正規形の条件に合致しない. 26

27.

12-4. 正規化 27

28.

正規化 • テーブルの分解などにより、正規形のレベルを上 げること • このとき、情報が失われたり、余分な情報が入る ようなことがあってはならない 28

29.

いまから考えるテーブル カレーライスは 400円 うどんは 250円 そばは 250円 名前 A B C D 昼食 そば カレーライス カレーライス うどん 料金 250 400 400 250 Aさんは、そばを食べた Bさんと、Cさんは、カレーライスを食べた Dさんは、うどんを食べた 29

30.

分解のバリエーション • どう分解するか? 名前 A B C D 昼食 そば カレーライス カレーライス うどん 名前 A B C D いろいろ考えることはできる 料金 250 400 400 250 昼食 そば カレーライス カレーライス うどん 昼食 そば カレーライス うどん 料金 250 400 250 分解① 名前 A B C D 昼食 そば カレーライス カレーライス うどん 名前 A B C D 料金 250 400 400 250 分解② 昼食 そば カレーライス うどん 名前 A B C D 料金 250 400 250 料金 250 400 400 250 分解③ 30

31.

分解① 名前 A B C D 昼食 そば カレーライス カレーライス うどん 名前 A B C D 料金 250 400 400 250 分解① 昼食 そば カレーライス カレーライス うどん 昼食 そば カレーライス うどん 料金 250 400 250 分解① では、元のテーブルにあった冗長の問題が解消. 異状の防止に効果あり. 31

32.

分解② 名前 A B C D 昼食 そば カレーライス カレーライス うどん 料金 250 400 400 250 分解② 名前 A B C D 昼食 そば カレーライス カレーライス うどん 名前 A B C D 料金 250 400 400 250 更新のとき、複数個所 書き換え必要な場合 あり 名前 A B C D 料金 250 400 400 250 350 350 分解② では、元のテーブルにあった冗長の問題が解消 されていない 32

33.

分解③ 名前 A B C D 昼食 そば カレーライス カレーライス うどん 昼食 そば カレーライス うどん 料金 250 400 400 250 分解③ 名前 A B C D 料金 250 400 250 料金 250 400 400 250 分解③ は、結合して元のテーブルに戻すことができない ので考慮外 33

34.

まとめ • テーブルの分解により、元のテーブルに戻せなく なる場合がある.そのような分解を、正規化のた めに行うことは論外である。 34

35.

まとめの2 • テーブルを分解して、第三正規形になったからと いって、データの冗長の問題が解決できていない 場合がある 35

36.

12-5. MySQL を用いた分解と正規化 の実演・実習 36

37.

Paiza.IO を用いた実演・実習 ① URL は https://paiza.io/ja ② 「コード作成を試してみる」をクリック ③ 「MySQL」を選ぶ 次ページへ続く 37

38.

正規化 テーブル A, B テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん 名前 A B C D 料金 250 400 400 250 分解 昼食 そば カレーライス カレーライス うどん 昼食 そば カレーライス うどん 料金 250 400 250 38

39.

• 次の SQL を入れ,実行結果を確認 39

40.

テーブルA, B の結合で、元のテーブルに戻る 40

41.

正規化にならない分解 テーブル A, B テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん 昼食 そば カレーライス うどん 料金 250 400 400 250 分解 名前 A B C D 料金 250 400 250 料金 250 400 400 250 41

42.

• 次の SQL を入れ,実行結果を確認 42

43.

テーブルA, B の結合で、元のテーブルに戻らない 43

44.

12-5 create table T(名前 text, 昼食 text, 料金 integer); insert into T values('A', 'そば', 250); insert into T values('B', 'カレーライス', 400); insert into T values('C', 'カレーライス', 400); insert into T values('D', 'うどん', 250); create table A as select distinct 名前, 昼食 from T; create table B as select distinct 昼食, 料金 from T; select * from A; select * from B; select A.名前, A.昼食, B.料金 from A, B where A.昼食 = B.昼食; 44

45.

12-5 create table T(名前 text, 昼食 text, 料金 integer); insert into T values('A', 'そば', 250); insert into T values('B', 'カレーライス', 400); insert into T values('C', 'カレーライス', 400); insert into T values('D', 'うどん', 250); create table A as select distinct 昼食, 料金 from T; create table B as select distinct 名前, 料金 from T; select * from A; select * from B; select A.昼食, A.料金, B.料金 from A, B where A.料金 = B.料金; 45

46.

関連資料 • リレーショナルデータベース序論(全4回) 全体を知る. https://www.kkaneko.jp/cc/di/index.html • リレーショナルデータベースの基本(全15回) 基礎を学ぶ. https://www.kkaneko.jp/cc/ds/index.html • リレーショナルデータベース演習(全15回) 演習により修得する. https://www.kkaneko.jp/cc/de/index.html 46