データベース管理システム DBMS

データベースを管理するためには、体系化が必要である。体系化したものを DBMS と呼び、3 つの機能からなる。

データ定義: データを集めたときに、どのような項目でまとめるか決めること。データの縦列のことは、属性と呼ぶ。横に並んだデータを行と呼ぶ。
データ操作: データ定義に従って関係表にしたものを操作すること。
1. 問い合わせ: 必要なデータを取り出すこと。
2. 更新: データを増減させたり訂正すること。
データ制御: 定義をしたり操作を実行している際に、データベースの内容を過不足なく反映させること。

データベースのデータ格納

データベースの行は、レコードと呼ぶ。レコード操作の基本は以下のとおり。

読込
追加
変更
削除

トランザクション処理

トランザクションとは、あるデータベースの処理を行うときに、他のユーザからのアクセスを受け付けずに進める処理のことである。飛行機の予約、銀行での取引きなどはトランザクション処理である。

ACID 特性

トランザクション処理に必要な概念は次のとおり。頭文字を取り、ACID 特性と呼ぶ。

Atomicity
原子性

データ操作は一連の処理を持って有効または無効となる。一部のみ有効あるいは無効という状態は許されない。
Consistency
整合性
データベースの内容には矛盾が生じない。
Isolation
隔離性

トランザクションは並行処理した場合にも、個別に処理される。順に実行した場合と同じ結果を得る。
Durability
耐久性

終了したトランザクションによりデータベースの変更が行われても、障害によりデータを失うことはない。

ACID 特性のため必要な機能

同時実行制御 (並行処理制御): 複数のアクセスがあるときにトランザクション実行を制御
障害回復: トランザクション中に障害が発生した場合、整合性を失わないこと、操作完了後にデータの耐久性を保証すること。

同時実行制御

同時実行制御をしないと、操作後にデータ更新に失敗することがある。

同時実行制御には、直立化可能性が基準となる。直立化可能性は、並行処理の結果がトランザクションを逐次実行して同じ結果が得られることである。逐次実行の結果は順序に変わることがあるが、並列実行の結果がどちらかに帰属するものであれば結果を正しいとする。

異常レベルの区別は以下の通り。下にいくほど異常の度合いが高くなる。また、下の条件が起きなければ、上の条件は起きない。

ダーティリード Dirty Read: 他のトランザクションによるダーティデータを読むこと。
繰り返し不可能リード: 同一レコードを複数回読んだときにも同じ結果を保証できないこと
ファントム: トランザクション中で同じ条件の読み込み複数回行った場合、同一のレコード集合以外を読んでしまうこと

ダーティデータ
: トランザクション中でコミットされる前のデータ

隔離性のレベルは以下の通り。

未コミットリード: ダーティリード以降全ての異常がおきうる。読み込みのみのトランザクションは可能
コミットリード: 繰り返し不可能リード以降が起きうる。
繰り返し可能リードL ファントムが起きうる。
直列可能化: 上記のレベルを全てクリアし、直列可能性が保証されている状態

直列化可能性判定

競合グラフを書くと、構造を判別し、直列化可能性を判定できる。

ノード 1 つ: 1 つのトランザクション
トランザクション間のアクセス 1 つについて直線でつなぐ。
読みこみのときは直線のみ。
書きこみ 1 つに対して有効エッジ (矢印) をつける。
トランザクションが 2 つ以上、アクセスの順番が逆になる場合を見つける。

競合グラフを書くと、 2 つのトランザクション間で有効エッジ 2 つで巡回する組み合わせがあるか、判定できる。巡回する場合はトランザクションの順序が決定できないので、直列化可能ではない。

グラフに巡回がない場合、ノードに入るエッジのないトランザクションは削除する。残ったグラフは非巡回であるため、次のトランザクションへとつながる。よってこの手続きで直列化可能なトランザクションを組み立てることができる。

直立化可能性を保証する同時実行制御には、2 相ロッキング法がよく使われる。

対象データはアクセスされたらロックする
対象データがロックされていたら他のトランザクションは実行できず、解除まで実行を待たされる
ロックが不要になると解除する。待っていたトランザクションに 1 つがロックを許される

複数ある場合には直列化可能性が保証されないので、複数の場合には、保守的ロッキング法を用いる。すなわち全てのデータにロックをかけ、トランザクション終了後に解除する。

ロックには共有ロックと占有ロックがある。データのアクセス中にトランザクションの閲覧を許すものが共有ロック、書き込みをも許すものが占有ロックである。

デッドロックとは、2 つのトランザクション A, B の実行結果待ちが同時に発生した場合のことである。A のトランザクションで B の実行結果を必要とするのに、 B のトランザクションで A の実行結果を必要とする場合に発生する。

障害回復

障害の種類は以下の通り。

トランザクション処理: プログラムのバグや端末の障害などによる異常終了
システム障害: ハードウェアの異常やソフトウェアのバグなどによるシステムダウン
メディア障害: ハードウェア障害または誤動作によるデータの消去

UNDO や REDO を使って障害回復をはかることができる。

REDO: トランザクションの起こる前まで、全ての処理の取り消しを行う。
UNDO: コミットしたトランザクションの再実行を行う。

トランザクション障害の回復: トランザクション UNDO
システム障害の回復: システム障害を回復させたのち、データベースの回復を行う
- 実行中のトランザクションは全局的 UNDO を行う。
- コミットしたトランザクションの更新したデータが失われた場合は局所的 REDO　を行う。

障害回復の方法はジャーナル (ログ)　をとり、データベースの書き込みの記録をもとに再現する。メディア障害の場合アーカイブを取ったあとからずっとログを追うより、ロールバックをある程度フラッシュする方法がある。これをチェックポイントという。

データの行と列

先週作成したデータ rdb01_ta05001.csv を見よう。列の属性が何か、考えてみよう。

データの型

属性ごとにデータの型を決定する。主な種類は次のとおり。

text	文字列
date	年月日
boolean	真偽値
integer	4 [b]
double precision	15 桁精度

データベースで関係表を作成するには

データをデータベースの関係表 (table) にする。関係表はいくつもあってよい。たとえば名簿と時間割、成績表、などさまざまな表を作ることができる。

まずは練習する表 practice を作ってみよう。

データの行は、属性を右から順に、教員氏名、曜日、時限、セメスタ、講義科目名、教室を作った場合を考える。それぞれ英語で、name, wday, hr, sem, sub, cls としよう。


ta05001=> CREATE TABLE practice (
ta05001(> name text,
ta05001(> wday text,
ta05001(> hr integer,
ta05001(> sem text,
ta05001(> sub text,
ta05001(> cls text
ta05001(> );
CREATE TABLE

CREATE TABLE 表名 (属性1 型名, 属性2 型名, ...);

データベース言語 SQL では実行の終了を ; (セミコロン) で表す。

表の属性のチェック

属性がきちんと存在し、求める型であるか調べるには次のようにする。

ta05001> \d practice

\d 表

record (tuple) へのデータ登録

データベースにデータを載せていこう。INSERT を用いる。

ta05001=> INSERT INTO practice VALUES (
ta05001(> '西村まどか',
ta05001(> '月',
ta05001(> 4, 
ta05001(> '前期'
ta05001(> '公益自由研究'
ta05001(> '102'
ta05001(> );

文字列は '' で囲む。数値は囲わない。

INSERT INTO 表 VALUES ( データ1, データ2, .... );

データのチェック

データを調べるには、SELECT 文にワイルドカード * を使う。


ta05001=> select * from practice;
    name    | wday | hr | sem  |     sub      | cls 
------------+------+----+------+--------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究 | 102
(3 rows)

SELECT * FROM 表

データの更新

例えば間違って登録していた場合に、訂正をしたいとする。


ta05001=> select * from practice;
    name    | wday | hr | sem  |     sub      | cls 
------------+------+----+------+--------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究 | 102
 西村まどか | 月   |  5 | 前期 | 専門演習I    | 102
(2 rows)

通年に直したい。


ta05001=> update practice set sem='通年' where sub='専門演習I';
UPDATE 1

update 表 set 属性='変更' where 属性='変更したい列にしかない属性値'

where 以下を指定し忘れると属性が全て変更されてしまう。変更したい列にしかない属性値になりうるものをプライマリキーと呼ぶ。


ta05001=> select * from practice;
    name    | wday | hr | sem  |     sub      | cls 
------------+------+----+------+--------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究 | 102
 西村まどか | 月   |  5 | 通年 | 専門演習I    | 102
(2 rows)

2 重登録データの削除

2 重登録である上に、1 つのレコードが間違っているので、レコードを削除したい。


ta05001=> select * from practice;
    name    | wday | hr | sem  |     sub      | cls 
------------+------+----+------+--------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究 | 102
 西村まどか | 月   |  5 | 通年 | 専門演習I    | 102
 西村まどか | 月   |  5 | 前期 | 専門演習II   | 102
 西村まどか | 月   |  6 | 前期 | 専門演習II   | 102
(4 rows)

delete from を用いる。


ta05001=> delete from practice where hr=5 and sub='専門演習II';
DELETE 1

実際に削られたか調べてみよう。


ta05001=> select * from practice;
    name    | wday | hr | sem  |     sub      | cls 
------------+------+----+------+--------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究 | 102
 西村まどか | 月   |  5 | 通年 | 専門演習I    | 102
 西村まどか | 月   |  6 | 前期 | 専門演習II   | 102
(3 rows)

SQL コマンドにより大量データのコピー

CSV データはカンマのみで区切る。 EUC コードであることを確認し、Tab を , に変換したファイルにし、これをコピーする。現在本学のバージョンでは Tab 形式のデータを受け付けないため。 Emacs の M-% を使用する際、Ctrl-v TAB とし、TAB 文字を入力する。


ta05001=> COPY practice FROM '絶対パスのta05001e.csvの位置' DELIMITER ',';
COPY

通常のユーザ登録では権限が与えられてない。失敗する場合は PostgreSQL のコマンドにより複製が可能である。

Ruby CGI の復習

基礎プログラミングII (第 9 講) / 基礎プログラミングII (第 10 講)

大量データを権限がない場合にコピーしたい

Postgres なら権限が与えられていなくてもコピーできる。

ta05001=> \copy practice from '/home/irhome/c105/ta05001/RDB/ta05001e.csv' with CSV HEADER

最初からデータをコピーするには、一度消しておいてからの方がよいかもしれない。その場合

ta05001=> DELETE FROM practice *;
DELETE データ数

でやりなおせる。もちろん新しいテーブルを作ってもよい。

テーブルを消してしまうと全てのデータを作り直すことになるが、

ta05001=> DROP TABLE practice;

も使える。

問い合わせ

データベースから必要なデータだけを取り出すことを問い合わせという。

SELECT 属性1, 属性2, ... FROM 表 WHERE 条件

選んだ属性だけで表を作って表現される。

* はワイルドカードで、全てを取り出す。

探索条件

不等号を用いて、インスタンスの比較を行うことができた。午後のデータだけを取り出してみよう。


ta05001=> select * from practice where hr > 2;
    name    | wday | hr | sem  |     sub      | cls 
------------+------+----+------+--------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究 | 102
 西村まどか | 月   |  5 | 通年 | 専門演習I    | 102
 西村まどか | 月   |  6 | 前期 | 専門演習II   | 102
(3 rows)

>=　以上, <= 以下も使える。

異なるものだけを取り出したいとき

異なるものだけを取り出しておく場合は、DISTINCT を用いる。教員が講義のある曜日だけを取り出したいとき、


ta05001=> SELECT DISTINCT wday from practice where name='西村まどか';
 wday 
------
 火
 月
 水
(3 rows)

SELECT DISTINCT 属性 from 表

並べ替えて表示したいとき

並べ替えて表示したいとき、ORDER BY 属性を用いる。大きな順は、ORDER BY 属性 DESC と指定する。

ORDER BY 属性
ORDER BY 属性 DESC

午後の講義を遅い順から表示してみよう。


ta05001=> SELECT name,hr FROM practice WHERE hr >= 3 ORDER BY hr;
    name    | hr 
------------+----
 西村まどか |  4
 西村まどか |  5
 西村まどか |  6
(3 rows)



DESC をつけた結果はどうなるか。

集合関数


データを計算することができる。




 COUNT: 総数
 SUM: 総和
 AVG: 平均
 MAX: 最大値
 MIN: 最小値



SELECT 関数(*) 
SELECT 関数(DISTINCT 属性)


公益ルビ緒が登録したデータベースにおいて、
教員の担当している講義数を数えるには


ta05001=> SELECT COUNT(*) FROM practice WHERE name='西村まどか';
 count 
-------
     9
(1 row)


水曜日に教員が講義をしている時限の範囲を調べるには


ta05001=> SELECT MAX(hr), MIN(hr) FROM practice WHERE wday='水';
 max | min 
-----+-----
   2 |   1
(1 row)


AS N'日本語' をつけると、日本語で属性を表すことができる。

グループ化して考えるには

教員が講義が終了する時限はいつかを調べるには、
曜日ごとに最大の時限がどこなのか調べればよい。


ta05001=> SELECT wday, MAX(hr) FROM practice GROUP BY wday;
 wday | max 
------+-----
 火   |   2
 月   |   6
 水   |   2
(3 rows)


グループに条件を課すには

グループでさらに条件をつけたい場合は、HAVING をつける。


ta05001=> SELECT wday, MAX(hr) FROM practice GROUP BY wday HAVING wday='月';
 wday | max 
------+-----
 月   |   6
(1 row)


月曜日は 6 限終了後ならばよいということが分かる。

データの更新


データの一部を更新する必要があるとき、UPDATE を使う。



UPDATE 表 SET (変更したい属性1, 変更したい属性2, ...)
VALUES ('値1', '値2', ...)



もともと空欄にしておく場合


「情報処理基礎論a」の講義時間が、
担当者は決まったが、時間割がまだ決定されていない。このとき、
hr, wday は指定できない。指定できないとき、NULL を代入しておくことができ
る。



ta05001=> INSERT INTO practice VALUES ('西村まどか',NULL,NULL,'後期','情報処理基礎論a','102');
INSERT 0 1
ta05001=> select * from practice;    name    | wday | hr | sem  |         sub          | cls 
------------+------+----+------+----------------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究         | 102
 西村まどか | 月   |  5 | 通年 | 専門演習I            | 102
 西村まどか | 月   |  6 | 前期 | 専門演習II           | 102
 西村まどか | 火   |  1 | 前期 | 基礎プログラミングIB | 101
 西村まどか | 火   |  2 | 前期 | 基礎プログラミングIC | 101
 西村まどか | 水   |  1 | 前期 | 情報交換概論         | 102
 西村まどか | 火   |  1 | 後期 | 基礎プログラミングII | 101
 西村まどか | 火   |  2 | 後期 | 基礎プログラミングII | 101
 西村まどか | 水   |  2 | 前期 | 基礎の自然科学(物理) | 102
 西村まどか |      |    | 後期 | 情報処理基礎論a      | 102
(10 rows)


UPDATE してみよう。


ta05001=> UPDATE practice SET hr=1, wday='水' where sub='情報処理基礎論a';
UPDATE 1
ta05001=> select * from practice;
    name    | wday | hr | sem  |         sub          | cls 
------------+------+----+------+----------------------+-----
 西村まどか | 月   |  4 | 前期 | 公益自由研究         | 102
 西村まどか | 月   |  5 | 通年 | 専門演習I            | 102
 西村まどか | 月   |  6 | 前期 | 専門演習II           | 102
 西村まどか | 火   |  1 | 前期 | 基礎プログラミングIB | 101
 西村まどか | 火   |  2 | 前期 | 基礎プログラミングIC | 101
 西村まどか | 水   |  1 | 前期 | 情報交換概論         | 102
 西村まどか | 火   |  1 | 後期 | 基礎プログラミングII | 101
 西村まどか | 火   |  2 | 後期 | 基礎プログラミングII | 101
 西村まどか | 水   |  2 | 前期 | 基礎の自然科学(物理) | 102
 西村まどか | 水   |  1 | 後期 | 情報処理基礎論a      | 102
(10 rows)


不要な行を削除するには


DELETE FROM 表 WHERE 探索条件


WHERE がないと表がすべて消える。

曜日をいっせい変更

ORDER BY を曜日、時限で使用したいので、教員氏名と、
時間と曜日で打ち出した。


ta05001=> SELECT name,hr,wday,sem FROM practice ORDER BY wday;
    name    | hr | wday | sem  
------------+----+------+------
 西村まどか |  2 | 火   | 前期
 西村まどか |  1 | 火   | 後期
 西村まどか |  2 | 火   | 後期
 西村まどか |  1 | 火   | 前期
 西村まどか |  4 | 月   | 前期
 西村まどか |  5 | 月   | 通年
 西村まどか |  6 | 月   | 通年
 西村まどか |  2 | 水   | 後期
 西村まどか |  1 | 水   | 前期
 西村まどか |  2 | 水   | 前期
 西村まどか |  1 | 水   | 後期


時間割に書くためには、曜日は
月、火、水、木、金、... と並べて出力させたいのだが、
漢字コード順であるため、月曜日が先頭に来ない。


曜日は、日曜日が 0, 月曜日が 1, ..., 土曜日が 6 
という数字を通常割り当てる。


UPDATE コマンドを使用して、月曜日は 1, 火曜日は 2 ... 
と変更しておこう。


ta05001=> UPDATE practice SET wday ='1' where wday='月';
UPDATE 3
ta05001=>  SELECT name,hr,wday FROM practice where sem='前期' ORDER
BY wday;
    name    | hr | wday 
------------+----+------
 西村まどか |  4 | 1
 西村まどか |  1 | 2
 西村まどか |  2 | 2
 西村まどか |  1 | 3
 西村まどか |  2 | 3
(5 rows)


全部でき上がるとこのようになる。


ta05001=> SELECT name,hr,wday,sem FROM practice ORDER BY wday;
    name    | hr | wday | sem  
------------+----+------+------
 西村まどか |  6 | 1    | 通年
 西村まどか |  5 | 1    | 通年
 西村まどか |  4 | 1    | 前期
 西村まどか |  1 | 2    | 後期
 西村まどか |  2 | 2    | 後期
 西村まどか |  1 | 2    | 前期
 西村まどか |  2 | 2    | 前期
 西村まどか |  1 | 3    | 前期
 西村まどか |  2 | 3    | 前期
 西村まどか |  1 | 3    | 後期
 西村まどか |  2 | 3    | 後期

wday のあと hr でも入れ替えをしないといけない。また、
前後期でも分けられるようにする必要がある。
, でつないでおく。

ta05001=> SELECT name,hr,wday FROM practice where sem='前期' ORDER BY wday,hr;
    name    | hr | wday 
------------+----+------
 西村まどか |  4 | 1
 西村まどか |  1 | 2
 西村まどか |  2 | 2
 西村まどか |  1 | 3
 西村まどか |  2 | 3
(5 rows)


sem は通年も含まれるので、AND/OR あるいは NOT 条件を増やして、
取り出せるようにしよう。取り出し方の例として

ta05001=> SELECT name,hr,wday FROM practice where sem='前期' OR sem='通年' ORDER BY wday,hr;
 
切り取ったデータ
と、
時間割にするプログラム

と、打ち出した結果

ta05001=> SELECT name,hr,wday FROM practice where NOT sem='後期' and
name='西村まどか' ORDER BY wday,hr;

切り取ったデータを CSV 形式で保存するには
 psql -d ta05001 -c 'select wday,hr from practice order by wday,hr desc;' -A -F, > result.csv 

, で区切られて出力される