cs186_fa07_mt1_sol 3

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

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

View Full Document Right Arrow Icon
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
Background image of page 1
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