###############################################
#Written by David Tam and Simeon Yacoob, 1998.#
#davidkftam@netscape.net Copyright 1999#
###############################################
CSC 434F - Database Management Systems
Assignment #3
David Tam
Simeon Yacoob
Harfrey Ng
Program Documentation
=====================
Part A - Static SQL
-------------------
When the program is started, the librarian must first enter the
Branch Id of the current branch in order to access the various functions.
Five additional functions were added to allow the librarian to view the
contents of the five underlying tables.
The following assumptions were made in the design of the program.
By the design mentioned in the first paragraph, we are assuming the
borrower may only borrow a book from the current branch. This seems to
be a reasonable assumption because a borrower must be physically at a
branch in order to borrow a book.
A card holder may NOT sign out more than one copy of a book from the
same branch. We make this assumption because of the inherent
constraint built into this database design. Since the primary key of
the Loans table is CardNo, ISBN, BranchId these three fields must
always identify a unique tuple. This database design CAN NOT support a
card holder with more than one copy of a book from the same branch
because it would mean that the Loans table contains two tuples that
have the same primary key.
The librarian must enter the due date field manually because the
the duration of loans will vary according to the type of book.
e.g. "DB2 Reference Manual" is an overnight loan
while "Little Red Riding Hood" is a 2 week loan
and "The Never Ending Story" is a 3 week loan
The ISBN must be known in order to sign out the book. This number is
normally read using a barcode scanner so this shouldn't be an issue.
We adopt the assumption that in the BookCopies table, qty is the constant
total copies of a book at the specified branch. We assume that it is the
librarian's responsibility to check whether the book has exceeded the due date
When inserting a new book into the database, the new book must be
placed in a branch. In other words, a book can not be added without
placing it in a branch.
A book may not be deleted from the Books table if there are any copies
on loan. The number of bookcopies deleted at a particular branch can not
exceed the number of copies available for loan (not taken out by users).
This allows some flexibilty when deleting copies of books. For instance,
if a branch has 7 copies of a book and 3 of those are currently on loan,
then a maximum of 4 copies may be deleted.
Part B - Dynamic SQL
--------------------
The query program was built using the handout to define the grammer
for possible queries. The program does not make any assumptions at to the
input that the user may enter. It parses any query string and if the query
does not adhere to the defined grammer, an error message will be displayed
and the query will be rejected.
The parser will only accepy attributes from the Books table, but
does not allow the ISBN, since anyone with the ISBN of a book should have
access to all information concerning the book.
As long as a query adheres to the given grammar, the program will
prepare the DB2 equivalent statement and display all books that match one
book at a time while listing all the branches that have the book and how
many copies each branch has available.
(
geocities.com/siliconvalley/campus/9640/4thYear) (
geocities.com/siliconvalley/campus/9640) (
geocities.com/siliconvalley/campus) (
geocities.com/siliconvalley)