1 / 30

8.1 Introduction

8.1 Introduction. 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.

ramla
Download Presentation

8.1 Introduction

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. 8.1 Introduction • 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. • The calculus merely provides a notation for stating the definition of that desired relation in terms of those given relations. • The query “Get supplier numbers and cities for suppliers who supply part P2” • Algebra: 1. join supplier and shipment over S#. 2. restrict the result of that join to tuples for part P2. 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 P2. Advanced Database System

  2. 8.1 Introduction (Cont.) • 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. • Tuple calculus: range variables over relations • Language called QUEL Range of Sx Is S; Retrieve (Sx.S#) Where Sx.City=“London”; • Domain calculus: range variables over domains • Language called Query-By-Example (QBE) Advanced Database System

  3. 8.2 Tuple Calculus • 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> • <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.) • 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#(‘P1’) ) ); Advanced Database System

  6. 8.2 Tuple Calculus (Cont.) • 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#(‘S1’) 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#(‘P2’)) Forall Px (Px.Color=Color(‘Red’)) ⇒ Spx and Px are bound and Sx is free. Advanced Database System

  8. 8.2 Tuple Calculus (Cont.) • Quantifiers Exists V ( p (V) ) ⇒ False Or p(t1) Or ... Or p(tm) Forall V ( p(V) ) ⇒ True And p(t1) And ... And p(tm) • 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 • Exam 1: {Sx.S#, Sx.Status} Where Sx.City=‘Paris’ And Sx.Status > 20 • Exam 2: {Sx.S# As Sa, Sy.S# As Sb} Where Sx.City=Sy.City And Sx.S# < Sy.S# • Exam 3: Sx Where Exists Spx (Spx.S#=Sx.S# And Spx.P#=P#(‘P2’)) • Exam 4: Sx.Sname Where Exists Spx (Sx.S#=Spx.S# And Exists Px (Px.P#=Spx.P# And Px.Color=Color(‘Red’))) • Exam 5: Sx.Sname Where Exists Spx ( Exists Spy (Sx.S#=Spx.S# And (Spx.P#=Spy.P# And Spy.S#=S#(‘S2’))) Advanced Database System

  10. 8.3 Examples (Cont.) • Exam 6: Sx.Sname Where Forall Px ( Exists Spx (Spx.S#=Sx.S# And Spx.P#=Px.P#)) • Exam 7: Sx.Sname Where Not Exists Spx (Spx.S#=Sx.S# And Spx.P#=P#(‘P2’)) • Exam 8: Sx.S# Where Forall Spx (Spx.S#≠S#(‘S2’) Or Exists Spy (Spy.S#=Sx.S# And Spy.P#=Spx.P#)) If p Then q End If ⇔ (Not p) Or q • Exam 9: Rangevar Pu Ranges Over (Px.P# Where Px.Weight > Weight(16.0)), (Spx.P# Where Spx.S#=S#(‘S2’)); Pu.P# Advanced Database System

  11. 8.4 Calculus vs. Algebra • The algebra is at least as powerful as the calculus. • Codd‘s reduction algorithm: by which an arbitrary expression of the calculus could be reduced to a semantically equivalent expression of the algebra. • 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.) • Why Codd defined precisely the eight algebraic operators? 1. Inherently implementable 2. A yardstick for measuring the expressive power of any given database language • A language is said to be relationally complete if it is at least as powerful as the calculus. • 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 • Relational completeness does not necessarily imply any other kind of completeness. Advanced Database System

  16. 8.5 Computational Capabilities • Exam 1: {Px.P#, Px.Weight＊454 As Gmwt} Where Px.Weight＊454 > Weight(10000.0) • Exam 2: {Sx, ‘Supplier’ As Tag} • Exam 3: {Spx, Px.Weight＊Spx.Qty As Shipwt} Where Px.P#=Spx.P# • Exam 4: {Px.P#, Sum(Spx Where Spx.P#=Px.P#, Qty) As Totqty} • Exam 5: Sum(Spx, Qty) As Grandtotal • Exam 6: {Sx.S#, Count(Spx Where Spx.S#=Sx.S#) As #_Of_Part} • 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 • Exam 1: Select Px.Color, Px.City From P As Px Where Px.City <> ‘Paris’ And Px.Weight > Weight (10.0); • Exam 2: Select P.P#, P.Weight＊454 As Gmwt From P; • 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; • Exam 4: Select Distinct S.City As Scity, P.City As Pcity From S Join SP Using S# Join P Using P#; • Exam5: 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.) • Exam 6: Select Count(*) As N From S; • Exam 7: Select Max(Sp.Qty) As Maxq, Min(Sp.Qty) As Minq From Sp Where Sp.P#=P#(‘P2’); • Exam 8: Select Sp.P#, Sum(Sp.Qty) As Totqty From Sp Group By Sp.P#; • Exam 9: Select Sp.P# From Sp Group By Sp.P# Having Count(Sp.S#) > 1; • Exam 10: Select Distinct S.Sname From S Where S.S# In • (Select Sp.S# • From Sp • Where Sp.P#=P#(‘P2’)); Advanced Database System

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

  20. 8.6 SQL Facilities (Cont.) • Exam 14: Select Distinct S.Sname From S Where Not Exists (Select * From Sp Where Sp.S#=S.S# And Sp.P#=P#(‘P2’)); • Exam 15: Select Distinct S.Sname From S Where Not Exists (Select * From P Where Not Exists (Select * From Sp Where Sp.S#=S.S# And Sp.P#=P.P#)); • Exam 16: Select P.P# From P Where P.Weight > P#(16.0) Union Select Sp.P# From Sp Where Sp.S#=S#(‘S2’) • Exam 17: With T1 As (Select P.P#, P.Weight＊454 As Gmwt • From P) • Select T1.P#, T1.Gmwt • From T1 • Where T1.Gmwt > Weight(10000.0); Advanced Database System

  21. 8.7 Domain Calculus • 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, City Cityx} And Sp {S# Sx, P# P#(‘P2’)} {Sx, Px} Where S {S# Sx, City Cityx} And P {P# Px, City Cityy} And Cityx≠Cityy Advanced Database System

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

  23. 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)) Exam 6: Namex Where Exists Sx (S {S# Sx, Sname Namex} And Not Sp {S# Sx, P# P#(‘P2’)}) Exam 7: Sx Where Forall Px (If Sp {S# S#(‘S2’), P# Px} Then Sp {S# Sx, P# Px} End If) Exam 8: Px Where Exists Weightx (P {P# Px, Weight Weightx} And Weightx > Weight(16.0)) Or Sp {S# S#(‘S2’), P# Px} 8.7 Domain Calculus (Cont.) Advanced Database System

  24. 8.8 Query-By-Example • Exam 1: • Exam 2: • Exam 3: • Exam 4: Advanced Database System

  25. 8.8 Query-By-Example (Cont.) • Exam 5: • Exam 6: • Exam 7: • Exam 8: Advanced Database System

  26. 8.8 Query-By-Example (Cont.) • Exam 9: • Exam 10: • Exam 11: • Exam 12: Advanced Database System

  27. 8.8 Query-By-Example (Cont.) • Exam 13: • Exam 14: • Exam 15: • Exam 16: Advanced Database System

  28. 8.8 Query-By-Example (Cont.) • Exam 17: Advanced Database System

  29. The End. Advanced Database System

More Related