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

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

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.

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

View Full Document
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.
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.

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern