---
# System prepended metadata

title: 資料庫系統 Database Management System
tags: [大三筆記, "資料庫系統\_"]

---

---
tags: 資料庫系統 大三筆記
---

# 資料庫系統 Database Management System

:::info
+ 課程名稱：資料庫系統 Database Management System
+ 授課老師：[name=張雅惠 Ya-Hui Chang ]
+ 開課學期：1121
:::

[TOC]

## CH2

### 名詞

* domain
欄位可能的值，如性別欄位可能的值為{"男", "女"}

* atomic
加設這domain為atomic，那這欄位的值**不能再被分割**
    * multivalued(不是atomic)
    value為一個集合 ex:{{1, 2}, {2, 3}}
    * composite(不是atmoic)
    value為一個tuple ex:{("02", "24622192")}

* relation schema
**R = (A1, A2, …, An )**
ex : instructor  = (ID,  name, dept_name, salary)

* relation
**D~1~ x D~2~ ....D~n~**
ex : D~1~ = {a, b, c}, D~2~ = {1, 2}, D~1~XD~2~ = {(a, 1), (a, 2), (b, 1), (b, 2), (c, 1), (c, 2)}
r =  {(a, 1), (a, 2), (b, 2)} 

* relation instance
**The current values of a relation**
**簡單來說就是個表格**
    * attribute
    直行
    * tuple
    橫列值共同形成的一個tuple
    ex : (a, 1)

    | A~1~ | A~2~ |
    | - | - |
    | a | 1 |
    | b | 1 |
    | b | 2 |
    
* superkey(SK)
可以正確識別出**唯一**行列的tuple

    | ID | Value |
    | - | - |
    | 1 | a |
    | 2 | a |
    | 3 | b |

    ID為superkey

* candidate key(CK)
**所有**可當作superkey的tuple

* primary key(PK)
任一candidate key都可當作primary key

* Foreign key(FK)
在一個relation的值必須出現在另外一個relation且作為**另外一個relation的primary key**
    * Referencing relation
    relation的**value**做為另一個relation的**PK**
    * Referenced relation
    relation的**PK**是另外一個
relation的**value**

    **Schema Diagram 的箭頭方向是從vlaue指向另一個relation的PK**

### Select Operation
instructor (ID, name, dept_name, salary)
teaches (ID, course_id, sec_id, semester, year)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
student (ID, name, dept_name, tot_cred)


* $\sigma$
select：從relation選出某一直行的特定值
*多個限制用and或or做連接*
ex : $\sigma_{dept\_name="Physics" \land salary>90000}$
* $\Pi$
選出特定幾個attribute
ex : $\Pi_{name}(\sigma_{dept\_name = "Physics"}(instructor))$
* $\cup(取聯集)$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cup$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
![](https://hackmd.io/_uploads/BypKrX5Ga.png)

* $-$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))-$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
![](https://hackmd.io/_uploads/HkBCrXcGp.png)

* $\times$
將兩個relation的tuple互相相關聯
**可能會關聯出不想要看到的結果**，通常會與$\sigma一起使用$
ex:$\sigma_{instructor.id=teaches.id}(instructor \times teaches)$
(將instuctor與teaches關聯，並選出instructor.id與teaches.id相同的tuple)
* $\bowtie$
Natural join:要求兩個表格**共同屬性其值相等的資料列**才能配對輸出
$instructor\bowtie_{instructor.id = teaches.id}teaches =$ $\sigma_{instructor.id=teaches.id}(instructor \times teaches)$
* $\cap(取交集)$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cap$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
![](https://hackmd.io/_uploads/SyPUr7cfa.png)

## CH3
instructor (ID, name, dept_name, salary)
teaches (ID, course_id, sec_id, semester, year)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
student (ID, name, dept_name, tot_cred)

### Domain Types

* char(n)
固定長度字串
* varchar(n)
可變長度字串
* int
* smallint
AKA short(C++)
* numeric(p,d)
浮點數，總共p位數，小數d位
ex:numeric(3,1)->xx.x
* real, double precision
AKA double(C++)
* float(n)
浮點數，小數點精確後n位

### Basic Edit

#### Create Table

```sql=   
create table r 
    (A1 D1, A2 D2, ..., An Dn,
    (integrity-constraint1),
    ...,
    (integrity-constraintk))
```
integrity-constraint：PK與FK關聯性約束條件

```sql=
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);
```

#### Insert

```sql=
insert into instructor 
    values (‘10211’, ‘Smith’, ‘Biology’, 66000); 
    --字串加單引號
insert into instructor 
    values (‘10211’, null, ’Biology’, 66000); 
    --出現錯誤訊息(name not null)
```

#### Delete
Remove **all** tuple
```sql=
delete from student  
```

#### Drop Table
**完整**刪掉整個relation
```sql=
drop table r
```

#### Alter
```sql=
alter table r add A D
```
在r新增Domain type為D名稱為A的attribute
若已存在表格中的tuple其新增的attribute會填入null
```sql=
alter table r drop A     
```
在r刪除名為A的attribute


### Query


#### Basic

```sql=
select A1, A2, ..., An --選擇的attribute
from r1, r2, ..., rm --查詢的relation
where P --條件
```
ex :
```sql=
select name
from instructor
--從instructor查詢name的attribute
```
```sql=
select distinct dept_name
from instructor
--從instructor查詢dept_name的attribute且消除重複項
```
```sql=
select all dept_name
from instructor
--從instructor查詢dept_name的attribute(保留重複)
```
```sql=
select *
from instructor
--從instructor查詢所有的attribute
```
```sql=
select ID, name, salary/12
from instructor
--從instructor查詢ID、name、salary三個attribute
--且salary的值/12
```
#### Where
ex:
$\Pi_{name}(\sigma_{dept\_name='Comp.Sci.' \land slalry > 70000}(instuctor))$

```sql=
select name
from instructor
where dept_name = 'Comp. Sci.'  
        and salary > 70000
```
$\Pi_{name}(\sigma_{dept\_name='Comp.Sci.' \land 90000<=slalry \land salary<=100000}(instuctor))$
```sql=
select name
from instructor
where salary between 90000 and 100000
```
$\Pi_{name, course\_id}(\sigma_{dept\_name='Biology' \land instructor.ID=teaches.ID}(instructor \times teaches)$
```sql=
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
```
#### Cross
$\Pi_*(instructor \times teaches)$
```sql=
select *
from instructor, teaches
```
搭配where
$instructor\bowtie_{instructor.id = teaches.id \land instrcuctor.dept\_name='Art'}teaches =$ $\sigma_{instructor.id=teaches.id\land instrcuctor.dept\_name='Art'}(instructor \times teaches)$
```sql=
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID
        and instructor.dept_name = 'Art'
```

#### Alias
```sql=
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.'
```

#### String
* %
%前或後的字串為任意
    * 'abc%' : abc開頭
    * '%abc' : abc結尾
    * '%abc%' : 含有abc子字串
* _
_ 可做為任意字元
ex : a_c : 匹配任何第一個為a第三個為c的字串

* 跳脫字元 \\% \\_

#### Order
對查詢結果作排序
* desc 降冪
* asc 升冪
```sql=
select *
from   instructor
order by  salary desc, name asc
--從instructor查詢所有attribute
--且依照salary降冪 name升冪排序
```

#### Set 
* union
    $\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cup$
    $\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
    ```sql=
    (select course_id  
     from section 
     where semester = 'Fall' and year = 2017)
    union
    (select course_id  
     from section 
     where semester = 'Spring' and year = 2018)
    ```
* except
    $\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))-$
    $\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
    ```sql=
    (select course_id  
     from section 
     where semester = 'Fall' and year = 2017)
     except
    (select course_id  
     from section 
     where semester = 'Spring' and year = 2018)
    ```

* intersect
    $\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cap$
    $\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
    ```sql=
    (select course_id  
     from section 
     where semester = 'Fall' and year = 2017)
    intersect
    (select course_id  
     from section 
     where semester = 'Spring' and year = 2018)
    ```
#### Aggregate Functions
* avg:    average value 	  平均
* min:    minimum value 	  極小
* max:    maximum value 	  極大
* sum:    sum of values 	  總和
* count:  number of values  個數

##### Basic
ex : 
```sql=
select avg (salary) as avg_salary
from instructor
where dept_name= 'Comp. Sci.'
--從instructor裡dept_name='Comp. Sci.'的salary取平均
```
```sql=
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;
/*
從teaches裡smester='Spring' and year=2018
取得不重複ID的數量
*/
```
```sql=
select count (*) from course;
-- 取得course所有tuple數量
```

##### Group
```sql=
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
/*
從instuctor以depy_name為一組對salary做平均
後取得dept_name和重新命名salary的平均為avg_salary
*/
```
```sql=
select dept_name, ID, avg (salary)
from instructor
group by dept_name;
--mulii-value occur(多個ID)
```

##### having
```sql=
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;
/*
從instuctor以depy_name為一組對salary做平均
後取得dept_name和重新命名salary的平均為avg_salary
且salary的平均大於42000
*/
```
**aggregate function 不能直接使用在where clause裡.** 
放在having和select clause裡的aggregate function 可不同

#### Set Membership

```sql=
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);
/*
從section中取得course_id
且section='Fall' and year=2017 and course_id不在
(從section中取得course_id
且section='Soring' and year=2018)的結果裡
*/
```

#### Set Comparison
```sql=
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary 
    and S.dept_name = 'Biology';
/*
找出salary大於dept_name='Biology'的name
*/
```
* Some
有任一達成
```sql=
select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name = 'Biology');
/*
找出salary大於任一dept_name='Biology'的name
*/
```
* all
達成所有
```sql=
select name
from instructor
where salary > all (select salary
                     from instructor
                     where dept_name = 'Biology');
/*
找出salary大於所有dept_name='Biology'的name
*/
```

* exist
```sql=
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);
/*
找出semester='Fall' and year=2017
而且要有semester='Fpring' and S.course_id=T.course_id的course_id)
*/
```

* unique
```sql=
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);
/*
找出course_id其course_id在year=2017只出現一次
*/
```
#### Subqueries
從找出來的relation找東西
```sql=
select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary
	  from instructor
	  group by dept_name)
      
where avg_salary > 42000;
/*
從instructor依照dept_name分組
並得到dept_name和avg(salary) 並將avg(salary)改成avg_salary
再從中找出所有avg_salary>42000的tuple
*/
```
```sql=
select dept_name, avg_salary
from (select dept_name, avg (salary)
      from instructor
      group by dept_name) 
    as dept_avg (dept_name, avg_salary)
where avg_salary > 42000; 
/*
從instructor依照dept_name分組
並得到dept_name和avg(salary) 
建立成名為dept_avg的relation
再從中找出所有avg_salary>42000的tuple
*/
```

#### With
```sql=
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;
/*
創建一個dept_total(dept_name, value)
從instructor對dept_name分組取總
創建一個dept_total_avg(alue)
從dept_total對dept_name取平均
再從dept_total, dept_total_avg找到
dept_total.value > dept_total_avg.value;
->找到各dept_name平均大於所有dept_name全部平均
*/
```
##### Scalar subquery
```sql=
select dept_name,
( select count(*) 
 from instructor
 where department.dept_name = instructor.dept_name)
as num_instructors
from department;
--相同結果
select department.dept_name,count(*)
from instructor, department
where department.dept_name = instructor.dept_name
group by(department.dept_name)
```

### Advanced Edit

#### Insert
```sql=
insert into instructor
select ID, name, dept_name, 18000
from   student
where dept_name = 'Music' and total_cred > 144;
--insert 2-4行找到的tuple
```

#### Delete
```sql=
delete from instructor
where dept_name='Finance'
--刪除dept_name='Finance'的tuple
```
```sql=
delete from instructor
where dept_name in (select dept_name
                    from department
                    where building = 'Watson');
--刪除instructor的tuple其dept_name有在department
--裡building='Watson'的dept_name中
```
```sql=
delete from instructor
where salary < (select avg (salary) 
                from instructor);
--刪除過程avg(salary)會改變可能會爆炸
```

#### update
```sql=
update instructor
set salary = salary * 1.05
where salary <  (select avg (salary)
                 from instructor);
-- 小於平均的都x1.05
```
* Case Statement
```sql=
update instructor
set salary = case
                when salary <= 100000 then salary * 1.05
                else salary * 1.03
             end
-- 如果salary <= 100000 x1.05 其餘x1.03
```


### Other

#### null
```sql=
select name
from instructor
where salary is null
-- 查詢salary為null的
-- where salary is not null
```
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
```


