SQL(5) トランザクション

Image from Gyazo

データベースの復元

前回までに作成したデータベースを読み込むには,[File]→[Open DB]をクリックし, 保存した sqlite4.db を選択します. 復元したデータベースに【学生リスト】,【学部リスト】,【テニス部】,【サッカー部】,【商品リスト】,【八百屋】,【果物屋】があることを確認しましょう.

sqlite4.db

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか
D006 山下隆 4 経営情報学部 やましたたかし

【学生リスト】

学部 キャンパス 教員数
工学部 春日井 30
人文学部 春日井 20
国際関係学部 名古屋 25

【学部リスト】

学籍番号 氏名 学年 学部 よみがな
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【テニス部】

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
B003 杉江弘子 3 人文学部 すぎえひろこ
A005 内田弘 3 工学部 うちだひろし

【サッカー部】

商品ID 商品名 仕入れ値 売値
C01 テレビ 50000 65000
C02 エアコン 60000 70000
C03 冷蔵庫 80000 55000
C04 電卓 3000 2000
C05 炊飯器 20000 25000

【商品リスト】

商品番号 商品名 価格
G1 ニンジン 100
G2 トマト 200
G3 スイカ 500

【八百屋】

商品番号 商品名 価格
G4 イチゴ 300
G5 ブドウ 200
G3 スイカ 500

【果物屋】

トランザクション

先ずは,トランザクションについて見ていきましょう. トランザクションとは,データベースに対する分割不可能な一連の処理のことを意味しています. 例えば,学籍番号が A002 の学生が 人文学部 に転学部したとします. このとき,下記の二つの処理が必要です.

もしこの処理が分割されて実行された場合, 処理の途中において【学生リスト】では 人文学部 であるのに,【テニス部】では 工学部 のままとなり矛盾が生じてしまいます. このように矛盾が生じないように,データベースに対する複数の処理を, トランザクションとしてまとめて実行することができます.

また,トランザクションが満たすべき性質として ACID と呼ばれる概念が存在します.

ACID

  • 原子性(Atomicity) トランザクションに含まれる処理は,全て実行されるか,全く実行されないことが保証される.
  • 独立性(Isolation) トランザクションの実行過程は隠蔽される(他者から観測できない).
  • 一貫性(Consistency) トランザクションの終了時に矛盾が発生する処理は実行されない(中断される).
  • 永続性(Durability) トランザクションが完了したら,その結果は失われない.

トランザクションをスタートするには,BEGIN TRANSACTIONと入力します.

/* トランザクションのスタート */
BEGIN TRANSACTION;

トランザクションをスタートすると,データベースがロックされ,他者は書き込みが出来なくなります (正確にはデータベースへの最初のアクセスがあった時点でロックされる). それでは,学籍番号が A002 の学生が,人文学部 に転学部したことを想定し, 【学生リスト】と【テニス部】のレコードをUPDATE文で更新してみましょう.

/* 学生リストの更新 */
UPDATE 学生リスト SET 学部="人文学部" WHERE 学籍番号="A002";
/* テニス部の更新 */
UPDATE テニス部 SET 学部="人文学部" WHERE 学籍番号="A002";

Image from Gyazo

Image from Gyazo

【学生リスト】と【テニス部】を確認すると, 確かに学籍番号が A002 の所属が 人文学部 に変更されていることが確認できます. しかし,実はこの時点では,更新は確定されておらず,更新前の状態に戻すことが可能です. 前の状態に戻すことを ロールバック と言います. ロールバックするには,ROLLBACK TRANSACTIONと入力します.

/* トランザクションのロールバック */
ROLLBACK TRANSACTION;

ロールバックすると,【学部リスト】と【テニス部】は更新前の状態に戻っていることが確認できます.

Image from Gyazo

Image from Gyazo

それでは,もう一度,トランザクションをスタートして, 先程と同じように【学部リスト】と【テニス部】を更新しましょう.

BEGIN TRANSACTION;
UPDATE 学生リスト SET 学部="人文学部" WHERE 学籍番号="A002";
UPDATE テニス部 SET 学部="人文学部" WHERE 学籍番号="A002";

今度は,更新を確定しましょう. 確定するには,COMMIT TRANSACTIONと入力します. これで,更新は確定され,データベースに対するロックも解除されます.

COMMIT TRANSACTION;

このように,トランザクションの処理は全て実行されるか,または, ロールバックで全て取り消されるか,どちらかの結果となります.

例題1

トランザクションを利用して【八百屋】と【果物屋】のスイカの価格を700円に変更しなさい.

Image from Gyazo

Image from Gyazo

ビュー

テーブルに対するクエリの結果を ビュー として保存することができます. 保存された ビュー はテーブルと同じように扱うことができます. ここでは,【学生リスト】と【学部リスト]の自然結合を【大学リスト]という名前のビューとして保存します.

CREATE VIEW 大学リスト AS SELECT * FROM 学生リスト natural JOIN 学部リスト;

SELECTで【大学リスト】のレコードを表示すると,【学生リスト】と【学部リスト]の自然結合が表示されます.

SELECT * from 大学リスト;

Image from Gyazo

例題2

【八百屋】と【果物屋】の和集合演算の結果を【スーパー】という名前のビューとして保存しなさい.

トリガー

テーブルに追加・削除・更新などのクエリが適用されたときに自動的に実行する処理を トリガー として登録することができます. ここでは,【売上商品】と【売上日時】のテーブルを作成し,【売上商品】にINSERTが発生したとき,同じタイミングで【売上日時】に時刻を記録する処理を【売上ログ】という名前のトリガーとして登録します.

CREATE TABLE 売上商品(
	ID INTEGER PRIMARY KEY AUTOINCREMENT,
  商品名 TEXT
);
CREATE TABLE 売上日時(
	ID INTEGER PRIMARY KEY AUTOINCREMENT,
  日時 TEXT
);
CREATE TRIGGER 売上ログ AFTER INSERT ON 売上商品
	BEGIN
  	INSERT INTO 売上日時(日時) VALUES(datetime());
  END;

【売上商品】にパソコンをINSERTすると, 【売上日時】に2021-04-24 10:39:17が記録されていることがわかります.

INSERT INTO 売上商品(商品名) VALUES("パソコン");

Image from Gyazo

Image from Gyazo

データベースの保存

作成したデータベースを保存するには,[File]→[Save DB]をクリックします. 保存したファイルは sqlite5.db とファイル名を変更してから,提出してください.

情報処理技術者試験・過去問

下記リンクは ITパスポート試験ドットコム基本情報技術者試験ドットコム応用情報技術者試験ドットコムに掲載されている問題です.

参考書籍

愛知県名古屋市にある椙山女学園大学 文化情報学部 向研究室の公式サイトです. 専門は情報科学であり,人工知能やデータベースなどの技術要素を指導しています. この公式サイトでは,授業で使用している教材を公開すると共に, ベールに包まれた女子大教員のミステリアスな日常を4コマ漫画でお伝えしていきます. サイトに関するご意見やご質問はFacebookまたはTwitterでお問い合わせください.