情報システム1:RDB補助教材

(ITパスポートでは扱わないけれど情報システムの基幹技術であるために重要な)SQL(RDBを操作する言語)の実習と,RDBの数学的な説明をします.

渡辺隆行
最終更新:2018年07月08日 23:50

1 SQL

1.1 SQLとは

SQL(Structured Query Language)は,RDBという種類のデータベースを設計・操作・管理するためのプログラミング言語,つまりRDBと人間がコミュニケーションするための言語,です.標準化されているので,SQLを知っていれば,いろいろなRDBを使用できます.

SQLの文法は英語に似ているので簡単に覚えることが出来ますが,相手はコンピュータなので,一字でも間違えると動いてくれません.

属性名や条件の値以外(つまり,SQLの命令や記号)はすべて半角です.スペース又は改行で単語(命令)を分けます.全角スペースは使えないので注意.

SQLのひとつの命令文の最後にはセミコロンをつけます.改行しただけでは文の終わりとはみなされませんので,命令文の最後のセミコロンをお忘れなく.この性質を使って,適当に(SELECT句やFROM句などの単位で)改行した方が見やすくなります.

SQLでは大文字と小文字は区別されませんが,SELECTなどのあらかじめ予約されている語は大文字で書き,変数やテーブル名を小文字で書くのが良い作法とされているようです.

1.2 SQLの3種類の言語

SQLには,スキマーの定義とデータの入力編集という2種類の操作+1があります.

DDL (Data Definition Language);
データベースのデータ構造(スキマー)を定義する言語.ここで主キーを定めたり,外部キー制約を課したりする.
DML (Data Manipulation Language);
データベースのデータを操作する言語.
DCL (Data Control Language);
データベース自身を制御する言語.

2 SQL実習

2.1 SQL実習環境

クラウドで使える実習環境SQL Fiddleを使ってSQLの実習をします.

最初に,ページロゴ右側にある「MySQL 5.6」を「SQLite (WebSQL)」に変更しておいてください.(この方が反応が早いはず)

以下,自分で入力してもエラーになる場合は,背景がaliceblue色で表示されている部分をSQL Fiddleにコピペすればエラーなく動きます.そして,何を間違えたのか考えてみてください.

2.2 テーブル定義(DDL)

テーブルを定義するSQLのコマンドはDDLなので,SQL Fiddle左側のSchema入力領域に入力します.

CREATE TABLE 品物テーブル (
 品名コード CHAR(5) PRIMARY KEY,
 品名 CHAR(20),
 値段 INTEGER
);

「品物テーブル」という名前で,品名コード,品名,値段という3つの属性を持ったテーブルを定義しました.テーブルを新規作成(定義)するときに,テーブルの名前だけでなく,テーブルの各属性の名前とデータ型を指定していることに注意してください.

PRIMARY KEYという修飾語で,このテーブルの主キーを指定しています.CHAR(5)はデータの保存に5character分の記憶領域を割り当てる,INTEGERは整数として記録するという意味です.

既に存在しているテーブルを作成しようとするとエラーになるので,作成したテーブルを削除するコマンドも以下に書いておきます.

DROP TABLE IF EXISTS 品物テーブル;

2.3 レコードの挿入(DML)

DDLで定義したテーブルにレコードを挿入します.数字はそのまま書けますが,文字や日付型のデータはシングルクォートで括ります.(注:「00010」は文字ではなくて主キーとなるID番号(文字)なのでシングルクォートで括ります.これを忘れると10という数字と解釈されてエラーになります.)

レコード入力のSQLコマンドも,SQL Fiddle左側のSchema入力領域に,さきほどのテーブル定義のコマンドに続けて入力します.

INSERT INTO 品物テーブル VALUES ('00010', 'みかん', 200);
INSERT INTO 品物テーブル VALUES ('00011', 'りんご', 250);
INSERT INTO 品物テーブル VALUES ('00012', 'イチゴ', 400);
INSERT INTO 品物テーブル VALUES ('00013', 'オレンジ', 200);
INSERT INTO 品物テーブル VALUES ('00014', 'バナナ', 100);
INSERT INTO 品物テーブル VALUES ('00015', 'マンゴー', 600);
INSERT INTO 品物テーブル VALUES ('00016', '熟成バナナ', 450);
INSERT INTO 品物テーブル VALUES ('00017', 'フィリピンバナナ', 200);
INSERT INTO 品物テーブル VALUES ('00018', '高級バナナ特選', 1200);

SQL Fiddleの「Build Schema」をクリックすると「Schema Ready」と緑色で表示されて,右側に入力できるようになりました.

ためしに,以下のSQLを左側の領域の最下行に追加して,主キーが同じレコードを挿入してみましょう

INSERT INTO 品物テーブル VALUES ('00010', '小みかん', 250);

"Duplicate entry '00010' for key 'PRIMARY'"というエラーが表示されました.このようにしてRDBMS(RDB管理システム)は主キーが重複することを防いでいます.

追加した一行を削除して「Build Schema」し直してください.これでデータを問い合わせる準備ができました.

2.4 query(データ検索)

OPACのような「検索」機能はこの「問い合わせ」機能,RDBのテーブルから条件に合ったレコードを検索して表示する,を用いています.

2.4.1 全属性の表示

SELECT *
FROM 品物テーブル
;

SQL Fiddleの右側の入力域にこのSQLを入力して,「Run SQL」をクリックしてください.しばらく待つと,下側に検索結果が表形式に整形されて表示されます.すべてのレコードが表示されていることを確認してください.

2.4.2 一部の属性の表示(射影)

SELECT 品名, 値段
FROM 品物テーブル
;

SQL Fiddleの右側の入力域にさきほど入力したSQLを消して,今度はこのSQLを入力して,「Run SQL」をクリックしてください.品名と値段だけが表示されます.

2.4.3 条件を満たすレコードの表示(選択)

SELECT *
FROM 品物テーブル
WHERE 値段 > 500
;

値段が500円を超えたレコードだけが表示されました.条件(=, >=, <, <=, !=)をいろいろ変えてみてください.

SELECT 品名, 値段
FROM 品物テーブル 
WHERE 値段 BETWEEN 200 AND 1000
;

値段の範囲を指定することもできます.

2.4.4 曖昧な条件

SELECT *
FROM 品物テーブル
WHERE 品名 like '%バナナ%'
;

品名にバナナを含むレコードを表示します.

「WHERE 品名 like 'バナナ%'」や「WHERE 品名 like '%バナナ'」のように%をつける場所を変えるとどうなるでしょうか? 「%」はワイルドカードで,何か文字があってもよいしなくてもよいことを示します.

2.4.5 複数の条件を満たすレコードの表示

SELECT *
FROM 品物テーブル
WHERE 品名 like '%バナナ%' AND 値段 > 200
;

複数の条件をAND(論理積)で指定しました.OR(論理和)で指定することもできます.

2.4.6 レコードの並び替え

SELECT 品名, 値段
FROM 品物テーブル 
ORDER BY 値段 ASC
;

昇順表示がASC(ascendant),降順表示がDESC(descendent)です.降順で表示させてみてください.

2.5 そのほかのデータ操作(DML)

2.5.1 更新

UPDATE 品物テーブル
SET 値段=値段*0.9 
WHERE 品名='みかん'
;

注:WHERE修飾を忘れると全レコードが更新されてしまいます!

下記SQLを実行して,みかんの値段が修正されていることを確認してください.

UPDATE 品物テーブル SET 値段=値段*0.9 WHERE 品名='みかん';
SELECT * FROM 品物テーブル;

2.5.2 削除

DELETE FROM 品物テーブル 
WHERE 品名='みかん'
;

注:WHERE修飾を忘れると全レコードが消えてしまいます!

下記SQLを実行して,みかんのレコードが削除されていることを確認してください.

DELETE FROM 品物テーブル WHERE 品名='みかん';
SELECT * FROM 品物テーブル;

2.6 計算

表計算のようにデータを元に計算させることもできます.

SELECT AVG(値段) FROM 品物テーブル;

値段の平均が出ましたか?

AVG(平均)の他に,MAX(最大値),MIN(最小値),SUM(合計)も計算できますので各自でやってみてください.

下記SQLでレコードの件数をカウントできます.

SELECT COUNT(*) FROM 品物テーブル;

2.7 テーブルの修正(DDL)

属性の追加もできます.

ALTER TABLE 品物テーブル
ADD COLUMN 産地 CHAR(20)
;

SQL Fiddleの左側の入力に入力します.

下記SQLで属性が追加されていること(でも,データを入力していないので値はNULLであること)が確認できます.

SELECT *
FROM 品物テーブル 
;

ADDの他に,DROP,MODIFYなどもあります.

2.8 SQL(DCL)

DCLの実習はしません.ロック,同時実行制御,トランザクション,ユーザ管理とセキュリティなどを後で説明します.

3 続SQL実習(サブクエリー,グループ化,結合,View表)

以下は複数のテーブルを対象にした操作です.複雑になるので,実習はしませんし,授業でも扱いません.

3.1 サブクエリー

SELECT * FROM 売上明細 WHERE 売上明細.商品ID=
(SELECT 商品ID FROM 商品 WHERE 商品名='マウス');

売上明細テーブルと商品テーブルの二つを対象にしています.2段構えにばらして考えるとわかりやすい.

前段(2行目):
SELECT 商品ID FROM 商品 WHERE 商品名='マウス';は,003(マウスの商品ID)を返す.
後段(1行目):
SELECT * FROM 売上明細 WHERE 売上明細.商品ID='003';は,商品IDが003の売上明細を返す.
合体:
SELECT * FROM 売上明細 WHERE 売上明細.商品ID= (SELECT 商品ID FROM 商品 WHERE 商品名='マウス');は,商品IDを知らなくても,マウスの売上明細を調べることができる.

3.2 グループ化

SELECT 出身大学, COUNT(*) FROM 採用内定者 GROUP BY 出身大学;

3.3 結合

直積になってしまう:

SELECT * 
FROM 売上明細, 商品;

ので,等結合をとる.

SELECT * 
FROM 売上明細, 商品
WHERE 売上明細.商品ID=商品.商品ID;

今度は自然結合

SELECT 売上明細.受付ID, 売上明細.商品ID, 商品.商品名, 商品.単価, 売上明細.個数 
FROM 売上明細, 商品
WHERE 売上明細.商品ID=商品.商品ID;
SELECT U.受付ID, U.商品ID, S.商品名, S.単価, U.個数 
FROM 売上明細 U, 商品 S
WHERE U.商品ID=S.商品ID;

3.4 View表

RDBでは,仮想的な表を作ることも出来ます.View表はDBMSが作り出した仮想的な表で,外部スキーマをあらわします.

CREATE VIEW 推薦枠g02c099 AS SELECT 新卒個人データ.整理番号, 新卒個人データ.氏名, 採用内定者.出身大
学FROM 新卒個人データ, 採用内定者WHERE 新卒個人データ.整理番号=採用内定者.整理番号;

VIEW表を削除するには,

DROP VIEW 推薦枠g02c099;

4 RDBのformalな説明

4.1 3層スキーマ

「3層スキーマ」(1)

・概念スキーマ;実世界の写絵.リレーショナルデータモデルの(Viewではない)実表が一例.

・外部スキーマ;概念スキーマで表現されたデータベースを利用する際のスキーマ.この授業の実習で使用しているWebアプリケーションも外部スキーマの一例.Viewというリレーショナルデータベースの機能も一例.

・内部スキーマ;概念スキーマをコンピュータに実装するための物理的なスキーマ.ハードディスクの上にどのようにデータを配置するかなどを含む.この授業では取り扱わない.

  1. 教科書では,「4.4 3種類のデータ・モデル(p.87)」に相当する概念

4.2 データモデルとE-R図

4.2.1 データモデル

  • 実世界にあるデータ(データ自身,データ間の関連,データ構造など)を概念化して「論理データモデル」を作る(「概念モデル」ともいう).E-R(実体-関連)モデルは,論理データモデルの一例.
  • 論理データモデルを,データベース管理システムで管理可能な形に表現したものが「物理データモデル」(「論理モデル」ともいう).

4.2.2 データベースの設計

(2)

(処理やアクセスが速いなどの)性能がよくて,データベースの管理がしやすく,データベース更新時に矛盾が生じないデータベースの設計方法

  1. 要件定義(業務分析)
  2. 論理設計(ERモデル(3),正規化(4)
  3. 物理設計
  1. 教科書「9章データベースの実践」(p.208)参照
  2. 教科書「9.6 ERモデル」(p.220)参照
  3. 教科書「9.7正規化」(p.224)参照

4.2.3 E-R(実体-関係)図

  • 実世界は,「実体(Entity)」と,実体間の「関連(Relationship)」で成り立っているとする.
  • 実体は,実世界の様々なものを個々の実体として認識するのではなく,共通の性質をとらえた抽象的な事物である.例えば,「学生」という実体は,学生番号とか氏名とかの共通の性質(属性)を持った実体.「識別子(primary key,主キー)」で区別できる.
  • 関連は,実体間の関連を示す.例えば,「学生」という実体と「科目」という実体の間には,「履修」という関連がある.関連には,1対1,1対多,多対多の対応関係(cardinarity)がある.
  • 実体は「属性(Attribute)」を持つ.属性の一つ(以上)が識別子になる.
  • 論理データモデルとしてのE-R図を,物理データモデルとしてのリレーショナルデータベース(スキーマ)に変換することができる.
  • 見方によって,E-R図の作り方が変わりうる.
  • E-R図の例;図書の貸し出し,教官と担当科目,注文

4.3 RDBは表ではない

テーブル;表

属性(attribute,フィールド);表の列

組(tuple,レコード);表の行

定義域(domain);データ型

リレーションシップ;リレーション(テーブル)とリレーション(テーブル)の関係.

4.4 集合からみたRDB

定義域(ドメイン)は集合(例;人名の集合,年齢の集合).

直積;複数の定義域の組み合わせ.

組;直積集合の各要素.

リレーション;直積集合の有限な部分集合(つまり,あらゆる組み合わせの中からある条件を満たす要素だけを取り出している集合).

組(タプル);リレーションの各要素.

リレーションはテーブル(表)として表すことができる.

リレーション名;リレーションに付ける名前,テーブルの名前に相当.

属性名;リレーションの各定義域につける名前,テーブル(表)の列名に相当.

4.4.1 集合演算

最初の3つの集合演算は,同じドメイン(属性)から作った同じ次数(列の数)のテーブルの間でしか成立しない.

和集合(union)演算
差集合(difference)演算
積(共通)(intersection)集合演算
直積(direct product)演算
直積演算は,別のドメインからなるテーブルでも演算できる.

4.4.2 関係演算

射影(projection)演算
テーブルから列を抜き出す演算になる.このとき,重複した行は取り除く.(集合は重複した要素を含まない.)
選択(selection)演算

テーブルから条件を満たす行を取り出す演算になる.

選択条件;同じドメインに属する2つの属性を比較する,タプルの別の属性を比較する.

結合(join)演算

ドメインが等しい列を張り合わせて2つのテーブルをつなぎ合わせる演算.直積と選択の組合せになる.

等結合(equal join)演算;同じドメインの属性値が等しいもの同士を結合する.

自然結合(natural join)演算;等結合した結果から(等結合するときのノリ代として使った)重複する属性の一方を取り除いたもの.

商演算

R/Sという商演算は,Rの中に商を示すタプルとSとの直積(t×S)が含まれている場合に,tを取り出すような演算.

商演算は,直積・差・射影の各演算を用いて書き換えることもできる.

4.5 リレーションスキマー

リレーションスキーマ;リレーションの各要素(タプル)は変化するが,リレーションの枠組み(スキーム)であるリレーション名や属性名は不変.

したがってRDBの設計においては,まずリレーションスキマーを定義する必要がある.

4.6 キー

キー(key):テーブルの中のレコード(タプル,行)を特定する属性

候補キー(candidate key):テーブルの中のレコード(タプル,行)を一意に同定することができる属性(の組).

主キー(prime key):レコード(タプル,行)を一意に同定するIDの役割を果たす属性.候補キーの中からひとつ選んで主キーとするか,別にIDやコードと呼ばれるユニークな値を持つ属性を追加して主キーとする.

複合キー:複数の属性を合わせることでキーとなることもある.

キー制約(key constraint);主キーは必ず値を持っていなければならない.(NULL値ではIDにならない.)

NULL(空値):値を持っていないこと.

従属関係:属性Aの値がわかれば属性Bの値も自動的に決まるとき,属性Bは属性Aに従属している.例えば,郵便番号は住所に従属している.

外部キー(foreign key):他のテーブルの主キーとなっている属性のこと.外部キーを設定することでテーブル同士の関連(関係性,リレーション)が作られる.

外部キー制約(foreign key constraint):外部キーは,参照している外部テーブルの主キーにない値を使用できない.参照制約ともいう.

4.7 データを管理しやすいテーブルの要件(正規化)

一貫性制約;矛盾したデータがないこと,ありもしない値が入力されないこと,データが重複しないこと,などの条件.

データを挿入したり削除したり更新したりしたときにもおかしくならない,メインテナンスし易い;正規化という作業が必要.

4.7.1 第一正規形(first normal form)

一事実一箇所,属性値がデータの集合でないテーブル.

主キーの属性名は下線を引いて示す,DDLでも主キーを指定する.

これでもまだ不完全;レコード(タプル,行)の挿入時異常,削除時異常,修正時異常が発生してしまうテーブルがある.

これらの更新時異常を改善するためには,お互いに自立した属性の組を持つ複数のテーブルに分解しなければならない.

4.7.2 第二正規形

完全従属;属性A.B,Cの組の値が決まれば属性X,Y,Zの値が自動的に決まるのが従属関係であるが,このうち,属性Aや属性Bだけで決まるのではなくて,A,B,Cすべての値に従属していることを完全従属という.つまり,決定する側に余分な属性が入っていないこと.

第二正規形では,第一正規形であるテーブルの非キー属性は,必ず候補キーに完全従属していなければならない.つまり,候補キーの値が指定されれば,残りの属性の値も決まる.

4.7.3 第三正規形

推移従属;仲介属性を介して従属関係にあること.AならばB,BならばCのとき,AならばCが成立するが,このような関係が推移従属.

第二正規形では,主キーとは別の属性同士にまだ従属関係が残っている(推移従属している)場合がある.そこで,完全従属以外の従属関係が残らないように分解すると,第三正規形になる.先ほどの例の場合は,属性A及びBのリレーションと,属性B及びCのリレーションに分解する.

第三正規形では,候補キー以外の属性の値によってその他の属性の値を決めることはできない.

4.7.4 正規化復習

  • 第2正規化の問題;(☆学生ID,学生氏名,☆履修した科目ID,履修した科目の評価)
  • 第3正規化の問題;(☆学生ID,☆履修した科目ID,その科目の単位数)
  • 第2正規化の問題;(☆学生番号,氏名,学年,学部,学部所在地,☆科目名,成績,担当教員)
  • 第3正規化の問題;(☆学生番号,氏名,学年,学部,学部所在地,☆科目名,成績,担当教員)
  • 正規化の問題;(☆商品コード,商品名,単価,☆仕入先コード,仕入先社名,仕入先住所)
  • 正規化の問題;(☆学生コード,学生名,☆授業コード,授業名,履修年度,成績)

4.8 データベース管理システム(DBMS)

ユーザとデータベース(DB)の間に立ってDBを管理するソフトウエア.DB(テーブルの集合)とDBMSを合わせてデータベースと呼ぶこともある.

DBMSの役割

  • データベース言語(SQL)を使って,大量のデータから,条件にあった(目的の)データを瞬時に検索する(取り出す),修正するなどの処理を効率的・正確に実行する.
  • 同時に複数の人がDBを利用する.(データの共有)
  • 同時に複数の人がDBを利用しても不具合が生じないようにする.(同時実行制御)
  • 権限のある人がその権限にそってDBを利用する.(アクセス管理,セキュリティ)
  • DBが壊れないようにする.壊れた際にすぐに復旧できるように定期的にバックアップを取るなど,障害回復の機能を持つ.(障害復旧)
  • データ修正時に間違いがないか不正な入力がないかチェックする.(データの完全性)
  • データの形が修正されてもDBMSには影響を与えない.その逆も.(データの独立)
  • 一塊の一連の操作をまとめて処理し,その塊ごとに操作が終了(コミット)したり,失敗して元に戻したり(ロールバック)する.(トランザクション)