First the COPY relation is searched using ISBN. If there is no BOOKID with the same ISBN and ON_LOAN equal to 0, a message is displayed: "Sorry no copy of this book is currently available".
Next the USER relation is searched using USER_ID. If there is no USER_ID, a message is displayed, "Sorry not a patron".
If there is BOOKID with same ISBN and ON_LOAN euqal to 0, then the ON_LOAN field is changed to 1, and a tuple is added to the relation CIRCUL, with USER_ID, BOOKID, DATE_OUT (current date) and the librarian is prompted to enter the DATE_DUE. The DATE_BACK and DATE_RENEW are left blank. The RETURNED is set to 0. The FINE is set to 0.
(Optional) Finally, the RESERVATION relation is checked, and the tuple with the same ISBN and USER_ID is deleted.
First the COPY relation is searched using BOOKID. We check whether the ON_LOAN is equal to 1. If ON_LOAN is equal to 0, error. Otherwise the ISBN is retrieved and ON_LOAN reset to 0.
Next, the CIRCUL relation is searched using BOOKID and USER_ID. If the tuple does not exist, error. Otherwise, enter DATE_BACK. From DATE_DUE and DATE_BACK, the FINE is calculated and entered. The RETURNED is set to 1.
Notice the book is returned, but the tutple is not deleted from the CIRCUL relation. It is kept there so that later we can collect statistical information. But we know the book has been returned, because the RETURNED field is 1.
The librarian can enter ISBN and USER_ID into the RESERVATION relation. She can also delete a tuple from the RESERVATION relation using ISBN and USER_ID. Right now the BESERVATION relation is used just for record keeping. It does not cause the automatic checkout of a book. For that reason, there is no need to add another field in COPY about ONRESERVATION. Notice patrons don't reserve based upon BOOKID, patrons reserve based upon ISBN, because all copies of the book are the same.