资源描述
,*,Advanced Database System,Yin-Fu Huang,1,1,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,and,divide.(See Fig.7.1),7.1Introduction,2,Advanced Database System,Eight operators of the relatio,3,Advanced Database System,3Advanced Database System,7.2Closure Revisited,The output from any given relational operation is another relation.,Nested relational expressions,4,Advanced Database System,7.2Closure RevisitedThe outpu,7.3The Original Algebra:Syntax,(See Page178-179),:=Relation,|,|,|,|,|(),:=|,:=All But ,:=|,|,5,Advanced Database System,7.3The Original Algebra:Synt,7.3The Original Algebra:Syntax(Cont.),:=Rename(),:=Union,:=Intersect,:=Minus,:=Times,:=Where,:=Join,:=Divideby Per,:=|(,),:=With :,:=As,6,Advanced Database System,7.3The Original Algebra:Synt,Tuple-homogeneous,Union,Intersect,and Difference(See Fig.7.2),Product(See Fig.7.3),If we need to construct the Cartesian product of two relations,that do have any such common attribute names,we must use,the Rename operator first to rename attributes appropriately.,Restrict(See Fig.7.4),Project(See Fig.7.5),7.4 The Original Algebra:Semantics,7,Advanced Database System,Tuple-homogeneous7.4 The Orig,Fig.7.2Union,intersection,and difference,8,Advanced Database System,Fig.7.2Union,intersection,Fig.7.3Cartesian product example,9,Advanced Database System,Fig.7.3Cartesian product exa,Fig.7.4Restriction examples,10,Advanced Database System,Fig.7.4Restriction examples1,Fig.7.5Projection examples,11,Advanced Database System,Fig.7.5Projection examples11,Join,natural join(See Fig.7.6),-join(See Fig.7.7),(S Rename City As Scity),Times,(P Rename City As Pcity),Where Scity Pcity,Divide(See Fig.7.8),7.4 The Original Algebra:Semantics(Cont.),12,Advanced Database System,Join7.4 The Original Algebra:,Fig.7.6&Fig.7.7,13,Advanced Database System,Fig.7.6&Fig.7.713Advanced,Fig.7.8Division examples,14,Advanced Database System,Fig.7.8Division examples14Ad,7.5Examples,Exam 1:(Sp Join S)Where P#=P#(P2)Sname,Exam 2:(P Where Color=Color(Red),Join Sp)S#Join S)Sname,Exam 3:(S S#Divideby P P#Per Sp S#,P#),Join S)Sname,Exam 4:S S#Divideby(Sp Where S#=S#(S2)P#,Per Sp S#,P#,Exam 5:(S Rename S#As Sa)Sa,City Join,(S Rename S#As Sb)Sb,City),Where Sa Sb)Sa,Sb,Exam 6:(S S#Minus(Sp Where P#=P#(P2)S#),Join S)Sname,15,Advanced Database System,7.5Examples Exam 1:(Sp Joi,7.6What Is the Algebra For?,The operators join,intersect,and divide can be defined in,terms of the other five.,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.,Some possible applications:,1.Defining a scope for retrieval,2.Defining a scope for update,3.Defining integrity constraints,4.Defining derived relvars,5.Defining stability requirements,6.Defining security constraints,16,Advanced Database System,7.6What Is the Algebra For?Th,7.6What Is the Algebra For?(Cont.),A high-level,symbolic representation of the users intent,Transformation rules,(Sp Join S)Where P#=P#(P2)Sname,(Sp Where P#=P#(P2)Join S)Sname,The algebra thus serves as a convenient basis for optimization.,A language is said to be relationally complete if it is at least as,powerful as the algebra.,17,Advanced Database System,7.6What Is the Algebra For?(,7.7Further Points,Associativity and Commutativity,Associative:Union,Intersect,Times,Join,e.g.(A Union B)Union C =A Union(B Union C),=A Union B Union C,Commutative:Union,Intersect,Times,Join,e.g.A Union B=B Union A,Some Equivalences,e.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=r,18,Advanced Database System,7.7Further PointsAssociativit,7.7Further Points(Cont.),Some Generalizations,If s contains no relations at all,then:,The join of all relations in s is defined to be Table_Dee.,The union of all relations in s is defined to be the empty relation.,The intersection of all relations in s is defined to be the“universal”relation.,19,Advanced Database System,7.7Further Points(Cont.)Some,(See Page 196),:=Semijoin,:=Semiminus,:=Extend Add(),:=As,:=Summarize Per,Add(),:=(),As,:=Count|Sum|Avg|Max|Min|All|Any,|Countd|Sumd|Avgd|,:=Tclose,7.8,Additional Operators,20,Advanced Database System,(See Page 196)7.8Additional O,7.8,Additional Operators(Cont.),Semijoin,(a Join b)X,Y,e.g.S Semijoin(Sp Where P#=P#(P2),Semidifference,a Minus(a Semijoin b),e.g.S Semiminus(Sp Where P#=P#(P2),Extend,e.g.Extend P Add(Weight,454)As Gmwt,(See Fig.7.9),21,Advanced Database System,7.8Additional Operators(Cont,7.8,Additional Operators(Cont.),Exam 1:Extend S Add Supplier As Tag,Exam 2:Extend(P Join Sp)Add(Weight,Qty)As Shipwt,Exam 3:(Extend S Add City As Scity)All But City,Rename,Exam 4:Extend P Add(Weight,454 As Gmwt,Weight,16 As Ozwt),Exam 5:Extend S,Add Count(Sp Rename S#As X)Where X=S#),As Np(See Fig.7.10),22,Advanced Database System
展开阅读全文