60 likes | 222 Views
The relational algebra and calculus. … SQL. The big 3: . Selection and projection are unary ops Join is binary Selection is based on a formula and returns a table that contains all tuples from a given table where the formula is valid
E N D
The big 3: • Selection and projection are unary ops • Join is binary • Selection is based on a formula and returns a table that contains all tuples from a given table where the formula is valid • Projection returns a table consisting of a subset of attributes from a given table, with dupes removed • Join creates tuples with attributes from two given tables, where a specific attribute in one matches a specific attribute in another (often a PK, FK pair)
Algebraic closure • Any relational algebra operation returns a legal derived table • The set operators are also part of the algebra • From a formal perspective, the join operator is not a minimal operator, and is therefore represented as a cross product followed by a selection (where the PK equals the FK) • Note that joins are symetric
Joins can be generalized • Complex join conditions • Non-equi joins • A “natural” join is based on matching all attributes with equal names in both tables • “Outer” join creates null-packed tuples when tuples on the left do not match any on the right; there is also a right outer join
The calculus • It is a tuple calculus, not a domain calculus • SQL is equivalent • Select From Where • The part after the Where is declarative • A tuple calculus (SQL) • Notice that the variables are indeed tuples • Note that set operators often act on tables that are being created in the query
More • IN operator is “element of” • EXISTS • Nesting • FOR ALL • FOR SOME • Putting computations in the SELECT clause • COUNT, SUM, AVG, MAX, MIN operators