Use the same library database schema used in homework 1 and 2: Book(bookID, ISBN

Question

Use the same library database schema used in homework 1 and 2:
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.
Write SQL statements for each of the following questions.
(1) (6 pts) For each member (using memberId, lastname and firstname), find the number of books currently checked out by the member.
(2) (7 pts) List the ISBN and title of the books with at least two copies.
(3) (7 pts) Create a view call member_book that includes memberID, lastname, firstname, bookID, and title, that shows which member has borrowed which book, including currently and in the past.
(4) (4 pts) Use the view created above in question (3) to find the bookID and title of all the books checked out by John Smith in the past or currently.
(5) (8 pts) Delete member David Jones and all the relevant records. Make sure you delete related tuples from all the relevant relations, and delete them in the right order.
(6) (8 pts) Increase the limit by 2 for all members, but the maximum limit is 10. Display the member ID and the limit for each member before and after you make the changes.
Note:
You may need to add more tuples to the database you created for previous homework in order to test the queries for this homework.
Please name your file as hw3_ yourPirateID.sql. For this assignment, you need to test your solutions in Oracle and submit your .sql file(s) electronically to Blackboard and also hand in a hard copy of your solutions. Please also submit the SQL file you used to create your database (name it as hw3_ yourPirateID_table.sql) so that the grader can test your query on your database. You can either include those commands (as well as drop table statements) at the beginning of this homework file, or submit it as a separate .sql file. Failure to do this will result in losing points.
Please remember to always attach the homework description page as the cover page for your hard copy submission. You will be deducted points if you don’t have the cover page.

Explanation / Answer

The sql queries for the given questions are

1. Select m. member_ID, firstname, lastname, count(cl. bookID) from Member m, CurrentLoan cl where m. memberID=cl. member_ID;

2. Select ISBN, title from Book having count(title) >2;

3 . Create view member_book as

Select m. memberID, lastname, firstname,b.bookID, b. title from Book b, CurrentLoan cl, member m, history h where m.memberID=cl.memberID and m.memberID =h.memberID and h.bookId=b.bookId and cl.bookID=b.bookID;

4. Select bookID, title from member_book where firstname ='John Smith';

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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