Consider the following schema: Publisher (name, phone, address), PK: name. Book

ID: 672462 • Letter: C

Question

Consider the following schema:

Publisher (name, phone, address), PK: name.

Book (ISBN, title, year, published_by, previous_edition), PK: ISBN, FK: published_by refs Publisher, previous_edition refs Book.

Author (SSN, first_name, last_name, address, income), PK: SSN.

Write(aSSN, bISBN), PK: (aSSN, bISBN), FK: aSSN refs Author, bISBN refs Book.

Author_Editor(aeSSN, works_for), PK: aeSSN, FK: aeSSN refs Author, works_for refs Publisher.

Give relational algebraic expressions or SQL statements for the following plain English language queries based on the above schema.

2. Provide the ISBN of those books whose authors include both Andy Smith and Mary Doe.

Solution:

SQL Statement

(1)

select p.name as Publisher , b.title as Book_Title from Book b, Publisher p, Editor e,Edit ed

where b.published_by = p.name and p.name = e.works_for and e.SSN = ed.SSN and ed.bISBN = b.ISBN group by

p.name, b.title having count(*) = (select max(temp) from (Select p.name as Publisher, b.title as

Book_Title,count(*) as temp from Book b, Publisher p,Editor e,Edit ed where b.published_by= p,.name and

p.name = e.works_for and e.SSN = ed.eSSN and ed.bISBN = b.ISBN group by p.name));

SQL Statement

(2)

select b.title from Book b where b.published_by = "ABC" order by year ASC;

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.