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

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

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

リレーショナルデータベースでの分解と結合 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 購入記録; 2

分解と結合 • テーブルを分解し，分解してできたテーブル２つを結合す ると，元のテーブルに戻る場合がある． • 異状の問題を，テーブル分解により解決できる場合がある （分解しても，結合により元のテーブルに戻せることが前 提） • DISTINCT ・・・ 重複行除去 • INTO ・・・ Access だけの機能．SQL の結果をテーブル に保存 3

アウトライン 番号 項目 説明時間の 目安 8-1 リレーショナルデータベースの異状 5分 8-2 テーブル分解による異状の防止 6分 8-3 冗長なデータの例 3分 8-4 重複行除去，DISTINCT 5分 8-5 重複行除去に関する演習 12分 8-6 分解と結合 8分 8-7 分解と結合の演習 8分 8-1, 8-2 は，第３回授業内容の関係ある部分の復習である． 8-4 は別の授業で学習済み（今回の授業に関係ある内容なのでおさらい） 4

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

問い合わせ（クエリ）の仕組み 問い合わせ（クエリ） の結果は，テーブル形式の データ 問い合わせ （クエリ） のコマンド リレーショナル データベースシステム データの種類ごとに分かれ た、たくさんのテーブル 6

Access で SQLビューを開く． ①「作成」タブで、 「クエリデザイン」 をクリック このような 表示が出た ときは 「閉じる」を クリック ②「デザイン」タブで、 「表示」を展開し「SQL ビュー」を選ぶ 7

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

異状とは • データベースで，一番困ることは何でしょうか？ データベース内のデータが、 ◆ つじつまの合わない状態 あるいは ◆ 記録したいデータが記録できない状態 になり、しかも、修復できない状態に陥ること ＝ 異状という（「異常」の書き間違いではない） 私の見解：異状が一番困る 9

異状の例 このバスは無料です このバスは運賃１０００円です 10

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

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

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

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

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

テーブル分解 テーブル 分解 テーブル テーブル 16

分解前のテーブルは，異状が起きやすい 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 400 テーブル 250 350 異状が起きている ◆朝食の値段が１つのはずなの に、違った値段が記録 されていてつじつまが合わない 17

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

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

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

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

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

8-3. 冗長なデータの例 23

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

テーブル分解により、冗長なデータをなくす 分解 分解後、冗長なデータはなくなる → データベースの異状の防止につながる 25

8-4. 重複行除去，DISTINCT 26

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

重複行除去が役に立つ場合 元のテーブル テーブル名: 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; 28

8-5. 重複行除去に関する演習 29

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

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

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

SQL ビュー Access で、SQL ビューを開くときは、「表示」→ 「SQLビュー」と操作する 表示の下に「SQLビュー」が ないときは、 「作成」，「クエリデザイン」 と操作 33

問い合わせ（クエリ）での、２つのビュー 実行 SQL ビュー 問い合わせ（クエリ）の 作成、編集 表示 + SQL ビュー データシートビュー 問い合わせ（クエリ）の 結果 マウス操作でビューを切り替え 34

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

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

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

• 時間などに余裕のある人は， DISTINCT 有りの場合と，無しの場合の SQL を実行 し，見比べてみる SELECT DISTINCT 教育 FROM 米国成人調査データ; SELECT 教育 FROM 米国成人調査データ; 38

8-6. 分解と結合 39

いまから行うこと テーブル テーブル 分解 結合 テーブル テーブル テーブルを分解し，分解してできたテーブル２つを 結合すると，元のテーブルに戻る場合がある． 40

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

SQL によるテーブル分解 ２つの SQL を実行 SELECT DISTINCT 名前, 朝食 INTO 各自の朝食 FROM 朝食と値段; 名前 朝食 名前 朝食 値段 A カレーライス A カレー 400 B うどん ライス C カレーライス B うどん 250 テーブル各自の朝食 C カレー 400 分解 朝食 値段 ライス カレーライス 400 テーブル朝食と値段 うどん 250 SELECT DISTINCT 朝食, 値段 テーブル朝食メニュー INTO 朝食メニュー FROM 朝食と値段; 42

Access の機能 INTO • INTO は，問い合わせ（クエリ）の結果から，新し いテーブルを１つ作る（２つ以上できることはな い） • マイクロソフト Access だけの機能 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 SELECT DISTINCT 朝食, 値段 INTO 朝食メニュー FROM 朝食と値段; テーブル朝食と値段 400 250 400 朝食 カレーライス うどん 値段 400 250 新しく作成される テーブル朝食メニュー 43

結合は １．２つのテーブルの行（レコード）のすべてのペアを作る 結合とは ２．結合条件があるときは、選択が行われる 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 結果 44

分解と結合 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 購入記録; 45

8-7. 分解と結合の演習 46

購入記録 47

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

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

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

分解と結合 • テーブルを分解し，分解してできたテーブル２つを結合す ると，元のテーブルに戻る場合がある． • 異状の問題を，テーブル分解により解決できる場合がある （分解しても，結合により元のテーブルに戻せることが前 提） • DISTINCT ・・・ 重複行除去 • INTO ・・・ Access だけの機能．SQL の結果をテーブル に保存 51