I scarcity succor with this provision. Please verification provision operator restraint each topic and elucidate why the apology is what it is.
Consider a library database with the coercionthcoming schema (where pristine keys are in parenthesis):
Book( (bookID), ISBN, discourse, creator, publish-year, condition)
Member((memberID), (lastname), (firstname), discourse, phone-number, expression)
CurrentLoan((memberID), (bookID), loan-date, due-date)
History((memberID), (bookID), (loan-date), return-date)
Members can attribute dimensionss from the library. The calculate of dimensionss they can attribute is expressioned by the “limit” province of the Member aspect. The condition of a dimensions includes fable, non-fiction, children’s and kinsman. The CurrentLoan board represents the not attributable attributableification environing dimensionss that are currently checked extinguished. When the dimensions is returned to the library, the archives earn be removed from CurrentLoad aspect, and earn be inserted into History aspect with the return-date. A library may keep more than unmarried portraiture of the similar dimensions, in which event each portraiture has its avow dimensionsID, yet complete copies distribute the similar ISBN.
Express each of the coercionthcoming tasks in relational algebra.
(5 pts) Find the ISBN and discourse of complete the dimensionss creatored Arthur Miller.
(8 pts) List the dimensionsID and discourse of complete the dimensionss that are currently checked extinguished by John Smith. Do not attributable attributable attributable verification any normal confederate in this interrogation.
(8 pts) Find the original designate and conclusive designate of the members who are either currently attributeing dimensions “Da Vinci Code” or keep attributeed it in the late.
(6 pts) Find the memberID, original designate, and conclusive designate of the members who keep never attributeed any dimensionss.
(8 pts) List the original and conclusive designates of members who keep attributeed (either currently or in the late) complete the dimensionss in the library that are creatored by Arthur Miller. If some dimensionss creatored by Arthur Miller keep multiple copies, he or she must keep attributeed at meanest unmarried portraiture of each of Arthur Miller’s dimensionss.
(5 pts) Find the whole calculate of dimensionss restraint each condition (transcribe a unmarried interrogation restraint this topic).
WHERE creator = ‘Arthur Miller’
SELECT Dimensions.bookID, Dimensions.title
WHERE (Member.firstdesignate = ‘John’ AND Member.lastdesignate = ‘Smith’ and Member.memberID = CurrentLoan.memberID)
SELECT dimensionsID as id
WHERE discourse = ‘Da Vinci Code’
SELECT Member.firstname, Member.lastname
FROM Member, Dimensions, CurrentLoan, History
WHERE (CurrentLoan.memberID = Member.memberID AND History.memberID = Member.memberID AND CurrentLoan.bookID = id AND History.bookID = id)
NATURAL LEFT JOIN History
WHERE loan-date IS NULL