Transcript Lecture 11

603 Database Systems
Senior Lecturer: Laurie Webster II,
M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E.
Lecture 11
A First Course in Database Systems
Relational Algebra
Chapter 5
SELECTION 
:
Movie
title
year length
Star Wars
1977 124
Mighty Duck 1971 104
inColor studioName producerC#
yes
Fox
12345
yes
Disney
67890
Suppose we want the Set of tuples in the relation
Movie(title, year, length, inColor, studioName, producerC#)
that represent Fox movies at least 100 minutes
long.
c(R) = length  100 AND studioName = ‘Fox’(Movie)
Relational Algebra
c(R) = length  100 AND studioName = ‘Fox’(Movie)
==>
Result
title
Star Wars
year
1977
length inColor
124 Yes
studioName
Fox
producerC#
12345
Relational Algebra
Chapter 5
PROJECTION  :
Attribute list (R)
Movie
title
Star Wars
Mighty Duck
Wayne’s W.
year
1977
1991
1992
length
124
104
95
inColor
yes
yes
yes
title, year, length (Movie)
studioName producerC#
Fox
12345
Disney
67890
Paramount 99999
Relational Algebra
Chapter 5
PROJECTION  :
title, year, length (Movie)
Movie
title
Star Wars
Mighty Duck
Wayne’s W.
year length
1977 124
1991 104
1992 95
Relational Algebra
Chapter 5
SET THEORETIC OPERATIONS:
Retrieve SSN of all employees who either work in DEP5 or
directly supervise an employee who works in DEP5.
Solution: Use union operation on Result1 and Result2
DEP5_EMPS   DNO = 5 (EMPLOYEE)
RESULT1   SSN (DEP5_EMPS )
RESULT2(SSN)   SUPPERSSN (DEP5_EMPS )
RESULT  RESULT1  RESULT2
Relational Algebra
QUERY RESULT:
RESULT1
RESULT2
SSN
123456789
333445555
666884444
453453453
RESULT
SSN
123456789
333445555
SSN
666884444
333445555
453453453
888665555
888666555
Query result after the UNION operation:
RESULT1 RESULT2
Relational Algebra
Set Theoretic Operations:
UNION, INTERSECTION, and
SET DIFFERENCE ==>
These operations are adapted to relational
databases where the two relations on which they apply
must have the same types of tuples;
==> UNION compatible
Relational Algebra
Chapter 5
Set Theoretic Operations:
R(A1, A2, …., An) and S(B1, B2, …, Bn) is UNION
compatible if dom(Ai) = dom(Bi) for 1 i  n.
==> two relations have the same number of attributes
and that each pair of corresponding attributes have the
same domain.
Relational Algebra
Chapter 5
JOIN Operation:
EMP_DEPENDENTS  EMPNAMES  DEPENDENT
ACTUAL_DEPENDENTS   ssn = essn(EMP_DEPENDENTS)
replaced with single JOIN operation:
ACTUAL_DEPENDENTS  EMPNAMES  SSN = ESSN DEPENDENT
Relational Algebra
Chapter 5
Examples of Queries in Relational Algebra:
Query 1
Retrieve the name and address of all employees who
work for the ‘Research’ department.
RESEARCH_DEPT   DNAME = ‘RESEARCH’ (DEPARTMENT)
RESEARCH_EMP
(RESEARCH_DEPT  DNUMBER = DNO EMPLOYEE)
RESULT FNAME, LNAME, ADDRESS (RESEARCH_EMPS)
Relational Algebra
Chapter 5
Examples of Queries in Relational Algebra:
Query 2
For every project located in ‘Stafford’, list the project
number, the controlling department number, and the
department manager’s last name, address, and birthdate.
STAFFORD_PROJS   PLOCATION = ‘Stafford’ (DEPARTMENT)
CONTR_DEPT 
(STAFFORD_PROJS  DNUM = DNUMBER DEPARTMENT)
PROJ_DEPT_MGR  (CONTR_DEPT  MGRSSN = SSN EMPLOYEE)
RESULT PNUMBER, DNUM, LNAME, ADDRESS, BDATE (PROJ_DEPT_MGR)
Relational Algebra
Chapter 5
Relational Calculus:
Relational calculus is a formal query language where
we write on declarative expression to specify a
retrieval request and hence there is no description of
how to evaluate a query!!
Declarative
Language
A calculus expression specifies WHAT is to be
retrieved rather than HOW to retrieve it.
Procedural Language
Relational Algebra
Chapter 5
Relational Calculus:
==> nonprocedural language (Declarative Language)
Relational Algebra:
==> Procedural Language
Relational Algebra
Chapter 5
Relational Algebra ==> Procedural
where we must write a sequence of
operations to specify a retrieval
request (HOW process).
Relational Calculus ==> Declarative
we express WHAT is to be retrieved
without specifying HOW to retrieve
it.
Relational Algebra
Chapter 5
Expressive Power of the Two Languages:
Any retrieval that can be specified in relational
algebra can also be specified in the relational calculus,
and vice versa => expressive power of two languages
is identical
Relational Algebra
Expressive Power of the Two Languages:
Any retrieval that can be specified in relational algebra
can also be specified in the relational calculus, and
vice versa => expressive power of two languages is
identical
==>
led to the definition of the concept of relationally
complete language.
Relational Algebra
Complete Set of Relational Algebra Operations:
All the operations discussed so far can be described as
a sequence of only the operations SELECT,
PROJECT, UNION, SET, DIFFERENCE, and
CARTESIAN PRODUCT.
Hence, the set {  , ,  , - ,  } is called a complete
set of relational algebra operations.
Relational Calculus
A relational query language L is considered
relationally complete if we can express in L any
query that can be expressed in relational calculus.
Relational completeness has become an important
basis for comparing the expressive power of highlevel query languages.
Relational Calculus
Tuple Variables and Range Relations
The tuple relational calculus is based on specifying
a number of tuple variables.
Each tuple variable usually ranges over a particular
database relation, meaning that the variable may
take as its value any individual tuple from that
relation.
Relational Calculus
Tuple Relational Calculus Query:
{ t | COND (t) }
where t is a tuple variable and COND(t) is a
conditional expression involving t.
The result of such a query is the set of all tuples t
that satisfy COND( t ) .
Relational Calculus
Relational Calculus Query Example:
Find all employees whose salary is above
$50000.
{ t | EMPLOYEE (t) and t.SALARY > 50000}
Relational Calculus
Relational Calculus Query Example:
Find all employees whose salary is above
$50000.
{ t | EMPLOYEE (t) and t.SALARY > 50000}
==>
EMPLOYEE(t) => specifies that the range relation of
tuple variable t is EMPLOYEE.
Each EMPLOYEE tuple t that satisfies the condition
Relational Calculus
NOTE:
t.SALARY references attribute SALARY
of tuple variable t;
==> this notation resembles how attribute
names are qualified with relation
names or aliases in SQL.
t.SALARY  t[SALARY]
Relational Calculus
NEXT LECTURE
Relational Calculus