第１１講　関係データベース　 1講 2講 3講 4講 5講 6講 7講 8講 9講 10講　 12講 13講 14講

コンピュータの基礎第１１講
関係データベース

【第１１講のポイント】
　前講では、データ（レコード）ファイルの編成法について、アクセス速度の観点も含めて議論してきた。本講では、データベースについて学ぶ。データベースは、現在のコンピュータシステムおいて最も基本的な役割を果たすサービスプログラムである。

【第１１講の目標】学習後、以下のことが身についたかチェックしよう。
  1. データベースの機能と役割を理解する
  2. 関係データベースの次の操作について理解する
    　射影、選択、直積、結合
  3. SQLによる問合せについて理解する。
  4. 表(テーブル)設計の基本的考え方を理解する。

【第１１講の構成】
  1. DBMS
  2. 関係データベース
  3. 射影操作
  4. 選択操作
  5. 直積操作
  6. 結合操作
  7. SQLの例題と問題
  8. 表の設計

本書の見方
ページをめくる
左(右)ページの左(右)端クリックまたはドラッグする
左(右)矢印キーを押す
１ページ分の移動
左右上端にある[Next]、[Previous]タブをクリックする
章の頭に移動
右上の【章の移動】タブをクリックし、現れた章を選ぶ
ページのジャンプ
右上のページ番号表示タブをクリックし、現れたページを選ぶ
画像の拡大およびリンク先への移動
画像やリンク先をクリックすれば、別ウィンドウ(タブ)で表示される

この教科書はBookletを利用して作成しています。

第１節　データベース管理システム(DBMS)

　データベースとは、関連するデータファイルを集めて整理･統合し、データベース管理システム(DBMS)を通して一元的に管理・利用する仕組みである。
　データベースが導入される以前は、大学でいえば、学生のデータファイルを教務課、学生課、経理課等がそれぞれ別々に管理していた。学生が住所を変更したとしよう。学生は、関連するすべての部署に住所変更届を出さなければいけないか、あるいは一ヶ所ですむとしたら、受け付けた部署が他のすべての部署に連絡しなければならない。ここで、変更届の提出漏れあるいは連絡漏れがあれば、ある部署では住所が更新されているのに漏れのあった部署では古いままというデータの不整合が生じる。このように、複数の部署が同じような(微妙に異なる)データを同じような(微妙に異なる)プログラムで別々に保守・管理するのは無駄だし、間違いも起こりやすい。
　すべての部署（のプログラム）がデータベース(管理システム)を共有してその機能を利用することの利点としては、
  1. データファイルの共有化により、重複したフィールド(項目)が一元化される
  2. ユーザ(個々のプログラム)はデータ管理の詳細に煩わされなくなる
  3. ユーザ(個々のプログラム)は高度な利用が簡単に行えるようになる
  4. データの(構成や編成法の)変更が、利用プログラムに影響を与えない
等が考えられる。そのためデータベース管理システムは、次の機能を持つ。
  1. データの集中管理：整合性の維持、機密保全、障害回復等
  2. データ構造の定義、再構築
  3. データの操作：問合せ、レコードの追加、削除

　データベースには、その表現モデル（第２講第４節参照）によって、ネットワーク型データベース、階層型データベース、関係データベースがある。現在は、もっぱら関係データベースが使われており、次節以降、より詳しく学んでいこう。

*問１１．１． （ヒント）
  1. 階層型データベースについて説明せよ
  2. ネットワーク型データベースについて説明せよ
  3. データベースが持つ機能とその機能により得られる利点を対応付けて説明せよ

第２節　関係データベースと SQL

学生
番号氏名住所
１０１青森太郎多摩市
１０２岩手陽子八王子市
１１９兵庫健多摩市
関係モデルを用い、レコード(データ)を(複数の)表(テーブル)で保持する。
  1. 表の各列には項目(フィールド)名が付く
  2. 表の各行がレコードで、各項目に対する値の組である
このようなレコードの集まりを数学では「関係」と呼ぶ。
成績
番号科目点数
１０１数学８０
１０１国語７０
１０２国語９０
１１９数学６０

SQL による表作成

SQLは、関係データベースへの操作を記述する言語である。SQLite compiled to javascript（sql.js）を使ったデモページを利用して、SQLについて学んでゆく。詳しく学ぶには、SQLiteの解説がある。

問１１．２．次頁の SQL interpreter で以下の操作を行え （ヒント）
  1. 以下のSQLをコピペし元のSQLと置換えて実行【Execute】し、学生、成績の表を作成せよ
    --表のクリアと構造定義（表の枠組み作成）
    DROP TABLE IF EXISTS 学生; DROP TABLE IF EXISTS 成績;
    CREATE TABLE 学生(番号 integer,氏名 text,住所 text);
    CREATE TABLE 成績(番号 integer,科目 text,点数 integer);
    --データの挿入
    INSERT INTO 学生 VALUES (101,'青森太郎','多摩市');
    INSERT INTO 学生 VALUES (102,'岩手陽子','八王子市');
    INSERT INTO 学生 VALUES (119,'兵庫健','多摩市');
    INSERT INTO 成績 VALUES (101,'数学',80);
    INSERT INTO 成績 VALUES (101,'国語',70);
    INSERT INTO 成績 VALUES (102,'国語',90);
    INSERT INTO 成績 VALUES (119,'数学',60);
    SELECT * FROM 学生; SELECT * FROM 成績;-->表の表示
  2. 後の問用に作成データベースを保存【Save db】(ダウンロード)せよ。
  3. レコード (120,'愛媛雅美','八王子市') を追加するSQL命令を書き、正しく動くことを確認せよ。
2.の注．ダウンロードファイル(例えばsql.db)はSQL Intepreterにロード【Load db】するものなので、パソコンに対応アプリ(SQLite)をインストールしなくてよい。（開けないというメッセージがでても構わない）
射影 各レコードの指定された項目だけを取り出す操作で、例えば次の SQL文は、学生表の番号と住所の項目のみからなる表を作成する。
学生
番号氏名住所
１０１青森太郎多摩市
１０２岩手陽子八王子市
１１９兵庫健
SELECT 番号, 住所
FROM 学生;
⇒
　
番号住所
１０１多摩市
１０２八王子市
１１９多摩市

選択 指定された条件をみたすレコードを取り出す操作で、例えば 次の SQL文は、成績表から科目項目の値が「数学」であるレコードが選択される。

成績
番号科目点数
１０１数学８０
１０１国語７０
１０２国語９０
１１９数学６０
SELECT *
FROM 成績
WHERE 科目='数学';
⇒
　
番号科目点数
１０１数学８０
１１９数学６０

問１１．３．次ページの SQL interpreter 内で以下の操作を行え （ヒント）
  1. 前ページで作成・保存したデータベースをロード【Load db】せよ
  2. 次のSQLをコピペし元のSQLと置換えて実行【Execute】し、射影、選択命令の結果を確認せよ
    SELECT * FROM 学生;-->全項目表示
    SELECT 番号, 住所 FROM 学生;-->番号, 住所の表示
    SELECT * FROM 成績;-->表全体の表示
    SELECT * FROM 成績 WHERE 科目='数学';-->科目の値が'数学'のレコードを表示
  3. 次の事を行う SQL命令を書き、実行して正しいことを確認せよ
    1. 学生の表から氏名と住所の項目を取り出す
    2. 成績の表で点数が80以上(<=80)のレコードを選択する
    3. 成績の表の科目が数学でかつ(AND)点数が80点以上のレコードを選択する
1.の注．ファイルを選択をクリックし、前問で保存したデータベースファイルを指定して【Load db】する。この操作は前問の1.の操作を繰り返しても同じである。

直積 指定された複数の表中のレコードの全組合せを作る操作である。
SELECT * FROM 学生, 成績;
学生
番号氏名住所
１０１青森太郎多摩市
１０２岩手陽子八王子市
１１９兵庫健多摩市
⇒
　
番号氏名住所番号科目点数
１０１青森太郎多摩市１０１数学８０
１０１青森太郎多摩市１０１国語７０
１０１青森太郎多摩市１０２国語９０
１０１青森太郎多摩市１１９数学６０
１０２岩手陽子八王子市１０１数学８０
１０２岩手陽子八王子市１０１国語７０
︙︙︙︙︙︙
成績
番号科目点数
１０１数学８０
１０１国語７０
１０２国語９０
１１９数学６０


結合 直積で多量に生じる無意味なレコードを除くため、表の間で共通する項目の値が一致するレコードのみを選択する操作である。次の例のように複数の表に同じ項目名が現れる時は 表名.項目名 と指定して区別する。

SELECT *
FROM 学生,成績
WHERE 学生.番号=成績.番号;
⇒
(学生.)
番号		氏名住所(成績.)
番号		科目点数
１０１青森太郎多摩市１０１数学８０
１０１青森太郎多摩市１０１国語７０
１０２岩手陽子八王子市１０２国語９０
１０９兵庫健多摩市１０９数学６０

参考．値の一致をとる項目の項目名が同一の場合、WHERE句を省略して次のようにも書ける：   SELECT * FROM 学生 NATURAL JOIN 成績;

*問１１．４．次ページの SQL interpreter 内で以下の操作を行い、問に答えよ （ヒント）
  1. 本節の最初に作成・保存したデータベースをロード【Load db】せよ
  2. 以下のSQLをコピペし元のSQLと置換えて実行【Execute】し、直積、結合命令の結果を確認せよ
    SELECT * FROM 学生; SELECT * FROM 成績;-->表全体の表示
    SELECT * FROM 学生, 成績;-->直積
    SELECT * FROM 学生, 成績 WHERE 学生.番号=成績.番号;-->結合
  3. レコード数mの表とレコード数nの表の直積は何個のレコードを持つか
  4. 直積では無意味なレコードが多量に生じるが、それはどのようなレコードか説明せよ
  5. 実習の結合操作で、重複して同じ値を持つ項目を削除するように命令の射影指定を修正し、実行して正しさを確認せよ。

第３節　SQLの例題と問題

これまでの学習を応用して、次のような問合せの SQL を作成してみよう。学生(番号,氏名,住所)と成績(番号,科目,点数)の表があるとする。
1. 国語で80点以上の学生の番号と点数は、成績の表だけからわかるので
SELECT 番号, 点数 -->番号と点数の項目を表示(射影)
FROM 成績 -->成績の表から
WHERE 科目='国語' AND 点数 >= 80; -->科目が国語で点数が80点以上をレコード選択
2. 国語で80点以上の学生の氏名と点数は、氏名が学生の表の項目で、点数が成績の表の項目だからそれらを結合(直積)する必要がある。
SELECT 氏名, 点数 -->氏名と点数の項目を表示(射影)
FROM 学生, 成績 -->学生と成績の直積
WHERE 学生.番号=成績.番号 -->表の結合
AND 科目='国語' AND 点数 >= 80; -->科目が国語で点数が80点以上をレコード選択

*問１１．５．次ページの SQL interpreter 内で以下の操作を行い、問に答えよ （ヒント）
  1. 前節の最初に作成・保存したデータベースをロード【Load db】せよ
  2. 以下のSQLをコピペし元のSQLと置換えて実行【Execute】し、結果を確認せよ
    SELECT * FROM 学生; SELECT * FROM 成績;-->表全体の表示
    -----国語で80点以上の学生の番号と点数----------------
    SELECT 番号,点数 FROM 成績 WHERE 科目='国語' AND 点数 >= 80;
    -----国語で80点以上の学生の氏名と点数----------------
    SELECT 氏名,点数 FROM 学生,成績 WHERE 学生.番号=成績.番号 AND 科目='国語' AND 点数 >= 80;
  3. 次の問合せを行う SQL命令を書き、実行して正しいことを確認せよ
    1. 多摩市の学生の氏名
    2. 110番以前で八王子市の学生の氏名
    3. 多摩市の学生の氏名と科目と点数

第４節　表(テーブル)の設計

番号 氏名 科目 点数 住所
１０１ 青森太郎 数学 ８０ 多摩市
１０１ 青森太郎 国語 ７０ 多摩市
１０２ 岩手陽子 国語 ９０ 八王子市
１１９ 兵庫健 数学 ６０ 多摩市
　これまで、学生の表と成績の表を分けて考えてきた。しかし、組合せて利用する場合があるのなら、最初から分けないで次のような表で保存しておいてもよさそうである。
　しかし、右の表には次のような問題点がある。
  1. データの重複がある：入力間違いや不整合の原因になる
  2. キー項目が複雑：その値からレコードが１つに定まる項目(の組)で、レコードの効率的な探索(追加・削除)に必要である。上の表では(番号,科目)。
その解決策として、以下が考えられ、特に 1. の処理を施せば本講の学生と成績の表になる。
  1. 表中に部分的なキー項目とそれから値が定まる項目があれば、別の表として分離し、元の表からキー項目以外を削除する
  2. キー項目(例えばレコード番号)を追加する

問１１．６． （ヒント）
  1. (番号、氏名、科目、点数、住所)という構造（項目の組）を持つ表から、分離すべきキーと項目を示し、適切な表に分割せよ。
  2. (ISBN、書名、著者名、購入日、出版社名、出版社電話)という項目を持つ蔵書データベースを作りたい。適切な表(複数)の構造（項目の組）を示せ。