web programming

BCIS 3680 Enterprise Programming Assignment 5 - 1

BCIS 3680 Enterprise Programming Instructor: Dr. Andy Wu

Assignment 5 Now you will build database-driven pages on the Books Galore site. You will modify the existing main.jsp and catalog.jsp and create another JSP file, book.jsp. 1. Make a copy of your deployment folder for Assignment 4 and rename it to 3680-HW5-

YourLastName-YourFirstName. 2. You will create a database for this assignment. Do not save the database in the deployment

folder. You may use the default data folder of MySQL (which means you don’t have to change anything). Alternatively, you may follow the MySQL Getting Started slides to change data location, and in that case, make sure it is outside of the assignment folder.

3. Start MySQL and once you are logged in to MySQL, create a new database by the name

<YourLastname><YourFirstInitial>, e.g., Justin Case would create casej. 4. Download the two script files for the library database – createbooks.sql and addbooks.sql

– to use for this assignment. 5. In MySQL, use the source command to run createbooks.sql and then addbooks.sql. This

should create three tables – book, patron, and bookpatron – and populate those tables with data.

6. Run show tables to make sure that you see all the three tables.

7. For each table, run the select * command to check whether you have data in the table. For

example, run select * from book and you should see eight rows of data:

BCIS 3680 Enterprise Programming Assignment 5 - 2

8. Once your database is set up correctly, go to your deployment/assignment folder and proceed to

the next step. 9. Modify catalog.jsp so that it now lists summary information on the books in the collection:

BCIS 3680 Enterprise Programming Assignment 5 - 3

For each book, show its barcode, call number, title, and whether it is currently on loan (in the onloan column, 1 means on loan).

10. Modify main.jsp so that the username entered by the user in login.jsp is stored as a session

variable. In catalog.jsp, retrieve that session variable and display it in the upper left corner (see the above screenshot).

11. The title of each book is displayed as a hyperlink. When clicked, a page (book.jsp) should

appear and display detailed information on the book. The browser title bar displays the wording Book #, the barcode of the book, followed by the word Details. Again, the user name is retrieved from the session variable and displayed.

If a book is currently checked out to a patron, the page will show such information. For example, clicking barcode 107 causes the following page to be displayed:

If a book currently is not checked out to any patron, the page says words to that fact. Unlike the case when a book is checked out, here only the barcode of book is shown in the message; you don’t have to show the title of the book (you may explore on your own about how to do that):

BCIS 3680 Enterprise Programming Assignment 5 - 4

Hint: To obtain data to be used in this page, you will perform a join query on all three tables. The barcode of the book can be retrieved from either book or bookpatron table; title of the book from book table; and patron fname and lname from patron table. Follow the steps shown in my in-class demonstration or consult the Forta book to build the SQL statement. If a book is on loan, the returned ResultSet will not be empty and you will read the column values to build the displayed message. If a book is not on loan, the returned ResultSet will be empty and running the next() method will return false immediately. The foreign key relationships involved are as shown below:

Submission Due Date (firm): Monday, November 25, 2019 (11:59 PM) Below are steps to follow for submission:  Submit your assignments only through Canvas.  Assignments submitted to the instructor or grader by email will NOT be graded, unless you

are instructed to do so.  Zip the folder into 3680-HW5-YourLastName-YourFirstName.zip (RAR is fine too).  Submit the zip file as an attachment on Canvas.  Before the assignment is due, you may resubmit your work for unlimited times. Simply

attach a new version of your assignment. Only the attachment with the latest timestamp will be graded.

 Failure to comply with these requirements will cause point deduction.