1 / 12

Relational Calculus

A nonprocedural query language, where each query is of the form { t | P ( t ) } When we substitute values for the arguments, function yields an expression, called a proposition , which can be either true or false. It is the set of all tuples t such that predicate P is true for t

cameo
Download Presentation

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. A nonprocedural query language, where each query is of the form {t | P (t) } When we substitute values for the arguments, function yields an expression, called a proposition, which can be either true or false. It is the set of all tuples t such that predicate P is true for t t is a tuple variable, t.A denotes the value of tuple t on attribute A t r denotes that tuple t is in relation r P is a formula similar to that of the predicate calculus Relational calculus query specifies what is to be retrieved rather than how to retrieve it. When applied to databases, relational calculus has two forms: tuple and domain. Relational Calculus

  2. Interested in finding tuples for which a predicate is true. Based on use of tuple variables. Tuple variable is a variable that ‘ranges over’ a named relation: i.e., variable whose only permitted values are tuples of the relation. Specify range of a tuple variable S as the Staff relation as: S  Staff Or, Staff(S) Tuple Relational Calculus

  3. To find details of all staff earning more than £10,000: {s | Staff(s)AND s.salary > 10000} To find a particular attribute, such as salary, write: {s.salary | Staff(s) AND s.salary > 10000)} Tuple Relational Calculus - Example

  4. A well-formed Predicate formula, P(t) is made out of atoms: Set of attributes and constants Set of comparison operators: (e.g., , , , , , ) Set of connectives: AND (), OR (v)‚ NOT (~) Set of quantifiers: t r (Q(t)) ”there exists” t r (Q(t)) “for all” tuples t in relation r Tuple variables qualified by " or $ are calledbound variables, otherwise called free variables. Only free Variables must show up on the left side of the ( | ). Predicate Calculus Formula

  5. Used in formulae that must be true for at least one instance, such as: {s| Staff(s) AND bBranch (b.branchNo = s.branchNo ANDb.city = ‘London’)} Means ‘There exists a Branch tuple with same branchNo as the branchNo of the current Staff tuple, S, and is located in London’. Existential quantifier

  6. Used in statements about every instance, such as: ("b) (b.city  ‘Paris’)) Means ‘For all (for every) Branch tuples, b, the address is not in Paris’. Can also use: ~( b) (b.city = ‘Paris’)) Means ‘There are no branches with an address in Paris’. Universal Qualifier

  7. Adjacent existential quantifiers commute: TTranscript(T1 Teaching (…)) is same as T1Teaching(T Transcript (…)) Adjacent existential and Universal quantifiers do not commute: T Transcript (T1 Teaching (…)) is different from:  T1 Teaching (T Transcript (…)) Can Adjacent Qualifiers Commute?

  8. List the names of all managers who earn more than £25,000. {S.fName, S.lName | Staff(S) S.position = ‘Manager’  S.salary > 25000} List the staff who manage properties for rent in Glasgow. {S | Staff (S) $PPropertyForRent (P.staffNo = S.staffNo)Ù P.city = ‘Glasgow’)} Example - Tuple Relational Calculus

  9. List the names of clients who have viewed at least one property for rent in Glasgow. {C.fName, C.lName | Client (C) Ù$V Viewing ( $PPropertyForRent ( C.clientNo = V.clientNo Ù V.propertyNo=P.propertyNoÙ P.city =‘Glasgow’))} Example - Tuple Relational Calculus

  10. Consider the following relations: Student(Id, Name, Status, Address) Professor(Id, Name, DeptId) Course(CrsCode, DeptId, CrsName, Description) Transcript(StudId, CrsCode, Semester, Grade) Teaching(ProfId, CrsCode, Semester) Student Registration Example

  11. Consider the following relations: Student(Id, Name, Status, Address) Professor(Id, Name, DeptId) Course(CrsCode, DeptId, CrsName, Description) Transcript(StudId, CrsCode, Semester, Grade) Teaching(ProfId, CrsCode, Semester) Find all teaching records for courses offered in the fall of 1997: {t | teaching(t) AND t.semester = F1997} Find names of the students and the course codes that they have taken in fall 1998: {R |  S  Student( TTranscript ( R.Name=S.Name AND R.CrsCode = T.CrsCode AND S.StuId=T.StuId AND T.semester = ‘F1998))} Example Queries

  12. Consider the following relations: Student(Id, Name, Status, Address) Professor(Id, Name, DeptId) Course(CrsCode, DeptId, CrsName, Description) Transcript(StudId, CrsCode, Semester, Grade) Teaching(ProfId, CrsCode, Semester) List the names of all professors who have taught ENSF 301. {P.Name | Professor(P) AND T Teaching ( P.Id = T.ProfId AND T.CrsCode = ‘ENSF 301’))} List courses that have been taken by every student. {C | Course(C) AND S Student ( T Transcript (T.StudId = S.Id AND T.CrsCode = C.CrsCode))} Example Queries

More Related