CSCI A114 / INFO I111
Homework Three

First semester 2001-2002

Due date
At the end of lab 7.

Late policy
No late policy. You need to turn this on time.

Work policy
Working in groups is accepted but you need to turn in your own version of the assignment. Also, please make sure you read, understand, and comply with the Computer Science Department's Statement on Academic Integrity before turning in your assignment.

Task
Explained in detail below.
Grading
There are a number of things that you will need to accomplish. How exactly you will accomplish them is up to you. We will simply enumerate the goals and count how many you will have accomplished at the end, when you turn it in, for your grade.

Grades will be posted on-line.

Homework Three

First, let's review the rules of relational database design.

We use the entity-relationship model. We also said that this model does an adequate, albeit imperfect, job of modeling real-world situations, such as business enterprises or the records kept by schools, hospitals, governments, and so on, where database systems are likely to be used. However, if we view the structures defined in the entity-relationship model as conceptual schemes, we shall not be grossly deceived.

So we will have the following recipe:

  1. first list the entities, with attributes
  2. then list the relationships Make a table for each entity, identify the key attributes.

    Make a table for each relationship, in which the columns are keys in the tables for the entities that the relationship is connecting.

    Please apply this rule to the following problem for which no E-R diagram is given.

    From here on you are (more or less) on your own.

    The Computer Science Department has a small library.

    Here are some examples of books you should expect to find in this library:

    1. Friedman,
    2. Knuth,
    3. Ullman,
    4. Gosling,
    5. Salomaa,
    6. Barendregt,
    7. Conway,
    and many other outstanding books. Design and populate a database that a library could use.

    Here's what's needed for this assignment:

    1. Books should be stored in the database.
    2. Library patrons (students) should be stored in the database.
    3. When a patron checks out a book that information should be stored in the database.
    4. When a patron returns a book we also want to store that information.
    5. When a patron returns a book the patron can rate the book (scale 0-4, 4 is best).
    Books can be borrowed for a period of two weeks only.

    You want to be able to query the database in the following ways:

    1. List all books currently overdue sorted by the patron's name.
    2. List all existing books sorted by their average ranking.
    3. List the patrons sorted by total number of books ever borrowed.
    4. List all good books (average ranking greater than 3.3) that are overdue.
    5. List all books on a particular topic (e.g., Access) sorted by their ranking.

    Accomplishing each of these goals amounts to 10 points on a 0-100 scale.

    I will post some sample data here tonight, and an example scenario.

    Last updated: September 13, 2001 by Adrian German for A114/I111