Chapter 8: Relational Algebra

3 Steps in defining a database

1) Data model 2) Data Definition Language 3) Data Manipulation Language

Relational Algebra Definition

Basic set of operations for the relational model that (1) enables a user to specify basic retrieval requests (queries) and (2) the result = new relation can be formed from one or more input relation

Which operation combines the tuples of one relation with all the tuples of the other relation.

Cartesian Product

Cartesian Product (X)

Combine tuples from two relations in a combinatorial fashion. Resulting relation state has one tuple for each combination of tuples. Does NOT have to by union/type compatible Cartesian Product is fairly meaningless unless you use SELECT with it. For R X S, if R has 10 tuples and S has 10 tuples, you will end up with (10 x 10) 100 tuples.

Natural Join ⋈

Created to get rid of the second attribute in equijoin2 Two join attributes must have the same name in both relations otherwise RENAME is done first

Which operation "finds tuples that are in one relation but are not in another".

Difference

Division ÷

Division operator A÷B can be applied if and only if: Attributes of B is proper subset of Attributes of A. The relation returned by division operator will have attributes = (All attributes of A - All Attributes of B) The relation returned by division operator will return those tuples from relation A which are associated to every B's tuple.

Which is a join condition containing an equality operator:

Equijoin

Relational Algebra Operations on Relations (2)

(1) Update Operations: insert, delete, modify (2) Retrieval Operations: select, project, join, union, intersection, difference, cartesian product, divide

Join ⋈

Join combines CARTESIAN PRODUCT and SELECT into a single operation. Displays all tuples unless otherwise specified Example: Retrieve the name of department managers dept_mgr <- department ⋈ mgrssn = ssn (Employee)

Binary Relational Operations (2)

Join ⋈ Division ÷

Project π Unary Relational Operator

Keeps certain columns/attributes from a relation and discards the other columns (creates vertical partitioning PROJECT is not commutative (order does not matter) Removes duplicate tuples & no conditions The number of tuples in the result of projection is always LESS THAN OR EQUAL TO the number of tuples in R. If list of attributes includes a key of R, then the result will be EQUAL to the tuples in R. Example: List first name, last name, salary of all employees PROJECT lname, fname, salary (Employee)

Union compatibility implies...

Relations should have the same degree and attributes should come from the same domain.

Rename ρ Unary Relational Operator

Renames attributes or relation (or both) Rename attributes to b1, b2, b3 and relation name from R to S: RENAME S (b1, b2, b3) (R) Rename relation name from R to S RENAME S(R) Rename attribute names to b1, b2, b3 RENAME (b1, b2, b3) (R)

Theta Join ⋈

Results in true or false

Select σ Unary Relational Operator

SELECT is used to select a subset of the tuples (with all attributes) from a relation based on a condition SELECT is commutative (order does not matter b/c result is the same). Shows all row information (attributes) that meets condition (selects whole row). Number of tuples in the result of SELECT is LESS THAN OR EQUAL TO the number of tuples in the input relation. Examples: Select Employees whose department number is four. SELECT dno=4 (Employee) Select Employees whose salary is greater than 30k and from department four. SELECT dno=4 AND salary>30000 (Employee)

Unary Relational Operations (3)

Select σ Project π Rename ρ

Difference (-)

The result of R - S is a relation that includes all tuples in R but not in S Must be union/type compatible Not commutative Example: Art Students - Dance Students This would display the names of all students taking art classes and NOT dance. So any art and dance students would not be shown.

Intersection (∩)

The result of R ∩ S is a relation that includes all tuples that are in both R and S (like the center of a venn diagram). Returns records that exist in both tables. Must be union/type compatible

To intersect two relations, the relations must be union compatible? True or False?

True

Relational Algebra Operations from Set Theory (4)

Union (U) Intersection (∩) Difference (-) Cartesian Product (X)

Union (U)

Unionize (combine) two relations under some condition Must be union/type compatible Duplicate tuples are eliminated R U S is a relation that includes all tuples that are either in R or S or in both R and S. So all tuples in either relation and any tuples that are the same are eliminated.

Equijoin ⋈

involves join conditions with equality (=) comparisons only. Result is always one or more pairs of attributes that have identical values in every tuple (superfluous)