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

• selection cond(R) or selectselection condR
• Example Employee(name, dept, sal)
• Employee select sal gt 20,000 Employee
• name dept sal
• jane pharmacy 30,000
• jack hardware 30,000
• jill pharmacy 75,000
• select (dept toy) or (sal lt 20,000)
  Employee
• name dept sal
• joe toy 20,000
• bill toy 12,000

s
name dept sal jane pharmacy 30,000 jack
hardware 30,000 jill pharmacy 75,000 joe toy 2
0,000 bill toy 12,000
5
Projection

• Proj list of attr of R (R ) or P list of
  attr of R (R)
• R A B C
  S A C
• Jane Toy 10,000
  Jane Toy
• Jim Toy 20,000
  John Complaint
• June Complaint 20,000
• ProjAR
  ProjCBR
• A C B
• Jane
  10,000 Toy
• Jim
  20,000 Complaint
• June 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 expressons.
  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
ToDiag Dis Test
Strep A Mono
B Meningitis
C Hepatitis D
Encehhalitis 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.