Temple University CIS Dept CIS 331 Principles of

Temple University – CIS Dept. CIS 331– 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 n n n history concepts Formal query languages n n n relational algebra rel. tuple calculus rel. domain calculus

General Overview - rel. model n n n history concepts Formal query languages n n n relational algebra rel. tuple calculus rel. domain calculus

Overview - detailed n rel. tuple calculus n n n why? details examples equivalence with rel. algebra more examples; ‘safety’ of expressions rel. domain calculus + QBE

Safety of expressions n FORBIDDEN: It has infinite output!! n Instead, always use

Safety of expressions n n n 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 n n n 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 n n 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 n n 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[customername]) 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[customername])

Example Queries n n 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 n 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 n 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 n n relational model Formal query languages n n n relational algebra rel. tuple calculus rel. domain calculus

Overview - detailed n rel. tuple calculus n n dfn details equivalence to rel. algebra rel. domain calculus + QBE

Rel. domain calculus (RDC) n n 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 n find STUDENT record with ssn=123’

Details n Like R. T. C - symbols allowed: n quantifiers

Details n but: domain (= column) variables, as opposed to tuple variables, e. g. : ssn name address

Domain Relational Calculus n n A nonprocedural query language equivalent in power to the tuple relational calculus Each query is an expression of the form: { x 1, x 2, …, xn | P (x 1, x 2, …, xn)} n n x 1, x 2, …, xn represent domain variables P represents a formula similar to that of the predicate calculus

Example queries n Find the branch-name, loan-number, and amount for loans of over $1200 n { l, b, a | l, b, a loan a > 1200} Find the names of all customers who have a loan of over $1200 n { 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 n n 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 CLASS c-id c-name units cis 331 d. b. 2 cis 321 o. s. 2 TAKES SSN c-id 123 cis 331 234 cis 331 grade A B

Examples n find all student records RTC:

Examples n (selection) find student record with ssn=123

Examples n (selection) find student record with ssn=123 or RTC:

Examples n (projection) find name of student with ssn=123

Examples n (projection) find name of student with ssn=123 need to ‘restrict’ “a” RTC:

Examples cont’d n (union) get records of both PT and FT students RTC:

Examples cont’d n (union) get records of both PT and FT students

Examples n difference: find students that are not staff RTC:

Examples n difference: find students that are not staff

Cartesian product n n eg. , dog-breeding: MALE x FEMALE gives all possible couples x =

Cartesian product n find all the pairs of (male, female) RTC:

Cartesian product n find all the pairs of (male, female) RDC:

‘Proof’ of equivalence rel. algebra <-> rel. domain calculus <-> rel. tuple calculus n

Overview - detailed n rel. domain calculus n n n why? details examples equivalence with rel. algebra more examples; ‘safety’ of expressions

More examples n join: find names of students taking cis 351

Reminder: our Mini-U db

More examples n join: find names of students taking cis 351 - in RTC

More examples n join: find names of students taking cis 351 - in RDC

Sneak preview of QBE: TAKES SSN c-id grade _x cis 351

Sneak preview of QBE: n n n very user friendly heavily based on RDC very similar to MS Access interface TAKES SSN c-id grade _x cis 351

More examples n 3 -way join: find names of students taking a 2 -unit course - in RTC: join projection selection

Reminder: our Mini-U db _x . P _y 2 CLASS c-id c-name units cis 331 d. b. 2 cis 321 o. s. 2 TAKES SSN c-id 123 cis 331 234 cis 331 _x _y grade A B

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

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

Even more examples: n self -joins: find Tom’s grandparent(s)

Even more examples: n self -joins: find Tom’s grandparent(s)

Even more examples: n self -joins: find Tom’s grandparent(s)

Even more examples: n self -joins: find Tom’s grandparent(s)

Hard examples: DIVISION n find suppliers that shipped all the ABOMB parts

Hard examples: DIVISION n find suppliers that shipped all the ABOMB parts

Hard examples: DIVISION n find suppliers that shipped all the ABOMB parts

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

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

Safety of expressions n n similar to RTC FORBIDDEN:

Safety of Expressions { x 1, x 2, …, xn | P(x 1, x 2, …, 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 (P 1(x)), the subformula is true if and only if there is a value x in dom (P 1) such that P 1(x) is true. 3. For every “for all” subformula of the form x (P 1 (x)), the subformula is true if and only if P 1(x) is true for all values x from dom (P 1).

Overview - detailed n rel. domain calculus + QBE n n n dfn details equivalence to rel. algebra