Relational Algebra What? Why? • • • • • • Similar to normal algebra (as in 2+3*x-y), except we use relations as values instead of numbers, and the operations and operators are different. Not used as a query language in actual DBMSs. (SQL instead.) The inner, lower-level operations of a relational DBMS are, or are similar to, relational algebra operations. We need to know about relational algebra to understand query execution and optimization in a relational DBMS. Some advanced SQL queries requires explicit relational algebra operations, most commonly outer join. Relations are seen as sets of tuples, which means that no duplicates are allowed. SQL behaves differently in some cases. Remember the SQL keyword distinct. SQL is declarative, which means that you tell the DBMS what you want, but not how it is to be calculated. A C++ or Java program is procedural, which means that you have to state, step by step, exactly how the result should be calculated. Relational algebra is (more) procedural than SQL. (Actually, relational algebra is mathematical expressions.) Set operations Relations in relational algebra are seen as sets of tuples, so we can use basic set operations. Review of concepts and operations from set theory • • • • • • • • • • • set element no duplicate elements (but: multiset = bag) no order among the elements (but: ordered set) subset proper subset (with fewer elements) superset union intersection set difference cartesian product
Projection Example: The table E (for EMPLOYEE) nr name salary 1 John 100 5 Sarah 300 7 Tom 100 SQL Result Relational algebra salary select salary from E 100 PROJECTsalary(E) 300 nr salary select nr, salary from E 1 100 5 300 PROJECTnr, salary(E) 7 100 Note that there are no duplicate rows in the result. Selection The same table E (for EMPLOYEE) as above. SQL Result Relational algebra select * from E where salary < 200 select * from E where salary < 200 and nr >= 7 nr name salary 1 John 100 SELECTsalary < 200(E) 7 Tom 100 nr name salary 7 Tom 100 SELECTsalary < 200 and nr >= 7(E) Note that the select operation in relational algebra has nothing to do with the SQL keyword select. Selection in relational algebra returns those tuples in a relation that fulfil a condition, while the SQL keyword select means "here comes an SQL statement".
Relational algebra expressions SQL Result select name, salary from E where salary < 200 Relational algebra PROJECTname, salary (SELECTsalary < 200(E)) name salary or, step by step, using an intermediate result John 100 Tom 100 Temp <- SELECTsalary < 200(E) Result <- PROJECTname, salary(Temp) Notation The operations have their own symbols. The symbols are hard to write in HTML that works with all browsers, so I'm writing PROJECT etc here. The real symbols: Operation My HTML Projection Operation My HTML PROJECT Cartesian product X Selection SELECT Join JOIN Renaming RENAME Left outer join LEFT OUTER JOIN Union UNION Right outer join RIGHT OUTER JOIN Full outer join FULL OUTER JOIN Semijoin SEMIJOIN Intersection INTERSECTION Assignment <- Symbol Example: The relational algebra expression which I would here write as PROJECTNamn ( SELECTMedlemsnummer < 3 ( Medlem ) ) should actually be written Symbol
Cartesian product The cartesian product of two tables combines each row in one table with each row in the other table. Example: The table E (for EMPLOYEE) enr 1 2 3 ename Bill Sarah John dept A C A Example: The table D (for DEPARTMENT) dnr A B C dname Marketing Sales Legal SQL Result Relational algebra enr ename dept dnr select * from E, D • • • dname 1 Bill A A Marketing 1 Bill A B Sales 1 Bill A C Legal 2 Sarah C A Marketing 2 Sarah C B Sales 2 Sarah C C Legal 3 John A A Marketing 3 John A B Sales 3 John A C Legal Seldom useful in practice. Usually an error. Can give a huge result. EXD