データベース工学および演習 第5章 リレーショナルデータベース言語SQL

Slides:



Advertisements
Similar presentations
1 情報基礎 A 第 9 週 プログラミング入門 VBA の基本文法 1 準備・変数・データの入出力 徳山 豪・全 眞嬉 東北大学情報科学研究科 システム情報科学専攻 情報システム評価学分野.
Advertisements

情報基礎 A 第 4 週 データベースと表計算 情報基礎 A 第 4 週 データベースと表計算 1 徳山 豪 東北大学情報科学研究科 システム情報科学専攻 情報システム評価学分野.
プログラミング言語論 第10回(演習) 情報工学科 木村昌臣   篠埜 功.
エンティティ・リレーションシップ・モデル
DB(データベース)のおはなし 作成者:小野正広 DBと言っても、  ドラゴンボール ではないですぞ! 3/1/2017.
SQLite3
情報理工学部 情報システム工学科 ラシキアゼミ 3年 H 井奈波 和也
リレーショナル・データベース データベース論 第10回.
SQL データベースアクセスのための文法と MySQL
3-1 MySQLについて 発表者:藤村元彦 自然言語処理研究室.
MySQLに接続するデータベースプログラム
2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算
6-2 データベース 1.SQLite SQLを単純化した SQLite を使ってデータベースを操作 表「fruit」
第5章 データベースの設計 5.1 データベース設計の概要 5.2 ERモデルとスキーマ設計 5.3 正規化 5.4 一貫性制約.
ACCESSによる データベースアプリケーション開発実習 日本工業大学 情報工学科 “データベースの実際” 教材
SQL J2EE I 第3回 /
地理情報システム論 第3回 コンピュータシステムおける データ表現(1)
データベースS 第3回 クリッカー配布 2015/4/22 A~Jの箱の中から、自分の学生番号に対応するID番号のラベルが貼られたクリッカーを受け取ること。 教壇 A ~ C D ~ F G ~ J A C E G I 学生番号 ID
プログラミング論 II RDBMSとSQL
情報科学1(G1) 2016年度.
RDBMSについて 2年7組  小鹿 慎太郎.
3-2.データを取り出す 2004年 5月20日(木) 01T6074X 茂木啓悟.
第2章 データベースのモデル 2.1 論理表現と3層モデル 2.2 階層モデル 2.3 ネットワークモデル 2.4 関係モデル.
情報基礎A 第14週プログラミング 実際のデータ処理での応用(2)
第7章 データベース管理システム 7.1 データベース管理システムの概要 7.2 データベースの格納方式 7.3 問合せ処理.
情報基礎A 第11週 プログラミング入門 VBAの基本文法3 配列・For~Next
UML入門 UML PRESS vol.1 より 時松誠治 2003年5月19日.
この資料は、テキストをもとに、講義のために作成したものです.学習用に活用してください.
14.テーブル定義,一対多の関係,多対多の関係, 外部キー,索引(インデックス),データベース操作
データベース設計 データベース設計 第0回 オリエンテーション 坂口利裕.
マイクロソフト Access での SQL 演習 第1回 SQL問い合わせ(クエリ)
C++とオブジェクトデータベース入門 8.オブジェクトデータベースとは 森井 喬 Webページ
データベース設計 第2回 データベースモデル(1)
2004/05/13 3-4 データ型(カラムタイプ) について 発表者:藤村元彦 自然言語処理研究室.
SQL パフォーマンス チューニング ~ カバーリングインデックス/クエリヒントの利用~
第2回.リレーショナルデータベース入門 SQL を用いたテーブルへの行の挿入 SQL 問い合わせの発行と評価結果の確認.
第1回.リレーショナルデータベースを使ってみよう
第1回.リレーショナルデータベースを使ってみよう
第2回.リレーショナルデータベース入門 SQL を用いたテーブルへの行の挿入 SQL 問い合わせの発行と評価結果の確認.
朝日大学大学院 経営学研究科 奥山 徹 データベース論 朝日大学大学院 経営学研究科 奥山 徹 2006/05/22 データベース論(6回目)
理学部 情報科学科 指導教官 千葉 滋 助教授 学籍番号 03_03686 内河 綾
マイクロソフト Access での SQL 演習 第5回 副問い合わせ
3-6.インデックスについて 3-7.関数と併用されることの 多いMySQLコマンド
3-3.テーブルを更新する 2004年 4月22日(木) 01T6074X 茂木啓悟.
「Webデータベースの構築技術」正誤表 ページ 項目 誤記 訂正 18 表1.4 アクセス 権限の削除 ・・・テーブル名 TO ユーザ名
コンピュータ概論B ー ソフトウェアを中心に ー #09 データベース (後編)
マイクロソフト Access での SQL 演習 第4回 並べ替え(ソート)
XMLゼミ 1.3 XML文書の表示 1.4 XMLの役割 1.5 XMLとプログラミング M2 正木 裕一.
講義ノート共有データベース NoteTotter?
データベース設計 第8回 クライアント=サーバーモデル(2)
データモデリング モデルの基本作法.
情報システム1及び演習 第一回 データベースの概要.
マイクロソフト Access での SQL 演習 第2回 集計,集約
~let's take fun when you can do it~
3.リレーショナルデータベース,主キー, SQL
情報処理基礎A・B 坂口利裕 横浜市立大学・商学部
E-R図 井上卓也.
再帰CTE を使って遊ぼう 大阪#9 2012/04/14.
関係データベースとSQL MZ Platform講習会(上級).
リレーショナル・データベース J2EE I (データベース論) 第2回 /
アルゴリズムとデータ構造1 2009年6月15日
アルゴリズムとデータ構造 2010年6月17日
第2回.リレーショナルデータベース入門 SQL を用いたテーブルへの行の挿入 SQL 問い合わせの発行と評価結果の確認.
第1回.リレーショナルデータベースを使ってみよう
SQL J2EE I (データベース論) 第3回 /
第3章 関係データベースの基礎 3.1 関係とは 3.2 関係代数.
情報基礎A 第14週プログラミング 実際のデータ処理での応用(2)
SQL データベース論 第11回.
第8章 データベースシステムの発展 8.1 オブジェクトリレーショナルデータベース 8.2 分散データベース 8.3 インターネットとデータベース.
Presentation transcript:

データベース工学および演習 第5章 リレーショナルデータベース言語SQL ジョン ジンヒ 全 眞嬉

担当教員 所属 名前 連絡先等 東北大学 大学院情報科学研究科 准教授 全 眞嬉(ジョン ジンヒ) 東北大学 大学院情報科学研究科 准教授 名前 全 眞嬉(ジョン ジンヒ) 連絡先等 jinhee@dais.is.tohoku.ac.jp Tel/ Fax 022-795-4747 http://www.dais.is.tohku.ac.jp/~jinhee/

5.1 背景

データベース(Database) 特定のテーマに従って収集され,容易に再利用でき るように整理されたデータの群 再利用できるようになっているところが重要 データベースは複数の目的で使用される 再利用する為にはデータは良く整理されている必要 がある 複数の目的で共有できる何らかのテーマがあるのが 一般的 例 音楽データベース,気象情報データベース,図書館データベース,顧 客データベース・・・

データベース管理システム データベース管理システム(Database Management System:DBMS) データベースをコンピュータ上に構築・管理する為の 仕組み データベースシステム(Database Sytem) データベース管理システムとそれによって管理される データの群を合わせてデータベースシステムと呼ぶ

SQL SQL(Structured Query Languages) データベースを操作するための言語 E.F. Codd SQL(Structured Query Languages) データベースを操作するための言語 1970年代IBMのE.F. Codd氏のよって リレーショナルデータベース概念が提唱 E.F.Codd , “A Relational Model of Data for Large Shared Data Banks”,Communications of the ACM Vol.13, No.6, pp.377–387,1970

SQL SQL(Structured Query Languages) System Rの開発 世界初のリレーショナルデータベース管理システム (RDBMS) IBMがリレーショナルモデルの有効性を実証する為開 発した実験的データベースシステム システムを操作するに当ってSEQUEL(Structured English Query Language)が採用 SQLSQL(Structured Query Languages)に改名 OracleやSybase, Informixなどの商用DBMSが SQLを実装し,各ベンダーごとに拡張されている E.F. Codd

SQL SQLの改良 OracleやSybase, Informixなどの商用DBMSがSQL を実装し,各ベンダーごとに拡張されている ANSI(American National Standard Institute) 1987年 SQL 87 ISO(International Organization Standardization) JIS(Japanese Industrial Standard)

SQL 近年では SQL99 SQL:2003 SQL:2006 オブジェクト指向データベースへの拡張 OLAP(Online Analytical Processing)関連そうあの強化 SQL:2003 マルチメディアデータベース関連機能への拡張 XMLデータ関連のフィーチャが追加 SQL:2006 XQuery

2005年に発売されたハリーポッターシリーズの本下さい SQLって何? 簡単に言えば リレーショナルデータベース管理システムと対話する 為の言語 リレーショナルデータベース管理システムに対して問 い合わせる際にSQLを使う 2005年に発売されたハリーポッターシリーズの本下さい RDB SQL語 RDBMS

データベース言語の標準化のメリット ユーザは一つのデータベース言語を学ぶことで各 種のDBMSを利用できる 標準データベース言語を用いたあるツールはユー ティリティを開発したり利用したりすることが容易 になる 異なるユーザ間でのDBMS利用技術のノウハウ 共有が促進される

5.2基本概念

SQLの対象とするデータ構造とRDBのデータ構造との相違点 重複したタプルの存在 リレーショナルデータモデルでは リレーションはタプルの集合として規定 全く同じ属性値の並びからなる重複したタプルは不可 現実のデータ操作において重複した値が自動的 に除去されると都合が悪い場合がある 属性について平均値計算をした場合 属性のみを残すような射影演算の結果中に重複した値がジ除 去されると正しい平均は計算できない SQLでは必要に応じて重複したタプルを許すことが可 能 マルチ集合 重複した要素の存在を許すような集まり SQLは集合ではなくマルチ集合を基礎とした体系

SQLの対象とするデータ構造とRDBのデータ構造との相違点 属性やタプルの順序付け リレーショナルデータモデルでは リレーションスキーマにおいては属性の並び順序は意味 がない SQLでは 属性及び属性値は明示的に順序付けられたものとして扱 う 問い合わせ結果を アプリケーションに渡す時点においてタプル同士が並ぶ順 序うぃ明示的に指定できる

SQLでの用語 SQLの対象とするデータ構造とRDBのデータ構造 との相違点からSQLでは表,列,行と呼ぶ 表(table) リレーション 列(column) 属性 行(low) タプル

5.3 データの定義

実表(base table) 実表 他の表 SQLでデータの実体を伴う表 ビュー表(viewed table) ビューを表現する表 導出表(derived table) 問い合わせ結果として一般的に出来る表

図3.3 リレーショナルデータベースの例 科目 学生 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 図3.3 リレーショナルデータベースの例 科目 学生 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 学生番号 氏名 専攻 住所 00001 山田一郎 情報工学 東京都XXX 00002 鈴木明 茨城県XXX 00003 佐藤花子 履修 実習課題 科目番号 学籍番号 成績 001 00001 90 00002 80 002 00003 70 科目番号 課題番号 課題名 001 01 データモデリング 02 データベース設計 03 SQL Cプログラミング 002 7システムk-る

実表の定義の例 : 科目 各列の列名とそのデータ型 を指定する データ型 文字列、数、ビット列、日時な ど CHAR(3) 3文字の固定長文字列型 NCHAR(12) 12文字の固定長感じ文 字列型 INTEGER 整数型 科目 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 CREATE TABLE 科目 (科目番号 CHAR(3) NOT NULL、  科目名  NCHAR(12) NOT NULL、  単位数  INTEGER、  PRIMARY KEY (科目番号)、  CHECK(単位数 BETWEEN 1 AND 12))

実表の定義の例 : 科目 CHAR(3) 3文字の固定長文字列型 NCHAR(12) 12文字の固定長感じ文字列型 INTEGER 整数型 PRIMARY KEY (科目番号) 科目表の主キーが科目番号で あることを示したキー制御の記 述 CHECK(単位数 BETWEEN 1  AND 12) 単位数の値が1から12までの範 囲でなければならないとの整合 性制御を記述 科目 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 CREATE TABLE 科目 (科目番号 CHAR(3) NOT NULL、  科目名  NCHAR(12) NOT NULL、  単位数  INTEGER、  PRIMARY KEY (科目番号)、  CHECK(単位数 BETWEEN 1 AND12))

定義域(domain) CREAT DOMAIN文で定義された対象を定義域 (DOMAIN)と呼ぶ 実表定義の際のデータ型に変わって用いることも 可能 その場合はCHECKで記述された整合制約が適用さ れる CREATE DOMAIN  単位数 INTEGER   CHECK(VALUE BETWEEN 1 AND 12)

実表の定義の例 : 履修 主キーは科目番号と学 籍番号のペアとして指定 FOREIGN KEY記述 外部キーの指定 参照整合制約の記述に 相当 科目番号 学籍番号 成績 001 00001 90 00002 80 002 00003 70 CREATE TABLE 履修 ( 学生番号 CHAR(3) NOT NULL、  科目番号 CHAR(5) NOT NULL、  成績    INTEGER、  PRIMARY KEY (科目番号、学籍番号)、  FOREIGN KEY (科目番号)      REFERENCES 科目(科目番号)、   FOREIGN KEY (学籍番号)      REFERENCES 学生(学籍番号)、  CHECK(成績 BETWEEN 0 AND 100))

5.4 問合せ 5.4.1 問合せの基本形

問合せの基本形 SELECT Ti1.C1,・・・,Tim.Cm FROM T1,・・・,Tn WHERE ψ SQLにおける典型的な問合せ記述の形式 T1,・・・,Tn  は表名 CT1,・・・,Cnm はそれぞれT1,・・・,Tn 中の表  Ti1.,・・・,Tim の例名 Ψは条件 SELECT Ti1.C1,・・・,Tim.Cm FROM   T1,・・・,Tn WHERE ψ

Q1: 科目番号005の科目の履修者の学籍番号と成績の一覧 SELECT 履修.学籍番号、履修.成績 FROM 履修 WHERE 履修.科目番号=‘005’ SELECT 学籍番号、成績 FROM 履修 WHERE 科目番号=‘005’

Q2: 学籍番号00100のの学生が履修した科目番号,成績の一覧 SELECT 科目.科目番号、科目名、成績 FROM 科目 WHERE 科目.科目番号=履修.科目番号         AND  学籍番号=‘00100’ SELECT 科目番号、科目名、成績 FROM 科目 NATURAL JOIN 履修 WHERE 学籍番号=‘00100’

Q3: 情報工学専攻のいずれかの学生が履修した科目の科目番号と科目名の一覧 SELECT 科目.科目番号、科目名 FROM 科目、履修、学生 WHERE 科目.科目番号=履修.科目番号        AND 履修.学籍番号=学生.学籍番号         AND 専攻=N’情報工学’ SELECT 科目番号、科目名 FROM 科目 NATURAL JOIN 履修 NATURAL JOIN 学生 WHERE 専攻=N’情報工学’ ORDER BY 科目番号

Q4: 科目番号005の科目に関して学籍番号00100の学生よりも成績の良かった学生の学籍番号一覧 SELECT y.学籍番号 FROM 履修 AS x、履修 AS y WHERE x.科目番号=‘005’ AND x.学籍番号=‘00100’                  AND y.科目番号=‘005’ AND y.成績>x.成績

Q5: 全科目の科目名と単位数一覧 SELECT 科目名、単位数 FROM 科目 SELECT DISTINCT 科目名、単位数

Q6: 単位数が3単位以上の科目の科目番号,科目名,単位数の一覧 SELECT * FROM 科目 WHERE  単位数 >= 3

5.4 問合せ 5.4.2 集合関数

Q7: 科目番号005の科目の平均点 SELECT AVG(成績) FROM 履修 WHERE  科目番号 = ‘005’

5.4 問合せ 5.4.3 グループ表

Q8: 全科目について科目番号と平均点の一覧 SELECT 科目番号、AVG(成績) FROM 履修 GROUP BY  科目番号

Q9: 情報工学専攻の学生が履修した科目の科目番号と情報工学専攻の学生に関する平均点の一覧 SELECT 履修.科目番号、AVG(成績) FROM 履修、学生 WHERE  履修.学籍番号=学生.学籍番号        AND 専攻=N’情報工学’ GROUP BY 履修.科目番号

Q10: 履修者が30人以上の科目の科目番号,履修者数,平均点の一覧 SELECT 科目番号、COUNT(*)、AVG(成績) FROM 履修 GROUP BY  科目番号 HAVING COUNT(*)>=30

5.4 問合せ 5.4.4 集合演算

Q11: 実習課題があるか,あるいは単位数が5単位以上の科目の科目番号,科目名,科目の一覧 SELECT 科目.* FROM 科目、実習課題 WHERE  科目.科目番号=実習課題.科目番号 UNION    SELECT * FROM   科目 WHERE  単位数>=5

Q12: 実習課題のない科目の科目番号と科目名の一覧 SELECT 科目番号、科目名 FROM 科目 EXCEPT UNION    SELECT  科目.科目番号、科目名 FROM   科目、実習課題 WHERE  科目.科目番号=実習課題.科目番号

5.4 問合せ 5.4.5 副問合せ

Q3: 情報工学専攻のいずれかの学生が履修した科目の科目番号と科目名の一覧 SELECT 科目.科目番号、科目名 FROM 科目、履修、学生 WHERE 科目.科目番号=履修.科目番号        AND 履修.学籍番号=学生.学籍番号         AND 専攻=N’情報工学’ SELECT 科目番号、科目名 FROM 科目 WHERE  科目番号 IN  (SELECT 科目番号     FROM 履修、学生     WHERE 履修.学籍番号=学生.学籍番号             AND  専攻=N’情報工学’)

Q12: 実習課題のない科目の科目番号と科目名の一覧 SELECT 科目番号、科目名 FROM 科目 EXCEPT UNION    SELECT  科目.科目番号、科目名 FROM   科目、実習課題 WHERE  科目.科目番号=実習課題.科目番号 SELECT 科目番号、科目名 FROM 科目 WHERE  NOT EXISTS  (SELECT *     FROM 実習問題     WHERE 実習問題.科目番号=科目.科目番号)

5.4 問合せ 5.4.6 ビュー

実習科目 CREAT VIEW 実習科目(科目番号、科目名、単位数) AS SELECT 科目.* FROM 科目、実習課題   WHERE  科目.科目番号=実習課題.科目番号

5.5 データの更新

INSERT:行を追加 U1 科目番号002の科目の実習問題03として 「シェル作成」を追加 科目番号010の科目履修者として学籍番号が ‘00099’以下の学生を全員登録 INSERT INTO  実習問題 VALUE (’002’、’03’、N’シェル作成’) INSERT INTO  履修(科目番号、学籍番号)  SELECT ‘010’、学籍番号  FORM   学生  WHERE 学籍番号<=‘00099’ 

DELETE:行を削除, UPDATE:列の値を更新 科目番号010の科目の単位数を3単位に変更 DELETE FROM  実習問題 WHERE 科目番号=‘005’ UPDATE 科目 SET 単位数=3 WHERE  科目番号=‘010’

課題 教科書100ページの演習問題 提出方法 問題5.1 問題5.5 問題5.6 A4の紙に作成し提出 次回の講義時に提出 学籍番号,名前右上に記入 表紙は不要 次回の講義時に提出 11月27日(土)