# CS 319: Theory of Databases: C6 - PowerPoint PPT Presentation

1 / 24

CS 319: Theory of Databases: C6. Dr. Alexandra I. Cristea http://www.dcs.warwick.ac.uk/~acristea/. … previous RA. Content. Generalities DB Integrity constraints (FD revisited) Relational Algebra (revisited) Query optimisation Tuple calculus Domain calculus Query equivalence

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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

### Content

• Generalities DB

• Integrity constraints (FD revisited)

• Relational Algebra (revisited)

• Query optimisation

• Tuple calculus

• Domain calculus

• Query equivalence

• Temporal Data

### Tuple Calculus

• 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

tr – means the tuple t is in relation r

### Example TC language

• QUEL (used by INGRES DBMS)

• SQL (to some extent)

### Examples TC

• {t | t loan ^ t[amount] > 1200}

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

### Domain calculus

• 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

### Example DC language

• QBE

• Others: ILL, FQL, DEDUCE

### Example DC

• {<l,b,a> | <l,b,a>  loan ^ a > 1200}

• {<l> | b,a (<l, b, a>loan ^ a > 1200)}

### Satefy of expressions: Tuple Calculus (& DC)

• 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).

### Queries in TC & DC

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.

### 1. Give the name of customers that have a loan with a branch where they also have an account.

TC: {t| bborrower(t[c_n]=b[c_n] ^ lloan(l[l_n]=b[l_n] ^ aaccount(a[b_n]=l[b_n] ^ ddepositor(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)))}

### 2. Give the name of customers who have a loan at a branch where they do not have an account.

TC: {t| bborrower(t[c_n]=b[c_n] ^ lloan(l[l_n]=b[l_n] ^ aaccount(a[b_n]=l[b_n] ^ ddepositor(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)))}

### 3. Give the name of customers who have a loan at every branch where they have an account.

This is necessary for

the safety of the

expression, to reduce

the search space

TC: {t| ddepositor(t[c_n]=d[c_n] ^ d2depositor((d2[c_n]=d[c_n] ^ aaccount(a[a_n]=d2[a_n])=> (lloan(l[b_n]=a[b_n] ^ bborrow(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)))))}

### 4. Give the name of customers who have loans only at branches where they have an account.

TC: {t| dborrow(t[c_n]=d[c_n] ^ d2borrow((d2[c_n]=d[c_n] ^ aloan(a[l_n]=d2[l_n])=> (laccount(l[b_n]=a[b_n] ^ bdepositor(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)))))}

### 4. Give the name of customers who have loans only at branches where they have an account.

TC: {t| bborrow(t[c_n]=b[c_n] ^ b2borrow((b2[c_n]=b[c_n] ^ lloan(l[l_n]=b2[l_n])=> (aaccount(a[b_n]=l[b_n] ^ ddepositor(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.

### Library TC/DC questions

• 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).

### 1. Give the name and department of borrowers, and the ISBN-number of the books of which they were the first borrower.

TC: {t | bborrow( t[name]=b[name] ^ t[department]=b[department] ^

ccopy(b[barcode]=c[barcode] ^ t[ISBN]=c[ISBN] ^

c2copy(c2[ISBN]=c[ISBN] ^ b2borrow(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)))) }

### 2. Give the name and department of borrowers who have never borrowed the most recently purchased copy of a book.

TC {t | bborrow( t[name]=b[name] ^ t[department]=b[department] ^

b2borrow((b2[name]=b[name] ^ b2[department]=b[department] ^ c2copy (c2[barcode]=b2[barcode])) =>

(c3copy(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)))))}

### 3. Give the title of books that are “present” in the library, reserved by someone, but not yet borrowed (by anyone).

TC: {t| bbook(t[title]=b[title] ^

rreservation(r[ISBN]=b[ISBN] ^

ccopy(c[ISBN]=b[ISBN] ^ c[present] = “true” ^

c2copy(c2[ISBN]=c[ISBN] ^ bborrow(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)))))}

### Beer Database

• visits(drinker, bar)

• serves(bar, beer)

• likes(drinker, beer).

### Beer questions with a difference

• 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)

### Summary

• We have learned TC & DC

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

… to follow

Query equivalence