Cs 319 theory of databases c6
Download
1 / 24

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


  • 84 Views
  • Uploaded on

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

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

PowerPoint Slideshow about ' CS 319: Theory of Databases: C6' - kisha


An Image/Link below is provided (as is) to download presentation

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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Cs 319 theory of databases c6

CS 319: Theory of Databases: C6

Dr. Alexandra I. Cristea

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



Content
Content

  • Generalities DB

  • Integrity constraints (FD revisited)

  • Relational Algebra (revisited)

  • Query optimisation

  • Tuple calculus

  • Domain calculus

  • Query equivalence

  • Temporal Data

  • The Askew Wall


Tuple calculus
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
Example TC language

  • QUEL (used by INGRES DBMS)

  • SQL (to some extent)


Examples tc
Examples TC

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

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


Domain calculus
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
Example DC language

  • QBE

  • Others: ILL, FQL, DEDUCE


Example dc
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
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
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
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
2. 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.

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
3. where they also have an account.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
4. where they also have an account.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 account1
4. where they also have an account.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
Library TC/DC questions where they also have an account.

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

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. ISBN-number of the books of which they were the first borrower. 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
Beer Database ISBN-number of the books of which they were the first borrower.

  • visits(drinker, bar)

  • serves(bar, beer)

  • likes(drinker, beer).


Beer questions with a difference
Beer questions with a difference ISBN-number of the books of which they were the first borrower.

  • 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
Summary ISBN-number of the books of which they were the first borrower.

  • We have learned TC & DC

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


… to follow ISBN-number of the books of which they were the first borrower.

Query equivalence


ad