Relational Algebra Consider the following relation tables fi

Relational Algebra

Consider the following relation tables (fields with underline is primary keys): Author(Name. Country) Book(lSBN, Title, Publisher. Subject) Writes(Name, ISBN) Write the relational algebra for the following query: Give the titles of all books with \"Art\" subject Give the name of all authors who publish with \"Harding\" Give the name of all authors who have written books with \"Science\" subject Give the titles of all books written by U.S. authors Give the titles, ISBNs, and publishers of all books with \"Art\" subject whose authors live in the US.

Solution

a. select title from book where subject=\"art\";

Explanation: it returns the all books which matches the subject with Art.

b. select name from writes where ISBN in(select ISBN from Book where publisher= \" Harding\");

Explanation: here name and publisher are from two different tables first we take isbn from book which matches the publisher Harding and then from that we select names from writes table

c. select name from writes where ISBN in(select ISBN from Book where subject= \" Science\");

Explanation: here name and subject are from two different tables first we take ISBN from book which matches the subject science and then from that we select names from writes table

d. select title from book where ISBN in(select ISBN from writs where Name in (select name from Author where country=\"U.S\"));

Explanation: here titles and country are from two different tables first we take name from Auther which matches the country U.S and then from that take ISBN from writes and then we take titles from book

e. select title, ISBN, publisher from Book where subject=\"Art\" and ISBN in (select ISBN from writes where name in (select name from Auther where country =\"U.S\"));

Explanation: here titles,ISBN and authers are from two different tables first we take name from Auther which matches the country U.S and then from that take ISBN from writes and then we take titles,ISBN,Publishers from book which matches subject art.