Please help me with this assignment. Use this database schema, primary keys are

Question

Please help me 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

Solution:

1)

SELECT lastname, firstname, MAX(limit) FROM Member

2)

SELECT lastname, firstname FROM Member INNER JOIN CurrentLoan ON Member.memberID= CurrentLoan.memberID INNER JOIN Book ON CurrentLoan.bookID= Book.bookID.

3)

SELECT DISTINCT bookID, author FROM Book WHERE author= (SELECT MAX(author) FROM Book)

4)

SELECT lastname, firstname, COUNT(bookID) FROM Member INNER JOIN CurrentLoan ON Member.memberID= CurrentLoan.memberID.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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