2
22 L3 Relational Data Model Overview There are many tasks that can be understood as calculating some relation by combining the information in one or more relation instances Relational algebra defines some operators that can be used to express a calculation like that A central insight: a query that extracts information can be seen as calculating a relation from the current state of the database

3
33 L3 Relational Data Model Relational Algebra Users request information from a database using a query language Six basic and several additional operators Basic operations: Selection ( ) Selects a subset of rows from relation. Projection ( ) Extracts only desired columns from relation. Cross-product ( ) Allows us to combine two relations. Set-difference ( ) Tuples in reln. 1, but not in reln. 2. Union ( ) Tuples in reln. 1 or in reln. 2. Rename ( ) Allows us to rename one field to another name. Additional operations: Intersection, join, division: Not essential, but (very!) useful. The operators take one or more relations as inputs and give a new relation as result X _

5
55 L3 Relational Data Model Projection sname, rating (S2) age Deletes attributes that are not in projection list. Schema of result contains exactly the fields in the projection list, with the same names that they had in the (only) input relation. (S2)

8
88 L3 Relational Data Model Cross-Product Each row of S1 is paired with each row of R1. Result schema has one field per field of S1 and R1, with field names `inherited’ if possible. Conflict: Both S1 and R1 have a field called sid. Sometimes called Cartesian product

9
99 L3 Relational Data Model Renaming Allows us to name, and therefore to refer to, the results of relational-algebra expressions. Allows us to refer to a relation by more than one name. Notation 1:  x (E) returns the expression E under the name X Notation 2:  x (A1, A2, …, An) (E) returns the result of expression E under the name X, and with the attributes renamed to A 1, A2, …., An. (assumes that the relational-algebra expression E has arity n) Example C( 1  sid1, 5  sid2) ( S1XR1)

10
1010 L3 Relational Data Model Joins Condition Join : Result schema same as that of cross-product. Fewer tuples than cross-product, might be able to compute more efficiently Sometimes called a theta-join.

12
1212 L3 Relational Data Model Division Not supported as a primitive operator, but useful for expressing queries like: Find sailors who have served on all boats. Let A have 2 fields, x and y; B have only field y: A/B = i.e., A/B contains all x tuples (sailors) such that for every y tuple (boat) in B, there is an xy tuple in A. Or: If the set of y values (boats) associated with an x value (sailor) in A contains all y values in B, the x value is in A/B. In general, x and y can be any lists of fields; y is the list of fields in B, and x y is the list of fields of A.