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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

① 冗長なデータの発見 • 「商品注文データベース」の「注文」テーブルです。顧客名、 住所、商品名、価格、購入日などの属性があります。 • 同じ顧客からの異なる注文で顧客名や住所が繰り返し登録さ れていることを確認してください。それはどこですか？ | 注文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

ヒント • まず、顧客名の列を順に見て、同じ顧客名が複数回出現するかどう かを確認します。 • 次に、同じ顧客名の行を見て、住所が同じであることを確認します。 • 顧客名や住所が複数回登録されている場合、そのデータは冗長であ ると言えます。 | 注文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

解答例 注文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

② データの不整合の確認 • 在庫管理データベースです。商品の価格情報が、商品テーブルと注文履歴 テーブルの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

ヒント 商品テーブルと注文履歴テーブルの同じ商品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

解答例 • 注文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

3-3. 異状 28

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

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

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

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

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

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

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

3-4. 正規化 36

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

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

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

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

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

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

正規化による冗長なデータの排除の例① 生徒名 クラス 教科 成績 田中 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

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

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

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

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

テーブル分解を行う 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 購入記録; ２つの SQL の実行により、テーブル分解を行っている 49

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

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

テーブル結合を行う 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

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

テーブルの分解と結合 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

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

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

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

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

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

• https://erdplus.com/
• https://erdplus.com/standalone