Relational Query Languages

1
Relational Query Languages

• Relational Algebra (procedural)
• Relational Calculus (non-procedural)

2
Relational Languages

• Relational Algebra (procedural)
• defines operations on tables
• Relational Calculus (declarative)
• based on first-order predicate calculus
• Every relational algebra query can be translated
  to relational calculus
• Every safe relational calculus query can be
  translated to relational algebra.
• Any language that is at least as expressive as
  relational algebra is said to be relationally
  complete.

3
Relational Algebra Operators

• Select given a relation R and a predicate P,
  select tuples from R that satisfy P.
• Project given a relation R and a subset of its
  attributes X, return a relation which is the
  same as R except that all columns not in X are
  left out.
• Rename given a relation R and a name N, return a
  relation that is exactly the same as R except
  that it has a name N.
• Cartesian Product Given 2 relations R1and
  R2,.return a relation R3 whose tuples are the
  concatenation of tuples in R1 and R2
• Union Given relations R1 and R2, return a
  relation R3 which contains all tuples in R1 and
  R2
• Set Difference Given relations R1 and R2, return
  a relation R3 containing all tuples in R1 that
  are not in R2

4
Selection Operation

• Example
• Employee(name,dept,sal)

select sal gt 20,000 Employee
Employee
select (dept toy) or (sal lt 20,000)
5
Projection

• Proj list of attr of R (R) or ? list of attr
  of R (R)

R
S
A C
Jane Toy
John Toy
A B C
Jane Toy 10,000
Jim Toy 20,000
June Complaint 20,000
ProjAR
ProjCBR
A
Jane
Jim
June
C B
10,000 Toy
20,000 Complaint
20,000 Toy
6
Cartesian Product

• Denoted by R x S
• R A B C S A D
• joe toy 10K joe jill
• jack com 20K jack jill
• RxS R.A B C S.A D
• joe toy 10K joe jill
• joe toy 10K jack jill
• jack com 20K joe jill
• jack com 20K jack jill
• Notice attribute naming strategy to disambiguate
  attribute names

attributes get the name, R.A, where A is attrib
name, and R is the relation name from which
attrib originates. If there is no possible
ambiguity, relation name is dropped!
7
Set Difference

• Denoted by R S
• (Illegal if R S have different numbers of
  attributes or if respective domains mismatch!)
• R A B S A C
• Jane Toy Jane Toy
• Jim Toy John Complaint
• June Complaint
• R - S A B
• Jim Toy
• June Complaint
• Note attributes in resulting relation take name
  from the first relation

8
Rename Operator

• Strategy used to disambiguate attribute names
• For union and set difference operators, the
  resulting relation takes the attribute names of
  the first relation
• For cartesian product, attributes are named as
  Relation-name.attribute-name, where Relation name
  refers to the relation from which the attribute
  originally came.
• Strategy will not disambiguate when the same
  relation appears multiple times in the relational
  query.
• Let R(A,B) be a relation. Consider R x R
• what to name the attributes of the resulting
  relation?
• Define a rename operator
• denoted by renameNR or by r N(R)
• returns a relation which is exactly same as R
  except it has the name N

9
Rename Operator

• Consider relation Employee(name, dept, sal)
• List all the employees who work in the same
  department as Jill
• We first find the department(s) for which Jill
  works
• Projdept(selectname Jill Employee)
  ---list of departments for which Jill works
• To find out about all Employees working for this
  department, we need to reference the Employee
  table again
• selectP ( Employee x Projdept(selectname
  Jill Employee) )
• where P is a selection predicate which requires
  dept values to be equal.
• If we use Employee.dept in P it is ambiguous
  which instance of Employee relation in the query
  the attribute refers to.
• To disambiguate, use rename operator
• ProjEmployee.name(selectEmployee.dept
  Employee2.dept
• Employee x (Projdept (selectname Jill(
• renameEmployee2(Employee))))

10
Formal Definition of Relational Algebra

• Basic Expressions
• Relation in a database
• Constant Relations
• General Expressions constructed from basic ones.
  Let E1 and E2 be relational algebra expressions.
  Then the following are also expressions
• E1 U E2, if arity of E1 E2 and corresponding
  attributes are of the same type
• E1 - E2, if arity of E1 E2 and corresponding
  attributes are of the same type
• E1 x E2, if attributes in E1 and E2 have
  different names
• SelectP(E1), where P is a predicate on
  attributes in E1
• ProjS(E1), where S is a list of some attributes
  in E1
• renameX(E1), where X is a new name for relation
  E1

11
Additional Operators
Basic Relational Algebra operators are like
assembly language. Define more powerful operators
that make the task of writing relational algebra
queries easier Each of these operators can be
expressed in relational algebra and do not
increase the expressibility of the
language Example Intersection R Ç S
R - (R - S) t t Î R
t Î S
12
Joins

• R join condition S select join condition
  (R x S)
• join condition is of the form
• ltconditiongt AND ltconditiongt AND ltconditiongt
• where each condiition is of the form Ai q Bj,
  where
• Ai is attribute of R
• Bj is attribute of S
• q is a comparison operator , lt, gt, lt, gt, ltgt

Example E(emp, dept) M(dept, mgr) List
all employees and their managers. Projemp,
mgr(selectE.dept M.dept (ExM)) can be
represented as Projemp,mgr ( E E.dept
M.dept M )
13
Types of Joins

• Theta-Join if a join condition uses some
  comparison operator other than equality.
• E.g., list names of all managers who manage
  departments other than Jills
• Projmgr( selectemp Jill(E ) (E.dept
  ¹ M.dept) M)
• Equi-Join if join conditions use only equality
  operator.
• E.g., list the managers name of Jills
  department
• Projmgr( selectemp Jill(E ) (E.dept
  M.dept) M)
• Natural Join special type of equi-join..
• Let R and S be relations. Let attributes of R and
  S be denoted by R and S respectively.
• Denote by R U S the union of the list of
  attributes of R and S
• Let list of attributes common to both R and S be
  A1, A2, , An
• Natural join of R and S (denoted R S) is
• ProjR U S (R R.A1 S.A1 and R.A2
  S.A2 and and R.An S.An S)
• E.g., Projmgr( selectemp Jill(E ) M)

14
Assignment Operator

• Lots of time convenient to write relational
  algebra expressions in parts using assignment to
  temporary relational variables.
• For this purpose use assignment operator, denoted
  by
• E.g., Who makes more than their manager?
• E(emp, dept, sal) M(mgr, dept) ESM(emp,
  sal, mgr) Projemp, sal, mgr (E
  M)(ProjESM.emp(ESM mgr E.emp ESM.sal
  gtE.sal E) )
• With the assignment operator, a query can be
  written as a sequential program consisting of a
  series of assignments followed by an expression
  whose value is the result of the query.
  

15
Examples

• A query is a composition of basic relational
  algebra operators
• Consider relations
• customer(ssno, name, street, city)
• account(acctno, custid, balance)
• list account balance of Sharad

16
To Diag Dis Test
Strep A
Mono B
Meningitis C
Hepatitis D
Encephalitis E
Meningitis F
Meningitis G
Diag Pat Dis
Winslett Strep
Liu Mono
Harandi Meningitis
Harandi Hepatitis
Liu Hepatitis
Outcome Pat Test Outcome
Winslett A T
Winslett B F
Liu B T
Harandi F T
Winslett E F
Harandi E F
Harandi G F
Winslett E T
17

• 1. Who has what disease?
• Diag
• 2. Who has a disease they have been tested for?
• Projpat(Diag ? ToDiag ? Outcome)
• 3. Who has a disease they tested positively for?
• Projpat(Diag ? ToDiag ? (selectoutcome
  T)Outcome))
• 4. Who has a disease that they tested both
  positively negatively for?
• Temp1(pat, dis) Projpat,dis(Diag ? ToDiag
  selectoutcome T)Outcome)
• Temp2(pat, dis) Projpat,dis(Diag ?
  ToDiag selectoutcome T)Outcome)
• Projpat(Temp1 Ç Temp2)
• Use better names!!

18
Example of Queries in Relational Algebra

• 5. Who tested both positively and negatively for
  a disease, whether or not they have it?
• Testpos(pat, dis) Projpat,dis(ToDiag ?
  selectoutcome T) Outcome)
• Testneg(pat, dis) Projpat,dis(ToDiag ?
  selectoutcome T) Outcome)
• (Testpos Ç Testneg)pat
• 6. Who tested both positively negatively for
  the same test? (Winslett)
• Projpat(Outcome ? condition
  (renameOutcome2(Outcome))
• where condition is
• Outcome.pat Outcome2.pat Outcome.test
  Outcome2.test Outcome.outcome Outcome2.
  outcome

19

• 7. What testable disease does no one have?
• (encephalitis)
• ProjdisToDiag - ProjdisDiag
• Note technique compute opposite of what you
  want, take a difference. Use in hard queries
  with negation (no one)
• 8. What disease does more than one person have?
• Projdis(Diag condition
  renameDiag2(Diag))
• where, condition is Diag.pat ¹ Diag2.pat
  Diag.dis Diag2dis
• 9. What disease does everyone have?
• clue that query is
  very hard
• Disease(dis) diagdis
• Patients(pat) diagpat
• DiseasesNotEveryoneHas(dis)
  Projdis((Patients x Disease) - Diag)
• Disease - Diseases Not Everyone Has
• Note technique used! A very hard query might
  require taking the difference several times.

20
Outer Joins

• E emp dept sal M mgr
  dept
• Jones Missiles 10K Mendez
  Tanks
• Chu Tanks 20K Frank
  Explosive
• Swami Tanks 50K Jones
  Missiles
• Barth Revolver 100K

Right outer join of E with M emp dept
sal mgr Jones Missiles
10K Jones Chu Tanks 20K
Mendez null Explosives null
Frank
left outer join of E with M emp dept
sal mgr Jones Missiles
10K Jones Chu Tanks 20K
Mendez Swami Tanks 50K
Mendez Barth Revolver 100K null
Full outer join of E with M emp dept
sal mgr Jones Missiles
10K Jones Chu Tanks 20K
Mendez Swami Tanks 50K
Mendez Barth Revolver 100K null null
Explosives null Frank
21
Recursive Closure Queries

• Consider parent relation
• parent Child Mom
  Dad
• sam Anda
  Chuck
• Chuck Donna
  Harvey
• Harvey Betty
  Reggie
• Reggie Cristie
  John
• It may be interesting to query the relation for
  the following
• retrieve all the female anscestors of sam.
• retrieve all male ansestors of chuck
• retrieve harveys family tree
• retrieve all the descendants of cristie
• Such queries (in general) require an unbounded
  application of
• joins of the parent relation to itself and CANNOT
  be represented
• in relational algebra.