temple university – cis dept. cis661– principles of database systems v. megalooikonomou...

Temple University – CIS Dept.CIS661– Principles of Database Systems

V. Megalooikonomou

Relational Model III

(based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

Overview

history concepts Formal query languages

relational algebra rel. tuple calculus rel. domain calculus

General Overview - rel. model

history concepts Formal query languages

relational algebra rel. tuple calculus rel. domain calculus

Overview - detailed

rel. tuple calculus why? details examples equivalence with rel. algebra more examples; ‘safety’ of

expressions rel. domain calculus + QBE

Safety of expressions

FORBIDDEN:

It has infinite output!! Instead, always use

}|{ STUDENTtt

}....|{ TABLESOMEtt

Safety of expressions Possible to write tuple calculus expressions that

generate infinite relations, e.g., {t | t r } results in an infinite relation if the domain of any attribute of relation r is infinite

To guard against the problem, we restrict the set of allowable expressions to safe expressions.

An expression {t | P (t) } in the tuple relational calculus is safe if every component of t appears in one of the relations, tuples, or constants that appear in P

More examples: Banking example

branch (branch-name, branch-city, assets) customer (customer-name, customer-

street, customer-city) account (account-number, branch-name,

balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-

number) borrower (customer-name, loan-number)

Example Queries Find the loan-number, branch-name, and amount for

loans of over $1200{t | t loan t [amount] 1200}

Find the loan number for each loan of an amount greater than $1200{t | s loan (t [loan-number] = s [loan-number]

s [amount] 1200}

Notice that a relation on schema [loan-number] is implicitly defined by the query

Example Queries Find the names of all customers having a loan, an

account, or both at the bank{t | s borrower(t[customer-name] = s[customer-name]) u depositor(t[customer-name] = u[customer-name])

Find the names of all customers who have a loan and an account at the bank

{t | s borrower(t[customer-name] = s[customer-name]) u depositor(t[customer-name] = u[customer-name])

Example Queries Find the names of all customers having a loan at the

Perryridge branch{t | s borrower(t[customer-name] = s[customer-name]

u loan(u[branch-name] = “Perryridge” u[loan-number] = s[loan-number]))}

Find the names of all customers who have a loan at the Perryridge branch, but no account at any branch of the bank{t | s borrower(t[customer-name] = s[customer-name] u loan(u[branch-name] = “Perryridge” u[loan-number] = s[loan-number])) not v depositor (v[customer-name] = t[customer-name]) }

Example Queries Find the names of all customers having a loan from

the Perryridge branch, and the cities they live in

{t | s loan(s[branch-name] = “Perryridge” u borrower (u[loan-number] = s[loan-number] t [customer-name] = u[customer-name]) v customer (u[customer-name] = v[customer-name]

t[customer-city] = v[customer-city])))}

Example Queries

Find the names of all customers who have an account at all branches located in Brooklyn:

{t | c customer (t[customer.name] = c[customer-name]) s branch(s[branch-city] = “Brooklyn” u account ( s[branch-name] = u[branch-name] s depositor ( t[customer-name] = s[customer-name] s[account-number] = u[account-number] )) )}

General Overview

relational model Formal query languages

relational algebra rel. tuple calculus rel. domain calculus

Overview - detailed

rel. tuple calculus dfn details equivalence to rel. algebra

rel. domain calculus + QBE

Rel. domain calculus (RDC)

Q: why? A: slightly easier than RTC,

although equivalent - basis for QBE idea: domain variables (w/ F.O.L.) –

e.g.: ‘find STUDENT record with

ssn=123’

Rel. Dom. Calculus

}123,,|,,{ sSTUDENTansans

find STUDENT record with ssn=123’

Details

Like R.T.C - symbols allowed:

quantifiers

),(,

,,,,,,

,,,

,

Details

but: domain (= column) variables, as opposed to tuple variables, e.g.:

STUDENTans ,,

ssnname address

Domain Relational Calculus

A nonprocedural query language equivalent in power to the tuple relational calculus

Each query is an expression of the form:

{ x1, x2, …, xn | P (x1, x2, …, xn)}

x1, x2, …, xn represent domain variables P represents a formula similar to that of the

predicate calculus

Example queries Find the branch-name, loan-number, and amount for loans of

over $1200

{ l, b, a | l, b, a loan a > 1200} Find the names of all customers who have a loan of over $1200

{ c | l, b, a ( c, l borrower l, b, a loan a > 1200)}

Find the names of all customers who have a loan from the Perryridge branch and the loan amount:

{ c, a | l ( c, l borrower b( l, b, a loan b = “Perryridge”))}

or { c, a | l ( c, l borrower l, “Perryridge”, a loan)}

Example Queries Find the names of all customers having a loan, an

account, or both at the Perryridge branch: { c | l ({ c, l borrower

b,a ( l, b, a loan b = “Perryridge”)) a ( c, a depositor b,n ( a, b, n account b = “Perryridge”))}

Find the names of all customers who have an account at all branches located in Brooklyn:

{ c | n ( c, s, n customer) x,y,z ( x, y, z branch y = “Brooklyn”)

a,b ( x, y, z account c,a depositor)}

Reminder: our Mini-U db

STUDENTSsn Name Address

123 smith main str234 jones forbes ave

CLASSc-id c-name unitscis331 d.b. 2cis321 o.s. 2

TAKESSSN c-id grade

123cis331 A234cis331 B

Examples

find all student records

}|{ STUDENTtt

},,|,,{ STUDENTansans

RTC:

Examples

(selection) find student record with ssn=123

Examples

(selection) find student record with ssn=123

}123][|{ ssntSTUDENTttRTC:

},,123|,,123{ STUDENTanan

or

}123,,|,,{ sSTUDENTansans

Examples

(projection) find name of student with ssn=123

},,123|{ STUDENTann

Examples

(projection) find name of student with ssn=123

}),,123(|{ STUDENTanan

need to ‘restrict’ “a”

])}[][

123][(|{

namesnamet

ssnsSTUDENTst

RTC:

Examples cont’d

(union) get records of both PT and FT students

}_

_|{

STUDENTPTt

STUDENTFTtt

RTC:

Examples cont’d

(union) get records of both PT and FT students

}_,,

_,,|,,{

STUDENTPTans

STUDENTFTansans

Examples

difference: find students that are not staff

RTC:

}

|{

STAFFt

STUDENTtt

Examples

difference: find students that are not staff

},,

,,|,,{

STAFFans

STUDENTansans

Cartesian product

eg., dog-breeding: MALE x FEMALE gives all possible couples

MALEnamespikespot

FEMALEnamelassieshiba

x =M.name F.namespike lassiespike shibaspot lassiespot shiba

Cartesian product

find all the pairs of (male, female) - RTC:

]}[][

][][

|{

namefnameft

namemnamemt

FEMALEf

MALEmt

Cartesian product

find all the pairs of (male, female) - RDC:

}

|,{

FEMALEf

MALEmfm

‘Proof’ of equivalence

rel. algebra <-> rel. domain calculus

<-> rel. tuple calculus

Overview - detailed

rel. domain calculus why? details examples equivalence with rel. algebra more examples; ‘safety’ of

expressions

More examples

join: find names of students taking cis351

Reminder: our Mini-U db

STUDENTSsn Name Address

123 smith main str234 jones forbes ave

CLASSc-id c-name units15-413 s.e. 215-412 o.s. 2

TAKESSSN c-id grade

123 15-413 A234 15-413 B

More examples

join: find names of students taking cis351 - in RTC

)}351][

][][

][][(

|{

cisidce

namesnamet

ssnessnsTAKESe

STUDENTst

More examples

join: find names of students taking cis351 - in RDC

)},351,

,,(|{

TAKESgciss

STUDENTansgasn

Sneak preview of QBE:

)},351,

,,(|{

TAKESgciss

STUDENTansgasn

STUDENTSsn Name Address_x P.

TAKESSSN c-id grade_x cis351

Sneak preview of QBE:

STUDENTSsn Name Address_x P.

TAKESSSN c-id grade_x cis351

very user friendly heavily based on RDC very similar to MS Access interface

More examples

3-way join: find names of students taking a 2-unit course - in RTC:

)}2][

][][

][][

][][(

|{

unitsc

namesnamet

idccidce

ssnessnsCLASSc

TAKESeSTUDENTst

selection

projection

join

Reminder: our Mini-U db

STUDENTSsn Name Address

123 smith main str234 jones forbes ave

CLASSc-id c-name unitscis331 d.b. 2cis321 o.s. 2

gradeTAKESSSN c-id

123cis331 A234cis331 B

_x .P

_x _y

_y 2

More examples

3-way join: find names of students taking a 2-unit course

}2,,

,,

,,

.............|{

CLASScnc

TAKESgcs

STUDENTans

n

More examples

3-way join: find names of students taking a 2-unit course

)}

2,,

,,

,,

(,,,,|{

CLASScnc

TAKESgcs

STUDENTans

cngcasn

Even more examples:

self -joins: find Tom’s grandparent(s)

PCp-id c-idMary TomPeter MaryJohn Tom

PCp-id c-idMary TomPeter MaryJohn Tom

Even more examples:

self -joins: find Tom’s grandparent(s)

)}""][

][][

][][(

|{

Tomidcq

idptidpp

idpqidcp

PCqPCpt

Even more examples:

self -joins: find Tom’s grandparent(s)

)}""][

][][

][][(

|{

Tomidcq

idptidpp

idpqidcp

PCqPCpt

)}"",

,(|{

PCTomp

PCpgpg

Even more examples:

self -joins: find Tom’s grandparent(s)

)}"",

,(|{

PCTomp

PCpgpg

Hard examples: DIVISION

find suppliers that shipped all the ABOMB parts

SHIPMENTs# p#s1 p1s2 p1s1 p2s3 p1s5 p3

ABOMBp#p1p2

BAD_Ss#s1

Hard examples: DIVISION

find suppliers that shipped all the ABOMB parts

])))}#[]#[

]#[]#[

(

((|{

ppps

ssst

SHIPMENTs

ABOMBppt

Hard examples: DIVISION

find suppliers that shipped all the ABOMB parts

])))}#[]#[

]#[]#[

(

((|{

ppps

ssst

SHIPMENTs

ABOMBppt

)},

(|{

SHIPMENTps

ABOMBpps

More on division

find students that take all the courses that ssn=123 does (and maybe even more)

)}

])[][1

][][1

(1

)123][((|{

ssnossnt

idctidct

TAKESt

ssntTAKEStto

More on division

find students that take all the courses that ssn=123 does (and maybe even more)

))})',,('

),,123((|{

TAKESgcsg

TAKESgcgcs

Safety of expressions

similar to RTC FORBIDDEN:

},,|,,{ STUDENTansans

Safety of Expressions

{ x1, x2, …, xn | P(x1, x2, …, xn)}

is safe if all of the following hold:1. All values that appear in tuples of the expression are values from dom a(P) (that is, the values appear either in P or in a tuple of a relation mentioned in P )

2. For every “there exists” subformula of the form x (P1(x)), the subformula is true if and only if there is a value x in dom (P1) such that P1(x) is true.

3. For every “for all” subformula of the form x (P1 (x)), the subformula is true if and only if P1(x) is true for all values x from dom (P1).

Overview - detailed

rel. domain calculus + QBE dfn details equivalence to rel. algebra