Select Operation Denoted selection condition c R Filters out tuples from r that don t match c c is a boolean combination of comparison clauses Terms in c are attributes or constants c makes sense only for one tuple at a time CSC 742 Database Management Systems Topic 7 Relational Algebra Spring 2002 CSC 742 DBMS by Dr Peng Ning 1 Relational Algebra input 1 relations output 1 relation independent of implementation procedural nesting of an expression defines the essential order of operations independent of implementation intuitively like a virtual machine for databases CSC 742 DBMS by Dr Peng Ning 2 Achtung 4 SSN Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 45000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James 56000 555 44 5555 English Joyce 53000 salary 50000 Salary Spring 2002 CSC 742 DBMS by Dr Peng Ning 5 Achtung is not the same as SELECT in SQL A series of s can be reordered or joined into one cond1 cond2 condn R cond1 AND cond2 AND condn R Operations are written in terms of R S etc referring to schema names applied to specific instances Spring 2002 CSC 742 DBMS by Dr Peng Ning Salary Offers operations defined at a high level Spring 2002 Spring 2002 CSC 742 DBMS by Dr Peng Ning 3 Spring 2002 CSC 742 DBMS by Dr Peng Ning 6 1 Project Rename Operation Denoted attribute list R Returns a relation that has only the attributes in the list all the tuples in r restricted to these attributes except that duplicates are removed Change the name of a relation the names of attributes or all of the above S R B1 B2 Bn R S B1 B2 Bn R Spring 2002 CSC 742 DBMS by Dr Peng Ning 7 Spring 2002 Salary SSN Lname Fname Salary SSN Lname Fname Salary 111 22 3333 Smith John 30000 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 45000 121 23 3333 Wong Frank 45000 153 32 1342 Wallace Jennifer 43000 153 32 1342 Wallace Jennifer 43000 154 33 3333 Borg James 56000 154 33 3333 Borg James 56000 555 44 5555 English Joyce 53000 555 44 5555 English Joyce 53000 CurrentSalary Salary B1 B2 B3 B4 Salary CurrentSalary B1 B2 B3 B4 Salary Lname Fname Lname Fname Salary Salary Lname Fname salary 50000 Salary Spring 2002 CSC 742 DBMS by Dr Peng Ning 8 Conventions Spring 2002 Relations may be temporary named or anonymous predefined in the schema Attributes may be renamed Define a new relation 11 Union Intersection Difference Apply only on union compatible arguments same degree same domains for each matching pair of attributes Attribute names come from first argument A B Spring 2002 CSC 742 DBMS by Dr Peng Ning Set Operations 10 Salary Lname Fname Salary Salary CSC 742 DBMS by Dr Peng Ning CSC 742 DBMS by Dr Peng Ning 9 Spring 2002 CSC 742 DBMS by Dr Peng Ning 12 2 EngineeringSalary SSN Theta Join Lname Fname Salary 111 22 3333 Smith John 30000 121 23 3333 Wong Frank 45000 153 32 1342 Wallace Jennifer 43000 R conditionS Equivalent to a Cartesian product nested within a select condition R S Tuples with null values for any attribute in condition are eliminated AccountingSalary SSN Lname Fname Salary 154 33 3333 Borg James 56000 555 44 5555 English Joyce 53000 Spring 2002 CSC 742 DBMS by Dr Peng Ning 13 Spring 2002 CSC 742 DBMS by Dr Peng Ning Employee Cartesian Product Also called cross product or cross join Notated R S All possible pairings All the attributes renamed if necessary lots of tuples r s r s Not to be used except in defining or simplifying expressions Spring 2002 CSC 742 DBMS by Dr Peng Ning Employee Dependent Lnane SSN Fname Lnane ESSN Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Joyce White 222 33 4444 Dependent Fname Lnane SSN Fname Lnane ESSN Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Joyce White 222 33 4444 Employee SSN ESSN Dependent 14 Fname 16 Fname Lnane SSN D FName D Lnane Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Spring 2002 CSC 742 DBMS by Dr Peng Ning D ESSN 17 Equijoin Theta join where only may be used as the comparison operator in the join condition Employee Dependent Fname Lnane SSN D FName D Lnane Alice Zelaya 999 88 7777 Eric Zelaya Alice Zelaya 999 88 7777 Alex Wallace 111 22 3333 Jennifer Wallace 111 22 3333 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Joyce White 222 33 4444 Eric Zelaya 999 88 7777 Joyce White 222 33 4444 Alex Wallace 111 22 3333 Spring 2002 CSC 742 DBMS by Dr Peng Ning D ESSN 999 88 7777 15 Spring 2002 CSC 742 DBMS by Dr Peng Ning 18 3 Natural Join Outer Join Denoted R S or R S Basically an equijoin followed by removal of the superfluous attributes Equijoin results in pairs of attributes with identical values Natural join eliminates the second of each pair Matching attributes must have the same name Equality for all matching attributes is required Includes tuples in result even if no match is found The result tuples are padded with nulls Variants left right full Spring 2002 CSC 742 DBMS by Dr Peng Ning Employee 19 Spring 2002 CSC 742 DBMS by Dr Peng Ning Employee Dependent 22 Dependent Fname Lnane SSN Fname Lnane ESSN Fname Lnane SSN Fname Lnane ESSN Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Joyce White 222 33 4444 Joyce White 222 33 4444 Employee DFname DLname SSN Dependent Employee DFname DLname SSN Dependent Fname Lnane SSN DFName Alice Zelaya 999 88 7777 Eric Zelaya Jennifer Wallace 111 22 3333 Alex Wallace Spring 2002 DLnane CSC 742 DBMS by Dr Peng Ning 20 Join Lnane SSN DFName DLnane Zelaya 999 88 7777 Eric Zelaya Jennifer Wallace 111 22 3333 Alex Wallace Joyce White 222 33 4444 NULL NULL Spring 2002 CSC 742 DBMS by Dr Peng Ning 23 Outer Union The heart of the relational model Helps combine information from multiple tables Essential because there are no direct pointers or references in the model Spring 2002 Fname Alice CSC 742 DBMS by Dr Peng Ning 21 Applies on incompatible relations For attributes in only one input relation tuples of the other are padded with nulls Spring 2002 CSC 742 DBMS by Dr Peng Ning 24 4 Faculty Student Name SSN Rank Department Alice 999 88 7777 Professor CSC Jennifer 111 22 3333 Assistant Prof ECE Joyce 222 33 4444 Associate Prof CSC Name SSN Advisor Department Eric 999 88 7777 Alice CSC Alex 111 22 3333 Jennifer ECE Faculty OUTER UNION