LIS 558 (2001 Fall) - Assignments

For assignments 1 and 4, any files submitted should be in Microsoft Access 2000 format.

Hand in only the database and objects required by the assignment. Do not include any additional files on your disk or any additional objects in the database.

Keep backup copies of any files you hand in. You may need them for the next assignment or in case of emergency.

Assignment 1

Part 1: Table

Create a Microsoft Access database containing a table table1 for the acquisition department of a special library. The table should include at least the following fields (in any sequence): author, title, publication_year, order_date, department, price, and copies. Determine the primary key field(s) and set it/them up. Input 20 records: ensure that at least some satisfy the query in Part 2 below; also ensure that the records demonstrate the correctness of the report sort order in Part 3 by including at least one pair of records with the same author but different titles and at least one pair with the same author and title but different publication dates.

Set up validity checks for the table as follows:

Part 2: query

Create a Make-Table query query1 for table1.

Query: all titles that were ordered within the last 30 days (excluding today), with price (per copy) more than $40 and were charged to the departments "FIMS" or "Education". The query result table should show the following fields in the sequence indicated: title, price, order_date, and department. The table created by the query should be called answer1 and should be in the same database.

Part 3: report

Prepare a report report1 of table1 which contains the following fields in the sequence indicated: author, title, publication_year, price, copies, department, and cost (= copies * price * 1.07). Records in the report should be sorted by author (ascending), subsorted by title (ascending), and subsubsorted by publication_year (descending). The average price and total cost of all books in the entire database should be presented in the appropriate position. Complete book titles should be displayed. Your name, the time, and the date that the report is generated should be indicated at the bottom of the report (with the date in Medium Date format and the time in Short Time format). The report should have a proper title at the top of the report and a page number on the top of each page. It should be well laid out, in portrait, not landscape, style, with a width of no more than 18 cm.

Hand in a 3.5" disk with the database. Hand in also the printout of the report.

Assignment 2

Analyze the data requirements for a circulation system by drawing an entity-relationship diagram as described by Jackson or as outlined in the "Entity-relationship diagrams" Web page You do not need to include attributes in the diagram, but list separately the main attributes and indicate the key for each entity in the diagram. Don't forget to indicate both degree of relationship and membership class in your diagram. The functions that must be supported are
  1. patron registration
  2. check-out of a book identified by call number
  3. check-in (return)
  4. reservation of books by instructors
  5. overdue notices
  6. queries about who has a particular book
  7. queries about which books a patron currently has on loan
Assume that one book can be put on reserve by more than one instructor, but that only instructors can put books on reserve.

Assignment 3

Part 1

Use the rules given by Jackson or in the "Rules for relation generation from ER type diagrams" Web page to transform the entity-relationship diagram from Assignment 2 into a set of normalized relations and indicate a primary key for each relation. Explain which rule was applied in each case. Explain how each of the seven functions in part 1 will be implemented.

Part 2

Identify three instances where you think table lookup(s) and/or referential integrity/cascading should be set up for Microsoft Access tables corresponding to the normalized relations. Identify the type of each and the child and parent fields involved and justify your decisions.

Assignment 4

Set up the necessary database elements, including table lookup(s) and/or referential integrity/cascading, and develop a form with buttons, named form1, to perform the following tasks: Note that, when a book is checked in, the circulation record may be deleted, but the record for the book must remain in the database.

Hand in a 3.5" disk with the database, a printout of each procedure/function (properly identified), and a printout of your tables. (The content of each table must be the same as the printout. Do not hide any fields.)

Assignment 5

Mount a copy of your database from Assignment 4 in your FrontPage subweb. Provide a home page (Default.asp) and two kinds of query capabilities (either on the home page or on pages to which there are links on the home page): (1) display the call numbers of all the books signed out to a patron given the patron's identification; (2) display the patron name and patron type given the patron's identification. To facilitate testing, hand in a simple printout showing patron identifications and names and call numbers of all books signed out to each patron.

Assignment 6

Do either A or B.


Create a sample database (about 10 records) for a special library's technical report collection using DB/TextWorks. Call the file Textwork.tba. Include the following seven fields in the database: report number, author, title, keywords, number of pages, publication year, and note. (The note field contains information such as "missing", "being repaired", or even several sentences stating problems with the report.)

Each report should have several keywords. Some reports are single authored and some are multiple authored. Set up a master password and the silent password. Anyone without the master password should be able to access all the fields with read-only privileges. Set up (an) appropriate stop word list(s).

Input ten records.

Hand in


Select a DBMS other than Microsoft Access or DB/TextWorks which you can demonstrate in class; for example, Paradox 9.

Submit a brief evaluative comparison (about 600 words) of the DBMS that you have chosen with Microsoft Access.

Demonstrate interesting or important features of the DBMS in the class. Length of demonstration is negotiable and will depend partly on how many students choose this option. You should plan on at least 15 minutes. If two or more students choose to demonstrate the same DBMS, they should coordinate their presentations in order to avoid overlap.

(The mark for this assignment will be based on a combination of the submitted comparison and the class presentation.)


Last updated September 5, 2001.
This page maintained by Prof. Tim Craven
E-mail (text/plain only):
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7