LIS 558 (2001 Fall)
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
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.
Part 1: Table
Create a Microsoft Access database containing a table
for the acquisition department of a special library.
The table should include at least the following fields
(in any sequence):
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:
- price is in the range 0 to 1000
- department is a required field
- the default for order_date is the date
that the record is entered
Part 2: query
Create a Make-Table query query1 for
Query: all titles that were ordered within the last 30 days
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:
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:
(= 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
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.
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
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
Assume that one book can be put on reserve
by more than one instructor,
but that only instructors can put books on reserve.
- patron registration
- check-out of a book identified by call number
- check-in (return)
- reservation of books by instructors
- overdue notices
- queries about who has a particular book
- queries about which books a patron currently has on loan
Use the rules given by Jackson
or in the "Rules for relation
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.
Identify three instances
where you think table lookup(s) and/or referential
should be set up
for Microsoft Access tables corresponding to the normalized
Identify the type of each and
the child and parent fields involved
and justify your decisions.
Set up the necessary database elements,
including table lookup(s) and/or referential
and develop a form with buttons, named form1,
to perform the following tasks:
when a book is checked in,
the circulation record may be deleted,
but the record for the book must remain in the database.
- patron registration
- check-out of books,
displaying the patron name and patron type
once the patron identification is typed in
Hand in a 3.5" disk with the database,
a printout of each procedure/function
and a printout of your tables.
(The content of each table
must be the same as the printout.
Do not hide any fields.)
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
and call numbers of all books signed out to each patron.
Do either A or B.
Create a sample database (about 10 records)
for a special library's technical report collection
Call the file Textwork.tba.
Include the following seven fields in the database:
number of pages,
(The note field contains information
such as "missing",
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.
- a disk with all the necessary files
- a printout of the ten records
- your master password
- a comparison of DB/TextWorks with Microsoft Access
in the form of a printed table of no more than one page.
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): firstname.lastname@example.org
Faculty of Information and
University of Western
Canada, N6A 5B7