1 / 23

Chapter 8

Chapter 8. Relational Calculus. Topics in this Chapter. Tuple Calculus Calculus vs. Algebra Computational Capabilities SQL Facilities Domain Calculus Query-By-Example. Relational Calculus.

alodie
Download Presentation

Chapter 8

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. Chapter 8 Relational Calculus

  2. Topics in this Chapter • Tuple Calculus • Calculus vs. Algebra • Computational Capabilities • SQL Facilities • Domain Calculus • Query-By-Example

  3. Relational Calculus • Based on predicate calculus, the relational calculus is a more natural language expression of the relational algebra • Instead of operators used by the system to construct a result relation, the calculus offers a notation to express the result relation in terms of the source relations • Relational calculus and relational algebra are logically equivalent

  4. Relational Calculus Implementations • Codd proposed a language called ALPHA to implement the relational calculus • QUEL, an early competitor to SQL, was based on ALPHA • Relational calculus came to be called tuple calculus, in distinction from domain calculus • Domain calculus has been implemented by Query By Example (QBE)

  5. Tuple Calculus - Syntax <relation expression> := RELATION {<tuple expression commalist>} | <relvar name> | <relation operator invocation> | <with expression> | <introduced name> | ( <relation expression>)

  6. Tuple Calculus - Syntax • Identical to the syntax of the algebra • <relation operator invocation> now is interpreted to mean relation definition • In addition, <range variable definition> ::= RANGEVAR <range variable name> RANGES OVER <relation expression commalist> ;

  7. Tuple Calculus - WFFs • <boolean expression>s are called well-formed formulas, WFFs, or “weffs” • Every reference to a range variable is either free or bound, within a context, and in particular, within a WFF

  8. Range Variables • RANGEVAR SX RANGES OVER S; • RANGEVAR SY RANGES OVER S; • RANGEVAR SPX RANGES OVER SP; • RANGEVAR SPY RANGES OVER SP; • RANGEVAR PX RANGES OVER P;

  9. Range Variables • RANGEVAR SU RANGES OVER ( SX WHERE SX.CITY = ‘London’ ) , ( SX WHERE EXISTS SPX (SPX.S# = SX.S# AND SPX.P# = P# (‘P1’) ) ) ; • In this example, SU ranges over the union of the set of supplier tuples for suppliers located in London, and those that supply part P1

  10. Free and Bound Variables • Every reference to a range variable is either free or bound • A bound reference can be replaced by a reference to some other variable without changing the meaning of the expression • A free reference is not so free

  11. Quantifiers - EXISTS • EXISTS is the existential quantifier • EXISTS V ( p ) -- There exists at least one value of V that makes p true • Formally this is an iterated OR • FALSE OR p (t1) OR … OR p (tm) -- will evaluate to false if m = 0

  12. Quantifiers - EXISTS EXISTS SPX (SPX.S# = SX.S# AND SPX.P# = ‘P2’) Read: There exists an SP tuple (call it SPX) such that its S# value = the value of SX.S# (whatever SX.S# is at the moment) AND its P# value = ‘P2’.

  13. Quantifiers - FORALL • FORALL is the universal quantifier • FORALL V ( p ) -- for all values of v, p is true • Formally this is an iterated AND • TRUE AND p (t1) AND … AND p (tm) -- will evaluate to true as long as all are true • This will evaluate to TRUE when the set is empty

  14. Quantifiers - FORALL FORALL PX (PX.COLOR = ‘Red’) Read: For all tuples in P the color is Red.

  15. Relational Operations • <relation op inv> in the calculus context is more a definition than an operator invocation • <relation operation invocation> ::= <proto tuple> [WHERE <bool exp>] For example: SX.S# WHERE SX.CITY = ‘London’ -- Get supplier numbers for suppliers in London

  16. Calculus vs. Algebra • Codd’s reduction algorithm reduces expressions of the calculus to algebra • A language is said to be relationally complete if it is at least as powerful as the calculus • And the same can be said of the algebra • QUEL, based on the calculus, can be implemented by applying the algorithm to it, and then implementing the underlying algebra

  17. SQL Facilities • Because the calculus statements can be translated into algebra, they map equally well to SQL • Example: Get supplier numbers for suppliers with status less than the current maximum status in the supplier table: SELECT S.S# FROM S WHERE S.STATUS < (SELECT MAX (S.STATUS) FROM S) ;

  18. Examples Get supplier names for suppliers who supply part P2. In SQL: SELECT SNAME FROM S WHERE S# IN (SELECT S# FROM SP WHERE P# = ‘P2’); In relational algebra: ( ( SP JOIN S ) WHERE P# = P# (‘P2’) ) { SNAME }

  19. Get supplier names for suppliers who supply part P2. • SELECT SNAME • FROM S • WHERE S# IN • (SELECT S# • FROM SP • WHERE P# = ‘P2’); • SELECT SNAME • FROM S, SP • WHERE S.S# = SP.S# AND P# = ‘P2’ ; • SELECT SNAME • FROM S • WHERE EXISTS • ( SELECT • FROM SP • WHERE S# = S.S# • AND P# = ‘P2’) ;  alternative SQL statements

  20. SELECT SNAME FROM S WHERE EXISTS ( SELECT FROM SP WHERE S# = S.S# AND P# = ‘P2’) ; for each SNAME, is there an SP tuple with SP.S# = S.S# and P# = ‘P2’ ? In relational calculus: SX.SNAME WHERE EXISTS SPX ( SX.S# = SPX.S# AND SPX.P# = ‘P2’)

  21. Get supplier names for suppliers who supply at least one red part. SELECT SNAME FROM S WHERE S# IN (SELECT S# FROM SP WHERE P# IN (SELECT P# FROM P WHERE COLOR = ‘RED’) ); ( ( ( P WHERE COLOR = COLOR (‘RED’) ) { P# } JOIN SP ) { S# } JOIN S {SNAME} SX.SNAME WHERE EXISTS SPX ( EXISTS PX ( SX.S# = SPX.S# AND SPX.P# = PX.P# AND PX.COLOR = ‘RED’ ) )

  22. Get all pairs of supplier numbers where the two suppliers are located in the same city. SELECT FIRST.S#, SECOND.S# FROM S FIRST, S SECOND WHERE FIRST.CITY = SECOND.CITY AND FIRST.S# < SECOND.S#; ( ( ( S RENAME S# AS FIRSTS# ) {FIRSTS#, CITY} JOIN (S RENAME S# AS SECONDS# ) {SECONDS#, CITY} ) WHERE FIRSTS# < SECONDS# ) { FIRSTS#, SECONDS# } FIRSTS# = SX.S#, SECONDS# = SY.S# WHERE SX.CITY = SY.CITY AND SX.S# < SY.S# 

  23. The relational algebra and the relational calculus are equivalent. An arbitrary expression in the calculus can be reduced to a semantically equivalent expression in the algebra. Either serves as a measure of any other database language. A language is said to be relationally complete if it is just as powerful as the calculus or the algebra. A language can be shown to be relationally complete if it has equivalent operations for the five basic operations of the algebra. More stringently, a language can be shown to be relationally complete if it can, by a single expression, define a relation definable by a single calculus expression. SQL is relationally complete.

More Related