Assignment 1: Design

In this assignment, you will be designing and will be writing a document for the library database system which contains:

an E-R diagram for the system. In particular, study the cardinalities of the relationship types. Write down your assumptions if necessary.
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.
This first assignment will be used in assignement 2.

Due date: March 4th, 2002. Hand in a hardcopy in class
Read the following project description. This description will be useful later on also.

Introduction

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

withdraw and return books
search the library's books
pay overdue charges

and librarians to:

add/delete books to/from the library's holding
move books from one shelf of the library to another.

Information to be Contained in the System

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).
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.
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.
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.

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.
A patron cannot withdraw a book if his/her unpaid fines exceed $5.
A patron cannot withdraw more than 2 books at any one time.
The date on which the last reminder letter for an overdue book was sent cannot be more than one week old.
There can be no more than 5 books on a shelf.
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.

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:

The book is already withdrawn.

The book is not in the library's collection.

The patron has already withdrawn 5 books.

The patron owes more than $5.

The book is on hold and the patron is not at the head of the hold list.

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; 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.; 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

The initiator is not a librarian (one implementation of this is that your interface ensures that only a librarian can initate this interaction).

The shelf is full.

The book specifications conflict with a book already in the library's collection (e.g., if there is a book in the library's collection with the same isbn number but a different author, title, or year.

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

The book is not in the library's holdings.

The initiator is not a librarian.

The shelf is full.

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.; 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.; 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.; 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.