You're in charge of a library, which has three patrons (listed below):

lbird

JPG BMP 		mjordan

JPG BMP 		tkukoc

JPG BMP

The library contains a total of four books:

dek001

JPG BMP 		dek002

JPG BMP 		con001

JPG BMP 		hpb001

JPG BMP

Here's the data that the librarian has:

Who What When Returned Rating
Bird DEK001 9/1 9/2 3.5
Kukoc DEK002 9/1 9/2 3.8
Jordan CON001 9/1 9/2 3.7
Bird HPB001 9/1 9/2 3.9
Kukoc DEK001 9/3 9/4 3.85
Bird DEK002 9/3 9/4 3.75
Kukoc CON001 9/3 9/4 3.7
Jordan HPB001 9/3 9/4 3.9
Bird DEK001 9/5 9/6 4.0
Jordan DEK002 9/5 9/6 3.8
Bird CON001 9/5 9/6 3.5
Bird HPB001 9/5 9/6 3.9
Jordan DEK001 9/8 - -
Bird DEK002 9/10 - -
Jordan CON001 9/15 - -
Jordan HPB001 9/20 - -

What you need to do (the exam is open-book):

1. Create a database to store this data. (3 x 5 = 15 points). Note that:

  1. For each book you need to store the picture of the cover.
  2. For each student you need to store the picture of the student.
  3. You are not allowed to store a picture in more than one place.
2. Design, run and save the following queries. (6 x 5 = 30 points)

  1. List all the books, sorted by their current average rating (highest on top).
  2. List all the patrons, sorted by the all-time number of books checked out (largest on top).
  3. List the current (overall) average rating of the books in the library.
  4. Show all the books that have an average rating higher than the overall average rating.
  5. List all the books that are currently checked out, and the number of days they've been out.
  6. List all the books that are currently checked out and have been out for 14 or more days.
Please take the current date to be (literally) September 26, 2001. Name your queries Query1-6.

3. Create the following forms. (2 x 15 = 30 points)

  1. Transactions Listed by Books contains information about a book (title, ISBN, picture) and a subform that contains the information about what patron checked this book out, when, when was the book returned (if it's currently in the library), and rating recorded for that book.

  2. Transactions Listed by Patrons contains information about a patron (name, username, picture) and a subform that contains the information about all the books this patron has checked out, when, when they were returned (if they're in), and with what rating.

4. Create the following report. (10 points)

Report the book ratings by book and patron. For each book list all the patrons and their ratings. Use report wizard on the table or query of your choice. At the end of a section listing patron ratings for a particular book provide the max, min, and the average rating for that book. Save the report as Book Ratings, Patron by Patron.
5. Create the following switchboard to help us grade the exam. (10 points)

From the main switchboard have five items: view tables, view queries, view forms, view report, exit application. Each takes us to a new switchboard. All switchboards take you back to the main switchboard, except the view report one, which has two options: return to main switchboard, and view report.
Call your database PRACTICAL. Points add up to 95 (highest A). For more contribute your creativity.

URL for this page: http://www.cs.indiana.edu/classes/a114/fall2001/lectures/nine