14. 従属，正規形，正規化

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

• https://www.kkaneko.jp/de/ds/index.html

• 従属 ⇒ データベースのデータが冗長か を分析する手がかり • 従属の分析は、データベースの設計で大切 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 「単価」は「商品名」に従属する 「商品名 → 単価」のように書く 2

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

アウトライン 番号 項目 14-1 14-2 14-3 14-4 14-5 14-6 14-7 従属 従属に関する演習 正規形 種々の正規形 第三正規形 正規化 MySQL を用いた分解と正規化の演習 4

14-1. 従属 5

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

従属の例 名前 A 昼食 そば 料金 250 B C D カレーライス 400 カレーライス 400 うどん 250 ①「昼食」は「名前」に従属する 「名前 → 昼食」のように書く 制約：それぞれの人は，昼食を１つしか食べない ②「料金」は「昼食」に従属する 「昼食 → 料金」のように書く 制約：それぞれの昼食の料金は１つ ③「料金」は「名前」に従属する 「名前 → 料金」のように書く 制約：それぞれの人の料金は１つ 7

A そば B カレーライス 名前 昼食 C カレーライス D うどん それぞれの人は，昼食を１つしか食べない 8

250 そば 昼食 400 料金 カレーライス 250 うどん それぞれの昼食の料金は１つ 9

A 250 B 400 名前 料金 C 400 D 250 それぞれの人の料金は１つ 10

14-2. Access を用いた従属に関 する演習 11

「商品」テーブルの従属 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 商品名 → 単価 （「単価」は「商品名」に従属する） 商品名 単価 みかん 50 りんご 100 メロン 500 商品名、単価のみ を射影したテーブル （重複行除去） 商品名でグループを作ると、 行数（レコード数）はすべて１ 12

「商品」テーブルの従属 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 単価と購入者は、従属にない 単価 購入者 50 aa 50 bb 100 cc 500 aa 単価、購入者のみ を射影したテーブル （重複行除去） 単価や購入者でグループを作っても、 「行数（レコード数）はすべて１」 とはならない 13

演習用のデータベースファイル • 演習用の Access データベースファイル ※ セレッソの利用者は，セレッソからダウンロー ド可能 • 「コンテンツの有効化」のメッセージが出たとき は、確認のうえ、次にすすむ • つぎのような表示が出たときは、確認のうえ、 「はい」 14

演習用のデータベースファイル 商品テーブル 15

SELECT DISTINCT 商品名, 単価 FROM 商品; 射影と重複行除去 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 16

SELECT 商品名, count(*) FROM (SELECT DISTINCT 商品名, 単価 FROM 商品) GROUP BY 商品名; グループ化して、行数をカウント 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 17

SELECT DISTINCT 単価, 購入者 FROM 商品; 射影と重複行除去 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 18

SELECT 単価, count(*) FROM (SELECT DISTINCT 単価, 購入者 FROM 商品) GROUP BY 単価; グループ化して、行数をカウント 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 19

SELECT 購入者, count(*) FROM (SELECT DISTINCT 単価, 購入者 FROM 商品) GROUP BY 購入者; グループ化して、行数をカウント 商品名 みかん みかん りんご メロン 単価 50 50 100 500 購入者 aa bb cc aa 20

14-1. 正規形 21

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

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

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

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

14-2. 種々の正規形 26

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

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

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

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

14-3. 第三正規形 31

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

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

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

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

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

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

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

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

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

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

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

次のテーブルは第三正規形か？ 主キー 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 は、 主キー以外のものに従属している． 第三正規形の条件に合致しない． 43

14-4. 正規化 44

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

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

分解のバリエーション • どう分解するか？ 名前 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 分解③ 47

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

分解② 名前 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 分解② では、元のテーブルにあった冗長の問題が解消 されていない 49

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

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

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

14-5. MySQL を用いた分解と正規化 の演習 53

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

• https://paiza.io/ja

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

• 次の SQL を入れ，実行結果を確認 56

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

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

• 次の SQL を入れ，実行結果を確認 59

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

14-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.昼食; 61

14-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.料金; 62

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

• https://www.kkaneko.jp/cc/di/index.html
• https://www.kkaneko.jp/cc/ds/index.html
• https://www.kkaneko.jp/cc/tools/index.html