3. データベース設計,異状,正規化

1.4K Views

January 12, 22

スライド概要

リレーショナルデータベースの基本(スライド資料とプログラム例)
https://www.kkaneko.jp/de/ds/index.html


ds-1. データベースとは,データベースシステムとは,情報とデータ
ds-2. SQL,属性のデータ型,テーブル定義,問い合わせ(クエリ)
ds-3. データベース設計、異状、正規化
ds-4. 問い合わせ(クエリ)
ds-5. 集計・集約
ds-6. 並べ替え(ソート)
ds-7. 結合
ds-8. データベースの異状,分解と結合
ds-9. 主キー,参照整合性制約,従属
ds-10. 中間まとめ,データベースの応用,データベースの種類
ds-11. SQL のIN,副問い合わせ
ds-12. データベースの AND,OR,NOT
ds-13. データベース操作,トランザクション,リカバリ,ロック,同時実行制御
ds-14. 従属,正規形,正規化
ds-15. さまざまなデータベース

YouTube 再生リスト「リレーショナルデータベースの基本」
https://youtube.com/playlist?list=PLwoDcGBEg9WGKPP6dExr8DcUf9nV2kYGD

金子邦彦研究室ホームページ:
https://www.kkaneko.jp/index.html

profile-image

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

シェア

またはPlayer版

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

関連スライド

各ページのテキスト
1.

3. データベース設計,異状,正規化 URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞:この資料では「いらすとや」のイラストを使用しています 1

2.

① データの整合性の保持 ② データベース設計スキル ③ 問題解決能力 学びの過程は、新しい発見や視野の広がり、さらにはそれ らがもたらす実益と直結しています。 2

3.

アウトライン 1. リレーショナルデータベースの 概要と重要性 2. 冗長なデータの問題点 3. 異状 4. 正規化 5. テーブルの分解と結合 3

4.

3-1. リレーショナルデータ ベースの概要と重要性 4

5.

データベースとは データベースは、特定のテーマや目的に従って収集され た大量のデータ 取引 記入 データ収集 データベース 計測 撮影 データ保存 銀行、商店、交通機関、電話会社などさまざま 5

6.

リレーショナルデータベースの仕組み • データをテーブルと呼ばれる表形式で保存 • テーブル間は関連で結ばれる。複雑な構造を持ったデータ を効率的に管理すること可能に。 関連 6

7.

テーブルと属性 テーブル ID テーブル名:商品 商品名 単価 1 みかん 50 2 りんご 100 3 メロン 500 「ID」と「商品名」と「単 価」の属性 7

8.

データベースの構築手順 ID ID データベース データベース 生成 設計 ※ 最初,デー タベースは空 購入 者 商品 ID 名前 数量 ID 購入 者 「こういうテーブルを使い たい」と設定するだけなの で、テーブルは空 数量 1 X 1 10 2 Y 2 5 ID 名前 単価 テーブル定義 商品 ID 単価 1 みかん 50 2 りんご 100 3 りんご 150 データ追加 8

9.

リレーショナルデータベースの重要性 1. データの整合性: リレーショナルデータベースは、デー タの整合性を保持するための機能を有する。これにより、 誤ったデータや矛盾したデータが保存されるのを防ぐこ とができる。 2. 柔軟な問い合わせ(クエリ)能力: リレーショナルデー タベースのSQL(Structured Query Language)の使用に より、複雑な検索やデータの抽出が可能になる。 3. トランザクションの機能: 一連の操作全体を一つの単位 として取り扱うことができる機能。これにより、データ の一貫性と信頼性が向上する。 4. セキュリティ: アクセス権限の設定などにより、セキュ リティを確保。 データの安全な保管、効率的なデータ検索・操作、ビジネス や研究の意思決定をサポート。 9

10.

3-2. 冗長なデータの問題点 10

11.

冗長なデータ このバスは運賃1000円です このバスは運賃1000円です 冗長なデータ = データベースとしては NG 「更新の際、すべての個所を更新しないといけない」 などの問題がある 11

12.

冗長なデータ 冗長なデータは、データベース内で不必要に重複し て保存されるデータを指す。 冗長なデータ 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 400 250 400 • カレーライスは、400円 • うどんは、250円 • Aさんはカレーライスを食べた • Bさんはうどんを食べた • Cさんはカレーライスを食べた テーブル 12

13.

冗長なデータの問題点 • データの更新の際、全ての重複箇所を更新しなけ ればならず、それが漏れるとデータの不整合が生 じる。(更新による不整合) • データベースのサイズが不必要に増大。 • データの検索やクエリの実行速度が遅くなる可能 性。 13

14.

冗長なデータの更新 更新 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 カレーライスが 400円から 350円に値下げ 350 • カレーライスは、400円 400 350 • うどんは、250円 250 • Aさんはカレーライスを食べた 400 • Bさんはうどんを食べた 350 • Cさんはカレーライスを食べた テーブル 14

15.

書き換え漏れによる不整合 更新 名前 A B C 朝食 カレー ライス うどん カレー ライス カレーライスが 400円から 350円に値下げ 値段 テーブル 400 350 250 400 書き換え漏れ 15

16.

書き換え漏れによる不整合 更新 名前 A B C 朝食 カレー ライス うどん カレー ライス カレーライスが 400円から 350円に値下げ 値段 400 350 250 400 朝食の値段が1つのはずなのに、 350, 400 の違った値段の記録が あり、不整合がある テーブル 16

17.

冗長なデータの例① 生徒の名前、生徒が所属するクラス、教科、成績を記録する テーブル 生徒名 クラス 教科 成績 田中 3年A組 数学 85 田中 3年A組 英語 90 佐藤 3年B組 数学 88 佐藤 3年B組 英語 92 「生徒名」と「クラス」の情報 が冗長に繰り返されている 17

18.

冗長なデータの例② 会計ーA,窓口ーB るのが冗長 が複数個所にあ 18

19.

冗長なデータの例② 生徒の名前、生徒が所属するクラス、教科、成績を記録する テーブル 生徒名 クラス 教科 成績 田中 3年A組 数学 85 田中 3年A組 英語 90 佐藤 3年B組 数学 88 佐藤 3年B組 英語 92 「生徒名」と「クラス」の情報 が冗長に繰り返されている 19

20.

ここまでのまとめ 冗長なデータの定義 不必要にデータベース内で重複して保存されるデデータ 冗長なデータにより生じる主な問題 • 更新時の不整合:全ての重複箇所を更新しないとデータの 不整合が生じる。 例:書き換え漏れにより、朝食の値段が350円と400円の2つ の異なる価格で記録。不整合が生じている。 20

21.

演習.冗長なデータ、デー タの不整合 【トピックス】 ①冗長なデータの発見 ②データの不整合の確認 21

22.

① 冗長なデータの発見 • 「商品注文データベース」の「注文」テーブルです。顧客名、 住所、商品名、価格、購入日などの属性があります。 • 同じ顧客からの異なる注文で顧客名や住所が繰り返し登録さ れていることを確認してください。それはどこですか? | 注文ID | 顧客名 | 住所 | 商品名 | 価格 | 購入日 | |-------|----------|--------------------|---------|-------|------------| | 1 | 田中太郎 | 東京都中央区1-1-1 | テレビ | 50000 | 2023-10-01 | | 2 | 田中太郎 | 東京都中央区1-1-1 | 冷蔵庫 | 100000| 2023-10-02 | | 3 | 山田花子 | 大阪府北区2-2-2 | 洗濯機 | 30000 | 2023-10-03 | | 4 | 田中太郎 | 東京都中央区1-1-1 | 掃除機 | 15000 | 2023-10-04 | 22

23.

ヒント • まず、顧客名の列を順に見て、同じ顧客名が複数回出現するかどう かを確認します。 • 次に、同じ顧客名の行を見て、住所が同じであることを確認します。 • 顧客名や住所が複数回登録されている場合、そのデータは冗長であ ると言えます。 | 注文ID | 顧客名 | 住所 | 商品名 | 価格 | 購入日 | |-------|----------|--------------------|---------|-------|------------| | 1 | 田中太郎 | 東京都中央区1-1-1 | テレビ | 50000 | 2023-10-01 | | 2 | 田中太郎 | 東京都中央区1-1-1 | 冷蔵庫 | 100000| 2023-10-02 | | 3 | 山田花子 | 大阪府北区2-2-2 | 洗濯機 | 30000 | 2023-10-03 | | 4 | 田中太郎 | 東京都中央区1-1-1 | 掃除機 | 15000 | 2023-10-04 | 23

24.

解答例 注文ID 1, 2, 4において、顧客「田中太郎」の住所「東京都中央 区1-1-1」が繰り返し登録されています。これは冗長なデータで す。 | 注文ID | 顧客名 | 住所 | 商品名 | 価格 | 購入日 | |-------|----------|--------------------|---------|-------|------------| | 1 | 田中太郎 | 東京都中央区1-1-1 | テレビ | 50000 | 2023-10-01 | | 2 | 田中太郎 | 東京都中央区1-1-1 | 冷蔵庫 | 100000| 2023-10-02 | | 3 | 山田花子 | 大阪府北区2-2-2 | 洗濯機 | 30000 | 2023-10-03 | | 4 | 田中太郎 | 東京都中央区1-1-1 | 掃除機 | 15000 | 2023-10-04 | 24

25.

② データの不整合の確認 • 在庫管理データベースです。商品の価格情報が、商品テーブルと注文履歴 テーブルの2つの場所に保存されています。 • 価格の不一致はどこで起きていますか? 商品テーブル | 商品ID| 商品名 | 価格 | |-------|---------|-------| | 1 | テレビ | 50000 | | 2 | 冷蔵庫 | 100000| | 3 | 洗濯機 注文履歴テーブル | 30000 | | 注文ID| 商品ID| 商品名 | 価格 | 注文日 | |-------|-------|---------|-------|------------| | A | 1 | テレビ | 50000 | 2023-10-01 | | B | 2 | 冷蔵庫 | 95000 | 2023-10-02 | | C | 3 | 洗濯機 | 31000 | 2023-10-03 | 25

26.

ヒント 商品テーブルと注文履歴テーブルの同じ商品IDに基づいて、価格の一致を確 認します。 商品テーブル | 商品ID| 商品名 | 価格 | |-------|---------|-------| | 1 | テレビ | 50000 | | 2 | 冷蔵庫 | 100000| | 3 | 洗濯機 注文履歴テーブル | 30000 | | 注文ID| 商品ID| 商品名 | 価格 | 注文日 | |-------|-------|---------|-------|------------| | A | 1 | テレビ | 50000 | 2023-10-01 | | B | 2 | 冷蔵庫 | 95000 | 2023-10-02 | | C | 3 | 洗濯機 | 31000 | 2023-10-03 | 26

27.

解答例 • 注文ID「B」の「冷蔵庫」の価格が、商品テーブルと異なっています (100,000 vs. 95,000)。 • 注文ID「C」の「洗濯機」の価格も、商品テーブルと異なっています (30,000 vs. 31,000)。 商品テーブル | 商品ID| 商品名 | 価格 | |-------|---------|-------| | 1 | テレビ | 50000 | | 2 | 3 | 冷蔵庫 | 洗濯機 | 100000| | 30000 | 注文履歴テーブル | 注文ID| 商品ID| 商品名 | 価格 | 注文日 | |-------|-------|---------|-------|------------| | A | 1 | テレビ | 50000 | 2023-10-01 | | B | C | 2 | 3 | 冷蔵庫 | 洗濯機 | 95000 | 2023-10-02 | | 31000 | 2023-10-03 | 27

28.

3-3. 異状 28

29.

異状とは • 異状とは、データベースの設計が不適切な場合に、 データの操作(追加、更新、削除)時に起こる予 期しない問題や振る舞いのことを指す。 • 不適切に設計されたデータベースでは、データの 冗長性が生じることが多く、これが異状の原因と なる。 (「異常」ではありません) 29

30.

異状の具体例 ペット名 オーナー 住所 ミミ 徳川家康 東京都中央区1-1-1 タロウ 徳川家康 東京都中央区1-1-1 • これは、動物病院のデータベース。徳川家康さん の2匹のペットの記録がある。 • 徳川家康さんが引っ越すと、2か所の変更が必要 → 不便、書き換え漏れによる不整合の危険 → データベースの設計が不適切 30

31.

異状の問題点 • データの不整合のリスクが高まる。 • 不整合が生じると、情報の信頼性が失われ、誤っ た意思決定や業務処理のミスを引き起こす可能性 がある。 • 大きな問題となる。 不整合が起きている例 顧客名 お気に入りのドリンク 価格 田中 カフェラテ 400円 田中 カプチーノ 450円 佐藤 カフェラテ 420円 データベース内に異なる価格が混在し、どちらが 正しいのかが不明確になっている 31

32.

不整合が起きている例① 田中さんのクラスが変更になった。書き換え漏れに おり、所属クラス情報の矛盾が発生 生徒名 クラス 教科 成績 田中 3年A組 数学 85 田中 3年D組 英語 90 佐藤 3年B組 数学 88 佐藤 3年B組 英語 92 32

33.

不整合が起きている例② • 新しい生徒が転入した。成績が無いので、行全体 を完成できない。 生徒名 クラス 教科 成績 田中 3年A組 数学 85 田中 3年A組 英語 90 佐藤 3年B組 数学 88 佐藤 3年B組 英語 92 鈴木 3年B組 33

34.

不整合が起きている例③ • 佐藤さんの英語と数学の成績が取り消しになった。 成績が無いので、行全体を完成できない。 生徒名 クラス 教科 成績 田中 3年A組 数学 85 田中 3年A組 英語 90 佐藤 3年B組 佐藤 3年B組 34

35.

異状のまとめ 異状とは • データベース操作(追加、更新、削除)時の予期しない問題 • 主にデータベースの不適切な設計から生じる • 冗長性の存在が、異状の主な原因 異状の具体例 • 動物病院のデータベースにおいて、徳川家康さんの住所と、 2匹のペットの記録 • 住所変更時、2つのレコードを別々に更新する必要がある。 = 予期しない問題。書き換え漏れのリスク 異状の問題点 • データの不整合のリスク増加 35

36.

3-4. 正規化 36

37.

リレーショナルデータベースの設計における 考慮事項 次のことを考慮して設計を行うことは、データの不 整合を防ぐなど、重要 • 冗長なデータの排除を行う正規化 • データの整合性を保証する制約 例:関連するテーブル間の参照に関する制約 例:同じ値が2度現れないという制約 • 属性ごとのデータ型の指定(同じ列のデータは同 じデータ型) 37

38.

正規化とその重要性 • 正規化は、リレーショナルデータベースのテーブ ルを適切な形に再構成することで、データの冗長 性を排除し、データの整合性を向上させるプロセ ス。 • 正規化を適切に実施することでデータの不整合を 防ぐことができる。 • 正規化は、データベース設計において欠かせない ステップです。 38

39.

データの不整合の例 更新 名前 A B C 朝食 カレー ライス うどん カレー ライス カレーライスが 400円から 350円に値下げ 値段 400 350 250 400 朝食の値段が1つのはずなのに、 350, 400 の違った値段の記録が あり、不整合がある テーブル 39

40.

テーブル分解による正規化 正規化後 正規化前 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 400 250 400 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 値段 400 250 40

41.

正規化によるデータの不整合の防止 カレーライスが 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 値段 400 250 400円から 350円に値下げ データの不整合はない 350 テーブル 41

42.

正規化前と正規化後の比較 正規化前 名前 A B C 朝食 値段 カレー 400 350 ライス うどん 250 カレー 400 350 ライス 冗長なデータがある 正規化後 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 値段 カレーライス 400 350 うどん 250 冗長なデータがない 42

43.

正規化による冗長なデータの排除の例① 生徒名 クラス 教科 成績 田中 3年A組 数学 85 田中 3年A組 英語 90 佐藤 3年B組 数学 88 佐藤 3年B組 英語 92 正規化 生徒ID 教科 成績 生徒ID 生徒名 クラス 1 数学 85 1 田中 3年A組 1 英語 90 2 佐藤 3年B組 2 数学 88 2 英語 92 データの冗長性が排除され、更新における不整合のリスクが低減 43

44.

正規化による冗長なデータの排除の例② 正規化 データの冗長性が排除され、更新における不整合のリスクが低減 44

45.

正規化のメリットとデメリット 正規化のメリット • データの冗長性を排除 • データの整合性が向上 • 更新、削除、挿入時の異状を減少 正規化のデメリット • 過度に正規化されたデータベースでは、テーブル の数が多くなり、利用が複雑になる場合がある。 性能上の問題が発生する可能性もある。 45

46.

正規化のまとめ • 正規化:リレーショナルデータベースのテーブル 再構成 • 目的:データの冗長性排除、整合性向上 • 結果:データの不整合防止 • 位置付け:データベース設計の必須ステップ 46

47.

テーブル分解 テーブル 分解 テーブル テーブル テーブル分解により、1つのテーブルが2つ以上の テーブルに分解される。 47

48.

テーブル分解を行う理由 ① 冗長なデータを排除する正規化を行う ② より小さなテーブルに分解することで、問い合 わせ(クエリ)の性能を向上させる 48

49.

テーブル分解を行う SQL SELECT DISTINCT ID, 商品名, 単価 INTO A FROM 購入記録; 商品名 ID 単価 購入 者 1 みかん 50 aa 1 みかん 50 bb 2 りんご 100 cc 3 メロン 500 dd SELECT DISTINCT 購入者, ID ID 1 2 3 商品名 単価 みかん 50 りんご 100 メロン 500 購入者 ID aa bb 1 1 cc dd 2 3 INTO B FROM 購入記録; 2つの SQL の実行により、テーブル分解を行っている 49

50.

テーブル結合 テーブル 結合 テーブル テーブル 50

51.

テーブル結合を行う理由 • 関連のある複数のテーブルを組み合わせて1つに するため • 特に、正規化のために分解されたテーブルをもと に戻すため 51

52.

テーブル結合を行う SQL SELECT A.ID, 商品名, 単価, 購入者 ID 1 2 3 商品名 単価 みかん 50 りんご 100 メロン 500 購入者 ID aa bb 1 1 cc dd 2 3 FROM A, B WHERE A.ID = B.ID; 商品名 ID 単価 購入 者 1 みかん 50 aa 1 みかん 50 bb 2 りんご 100 cc 3 メロン 500 dd 52

53.

テーブルの分解と結合に役立つ SQL コマンド • DISTINCT ・・・ 重複行除去 • INTO ・・・ Access だけの機能.SQL の結果をテーブル に保存 53

54.

テーブルの分解と結合 SELECT DISTINCT ID, 商品名, 単価 SELECT A.ID, 商品名, 単価, 購入者 INTO A FROM 購入記録; FROM A, B 商品名 ID 単価 購入 者 ID 1 2 3 商品名 単価 WHERE A.ID = B.ID; みかん 50 りんご 100 ID 商品名 メロン 500 1 みかん 50 aa 1 みかん 50 bb 2 りんご 100 cc 購入者 dd aa 3 メロン 500 SELECT DISTINCT 購入者, ID INTO B FROM 購入記録; 単価 購入 者 1 みかん 50 aa 1 みかん 50 bb 1 2 りんご 100 cc bb cc 1 2 3 メロン 500 dd dd 3 ID テーブルの分解と結合によ り、もとに戻る場合がある 54

55.

全体まとめ ① リレーショナルデータベースの重要性 • データの整合性:誤ったデータや矛盾したデータの保存を防ぐ。 • 柔軟な問い合わせ能力:SQLを使用し、複雑な検索やデータ抽出 が可能。 • トランザクション:一連の操作を一つの単位として扱う機能。 • セキュリティ:アクセス権限の設定などで確保。 冗長なデータの問題点 • 更新の際、全ての個所を更新する必要性。 異状とは • データベース操作時の予期しない問題。 • 主にデータベースの不適切な設計により、冗長なデータが発生 してしまうことが主な原因 55

56.

全体まとめ ② 正規化とその重要性 • 正規化は、テーブルを適切な形に再構成するプロセス。 • 冗長性の排除とデータ整合性の向上が目的。 • データベース設計の必須ステップ。 テーブル分解の理由 • 正規化のための冗長なデータの排除。 • 問い合わせの性能向上。 テーブル結合の理由 • 関連する複数テーブルの組み合わせ。 • 正規化で分解されたテーブルの再結合。 56

57.

① データの整合性の保持 • テーブルを正規化することで、データの冗長性を排除し、 整合性を高めることが可能となります。 • この学習を通じて、データ管理における新しい視角や考え 方を獲得できます。 ② データベース設計スキル • データベース設計の基本を学ぶことは、多様なデータベー スシステムの設計や運用能力に直結します。 • このとき、データの冗長性、異状、正規化について理解し ておくことは有用です。 ③ 問題解決能力 データの整合性やデータベース設計スキルを深化させること で、現実の問題解決能力が向上します。 学びの過程は、新しい発見や視野の広がり、さらにはそれ らがもたらす実益と直結しています。 57

58.

自習 冗長なデータの再現 課題 • 自分で考えたデータをもとに、わざと、冗長なデータを持 つ新しいテーブルを作成してください。 ヒント • 例として、学生の一覧と、彼らが所属するクラブの一覧の 2つのテーブルを考えてください。 • そして、それらの2つのテーブルを1つにまとめて、各学 生が所属するクラブを示す新しいテーブルを作成します。 このテーブルは、冗長なデータを持つことが考えられます (提出する必要はありません。) 58

59.

自習 ER図の作図サイト ERDPlus は、ER図などを作成するためのオンライン ツールです https://erdplus.com/standalone ITパスポート試験対策などで、すでに自分でER図のこと を学んだ経験がある人に向いた自習です。 ER図については、授業の別の回で説明するので待って いてください。 (提出する必要はありません。) 59