RelationalAlgebraandCalculus.pptVIP

Relational Calculus Relational Model : Topic 2 Relational Calculus Comes in two flavors: Tuple relational calculus (TRC) and Domain relational calculus (DRC). Calculus has variables, constants, comparison ops, logical connectives and quantifiers. TRC: Variables range over (i.e., get bound to) tuples. DRC: Variables range over domain elements (= field values). Both TRC and DRC are simple subsets of first-order logic. Calculus expressions called formulas. Answer tuple: assignment of constants to variables that makes formula evaluate to true. Domain Relational Calculus Query has the form: DRC Formulas Atomic formula: , or X op Y, or X op constant op is one of Formula: an atomic formula, or , where p and q are formulas, or , where variable X is free in p(X), or , where variable X is free in p(X) Example Instances “Sailors”, “Reserves”, and “Boats” relations Sailors(Id, Name, TechRating, Age) Boats(Id, Name, Color) Reserves(SailorId, BoatId, Date) Find all sailors with a rating above 7 The condition ensures that the domain variables I, N, T and A get bound to fields of the same Sailors tuple. The term to the left of `|’ says that every tuple that satisfies T7 is in the answer. Modify this: find all sailors who are older than 18 or have a rating under 9, and are called ‘Aaron’. Find sailors rated 7 who’ve reserved boat #103 We have used as a shorthand for Find sailors rated 7 who’ve reserved a red boat Find sailors who’ve reserved all boats Find all sailors I such that for each 3-tuple either it is not a tuple in Boats or there is a tuple in Reserves showing that sailor I has reserved it. Find sailors who’ve reserved all boats (again!) Simpler notation, same query. (Much clearer!) To find s