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

目次

1 はじめに

シラバス：C329：情報システム１（データベース）

1.1 情報処理技術者試験

情報に関連した資格試験は色々ありますが，経済産業省が実施している国家試験が「情報処理技術者試験」です．この試験は基本的な試験から専門的なものまで細かい試験区分に分類されていますが，基本的な試験が「初級システムアドミニストレータ」と「基本情報技術者試験」です．

「初級シスアド」は，企業などの情報システムの利用者が対象で，情報技術の有効利用を図るための知識・技能を問う試験です．2002年度の合格率は32%だそうです．「基本情報」は，SEを目指す人や情報関連の仕事に携わっている人が対象で，情報技術に関する基礎的な知識と技能を問う試験です．2002年度の合格率は17%だそうです．

本屋さんに行けば，試験のテキストや過去問題集がたくさんあります．「合格情報処理」という雑誌もあります（9304号室にバックナンバーが置いてあります．）．この試験に関心がある人は，本屋さんで情報を集めてください．

1.2 用語集の作り方

授業中の説明，本，Webをベースに，授業で出てきたり，授業の予習や復習で出てきた用語の用語集（見出しと意味，簡単な説明）を作ることが大事．語学の学習と同じで，最初は用語がわからない．ある程度ボキャブラリーが蓄積しないと，言っていることがわからない．英語の勉強を始めたときに単語帳を作って暗記したように，情報の勉強でも，この初期段階のボキャブラリを蓄積するために用語集を作っておくと，資格試験受験などにも役立ちます．

• 雑誌「合格情報処理」にも，「情報処理の基礎用語」などの付録あり．
• 本屋さんにも色々な用語辞典があります．
• 電子辞書にも情報処理用語の辞書があります．
• Webにも，「e-words」や，「アスキーデジタル用語辞典」や，「Yahoo!コンピュータ用語辞典」など，便利や用語辞典がたくさんあります．

2 SQL実習

RDB(リレーショナルデータベース)のフォーマルな話を学習する前に，本物のRDBMSを使って，SQLの実習をします．

2.1 SQL

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

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

2.2 実習の仕方

学内限定公開のURLでSQLの実習ができるようになっています．URLやユーザ名・パスワードは授業で指示します．このURLで動作している情報システムは，データベースを利用したWebアプリケーションの一例にもなっています．

また，コマンドラインでRDBMSをフル操作することもできます．その方法も授業中に指示します．

2.3 諸注意

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

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

2.4 はじめてのSQL実習

SQLの中でも最も簡単でわかりやすい「データベースへの問い合わせ」をしてみましょう．OPACのような「検索」機能はこの「問い合わせ」機能を用いています．

・全項目の表示

・一部の項目の表示

・条件を満たすデータの表示

・複数の条件を満たすデータの表示

・論理演算；AND，OR，NOT

・曖昧な条件

・データの並び替え

select *
from 新卒個人データ
where 氏名 like '%子%'
;

select *
from 新卒個人データ
where 生年月日 < '1982-01-01'
;

select 氏名, TOEIC
from 新卒個人データ
where TOEIC > 500 and 氏名 like '%子%'
order by TOEIC desc
;

3 SQLの3種類の言語

SQLにも，スキマーの定義と，データの入力編集という2種類の操作＋１がある．

DDL (Data Definition Language);

データベースのデータ構造（スキマー）を定義する言語．ここで主キーを定めたり，外部キー制約を課したりする．

DML (Data Manipulation Language);

データベースのデータを操作する言語．

DCL (Data Control Language);

データベース自身を制御する言語．

3.1 SQL実習（DDL）

3.1.1 テーブル定義

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

テーブルを新規作成（定義）するときに，テーブルの名前だけでなく，テーブルの各属性の名前とデータ型を指定していることに注意してください．

PRIMARY KEYという修飾語で，このテーブルの主キーを指定しています．「主キー」については後で学びます．

3.1.2 テーブルの修正

ALTER TABLE 品物g02c999
ADD COLUMN 材質 CHAR(20);

ADDの他に，DROP，MODIFYなどもあります．

ALTER TABLE 品物g02c999
DROP COLUMN 材質;

3.1.3 テーブルの削除

これは最後にやります．今は見るだけ，操作はしないでください．

DROP TABLE 品物g02c999;

3.2 SQL実習（DML）

3.2.1 挿入

DDLで定義したテーブルにデータを入れます．数字はそのまま書けますが，文字や日付型のデータはシングルクォートで括ります．

INSERT INTO 品物g02c999 VALUES ('00010', 'みかん', 200);

INSERT INTO 品物g02c999 VALUES ('00011', 'りんご', 400);

INSERT INTO 品物g02c999 VALUES ('00012', 'イチゴ', 600);

3.2.2 更新

UPDATE 品物g02c999 SET 値段=値段*0.9 WHERE 品名='みかん';

WHERE修飾を忘れると全部更新されてしまいます!

3.2.3 削除

DELETE FROM 品物g02c999 WHERE 品名='みかん';

WHERE修飾を忘れると全部消えてしまいます!

3.2.4 表示

品物テーブルの全データを表示します．

SELECT *
FROM 品物g02c999;

品物テーブルのデータのうち，条件を満たすデータの，一部の属性を表示します．

SELECT 品名, 値段
FROM 品物g02c999 
WHERE 値段<200;

WHERE句で選択条件を指定できます．

SELECT 品名
FROM 品物g02c999 
WHERE 値段 BETWEEN 200 AND 1000 AND 品名='イチゴ';

SELECT 品名
FROM 品物g02c999 
 ORDER BY 品名 ASC;

昇順表示がASC（ascendant），降順表示がDESC（descendent）です．

3.3 計算

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

SELECT AVG(単価) FROM 商品;

SELECT MAX(単価) FROM 商品;

SELECT MIN(単価) FROM 商品;

SELECT SUM(単価) FROM 商品;

SELECT COUNT(*) FROM 商品;

3.4 SQL（DCL）

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

4 サブクエリー，グループ化，結合，View表

以下の実習は後でやります．

4.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を知らなくても，マウスの売上明細を調べることができる．

4.2 グループ化

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

4.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;

4.4 View表

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

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

VIEW表を削除するには，

DROP VIEW 推薦枠g02c099;

5 RDBのformalな説明

5.1 3層スキーマ

「3層スキーマ」⁽¹⁾

・概念スキーマ；実世界の写絵．リレーショナルデータモデルの（Viewではない）実表が一例．

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

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

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

5.2.1 データモデル

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

5.2.2 データベースの設計

⁽²⁾

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

1. 要件定義（業務分析）
2. 論理設計（ERモデル⁽³⁾，正規化⁽⁴⁾）
3. 物理設計

5.2.3 E-R（実体-関係）図

・実世界は，「実体（Entity）」と，実体間の「関連（Relationship）」で成り立っているとする．

・実体は，実世界の様々なものを個々の実体として認識するのではなく，共通の性質をとらえた抽象的な事物である．例えば，「学生」という実体は，学生番号とか氏名とかの共通の性質（属性）を持った実体．「識別子（primary key，主キー）」で区別できる．

・関連は，実体間の関連を示す．例えば，「学生」という実体と「科目」という実体の間には，「履修」という関連がある．関連には，1対1，1対多，多対多の対応関係（cardinarity）がある．

・実体は「属性（Attribute）」を持つ．属性の一つ（以上）が識別子になる．

・論理データモデルとしてのE-R図を，物理データモデルとしてのリレーショナルデータベース（スキーマ）に変換することができる．

・見方によって，E-R図の作り方が変わりうる．

・E-R図の例；図書の貸し出し，教官と担当科目，注文

5.3 RDBは表ではない

リレーション（relation）；テーブル；表

属性（attribute）；表の列

組（tuple）；表の行

定義域（domain）；データ型

リレーションシップ；リレーション（テーブル）とリレーション（テーブル）の関係．

5.4 集合からみたRDB

定義域（ドメイン）は集合（例；人名の集合，年齢の集合）．

直積；複数の定義域の組み合わせ．

組；直積集合の各要素．

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

組（タプル）；リレーションの各要素．

リレーションはテーブル（表）として表すことができる．

リレーション名；リレーションに付ける名前，テーブルの名前に相当．

属性名；リレーションの各定義域につける名前，テーブル（表）の列名に相当．

5.4.1 集合演算

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

和集合(union)演算

差集合(difference)演算

積（共通）(intersection)集合演算

直積(direct product)演算

直積演算は，別のドメインからなるテーブルでも演算できる．

5.4.2 関係演算

射影(projection)演算

テーブルから列を抜き出す演算になる．このとき，重複した行は取り除く．（集合は重複した要素を含まない．）

選択(selection)演算

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

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

結合(join)演算

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

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

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

商演算

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

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

5.5 リレーションスキマー

リレーションスキーマ；リレーションの各要素（タプル）は変化するが，リレーションの枠組み（スキーム）であるリレーション名や属性名は不変．

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

5.6 キー

候補キー（candidate key）；テーブルの中のタプル（行）を一意に同定することができる属性（の組）．

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

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

キー制約（key constraint）；主キーは必ず値を持っていなければならない．（NULL値ではIDにならない．）

NULL（空値）；値を持っていないこと．

外部キー（foreign key）；他のテーブルの主キーとなっている属性のこと．

外部キー制約（foreign key constraint）；参照している外部テーブルの主キーにない値を使用できない．外部キー制約を使って，複数のテーブルのリレーションシップを表す．

5.7 データを管理しやすいテーブルの要件

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

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

5.7.1 第一正規形（first normal form）

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

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

これでもまだ不完全；タプルの挿入時異常，削除時異常，修正時異常が発生してしまうテーブルがある．

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

5.7.2 第二正規形

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

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

5.7.3 第三正規形

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

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

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

5.7.4 正規化復習

・第2正規化の問題；（☆学生ID，学生氏名，☆履修した科目ID，履修した科目の評価）

・第3正規化の問題；（☆学生ID，☆履修した科目ID，その科目の単位数）

・第2正規化の問題；（☆学生番号，氏名，学年，学部，学部所在地，☆科目名，成績，担当教員）

・第3正規化の問題；（☆学生番号，氏名，学年，学部，学部所在地，☆科目名，成績，担当教員）

・正規化の問題；（☆商品コード，商品名，単価，☆仕入先コード，仕入先社名，仕入先住所）

・正規化の問題；（☆学生コード，学生名，☆授業コード，授業名，履修年度，成績）

6 Appendix:（PostgreSQLの）データ型

日本語は1文字1-3バイト必要．