CS 319: Theory of Databases: C7

1. CS 319: Theory of Databases: C7 Dr. Alexandra I. Cristea http://www.dcs.warwick.ac.uk/~acristea/

2. … previous TC, DC

3. Equivalence of queries • RA <-> TC <-> DC <-> Datalog <-> SQL

4. Equivalence of TC and RA • For every relational algebra (RA) query there is an equivalent tupel calculus (TC) query (and v.v.). • Translation RA  TC is fairly straightforward and can be used to obtain TC queries from RA queries. • Translation TC  RA is very artificial and does not result in readable RA queries. • We will show the RA to TC translation.

5. Translation of RA to TC • Step 1: Reduce the algebra expression E to the basic operators • Step 2: If E is a single relation r then E translates to { t | tr }. If r has attributes B1, ...,Bn then it can also be written as {t | sr ( t[B1]=s[B1] ... t[Bn]=s[Bn] ) }.

6. Translation of RA to TC • Step 3: Apply the following substeps recursively (if the algebra expressions are base relations an  may need to be added): • Renaming: let { t | f(t) } be a TC expression equivalent to an RA expression E that uses attributes B1, ...,Bn: Let E1 = x(A1,...,An)(E), then the translation to TC is { s | tr (f(t)  s[A1]=t[B1] ... s[An]=t[Bn]) }

7. Translation of RA to TC • Cartesian Product: if E1 (over A1, ...,An) translates to { t | f(t) } and E2 over B1, ...,Bm translates to { s | g(s) } then E1 E2 translates to { r | f(t) ( g(s) ( t[A1]=r[A1] ... t[An]=r[An]  s[B1]=r[B1] ... s[Bm]=r[Bm] )) } (attribute names must be unique in t)

8. Translation of RA to TC • Selection: let { t | f(t) } be a TC expression equivalent to E, then E1= AB(E) or E1= Ac(E) (where  is , , ,...) is translated to { t | f(t)  t[A]t[B] } and { t | f(t)  t[A]c } resp. (replace t[.] with basis tuples from quantificators) • Projection: if E translates to { t | f(t) } then B1, ...,Bn (E) translates to { t | f'(t) } where f' is f with references to t[B1] ... t[Bn] only (no other attributes of t are used in f).

9. Translation of RA to TC • Union: let { t | f(t) } be a TC expression equivalent to E1 and { t | g(t) } be a TC expression equivalent to E2 then E1 E2 is translated to { t | f(t)  g(t) } • Difference: let { t | f(t) } be a TC expression equivalent to E1 and { t | g(t) } be a TC expression equivalent to E2 then E1 E2 is translated to { t | f(t)  g(t) }

10. Example • List all drinkers who visit a bar that serves a beer they like. • RA: V.d(V  S  L) • Step 1: V.d( V.d, V.k, S.b(V.k=S.kS.b=L.bV.d=L.d(V  S  L))) • Step 2: The relations are translated to { v | vV } { s | sS } { l | lL } • Step 3: We first translate the product, then the selection and then the projection.

11. Example (cont.) • Cartesian product: { t | vV ( sS ( lL (t[vd]=v[d]  t[vk]=v[k]  t[sk]=s[k]  t[sb]=s[b]  t[ld]=l[d]  t[lb]=l[b] ))) } (attribute names must be unique) • Selection: { t | vV ( sS ( lL (t[vd]=v[d]  t[vk]= v[k]  t[sk]=s[k]  t[sb]=s[b]  t[ld]=l[d]  t[lb]=l[b]  v[k]=s[k]  s[b]=l[b]  v[d]=l[d] ))) }

12. Example (cont.) • Projection: { t | vV ( sS ( lL (t[vd]=v[d]  v[k]=s[k]  s[b]=l[b]  v[d]=l[d] ) ) ) }

13. Summary • We have learned query equivalence

14. … to follow Exam discussion

15. Beer question n (tupel calculus) • Give all beers that are served in bars where none of the visitors like any other beer (than the ones served in that bar). • Is this a correct answer?

16. A faulty answer • Consider • Or after a rewrite • Suppose there are 2 beers a, b, 1 bar k, 2 drinkers x, y;everyone serves/visits/likes everything Then the correct answer should result in {a, b}.The tupel calculus expression does not contain a, because every visitor of k also likes b.

17. Beer question n (RA) • Give all beers that are served in bars where none of the visitors like any other beer (than the ones served in that bar). • b(S  (k(S) - k( V  L - V  L  S))) • This is not so easy to translate to the earlier TC expression

18. Translate L&V quantor as and not as Common tupel calculus mistakes • Explain what is wrong in the following queries:

19. 4 5 6 7 8

20. 9 10 11 12 13 14

21. 15 16 17 18