1 / 15

RELATIONAL CALCULUS

RELATIONAL CALCULUS. Rohit Khokher. INTRODUCTION . Relational calculus is a formal query language where the queries are expressed as variables and formulas on these variables.

oral
Télécharger la présentation

RELATIONAL CALCULUS

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. RELATIONAL CALCULUS RohitKhokher

  2. INTRODUCTION • Relational calculus is a formal query language where the queries are expressed as variables and formulas on these variables. • Relational calculus is considered to be a non-procedural language because a relational calculus expression specifies what is to be retrieved rather than how to retrieve it. It is up to the RDBMS to convert these non-procedural queries into equivalent, efficient procedural queries.

  3. Relational calculus is a collective term used for TUPLE calculus and DOMAIN calculus, which are declarative query languages based on mathematical logic. These two formal languages form the basis for two more user friendly languages: QBE(Query-By-Example) and Datalog.

  4. TUPLE RELATIONAL CALCULUS • It is a non-procedural query language which describes the required relation without giving a specific procedure for obtaining that information. • It is based on specifying a number of tuple variables. This implies that a variable may take any individual tuple from that relation as its value. • A tuple-relation-calculus expression is of the form • {t|P(t)} • i.e it is a set of all tuples t such that predicate P is true for t

  5. In a tuple calculus expression we need to specify the following information:

  6. A tuple-relational-calculus formula is build up of atoms. An atom has one of the following forms: • sЄr, where s is a tuple variable and r is a relation. • s[x]Ѳu[y], where s and u are tuple variables, x is an attribute on which x is defined, y is an attribute on which u is defined and Ѳ is a comparison operator(<,<=,=,!=,>,>=). • S[x]Ѳc where c is a constant in the domain of attribute x.

  7. EXAMPLE QUERIES

  8. Consider the following schema: • branch(branch_name, branch_city, assets) • customer(c_name, c_street, c_city) • loan(loan_number, branch_name, amount) • borrower(c_name, loan_number) • account(account_number, branch_name, balance) • depositor(c_name, account_number)

  9. Find the branch name, loan number and amount for loans of over Rs. 1200: • { t|t Є loan ^ t[amount] > 1200 } • It reads as “there exists a tuple t in relation loan such that the amount for it is greater than Rs.1200”.

  10. Find loan number for each loan of an amount greater than Rs. 1200: • { t|Эs Є loan(t[loan_number]=s[loan_number] ^ s[amount]>1200} • It reads as “the set of all tuples t such that there exists a tuple s in relation loan for which the values of t and s for the loan_number attribute are equal, and the value of s for the amount attribute is greater than Rs. 1200.” • Tuple variable t is only defined on the loan_number attribute, since that is the only attribute having a condition specified for t.

  11. Find the names of all customers who have a loan from the Perryridge branch: • { t|Эs Є borrower (t[c_name]=s[c_name] ^ Эu Є loan (u[loan_number]=s[loan_number] ^ u[branch_name]=“Perryridge”))} • It returns to us “ the set of all (c_name) tuples for which the customer has a loan that is at the Perryridge branch. • Tuple variable s is restricted to pertain to the same loan number as s.

  12. Find all the customers who have a loan, an account, or both at the bank: • {t|Эs Є borrower (t[c_name]=s[c_name]) U Эu Є depositor (t[c_name]=u[c_name])} • It gives us the set of all c_nametuples for which at least one of the following holds: • The c_name appears in some tuple of the borrower relation. • The c_name appears in some tuple of the depositor relation. • If some customer has both a loan and an account, that customer appears only once in the result.

  13. Find the customers who have both an account and a loan at the bank: • {t|Эs Є borrower (t[c_name]=s[c_name]) ^ Эu Є borrower (t[c_name]=s[c_name])}

  14. Find all customers who have an account at the bank but do not have a loan from the bank: • {t|Эu Є depositor (t[c_name]=u[c_name]) ^ ~Эs Є borrower (t[c_name]=s[c_name])}

  15. Find all customers who have an account at all branches located in Brooklyn: • t|Эr Є customer (r[c_name]=t[c_name]) ^ ( Ѵu Є branch (u[branch_city]=“Brooklyn” => Эs Є depositor (t[c_name]=s[c_name] ^ Эw Є account (w[account_number]=s[account_number] ^ w[branch_name]=u[branch_name]))))}

More Related