--- title: 8. データベースの異状，分解と結合 tags: #リレーショナルデータベース #異状 #テーブルの分解 #重複業除去 #sql #distinct #結合 author: [kunihikokaneko](https://www.docswell.com/user/6674398749) site: [Docswell](https://www.docswell.com/) thumbnail: https://bcdn.docswell.com/page/VJN5X5V678.jpg?width=480 description: リレーショナルデータベースの基本（スライド資料とプログラム例） https://www.kkaneko.jp/de/ds/index.html ds-1. データベースとは，データベースシステムとは，情報とデータ ds-2. SQL，SQL のデータ型，テーブル定義，問い合わせ（クエリ） ds-3. ER 図，関連，異状, テーブル分解 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 published: January 12, 22 canonical: https://www.docswell.com/s/6674398749/KRG1J5-2022-01-12-111306 --- # Page. 1 ![Page Image](https://bcdn.docswell.com/page/VJN5X5V678.jpg) 8. データベースの異状，分解と結合 URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞：この資料では「いらすとや」のイラストを使用しています 1 # Page. 2 ![Page Image](https://bcdn.docswell.com/page/YE9Y8Y6RJ3.jpg) リレーショナルデータベースでの分解と結合 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 # Page. 3 ![Page Image](https://bcdn.docswell.com/page/GE8686ZG7D.jpg) 分解と結合 • テーブルを分解し，分解してできたテーブル２つを結合す ると，元のテーブルに戻る場合がある． • 異状の問題を，テーブル分解により解決できる場合がある （分解しても，結合により元のテーブルに戻せることが前 提） • DISTINCT ・・・ 重複行除去 • INTO ・・・ Access だけの機能．SQL の結果をテーブル に保存 3 # Page. 4 ![Page Image](https://bcdn.docswell.com/page/LEL9R9DXJR.jpg) アウトライン 番号 項目 説明時間の 目安 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 # Page. 5 ![Page Image](https://bcdn.docswell.com/page/4JMZRZP3EW.jpg) リレーショナルデータベースシステム データベースシステムの一種 コンピュータ 記憶 装置 リレーショナル リレーショナル データベース データベース 管理システム たくさんのテーブルが格納される あわせて リレーショナルデータベースシステム 5 # Page. 6 ![Page Image](https://bcdn.docswell.com/page/PJR6R61R79.jpg) 問い合わせ（クエリ）の仕組み 問い合わせ（クエリ） の結果は，テーブル形式の データ 問い合わせ （クエリ） のコマンド リレーショナル データベースシステム データの種類ごとに分かれ た、たくさんのテーブル 6 # Page. 7 ![Page Image](https://bcdn.docswell.com/page/PEXMRMYY7X.jpg) Access で SQLビューを開く． ①「作成」タブで、 「クエリデザイン」 をクリック このような 表示が出た ときは 「閉じる」を クリック ②「デザイン」タブで、 「表示」を展開し「SQL ビュー」を選ぶ 7 # Page. 8 ![Page Image](https://bcdn.docswell.com/page/3EKGRG34JD.jpg) 8-1. リレーショナルデータベー スの異状 8 # Page. 9 ![Page Image](https://bcdn.docswell.com/page/L73N8NGD75.jpg) 異状とは • データベースで，一番困ることは何でしょうか？ データベース内のデータが、 ◆ つじつまの合わない状態 あるいは ◆ 記録したいデータが記録できない状態 になり、しかも、修復できない状態に陥ること ＝ 異状という（「異常」の書き間違いではない） 私の見解：異状が一番困る 9 # Page. 10 ![Page Image](https://bcdn.docswell.com/page/87DWMW12JG.jpg) 異状の例 このバスは無料です このバスは運賃１０００円です 10 # Page. 11 ![Page Image](https://bcdn.docswell.com/page/VJP9R9QLE8.jpg) 異状が起きやすいリレーショナルデータベースの例 名前 A B C 朝食 カレー ライス うどん カレー ライス テーブル 値段 400 250 400 • カレーライスは、４００円 • うどんは、２５０円 • Aさんはカレーライスを食べた • Bさんはうどんを食べた • Cさんはカレーライスを食べた 情報 11 # Page. 12 ![Page Image](https://bcdn.docswell.com/page/2EVMRM56EQ.jpg) 異状が起きやすいリレーショナルデータベースの例 情報の更新 名前 A B C 朝食 カレー ライス うどん カレー ライス テーブル カレーライスが 400円から 350円に値下げ 350 値段 • カレーライスは、４００円 400 350 • うどんは、２５０円 250 • Aさんはカレーライスを食べた 400 • Bさんはうどんを食べた 350 • Cさんはカレーライスを食べた 情報 12 # Page. 13 ![Page Image](https://bcdn.docswell.com/page/57GGMG627L.jpg) 異状が起きやすいリレーショナルデータベースの例 情報の更新 名前 A B C 朝食 カレー ライス うどん カレー ライス カレーライスが 400円から 350円に値下げ 値段 400 250 400 350 書き換え忘れして しまうかも！ テーブル 13 # Page. 14 ![Page Image](https://bcdn.docswell.com/page/4EQXRX497P.jpg) 異状が起きやすいリレーショナルデータベースの例 情報の更新 名前 A B C 朝食 カレー ライス うどん カレー ライス テーブル カレーライスが 400円から 350円に値下げ 値段 400 250 350 異状が起きている ◆朝食の値段が１つのはずなの に、違った値段が記録 されていてつじつまが合わない 14 # Page. 15 ![Page Image](https://bcdn.docswell.com/page/KJ4686LR71.jpg) 8-2. テーブル分解による 異状の防止 15 # Page. 16 ![Page Image](https://bcdn.docswell.com/page/LE1V2VLV7G.jpg) テーブル分解 テーブル 分解 テーブル テーブル 16 # Page. 17 ![Page Image](https://bcdn.docswell.com/page/GEWLRLV372.jpg) 分解前のテーブルは，異状が起きやすい 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 400 テーブル 250 350 異状が起きている ◆朝食の値段が１つのはずなの に、違った値段が記録 されていてつじつまが合わない 17 # Page. 18 ![Page Image](https://bcdn.docswell.com/page/47ZMRM4GE3.jpg) テーブル分解 名前 A B C 朝食 カレー ライス うどん カレー ライス 名前 A B C 値段 400 250 400 分解 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 値段 400 250 分解後、情報は失わ れていない 18 # Page. 19 ![Page Image](https://bcdn.docswell.com/page/YJ6NRN3QJV.jpg) 分解後のテーブルは，異状が起きにくい カレーライスが 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 400円から 350円に値下げ 異状はない 値段 350 250 テーブル 19 # Page. 20 ![Page Image](https://bcdn.docswell.com/page/GJ5585L2J4.jpg) 分解後のテーブルは，異状が起きにくい カレーライスが 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 カレーライス うどん 400円から 350円に値下げ 異状はない 値段 350 250 テーブル 20 # Page. 21 ![Page Image](https://bcdn.docswell.com/page/LE3N8NL1E5.jpg) 設計変更による異状の防止 名前 A B C 朝食 値段 情報は カレー 400 350 同じ ライス うどん 250 カレー 400 350 ライス 異状が起きやすいデータベース 冗長なデータがある カレーライスの値下げのとき、 片方を書き忘れると → 異状 名前 A B C 朝食 カレーライス うどん カレーライス 朝食 値段 カレーライス 400 350 うどん 250 異状が起きにくいデータベース 冗長なデータがない 21 # Page. 22 ![Page Image](https://bcdn.docswell.com/page/8EDWMWNK7G.jpg) １つのテーブルを、複数のテーブルに分解す ることで、異状を起きにくくすることができ る場合がある． 22 # Page. 23 ![Page Image](https://bcdn.docswell.com/page/V7P9R953J8.jpg) 8-3. 冗長なデータの例 23 # Page. 24 ![Page Image](https://bcdn.docswell.com/page/2JVMRMPNJQ.jpg) 冗長なデータ 冗長なデータ 会計ーA，窓口ーB るのが冗長 が複数個所にあ 24 # Page. 25 ![Page Image](https://bcdn.docswell.com/page/5EGGMGP5EL.jpg) テーブル分解により、冗長なデータをなくす 分解 分解後、冗長なデータはなくなる → データベースの異状の防止につながる 25 # Page. 26 ![Page Image](https://bcdn.docswell.com/page/4JQXRX2LEP.jpg) 8-4. 重複行除去，DISTINCT 26 # Page. 27 ![Page Image](https://bcdn.docswell.com/page/K74686L5E1.jpg) 重複行除去 • 重複行除去は，同じ値を持つ複数の行について， １行だけ残して，他は消すこと 重複行除去 27 # Page. 28 ![Page Image](https://bcdn.docswell.com/page/LJ1V2VL2EG.jpg) 重複行除去が役に立つ場合 元のテーブル テーブル名: 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 # Page. 29 ![Page Image](https://bcdn.docswell.com/page/GJWLRLV2E2.jpg) 8-5. 重複行除去に関する演習 29 # Page. 30 ![Page Image](https://bcdn.docswell.com/page/4EZMRM44J3.jpg) 演習用のデータベースファイル • 演習用の Access データベースファイル セレッソの利用者は，セレッソからもダウンロード可能 ファイル名: db8.accdb • 「コンテンツの有効化」のメッセージが出たときは、確認 のうえ、次にすすむ • つぎのような表示が出たときは、確認のうえ、「はい」 30 # Page. 31 ![Page Image](https://bcdn.docswell.com/page/Y76NRN3G7V.jpg) 演習で使うデータベース 米国成人調査データ （１９９４年、米国における統計調査データのうち 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 # Page. 32 ![Page Image](https://bcdn.docswell.com/page/G75585LX74.jpg) 米国成人調査データ 「購入記録」はあとで使う 32 # Page. 33 ![Page Image](https://bcdn.docswell.com/page/9J2NKNLQER.jpg) SQL ビュー Access で、SQL ビューを開くときは、「表示」→ 「SQLビュー」と操作する 表示の下に「SQLビュー」が ないときは、 「作成」，「クエリデザイン」 と操作 33 # Page. 34 ![Page Image](https://bcdn.docswell.com/page/DEYPNPQYEM.jpg) 問い合わせ（クエリ）での、２つのビュー 実行 SQL ビュー 問い合わせ（クエリ）の 作成、編集 表示 + SQL ビュー データシートビュー 問い合わせ（クエリ）の 結果 マウス操作でビューを切り替え 34 # Page. 35 ![Page Image](https://bcdn.docswell.com/page/VJN5X5KR78.jpg) SELECT DISTINCT 教育 FROM 米国成人調査データ; ・空白文字は半角 ・「;」は最後にだけ 重複行除去 35 # Page. 36 ![Page Image](https://bcdn.docswell.com/page/YE9Y8YVZJ3.jpg) SELECT DISTINCT 職業 FROM 米国成人調査データ; ・空白文字は半角 ・「;」は最後にだけ 重複行除去 36 # Page. 37 ![Page Image](https://bcdn.docswell.com/page/GE86864Y7D.jpg) SELECT DISTINCT 母国 FROM 米国成人調査データ; ・空白文字は半角 ・「;」は最後にだけ 重複行除去 37 # Page. 38 ![Page Image](https://bcdn.docswell.com/page/LEL9R969JR.jpg) • 時間などに余裕のある人は， DISTINCT 有りの場合と，無しの場合の SQL を実行 し，見比べてみる SELECT DISTINCT 教育 FROM 米国成人調査データ; SELECT 教育 FROM 米国成人調査データ; 38 # Page. 39 ![Page Image](https://bcdn.docswell.com/page/4JMZRZ5VEW.jpg) 8-6. 分解と結合 39 # Page. 40 ![Page Image](https://bcdn.docswell.com/page/PJR6R63W79.jpg) いまから行うこと テーブル テーブル 分解 結合 テーブル テーブル テーブルを分解し，分解してできたテーブル２つを 結合すると，元のテーブルに戻る場合がある． 40 # Page. 41 ![Page Image](https://bcdn.docswell.com/page/PEXMRM4V7X.jpg) テーブル分解で気を付けること テーブル分解の結果は、重複行除去されていること 名前 朝食 名前 朝食 値段 A カレーライス A カレー 400 B うどん ライス C カレーライス B うどん 250 テーブル各自の朝食 C カレー 400 分解 朝食 値段 ライス カレーライス 400 テーブル朝食と値段 うどん 250 次は NG．重複行除去が必要 朝食 カレーライス 値段 400 うどん カレーライス 250 400 テーブル朝食メニュー 41 # Page. 42 ![Page Image](https://bcdn.docswell.com/page/3EKGRGZ5JD.jpg) SQL によるテーブル分解 ２つの SQL を実行 SELECT DISTINCT 名前, 朝食 INTO 各自の朝食 FROM 朝食と値段; 名前 朝食 名前 朝食 値段 A カレーライス A カレー 400 B うどん ライス C カレーライス B うどん 250 テーブル各自の朝食 C カレー 400 分解 朝食 値段 ライス カレーライス 400 テーブル朝食と値段 うどん 250 SELECT DISTINCT 朝食, 値段 テーブル朝食メニュー INTO 朝食メニュー FROM 朝食と値段; 42 # Page. 43 ![Page Image](https://bcdn.docswell.com/page/L73N8ND175.jpg) Access の機能 INTO • INTO は，問い合わせ（クエリ）の結果から，新し いテーブルを１つ作る（２つ以上できることはな い） • マイクロソフト Access だけの機能 名前 A B C 朝食 カレー ライス うどん カレー ライス 値段 SELECT DISTINCT 朝食, 値段 INTO 朝食メニュー FROM 朝食と値段; テーブル朝食と値段 400 250 400 朝食 カレーライス うどん 値段 400 250 新しく作成される テーブル朝食メニュー 43 # Page. 44 ![Page Image](https://bcdn.docswell.com/page/87DWMWPKJG.jpg) 結合は １．２つのテーブルの行（レコード）のすべてのペアを作る 結合とは ２．結合条件があるときは、選択が行われる 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 # Page. 45 ![Page Image](https://bcdn.docswell.com/page/VJP9R9N3E8.jpg) 分解と結合 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 # Page. 46 ![Page Image](https://bcdn.docswell.com/page/2EVMRMKNEQ.jpg) 8-7. 分解と結合の演習 46 # Page. 47 ![Page Image](https://bcdn.docswell.com/page/57GGMGQ57L.jpg) 購入記録 47 # Page. 48 ![Page Image](https://bcdn.docswell.com/page/4EQXRXKL7P.jpg) SELECT DISTINCT ID, 商品名, 単価 INTO A FROM 購入記録; 結果を確認 テーブル A ができる 48 # Page. 49 ![Page Image](https://bcdn.docswell.com/page/KJ4686P571.jpg) SELECT DISTINCT 購入者, ID INTO B FROM 購入記録; 結果を確認 テーブル B ができる 49 # Page. 50 ![Page Image](https://bcdn.docswell.com/page/LE1V2V927G.jpg) SELECT A.ID, 商品名, 単価, 購入者 FROM A, B WHERE A.ID = B.ID; 結果を確認 元のテーブルが得られる 50 # Page. 51 ![Page Image](https://bcdn.docswell.com/page/GEWLRLQ272.jpg) 分解と結合 • テーブルを分解し，分解してできたテーブル２つを結合す ると，元のテーブルに戻る場合がある． • 異状の問題を，テーブル分解により解決できる場合がある （分解しても，結合により元のテーブルに戻せることが前 提） • DISTINCT ・・・ 重複行除去 • INTO ・・・ Access だけの機能．SQL の結果をテーブル に保存 51