数据库系统教学课件：第5讲-关系模型2

1、第5讲(第6章): 关 系 模 型2重庆大学计算机学院课程名称: 数据库系统 -关系代数提供关系模型操作关系代数是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.六种关系代数基本操作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无序，用属性名识别！(关系的又一重要特征)消除重复!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)消除重复! 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)这些操作可以灵活复合

9、Can build expressions using multiple operationsExample: A=C(r x s) 假设：r,s同前页，则该操作结果为因r x s: 上述五种基本操作能够满足应用需要？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 .有何用途？适应不同部门的习惯！利于遗留系

11、统的移植！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* - 赋值Additional Operations - 附加操作并不增加操作能力！但可简化应用操作！We define additional operations that do not add any power to the relationa

13、l algebra, but that simplify common queries.数据操作能力有所增强？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例： 假设 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)消除重复属性!注意属性排列顺序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 - 扩展的关系代数操作Generalized Projection - 广义投影Aggregate Functions - 聚集函数扩展的查询操作还有什么查询操作非常重要？增加数据库查询操作有什么准则？方便用户，应用需要，使用价值！下述查询操作应用中频繁用到！数据操作能力有所增强？答案：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 求元组数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 - 删除多个元组 *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 - 插入一/多个元组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