双语数据库ppt课件

1,RelationalAlgebra(关系代数),Operators（操作符）ExpressionTrees（表达式树）,2,Whatisan“Algebra”(代数),Analgebraconsistsofoperatorsandatomicoperands:Operands-arevariableslikeXandconstantslike15.Operators-aretheusualarithmeticones:addition,subtraction,multiplicationanddivision.,X,+,15,Operands,Operator,3,WhatisRelationalAlgebra?,RelationalAlgebraisanotherexampleofanalgegra.Itsatomicoperandsare:1.Variablesthatstandforrelations2.Constants,whicharefiniterelations,4,OverviewofRelationalAlgebra,RelationalAlgebraisanotherexampleofanalgegra.Analgebrawhoseoperandsarerelationsorvariablesthatrepresentrelations.Operatorsaredesignedtodothemostcommonthingsthatweneedtodowithrelationsinadatabase.Theresultisanalgebrathatcanbeusedasaquerylanguageforrelations.,R,S,5,FourClassesofRelationalAlgebra(四类关系代数),Union,intersection,difference(并,交,叉)Usualsetoperations,butbothoperandsmusthavethesamerelationschema.Selection,Projection(选择，投影)Removepartsofarelation.CartesianProduct,join(笛卡尔积,连接)CombinethetuplesoftworelationsRenaming(重命名)Changetherelationschema,i.e.,thenamesofattributesorthenameoftherelationitself.,6,SetOperatorsonRelations(关系集合运算符),RUSunion,thesetofelementsthatareinRorSorboth.RSintersection,thesetofelementsthatareinbothRandS.RSdifference,thesetofelementsthatareinRbutnoinS.RequiredRandSmusthaveschemawithidenticalsetofattributes,andthetypes(domains)mustbethesame.Beforecalculation,thecolumnsofRandSmustbeordered.,7,8,Projection（投影）,TheprojectionoperatorisusedtoproducefromarelationRanewrelationthathasonlysomeofRscolumns.L(R)LisalistofattributesfromtheschemaofR.TheresultisanewrelationthathasonlysomeofRscolumns.Eliminateduplicatetuples,ifany.,9,Example,title,year,length(Movies),Result,10,Example,RelationSells:barbeerpriceJoesBud2.50JoesMiller2.75SuesBud2.50SuesMiller3.00,11,Selection（选择）,Theselectionoperator,appliedtoarelationR,producesanewrelationwithasubsetofRstuples.C(R)Cisacondition(asin“if”statements)thatreferstoattributesofR.TheresultisanewrelationwithasubsetofRstuplesthatsatisfyC.,12,Example,lentgh=100ANDstudioName=FOX(Movie),Result,13,Example,RelationSells:barbeerpriceJoesBud2.50JoesMiller2.75SuesBud2.50SuesMiller3.00,14,CartesianProduct（笛卡尔积）,Orcross-product(交叉积),orjustproduct(积)RSPair(配对)eachtuplet1ofRwitheachtuplet2ofS.Result:anewrelationwithnewtuples,eachofthemconcatenation(连接)apairoft1t2,theattributesofRandSareinordered.Beware:attributeAofthesamenameinRandS:useR.AandS.A.,15,Example:R3:=R1R2,R1AB1234R2BC5678910,16,NaturalJoin(自然连接),RSPaironlythosetuplesfromRandSthatagreeinwhateverattributesarecommon(共同的)totheschemasofRandS.Eliminatingonecopyofeachpairofequatedattributes.,17,ABCa1b15a1b26a2b38a2b412,R,BEb13b27b310b32b52,S,AR.BCS.BEa1b15b27a1b15b310a1b26b27a1b26b310a2b38b310,RCES,AR.BCS.BEa1b15b13a1b26b27a2b38b310a2b38b32,等值连接,a1b153a1b267a2b3810a2b382,自然连接,RR.B=S.BS,RS,Example:NaturalJoin,18,Example:NaturalJoin,SellsbarbeerpriceBarsbaraddrJoesBud2.50JoesMapleSt.JoesMiller2.75SuesRiverRd.SuesBud2.50SuesCoors3.00BarInfo:=SellsBarsNote:BhasbecomeBars.bartomakethenaturaljoin“work.”,19,Example:NaturalJoin,(a)RelationU,(b)RelationV,(c)ResultUV,20,Theta-Join(连接),RCSTaketheproductRS.ThenapplyCtotheresult.,21,ABCa1b15a1b26a2b38a2b412,R,BEb13b27b310b32b52,S,AR.BCS.BEa1b15b27a1b15b310a1b26b27a1b26b310a2b38b310,RC0),那么RS是一个(r-s)元的元组的集合,(RS)是满足下列条件的最大关系:其中每个元组t与S中每个元组u组成的新元组必在关系R中.具体过程:(1)T=r-s(R)(2)W=(TS)-R(3)V=r-s(W)(4)RS=T-V,33,例:RS,RSRS,34,Precedenceofrelationaloperators:(关系运算符的优先级),Unary(一元的)operators-select,project-havehighestprecedence,bindfirst.Thencomeproductsandjoins.Thenintersection.Finally,unionandsetdifferencebindlast.Butyoucanalwaysinsertparenthesestoforcetheorderyoudesire.,35,例：学生课程数据库，包括Student，Course，SC三个关系,SnoSnameSsexSageSdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS,Student,CnoCnameCpqoCcredit1数据库542数学2信息系统144操作系统635数据结构746数据处理27Pascal语言64,Course,SnoCnoGrade9500119295001285950013889500229095002380,SC,36,Sname,Sdept(Student),Sdept=IS(Student),Sno(Cno=1(SC),Sno(Cno=1orCno=3(SC),Student(Sno,Sname,Ssex,Sage,Sdept)Course(Cno,Cname,Cpqo,Ccredit)SC(Sno,Cno,Grade),37,教学数据库,教师关系T（T#,TNAME,TITLE）课程关系C（C#，CNAME，T#）学生关系S（S#，SNAME，AGE，SEX）选课关系SC（S#,C#,SCORE）,1.检索学习课程号为C2课程的学生学号与成绩。2.检索学习课程号为C2课程的学生学号与姓名。3.检索至少选修LIU老师所授课程中一门课程的学生学号与姓名4.检索选修课程号为C2或C4课程的学生学号。5.检索选修课程号为C2和C4课程的学生学号。6.检索不学C2课程的学生姓名与年龄。,38,Example（1）:,Teachingdatabaseasfollows:Writethefollowingqueriesinrelationalalgebra.(写关系代数表达式),1.检索学习课程号为C2课程的学生学号与成绩。S#,SCORE(C#=C2(SC)2.检索学习课程号为C2课程的学生学号与姓名。S#,SNAME(C#=C2(SC)S)3.检索至少选修LIU老师所授课程中一门课程的学生学号与姓名S#,SNAME(TNAME=LIU(T)CSCS),1,3(2=C2(SC),39,Example（2）:,4.检索选修课程号为C2或C4课程的学生学号。S#(C#=C2ORC#=C4(SC)5.检索选修课程号为C2和C4课程的学生学号。S#(C#=C2(SC)S#(C#=C4(SC)6.检索不学C2课程的学生姓名与年龄。SNAME,AGE(S)-SNAME,AGE(