Relational CalculusTuple Relational CalculusQuery ExamplesExamples (Cont’d)Slide 5Slide 6Slide 7Slide 8Formal DefinitionFormal Definition (Cont’d)Slide 11Safety of ExpressionsDomain Relational CalculusSlide 14Slide 15Slide 16Example queriesSlide 18Slide 19Safety of expressionsSafety of expressions (Cont’d)Relational CalculusRelational CalculusAmeetinder SinghAmeetinder SinghCS 157ACS 157ATuple Relational CalculusTuple Relational Calculusnon-procedural query language as compared to non-procedural query language as compared to relational algebra that is procedural. relational algebra that is procedural. only requires the user to specify what only requires the user to specify what information is required and not how that information is required and not how that information should be obtained.information should be obtained.A query in tuple relational calculus expressed asA query in tuple relational calculus expressed as {t | P(t)}{t | P(t)}i.e. set of all tuples t such that P is true for t i.e. set of all tuples t such that P is true for tQuery Examples Query Examples To find the To find the branch-name, loan-number, branch-name, loan-number, andand amount amount for for loanloans over $1000:s over $1000: {t | t {t | t loanloan t[ t[amountamount] > 1000} ] > 1000} To find only the To find only the loan-numberloan-number attribute, rather attribute, rather than all the attributes of the than all the attributes of the loanloan relation: relation: {t | {t | s s loanloan ( t ( t[loan-number[loan-number] = s[] = s[loan-loan-numbernumber] ] s[ s[amountamount] > 1000)} ] > 1000)} This query will retrieve those tuples in This query will retrieve those tuples in loanloan--number such that there is a tuple in number such that there is a tuple in loanloan with with the amount attribute > 1000 the amount attribute > 1000Examples (Cont’d)Examples (Cont’d)To find the names of all customers who borrowed a To find the names of all customers who borrowed a loanloan from the Downtown branch. from the Downtown branch. {t | {t | s s borroweborrower ( t[r ( t[customer-namecustomer-name] = s[] = s[customer-customer-namename] ] u u loanloan (u[ (u[loan-numberloan-number] = s[] = s[loan-numberloan-number] ] u[u[branch-namebranch-name] = “Downtown”))}] = “Downtown”))} requires two “there exists” clauses since it involves two requires two “there exists” clauses since it involves two relations relations retrieves the set of all (retrieves the set of all (customer-namecustomer-name) tuples for ) tuples for which the customer has a which the customer has a loanloan from the Downtown from the Downtown branch branchExamples (Cont’d)Examples (Cont’d)To find customers who have a To find customers who have a loanloan, an account, or both at the , an account, or both at the bank bank {t | {t | s s borroweborrower ( t[r ( t[customer-namecustomer-name] = s[] = s[customer-namecustomer-name]) ]) u u depositordepositor (t[ (t[customer-namecustomer-name] = u[] = u[customercustomer--namename] )} ] )} If a customer has both account and a If a customer has both account and a loanloan from the bank, the from the bank, the customer-name customer-name only appears once only appears once Changing (Changing () to () to () in the previous query will generate results with ) in the previous query will generate results with the set of customer-name tuples who have both an account and a the set of customer-name tuples who have both an account and a loanloan at the bank. at the bank.Examples (Cont’d)Examples (Cont’d)To find all the customers who have an account To find all the customers who have an account at the bank but do not have a at the bank but do not have a loanloan from the from the bank, we use the not (bank, we use the not () symbol in the ) symbol in the previous expression previous expression {t | {t | s s borroweborrower ( t[r ( t[customer-namecustomer-name] = ] = s[s[customer-namecustomer-name]) ]) u u depositordepositor ( ( t[t[customer-namecustomer-name] = u[] = u[customercustomer--namename] )} ] )}Examples (Cont’d)Examples (Cont’d)Some queries require the use of Some queries require the use of implication (implication (). P). PQ means, “if P is true, Q means, “if P is true, then Q must be true.” then Q must be true.” To find all customers who have an To find all customers who have an account at all branches located in account at all branches located in Brooklyn, we write a query using “for all” Brooklyn, we write a query using “for all” (() construct as follows:) construct as follows:Examples (Cont’d)Examples (Cont’d){t | {t | r r customercustomer ( r[ ( r[customer-namecustomer-name] = t[] = t[customer-customer-namename]) ]) ( ( u u branch (u[branch-city] branch (u[branch-city] = “Brooklyn” = “Brooklyn” s s depositordepositor (t[ (t[customer-namecustomer-name] = s[] = s[customercustomer--namename] ] w w accountaccount (w[ (w[account-numberaccount-number] = s[] = s[account-account-numbernumber] ] w[ w[branch-namebranch-name] = s[] = s[branch-namebranch-name]))))} ]))))} this is interpreted as “the set of all customers such that, this is interpreted as “the set of all customers such that, for all tuples u in the for all tuples u in the branchbranch relation, if the value of u on relation, if the value of u on attribute attribute branch-citybranch-city is Brooklyn, then the customer has is Brooklyn, then the customer has an account at the branch whose name appears in the an account at the branch whose name appears in the branch-namebranch-name attribute of u.” attribute of u.”Formal DefinitionFormal DefinitionA tuple-relational-calculus expression is A tuple-relational-calculus expression is in the formin the form {t | P(t)} {t | P(t)} where P is a formula where P is a formula The formula in tuple-relational-calculus is The formula in tuple-relational-calculus is built up out of atoms. built up out of atoms.Formal Definition (Cont’d)Formal Definition (Cont’d) An atom can have one of the following forms:An atom can have one of the
View Full Document
Unlocking...