資料庫系統 1

資料庫系統Database Systems

Database System Concepts

Schemas diagram

資料庫系統 2

每學期課程

學生資料

修課成績

課程資料

先修課程

Ex:UNIVERSITY

資料庫系統 3

Schemas diagram (ex1)

資料庫系統 4

create table

Schemas diagram (ex2)

省略資料型態 Student (Name, StudentNumber,class,Major)

帶有資料型態的表示形式 Student (Name(char 20), StudentNumber(int),

class(smallint),Major (char 10) ) 你也可以這樣表示…

student ( StudentNumber int not null,

name char(20), class smallint, Major char(10)

primary key (StudentNumber) )

加上“ create table”即為建立 student 表格的 SQL 語法

資料庫系統 5

資料庫系統Database Systems

Relational Database Constraints

若資料異動違反限制條件時…

資料庫系統 6

Update Operations on Relations Insert Operation Delete Operation Update Operation

資料庫系統 7

Insert 定義域限制 : 假設 SALARY 為整數型態 , 在 EMPLOYEE 中新增一個

SALARY 為’ NT30000’的記錄… 鍵值限制 : 在 EMPLOYEE 中新增一個 SSN 為 123456789 的記錄… 實體完整性限制 : 在 EMPLOYEE 中新增一個 SSN 為 NULL 的記錄… 參考完整性限制 : 假設 DNO 屬性參考到另一個 DEPARTMENT 的

DNUM 主鍵屬性 , 其中存在 1,4,5 號等三個部門 , 如今在 EMPLOYEE中新增一個 DNO 為 7 的記錄…

資料庫系統 8

Insert 違反限制時

拒絕 Insert 嘗試更正

資料庫系統 9

Delete 只可能違反參考完整性限制

可以刪除 !

資料庫系統 10

Delete 只可能違反參考完整性限制

不能刪除

資料庫系統 11

Delete 違反限制時

拒絕 Delete 嘗試更正 虛值 (null) 化 連鎖反應 (cascade)

資料庫系統 12

Update EMPLOYEE 的 SALARY,DNO 可以被更改 若將 EMPLOYEE 的 SSN 更改可能違反限制

SSN=‘999887777’ 更改為‘ 987654321’=> 違反　“主鍵” 、“參考完整性”　限制

資料庫系統 13

Update 違反限制時

拒絕 UPDATE 嘗試更正 虛值 (null) 化 連鎖反應 ( 系統可能不提供 )

資料庫系統 14

資料庫系統Database Systems

The Relational Algebra ( 補充 )

資料庫系統 15

The Relational Algebra 資料模型 (Data Model) 包括…

結構 限制 運算集合 ( 操作 )

Relational Data Model 的運算集合稱為Relational Algebra( 關聯式代數 )

資料庫系統 16

SELECT operations

選擇關聯中滿足選擇條件 (Selection condition) 的值組集合

Ex: 在 EMPLOYEE 中找出在 4 號部門工作的 tuple <selection condition> (relation name)

資料庫系統 17

SELECT operations Ex: 在 EMPLOYEE 中找出在 4 號部門工作的 tuple

DNO=4(EMPLOYEE)

SALARY>30000(EMPLOYEE)

資料庫系統 18

PROJECT operations 選擇所要的 (Attribute list) 的欄位項目 Ex: 列出 EMPLOYEE 中的 SEX,SALARY <attribute list> (relation name)

資料庫系統 19

PROJECT operations

<LNAME,FNAME,SALARY> (EMPLOYEE) <SEX,SALARY> (EMPLOYEE)

資料庫系統 20

SELECT and PROJECT operations

Ex1: 找出 SALARY<50000 的值組 , 列出其

SEX 、 SALARY 資料

資料庫系統 21

CARTESIAN PRODUCTEMP_DEPENDENTS ← EMPNAMS DEPENDENT

資料庫系統 22

CARTESIAN PRODUCT P2_2

EMP_DEPENDENTS ← EMPNAMS DEPENDENT

資料庫系統 23

Join 運算 Join Join 運算含有三個參數運算含有三個參數

Join condition (Predicate) : PJoin condition (Predicate) : P 關聯表 關聯表 R1R1 關聯表 關聯表 R2 R2

將 將 R1 R1 與 與 R2 R2 依依 Join Join 條件條件 PP 做 做 R1R1 ∞PP R2R2

∞

資料庫系統 24

Join 運算的範例Emp Dept

no name dept_no no name mgr_no

1 張三 1 1 會計部 3

2 李四 2 2 工程部 4

3 王五 1

4 毛六 2

Emp Emp.dept_no = Dept.no Dept

Emp.no Emp.name dept_no Dept.no Dept.name mgr_no

1 張三 1 1 會計部 3

2 李四 2 2 工程部 4

3 王五 1 1 會計部 3

4 毛六 2 2 工程部 4

等位合併(Equi-join)

∞

資料庫系統 25

NATURAL JOIN 在執行 EQUIJOIN 運算後，結果中一定會有兩欄的資料完全相同

去除其中一欄仍不影響整體意義，我們稱為「自然合併」

Emp Emp.dept_no = Dept.no Dept

Emp.no Emp.name dept_no Dept.no Dept.name mgr_no

1 張三 1 1 會計部 3

2 李四 2 2 工程部 4

3 王五 1 1 會計部 3

4 毛六 2 2 工程部 4

∞

資料庫系統 26

資料庫系統Database Systems

Functional Dependencies and Normalization

更新異常

資料庫系統 27

重要觀念 良好的語意 減少 tuple 的重複值 減少 tuple 的空值 關聯不會出現插入、刪除及修改異常 ( 補

充 )

不允許假值組 (Spurious Tuples) 的存在

資料庫系統 28

tuple 的重複值問題

資料庫系統 29

tuple 的重複值問題

NaturalJoinNatural-join :

EQUIJOIN 運算後 , 去除 Join attributes 其中一欄

資料庫系統 30

減少 tuple 的空值 空值易導致空間的浪費EMPID NAME ADDRESS TELNO PNO PNAME

E01 ALEX TAIPEI 123456 1 PROJ_1

E02 CATHY 2E03 JACK HOUSTON 654321 3 PROJ_3

E02 CATHY 3 PROJ_3

資料庫系統 31

Update anomalies 更新異常 ( 補充 )

資料的插入、刪除、修改產生的異常

Insertion Anomalies Deletion Anomalies Modification Anomalies

資料庫系統 32

Insertion Anomalies Case 1:

新增一筆員工資料 , 所屬 5 部門 , 必須確定部門的相關資訊是對的

Case 2: 若有一新成立的部門 , 但目前沒有任何員工 , 則此

部門資料無法建立 ( 因為 SSN 為 Primary Key)

Relation 分割之後可以有效解決 Insertion Anomalies的問題

資料庫系統 33

Relation 分割之後可以有效解決 Insertion Anomalies 的問題

NaturalJoin

Information 7 987987987

資料庫系統 34

Deletion Anomalies 若 Zelaya 為 Administration 部門的唯一員工 , 當

Zelaya 刪除時 , 會導致該部門的資訊完全消失

Relation 分割之後可以有效解決Deletion Anomalies的問題

資料庫系統 35

Modification Anomalies 當某一部門的一屬性值被更改時 , 必須欄

帶修正所有的相同屬性值 , 以確保資料的正確

資料庫系統 36

Modification Anomalies

資料庫系統 37

重要觀念 良好的語意 減少 tuple 的重複值 減少 tuple 的空值 關聯不會出現插入、刪除及修改異常 ( 補

充 )

不允許假值組 (Spurious Tuples) 的存在