I need help with this assignment. Use this database schema, primary keys are in
ID: 3873098 • Letter: I
Question
I need help with this assignment.
Use this database schema, primary keys are in bold:
Book(bookID, ISBN, title, author, publish-year, category)
Member(memberID, lastname, firstname, address, phone-number, limit)
CurrentLoan(memberID, bookID, loan-date, due-date)
History(memberID, bookID, loan-date, return-date)
Members can borrow books from the library. The number of books they can borrow is limited by the “limit” field of the Member relation (it may differ for different members). The category of a book includes fiction, non-fiction, children’s and reference. The CurrentLoan table represents the information about books that are currently checked out. When the book is returned to the library, the record will be removed from CurrentLoad relation, and will be inserted into History relation with the return-date. A library may have more than one copy of the same book, in which case each copy has its own bookID, but all copies share the same ISBN.
(1) (6 pts) Find the lastname and firstname of the member(s) who have the highest limit allowed.
(2) (10 pts) Find the lastname and firstname of members who have borrowed both books titled “XML and
XQuery” and “XQuery: The XML Query Language” either currently or in the past.
(3) (10 pts) Find the name of the author(s) that has the largest number of different books owned by the
library (multiple copies of the same book only count as one book).
(4) (12 pts) For each member (using both member ID, last name, and first name), list the number of books
he/she has currently checked out, and the number of books he/she checked out in the past. If a person
checked out the same book multiple times, it will be counted multiple times. Zero count should also be
listed as 0.
(5) (12 pts) List the member ID, first name and last name of members who have borrowed (either currently
or in the past) all the books in the library with “Harry Potter” in the title. If any of such books have multiple
copies, he or she must have borrowed at least one copy of each of such books.
Explanation / Answer
1) SELECT lastname,firstname from member order by limit desc limit 1;
2) SELECT member.lastname,member.firstname from member,book,CurrentLoan,History where book.title=“XML and
XQuery” and ((book.bookid=CurrentLoan.bookid and member.memberid=CurrentLoan.memberid) OR (book.bookid=History.bookid and member.memberid=History.memberid))
3) select author,count(*) as c from books group by author order by c desc limit 1;
4) select members.memberid,members.lastname,members.firstname,count(currentloan.bookid),count(History.bookid) from members,CurrentLoan,History where members.memberid=Currentloan.memberid and members.memberid=History.memberid group by members.memberid,members.lastname,members.firstname;