复试877学长数据库003课件14版5algebraic

1、Chapter 5 Algebraic and Logical Query Languages 第5章 代数和逻辑查询语言5.1 Relational Operations on BagsWhat is a bag?A bag means a relation that may ( or may not ) have duplicate tuples.A set means a relation without duplicate tuples.Why bags?SQL, the most important query language for relational databases, i

2、s actually a bag language.Some operations, like projection, are much more efficient on bags than sets.5.1 Relational Operations on BagsSelection applies to each tuple, so its effect on bags is like its effect on sets.Projection also applies to each tuple, but as a bag operator, we do not eliminate d

3、uplicates.Products and joins are done on each pair of tuples, so duplicates in bags have no effect on how we operate.Example: Bag SelectionR(A,B )125612A+B5 (R) =AB1212Example: Bag ProjectionR(A,B ) 125612 A (R) =A151Example: Bag ProductR(A,B )S(B,C )1234567812R S =AR.BS.BC123412785634567812341278Ex

4、ample: Bag Theta-JoinR(A,B )S(B,C )1234567812R R.BS.B S =AR.BS.BC12341278567812341278Bag UnionAn element appears in the union of two bags the sum of the number of times it appears in each bag.Example: 1,2,1 1,1,2,3,1 = 1,1,1,1,1,2,2,3Bag IntersectionAn element appears in the intersection of two bags

5、 the minimum of the number of times it appears in either.Example: 1,2,1,1 1,2,1,3 = 1,1,2.Bag DifferenceAn element appears in the difference A B of bags as many times as it appears in A, minus the number of times it appears in B.But never less than 0 times.Example: 1,2,1,1 1,2,3 = 1,1.Duplicate Elim

6、inationA BR1 22 31 2 (R)A B1 22 3Aggregation OperatorsSUMAVGMINMAXCOUNTA B1 22 31 2 SUM(B)=2+3+2=7Grouping OperatorsIf there is grouping, then the aggregation is within groups.L(R)Constructing method:Partition the tuples of R into groups. Each group consists of all tuples having one particular assig

7、nment of values to the grouping attributes in the list L. If there are no grouping attributes, the entire relation R is one group.For each group, produce one tuple consisting of:The grouping attributes values for that group andThe aggregations, over all tuples of that group, for the aggregated attri

8、butes on list L.starName, MIN(year)minYear, COUNT(titile) ctTitle(StarsIn)Renaming Operator and Sorting OperatorRenaming: Sorting: L(R)AB(R) A+BC(R)A(R) A, B (R)OuterjoinSuppose we join R S.A tuple of R that has no tuple of S with which it joins is said to be dangling.Similarly for a tuple of S.Outerjoin preserves dangling tuples by padding them with a special NULL symbol in the result.Left outerjoin R S.Right outerjoin R S.Full outerjoin R S.LRLeft OuterjoinOuterjoinRight OuterjoinOuterjoinFull OuterjoinOuterjoinExample: OuterjoinR = ( AB