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 Sinclair | 9997531590 |
Erik Wennstrom | ewennstr | 3/23/14 | 4 | World's End | Upton Sinclair | 9997531590 |
Karteek Pittala | kpittala | 3/23/14 | 6 | Different Seasons | Stephen King | 0451167538 |
Sudhakar Gollapinni | sudhgoll | 4/25/14 | 9 | The Alchemist | Paulo Coelho | 0061122416 |
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:
(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.