關系模型及oracle對象關系數(shù)據(jù)庫ppt課件

1、第三章 關系模型及oracle對象關系數(shù)據(jù)庫3.1 關系模型和根本概念3.2 關系代數(shù)3.3 oracle對象關系數(shù)據(jù)庫系統(tǒng).關系數(shù)據(jù)庫系統(tǒng)是支持關系模型的數(shù)據(jù)庫系統(tǒng)。 1970年初，IBM公司的高級研討員Dr.E.F.Codd發(fā)表論文提出了關系模型，奠定了關系數(shù)據(jù)庫的實際根底。Dr.E.F.Codd proposed the relational model for database systems in 1970s. Vendors: ORACLE(9i ),IBM(DB2) , Microsoft (ACESS and SQLServer), Foxbase(Foxpro), Sybas

2、e.關系的定義關系relation:一個關系對應一張二維表 元組tuple：表中的一行即為一個元組屬性attribute：表中的一列為一個屬性，給每個屬性起一個名字即屬性名ID，NAME，PHONE，SALES-REP-ID .主碼primary key:表中的某個屬性組，它可以獨一確定元組。each row of data in a table is uniquely identified by a primary key. It must contain a value 外碼foreign key：you can logically relate information from mult

3、iple tables using foreign keys. .關系模型 關系模型由關系數(shù)據(jù)構造，關系操作和關系完好性約束三部分組成。數(shù)據(jù)構造 關系二維表，它由行和列組成。 關系操作常見的關系操作包括：選擇select，投影project，銜接join，除divide，并union，交intersection，差difference等查詢query操作和添加insert，刪除delete，修正update操作兩大部分。 完好性關系的三類完好性約束 實體完好性 、參照完好性、用戶定義的完好性 前往.關系代數(shù)The relational algebra(關系代數(shù)) is the basis of

4、 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=導師集合= 張清玫，劉逸， D2 =專業(yè)集合= 計算機，信息， D3 =研討生集合= 李勇，劉晨，王名 那么 D1 X D2 X D3 = (張清玫，計算機，李勇 ) , (張清玫，計算機，劉晨 )

5、, (張清玫，計算機，王名 ) , (張清玫，信 息，李勇 ) , (張清玫，信 息，劉晨 ) , (張清玫，信 息，王名 ) , (劉 逸，計算機，李勇 ) , (劉 逸，計算機，劉晨 ) , (劉 逸，計算機，王名 ) , (劉 逸，信 息，李勇 ) , (劉 逸，信 息，劉晨 ) , (劉 逸，信 息，王名 ) , 共12個元組。笛卡兒積可對應一張二維表。Example of Cartesian product. basic operatorsSet intersection 交Union 并set difference 差集合差Cartesian product 笛卡兒積Select

6、選擇Project 投影Natural join 自然銜接Division 除The 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注：選擇條件必需是針對同一元組中的相應屬性值進展比較.Notation: p(r)p is called the selection predicateDefined as: p(r) = t | t

7、 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 selection: branch-name=“Perryridge(account).(2) Project Operation ExampleRelation r:ABC102030401112AC1112=AC112A,C (r).No

8、tation: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 listedDuplicate rows removed from result, since relations are setsE.g. To eliminate the branch-name attribute of account ac

9、count-number, balance (account) .(3) Union Operation ExampleRelations r, s: r s:AB121AB23rsAB1213.Notation: r sDefined as: r s = t | t r or t sFor r s to be valid：1. r, s must have the same arity (等目,同元,same number of attributes)2. The attribute domains must be compatible (e.g., 2nd column of r deal

10、s 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).4Set Difference Operation ExampleRelations r, s:r s:AB121AB23rsAB11.Notation r sDefined as: r s = t | t r and t sSet differences m

11、ust be taken between compatible relations.r and s must have the same arityattribute domains of r and s must be compatible.5Cartesian-Product Operation-Example廣義笛卡兒積Relations r, s:r x s:AB12AB11112222CD 1010201010102010EaabbaabbCD10102010Eaabbrs.(6) Set-Intersection OperationNotation: r sDefined as:r

12、 s = t | t r and t s Assume: r, s have the same arity attributes of r and s are compatibleNote: r s = r - (r - s).Set-Intersection Operation - ExampleRelation r, s:r sA B121A B23rsA B 2. Notation: r s(7) Natural-Join OperationLet r and s be relations on schemas R and S respectively. Then, r s is a r

13、elation on schema R S obtained as follows:Consider each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in R S, add a tuple t to the result, wheret has the same value as tr on rt has the same value as ts on sExample:R = (A, B, C, D)S = (E, B, D)Resu

14、lt schema = (A, B, C, D, E)r 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:AB12412CDaababB13123DaaabbErAB11112CDaaaabEsr s注：(1)r,s必需含有共同屬性 (名, 域?qū)粯?, (2) 銜接二個關系中同名屬性值相等的元組(3) 結(jié)果屬性是二者屬性集的并集，但消去重名屬性。.Theta join: r s = (r x s) is the

15、 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 be relations on schemas R and S respectively whereR = (A1, , Am, B1, , Bn)S = (B1, , Bn)The result of r s is a relation on schema

16、R 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 體系構造(1)物理構造 datafiles redo log files control files parameter file 數(shù)據(jù)文件 日志文件 控制文件 參數(shù)文件 data filesredo log filescontrol fileparameter iles*.dbf*

17、.logInitoraid.ora*.ctl.(2)內(nèi)存構造(SGA) 占OS內(nèi)存的60-70%，大小可由參數(shù)文件內(nèi)參數(shù)計算 shared pool(共享池)， database buffer cache(數(shù)據(jù)緩沖區(qū)), redo log buffer(重做日志緩沖區(qū)) (如以以下圖所示)SGA=share_pool_size+db_block_size*db_block_buffers+log_buffers.shared_pooldatabase_buffer_cacheredo_log buffer.SGAbackground process(3)instance=SGA+backgro

18、und process 實例 = 內(nèi)存分配 + 一組后臺進程 假設把Oracle比作一部汽車，instance相當于汽車的發(fā)動機一樣,啟動oracle前提應先啟動instance.(4)session(銜接) Oracle是多用戶、多義務、可分布式管理的數(shù)據(jù)庫，同時可有許 多個用戶對數(shù)據(jù)庫操作。oracleuseruserusersession. (5)transaction(一組修正動作的集合) 買賣事務Eg: 1、 insert DDL (數(shù)據(jù)定義語句) delete 例如：create,alter,drop,conmit 等 commit 每兩個DDL語句間是一個transaction 2、 update DML (數(shù)據(jù)控制語句) rollback 例如：Insert,Delete,Update .(6)后臺進程 PMON，LCLN，RECO，SMON，DBWR，LGWR，CKPT，ARCHPMON 做程序的清潔任務，處置一些不正常退出的事件SMON 做系統(tǒng)的清潔任務，執(zhí)行系統(tǒng)出錯后自動恢復任務LCKN Orac