# データベース

## 概要

- デジタライゼーション：生活の中のあらゆるモノ，コトがデジタルの世界に
- DB：大量のデータを扱える，情報を取り出しやすい，多くの人が同時に使える，安全

### データベースの要件

- **データ独立性** ：データ（数字や文字列）と操作（プログラム）を分離する．

- **論理データ独立** ：データを変更してもプログラムを変更不要（項目が増減，表が増減，構造の変更）
- **物理データ独立** ：データの物理的な保存方法を変更してもプログラム変更不要(CSV,TSV,固定長ファイル，OS，CPU...)
- 外部スキーマ → 論理データ独立 → 概念スキーマ → 物理データ独立 → 内部スキーマ

- **外部スキーマ** ：アプリケーションから見たデータの構造や指定方法などを表す．
- **概念スキーマ** ：データを構成する属性や関係を表す．表の定義や関係の定義．ER図
- **内部スキーマ** ：データベースを物理的な記憶システムに実装したもの

- **データ一貫性** ：格納されたデータが正確ないし妥当であること．DBは保証する．

- **一意性制約** ：重複行が存在しない
- **CHECK制約** ： データ範囲を制限（文字列，整数列，桁数などのフォーマット）
- **参照整合性** ：項目間，テーブル間のデータ関係が正しい（包含関係，論理的矛盾）

- [複数の同時アクセス](#同時実行制御)：ロックで解決．デッドロック問題．

- 機密保護

- [障害復旧](#バックアップ)

- メディア障害：ハードディスクの障害
- システム障害：地震や落雷による強制終了
- トランザクション障害：データが中途半端に更新される

### リレーションデータベース

- リレーション：データの関係（一対一，一対多，多対多）
- 他には

- 階層型，ネットワーク型，オブジェクト，カード型

- 関係代数によってrelationを示す（集合と集合の関係）

- 集合演算（和集合，積集合，差集合，直積）

- 和集合：A ∪ B（AとBの合計）
- 積集合：A ∩ B（AにもBにも両方にあるもの）
- 差集合：A - B（BになくAだけにあるもの）
- 直積：A × B（AとBの全組み合わせ）

- 関係演算（選択，射影，結合）

- 選択：条件に合う行のみを取り出す
- 射影：指定した列のみを取り出す（select カラム名 from テーブル名;）
- 結合：指定した列に基づき２つの表を結合させる．（全部あれば等結合，それ以外は内部結合か外部結合）

- 内部結合：両方の表に存在するもののみ結合（inner join テーブル名 on 条件式;）
- 外部結合：存在しないものは空白(NULL)として結合

- 左外部結合 `left outer join テーブル名 on 条件式;`
- 右外部結合 `right outer join テーブル名 on 条件式;`
- 完全外部結合 `full outer join テーブル名 on 条件式;`

### 関係演算の例

**履修科目**

| 学生 | 講義名 |
| :-: | :---------: |
| 桂 | 哲学 |
| 桂 | 日本国憲法 |
| 桂 | 組織意思決定 |
| 桂 | 社会心理学 |
| 坂田 | 哲学 |
| 坂田 | 日本国憲法 |
| 坂本 | 日本国憲法 |
| 坂本 | ビジネスイノベーション |
| 坂本 | ベンチャービジネス論 |

**開講情報**

| 講義名 | 曜日 |
| :---------: | :-: |
| 哲学 | 月曜 |
| 日本国憲法 | 火曜 |
| 社会心理学 | 水曜 |
| ビジネスイノベーション | 水曜 |
| 組織意思決定 | 木曜 |
| ベンチャービジネス論 | 金曜 |

- 上記二つのテーブルを結合する
- where句で書いてもjoin on 句で書いても等価

- `select * from 履修科目 join 開講情報 on 履修科目.講義名 = 講義情報.講義名;`
- `select * from 履修科目, 開講情報 where 履修科目.講義名 = 講義情報.講義名;`

- 三つ以上の場合は where句の条件式を and で結ぶ

- `where 条件式 and 条件式 and 条件式`

| 学生 | 講義名 | 曜日 |
| :-: | :---------: | :-: |
| 桂 | 哲学 | 月曜 |
| 桂 | 日本国憲法 | 火曜 |
| 桂 | 組織意思決定 | 木曜 |
| 桂 | 社会心理学 | 水曜 |
| 坂田 | 哲学 | 月曜 |
| 坂田 | 日本国憲法 | 火曜 |
| 坂本 | 日本国憲法 | 火曜 |
| 坂本 | ビジネスイノベーション | 水曜 |
| 坂本 | ベンチャービジネス論 | 金曜 |

- 結合後のテーブルから **射影** する

- `select 講義名 from 受講状況;`

| 講義名|
| :---------:|
| 哲学|
| 日本国憲法|
| 組織意思決定|
| 社会心理学|
| 哲学|
| 日本国憲法|
| 日本国憲法|
| ビジネスイノベーション|
| ベンチャービジネス論|

- 先ほどの結合後のテーブルから **選択** する

- `select * from 受講状況 where 講義名 = "ビジネスイノベーション";`

| 学生 | 講義名 | 曜日 |
| :-: | :---------: | :-: |
| 坂本 | ビジネスイノベーション | 水曜 |

#### 内部結合と外部結合

- 結合時，もし欠けたデータがあったら？<br>

**履修科目**

| 学生 | 講義名 |
| :-: | :---------: |
| 桂 | 哲学 |
| 桂 | 日本国憲法 |
| 桂 | 組織意思決定 |
| 桂 | 社会心理学 |
| 坂田 | 哲学 |
| 坂田 | 日本国憲法 |
| 坂本 | 日本国憲法 |
| 坂本 | ビジネスイノベーション |
| 坂本 | ベンチャービジネス論 |

**開講情報**

| 講義名 | 曜日 |
| :---------: | :----: |
| 哲学 | _NULL_ |
| 日本国憲法 | 火曜 |
| 社会心理学 | 水曜 |
| ビジネスイノベーション | 水曜 |
| 組織意思決定 | 木曜 |
| ベンチャービジネス論 | 金曜 |

- **内部結合**

- 欠けたデータが含まれる行は消去する

| 学生 | 講義名 | 曜日 |
| :-: | :---------: | :-: |
| 桂 | 日本国憲法 | 火曜 |
| 桂 | 組織意思決定 | 木曜 |
| 桂 | 社会心理学 | 水曜 |
| 坂田 | 日本国憲法 | 火曜 |
| 坂本 | 日本国憲法 | 火曜 |
| 坂本 | ビジネスイノベーション | 水曜 |
| 坂本 | ベンチャービジネス論 | 金曜 |

- **外部結合**

- 欠けたデータは空白(NULL)にしておく

**受講状況**

| 学生 | 講義名 | 曜日 |
| :-: | :---------: | :----: |
| 桂 | 哲学 | _NULL_ |
| 桂 | 日本国憲法 | 火曜 |
| 桂 | 組織意思決定 | 木曜 |
| 桂 | 社会心理学 | 水曜 |
| 坂田 | 哲学 | _NULL_ |
| 坂田 | 日本国憲法 | 火曜 |
| 坂本 | 日本国憲法 | 火曜 |
| 坂本 | ビジネスイノベーション | 水曜 |
| 坂本 | ベンチャービジネス論 | 金曜 |

## SQL

### データベースで使われる言語まとめ

#### データ定義言語：データ管理

- **select**：データを読み込む

- **select** \* from テーブル名;

- **insert**：新規行を追加する

- **insert** into テーブル名 values(“カラム1の値” , “カラム2の値” …);

- **update**：行のデータを変更する

- **update** テーブル名 set カラム名 = カラムの値 where 更新対象の条件式;

- **delete**：行を削除する

- **delete** from テーブル名 where 更新データの条件式;

#### データ操作言語：テーブル管理

- **create**：テーブル等を作成する

- **create** table テーブル名 カラム名 型;

- **alter**：テーブル等を変更する

- **alter** table テーブル名 rename to 新テーブル名;

- **drop**：テーブル等を削除する

- **drop** table テーブル名;

#### データ制御言語

- **grant**：表へのアクセス権限等を与える
- **revoke**：権限を剥奪する

## SQL文の例題

**注意！**<br>

- SQL文では大文字と小文字は区別されない

- SELECT とselect は同じ意味

- 値やパターンを入力する際は "ダブルクオテーション" でくくる
- 行末は ; をつける

### WHERE

- データを絞り込む条件式を形成する

- ある条件にマッチする行のみを対象とする
- like句を用いることで曖昧検索も可能

- **where** カラム名 = "値"
- **where** カラム名 > "値"
- **where** カラム名 &lt; "値"
- **where** カラム名 like "パターン"

### LIKE

- 曖昧検索に用いる

- **%**：任意の0文字以上の文字列
- **\_**：任意の1文字

- where カラム名 **like** パターン

### CREATE

- テーブル等を作成する

- テーブル名とカラム名，カラムの型を指定できる
- カラムは列(縦)のこと

- **create** table テーブル名;
- **create** table テーブル名 (カラム名1 , カラム名2 , カラム名3);
- **create** table テーブル名 (カラム名1 型 , カラム名2 型);

#### 簡単な例

- 名前カラムを持つ登場人物テーブルを作成する

- `create table 登場人物 (名前);`

- 名前カラムと誕生日カラムを持つキャラテーブルを作成する

- `create table キャラ (名前, 誕生日);`

### DROP

- テーブル等を削除する

- テーブルの削除の場合

- **drop** table テーブル名;

#### 簡単な例

- キャラクターテーブルを削除する

- `drop table キャラ;`

### ALTER

- テーブルを変更する

- テーブルの名前を変更する

- **alter** table rename to 新しい名前;

- テーブルにカラムを追加する

- **alter** table add 新しいカラム;

#### 簡単な例

- 登場人物テーブルをキャラテーブルにリネームする

- `alter table 登場人物 rename to キャラ;`

- キャラテーブルに誕生日カラムを追加する

- `alter table 登場人物 add 誕生日;`

### INSERT

- テーブルに行を追加する

- **insert** into テーブル名 values("カラム1の値", "カラム2の値", ...... );

#### 簡単な例

- キャラテーブルに 名前が金太郎で誕生日が10月10日である行を追加する

- `insert into キャラ values("金太郎","10月10日");`

### DELETE

- 行を削除する

- **drop** from テーブル名 where 条件式;

#### 簡単な例

- キャラテーブルから名前が金太郎の行を削除する

- `delete from "キャラ" where name = "金太郎";`

### SELECT

- 指定したデータを抽出する

- - を指定すると，全てのデータという意味になる．

- **select** \* from テーブル名;
- **select** \* from テーブル名 where 条件式;
- **select** \* from テーブル名 where カラム名 like パターン;

#### 簡単な例

- キャラテーブルから名前が坂田銀時であるユーザの名前と誕生日を抽出する

- `select 名前 , 誕生日 from キャラ where 名前 = "坂田銀時";`

- 坂田銀時 , 10月10日

- キャラから名前に田を含むユーザの名前と誕生日を抽出する

- `select 名前 , 誕生日 from キャラ where 名前 like "%田%";`

- 坂田銀時 , 10月10日
- 沖田総悟 , 7月8日
- 寺田綾乃 , 7月7日

### UPDATE

- 行のデータを変更する

- 条件句(WHERE句)で対象行を絞り込む
- 対象行が複数ある場合は，複数の行が全て置き換わる

- **update** テーブル名 set カラム名 = カラムの値 where 更新対象の条件式;

#### 簡単な例

- キャラテーブルの坂田銀時の名前を坂田金時にする

- `update "キャラ" set "名前" = "坂田金時" where "名前" = "坂田銀時";`

- キャラテーブルの名前に田がつく人の名前を全てウイルス・ミスにする

- `update "キャラ" set "名前" = "ウイルス・ミス" where "名前" like "%田%";`

### キー

- テーブルの行を特定できる項目，項目の組み合わせ
- 複数の項目の組み合わせがキーとなる場合は **複合キー** ともいう

| 名前 | 誕生日 | 所属|
|:-----:|:-----:|:----:|
|坂田銀時 | 10月10日 | 万事屋|
|志村新八 | 8月12日 | 万事屋|
| 神楽 | 11月3日 | 万事屋|
| 近藤勲 | 9月4日 | 真選組|
|土方十四郎 | 5月5日 | 真選組|
|沖田総悟 | 7月8日 | 真選組|

- この場合は _名前_ がキー

- 名前が決まれば誕生日と所属が決まる
- 誕生日が被ることはないと約束されているなら誕生日もキーになり得る

#### 主キー

- テーブル作成時に主として使うと宣言したキー
- 項目の組み合わせなら **複合主キー** ともいう
- 主キー以外のキーを候補キーと呼ぶ

| 名前 | 誕生日 | 所属 |
| :---: | :----: | :-: |
| 坂田銀時 | 10月10日 | 万事屋 |
| 志村新八 | 8月12日 | 万事屋 |
| 神楽 | 11月3日 | 万事屋 |
| 近藤勲 | 9月4日 | 真選組 |
| 土方十四郎 | 5月5日 | 真選組 |
| 沖田総悟 | 7月8日 | 真選組 |

- 名前が主キー
- 誕生日は候補キー（誕生日は被らない場合のみ）
- 誕生日は現状ではキーたり得るが，行が追加されると被ってしまうかもしれない

- 誕生日はキーたり得ない
- ID や 通し番号を付与することで必ず一意な主キーを作れる

| ID | 名前 | 誕生日 | 所属 |
| :-: | :---: | :----: | :-: |
| 01 | 坂田銀時 | 10月10日 | 万事屋 |
| 02 | 志村新八 | 8月12日 | 万事屋 |
| 03 | 神楽 | 11月3日 | 万事屋 |
| 04 | 近藤勲 | 9月4日 | 真選組 |
| 05 | 土方十四郎 | 5月5日 | 真選組 |
| 06 | 沖田総悟 | 7月8日 | 真選組 |

#### ユニークキー

- 空白値(NULL)以外を特定できるキー
- 列の値にNULLがなく全て一意に特定できるなら主キー

| ID | 名前 | 誕生日 | 所属 |
| :----: | :----: | :----: | :----: |
| 01 | 坂田銀時 | 10月10日 | 万事屋 |
| 02 | 志村新八 | 8月12日 | 万事屋 |
| 03 | 神楽 | 11月3日 | 万事屋 |
| 04 | 近藤勲 | 9月4日 | 真選組 |
| 05 | 土方十四郎 | 5月5日 | 真選組 |
| 06 | 沖田総悟 | 7月8日 | 真選組 |
| _NULL_ | 長谷川 泰三 | 6月13日 | _NULL_ |

- 名前は主キー （名前がわかればその行を特定できる）
- IDはユニークキー (ID が _NULL_ の長谷川泰三 以外は一意に特定できる)

#### 外部キー

- 表と表を結ぶ項目
- 両方の表に含まれている項目
- 右テーブル（後者のテーブル）の主キー

**履修科目**

| ID | 学生 | 講義名 |
| :-: | :-: | :---------: |
| 1 | 桂 | 哲学 |
| 2 | 桂 | 日本国憲法 |
| 3 | 桂 | 組織意思決定 |
| 4 | 桂 | 社会心理学 |
| 5 | 坂田 | 哲学 |
| 6 | 坂田 | 日本国憲法 |
| 7 | 坂本 | 日本国憲法 |
| 8 | 坂本 | ビジネスイノベーション |
| 9 | 坂本 | ベンチャービジネス論 |

**開講情報**

| 講義名 | 曜日 |
| :---------: | :-: |
| 哲学 | 月曜 |
| 日本国憲法 | 火曜 |
| 社会心理学 | 水曜 |
| ビジネスイノベーション | 水曜 |
| 組織意思決定 | 木曜 |
| ベンチャービジネス論 | 金曜 |

- 履修項目では _データID_ が主キー， _講義名_ は外部キー
- 開講情報では _講義名_ が主キー

## インデックス

### インデックスとは

- 検索を高速にするために用いる
- なんらかのルールでソートされていると検索しやすい
- 全ての行をソートするのは難しい（データがあとで増えた時は？）
- 生データと別にインデックス作成し，各行をポインタで示す
- インデックスから検索する際，[リニアサーチ](https://ja.wikipedia.org/wiki/%E7%B7%9A%E5%9E%8B%E6%8E%A2%E7%B4%A2)と [バイナリサーチ](https://www.codereading.com/algo_and_ds/algo/binary_search.html) が考えられる

- リニアサーチ ： スキャン
- バイナリサーチ ： シーク

- インデックスなしスキャン ： テーブルスキャン
- インデックスありスキャン ： インデックススキャン
- インデックスなしシーク ： 不可能
- インデックスありシーク ： インデックスシーク

### インデックスの種類

- [バランス木](https://ja.wikipedia.org/wiki/B%E6%9C%A8)

- [バイナリサーチ](https://www.codereading.com/algo_and_ds/algo/binary_search.html)で探索
- 二分木の実装例の一つ
- 挿入，削除に強い 

- [ツリーインデックス](http://qiita.com/suzukito/items/908a3523f4b90dd77c6a)

- より効率的な探索を実現する
- 大規模なデータベースで利用される 

- クラスタインデックス
- 非クラスタインデックス

### 効率的なIndex作成

- データ件数が少ないなら作成しない
- 同じ値が多いなら作成しない（テータ分布を考慮）
- 検索に使用しない行には作成しない
- インデックス列の順番を考慮する（複合キー）
- 主キー・外部キーにはインデックスを作成
- １テーブルに１つのインデックスが良い

## ER図

- データベースの表と表の関係を示す図

- 表とそれらの関係を三つの構成要素でモデル化
- 実体（Entity）：表
- 関連（Relation）：表と表のつながり
- 属性（attribute）：表の項目

- 記法

- 教科書の書き⽅
- IE記法 (Information Engineering記法）
- IDEF1X記法 (Integration Definition記法 )
- Peter Chen記法

- 様々な記法があるが基本は同じ
- どれも三つの構成要素からなる

- 実体（Entity）：表
- 関連（Relation）：表と表のつながり
- 属性（attribute）：表の項目

- 教科書の書き方
- 主キーには下線 
- 教科書の書き方の例 

### 対応関係

- 1 対 1
- テーブル内の関係
- 主キーと他の項目の関係
- 1 対 N
- テーブル間の関係（関連）
- N 対 N
- 複合主キーの各項目の関係

## 正規化

- 使いやすいデータ，更新異常の発生しないデータを作る

- **非正規形**
- **第一正規形** ：値がスカラー（集合やベクトルでない）
- **第二正規形** ：非キー属性が全ての候補キーに完全従属
- **第三正規形** ：非キー属性が候補キーに非推移的関数従属
- **ボイスコット正規形** ：非キー属性が主キーに完全従属している
- **第四正規形** ：自明でない多値従属性を排除する
- **第五正規形** ：結合従属性の決定項が候補キーのみ

- 実質は第三正規形まで
- 授業で扱うのも第三正規形まで
- ボイスコット正規形が存在することのみ把握する必要あり

### 第一正規形

- 一事実一箇所
- 同じ意味合いの項目が繰り替えさない
- 更新異常を防止する
- 値はスカラー
- 表のセル（一区画）に複数の値が入らない
- 主キーがある
- 他と区別できる値がある
- 計算で求められる値は持たない
- 各値を明記したならば合計値をカラムとして所持してはいけない
- 合計カラムを持つと，各値を更新した際に合計が自動で更新されない事で矛盾が発生する

**非正規形**

| 名前 | アイテム名 | 価格 |
| :---: | :------: | :--------: |
| 坂田銀時 | いちごみるく | 120 |
| 坂田銀時 | 週刊少年ジャンプ | 270 |
| 坂田銀時 | 週刊少年ジャンプ | 270 |
| 坂田銀時 | 木刀，財布 | 4000, 1500 |
| 土方十四郎 | マヨネーズ | 270 |
| 土方十四郎 | タバコ | 600 |
| 土方十四郎 | タバコ | 600 |
| 土方十四郎 | タバコ | 580 |
| 土方十四郎 | 刀 | 200000 |
| 土方十四郎 | 週刊少年ジャンプ | 270 |
| 土方十四郎 | 財布 | 20000 |

- 一区画内に複数の値が存在（木刀，宇治銀時丼）
- 二行に分ける
- 同じ内容の項目が存在するので一事実一箇所の鉄則を破っている
- 区別する必要があるならIDを付与
- 先週のジャンプと今週のジャンプは違う等の理由
- 区別する必要がないのなら数量を記載するカラムを増やして行をマージ

**第一正規形** ①

| 名前 | アイテム名 | アイテムID | 数量 | 単価 |
| :---: | :------: | :----: | :-: | :----: |
| 坂田銀時 | いちごみるく | 01 | 01 | 120 |
| 坂田銀時 | 週刊少年ジャンプ | 01 | 01 | 270 |
| 坂田銀時 | 週刊少年ジャンプ | 02 | 01 | 270 |
| 坂田銀時 | 木刀 | 01 | 01 | 4000 |
| 坂田銀時 | 財布 | 01 | 01 | 15000 |
| 土方十四郎 | マヨネーズ | 01 | 01 | 270 |
| 土方十四郎 | タバコ | 01 | 02 | 600 |
| 土方十四郎 | タバコ | 02 | 01 | 580 |
| 土方十四郎 | 刀 | 01 | 01 | 200000 |
| 土方十四郎 | 週刊少年ジャンプ | 01 | 01 | 270 |
| 土方十四郎 | 財布 | 01 | 01 | 20000 |

- 計算で求められる値なので，数量×単価で求められる合計額カラムは作らない
- 名前，アイテム名，アイテムIDがわかって初めて値段が一意に決まる
- 名前，アイテム名，アイテムIDが複合主キー
- IDを付与することで簡単に主キーを用意することも可能

**第一正規形** ②

| ID | 名前 | アイテム名 | アイテムID | 数量 | 単価 |
| :-: | :---: | :------: | :----: | :-: | :----: |
| 001 | 坂田銀時 | いちごみるく | 01 | 01 | 120 |
| 002 | 坂田銀時 | 週刊少年ジャンプ | 01 | 01 | 270 |
| 003 | 坂田銀時 | 週刊少年ジャンプ | 02 | 01 | 270 |
| 004 | 坂田銀時 | 木刀 | 01 | 01 | 4000 |
| 005 | 坂田銀時 | 財布 | 01 | 01 | 15000 |
| 006 | 土方十四郎 | マヨネーズ | 01 | 01 | 270 |
| 007 | 土方十四郎 | タバコ | 01 | 02 | 600 |
| 007 | 土方十四郎 | タバコ | 02 | 01 | 580 |
| 008 | 土方十四郎 | 刀 | 01 | 01 | 200000 |
| 009 | 土方十四郎 | 週刊少年ジャンプ | 01 | 01 | 270 |
| 010 | 土方十四郎 | 財布 | 01 | 01 | 20000 |

### 第二正規形

- 非キー属性が全ての候補キーに完全従属
- 主キーとそれ以外に分ける
- 主キーが決まれば一意に主キー以外の値が決まる
- 複合キーでなければ当たり前
- 複合キーの場合
- **複合キーの一部で値が決まる項目がない**

**第一正規形**

| 名前 | アイテム名 | アイテムID | 価格 | 販売場所 |
| :---: | :------: | :----: | :----: | :--: |
| 坂田銀時 | いちごみるく | 01 | 120 | コンビニ |
| 坂田銀時 | 週刊少年ジャンプ | 01 | 270 | コンビニ |
| 坂田銀時 | 週刊少年ジャンプ | 02 | 270 | コンビニ |
| 坂田銀時 | 木刀 | 01 | 4000 | 通販 |
| 坂田銀時 | 財布 | 01 | 15000 | 百貨店 |
| 土方十四郎 | マヨネーズ | 01 | 300 | コンビニ |
| 土方十四郎 | タバコ | 01 | 600 | タバコ屋 |
| 土方十四郎 | タバコ | 02 | 580 | タバコ屋 |
| 土方十四郎 | 刀 | 01 | 200000 | 刀匠 |
| 土方十四郎 | 週刊少年ジャンプ | 01 | 270 | コンビニ |
| 土方十四郎 | 財布 | 01 | 20000 | 百貨店 |

- 名前，アイテム名，アイテムIDが複合主キー
- アイテム名だけで販売店がわかる
- アイテム名と販売場所は別表にする

**第二正規形** (所有アイテム)

| 名前 | アイテム名 | アイテムID | 価格 |
| :---: | :------: | :----: | :----: |
| 坂田銀時 | いちごみるく | 01 | 120 |
| 坂田銀時 | 週刊少年ジャンプ | 01 | 270 |
| 坂田銀時 | 週刊少年ジャンプ | 02 | 270 |
| 坂田銀時 | 木刀 | 01 | 4000 |
| 坂田銀時 | 財布 | 01 | 15000 |
| 土方十四郎 | マヨネーズ | 01 | 300 |
| 土方十四郎 | タバコ | 01 | 600 |
| 土方十四郎 | タバコ | 02 | 580 |
| 土方十四郎 | 刀 | 01 | 200000 |
| 土方十四郎 | 週刊少年ジャンプ | 01 | 270 |
| 土方十四郎 | 財布 | 01 | 20000 |

**第二正規形** (販売場所)

| いちごみるく | コンビニ |
| :------: | :--: |
| 週刊少年ジャンプ | コンビニ |
| 木刀 | 通販 |
| 財布 | 百貨店 |
| マヨネーズ | コンビニ |
| タバコ | タバコ屋 |
| 刀 | 刀匠 |

### 第三正規形

- 非キー属性が候補キーに非推移的関数従属
- 主キー以外で決まる項目があったらダメ

**第二正規形**

| ID | 名前 | 誕生日 | 所属 | 代表 |
| :-: | :---: | :----: | :-: | :--: |
| 01 | 坂田銀時 | 10月10日 | 万事屋 | 坂田銀時 |
| 02 | 志村新八 | 8月12日 | 万事屋 | 坂田銀時 |
| 03 | 神楽 | 11月3日 | 万事屋 | 坂田銀時 |
| 04 | 近藤勲 | 9月4日 | 真選組 | 近藤勲 |
| 05 | 土方十四郎 | 5月5日 | 真選組 | 近藤勲 |
| 06 | 沖田総悟 | 7月8日 | 真選組 | 近藤勲 |

- IDが主キー
- 名前が決まると誕生日と所属が決まる
- テーブルを分ける
- 所属が決まると代表が決まる
- テーブルを分ける

**第三正規形** (名前)

| ID | 名前 |
| :-: | :---: |
| 01 | 坂田銀時 |
| 02 | 志村新八 |
| 03 | 神楽 |
| 04 | 近藤勲 |
| 05 | 土方十四郎 |
| 06 | 沖田総悟 |

**第三正規形** (パーソナルデータ)

| 名前 | 誕生日 | 所属 |
| :---: | :----: | :-: |
| 坂田銀時 | 10月10日 | 万事屋 |
| 志村新八 | 8月12日 | 万事屋 |
| 神楽 | 11月3日 | 万事屋 |
| 近藤勲 | 9月4日 | 真選組 |
| 土方十四郎 | 5月5日 | 真選組 |
| 沖田総悟 | 7月8日 | 真選組 |

**第三正規形** (組織情報)

| 所属 | 代表 |
| :-: | :--: |
| 万事屋 | 坂田銀時 |
| 真選組 | 近藤勲 |

### 正規化のその他

- 利便性のためあえて正規化を崩す場合がある
- 多くのテーブルを用いる検索を頻繁に行う
- 解析のみに用いる場合
- 行数が多すぎる場合
- 1.8億行　→　90万行×200列

| 名前 | 科目 | 得点 |
| :-: | :-: | :-: |
| 桂 | 習字 | 80 |
| 桂 | 算盤 | 70 |
| 桂 | 地理 | 80 |
| 坂田 | 習字 | 60 |
| 坂田 | 算盤 | 50 |
| 坂田 | 地理 | 70 |
| 坂本 | 習字 | 40 |
| 坂本 | 算盤 | 90 |
| 坂本 | 地理 | 80 |
| 高杉 | 習字 | 80 |
| 高杉 | 算盤 | 80 |
| 高杉 | 地理 | 80 |

| 名前 | 習字 | 算盤 | 地理 |
| :-: | --- | --- | --- |
| 桂 | 80 | 70 | 80 |
| 坂田 | 60 | 50 | 70 |
| 坂本 | 50 | 90 | 80 |
| 高杉 | 80 | 80 | 80 |

## トランザクション

- データベースの中身を更新する処理の事

**所持金**

| 名前 | 所持金 |
| :---: | :--: |
| 坂田銀時 | 2000 |
| 土方十四郎 | 4000 |

**所持品**

| 名前 | 所持品 |
| :---: | :------: |
| 坂田銀時 | イチゴ牛乳 |
| 坂田銀時 | 週刊少年ジャンプ |
| 土方十四郎 | マヨネーズ |
| 土方十四郎 | タバコ |
| 土方十四郎 | 土方スペシャル |

- ここで坂田銀時が600円の宇治銀時丼を購入したと考える
- 二つのテーブルの変更は同時に行われなくてはならない
- どちらか片方だけ変更することがあってはならない（更新異常）
- 同時に実行しなくてはならないこの一連の処理：トランザクション

### ACID

- トランザクションが備えるべき要件
1. 原子性 Atomicity
2. 一貫性 Consistency
3. 分離性 Isolation
4. 持続性 Durability

#### 原子性

- トランザクションは次の二状態のうちどちらかでなくてはならない
- 全て処理（更新）される ： **コミット**
- 全て処理（更新）されない ： **ロールバック**
- 途中で終わるのはダメ

#### 一貫性

- 項目の制約を破綻させてはならない
- 例）所持金がマイナスになる
- 例）IDの重複
- 事前に設定してある条件がトランザクション後も守られていなければならない

#### 分離性

- トランザクションが他のトランザクションの影響を受けない
- データを書き換えている間はあのトランザクションがデータを読めないようロック
- 例）坂田銀時が270円の買い物をする　→　坂田銀時が600円の買い物をする
- 分離性が担保できてなければ
- 所持金の2000円から270円引く
- 所持金の2000円から600円引く
- 最終残高は1400円か1730円
- 分離性が担保できていれば
- 所持金の2000円から270円引く　→　残高の1730円から600円引く
- 最終残高は1130円

#### 持続性

- トランザクション終了後は システム障害があっても結果は変わらない
- トランザクション途中に予期せず中断した場合トランザクション開始前に戻る
- システム障害
- 電源停止
- ネットワーク障害
- 人為的ミス
- 実現には
- 更新記録（ログ）を常に保存しておく
- 再起動時にバックアップデータのログを用いて，コミットされたトランザクションを順に再反映させていく（ロールバック）

#### コミットとロールバック

- トランザクションが更新異常に陥らないように
- 原子性：トランザクションの毎に
- 持続性：障害発生時
- トランザクションがコミットされた状態とは？
- 同時に行わなくてはならない一連の処理を済ませ，必要な情報を全て更新される
- トランザクションがロールバックされた状態とは？
- 更新異常時に前回コミットされた状態まで復元する

### 同時実行制御

- データベースを利用するのは一人ではない
- 風数のユーザが同時にテーブルを更新することがある
- 同時実行
- できなきゃ困る
- 同時実行を制御する方法
- 同時実行制御

#### 同時実行制御の種類

##### ペシミスティック　（悲観的）

- きっとダメだ
- マーフィーの法則
- 失敗する可能性のあるものは失敗する
- きっと他の人も同時に更新するだろう
- 他の人が触れないようにロックしてしまう
1. ロック
2. 読み取り
3. 更新
4. ロック解除
- 他のトランザクションが処理されていてデータベースにアクセスできないなら，あとでやり直す
- 読み取りの段階で判断
- 単純，ロック時間が長い
- 同時更新が起こりやすい時に利用

##### オプティミスティック　（楽観的）

- 多分大丈夫
- 多分他の人は更新しないだろう
1. 読み取り・更新データ用意
2. ロック
3. 他人の更新がないかチェック・更新
4. ロック解除
- 他のトランザクションが処理されていてデータベースにアクセスできないなら，あとでやり直す
- 読み取り，更新データの用意ができてから判断
- ロックも同じタイミング
1. *処理１* がデータを読み込み，更新データを用意
1. *処理2* が遅れてデータを読み込み，更新データを用意
2. *処理2* の方が短時間で更新データを用意できたので処理２が先にロック
3. *処理１* が更新しようとするがロックされているので待機
4. *処理２* がチェック，更新
5. *処理２* がロック解除
6. *処理１* がロック
7. *処理１* がチェック，更新
8. *処理１* がロック解除
- このような割り込みが発生する
- 複雑，ロック時間が短い
- 同時更新が起こると処理が複雑なので遅くなる

##### ロックの単位

- 表単位
- ページ単位
- 行単位

##### ロックの種類

###### **共有ロック**

- 読み込み時にロック
- 書き込みを禁止する
- 読み込みは自由（他のユーザも）

###### **排他ロック**

- トランザクション実行中にロック
- トランザクション実行時に自動的に行われる

###### **更新ロック**

- これから更新することを宣言
- これを実行すると排他ロックになる

#### [デッドロック](https://ja.wikipedia.org/wiki/%E3%83%87%E3%83%83%E3%83%89%E3%83%AD%E3%83%83%E3%82%AF)

- 複数のトランザクションがロックされた情報にアクセスするために互いの処理終了を待ち，そこから処理が進まなくなる
- 簡単な例）AとBが互いにお金を振り込む

0. AがBに5万円振り込む
1. Aの口座残高をロック
2. Bの口座をロックしようとする
3. Bによる「Bの口座のロック」が解除されなければBの口座をロックできない
4. 処理を完了できないのでAの口座をロックしたまま
1. BがAに10万円振り込む
1. Bの口座残高をロック
2. Aの口座をロックしようとする
3. Aによる「Aの口座のロック」が解除されなければAの口座をロックできない
4. 処理を完了できないのでBの口座をロックしたまま

- 互いに互いの処理完了を待つ状態になる

##### 回避方法

- 100%回避できる方法は存在しない
- 一定時間更新できないと自動的にロールバック，再実行

## セキュリティ

- データを誰もが参照，変更できたら？
- チートし放題
- 適切な権限管理が必要

### 権限管理

- データベースの利用者毎に異なる管理が必要
- 参照可能な表
- 更新可能な表
- 色々な人がいる．適切な権限を付与する必要がある．
- ユーザ
- 開発企業
- ゲーム管理
- ステージ管理
- イベント管理
- 課金管理
- 課金者情報
- クレジット情報

#### ログイン管理

- 個人による違い
- 誰が使っているかを明らかにする
- 匿名の場合も内部的には識別が必要かも
- 誰が使っているかわかれば各人毎に権限を設定

#### ロール

- 役割による違い
- 個々人に権限を付与するのではなく，個々人の役割毎に権限を付与する
- ユーザ，ゲームマスター，デバッカー，開発者，
- １人１役とは限らない
- ステージ管理者とイベント管理者を兼ねる人がいるかも

#### 権限の種類

##### ステートメント権限

- 実行できるコマンドを制限
- データベースの作成/削除
- 表（テーブル）の作成/削除
- 権限の追加/削除
- 一般ユーザに他のユーザに障害が出るような作業はさせない
- データベースや表の追加や削除など

##### オブジェクト権限

- アクセスできる表を制限
- テーブルとビューの読み取り，更新，行の追加
- プロシージャ（一連の作業）実行
- 読み取り，更新，行の組み合わせ
- 特定のユーザにしかデータにアクセスさせない
- 一般ユーザはカードデーブルにはアクセスできない

###### データ制御言語

- **grant**：表へのアクセス権限等を与える
- **revoke**：権限を剥奪する

### バックアップ

- データは紛失しうるもの
- ハードウェア障害
- ソフトウェアのバグ
- 人為ミス
- 意図的な攻撃
- 紛失した際に戻せる様にする
- バックアップ

#### ファイルバックアップとの違い

- データのサイズが非常に大きい
- 常に稼働中であることが多い
- ゲームなどならメンテ時にシステムをとめてもいい
- 銀行や警察システムではそうはいかない
- 履歴を取りたいことが多い
- 間違った操作を修復できる
- 犯罪捜査や過去の動向調査などで過去のデータが必要になる

#### バックアップ場所

- データベースと別のストレージにバックアップする
- 同じストレージだと物理的に壊れた場合対処不要
- 地理的に離れた場所にバックアップ
- 家事で建物が壊れる
- 地震で地域のインフラ破壊
- 磁気テープを保管場所までトラックで運ぶ
- 機密性の問題
- 大規模になればネットワーク経由は遅い
- 物理的に移動させる方が高速

#### バックアップの種類

##### フルバックアップ：完全バックアップ

- 全データとトランザクションログのバックアップ
- 現状のデータベースを丸ごと複製
-

##### ログバックアップ

- 前回のフルバックアップ以降の _トランザクションログ_ のバックアップ
- 前回のバックアップからの変更内容を保存していく
- 復元時までの全てのロクを順にたどる必要があるのでリストアに時間がかかる

##### 差分バックアップ

- 前回のフルバックアップ以降の _データ部分_ の差分バックアップ
- 前回の _完全バックアップ_ から変更があった部分だけ保存する
- 前回の完全バックアップまで一気に戻ることができるのでログバックアップよりは高速にリストアできる
- バックアップの容量が大きくなり，バックアップ取得にログバックアップよりは時間がかかる

#### バックアップの指針

- フルバックアップが基本となる
- 定期的にフルバックアップを行う
- 間を補完する形でログバックアップ，差分バックアップを行う
- データベースが停止中
- 静的なバックアップ， オフラインバックアップ
- 簡単
- データベースが稼働中
- 動的なバックアップ，オンラインバックアップ
- 難しい
- トランザクションの途中ではバックアップできない
- DBMSによって様々
- DB2(IBM)はDBMSがロック等面倒な作業を自動化

#### 障害からの復旧：リストア

- 毎回フルバックアップを行なっていた場合
- 最後のフルバックアップをそのまま用いる
- フルバックアップとログバックアップを行なっていた場合
- 最後のフルバックアップまで復元
- フルバックアップ後のログ（作業履歴）を順に実行していく
- ログが増えるとログを辿るのが大変
- 差分バックアップの発想が生まれる
- フルバックアップと差分バックアップとログバクアップを行なっていた場合
- 最後のフルバックアップまで復元
- 最後の差分バックアップを復元
- 差分バックアップ後のログ（作業履歴）を順に実行していく

### 分散データベース

- 一つのシステムが複数のデータベースを利用すること
- 用途によって使い分ける
- 複数のDBMSに同じデータを置く
- **レプリケーション**
- 定期的に相互コピーする
- アーティクルという単位
- バックアップ
- 地理的に近いサーバにアクセスすることで処理の高速化を図る
- **分散トランザクション**
- トランザクションを各データベースに発行
- 全データベースでコミットされる
- 時間差なしでデータベースが複製される
- 同じDBMSで複数のサーバに違うデータを置く
- 分散パーティション
- データは一箇所
- 日本のデータはDBMS\_日本
- 米国のデータはDBMS_LA,DBMS_NY