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 10.5 = 31.5 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 10.5 = 63 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.

Call your database PRACTICAL. Points add up to 94.5 (almost the highest A). For more contribute your creativity.

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