Relational Algebra. The basic set of operations for the relational model is the relational algebra. enable the specification of basic retrievals The result of a retrieval is a new relation, which may have been formed from one or more relations.
DNO = 4(EMPLOYEE)
SALARY > 30,000(EMPLOYEE)
The SELECT operation <selection condition>(R) produces a relation S that has the same schema as R
The SELECT operation is commutative; i.e.,
A cascaded SELECT operation may be applied in any order; i.e.,
A cascaded SELECT operation may be replaced by a single selection with a conjunction of all the conditions; i.e.,
Id Name Address Hobby
1123 John 123 Main stamps
1123 John 123 Main coins
5556 Mary 7 Lake Dr hiking
9876 Bart 5 Pine St stamps
Id>3000OR Hobby=‘hiking’ (Person)
Id>3000 AND Id <3999(Person)
The general form of the project operation is: <attribute list>(R) where is the symbol used to represent the project operation and <attribute list> is the desired list of attributes.
PROJECT removes duplicate tuples, so the result is a set of tuples and hence a valid relation.
The number of tuples in the result of <list> R is always less or equal to the number of tuples in R.
If attribute list includes a key of R, then the number of tuples is equal to the number of tuples in R.
<list1> <list2> R)<list1> Ras long as<list2>contains theattributes in<list1>
SELECT and PROJECT Operations (a)s(DNO=4 AND SALARY>25000) OR (DNO=5 AND SALARY>30000)(EMPLOYEE) (b) pLNAME, FNAME, SALARY(EMPLOYEE) (c) pSEX, SALARY(EMPLOYEE)
, , (set difference)
Denoted by R S
Result is a relation that includes all tuples that are either in R or in S or in both. Duplicate tuples are eliminated.
Example: Retrieve the SSNs of all employees who either work in department 5 or directly supervise an employee who works in department 5:
The union operation produces the tuples that are in either RESULT1 or RESULT2 or both. The two operands must be “type compatible”.
Type (Union) Compatibility
The operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) must have the same number of attributes, and the domains of corresponding attributes must be compatible, i.e.
Person(SSN, Name, Address, Hobby)
Professor(Id, Name, Office, Phone)
are not union compatible.
are union compatible so
What would STUDENT INSTRUCTOR be?
Set Difference (or MINUS) Operation
The result of this operation, denoted by R - S, is a relation that includes all tuples that are in R but not in S. The two operands must be "type compatible”.
R S = S R, and R S = S R
R (S T) = (R S) T, and
(R S) T = R (S T)
R - S ≠ S – R
A B C D A B C D
x1 x2 y1 y2 x1 x2 y1 y2
x3 x4 y3 y4 x1 x2 y3 y4
x3 x4 y1 y2
RS x3 x4 y3 y4
R <join condition>S
where R and S can be any relations that result from general relational algebra expressions.
The Binary Join Operation
(a) PROJ_DEPT PROJECT * DEPT
(b) DEPT_LOCS DEPARTMENT * DEPT_LOCATIONS
(a) Dividing SSN_PNOS by SMITH_PNOS. (b) T R ÷ S.
E.g. SUM, AVERAGE, MAX, MIN, COUNT
E.g. List all employees and the department
they manage, if they manage a department.