Introduction

This is an individual assignment in which you are required to develop a dynamic web application as described below using PHP, MySQL, JavaScript and CSS. Details of the weight of the assignment and due date are given in the course description.

Task Description

Database structure

The web application uses a 3-table relational database stored on a server that records the details of books borrowed from a library. The structure of the database is:

BORROWER(BORROWER_NUMBER, BORROWER_NAME, EMAIL)
BOOK(ISBN, TITLE, AUTHOR, PUBLISHER, YEAR_OF_PUBLICATION, NUMBER_OF_COPIES)
BOOK_BORROWED(BORROWER_NUMBER, ISBN, COPY_NUMBER, DATE_BORROWED)

Each record in the BOOK_BORROWED table refers to one book borrowed by one borrower on the date specified. Borrowers may borrow up to 6 books at a time. The field NUMBER_OF_COPIES in table BOOK stores the number of copies of a specific book owned by the library and available for borrowing. If the library owns a single copy only of any book, then 1 should be entered for the COPY_NUMBER whenever it is
borrowed. No nulls are permitted. The BOOK_BORROWED table has a primary key that combines the primary keys of the other 2 tables. Primary keys have been underlined.

The borrower number is a 5-digit number with no leading zeroes. The ISBN (International Standard Book Number) is a 13-digit number that uniquely identifies a published book. All copies of a given book have the same ISBN. The year of publication is a 4-digit number. The date borrowed has the format ‘ddmmyyyy'.

Initial data
When the database is set up it should be populated with the data that you have chosen. Display this data as part of your documentation. Each table should have from 3 to 6 records initially.

Borrowing forms
Before implementing the database, use PHP and CSS to create a form that enables a librarian to enter the details for the BOOK_BORROWED table when a borrower borrows one or more books by a student for one or more courses as part of a single program. Wherever appropriate you should use drop down boxes for user-friendly entry of data and to reduce entry errors.

Use JavaScript to ensure that an entry has been made in each element of the form and that the entries are valid. Check that numbers have been entered in fields that are numerical and that the numbers entered are within the specified range. Test the form by posting the values of the entries in the borrowing form to a separate php file that will display the borrowing details in full, including information obtained from the other two tables (once the database has been implemented).

Creating the database
Use MySQL commands from the command line to create a database on the server with the structure specified and populated with the initial data. Test the database by writing a query on the command line that displays all initial data as described on page 1. Write PHP code that will enable users to create an account giving them access to the database with permission to query the database and add new bookings. Account usernames will generally be email addresses. However include username ‘tutor' with password ‘guest' as one of the accounts. User passwords should be encrypted with an MD5() hash function.