金子邦彦研究室データベースリレーショナルデータベースの基本(スライド資料と動画とプログラム例)(全14回)

リレーショナルデータベースの基本(スライド資料と動画とプログラム例)(全14回)

大学で使用した自作の資料等を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.

目次

  1. ds-1. データベースとは,データベースシステムとは,情報とデータ [PDF], [パワーポイント], [スライド HTML]
  2. ds-2. SQL,SQL のデータ型,テーブル定義,問い合わせ(クエリ) [PDF], [パワーポイント], [スライド HTML]
  3. ds-3. ER 図,関連,異状, テーブル分解 [PDF], [パワーポイント], [スライド HTML]
  4. ds-4. 問い合わせ(クエリ) [PDF], [パワーポイント], [スライド HTML]
  5. ds-5. 集計・集約 [PDF], [パワーポイント], [スライド HTML]
  6. ds-6. 並べ替え(ソート) [PDF], [パワーポイント], [スライド HTML]
  7. ds-7. 結合 [PDF], [パワーポイント], [スライド HTML]
  8. ds-8. データベースの異状,分解と結合 [PDF], [パワーポイント], [スライド HTML]
  9. ds-9. 主キー,参照整合性制約,従属 [PDF], [パワーポイント], [スライド HTML]
  10. ds-10. 中間まとめ,データベースの応用,データベースの種類 [PDF], [パワーポイント], [スライド HTML]
  11. ds-11. SQL のIN,副問い合わせ [PDF], [パワーポイント], [スライド HTML]
  12. ds-12. データベースの NULL,AND,OR [PDF], [パワーポイント], [スライド HTML]
  13. ds-13. データベース操作,トランザクション,リカバリ,ロック,同時実行制御 [PDF], [パワーポイント], [スライド HTML]
  14. ds-14. 従属,正規形,正規化 [PDF], [パワーポイント], [スライド HTML]

YouTube 再生リスト「リレーショナルデータベースの基本」: https://youtube.com/playlist?list=PLwoDcGBEg9WGKPP6dExr8DcUf9nV2kYGD

【サイト内のリレーショナルデータベース関連の資料】

1. データベースとは,データベースシステムとは,情報とデータ

ドクセル のURL: https://www.docswell.com/s/6674398749/KGG3QK-2022-01-12-110749

資料:[PDF], [パワーポイント], [スライド HTML]

データベースは,資 実は,我々の生活,社会に浸透しており,大切なもの. オンラインでのデータのやり取り,データの蓄積には,データベースシステムが欠かせない.

2. SQL,SQL のデータ型,テーブル定義,問い合わせ(クエリ)

ドクセル のURL: https://www.docswell.com/s/6674398749/Z9WXMZ-2022-01-12-110917

資料:[PDF], [パワーポイント], [スライド HTML]

3. ER 図,関連,異状, テーブル分解

ドクセル のURL: https://www.docswell.com/s/6674398749/5LJM3Z-2022-01-12-111001

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

ER図を書いてみてください.

https://app.diagrams.net/

最初,「Save diagram to:」という画面が出たときは,「Decide later」を選んでください. これは,データの保存先を設定するものです.

4. 問い合わせ(クエリ)

ドクセル のURL: https://www.docswell.com/s/6674398749/KP46LZ-2022-01-12-111032

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

① SQLによる問い合わせ(クエリ) (2つのテーブルを使用)

Accessデータベースファイル

db4-3.accdb

次のSQLを実行し,結果を確認

射影

SELECT CUST
FROM P;

射影

SELECT PRODUCT, CUST
FROM P;

選択

SELECT *
FROM P
WHERE PRICE > 50;

選択と射影の組み合わせ

SELECT PRODUCT
FROM P
WHERE PRICE > 50;

結合と射影の組み合わせ

SELECT NAME, PRODUCT
FROM P, C
WHERE P.CUST = C.ID;

重複行除去する

SELECT DISTINCT CUST
FROM P;

並べ替え(ソート)

SELECT *
FROM P
ORDER BY PRICE;

集計・集約

SELECT CUST, COUNT(*)
FROM P
GROUP BY CUST;

副問い合わせ

SELECT NAME
FROM C
WHERE ID IN
  (SELECT CUST
    FROM P
    WHERE PRODUCT='P100');

② 実データによる演習 (1つのテーブルを使用)

Accessデータベースファイル

db4-4.accdb

重複行除去,教育の列(属性)の値を見る

SELECT DISTINCT 教育
FROM 米国成人調査データ;

年収5万ドル以上の人の年齢と職業

SELECT 年齢, 職業
FROM 米国成人調査データ
WHERE 年収5万ドル以上か = '>50K';

年収5万ドル以上の人の職業、重複行除去

SELECT DISTINCT 職業
FROM 米国成人調査データ
WHERE 年収5万ドル以上か = '>50K';

80歳よりも上の人は? そして、並べ替え(ソート)

SELECT *
FROM 米国成人調査データ
WHERE 年齢 > 80
ORDER BY 年齢;

5. 集計・集約

ドクセル のURL: https://www.docswell.com/s/6674398749/KXW8GZ-2022-01-12-111109

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

Access データベースファイル

db4-4.accdb

SELECT 年齢, count(*)
FROM 米国成人調査データ
GROUP BY 年齢;
SELECT 教育, count(*)
FROM 米国成人調査データ
GROUP BY 教育;
SELECT 週当たり労働時間, count(*)
FROM 米国成人調査データ
GROUP BY 週当たり労働時間;
SELECT 年収5万ドル以上か, count(*)
FROM 米国成人調査データ
GROUP BY 年収5万ドル以上か;
SELECT 教育, 年収5万ドル以上か, count(*)
FROM 米国成人調査データ
GROUP BY 教育, 年収5万ドル以上か;

6. 並べ替え(ソート)

ドクセル のURL: https://www.docswell.com/s/6674398749/ZDGR6Z-2022-01-12-111147

資料:[PDF], [パワーポイント], [スライド HTML]

SQL による並べ替え(ソート)

【演習】

Accessデータベースファイル

db4-4.accdb

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(*);

7. 結合

ドクセル のURL: https://www.docswell.com/s/6674398749/5WV28K-2022-01-12-111219

資料:[PDF], [パワーポイント], [スライド HTML]

SQL による結合

【演習】

Access データベースファイル

db7-3.accdb

SELECT * FROM T, S;

SELECT a FROM T, S;

SELECT * FROM T, S WHERE a = b;

SELECT *
FROM 米国成人調査データ
WHERE 母国 = '日本';

② これは「日本あるいはインド」という条件での選択である

SELECT *
FROM 米国成人調査データ
WHERE 母国 = '日本' or 母国 = 'インド';

SELECT *
FROM 米国成人調査データ, 対象国
WHERE 母国 = 名前;

④ 「対象国」を「オランダ,インド」に書き換えてから実行してください

SELECT *
FROM 米国成人調査データ, 対象国
WHERE 母国 = 名前;

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

ドクセル のURL: https://www.docswell.com/s/6674398749/KRG1J5-2022-01-12-111306

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

Access データベースファイル

db8.accdb

① 重複行に関する演習

SELECT DISTINCT 教育
FROM 米国成人調査データ;
SELECT DISTINCT 職業
FROM 米国成人調査データ;
SELECT DISTINCT 母国
FROM 米国成人調査データ;

② 分解と結合

SELECT DISTINCT ID, 商品名, 単価
INTO A FROM 購入記録;
SELECT DISTINCT 購入者, ID
INTO B FROM 購入記録;
SELECT A.ID, 商品名, 単価, 購入者
FROM A, B
WHERE A.ID = B.ID;

9. 主キー,参照整合性制約,従属

ドクセル のURL: https://www.docswell.com/s/6674398749/Z28DGZ-2022-01-12-111413

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

paiza.IO の URL: https://paiza.io/ja

MySQL を選んで使ってください

① 主キーに関する演習(Paiza.IO を使用)

・テーブル menu (テーブル定義と行の挿入と確認)

CREATE TABLE menu (
  ID INTEGER PRIMARY KEY,
  name TEXT,
  price TEXT
) engine = InnoDB;
INSERT INTO menu VALUES(1, 'orange', 50);
INSERT INTO menu VALUES(2, 'apple', 100);
INSERT INTO menu VALUES(3, 'apple', 150);

・主キーについて集計・集約すると行数は1

SELECT ID, COUNT(*)
FROM menu
GROUP BY ID;

・同じ ID の行を挿入することはできない

INSERT INTO menu VALUES(2, 'melon', 500);

② 参照整合性制約に関する演習(Paiza.IO を使用)

・テーブル menu (テーブル定義と行の挿入と確認)

CREATE TABLE menu (
  ID INTEGER PRIMARY KEY,
  name TEXT,
  price TEXT
) engine = InnoDB;
INSERT INTO menu VALUES(1, 'orange', 50);
INSERT INTO menu VALUES(2, 'apple', 100);
INSERT INTO menu VALUES(3, 'apple', 150);

・テーブル sales (テーブル定義と行の挿入と確認)

CREATE TABLE sales (
  ID INTEGER PRIMARY KEY,
  who TEXT,
  buy integer,
  num integer,
  FOREIGN KEY(buy) REFERENCES menu(ID)
) engine = InnoDB;

INSERT INTO sales VALUES(1, 'X', 1, 10);
INSERT INTO sales VALUES(2, 'Y', 2, 5);

・参照整合性制約に違反する行を挿入することはできない

INSERT INTO sales VALUES(3, 'X', 22, 1);

10. 中間まとめ,データベースの応用,データベースの種類

ドクセル のURL: https://www.docswell.com/s/6674398749/K81XEZ-2022-01-12-111450

資料:[PDF], [パワーポイント], [スライド HTML]

リレーショナルデータベースシステム全体のまとめ,関連する話題.

11. SQL のIN,副問い合わせ

ドクセル のURL: https://www.docswell.com/s/6674398749/Z33XVZ-2022-01-12-111542

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

 

実データを用いた演習を実施 Access データベースファイル

d11.accdb

11-2

SELECT *
FROM 米国成人調査データ
WHERE 教育 IN ('10th', '11th');
SELECT *
FROM 米国成人調査データ
WHERE 母国 IN ('インド', 'メキシコ');

11-4

SELECT 科目名
FROM 成績
WHERE 学生番号 = 101;
SELECT MAX (得点)
FROM 成績;
SELECT DISTINCT 学生番号
FROM 成績
WHERE 科目名 IN ('データベース', '数学');
SELECT 学生番号
FROM 成績
WHERE 得点 IN (SELECT MAX (得点) FROM 成績);
SELECT DISTINCT 学生番号
FROM 成績
WHERE 科目名 IN (SELECT 科目名
    FROM 成績
    WHERE 学生番号 = 101);

paiza.IO の URL: https://paiza.io/ja

MySQL を選んで使ってください ①1つめ

create table T(商品 text, 価格 integer);
insert into T values('かき氷', 400);
insert into T values('カレーライス', 500);
insert into T values('サイダー', 200);

select *
from T
where 価格 IN(200, 500);

②2つめ

create table 成績 (
  学生番号 integer,
  科目名 text,
  得点 integer
);
insert into 成績 values(101, 'データベース', 90);
insert into 成績 values(101, '数学', 80);
insert into 成績 values(102, 'データベース', 85);
insert into 成績 values(102, 'プログラミング', 100);
insert into 成績 values(103, 'データベース', 95);
insert into 成績 values(201, '歴史', 90);
insert into 成績 values(202, '地理', 90);
SELECT 科目名
FROM 成績
WHERE 学生番号 = 101;
SELECT DISTINCT 学生番号
FROM 成績
WHERE 科目名 IN (SELECT 科目名
    FROM 成績
    WHERE 学生番号 = 101);
SELECT MAX(得点)
FROM 成績;
SELECT 学生番号
FROM 成績
WHERE 得点 IN (SELECT MAX(得点)
    FROM 成績);

12. データベースの NULL,AND,OR

ドクセル のURL: https://www.docswell.com/s/6674398749/KQMVWZ-2022-01-12-111615

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

Access データベースファイル

d12.accdb

12-2 Access を用いて IS NULL, IS NOT NULL の演習

select *
from T
where 価格 IS NULL;
select *
from T
where 価格 IS NOT NULL;

12-4 Access を用いて復習,AND や OR の演習

<復習>

select *
from 授業;
select *
from 授業
where 科目 = '国語';
select *
from 授業
where 教室名 = '一階';

<AND や OR>

select *
from 授業
where 科目 = '国語' AND 教室名 = '一階';
select *
from 授業
where 科目 = '国語' OR 教室名 = '一階';

<結合>

select 科目, 学生番号, 得点
from 授業, 成績;
select 科目, 学生番号, 得点
from 授業, 成績
where 授業.ID = 成績.科目番号;
select 科目, 学生番号, 得点
from 授業, 成績
where 授業.ID = 成績.科目番号 AND 学生番号 = 101;

12-5 Paiza.IO を用いた演習

paiza.IO の URL: https://paiza.io/

MySQL を選ぶ

① テーブル T を用いて NULL の演習

create table T(商品 text, 価格 integer);
insert into T values('かき氷', 400);
insert into T values('カレーライス', NULL);
insert into T values('サイダー', 200);

select *
from T
where 価格 IS NULL;

select *
from T
where 価格 IS NOT NULL;

② 授業テーブル,成績テーブルを用いて AND や OR の演習

create table 授業 (
  ID integer primary key,
  担当者 text,
  科目 text,
  教室名 text,
  曜日 text
);

create table 成績 (
  学生番号 integer,
  科目番号 integer,
  得点 integer,
  foreign key(科目番号) references 授業(ID)
);

insert into 授業 values(1, 'AA', '国語', '一階', '月');
insert into 授業 values(2, 'BB', '国語', '二階', '月');
insert into 授業 values(3, 'CC', '国語', '三階', '月');
insert into 授業 values(4, 'AA', '算数', '一階', '火');
insert into 授業 values(5, 'XX', '算数', '二階', '火');

insert into 成績 values(101, 1, 90);
insert into 成績 values(101, 4, 95);
insert into 成績 values(102, 2, 100);
insert into 成績 values(102, 5, 80);
insert into 成績 values(103, 3, 85);
insert into 成績 values(103, 4, 100);

select *
from 授業;

select *
from 授業
where 科目 = '国語';

select *
from 授業
where 教室名 = '一階';

select *
from 授業
where 科目 = '国語' AND 教室名 = '一階';

select *
from 授業
where 科目 = '国語' OR 教室名 = '一階';

select 科目, 学生番号, 得点
from 授業, 成績;

select 科目, 学生番号, 得点
from 授業, 成績
where 授業.ID = 成績.科目番号;

select 科目, 学生番号, 得点
from 授業, 成績
where 授業.ID = 成績.科目番号 AND 学生番号 = 101;

13. データベース操作,トランザクション,リカバリ,ロック,同時実行制御

ドクセル のURL: https://www.docswell.com/s/6674398749/Z7EX25-2022-01-12-111655

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

paiza.IO の URL: https://paiza.io/

MySQL を選んで使ってください

13-2

①挿入

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;

②更新

次を書き加える

update T set 料金 = 300 where 昼食 = 'うどん';
select * from T;

③削除

次を書き加える

delete from T where 名前 = 'A';
select * from T;

13-4

ロールバック

create table T(名前 text, 昼食 text, 料金 integer) engine = InnoDB;
insert into T values('A', 'そば', 250);
insert into T values('B', 'カレーライス', 400);
insert into T values('C', 'カレーライス', 400);
insert into T values('D', 'うどん', 250);
start transaction;
update T set 料金 = 300 where 昼食 = 'うどん';
delete from T where 名前 = 'A';
select * from T;
rollback;
select * from T;

14. 従属,正規形,正規化

ドクセル のURL: https://www.docswell.com/s/6674398749/5JXJJK-2022-01-12-111718

資料:[PDF], [パワーポイント], [スライド HTML]

【演習】

paiza.IO の URL: https://paiza.io/

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.料金;