數(shù)據(jù)庫系統(tǒng)教學(xué)課件：第5講-關(guān)系模型2

1、第5講(第6章): 關(guān) 系 模 型2重慶大學(xué)計(jì)算機(jī)學(xué)院課程名稱: 數(shù)據(jù)庫系統(tǒng) -關(guān)系代數(shù)提供關(guān)系模型操作關(guān)系代數(shù)是Procedural languageSix basic operatorsselect: (選擇)project: (投影)union: (并)set difference: (差)Cartesian product: x (笛卡爾積)rename: (重命名)These operators take one or two relations as inputs and produce a new relation as a result.六種關(guān)系代數(shù)基本操作Select Ope

2、ration 挑選合符條件的元組(行)Relation rABC=B D 5 (r)ABCD123710形式化定義: p(r) = t | t r and p(t)p is called the selection predicateWhere 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: =, , , . . Project Opera

3、tion 挑選指定屬性(列)Relation r:ABC102030401112AC1112=AC112 A,C (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無序，用屬性名識別！(關(guān)系的又一重要特征)消除重復(fù)!Union Operatio

4、n 將元組放在一起Relations r： s:r s:AB121rAB23sAB1213形式化定義: r s = t | t r or t sr, s must have the same number of attributesThe attribute domains must be compatible (example: 2nd column of r deals with the same type of values as does the 2nd column of s)消除重復(fù)! course_id ( semester=“Fall” year=2009 (section)

5、course_id ( semester=“Spring” year=2010 (section)Example: to find all courses taught in the Fall 2009 semester, or in the Spring 2010 semester, or in both(pp.127,Fig.6-4 section)Set Difference Operation 集合差Relations r： s:r s:AB121rAB23sAB11形式化定義：r s = t | t r and t sr and s must have the same number

6、 of attributesattribute domains of r and s must be compatible course_id ( semester=“Fall” year=2009 (section) course_id ( semester=“Spring” year=2010 (section)Example: to find all courses taught in the Fall 2009 semester, but not in the Spring 2010 semesterCartesian Product Operation 兩表的元組連接Relation

7、s r: s:r x s:AB11112222CD 1010201010102010EaabbaabbAB12rCD10102010Eaabbs形式化定義：r x s = t q | t r and q sAssume that attributes of r(R) and s(S) are disjoint. (That is, R S = ).If attributes of r(R) and s(S) are not disjoint, then renaming must be used.Example QueriesFind all instructors in the Physic

8、s department, along with the course_id of all courses they have taught（pp.28,Fig.2-9）Query 1 instructor.ID,course_id (dept_name=“Physics” ( instructor.ID=teaches.ID (instructor x teaches)Query 2 instructor.ID,course_id (instructor.ID=teaches.ID ( dept_name=“Physics” (instructor) x teaches)這些操作可以靈活復(fù)合

9、Can build expressions using multiple operationsExample: A=C(r x s) 假設(shè)：r,s同前頁，則該操作結(jié)果為因r x s: 上述五種基本操作能夠滿足應(yīng)用需要？AB11112222CD 1010201010102010EaabbaabbABCDE122101020aab故A=C(r x s):這五種操作形成完備集！故足夠用!Rename Operation - 表/屬性的重命名Allows us to name, and therefore to refer to, the results of relational-algebra e

10、xpressions.Allows us to refer to a relation by more than one name.Example: x (E)returns the expression E under the name XIf a relational-algebra expression E has arity n, then returns the result of expression E under the name X, and with theattributes renamed to A1 , A2 , ., An .有何用途？適應(yīng)不同部門的習(xí)慣！利于遺留系

11、統(tǒng)的移植！Example QueryFind the largest salary in the universityStep 1: find instructor salaries that are less than some other instructor salary (i.e. not maximum)using a copy of instructor under a new name dinstructor.salary ( instructor.salary d,salary (instructor x d (instructor) Step 2: Find the larg

12、est salarysalary (instructor) instructor.salary ( instructor.salary d,salary (instructor x d (instructor) 附加查詢操作Set intersection - 交Natural join - 自然連接Division* - 除Assignment* - 賦值A(chǔ)dditional Operations - 附加操作并不增加操作能力！但可簡化應(yīng)用操作！We define additional operations that do not add any power to the relationa

13、l algebra, but that simplify common queries.數(shù)據(jù)操作能力有所增強(qiáng)？Set Intersection Operation - 保留相同元組形式化定義: r s = t | t r and t s Assume: r, s have the same arity attributes of r and s are compatibleA B121rA B23sA B 2 Relation r : s : r sr s = r (r s)Natural Join Operation 元組按屬性值相同粘貼AB12412CDaababrAB11112CDaaa

14、abEB13123DaaabbEsr s：Relations r： s: 形式化定義: r sLet r and s be relations on schemas R and S respectively. Then, r s is a relation 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

15、 the result, where （t的屬性是的tr and ts屬性的合并，但去重）t has the same value as tr on rt has the same value as ts on s例： 假設(shè) R = (A, B, C, D) ,S = (E, B, D) 則有r s = r.A, r.B, r.C, r.D, s.E (r.B = s.B r.D = s.D (r x s)消除重復(fù)屬性!注意屬性排列順序Outer Join 還要保留無連接條件的元組形式化描述: R S An extension of the join operation that avoids

16、 loss of information.Computes the join and then adds tuples from one relation that does not match tuples in the other relation to the result of the join. Uses null values:- null signifies that the value is unknown or does not exist - All comparisons involving null are (roughly speaking) false by def

17、inition.Outer Join ExampleRelation instructor1Relation teaches1IDcourse_id101011212176766CS-101FIN-201BIO-101Comp. Sci.FinanceMusicIDdept_name101011212115151nameSrinivasanWuMozart Left Outer Join instructor teachesOuter Join ExampleJoin instructor teachesIDdept_name1010112121Comp. Sci.Financecourse_

18、id CS-101 FIN-201nameSrinivasanWuIDdept_name101011212115151Comp. Sci.FinanceMusiccourse_id CS-101 FIN-201 nullnameSrinivasanWuMozartOuter Join Example Full Outer Join instructor teaches Right Outer Join instructor teachesIDdept_name101011212176766Comp. Sci.Financenullcourse_id CS-101 FIN-201 BIO-101

19、nameSrinivasanWunullIDdept_name10101121211515176766Comp. Sci.FinanceMusicnullcourse_id CS-101 FIN-201 null BIO-101nameSrinivasanWuMozartnull*Assignment OperationThe assignment operation () provides a convenient way to express complex queries. Write query as a sequential program consisting ofa series

20、 of assignments followed by an expression whose value is displayed as a result of the query.Assignment must always be made to a temporary relation variable.Example: Write r s as temp1 R-S (r ) temp2 R-S (temp1 x s ) R-S,S (r )result = temp1 temp2The result to the right of the is assigned to the rela

21、tion variable on the left of the .May use variable in subsequent expressions.Extended RA Operations - 擴(kuò)展的關(guān)系代數(shù)操作Generalized Projection - 廣義投影Aggregate Functions - 聚集函數(shù)擴(kuò)展的查詢操作還有什么查詢操作非常重要？增加數(shù)據(jù)庫查詢操作有什么準(zhǔn)則？方便用戶，應(yīng)用需要，使用價(jià)值！下述查詢操作應(yīng)用中頻繁用到！數(shù)據(jù)操作能力有所增強(qiáng)？答案：Yes！(從后定義可知)為了處理之前未考慮的情況！Generalized Projection形式化定義：It

22、allows arithmetic functions to be used in the projection list.E is any relational-algebra expressionEach of F1, F2, , Fn are are arithmetic expressions involving constants and attributes in the schema of E.e.g. Aggregate Functions & Operations形式化定義如下：Aggregation function：takes a collection of values

23、 and returns a single value as a result.avg: average value 求平均值min: minimum value 求最小值max: maximum value 求最大值sum: sum of values 求和count:number of values 求元組數(shù)Aggregate operation：in relational algebra E is any relational-algebra expressionG1, G2 , Gn is a list of attributes on which to group (can be e

24、mpty)Each Fi is an aggregate functionEach Ai is an attribute nameAggregate Operation ExampleFind the average salary in each department dept_name avg(salary) (instructor)avg_salaryAggregate Functions (Cont.)Result of aggregation does not have a nameCan use rename operation to give it a nameFor conven

25、ience, we permit renaming as part of aggregate operationdept_name avg(salary) as avg_sal (instructor)Modification of the DatabaseThe content of the database may be modified using the following operations:Deletion - 刪除元組Insertion - 插入元組Updating - 修改元組All these operations are expressed using the assig

26、nment operator.A delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the database.Can delete only whole tuples; cannot delete values on only particular attributesA deletion is expressed in relational algebra by:r r E

27、where r is a relation and E is a relational algebra query.ExamplesDeletion - 刪除多個(gè)元組 *Delete all accounts at branches located in Needham.r1 branch_city = “Needham” (account branch )r2 account_number, branch_name, balance (r1)r3 customer_name, account_number (r2 depositor)account account r2depositor d

28、epositor r3 *Delete all loan records with amount in the range of 0 to 50loan loan amount 0and amount 50 (loan)account account branch_name = “Perryridge” (account ) Delete all account records in the Perryridge branch.Examples*Provide as a gift for all loan customers in the Perryridge branch, a $200 s

29、avings account. Let the loan number serve as the account number for the new savings account.account account (“A-973”, “Perryridge”, 1200)depositor depositor (“Smith”, “A-973”)r1 (branch_name = “Perryridge” (borrower loan)account account loan_number, branch_name, 200 (r1)depositor depositor customer_

30、name, loan_number (r1)Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch.To insert data into a relation, we either:specify a tuple to be insertedwrite a query whose result is a set of tuples to be insertedin relational algebra, an insertion i

31、s expressed by:r r Ewhere r is a relation and E is a relational algebra expression.The insertion of a single tuple is expressed by letting E be a constant relation containing one tuple. Insertion - 插入一/多個(gè)元組ExamplesA mechanism to change a value in a tuple without changing all values in the tupleUse the generalized projection operator to do this taskEach Fi is either the i th attribute of r, if the i th attribute is not updated, or,if the attribute is to be updated Fi is an expression, involving only constants and the attributes of r, which g