book borrowing system with php, mysql database

Total authors = 4 Total book titles = 10 An author may have one or more books. Also two authors may have the same book (i.e two copies ). System should have web interface which allow for; selection the name of one of the four authors through a drop-down list.(populated with data from the database) selection of book title in the possession of the selected author through a separate drop-down list.(populated with data from the database) textfield to enter the name of the person who borrows the book. textfield to enter the e-mail address of the person who borrows the book. On submission of this data, successful or unsuccessful confirmation should be displayed. successful if the selected author is still in the possession of the selected book, that is, the book has not already been loaned by the author to someone else. unsuccessful if someone else has already borrowed the selected book from the selected author and it should then be possible to select a different book from the same or or one of the other authors in which case there is no need to enter name and e-mail address again. Also only showing authors that still have books that can be borrowed and only book titles that can still be borrowed. MySQL database should store for each author and each book title whether it is available or has been borrowed and for any borrowed book the name and e-mail address of the person who has borrowed it. NOTE: A user can borrow more than one book. There is the possibility that two users nearly simultaneously try to borrow the same book. Depending on how you implement the interaction with the database, this could lead to a situation in which your system determines for both users that the book is still available and then records for both users that he/she has borrowed that book, with only the information on one of the users remaining in the database but both users receiving a confirmation that their borrowing transaction has been successful. The system needs to avoid this situation.