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

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.

Please don't repost from the other time this question was asked, it was no help.

Explanation / Answer

Query

select Book.ISBN from Book where published_by=works_for and Author_Editor AND aeSSN=(select SSN where (first_name="Andy" AND last_name="Smith") AND (first_name="Mary" AND last_name="Doe"));

In the above query we selected the isbn number by optimisimg the tables with the published by and the ref by that we can get one publisher and from aessn and ssn then we get one author.

So we can get the required ISBN the table

Thank You For the oppurtunity

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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