# 資料庫期末筆記

## CH6
### SQL 和關聯模式的名詞對應
- 資料表 = 關聯
- 紀錄、列 = 序列值
- 欄位、行 = 屬性

### SQL 提供了三種語言
- 資料定義語言（DDL）
- 資料處理語言（DML）
- 資料控制語言（DCL）

### ON DELETE / UPDATE
- RESTRICT （預設）
一筆紀錄沒有被參考時才可刪除／修改。
- SET NULL
一筆紀錄被刪除時，所有參考它的外鍵都變成空值。
- SET DEFAULT
一筆紀錄被刪除時，所有參考它的外鍵都變成預設值。
- CASCADE
一筆紀錄被刪除時，所有參考都跟著刪除／修改。

### DROP TABLE
用來刪除資料表定義，SQL 容許山除資料表定義時設定如何處理：
- `DROP TABLE Product RESTRICT`
資料表 Product 的定義只有再未被參考的情況下才能被刪除。
- `DROP TABLE Product CASCADE`
資料表 Product 的定義被刪除時，所有參考到這些定義的部分都會一併被刪除。

### ALTER TABLE
修改資料表定義
- 新增：ADD
- 刪除：DROP
- 修改 ALTER

## CH7
### BAG 運算式
- UNION ALL
結合 R 和 S 的所有紀錄（不去除重複）
- EXCEPT ALL
若一記錄在 R 中出現 K1 次，在 S 出現 K2 次，則結果為該記錄出現 K1 - K2 次。（<=0 不出現）
- INTERSECT ALL
若一記錄在 R 中出現 K1 次，在 S 出現 K2 次，則結果為該記錄出現 Min(K1, K2) 次。


### SQL 查詢彙總函數和分群

SELECT 分群屬性, 彙總函數
FROM 資料表
WHERE 記錄選取條件
GROUP BY 分群屬性
HAVING 記錄群選取條件


### 彙總函數
- SUM
- AVG
- COUNT
- MAX / MIN

### JOIN
- INNER JOIN

- OUTER JOIN
- LEFT JOIN

- RIGHT JOIN

- FULL JOIN

- CROSS JOIN

### VIEW
虛擬資料表，沒有實體
- 用途：
- 常見的查詢可以訂義成 VIEW
- 可形成外部綱目，並設定權限

- 語法：

CREATE <VIEW 名稱>
AS <SQL查詢語句>

- 刪除：

DROP VIEW <VIEW 名稱>

### 不能被修改的原因
因為沒有唯一的資料，以下幾個情況無法刪除
- view 包含彙總函數

UPDATE total
SET totalPrice = totalPrice - 100

- 不包含任何關聯鍵（key）

CREATE VIEW Catalog_price
AS (SELECT catalog, unitPrice
    FROM Product
    WHERE unitPrice > 300);

- view 由兩個或以上個資料表所 JOIN 成

### SQL 權限控制
- 產生 TABLE 的權力
GRANT CREATETAB TO Account1;
- 新增和刪除記錄的權力
GRANT INSERT, DELETE ON Product TO Account2;
- 修改記錄屬性的權力
GRANT UPDATE ON Product (unitPrice) TO Account3;
- 查詢的權力
GRANT SELECT ON Product TO Account4;
- 權利轉移的權力
GRANT SELECT ON Product TO Account4 WITH GRANT OPTION;
- 取消權力
REVOKE SELECT ON Product FROM Account4;
- 存取VIEW的權力
GRANT SELECT ON Trans_total TO Account5;

### 語意限制

CREATE ASSERTION <ASSERTION名稱>
CHECK <條件句>;

### SQL 索引
為了加快查詢處理

## CH8
### 好的關聯綱目特性
1. 意義明確
2. 資料不重複儲存
3. 屬性值很少有空值
4. 關聯的 JOIN 不會產生虛假序列值（關聯綱目包含過多屬性造成）

### 無損 JOIN 分解
切割之後的兩個關聯，其共同屬性必須是其中一個關聯的關聯鍵。

### 函數（功能）相依
#### 定義：
兩個（群）屬性間存在一種類似函數裡定義域和對應域的關係
X → Y （X 決定 Y）
X 代表決定因素，Y 為相依因素

給定一個關聯 R，R 的屬性子集 Y 功能相依於 R 的屬性子集 X（= X 決定 Y）, 則：
1. iff 無論何時 R 的兩個 Tuples 若有相同的 X 值時，則必有相同的 Y 值。
2. 對於關聯 R 中的每個 X 值，均有唯一的 Y 值來對應。

#### 特性：
1. X → Y 成立，不代表反之成立。
2. 功能相依是一對一的關係
3. 若 X 為主鍵，關聯中所有其他屬性必功能相依於 X



#### 型式：
* 完全功能相依

* 部分功能相依

* 遞移相依

### 鍵值屬性
- 能構成**主鍵**或候選鍵的**所有屬性**
- 通常鍵值屬性是指主鍵

### 1NF
- 每個屬性都是**簡單**且**單值**
- 會有資料重複性的問題，需要靠 2NF、3NF、BCNF 降低重複性

### 2NF
- 非鍵值屬性皆**完全功能（函數）相依**於主鍵
- 拆解關聯 → 決定外鍵

### 3NF
- 遞移函數相依
- 功能相依 X → Y（Y 遞移相依於 X），存在另外功能相依 X → Z & Z → Y，且 Z 不為超級鍵，則 X → Y 為遞移相依。
（= 非鍵值屬性間，不該存在功能相依性）
- 作法：拆解關聯 → 決定外鍵

### BCNF（Boyce-Codd）
滿足 3NF 卻不滿足 BCNF

滿足 BCNF

一個關聯 R 為 BCNF 表格，iff 該關聯表中的功能相依性之決定因素都是候選鍵（不含有多餘屬性的超鍵）。
即：關聯R中，每個功能相依 Xi→Yi 中，Xi 為 R 之候選鍵。


### 4NF
源於多值相依概念
- 給定一個 X 屬性值，會有一組 Y 的屬性值

不符合 4NF

符合 4NF

## CH12

### 交易
資料庫交易是將數個**資料存取或更新的動作當成一個整體**
如果執行過程中有任何錯誤，可以全部取消；
如果沒有差錯，則交易會永久存在。

### 交易基本運算動作
- begin(T)：交易 T 的開始
- read(X,x)：讀取硬碟資料項 X 到主記憶體變數 x
- write(x,X)：主記憶體變數 x 寫到硬碟資料項 X
- commit(T)：表示交易 T 成功結束
- abort(T)：交易 T 被駁回，做過的動作都還原

DBMS 會同時處理數個交易運算，稱為**並行處理**

### 錯誤的執行結果
任意交錯執行交易運算動作，可能會有錯誤的執行結果：
- 更新遺失
- 汙染讀取
- 無法重複讀取（兩次讀到的資料不一樣）
- 幽靈資料

### 交易失敗
交易處理的基本原則：**All or Nothing**

失敗的原因：
- 應用系統駁回
- 電腦系統駁回
- 儲存媒體損毀

### ACID 性質
- Atomicity 單元性
一個交易中的所有操作只有全部完成或者全部不完成，不會結束在中間某個環節。交易在執行過程中發生錯誤，會被回滾（Rollback）到交易開始前的狀態，就像這個交易從來沒有執行過一樣。

- Consistency 一致性
指的是交易裡進行資料改動。交易若成功，資料改動必須滿足限制，如果不遵守就當作交易失敗，資料改動必須全數 rollback。

- Isolation 獨立性
多筆交易可以同時進行，交易進行時，交易資料在還沒結束交易前不會被其他交易影響、使用。

- Durability 永久性
交易完成後，對資料修改是永久性的，且永久存在資料庫中。不會因為系統重啟或錯誤而改變資料。

### 資料＆追蹤記錄

### 運算動作
交易開始／讀取／寫入：執行運算動作後將相對的追蹤記錄寫入系統追蹤檔緩衝區裡

- 交易 COMMIT：
COMMIT 追蹤記錄寫入系統追蹤檔緩衝區
將緩衝區裡所有的追蹤記錄全部寫入硬碟

- 交易ABORT：
將 ABORT 追蹤記錄寫入系統追蹤檔緩衝區
將其追蹤記錄的資料項寫入記錄反向執行一次，復原其寫入前的值
將緩衝區裡所有的追蹤記錄全部寫入硬碟

### 系統復原
系統當機是無預警的，已執行完畢（COMMIT）的交易資料必須保留，正在執行中的交易被視為 ABORT。
復原動作如下：
1. 將硬碟裡系統追蹤檔裡記錄的運算動作從頭重作（Redo）一次
2. 然後再反向將被視為ABORT的交易動作還原（Undo）

### 交易復原評判交易排程
- 可復原

- 無連鎖駁回

- 嚴格

嚴格的排程一定是無連鎖駁回的排程，而無連鎖駁回的排程也一定是可復原的排程