Question

Database Schema:

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) (16 pts) Create all the relations listed above. Make sure to indicate the primary key and the foreign keys (if any) in your statements.

(2) (10 pts) Insert at least 5 members, 10 books, and enough tuples in the CurrentLoan and History relation. Add tuples as needed to be able to test the following queries for different test cases.

(3) (8 pts) Find the book ID, title, author, and publish-year of all the books with the words “XML” and “XQuery” in the title. These two keywords can appear in the title in any order and do not have to be next to each other. Sort the results by publish year in descending order.

(4) (8 pts) Find the book ID, title, and due date of all the books currently being checked out by John Smith.

(5) (8 pts) Find the member ID, last name, and first name of the members who have never borrowed any books in the past or currently.

Note:

• Please put all the SQL statements in a single file and name it as hw2_yourPirateID.sql.

• Remember to change all the dash “-” in attribute names to underscore “_”.

• For the ease of testing, please add the “drop table” statements at the beginning of your file to drop all the tables.

• Remember to add “commit;” after your last “insert” statement.

• Comment your code as needed. You can use /* … */ to have multi-line comments or use double hyphen (--) for single line commenting. You may use PROMPT … to print any message to the screen. For example, you may use PROMPT Answer for Question 3 (before the query for question 3).

• For this assignment, you need to test your solutions in Oracle and submit your .sql file

Answer 1