AdvancedDataBaseSystemCH教学讲解课件.ppt

1、Advanced Database System Yin-Fu Huang n Eight operators of the relational algebra:1.The traditional set operators union,intersection,difference,and Cartesian product.2.The special relational operators restrict,project,join,anddivide.(See Fig.7.1)Advanced Database System Yin-Fu Huang Advanced Databas

2、e System Yin-Fu Huang n The output from any given relational operation is another relation.n Nested relational expressionsAdvanced Database System Yin-Fu Huang n(See Page178-179):=Relation|():=|:=All But :=|Advanced Database System Yin-Fu Huang :=Rename():=Union :=Intersect :=Minus :=Times :=Where :

3、=Join :=Divideby Per :=|(,):=With :=As Advanced Database System Yin-Fu Huang n Tuple-homogeneousn Union,Intersect,and Difference(See Fig.7.2)n Product(See Fig.7.3)If we need to construct the Cartesian product of two relationsthat do have any such common attribute names,we must use the Rename operato

4、r first to rename attributes appropriately.n Restrict(See Fig.7.4)n Project(See Fig.7.5)Advanced Database System Yin-Fu Huang Advanced Database System Yin-Fu Huang Advanced Database System Yin-Fu Huang Advanced Database System Yin-Fu Huang Advanced Database System Yin-Fu Huang n Join natural join(Se

5、e Fig.7.6)-join(See Fig.7.7)(S Rename City As Scity)Times(P Rename City As Pcity)Where Scity Pcityn Divide(See Fig.7.8)Advanced Database System Yin-Fu Huang Advanced Database System Yin-Fu Huang Advanced Database System Yin-Fu Huang n Exam 1:(Sp Join S)Where P#=P#(P2)Snamen Exam 2:(P Where Color=Col

6、or(Red)Join Sp)S#Join S)Snamen Exam 3:(S S#Divideby P P#Per Sp S#,P#)Join S)Snamen Exam 4:S S#Divideby(Sp Where S#=S#(S2)P#Per Sp S#,P#n Exam 5:(S Rename S#As Sa)Sa,City Join (S Rename S#As Sb)Sb,City)Where Sa Sb)Sa,Sbn Exam 6:(S S#Minus(Sp Where P#=P#(P2)S#)Join S)SnameAdvanced Database System Yin-

7、Fu Huang n The operators join,intersect,and divide can be defined interms of the other five.n Of the remaining five,however,none can be defined in terms of the other four,so we can regard those five as constituting a primitive or minimum set.n Some possible applications:1.Defining a scope for retrie

8、val2.Defining a scope for update3.Defining integrity constraints4.Defining derived relvars5.Defining stability requirements6.Defining security constraintsAdvanced Database System Yin-Fu Huang n A high-level,symbolic representation of the users intentTransformation rules(Sp Join S)Where P#=P#(P2)Snam

9、e(Sp Where P#=P#(P2)Join S)Snamen The algebra thus serves as a convenient basis for optimization.n A language is said to be relationally complete if it is at least aspowerful as the algebra.Advanced Database System Yin-Fu Huang n Associativity and Commutativity Associative:Union,Intersect,Times,Join

10、e.g.(A Union B)Union C =A Union(B Union C)=A Union B Union C Commutative:Union,Intersect,Times,Joine.g.A Union B=B Union An Some Equivalencese.g.r =Table_Dum if r=empty,Table_Dee otherwise(a nullary projection)r Join Table_Dee=Table_Dee Join r=r r Times Table_Dee=Table_Dee Times r=rAdvanced Database

11、 System Yin-Fu Huang n Some GeneralizationsIf s contains no relations at all,then:lThe join of all relations in s is defined to be Table_Dee.lThe union of all relations in s is defined to be the empty relation.lThe intersection of all relations in s is defined to be the“universal”relation.Advanced D

12、atabase System Yin-Fu Huang n(See Page 196):=Semijoin :=Semiminus :=Extend Add():=As :=Summarize Per Add():=()As :=Count|Sum|Avg|Max|Min|All|Any|Countd|Sumd|Avgd|:=Tclose Additional OperatorsAdvanced Database System Yin-Fu Huang Additional Operators(Cont.)n Semijoin (a Join b)X,Ye.g.S Semijoin(Sp Wh

13、ere P#=P#(P2)n Semidifference a Minus(a Semijoin b)e.g.S Semiminus(Sp Where P#=P#(P2)n Extende.g.Extend P Add(Weight454)As Gmwt(See Fig.7.9)Advanced Database System Yin-Fu Huang Additional Operators(Cont.)Exam 1:Extend S Add Supplier As TagExam 2:Extend(P Join Sp)Add(WeightQty)As ShipwtExam 3:(Exten

14、d S Add City As Scity)All But City RenameExam 4:Extend P Add(Weight454 As Gmwt,Weight16 As Ozwt)Exam 5:Extend S Add Count(Sp Rename S#As X)Where X=S#)As Np(See Fig.7.10)Advanced Database System Yin-Fu Huang Additional Operators(Cont.)Exam 1:Summarize Sp Per P P#Add(Sum(Qty)As Totqty,Avg(Qty)As Avgqt

15、y)Exam 2:Summarize Sp Per S S#Add Count As Np Summarize is not a primitive operator.Extend Exam 3:Summarize S Per S CityAdd Avg(Status)As Avg_Status Exam 4:Summarize Sp Per Sp Add Sum(Qty)As Grandtotaln Summarizee.g.Summarize Sp Per P P#Add Sum(Qty)As Totqty(See Fig.7.11)n Tclose the transitive closure of aAdvanced Database System Yin-Fu Huang n Groupe.g.SP Group(P#,Qty)As PQ(See Fig.7.12)n Ungroupe.g.SPQ Ungroup PQn The reversibility of the Group and Ungroup operations(See Fig.7.13)Functionally dependencyGrouping and UngroupingAdvanced Database System Yin-Fu Huang