RELATIONAL SET OPERATORS
DBSYS 1
K. A. SECUGAL
RELATIONAL ALGEBRA
The degree of relational
completeness can be defined by
the extent to which relational
algebra is supported.
Relational Algebra defines by
theoretical way of manipulating
table contents using the eight
relational operators: SELECT,
PRODUCT, UNION, INTERSECT, JOIN,
DIFFERENCE, PROJECT, and DIVIDE.
2
RELATIONAL ALGEBRA
THE MAIN PURPOSE OF USING RELATIONAL ALGEBRA IS TO
DEFINE OPERATORS THAT TRANSFORM ONE OR MORE INPUT
RELATIONS INTO AN OUTPUT RELATION. GIVEN THAT THESE
OPERATORS ACCEPT RELATIONS AS INPUT AND PRODUCE
RELATIONS AS OUTPUT, THEY CAN BE COMBINED AND USED
TO EXPRESS POTENTIALLY COMPLEX QUERIES THAT
TRANSFORM POTENTIALLY MANY INPUT RELATIONS (WHOSE
DATA ARE STORED IN THE DATABASE) INTO A SINGLE
OUTPUT RELATION (THE QUERY RESULTS). AS IT IS PURE
MATHEMATICS, THERE IS NO USE OF ENGLISH KEYWORDS IN
RELATIONAL ALGEBRA AND OPERATORS ARE REPRESENTED
USING SYMBOLS.
3
1. UNION
• Combines all rows from tables, excluding duplicate rows. The
tables must have the same attribute characteristics (the
columns and the domains must be identical) to be used in the
UNION.
• A union of two relations (R1 U R2) can only be performed if the
two relations are union compatible. This essentially means that
both relations have the exact same attributes. A union
combines the rows of the two relations and outputs a new
relation that has both input relations’ rows in it.
4
P_code P_name P_price
P_code P_name P_price
123 Lamp 15
123 Lamp 15
124 Bulb 12 UNION
128 Glue 9
125 Switch 6.50
129 LED 5
126 Battery 18
Yields
P_code P_name P_price
123 Lamp 15
124 Bulb 12
125 Switch 6.50
126 Battery 18
128 Glue 9
129 LED 5
2. INTERSECT
• Yields only the rows that appear in both tables.
• Is the second SQL command that takes two tables and
combines only the rows that appear in both tables. The tables
must be union-compatible to be able to use the Intersect
command or else it won't work.
7
F_Name F_Name
F_Name
Dodoy Dodoy
Intersect Eli Buendia
Yields Dodoy
Larry boy
Larry boy
Franz Magalona Larry boy
Charles
Mike Jordan
3. DIFFERENCE
Yields all rows in one table that The set difference operators takes
are not found in the other table, the two sets and returns the values
that is, it subtracts one table from that are in the first set but not the
the other. second set.
9
4. PRODUCT
Yields all possible pairs of rows from
two tables – also known as the
Cartesian Product.
Yields all possible pairs of rows from
two tables- also known as Cartesian
product. Therefore, if one table has
six rows and the other table has
three, the PRODUCT yields a list
composed of 6 x 3= 18 rows.
11
5. SELECT
• Also known as RESTRICT, yields all rows found in a table. SELECT
can be used to list all of the row values, or it can yield only
those row values that match a specified criterion. In other
word, SELECT yields a horizontal subset of a table.
6. PROJECT
• Yields all values for selected attributes. In other words, PROJECT yields a
vertical subset of a table.
• Selects (or chooses) certain attributes discarding other attributes. The Project
operation is also known as vertical partitioning since it partitions the relation or
table vertically discarding other columns or attributes.
17
PROJECT CLASS
AND DEPT
7. JOIN
• JOIN – allows information to combine two or more tables. JOIN is the real power behind
the relationship database, allowing the use of independent tables linked by the common
attribute.
• NATURAL JOIN – links tables by selecting only the rows with common values in their
common attribute(s).
• LEFT OUTER JOIN – yields all rows in the LEFT table including those do not have matching
values RIGHT.
• RIGHT OUTER JOIN – yields all rows in the RIGHT table including those do not have
matching values in the LEFT.
Cus_Code Name Agent_Code
1123 Sarah 231
1234 Fema 125
1342 Ivy 167
1432 Annie 125
1324 Jezza 134
Agent_Code Phone_#
125 09099912
231 09124523
167 09124345
156 09182832
20
RIGHT JOIN
8. DIVIDE
Requires the use of one single column table and 2-column table.
The dividend table must match exactly with the values of the divisor
without any extra values.
23
DIVIDE
SAMPLE
THANK YOU
• End of topic
