# Database - Note
###### `大三上` `DB` `SQL`
---
# CH1 Overview of Database Systems
* File System
支援管理、文件命名、賦予存取規則等基本操作
早期DB建立在file system之上 <- 有問題 不優
* Data redundancy and inconsistency資料冗餘和不一致
* Difficulty in accessing data存取資料困難
* Data isolation資料隔離
* Integrity problems誠信問題
* Atomicity of updates更新的原子性
* Concurrent access by multiple多用戶並發訪問users
* Security problems安全問題
* Levels of Abstraction
* Physical level
>資料記錄（例如，講師）的儲存方式
* Logical level
```mysql
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
```
* View level
> highest level of abstraction
* Data Definition Language (DDL)
>資料定義語言（DDL）
> DDL定義了physical/ Logical/ view schema
>*懶人創Table超好用!!!*
>EX:
>```mysql
>create table instructor (
>ID char(5),
>name varchar(20),
>dept_name varchar(20),
>salary numeric(8,2))
>```
* Data Manipulation Language (DML)
>資料操作語言 (DML)
>DML 也稱為查詢語言
* Procedural DML
>要求使用者指定需要哪些資料以及如何取得這些資料
* Declarative DML
>要求使用者指定需要哪些數據，而不指定如何取得這些數據
:::info
:label:
▪ Declarative DMLs通常比Procedural DMLs更容易學習和使用
▪ Declarative DMLs 也稱為non-procedural DMLs
▪ DML中涉及資訊檢索的部分稱為查詢語言(query language)
:::
* Structured Query Language (SQL)
>結構化查詢語言 (SQL)
>主角登場!!!
* SQL是屬於non-procedural DML
* EX:
```mysql
select name
from instructor
where dept_name = 'Comp. Sci.'
```
* SQL is **NOT** a Turing machine(圖靈機) equivalent language(等效語言)
* App存取DB方式:
>* 允許嵌入SQL 的語言擴展
>* 應用程式介面（ODBC/JDBC） 允許將 SQL 查詢傳送到資料庫
* APP proogram DB access
>* 須使用主機語言（C/C++、Java or Python） 並帶有存取資料庫中資料的嵌入式SQL查詢
>* 應用程式 -- 用於以這種方式與資料庫互動的程式
* Query Processing
>
* Transaction Management交易管理
* Transaction-management component
>確保資料庫在系統故障和交易失敗的情況下仍保持正確狀態
* Concurrency-control manager
>控制並發交易之間的交互
* Partitioned into two or three parts
* Two-tier architecture兩層架構－應用程式駐留在客戶端電腦上，呼叫伺服器電腦上的資料庫系統功能
* Three-tier architecture三層架構－客戶端電腦充當前端，不包含任何直接的資料庫呼叫
* DB Users
* Database Administrator(DBA)資料庫管理員
---
# CH2 Introduction to Relational Model
* Relation schema
>
>* Relation instance = table
>* Instance of a relation = a set of tuples(records)
>* 盡量避免NULL值
* Keys
No two tuples can have the same key(兩個元組不能有相同的key)
* superkey
EX: {ID} and {ID,name} are both superkeys of instructor
* Foreign key
* 可以很複雜
* Relational Algebra
>Nine basic operators
>
* Select Operation
>
>p is called the selection predicate
>EX:
>選擇教師在「物理」系的tuple
>
>可以用這些比大小,找範圍
>
>可以用連接詞
>
* Project Operation
>
> Duplicate rows removed from result, since relations are sets(由於關係是集合，因此從結果中刪除了重複行)
> EX:
> 刪除instructo的dept_name屬性
> 忽略不要的傢伙 只寫我要的傢伙
> 
* 結合Composition of Relational Operations
>先select dept=physics 再顯示名字
>
* Cartesian-Product Operation笛卡兒積運算
>有兩個屬性一樣時 :point_down:
>透過將屬性最初來自的關係的名稱附加到屬性來區分這些屬性
>
>ID前方標示原本table(從哪來的)
>
* Join Operation
>
>可以選出 Instructor.ID 和 teachers.ID一樣的
>
>* 用theta表示屬性關係(R “union” S)
>
>* 下面這兩條等效
>
>\=
>
>* 只有黃色圈起來的部分符合條件
>
* Natural Join
>只將 R 和 S 中共有屬性一樣的tuple進行配對
>* 只有B有重覆到
>
* Set-Union Operation
>1. r, s必須具有相同的arity（相同的屬性數量）
>2. 屬性域必須相容(compatible)（r的第二列與s的第二列處理相同類型的值）
>* EX:
>尋找 2017 年秋季學期和/或 2018 年春季學期教授的所有課程
>
>* EX:
>欄位數量要一樣 類型也一樣
>
* Set-Intersection Operation
>跟union一樣
>1. r, s必須具有相同的arity（相同的屬性數量）
>2. 屬性域必須相容(compatible)（r的第二列與s的第二列處理相同類型的值）
>* EX:
>尋找 2017 年秋季學期和 2018 年春季學期教授的所有課程集
>
* Set-Difference Operation
>1. r 和 s 必須具有相同的數量
>2. r 和 s 的屬性域必須相容
>* EX:
>to find all courses taught in the Fall 2017 semester, but not in the Spring 2018 semester
>
* The Assignment Operation
>類似程式的賦值
>* EX:
>尋找“物理”或“音樂”系的所有講師
>
* The Rename Operation
>關係代數表達式的結果沒有我們可以用來引用它們的名稱 需要Rename
>
>* EX:
>將關係 r 和關係 s 之間的集合差異的結果重新命名為新模式 X，其中兩個屬性重新命名為 A1 和 A2
>(x 是新名字: A1,A2)
>
* Equivalent Queries
>* EX1:
>查找有關薪水超過 90,000 的物理系講師的信息
>第一種: 選物理系 且 薪水 > 90000的
>第二種: 先選出薪水 > 90000的 再從中選出物理系的
>
>* EX2:
>尋找有關在物理系教授課程的教師的信息
>第一種: 先看兩張表ID一樣的選出來 再從中抓出物理系的
>第二種: 先在一張表中抓出物理系的 再看兩張表ID一樣的抓起來
>
:::warning
:dart: 下面3位是延伸
Extended Operators of Relational Algebra
:::
* Duplicate elimination:
>透過刪除重複的tuple將套件轉換為集合
>刪掉多餘的重複傢伙
>
* The Grouping Operator
>不囉嗦看例子
>* EX:
>先把A ,B屬性分組 再取C平均
>
* The Sorting Operator
>上例子
>* EX:
>那個念(ㄊㄠ)
>以B優先排序 再來是C
>
---
# CH3 Introduction to SQL
* SQL Parts
* DML – 提供從資料庫查詢資訊以及在資料庫中插入元組、刪除元組和修改元組的能力
* Integrity－DDL包含用於指定完整性限制的指令
* View definition－DDL包含用於定義視圖的指令
* Transaction control－包括指定事務開始和結束的命令
* Embedded SQL and Dynamic SQL – 定義如何將SQL語句嵌入到通用程式語言中
* Authorization – 包含指定關係和檢視存取權限的命令
* Data Definition Language (DDL)
* Create Table Construct建立表結構
```mysql
create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
```
* Integrity Constraints in Create Table建立表中的完整性約束
>SQL會封鎖任何違反完整性限制的資料庫更新
```mysql
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department);
```
>如果使用primary key，則鍵中的屬性不允許將 null 作為其元件的值
* 標準 SQL
>只能有一個primary key，但有多個unique屬性
>primary key的任何屬性都不能為空，但聲明為unique的屬性可以為空
* distinct
>列出不同的
```mysql
-- 列出dept_name不重複的
select distinct dept_name
from instructor
```
* all
>有寫只是更加確認不移除重複項而已
* where
```mysql
select name
from instructor
where dept_name = 'Comp. Sci.'
```
>可以用 **and**, **or**, **not**, **<**, **<=**, **>**, **>=**, **=**, **<>**
>EX:
```mysql
select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 70000
```
* The Rename Operation重新命名操作
>用 as
```mysql
old-name as new-name
-- 找出所有薪資高於'Comp. Sci'中某些講師的講師的姓名
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.’
-- as 可以省略
```
* Self Join Example
>
* Find the supervisor of the supervisor of “Bob”
```mysql
select distinct S.supervisor
from emp_super as T, emp_super as S
where T.person = ‘Bob’ and T.supervisor = S.person
```
* String Operations
* 找字串中有“dar”的.
```mysql
select name
from instructor
where name like '%dar%'
```
* Match the string “100%”
```mysql
like '100 \%' escape '\'
```
* Ordering the Display of Tuples
* 依字母順序列出所有講師的姓名
```mysql
select distinct name
from instructor
order by name
-- order by name desc 可以降冪排列
-- order by dept_name, name 可以一次用多個排列
```
* Where Clause Predicates
* between
```mysql
select name
from instructor
where salary between 90000 and 100000
```
* Tuple comparison
>找出生物系所有教師的姓名誰教授過課程及其相應的課程 ID
```mysql
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
```
* Set Operations
```mysql
-- Find courses that ran in Fall 2017 or in Spring 2018
(select course_id from section where semester = 'Fall' and year = 2017)
union
(select course_id from section where semester = 'Spring' and year = 2018)
-- Find courses that ran in Fall 2017 and in Spring 2018
(select course_id from section where semester = 'Fall' and year = 2017)
intersect
(select course_id from section where semester = 'Spring' and year = 2018)
-- Find courses that ran in Fall 2017 but not in Spring 2018
(select course_id from section where semester = 'Fall' and year = 2017)
except
(select course_id from section where semester = 'Spring' and year = 2018)
```
>Set operations union, intersect, and except
>Each of the above operations automatically eliminates duplicates(自動消除重複項)
>要保留所有重複項，請使用
• union all
• intersect all
• except all
* Default Values(預設值)
* 通常，使用null 作為其預設值
* 可以為每個屬性選擇預設值
* When defining a relation schema:
```mysql
gender char(1) default ’?’
```
* When modifying a relation schema:
```mysql
alter table department add dept_head char(20) default ’unlisted’;
```
* Effect of Defaults
* 假設我們要將Sally插入到由以下內容組成的客戶關係中：姓名、地址和電話號碼。但我們不知道她的電話
* 帶有部分屬性清單的插入使得插入成為可能：
```mysql
insert into customer values (ʼSallyʼ, ‘123 Sesame St’)
```
>如果我們聲明phone 不為空，則此插入失敗
* Null Values
* 任何涉及 null 的算術表達式的結果都是 null
```mysql
5 + null returns null
```
* 謂詞is null 可用來檢查空值
```mysql
select name
from instructor
where salary is null
```
* 任何涉及 null 的比較結果視為 unknown
```mysql
-- • and :
(true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
-- • or:
(unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
```
* 如果 where 子句謂詞的結果評估為 unknown，則將其視為 false
* Aggregate Functions
>
* Aggregate Functions Examples
* 尋找計算機科學系講師的平均薪資
```mysql
select avg (salary)
from instructor
where dept_name = 'Comp. Sci.';
```
* 尋找 2018 年春季學期教授課程的教師總數
```mysql
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;
```
* 找出'數量'
```mysql
select count (*)
from course;
```
* Grouping
* 求各系講師的平均薪資
```mysql
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
```
* Having Clause
* 找出所有平均薪資大於42000的部門
```mysql
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;
```
* Set Membership
* 尋找 2017 年秋季和 2018 年春季提供的課程
```mysql
select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id in (select course_id
from section
where semester = 'Spring' and year= 2018);
```
* 尋找 2017 年秋季提供但 2018 年春季不提供的課程
```mysql
select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id not in (select course_id
from section
where semester = 'Spring' and year= 2018);
```
* 尋找所有名字既不是“莫札特”也不是愛因斯坦的講師”
```mysql
select distinct name
from instructor
where name not in ('Mozart', 'Einstein')
```
* 尋找已修讀 ID 為 10101 的講師教授的課程部分的（不同）學生總數
```mysql
-- 可以簡單寫 這個例子比較複雜
select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in
(select course_id, sec_id, semester, year
from teaches
where teaches.ID= 10101);
```
* Set Comparison
* “some” Clause
* 找出薪水高於生物系某些（至少一名）講師的姓名
```mysql
select name
from instructor
where salary > some (select salary
from instructor
where dept name = 'Biology');
```
* “all” Clause
* 尋找所有薪資高於生物系所有教師薪資的教師姓名
```mysql
select name
from instructor
where salary > all (select salary
from instructor
where dept name = 'Biology');
```
* Use of “exists” Clause
```mysql
select course_id
from section as S
where semester = 'Fall' and year = 2017 and
exists (select *
from section as T
where semester = 'Spring' and year= 2018
and S.course_id = T.course_id);
```
* Use of “not exists” Clause
```mysql
-- 尋找所有修過生物系提供的所有課程的學生
select distinct S.ID, S.name
from student as S
where not exists ( (select course_id
from course
where dept_name = 'Biology')
except
(select T.course_id
from takes as T
where S.ID = T.ID));
```
* Test for Absence of Duplicate Tuples
```mysql
-- 尋找 2017 年最多提供一次的所有課程
select T.course_id
from course as T
where unique ( select R.course_id
from section as R
where T.course_id= R.course_id
and R.year = 2017);
```
* Subqueries in the From Clause
```mysql
-- 找出平均薪資高於 42,000 美元的部門的平均講師薪資
select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
```
* With Clause
```mysql
-- 尋找預算最高的所有部門
with max_budget (value) as
(select max(budget)
from department)
select department.name
from department, max_budget
where department.budget = max_budget.value;
```
* Complex Queries using With Clause
```mysql
-- 找出所有薪資總額大於平均薪資的部門各部門薪資總額
with dept _total (dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;
```
* Scalar Subquery
```mysql
-- 列出所有部門以及每個部門的講師人數
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name )
as num_instructors
from department;
```
* Modification of the Database
* Deletion
```mysql
--- 刪除所有元組
delete from student
-- 刪除財務部門的所有講師
delete from instructor
where dept_name= 'Finance’;
-- 刪除與位於 Watson 大樓中的部門關聯的那些講師的講師關係中的所有元組
delete from instructor
where dept name in (select dept name
from department
where building = 'Watson');
-- 刪除所有薪資低於教師平均薪資的教師
delete from instructor
where salary < (select avg (salary)
from instructor);
```
* Insertion
```mysql
-- 為講師新增元組
insert into instructor values ('10211', 'Smith', 'Biology', 66000);
-- 在課程中新增元組
insert into course
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
-- 同等水平
insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
-- 學生新增一個新元組，並將 tot_creds 設為 null
insert into student
values ('3003', 'Green', 'Finance', null);
-- 讓每位獲得超過 144 個學分的音樂系學生成為音樂系講師，薪水為 18,000 美元
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and total_cred > 144;
```
* Updates
```mysql
-- 為所有講師加薪5%
update instructor
set salary = salary * 1.05
-- 給收入低於70000的教練加薪5%
update instructor
set salary = salary * 1.05
where salary < 70000;
-- 薪資低於平均的教師加薪5%
update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
from instructor);
-- 薪資超過10萬美元的教師薪資增加3%，其他所有教師薪資增加5%
update instructor
set salary = salary * 1.03
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;
-- 有條件更新的案例聲明
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
```
---
# CH4 Intermediate SQL
>Three types of joins
>* Natural join
>* Inner join
>* Outer join
* Natural Join
>匹配所有公共屬性具有相同值的tuple，並僅保留每個公共列的副本
>EX: 關係代數
>```mysql
>select name, course_id
>from students, takes
>where student.ID = takes.ID;
>```
>可以用下面這個
>```mysql
>select name, course_id
>from students natural join takes;
>```
>* table例子
>
>:point_down::point_down::point_down:
>
>* Dangerous in Natural Join
>有相同名稱 但互相沒關係的屬性
>EX:
>列出學生姓名以及他們所修課程的名稱
>* 正確
>```mysql
>select name, title
>from student natural join takes, course
>where takes.course_id = course.course_id;
>```
>* 錯誤
>忽略了學生在其系所以外的選修課程的all pairs of(student name, course title)
>```mysql
>select name, title
>from student natural join takes natural join course;
>```
* Inner Join
>* 根據ON 明確指定的欄位連接兩個table
>* 結果表將包含兩個表的所有屬性，也包含重複列
>* 僅傳回兩個表中都存在的記錄
>* 可憐 Inner Join沒有一個通用的symbol
>EX:
>```mysql
>course inner join prereq on
>course.course_id = prereq.course_id
>```
>找到course_id相同的tuple
>
* Outer Join
>* 避免資訊遺失
>* 計算連接，將不匹配的tuple加入連接結果
>* 使用null
>Three forms of outer join
>* left outer join
>* right outer join
>* full outer join
>
* Left Outer Join
>
>以左table的course_id為主 保留 (沒配到的填null)
>
* Right Outer Join
>
>以右table的course_id為主 保留 (沒配到的填null)
>
* Full Outer Join
>
>同理 full都保留
>
:::info
:bulb: 總結個Join
要是沒寫outer就是默認為inner
:::
* Views
>有時候不方便給別人看到的資訊
>可以查ID, name, dept_name
>但不能看到salary
>```mysql
>select ID, name, dept_name
>from instructor
>```
>* base table
>真正儲存在資料庫中的關係
* create view
```mysql
create view v as < query expression >
```
* 沒有薪水的教師視圖
>用create
```mysql
create view faculty as
select ID, name, dept_name
from instructor
```
* 根據以上視圖 找到生物系所有導師
```mysql
select name
from faculty
where dept_name = 'Biology’
```
* drop掉
```mysql
drop view faculty
```
* 建立部門名稱及其總薪資的視圖
```mysql
create view departments_total_salary (dept_name, total_salary) as
select dept_name, sum (salary)
from instructor
group by dept_name;
```
* Views's depend
* depend directly
>定義 v1 的表達式中使用 v2 ,則視圖關係 v1 被認為直接依賴視圖關係 v2
* depend on
>v1 直接依賴 v2 或存在從 v1 到 v2 的依賴路徑
* recursive
>v 依賴自身 ,則稱該視圖關係 v 是遞歸
```mysql
create view physics_fall_2017 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = '2017’;
-- (+)
create view physics_fall_2017_watson as
select course_id, room_number
from physics_fall_2017
where building= 'Watson';
-- (=)
create view physics_fall_2017_watson as
select course_id, room_number
from (select course.course_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = '2017')
where building= 'Watson';
```
* Materialized view
>確保如果視圖定義中使用的實際關係發生變化視圖也能保持最新
| | View | Materialized View |
| -------- | -------- | -------- |
| Definition | 由一個或多個base tables或views所形成的虛擬表 | base table的實體副本 |
| Update | 每次使用虛擬表（View）時視圖都會更新 | 物化視圖必須手動或使用觸發器更新 |
| Processing speed | 慢 | 快 |
* transaction交易
* Commit work: 更新將永久保留在資料庫中
* Rollback work: 交易中SQL語句執行的所有更新都將被撤銷
>Atomic transaction原子事務:
>要麼完全執行 要麼Rollback 就好像它從未發生過一樣（如果尚未執行提交工作則Rollback）
* Integrity Constraints完整性約束
>確保資料庫的授權變更不會導致資料一致性遺失(loss of data consistency) 防止資料庫意外損壞
:::info
下面是一些Constraints
:::
* not null
```mysql
create table department
(dept_name varchar(20) not null,
building varchar (15),
budget numeric(12,2) not null)
```
* unique
>候選鍵允許為空
>EX:
```mysql
create table section
(course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
building varchar (15),
room_number varchar (7),
time slot id varchar (4),
unique (course_id, sec_id, semester, year))
```
* check (P)
>確保每個tuple必須滿足(P)
>EX: 確保該學期是秋季、冬季、春季或夏季之一
```mysql
create table section
(course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
building varchar (15),
room_number varchar (7),
time slot id varchar (4),
primary key (course_id, sec_id, semester, year),
check (semester in ('Fall', 'Winter', 'Spring', 'Summer')))
```
* Referential Integrity參照完整性
* foreign key
* 外鍵不一定需要引用另一個表的主鍵，只要引用的屬性符合以下條件即可：
* 它們的資料值必須是唯一的
* 它們的資料類型必須與外鍵的資料類型相符
* Cascading Actions級聯操作(???啥鬼
>也可用這兩個取代cascade
>* set null,
>* set default /<value/>
```mysql
create table course (
(…
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
…)
```
* Referential Integrity Violation違反參照完整性
>EX:
```mysql
create table person (
ID char(10),
name char(40),
mother char(10),
father char(10),
primary key ID,
foreign key father references person,
foreign key mother references person)
```
* Date and Time Types in SQL 時間的TYPE
* date
```mysql
date '2005-7-27'
```
* time
```mysql
time '09:00:30'
time '09:00:30.75'
```
* timestamp
```mysql
timestamp '2005-7-27 09:00:30.75'
```
* interval
```mysql
interval '1' day
```
* Large-Object Types大物件類型
* blob二進位大型物件
* clob字元大物件
```mysql
book_review clob --(10KB)
image blob --(10MB)
movie blob --(2GB)
```
* User-Defined Types使用者定義類型
* create type
```mysql
create table department
(dept_name varchar (20),
building varchar (15),
budget Dollars);
```
* create domain
>域可以具有指定的約束 EX: not null
```mysql
create domain degree_level varchar(10)
constraint degree_level_test
check (value in ('Bachelors', 'Masters', 'Doctorate'));
```
* create index
```mysql
create table student
(ID varchar (5),
name varchar (20) not null,
dept_name varchar (20),
tot_cred numeric (3,0) default 0,
primary key (ID))
--建立index
create index studentID_index on student(ID)
--直接搜尋ID
select *
from student
where ID = '12345'
```
* Authorization授權
* 為使用者指派資料庫部分的多種形式的授權。
* Read - allows reading, but not modification of data.
* Insert - allows insertion of new data, but not modification of existing data.
* Update - allows modification, but not deletion of data.
* Delete - allows deletion of data.
* 修改資料庫模式的授權形式
* Index - allows creation and deletion of indices.
* Resources - allows creation of new relations.
* Alteration - allows addition or deletion of attributes in a relation.
* Drop - allows deletion of relations.
* Authorization Specification in SQL(SQL 中的授權規範)
* grant
>用於授予授權
```mysql
grant select on department to Amit, Satoshi
```
* Privileges in SQL (SQL 中的權限)
* select
>grant users U1, U2, and U3 select authorization on the instructor relation
```mysql
grant select on instructor to U1, U2, U3
```
* insert
* update
* delete
* all privileges: 用作所有允許的權限的縮寫形式
* Revoking Authorization in SQL 撤銷SQL中的授權
* revoke: 用於revoke authorization
```mysql
revoke select on student from U1, U2, U3
```
* Roles
* role: 區分各種使用者的方式
```mysql
create role instructor;
grant instructor to Amit;
-- 為角色授予權限
grant select on takes to instructor;
-- 創建一個新role(teaching_assistant)並受予權限
create role teaching_assistant;
grant teaching_assistant to instructor;
-- 建立角色鏈Chain of roles
create role dean;
grant instructor to dean;
grant dean to Satoshi;
```
* Authorization on Views視圖授權
>EX:
```mysql
create view geo_instructor as
(select *
from instructor
where dept_name = 'Geology');
```
* Other Authorization Features其他授權功能
* references: 建立外鍵的權限
```mysql
grant reference (dept_name) on department to Mariano;
-- 如果我們希望允許Amit在部門上選擇特權並允許他將此特權授予其他人
grant select on department to Amit with grant option;
```
* revoke: revoke語句可以指定restrict以防止級聯撤銷
```mysql
revoke select on department from Amit, Satoshi restrict;
-- 可以使用關鍵字cascade代替restrict
revoke select on department from Amit, Satoshi cascade;
```
# CH5 Advanced SQL
* JDBC (Java的API)
>步驟:
>開啟與資料庫的連線（選擇要使用的資料庫）
>建立「語句」對象
>使用語句物件執行查詢以傳送查詢並取得結果
>處理錯誤的異常機制
>EX:
```java
public static void JDBCexample(String dbid, String userid, String passwd)
{
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd);
Statement stmt = conn.createStatement();
… Do Actual Work ….
stmt.close();
conn.close();
}
catch (SQLException sqle) {
System.out.println("SQLException : " + sqle);
}
}
```
>Update:
```java
try {
stmt.executeUpdate(
"insert into instructor values('77987', 'Kim', 'Physics', 98000)");
} catch (SQLException sqle)
{
System.out.println("Could not insert tuple. " + sqle);
}
```
>Execute query and fetch and print results
```java
ResultSet rs = stmt.executeQuery(
"select dept_name, avg (salary) from instructor group by dept_name");
while (rs.next()) {
System.out.println(rs.getString("dept_name") + " " +rs.getFloat(2));
}
```
>Getting result fields:
```java
rs.getString(“dept_name”)
//如果 dept_name 是選擇結果的第一個參數，則等效
rs.getString(1)
```
>Dealing with Null values
```java
int a = rs.getInt(“a”);
if (rs.wasNull()) Systems.out.println(“Got null value”);
```
* Prepared Statement
>不要用串接的字串
>就是"123"+"456"+"789"之類的
>並且有資安問題
>Always use prepared statements, with user inputs as parameters(始終使用準備好的語句，並將使用者輸入作為參數)
* Metadata Features
>使用executeQuery 方法提交查詢時，查詢結果包含在ResultSet 物件中
>EX:
>執行查詢以取得 ResultSet rs 後：
```java
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.println(rsmd.getColumnName(i));
System.out.println(rsmd.getColumnTypeName(i));
}
```
>用ResultSet metadata :point_up:
>則即使我們不知道結果的模式，我們也可以執行查詢
* List all attributes
```java
DatabaseMetaData dmd = conn.getMetaData();
// Arguments to getColumns: Catalog, Schema-pattern, Table-pattern,
// and Column-Pattern
// Returns: One row for each column; row has a number of attributes
// such as COLUMN_NAME, TYPE_NAME
// The value null indicates all catalogs/schemas.
// The value “” indicates current catalog/schema
// The value “%” has the same meaning as SQL like clause
ResultSet rs = dmd.getColumns(null, "univdb", "department", "%");
while( rs.next() ) {
System.out.println(rs.getString("COLUMN_NAME"),
rs.getString("TYPE_NAME");
}
```
* List all tables
```java
DatabaseMetaData dmd = conn.getMetaData();
// Arguments to getTables: Catalog, Schema-pattern, Table-pattern,
// and Table-Type
// Returns: One row for each table; row has a number of attributes
// such as TABLE_NAME, TABLE_CAT, TABLE_TYPE, ..
// The value null indicates all Catalogs/Schemas.
// The value “” indicates current catalog/schema
// The value “%” has the same meaning as SQL like clause
// The last attribute is an array of types of tables to return.
// TABLE means only regular tables
ResultSet rs = dmd.getTables (“”, "", “%", new String[] {“TABLES”});
while( rs.next()) {
System.out.println(rs.getString(“TABLE_NAME“));
}
```
* Find primary keys
```java
DatabaseMetaData dmd = conn.getMetaData();
// Arguments below are: Catalog, Schema, and Table
// The value “” for Catalog/Schema indicates current catalog/schema
// The value null indicates all catalogs/schemas
ResultSet rs = dmd.getPrimaryKeys(“”, “”, tableName);
while(rs.next()){
// KEY_SEQ indicates the position of the attribute in
// the primary key, which is required if a primary key has multiple
// attributes
System.out.println(rs.getString(“KEY_SEQ”),
rs.getString("COLUMN_NAME");
}
```
# CH6 Database Design Using the E-R Model
* Outline of the ER Model
* Representing Entity Sets in ER Diagram
* 矩形代表實體集
* 實體矩形內所列的屬性
* 底線表示主鍵屬性
* Representing Relationship Sets via ER Diagrams
* 菱形代表關係集
* 具有屬性的關係集
* Degree of a Relationship Set
* E-R Diagram with a Ternary Relationship
* Mapping Cardinality Constraints
* One-to-On
* One-to-Many
* Many-to-One
* Many-to-Many
* Total participation (indicated by double line):
* Redundant Attributes
* 下面學生中的屬性 dept_name 複製了目前的訊息在關係中，因此是多餘的
* 需要刪除
* E-R Diagram for a University Enterprise
* Reduction to Relation Schemas
* Representing Entity Sets
* A strong entity set reduces to a schema with the same attributes
```mysql
course (course_id, title, credits)
```
* A weak entity set becomes a table
```mysql
section ( course_id, sec_id, semester, year )
```
* Completeness constraint
>指定較高層級實體集中的實體是否必須屬於泛化中的至少一個較低層級實體集
• total: an entity must belong to one of the lower-level entity sets
• partial: an entity need not belong to one of the lower-level entity sets
* 
* 
* UML
* 
# CH7 Normalization
就不寫了...
因為我沒有讀這章XDDD
Sign in with Wallet
Connect another wallet