---
# System prepended metadata

title: 資料庫期末筆記

---

# 資料庫期末筆記

## 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) 次。

![](https://i.imgur.com/QPBz7rW.png)

### SQL 查詢彙總函數和分群
```sql=
SELECT 分群屬性, 彙總函數
FROM 資料表
WHERE 記錄選取條件
GROUP BY 分群屬性
HAVING 記錄群選取條件
```
![](https://i.imgur.com/fQ9YLTg.png)

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

### JOIN
- INNER JOIN
![](https://i.imgur.com/Lo0P6uL.png)

- OUTER JOIN
    - LEFT JOIN 
    ![](https://i.imgur.com/AynQCB1.png)
    
    - RIGHT JOIN
    ![](https://i.imgur.com/S65Ptfl.png)
    
    - FULL JOIN
    ![](https://i.imgur.com/QC3AMcS.png)
    
    - CROSS JOIN
    ![](https://i.imgur.com/f7wTOTy.png)

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

- 語法：
```sql=
CREATE <VIEW 名稱>
AS <SQL查詢語句>
```

- 刪除：
```sql=
DROP VIEW <VIEW 名稱>
```

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

- 不包含任何關聯鍵（key）
```sql=
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;

### 語意限制
```sql=
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

![](https://i.imgur.com/T1oVhkr.png)

![](https://i.imgur.com/fpcnK3S.png)

#### 型式：
* 完全功能相依
![](https://i.imgur.com/BOOWjLX.png)

* 部分功能相依
![](https://i.imgur.com/Fa9i9EQ.png)

* 遞移相依
![](https://i.imgur.com/VvZrunF.png)


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

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

### 2NF
- 非鍵值屬性皆**完全功能（函數）相依**於主鍵
- 拆解關聯 → 決定外鍵
![](https://i.imgur.com/hNBt6ly.png)


### 3NF
- 遞移函數相依
- 功能相依 X → Y（Y 遞移相依於 X），存在另外功能相依 X → Z & Z → Y，且 Z 不為超級鍵，則 X → Y 為遞移相依。
（= 非鍵值屬性間，不該存在功能相依性）
- 作法：拆解關聯 → 決定外鍵
![](https://i.imgur.com/cNWNiIm.png)



### BCNF（Boyce-Codd）
滿足 3NF 卻不滿足 BCNF
![](https://i.imgur.com/G2tHui0.png)

滿足 BCNF
![](https://i.imgur.com/6Ic8Bfz.png)


![](https://i.imgur.com/52Q1Bdq.png)
一個關聯 R 為 BCNF 表格，iff 該關聯表中的功能相依性之決定因素都是候選鍵（不含有多餘屬性的超鍵）。
即：關聯R中，每個功能相依 Xi→Yi 中，Xi 為 R 之候選鍵。

![](https://i.imgur.com/4d7khOW.png)


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

不符合 4NF
![](https://i.imgur.com/AxZP1Xw.png)

符合 4NF
![](https://i.imgur.com/idJ5yzq.png)

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

### 資料＆追蹤記錄
![](https://i.imgur.com/hsnVfG9.png)

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

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

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

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

### 交易復原評判交易排程
- 可復原
![](https://i.imgur.com/zY5DhtU.png)

- 無連鎖駁回
![](https://i.imgur.com/L7I2yoF.png)

- 嚴格
![](https://i.imgur.com/dkBTFBz.png)

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