8. データベースの異状,分解と結合

>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.

8. データベースの異状,分解と結合 URL: https://www.kkaneko.jp/cc/ds/index.html 金子邦彦 謝辞:この資料では「いらすとや」のイラストを使用しています 1

2.

アウトライン 番号 項目 説明時間の 目安 8-1 リレーショナルデータベースの異状 5分 8-2 テーブルの分解による異状の防止 6分 8-3 DISTINCT による重複行除去,Access を用 いた実演・実習 12分 8-4 分解と結合 8分 8-5 Access を用いた分解と結合の実演・実習 8分 2

3.

リレーショナルデータベースでの分解と結合 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 単価 購入 者 1 みかん 50 aa 1 みかん 50 bb 1 2 りんご 100 cc bb cc 1 2 3 メロン 500 dd dd 3 ID SELECT DISTINCT 購入者, ID INTO B FROM 購入記録; 3

4.

今日の内容 • 異状とは,データベース内のデータが、 ◆ つじつまの合わない状態 あるいは ◆ 記録したいデータが記録できない状態 になり、しかも、修復できない状態に陥ること • テーブルを分解し,分解してできたテーブル2つを結合す ると,元のテーブルに戻る場合がある. 4

5.

8-1. リレーショナルデータベー スの異状 5

6.

異状とは • データベースで,一番困ることは何でしょうか? データベース内のデータが、 ◆ つじつまの合わない状態 あるいは ◆ 記録したいデータが記録できない状態 になり、しかも、修復できない状態に陥ること = 異状という(「異常」の書き間違いではない) 私の見解:異状が一番困る 6

7.

異状の例 このバスは無料です このバスは運賃1000円です 7

8.

異状が起きやすいリレーショナルデータベースの例 名前 A B C 朝食 カレー ライス うどん カレー ライス テーブル 値段 400 250 400 • カレーライスは、400円 • うどんは、250円 • Aさんはカレーライスを食べた • Bさんはうどんを食べた • Cさんはカレーライスを食べた 情報 8

9.

異状が起きやすいリレーショナルデータベースの例 情報の更新 名前 A B C 朝食 カレー ライス うどん カレー ライス テーブル カレーライスが 400円から 350円に値下げ 350 値段 • カレーライスは、400円 400 350 • うどんは、250円 250 • Aさんはカレーライスを食べた 400 • Bさんはうどんを食べた 350 • Cさんはカレーライスを食べた 情報 9

10.

異状が起きやすいリレーショナルデータベースの例 情報の更新 名前 A B C 朝食 カレー ライス うどん カレー ライス カレーライスが 400円から 350円に値下げ 値段 400 250 400 350 書き換え忘れして しまうかも! テーブル 10

11.

異状が起きやすいリレーショナルデータベースの例 情報の更新 名前 A B C 朝食 カレー ライス うどん カレー ライス テーブル カレーライスが 400円から 350円に値下げ 値段 400 250 350 異状が起きている ◆朝食の値段が1つのはずなの に、違った値段が記録 されていてつじつまが合わない 11

12.

8-2. テーブルの分解による 異状の防止 12

13.

テーブルの分解 名前 A B C 朝食 カレー ライス うどん カレー ライス 名前 A B C 値段 400 250 400 分解 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 値段 400 250 分解後、情報は失わ れていない 13

14.

分解後のテーブルは,異状が起きにくい カレーライスが 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 400円から 350円に値下げ 異状はない 値段 350 250 テーブル 14

15.

設計変更による異状の防止 名前 A B C 朝食 値段 情報は カレー 400 350 同じ ライス うどん 250 カレー 400 350 ライス 異状が起きやすいデータベース 冗長なデータがある カレーライスの値下げのとき、 片方を書き忘れると → 異状 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 値段 カレーライス 400 350 うどん 250 異状が起きにくいデータベース 冗長なデータがない 15

16.

1つのテーブルを、複数のテーブルに分解す ることで、異状を起きにくくすることができ る場合がある. 16

17.

冗長なデータ ID First_Name Last_Name Department Room 001234 Ignacio Fleta Accounting A 002000 Christian Martin Computer Support B 002122 Orville Gibson Human Resources C 00300 Ramirez Research & Devel Jose B 003400 Ben Smith Accounting A 003780 Allison Chong Computer Support B 冗長なデータ 17

18.

テーブルの分解により、冗長なデータをなくす Dept_Name Room Human Resources C Accounting A Computer Support B Research & Development B ID 分解後、冗長なデータはなくなる → データベースの異状の防止 First_Name Last_Name Dept_Name 001234 Ignacio Fleta Accounting 002000 Christian Martin Computer Support 002122 Orville Gibson Human Resources 003000 Jose Ramirez Research & Development 003400 Ben Smith Accounting 003780 Allison Chong Computer Support 18

19.

8-3. DISTINCT による 重複行の除去, Access を用いた実演・実習 19

20.

重複行除去 • 重複行除去は,同じ値を持つ複数の行について, 1行だけ残して,他は消すこと 重複行除去 20

21.

重複行除去 元のテーブル テーブル名: P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 PRICE 20 30 1000 100 誰が、何を、いくらで買ったか 重複行除去しない SELECT CUST FROM P; 重複行除去する SELECT DISTINCT CUST FROM P; 21

22.

テーブルの分解では重複行を除去 2つの SQL を実行 SELECT DISTINCT 名前, 朝食 INTO 各自の朝食 FROM 朝食と値段; 名前 朝食 名前 朝食 値段 A カレーライス A カレー 400 B うどん ライス C カレーライス B うどん 250 テーブル各自の朝食 C カレー 400 分解 朝食 値段 ライス カレーライス 400 テーブル朝食と値段 うどん 250 SELECT DISTINCT 朝食, 値段 テーブル朝食メニュー INTO 朝食メニュー FROM 朝食と値段; 22

23.

テーブルの分解で気を付けること テーブルの分解の結果は、重複行除去されていること 名前 朝食 名前 朝食 値段 A カレーライス A カレー 400 B うどん ライス C カレーライス B うどん 250 テーブル各自の朝食 C カレー 400 分解 朝食 値段 ライス カレーライス 400 テーブル朝食と値段 うどん 250 次は NG.重複行除去が必要 朝食 カレーライス 値段 400 うどん カレーライス 250 400 テーブル朝食メニュー 23

24.

実演・実習で使うデータベース 米国成人調査データ (1994年、米国における統計調査データのうち 32561 人分) ※ このデータを使います (演習では、特定の職業、学歴、性別、母国を差別的に見ないようにしてください) データの出典:Lichman, M. (). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science (米国) 24

25.

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

26.

米国成人調査データ 「購入記録」はあとで使う 26

27.

SELECT DISTINCT 教育 FROM 米国成人調査データ; ・空白文字は半角 ・「;」は最後にだけ 重複行除去 27

28.

SELECT DISTINCT 職業 FROM 米国成人調査データ; ・空白文字は半角 ・「;」は最後にだけ 重複行除去 28

29.

SELECT DISTINCT 母国 FROM 米国成人調査データ; ・空白文字は半角 ・「;」は最後にだけ 重複行除去 29

30.

8-4. 分解と結合 30

31.

リレーショナルデータベースシステム データベースシステムの一種 コンピュータ 記憶 装置 リレーショナル リレーショナル データベース データベース 管理システム たくさんのテーブルが格納される あわせて リレーショナルデータベースシステム 31

32.

結合は 1.2つのテーブルの行(レコード)のすべてのペアを作る 結合とは 2.結合条件があるときは、選択が行われる SELECT * FROM 商品, 購入 商品 ID 商品名 単価 1 みかん 50 2 りんご 100 3 メロン 500 WHERE ID = 商品番号; 購入 購入者 商品番号 X X 1 3 Y 2 商品名 ID 結合 結合条件 単価 購入者 商品番号 1 みかん 50 X 1 2 りんご 100 Y 2 3 メロン 500 X 3 結果 32

33.

テーブル テーブル 分解 結合 テーブル テーブル テーブルを分解し,分解してできたテーブル2つを 結合すると,元のテーブルに戻る場合がある. 33

34.

INTO (マイクロソフト Access だけの機能) 問い合わせ(クエリ)の結果を、 新しいテーブルに保存 SELECT DISTINCT CUST INTO A FROM P; テーブル P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 INTO は 問い合わせ結果から 新しいテーブルを作る テーブル A PRICE 20 30 1000 100 CUST 100 101 102 34

35.

分解と結合 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 単価 購入 者 1 みかん 50 aa 1 みかん 50 bb 1 2 りんご 100 cc bb cc 1 2 3 メロン 500 dd dd 3 ID SELECT DISTINCT 購入者, ID INTO B FROM 購入記録; 35

36.

8-5. Access を用いた分解と結合 の実演・実習 36

37.

購入記録 37

38.

SELECT DISTINCT ID, 商品名, 単価 INTO A FROM 購入記録; 結果を確認 テーブル A ができる 38

39.

SELECT DISTINCT 購入者, ID INTO B FROM 購入記録; 結果を確認 テーブル B ができる 39

40.

SELECT A.ID, 商品名, 単価, 購入者 FROM A, B WHERE A.ID = B.ID; 結果を確認 元のテーブルが得られる 40

41.

関連資料 • リレーショナルデータベース序論(全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 41