130 likes | 312 Views
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
E N D
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
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
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
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
Used in formulae that must be true for at least one instance, such as: {s| Staff(s) AND bBranch (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
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
Adjacent existential quantifiers commute: TTranscript(T1 Teaching (…)) is same as T1Teaching(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?
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) $PPropertyForRent (P.staffNo = S.staffNo)Ù P.city = ‘Glasgow’)} Example - Tuple Relational Calculus
List the names of clients who have viewed at least one property for rent in Glasgow. {C.fName, C.lName | Client (C) Ù$V Viewing ( $PPropertyForRent ( C.clientNo = V.clientNo Ù V.propertyNo=P.propertyNoÙ P.city =‘Glasgow’))} Example - Tuple Relational Calculus
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
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( TTranscript ( R.Name=S.Name AND R.CrsCode = T.CrsCode AND S.StuId=T.StuId AND T.semester = ‘F1998))} Example Queries
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