参考教案2数据库Clicktoedi

6.1therelationalalgebra

(oneofformalrelationalquerylanguages)TheformalrelationalquerylanguagesTheformallanguagesTherelationalalgebraTuplerelationalcalculusThedomainrelationalcalculusTherelationalgebraformsthebasisofthewidelyusedSQLquerylanguage.TherelationalalgebraTherelationalalgebraisaprocedurallanguage.Theuserinstructsthesystemtoperformasequenceofoperationsonthedatabasetocomputethedesiredresult.Nonprocedurallanguage:Theuserdescribesthedesiredinformationwithoutgivingaspecificprocedureforobtainingthatinformation.TherelationalAlgebraSixbasicoperatorsselect:project:union:setdifference:–

Cartesianproduct:xrename:AdditionalrelationalalgebraoperationsSet-intersection∩Natural-joinTherelationalAlgebraTheoperatorstakeoneortworelationsasinputsandproduceanewrelationasaresult.1)SelectOperationTheselectoperationselectstuplesthatsatisfyagivenpredicate(谓词).

σRelationrA=B^D>5

(r)1)SelectOperationNotation:

p(r),pisselectionpredicateDefinedas:

p(r)={t|trandp(t)}Wherepisaformulainpropositionalcalculus(命题演算)consistingoftermsconnectedby:(and),(or),(not)Eachtermisoneof:<attribute>op <attribute>or<constant>whereopisoneof:=,,>,.<.

1)SelectOperationExampleofselection:

dept_name=“Physics”(instructor)ExampleofselectionoperationSnoSnameSsexSageSdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS

StudentEx1:RetrieveallstudentsfromIS.Ex2:Retrieveallstudentswhoareunder20yearsofage.Exampleofselectionoperation

Ex3:σ2>4∧1=′a′(R)2)ProjectOperationTheprojectoperationisaunaryoperationthatreturnsitsargumentrelation,withcertainattributesleftout.Example:

Relationr:A,C(r)π2)ProjectOperationNotation:

whereA1,A2,……..areattributenamesandrisarelationname.

ExampleofprojectionoperationSnoSnameSsexSageSdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS

StudentEx1:Retrievenameanddeptofallstudents.Ex2:Retrievealldeptsinstudentrelation.

EX：π3,1(R)=Exampleofprojectionoperation3)UnionOperationTheunionoperationisabinaryoperationthatgettuplesthatappearineitherorbothofthetworelations.Example:

URS3)UnionOperationNotation:r

sDefinedas:

r

s={t|trorts}Forr

stobevalid.r,

smusthavethesamearity(数量)(samenumberofattributes)2.Thedomainsoftheithattributeofrandtheithattributeofsmustbethesame,foralli.3)UnionOperation–ExampleRelationsr,s:rs:Example:tofindallcoursestaughtintheFall2009semester,orintheSpring2010semester,orinbothsectioncourse_id(

semester=“Fall”Λyear=2009(section))

course_id(

semester=“Spring”Λyear=2010(section))Answer:Answer:4)SetDifferenceOperationTheset-differenceoperationallowsustofindtuplesthatareinonerelationbutarenotinanother.Theexpressionr-sproducesarelationcontainingthosetuplesinrbutnotins.4)SetDifferenceOperationNotation:r–sDefinedas:

r–s={t|t

randts}Setdifferencesmustbetakenbetweencompatiblerelations.randsmusthavethesamearityattributedomainsofrandsmustbecompatibleExample:tofindallcoursestaughtintheFall2009semester,butnotintheSpring2010semester.Example：

course_id(

semester=“Fall”Λyear=2009(section))−

course_id(

semester=“Spring”Λyear=2010(section))Example2Relationsr,s:r–s:5)Cartesian-ProductOperationTheCartesian-productoperationallowsustocombineinformationfromanytworelations.5)Cartesian-ProductOperationNotation:rxsDefinedas:

rxs={tq|trandqs}

Assumethatattributesofr(R)ands(S)aredisjoint.(Thatis,RS=).Ifattributesofr(R)ands(S)arenotdisjoint,thenrenamingmustbeused.5)Cartesian-ProductOperation–ExampleRelationsr,s:rx

s:exampleABCa1b1c1a1b2c2a2b2c1DEFa1b2c2a1b3c2a2b2c1RSABCa1b1c1a1b1c1a1b1c1a1b2c2a1b2c2a1b2c2a2b2c1a2b2c1a2b2c1DEFa1b2c2a1b3c2a2b2c1a1b2c2a1b3c2a2b2c1a1b2c2a1b3c2a2b2c1RxsR1∪R2,R1-R2,R2-R1,R1∩R2R1×R2,R1-R3,R1×R3

6)CompositionofOperationsCanbuildexpressionsusingmultipleoperationsExample:A=C(rxs)rxs7)RenameOperationAllowsustoname,andthereforetoreferto,theresultsofrelational-algebraexpressions.Allowsustorefertoarelationbymorethanonename.Example:

x(E)

returnstheexpressionEunderthenameX7)RenameOperationIfarelational-algebraexpressionEhasarityn,then

returnstheresultofexpressionEunderthenameX,andwiththeattributesrenamedtoA1

,A2

,….,An

.exampleFindthelargestsalaryintheuniversityAnswer:Step1:findinstructorsalariesthatarelessthansomeotherinstructorsalary(i.e.notmaximum)usingacopyofinstructorunderanewnamedinstructor.salary(

instructor.salary<d,salary

(instructorxd

(instructor)))Step2:Findthelargestsalarysalary(instructor)–

instructor.salary

(

instructor.salary<d,salary

(instructorxd

(instructor)))

Example2FindthenamesofallinstructorsinthePhysicsdepartment,alongwiththecourse_idofallcoursestheyhavetaughtQuery1:instructor.ID,course_id

(dept_name=“Physics”(

instructor.ID=teaches.ID(instructorxteaches)))Query2instructor.ID,course_id

(instructor.ID=teaches.ID

(

dept_name=“Physics”(instructor)xteaches))AdditionalOperationsWedefineadditionaloperationsthatdonotaddanypowertotherelationalalgebra,butthatsimplifycommonqueries.SetintersectionNaturaljoinAssignmentOuterjoin8)Set-IntersectionOperationNotation:r

sDefinedas:r

s={t|t

randt

s}Assume:r,shavethesamearity

attributesofrandsarecompatibleNote:r

s=r–(r–s)8)Set-IntersectionOperation–ExampleRelationr,s:r

s9)Natural-JoinOperationThenatural-joinisabinaryoperationthatallowsustocombinecertainselectionandacartesianproductintooneoperation.Thenatural-joinoperationformsaCartesianproductofitstwoarugments,performsaselectionforcingequalityonthoseattributesthatappearinbothrelationschemas,andfinallyremovesduplicateattributes.

Notation:rs9)Natural-JoinOperationrsisarelationonschemaR

Sobtainedasfollows:R

S={trts|trR∧tsS∧tr[B]=ts[B]}9)Natural-JoinOperationExample:R=(A,B,C,D)S=(E,B,D)Resultschema=(A,B,C,D,E)r

sisdefinedas:

r.A,r.B,r.C,r.D,s.E(r.B=s.Br.D=s.D(rxs))9)NaturalJoinExampleRelationsr,s:rsexampleFindthenamesofallinstructorsintheComp.Sci.departmenttogetherwiththecoursetitlesofallthecoursesthattheinstructorsteach.Analysis:Seediagraminword……

name,title(

dept_name=“Comp.Sci.”(instructor

teaches

course))9)NaturalJoinNaturaljoinisassociative(instructorteaches)courseisequivalentto

instructor(teachescourse)Naturaljoiniscommutativeinstructteachesisequivalentto

teachesinstructorexampleABCa1b15a1b26a2b38a2b412BEb13b27b310b32b52RSR

S

ABCEa1b153a1b267a2b3810a2b382ThethetajoinThethetajoinoperationisavariantofthenatural-joinoperationthatallowsustocombineaselectionandaCartesianproductintoasingleoperation.Notation:rs=ᶱ(rxs)ᶱexampleABCa1b15a1b26a2b38a2b412BEb13b27b310b32b52RSR

S

C<EAR.BCS.BEa1b15b27a1b15b310a1b26b27a1b26b310a2b38b310ModificationoftheDatabaseThecontentofthedatabasemaybemodifiedusingthefollowingoperations:DeletionInsertionUpdatingAlltheseoperationscanbeexpressedusingtheassignmentoperatorAssignmentoperationTheassignmentoperation()providesaconvenientwaytoexpresscomplexqueries.Writequeryasasequentialprogramconsistingofaseriesofassignmentsfollowedbyanexpressionwhosevalueisdisplayedasaresultofthequery.Assignmentmustalwaysbemadetoatemporaryrelationvariable.exampletemp1←RxStemp2←r.A1=s.A1^r.A2=s.A2^…^r.An=s.An(temp1)Result=

RUS(temp2)

OuterjoinAnextensionofthejoinoperationthatavoidslossofinformation.Computesthejoinandthenaddstuplesformonerelationthatdoesnotmatchtuplesintheotherrelationtotheresultofthejoin.Usesnullvalues:nullsignifiesthatthevalueisunknownordoesnotexistAllcomparisonsinvolvingnullare(roughlyspeaking)falsebydefinition.WeshallstudyprecisemeaningofcomparisonswithnullslaterOuterjoinexampleRelationinstructor1Relationteaches1IDcourse_id101011212176766CS-101FIN-201BIO-101Comp.Sci.FinanceMusicIDdept_name101011212115151nameSrinivasanWuMozartOuterjoinexampleLeftOuterJoininstructorteachesJoininstructorteachesIDdept_name1010112121Comp.Sci.Financecourse_idCS-101FIN-201nameSrinivasanWuIDdept_name101011212115151Comp.Sci.FinanceMusiccourse_idCS-101FIN-201nullnameSrinivasanWuMozartOuterjoinexampleFullOuterJoininstructorteachesRightOuterJoininstructorteachesIDdept_name101011212176766Comp.Sci.Financenullcourse_idCS-101FIN-201

BIO-101nameSrinivasanWunullIDdept_name10101121211515176766Comp.Sci.FinanceMusicnullcourse_idCS-101FIN-201

null

BIO-101nameSrinivasanWuMozartnullAggregationfunctionAggregationfunctiontakesacollectionofvaluesandreturnsasinglevalueasaresult.

avg:averagevalue

min:minimumvalue

max:maximumvalue

sum:sumofvalues

count:numberofvaluesAggregationfunctionAggregateoperationinrelationalalgebra

Eisanyrelational-algebraexpressionG1,G2…,Gnisalistofattributesonwhichtogroup(canbeempty)EachFi

isanaggregatefunctionEachAi

isanattributenameexampleRelationr:ABC77310

sum(c)(r)sum(c)27

avg(c)(