Assignment 3: Demonstration

In this assignment you will be demonstrating the code that performs the interactions listed below.

• You should work in pairs.  Pairs have to come from the same section.
• You can use the spec/design document of either member of the pair, or some combination of the two to create a spec/design document for the system you de,pmstrate.  The document will be collected at the time of the demo.
• The interactions are divided into two sets: set A = {withdraw, return, hold, pay},  set B = {add, remove, move, search, next-day}.  Each member of the group will be responsible for one set and the grade for a member will be (partially) based on the performance of the set that member implements.
• Demos will be held in the last week of the semester.

1. Give an E-R diagram  for the system.   Use arrows or thick lines to represent constraints appropriately. Write down your assumptions if necessary.
2. Translate the E-R diagram to the relational model: all SQL CREATE  statements must be provided. Specify primary key, candidate key, and foreign key constraints.
3. Give a description of the meaning of all attributes.
4. Explain how you intend to enforce each integrity constraint:  in the schema or by checking in the code.  If the latter, in which interactions does the check need to be made?
5. Specify all functional dependencies.
6. For each table, state what normal form it is in and justify your answer.

• Due date: 11/8/2001.  Hand in a hardcopy in class.
• Read the following project description again very carefully since some revisions (underlined) have been made.
• The project is to be done individually.

Introduction

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

• 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

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, publisher, publisher's city, 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 the year of birth of each author.
5. The system shall contain information about each shelf in the library: shelf-Id, capacity (number of books that it can hold).

Assumptions

• Author names (but not patron names) are unique.
• An author does not (co-) author more than one book per year.
• An author does not (co-) author more than one book with a particular title.

The database shall satisfy the following integrity constraints.

1. A patron can request a book that is currently withdrawn.  This fact needs to be stored in the database, and when the book is returned it should be placed on hold status for at most 3 days (the count starts when the book is returned).  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. The number of books on a shelf cannot exceed its capacity.
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:

• The book is already withdrawn.
• The book is not in the library's collection.
• The patron has already withdrawn 2 books.
• The patron owes more than $5.
• The book is on hold and the patron is not at the head of the hold list.

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

The patron has withdrawn and not yet returned the book

The book is not in the library's collection

The book is on the shelf

The patron has already withdrawn 2 books.

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

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

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

• The book is not in the library's holdings.
• The initiator is not a librarian.
• The shelf is full.

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, if the book is currently withdrawn, 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.