- 63 Views
- Uploaded on
- Presentation posted in: General

CS 319: Theory of Databases: C6

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

CS 319: Theory of Databases: C6

Dr. Alexandra I. Cristea

http://www.dcs.warwick.ac.uk/~acristea/

… previous

RA

- Generalities DB
- Integrity constraints (FD revisited)
- Relational Algebra (revisited)
- Query optimisation
- Tuple calculus
- Domain calculus
- Query equivalence
- Temporal Data
- The Askew Wall

- non-procedural query language
- A query in TC is expressed as:
{t|P(t)}

where:

t – set of tuples (also called free or range variable)

P – predicate that is true for t (also called formula)

Also:

t[A] – value of tuple t on attribute A

tr – means the tuple t is in relation r

- QUEL (used by INGRES DBMS)
- SQL (to some extent)

- {t | t loan ^ t[amount] > 1200}
- {t | s loan (t[loan-number] = s[loan-number] ^ s[amount] > 1200)}

- Uses domain variables to take values from an attributes domain, instead of tuple values
- is related to QBE language
- An expression in DC is of the form:
{<x1,x2,…,xn> | P(x1,x2,…,xn)}

where:

x1,…xn are domain variables

P is a formula

- QBE
- Others: ILL, FQL, DEDUCE

- {<l,b,a> | <l,b,a> loan ^ a > 1200}
- {<l> | b,a (<l, b, a>loan ^ a > 1200)}

- A TC expression {t | P(t)} may give an infinite relation, e.g.,
{ t | (t loan) }

- Define dom(P) as the set of all values referenced by P (e.g. literals or relations)
- A TC expression {t | P(t)} is safe when all values appearing in the result are values from dom(P).

Translate the following questions (for the bank database) to the tuple & domain calculus:

- Give the name of customers that have a loan with a branch where they also have an account.
- Give the name of customers who have a loan at a branch where they do not have an account.
- Give the name of customers who have a loan at every branch where they have an account.
- Give the name of customers who have loans only at branches where they have an account.

TC: {t| bborrower(t[c_n]=b[c_n] ^ lloan(l[l_n]=b[l_n] ^ aaccount(a[b_n]=l[b_n] ^ ddepositor(a[a_n]=d[a_n] ^ d[c_n]=b[c_n] ))))}

DC: {<cn>| ln (<cn,ln>borrower ^ bn,a(<ln,bn,a>loan ^ an,b(<an,bn,b>account ^ <cn,an>depositor)))}

TC: {t| bborrower(t[c_n]=b[c_n] ^ lloan(l[l_n]=b[l_n] ^ aaccount(a[b_n]=l[b_n] ^ ddepositor(a[a_n]=d[a_n] ^ d[c_n]=b[c_n] ))))}

DC: {<cn>| ln (<cn,ln>borrower ^ bn,a(<ln,bn,a>loan ^ an,b(<an,bn,b>account ^ <cn,an>depositor)))}

This is necessary for

the safety of the

expression, to reduce

the search space

TC: {t| ddepositor(t[c_n]=d[c_n] ^ d2depositor((d2[c_n]=d[c_n] ^ aaccount(a[a_n]=d2[a_n])=> (lloan(l[b_n]=a[b_n] ^ bborrow(b[l_n]=l[l_n] ^ b[c_n]=d2[c_n]))))))}

DC: {<cn>| an(<cn,an>depositor ^ an2 (( <cn,an2>depositor^ bn,b(<an2,bn,b>account)=> (ln,a(<ln,bn,a>loan ^ <cn,ln>borrow)))))}

TC: {t| dborrow(t[c_n]=d[c_n] ^ d2borrow((d2[c_n]=d[c_n] ^ aloan(a[l_n]=d2[l_n])=> (laccount(l[b_n]=a[b_n] ^ bdepositor(b[a_n]=l[a_n] ^ b[c_n]=d2[c_n]))))))}

DC: {<cn>| an(<cn,an>borrow ^ an2 (( <cn,an2>borrow^ bn,b(<an2,bn,b>loan)=> (ln,a(<ln,bn,a>account ^ <cn,ln>depositor)))))}

TC: {t| bborrow(t[c_n]=b[c_n] ^ b2borrow((b2[c_n]=b[c_n] ^ lloan(l[l_n]=b2[l_n])=> (aaccount(a[b_n]=l[b_n] ^ ddepositor(d[a_n]=a[a_n] ^ d[c_n]=n2[c_n]))))))}

DC: {<cn>| ln(<cn,ln>borrow ^ ln2 (( <cn,ln2>borrow^ bn,a(<ln2,bn,a>loan)=> (an,b(<an,bn,b>account ^ <cn,an>depositor)))))}

With more

intuitive attrs

naming.

- Give the name and department of borrowers, and the ISBN-number of the books of which they were the first borrower.
- Give the name and department of borrowers who have never borrowed the most recently purchased copy of a book.
- Give the title of books that are “present” in the library, reserved by someone, but not yet borrowed (by anyone).

TC: {t | bborrow( t[name]=b[name] ^ t[department]=b[department] ^

ccopy(b[barcode]=c[barcode] ^ t[ISBN]=c[ISBN] ^

c2copy(c2[ISBN]=c[ISBN] ^ b2borrow(b2[barcode]=c2[barcode] ^ b2[from]<b[from])))) }

DC: {<n,d,ISBN> | b,f,t( <n,b,d,f,t>borrow ^

Y,p,d1(<b,ISBN,d1,Y,p>copy ^

b2,d2,Y2,p2(<b2,ISBN,d2,Y2,p2>copy ^ n2,f2,t2,d3(<n2,b2,d3,f2,t2>borrow ^ f2<f)))) }

TC {t | bborrow( t[name]=b[name] ^ t[department]=b[department] ^

b2borrow((b2[name]=b[name] ^ b2[department]=b[department] ^ c2copy (c2[barcode]=b2[barcode])) =>

(c3copy(c3[ISBN]=c2[ISBN] ^ c3[cpYear]>c2[cpYear] ^ b2[from] c3[cpYear])))}

DC {<n, d> | b,f,t (<n,b,d,f,t>borrow ^

b2,f2,t2((<n,b2,d,f2,t2>borrow ^ ISBN,d2,Y2,p2(<b2,ISBN,d2,Y2,p2>copy]) =>

(b3,d3,Y3,p3(<b3,ISBN,d3,Y3,p3>copy ^ Y3>Y2 ^ f2 Y3)))))}

TC: {t| bbook(t[title]=b[title] ^

rreservation(r[ISBN]=b[ISBN] ^

ccopy(c[ISBN]=b[ISBN] ^ c[present] = “true” ^

c2copy(c2[ISBN]=c[ISBN] ^ bborrow(b[barcode]=c2[barcode])))))}

DC: {<title>| ISBN,p,y(<ISBN,title,p,y>book ^ n,d,date,c(<n,d,ISBN,date,c>reservation ^ b2,d2,Y2,p2(<b2,ISBN,d2,Y2,p2>copy ^ p2 = “true”

^ b3,d3,Y3,p3(<b3,ISBN,d3,Y3,p3>copy ^ n4,d4,f4,t4(<n4,b3,d4,f4,t4>borrow)))))}

- visits(drinker, bar)
- serves(bar, beer)
- likes(drinker, beer).

- Give all drinkers that visit bars that don’t serve any beer they like
- Give all drinkers that only visit bars that serve a beer they like
- Give all drinkers that only visit bars that serve no beer they like
- Give all drinkers that only visit bars that serve all beers they like (and maybe other beers as well)
- Give all drinkers that only visit bars that only serve beers they like (and thus serve nothing else)

- We have learned TC & DC
- We have learned to perform simple and more complex queries in TC & DC

… to follow

Query equivalence