Transcript Lecture 9

603 Database Systems
Senior Lecturer: Laurie Webster II,
M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E.
Lecture 9
A First Course in Database Systems
Relational Algebra
Chapter 5
Up to Now:
*
discussed concepts that the relational
model provides for defining the
structure and constraints of a relational
database
Now Relational Algebra:
*
when data are viewed as relations, it is
possible to formulate queries utilizing
the properties of relational theory
Relational Algebra
Chapter 5
*
*
Relational Algebra provides a powerful
tool for such a view of the data for
formulating real world concepts into
queries which can be related to the data
description
Set Operations and Operations
developed specifically for relation
databases
Relational Algebra
Chapter 5
– This chapter develop database programming
==> used to facilitate the user asking queries of
the database and modify the contents of the
database
– Begin by introducing the relational algebra
operations
– This algebra formally applies to sets of tuples
– THE OPERATIONS OF RELATIONAL
ALGEBRA ARE ALL IMPLEMENTED IN
THE SQL QUERY LANGUAGE
Chapter 5
Relational Algebra ==> Atomic operands are:
1. Variables that stand for relations R
2. Constants, which are finite relations
Operations of Relational Algebra :
1. The usual set operations - union,
intersection, and difference - applied to
relations R
2. Operations that remove parts of a relation:
Selection  and Projection 
Relational Algebra
Chapter 5
* Selection  eliminates some rows (tuples) of R and
* Projection  eliminates some columns (attributes) of
R
*
Operations that combine the tuples of two
relations R1 and R2, including “Cartesian
Product,” which pairs the tuples of two relations
in all possible ways, and various kinds of “join”
operations, which selectively pair tuples from
two relations
Relational Algebra
Chapter 5
Set Operations on Relations R:
*
R  S, the union or R and S =>
the set of elements that are in R
or S or both. An element
appears only once in the union
R
S
even if it is present in both R
and S.
Union
Relational Algebra
Chapter 5
Set Operations continued:
*
R  S, the intersection of R and S =>
the set of elements that are in both
R and S
R
S
Intersection
Relational Algebra
Chapter 5
R - S, the difference of R and S => the set of elements
that are in R but not in S
NOTE: When applying these set theoretic operations
to relations, we need to put some conditions on R and
S:
1. R and S must have schemas with identical
sets of attributes, and the types(domains) for
each attribute must be the same in R and S
2. Before we compute the set-theoretic union,
intersection, or difference of sets of tuples, the columns
of R and S must be ordered so that the order of the
EMPLOYEE
Relational Algebra
Chapter 5
FNAME MINIT LNAME
Franklin T
Wong
Jennifer
S
Wallace
Ramesh
K
Narayan
SSN
333445555
987654321
66688444
SEX
M
F
M
DNO = 4 (EMPLOYEE)
SALARY > 3000 (EMPLOYEE)
In General: <selection condition> ( R )
SALARY DNO
40000
5
43000
4
38000
5
Relational Algebra
Chapter 5
Select Operation:
In General: <selection condition> ( R )
The Boolean expression specified in
<selection condition> is made up of clauses
of the form
<attribute name><comparison op><constant value> or
<attribute name><comparison op><attribute name>
Relational Algebra
Chapter 5
SELECT Operator:
=> unary; that is, it is applied to a single relation
=> applied to each tuple individually
NOTE: Selection conditions cannot involve more than
one tuple
The degree of the relation resulting from a SELECT
operation is the same as that of R !
Relational Algebra
Chapter 5
Selectivity of the condition:
The fraction of tuples selected by a selection
condition is referred to as the selectivity of
the condition
Relational Algebra
Chapter 5
Notice that the SELECT operation is commutative:
<cond1>(<cond2> ( R )) = <cond2> (<cond1> ( R ))
Relational Algebra
Chapter 5
PROJECT Operation:
If we think of the relation R as a Table, the SELECT
operation selects some of the rows for the table while
discarding other rows
The PROJECT operation, on the other hand, selects
certain columns from the table and discards the other
columns
Introduction to Relational
Algebra (Chapter 5)
•
•
•
•
•
•
•
Basic of Relational Algebra
Set Operations on Relations
Projection
Selection
Cartesian Product
Natural Joins
Theta-Joins
Relational Algebra
Chapter 5
PROJECT Operation:
List each employee’s first and last name and
salary LNAME, FNAME, SALARY (EMPLOYEE)
In general, the PROJECT Operator is:
<attribute list> (R)
Relational Algebra
Chapter 5
PROJECT Operations:
If the attribute list includes only nonkey
attributes of R, duplicate tuples are likely to occur; the
PROJECT operation removes any duplicate tuples, so
the result of the PROJECT operation is a set of tuples
and hence a valid relation
<list1> (<list2> (R)) = <list1> (R)
Relation R
name
Carrie Fisher
Mark Hamill
Relation S
name
Carrie Fisher
Harrison Ford
Relational Algebra
Chapter 5
address
gender
123 Maple St. Holl. F
456 Oak Rd., Brent. M
birthdate
9/9/99
8/8/88
address
gender
123 Maple St., Holl. F
789 Palm Dr., B. H. M
birthdate
9/9/99
7/7/77
Relational Algebra
Chapter 5
RS
name
Carrie Fisher
Mark Hamill
Harisson Ford
address
123 Maple St. , Holl.
456 Oak Rd., Brent.
789 Palm Dr., B. H.
gender
F
M
M
birthdate
9/9/99
8/8/88
7/7/77
name
address
gender
Carrie Fisher 123 Maple St., Holl. F
birthdate
9/9/99
RS
Relational Algebra
Chapter 5
R-S
name
Mark Hamill
address
gender
456 Oak Rd., Brent. M
birthdate
8/8/88
Relational Algebra
Chapter 5 - Next Lecture
•
•
•
•
Projection
Selection
Cartesian Product
Natural Joins