![[kaneko lab.]](https://www.kkaneko.jp/info/logo_png.png){kind=logo}
リレーショナルデータベースシステム（スライド資料１６回）
リレーショナルデータベースシステムはテーブル形式でデータを管理し，SQL言語を用いて検索・加工を行う．SQLはSELECT，FROM，WHEREなどの基本コマンドで操作し，正規化によりデータ冗長性を排除して「異状」を防ぐ．トランザクション機能は複数の操作を一連の処理として扱い，データの一貫性と信頼性を確保する．以上とその他リレーショナル・データベースの全般を16回のスライド資料と演習で学ぶ．
大学で使用した自作の資料等を，手直しの上公開している． クリエイティブ・コモンズ BY NC SA．
【目次】
- ds-1. データベースの基本: データベースの定義と用途，データベースシステムの特徴、情報とデータの違い [PDF], [パワーポイント], [HTML]
【概要】データベースは，特定のテーマや目的に従って収集されたデータの集合体であり，銀行取引，予約システム，大学の履修登録など，日常生活のあらゆる場面でデータベースが活用されている．データベースシステムは，データベースとそれを管理するソフトウェアであるデータベース管理システムから構成される．データベース管理システムは，データの整合性の維持，複数ユーザーによる同時アクセス，データの一元管理，データの検索のための機能を持つ．現代では，情報セキュリティの観点からもデータベースの重要性が増している．さらに，オンラインコミュニケーション，人工知能，データ分析など，新しい技術との連携により，データベースの応用範囲は今後も拡大していくことが予想される．データベースは現代のデジタル社会を支える基盤技術として，重要な役割を果たすものである．
- ds-2. SQL の基本: SQL の役割、テーブルと属性、テーブル定義、問い合わせ（クエリ） [PDF], [パワーポイント], [HTML]
【概要】 リレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの管理と検索を行う．テーブルの構造は，属性（列）と行から成り，属性にはデータ型が設定される．各セルには属性のデータ型に応じた1つの値のみが格納される．問い合わせ（クエリ）は，データベースから必要なデータを検索・加工するためのコマンド（指令）であり，SQLを用いて実行される．SQL では，create table文によるテーブル定義，select文による検索，where句による条件指定など，様々な操作が可能である．また，射影による必要な属性の選択や，選択による条件に合致する行の抽出，複数テーブルの結合なども行える．これらの機能により，データの構造化，整合性，永続性が保証され，大量のデータを安全かつ効率的に保存，管理，検索，共有することが可能となる．
- ds-3. データベース設計と正規化: データベース設計の留意点とメリット，異状，正規化の基本概念，正規化の手法 [PDF], [パワーポイント], [HTML]
【概要】 リレーショナルデータベースでは，データをテーブル形式で保存し，テーブル間を関連で結ぶ．しかし，不適切な設計は冗長なデータを生み出し，これが異状の原因となる．「異状」によりデータの不整合が発生したり，本来の機能が発揮できなくなる．この問題を解決するため，テーブルを適切な形に再構成してデータの冗長性を排除する「正規化」が重要となる．正規化は，テーブル分割によって冗長なデータを排除する．データベース設計において「正規化」は「異状」を防ぐために有効である．ただし，過度な正規化はテーブル数の増加や複雑化を招く可能性があるため，適切な設計が重要である．
- ds-4. SQL入門：SQLFiddleを活用したデータベース操作の基礎と応用 [PDF], [パワーポイント], [HTML]
【概要】 SQL（Structured Query Language）は，データベースを操作するための言語であり，データの検索や加工を可能にする．SQLの基本機能として，SELECT（必要なデータの取得），FROM（対象テーブルの指定），WHERE（取得したいデータの条件指定）がある．高度な操作として，テーブル結合（JOIN）によって複数のテーブルを結合できる．例えば，従業員テーブルと部署テーブルを結合すれば，従業員の名前と所属部署の名前を一つの結果として取得することが可能である．グループ化（GROUP BY）や集計関数を用いて統計的な分析を行うことができる．SQLを学ぶために、SQLFiddleのようなオンラインツールを活用すれば，データベースソフトウェアのインストールなしに，ウェブブラウザだけで実践的なSQLスキルを身につけることができる．
- ds-5. SQL基礎：SELECT文による効率的なデータ検索と操作の基本 [PDF], [パワーポイント], [HTML]
【概要】 リレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの管理と検索を行う．基本的なSQL問い合わせは，SELECT（取得する列の指定），FROM（対象テーブルの指定），WHERE（条件指定）の組み合わせで構成される．例えば、SELECT * FROM T;はテーブルTの全てのデータを取得し，SELECT a, b FROM T WHERE b > 80;は特定の条件（b > 80）を満たすデータだけを選択する．WHERE句では比較演算子，BETWEEN，IN，LIKEなどを使用して条件を指定できる．DISTINCTキーワードにより重複行を除去（例：SELECT DISTINCT a FROM T;）できる．SELECT * FROM T WHERE c LIKE '%階';でのパターンマッチングなど，多彩な方法で問い合わせ（クエリ）を行うことができる．
- ds-6. テーブル結合とSQLによるデータ統合 [PDF], [パワーポイント], [HTML]
【概要】 リレーショナルデータベースは，テーブル間には関連がある．この関連を活用するために，結合という操作が重要な役割を果たす．テーブルの結合は，異なるテーブルを一つにまとめる操作である．SQLではINNER JOINとON句を使用してテーブルの結合を行うことができる．例えば，商品テーブルと購入テーブルを結合する場合，「SELECT * FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号」のように記述する．ON句では結合条件を指定し，通常は2つのテーブルの特定の属性同士の値が等しいという条件を設定する．複雑な条件も指定できる．結合を活用することで，複数のテーブルに分散したデータを統合し，より有用な情報を得ることができる．
- ds-7. SQLによるデータ分析：GROUP BYを用いたグループ化と集約 [PDF], [パワーポイント], [HTML]
【概要】 SQL の機能としてGROUP BY句によるグループ化と集約がある．グループ化は同じ属性値を持つデータを集めることであり，例えば科目ごとや受講者ごとにデータをグループ分けすることで，科目別の平均点や受講者ごとの得点合計を算出するなど，データの分析が容易になる．SQLでは，グループに対して，AVG（平均），MAX（最大），MIN（最小），SUM（合計），COUNT（行数）の集約関数を適用することで，データの傾向や特徴を把握できる．例えば，「SELECT 科目, COUNT(*) FROM 成績 GROUP BY 科目」により科目ごとの行数を得ることができる．グループ化と集約と集約により，売上分析やトレンド分析，顧客セグメント分析などのビジネスインテリジェンスが可能となる．SQL以外のツールを使うときにも役に立つ考え方である．
- ds-8. 並べ替え（ソート）[PDF], [パワーポイント], [HTML]
演習用のデータベースファイル: db4-4.accdb
【概要】 リレーショナルデータベースはデータをテーブルと呼ばれる表形式で扱う．SQLによる問い合わせでの並べ替え（ソート）は，「SELECT 列名 FROM テーブル名 ORDER BY 並べ替えの属性;」を基本形式とし，昇順は指定なし，降順は「DESC」を付ける．並べ替え操作は昇順・降順の指定，複数属性の使用，選択や集計との組み合わせなど，様々なバリエーションが可能であり，データ分析の目的に応じて活用できる．例えば，「SELECT 母国, COUNT() FROM データ GROUP BY 母国 ORDER BY COUNT();」では集計結果の並べ替えを行うもので，データ傾向の把握に有効である．
- ds-9. SQLにおける副問い合わせと論理演算子（AND, OR）の基礎 [PDF], [パワーポイント], [HTML]
【概要】 SQLの副問い合わせ，IN演算子，論理演算子ANDとORは，データベースクエリの柔軟性向上に役立つ．副問い合わせは，SQL文の中に別のSQL文を組み込むことで複雑な条件指定を実現する．例えば「SELECT 受講者 FROM 成績 WHERE 得点 = (SELECT MAX(得点) FROM 成績)」のように，最高得点の受講者を取得できる．IN演算子は複数の値のいずれかに一致するかをテストするものであり，「WHERE 科目 IN ('国語', '算数')」のように条件指定ができる．論理演算子のANDは両方の条件が成立する場合に真となり，ORはいずれかの条件が成立する場合に真となる．これらを活用することで，効率的なデータ検索や条件指定が可能になり，複雑な問い合わせも実現できる．
- ds-10. データベース設計の基礎：主キー・外部キーを用いたテーブル間の関連付けとSQL実践 [PDF], [パワーポイント], [HTML]
【概要】 データベース設計では，主キーと外部キーを用いて，テーブル間の適切な関連付けを行う．主キーはテーブルの各行を一意に識別し，同一IDの重複を防止する不変のキーである．外部キーは他のテーブルの主キーを参照することでテーブル間の関連を示すものである．参照整合性制約はこれらの関連付けられたデータの一貫性を保証するためのもので，外部キーの値が，参照先のテーブルの主キーのいずれかの値であることを保証する．それにより，データの信頼性を向上させ，誤ったデータの混入を防ぐ．データベース設計では，テーブル名の決定，属性の設定，データ型の選択，制約の設定，索引の作成，テーブル間の関係性の定義を行う．適切なデータベース設計のよって，複雑なデータも扱いやすくなる．
- ds-11. データベース操作とトランザクション管理：データ整合性と永続性 [PDF], [パワーポイント], [HTML]
【概要】 データベースには，データ整合性を維持するために主キー制約，参照整合性制約などの仕組みが備わっており，誤ったデータや矛盾したデータが保存されるのを防いでいる．また，リレーショナルデータベースの運用では，複数のデータベース操作を一連の単一処理として扱うトランザクション機能が重要である．トランザクションによりデータの一貫性と信頼性が確保される．データの永続性機能により，一度コミットされたデータは確実に保存され，システム障害が発生しても失われることがない．問題発生時にはロールバック機能によりトランザクション開始時点へのデータ状態の復元が可能であり，全変更を取り消して整合性を維持できる．これらの機能が組み合わさることで，信頼性の高いデータ管理が実現される．
- ds-12. 中間まとめ：効率的なデータ管理と分析の実践 [PDF], [パワーポイント], [HTML]
【概要】リレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの検索と加工を行う．属性（列）はデータの種類に対応し，行は具体的なデータの並びとなる．SQLはSELECT, FROM, WHERE，INNER JOIN，ON, GROUP BY, ORDER BY，IN など多様なコマンドを使用して結合，集計，ソート，副問い合わせなどの高度な操作ができる．各テーブルに対して適切な制約を設定することでデータの整合性を保持する．NULLはデータ化できないものを表す特別な値で，未定，未知，存在しないなどの状態を記録するために使用される．これらの機能を利用することで，データの保管，検索・操作ができ，さまざまな意思決定をサポートすることができる．
- ds-13. データ管理の基礎：オンライントランザクションとデータウェアハウスの特徴と活用 [PDF], [パワーポイント], [HTML]
【概要】リレーショナルデータベースを用いたデータ管理・処理方法には2つのアプローチがある．１つはリアルタイムでのデータ処理を行うオンライントランザクションであり，銀行取引や予約システムなどリアルタイム処理に適し，最新データの維持と迅速な処理に適する．オンライントランザクションシステムでは，正規化によりデータの冗長性を排除し，データの整合性を確保する．もう１つは履歴データを重視するデータウェアハウスであり，長期的なデータ分析に適する．履歴データは，各行に日時情報を付加し，データ変化があるたびに新しい行を挿入する形で履歴を保持する．一度保存したデータは削除せずに，過去のデータの履歴を保持する．データウエアハウスは，時間軸での分析や傾向把握が簡単にでき，意思決定を支援する基盤となる．
- ds-14. データベース設計演習，正規化 [PDF], [パワーポイント], [HTML]
【概要】 データベース設計は，データベースの基本構造を定める重要なプロセスであり，テーブル名，属性，データ型，制約，索引，テーブル間の関係性などを定める．データベース設計において，正規化はデータベースの構造を最適化し，冗長性を減らすことで「異状」を防ぎ，効率的なデータ管理を実現する手法である．これらの操作において重要となるのが情報無損失の原則であり，正規化によるテーブルを分割を行っても，分割結果を結合することにより元の情報を完全に復元できることを保証する．正規化は，情報無損失のもと，データの冗長性を減少させるようにテーブルを分割することで行う．このプロセスは，「SELECT」や「CREATE TABLE ... AS」などのSQLコマンドを使用して実行できる．以上のように正規化はデータベース設計において重要な役割を果たし，冗長性の削減とデータ整合性の確保を実現する．
- ds-15. 関数従属性 [PDF], [パワーポイント], [HTML]
【概要】 関数従属性は属性間の依存関係を表すな概念である．属性Xの値が決まると属性Yの値が一意に決まる場合，「Y はX に従属する」と表現し，X → Yのようにと記述する．種々の正規形は，従属性に基づくものである．第三正規形は，主キー以外のすべての属性が主キーにのみ直接従属することを求め，データの冗長性を減らし，更新時の異状を防ぐ効果がある．主キーは，テーブル内の行を一意に識別するものである．多対多の関連では，両方のテーブルの主キーを参照する中間テーブルを作成することで簡単に取り扱うことができる．
- ds-16. インデックス，セキュリティ，データベースの歴史と展望，データベースのバリエーション [PDF], [パワーポイント], [HTML]
【概要】 データベースシステムは，データを効率的に整理・管理・利用するための基盤技術として発展してきた．データベースの主要な種類であるリレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの管理と検索を行うことである．インデックスは，全データスキャンではなくインデックスのみのスキャンでSQL問い合わせ処理を行うことにより，SQLの効率的な処理を実現する．リレーショナルデータベース管理システムは，データの整合性保持，セキュリティ確保の機能を備え，オンラインでのデータ共有に適している．現在は人工知能との統合，NoSQLなどの新技術が進展し進化している．
1. データベースの基本: データベースの定義と用途，データベースシステムの特徴、情報とデータの違い
【資料】
【概要】
データベースは，特定のテーマや目的に従って収集されたデータの集合体であり，銀行取引，予約システム，大学の履修登録など，日常生活のあらゆる場面でデータベースが活用されている．データベースシステムは，データベースとそれを管理するソフトウェアであるデータベース管理システムから構成される．データベース管理システムは，データの整合性の維持，複数ユーザーによる同時アクセス，データの一元管理，データの検索のための機能を持つ．現代では，情報セキュリティの観点からもデータベースの重要性が増している．さらに，オンラインコミュニケーション，人工知能，データ分析など，新しい技術との連携により，データベースの応用範囲は今後も拡大していくことが予想される．データベースは現代のデジタル社会を支える基盤技術として，重要な役割を果たすものである．
【関連する外部ページ】
- CelebA: http://mmlab.ie.cuhk.edu.hk/projects/CelebA.html
- Flicker: https://www.flickr.com/groups/japanese/
- Google Map: https://www.google.co.jp/maps/
- Google Earth: https://www.google.co.jp/intl/ja/earth/
- SQL Tutorial/ja: https://sqlzoo.net/wiki/SQL_Tutorial/ja
キーワードとその説明文
- データベース
特定のテーマや目的に従って収集された大量のデータの集合体である．銀行取引，商店の販売記録，交通機関の運行データなど，さまざまな場面で活用されている．オンラインコミュニケーション，取引，人工知能，データ分析など，現代社会の様々な分野で活用されている．
- 情報とデータ
データは数字や符号の集まりであり，情報はそのデータに意味を持たせたものである．たとえば，「720-0292」はデータであり，それが「福山大学の郵便番号である」という意味を持たせると情報となる．
- データの整合性
データベース内のデータが矛盾なく，一貫性を保った状態を指す．リレーショナルデータベースは，誤ったデータや矛盾したデータが保存されるのを防ぐ機能を有している．これにより，データの信頼性が確保される．
- データベースシステム
データベースとそれを管理するソフトウェアを組み合わせたシステムである．大量のデータを安全かつ効率的に保存，管理，検索，共有することで，迅速な業務実行と正確な意思決定を実現する．
- サイバーフィジカル
物理的な現実世界とデジタルな情報世界が融合したシステムである．農林畜産業，医療，製造業，都市交通，電力などの分野で，センサーからのデータを活用し，効率化や品質向上，新サービスの創生を実現している．
- 情報セキュリティ
データベースにおける重要な情報を保護する仕組みである．コンピュータシステムの故障，電力不足，災害からのデータ保護，また不正侵入や情報漏洩からの会員情報やデータの保護など，様々な脅威への対策が必要である．
- クラウドコンピューティング
コンピュータを所有せずに借りる仕組みである．インターネットを介してコンピューティングリソースにアクセスし，データベースとそのアプリケーションをオンラインで実現する．購入，保有，運用管理コストの削減が可能である．
- データサイエンス
データから有益な情報を抽出する学問である．ビジネス分野での顧客分析，医療分野での早期発見，工学分野での品質改善など，様々な分野で活用されている．機械学習など人工知能や情報処理とも密接に関連している．
- 同時アクセス
複数のユーザーが同時にデータベースにアクセスし，データを共有できる機能である．ネットワークを介して，異なる利用者が同時に取引，記入，データ保存などの操作を行うことが可能である．
- プログラム統合
データベースは様々なアプリケーションと統合しやすい特徴を持っている．これにより，データの処理や業務の自動化が容易になり，効率的なシステム構築が可能である．
- データの一元化
データを一箇所に集中して管理する方式である．これにより重複が排除され，データの整合性が向上し，混乱を避けることができる．効率的なデータ管理と運用を実現する重要な特徴である．
- オンラインサービス
インターネットを介して提供されるサービスである．例えばGoogle Earthでは，写真，3次元コンピュータグラフィックス，標高データなどのデータベースを活用し，地球の様々な場所の視覚的な情報を提供している．
- データの永続性
データを永続的に保存する特性である．データは電源を切っても消失せず，長期間にわたって安全に保管される．これにより，重要なデータを失う心配がなくなり，安定した業務運営が可能となる．
- データの構造化
データを整理し，分かりやすく体系化する作業である．これにより，データは理解しやすくなり，関連性のあるデータ同士を結び付けることも容易になる．効率的なデータ管理の基盤となる．
- リレーショナルデータベース
データをテーブル形式で保存し，テーブル間を関連で結ぶデータベース方式である．データの整合性保持機能や柔軟な問い合わせ機能を持ち，銀行や商店，交通機関などで広く利用されている．
2. SQL の基本: SQL の役割、テーブルと属性、テーブル定義、問い合わせ（クエリ）
【資料】
【概要】
リレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの管理と検索を行う．テーブルの構造は，属性（列）と行から成り，属性にはデータ型が設定される．各セルには属性のデータ型に応じた1つの値のみが格納される．問い合わせ（クエリ）は，データベースから必要なデータを検索・加工するためのコマンド（指令）であり，SQLを用いて実行される．SQL では，create table文によるテーブル定義，select文による検索，where句による条件指定など，様々な操作が可能である．また，射影による必要な属性の選択や，選択による条件に合致する行の抽出，複数テーブルの結合なども行える．これらの機能により，データの構造化，整合性，永続性が保証され，大量のデータを安全かつ効率的に保存，管理，検索，共有することが可能となる．
キーワードとその説明文
- データベース
特定のテーマや目的に従って収集された大量のデータの集合体である．銀行取引，商店の販売記録，交通機関の運行データなど，さまざまな場面で活用されている．オンラインコミュニケーション，取引，人工知能，データ分析など，現代社会の様々な分野で活用されている．
- データベースシステム
データベースとそれを管理するソフトウェアを組み合わせたシステムである．大量のデータを安全かつ効率的に保存，管理，検索，共有することで，迅速な業務実行と正確な意思決定を実現する．
- リレーショナルデータベース
データをテーブル形式で保存し，テーブル間を関連で結ぶデータベース方式である．データの整合性保持機能や柔軟な問い合わせ機能を持ち，銀行や商店，交通機関などで広く利用されている．
- テーブル
データベースにおける基本的なデータ保存形式であり，属性（列）とそれに対応する具体的なデータの集まり（行）から構成される表形式の構造である．例えば商品テーブルでは商品ID，商品名，単価などの属性を持つ．
- 属性（列）
テーブル内のデータの種類を定義する要素である．例えばID，商品名，単価など，データの性質を表す．各属性には特定のデータ型が設定され，その型に応じた値のみが入力可能となる．
- データ型
属性に設定される値の種類を定義するものである．主な種類として，整数（INTEGER），テキスト（TEXT），日付/時刻（DATETIME），Yes/No（BIT, BOOL）などがある．短いテキストは半角255文字分までが目安とされる．
- セル
テーブルにおいて属性と行が交差した位置に存在する要素である．1つのセルには必ず1つの値のみが入り，その値は属性のデータ型に適合していなければならない．マルチカラム形式は使用できない．
- SQL（Structured Query Language）
データベースを操作するための専用言語であり，複雑な検索やデータの抽出を可能にする．SELECT文やDISTINCT句，INTO句などの命令を使用してテーブルの操作を行う．複雑な検索やデータの抽出を可能にし，結合，集計，ソート，副問い合わせなどの高度な操作も可能である．
- 問い合わせ（クエリ）
データベースから必要なデータを検索，加工するための指令である．SELECT，FROM，WHEREなどの多様なコマンドを組み合わせることで，複雑なデータ抽出や加工が可能となる．
- テーブル定義
lcreate table文を使用してテーブルの構造を定義する操作である．テーブル名，属性名，データ型などを指定し，データの整合性を保つための制約を設定することができる．
- SELECT文
問い合わせの基本となる命令で，取得したいデータを指定するために使用する．アスタリスク（*）を使用すると全データを取得でき，特定の属性名を指定することで必要な列のみを取得できる．
- FROM句
select文でデータを取得する対象となるテーブルを指定するための句である．基本的な構文は「from テーブル名」となり，複数のテーブルを指定することも可能である．
- WHERE句
特定の条件を満たす行を選択するための句である．例えば「where 単価 > 80」とすることで，単価が80より大きい商品のみを取得できる．
- 射影
問い合わせにおいて必要な属性（列）のみを選択する操作である．select文で取得したい属性名を指定することで実現される．全ての属性を取得する必要がない場合に使用する．
- 選択
問い合わせにおいて条件に合致する行のみを抽出する操作である．where句を使用して条件を指定することで実現され，必要なデータのみを効率的に取得できる．
- データの整合性
データベース内のデータが矛盾なく，一貫性を保った状態を指す．リレーショナルデータベースは，誤ったデータや矛盾したデータが保存されるのを防ぐ機能を有している．これにより，データの信頼性が確保される．
- トランザクション機能
複数の処理を一つの論理的な単位として扱う機能である．一連の操作全体を一つの単位として実行することで，データの一貫性と信頼性を向上させることができる．begin，commit，rollbackなどのコマンドにより，データの一貫性と信頼性を向上させることができる．
- 集計・集約
データを特定の条件でグループ化し，合計や平均などの計算を行う操作である．group by句を使用して実現され，売上の集計などのビジネス分析に活用される．
- 並べ替え（ソート）
データを特定の属性の値に基づいて昇順または降順に整列させる操作である．order by句を使用して実現され，データの分析や表示に活用される．
- テーブル結合
複数のテーブル間の関連に基づいて複数のテーブルを1つにまとめる操作である．ONキーワードと組み合わせて結合条件を指定し，関連するデータを統合して表示することができる．
3. データベース設計、異状、正規化
【資料】
【概要】
リレーショナルデータベースでは，データをテーブル形式で保存し，テーブル間を関連で結ぶ．しかし，不適切な設計は冗長なデータを生み出し，これが異状の原因となる．「異状」によりデータの不整合が発生したり，本来の機能が発揮できなくなる．この問題を解決するため，テーブルを適切な形に再構成してデータの冗長性を排除する「正規化」が重要となる．正規化は，テーブル分割によって冗長なデータを排除する．データベース設計において「正規化」は「異状」を防ぐために有効である．ただし，過度な正規化はテーブル数の増加や複雑化を招く可能性があるため，適切な設計が重要である．
【関連する外部ページ】
ER図作成などのオンラインツール ERDPlus: https://erdplus.com/standalone
演習
- 冗長なデータの発見
| 注文ID | 顧客名 | 住所 | 商品名 | 価格 | 購入日 | |-------|----------|--------------------|---------|-------|------------| | 1 | 田中太郎 | 東京都中央区1-1-1 | テレビ | 50000 | 2024-10-01 | | 2 | 田中太郎 | 東京都中央区1-1-1 | 冷蔵庫 | 100000| 2024-10-02 | | 3 | 山田花子 | 大阪府北区2-2-2 | 洗濯機 | 30000 | 2024-10-03 | | 4 | 田中太郎 | 東京都中央区1-1-1 | 掃除機 | 15000 | 2024-10-04 |
- データの不整合の確認
商品テーブル
| 商品ID| 商品名 | 価格 | |-------|---------|-------| | 1 | テレビ | 50000 | | 2 | 冷蔵庫 | 100000| | 3 | 洗濯機 | 30000 |
注文履歴テーブル
| 注文ID| 商品ID| 商品名 | 価格 | 注文日 | |-------|-------|---------|-------|------------| | A | 1 | テレビ | 50000 | 2024-10-01 | | B | 2 | 冷蔵庫 | 95000 | 2024-10-02 | | C | 3 | 洗濯機 | 31000 | 2024-10-03 |
キーワードとその説明文
- リレーショナルデータベース
データをテーブル形式で保存し，テーブル間を関連で結ぶデータベース方式である．データの整合性保持機能や柔軟な問い合わせ機能を持ち，銀行や商店，交通機関などで広く利用されている．
- テーブル
データベースにおける基本的なデータ保存形式であり，属性（列）とそれに対応する具体的なデータの集まり（行）から構成される表形式の構造である．例えば商品テーブルでは商品ID，商品名，単価などの属性を持つ．
- データの整合性
データベース内のデータが矛盾なく，一貫性を保った状態を指す．リレーショナルデータベースは，誤ったデータや矛盾したデータが保存されるのを防ぐ機能を有している．これにより，データの信頼性が確保される．
- SQL（Structured Query Language）
データベースを操作するための専用言語であり，複雑な検索やデータの抽出を可能にする．SELECT文やDISTINCT句，INTO句などの命令を使用してテーブルの操作を行う．複雑な検索やデータの抽出を可能にし，結合，集計，ソート，副問い合わせなどの高度な操作も可能である．
- 冗長なデータ
データベース内で不必要に重複して保存されるデータを指す．例えば，同じ商品の価格が複数のレコードに重複して保存される場合がこれに該当する．データの更新時に全ての重複箇所を更新する必要があり，更新漏れによる不整合のリスクがある．
- データの不整合
データベース内で矛盾した情報が存在する状態を指す．例えば，同一商品の価格が異なる値で保存される場合や，生徒の所属クラスが複数の値で記録される場合などが該当する．
- 異状（いじょう）
データベースの設計が不適切な場合に，データの操作（追加，更新，削除）時に起こる予期しない問題や振る舞いを指す．主にデータの冗長性が原因となって発生し，データの不整合につながる可能性がある．
- 正規化
データベースのテーブルを適切な形に再構成することで，データの冗長性を排除し，データの整合性を向上させるプロセスである．不整合を防ぐための必須ステップだが，過度な正規化はテーブル数の増加や複雑化を招く可能性がある．
- テーブルの分割
1つのテーブルを2つ以上のテーブルに分割する操作である．冗長なデータを排除する正規化の実施や，問い合わせ（クエリ）の性能向上を目的として行われる．
- テーブル結合
複数のテーブル間の関連に基づいて複数のテーブルを1つにまとめる操作である．ONキーワードと組み合わせて結合条件を指定し，関連するデータを統合して表示することができる．
- SELECT文
問い合わせの基本となる命令で，取得したいデータを指定するために使用する．アスタリスク（*）を使用すると全データを取得でき，特定の属性名を指定することで必要な列のみを取得できる．
- 重複行除去（DISTINCT）
SELECT文と組み合わせて使用し，重複するデータを除外して結果を表示する機能である．指定した列の値が重複する行を1つにまとめることができる．
- INTO句
Accessデータベースにおいて，SQL実行結果を新しいテーブルとして保存するための句である．テーブルの分割操作などで使用される．
4. SQL入門：SQLFiddleを活用したデータベース操作の基礎と応用
【資料】
- PDFファイル: d4.pdf
- パワーポイントファイル（PDFファイルと同じ内容）: d4.pptx
- HTML （PDFファイルと同じ内容）: d4.html
- 演習用のデータベースファイル: db4-4.accdb
【概要】
SQL（Structured Query Language）は，データベースを操作するための言語であり，データの検索や加工を可能にする．SQLの基本機能として，SELECT（必要なデータの取得），FROM（対象テーブルの指定），WHERE（取得したいデータの条件指定）がある．高度な操作として，テーブル結合（JOIN）によって複数のテーブルを結合できる．例えば，従業員テーブルと部署テーブルを結合すれば，従業員の名前と所属部署の名前を一つの結果として取得することが可能である．グループ化（GROUP BY）や集計関数を用いて統計的な分析を行うことができる．SQLを学ぶために、SQLFiddleのようなオンラインツールを活用すれば，データベースソフトウェアのインストールなしに，ウェブブラウザだけで実践的なSQLスキルを身につけることができる．
【関連する外部ページ】
SQLFiddle: http://sqlfiddle.com/
演習
- 演習１．SQLFiddle を用いたSQL の実行
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う。SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
create table 朝食と値段 ( 名前 text, 朝食 text, 値段 integer ); insert into 朝食と値段 values ('A', 'カレーライス', 400); insert into 朝食と値段 values ('B', 'うどん', 250); insert into 朝食と値段 values ('C', 'カレーライス', 400); select * from 朝食と値段; - 演習２．テーブル定義とデータの追加
SQLFiddle のURL: http://sqlfiddle.com/
SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 従業員 ( id INTEGER, name TEXT, age INTEGER, salary INTEGER, department_id INTEGER); CREATE TABLE 部署 ( id INTEGER, name TEXT); INSERT INTO 従業員 VALUES (1, 'Alice', 30, 50000, 1); INSERT INTO 従業員 VALUES (2, 'Bob', 40, 60000, 1); INSERT INTO 従業員 VALUES (3, 'Charlie', 35, 70000, 2); INSERT INTO 部署 VALUES (1, 'HR'); INSERT INTO 部署 VALUES (2, 'Engineering'); select * from 従業員; select * from 部署;次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT age FROM 従業員; SELECT * FROM 従業員 WHERE age = 30;
- 演習３．種々の問い合わせ（クエリ）
SQLFiddle のURL: http://sqlfiddle.com/
- SELECT（データの検索・加工や射影）
SELECTは、データベースからデータを取得するための基本。
SELECT * FROM 従業員; SELECT name, age FROM 従業員;
- FROM（問い合わせ対象テーブルの指定）
FROMは、問い合わせ（クエリ）が対象とするテーブルを指定する。
SELECT name FROM 従業員; SELECT name FROM 部署;
- WHERE（選択）
WHEREは、特定の条件に一致する行を選択するために使う。
SELECT * FROM 従業員 WHERE age > 30;
- JOIN、ON（結合、結合条件）
関係のあるテーブルを、結合条件を指定して１つに結合する。
SELECT 従業員.name, 部署.name FROM 従業員 JOIN 部署 ON 従業員.department_id = 部署.id; - DISTINCT（重複行の除去）
SELECT DISTINCT department_id FROM 従業員;
- COUNT（行数のカウント）
COUNTは、行数をカウントする。
SELECT COUNT(*) FROM 従業員;
- AVG、MAX、MIN、SUM（平均、最大、最小、合計の計算）
これらは、数値データに対する平均、最大、最小、合計を計算する。
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM 従業員;
- GROUP BY（属性でグループ化）
GROUP BY は、指定した属性についてグループ化する。
SELECT department_id, COUNT(*) FROM 従業員 GROUP BY department_id; - ORDER BY（並べ替え（ソート））
ORDER BY は、指定した属性についてソートする。
SELECT name, salary FROM 従業員 ORDER BY salary DESC; - 副問い合わせ
INや括弧()は、SQLでさまざまな用語があるが、問い合わせ内に別の問い合わせを含めるときにも使える。 これは、ある問い合わせの結果を別の問い合わせのために使うもの。
SELECT * FROM 従業員 WHERE salary > (SELECT AVG(salary) FROM 従業員);
- SELECT（データの検索・加工や射影）
キーワードとその説明文
- リレーショナルデータベース
データをテーブル形式で保存し，テーブル間を関連で結ぶデータベース方式である．データの整合性保持機能や柔軟な問い合わせ機能を持ち，銀行や商店，交通機関などで広く利用されている．
- テーブル
データベースにおける基本的なデータ保存形式であり，属性（列）とそれに対応する具体的なデータの集まり（行）から構成される表形式の構造である．例えば商品テーブルでは商品ID，商品名，単価などの属性を持つ．
- SQL（Structured Query Language）
データベースを操作するための専用言語であり，複雑な検索やデータの抽出を可能にする．SELECT文やDISTINCT句，INTO句などの命令を使用してテーブルの操作を行う．複雑な検索やデータの抽出を可能にし，結合，集計，ソート，副問い合わせなどの高度な操作も可能である．
- SQLFiddle
Webブラウザ上でSQLを実行できるオンラインツールである．インストールが不要でアクセスが手軽であり，自分の都合に合わせて練習できる環境を提供する．ただし，秘密情報は扱わないよう注意が必要である．
- 問い合わせ（クエリ）
データベースから必要なデータを検索，加工するための指令である．SELECT，FROM，WHEREなどの多様なコマンドを組み合わせることで，複雑なデータ抽出や加工が可能となる．
- SELECT文
問い合わせの基本となる命令で，取得したいデータを指定するために使用する．アスタリスク（*）を使用すると全データを取得でき，特定の属性名を指定することで必要な列のみを取得できる．
- FROM句
select文でデータを取得する対象となるテーブルを指定するための句である．基本的な構文は「from テーブル名」となり，複数のテーブルを指定することも可能である．
- WHERE句
特定の条件を満たす行を選択するための句である．例えば「where 単価 > 80」とすることで，単価が80より大きい商品のみを取得できる．
- テーブル結合
複数のテーブル間の関連に基づいて複数のテーブルを1つにまとめる操作である．ONキーワードと組み合わせて結合条件を指定し，関連するデータを統合して表示することができる．
- データ型
属性に設定される値の種類を定義するものである．主な種類として，整数（INTEGER），テキスト（TEXT），日付/時刻（DATETIME），Yes/No（BIT, BOOL）などがある．短いテキストは半角255文字分までが目安とされる．
- INSERT INTO
テーブルに新しい行（データ）を追加するためのコマンドである．VALUES句と組み合わせて使用し，テーブルの各列に対応する値を指定することができる．
- CREATE TABLE
新しいテーブルを定義するためのコマンドである．テーブル名，属性名，データ型を指定し，必要に応じてデータの整合性を保つための制約を設定することができる．
- 重複行除去（DISTINCT）
SELECT文と組み合わせて使用し，重複するデータを除外して結果を表示する機能である．指定した列の値が重複する行を1つにまとめることができる．
- グループ化（GROUP BY）
指定した属性でデータをグループ化する機能である．COUNT，AVG，MAXなどの集計関数と組み合わせることで，グループごとの統計情報を得ることができる．
- 集計関数
データの集計を行うための関数群である．COUNT（行数のカウント），AVG（平均値），MAX（最大値），MIN（最小値），SUM（合計値）などがあり，数値データの分析に使用される．
- 並べ替え（ORDER BY）
データを特定の属性の値に基づいて昇順または降順に整列させる操作である．order by句を使用して実現され，データの分析や表示に活用される．
- 副問い合わせ
SQL文の中に別のSQL文を埋め込む機能である．WHERE句やFROM句の中で使用でき，ある問い合わせの結果を別の問い合わせの条件として利用することができる．
5. SQL基礎：SELECT文による効率的なデータ検索と操作の基本
【資料】
- PDFファイル: spsqlintro.pdf
- パワーポイントファイル（PDFファイルと同じ内容）: d4.pptx
- HTML （PDFファイルと同じ内容）: spsqlintro.html
【概要】
リレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの管理と検索を行う．基本的なSQL問い合わせは，SELECT（取得する列の指定），FROM（対象テーブルの指定），WHERE（条件指定）の組み合わせで構成される．例えば、SELECT * FROM T;はテーブルTの全てのデータを取得し，SELECT a, b FROM T WHERE b > 80;は特定の条件（b > 80）を満たすデータだけを選択する．WHERE句では比較演算子，BETWEEN，IN，LIKEなどを使用して条件を指定できる．DISTINCTキーワードにより重複行を除去（例：SELECT DISTINCT a FROM T;）できる．SELECT * FROM T WHERE c LIKE '%階';でのパターンマッチングなど，多彩な方法で問い合わせ（クエリ）を行うことができる．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．テーブル定義とデータの追加（SQLFiddle を使用）
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う。SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 記録 ( 名前 TEXT, 得点 INTEGER, 居室 TEXT); INSERT INTO 記録 VALUES('徳川家康', 85, '1階'); INSERT INTO 記録 VALUES('源義経', 78, '2階'); INSERT INTO 記録 VALUES('西郷隆盛', 90, '3階'); INSERT INTO 記録 VALUES('豊臣秀吉', 82, '1階'); INSERT INTO 記録 VALUES('織田信長', 75, '2階'); select * from 記録;次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
select 名前 from 記録; select 得点 from 記録;
- 演習２．SQLによる問い合わせ（クエリ）（SQLFiddle を使用）
SQLFiddle のURL: http://sqlfiddle.com/
今まで使っていたSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
create table 記録 ( 名前 text, 得点 integer, 居室 text ); insert into 記録 values ('徳川家康', 85, '1階'); insert into 記録 values ('源義経', 79, '2階'); insert into 記録 values ('西郷隆盛', 90, '3階'); insert into 記録 values ('豊臣秀吉', 82, '1階'); insert into 記録 values ('織田信長', 75, '2階'); select 居室 from 記録; select distinct 居室 from 記録;次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
select 名前, 得点 from 記録 where 得点 > 80; select 名前, 得点 from 記録 where 得点 between 80 and 85;
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
select avg(得点) from 記録;
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
select * from 記録 where 居室 in ('1階', '2階');
6. テーブルの結合: JOIN、結合と SQL 問い合わせ
【資料】
【概要】
リレーショナルデータベースは，テーブル間には関連がある．この関連を活用するために，結合という操作が重要な役割を果たす．テーブルの結合は，異なるテーブルを一つにまとめる操作である．SQLではINNER JOINとON句を使用してテーブルの結合を行うことができる．例えば，商品テーブルと購入テーブルを結合する場合，「SELECT * FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号」のように記述する．ON句では結合条件を指定し，通常は2つのテーブルの特定の属性同士の値が等しいという条件を設定する．複雑な条件も指定できる．結合を活用することで，複数のテーブルに分散したデータを統合し，より有用な情報を得ることができる．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．テーブル定義とデータの追加
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); select * FROM 商品; - 演習２．SQL による結合
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( 購入者 TEXT, 商品番号 INTEGER); INSERT INTO 購入 VALUES('X', 1); INSERT INTO 購入 VALUES('X', 3); INSERT INTO 購入 VALUES('Y', 2); SELECT * FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号;次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 商品名, 購入者 FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号;
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT COUNT(*) FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号;
- 演習３．複数の条件の指定
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( 購入者 TEXT, 商品番号 INTEGER); INSERT INTO 購入 VALUES('X', 1); INSERT INTO 購入 VALUES('X', 3); INSERT INTO 購入 VALUES('Y', 2); SELECT * FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号 WHERE 購入.購入者 = 'X'; SELECT 商品名, 購入者, 単価 FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号 WHERE 購入.購入者 = 'X';次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 商品名, 購入者, 単価 FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品番号 WHERE 購入.購入者 = 'Y';
- 演習４．結合条件のない結合
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( 購入者 TEXT, 商品番号 INTEGER); INSERT INTO 購入 VALUES('X', 1); INSERT INTO 購入 VALUES('X', 3); INSERT INTO 購入 VALUES('Y', 2); SELECT * FROM 商品 INNER JOIN 購入; - 演習５
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
create table 名簿( ID integer, name text, buy integer ); INSERT INTO 名簿 VALUES(1, '織田', 1); INSERT INTO 名簿 VALUES(2, '豊臣', 2); INSERT INTO 名簿 VALUES(3, '徳川', 2); create table 食材( ID integer, name text ); INSERT INTO 食材 VALUES(1, 'とうふ'); INSERT INTO 食材 VALUES(2, '納豆'); SELECT * FROM 名簿 INNER JOIN 食材; SELECT * FROM 名簿 INNER JOIN 食材 ON 名簿.buy = 食材.ID; SELECT 名簿.name, 食材.name FROM 名簿 INNER JOIN 食材 ON 名簿.buy = 食材.ID; SELECT 名簿.name, 食材.name FROM 名簿 INNER JOIN 食材 ON 名簿.buy = 食材.ID WHERE 食材.name ='とうふ'; SELECT count(*) FROM 名簿 INNER JOIN 食材 ON 名簿.buy = 食材.ID;
7. SQLによるデータ分析：GROUP BYを用いたグループ化と集約
【資料】
【概要】
SQL の機能としてGROUP BY句によるグループ化と集約がある．グループ化は同じ属性値を持つデータを集めることであり，例えば科目ごとや受講者ごとにデータをグループ分けすることで，科目別の平均点や受講者ごとの得点合計を算出するなど，データの分析が容易になる．SQLでは，グループに対して，AVG（平均），MAX（最大），MIN（最小），SUM（合計），COUNT（行数）の集約関数を適用することで，データの傾向や特徴を把握できる．例えば，「SELECT 科目, COUNT(*) FROM 成績 GROUP BY 科目」により科目ごとの行数を得ることができる．グループ化と集約と集約により，売上分析やトレンド分析，顧客セグメント分析などのビジネスインテリジェンスが可能となる．SQL以外のツールを使うときにも役に立つ考え方である．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 成績 ( 科目 TEXT, 受講者 TEXT, 得点 INTEGER); INSERT INTO 成績 VALUES('国語', 'A', 85); INSERT INTO 成績 VALUES('国語', 'B', 90); INSERT INTO 成績 VALUES('算数', 'A', 90); INSERT INTO 成績 VALUES('算数', 'B', 96); INSERT INTO 成績 VALUES('理科', 'A', 95); select * FROM 成績; - 演習２
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 成績 ( 科目 TEXT, 受講者 TEXT, 得点 INTEGER); INSERT INTO 成績 VALUES('国語', 'A', 85); INSERT INTO 成績 VALUES('国語', 'B', 90); INSERT INTO 成績 VALUES('算数', 'A', 90); INSERT INTO 成績 VALUES('算数', 'B', 96); INSERT INTO 成績 VALUES('理科', 'A', 95); SELECT AVG(得点) FROM 成績 WHERE 科目 = '国語';次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT AVG(得点) FROM 成績 WHERE 科目 = '算数';
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT MAX(得点) FROM 成績;
- 演習３
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 成績 ( 科目 TEXT, 受講者 TEXT, 得点 INTEGER); INSERT INTO 成績 VALUES('国語', 'A', 85); INSERT INTO 成績 VALUES('国語', 'B', 90); INSERT INTO 成績 VALUES('算数', 'A', 90); INSERT INTO 成績 VALUES('算数', 'B', 96); INSERT INTO 成績 VALUES('理科', 'A', 95); SELECT 科目, COUNT(*) FROM 成績 GROUP BY 科目;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 成績 ( 科目 TEXT, 受講者 TEXT, 得点 INTEGER); INSERT INTO 成績 VALUES('国語', 'A', 85); INSERT INTO 成績 VALUES('国語', 'B', 90); INSERT INTO 成績 VALUES('算数', 'A', 90); INSERT INTO 成績 VALUES('算数', 'B', 96); INSERT INTO 成績 VALUES('理科', 'A', 95); SELECT 科目, COUNT(*) FROM 成績 WHERE 得点 >= 90 GROUP BY 科目;次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 受講者, COUNT(*) FROM 成績 GROUP BY 受講者;
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 科目, AVG(得点) FROM 成績 GROUP BY 科目;
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 受講者, AVG(得点) FROM 成績 GROUP BY 受講者;
- 演習４
SQLFiddle のURL: http://sqlfiddle.com/
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 売上 ( 日付 DATETIME, 商品 TEXT, 個数 INTEGER, 単価 INTEGER ); INSERT INTO 売上 VALUES('2024-01-01', '商品A', 1, 500); INSERT INTO 売上 VALUES('2024-01-01', '商品A', 2, 500); INSERT INTO 売上 VALUES('2024-01-01', '商品A', 3, 500); INSERT INTO 売上 VALUES('2024-01-01', '商品A', 4, 500); INSERT INTO 売上 VALUES('2024-01-01', '商品B', 1, 1500); INSERT INTO 売上 VALUES('2024-01-01', '商品B', 2, 1500); INSERT INTO 売上 VALUES('2024-01-01', '商品B', 3, 1500); INSERT INTO 売上 VALUES('2024-01-01', '商品B', 4, 1500); INSERT INTO 売上 VALUES('2024-01-02', '商品A', 1, 500); INSERT INTO 売上 VALUES('2024-01-02', '商品A', 2, 500); INSERT INTO 売上 VALUES('2024-01-02', '商品A', 3, 500); INSERT INTO 売上 VALUES('2024-01-02', '商品A', 4, 500); INSERT INTO 売上 VALUES('2024-01-02', '商品B', 1, 1500); INSERT INTO 売上 VALUES('2024-01-02', '商品B', 2, 1500); INSERT INTO 売上 VALUES('2024-01-02', '商品B', 3, 1500); INSERT INTO 売上 VALUES('2024-01-02', '商品B', 4, 1500); INSERT INTO 売上 VALUES('2024-01-03', '商品A', 1, 500); INSERT INTO 売上 VALUES('2024-01-03', '商品A', 2, 500); INSERT INTO 売上 VALUES('2024-01-03', '商品A', 3, 500); INSERT INTO 売上 VALUES('2024-01-03', '商品A', 4, 500); INSERT INTO 売上 VALUES('2024-01-03', '商品B', 1, 1500); INSERT INTO 売上 VALUES('2024-01-03', '商品B', 2, 1500); INSERT INTO 売上 VALUES('2024-01-03', '商品B', 3, 1500); INSERT INTO 売上 VALUES('2024-01-03', '商品B', 4, 1500); SELECT 日付, SUM(個数 * 単価) FROM 売上 GROUP BY 日付;次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT SUM(個数 * 単価) FROM 売上 WHERE 商品 = '商品A’;
次を追記する（いまのSQLを消さずに、次のSQLを書き加える）．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 日付, 商品, SUM(個数 * 単価) FROM 売上 GROUP BY 日付, 商品;
8. 並べ替え（ソート）
【資料】
- PDFファイル: d6.pdf
- パワーポイントファイル（PDFファイルと同じ内容）: d6.pptx
- HTML （PDFファイルと同じ内容）: d6.html
- 演習用のデータベースファイル: db4-4.accdb
【概要】
リレーショナルデータベースはデータをテーブルと呼ばれる表形式で扱う．SQLによる問い合わせでの並べ替え（ソート）は，「SELECT 列名 FROM テーブル名 ORDER BY 並べ替えの属性;」を基本形式とし，昇順は指定なし，降順は「DESC」を付ける．並べ替え操作は昇順・降順の指定，複数属性の使用，選択や集計との組み合わせなど，様々なバリエーションが可能であり，データ分析の目的に応じて活用できる．例えば，「SELECT 母国, COUNT() FROM データ GROUP BY 母国 ORDER BY COUNT();」では集計結果の並べ替えを行うもので，データ傾向の把握に有効である．
【演習】
Accessデータベースファイル
SELECT *
FROM 米国成人調査データ
ORDER BY 年齢;
SELECT *
FROM 米国成人調査データ
ORDER BY 年齢 DESC;
SELECT *
FROM 米国成人調査データ
ORDER BY 教育年数;
SELECT *
FROM 米国成人調査データ
ORDER BY 教育年数 DESC;
SELECT *
FROM 米国成人調査データ
ORDER BY 年齢, 教育年数;
SELECT *
FROM 米国成人調査データ
ORDER BY 年齢 DESC, 教育年数 DESC;
SELECT *
FROM 米国成人調査データ
WHERE 年齢 > 80
ORDER BY 年齢;
SELECT *
FROM 米国成人調査データ
WHERE 年齢 > 80
ORDER BY 年齢 DESC;
SELECT *
FROM 米国成人調査データ
WHERE 年齢 BETWEEN 20 AND 30
ORDER BY 年齢;
SELECT 母国, COUNT(*)
FROM 米国成人調査データ
GROUP BY 母国
ORDER BY COUNT(*);
9. SQLにおける副問い合わせと論理演算子（AND, OR）の基礎
【資料】
【概要】
SQLの副問い合わせ，IN演算子，論理演算子ANDとORは，データベースクエリの柔軟性向上に役立つ．副問い合わせは，他の問い合わせ内に埋め込まれた問い合わせで，別のSQL問い合わせ内に埋め込まれた問い合わせであり，複雑なデータ抽出を可能にする．例えば「SELECT 受講者 FROM 成績 WHERE 得点 = (SELECT MAX(得点) FROM 成績)」のように，最高得点の受講者を取得できる．IN演算子は複数の値のいずれかに一致するかをテストするものであり，「WHERE 科目 IN ('国語', '算数')」のように条件指定ができる．論理演算子のANDは両方の条件が成立する場合に真となり，ORはいずれかの条件が成立する場合に真となる．これらを活用することで，効率的なデータ検索や条件指定が可能になり，複雑な問い合わせも実現できる．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．テーブル定義とデータの追加、主キー制約
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', 400); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー;
- 演習２．範囲指定
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', NULL); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー;
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', NULL); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー WHERE 単価 IS NULL;
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', NULL); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー WHERE 単価 >= 0;
- 演習３．商品テーブルと申し込みテーブルは、関連付けられている
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 商品;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 申し込み;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 申し込み INNER JOIN 商品 ON 申し込み.商品番号 = 商品.id;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT 申し込み.日時,申し込み.氏名,申し込み.個数 * 商品.単価 FROM 申し込み INNER JOIN 商品 ON 申し込み.商品番号 = 商品.id;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT 氏名, SUM(個数 * 商品.単価) FROM 申し込み INNER JOIN 商品 ON 申し込み.商品番号 = 商品.id GROUP BY 氏名;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 商品 WHERE 単価 > (SELECT AVG(単価) FROM 商品); - 発展演習１．商品の単価の最大値を得るSQL
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT MAX(単価) FROM 商品; - 発展演習２．Xによる申し込み
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 申し込み WHERE 氏名 = 'X'; - 発展演習３．商品Aを申し込んだ人の取得
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT DISTINCT(申し込み.氏名) FROM 申し込み INNER JOIN 商品 ON 申し込み.商品番号 = 商品.id WHERE 商品.商品名 = '商品A'; - 発展演習４．氏名別の申し込み数の計算
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT 氏名, COUNT(*) AS 申し込み数 FROM 申し込み GROUP BY 氏名; - 発展演習５．
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT SUM(申し込み.個数) FROM 申し込み INNER JOIN 商品 ON 申し込み.商品番号 = 商品.id WHERE 商品.商品名 = '商品B'; - 演習４．
SQLFiddle のURL: http://sqlfiddle.com/
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); UPDATE 商品 SET 単価 = 120 WHERE 商品名 = '商品A'; SELECT * FROM 商品;CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); DELETE FROM 商品 WHERE 商品名 = '商品C'; SELECT * FROM 商品; - 発展演習６．
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); UPDATE 商品 SET 単価 = 1000 WHERE 商品名 = '商品B';
10. データベース設計の基礎：主キー・外部キーを用いたテーブル間の関連付けとSQL実践
【資料】
【概要】
データベース設計では，主キーと外部キーを用いて，テーブル間の適切な関連付けを行う．主キーはテーブルの各行を一意に識別し，同一IDの重複を防止する不変のキーである．外部キーは他のテーブルの主キーを参照することでテーブル間の関連を示すものである．参照整合性制約はこれらの関連付けられたデータの一貫性を保証するためのもので，外部キーの値が，参照先のテーブルの主キーのいずれかの値であることを保証する．それにより，データの信頼性を向上させ，誤ったデータの混入を防ぐ．データベース設計では，テーブル名の決定，属性の設定，データ型の選択，制約の設定，索引の作成，テーブル間の関係性の定義を行う．適切なデータベース設計のよって，複雑なデータも扱いやすくなる．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); select * FROM 商品;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); INSERT INTO 商品 VALUES(1, 'いちご', 1000); - 発展演習１．
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 成績 ( 学生ID INTEGER, 科目ID INTEGER, 得点 INTEGER, PRIMARY KEY(学生ID, 科目ID)); INSERT INTO 成績 VALUES(1, 1001, 90); INSERT INTO 成績 VALUES(1, 1002, 100); INSERT INTO 成績 VALUES(2, 1001, 85); INSERT INTO 成績 VALUES(2, 1002, 90); INSERT INTO 成績 VALUES(2, 1003, 95); select * FROM 成績;
- 発展演習２．
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 成績 ( 学生ID INTEGER, 科目ID INTEGER, 得点 INTEGER, PRIMARY KEY(学生ID, 科目ID)); INSERT INTO 成績 VALUES(1, 1001, 90); INSERT INTO 成績 VALUES(1, 1002, 100); INSERT INTO 成績 VALUES(2, 1001, 85); INSERT INTO 成績 VALUES(2, 1002, 90); INSERT INTO 成績 VALUES(2, 1003, 95); INSERT INTO 成績 VALUES(1, 1001, 95); select * FROM 成績;
- 演習２．テーブル定義，主キー，外部キー，参照整合性制約
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); SELECT * FROM 商品;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10); INSERT INTO 購入 VALUES(2, 'Y', 2, 5); SELECT * FROM 購入;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10); INSERT INTO 購入 VALUES(2, 'Y', 2, 5); SELECT 購入.購入者, 商品.商品名, 商品.単価 * 購入.数量 FROM 購入 INNER JOIN 商品 ON 購入.商品ID = 商品.ID; - 発展演習３
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10); INSERT INTO 購入 VALUES(2, 'Y', 2, 5); INSERT INTO 購入 VALUES(3, 'X', 22, 1); - 発展演習４
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し CREATE TABLE 学生 ( 学生ID INTEGER PRIMARY KEY, 名前 TEXT, 専攻 TEXT ); CREATE TABLE 講義 ( 講義ID INTEGER PRIMARY KEY, 講義名 TEXT, 担当教員 TEXT ); CREATE TABLE 成績 ( 学生ID INTEGER, 講義ID INTEGER, 成績 INTEGER, PRIMARY KEY (学生ID, 講義ID), FOREIGN KEY (学生ID) REFERENCES 学生(学生ID), FOREIGN KEY (講義ID) REFERENCES 講義(講義ID) ); INSERT INTO 学生 VALUES (1, '山田太郎', '情報工学'); INSERT INTO 学生 VALUES (2, '鈴木花子', '数学'); INSERT INTO 学生 VALUES (3, '佐藤次郎', '物理学'); INSERT INTO 学生 VALUES (4, '田中美咲', '情報工学'); INSERT INTO 学生 VALUES (5, '中村匠', '数学'); INSERT INTO 講義 VALUES (1, 'データベース基礎', '加藤教授'); INSERT INTO 講義 VALUES (2, '線形代数', '木村教授'); INSERT INTO 講義 VALUES (3, 'プログラミング入門', '山本教授'); INSERT INTO 講義 VALUES (4, '確率統計', '木村教授'); INSERT INTO 成績 VALUES (1, 1, 85); INSERT INTO 成績 VALUES (1, 2, 78); INSERT INTO 成績 VALUES (1, 3, 92); INSERT INTO 成績 VALUES (2, 1, 90); INSERT INTO 成績 VALUES (2, 2, 95); INSERT INTO 成績 VALUES (3, 1, 82); INSERT INTO 成績 VALUES (3, 3, 88); INSERT INTO 成績 VALUES (4, 1, 86); INSERT INTO 成績 VALUES (4, 2, 83); INSERT INTO 成績 VALUES (5, 2, 91); SELECT 学生.名前, 講義.講義名, 成績.成績 FROM 成績 INNER JOIN 学生 ON 成績.学生ID = 学生.学生ID INNER JOIN 講義 ON 成績.講義ID = 講義.講義ID;
11. データベース操作とトランザクション管理：データ整合性と永続性
【資料】
【概要】
データベースには，データ整合性を維持するために主キー制約，参照整合性制約などの仕組みが備わっており，誤ったデータや矛盾したデータが保存されるのを防いでいる．また，リレーショナルデータベースの運用では，複数のデータベース操作を一連の単一処理として扱うトランザクション機能が重要である．トランザクションによりデータの一貫性と信頼性が確保される．データの永続性機能により，一度コミットされたデータは確実に保存され，システム障害が発生しても失われることがない．問題発生時にはロールバック機能によりトランザクション開始時点へのデータ状態の復元が可能であり，全変更を取り消して整合性を維持できる．これらの機能が組み合わさることで，信頼性の高いデータ管理が実現される．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．テーブル定義，主キー，外部キー，参照整合性制約
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); SELECT * FROM 商品;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10); INSERT INTO 購入 VALUES(2, 'Y', 2, 5); SELECT * FROM 購入;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10); INSERT INTO 購入 VALUES(2, 'Y', 2, 5); SELECT 購入.購入者, 商品.商品名, 商品.単価 * 購入.数量 FROM 購入 INNER JOIN 商品 ON 購入.商品ID = 商品.ID; - 発展演習１．
今までのSQL は消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 単価 FROM 商品 WHERE 商品名 = 'みかん';
- 発展演習２．
今までのSQL は消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT SUM(商品.単価 * 購入.数量) FROM 購入 INNER JOIN 商品 ON 購入.商品ID = 商品.ID WHERE 購入者 = 'X';
- 発展演習３．
今までのSQL は消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT 購入.購入者 FROM 購入 INNER JOIN 商品 ON 購入.商品ID = 商品.ID WHERE 商品.商品名 = 'りんご';
- 演習２．データベース操作の実践
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); SELECT * FROM T;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); UPDATE T SET 料金 = 450 WHERE 昼食 = 'カレーライス'; SELECT * FROM T;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); UPDATE T SET 昼食 = 'ラーメン' WHERE 名前 = 'C'; SELECT * FROM T;今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); DELETE FROM T WHERE 名前 = 'B'; SELECT * FROM T; - 発展演習４．
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
UPDATE T SET 料金 = 300 WHERE 昼食 = 'そば' OR 昼食 = 'うどん';
Tテーブル内で昼食が「そば」または「うどん」であるすべての行の料金を300円に更新します。
- 発展演習５．
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
INSERT INTO T VALUES ('E', '天ぷら', 500);
12. 中間まとめ：効率的なデータ管理と分析の実践
【資料】
【概要】
リレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの検索と加工を行う．属性（列）はデータの種類に対応し，行は具体的なデータの並びとなる．SQLはSELECT, FROM, WHERE，INNER JOIN，ON, GROUP BY, ORDER BY，IN など多様なコマンドを使用して結合，集計，ソート，副問い合わせなどの高度な操作ができる．各テーブルに対して適切な制約を設定することでデータの整合性を保持する．NULLはデータ化できないものを表す特別な値で，未定，未知，存在しないなどの状態を記録するために使用される．これらの機能を利用することで，データの保管，検索・操作ができ，さまざまな意思決定をサポートすることができる．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．テーブル定義とデータの追加、主キー制約
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', 400); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー;
- 演習２．
１つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', NULL); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー;
２つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', NULL); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー WHERE 単価 IS NULL;
３つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE メニュー ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO メニュー VALUES(1, 'かき氷', 400); INSERT INTO メニュー VALUES(2, 'カレーライス', NULL); INSERT INTO メニュー VALUES(3, 'サイダー', 200); SELECT * FROM メニュー WHERE 単価 >= 0;
- 演習３．
商品テーブルと申し込みテーブルは、関連付けられている
１つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 商品;２つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 申し込み;３つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 申し込み JOIN 商品 ON 申し込み.商品番号 = 商品.id;４つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT 申し込み.日時,申し込み.氏名,申し込み.個数 * 商品.単価 FROM 申し込み JOIN 商品 ON 申し込み.商品番号 = 商品.id;５つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT 氏名, SUM(個数 * 商品.単価) FROM 申し込み JOIN 商品 ON 申し込み.商品番号 = 商品.id GROUP BY 氏名;
６つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
>CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 商品 WHERE 単価 > (SELECT AVG(単価) FROM 商品); - 発展演習１．商品の単価の最大値を得るSQL
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT MAX(単価) FROM 商品; - 発展演習２．Xによる申し込み
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT * FROM 申し込み WHERE 氏名 = 'X'; - 発展演習３．商品Aを申し込んだ人の取得
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT DISTINCT(申し込み.氏名) FROM 申し込み INNER JOIN 商品 ON 申し込み.商品番号 = 商品.id WHERE 商品.商品名 = '商品A'; - 発展演習４．氏名別の申し込み数の計算
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT 氏名, COUNT(*) AS 申し込み数 FROM 申し込み GROUP BY 氏名; - 発展演習５．
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); CREATE TABLE 申し込み ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 日時 DATETIME, 氏名 TEXT, 商品番号 INTEGER, 個数 INTEGER, FOREIGN KEY (商品番号) REFERENCES 商品(id) ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 2, 10); INSERT INTO 申し込み VALUES (NULL, NOW(), 'Y', 2, 5); INSERT INTO 申し込み VALUES (NULL, NOW(), 'X', 1, 1); SELECT SUM(申し込み.個数) FROM 申し込み JOIN 商品 ON 申し込み.商品番号 = 商品.id WHERE 商品.商品名 = '商品B'; - 演習４
１つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); UPDATE 商品 SET 単価 = 120 WHERE 商品名 = '商品A'; SELECT * FROM 商品;２つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); DELETE FROM 商品 WHERE 商品名 = '商品C'; SELECT * FROM 商品; - 発展演習６．
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, 商品名 TEXT, 単価 INTEGER ); INSERT INTO 商品 VALUES (NULL, '商品A', 100); INSERT INTO 商品 VALUES (NULL, '商品B', 200); INSERT INTO 商品 VALUES (NULL, '商品C', 150); UPDATE 商品 SET 単価 = 1000 WHERE 商品名 = '商品B';
13. データ管理の基礎：オンライントランザクションとデータウェアハウスの特徴と活用
【資料】
【概要】
リレーショナルデータベースを用いたデータ管理・処理方法には2つのアプローチがある．１つはリアルタイムでのデータ処理を行うオンライントランザクションであり，銀行取引や予約システムなどリアルタイム処理に適し，最新データの維持と迅速な処理に適する．オンライントランザクションシステムでは，正規化によりデータの冗長性を排除し，データの整合性を確保する．もう１つは履歴データを重視するデータウェアハウスであり，長期的なデータ分析に適する．履歴データは，各行に日時情報を付加し，データ変化があるたびに新しい行を挿入する形で履歴を保持する．一度保存したデータは削除せずに，過去のデータの履歴を保持する．データウエアハウスは，時間軸での分析や傾向把握が簡単にでき，意思決定を支援する基盤となる．
【関連する外部ページ】 http://sqlfiddle.com/
【演習】
- 演習１．テーブル定義とデータの追加、主キー制約
https://sqlfiddle.com/で「MySQL」を選ぶ
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); select * FROM 商品; - 演習２．外部キー，参照整合性制約
https://sqlfiddle.com/で「MySQL」を選ぶ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10); INSERT INTO 購入 VALUES(2, 'Y', 2, 5); select * FROM 商品; select * FROM 購入; - 演習３．now() による現在日時の取得
https://sqlfiddle.com/で「MySQL」を選ぶ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
select now();
- 演習４．日時を扱うテーブル
https://sqlfiddle.com/で「MySQL」を選ぶ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-01-04 09:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-01-04 10:00:00'); select * FROM 商品; select * FROM 購入; - 演習５．データウエアハウス
https://sqlfiddle.com/で「MySQL」を選ぶ
１つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); select * FROM 商品; select * FROM 購入;２つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT * FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品ID;３つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT 購入.購入日時, 購入.購入者, 購入.数量 * 商品.単価 FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品ID;４つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT 購入.購入者, SUM(購入.数量 * 商品.単価) FROM 商品 INNER JOIN 購入 ON 商品.ID = 購入.商品ID GROUP BY 購入.購入者;５つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT 商品名, MAX(改訂日時) FROM 商品 GROUP BY 商品名;６つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT 商品名, 単価, 改訂日時 FROM 商品 WHERE (商品名, 改訂日時) IN ( SELECT 商品名, MAX(改訂日時) FROM 商品 GROUP BY 商品名); - 発展演習
１つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT * FROM 購入 WHERE 購入者 = 'Y';２つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT DISTINCT(購入.購入者) FROM 購入 INNER JOIN 商品 ON 購入.商品ID = 商品.ID WHERE 商品.商品名 = 'りんご';３つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER, 改訂日時 DATETIME); INSERT INTO 商品 VALUES(1, 'みかん', 50, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(2, 'りんご', 100, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(3, 'メロン', 500, '2024-12-01 09:00:00'); INSERT INTO 商品 VALUES(4, 'りんご', 150, '2025-01-01 09:00:00'); INSERT INTO 商品 VALUES(5, 'メロン', 400, '2025-01-01 09:00:00'); CREATE TABLE 購入 ( ID INTEGER PRIMARY KEY, 購入者 TEXT, 商品ID INTEGER, 数量 INTEGER, 購入日時 DATETIME, FOREIGN KEY (商品ID) REFERENCES 商品(ID)); INSERT INTO 購入 VALUES(1, 'X', 1, 10, '2024-12-10 10:00:00'); INSERT INTO 購入 VALUES(2, 'Y', 2, 5, '2024-12-20 12:00:00'); INSERT INTO 購入 VALUES(3, 'Y', 4, 20, '2025-01-05 09:00:00'); INSERT INTO 購入 VALUES(4, 'Z', 5, 3, '2025-01-05 11:00:00'); SELECT 購入者, COUNT(*) FROM 購入 GROUP BY 購入者;
14. データベース設計演習，正規化
【資料】
【概要】
データベース設計は，データベースの基本構造を定める重要なプロセスであり，テーブル名，属性，データ型，制約，索引，テーブル間の関係性などを定める．データベース設計において，正規化はデータベースの構造を最適化し，冗長性を減らすことで「異状」を防ぎ，効率的なデータ管理を実現する手法である．これらの操作において重要となるのが情報無損失の原則であり，正規化によるテーブルを分割を行っても，分割結果を結合することにより元の情報を完全に復元できることを保証する．正規化は，情報無損失のもと，データの冗長性を減少させるようにテーブルを分割することで行う．このプロセスは，「SELECT」や「CREATE TABLE ... AS」などのSQLコマンドを使用して実行できる．以上のように正規化はデータベース設計において重要な役割を果たし，冗長性の削減とデータ整合性の確保を実現する．
【関連する外部ページ】
【演習】
- 演習１．テーブル定義とデータの追加
https://sqlfiddle.com/で「MySQL」を選ぶ
１つめ
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); SELECT * FROM T;２つめ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); SELECT 昼食 FROM T; SELECT DISTINCT 昼食 FROM T; - 発展演習１
今までのSQLは消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
INSERT INTO T VALUES('E', 'ラーメン', 500); - 発展演習２
今までのSQLは消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT * FROM T WHERE 料金 > 300;
- 演習２．テーブルの分解による正規化
https://sqlfiddle.com/で「MySQL」を選ぶ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); CREATE TABLE X AS SELECT DISTINCT 名前, 昼食 FROM T; CREATE TABLE Y AS SELECT DISTINCT 昼食, 料金 FROM T; SELECT * FROM X; SELECT * FROM Y; - 演習３．正規化における情報無損失
https://sqlfiddle.com/で「MySQL」を選ぶ
今までのSQLはすべて消してから演習を行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE T ( 名前 TEXT, 昼食 TEXT, 料金 INTEGER); INSERT INTO T VALUES('A', 'そば', 250); INSERT INTO T VALUES('B', 'カレーライス', 400); INSERT INTO T VALUES('C', 'カレーライス', 400); INSERT INTO T VALUES('D', 'うどん', 250); CREATE TABLE X AS SELECT DISTINCT 名前, 昼食 FROM T; CREATE TABLE Y AS SELECT DISTINCT 昼食, 料金 FROM T; SELECT X.名前, X.昼食, Y.料金 FROM X INNER JOIN Y ON X.昼食 = Y.昼食; - 発展演習３．学生情報のためのテーブル作成
今までのSQLは消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE S ( StudentID INTEGER, StudentName TEXT, Course TEXT ); INSERT INTO S VALUES (1, 'Alice', 'Math'); INSERT INTO S VALUES (1, 'Alice', 'Science'); INSERT INTO S VALUES (2, 'Bob', 'History'); INSERT INTO S VALUES (3, 'Charlie', 'Math'); INSERT INTO S VALUES (3, 'Charlie', 'History'); INSERT INTO S VALUES (3, 'Charlie', 'Science'); SELECT * FROM S;
- 発展演習４．冗長なデータの確認
今までのSQLは消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT StudentID, StudentName FROM S;
- 発展演習５．重複なしの学生情報抽出
今までのSQLは消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
SELECT DISTINCT StudentID, StudentName FROM S;
- 発展演習６．正規化
今までのSQLは消さずに，次のSQLを追加して実行する．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE U AS SELECT DISTINCT StudentID, StudentName FROM S; CREATE TABLE V AS SELECT DISTINCT StudentID, Course FROM S; SELECT * FROM U; SELECT * FROM V;
15. 関数従属性
【資料】
【概要】
関数従属性は属性間の依存関係を表すな概念である．属性Xの値が決まると属性Yの値が一意に決まる場合，「Y はX に従属する」と表現し，X → Yのようにと記述する．種々の正規形は，従属性に基づくものである．第三正規形は，主キー以外のすべての属性が主キーにのみ直接従属することを求め，データの冗長性を減らし，更新時の異状を防ぐ効果がある．主キーは，テーブル内の行を一意に識別するものである．多対多の関連では，両方のテーブルの主キーを参照する中間テーブルを作成することで簡単に取り扱うことができる．
【関連する外部ページ】
【演習】
https://sqlfiddle.com/で「MySQL」を選ぶ
テーブルを分割した後，結合することにより元に戻る場合．
create table T(名前 text, 昼食 text, 料金 integer);
insert into T values('A', 'そば', 250);
insert into T values('B', 'カレーライス', 400);
insert into T values('C', 'カレーライス', 400);
insert into T values('D', 'うどん', 250);
create table A as select distinct 名前, 昼食 from T;
create table B as select distinct 昼食, 料金 from T;
select * from A;
select * from B;
select A.名前, A.昼食, B.料金 from A, B where A.昼食 = B.昼食;
テーブルを分割した後，結合しても元に戻らない場合．
create table T(名前 text, 昼食 text, 料金 integer);
insert into T values('A', 'そば', 250);
insert into T values('B', 'カレーライス', 400);
insert into T values('C', 'カレーライス', 400);
insert into T values('D', 'うどん', 250);
create table A as select distinct 昼食, 料金 from T;
create table B as select distinct 名前, 料金 from T;
select * from A;
select * from B;
select A.昼食, A.料金, B.料金 from A, B where A.料金 = B.料金;
16. インデックス，セキュリティ，データベースの歴史と展望，データベースのバリエーション
【資料】
- PDFファイル: d10new.pdf
- パワーポイントファイル（PDFファイルと同じ内容）: d10new.pptx
- HTML （PDFファイルと同じ内容）: d10new.html
【概要】
【概要】 データベースシステムは，データを効率的に整理・管理・利用するための基盤技術として発展してきた．データベースの主要な種類であるリレーショナルデータベースシステムの特徴は，データをテーブル形式で保存し，SQL言語を用いてデータの管理と検索を行うことである．インデックスは，全データスキャンではなくインデックスのみのスキャンでSQL問い合わせ処理を行うことにより，SQLの効率的な処理を実現する．リレーショナルデータベース管理システムは，データの整合性保持，セキュリティ確保の機能を備え，オンラインでのデータ共有に適している．現在は人工知能との統合，NoSQLなどの新技術が進展し進化している．
【関連する外部ページ】
【演習】
- 演習１．SQLの基本：テーブル定義とデータの追加、主キー制約
最初に画面に出てくるSQLはすべて消してから行う．SQLFiddle への貼り付けは CTRLキーと v キーの同時押し
CREATE TABLE 商品 ( ID INTEGER PRIMARY KEY, 商品名 TEXT, 単価 INTEGER); INSERT INTO 商品 VALUES(1, 'みかん', 50); INSERT INTO 商品 VALUES(2, 'りんご', 100); INSERT INTO 商品 VALUES(3, 'メロン', 500); select * FROM 商品;