CSC 4480: Phase 1: Project Proposal
Ken's Library

Due to gerrymandering in Bryn Mawr, many residents of the town aren't allowed to use the public library and are forced to travel far away to gain access to intellectual content. One particularly disgruntled resident, Ken, decided to make his own library for those who wish to join. Unlike the "public" Bryn Mawr library, Ken's library will be truly free - anyone can join. The library is funded from generous donations of wealthy benefactors, and accepts book donations.

Business Rules:

  1. Ken's Library has many different types of content. While content comes in various forms, all content contains a unique ID (unique to Ken's Library) and a publication date, and a cost.
  2. The types of content include books, magazines, newspapers, CDs (music), and DVDs (movies). Since multiple copies of content can exist in a library, copies of the identical content are given a copy number.
  3. A book has a title, author(s), and a publisher. A magazine has a magazine name, volume number, issue number. Newspapers have a name, volume number, and an issue number. CDs have an artist name, album name, genre, and a record company. DVDs have a Title, genre, and the movie producer.
  4. Patrons of the library should be able to search through the content of the library and view information pertaining to the content.
  5. Patrons should also be able to see what copies they currently hold, and their due date, which is established when a book is checked out.
  6. A librarian is a patron of the library, but has additional privileges. A librarian can permanently add or remove books from the library system. To be able to assist users, a librarian will have access to all of patron data. Ordinary patrons do not have access.
  7. A Patron has a patron ID, a name (first and last), and an email address. A patron can borrow as many books as they want. A patron may have charges on his/her account.
  8. Users can donate a book and see what copies they have donated.

Exceptions:

  1. A patron cannot borrow multiple copies of a book.
  2. Typically, a patron cannot add or remove books. However, if the patron has misplaced or destroyed their copy, they can mark their copy as lost. A lost copy is removed from the database and a charge is applied to the patron's account.

Questions:

  1. How many copies are available for a particular work?
  2. How many books (or another type of content) does Ken's Library have?
  3. Which items am I currently borrowing?
  4. Are there any charges on my account?

Phase 2: ER Diagram

Phase 3: Relational Schema

Phase 4: DDL and DML Implementation

Phase 5: Sample DML Queries