Homework Solution: I need help answering these questions. I've gotten the first one and am having trouble with ones after. If I see how a few a…

    I need help answering these questions. I've gotten the first one and am having trouble with ones after. If I see how a few are done I will be able to better understand it. Consider a library database with the following schema (where primary keys are bolded): 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. 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. Express each of the following tasks in relational algebra. (5 pts) Find the ISBN and title of all the books authored Arthur Miller. (8 pts) List the bookID and title of all the books that are currently checked out by John Smith. Do not use any natural join in this query. (8 pts) Find the first name and last name of the members who are either currently borrowing book “Da Vinci Code” or have borrowed it in the past. (6 pts) Find the memberID, first name, and last name of the members who have never borrowed any books. (8 pts) List the first and last names of members who have borrowed (either currently or in the past) all the books in the library that are authored by Arthur Miller. If some books authored by Arthur Miller have multiple copies, he or she must have borrowed at least one copy of each of Arthur Miller’s books. (5 pts) Find the total number of books for each category (write a single query for this question).

    Expert Answer

     
    Given relation is: Book(bookID, ISBN, title, author, publish-year, category)

    I scarcity succor confutationing these topics. I’ve gotten the pristine individual and am having molestation with individuals succeeding. If I comprehpurpose how a scant are dindividual I conquer be potent to empurpose recognize it.

    Consider a library database with the restraintthcoming schema (where primary keys are bolded):

    Book(bookID, ISBN, style, creator, publish-year, mode)

    Member(memberID, developedname, pristinename, harangue, phone-number, stipulation)

    CurrentLoan(memberID, bookID, loan-date, due-date)

    History(memberID, bookID, loan-date, return-date)

    Members can attribute tomes from the library. The calculate of tomes they can attribute is stipulationed by the “limit” room of the Member agreement. The mode of a tome includes romance, non-fiction, children’s and regard. The CurrentLoan tpotent represents the instruction abquenched tomes that are currently checked quenched. When the tome is returned to the library, the proceedings conquer be removed from CurrentLoad agreement, and conquer be inserted into History agreement with the return-date. A library may possess gone-by than individual vision of the selfselfcorresponding tome, in which event each vision has its enjoy tomeID, referablewithstanding complete copies divide the selfselfcorresponding ISBN. Express each of the restraintthcoming tasks in relational algebra.

    (5 pts) Find the ISBN and style of complete the tomes creatored Arthur Miller.

    (8 pts) List the tomeID and style of complete the tomes that are currently checked quenched by John Smith. Do referable portraiture any true annex in this topic.

    (8 pts) Find the pristine indicate and developed indicate of the members who are either currently attributeing tome “Da Vinci Code” or possess attributeed it in the gone-by.

    (6 pts) Find the memberID, pristine indicate, and developed indicate of the members who possess never attributeed any tomes.

    (8 pts) List the pristine and developed indicates of members who possess attributeed (either currently or in the gone-by) complete the tomes in the library that are creatored by Arthur Miller. If some tomes creatored by Arthur Miller possess multiple copies, he or she must possess attributeed at meanest individual vision of each of Arthur Miller’s tomes.

    (5 pts) Find the completion calculate of tomes restraint each mode (transcribe a uncombined topic restraint this topic).

    Expert Confutation

     

    Given agreement is:
    Book(bookID, ISBN, style, creator, publish-year, mode)
    Member(memberID, developedname, pristinename, harangue, phone-number, stipulation)
    CurrentLoan(memberID, tomeID, loan-date, due-date)
    History(memberID, tomeID, loan-date, return-date)

    Find the ISBN and style of complete the tomes creatored Arthur Miller?

    select ISBN, style from Tome where creator=’Arthur Miller’;

    List the tomeID and style of complete the tomes that are currently checked quenched by John Smith. Do referable portraiture any true annex in this topic.

    select B.bookID, B.style from Tome B,Member M,CurrentLoan C where B.bookID=C.bookID and M.memberID=C.memberID and M.firstname=’John Smith’;

    Find the pristine indicate and developed indicate of the members who are either currently attributeing tome “Da Vinci Code” or possess attributeed it in the gone-by.

    select M.firstname, M.lastindicate from Tome B,Member M,CurrentLoan C where B.bookID=C.bookID and M.memberID=C.memberID and B.title=’Da Vinci Code’;

    Find the completion calculate of tomes restraint each mode ?
    select isbn, tometitle, consolidate(event when status=’available’ then 1 else 0 purpose) as advantage, reckon(*) as completion
    from Tome;
    group by isbn, tometitle