關(guān)系模型及oracle對象關(guān)系數(shù)據(jù)庫

1、第三章 關(guān)系模型及oracle對象關(guān)系數(shù)據(jù)庫3.1 關(guān)系模型和基本概念3.2 關(guān)系代數(shù)3.3 oracle對象關(guān)系數(shù)據(jù)庫系統(tǒng)關(guān)系的定義關(guān)系（關(guān)系（relation）:一個(gè)關(guān)系對應(yīng)一張二維表 元組（元組（tuple）：表中的一行即為一個(gè)元組屬性（屬性（attribute）：表中的一列為一個(gè)屬性，給每個(gè)屬性起一個(gè)名字即屬性名（ID，NAME，PHONE，SALES-REP-ID） 主碼（主碼（primary key）:表中的某個(gè)屬性組，它可以唯一確定元組。each row of data in a table is uniquely identified by a primary key. It

2、must contain a value 外碼（外碼（foreign key）：you can logically relate information from multiple tables using foreign keys. 關(guān)系模型 關(guān)系模型由關(guān)系數(shù)據(jù)結(jié)構(gòu)，關(guān)系操作和關(guān)系完整性約束三部分組成。數(shù)據(jù)結(jié)構(gòu) 關(guān)系（二維表），它由行和列組成。 關(guān)系操作常見的關(guān)系操作包括：選擇select，投影project，連接join，除divide，并union，交intersection，差difference等查詢query操作和增加insert，刪除delete，修改update操作兩大部分。

3、完整性關(guān)系的三類完整性約束 實(shí)體完整性 、參照完整性、用戶定義的完整性 返回關(guān)系代數(shù)The relational algebra(關(guān)系代數(shù)) is the basis of SQL.Basic Structure Cartesian product(笛卡兒積) Formally, given sets D1, D2, . Dn a relation r is a subset of D1 x D2 x x Dn , Thus a relation is a set of n-tuples (a1, a2, , an) where each ai Di例如， D1=導(dǎo)師集合= 張清玫，劉逸， D

4、2 =專業(yè)集合= 計(jì)算機(jī)，信息， D3 =研究生集合= 李勇，劉晨，王名 則 D1 X D2 X D3 = (張清玫，計(jì)算機(jī)，李勇 ) , (張清玫，計(jì)算機(jī)，劉晨 ) , (張清玫，計(jì)算機(jī)，王名 ) , (張清玫，信 息，李勇 ) , (張清玫，信 息，劉晨 ) , (張清玫，信 息，王名 ) , (劉 逸，計(jì)算機(jī)，李勇 ) , (劉 逸，計(jì)算機(jī)，劉晨 ) , (劉 逸，計(jì)算機(jī)，王名 ) , (劉 逸，信 息，李勇 ) , (劉 逸，信 息，劉晨 ) , (劉 逸，信 息，王名 ) , 共共12個(gè)元組。個(gè)元組。笛卡兒積笛卡兒積可對應(yīng)一張二維表。Example of Cartesian prod

5、uctn basic operatorsSet intersection 交Union 并set difference 差（集合差）Cartesian product 笛卡兒積Select 選擇Project 投影Natural join 自然連接Division 除nThe operators take two or more relations as inputs and give a new relation as a result.(1) Select Operation ExampleRelation rABC=B D 5 (r)ABCD123710注：選擇

6、條件必須是針對注：選擇條件必須是針對同一元組中的相應(yīng)屬性值同一元組中的相應(yīng)屬性值進(jìn)行比較進(jìn)行比較Notation: p(r)p is called the selection predicateDefined as: p(r) = t | t r and p(t)Where p is a formula in propositional calculus consisting of terms connected by : (and), (or), (not)Each term is one of:op or where op is one of: =, , , . . Example of

7、selection: branch-name=“Perryridge”(account)(2) Project Operation ExampleRelation r:ABC102030401112AC1112=AC112A,C (r)Notation:A1, A2, , Ak (r)where A1, A2 are attribute names and r is a relation name.The result is defined as the relation of k columns obtained by erasing the columns that are not lis

8、tedDuplicate rows removed from result, since relations are setsE.g. To eliminate the branch-name attribute of account account-number, balance (account) (3) Union Operation ExampleRelations r, s: r s:AB121AB23rsAB1213Notation: r sDefined as: r s = t | t r or t sFor r s to be valid：1. r, s must have t

9、he same arity (等目,同元,same number of attributes)2. The attribute domains must be compatible (e.g., 2nd column of r deals with the same type of values as does the 2nd column of s)E.g. to find all customers with either an account or a loan customer-name (depositor) customer-name (borrower)（4）Set Differ

10、ence Operation ExampleRelations r, s:r s:AB121AB23rsAB11Notation r sDefined as: r s = t | t r and t sSet differences must be taken between compatible relations.lr and s must have the same aritylattribute domains of r and s must be compatible（5）Cartesian-Product Operation-Example（廣義笛卡兒積）Relations r,

11、s:r x s:AB12AB11112222CD 1010201010102010EaabbaabbCD10102010Eaabbrs(6) Set-Intersection OperationNotation: r sDefined as:r s = t | t r and t s Assume: lr, s have the same arity lattributes of r and s are compatibleNote: r s = r - (r - s)Set-Intersection Operation - ExampleRelation r, s:r sA B121A B2

12、3rsA B 2n Notation: r s(7) Natural-Join OperationLet r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows:lConsider each pair of tuples tr from r and ts from s. lIf tr and ts have the same value on each of the attributes in R S, add a tuple

13、t to the result, wherelt has the same value as tr on rlt has the same value as ts on sExample:R = (A, B, C, D)S = (E, B, D)lResult schema = (A, B, C, D, E)lr s is defined as: r.A, r.B, r.C, r.D, s.E ( r.B = s.B r.D = s.D (r x s)Natural Join Operation ExampleRelations r, s:AB12412CDaababB13123DaaabbE

14、rAB11112CDaaaabEsr s注：(1)r,s必須含有共同屬性 (名, 域?qū)?yīng)相同), (2) 連接二個(gè)關(guān)系中同名屬性值相等的元組(3) 結(jié)果屬性是二者屬性集的并集，但消去重名屬性。Theta join: r s = (r x s) is the predicate on attributes in the schema.Theta Join is the extension to the Nature Join.(8) Division OperationSuited to queries that include the phrase “for all”.Let r and s

15、be relations on schemas R and S respectively wherelR = (A1, , Am, B1, , Bn)lS = (B1, , Bn)The result of r s is a relation on schemaR S = (A1, , Am)r s = t | t R-S(r) u s ( tu r ) 注：商來自于 R-S(r) ，并且其元組t與s的拼接被r覆蓋。r s Division Operation ExampleRelations r, s:r s:AB12AB12311134612rs返回Oracle 體系結(jié)構(gòu)(1)物理結(jié)構(gòu) d

16、atafiles redo log files control files parameter file 數(shù)據(jù)文件 日志文件 控制文件 參數(shù)文件 data filesredo log filescontrol fileparameter iles*.dbf*.logInitoraid.ora*.ctlPMONLCKnRECORECOshared PoolDatabase Buffer CacheRedo LogBufferSGADataDataDataDataDataDataDataDataDataDataDatafilesRedo LogfilesParameterFileControlfi

17、lesServerUserDBWRLGWRCKPTARCHOfflinestorage(2)內(nèi)存結(jié)構(gòu)(SGA) 占OS內(nèi)存的60-70%，大小可由參數(shù)文件內(nèi)參數(shù)計(jì)算 shared pool(共享池)， database buffer cache(數(shù)據(jù)緩沖區(qū)), redo log buffer(重做日志緩沖區(qū)) (如以下圖所示)SGA=share_pool_size+db_block_size*db_block_buffers+log_buffersshared_pooldatabase_buffer_cacheredo_log bufferSGAbackground process(3)ins

18、tance=SGA+background process 實(shí)例 = 內(nèi)存分配 + 一組后臺進(jìn)程 如果把Oracle比作一部汽車，instance相當(dāng)于汽車的發(fā)動(dòng)機(jī)一樣,啟動(dòng)oracle前提應(yīng)先啟動(dòng)instance.(4)session(連接) Oracle是多用戶、多任務(wù)、可分布式管理的數(shù)據(jù)庫，同時(shí)可有許 多個(gè)用戶對數(shù)據(jù)庫操作。oracleuseruserusersession (5)transaction(一組修改動(dòng)作的集合) 交易事務(wù)Eg: 1、 insert DDL (數(shù)據(jù)定義語句) delete 例如：create,alter,drop,conmit 等 commit 每兩個(gè)DDL語句間是一個(gè)transaction 2、 update DML (數(shù)據(jù)控制語句) rollback 例如：Insert,Delete,Update (6)后臺進(jìn)程 PMON，LCLN，RECO，SMON，DBWR，LGWR，CKPT，ARCHPMON 做程序的清潔工作，處理一些不正常退出的事件SMON 做系統(tǒng)的清潔工作，執(zhí)行系統(tǒng)出錯(cuò)后自動(dòng)恢復(fù)工作LCKN Oracle系統(tǒng)表級或行級加鎖的進(jìn)程.RECO 恢復(fù)進(jìn)程DBWR 數(shù)據(jù)寫進(jìn)程LGWR 日志文件寫的進(jìn)程CKPT 檢測點(diǎn)AR