Announcements• Exam Tuesday in class– Closed book• Program 2 due Friday– Turn in using D2L dropbox– Only turn in the 4 files specified in the assignment: (secondary_index.h, secondary_index.C, secIdxSearch.h,secIdxSearch.C)– Only latest files will be graded• Office hours today: 2-3, I will be in E285• Career Fair tomorrow: 9-1 in the Wisconsin room of the unionUNION, INTERSECT, and DIFFERENCEUNION, INTERSECT, and DIFFERENCERelational Algebra Operations from Set Theory: CARTESIAN PRODUCT• CARTESIAN (or CROSS) PRODUCT Operation– This operation is used to combine tuples from two relations in a combinatorial fashion.– Denoted by R(A1, A2, . . ., An) x S(B1, B2, . . ., Bm)– Result is a relation Q with degree n + m attributes:• Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order.– The resulting relation state has one tuple for each combination of tuples—one from R and one from S. – Hence, if R has nRtuples (denoted as |R| = nR), and S has nStuples, then R x S will have nR* nStuples.– The two operands do NOT have to be "type compatible”Relational Algebra Operations from Set Theory: CARTESIAN PRODUCT (cont.)• Generally, CROSS PRODUCT is not a meaningful operation– Can become meaningful when followed by other operations• Example (not meaningful):– FEMALE_EMPS ← σSEX=’F’(EMPLOYEE)– EMPNAMES ← πFNAME, LNAME, SSN (FEMALE_EMPS)– EMP_DEPENDENTS ← EMPNAMES x DEPENDENT• EMP_DEPENDENTS will contain every combination of EMPNAMES and DEPENDENT– whether or not they are actually relatedRelational Algebra Operations from Set Theory: CARTESIAN PRODUCT (cont.)• To keep only combinations where the DEPENDENT is related to the EMPLOYEE, we add a SELECT operation as follows• Example (meaningful):– FEMALE_EMPS ← σSEX=’F’(EMPLOYEE)– EMPNAMES ← πFNAME, LNAME, SSN (FEMALE_EMPS)– EMP_DEPENDENTS ← EMPNAMES x DEPENDENT– ACTUAL_DEPS ← σSSN=ESSN(EMP_DEPENDENTS)– RESULT ← πFNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS)• RESULT will now contain the name of female employees and their dependentsBinary Relational Operations: JOIN• JOIN Operation (denoted by )– The sequence of CARTESIAN PRODECT followed by SELECT is used quite commonly to identify and select related tuples from two relations– A special operation, called JOIN combines this sequence into a single operation– This operation is very important for any relational database with more than a single relation, because it allows us combine related tuples from various relations – The general form of a join operation on two relations R(A1, A2, . . ., An) and S(B1, B2, . . ., Bm) is:R <join condition>S– where R and S can be any relations that result from general relational algebra expressions.Binary Relational Operations: JOIN (cont.)• Example: Suppose that we want to retrieve the name of the manager of each department.– To get the manager’s name, we need to combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple. – We do this by using the join operation.– DEPT_MGR ← DEPARTMENT MGRSSN=SSN EMPLOYEE• MGRSSN=SSN is the join condition– Combines each department record with the employee who manages the department– The join condition can also be specified as DEPARTMENT.MGRSSN= EMPLOYEE.SSNExample of applying the JOIN operationDEPT_MGR ← DEPARTMENT MGRSSN=SSNEMPLOYEESome properties of JOIN• Consider the following JOIN operation:– R(A1, A2, . . ., An) S(B1, B2, . . ., Bm)R.Ai=S.Bj– Result is a relation Q with degree n + m attributes:• Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order.– The resulting relation state has one tuple for each combination of tuples—r from R and s from S, but only if they satisfy the join condition r[Ai]=s[Bj]– Hence, if R has nRtuples, and S has nStuples, then the join result will generally have less than nR* nStuples.– Only related tuples (based on the join condition) will appear in the resultSome properties of JOIN• The general case of JOIN operation is called a Theta-join: R Stheta• The join condition is called theta• Theta can be any general boolean expression on the attributes of R and S; for example:– R.Ai<S.Bj AND (R.Ak=S.Bl OR R.Ap<S.Bq)• Most join conditions involve one or more equality conditions “AND”ed together; for example:– R.Ai=S.Bj AND R.Ak=S.Bl AND R.Ap=S.BqBinary Relational Operations: EQUIJOIN• EQUIJOIN Operation• The most common use of join involves join conditions with equality comparisons only• Such a join, where the only comparison operator used is =, is called an EQUIJOIN.– In the result of an EQUIJOIN we always have one or more pairs of attributes (whose names need not be identical) that have identical values in every tuple. – The JOIN seen in the previous example was an EQUIJOIN.Binary Relational Operations: NATURAL JOIN Operation• NATURAL JOIN Operation – Another variation of JOIN called NATURAL JOIN — denoted by * — was created to get rid of the second (superfluous) attribute in an EQUIJOIN condition.• because one of each pair of attributes with identical values is superfluous– The standard definition of natural join requires that the two join attributes, or each pair of corresponding join attributes, have the same name in both relations– If this is not the case, a renaming operation is applied first.Binary Relational Operations NATURAL JOIN (contd.)• Example: To apply a natural join on the DNUMBER attributes of DEPARTMENT and DEPT_LOCATIONS, it is sufficient to write: – DEPT_LOCS ← DEPARTMENT * DEPT_LOCATIONS• Only attribute with the same name is DNUMBER• An implicit join condition is created based on this attribute:DEPARTMENT.DNUMBER=DEPT_LOCATIONS.DNUMBER• Another example: Q ← R(A,B,C,D) * S(C,D,E)– The implicit join condition includes each pair of attributes with the same name, “AND”ed together:• R.C=S.C AND R.D.S.D– Result keeps only one attribute of each such pair:• Q(A,B,C,D,E)Example of NATURAL JOIN operationRelational Algebra Overview• Relational Algebra consists of several groups of operations– Unary Relational Operations• SELECT (symbol: σ (sigma))• PROJECT (symbol: π (pi))• RENAME (symbol: ρ (rho))– Relational Algebra Operations From Set Theory• UNION ( ∪ ), INTERSECTION ( ∩ ), DIFFERENCE (or MINUS, – )• CARTESIAN PRODUCT ( x )– Binary Relational