CSC 415

Problem Set 3

 
  1. Design a database for a library. Your database should be able to hold information about all the books in the library. In particular, for each book, your database must be capable of storing the ISBN number for a book (unique for a book), the title of the book, the author(s), the subject category(ies) as designated by the Library of Congress, an abstract for the book, year of publication, publisher, dates when it was checked out/returned and by whom (id. number (unique) and name of patron), whether a book is being used as a reference for a course, and if so which course number, and the due date on a book if it has been checked out and has not yet been returned.

    2.  

     
     
     

    Besides information about books, your database should also be able to hold information about the patrons. The library assigns a category to each patron---category 1, 2 or 3. The category of a patron determines the maximum number of books that the patron can have checked out at any given time, and also the duration that the patron can have a book. In particular, for each patron, your database should be able to store the patron's id. and name, the patron's address and telephone number, the patron's category, the maximum number of books the patron can have checked out at any given time, the number of books currently checked out by each patron, the number of overdue books currently checked out by the patron, and any fines that the patron has not paid.

    Note that the due date on a book that is checked out depends on the date it was checked out and the category of the patron who checked it out.

    Feel free to add any other information you need to make the database more realistic.

    All the relations in your database should be in BCNF.

    For each of your relations,  show a candidate key for the relation.
     

  2. Using your database design above, formulate the following queries in SQL.

    3.  

     
     
     

    Assume that you can subtract dates, d1 and d2, in the following sense: d1 - d2 is the number of days between d1 and d2 if d1 is after d2; is 0 if d1 = d2 or d1 is before d2. Assume Today stands for today's (the current) date.

    1. All the names of patrons who have overdue books.
    2. All the titles of books that have not been checked out in the last six months.
    3. All the titles of books that, in the last six months, have been in the library for less than 4 days (i.e., these books were checked out almost immediately after they were returned).
    4. All names of patrons who have checked out at least one book by an author, all of whose books are about philosophy (one of the subject categories).
    5. All names of patrons so that if the patron checked out a book by an author, then the patron, in fact, checked out all the books in the library holdings by that author. For example, suppose there are three books by Robert Browning in the library. If a patron, Smith, has checked out one of these books, but not all three, then Smith will not be in the result of this query.