7 - 1Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] 8040 -Relational AlgebraRelational Algebra OutlineWhy Relational Algebra?Traditional Set Operators. Union Intersection Difference Cartesian ProductSpecial Relational Operators. Restriction (Select) Projection Join Division7 - 2Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] Relational Algebra?Purposes: Provides a theoretical foundation for relational data manipulation. Helps the study of query optimization. Is an important component of relational data model.Relational algebra is based on the mathematical set theory Operands are relations, attributes, and tuples. Operators are the extension of the set theory.Union CompatibleTwo relations, A and B, are union-compatible iff they have the same degree and you can find corresponding attributes in A and B having been defined in the same domainIn-class exercise:Supplier (S#: Did; SName: Dname; Status: Dstatus; City: Dcity);Preferred_Supplier (SFullName: Dname; SupID: Did; SCity: Dcity; Status: Dstatus);Part (P#: Dnumber; Name: Dpartname; Color: Dpartcolor)Are Supplier and Preferred_Supplier union compatible?Are Supplier and Part union compatible?7 - 3Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] Set Operators Union Intersection Difference Cartesian ProductBNF Notation: •ALL CAPS or boldface indicate required and reserved words•Mixed case or italics indicate user-supplied values•Square brackets [ ] indicates optional•Parentheses are significant. Must be included if shown•Bar character (“|”) indicates an or boolean conditionUnion, Intersection, & DifferenceABABABA and B must be two compatible relationsUnion (  )A  B = {t | t in A or t in B}UNION relation-1 WITH relation-2 GIVING relation-3Intersection (  )A  B = { t | t in A and t in B}INTERSECT relation-1 WITH relation-2 GIVING relation-3Difference ( - )A - B = {t | t in A and t not in B}SUBTRACT relation-1 FROM relation-2 GIVING relation-37 - 4Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] ISupplier  Preferred_Supplier =?Supplier  Preferred_Supplier =?Supplier - Preferred_Supplier=?Supplier  Part =?S#s1s3s4SNameSmithBlakeClarkStatus203020CityLondonParisLondonSupplierPreferred_Supplierp#p1p2p3namexxxyyyzzzcolorRedGreenBrownPart:In-class exercise:S#s2s3S4SNameJonesBlakeClarkStatus103020CityParisParisLondonCartesian Producta1a2b1b2b3b1b2b3a1a212xyamgsnjmrm1 x a s m1 x m n r1 x g j m2 y a s m2 y m n r2 y g j mABX=• Need not be union compatible• Resulting Cardinality is the product of the two• Resulting Degree is the addition of the twoPRODUCT relation-1 BY relation-2 GIVING relation-37 - 5Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] ExerciseS#s1s2s3SNameSmithJonesBlake......S#s1s1s2s1P#p1p1P3p2J#j1j4J1j1......SupplierSPJSpecial Relational Operators Restriction (Selection of rows/tuples) Projection (Picking out certain columns/attributes) Join (a Cartesian product with a q condition…more later) Division7 - 6Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] of tuples that satisfy a given Boolean condition qSq( R ) = {t | t in R and q is true}SELECT relation-1 WHERE condition GIVING relation-2List all suppliers with Status > 10SELECT Supplier WHERE Status>10 GIVING tempSStatus > 10( Supplier )SupplierResult?Complex RestrictionList all suppliers with Status > 10 and who are from London.answer ←SStatus > 10 and City = ‘London’(Supplier ) – math syntaxSELECT Supplier WHERE Status>10 and City=‘London’ GIVING answer – Eng. syntaxSupplierResult?SStatus > 10 or City = ‘London’ ( Supplier ) = ? SStatus = 10 and City = ‘London’ ( Supplier ) = ? (Careful)7 - 7Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] RestrictionEquivalence Properties:Sxand y ( R ) = Sx( R )  Sy( R )Sx or y ( R ) = Sx( R )  Sy( R )Snot x( R ) = R - Sx( R )SStatus > 10 and City = ‘London’ (Supplier) = SStatus > 10 (Supplier)  SCity = ‘London’ (Supplier)SStatus > 10 or City = ‘London’ (Supplier) = SStatus > 10 (Supplier)  SCity = ‘London’ (Supplier)Snot Status > 10 (Supplier) = Supplier - SStatus > 10(Supplier)SupplierProjectionA projection of a relation R on attributes A1, A2, ..., An, is a relation which consists attributes, A1, A2, ..., An.PA1,A2,...An ( R ) = R'( A1, A2, ..., An)PROJECT attribute-1 [,attribute-2, attribute-3...] FROM relation-1 GIVING relation-2List S#, Supplier name, and status of all suppliers.P S#, Sname, Status( SUPPLIER )SupplierResult?7 - 8Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected] Joins (Equijoin)The equal join of two product-compatible relations (join attributes must be from the same domain), R1and R2based on the join attribute R1.x and R2.y isR1Xx=yR2= Sx=y( R1 R2)JOIN relation-1 USING attribute-1 WITH relation-2 USING attribute-2 GIVING relation-3List all suppliers that are related to Project j1.XSupplier.S# = SPJ.S#Results?S#s1s2s3SNameSmithJonesBlake......SupplierS#s1s1s2P#p1p1p3J#j1j4j1......SPJNatural JoinsAn equijoin by removing one of the repeating joining attributes…why?By default, "Join" refers to the natural joinSupplier XSupplier.S#=SPJ.S#SPJ ?7 - 9Copyright © 2012Robinson College of Business, Georgia State UniversityDavid S. McDonald Director of Emerging TechnologiesTel: 404-413-7368; e-mail: [email protected]…Greater-Than Join (A Xx > yB)The comparison operator is '>'. A joined