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

Question

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"

Explanation / Answer

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;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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