Chapter 8 Relational Calculus Prof YinFu Huang CSIE

Chapter 8 Relational Calculus Prof. Yin-Fu Huang CSIE, NYUST

8. 1 Introduction n The algebra provides a set of explicit operators that can be used to tell the system how to construct some desired relation from certain given relations. n The calculus merely provides a notation for stating the definition of that desired relation in terms of those given relations. n The query “Get supplier numbers and cities for suppliers who supply part P 2” Ø Algebra: 1. join supplier and shipment over S#. 2. restrict the result of that join to tuples for part P 2. 3. project the result of that restriction over S# and City. Ø Calculus: Get S# and City for suppliers such that there exists a shipment Sp with the same S# value and with P# value P 2. Advanced Database System 2

8. 1 Introduction (Cont. ) n n prescriptive (Algebra) vs. descriptive (Calculus) procedural (Algebra) vs. nonprocedural (Calculus) The algebra and the calculus are logically equivalent. Relation calculus is based on a branch of mathematical logic called predicate calculus. n Tuple calculus: range variables over relations n Language called QUEL Range of Sx Is S; Retrieve (Sx. S#) Where Sx. City=“London”; n Domain calculus: range variables over domains n Language called Query-By-Example (QBE) Advanced Database System 3

8. 2 Tuple Calculus n Syntax (See Page 215 -216) <relation exp> : : = Relation {<tuple exp commalist>} | <relvar name> | <relation op inv> | <with exp> | <introduced name> | (<relation exp>) <range var def> : : = Rangevar <range var name> Ranges Over <relation exp commalist>; <range attribute ref> : : = <range var name>. <attribute name> [ As <attribute name> ] Advanced Database System 4

8. 2 Tuple Calculus (Cont. ) <bool exp> : : = …all the usual possibilities, together with: | <quantified bool exp>: : = <quantifier> <range var name> ( <bool exp> ) <quantifier> : : = Exists | Forall <relation op inv> : : = <proto tuple> [ Where <bool exp> ] <proto tuple> : : = …see the body of the text Advanced Database System 5

8. 2 Tuple Calculus (Cont. ) n Range variables e. g. Rangevar Sx Ranges Over S; Rangevar Spx Ranges Over Sp; Rangevar Su Ranges Over ( Sx Where Sx. City = ‘London’ ), ( Sx Where Exists Spx ( Spx. S# = Sx. S# And Spx. P# = P#(‘P 1’) ) ); Advanced Database System 6

8. 2 Tuple Calculus (Cont. ) n Free and bound variable references Let V be a range variable Ø References to V in Not p ⇒ according as they are free or bound in p Ø References to V in p And q and p Or q ⇒ according as they are free or bound in p or q Ø References to V that are free in p are bound in Exists V (p) and Forall V (p) Advanced Database System 7

8. 2 Tuple Calculus (Cont. ) Examples Sx. S#=S#(‘S 1’) Sx. S#=Spx. S# Spx. P#≠Px. P# ⇒ Sx, Px, and Spx are free Px. Weight < Weight(15. 5) And Px. City=’Oslo’ Not (Sx. City=‘London’) Sx. S#=Spx. S# And Spx. P#≠Px. P# Px. Color=Color(‘Red’) Or Px. City=‘London’ ⇒ Sx, Px, and Spx are free Exists Spx (Spx. S#=Sx. S# And Spx. P#=P#(‘P 2’)) Forall Px (Px. Color=Color(‘Red’)) ⇒ Spx and Px are bound and Sx is free. Advanced Database System 8

8. 2 Tuple Calculus (Cont. ) n Quantifiers Exists V ( p (V) ) ⇒ False Or p(t 1) Or. . . Or p(tm) Forall V ( p(V) ) ⇒ True And p(t 1) And. . . And p(tm) n Free and bound variable references revisited Exists x ( x > 3 ) ⇔ Exists y ( y > 3 ) Exists x ( x > 3 ) and x < 0 ⇔ Exists y ( y > 3 ) and x < 0 Exists y ( y > 3 ) and y < 0 Advanced Database System 9

8. 3 Examples n Exam 1: {Sx. S#, Sx. Status} Where Sx. City=‘Paris’ And Sx. Status > 20 n Exam 2: {Sx. S# As Sa, Sy. S# As Sb} Where Sx. City=Sy. City And Sx. S# < Sy. S# n Exam 3: Sx Where Exists Spx (Spx. S#=Sx. S# And Spx. P#=P#(‘P 2’)) n Exam 4: Sx. Sname Where Exists Spx (Sx. S#=Spx. S# And Exists Px (Px. P#=Spx. P# And Px. Color=Color(‘Red’))) n Exam 5: Sx. Sname Where Exists Spx ( Exists Spy (Sx. S#=Spx. S# And (Spx. P#=Spy. P# And Spy. S#=S#(‘S 2’))) Advanced Database System 10

8. 3 Examples (Cont. ) n Exam 6: Sx. Sname Where Forall Px ( Exists Spx (Spx. S#=Sx. S# And Spx. P#=Px. P#)) n Exam 7: Sx. Sname Where Not Exists Spx (Spx. S#=Sx. S# And Spx. P#=P#(‘P 2’)) n Exam 8: Sx. S# Where Forall Spx (Spx. S#≠S#(‘S 2’) Or Exists Spy (Spy. S#=Sx. S# And Spy. P#=Spx. P#)) If p Then q End If ⇔ (Not p) Or q n Exam 9: Rangevar Pu Ranges Over (Px. P# Where Px. Weight > Weight(16. 0)), (Spx. P# Where Spx. S#=S#(‘S 2’)); Pu. P# Advanced Database System 11

8. 4 Calculus vs. Algebra n The algebra is at least as powerful as the calculus. n Codd‘s reduction algorithm: by which an arbitrary expression of the calculus could be reduced to a semantically equivalent expression of the algebra. n Example Query: Get names and cities for suppliers who supply at least one Athens project with at least 50 of every part. A calculus expression: {Sx. Sname, Sx. City} Where Exists Jx Forall Px Exists Spjx { (Jx. City=‘Athens’ And Jx. J#=Spjx. J# And Px. P#=Spjx. P# And Sx. S#=Spjx. S# And Spjx. Qty≧Qty ( 50 ) ) Advanced Database System 12

8. 4 Calculus vs. Algebra (Cont. ) (See Page 226 -228 & Fig. 8. 1) Step 1: Advanced Database System 13

8. 4 Calculus vs. Algebra (Cont. ) Step 2: Cartesian product 5＊6＊2＊24 =1440 tuples Step 3: Restriction Jx. J#=Spjx. J# And Px. P#=Spjx. P# And Sx. S#=Spjx. S# Advanced Database System 14

8. 4 Calculus vs. Algebra (Cont. ) Step 4: Apply the quantifiers from right to left Exists V ⇒ project the current intermediate result to eliminate all attributes of relation r. Forall V ⇒ divide the current intermediate result by the “restricted range” relation associated with V. Step 5: Projection Advanced Database System 15

8. 4 Calculus vs. Algebra (Cont. ) n Why Codd defined precisely the eight algebraic operators? 1. Inherently implementable 2. A yardstick for measuring the expressive power of any given database language n A language is said to be relationally complete if it is at least as powerful as the calculus. n Any given language L is complete, if it is sufficient to show that L includes analogs of each of the eight algebraic operators. e. g. SQL n Relational completeness does not necessarily imply any other kind of completeness. Advanced Database System 16

8. 5 Computational Capabilities n Exam 1: {Px. P#, Px. Weight＊454 As Gmwt} Where Px. Weight＊454 > Weight(10000. 0) n Exam 2: {Sx, ‘Supplier’ As Tag} n Exam 3: {Spx, Px. Weight＊Spx. Qty As Shipwt} Where Px. P#=Spx. P# n Exam 4: {Px. P#, Sum(Spx Where Spx. P#=Px. P#, Qty) As Totqty} n Exam 5: Sum(Spx, Qty) As Grandtotal n Exam 6: {Sx. S#, Count(Spx Where Spx. S#=Sx. S#) As #_Of_Part} n Exam 7: Rangevar Py Ranges Over P; Px. City Where Count (Py Where Py. City=Px. City And Py. Color=(‘Red’)) > 5 Advanced Database System 17

8. 6 SQL Facilities n Exam 1: Select Px. Color, Px. City From P As Px Where Px. City <> ‘Paris’ And Px. Weight > Weight (10. 0); n Exam 2: Select P. P#, P. Weight＊454 As Gmwt From P; n Exam 3: 1. Select S. *, P. P#, P. Pname, P. Color, P. Weight From S, P Where S. City=P. City; 2. S Join P Using City; 3. S Natural Join P; n Exam 4: Select Distinct S. City As Scity, P. City As Pcity From S Join SP Using S# Join P Using P#; n Exam 5: Select A. S# As Sa, B. S# As Sb From S As A, S As B Where A. City=B. City And A. S# < B. S#; Advanced Database System 18

8. 6 SQL Facilities (Cont. ) n Exam 6: Select Count(*) As N From S; n Exam 7: Select Max(Sp. Qty) As Maxq, Min(Sp. Qty) As Minq From Sp Where Sp. P#=P#(‘P 2’); n Exam 8: Select Sp. P#, Sum(Sp. Qty) As Totqty From Sp Group By Sp. P#; n Exam 9: Select Sp. P# From Sp Group By Sp. P# Having Count(Sp. S#) > 1; n Exam 10: Select Distinct S. Sname From S Where S. S# In (Select Sp. S# From Sp Where Sp. P#=P#(‘P 2’)); Advanced Database System 19

8. 6 SQL Facilities (Cont. ) n Exam 11: Select Distinct S. Sname n Exam 13: Select Distinct S. Sname From S Where S. S# In Where Exists (Select Sp. S# (Select * From Sp Where Sp. P# In Where Sp. S#=S. S# (Select P. P# And Sp. P#=P#(‘P 2’)); From P Where P. Color=Color(‘Red’))); n Exam 12: Select S. S# From S Where S. Status < (Select Max(S. Status) From S); Advanced Database System 20

8. 6 SQL Facilities (Cont. ) n Exam 14: Select Distinct S. Sname n Exam 16: From S Where Not Exists (Select * From Sp Where Sp. S#=S. S# And Sp. P#=P#(‘P 2’)); Select P. P# From P Where P. Weight > P#(16. 0) Union Select Sp. P# From Sp Where Sp. S#=S#(‘S 2’) n Exam 17: n Exam 15: Select Distinct S. Sname With T 1 As From S (Select P. P#, P. Weight＊454 As Gmwt Where Not Exists From P) (Select * Select T 1. P#, T 1. Gmwt From P From T 1 Where Not Exists Where T 1. Gmwt > Weight(10000. 0); (Select * From Sp Where Sp. S#=S. S# And Sp. P#=P. P#)); Advanced Database System 21

8. 7 Domain Calculus n The most immediately obvious difference between the domain and the tuple calculus is that the former supports an additional form of <bool exp> called a membership condition. R { <pair commalist> } e. g. Sx Sx Where S {S# Sx} Sx Where S {S# Sx, City ‘London’} {Sx, Cityx} Where S {S# Sx, Cityx} And Sp {S# Sx, P# P#(‘P 2’)} {Sx, Px} Where S {S# Sx, Cityx} And P {P# Px, Cityy} And Cityx≠Cityy Advanced Database System 22

8. 7 Domain Calculus (Cont. ) n Exam 1: Sx Where Exists Statusx (Statusx > 20 And S {S# Sx, Statusx, City ‘Paris’}) n Exam 2: {Sx As Sa, Sy As Sb} Where Exists Cityz (S {S# Sx, Cityz} And S {S# Sy, Cityz} And Sx < Sy) n Exam 3: Namex Where Exists Sx Exists Px (S {S# Sx, Sname Namex} And Sp {S# Sx, P# Px} And P {P# Px, Color(‘Red’)}) n Exam 4: Namex Where Exists Sx Exists Px (S {S# Sx, Sname Namex} And Sp {S# Sx, P# Px} And Sp {S# S#(‘S 2’), P# Px}) Advanced Database System 23

8. 7 Domain Calculus (Cont. ) n Exam 5: Namex Where Exists Sx (S {S# Sx, Sname Namex} And Forall Px (If P {P# Px} Then Sp {S# Sx, P# Px} End If)) n Exam 6: Namex Where Exists Sx (S {S# Sx, Sname Namex} And Not Sp {S# Sx, P# P#(‘P 2’)}) n Exam 7: Sx Where Forall Px (If Sp {S# S#(‘S 2’), P# Px} Then Sp {S# Sx, P# Px} End If) n Exam 8: Px Where Exists Weightx (P {P# Px, Weightx} And Weightx > Weight(16. 0)) Or Sp {S# S#(‘S 2’), P# Px} Advanced Database System 24

8. 8 Query-By-Example n Exam 1: S S# Sname P. n Exam 2: SP S# UNQ. n Exam 3: Status City > 20 Paris P# Qty P. S S# Status City P. AO(2). P. DO(1). Paris n Exam 4: S S# Status P. P. City Paris > 20 Advanced Database System 25

8. 8 Query-By-Example (Cont. ) n Exam 5: n Exam 6: P P P# Weight P. >= 16. 0 <= 19. 0 P# Weight Gmwt P. _PW P. _PW * 454 S# Sname _SX P. S# City _SX _CX n Exam 7: S SP S# P# _SX P 2 n Exam 8: S P P# City _PX _CX Advanced Database System P. _SX _PX 26

8. 8 Query-By-Example (Cont. ) n Exam 9: S S# City _SX _CZ _SY _CZ P. _SX _SY n Exam 10: SP n Exam 11: SP S# S# P# Qty P 2 _QX P# Qty G. P. _QY P. SUM. _QX P. SUM. _QY n Exam 12: SP S# P# Conditions _SX G. P. CNT. _SX > 1 Advanced Database System 27

8. 8 Query-By-Example (Cont. ) n Exam 13: P P# Weight _PX > 16. 0 SP S# P# S 2 _PY P. _PX P. _PY n Exam 14: n Exam 15: P P# I. P 7 SP S# Pname Color Weight 24. 0 P# City Athens Qty D. > 300 n Exam 16: P P# P 2 Pname Color Weight City U. Yellow _WT U. _WT+5 U. Oslo Advanced Database System 28

8. 8 Query-By-Example (Cont. ) n Exam 17: SP S# Qty _SX U. 5 S S# City _SX London Advanced Database System 29

The End. Advanced Database System 30