データベースことはじめ
データベースとは、構造化した情報またはデータの組織的な集合であり、通常はコンピューター・システムに電子的に格納されています。
例 )
社員管理システム
生産管理システム
データベースにはいくつか種類があるが、よく最初に勉強するのはリレーショナル・データベース（RDS）です。
リレーショナル : relational
リレーショナルデータベース
1980年代に主流となったデータベース。
各項目は、列と行を持つテーブルの集合として編成されています。
構造化された情報にアクセスする最も効率的で柔軟な方法を提供します。
データベースの管理は（そのままですが）データベース管理システム（DBMS）が行う。
なぜデータベースを利用するのか？
大量のデータを取り扱うため
素早くデータを取り出すため
複数のユーザーで同時に利用するため
セキュリティを担保するため
バックアップなど耐障害性を備えるため
リレーショナル・データモデルの基礎 (用語の整理)
https://gyazo.com/e93ddfe61c611ad28c94ceebeaacfbbc
エンティティ（・タイプ）
システム化対象世界（業務）から、管理すべき事物（定義情報）
エンティティは一つ以上の属性からなる
属性が具体的な値を持ったものを「インスタンス」という
ER図
実世界をエンティティ（Entity：実体）とリレーションシップ（Relationship：関連）でモデル化した図
論理名
人からみてわかりやすい名前
仕様書で書かれている名前
物理名
コンピュータからみてわかりやすい名前
そのままテーブルの定義情報として利用されている
カーディナリティ（Cardinality）
カーディナリティには以下3種類がある。
表記方法として矢印で示す場合もある。
1対1
https://gyazo.com/08c3a3658c6aad400bceafb1ad5e8712
「学生は一つの学生証を所持する」という関係
他）
商品 - 発注
見積 - 請求
所感) 概念としてはわかりやすいが、具体例を出すのはけっこう難しい。
パターンとしては少ないのでは？
「一つのテーブルにもできるじゃん」というのもある。
1対多
https://gyazo.com/6421b612c0ae8708bfcc5b4337e13e5d
「研究室に複数の学生が所属できる」という関係
多対多
https://gyazo.com/8c6374340f39c317d7884b857aab34ea
「学生は複数のサークルに所属できる」という関係
「サークルには複数の学生が所属できる」という関係
ちょっと発展
実際のテーブルを想像すると、このままだと扱いずらかったりする。
「中間テーブル」というものを用意して「1対多」の関係にすることが多い印象。
「多対多」だと思ったときは設計を見直すサインかも
情報技術者試験の問題
もう一度、ER図を見てみる。
「〜〜1つに対して」がキーワード
テーブル
https://gyazo.com/2055f6757315e6f2f809747d206736fd
列 : カラム
行 : レコード
一つの項目 : フィールド
主キー （Primary Key）
https://gyazo.com/86bab360d145a7733dec373ae5dd2b1b
テーブル内でレコードを一意に識別できるように指定される項目
RDBでは必ず主キーが存在する。
主キーはRDBの「制約」の一つと捉えることができる。
RDBの制約
NOT NULL制約
null値を禁止
キーワード : NOT NULL
一意制約
重複したデータを禁止
複数の列に設定可能
NULLを禁止するわけではない
キーワード : UNIQUE KEY
主キー制約
一意制約 + NOT NULL制約
ただし、1つのテーブルに1つのみ
複合主キーはある
キーワード : PRIMARY KEY
チェック制約
条件を指定し、条件を満たさないデータを禁止
キーワード : CHECK
参照整合性制約
他のテーブルの列を参照し、その列にないデータを禁止
キーワード : FOREIGN KEY
デフォルト制約
値が指定されていないときに、自動で値を設定する。
キーワード : DEFAULT
余談
私はあまり「チェック制約」は書いたことがない。
「参照整合性制約」をつけると、SQL文の実行順がシビアになるので注意が必要。
SQL
Structured Query Language
構造化された問い合わせ言語
RDBを操作するための言語
SQLの操作は以下の3つに大別される。
DML
Data Manipulation Language データ操作言語
主にデータの作成や閲覧、更新、削除などの命令
INSERT
SELECT
UPDATE
DELETE
CRUD操作とも呼ばれる。
C (Create) R(Read) U(Update) D(Delete)
INSERT, SELECT, UPDATE, DELETE
CRUD図なるものがあったりする。
DDL
Data Definition Language データ定義言語
テーブルそのものを作成・削除や、列の追加・削除などができる
主な命令
CREATE
ALTER
DROP
TRUNCATE
テーブルごと削除してから再作成する命令
DCL
Data Control Language データ制御言語
利用者へのデータベースへのアクセス権の制御やデータベースの状態管理などを行なうためのもの
GRANT 権限を与える
REVOKE 権限を奪う
COMMIT トランザクションの確定
ROLLBACK トランザクションの破棄
参考
☆はじめは「DML」をマスターしよう。
トランザクション
トランザクションは、データベースをある一貫した状態から別の一貫した状態へ変更するアクションを1つに束ねたものである。
例
https://gyazo.com/676a165d5c6b51175a1d102b50fdb33c
「A口座からB口座に5万円移動させる」という処理がある。
これは実際は以下の二つの処理で成り立つものである。
①A口座から5万円出金
②B口座に5万円入金
上記①②は一つに束ねてトランザクションで処理されるものである。
「トランザクションで処理される」ということはどちらかの処理が失敗したら、全ての処理が失敗する、ということである。
もしトランザクション内で処理されない場合、以下のようなケースが発生する。
「①A口座から5万円出金」だけ失敗する。
総額 + 5 万円になる。
https://gyazo.com/ea313d05843e1c0a0adfd1ddac333d03
「②B口座に5万円入金」だけ失敗する。
総額 - 5万円になる。
https://gyazo.com/d4a7c0ea01635b097fa2a190ec484045
トランザクション体感しよう
autocommitの確認
code:console
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
autocommitが「1 (true)」で動作している場合、一つの処理に複数の処理を含めることはできない。
MySQLはデフォルトで有効。
autocommitを無効化
code:console
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
no = 1001の田中太郎さんのsalを「310000」に更新する。
code:console
mysql> update emp set sal = 310000 where no = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
もう一つMySQLのコマンドのプロント(B)を開いて、no = 1001の田中太郎さんのデータを確認する。
更新している人とは違う人がデータベースの中身を参照しているイメージ。
code:console
mysql> select * from emp where no = 1001;
+------+----------+------------+--------+----------+-------+
| no | name | hiredate | sal | quitdate | posno |
+------+----------+------------+--------+----------+-------+
| 1001 | 田中太郎 | 2010-04-01 | 300000 | NULL | 1 |
+------+----------+------------+--------+----------+-------+
1 row in set (0.00 sec)
更新されていない。
元のMySQLのコマンドのプロント(A)でcommit文を実行する。
code:console
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
改めてコマンドのプロント(B)で同じselect文を実行する。
code:console
mysql> select * from emp where no = 1001;
+------+----------+------------+--------+----------+-------+
| no | name | hiredate | sal | quitdate | posno |
+------+----------+------------+--------+----------+-------+
| 1001 | 田中太郎 | 2010-04-01 | 310000 | NULL | 1 |
+------+----------+------------+--------+----------+-------+
1 row in set (0.00 sec)
rollback文を実行すると処理が元に戻る。
試してみよう。
デッドロック
二つのMySQLのコマンドのプロント(A)(B)を開いて、以下の処理を実行する。
両方ともautocommitを無効化する。
table:deadlock
A B
①empテーブルの no= 2002 佐藤一郎さんの
salを260000に更新する
② emp_posテーブルのno = 1をnameをオーナーに更新
③emp_posテーブルのno = 1をnameをリーダーに更新
④empテーブルの no= 2002 佐藤一郎さんの
salを240000に更新する
①の時点で「empテーブルの no= 2002」のデータはロック。
②の時点で「emp_posテーブルのno = 1」のデータはロック。
③の時点で「emp_posテーブルのno = 1」のデータはロックしようとするも、すでに②でロック済み。
処理待ちが発生。
AはBの処理が完了するまで、先に進めない。
④の時点で「empテーブルの no= 2002」のデータはロックしようとするも、すでに①でロック済み。
処理待ちが発生。
BはAの処理が完了するまで、先に進めない。
AはBを待っていて、BはAを待っている状態になるので、処理が完全に止まってしまう。
これが「デッドロック」
最近はMySQLサーバでもデッドロックを検出して処理してくれる。
デッドロックが起きない設計をする。
データの重複をなくし、整合的にデータを取り扱えるようにデータベースを設計すること。
ざっくりいうと「一つのデータは一つのテーブルで管理する」を実現するための考え方。
第一正規化 ~ 第三正規化（~ 第六正規化）までの種類がある。
参考 :
第一正規化
テーブル中に繰り返し項目が存在しない
第二正規化
テーブルのすべての候補キーにおいて部分関数従属性が存在しないこと
第三正規化
テーブル内の非キー属性→非キー属性の関数従属、つまり推移関数従属性が存在しないこと
例 :
回答・説明