Relational Algebra Lecture 7 1 Outline • Relational Algebra (Section 6.1) 2
Relational Algebra • Formalism for creating new relations from existing ones • Its place in the big picture: Declarative query Algebra Implementation language Relational algebra SQL, 3 relational calculus Relational Algebra • Five operators: – Union: � – Difference: - – Selection: s – Projection: P – Cartesian Product: � • Derived or auxiliary operators: – Intersection, complement – Joins (natural,equi-join, theta join, semi-join) – Renaming: r 4
1. Union and 2. Difference • R1 � R2 Example: – ActiveEmployees � RetiredEmployees • R1 – R2 Example: – AllEmployees � RetiredEmployees 5 What about Intersection? • It is a derived operator R1 � R2 = R1 – (R1 – R2) • Also expressed as a join (will see later) Example – UnionizedEmployees � RetiredEmployees 6
3. Selection • Returns all tuples which satisfy a condition • Notation: s c (R) (sometimes also � ) • Examples – s Salary > 40000 (Employee) condition – s name = “Smith” (Employee) • The condition c can be =, <, � , >, � , <> [in SQL: SELECT * FROM Employee WHERE Salary > 40000] 7 Selection Example Employee SSN Name DepartmentID Salary 999999999 John 1 30,000 777777777 Tony 1 32,000 888888888 Alice 2 45,000 Find all employees with salary more than $40,000. s Salary > 40000 (Employee) SSN Name DepartmentID Salary 888888888 Alice 2 45,000 8
4. Projection • Eliminates columns, then removes duplicates • Notation: P A1,…,An (R) (sometimes � ) • Example: attributes – project to social-security number and names: – P SSN, Name (Employee) – Output schema: Answer(SSN, Name) [In SQL: SELECT DISTINCT SSN, Name FROM Employee] 9 Projection Example Employee SSN Name DepartmentID Salary 999999999 John 1 30,000 777777777 Tony 1 32,000 888888888 Alice 2 45,000 P SSN, Name (Employee) SSN Name 999999999 John 777777777 Tony 888888888 Alice 10
5. Cartesian Product • Combine each tuple in R1 with each tuple in R2 • Notation: R1 � R2 • Example: – Employee � Dependents • Very rare in practice; mainly used to express joins [In SQL: SELECT * FROM R1, R2] 11 Cartesian Product Example Employee Name SSN John 999999999 Tony 777777777 Dependents EmployeeSSN Dname 999999999 Emily 777777777 Joe Note the output Employee x Dependents Name SSN EmployeeSSN Dname John 999999999 999999999 Emily John 999999999 777777777 Joe Tony 777777777 999999999 Emily Tony 777777777 777777777 Joe 12
Cartesian Product and SQL select * from Employee, Dependents; +------+-----------+-------------+-------+ | Name | SSN | EmployeeSSN | Dname | +------+-----------+-------------+-------+ | John | 999999999 | 999999999 | Emily | | Tony | 777777777 | 999999999 | Emily | | John | 999999999 | 777777777 | Joe | | Tony | 777777777 | 777777777 | Joe | +------+-----------+-------------+-------+ select * from Employee, Dependents where SSN=EmpoyeeSSN ; +------+-----------+-------------+-------+ | Name | SSN | EmployeeSSN | Dname | +------+-----------+--------------+-------+ | John | 999999999 | 999999999 | Emily | | Tony | 777777777 | 777777777 | Joe | 13 +------+-----------+-------------+-------+ Relational Algebra • Five operators: – Union: � – Difference: - – Selection: s – Projection: P – Cartesian Product: � • Derived or auxiliary operators: – Intersection, complement – Joins (natural,equi-join, theta join, semi-join) – Renaming: r 14
Renaming • Changes the schema, not the instance • Schema: R(A 1 , …, A n ) • Notation: r B1,…,Bn (R) • Example: – r LastName, SocSocNo (Employee) – Output schema: Answer(LastName, SocSocNo) [in SQL: SELECT Name AS LastName, SSN AS SocSocNo FROM Employee] 15 Renaming Example Employee Name SSN John 999999999 Tony 777777777 r LastName, SocSocNo ( Employee ) LastName SocSocNo John 999999999 Tony 777777777 16
Natural Join • Notation: R1 ! R2 • Meaning: R1 ! R2 = P A ( s C (R1 � R2)) – Equality on common attributes names • Where: – The selection s C checks equality of all common attributes – The projection eliminates the duplicate common attributes [in SQL: SELECT DISTINCT R1.A, R1. B, R2.C FROM R1, R2 WHERE R1.B = R2.B Schema: R1(A,B), R2(B,C)] 17 Natural Join Example Employee Name SSN John 999999999 Tony 777777777 Dependents SSN Dname 999999999 Emily 777777777 Joe Employee Dependents = P Name, SSN, Dname ( s SSN=SSN2 (Employee x r SSN2, Dname (Dependents)) Name SSN Dname John 999999999 Emily Tony 777777777 Joe 18
Natural Join • R= S= B C A B Z U X Y V W X Z Z V Y Z Z V A B C • R ! S= X Z U X Z V Y Z U Y Z V Z V W 19 Natural Join • Given the schemas R(A, B, C, D), S(A, C, E), what is the schema of R ! S ? • Given R(A, B, C), S(D, E), what is R ! S ? • Given R(A, B), S(A, B), what is R ! S ? 20
Theta Join • A join that involves a predicate • R1 ! q R2 = s q (R1 � R2) • Here q can be any condition 21 Eq-join • A theta join where q is an equality R1 ! A=B R2 = s A=B (R1 � R2) Equality on two different attributes • Example: – Employee ! SSN=SSN Dependents • Most useful join in practice (difference to natural join?) 22
Semijoin • R " S = P A1,…,An (R ! S) • Where A 1 , …, A n are the attributes in R • Example: – Employee " Dependents 23 Semijoin - Example Dept Employee DeptName Manager Name EmpId DeptName Sales Harriet Harry 3415 Finance Production Charles Sally 2241 Sales George 3401 Finance Harriet 2202 Sales Employee ! Dept Name EmpId DeptName Sally 2241 Sales Harriet 2202 Sales Only attributes of Employee are selected 24
Semijoins in Distributed Databases • Semijoins are used in distributed databases Dependents Employee SSN Dname Age . . . . . . SSN Name network . . . . . . Employee ! ssn=ssn ( s age>71 (Dependents)) T = P SSN s age>71 (Dependents) R = Employee " ! T 25 Answer = R ! Dependents Complex RA Expressions P name buyer-ssn=ssn pid=pid seller-ssn=ssn P ssn P pid s name=fred s name=gizmo Person Purchase Person Product 26
Application: Query Rewriting for Optimization sname sname rating > 5 bid=100 sid=sid (Scan; (Scan; sid=sid rating > 5 write to write to bid=100 temp T2) temp T1) Reserves Sailors Reserves Sailors The earlier we process selections, the fewer tuples we need to manipulate higher up in the tree (predicate pushdown) Disadvantages? 27 Algebraic Laws (Examples) • Commutative and Associative Laws – R � S = S � R, R � (S � T) = (R � S) � T ! ! ! ! ! ! – R S = S R, R (S T) = (R S) T • Laws involving selection – s C AND C � (R) = s C ( s C � (R)) = s C (R) � s C � (R) ! ! – s C (R S) = s C (R) S • When C involves only attributes of R • Laws involving projections – P M ( P N (R)) = P M,N (R) 28
Operations on Bags A bag = a set with repeated elements All operations need to be defined carefully on bags • {a,b,b,c} � {a,b,b,b,e,f,f}={a,a,b,b,b,b,b,c,e,f,f} • {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b} • s C (R): preserve the number of occurrences • P A (R): no duplicate elimination • Cartesian product, join: no duplicate elimination Important: Relational Engines work on bags, not sets! 29 Finally: RA has Limitations ! • Cannot compute “transitive closure” Name1 Name2 Relationship Fred Mary Father Mary Joe Cousin Mary Bill Spouse Nancy Lou Sister • Find all direct and indirect relatives of Fred • Cannot express in RA !!! Need to write C program 30
Conclusion • Relational algebra is important for SQL • We mainly need projects, selections, joins • Joins are typically time consuming for large databases 31
Recommend
More recommend
