cs186_fa07_mt1_sol 3

# cs186_fa07_mt1_sol 3 - WHERE b.author = a.name AND NOT...

This preview shows page 1. Sign up to view the full content.

2. Query Languages Consider the fol owing schema of a library. Primary keys are underlined. Author(name , citizenship, birthYear, birthPlace) Book (isbn , title ,author) Library (lname , city) Bindex (isbn, subject ) In_stock (isbn, lib_name , edition, quantity) Based on the above schema, try to answer the fol owing questions: a) [4 points] Convert this SQL query to relational algebra: SELECT * FROM Author a, Book b WHERE a.name = b.author AND b.title = 'Hopscotch'; -1 if no selection -1 if projection was specified on the wrong set of fields -1 if cross-product without selection predicate to account for the join between Books and Authors b) [6 points] Consider the fol owing SQL query: SELECT a.name FROM author a WHERE NOT EXISTS (SELECT b.isbn FROM book b
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: WHERE b.author = a.name AND NOT EXISTS ( SELECT * FROM in_stock i WHERE i.isbn = b.isbn AND i.lib_name = ‘Evans’ AND i.quantity > 2)) ; Fil in the blanks in the fol owing relational calculus query that makes it equivalent to the SQL above,. Note: the correct answer may not require al blanks to be fil ed in! {R | ∃ A ∊ Authors ( A.name = R.name ∧ ∀ B ∊ Book ( ( B.author = A.name ) → ( ∃ I ∊ InStock ( I.isbn = B.isbn ∧ I.lib_name=’Evans’ ∧ I.quantity > 2 )))) } Your answer: ( σ t itle=’Hopscotch’ Book) \join Author Eirinaios Michelakis 11/6/07 1:02 AM-2 Eirinaios Michelakis 11/6/07 1:02 AM-1 Eirinaios Michelakis 11/6/07 1:02 AM-1 Eirinaios Michelakis 11/6/07 1:02 AM-2...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online