--- # System prepended metadata title: Database management system 資料庫管理系統 tags: [DBMS, ' Database management system 資料庫管理系統'] --- --- tags: DBMS, Database management system 資料庫管理系統 --- # Database management system 資料庫管理系統 ## Course Website http://dblab.csie.ncku.edu.tw/course/2021u/ ## Database characteristics 1. **Self-contained nature** [Meta data](https://zh.wikipedia.org/wiki/%E5%85%83%E6%95%B0%E6%8D%AE) 自己包含自己，意思為 database 都會有個 catalog (目錄)，裡面包含 meta data。meta data 是描述此資料庫的資料， e.g `sex` 就是描述這列 data 是什麼。 2. **Insultaion between program and data** Program - data independence，DBMS 作為中繼 API ，管理程式對於資料的存取，限制資料操作程式不可更動資料庫內的資料。 3. **Data Abstraction** 將 database 作概念化的描述，不去特別寫清資料間的關係。 4. **Multiple view of the data** 讓不同使用者能夠有不同的角度去檢視 database。 ## Database concept and architecture 1. **Data Model** [Data Model](https://www.tutorialspoint.com/dbms/dbms_data_models.htm) 描述 database 裡 data 彼此之間的關係以及資料處理的流程順序。data model 有不同型態，代表不同的 database 理解。 > 資料模型 (Data Model) 是一種抽象化的資料表示法，透過對資料的呈現來瞭解現實世界。 資料模型使用一組整合的邏輯觀念來描述資料，包括資料結構及其操作方法。它可以幫助我們清楚的看出資料的抽象概念 [Types of Data model](https://i.pinimg.com/originals/86/7f/a7/867fa7370c3a0d9508db123a45b2c5b2.png) ![](https://i.imgur.com/4i9b1Pc.png) * Conceptual high-level 提供概念性的理解，也被稱為 `Entity-Based` 或 `Object-Based`(Stud details) * Physical low-level 最底層的描述，是電腦去理解的描述。(INT, CHAR) * Implementation 中間層，讓電腦能夠理解使用者的理解。(折衷，First name) 2. **Database schema** 資料庫架構，描述如何存放資料以及資料之間的關係，可以想像成 database 的骨架。也被稱為 Intension (內涵) [What are Database schema](https://www.educative.io/blog/what-are-database-schemas-examples) >A database schema is an abstract design that represents the storage of your data in a database. It describes both the organization of data and the relationships between tables in a given database. [schema](http://www.dspmuranchi.ac.in/pdf/Blog/Instance%20and%20Schema.pdf) ![](https://i.imgur.com/tsz7HEJ.png) 3. **Database Instance** [Instance = 例項，例子](https://dictionary.cambridge.org/zht/%E8%A9%9E%E5%85%B8/%E8%8B%B1%E8%AA%9E-%E6%BC%A2%E8%AA%9E-%E7%B9%81%E9%AB%94/instance) database 在任一瞬間的資料儲存狀態，也可以視為 database status。也被稱為 Extension (外顯) [What is database instance](https://www.quora.com/What-is-a-database-instance-DB-instance) > A database instance is a state of operational database with data at any given time. 4. **Schema Architecture** [Schema](https://www.guru99.com/dbms-schemas.html) ![](https://i.imgur.com/DmDSsqX.png) * Internal Schema * 描述資料儲存結構以及存取路徑，多數使用在`Physical` data model。也就是描述 INT 或 CHAR 等資訊要如何儲存以及他們之間的關係。 * lowest level of data abstraction * The internal view tells us what data is stored in the database and how * views a physical device as a collection of physical pages * Conceptual Schema * 概念性的描述**整個** Database * 描述整個 database 的概念性架構，多數使用在 `Implementation` data model or `Conceptual` model。 * focuses on describing data types, entities, relationships, etc. * Defines all database entities, their attributes, and their relationships * External Schema * 概念性的描述不同面向的 Database 。例如，學生在使用 moodle 時就應該看不到其他學生的成績 * 根據不同使用者呈現不同的 database 架構，多數使用在 `Implementation` data model。 * External schema level is nearest to the user * The external schema describes the segment of the database which is needed for a certain user group and hides the remaining details from the database from the specific user group 5. **Data Independence** * Logical Data Independence 改變 Conceptual Schema 而不需改變 External Schema * Physical Data Independence 改變 Internal Schema 而不需改變 Conceptual Schema 使用者可以改變裡面的 Concept schema 的資料關係而自己看向 database 的外觀不會被改變。而 Concept schema 可以更改 Internal 的資料而不會被改變。 * When a schema at **lower level** is changed, only the mapping btw it and higher level needs to change. The **higher level** schemas are unchanged. 6. **DBMS Languages** * Data Definition Language (DDL) * Can be used to define internal and external schemas * Data Manipulation Language (DML) ## ER Model [Ref_1](https://medium.com/pierceshih/%E7%AD%86%E8%A8%98-%E5%AF%A6%E9%AB%94%E9%97%9C%E8%81%AF%E5%9C%96-87c3ecbc5ff0) [Ref_2](http://cc.cust.edu.tw/~ccchen/doc/db_03.pdf) [Weak_entity](https://www.geeksforgeeks.org/difference-between-strong-and-weak-entity/) ER model based on * Entities and their Attributes * Relationships among entities 1. **Entities and Attributes** * Entities 某種在 database 裡被表示的物件。e.g 學生，公司... * Attributes Entities 所擁有的特徵。e.g 學號、性別、資產總額... 特徵有很多種。 * Simple 該特徵只會有一個對應的值，不管有何種可能。e.g 性別，政黨，支持球隊。 * Composite 該特徵需要有多於一個值來組合表示。e.g 地址(國家, 城市, 街道, 號碼)，每個值代表意義都不同。 * Multi-valued 該特徵會有多於一個值。e.g 電話號碼，擁有的車牌號碼(多於一輛)，每個值都是相同類別。 **Difference Between Mulit vs Compose Attr** | Multi | Compose | | -------- | -------- | | Has Multiple value for same attr|Multiple attr's each has single value | | Phone Number | Address | | 04-23XXXX | Road - DongAn | | 04-22XXXX | City - Tainan | * Entity Type 擁有相同 Attribute 的 Enities 可以集合起來稱為一種 Entity type。Entity Type 可以擁有**多於一個**的 Key Attribute。 * Key Attribute 可以使用該 Attribute 找出唯一的 Entity e.g `Name`。 2. **Relationships** 描述 Entities 之間的互動關係。 * Entities 可以不同也可以相同。e.g `Employees` WORK_IN `Department`，`Employees` SUPERVISE `Employees`。 * Relationship 也可以擁有 Attribute。e.g. VOTE_ON * Cardinality ratio (of a binary relationship): * 1:1 * 左側實體最多只能與右側實體發生一對一的關係 * e.g. MAN VS DEP 每個 MANAGER 只能 MANAGE 一個 DEP，每個 DEP 也只會被一個 MANAGER MANAGE。 * 1:N * 左側實體能夠與多個右側實體發生關係，但右側只能跟一個左側實體發生關係。 * e.g. EMP VS DEP 一個員工只能 WORK_IN 一個 DEP，但每個 DEP 能有多個員工 WORK_IN * N:1 * 右側實體能夠與多個左側實體發生關係，但左側只能跟一個右側實體發生關係。 * 同上 * M:N * 左右實體都能與對方發生多對多的關係 * e.g. EMP VS PROJECT 每個員工能WORK_ON多個 PROJECT 每個 PROJECT 都有多個員工 WORK_ON 3. **Relationship and cardinality** [各種實體關係與資料表](http://cc.cust.edu.tw/~ccchen/doc/db_03.pdf) ![](https://i.imgur.com/VGq7qNq.png) * Participation constraint 參與限制。 * Total : Entity Set 內的每一個 Entity 都一定存在有這樣的關係 * Partial 3. **Weak Entity** [Weak_Entity](https://www.geeksforgeeks.org/weak-entity-set-in-er-diagrams/) 沒有 Key Attribute 的 Entity，Weak Entity 會跟 Owner Entity 有 identifying relationship。簡單來說， Weak Entity 是依附在 Strong Entity 底下的實體，如果 Strong Entity 不存在，那他也將不復存在。 ![](https://i.imgur.com/6T2gaVW.png) ## Relational Model > The relational model's central idea is to describe a database as a collection of predicates over a finite set of predicate variables, describing constraints on the possible values and combinations of values. * Relation * A table of values. Each column header called **attribute**. Each row called **tuple** * Domain * Set of atomic values(單值或空值) * Degree * Numbers of attributes * Cardinality * Numbers of rows ![](https://i.imgur.com/l5k2hNU.png) Tuple 以及 Attribute 之間的關係會寫成 $R(A_{1}, A_{2}, A_{3} ... , A_{n})$ e.g $STUDENT(Name, SSN, BirthDate, Sex)$ 在 relation model 中是將資料視為 Attribute 個數維度中的一個點。n-tuple 代表該關聯有 n 個 attribute Relational Model 就是在表示值之間的邏輯關係。Table 裡的值必須為 atomic (單值)或 NULL(空值)，不可為多值。 ### Relational Integrity Constraints 1. **Key Constraints** * Super Key * $No\ two\ tuples\ in\ any\ valid\ relation\ instance\ r(R)\ will\ have\ same\ value\ of\ Super\ Key$ * 一個 Attribute 的集合能夠唯一的描述該關聯。e.g {Student_id}，{Student_id, Student_no}，{Student_id, Student_score}...。 * Key * Minial superkey，最小的 superkey。 * 一個 table 可以有多個 key。 * Primary Key 從 Key 裡面挑出來代表該關聯的 Attribute，在表示的時候需要加註底線。 * Alternate Key Key 中沒被挑選成 Primary Key 剩下的 Keys 2. **Entity Integrity** 每個 tuple 的 PK 不得為 NULL，因為 PK 是用來辨識關聯的，若 PK 為 NULL，則找不到該關聯。(ER Model 可以用 Weak Entity Dependent 的 Strong entity 找到) 3. **Referential Integrity** 限制參考關聯以及被參考關聯之間的關係。 ```graphviz digraph structs { node[shape=record] {rank=same; structa} structptr [label=" Reference_t1|FNAME|SSN|DNO(FK)"]; structa [label=" Referenced_t2|DNAME|DNUMBER(PK)|MGRSSN"]; structptr:ref -> structa:refed } ``` 參考者會有 $Foreign\ Key\ (FK)$，是被參考者的 $Primary\ Key\ (PK)$。 參考者需要有被參考者的 $Primary Key$，而被參考者的 $Primary\ Key$ 放在參考者的 table 裡就被稱為 $Foreign\ Key$ t1[FK] = t2[PK] FK 有 PK 所有的值(包含於) ### Relational Algebra [CH_關聯代數](https://www.mis.nsysu.edu.tw/~syhwang/Courses/DB/manuscript.pdf) [CH_關聯代數_2](http://cc.cust.edu.tw/~ccchen/doc/db_05.pdf) * Operations to manipulate relations * Used to specify retrieval requests * Query result is in the form of a relation * 對關聯做操作，而回傳值也是某種關聯 #### Select $\sigma$ * Select **rows** that satisfy a certain condition * 找出符合特定條件的 row * e.g. EMP_AGE_20 = $\sigma_{age>=20}(ALL\_EMPS)$ * ![](https://i.imgur.com/hMncmOF.png) #### Project $\pi$ * Keep certain attributes (columns) from a relation * 找出特定的 column * e.g. EMP_NAME = $\pi FNAME$ , $LNAME(ALL\_EMPS)$ * ![](https://i.imgur.com/nXhsErk.png) #### Cartesian $X$ * If $R_{1}$ has $n_{1}$ tuples and $R_{2}$ has $n_{2}$ tuples, then $R = R_{1} * R_{2}$ will have $n_{1}$ * $n_{2}$ tuples * 每個 Row 都接下另一個 table 的全部 Row * ![](https://i.imgur.com/gYj3Hzx.png) #### Join $\bowtie$ * 根據條件合併，而且左右兩個 table 都必須要有相同值 * ![](https://i.imgur.com/iwFsoD4.png) #### Divide $\div$ * 找出被除的 table 中的 tuple 必須含有除的 table 的所有 tuple * ![](https://i.imgur.com/4uxpnuJ.png) (a) $SSNS \leftarrow SSN\_PNOS \div SMITH\_PNOS$ (b) $T \leftarrow R \div S$ ![](https://i.imgur.com/5D0disu.png) ## SQL ### SELECT-FROM-WHERE * 找尋 table 中符合 where 條件的位置 * [SQL WHERE](https://www.fooish.com/sql/where.html) ```sql= SELECT * FROM enteripse WHERE stock_symbol = 2330; ``` ### DELETE * 刪除 table 中的資料 * [SQL DELETE](https://www.fooish.com/sql/delete-from.html) ```sql= DELETE * FROM stock WHERE stock_symbol = 6452; ``` ### INSERT * 新增 table 中的資料 * [SQL INSERT](https://www.fooish.com/sql/insert-into.html) ```sql= INSERT INTO stock(stock_symbol, 開, 高, 低, 收) VALUES(2330, 600, 679, 550, 600); ``` ### UPDATE * 修改 table 中的資料 * [SQL UPDATE](https://www.fooish.com/sql/update.html) ```sql= UPDATE stock SET 收盤價 = 1025 WHERE stock_symbol = 2454; ``` ### IN * 在 table 透過符合一或數個離散的值限制來查詢資料 * [SQL IN](https://www.fooish.com/sql/in.html) ```sql= SELECT * FROM stock WHERE stock_symbol IN (2330, 2303, 2454); ``` ### NOT IN * 在 table 透過**不**符合一或數個離散的值限制來查詢資料 ### EXISTS * EXISTS 運算子可以連接子查詢，用來判斷子查詢是否有返回的結果，如果有結果返回則為真、否則為假。若 EXISTS 為真，就會繼續執行外查詢中的 SQL * [SQL EXISTS](https://www.fooish.com/sql/exists.html) ```sql= SELECT * FROM enterprise WHERE EXISTS (SELECT stock_symbol FROM stock WHERE stock.stock_symbol = enterprise.stock_symbol); ``` ### NOT EXISTS * 若 EXISTS 為假，則不進行查詢 ### COUNT * COUNT() 函數用來計算符合查詢條件的欄位紀錄總共有幾筆。 * [SQL COUNT()](https://www.fooish.com/sql/count-function.html) ```sql= SELECT COUNT(trading day) FROM stock WHERE stock_symbol = 2330; ``` ### AVG * AVG() 函數用來計算一數值欄位的平均值。 * [SQL AVG()](https://www.fooish.com/sql/avg-function.html) ```sql= SELECT AVG(close_price) FROM stock WHERE stock_symbol = 2330; ``` ### MAX * MAX() 函數用來取得特定欄位中的最大紀錄值。 * [SQL MAX()](https://www.fooish.com/sql/max-function.html) ```sql= SELECT MAX(high_price) FROM stock WHERE stock_symbol = 2330; ``` ### MIN * MIN() 函數用來取得特定欄位中的最大紀錄值。 * [SQL MIN()](https://www.fooish.com/sql/min-function.html) ```sql= SELECT MIN(low_price) FROM stock WHERE stock_symbol = 2330; ``` ### SUM * SUM() 函數用來計算一數值欄位的總合。 * [SQL SUM()](https://www.fooish.com/sql/sum-function.html) ```sql= SELECT SUM(trading_share) FROM long_stock WHERE stock_symbol = 2454 AND enterprise_symbol = 22099131; ``` ### GROUP BY * GROUP BY 敘述句搭配聚合函數 (aggregation function) 使用，是用來將查詢結果中特定欄位值相同的資料分為若干個群組，而每一個群組都會傳回一個資料列。 * [多個 Attr Group](https://www.796t.com/article.php?id=187878)) * [SQL GROUP BY](https://www.fooish.com/sql/group-by.html) ```sql= SELECT enterprise_symbol, SUM(trading_share) FROM long_stock GROUP BY enterprise_symbol; /*各企業的交易股數*/ ``` ### HAVING * HAVING 子句是用來取代 WHERE 搭配聚合函數 (aggregate function) 進行條件查詢，因為 WHERE 不能與聚合函數一起使用。 * [SQL HAVING](https://www.fooish.com/sql/having.html) ```sql= SELECT enterprise_symbol, SUM(trading_share) FROM long_stock GROUP BY enterprise_symbol; /*各企業的交易股數*/ HAVING SUM(trading_share)<1,000,000 /*找 trading share 小於 1,000,000 的 企業 */ ``` ### DISTINCT * 尋找不重複的資料 ### UNION、MINUS、INTERSECT * 對兩組資料作聯集、差集以及交集 * 其中這兩組資料要有相同的 Attr ### LIKE * 比較特定字串 * %AAA%，尋找資料中含有 AAA 的 ### ARITHMETIC OPERATION * 只有對資料的 copy 進行操作，沒有更改到實際的值 * 要使用 UPDATE 才能更改實際值 SELECT, FROM are mandatory 其餘視個別狀況使用。 ```sql= SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] ``` ## View [SQL view](https://www.fooish.com/sql/view.html) * View 是一種虛擬結構，是將 database 中的某些資料取出來並合成 table。 * View 不是 base relation 的 table * 可以在 view 上進行查詢，就像上述語法一樣，能夠像對 database table 一樣執行動作。 * UPDATE view 有許多限制 * view 很可能是由許多 database 裡面的 table 組成的，所以如果對 view 裡面的 attr 作更新可能會產生 ambiguous 的操作 * **含 2 個以上的 table join** 還有**聚合函數**的 view 不允許更新 * View 必須由**一個 table 中**取出並且**有 key**才能進行更新 * 因此 view 應該以檢視為主，減少產生更新機會的可能 ## ER-to-Relational mapping * Strong entity 1. 創造 table 2. 將 entity attr 變成 table attr 3. 將 key attr 變成 primary key of table * Weak entity 1. 創造 Relation R 2. 將 weak attr 放入 R table 3. 將 owner attr primary 放入 R table 4. 將 owner 還有 weak entity 的 key 組合起來成為該 table 的 primary key * Binary relation 1:1 1. 選其中一個 entity 的 primary key 做為另一個 entity 的 foreign key 然後存放進該 entity 的 table 2. 通常會選擇完全參與該關係的 entity 去獲得另一個關係中的 primary key ，因為這樣保證這 foreign key 這格不為 NULL * Binary relation 1:N 1. N 參與的該 entity 去存放關係中另一個 entity 的 primary key 作為 foreign key 2. e.g. (EMP)A->(DNO)1, (EMP)B->(DNO)1.... * Binary relation M:N 1. 必須要產生一個新的 table 去存放彼此的 primary key ，這樣在關係中兩個 entity 透過該 table 去查詢彼此 ## FD & Normalization [DB Functional Dependencies @P28](https://mail.tku.edu.tw/inhon/Database2014.pdf) ![](https://i.imgur.com/XwBZuvE.png) ### Functional dependency * X Y 皆為 table 中的一個 attr * iff 每個 X 都有唯一對應的 Y 值 * Y 是由 X 唯一決定 ![](https://i.imgur.com/uUqFxgS.png) ### Normalization * 越高 level 的正則化同時也會符合低 level 的正則化規則 * 1NF * [1NF](http://database.klab.tw/teach/t5_1.php) * iff 每個屬性值皆為單值 (Atomic value) * 若有超過一個則要取出另組 table 或 tuple ![](https://i.imgur.com/xmviHZH.png) * 2NF * [2NF](http://database.klab.tw/teach/t5_2.php) * iff 所有非鍵值 attr 只能被 key attr 所決定 (Full FD) * 若有例外，則將 key attr 以及其相依 attr 移出成另一 table ![](https://i.imgur.com/9SC8XEz.png) * 3NF * [3NF](http://database.klab.tw/teach/t5_3.php) * 非鍵值 attr 不存在 FD 遞移性 * $Y \rightarrow Z$ 不能存在 $Y \rightarrow X,\ X \rightarrow Z$ * 3NF 在以下兩條件達成任一時就成立，令一關係 $X \rightarrow A$ 存在於關係 R 中 * X 是 R 的 superkey * A 是 R 的 鍵值 * ![](https://i.imgur.com/23qonpi.png) ![](https://i.imgur.com/f3U3ghk.png) * BCNF * 令一關係 $X \rightarrow A$ 存在於關係 R 中 * X 是 R 的 superkey ![](https://i.imgur.com/Nz22X9Z.png) ![](https://i.imgur.com/ugWRzog.png) 通常只會到 3NF 更高 level 的正則化在實務資料中不易達成 ## Concurrency [deadlock-sol](http://www.csie.sju.edu.tw/cm/course/db/ch13.pdf) ### Conflict equivalent * 當 transaction 對某資料 X 進行讀寫時，就會與另一個 transaction 中對 X 進行讀寫發生 Conflict * 兩個 transaction 只有在彼此 conflict operation 的執行順序一樣時稱為 equivalent * 也就是說兩組 execution 對 X 的讀寫順序是一樣的 ### Serializable * 當上述 transactions 對 serial 的 transaction conflict equivalent，則其也是 Serializable，並行或是順序執行結果不變(因為讀寫的先後順序一樣) * 確保 Serializable 的方式 ### Two phase locking * 每個進入 DBMS 的 Transaction 都必須被這兩個 protocol 限制 * Growing Phase * 要求並等待所有 transaction 會用的 data 的 lock * e.g. 該 transaction 會讀寫 x, y, z，則 transaction 可以獲得對 x, y, z 上鎖，並且要等到所有 lock 都獲得才能繼續 * Shrinking Phase * 釋放所有 data 的 lock * 缺點 * 速度慢，要等待所有 lock * 會產生 deadlock ($T_{1}, T_{2}$ 都要 x, y ，但是 $T_{1}$ 拿到 x ，$T_{2}$ 拿到 y) * 避免 deadlock 的一些方法 * 等一段時間 * 清掉某個 trans * 最少 lock 的 trans * 最少 update 的 trans * 最年輕的 trans (TimeStamp 最大的) * 清掉之後會 undo trans 作過的所有動作並且釋放 locks * Wait - die * 如果 $T_{i}$ 是比較早(與 $T_{j}$ 相比)進入 DBMS 的 Trans 則他會**等待**直到獲得 lock ，如果 $T_{i}$ 比較年輕則他會**死亡(Abort)** * Wound - wait * 如果 $T_{i}$ 是比較早(與 $T_{j}$ 相比)進入 DBMS 的 Trans 則他會**殺害** $T_{j}$ 獲得 lock ，如果 $T_{i}$ 比較年輕則他會**等待** * Two phase lock 一定能夠確保 serializable 但不是達成 serializable 的唯一方法 ![](https://i.imgur.com/09I9EHP.png) ### Time Stamping 每個 trans 都被加入進入系統的 timestamp * Protocol * RMAX * 最年輕執行 read() 的 trans 的 timestamp * WMAX * 最年輕執行 write() 的 trans 的 timestamp * TS * T 的 timestamp * 預設值都是負無限 * 再執行讀寫時會傾向讓 Timestamp 大(也就是比較年輕)的執行，如果你被拒絕會直接重來，再此時你的 Timestamp 就會是最大(最年輕)的。 ![](https://i.imgur.com/eegvmcW.png) ### Optimistic concurrency control 相信大部分的 trans 不會發生衝突，所以先執行，再驗證最後執行更新 * Read phase * 所有 trans 都能也只能讀取 DB 的 data 並建立 local copies 進行操作 * Validation phase * 驗證是否符合 Serializability * Write phase * 驗證符合才能寫入 Database ### ACID [ACID](https://zh.wikipedia.org/wiki/ACID) * Atomicity * trans 不可分割，全執行或全不執行。 * Consistency * 資料的一致性，讀寫都符合規則 * Isolation * 每個 trans 似乎都是獨立執行，一定會有一個 trans 比另一個早結束沒有發生並行(實際上並行) * Durability * 對資料庫 data 的動作要能夠存續 ![](https://i.imgur.com/SsUd7xe.png) ### Recovery Trans 的失敗都需要靠 Rollback(回滾)來復原，如何 Rollback 則是需要 DBMS 的 On-Line Log 來記錄。 * Write-ahead log * 在執行所有動作之前都要先寫入 log 才進行動作，這樣才知道作了甚麼事 * Rollback 一次就能達成多次的 Rollback * Checkpoint * DBMS 在運行一段時間後將上個 checkpoint 到現在這個 checkpoint 這段時間所有的 Trans commit 進 DBMS * 在 checkpoint 時候 DBMS 執行以下三件事 * Suspend execution * Force-Write * Write log * Resume * Deferred update * 所有對資料的 Update defer 直到該 trans 順利執行完畢才會 commit 進 DBMS * 在 commit 之前所有的 update 只會記錄在 log * Trans 在到達 commit point 前都不能更改 Database ![](https://i.imgur.com/vC6I8hb.png) * Immediate update * Trans 的 update 會即時進入 Database ![](https://i.imgur.com/yu4eRoa.png) ## HomeWorks ### Hw1 ![](https://i.imgur.com/TNR6Sxa.jpg) 假設所有眾議員名字皆不相同。 In the `State_Name` of `State_Region` `Has` many `Congress_Person`。Named `Name` and represent `District`，work in `Party`，start from `Start_date`。Many `Congress_Person` `Vote_to` Single `Bill`。`Bill` named `Bill_name`，voted at `Date_of_vote`，sponsored by `Sponsor`and finally `Passed_or_failed`。 ![](https://i.imgur.com/P6ZVCSx.png) ### Hw2 ![](https://i.imgur.com/UWhCjkM.png) ![](https://i.imgur.com/Xv5lErl.png) ( a ) $PR\_X$ $\leftarrow$ $(\sigma_{PNAME = X}(PROJECT))\bowtie_{PNUMBER = PNO}(WORKS\_ON)$ > 先找到 PROJECT 中 PNAME 稱為 X 的 tuple ，之後再從 PNUMBER = PNO 的條件與 WORKS_ON JOIN $EMP\_W10\_X$ $\leftarrow$ $(\sigma_{HOURS >= 10}(PR\_X))\bowtie_{ESSN = SSN}(EMPLOYEE)$ > 從 PR_X 找出符合時長超過 10 小時的 tuple 後從 ESSN = SSN 的條件與 EMPLOYEE JOIN $ANS(FNAME, LNAME)$ $\leftarrow$ $\pi_{FNAME,LNAME}(\sigma_{DNO=5}(EMP\_W10\_X))$ > 最後取出 DNO = 5 的 EMP 的 FNAME 與 LNAME ( b ) $PR\_HR(PNAME, HOURS)$ $\leftarrow$ $\pi_{PNAME, HOURS}((PROJECT \bowtie_{PNUMBER=PNO}(WORKS\_ON))$ > 先讓 PROJECT 從 PNUMBER = PNO 的條件與 WORKS_ON JOIN 再刪除除了 PNAME 以及 HOURS 之外的 columns $ANS(PNAME, TOT\_HR)$ $\leftarrow$ $_{PNAME}$ $ℑ_{SUM HOURS}(PR\_HR)$ > 最後以 PNAME 作為 Grouping Attr 完成 SUM 的聚合運算 ( c ) $ALL\_PR$ $\leftarrow$ $\pi_{PNUMBER}(PROJECT)$ > 找出並只留下所有的 PNUMBER $EMP\_PR$ $\leftarrow$ $\pi_{PNO, ESSN}(WORKS\_ON)$ > 找出並只留下所有正在進行的 PROJECT 的 PNO ESSN $EMP\_ALL\_PR$ $\leftarrow$ $EMP\_PR \div ALL\_PR$ > 進行除法 代表結果是 ALL_PR 中包含 EMP_PR 全部的 tuple $ANS(FNAME, LNAME)$ $\leftarrow$ $\pi_{FNAME, LNAME}(EMPLOYEE \bowtie EMP\_ALL\_PR)$ > 最後取出 FNAME 與 LNAME ( d ) $WORK\_EMP$ $\leftarrow$ $\pi_{ESSN}(WORKS\_ON)$ > 找出並只留下所有有在進行PROJECT的 ESSN $ALL\_EMP$ $\leftarrow$ $\pi_{SSN}(EMPLOYEE)$ > 找出並只留下所有 EMPLOYEE 的 SSN $NO\_PR\_EMP$ $\leftarrow$ $ALL\_EMP - WORK\_EMP$ > 兩者取差集 得到不在 WORK_EMP 裡面的 SSN $ANS(FNAME, LNAME)$ $\leftarrow$ $\pi_{FNAME, LNAME}(EMPLOYEE \bowtie NO\_PR\_EMP)$ > 最後取出 FNAME 與 LNAME ( e ) $FE\_EMP$ $\leftarrow$ $(\sigma_{SEX=female}(\pi_{SEX, SALARY}(EMPLOYEE)))$ > 找出並只留下 SEX 以及 SALARY 再從其中挑出 female $ANS(AVG\_SAL)$ $\leftarrow$ $ℑ_{AVERAGE SALARY}(FE\_EMP)$ > 最後完成 AVERAGE 的聚合運算 ### Hw3 ![](https://i.imgur.com/8STQLgB.png) [ANS](http://site.iugaza.edu.ps/wp-content/uploads/file/Ghadir%20Al%20Jaro/Data%20Base/DB_2.pdf) (a) ```sql= SELECT C-Name FROM Course, SECTION WHERE SECTION.C# = Course.C# AND (SECTION.Year=2014 OR SECTION.Year=2015) AND SECTION.Instructor = 'King' ; ``` (b) ```sql= SELECT C#, Semester, Year, COUNT(*) FROM Course, SECTION, Grade, STUDENT WHERE SECTION.Instructor = 'King' AND Grade.Sec# = SECTION.Sec# GROUP BY C#, Semester, Year; ``` GROUP BY 多個 Attr 代表將這三個組成一個 Key，三個相同的一組 (c) ```sql= SELECT Name, C-Name, C#, Credit, Semester, Year, Grade FROM STUDENT, Course, SECTION, Grade WHERE STUDENT.Major = 'CS' AND STUDENT.Class = 4 AND Grade.Sec# = SECTION.Sec# AND STUDENT.Sec# = Grade.Sec# AND Course.C# = SECTION.C#; ``` (d) ```sql= SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM Grade WHERE STUDENT.Sec# = Grade.Sec# AND NOT(Grade = 'A') ); ``` (e) ```sql= SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM Grade WHERE STUDENT.Sec# = Grade.Sec# AND Grade = 'A' ); ```