SQL(4) 関係代数演算

Image from Gyazo

データベースの復元

前回までに作成したデータベースを読み込むには,[File]→[Open DB]をクリックし, 保存した sqlite3.db を選択します. 復元したデータベースに【学生リスト】,【学部リスト】,【テニス部】,【サッカー部】,【商品リスト】があることを確認しましょう.

sqlite3.db

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【学生リスト】

学部 キャンパス 教員数
工学部 春日井 30
人文学部 春日井 20
国際関係学部 名古屋 25

【学部リスト】

学籍番号 氏名 学年 学部 よみがな
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【テニス部】

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
B003 杉江弘子 3 人文学部 すぎえひろこ
A005 内田弘 3 工学部 うちだひろし

【サッカー部】

商品ID 商品名 仕入れ値 売値
C01 テレビ 50000 65000
C02 エアコン 60000 70000
C03 冷蔵庫 80000 55000
C04 電卓 3000 2000
C05 炊飯器 20000 25000

【商品リスト】

集合演算

リレーションに対する4つの集合演算がSQLで表現可能であることを確認していきましょう.

和集合演算

和集合演算が適用可能であるためにはリレーションが 和両立 である必要があります. ここでは,和両立の条件を満たしている【テニス部】と【サッカー部】を対象に考えていきます.

和集合演算にはUNIONを用います. テーブル名1テーブル名2は対象のテーブルの名前です.

/* 和集合演算 */
SELECT * FROM テーブル名1 UNION SELECT * FROM テーブル名2;

それでは,【テニス部】と【サッカー部】の和集合を求めてみましょう. ここで,注意して欲しいのは, いずれのリレーションにも共通の (B003,杉江弘子,3,人文学部,すぎえひろこ) です. 和集合の結果には,該当のレコードは一つだけとなります.

/* 【テニス部】と【サッカー部】の和集合 */
SELECT * FROM テニス部 UNION SELECT * from サッカー部;

Image from Gyazo

差集合演算

差集合演算にはEXCEPTを用います. テーブル名1テーブル名2は対象のテーブルの名前です.

/* 差集合演算 */
SELECT * FROM テーブル名1 EXCEPT SELECT * FROM テーブル名2;

それでは,【テニス部】と【サッカー部】の差集合を求めてみましょう. 【テニス部】から共通のレコードが取り除かれていることがわかります.

/* 【テニス部】と【サッカー部】の差集合 */
SELECT * FROM テニス部 EXCEPT SELECT * from サッカー部;

Image from Gyazo

共通集合演算

共通集合演算にはINTERSECTを用います. テーブル名1テーブル名2は対象のテーブルの名前です.

/* 共通集合演算 */
SELECT * FROM テーブル名1 INTERSECT SELECT * FROM テーブル名2;

それでは,【テニス部】と【サッカー部】の共通集合を求めてみましょう. 共通のレコードが結果に含まれていることがわかります.

/* 【テニス部】と【サッカー部】の共通集合 */
SELECT * FROM テニス部 INTERSECT SELECT * from サッカー部;

Image from Gyazo

直積演算

直積演算にはCROSS JOINを用います. テーブル名1テーブル名2は対象のテーブルの名前です. これまでとは異なりCROSS JOINの後にSELECT文は不要です.

/* 直積演算 */
SELECT * FROM テーブル名1 CROSS JOIN テーブル名2;

それでは,【テニス部】と【サッカー部】の直積を求めてみましょう. 全ての組み合わせでレコードが構成されていることがわかります.

/* 【テニス部】と【サッカー部】の直積 */
SELECT * FROM テニス部 CROSS JOIN サッカー部;

Image from Gyazo

例題1

下記の【八百屋】と【果物屋】を新規に作成しなさい. また,和集合,差集合,共通集合,直積集合を求めなさい.

商品番号 商品名 価格
G1 ニンジン 100
G2 トマト 200
G3 スイカ 500
【八百屋】
商品番号 商品名 価格
G4 イチゴ 300
G5 ブドウ 200
G3 スイカ 500
【果物屋】

Image from Gyazo

Image from Gyazo

関係代数演算

リレーションに対する3つの関係代数演算(リレーショナル代数演算)が SQLで表現可能であることを確認していきましょう(商演算は割愛) .

射影演算

射影演算は SELECT文 を用いるだけです.

/* 射影演算 */
SELECT 属性名 FROM テーブル名;

それでは,【学生リスト】を氏名とよみがなで射影してみましょう.

/* 【学生リスト】を氏名とよみがなで射影 **/
SELECT 氏名,よみがな FROM 学生リスト;

Image from Gyazo

次に,【学生リスト】を学部で射影してみましょう. 「工学部」に所属している学生が2人いるため,射影した結果にも「工学部」が2回表示されます.

/* 【学生リスト】を学部で射影 **/
SELECT 学部 FROM 学生リスト;

Image from Gyazo

重複行を除外して「工学部」を1回だけ表示するには,DISTINCT句 を利用します.

/* 【学生リスト】を重複を避けて学部で射影 **/
SELECT DISTINCT 学部 FROM 学生リスト;

Image from Gyazo

選択演算

選択演算は SELECT文WHERE句 を組み合わせるだけです.

/* 選択演算 */
SELECT 属性名 FROM テーブル名 WHERE 条件式;

それでは,【学生リスト】から工学部のレコードを選択しましょう.

/* 【学生リスト】から工学部のレコードを選択 */
SELECT * FROM 学生リスト where 学部="工学部";

Image from Gyazo

結合演算

結合演算は,条件を指定して結合する方法(内部結合外部結合)と, 一致するドメインを条件として結合する方法(自然結合)があります.

条件を指定して結合(内部結合)するには INNER JOIN を用います. テーブル名1テーブル名2は対象のテーブルの名前です. ONの後に,結合の条件となる各テーブルの属性を指定します.

/* 内部結合 */
SELECT * FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.属性名1 = テーブル名2.属性名2;

それでは,【学生リスト】と【学部リスト】を「学部」を条件に結合してみましょう. 学部の属性値が一致するレコードが結合されていることがわかります.

/* 【学生リスト】と【学部リスト】を「学部」を条件に結合 */
SELECT * FROM 学生リスト INNER JOIN 学部リスト ON 学生リスト.学部 = 学部リスト.学部;

Image from Gyazo

一致するドメインを条件として結合(自然結合)するには NATURAL INNER JOIN を用います. 上述の方法とは異なり,具体的に条件となる属性は指定しません.

/* 自然結合 */
SELECT * FROM テーブル名1 NATURAL INNER JOIN テーブル名2;

それでは,【学生リスト】と【学部リスト】を自然結合してみましょう. 自動的に「学部」を条件として結合されていることがわかります. また,冗長な属性は取り除かれています.

/* 【学生リスト】と【学部リスト】を自然結合 */
SELECT * FROM 学生リスト NATURAL INNER JOIN 学部リスト;

Image from Gyazo

例題2

【学生リスト】に新たに下記のレコードを追加しなさい (外部キー制約は無効にすること PRAGMA foreign_keys=OFF; ).

学籍番号 氏名 学年 学部 よみがな
D006 山下隆 4 経営情報学部 やましたたかし
その後で,学部リストとの内部結合(INNER JOIN),左外部結合(LEFT OUTER JOIN)を行い, その差を考察しなさい.

Image from Gyazo

データベースの保存

作成したデータベースを保存するには,[File]→[Save DB]をクリックします. 保存したファイルは sqlite4.db とファイル名を変更してから,提出してください.

情報処理技術者試験・過去問

下記リンクは ITパスポート試験ドットコム基本情報技術者試験ドットコム応用情報技術者試験ドットコムに掲載されている問題です.

参考書籍

愛知県名古屋市にある椙山女学園大学 文化情報学部 向研究室の公式サイトです. 専門は情報科学であり,人工知能やデータベースなどの技術要素を指導しています. この公式サイトでは,授業で使用している教材を公開すると共に, ベールに包まれた女子大教員のミステリアスな日常を4コマ漫画でお伝えしていきます. サイトに関するご意見やご質問はFacebookまたはTwitterでお問い合わせください.