# Database Management System

# Preface

如何學習資料庫以及如何使用這些學習資源

### Terminlogy

- Entity Relationship Diagram ERD
- Entity Relationship Model ERM
- Database Administrator DBA
- Precedence Graph 優先順序圖、先行圖
- Super Key 指在該 Table 中為 Unique 的 Key，可為複合的
- Candidate Key 指在該 Table 中為 Unique 且 Minimal 的 Key
- Primary Key 指從 Candidate Key 中挑選其中一個，也就是最具有識別意義的 Key
- Alternate Key 指沒有被選為 Primary Key 的其他 Candidate Key
- Foreign Key 指將目前 Table 關聯至其它 Table Primary Key 的 Key
- Surrogate Key 指 Table 中的 Candidate Key 都不適合當 Primary Key 的情況下，如資料太長等等，就會用一個無意義的但唯一的 Column 來代作 Primary Key
- Full Functional Dependency 完全功能相依
- Partial Functional Dependency 部分功能相依
- Transitive Dependency 遞移相依、間接相依
- Alter 修改
- Collation 定序
- Cardinality 基數
- Ascending 升序
- Descending 降序
- Relational Algebra 關聯式代數
- Workbench 工作臺
- Software Bundle 軟體安裝包
- Constraints 限制、約束
- Extended Form of BNF Notation 擴展巴科斯範式
- Object Relational Mapping ORM 物件至關係的映射
- Schema 指 DB 中 資料在 Table 中如何被儲存的格式
- Data Definition Language DDL 資料描述語言，描述資料表中各欄位的內容
- Data Manipulation Language DML 資料庫操作語言，操作資料庫進行寫入動作
- Data Control Language DCL 資料庫控制語言，操作資料庫的權限
- Data Query Language DQL 資料庫查詢語言，操作資料庫進行讀取動作
- CRUD Create、Read、Update、Delete 增刪改查的縮寫
- DBCP Database Connection Pool 資料庫連接池
- ODBC Open Database Connectivity 開放資料庫互聯
- Java Database Connectivity Java 資料庫互聯
- Lossless Decomposition 無損失分解
- JavaServer Pages JSP
- Transaction Tx 指資料庫執行過程中的一個邏輯單位，一個 Tx 中含多個對 DB 的操作，且只能有兩種結果：全部執行成功或全部不執行，即只要其一行失敗就 Rollback
- Servlet Server Applet

# Introduction

### The Benefits of Using Databases

- 降低資料的重複性 Redundancy
Database 的核心精神即是在相同的資料情況下，只需儲存一次，而之間則以關聯連接，以減少資料的重複性
- 達成資料的一致性 Consistency
相同的資料（如學籍資料）在資料庫中可能是各部門共用的（教務處、學務處），若有某一項資料更新，則其他單位必須同時更新資料，則資料有一致性
- 達成資料的共享性 Data Sharing
指同一份資料可在同一時間提供給多位使用者同時 Access Data
- 達成資料的獨立性 Data Independence
指 Data 與 Program 之間無關、獨立，當使用者對 API 有新增、修改需求時，可以修改外部的 Program，並不影響 Data 的儲存結構
- 達成資料的完整性 Integrity
即透過 Integrity Constraint 可以防止 Database 產生 Abnormal
- 避免紙張與空間浪費 Reduce Paper and Space
原始的資料庫都是使用紙本儲存，搜尋慢以外還需要極大的儲存空間
- 達成資料的安全性 Security
資料庫方便備份，且帶有權限管理功能，能夠保障資料的安全性

### ACID

指 DBMS 在寫入、更新或刪除資料的過程中，為保證 Transaction 是正確可靠所必須具備的四項特性

- 原子性 Atomicity
又稱不可分割性，一個 Transaction 中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節，Transaction 在執行過程中若發生錯誤，會被恢復（Rollback）到 Transaction 開始前的狀態，就像這個 Transaction 從來沒有執行過一樣
- 一致性 Consistency
在 Transaction 開始前和 Transaction 結束以後，Database 的完整性沒有被破壞，這表示寫入的資料須完全符合所有的預設 Integrity Constraint、Trigger、Cascading Rollback
- 隔離性 Isolation
數據庫允許多個 Transaction 以 Concurrency 的方式同時對其 Database 的內容進行 Read、Write、Update，而具有隔離性可以防止多個 Transaction Concurrency 時由於交叉執行而導致數據的不一致，在修改資料時必須要取得 Lock，使其他 Transaction 不會與現在正在修改資料的 Transaction 衝突，例如 A、B Transaction 皆正在執行時，它們進行的時候所使用的資料庫應該要長的一模一樣，不會發生 B 抓到的資料是 A 進行到一半的資料庫
- 持久性 Durability
Transaction 結束後，對數據的修改就是永久的，即便系統故障也不會丟失

### Integrity Constraints

Integrity Constraint 可確保資料庫的 Integrity，避免資料經過 Create、Delete、Update（也就是 Write 行為）會產生 Abnormal，若嘗試將錯誤、不合法的 Record 存入 Database 之中會被阻擋

- 實體完整性 Entity Integrity
每個單一 Table，都必須要有 Primary Key，Primary Key 必須為 Unique 且 Not Null
- 參考完整性 Referential Integrity
如果一個 Foreign Key 存在一個 Table 之中，則 Foreign Key 必須要 Match 到其他 Table 中的 Candidate Key or Primary Key，且對應到的 Key 必須一定要存在，不可對應到 Null
- 值域完整性 Domain Integrity
每個單一 Table，同一 Column 的資料屬性必須要相同

### DDL、DML and DCL

- Data Definition Language DDL
資料描述語言，描述資料表中各欄位的內容
- Data Manipulation Language DML
資料庫操作語言，操作資料庫進行寫入動作
- Data Query Language DQL
資料庫查詢語言，操作資料庫進行讀取動作
- Data Control Language DCL
資料庫控制語言，管理、設定使用者的權限

其中 DML 又可分為以下兩種：
- High Level DML = Non-Procedural DML
- Low Level DML = Procedure DML

Non-Procedural DML 需要使用者去指定要取得哪些資料，但不需要指定如何取得資料，SQL 就是一種 Non-Procedural DML，而 Procedural DML 不只需要指定要取得哪些資料，還要一步一步地指定如何取得資料的步驟，舉例說 Relational Algebra 就是一種 Procedural DML

此外，有些書籍將 DML 以及 DQL 合併當成 DML，即包含完整的 CRUD 四種操作

# Entity Relationship Model

### Attributed

一個 Entity 即是由 Attributes 所組成，E.g. Student is Composed By Name、ID、Age... 而Attribute Types 有以下幾種分類方式：

- Single-Valued and Multi-Valued Attributes（E.g. Phone is a Multi-Valued Attributed, Everyone Can Have Multiple Phones）
- Simple、Composite Attributes and Derived Attributes（Can be Computed From Other Attributes E.g. Age、Given Date Of Birth）

### Entity

在 ERM 之中，Entity 通常被視為真實世界的物件，含有各種 Attribute，E.g. Name、ID、Age，而 Entity 又可分為 Regular Entity、Weak Entity

### Entity Sets

Entity Set 即是多個擁有相同 Attribute 的 Entities 的集合

### Relationship

Relationship 指 Entity 之間的關係，通常含有動詞意義，E.g. "Amy Chooses Digital Logic Design Course" is a Relationship Between Amy（Student's Entity） and Digital Logic Design（Course's Entity）

${(e_1, e_2, … e_n) | e_1 ∈ E_1, e_2 ∈ E_2, …, e_n ∈ E_n}, (e_1, e_2, …, e_n)$ is a Relationship, Every Entity(e) are the Attribute of Every Respective Entity Set

Relationship 又可分為四種類型：

- One to One
- One to Many
- Many to One
- Many to Many

### Relationship Sets

Relationship Set 即是多個 Relationship 的集合，可根據連出去的數量，即 Degree of a Relationship Set，分為 Unary、Binary、Ternary、N-ary Relationship

### Keys

- Super Key
指選出兩個或兩個以上的 Column 組合起來，以作為 Unique 的資料欄位
- Candidate Key
即 Minial 的 Super Key
- Primary Key
由 Candidate Key 中依據語意，選出一個 Candidate Key 作為 Primary Key，剩下的則稱作 Alternative Key
- Alternative Key
指所有 Candidate Key 中除了 Primary Key 以外所剩下的
- Composite Key
指是由兩個或兩個 Column 以上所組成的 Primary Key
- Foreign Key
任兩 Table 要進行關聯時，須透過 Foreign Key 參考 Primary Key 才能建立，其中持有 Primary Key 的 Table 稱作 Parent，持有 Foreign Key 的 Table 則稱作 Child，　Foreign Key 的存在是為了要保證 Referential Integrity 而給予的一種限制

### How to Convert ERD into Table

- 將每個 Entity 名稱轉為 Table 名稱
- 將每個 Entity 的 Attribute 名稱轉為該 Table 的 Column 名稱
- 將每個 Entity 中含有底線的 Key Attribute 在 Table 中設為 Primary Key，如果這個 Key 是 Composite Attribute，則這個 Composite Attribute 所含的 Columns 皆為 Primary Key 的一部分
- 如果兩 Enity 以 One to One 連接，則在其一 Table 加入 Foreign Key 連接至另一 Table
- 如果兩 Enity 以 One to Many、Many to One 連接，則在 Cardinalty 為 Many 的 Table 加入 Foreign Key 連接至 Cardinalty 為 One 的 Table

# Normalization

想法上是將一個大 Table 分解成小的各個 Table，讓 Table 的 Data 能夠 Reuse，目的是將 Database 所需的儲存空間更小（Data Redundancy）、避免資料更新異常（Anomalies）發生的機會，一般而言，正規化的精神就是讓資料庫中重複的欄位資料減少到最少，並且能快速的找到資料，以提高 Database 的性能

Normalization 共可分為六種階段，分別為：
- First Normal Form 1 NF
即所有紀錄中的屬性的內含值都是單一的，未包含重複項目群
- Second Normal Form 2 NF
符合 1 NF 且每一非 Primary Key 的 Column 都必須是 Full Dependece on Primary Key，即代表不可 Partial Dependence on Primary Key
- Third Normal Form 3 NF
符合 2 NF 且每一非 Primary Key 欄位非 Transitive Dependence on Primary Key 即去除 Transitive Dependence
- Boyce-Codd Normal Form BCNF
符合 3 NF 且每一決定因素皆是 Candidate Key
- Fouth Normal Form 4 NF
實務上不使用
- Fifth Normal Form 5 NF
實務上不使用

### What is Data Anomalies

資料異常可分為三種：

- 新增異常 Insert Anomalies
新增某些資料時必須同時新增其它資料，否則會產生新增異常，，亦即因 Integrity Constraint，另一個 Entity 的資料尚未插入之前，無法插入目前這個 Entity 的資料
- 修改異常 Update Anomalies
修改某些資料時必須一併修改其它的資料，亦即因 Integrity Constraint，修改的資料不符合規則而產生的異常現象
- 刪除異常 Delete Anomalies
刪除某些資料時必須同時刪除其它的資料，否則會產生刪除異常，亦即刪除單一資料列造成其它 Table 中的 Record 遺失，需使用 Integrity Constraint 限制

### Lossless Decomposition

Lossless Decomposition 無損失分解，代表將原先關聯的 Table 的所有資訊，在分解成多個 Table 後，仍能經過 Join 得到原先未分解前的資訊

### Functional Dependence

假設 R 代表一個 Table，X、Y 是該 Table 中的 Column，若 X → Y（X Mapping to Y）即代表 X 的值決定 Y 的值，這就稱做 Y is Functionally Dependency on X，或也可以稱為 X Funcitonally Determines Y，X 也可被稱作 Determinant

Functional Dependence 又可分為以下三種：
- Full Functional Dependency 完全功能相依
R（X,Y,Z）包含一組 Functional Dependecy（X,Y) → Z，若我們從 R 中移除任一屬性 X 或 Y，則此 Mapping 不存在，即為 Full Functional Dependecy
- Partial Functional Dependency 部分功能相依
R（X,Y,Z）包含一組 Functional Dependecy（X,Y) → Z，若我們從 R 中移除任一屬性 X 或 Y，則此 Mapping 依舊存在，即為 Partial Functional Dependecy，代表其中有一屬性為冗餘
- Transitive Dependency 遞移相依：
指兩 Column 間並非 Direct Dependecy，而是藉由中間的 Column 來達成間接 Mapping 的關係，例如 R（X,Y,Z），X → Y，Y → Z，則 X → Z

### 1 Normal Form

定義：在一個 Table 內，欄位的內容都是 Atomic Value，且沒有任何兩筆以上的 Record 是完全重覆，而 Atomic 的意思是指：屬性必須要是單值屬性，不能是複合、多值或巢狀多值

| student_id | name | sex | course_id | course_name | credit | required/elective| grade | teacjer_id | teacher_name |
| ---- | ----- | ---- | -------------------- | ---------------------------------------------------------------------- | ----------- | -------------------------------- | ------ | -------------------- | --------------------- |
| 001 | John | Male | C001<br>C002 | Programming Language <br> Web Design | 4<br>3 | Required<br>Elective | B<br>C | T001<br>T002 | Nancy<br>Amy |
| 002 | David | Male | C002<br>C003<br>C005 | Web Design<br>Introduction Of Computer Science<br>Digital Logic Design | 3<br>2<br>4 | Elective<br>Required<br>Elective | ABA | T002<br>T003<br>T005 | Amy<br>Kevin<br>Brian |

具體作法是將多值屬性的部分拆成另一個 Table，並且多該的 Table 的 Key 還要加入原 Table 的 PK，以上述 Table 來說將分解為：student（<u>student_id</u>, name, sex）, course（<u>course_id, student_id</u>, course_name, credit, required/elective, teacher_id, teacher_name）

### 2 Normal Form

定義：符合 1NF，且 Primary Key 以外的 Column 都必須 Full Functionally Depend on Primary Key，而不能是 Partial Dependence，例如 RentalLine（<u>TransID, VideoID</u>, Copy, Title, Rent），Copy Depend on TransID、VideoID，而 Title、Rent 則只 Partial Depend on Video ID，因此該 Table 可分解成 Videos（VideoID, Title, Rent）以及 VideosRented（TransID, VideoID, Copy）

### 3 Normal Form

定義：符合 2NF，且 Primary Key 以外的 Column 都不能是 Transitive Dependence，例如 RentalForm（<u>TransID</u>, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode），之鍾的 Phone、Name、Address、City、State、ZipCode 與 TransID 之間的關係都是 Transitive Dependency，中間還夾了一個 CustomerID，因此經過 3NF 可分解為 Rentals（TransID, RentDate, CustomerID）、Customers（CustomerID, Phone, Name, Address, City, State, ZipCode）

### Boyce-Codd Normal Form

定義：又稱作 3.5 NF，須符合 3NF，且若某個 Table 的由多個欄位所組成，則可以再進行 BCNF（只有單一屬性的 Primary Key 必定符合 BCNF），原則即 Primary Key 中的各欄位不可 Depend on 他非主鍵的欄位

BCNF 與 3NF 的不同之處在於：3NF 中不允許 non-Primary Attribute 被另一個 non-Primary Attribute 決定，但 3NF 允許 Primary Attribute 被 non-Primary Attribute 所決定，但在BCNF中，任何 Attribute（包括 non-Primary Attribute 和 Primary Attribute）都不能被 non-Primary Attribute 所決定，即 Table 中的所有 Determinant 都是 Candidate Key

在某個 Relation 的 Candidate Key 之中包含的屬性就稱作 Primary Attribute，反之稱作 non-Primary Attribute

任何一個BCNF必然滿足：

BCNF 必滿足：
- 所有非 Primary Key 的 Column 都 Full FD 於每個 Candidate Key
- 所有 Primary Key 都 Full FD 於每個不包含它的 Candidate Key
- 沒有任何 Column Full FD 於非 Candidate Key 的任何一組屬性，即沒有 Partial Dependence

### 4 Normal Form

# Structured Query Language

# Relational Algebra

Relational Algebra 常用的 Operator 共有八種，其中五個為 Primitive Operators，另外三個則為 Non-Primitive Operators

### Primitive Operators

共有五種 Primitive Operators，為 Relational Algebra 中最基礎、不可取代的五種最基本的 Operator，該五種 Primitive Operators 無法由其他 Operator 導出：

- Union 聯集
- Difference 差集
- Cartesian Product 笛卡爾積、卡式積、乘積
- Select 選擇
- Project 投影

只要有以上五種 Operator 就能建構出關聯式資料庫的查表所有功能（不包含 Aggregation Function）

### Non-Primitive Operators

可由 Primitive Operators 推出，但由於經常使用，因此賦予特定的名稱以及符號給予這些操作

- Join 合併
- Intersection 交集
- Division 除法

以上八種 Operator 除了 Project、Select 外，其餘的運算為 Binary Operator 二元運算子，它們具有以下的性質：

- Closure 封閉性
任何 Relation 經過了上面的運算後，其輸出結果仍是一個 Relation
- Nested 巢狀性
Operand 的輸出可以做為下一個 Operand 的輸入，即代表可以做連續的操作，其結果仍是 Relation

### Relationally Complete

以 Relational Calculus 這個查詢語言為基準，若有某一種關聯式語言可以處理與 Relational Calculus 一樣的功能時，即 Relationally Complete，Relational Algebra 以及 SQL 都屬於 Relational Complete

### How to Use Primitive Operators to Form Intersection Operator

$R \cap T = R1 - (R1 - R2) = R2 - (R2 - R1)$，所以只要有 Union Operation、Difference Operation 就可以做出 Intersection Operation

### How to Use Primitive Operators to Form Division Operator

### Unary Operation

- Selection：$\sigma_{condition}(R)$，將 Relation 中符合 Condition 的 Tuple 合成一個新的 Relation
- Projection：$\pi_{attribute\_list}(R)$，將 Relation 中的各個 Tuple 的屬性篩選，只取在 Attribute List 的部分，重新組成新 Relation 的 Tuple
- Rename（Special）：$\rho_{S(attr1,attr2,attr3)}(R)$，將 Tuple 中的特定 Attribute Name 重新命名，S 代表 New Relation Name，attr1、attr2 為 New Attribute Name，若只要修改 Relation Name 可改寫為 $\rho_{S}(R)$，若只要修改 Attribute Name 可改寫為 $\rho_{(attr1,attr2)}(R)$，這個運算通常不會被包含在正統的 Relational Algebra 之中

### Binary Operation

- Union：$S\ \cup \ T$，將兩個 Relation 聯集
- Intersection：$S\ \cap \ T$，將兩個 Relation 交集
- Difference：$S\ - \ T$，將兩個 Relation 差集
- Product：$S\ \times\ T$，將兩個 Relation 中的兩組 Tuple 交叉組合，合成為一個新的 Relation 的 Tuple，New Relation 中含有的 Tuple 數量為 $\mid S\mid * \mid T \mid$ 個
- Division：$S\ \div\ T$，等價於 $π_{x}(S_{x,y})-\sigma_{x}(S_{x,y}\times T_{y}))$，通常用於以 T 為條件，從 S 中查詢符合的 Tuple
- Join：$S\Join_{condition}T$，等價於 $\sigma_{<condition>}(S\times T)$，將兩個 Relation 中的 Tuple，根據某個 Condition 而 Join 為一個 Relation，Join Operation 實際上就是先將兩個 Relation 做 Product 後再 Selection，Join 又可再分為兩類：
- Inner Join：用於使用給定的 Condition 將 Relation 篩選，且得出的 Relation 必定是原兩個 Relation 的 Intersection，Outer Join 則相反
- Natural Join：
將兩個 Relation 中各 Tuple 的 Attribute Name 相同的 Column 合併，通常 Natural Join 的運算會另外使用 Star 這個 Operator 表示（\*），E.g. $S * T$
- Theta Join：
將兩個 Relation 中各 Tuple 合併時，若 Condition 的 Compare Operator 為 >、<、=、!=、>=、<= 時即為 Theta Join，E.g. $S\Join_{attr1\ \theta\ attr2}T$
- Equi Join：
將兩個 Relation 中各 Tuple 合併時，若 Condition 的 Compare Operator 為 = 時，即為 Equi Join，同時也是 Theta Join 的特例
- Outer Join： 在 Outer Join 中，即使 Relation 與另一個 Relation 不見得 Match，產出的 New Relation 仍會保留它，即 New Relation 除了原兩個 Relation 都 Match 的 Record 外還包含 Unmatch 的 Record，共有以下三種：
- Left Outer Join
- Right Outer Join
- Full Outer Join

目前所提到的 Relational Algebra 只是提供最基本的功能，尚無法做到 Outer Join，只能做 Inner Join，而 Outer Join 的部分之後篇章再深入描述

### Properties of Relational Algebra

- 結合律 (Associativity)
- 交換律 (Commutativity)
- 分配律 (Distributivity)

# Extended Relational Operations

實務上商業應用的 RDBMS 不能使用 Relational Algebra 操作，因此在此段落我們會定義 Extended Relational Operations 以表達這些需求，這些功能可以增強 Relational Algebra 的表達能力，新增的功能共有五項：

- Generalized Projection
- Aggregate Function
- Recursive Closure Operations
- Outer Join
- Outer Union Join

### Generalized Projection

Projection Operation 的擴展讓 Projection 可以使用以 Attribute 為輸入的 Function，通常以 $\pi_{F_1,F_2,F_3...F_n}(R)$ 表示，Function 之中的操作可以有 Arthmetic Operator and Constant

E.g. Employee(ID, Salary, Deduction, Years_Serviced)，若想求出各員工的 Net Salary、Bonus、Tax，則以 $REPORT = \rho_{ID,\ Net Salary,\ Bonus,\ Tax}(\pi_{ID, Salary\ -\ Deduction,\ 2000\ *\ Years\_Serviced,\ 0.2\ *\ Salary})(EMPLOYEE)$ 這個 Generalized Projection表示

### Aggregate Function

Aggregate Function 即為類似 Excel 中的 SUM、AVG、COUNT、MAX、MIN... 等，用於輸入一組 Relation，但輸出並非 Relation，而是 Value，這與舊有的 Relational Algebra 不同

Aggregate Function Operation 通常以 $_{grouping\_attributes}F_{function\_list}(R)$ 表示，Grouping Attribute 代表要以那個 Attribute 進行 Grouping，Function_list 的元素為 <Func, Attr>，Func 代表 Aggregate Function Name，Attr 則代表該 Function 所需作為參數的 Attribute

該函數以 Relations 作為輸入，以 Grouping Attribute 分組後，分別依據各 Aggregate Function 所需的 Attribute 分別輸入後的輸出的 Value 再組成 Relations 後輸出

E.g. 若我想得到所有員工數量以及平均薪資，則 Aggregate Function 可表示為：$F_{COUNT_{employee}, AVG_{salary}}(R)$

E.g. 若我想得到各部門的員工數量以及平均薪資，則 Aggregate Function 可表示為：$_{departmentID}F_{COUNT_{employee}, AVG_{salary}}(R)$

# MySQL

### Software Bundle

建立資料庫後若要應用，可能會用於架設網站、手機應用程式等等，因此會需要多種軟體、程式語言等交互使用，而發展成一個生態系，而這些軟體、程式語言要分開安裝過於麻煩，因此我們常常會用組合包的方式去快速建立環境，而這些組合包主流有以下幾類：

- XAMPP：X（意旨跨平台）、Apache、MariaDB、Php、Perl
- MAMP：MacOS、Apache、MySQL、PHP、Python、Perl
- LAMP：Linux、Apache、MySQL、Php
- WAMP：Winodws、Apache、MySQL、Php

原則上命名都是來自使用的語言、軟體、平台的首字元組成，但組合包中也不會僅僅只含有這些東西，例如 MAMP 的 Web-Server 除了 Apache 以外也含有 Nginx

### Collation

指 Database 將資料 Sorting 的方式，可分為以下幾種：

- Case Sensetivity（CS）
區分大小寫，如果是 Case Insensitive（CI）的話 A、a 在排序或者查詢時就會被視為相同，都會被一起查詢到
- Accent Sensitivity（AS）
代表的是腔調上的差別，a 跟 a、o 跟 o 在腔調上是相同的，那查詢時是要視為相同，反之也有 Accent Insensitive（AI）
- Kana Sensitivity（KS）
日文中的片假名與平假名如果被視為相同，那就是 Kana Insensitive（KI），反之就是 Kane Sensitive（KS）
- Width Sensitivity（WS）
當半形與全型被視為相同（A跟Ａ），就是Width Insensitive（WI），反之就是 Width Sensitive（WS）

### Comments

MySQL 共有三種註解方式：

- 以 # 開頭直到行尾
- 以 -- 開頭直到行尾
- 以 /* */ 開頭及結尾，可多行註解

### Type

Table 的各個 Column 中通常使用的各種型別：

- Int：整數
- Decimal(m, n)：即浮點數，m 代表總共有幾位數，n 代表有幾位小數
- Char(n)：固定長度字串，n 代表最多能存放多少字元
- Varchar(n)：不定長度字串，n 代表最多能存放多少字元
- Blob：Binary Large Object：用於存放圖片、影片、檔案
- Date：紀錄日期
- TimeStamp：紀錄時間

### DDL Data Definition Language

此類指令針對 Database、Table、View 的定義進行建立、修改、刪除

||Databases |Table|View|
|---|---|---|---|
|Create|Create Database|Create Table|Create View|
|Alter|Alter Database|Alter Table|Alter View|
|Drop|Drop Database|Drop Table|Drop View|

符號說明：
- \{ | \}：代表必填，且擇一即可
- \[ | \]：代表選填，且擇一即可，也可不選

-- Database

-- 以下指令的 DATABASE 這個 Keyword 都可改為使用 SCHEMA
CREATE DATABASE [IF NOT EXISTS] `Database_Name`;
-- 建立 DATABASE，如果沒有加上 IF NOT EXISTS 且已經存在該 Database_Name，則會產生錯誤
ALTER DATABASE `Database_Name`
[CHARACTER SET `Character_Set_Name`]
[COLLATE `Collation_Name`];
-- 修改 DATABASE，並且可修改它所使用的 Character_Set 以及 Collation
DROP DATABASE [IF EXISTS] `Database_Name`;
-- 刪除 DATABASE，如果沒有加上 IF EXISTS 且不存在該 Database_Name，則會產生錯誤

-- Table

CREATE TABLE [IF NOT EXISTS] `Table_Name` (
    `Column_Name` {Type | Domain} [NULL | NOT NULL] [DEFUALT Default_Value] [AUTO INCREAMENT]
    ...,
    ...,
    ...
    PRIMARY KEY (Column_Name_Set)
    UNIQUE (Column_Name_Set)
    FOREIGN KEY (Column_Name_Set) Reference `Table_Name` (Attribute_Name_Set ) 
    [ON DELETE CASCADE|RESTRICT|NO ACTION|SET NULL]
    [ON UPDATE CASCADE|RESTRICT|NO ACTION|SET NULL]
);
-- 建立 Table，建立 Column 的部分可以有多筆，使用逗號分隔
ALTER TABLE `Table_Name` (
    [
        ADD `Column_Name` {Type | Domain} [NULL | NOT NULL] [Default_Value] [Integral_Constraint]
    |
        MODIFY `Column_Name` {Type | Domain} [NULL | NOT NULL] [Default_Value] [Integral_Constraint]
    |
        DROP [COLUMN] `Column_Name`
        -- DROP 後面的 COLUMN 可加可不加
    ]
)
-- 修改 Table
DROP TABLE [IF EXISTS] `Table_Name`
-- 刪除 Table

-- View
VIEW 待補

### DML Data Manimuplate Language

針對 Table 操作的語言，將其之中的 Record 做增刪改查，因此主要有以下四種指令：
- INSERT
- UPDATE
- DELETE
- SELECT

INSERT INTO `Table_Name` VALUES
(<Sequence_of_Record_Value> | <Select_Command>),
...,
...,
...
;
-- 對 Table_Name 新增資料，Values 後面可接上多筆 Data 使用逗號分隔
UPDATE `Table_Name` SET {<`Column_Name`> = <Value>}
[WHERE <Condition>];
-- 對 Table_Name 更新資料
DELETE FROM `Table_Name` [WHERE <Condition>]
-- 對 Table_Name 刪除資料
SELECT `Sequence_of_Columns_Name` --- 欲投影的欄位名稱
FROM `Table_Name`
[WHERE <Condition>]
[GROUP BY <Sequence_of_Columns_Name>]
[HAVING]
[ORDER BY <Columns_Name>[ASC|DESC]]
[LIMIT Number] --- 限制顯示的數量
-- 對 Table_Name 查詢資料

### Join

可以將兩個表格連接在一起

Select * From `employee` Join `branch` on `emp_id` = `manager_id`
# 選擇 employee 的 Table Join branch 以 emp_id = manager_id 作為媒介

Join 實際上分為三種，一般 Join、Left Join、Right Join，一般 Join 就是指兩邊 Join 的表格一定要左右互相匹配才會 Print 出來，Left Join 則代表左邊的資料會全部 Print 出來，但是右邊的要有匹配到才會，否則就顯示空，Right Join 也是同理

select * from `employee` left join `branch` on `emp_id`=`manager_id`;
# 將 employee 的資料全部 Print 出來，並且有匹配到的 branch 資料也 Print 出來，沒有則 Null

### Subquery

假設你要使用一個查詢結果再次查詢，那該怎麼做

Select `name` From `employee` Where `emp_id` = ???;
Select `manager_id` From `branch` Where `branch_name` = '研發';
# 假設我要以第二行查詢到 manager_id，然後我又要使用它做為第一行的查詢參數
# 我就可以使用以下的子查詢語法
Select `name` From `employee` Where `emp_id`=(
Select `manager_id` From `branch` Where `branch_name` = '研發'
);

### On Delete、On Update

當 Table A 透過 Foreign Key 對外連接上 Table B 的 Primary Key 時，即 Table A 的資料依賴於 Table B 的 Primary Key，若 Table B 的 Primary Key 不存在某項資料，則 Table A 的 Foreign Key 必定也不包含該資料，因此 Table B 的 Primary Key 的修改會影響 Table A，而設定 On Delete、On Update 的設定即是決定當 Table B 的 Primary Key Update、Delete 時，要如何同步 Table A 的 Record，以達成 Referential Constraint

FOREIGN KEY ('manager_id') REFERENCE `employee`(`employee_id`) ON DELETE SET NULL
-- 當 employee_id 被刪除時，則 manager_id 欄位就設置為 NULL
FOREIGN KEY ('manager_id') REFEREMCE `employee`(`employee_id`) ON DELETE CASCADE
-- 當 employee_id 被刪除時，則其他有對應到 employee_id 的 Record 會整筆刪除

### Syntax Convention

- 使用大寫字表示 Keyword
- 使用小寫字表示 User-Defined Word
- Command 的結尾都要加上分號
- 字串通常會需要使用 \` 括起來，Ex：Create Database \`sql_tutorial\`，使用該符號可以避免名稱與 Keyword 衝突
- 通常 Keywork 會習慣以大寫字體表示，但實際上無論大小寫都是可以運作的
- INNER JOIN 通常會省略 INNER，只寫成 FROM r1 JOIN r2 ON...，另一種寫法則是只用逗號隔開多個 Table，然後用 WHERE 取代 ON，寫成 FROM r1, r2 WHERE ...，但由於語意問題，雖然說 JOIN 可以使用 FROM WHERE 做到，但為了語意上直覺，建議使用 FROM JOIN ON 會比較好，WHERE 才是真的邏輯上需要的判斷式
- LEFT/RIGHT OUTER JOIN 通常會省略 OUTER，寫成 LEFT/RIGHT JOIN，而大多數情況下會用 LEFT JOIN，只要把 Table 的順序對調，RIGHT JOIN 就會變成 LEFT JOIN，因此實務上較少會用 RIGHT JOIN

### Execution Ｏrder of SQL

1. FROM：指定所需表格，如果有兩個或兩個表格以上，則先進行乘積再 JOIN
2. ON：透過 ON 所含的 Table 的條件進行篩選
3. JOIN：Join Table
4. WHERE：透過 Condition 篩選 Record，一般都不包含 Aggregate Function
5. GROUP BY：根據指定欄位進行分群
6. HAVING：找出符合指定條件的所有群組，都一定要是使用 Aggregate Function
7. SELECT：指定欄位 Projection
8. DISTINCT：指定為列出不重複的 Record
9. ORDER　BY：排序
10. LIMIT IN：指定只列出 N 筆紀錄

# Transaction

### Syntax

savepoint 中斷點

### Memo

- Single-Valued Attribute 使用橢圓形表示
- Multi-Valued Attributes 使用雙圈橢圓形表示
- Derived Attributes 使用虛線橢圓形表示
- Entity Set 使用長方形表示
- Relationship Set 使用菱形表示
- Unique Attribute 使用底線表示，因此 Primary Key 必定有底線
- Partial Key 使用底線虛線表示
- Partial Participation 使用 Single Line 表示關係
- Total Participation 使用 Double Line 表示關係
- 資料庫系統 Index 是從 1 開始而不是 0，寫程式處理時須注意
- Cardinalty 描述 Relationship 之間連結的最大的可能性
- Relation Schema 以及 Relation 的差異相當於 Class 與 Instance 的差異
- Weak Entities 一定是 Total Participation
- 水平的資料即為 Entity，又可稱作 Row、Tuple、Record
- 垂直的屬性即為 Attributes，又可稱作 Column、Field
- Relation 中每個 Attribute 所有允許的值而成的 Set 稱做 Domain
- CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4__unicode_ci：創建 Table 的時候順便設定之後 Table 預設的 Charset 以及 Collation
- Table 又可稱作 Relation，且 Relation Model 與 Entity Relation Model 的 Relation 意義不相同，前者是指 Table，後者是指 Entity 之間的關係，前者的 Relation 同時是後者的 Entity 以及 Relation
- A Left Join B 的數量不一定等於 A，A Right Join B 的數量也不一定等於 B
- 在 ERM 之中 Relationship 指的是 Entity 之間的關系，在 Relation Model 中 Relation 指的是 Table 也對應到 ERM 的 Entity 以及 Relationship
- 當 Modify 欄位時，如果修改時有任意 Record 被 Turncate 則視為錯誤，例如 address CHAR(10) 被修改成 CHAR(6) 時，有任意的 Record address 超過六個字元即錯誤
- 如果某個 Column 同時是 PK，又是對外的 FK，則該 FK 對外的 Referential Integraity 的 ON DELETE 處理不能是 SET NULL，若為 NULL 則違反 PK，會導致錯誤
- 要檢查某個欄位是否是 NULL，必須要用 IS / IS NOT 檢查，例如 age IS NULL，不可使用等號！
- NATURAL JOIN 的時候相同的欄位會被刪除
- 一對多、多對一的 relationship 要將關聯的欄位加在多的地方
- AS 可以省略
- 多值屬性實作成 Table 通常為多對多，例如在 Employee 的描述 Expertise 的 Column 中可能員工 A、B 都會有會計專長，且會計專長也會對應到多位員工，因此為 M:N，但如果是對應到 Unique 的，例如員工對到手機號碼，雖然為多值屬性，但反而變成 M:1 了
- BEGIN TRANSACTION COMMMIT 若沒有包含在 TRANSACTION BLOCK 中，每句 statement 視為一個獨立的 transaction
- SQL statement 中注意不要有全形空白 看起來是對的，但實際上是錯的
- 網址的構成：scheme:[//[user:password@]host[:port]][/]path[?query][#fragment]，因此不一定需要 set username 以及 set password，scheme 通訊協議 ?query 參數，以 Key-Value 呈現，且以 & 分隔，fragment 片段，用法是在 # 號後面加上文字，類似書籤，會導向特定錨點，HTML 來說，會定到特定瀏覽的位置，對影片來說，會跳到對應的時間點
- table 要 union 只要欄位數量相同即可，即使欄位名稱不同也可以
- 使用 @ 代表使用者變數 而不是系統變數


### Reference
https://www.mysql.tw/2018/05/sqlrelational-algebra.html
http://120.105.184.250/lwcheng/961Access/textbook/kidpps/CHAP05.pdf
https://www.lis.ntu.edu.tw/~khchen/course/db/db2007/DBCh06RAlgebra.pdf
https://www.youtube.com/watch?v=OMwgGL3lHlI&list=PLBlnK6fEyqRiyryTrbKHX1Sh9luYI0dhX
https://www.books.com.tw/products/0010720461