AdvancedDataBaseSystemCH教学讲解课件

11Eightoperatorsoftherelationalalgebra:1.Thetraditionalsetoperatorsunion,intersection,difference, andCartesianproduct.2.Thespecialrelationaloperatorsrestrict,project,join,and divide.(SeeFig.7.1)7.1 Introduction2AdvancedDatabaseSystemEightoperatorsoftherelatio3AdvancedDatabaseSystem3AdvancedDatabaseSystem7.2 ClosureRevisitedTheoutputfromanygivenrelationaloperationisanotherrelation.Nestedrelationalexpressions4AdvancedDatabaseSystem7.2 ClosureRevisitedTheoutpu7.3 TheOriginalAlgebra:Syntax(See179) <relationexp>::=Relation{<tupleexpcommalist>} |<relvarname> |<relationopinv> |<withexp> |<introducedname> |(<relationexp>)<relationopinv>::=<project>|<nonproject><project>::=<relationexp>{[AllBut]<attributenamecommalist>}<nonproject>::=<rename>|<union>|<intersect>|<minus> |<times>|<where>|<join>|<divide> 5AdvancedDatabaseSystem7.3 TheOriginalAlgebra:Synt7.3 TheOriginalAlgebra:Syntax(Cont.) <rename>::=<relationexp>Rename(<renamingcommalist>)<union>::=<relationexp>Union<relationexp><intersect>::=<relationexp>Intersect<relationexp><minus>::=<relationexp>Minus<relationexp>

<times>::=<relationexp>Times<relationexp><where>::=<relationexp>Where<boolexp><join>::=<relationexp>Join<relationexp><divide>::=<relationexp>Divideby<relationexp>Per<per> <per>::=<relationexp>|(<relationexp>,<relationexp>)

<withexp>::=With<nameintrocommalist>:<exp> <nameintro>::=<exp>As<introducedname>6AdvancedDatabaseSystem7.3 TheOriginalAlgebra:SyntTuple-homogeneousUnion,Intersect,andDifference(SeeFig.7.2)Product(SeeFig.7.3)IfweneedtoconstructtheCartesianproductoftworelationsthatdohaveanysuchcommonattributenames,wemustuse theRenameoperatorfirsttorenameattributesappropriately.Restrict(SeeFig.7.4)Project(SeeFig.7.5)7.4 TheOriginalAlgebra:Semantics7AdvancedDatabaseSystemTuple-homogeneous7.4 TheOrigFig.7.2 Union,intersection,anddifference8AdvancedDatabaseSystemFig.7.2 Union,intersection,Fig.7.3 Cartesianproductexample9AdvancedDatabaseSystemFig.7.3 CartesianproductexaFig.7.4 Restrictionexamples10AdvancedDatabaseSystemFig.7.4 Restrictionexamples1Fig.7.5 Projectionexamples11AdvancedDatabaseSystemFig.7.5 Projectionexamples11Joinnaturaljoin(SeeFig.7.6)θ-join(SeeFig.7.7)((SRenameCityAsScity)Times(PRenameCityAsPcity))WhereScity>PcityDivide(SeeFig.7.8)7.4 TheOriginalAlgebra:Semantics(Cont.)12AdvancedDatabaseSystemJoin7.4 TheOriginalAlgebra:Fig.7.6 & Fig.7.713AdvancedDatabaseSystemFig.7.6 & Fig.7.713AdvancedFig.7.8 Divisionexamples14AdvancedDatabaseSystemFig.7.8 Divisionexamples14Ad7.5 Examples

Exam1:((SpJoinS)WhereP#=P#(‘P2’)){Sname}Exam2:(((PWhereColor=Color(‘Red’)) JoinSp){S#}JoinS){Sname}Exam3:((S{S#}DividebyP{P#}PerSp{S#,P#}) JoinS){Sname}Exam4:S{S#}Divideby(SpWhereS#=S#(‘S2’)){P#} PerSp{S#,P#}Exam5:(((SRenameS#AsSa){Sa,City}Join (SRenameS#AsSb){Sb,City}) WhereSa<Sb){Sa,Sb}Exam6:((S{S#}Minus(SpWhereP#=P#(‘P2’)){S#}) JoinS){Sname}15AdvancedDatabaseSystem7.5 ExamplesExam1:((SpJoi7.6 WhatIstheAlgebraFor?Theoperatorsjoin,intersect,anddividecanbedefinedin termsoftheotherfive.Oftheremainingfive,however,nonecanbedefinedintermsoftheotherfour,sowecanregardthosefiveasconstitutingaprimitiveorminimumset.Somepossibleapplications:1.Definingascopeforretrieval2.Definingascopeforupdate3.Definingintegrityconstraints4.Definingderivedrelvars5.Definingstabilityrequirements6.Definingsecurityconstraints16AdvancedDatabaseSystem7.6 WhatIstheAlgebraFor?Th7.6 WhatIstheAlgebraFor?(Cont.)Ahigh-level,symbolicrepresentationoftheuser‘sintentTransformationrules ((SpJoinS)WhereP#=P#(‘P2’)){Sname} ((SpWhereP#=P#(‘P2’))JoinS){Sname}Thealgebrathusservesasaconvenientbasisforoptimization.Alanguageissaidtoberelationallycompleteifitisatleastas powerfulasthealgebra.17AdvancedDatabaseSystem7.6 WhatIstheAlgebraFor?(7.7 FurtherPointsAssociativityandCommutativityAssociative:Union,Intersect,Times,Join e.g.(AUnionB)UnionC=AUnion(BUnionC) =AUnionBUnionCCommutative:Union,Intersect,Times,Join e.g.AUnionB=BUnionASomeEquivalences e.g.r{}=Table_Dumifr=empty, Table_Deeotherwise(anullaryprojection) rJoinTable_Dee=Table_DeeJoinr=rrTimesTable_Dee=Table_DeeTimesr=r18AdvancedDatabaseSystem7.7 FurtherPointsAssociativit7.7 FurtherPoints(Cont.)SomeGeneralizations Ifscontainsnorelationsatall,then:ThejoinofallrelationsinsisdefinedtobeTable_Dee.Theunionofallrelationsinsisdefinedtobetheemptyrelation.Theintersectionofallrelationsinsisdefinedtobethe“universal”relation.19AdvancedDatabaseSystem7.7 FurtherPoints(Cont.)Some(SeePage196)<semijoin>::=<relationexp>Semijoin<relationexp><semiminus>::=<relationexp>Semiminus<relationexp><extend>::=Extend<relationexp>Add(<extendaddcommalist>) <extendadd>::=<exp>As<attributename><summarize>::=Summarize<relationexp>Per<relationexp> Add(<summarizeaddcommalist>) <summarizeadd>::=<summarytype>[(<scalartype>)] As<attributename><summarytype>::=Count|Sum|Avg|Max|Min|All|Any |Countd|Sumd|Avgd|… <tclose>::=Tclose<relationexp>7.8 AdditionalOperators20AdvancedDatabaseSystem(SeePage196)7.8 AdditionalO7.8 AdditionalOperators(Cont.)Semijoin(aJoinb){X,Y} e.g.SSemijoin(SpWhereP#=P#(‘P2’))SemidifferenceaMinus(aSemijoinb) e.g.SSemiminus(SpWhereP#=P#(‘P2’))Extend e.g.ExtendPAdd(Weight＊454)AsGmwt (SeeFig.7.9)21AdvancedDatabaseSystem7.8 AdditionalOperators(Cont7.8 AdditionalOperators(Cont.)Exam1:ExtendSAdd‘Supplier’AsTagExam2:Extend(PJoinSp)Add(Weight＊Qty)AsShipwtExam3:(ExtendSAddCityAsScity){AllButCity} RenameExam4:ExtendPAdd(Weight＊454AsGmwt, Weight＊16AsOzwt)Exam5:ExtendS AddCount((SpRenameS#AsX)WhereX=S#) AsNp (SeeFig.7.10)22AdvancedDatabaseSystem7.8 AdditionalOperators(Cont7.8 AdditionalOperators(Cont.)Exam1:SummarizeSpPerP{P#}Add(Sum(Qty)AsTotqty,