{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

A b 17 r b r s c a s r a r b d b r d r 68 consider

Info iconThis preview shows pages 5–8. Sign up to view the full content.

View Full Document Right Arrow Icon
A ( B = 17 ( r )) b. r s c. A ( s ( r . A ( r . b > d . b ( r × d ( r ))))) 6.8 Consider the relational database of Figure 6.22 where the primary keys are underlined. Give an expression in tuple relational calculus for each of the following queries: a. Find all employees who work directly for Jones. b. Find all cities of residence of all employees who work directly for Jones.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
6 Chapter 6 Formal Relational Query Languages c. Find the name of the manager of the manager of Jones. d. Find those employees who earn more than all employees living in the city Mumbai. Answer: a. { t | ∃ m manages ( t [ person name ] = m [ person name ] m [ manager name ] = ’Jones’) } b. { t | ∃ m manages e employee ( e [ person name ] = m [ person name ] m [ manager name ] = ’Jones’ t [ city ] = e [ city ]) } c. { t | ∃ m 1 manages m 2 manages ( m 1[ manager name ] = m 2[ person name ] m 1[ person name ] = ’Jones’ t [ manager name ] = m 2[ manager name ]) } d. { t | ∃ w 1 works ¬∃ w 2 works ( w 1[ salary ] < w 2[ salary ] e 2 employee ( w 2[ person name ] = e 2[ person name ] e 2[ city ] = ’Mumbai’)) } 6.9 Describe how to translate join expressions in SQL to relational algebra. Answer: A query of the form select A 1 , A 2 , . . . , An from R 1 , R 2 , . . . , Rm where P can be translated into relational algebra as follows: A 1 , A 2 ,..., An ( P ( R 1 × R 2 × . . . × Rm )) An SQL join expression of the form R 1 natural join R 2 can be written as R 1 R 2. An SQL join expression of the form R 1 join R 2 on ( P ) can be written as R 1 P R 2.
Background image of page 6
Practice Exercises 7 An SQL join expression of the form R 1 join R 2 using ( A 1 , A 2 , . . . , An ) can be written as S ( R 1 R 1 . A 1 = R 2 . A 1 R 1 . A 2 = R 2 . A 2 ... R 1 . An = R 2 . An R 2) where S is A 1 , A 2 , . . . , An followed by all attributes of R 1 other than R 1 . A 1 , R 1 . A 2 , . . . , R 1 . An , followed by all attributes of R 2 other than R 2 . A 1 , R 2 . A 2 , . . . , R 2 . An , The outer join versions of the SQL join expressions can be similarly written by using , and in place of . 1 The most direct way to handle subqueries is to extend the relational algebra. To handle where clause subqueries, we need to allow selection predicates to contain nested relational algebra expressions, which can reference correla- tion attributes from outer level relations. Scalar subqueries can be similarly translated by allowing nested relational algebra expressions to appear in scalar expressions. An alternative approach to handling such subqueries used in some database systems, such as Microsoft SQL S erver, introduces a new relational algebra operator called the Apply operator; see Chapter 30, page 1230-1231 for details. Without such extensions, translating subqueries into standard relational algebra can be rather complicated.
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}