studentstudentid name dob booksisbn title authors publisher

student(student_id, name, dob) books(isbn, title, authors, publisher) borrowed(student_id, isbn, date)

(a) Write corresponding relational algebra, relational calculus, SQL expressions , QBE for the following query: Find the names of students who have borrowed book with ISBN 87676

(b) Write corresponding relational algebra, relational calculus and SQL expressions for the following query: Find the names of students who have borrowed every book published by \"McGraw-Hill\"

(c) Write corresponding relational algebra, relational calculus, and SQL expressions for the following query: Find the isbn of all the books which have been borrowed by the students who have borrowed books published by \"McGraw-Hill\"

(d) Write corresponding relational algebra, relational calculus, and SQL expressions for the following query: Find the id and name of students who have never borrowed any book published by \"McGraw-Hill\"

(e) Write corresponding relational algebra, relational calculus, and SQL expressions for the following query. Find the id and name of students who have only borrowed books from the publisher \"McGraw-Hill\"

Solution

Dear student,

You have not mentioned any key constraint in your question.

assumptions for the same have been done based on common practice :

For relation Student - Student ID will be taken as the primary key

Books - isbn will be taken as the primary key

Borrowed - student id + isbn; composite key

Foreign key - student id, isbn

Now we will proceed to each of the solutions :

a) Select s.name from student s, borrowed br where br.isbn = 87676 AND s.student_id=br.student_id ;

b) Select s.name from student s , books b, borrowed br where b.isbn IN ( Select isbn from books where b.publisher = \'Tata Mc.Graw Hill\') AND b.isbn=br.isbn;

c) Select s.name from student s , books b, borrowed br where b.isbn IN ( Select isbn from books where b.publisher = \'Tata Mc.Graw Hill\');

d) Select s.name from student s , books b, borrowed br where b.isbn NOT IN ( Select isbn from books where b.publisher = \'Tata Mc.Graw Hill\') AND b.isbn=br.isbn;