Homework 4 Solution

Designing a database




There is a team developing an application for the network of libraries forming the Chicago Public Library System. They describe a set of operations that they want their application to perform. Based on this set of rules, they ask you to develop a database which will enable their application to operate.




The operations of their web-based interface are as follows:




A customer may check out up to 5 books at a time. (The operation is still performed one book at a time) A customer may check in a book.




A customer may put a hold on up to 3 books at a time. (The operation is still performed one book at a time) A customer may cancel their hold on a book.




A customer may search for books based on genre, number of pages, title, author, publisher, year published, DDN, ISBN.

A customer may search for the location and status of copies of a book.

A staff member may validate the condition of a book that has been checked in.

A staff member may return a book that has been checked in to a specific shelf.

A staff member may move books from one library to another.

A staff member and the customer in question may find which books a customer currently has checked out.

A staff member may create new member accounts.

An administrator may search staff members to get various statistics (books shelved per worker, etc.)




An administrator may acquire a list of staff members, narrowed by various criteria (works in the morning, reception, etc) An administrator may adjust the salaries of staff members.




An administrator may revoke membership privileges of customers An administrator may hire and fire staff members.




A customer may not view or adjust salaries of staff members.

A customer may view but not adjust the schedule of staff members




A. Include a copy of your design from HW2 in your submission (pdf is fine). You may wish to change your design (choose an alternate schema, add/remove tables) based on the questions below and the features above.




B. Build a database based on your design. Put the commands used to generate the table in CreateLibrary.sql.




C. For each of your tables, put at least 5 records with actual values in each of the tables. Note that you may need to add some specific items to your database in order to execute the queries in the latter part of the assignment. Put the commands used to put these values into your database into PopulateLibrary.sql.

Relational Algebra




Given your database design, construct sql queries to build tables answering the following questions. Put the SQL query that generates the answer into Q##.sql where # is the number of the question. For example, the answer to the first question should be placed into Q01.sql.




What are the titles of the books by the author “J. K. Rowling”?
























What genre do the books with Dewey Decimal numbers in the 510s have? If you are not familiar with Dewey Decimal numbers, they are an identifier used by libraries to help organize books in the library, they do not uniquely identify copies of books.


















At which libraries can you find copies of the book titled “A Song of Ice and Fire”?





















What is the library number of the customer “Samwise Gamgee”?
























Which books does the customer from the previous question have checked out? Do not assume that you already have their library number.



























What are the titles of the books that have both the Fantasy and Western genre tags at Washington Public Library? Note that this query should not return Horror titles that are not also Young Adult titles.


















Page 2 of 5
Create two copies of the book Anne of Green Gables at the Washington Public Library and insert them into your database. Information about this book can be found at this Library of Congress link: https://lccn.loc.gov/2013004485












Create two users, Anne Shirley and Gilbert Blythe. If you need additional information besides names, ask on piazza for it. For example, if you require a phone number to register a user, Anne’s phone number is 266-2684 and Gilbert’s is 589-5965






Have Anne Shirley check out a copy of Anne of Green Gables.






Have Gilbert Blythe check out a copy of Anne of Green Gables.






Have Anne Shirley check in a copy of Anne of Green Gables.






Have Gilbert Blythe check out a copy of Anne of Green Gables.






Have Gilbert Blythe check in a copy of Anne of Green Gables.






Have Gilbert Blythe check in a copy of Anne of Green Gables.






Who is scheduled to work on December 11th, 2018?






Which staff were working on August 1st, 2018?









Which copies of “Little Red Riding Hood” are currently on hold?









How many books are currently checked in?









How many books are currently on hold?





















Page 3 of 5

How many books are currently checked out?









What books were checked out on August 15th, 2018?









List all the dates that the user Jean Valjean checked in books, listing each date only once.









For each month in 2018, how many books were checked out?









Between June and December of 2018, what were the 5 most popular genres among checked out books?









Remove all books at the “Grand Library at Alexandria” library from the database.

































































































































Page 4 of 5