1. Relational Algebra For CS420

2. Relational Algebra • The relational algebra is a collection of operators that take relations as their operands and return a relation as their result • Eight operators • Union, intersect, difference • Restrict, project, • Cartesian product, Join, divide • The operators are “read only”

3. The operations are closed • The output from any relational operator is another relation: the closure property • Relational expressions can be nested (analogously to arithmetic expressions) • RENAME changes the name of an attribute without changing its type or content

4. Union - (A UNION B) or A + B • Union operates on two sets and returns a set that contains all elements belonging to either • Both sets must be of the same type - formerly known as union compatibility • Relations cannot have duplicate tuples; we say loosely that UNION “eliminates duplicates”

5. Intersect and Difference • Intersect operates on two sets and returns a set that contains all tuples belonging to both • Difference operates on two sets and returns a set containing all tuples occuring in one but not the other, using MINUS • For both Intersect and Difference, the sets operated upon must be of the same type - formerly known as union compatibility • (A INTERSECT/MINUS B) OR A - B

6. Semantics of the Original Algebra –Intersect and Difference

7. Cartesian Product • A Cartesian Product is the set of all ordered pairs such that in each pair, the first element comes from the first set, and the second element comes from the second set • However, since the result of a relational operator is a relation, the result of each pair is a single tuple containing all the elements of both of the source tuples • (A TIMES B) or A x B

8. Cartesian Product

9. Restrict or Select • Yields a horizontal subset • Format: A WHERE p • p is called the restriction condition • p is a predicate, and returns boolean for each tuple

10. Restrict and others • OR and Union • A WHERE p1 or p2 • (A WHERE p1) UNION (A WHERE p2) • AND and INTERSECT • A WHERE p1 AND p2 • (A WHERE p1) INTERSECT (A WHERE p2) • NOT and MINUS • A WHERE NPT (p) • A MINUS (A WHERE p)

11. Project • Yields a vertical subset • The general form is a comma list of attributes to be kept in the result • For all attributes kept, all tuples are kept • Format A{C1, C2}

12. Join – Natural Join • Join is a Product followed by Select • When unqualified, join means “natural join” • Format: A Join B on A.C1 = B.C2

13. Examples • Get supplier names for suppliers who supply part p2 ((SP Join S on S.SNO = SP.SNO) Where PNO = ‘P2’) {SNAME} • Get supplier names for suppliers who supply a red part (((P WHERE COLOR = ‘Red’) JOIN SP on SP.PNO = P.PNO) JOIN S on SP.SNP = S.SNO) {SNAME} • Get supplier names for suppliers who supply all parts (S WHERE (SP WHERE SP.SNO = S.SNO) {PNO} = P {PNO}) {SNAME} • Get supplier names for suppliers who do not supply P2 ((S{SNO} MINUS (SP WHERE PNO = ‘P2’) {SNO}) Join S {SNAME}

14. More Examples • Get pairs of supplier numbers such that the suppliers are located in the same city ( ( (S RENAME SNO as SA) {SA, CITY} JOIN // on city = city (S RENAME SNO as SB) {SB, CITY} ) WHERE SA < SB ) {SA, SB} S JOIN S as S1 on S.CITY=S1.CITY WHERE S.SNO < S1.SNO {S.SNO, S1.SNO} (S RENAME SNO as SA) {SA, CITY} as T1 (S RENAME SNO as SB) {SA, CITY} as T2 T1 Join T2 as T3 // Join condition is T1.City = T2.City T3 Where SA < SB {SA, SB}