Assignment 1: Design

In this assignment, you will be designing and will be writing a document for the library database system which contains:
  1. an E-R diagram  for the system. In particular, study the cardinalities of the relationship types. Write down your assumptions if necessary.
  2. Translate the above E-R diagram to the relational model (i.e. SQL CREATE  statements, implement your statements in ORACLE on matrix). In particular, specifiy primary key and foreign key constraints clearly. Give a description of the meaning of all attributes.
  3. This first assignment will be used in assignement 2.

Introduction

The objectives of the library system are to allow patrons to:
 and librarians to:


Information to be Contained in the System

  1. The system shall contain information about each valid patron of the system: name, password, address, Id, unpaid fines, the identity of each book the patron has currently withdrawn and its due-date.  It is assumed that the information about all patrons is part of the initial state of  the database (no interactions are needed to add or delete patrons from the system).
  2. The system shall contain information about each librarian: name, password, address, Id.  It is assumed that the information about all librarians is part of the initial state of  the database (no interactions are needed to add or delete librarians from the system).  Librarians can be patrons.
  3. The system shall contain information about each book in the library's holdings: isbn number, title author(s), year (of publication), copy number, shelf-Id, current status (on-loan, on-shelf, on-hold, on-loan-and-on-hold), the date on which the book entered loan and/or hold status.  For books on loan the system shall contain the Id of the patron involved.  For books on loan and overdue the system shall contain the date on which the last reminder letter was sent.  For books on hold the system shall contain an ordered list of Ids of patrons who have requested hold status.
  4. The system shall contain information about each shelf in the library: ShelfId, capacity (number of books that it can hold).


Integrity Constraints
The database shall satisfy the following integrity constraints.
    1. A book cannot be on hold for a particular patron for more than 3 days.  If after removing a book from hold status as a result of this constraint, the hold list is empty, the book reverts to the on shelf status.
    2. A patron cannot withdraw a book if his/her unpaid fines exceed $5.
    3. A patron cannot withdraw more than 2 books at any one time.
    4. The date on which the last reminder letter for an overdue book was sent cannot be more than one week old.
    5. There can be no more than 5 books on a shelf.
    6. A book on loan for more than one week becomes overdue.


Interactions with the System

In order to perform the following interactions with the system, a user  must login  first with a valid Name and password.  If login is successful the user's Id is returned by the DBMS to the application program. All subsequent interactions are assumed to have been initiated by that user until the session completes (the user logs out) .  Since Id is used to identify the user in these interactions, the application should retain Id until the end of the session, supplying it as a parameter when necessary to the DBMS.  The session is ended with logout, at which point the application can discard Id and the next interaction must be a new login.
withdraw - (patron)
The purpose of this interaction is to allow a patron to withdraw a book. title and one of the author names are supplied at the screen . The interaction fails if:
hold - (patron)
The purpose of this interaction is to allow a patron to place a book that is currently withdrawn or being held (for a different patron) on hold. title and one of the author names are supplied at the screen. An ordered list is maintained of all patrons who have placed the book on hold. The interaction fails if
  1. The book is on the shelf
    1. The patron has withdrawn and not yet returned the book
    2. The book is not in the library's collection
    3. The patron has already withdrawn 5 books.
    4. The patron owes more than $5.

return - (patron)
The purpose of this interaction is to allow a patron to return a book that he/she has withdrawn. title and one of the author names are supplied at the screen. If the book is overdue, the patron's charge record is updated by an amount of $1 a day. If the book is on hold, a call is made to the patron at the head of the hold list (you can ignore the call in the transaction, but you should record the date that the call was made). The interaction fails if the book is not recorded as being withdrawn by that patron.

add - ( librarian)
The purpose of this interaction is to allow a librarian to add a new book to the library's holding.  isbn, title, year, author(s), and shelf-Id (of the shelf on which the book is to be stored) are supplied at the screen. Multiple copies of the same book can exist in the library's holdings. The interaction fails  if
move - (librarian)
The purpose of this interaction is to allow a librarian to move a book from one shelf to another. title, one of the author names, and copy number (to identify the book) and shelf-Id (to identify the new shelf) are supplied. The interaction fails if
remove - ( librarian)
The purpose of this interaction is to allow a librarian to remove a book from the library's holding.   title and one  of the author names are supplied at the screen. The interaction fails if the book is not in the library's holdings or if the initiator is not a librarian.

search - ( patron)
The purpose of this interaction is to allow a patron to search for a book in the library's holdings.   One or more of the following: title, author, year are supplied at the screen. Either title or author must be supplied, however the title need not be complete - a substring of the title might be supplied - and only a single author can be supplied (even though books can have multiple authors).  A range of years can be specified.  For example, the search "list all books with the word `hiking' in the title written by Smith (there might be coauthors) between 1995 and 2000.  The title, author and year of all books that satisfy the search criterion are  output.

pay - (patron)
The purpose of this interaction is to allow a patron to pay a fine.  The amount of payment is deducted from the balance owed. The  amount of the payment is input at the screen. Any excess is considered a donation to the library.  The amount of fine remaining is displayed on the screen.

next-day - (librarian)
The purpose of this interaction is to allow a librarian to record the fact that another day has passed.  The interaction fails if the initiator is not a librarian.  Actions which depend on the date (e.g., removing a book from hold status) should be automatically triggered by this interaction.