Homework Assignment #7 (Databases)

In this assignment, you will create a database to store data about a fictional library, its books, its patrons, which books its patrons have checked out, and the rating they gave them. Here is the information that will be stored in the database:

Full Name Patron ID Checkout Date Rating Book Title Book Author ISBN
Erik Wennstrom ewennstr 1/10/14 9 American Gods Neil Gaiman 0380789035
John Wennstrom jwennstr 2/2/14 7 World's End Upton Sinclair9997531590
Erik Wennstrom ewennstr 3/23/14 4 World's End Upton Sinclair9997531590
Karteek Pittala kpittala 3/23/14 6 Different Seasons Stephen King0451167538
Sudhakar Gollapinni sudhgoll 4/25/14 9 The Alchemist Paulo Coelho0061122416
Erik Wennstrom ewennstr 6/30/14 8 American Gods Neil Gaiman 0380789035
Erik Wolpaw erwolpaw 7/1/14 7 World's End Neil Gaiman 140123402X

Make sure that you store the data in three separate tables: one for the patrons (including fields for first name, last name, and patron id), one for the books (including fields for title, ISBN, and author), and one for the checkouts (with fields for checkout date, patron ID, ISBN, and rating). Each table should have an appropriate primary key. Make sure you grant access to the database to me and both of your AI's.

If you want to, you can assume that each book can only be checked out once per day.

After creating the database, you should write queries to complete each of the following tasks:

  1. List the titles and authors of all the books in the library.
  2. List the full names of all patrons in the library, in alphabetical order (by last name).

(2014-7-2 10:50am) Since we didn't get through as much in class as I wanted to, I've removed some of the queries from the assignment.

When you send in your e-mail, include the name of your database and your MySQL port number (so that your graders don't have to waste time looking it up). Also write down all of the queries (I mean, write down what you typed into MySQL to get the lists above), so that we can test them to see if they work as they're supposed to. Remember to make sure that MySQL is running so that your AI's can see the database you created and test your queries.

This assignment is due Thursday, July 3rd before class.